1 / 31

Lecture 08: E/R Diagrams and Functional Dependencies

Lecture 08: E/R Diagrams and Functional Dependencies. 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.

dory
Download Presentation

Lecture 08: E/R Diagrams and Functional Dependencies

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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)

More Related