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