1 / 12

Extending ER Diagrams (4.4)

Extending ER Diagrams (4.4) CPSC 356 Database Ellen Walker Hiram College (Includes figures from Database Systems: An Application-Oriented Approach 2ed , by Kifer, Bernstein & Lewis, © Addison Wesley 2005) Basic ER Diagram Cannot Capture Specialization/ Generalization Examples

adamdaniel
Download Presentation

Extending ER Diagrams (4.4)

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. Extending ER Diagrams (4.4) CPSC 356 Database Ellen Walker Hiram College (Includes figures from Database Systems: An Application-Oriented Approach 2ed, by Kifer, Bernstein & Lewis, © Addison Wesley 2005)

  2. Basic ER Diagram Cannot Capture Specialization/ Generalization • Examples • Doctors and Nurses are Medical Staff • Staff includes Medical staff, Secretarial staff, Housekeeping staff, etc. • Relationships might include Doctors, Medical Staff, and/or Staff • ER diagram cannot represent the fact that Staff > Medical staff > doctors • EER diagram can!

  3. Entity Type Hierarchies • An entity type E1 is a subtype of the entity type E2 when • Every entity instance of E1 is also an instance of E2 • Every attribute in E2 is also in E1 • The opposite of a subtype is a supertype • Subtypes are related to supertypes through the IS-A relationship

  4. IS-A IS-A Subtype / Supertype Example • Doctor is a subtype of medical-staff • Staff is a supertype of medical-staff Staff • Attributes • Staff - name, addr, salary • Med-staff - pager # • Doctor - license # Medical-Staff Admin-Staff Doctor Nurse

  5. Attribute Inheritance • Each entity type inherits all attributes of its supertype(s) • Staff has name, address, salary • Medical staff has {n,a,s} + beeper-number • Doctor has {n, a, s, b#} + license # • In EER diagram, inherited attributes only appear once (in the highest supertype)

  6. Generalizing Type Hierarchies • Multiple supertypes for the same subtype • A student-employee is both a student and an employee • Overlapping subtypes • Subclasses of person are student and employee • There is at least one individual who is both a student and an employee • Non-overlapping subtypes are called “disjoint”

  7. Figure 4.6 Example of an E-R diagram with an ISA hierarchy.

  8. Extended ER (EER) Diagram • IS-A relationships • Notation for disjoint (vs. overlapping) subtypes • Notation for “covering” • Can someone be staff without being Medical or administrative?

  9. Additional Semantic Relationships • Aggregation and Composition • Collecting together related entities that represent ‘parts’ of a ‘whole’ • Aggregation (non-exclusive subpart): parts can change parents • Composition (exclusive subpart): single parent, very strong association - parts make no sense without parent • Examples: • Branch (whole) has Staff (part) Aggregation • Newspaper (whole) contains Story (part) Composition • EER representation is diamond with arrow from “part”

  10. Exclusive Subpart = Weak Entity • A Weak Entity depends on some related entity for its existence. It has no candidate keys without including the primary key of the entity it depends on. • Example • Employee ( empNo, fname, lname salary,…) HAS Dependent (fname) • Dependent is not represented without employee • Parent’s record used to distinguish (e.g.) Jennifers

  11. Figure 4.11 Exclusive part-of relationship in E-R: weak entities.

  12. Figure 4.12 Summary of the E-R notation.

More Related