Modeling Your Data

1. Modeling Your Data Chapter 2

2. Part II Discussion of the Model: Good Design/ Bad Design?

3. Design : The Obvious ! • Use meaningful and descriptive names (it’s for the human after all) • Keep as simple as possible, and relevant to the application at hand • Avoid redundant constructs • Express all constraints, if possible, as then the DBMS will help you to enforce them

4. Conceptual Design Using ER Model • Design choices: • Should a concept be modeled as an entity or an attribute? • Should a concept be modeled as an entity or a relationship? • Identifying relationships: Binary or ternary? Aggregation? • Constraints in the ER Model: • A lot of data semantics can (and should) be captured. • But some constraints cannot be captured in ER diagrams.

5. Entity vs. Attribute • Should addressbe an attribute of Employees or an entity (connected to Employees by a relationship)?

6. Entity vs. Attribute • Should addressbe an attribute of Employees or an entity (connected to Employees by a relationship)? • Depends upon the use we want to make of address information and its semantics : • If we have several addresses per employee, address must be an entity (since attributes cannot be set-valued). • If the structure (city, street, etc.) is important, e.g., we want to retrieve employees in a given city, address must be modeled as an entity (since attribute values are atomic).

7. Entity vs. Attribute • Reminder : • Do not introduce un-necessary entities (and complexity) if not needed for your application !

8. name dname ssn lot did Employees Entity vs. Attribute to from budget Departments Works_In4

9. name dname ssn lot did Employees Entity vs. Attribute • Does Works_In4 allow an employee to work in a department for two or more periods??? to from budget Departments Works_In4

10. name dname ssn lot did Employees Entity vs. Attribute (Contd.) • Works_In4 does not allow an employee to work in a department for two or more periods • à no multi-valued attributes in ER ! to from budget Departments Works_In4

11. name dname ssn lot did Employees Entity vs. Attribute (Contd.) • Works_In4 does not allow an employee to work in a department for two or more periods. • Similar to the problem of wanting to record several addresses for an employee: We want to record several values of the descriptive attributes for each instance of this relationship. • Accomplished by introducing new entity set, Duration. to from budget Departments Works_In4 • What do ?

12. name dname ssn lot did Employees dname did budget Duration to from Entity vs. Attribute to from budget Departments Works_In4 name ssn lot Works_In4 Departments Employees

13. Entity vs. Relationship? since dbudget name dname ssn lot did budget Departments Employees Manages2

14. Entity vs. Relationship since dbudget • What if a manager gets a separate discretionary budget for each dept ? • What if a manager gets a discretionary budget that covers all managed depts? name dname ssn lot did budget Departments Employees Manages2

15. Entity vs. Relationship since dbudget name dname ssn lot did budget • ER diagram OK if a manager gets a separate discretionary budget for each dept. • What if a manager gets a discretionary budget that covers all managed depts? • Redundancy: dbudget stored for each dept managed by manager. • Misleading: Suggests dbudget associated with department-mgr combination. Departments Employees Manages2

16. Entity vs. Relationship since dbudget name dname • Discretion-ary budget of manager that covers all managed depts? ssn lot did budget Departments Employees Manages2 name ssn lot dname since did Employees budget Departments Manages2 ISA This fixes the problem! Managers dbudget

17. name ssn lot Employees Policies policyid cost Binary vs. Ternary Relationships pname age Dependents Covers

18. name ssn lot Employees Policies policyid cost Binary vs. Ternary Relationships pname age Dependents Covers • What if each policy is owned by just 1 employee? • What if each dependent should be tied to only 1 covering policy?

19. name ssn lot Employees Policies policyid cost name ssn lot Employees Beneficiary Policies policyid cost Binary vs. Ternary Relationships pname age Dependents Covers • What do additional constraints in 2nd diagram? Bad design! pname age Dependents Purchaser Better design?

20. name ssn lot Employees Policies policyid cost name ssn lot Employees Beneficiary Policies policyid cost Binary vs. Ternary Relationships pname age Dependents Covers Bad design pname age Dependents Purchaser Even Better Design

21. Binary vs. Ternary Relationships • Previous example illustrated when two binary relationships better than one ternary relationship. • How about ternary relation Contracts : • entity sets Parts, Departments and Suppliers, • and has descriptive attribute qty. Qty pnum num Parts Suppliers contract Department D-id

22. Binary vs. Ternary Relationships • Ternary relation Contracts relates entity sets Parts, Departments and Suppliers, and has attribute qty. • What about following binary relationships : • S “can-supply” P, • D “needs” P, and • D “deals-with” S • No combination of binary relationships is an adequate substitute: • Together 3 binary relationships don’t imply that D has agreed to buy P from S. • Also, how could we record qty?

23. Summary of ER • There are often many ways to model a given scenario! Not one correct ER design • Analyzing alternatives can be tricky, especially for a large enterprise. • Common choices include: • Entity vs. attribute, entity vs. relationship, binary or n-ary relationship, whether or not to use ISA hierarchies, and whether or not to use aggregation. • Ensuring good database design: - Resulting relational schema should be analyzed and refined further. FD information and normalization techniques useful.