sa0951a enhanced entity relationship modelling eerm and mapping l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SA0951a Enhanced Entity-Relationship Modelling (EERM) and Mapping PowerPoint Presentation
Download Presentation
SA0951a Enhanced Entity-Relationship Modelling (EERM) and Mapping

Loading in 2 Seconds...

play fullscreen
1 / 27

SA0951a Enhanced Entity-Relationship Modelling (EERM) and Mapping - PowerPoint PPT Presentation


  • 341 Views
  • Uploaded on

SA0951a 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". Some limitations of ERMs.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SA0951a Enhanced Entity-Relationship Modelling (EERM) and Mapping' - dewayne


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sa0951a enhanced entity relationship modelling eerm and mapping

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".

some limitations of erms
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

specialisation generalisation
Specialisation/Generalisation

Ties into Object Oriented design

This extension uses

Superclasses

Subclasses

Attribute inheritance

Constraints

Participation

Disjoint

super subclasses
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

continued
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

a word on attribute inheritance
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

real example
Real Example

Staff

id

name

Age

generalisation

Full-Time

salary

holidays

Part-Time

hourlyRate

contractType

specialisation

poor example 1
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

poor example 2
Poor Example 2

Staff

id

name

age

generalisation

Full-Time

salary

holidays

Car

registration

colour

specialisation

What is the problem here?

constraints
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)
constraints continued
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

simple example
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”

if the logic changed to
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

example
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

example ctd
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

example explanation
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
example library eerm
Example: Library EERM

We have already mapped most of this – so how do we map the super- and subclasses?

mapping super and subclasses
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
step 6 example 1
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
step 6 ctd
Now deal with Reader superclass

From previous work, this currently has three subclasses:

Staff, TaughtStudent, ResearchStudent

Step 6 ctd
which mapping
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

step 6 example ctd
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

step 6 example ctd23
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.
example summary
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*)

key points
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
reading
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
what s coming up
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)