SA0951a Enhanced Entity-Relationship Modelling (EERM) and Mapping

1. SA0951aEnhanced Entity-Relationship Modelling(EERM)andMapping Reading: e.g. Connolly/Begg (4th ed): Chapter 12 – Enhanced ERM; Mapping: “Step 6” Rob et al: Chapter 6.1 (Advanced data modelling), Chapter 11.2 "Step 6".

2. Some limitations of ERMs ERM’s are fine for traditional applications But what about complex databases? CAD/CAM, GIS, OIS etc Enhanced ERM (EERM) supports additional concepts Specialisation/generalisation Uses the UML notation

3. Specialisation/Generalisation Ties into Object Oriented design This extension uses Superclasses Subclasses Attribute inheritance Constraints Participation Disjoint

4. Super/Subclasses Generalisation is the Superclass concept An entity with one or more distinct subgroupings Specialisation is the Subclass concept An entity of a distinct subgrouping Superclass Staff  Full-Time Part-Time Subclasses

5. Continued ….. Staff has a superclass/subclass relationship With 2 subclasses The relationship is ONE-TO-ONE The super/subclass structure Avoids modelling different attributes in the same entity Avoids therefore nulls Models common attributes in the superclass Models unshared attributes in the subclasses Staff  Full-Time Part-Time

6. A word on Attribute Inheritance Which attributes are Inherited by Entity1.3.2? A) A,B,C,I,J B) I,J C) A,B,CD) L Entity1 A B C  Entity1.1 D E F Entity1.2 G H Entity1.3 I J  Entity1.3.1 K Entity1.3.2 L

7. Real Example Staff id name Age generalisation  Full-Time salary holidays Part-Time hourlyRate contractType specialisation

8. Poor Example 1 Staff id name age generalisation  Full-Time salary sex Part-Time hourlyRate sex WHY is this a poor example? A salary should be in the Staff entity B sex should be in the Staff entity C name and age should be in both sub-classes D There shouldn’t be two sub-classes specialisation

9. Poor Example 2 Staff id name age generalisation  Full-Time salary holidays Car registration colour specialisation What is the problem here?

10. Constraints • Participation • A subclass member is always also a member of the superclass • Mandatory participation (of a superclass member in a subclass member): • A superclass member must be a member of a subclass • Optional participation (of a superclass member in a subclass member): • A superclass member need not be a member of any subclass • Disjoint {OR} • When a superclass member is a member of only one subclass • Non-disjoint {AND} • A superclass member may a member of more than one subclass (also called overlapping)

11. Constraints continued … Disjoint represented by an ‘OR’ Non-disjoint (overlapping) represented by ‘AND’ Disjoint constraint only used for a hierarchy with more than one subclass So 4 possibilities for constraints shown on EERM: {Mandatory, OR} Must belong to exactly one subclass {Mandatory, AND} Must belong to one or more subclasses {Optional, OR} May belong to one subclass or none {Optional, AND} May belong to any number of subclasses

12. Simple Example Staff id name Age  {Mandatory, OR} Full-Time Salary holidays Part-Time hourlyRate contractType “Every member of staff must be either full time or part time”

13. If the logic changed to ….. Staff id name Age  {Optional, OR} Full-Time Salary holidays Part-Time hourlyRate contractType Which statement is correct? A a member of staff may be full and part time B a member of staff has to be at least part-time C a member of staff must be neither full nor part-time D a member of staff may be either full or part time

14. Example Which of these is true? A) A reader could be both Student and Staff B) A student could be taught and research C) Every reader is a member of Staff D) A student is always a research student

15. Example ctd Which of these is true? A) ResearchStudent is a subclass of Staff B) Staff is a superclass of ResearchStudent C) Staff may supervise TaughtStudent D) A ResearchStudent must be supervised by up to 3 Staff

16. Example explanation • A reader may be student, staff, or both, but need not be either • Each Student must be either a taught or a research student • Each research student has one to three supervisors

17. Example: Library EERM We have already mapped most of this – so how do we map the super- and subclasses?

18. Mapping super- and subclasses • Treat superclasses like strong entities (step 1) • Treat subclasses like weak entities (step 2) • Deal with the relationship in Step 6: • 4 possible ways, guidelines below • If using several relations, all include same PK • designer makes final decision

19. Work from the bottom: consider Student and its subclasses first. {Mandatory, Or} suggestsone relation for each combined super/subclass What results from this? Step 6 Example 1

20. Now deal with Reader superclass From previous work, this currently has three subclasses: Staff, TaughtStudent, ResearchStudent Step 6 ctd

21. Reader(readerNo, firstN, lastN, addr) ReaderDetails(readerNo*, matNo, stuEmail, course, stuDept, staffEmail, staffDept, tStu?, rStu?, staff?) Which mapping? • Which is recommended here? • Which is totally unsuitable here? • Which do you prefer? A • Reader(readerNo, firstN, lastN, addr) • TaughtStudent (readerNo*, matNo, email, course) • ResearchStudent (readerNo*, matNo, email, dept) • Staff(readerNo*,email, dept) B • Reader(readerNo, firstN, lastN, addr, matNo, stuEmail, course, stuDept, staffEmail, staffDept, tStu?, rStu?, staff?) C • TaughtStudent(readerNo*, firstN, lastN, addr, matNo, email, course) • ResearchStudent(readerNo*, firstN, lastN, addr, matNo, email, dept) • Staff(readerNo*, firstN, lastN, address,email, dept) D

22. Step 6 Example ctd Now consider Reader with Staff and TaughtStudent, ResearchStudent “subclasses” • {Optional, And} suggests one relation for the superclass and one for all subclasses combined: • Reader(readerNo, firstName, lastName, address) • ReaderDetails(readerNo*, matricNo, studentEmail, course, stuDept, staffEmail, staffDept, tStu?, rStu?, staff?) Flags indicate subclass membership explicitly

23. Step 6 Example ctd • The two tables suggested are clumsy – and will have lots of nulls. • Discard that option and use method for {Optional, Or} instead: use one relation for the superclass and one for each subclass: • Reader(readerNo, firstName, lastName, address) • TaughtStudent(readerNo*, matricNo, email, course) • ResearchStudent(readerNo*, matricNo, email, department) • Staff(readerNo*, email, department) • This works nicely, also for implementing Supervises relationship.

24. Example Summary After mapping is completed, the relational model consists of 9 relations: Author(ISBN*, authorName) Book(ISBN, mainTitle, subtitle, publisher, year) BookCopy(ISBN*, copyID, loanType, purchaseDate, shelf) Borrows(CopyID*, ISBN*, ReaderNo*, dateOut, returnDate) Reader(readerNo, firstName, lastName, address) Staff(readerNo*, email, department) ResearchStudent(readerNo*, matricNo, email, department) TaughtStudent(readerNo*, matricNo, email, course) Supervises(rStudentReaderNo*, staffReaderNo*)

25. Key Points • EERM • Expands ERM • Follows UML standard • Super/subclass structure; Attribute inheritance • One-to-one relationship between super/subclasses • Subclasses can be hierarchical or shared • Participation and disjoint constraints used {Mandatory, Or}, {Optional, And} etc • Mapping: 9 Step procedure includes EERM extension: • In steps 1&2, treat superclasses as strong entities, subclasses as weak entities • Use Step 6 for fine tuning - may change relations

26. Reading • Connolly and Begg “Database Solutions” • Chapter 7 for ERM • Chapter 11 for Enhanced ERM • Connolly and Begg “Database Systems” • Chapter 11 for ERM • Chapter 12 for Enhanced ERM • Chapter 16 for mapping • Rob et al "Database Systems" • Chapter 5 for ERM • Chapter 6 for EERM • Chapter 11.2 for mapping • Any other database main text book will offer help but will use a slightly different notation

27. What’s coming up? • After completing (E)ERM modelling …. • We look at Normalisation • Any database textbook will have a chapter on this • We shall then go back into Oracle • And really start learning SQL • Coming up later: • There will be a class test covering modelling, mapping and normalisation held either just before or just after Christmas • You will be allowed to bring one A4 sheet of notes (double-sided)