Relational Model and Translating ER into Relational

1. Relational Model and Translating ER into Relational

2. Lecture Outline • Relational model • Translating from ER to relational model

3. Motivations & comparison of ER with relational model ...

4. Database Modeling & Implementation Ideas Database Model (E/R, ODL) Relational Schema Physical storage Complex file organization and index structures. Diagrams (E/R) Tables: column names: attributes rows: tuples

5. ER Model vs. Relational Model • Both are used to model data • ER model has many concepts • entities, relations, attributes, etc. • well-suited for capturing the app. requirements • not well-suited for computer implementation • (does not even have operations on its structures) • Relational model • has just a single concept: relation • world is represented with a collection of tables • well-suited for efficient manipulations on computers

6. The basics of the relational model ...

7. An Example of a Relation Table name Attribute names Products: Name Price Category Manufacturer gizmo $19.99 gadgets GizmoWorks Power gizmo $29.99 gadgets GizmoWorks SingleTouch $149.99 photography Canon MultiTouch $203.99 household Hitachi tuples

8. Domains • Each attribute has a type • Must be atomic type (why? see later) • Called domain • Examples: • Integer • String • Real • …

9. Schemas vs. instances (very important, make sure you knowthe difference)

10. Schemas The Schema of a Relation: • Relation name plus attribute names • E.g. Product(Name, Price, Category, Manufacturer) • In practice we add the domain for each attribute The Schema of a Database • A set of relation schemas • E.g. Product(Name, Price, Category, Manufacturer), Vendor(Name, Address, Phone), . . . . . . .

11. Instances • Relational schema = R(A1,…,Ak):Instance = relation with k attributes (of “type” R) • values of corresponding domains • Database schema = R1(…), R2(…), …, Rn(…)Instance = n relations, of types R1, R2, ..., Rn

12. Name Price Category Manufacturer gizmo $19.99 gadgets GizmoWorks Power gizmo $29.99 gadgets GizmoWorks SingleTouch $149.99 photography Canon MultiTouch $203.99 household Hitachi Example Relational schema:Product(Name, Price, Category, Manufacturer) Instance:

13. Annother Example Students: Takes: Courses: Three relational schemas here + three relational instances One database schema + one database instance

14. Updates The database maintains a current database state (that is, a database instance). Updates to the data (that is, the database instance) 1) add a tuple 2) delete a tuple 3) modify an attribute in a tuple Updates to the data happen very frequently. Updates to the schema: relatively rare. Rather painful. Why?

15. Schemas and Instances • Analogy with programming languages: • Schema = type • Instance = value • Important distinction: • Database Schema = stable over long periods of time • Database Instance = changes constantly, as data is inserted/updated/deleted

16. How should we talk about relations (that is, represent them)?Will skip this in the classYou can read for fun

17. Two Mathematical Definitions of Relations Relation as Cartesian product • Tuple = element of string x int x string x string • E.g. t = (gizmo, 19, gadgets, GizmoWorks) • Relation = subset of string x int x string x string • Order in the tuple is important ! • (gizmo, 19, gadgets, GizmoWorks) • (gizmo, 19 , GizmoWorks, gadgets) • No attributes

18. Relation as a set of functions • Fix the set of attributes • A={name , price, category, manufacturer} • A tuple = function t:A Domains • Relation = set of tuples • E.g. • Order in a tuple is not important • Attribute names are important {name gizmo, price 19, category gadgets, manufacturer gizmoWorks}

19. Two Definitions of Relations • We will switch back and forth between these two: • Positional tuples, without attribute names • Relational schemas with attribute names

20. Now the fun part: translating from ER to relational model

21. Translating ER Diagram to Rel. Design • Basic cases • entity set E = relation with attributes of E • relationship R = relation with attributes being keys of related entity sets + attributes of R • Special cases • combining two relations • translating weak entity sets • translating is-a relationships and subclasses

22. An Example name category name price makes Company Product Stock price buys employs Person name ssn address

23. Basic cases ...

24. Entity Sets to Relations name category price Product Product: Name Category Price gizmo gadgets $19.99

25. Relationships to Relations price name category Start Year name makes Company Product Stock price Relation Makes (watch out for attribute name conflicts) Product-name Product-Category Company-name Starting-year gizmo gadgets gizmoWorks 1963

26. Likes husband 2 1 Favorite Buddies Likes(drinker, beer) Favorite(drinker, beer) wife Married Buddies(name1, name2) Married(husband, wife) Relationship to Relation: Another Example name name addr manf Drinkers Beers

27. Special cases:1) many-one relations2) weak entity sets3) is-a cases

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

29. 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).

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

31. 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 ?)

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

33. 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)

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

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

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

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

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

39. Example Beers name manf isa Ales color

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

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

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

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

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