Lecture 08: E/R Diagrams and Functional Dependencies

1. Lecture 08: E/R Diagrams and Functional Dependencies

2. Modeling Subclasses • The world is inherently hierarchical. Some entities are special cases of others • We need a notion of subclass. • This is supported naturally in object-oriented formalisms. Products Software products Educational products

3. Subclasses in E/R Diagrams category name price Product isa isa Software Product Educational Product platforms Age Group

4. Understanding Subclasses • Think in terms of records: • Product • SoftwareProduct • EducationalProduct

5. Subclasses to Relations category name Product price Product Sw.Product isa isa Ed.Product Software Product Educational Product platforms Age Group

6. FurniturePiece Company Person Modeling Union Types with Subclasses Say: each piece of furniture is owned either by a person, or by a company

7. Modeling Union Types with Subclasses Say: each piece of furniture is owned either by a person, or by a company Solution 1. Acceptable, imperfect Person FurniturePiece Company ownedByPerson ownedByCompany (What’s wrong ?)

8. Modeling Union Types with Subclasses Will this always work? (hint: key?) Solution 2: better! Company Owner isa isa ownedBy Person FurniturePiece

9. Constraints in E/R Diagrams Finding constraints is part of the modeling process. Commonly used constraints: Keys: social security number uniquely identifies a person. Single-value constraints: a person can have only one father. Referential integrity constraints: if you work for a company, it must exist in the database. Other constraints: peoples’ ages are between 0 and 150.

10. Keys in E/R Diagrams category name Underline: price Product No formal way of specifying multiple keys in E/R diagrams Person address name ssn

11. Single Value Constraints makes vs. makes

12. Referential Integrity Constraints makes Product Company makes Product Company

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

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

15. Handling Weak Entity Sets affiliation Team University sport number name Convert to a relational schema (in class)

16. The Relational Data Model

17. The Relational Data Model Relational Schema Physical storage Data Modeling E/R diagrams Tables: column names: attributes rows: tuples Complex file organization and index structures.

18. Recalling The Terminology Table name or relation name Attributenames or field 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 or rows or records

19. First Normal Form (1NF) • A database schema is in First Normal Form if all tables are flat Student Student Takes Course

20. Functional Dependencies • A form of constraint • hence, a part of the schema • Finding them is part of the database design • Also used in normalizing the relations

21. Functional Dependencies Definition: If two tuples agree on the attributes A1, A2, … An then they must also agree on the attributes B1, B2, … Bm A1, A2, … An→B1, B2, … Bm Formally:

22. Examples • EmpID → Name, Phone, Position • Position → Phone • but Phone → Position

23. In General • To check if A → B holds in R, erase all other columns • check if the remaining relation is many-one (called functional in mathematics) Note: A functional dependency is a constraint defined regardless of a particular DB instance

24. Example

25. Typical Examples of FDs Product: manufacturer, mpn→price, name Person: ssn →name, age Company: name →stockprice, president

26. Formal definition of a key • A key is a set of attributes A1, ..., An s.t. for any other attribute B, A1, ..., An→B • A minimal key is a set of attributes which is a key and for which no subset is a key • Note: book calls them superkey and key

27. Examples of Keys • Product(name, price, category, color) name, category →price category →color Keys are: {name, category} and all supersets • Enrollment(student, address, course, room, time) student → address room, time →course student, course →room, time Keys are: [in class]

28. 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 Person(address, name, ssn) address name ssn

29. 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 Product Person price name ssn date buys(name, ssn, date)

30. Finding the Keys Except: 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. Product sname name Purchase Store Payment Method Person ssn card-no Purchase(name , sname, ssn, card-no)

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