Translating ER Schema to Relational Model

1
Translating ER Schema to Relational Model

• Instructor Mohamed Eltabakh
• meltabakh_at_cs.wpi.edu

2
First Check Your Oracle Account
3
Translating ER Schema to Relational Schema

• Primary keys allow entity sets and relationship
  sets to be expressed uniformly as relational
  schemas
• Generally, each relational schema will have
• Number of columns corresponding to the number of
  attributes in ERD
• Column names that correspond to the attribute
  names

4
Basic Mapping

• Simple algorithm covers base the cases, Idea
• Each entity set ? separate relation
• Each relationship type ? separate relation
• Define the primary keys as discussed in the ER
  Model

More optimizations will come
5
Example 1

• Loan (load_number, amount)
• Customer (customer_id, customer_name,
  customer_street, customer_city)
• Borrower (customer_id, load_number)
  Many-to-Many Relationship
• FOREIGN KEY Borrower (customer_id) REFERENCES
  Customer (customer_id)
• FOREIGN KEY Borrower (loan_number) REFERENCES
  Loan (loan_number)

6
Example 2

• Dept (dNumber, dName)
• Course (cNumber, cName)
• Offers (dNumber, cNumber) -- One-to-Many
  Relationship from Dept to Course
• FOREIGN KEY Offers(dNumber) REFERENCES
  Dept(dNumber)
• FOREIGN KEY Offers (cNumber) REFERENCES
  Course(cNumber)

7
Example 3

• Product (pName, pNumber)
• Supplier (sName, sLoc)
• Consumer(cName, cLoc)
• Supply (sName, cName, pName, price, qty)
• FOREIGN KEY Supply(sName) REFERENCES
  Supplier(sName)
• FOREIGN KEY Supply (pName) REFERENCES
  Product(pName)
• FOREIGN KEY Supply (cName) REFERENCES
  Consumer(cName)

8
Example 4

• Part(pNumber, pName)
• Contains(super_pNumber, sub_pNumber, quantity)
• FOREIGN KEY Contains (super_pNumber) REFERENCES
  Part (pNumber)
• FOREIGN KEY Contains (sub_pNumber) REFERENCES
  Part (pNumber)

9
Refinement I Weak Entity Sets

• Weak entity set does not have its own key
• It must relate to the identifying entity set via
  a total, one-to-many relationship set from the
  identifying to the weak entity set

• A weak entity set is mapped to a relation with
  all its attributes the key(s) of the
  identifying entity set(s)
• Primary key of the new relation is the
• Identifying key(s) from identifying entity
  set(s), Plus
• Discriminator of the weak entity set
• Supporting relationship is not mapped in the
  relation model

10
Example 5

• Dept(dNumber, dName)
• Course(dNumber, cNumber, cName)
• FOREIGN KEY Course(dNumber) REFERENCES
  Dept(dNumber)

11
Refinement II One-to-Many Many-to-One
Cardinalities

• Many-to-one and one-to-many relationship sets can
  be represented by adding an extra attribute to
  the many side, containing the primary key of
  the one side
• This transferred primary key becomes a foreign
  key
• The relationship itself is not mapped to the
  relational model
• Any attributes on the relationship go to the
  Many side

12
Example 6

• Dept (dNumber, dName)
• Course (cNumber, dnumber, cName)
• FOREIGN KEY Course(dNumber) REFERENCES
  Dept(dNumber)

Compare this with Example 2 (this is a better
representation)
13
Example 7

• Part(pNumber, pName)
• SubPart(sub_pNumber, super_pNumber, quantity)
• FOREIGN KEY SubPart(super_pNumber) REFERENCES
  Part (pNumber)
• FOREIGN KEY SubPart(sub_pNumber) REFERENCES Part
  (pNumber)

Compare this with Example 4(Here the primary key
of subPart is stronger)
14
Example 8

• Dept (dNumber, dName)
• Course (cNumber, dnumber, cName)
• FOREIGN KEY Course(dNumber) REFERENCES
  Dept(dNumber)

Compare this with Example 6 -- In Example 6
Course.dnumber can be null --In Example 8
Course.dnumber cannot be null
15
Refinement III One-to-One Cardinalities

• One-to-one relationship sets can be represented
  by adding the primary key of either sides to the
  other side
• This transferred primary key becomes a foreign
  key
• The relationship itself is not mapped to the
  relational model
• Any attributes on the relationship go to the side
  receiving the transferred primary key

size
owns
StartDate
16
Example 9
StartDate

• Player(pID, pNumber)
• StorageArea(Number, pID, startDate, Location,
  size)
• FOREIGN KEY StorageArea(pID) REFERENCES
  Player(pID)

17
Refinement IV Composite Derived Attributes
sName
sNum
Student
sAge
address
state
street
city
Mapping strategy (Composite) Include an
attribute for every primitive component of the
composite attribute in the entity Mapping
strategy (Derived) Mapped as is (enforced
later using triggers) Student(sNum, sName, sAge,
street, city, address)
18
Refinement V Multi-valued Attributes
sName
sNum
Student
major
sAge
address
state
street
city

• Mapping strategy
• Represented as a relation by itself.
• The primary key of the relation Attribute the
  primary key of the main entity set
• Student(sNum, sName, sAge, street, city, address)
• StudentMajor(sNum, major)
• FOREIGN KEY StudentMajor (sNum) REFERENCES
  Student (sNum)

19
Refinement VI ISA Relationships

• ISA is a one-to-one relationship BUT the
  sub-class entity sets inherit attributes from the
  super-class entity set
• That is why it does not follow the one-to-one
  rules
• Basically many ways for the mapping depending on
  whether it is total vs. partial and overlapping
  vs. disjoint
• Super-class key is always the primary key

20
ISA Relationship Method 1 (Relation for each
Entity Set)
Student (sNumber, sName) UGStudent (sNumber,
year) GradStudent (sNumber, program) PRIMARY KEY
(Student) ltsNumbergt PRIMARY KEY (UGStudent)
ltsNumbergt PRIMARY KEY (GradStudent)
ltsNumbergt FOREIGN KEY UGStudent (sNumber)
REFERENCES Student (sNumber) FOREIGN KEY
UGStudent (sNumber) REFERENCES Student
(sNumber)

• An UGStudent will be represented in both Student
  relation as well as UGStudent relation
• A GStudent will be represented in both Student
  relation as well as GStudent relation

21
ISA Relationship Method 2 (One Relation for All)
Student (sNumber, sName, year, program) PRIMARY
KEY (Student) ltsNumbergt Note There will be
null values in the relation.
22
ISA Relationship Method 3 (Relations only for
SubClasses)

• Any student will be represented in only one or
  possibly both relations as appropriate
• Assumes total relationship

UGStudent (sNumber, sName, year) GradStudent
(sNumber, sName, program) PRIMARY KEY
(UGStudent) ltsNumbergt PRIMARY KEY (GradStudent)
ltsNumbergt
23
ISA Relationship Method 4 (Relation for each
combination)
Any student will be represented in only one of
the relations as appropriate.
Student (sNumber, sName) UGStudent (sNumber,
sName, year) GradStudent (sNumber, sName,
program) UGGradStudent (sNumber, sName, year,
program) PRIMARY KEY (Student)
ltsNumbergt PRIMARY KEY (UGStudent)
ltsNumbergt PRIMARY KEY (GradStudent)
ltsNumbergt PRIMARY KEY (UGGradStudent) ltsNumbergt
24
Mapping from ER model to Relational model Summary

• Basic algorithm covers the main cases
• Refinement I Weak Entity Sets
• Refinement II One-to-Many Relationships
• Refinement III One-to-One Relationships
• Refinement IV Composite Derived Attributes
• Refinement V Multi-Valued Attributes
• Refinement VI ISA Relationships

25
What about an Exercise