C20.0046: Database Management Systems Lecture #5

1. C20.0046: Database Management SystemsLecture #5 Matthew P. Johnson Stern School of Business, NYU Spring, 2005 M.P. Johnson, DBMS, Stern/NYU, Spring 2005

2. Crew StudioName Crew_ID Miramax C1 Miramax C2 Disney C1 Converting weak ESs – differences • Atts of Crew Rel are: • attributes of Crew • key attributes of supporting ESs StudioName address Crew_ID Crew Unit-of Studio • Supporting relships may be omitted (why?) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

3. Insurance IName Address Aetna 1250 6th Av.NY BlueCross 1260 7th Av.NY Weak entity sets - relationships StudioName address Crew_ID Unit-of Crew Studio Subscribes IName Address Insurance M.P. Johnson, DBMS, Stern/NYU, Spring 2005

4. Subscribes StudioName Crew_ID Insurer Universal C21 Aetna Universal C22 BlueCross Disney C21 Aetna Weak entity sets - relationships • Non-supporting relationships for weak ESs are converted • keys include entire weak ES key M.P. Johnson, DBMS, Stern/NYU, Spring 2005

5. Conversion example • Video store rental example, plus some atts • Q: Conversion to relations? date VideoStore Rental MID Movie address year Customer MName Cname M.P. Johnson, DBMS, Stern/NYU, Spring 2005

6. Conversion example, continued MID • Resulting binary-relationship version • Q: Conversion to relations? MName year MovieOf date Movie Rental StoreOf address BuyerOf Store Cname Customer M.P. Johnson, DBMS, Stern/NYU, Spring 2005

7. Converting inheritance hierarchies • No best way • Several non-ideal methods: • E/R-style: each ES  relation • OO-style: each possible “object”  relation • nulls-style: each rooted hierarchy  relation • non-applicable fields filled in with nulls • Pros & cons • for each method, exist situations favoring it M.P. Johnson, DBMS, Stern/NYU, Spring 2005

8. Converting inheritance hierarchies title year length stars Movies isa isa Weapon Voices Lion King Murder-Mysteries Cartoons Component M.P. Johnson, DBMS, Stern/NYU, Spring 2005

9. Root entity set: Movies(title, year, length) Title Year length Star Wars 1980 120 Roger Rabbit 1990 115 Scream 1988 110 Lion King 1993 130 Subclass: MurderMysteries(title, year, murderWeapon) Title Year murderWeapon Subclass: Cartoons(title, year) Scream 1988 Knife Title Year R. Rabbit 1990 Knife Roger Rabbit 1990 Lion King 1993 Inheritance: E/R-style conversion • Each ES  relation M.P. Johnson, DBMS, Stern/NYU, Spring 2005

10. Subclasses: object-oriented approach • Every possible “subtree” (what’s this?): • Movies • Movies + Cartoons • Movies + Murder-Mysteries • Movies + Cartoons + Murder-Mysteries 1. 3. 2. 4. M.P. Johnson, DBMS, Stern/NYU, Spring 2005

11. Subclasses: nulls approach • One relation for entire hierarchy • Any non-applicable fields are NULL • Q: How do we know if a movie is a MM? • Q: How do we know if a movie is a cartoon? M.P. Johnson, DBMS, Stern/NYU, Spring 2005

12. Subclasses methods: considerations • Query time ~ number of tables accessed in query • nulls: best, since each entity  single row • multi-node questions: “Find 1999 films with length > 150 mins” • E/R: just Movies, so fast • OO: Movies AND cartoons, so slow • single-node questions: “Find weapons in >150-min. cartoons” • E/R: Movies, Cartoons AND MMs, so slow • OO: just MoviesCMM, so fast • Number of relations per entity set • nulls: just one, so very few • E/R: one per ES, so more • OO: exponential in #ESs, so very many (how many?) • Number/size of rows per entity • nulls: one “long” row per entity • But all non non-applicables become null • OO: one (all-relevant) row per entity, so smallest • E/R: several (tho all relevant) rows per entity • but keys are duplicated M.P. Johnson, DBMS, Stern/NYU, Spring 2005

13. E/R-style & quasi-redundancy • Name and year of Roger Rabbit were listed in three different rows (in different tables) • Suppose title changes (“Roger”  “Roget”) •  must change all three places • Q: Is this redundancy? • A: No! • name and year are independent • multiple movies may have same name • Real redundancy reqs. dependency • two rows agree on SSN  must agree on rest • conflicting hair colors in these rows is an error • two rows agree on movie title  may still disagree • conflicting years may be correct – or may not be • can forbid first case but not second • Better: introduce “movie-id” key att M.P. Johnson, DBMS, Stern/NYU, Spring 2005

14. Combined isa/weak example • Exercise 3.3.1 • Convert from E/R to R, by E/R, OO and nulls chair dname cnumber room givenBy Depts courses cname isa platform Lab-courses #machines M.P. Johnson, DBMS, Stern/NYU, Spring 2005

15. Agenda • Last time: relational model • This time: • Functional dependencies • Keys and superkeys in terms of FDs • Finding keys for relations • Rules for combining FDs • Next time: anomalies & normalization M.P. Johnson, DBMS, Stern/NYU, Spring 2005

16. Next topic: Functional dependencies • FDs are constraints • part of the schema • can’t tell from particular relation instances • FD may hold for some instances “accidentally” • Finding all FDs is part of DB design • Used in normalization M.P. Johnson, DBMS, Stern/NYU, Spring 2005

17. then they must also agree on the attributes B1, B2, …, Bm Functional dependencies • Definition: • Notation: • Read: Ai functionally determines Bj If two tuples agree on the attributes A1, A2, …, An A1, A2, …, An B1, B2, …, Bm M.P. Johnson, DBMS, Stern/NYU, Spring 2005

18. Typical Examples of FDs • Product • name  price, manufacturer • Person • ssn  name, age • father’s/husband’s-name  last-name • zipcode  state • phone  state (notwithstanding inter-state area codes) • Company • name  stockprice, president • symbol  name • name  symbol M.P. Johnson, DBMS, Stern/NYU, Spring 2005

19. A1 ... Am B1 ... Bm t1 t2 Functional dependencies • To check A  B, erase all other columns; for each rows t1, t2 • i.e., check if remaining relation is many-one • no “divergences” • i.e., if AB is a well-defined function • thus, functional dependency if t1, t2 agree here then t1, t2 agree here M.P. Johnson, DBMS, Stern/NYU, Spring 2005

20. FDs Example Product(name, category, color, department, price) Consider these FDs: namecolor categorydepartment color, categoryprice What do they say ? M.P. Johnson, DBMS, Stern/NYU, Spring 2005

21. FDs Example • FDs 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 ? M.P. Johnson, DBMS, Stern/NYU, Spring 2005

22. FDs Example namecolor categorydepartment color, categoryprice What about this one ? M.P. Johnson, DBMS, Stern/NYU, Spring 2005

23. Recognizing FDs • Q: Is PositionPhone an FD here? • A: It is for this instance, but no, presumably not in general • Others FDs? • EmpID  Name, Phone, Position • but Phone  Position EmpID Name Phone Position E0045 Smith 1234 Clerk E1847 John 9876 Salesrep E1111 Smith 9876 Salesrep E9999 Mary 1234 Lawyer M.P. Johnson, DBMS, Stern/NYU, Spring 2005

24. Keys of relations • {A1A2A3…An} is a key for relation R if • A1A2A3…Anfunctionally determine all other attributes • Usual notation: A1A2A3…An B1B2…Bk • rels = sets  distinct rows can’t agree on all Ai • A1A2A3…An is minimal • No proper subset of A1A2A3…An functionally determines all other attributes of R • Primary key: chosen if there are several possible keys M.P. Johnson, DBMS, Stern/NYU, Spring 2005

25. Keys example • Relation: Student(Name, Address, DoB, Email, Credits) • Which (/why) of the following are keys? • SSN • Name, Address (on reasonable assumptions) • Name, SSN • Email, SSN • Email • NB: minimal != smallest M.P. Johnson, DBMS, Stern/NYU, Spring 2005

26. Superkeys • A set of attributes that contains a key • Satisfies first condition: • functionally determines every other attribute in the relation • Might not satisfy the second condition: minimality • may be possible to peel away some attributes from the superkey • keys are superkeys • key are special case of superkey • superkey set is superset of key set • name;ssn is a superkey but not a key M.P. Johnson, DBMS, Stern/NYU, Spring 2005

27. Discovering keys for relations • Relation  entity set • Key of relation = (minimized) key of entity set • Relation  binary relationship • Many-many: union of keys of both entity sets • Many(M)-one(O): only key of M (Why?) • One-one: key of either entity set (but not both!) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

28. Example – entity sets • Key of entity set = (minimized) key of relation • Student(Name, Address, DoB, SSN, Email, Credits) SSN Name Student Email Address Credits DoB M.P. Johnson, DBMS, Stern/NYU, Spring 2005

29. Example – many-many • Many-many key: union of both ES keys Student Enrolls Course SSN Credits Name CourseID Enrolls(SSN,CourseID) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

30. Example – many-one • Key of the many ES but not of the one ES • keys from both would be non-minimal Course MeetsIn Room CourseID Name Capacity RoomNo MeetsIn(CourseID,RoomNo) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

31. Example – one-one • Keys of both ESs included in relation • Key is key of either ES (but not both!) Husbands Married Wives SSN Name Name SSN Married(HSSN, WSSN) or Married(HSSN, WSSN) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

32. Course Enrolls Student CourseID Name SSN Section Name RoomNo Capacity Discovering keys: multiway • Multiway relationships: • Multiple ways – may not be obvious • R:F,G,HE is many-one  E’s key is included • but not part of key • Recall that relship atts are implicitly many-one Enrolls(CourseID,SSN,RoomNo) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

33. Example • Exercise 3.4.2 • Relation relating particles in a box to locations and velocities InPosition(id,x,y,z,vx,vy,vz) • Q: What FDs hold? • Q: What are the keys? M.P. Johnson, DBMS, Stern/NYU, Spring 2005

34. Next time • Do reading on web • Finish project part 1 • Office hours now M.P. Johnson, DBMS, Stern/NYU, Spring 2005