Database Design

1. Database Design Chapter 2

2. Goal of all Information Systems • To add value • Reduce costs • Increase sales or revenue • Provide a competitive advantage

3. Information System (IS) Project Success • On-time • Within budget • Meets customer’s expectations

4. IS Project Development • Model – a simplified view of a real-world system • Three common types of models • Process models (data flow diagrams) • Class or object models* • Event models (sequence charts which show timing of events)

5. Initial Steps in Database Design • Identify the exact goals of the system. • Talk with the users to identify the basic forms (inputs) and reports (outputs). • Identify the data items to be stored. • Design the classes (tables) and relationships. • Identify any business constraints. • Verify the design matches the business rules.

6. Important Reminder • Business rules are very important. For example: • There is a single individual designated as the order placer for each company purchasing products from your organization • A customer’s order cannot be placed until all outstanding bills have been paid • Customer’s address and phone number must be on file before an order can be placed

7. Identifying User Requirements • Interview users • Observe operations in the firm • Review forms and reports

8. Business Objects • All business deal with entities or objects • Customers • Products • Employees • Sales • When developing IS systems, we use the term entity to describe some item in the real world that we wish to track

9. Entities/Classes Customer Name CustomerID LastName FirstName Phone Address City State ZIP Code Attributes or Properties Methods (optional for database) Add Customer Delete Customer *In relational databases, tables represent each class with attributes being reported in columns and individual entries being stored in rows (tuples)

10. Class Diagram

11. Primary Keys • Every database table must have a primary key so that tables can be linked • The primary key must be a unique identifier for each entry (row) in the table • Name is not a unique identifier • Don’t use SSN

12. Associations and Relationships among Entities (classes) • Cardinality Constraint identifies: • The number of instances (multiplicity) one entity can be associated with another • One-to-one • One-to-many • Many-to-many • Whether the association is mandatory or optional • Optional association • Mandatory association

13. Multiplicity Examples: * 1 Order Customer Each customer can place many orders An order is placed by 1 customer * * Employee Tasks Each employee can perform several tasks Tasks can be completed by several different employees

14. Mandatory? Examples: 0 1 Order Customer A customer does not have to place an order An order must be placed by a customer 1 1 Employee Tasks Each employee must perform tasks Tasks must be completed by an employee

15. Alternate Terminology 1…1 0…* Customer Order Mandatory-One Optional-Many

16. Many-to-Many (N-ary) Associations • When two or more classes have a many-to-many association • Must be eliminated by adding a new entity called an associative entity

17. N-ary Associations Employee * * Component * * Product

18. N-ary Association Example Employee Name ... 1 * Assembly 1 * * 1 Component CompID Type Name Product ProductID Type Name Assembly EmployeeID CompID ProductID Multiplicity is defined as the number of items that could appear if the other N-1 objects are fixed. Almost always “many.”