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

• ERMs 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

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 shouldnt 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
Step 6 Example 1

• Work from the bottom consider Student and its
  subclasses first.
• Mandatory, Or suggests one relation for each
  combined super/subclass
• What results from this?

20
Step 6 ctd

• Now deal with Reader superclass
• From previous work, this currently has three
  subclasses
• Staff, TaughtStudent, ResearchStudent

21
Which mapping?

1. Which is recommended here?
2. Which is totally unsuitable here?
3. Which do you prefer?

• Reader(readerNo, firstN, lastN, addr)
• TaughtStudent (readerNo, matNo, email, course)
• ResearchStudent (readerNo, matNo, email, dept)
• Staff(readerNo,email, dept)

• A

B

• Reader(readerNo, firstN, lastN, addr, matNo,
  stuEmail, course, stuDept,
  staffEmail, staffDept, tStu?, rStu?, staff?)

• TaughtStudent(readerNo, firstN, lastN, addr,
  matNo, email, course)
• ResearchStudent(readerNo, firstN, lastN, addr,
  matNo, email, dept)
• Staff(readerNo, firstN, lastN, address,email,
  dept)

C

• Reader(readerNo, firstN, lastN, addr)
• ReaderDetails(readerNo, matNo, stuEmail, course,
  stuDept, staffEmail,
  staffDept, tStu?, rStu?, staff?)

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 12, 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
Whats 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)