Transcript PPTX

Chapter 9 (6/5e): ER to Relational Transformation
CSE
4701
Prof. Steven A. Demurjian, Sr.
Computer Science & Engineering Department
The University of Connecticut
191 Auditorium Road, Box U-155
Storrs, CT 06269-3155
[email protected]
http://www.engr.uconn.edu/~steve
(860) 486 - 4818


The Majority of these slides are being used with the permission of Dr. Ling
Lui, Associate Professor, College of Computing, Georgia Tech.
Other slides (figures) have been adapted from the AWL web site for the
textbook.
Chapter 9-1
Designing a Relational DB Schema

CSE
4701

Defining Relations
 Deciding which Attributes belong Together in Each
Relation
 Choosing Appropriate Names for the Relations and
Their Attributes
 Specifying the Domains and Data Types of the
Various Attributes
 Identifying the Candidate Keys and Choosing a PK
for Each Relation, and Specifying All Foreign Keys
Two Techniques for Relational Schema Design
 Using ER-to-Relational Mapping (Chapter 9)
 Relational Normalization Theory (Chapter 14)
Chapter 9-2
Design Process - Where are we?
CSE
4701
Conceptual
Design
Conceptual
Schema
(ER Model)
Logical
Design
Logical Schema
(Relational Model)
Step 1: ER-to-Relational Mapping
Analysis
of Schema
Normalized
Schema
Step 2: Normalization
Chapter 9-3
ER-to-Relational Mapping Algorithm
CSE
4701


Step 1: For Each Regular Entity Type E
 Create a Relation RE
 Include only the Simple Attributes of a Composite
Attribute
Step 2: For Each Weak Entity Type W with Owner
Entity Type E
 Create a Relation RW
 Include as Attributes
All
Simple Attributes of W
Primary Key attribute(s) of the Relation that Corresponds
to W’s Owner Entity Type E
Chapter 9-4
ER-to-Relational Mapping Algorithm
CSE
4701


Step 3: For Each 1:1 Relationship
 Identify the Relations R1 and R2
 Include as Foreign Key of one Relation the Primary
Key of the Other Relation
Step 4: For each Regular 1:n Relationship
 Include as Foreign Key in the Entity Type at the nside of the Relationship, the Primary Key of the
Entity Type at the 1-side of the Relationship
Chapter 9-5
ER-to-Relational Mapping Algorithm
CSE
4701

Step 5: For Each Binary n:m Relationship
 Create a New Relation, whose Attributes Include
All
Simple Attributes of the n:m Relationship as Non-key
Attributes
PKs of the Relations that Represent the Participating
Entity Types, as FK Attributes in this New Relation

Step 6: For Each Multi-valued Attribute A
 Create a New Relationship R that Includes
An
Attribute Corresponding to A
The PK Attribute of the Relation Whose Corresponding
Entity Type or Relationship Has A as an Attribute
Chapter 9-6
ER-to-Relational Mapping Algorithm
CSE
4701


Step 7: For Each n-ary Relationship R, n>2
 Create a New Relation to Represent R
Step 8: Convert Each Specialization for Superclass C
with Attributes {k, A1, …, An} (k is the PK), where C
has n Subclasses {S1, ..., Sn}
 Create a Relation Si for each Subclass Entity
(1<= i <= n) with Attributes
Attrs(Si) = {k}  {attributes of Si}, and PK{Si} = k
 Note that the Relation for C was created in an
Earlier Step
 Note also that there are Three Other Options for
Mapping Specialization Hierarchies
Chapter 9-7
Recall our Ongoing Example
Acount #
Income
CSE
4701
ACCOUNT
Supplier
Name
Supplier No
SUPPLIER
Expenses
1
RECORDS
BALANCE
Project No
Project
Name
1
N
Location
PROJECT
SUPPLY
M
1
1
Credit
Budget
Location
Amount
Duration
Date
MANAGES
Part No
L
Part
Name
PART
WORKS ON
Office
Color
N
1
N
Employee
Name
Consists of
Employee No
EMPLOYEE
CONTAIN
Title
o
MANUFACTURED_PART
Batch No
Drawing No
ENGINEER
Responsibility
Weight
M
Made-up of

Project
d
Salary
QTY

PURCHASED_PART
Price
Street #
SECRETARY
Specialty
Car
Address
Apt. #
Office
City
SALESPERSON
Region
Chapter 9-8
Can we Transition the Previous ER to ...
CSE
4701
Note that there are
no subclasses here!
Chapter 9-9
Step 1 - Handling Entities
CSE
4701

For Each Regular Entity Type E in the E-R Schema,
Create a Relation R
 Include as Attributes in R Only the Simple
Attributes of E
 For Composite Attributes of E, Include Only their
Constituent Simple Attributes in R
 The Key of E Becomes the PK of R
If There is More Than One Key Attributes of E,
Then Choose One as the Primary Key of R
 For Multi-Valued Attributes – Create a new Table in
Step 6
Chapter 9-10
Step 1 – Example

CSE
4701
From the Following Strong Entity Types:
Supplier
Name
Supplier No
Project No
Project
Name
Budget
SUPPLIER
PROJECT
Credit
Employee
Name
Location
Employee No
Part No
Part
Name
Location
Weight
EMPLOYEE
Title
Salary
Address
PART
Color
Apt. #
City
Street #

Create Four Corresponding Relations with Attribute
Keys as PKs
Chapter 9-11
Step 1 – Example

CSE
4701
The Keys are Underlined
EMPLOYEE(ENO, ENAME, TITLE, SALARY, APT#,
STREET, CITY)
PROJECT(PJNO, PNAME, BUDGET)
SUPPLIER(SNO, SNAME, CREDIT, LOCATION)
PART(PNO, PNAME, WGT, COLOR)

Notes:


LOCATION is Not Included in PROJECT – handled
later in Step 6
ACCOUNT is Weak and ENGINEER,
SECRETARY, and SALESPERSON are Subclasses
Chapter 9-12
Step 2 – Weak Entities
CSE
4701

For Each Weak Entity Type W Associated with the
Strong Entity Type E in the E-R Schema, Create a
Relation R
 Attributes of R are the Simple Attributes of W (or
the Simplified Versions of Composite Attributes)
 Include Among the Attributes of R all of the Key
Attributes of Strong Entity E
These

are the Foreign Keys of R
The PK of R is the Combination of the PK of E and
the Partial Key of W
Chapter 9-13
Step 2 – Example

From:
Acount #
Income
Expenses
CSE
4701
ACCOUNT

Create relation ACCOUNT as follows
ACCOUNT(PJNO, ACNO, INCOME, EXPENSES)
foreign key

Note that
ACNO

Corresponds to Account# on ACCOUNT
PJNO Corresponds to ProjectNo on PROJECT
We’ll
Handle Records Relationship in Separate Step
Chapter 9-14
If there was a Dependent …

CSE
4701
Create a Dependent Table:
 DEPENDENT(ENO, NAME, SEX,
BIRTHDATE, RELATIOSHIP)
 Primary Key from EMPLOYEE (ENO) PLUST
DEPENDENT (NAME)
Chapter 9-15
Step 3 – 1:1 Relationships
CSE
4701


For Each 1:1 Relationship R in E-R Schema where the
Two Related Entities are E1 and E2
Let Relations S and T Correspond to E1 and E2
Respectively
 Choose One of the Relations, Preferably One Whose
Participation in R is Total (Say S)
Include in S as a FK, the PK of T
 If there are Attributes Associated With the
Relationship R, Include Them in S
 You May Want to Rename the Attributes When You
Do This
Chapter 9-16
Step 3 – Example
CSE
4701
Project No
Project
Name
Employee No
Budget
Location
PROJECT
1
Employee
Name
EMPLOYEE
Title
Salary
Address
1
MANAGES
Apt. #
City
Street #

For 1:1 Relationship MANAGES between the
EMPLOYEE and PROJECT Entities
 Choose PROJECT as S, Since its Participation in
the MANAGES relationship is Total
Include in PROJECT the PK of EMPLOYEE
PROJECT(PJNO, PNAME, BUDGET)
Becomes
PROJECT(PJNO, PNAME, BUDGET, MGR)
Chapter 9-17
Step 3 – Example
CSE
4701
Project No
Project
Name
Location
PROJECT
Income
Expenses
BALANCE
ACCOUNT
1

Acount #
Budget
RECORDS
1
For 1:1 Relationship RECORDS between PROJECT
and ACCOUNT Entities:
 Choose ACCOUNT as S (ACCOUNT is a Weak
Entity, so this is the only choice that makes sense)
 Include PJNO (done in step 2) and BALANCE
ACCOUNT(PJNO, ACNO, INCOME, EXPENSES)
Becomes
ACCOUNT(PJNO, ACNO, INCOME, EXPENSES,
BALANCE)
Chapter 9-18
Step 4 – 1:N Relationships
CSE
4701

For Each Regular (Non-weak) Binary 1:N Relationship
Type R in the E-R Schema
 Identify the Relation S that Corresponds to the
Entity Type at the N-side of the Relationship
 Let the Other Relation on the 1-side be T
Include
in S as a Foreign Key, the Primary Key of T
If There are Attributes Associated with the Relationship
R, Include them in S as well
Chapter 9-19
Step 4 – Example
CSE
4701
Project No
Project
Name
Employee No
Budget
Employee
Name
EMPLOYEE
Duration
Title
Location
PROJECT
N
1
WORKS_ON
Responsibility
Salary
Address
Apt. #
City
Street #

We have only the WORKS ON relationship
Defined between PROJECT and EMPLOYEE
 N side of the Relationship is EMPLOYEE
 1 side of the Relationship is PROJECT
Chapter 9-20
Step 4 – Example
CSE
4701


Include in EMPLOYEE
 Primary Key (PJNO) of PROJECT
 Attributes of the WORKS ON relationship
(Duration & Responsibility)
 This is Since EMPLOYEE WORKS ON Only 1
PROJECT
EMPLOYEE(ENO, ENAME, TITLE, SALARY, APT#,
STREET, CITY)
Becomes
EMPLOYEE(ENO, ENAME, TITLE, SALARY, APT#,
STREET, CITY, PJNO, DURATION, RESP)
Chapter 9-21
Step 5 – M:N Relationships
CSE
4701

For each Binary M:N Relationship Type R Connecting
E1 and E2 in the ER schema, create a relation S:
 Include as FKs of S, the PKs of the two relations
that correspond to E1 and E2
These
Attributes, Together, Form the primary key of S
Also Include in S any Attributes of the Relationship R
Chapter 9-22
Step 5 – M:N Relationships
Part No
CSE
4701
Part
Name
Weight
PART
M
N
Made-up of
Consists of
CONTAIN

Color
QTY
We have one M:N Relationship: CONTAIN, which is a
Recursive Relationship over the PART Entity

We create the following relation:
CONTAIN(PNO, CPNO, QTY)

Recursive Relationship Requires us to Distinguish the
Part No as PNO and CPNO
Chapter 9-23
Step 6 – Multivalued Attributes
CSE
4701

For each Multivalued Attribute A, Create a New
Relation R
 The Attributes of R are A (if composite, then use
only the simple components)
 Include in R the PK K of the entity that contained A
 The PK of R then becomes K and A together

Recall that Multivalued Means that a Given Attribute
has Multiple Values in the Entity

Previous Degrees of a Student {Bachelors, Masters}

Can have Neither, One, or Both for each Student
Chapter 9-24
Step 6 – Multivalued Attributes
The Same Project (Project No) can
be Spread Across Multiple Locations
CSE
4701
Project No
Project
Name
PROJECT

Budget
Location
In our Example, we create One New Relation for the
Multivalued Attribute LOCATION in PROJECT

This Relation is Created as Follows:
LOC (PJNO, LOCATION)

That is, Storrs, Hartford, etc., for Project P1
Chapter 9-25
Step 7 – Higher Order Relationships
CSE
4701

For Each Higher Order Relationship Type R
Connecting E1, E2, …, En in the E-R schema, Create a
relation S
 Include in S the PKs of the Relations Corresponding
to E1, E2, …, En
 Also Include in S any Attributes of R
 The PK of S is the Combination of the PKs of the
Relations Corresponding to E1, E2, …, En
Chapter 9-26
Step 7 – Example
CSE
4701

The Only High-Order Relation is SUPPLY between
SUPPLIER, PROJECT and PART
 Create Relation SUPPLY where Amount and Date
were Attributes Defined on Supply
SUPPLY(SNO,
SUPPLIER
N
PJNO, PNO, AMOUNT, DATE)
PROJECT
SUPPLY
M
Amount
Date
L
PART
Chapter 9-27
Step 8 – Specialization

CSE
4701
For Each Specialization with m Subclasses {S1, …, Sm}
and Generalized Superclass C, where the Attributes of
C are {k, A1, …, An} (k is the PK), Convert According
to the Following:
Option 1: General Case:
Create a Relation T for C with Attributes
{k, A1, …, An} and use k as the PK
Create one Relation Ui for each Si
Include in Ui all the attributes of Si and k
Use k as the primary key of Ui.
Chapter 9-28
Step 8 – Specialization (cont’d)
CSE
4701
Option 2: No Superclass Relation:
Create One Relation Ui for each Si.
Include in Ui all Attributes of Si and {k, A1, …,
An}
Use k as the Primary Key of Ui
Option 3: For Disjoint Subclasses:
Create a Single Relation U which Contains all the
Attributes of all Si and {k, A1, …, An} and t
Use k as the primary key of Ui
The Attribute t Indicates the Type Attribute
According to which Specialization is Performed
Chapter 9-29
Step 8 – Specialization (cont’d)
CSE
4701
Option 4: For Overlapping Subclasses:
Create a single relation U which contains all
Attributes of all Si and all Attributes of C
({k, A1, …, An}) and {t1, …, tm}
Use k as the Primary Key of Ui
The Attributes ti are Boolean Valued, Indicating
if a Tuple Belongs to Subclass Si
Note: May Generate a Large Number of Null
Values in the Relation
Chapter 9-30
Step 8 – Example

CSE
4701
Specialization of EMPLOYEE
 EMPLOYEE Already Exists; option 2 is not valid
 Specialization is Disjoint; option 4 is not valid
 Option 1:
EMPLOYEE(ENO, ENAME, TITLE, SALARY, APT#,
STREET, CITY, PJNO, DURATION, RESP)
ENGINEER (ENO, PROJECT, OFFICE)
SECRETARY(ENO, OFFICE, SPECIALTY)
SALESPERSON(ENO, CAR, REGION)

Option 3:
EMPLOYEE(ENO, ENAME, TITLE, SALARY, APT#,
STREET, CITY, PJNO, DURATION, RESP, TYPE,
PROJECT, OFFICE, SPECIALTY, CAR, REGION)
Chapter 9-31
Step 8 – Example (cont’d)

CSE
4701
Specialization of PART
 Relation PART Already Exists; Option 2 is not valid
 Specialization is Overlapping;Option 3 is not valid
 Option 1:
PART(PNO,
PNAME, WGT, COLOR)
MANUFACTURED_PART(PNO, BATCH#,
DRAWING#)
PURCHASED_PART(PNO, PRICE)

Option 4:
PART(
PNO, PNAME, WGT, COLOR,
MAN, BATCH#, DRAWING#,
PURC, PRICE)
Note that MAN and PURC are Boolean Flags
Chapter 9-32
Final Set of Relations
CSE
4701
EMPLOYEE(ENO, ENAME, TITLE, SALARY, APT#,
STREET, CITY, PJNO, DURATION, RESP)
PROJECT(PJNO,PNAME,BUDGET,MGR)
SUPPLIER(SNO,SNAME,CREDIT,LOCATION)
PART(PNO, PNAME, WGT, COLOR, MAN, PURC,
BATCH#, DRAWING#, PRICE)
ENGINEER(ENO, PROJECT,OFFICE)
SECRETARY(ENO, OFFICE, SPECIALTY)
SALESPERSON(ENO, CAR, REGION)
SUPPLY(SNO, PJNO, PNO, AMOUNT,DATE)
LOC(PJNO, LOCATION)
CONTAIN(PNO, CPNO,QTY)
ACCOUNT(PJNO, ACNO, INCOME, EXPENSES,
BALANCE)
Chapter 9-33
Step 8 – Option 1 Example
CSE
4701
Chapter 9-34
Step 8 – Option 2 Example
CSE
4701
Chapter 9-35
Step 8 – Option 3 Example
CSE
4701
Secretary
Tech
Engr
Where JobType Playing the
Role of a Type Attribute
Chapter 9-36
Step 8 – Option 4 Example
CSE
4701
Boolean
Mflag - are the
next three fields
active - then
Manufactured_Part
Boolean
Pflag - are the
next three fields
active - then
Purchased_Part
Chapter 9-37
Step 8 - Recall Specialization Lattice
Person
CSE
4701
Employee
Staff
Faculty
Alumni
StudAsst
ResAsst
Student
Grad
UnderGrad
TeachAsst
Chapter 9-38
Step 8 - Corresponding Relations
CSE
4701
Chapter 9-39
Step 8 - Recall Categories - Figure 4.8
CSE
4701
Chapter 9-40
Step 8 - Recall Categories - Figure 4.8
CSE
4701
Chapter 9-41
Step 8 - Corresponding Relations
CSE
4701
Chapter 9-42
Concluding Remarks

CSE
4701

What have we Learned in Chapter 9?
 Transition from a Conceptual Schema (ER) to Set of
Relations
 Transition is Necessary as First Step in the
Relational Database Design Process
 Result of Transition is a Set of Relations that Capture
the Entities and “Relationships” in ER Diagram
 Objective: Flatten out the ER into Relations
How is Chapter 9 Related to the Semester Project?
 Phase II in the Semester Project is to Transition your
ER Diagram into a Set of Relational Tables
Step
1: ER to Relational Transformation (Chapter 9)
Step 2: Relational Normalization (Chapter 14)
Chapter 9-43