Translating ER into Relational (cont.) Schema Design & Refinement

1. Translating ER into Relational (cont.)Schema Design & Refinement Lecture #6

2. Administrative Stuff • Oracle account • Password

3. Special cases:1) many one relations2) weak entity sets3) isa cases

4. Combining Two Relations price name category Start Year name makes Company Product Stock price No need for Makes. Just modify Product: name category price StartYear companyName gizmo gadgets 19.99 1963 gizmoWorks

5. Combining Relations • It is OK to combine the relation for an entity-set E with the relation R for a many-one relationship from E to another entity set. • Example: Drinkers(name, addr) and Favorite(drinker, beer) combine to make Drinker1(name, addr, favoriteBeer).

6. Redundancy Risk with Many Many Relationships • Combining Drinkers with Likes would be a mistake. It leads to redundancy, as: name addr beer Sally 123 Maple Bud Sally 123 Maple Miller

7. Handling Weak Entity Sets affiliation Team University sport number name Relation Team: Sport Number Affiliated University mud wrestling 15 Montezuma State U. - need all the attributes that contribute to the key of Team - don’t need a separate relation for Affiliation. (why ?)

8. Handling Weak Entity Sets • Relation for a weak entity set must include attributes for its complete key (including those belonging to other entity sets), as well as its own, nonkey attributes. • A supporting (double-diamond) relationship is redundant and yields no relation.

9. Must be the same At becomes part of Logins Another Example name name Logins At Hosts time Hosts(hostName) Logins(loginName, hostName, time) At(loginName, hostName, hostName2)

10. Translating Subclass Entities Product ageGroup topic Platforms required memory isa isa Educational Product Software Product isa isa Educational-method Educ-software Product

11. Option #1: the OO Approach 4 tables: each object can only belong to a single table Product(name, price, category, manufacturer) EducationalProduct( name, price, category, manufacturer, ageGroup, topic) SoftwareProduct( name, price, category, manufacturer, platforms, requiredMemory) EducationalSoftwareProduct( name, price, category, manufacturer, ageGroup, topic, platforms, requiredMemory) All names are distinct

12. Option #2: the E/R Approach Product(name, price, category, manufacturer) EducationalProduct( name, ageGroup, topic) SoftwareProduct( name, platforms, requiredMemory) No need for a relation EducationalSoftwareProduct Unless, it has a specialized attribute: EducationalSoftwareProduct(name, educational-method) Same name may appear in several relations

13. Option #3: The Null Value Approach Have one table: Product ( name, price, manufacturer, age-group, topic, platforms, required-memory, educational-method) Some values in the table will be NULL, meaning that the attribute not make sense for the specific product. Too many meanings for NULL

14. Translating Subclass Entities: The Rules Three approaches: • Object-oriented : each entity belongs to exactly one class; create a relation for each class, with all its attributes. • E/R style : create one relation for each subclass, with only the key attribute(s) and attributes attached to that E.S.; entity represented in all relations to whose subclass/E.S. it belongs. • Use nulls : create one relation; entities have null in attributes that don’t belong to them.

15. Example Beers name manf isa Ales color

16. Object Oriented Beers name manf isa name manf Bud Anheuser-Busch Beers name manf color Summerbrew Pete’s dark Ales Ales color

17. E/R Style Beers name manf isa name manf Bud Anheuser-Busch Summerbrew Pete’s Beers name color Summerbrew dark Ales Ales color

18. Using Nulls Beers name manf isa Ales color name manf color Bud Anheuser-Busch NULL Summerbrew Pete’s dark Beers

19. Comparisons • O-O approach good for queries like “find the color of ales made by Pete’s.” • Just look in Ales relation. • E/R approach good for queries like “find all beers (including ales) made by Pete’s.” • Just look in Beers relation. • Using nulls saves space unless there are lots of attributes that are usually null.

20. Translation Review • Basic cases • entity to table, relation to table • selecting attributes based on keys • Special cases • many-one relation can be merged • merging many-many is dangerous • translating weak entity sets • translating isa hierarchy • 3 choices, with trade-offs

21. Schema Design and Refinement

22. Motivation • We have designed ER diagram, and translated it into a relational db schema R = set of R1, R2, ... • Now what? • We can do the following • specify all relevant constraints over R • implement R in SQL • start using it, making sure the constraints always remain valid • However, R may not be well-designed, thus causing us a lot of problems

23. Example of Bad Design Persons with several phones: Name SSN Phone Number Fred 123-321-99 (201) 555-1234 Fred 123-321-99 (206) 572-4312 Joe 909-438-44 (908) 464-0028 Joe 909-438-44 (212) 555-4000 Problems (also called "Anomalies"): Redundancy = repetition of data update anomalies = update one item and forget others = inconsistencies deletion anomalies = delete many items, delete one item, loose other information insertion anomalies = can't insert one item without inserting others

24. Better Designs Exist Break the relation into two: SSN Name 123-321-99 Fred 909-438-44 Joe SSN Phone Number 123-321-99 (201) 555-1234 123-321-99 (206) 572-4312 909-438-44 (908) 464-0028 909-438-44 (212) 555-4000

25. How do We Obtain a Good Design? • Start with the original db schema R • Transform it until we get a good design R* • Desirable properties for R* • must preserve the information of R • must have minimal amount of redundancy • must be dependency-preserving • if R is associated with a set of constraints C, then it should be easy to also check C over R* • (must also give good query performance)

26. OK, But ... • How do we recognize a good design R*? • How do we transform R into R*? • Answers: use normal forms

27. Normal Forms • DB gurus have developed many normal forms • Most important ones • Boyce-Codd, 3rd, and 4th normal forms • If R* is in one of these forms, then R* is guaranteed to achieve certain good properties • e.g., if R* is in Boyce-Codd NF, it is guaranteed to not have certain types of redundancy • DB gurus have also developed algorithms to transform R into R* that is in some of these normal forms

28. Normal Forms (cont.) • DB gurus have also discussed trade-offs among normal forms • Thus, all we have to do is • learn these forms • transform R into R* in one of these forms • carefully evaluate the trade-offs • Many of these normal forms are defined based on various constraints • functional dependencies and keys

29. Our Attack Plan • Motivation • Functional dependencies & keys • Reasoning with FDs and keys • Desirable properties of schema refinement • Various normal forms and the trade-offs • BCNF, 3rd normal form, 4th normal form, etc. • Putting all together: how to design DB schema

30. Functional Dependencies and Keys

31. Functional Dependencies • A form of constraint (hence, part of the schema) • Finding them is part of the database design • Used heavily in schema refinement Definition: If two tuples agree on the attributes A , A , … A 1 2 n then they must also agree on the attributes B , B , … B 1 2 m Formally: A , A , … A B , B , … B 1 2 m 1 2 n

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

33. In General • To check A B, erase all other columns • check if the remaining relation is many-one (called functional in mathematics)

34. Example More examples: Product: name price, manufacturer Person: ssn name, age Company: name stock price, president

35. Relation Keys • After defining FDs, we can now define keys • Key of a relation R is a set of attributes that • functionally determines all attributes of R • none of its subsets determines all attributes of R • Superkey • a set of attributes that contains a key • We will need to know the keys of the relations in a DB schema, so that we can refine the schema

36. Person name ssn address Finding the Keys of a Relation Given a relation constructed from an E/R diagram, what is its key? Rules: 1. If the relation comes from an entity set, the key of the relation is the set of attributes which is the key of the entity set. Person(address, name, ssn)

37. Finding the Keys Rules: 2. If the relation comes from a many-many relationship, the key of the relation is the set of all attribute keys in the relations corresponding to the entity sets name buys Person Product price name ssn date buys(name, ssn, date)

38. Product Purchase Store Payment Method Person Finding the Keys But: if there is an arrow from the relationship to E, then we don’t need the key of E as part of the relation key. sname name card-no ssn Purchase(name , sname, ssn, card-no)

39. Finding the Keys More rules: • Many-one, one-many, one-one relationships • Multi-way relationships • Weak entity sets (Try to find them yourself, check book)

40. Reasoning with FDs1) closure of FD sets2) closure of attribute sets

41. Closure of FD sets • Given a relation schema R & a set S of FDs • is the FD f logically implied by S? • Example • R = {A,B,C,G,H,I} • S = A B, A C, CG  H, CG  I, B  H • would A  H be logically implied? • yes (you can prove this, using the definition of FD) • Closure of S: S+ = all FDs logically implied by S • How to compute S+? • we can use Armstrong's axioms

42. Armstrong's Axioms • Reflexivity rule • A1A2...An  a subset of A1A2...An • Augmentation rule • A1A2...An  B1B2...Bm, then A1A2...An C1C2..Ck  B1B2...Bm C1C2...Ck • Transitivity rule • A1A2...An  B1B2...Bm and B1B2...Bm  C1C2...Ck, thenA1A2...An  C1C2...Ck

43. Inferring S+ using Armstrong's Axioms • S+ = S • Loop • foreach f in S, apply reflexivity and augment. rules • add the new FDs to S+ • foreach pair of FDs in S, apply the transitivity rule • add the new FD to S+ • Until S+ does not change any further

44. Additional Rules • Union rule • X  Y and X  Z, then X  YZ • (X, Y, Z are sets of attributes) • Decomposition rule • X  YZ, then X  Y and X  Z • Pseudo-transitivity rule • X  Y and YZ  U, then XZ  U • These rules can be inferred from Armstrong's axioms

45. Closure of a Set of Attributes Given a set of attributes {A1, …, An} and a set of dependencies S. Problem: find all attributes B such that: any relation which satisfies S also satisfies: A1, …, An B The closure of {A1, …, An}, denoted {A1, …, An} , is the set of all such attributes B We will discuss the motivations for attribute closures soon +

46. Algorithm to Compute Closure Start with X={A1, …, An}. Repeat until X doesn’t change do: if is in S, and C is not in X then add C to X. C B , B , … B 1 2 n B , B , … B are all in X, and n 1 2

47. Example A B C A D E B D A F B Closure of {A,B}: X = {A, B, C, D, E} Closure of {A, F}: X = {A, F, B, D, C, E}

48. Usage for Attribute Closure • Test if X is a superkey • compute X+, and check if X+ contains all attrs of R • Check if X  Y holds • by checking if Y is contained in X+ • Another way to compute closure S+ of FDs • for each subset of attributes X in relation R, compute X+ • for each subset of attributes Y in X+, output the FDX  Y

49. Desirable Properties of Schema Refinement1) minimize redundancy2) avoid info loss3) preserve dependency4) ensure good query performance

50. name buys Person Product price name ssn Relational Schema Design(or Logical Design) Conceptual Model: Relational Model: - create tables - specify FD’s - find keys Normalization - use FDs to decompose tables to achieve better design