SA 0951 a Enhanced EntityRelationship Modelling EERM and

SA 0951 a Enhanced Entity-Relationship Modelling (EERM) and Mapping Reading: e. g. Connolly/Begg (4 th ed): Chapter 12 – Enhanced ERM; Mapping: “Step 6” Rob et al: Chapter 6. 1 (Advanced data modelling), Chapter 11. 2 "Step 6". 1

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

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

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

Continued …. . l Staff has a superclass/subclass relationship – – l 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 5 Full. Time Part. Time

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

Real Example Staff id name Age Full-Time salary holidays generalisation Part-Time hourly. Rate contract. Type specialisation 7

Poor Example 1 Staff id name age Full-Time salary sex 8 generalisation Part-Time hourly. Rate sex WHY is this a poor example? A salary should be in the Staff entity specialisation 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

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

Constraints l Participation – l l 10 A subclass member is always also a member of the superclass Mandatory participation (of a superclass member in a subclass member): l A superclass member must be a member of a subclass Optional participation (of a superclass member in a subclass member): l 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)

Constraints continued … l l 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} l – {Mandatory, AND} l – May belong to one subclass or none {Optional, AND} l 11 Must belong to one or more subclasses {Optional, OR} l – Must belong to exactly one subclass May belong to any number of subclasses

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

If the logic changed to …. . Staff id name Age Full-Time Salary holidays 13 {Optional, OR} Part-Time hourly. Rate contract. Type 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

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 14

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

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

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

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

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

Step 6 ctd l l l 20 Now deal with Reader superclass From previous work, this currently has three subclasses: Staff, Taught. Student, Research. Student

Which mapping? 1. Which is recommended here? 2. Which is totally unsuitable here? 3. Which do you prefer? A l l Reader(reader. No, first. N, last. N, addr) Taught. Student (reader. No*, mat. No, email, course) Research. Student (reader. No*, mat. No, email, dept) Staff(reader. No*, email, dept) B l Reader(reader. No, first. N, last. N, addr, mat. No, stu. Email, course, stu. Dept, staff. Email, staff. Dept, t. Stu? , r. Stu? , staff? ) C l Taught. Student(reader. No*, first. N, last. N, addr, mat. No, email, course) Research. Student(reader. No*, first. N, last. N, addr, mat. No, email, dept) Staff(reader. No*, first. N, last. N, address, email, dept) l l D 21 l l Reader(reader. No, first. N, last. N, addr) Reader. Details(reader. No*, mat. No, stu. Email, course, stu. Dept, staff. Email, staff. Dept, t. Stu? , r. Stu? , staff? )

Step 6 Example ctd Now consider Reader with Staff and l Taught. Student, Research. Student “subclasses” l l 22 {Optional, And} suggests one relation for the superclass and one for all subclasses combined: Reader(reader. No, first. Name, last. Name, address) Reader. Details (reader. No*, matric. No, student. Email, course, stu. Dept, staff. Email, staff. Dept, t. Stu? , r. Stu? , staff? ) Flags indicate subclass membership explicitly

Step 6 Example ctd l l 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: l l l 23 Reader(reader. No, first. Name, last. Name, address) Taught. Student (reader. No*, matric. No, email, course) Research. Student (reader. No*, matric. No, email, department) Staff(reader. No*, email, department) This works nicely, also for implementing Supervises relationship.

Example Summary After mapping is completed, the relational model consists of 9 relations: 24 Author(ISBN*, author. Name) Book(ISBN, main. Title, subtitle, publisher, year) Book. Copy(ISBN*, copy. ID, loan. Type, purchase. Date, shelf) Borrows(Copy. ID*, ISBN*, Reader. No*, date. Out, return. Date) Reader(reader. No, first. Name, last. Name, address) Staff(reader. No*, email, department) Research. Student(reader. No*, matric. No, email, department) Taught. Student(reader. No*, matric. No, email, course) Supervises(r. Student. Reader. No*, staff. Reader. No*)

Key Points l EERM Expands ERM – Follows UML standard l 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 – l 25

Reading l Connolly and Begg “Database Solutions” – – l Connolly and Begg “Database Systems” – – – l – – 26 Chapter 11 for ERM Chapter 12 for Enhanced ERM Chapter 16 for mapping Rob et al "Database Systems" – l Chapter 7 for ERM Chapter 11 for Enhanced ERM 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

What’s coming up? l After completing (E)ERM modelling …. – We look at Normalisation l l We shall then go back into Oracle And really start learning SQL Coming up later: – – 27 Any database textbook will have a chapter on this 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 A 4 sheet of notes (double-sided)