IS6145 Database Analysis and Design Lecture 5: Enhanced Entity-Relationship (EER) Modeling

1. IS6145 Database Analysis and DesignLecture 5: Enhanced Entity-Relationship (EER) Modeling Rob Gleasure R.Gleasure@ucc.ie www.robgleasure.com

2. Course structure • Or more specifically • Week 1: Introduction • Week 2: Foundational Concepts of Data Modelling • Week 3: ER Modelling and Beyond the Presentation Layer • Week 4: Fine-Granular Design-Specific ER Modelling • Week 5: Enhanced Entity-Relationship (EER) Modelling • Week 6: Practice with ERDs • Week 7: In-Class Data Modelling Exam • Week 8: The Data Value Map • Week 9: Data Normalisation • Week 10: NoSQL and Hadoop • Week 11: Blockchain • Week 12: Revision

3. IS6145 • Today’s session • Enhanced Entity-Relationship Modelling • An exercise

4. Example from Lecture 4: BigArt Galleries • Galleries keep information about artists, their name (which is unique), birthplace, age, and one or more styles of art. For each piece of artwork, the artist, the year it was made, its unique title, its type of art (e.g., painting, lithograph, sculpture, photograph), and its price must be stored. Pieces of artwork are also classified into groups of various kinds, for example, portraits, still lifes, etc., and assigned a curator. A given piece may belong to more than one group. • Each group is identified by a name (like those just given) that describes the group. Finally, galleries keep information about customers. For each customer, galleries keep that person’s unique name, address, total amount of dollars spent in the gallery (very important!), and the artists and groups of art that the customer tends to like.

5. Fine-Granular Design-Specific ERD

6. Questions • Which part of this is least clear/most challenging? • Which parts of the presented ERD did you do differently?

7. The last few weeks • Presentation Layer ER Diagram • Maps out entities, attributes, and relationships • Design-specific ER Diagram (Coarse-granularity) • Uses (min, max) notation • Maps deletion rules • Design-specific ER Diagram (Fine-granularity) • Maps attribute characteristics into ER diagram • Decomposes multi-valued attributes • Decomposes m:n relationships • Formalises semantic integrity constraints

8. The missing piece… • Enhanced Entity-Relationship (EER) extends the ERD by incorporating additional constructs, specifically the superclass/subclass (SC/sc) relationship • EERDs are part of the fundamental entity, attribute, relationship structure, so aren’t level-specific, i.e. we can have • Presentation-layer EERDs • Coarse-Granular EERDs • Fine-Granular EERDs

9. An example of a superclass/subclass relationships (SC/sc) Image from Data Modeling and Database Design, By Narayan Umanath, Richard Scamell

10. Why bother with superclasses/subclasses? • This isn’t the only option for this example, e.g. we could have… • Modelled furniture as one entity type with an attribute called furniture_type; then chair, table, and sofa would be values of that attribute • Modelled three independent entity types for chair, table, and sofa, and create three separate relationship types with the entity type store • The first options creates redundancy, the second struggles with the relationship constraints for large numbers of sub-classes

11. A vignette Image from Data Modeling and Database Design, By Narayan Umanath, Richard Scamell

12. Option 1: modelling student athletes with composite attributes Image from Data Modeling and Database Design, By Narayan Umanath, Richard Scamell

13. Option 2: modelling student athletes using inter-entity class relationships Image from Data Modeling and Database Design, By Narayan Umanath, Richard Scamell

14. Types of SC/sc relationships • There are two basic kinds of Sc/sc relationships • Specialization/Generalization: One superclass (SC) is related to one or more subclasses (sc) • Categorization: One subclass (sc) is related to one or more superclasses (SC)

15. Specialisation and Generalisation • Specialisation is the process of generating subgroups (‘sc’s) of a generic entity class (SC) by specifying the distinguishing properties (attributes) of the subgroups (= top-down approach) • Generalisation, on the other hand, crystallizes the common properties (attributes) shared by a set of entity types (‘sc’s) into a generic entity type (SC) (= bottom-up approach) • Notation: circle + fork (indicating subset) • Read: “is-a”

16. Two important constraints of specialisation and generalisation Completeness Constraint can be one of two values • Totalspecialisation means that every entity of the superclass must participate in this specialisation/ generalisation relationship (indicated by a solid line from the superclass to the specialization/generalization symbol (i.e., the circle)) • Partialspecialisation means that there may be entities present in the superclass that do not participate in this specialisation/generalisation (indicated by a dotted line)

17. Two important constraints of specialisation and generalisation DisjointnessConstraint can also be one of two values • Disjointed means an entity of the superclass cannot be a member of more than one subclass (indicated by ‘D’), • Overlappedmeans an entity of the superclass canbe a member of more than one subclass (indicated by ‘O’)

18. Notation for completeness and disjointness constraints Image from Data Modeling and Database Design, By Narayan Umanath, Richard Scamell We haven’t come to this yet

19. Notation for completeness and disjointness constraints Image from Data Modeling and Database Design, By Narayan Umanath, Richard Scamell

20. Notation for completeness and disjointness constraints Image from Data Modeling and Database Design, By Narayan Umanath, Richard Scamell

21. Categorisation and Aggregation Specialisation/ Generalisation d Generalisation Specialisation U U d Categorisation Aggregation U U

22. Categorisation and Aggregation • The first form of this is a category construct • Notation: ‘U’ Image from Data Modeling and Database Design, By Narayan Umanath, Richard Scamell

23. Categorisation and Aggregation • The second form of this is an aggregation construct • Notation: ‘A’ (note that an aggregate can never be partial) Image from Data Modeling and Database Design, By Narayan Umanath, Richard Scamell

24. An aggregate hierarchy Image from Data Modeling and Database Design, By Narayan Umanath, Richard Scamell

25. Example of Presentation Layer EERD Image from Data Modeling and Database Design, By Narayan Umanath, Richard Scamell

26. A Fine-granular Design-Specific EER Diagram for the vignette Image from Data Modeling and Database Design, By Narayan Umanath, Richard Scamell

27. Exercise • For the Design-Specific EER Diagram for the vignette on the previous slide • What are the superclass entity types and subclass entity types? • What attributes are inherited by the entity type Pitcher

28. Exercise • Draw a Presentation Layer ER Diagram for the following narrative • A commercial air flight stores a flight list containing both passengers and staff. This flight list detail the final destination and passport number for everyone on board. Passengers will also have data for their name, nationality and seat number. Staff will also have data for their name, job title and the number of years they have worked for the company. Some staff are contract staff, in which case their pay rate and employment agency should be listed. Other employees are permanent staff, in which case their salary, pension details, and union should be listed. Other employees are non-paid government officials, in which case their department and clearance level should be listed. For paid staff (meaning contract and permanent staff), an employee number must be stored and home location may also be listed.

29. Exercise (possible solution)

30. Exercise (possible solution)