1 / 29

CSE 480: Database Systems

CSE 480: Database Systems. Lecture 4: Enhanced Entity-Relationship Modeling. Reference: Read Chapter 8.1 – 8.5 of the textbook. Announcements. Class Project Each group must email the instructor by next Tuesday your group members

hertz
Download Presentation

CSE 480: Database Systems

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. CSE 480: Database Systems • Lecture 4: Enhanced Entity-Relationship Modeling • Reference: • Read Chapter 8.1 – 8.5 of the textbook

  2. Announcements • Class Project • Each group must email the instructor by next Tuesday your group members • If your group has only 2 members, state whether you’re willing to accept a third member • If you don’t have a group yet, email me too. I’ll try to set up one for you.

  3. Why EER? • ER diagram has limitations in terms of accurately reflecting the data properties and constraints • EER, which stands for Enhanced ER includes all the modeling concepts of basic ER with the following additional concepts • subclasses/superclasses • type inheritance • shared subclasses and multiple inheritance • categories (UNION types)

  4. Why Subclasses and Superclasses? • An entity type may have additional subgroupings: • EMPLOYEE may be further grouped into: • SECRETARY, ENGINEER, TECHNICIAN • Based on job type • SALARIED_EMPLOYEE, HOURLY_EMPLOYEE • Based on method of pay • EER diagram extends ER diagram to represent these additional subgroupings • Each subgrouping is called a subclass of EMPLOYEE • EMPLOYEE is the superclass of these subclasses

  5. Is-A Relationships in EER Subclass-superclass relationships are also called IS-A relationships SECRETARY IS-A EMPLOYEE, TECHNICIAN IS-A EMPLOYEE, …. Subclass Superclass Subclass1 Subclass2 Superclass Subclass3

  6. Specialization • A superclass can have several specializations • EMPLOYEE has 3 specializations • Some specializations have only 1 subclass (e.g., Manager)

  7. Is-A vs 1-to-1 Relationship MANAGES EMPLOYEE DEPARTMENT 1 1 EMPLOYEE TECHNICIAN • In a 1-1 relationship, two distinct entities are related • In a subclass-superclass relationship, the subclass member is the same entity as the superclass member but in a distinct specific role

  8. Subclasses and Superclasses • A subclass entity must be a member of the superclass • But a superclass entity does not have to be a member of any of its subclasses

  9. Why do we need Subclass/Superclass? • Certain attributes may apply to some but not all entities of the superclass • Ex: Typing speed is an attribute of SECRETARY but not a TECHNICIAN • Certain relationship types are applicable to some entities but not others • HOURLY_EMPLOYEE has a relationship with TRADE_UNION; SALARIED_EMPLOYEE does not have such a relationship

  10. Attributes of a Subclass • Local attributes: TypingSpeed of SECRETARY, Tgrade of TECHNICIAN • Type inheritance: a subclass also inherits all attributes of its superclass • SECRETARY inherits the attributes Name, SSN, Birth_date and Address from the EMPLOYEE entity type (its superclass)

  11. Relationships of a Subclass • A subclass can participate in specific relationship types • Ex: MANAGER subclass participates in the MANAGES relationship with PROJECT entity type

  12. Relationships of a Subclass • A subclass also inherits all the relationships in which the superclass participates • If EMPLOYEE participates in WORKS_FOR relationship with DEPARTMENT then • SECRETARY also participates in WORKS_FOR relationship with DEPARTMENT • ENGINEER also participates in WORKS_FOR relationship with DEPARTMENT • TECHNICIAN also participates in WORKS_FOR relationship with DEPARTMENT • MANAGER also participates in WORKS_FOR relationship with DEPARTMENT • SALARIED_EMPLOYEE also participates in WORKS_FOR relationship with DEPARTMENT • HOURLY_EMPLOYEE also participates in WORKS_FOR relationship with DEPARTMENT

  13. Constraints on Is-A Relationships • Constraint is defined on the subclass membership of the superclass entities • Disjointness constraint • How many subclasses a superclass entity can belong? (max) • Completeness constraint • Must a superclass entity belong to any of the subclasses? (min) Subclass1 Subclass2 Superclass Subclass3

  14. d o Disjointness Constraint • Specifies whether subclasses are disjoint: • an entity can be a member of at most one of the subclasses of the specialization • Specified by “d” in EER diagram • Ex: STUDENT is either FRESHMAN, SOPHOMORE, JUNIOR, SENIOR, GRADUATE • If not disjoint, specialization is overlapping: • an entity may be a member of more than onesubclasses • Specified by “o” in EER diagram • Ex: PERSON can be STUDENT, EMPLOYEE, or both superclass superclass

  15. Completeness Constraint • Total specialization • Every entity in superclass must be a member of at least one subclass in the specialization • Ex: every EMPLOYEE must be either an HOURLY_EMPLOYEE or SALARIED_EMPLOYEE • Shown in EER diagrams by a double line • Partial specialization • Allows a superclass entity not to belong to any subclasses • Ex: some EMPLOYEE entities do not belong to any of the subclasses SECRETARY, TECHNICIAN, or ENGINEER • Shown in EER diagrams by a single line superclass superclass

  16. Partial Disjoint Example: Disjoint Partial Specialization

  17. Total Overlapping Example: Overlapping Total Specialization

  18. Exercise • Can a technician be an engineer? • Can a manager belong to a trade union?

  19. Exercise: MOVIE database Each movie is identified by title and year of release. Each movie has a length in minutes and is classified under one or more genres (horror, action, drama, etc) Each movie has one or more directors and one or more actors appear in it. Each movie also has a plot outline and zero or more quotable quotes, each of which is spoken by a particular actor appearing in the movie Actors are identified by name and date of birth and appear in one or more movies. Each actor has a role in the movie Directors are also identified by name and date of birth and direct one or more movies. It is possible for a director to act in a movie (including one he or she may also direct)

  20. Exercise: Movie Database role quotes M ACTOR ACTS Name DoB genres N ID Title Person ID MOVIE o Year length Plot_Outline N M DIRECTOR DIRECTS

  21. Hierarchies & Lattices Hierarchy (Single Inheritance) every subclass has only one immediate superclass Lattice (Multiple inheritance) a subclass can be subclass of more than one immediate superclass A subclass with multiple immediate superclasses is called a shared subclass In a lattice or hierarchy, a subclass inherits attributes not only of its immediate superclass, but also of all its predecessor superclasses

  22. SharedSubclass Lattice (Multiple Inheritance)

  23. ENGINEERING_MANAGER is a shared subclass ENGINEERING_MANAGER Is-a ENGINEER ENGINEERING_MANAGER Is-a MANAGER ENGINEERING_MANAGER Is-a SALARIED_EMPLOYEE It will inherit all the attributes of its superclasses, including EMPLOYEE, ENGINEER, MANAGER, and SALARIED_EMPLOYEE Another Shared Subclass Example

  24. Categories (UNION types) • A shared subclass is a subclass participating in multiple superclass/subclass (is-a) relationships • Each distinct relationship has a single superclass • In some cases, we need to model a single superclass/subclass relationship with more than one superclass • Each superclass can represent a different entity type • Such a subclass is called a category or UNION TYPE • Difference between shared subclass and union type • An entity of a shared subclass exists in all of its superclasses • An entity of a category exists in only one of its superclasses • It inherits only attributes of the superclass in which it belongs

  25. Categories (UNION types) OWNER is the subclass of the union of 3 superclass entity types, PERSON,BANK, COMPANY (which have different key attributes)

  26. Example of UNION types OWNER is the subclass of the union of 3 entity types, PERSON,BANK, COMPANY (which have different key attributes) REGISTERED_VEHICLE is the subclass of the union of CAR and TRUCK (which have same key attribute)

  27. Constraints on UNION types • Total vs partial category (see example on next slide) • Total category holds the union of all entities in its superclasses • Represented by a double line • Partial category holds a subset of the union • Represented by a single line

  28. Total vs Partial Category Registered Unregistered u User User is the union of all registered and unregistered user entity types Owner is the union of all banks, but not all persons and not all companies

  29. Summary • Introduced the EER model concepts • Class/subclass relationships • Type inheritance • Shared subclass and categories (union types) • These augment the basic ER model concepts introduced in lectures 2-3

More Related