CSE544 Data Modeling, Conceptual Design

1. CSE544Data Modeling,Conceptual Design Wednesday, April 7, 2004

2. Outline • ER diagrams (Chapter 2) • Conceptual Design (Chapter 19)

3. Database Design Data Modeling SQL Tables Refinement Files E/R diagrams Relations

4. name buys Person Product price name ssn Relational Schema Design Conceptual Model: Relational Model: plus FD’s Normalization: Eliminates anomalies

5. Entity / Relationship Diagrams Attributes address Product Entity sets buys Relationships

6. name category name price makes Company Product stockprice buys employs Person name ssn address

7. Keys in E/R Diagrams • Every entity set must have a key name category price Product

8. 1 2 3 1 2 3 1 2 3 a b c d a b c d a b c d Multiplicity of E/R Relations • one-one: • many-one • many-many

9. name category name price makes Company Product stockprice What doesthis say ? buys employs Person name ssn address

10. Product Purchase Store Person Multi-way Relationships

11. Invoice VideoStore Rental Movie Person Arrows in Multiway Relationships Q: what does the arrow mean ? A: if I know the store, person, invoice, I know the movie too

12. Arrows in Multiway Relationships Q: what do these arrow mean ? Invoice VideoStore Rental Movie Person A: store, person, invoice determines movie and store, invoice, movie determines person

13. Invoice VideoStore Rental Movie Person Arrows in Multiway Relationships Q: how do I say: “invoice determines store” ? A: no good way; best approximation: Incomplete (why ?)

14. Roles in Relationships What if we need an entity set twice in one relationship? Product Purchase Store buyer salesperson Person

15. Attributes on Relationships date Product Purchase Store Person

16. Converting Multi-way Relationships to Binary ProductOf date Product Purchase StoreOf Store BuyerOf Person Need arrows here ! Which direction ?

17. From E/R Diagramsto Relational Schema • Entity set  relation • Relationship  relation

18. Entity Set to Relation name category price Product Product(name, category, price) name category price gizmo gadgets $19.99

19. Relationships to Relations price name category Start Year name makes Company Product Stock price Makes(product-name, product-category, company-name, year) (watch out for attribute name conflicts)

20. Relationships to Relations price name category Start Year name makes Company Product Stock price No need for Makes. Modify Product: Product(name, category, price, startYear, companyName)

21. Multi-way Relationships to Relations address name Product Purchase Store price name Purchase(prodName,stName,ssn) Person ssn name

22. Design Principles What’s wrong? Purchase Product Person President Country Person Moral: be faithful!

23. Design Principles:What’s Wrong? date Product Purchase Store Moral: pick the right kind of entities. personAddr personName

24. Design Principles:What’s Wrong? date Dates Product Purchase Store Moral: don’t complicate life more than it already is. Person

25. Subclasses name category price Product isa isa Software Product Educational Product platforms Age Group

26. name category price Product isa isa Software Product Educational Product platforms Age Group Product Subclasses to Relations Sw.Product Ed.Product Notice: subclass = subset Alternative: disjoint classes (Java, C++)

27. FurniturePiece Company Person Modeling UnionTypes With Subclasses Each piece of furniture is owned either by a person, or by a company

28. Person FurniturePiece Company ownedByPerson ownedByPerson Modeling Union Types with Subclasses Solution 1. Acceptable, imperfect (What’s wrong ?)

29. Modeling Union Types with Subclasses Solution 2: better Company Owner isa isa ownedBy Person FurniturePiece

30. Referential Integrity Constraints makes Product Company Each product made by at most one company. Some products made by no company makes Product Company Each product made by exactly one company.

31. Other Constraints makes <100 Product Company What does this mean ?

32. Weak Entity Sets Entity sets are weak when their key comes from other classes to which they are related. affiliation Team University sport number name University(name) Team(universityName, number, sport)

33. R1 R2 R3 Weak Entity Sets E3 E1 A3 A1 A2 B3 E2 What are the keys ? E4 A4

34. Schema Refinement • For the relational model • Relation: R(A1, A2, …, Am) • Schema: relation name, attribute names • Instance: a mathematical m-ary relation • Database: R1, R2, …, Rn • Schema • Instance • Schema refinement = normalization

35. First Normal Form (1NF) • A database schema is in First Normal Form if all tables are flat Student Student Takes Course May needto add keys

36. More Normal Forms • Based on Functional Dependencies • 2nd Normal Form (obsolete) • 3rd Normal Form • Boyce Codd Normal Form (BCNF) • Based on Multivalued Dependencies • 4th Normal Form • Based on Join Dependencies • 5th Normal Form Discussnext

37. Functional Dependencies • A form of constraint • hence, part of the schema • Finding them is part of the database design

38. Meaning: If two tuples agree on the attributes A1, A2, …, An then they must also agree on the attributes B1, B2, …, Bm Functional Dependency: A1, A2, …, An B1, B2, …, Bm Functional Dependencies

39. Functional Dependencies Definition: A1, ..., An  B1, ..., Bm holds in R if: t, t’  R, (t.A1=t’.A1  ...  t.An=t’.An  t.B1=t’.B1  ...  t.Bm=t’.Bm ) R t if t, t’ agree here then t, t’ agree here t’

40. Examples EmpID Name Phone Position • EmpID  Name, Phone, Position • Position  Phone • but Phone  Position E0045 Smith 1234 Clerk E1847 John 9876 Salesrep E1111 Smith 9876 Salesrep E9999 Mary 1234 Lawyer

41. Example Product(name, category, color, department, price) Consider these FDs: namecolor categorydepartment color, categoryprice What do they say ?

42. Example • FD’s are constraints: • On some instances they hold • On others they don’t namecolor categorydepartment color, categoryprice Does this instance satisfy all the FDs ?

43. Example namecolor categorydepartment color, categoryprice What about this one ?

44. Inference If some FDs are satisfied, thenothers are satisfied too namecolor categorydepartment color, categoryprice If all these FDs are true: name, categoryprice Then this FD also holds: Why ?? We say that the new FD is implied

45. Armstrong’s Axioms A1, A2, …, An B1, B2, …, Bm Splitting rule and Combing rule Is equivalent to A1, A2, …, An B1 A1, A2, …, An B2 . . . . . A1, A2, …, An Bm

46. Armstrong’s Axioms Trivial Rule A1, A2, …, An Ai where i = 1, 2, ..., n Why ?

47. A1, A2, …, An B1, B2, …, Bm If B1, B2, …, Bm  C1, C2, …, Cp and A1, A2, …, An C1, C2, …, Cp then Armstrong’s Axioms Transitive Closure Rule Why ?

48. Example (continued) 1. namecolor 2. categorydepartment 3. color, categoryprice To: name, categoryprice From:

49. Example (continued) 1. namecolor 2. categorydepartment 3. color, categoryprice Answers:

50. Closure of a Set of FDs Definition. Given a set F of functional dependencies, the closure, F+, denotes all FDs implied by F Theorem. Armstrong axioms are sound and complete for computing F+ What do sound and complete mean ?