CSE544 Data Modeling, Conceptual Design, XML

1. CSE544Data Modeling,Conceptual Design, XML Wednesday, April 5, 2006

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

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. Data Anomalies Recall set attributes (persons with several phones): SSN Name, City but not SSN  PhoneNumber • Anomalies: • Redundancy = repeat data • Update anomalies = Fred moves to “Bellevue” • Deletion anomalies = Joe deletes his phone number: what is his city ?

38. Relation Decomposition Break the relation into two: • Anomalies have gone: • No more repeated data • Easy to move Fred to “Bellevue” (how ?) • Easy to delete all Joe’s phone number (how ?)

39. Decompositions in General R(A1, ..., An, B1, ..., Bm, C1, ..., Cp) R1(A1, ..., An, B1, ..., Bm) R2(A1, ..., An, C1, ..., Cp) R1 = projection of R on A1, ..., An, B1, ..., Bm R2 = projection of R on A1, ..., An, C1, ..., Cp

40. Problems With Decomposition • Can we get the data back correctly ? • Lossless decomposition • Discuss next • Can we recover the FD’s on the ‘big’ table from the FD’s on the small tables ? • Dependency-preserving decomposition • Figure out yourself, or read 19.5.2

41. Lossless Decomposition • Sometimes it is correct:

42. Lossy Decomposition • Sometimes it is not: What’swrong ??

43. Decompositions in General R(A1, ..., An, B1, ..., Bm, C1, ..., Cp) R1(A1, ..., An, B1, ..., Bm) R2(A1, ..., An, C1, ..., Cp) Theorem If A1, ..., An B1, ..., Bm Then the decomposition is lossless Note: don’t need necessarily A1, ..., An C1, ..., Cp Example: name price, hence the first decomposition is lossless

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

45. 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

46. 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’

47. 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

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

49. 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 ?

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