Transcript What is a Database Management System?

Transforming ER & EER diagrams
into Relations
(Chapter 9)
Database Management
COP4540, SCS, FIU
Overview
• A relatively straightforward process with a welldefined set of rules.
• Many CASE Tools can automatically perform
many of the conversion steps.
– CASE tools often cannot model complex data
relationships.
– There are sometimes legitimate alternatives where
you will need to choose a particular solution.
– You must be prepared to perform a quality check on
the results obtained with a CASE Tool.
Database Management
COP4540, SCS, FIU
Map Simple Regular Entities
• Each regular entity type in an ER diagram is
transformed into a relation.
• The name given to the relation is generally the same
as the entity type.
• Each simple attribute of the entity type become an
attribute of the relation.
• Choose one of the key(s) as primary key of the
relation.
• How about composite attributes?
• How about multi-valued attributes?
Database Management
COP4540, SCS, FIU
Map Composite Attributes
• When a regular entity type has a composite attribute, only
the simple component attributes of the composite attribute
are included in the new relation.
Street
Name
City
ID
COSTOMER
Address
State
Zip
CUSTOMER(ID, Name, Street, City, State, Zip)
Database Management
COP4540, SCS, FIU
Map Multi-valued Attributes
• When a regular entity type contains a multivalued attribute, two
new relations (rather than one) are created.
• The first relation contains all of the attributes of the entity type
except the multi-valued attribute.
• The second relation contains two sets of attributes.
– The primary key from the first relation, which becomes a
foreign key of the second relation.
– Multi-valued attribute itself.
– The primary key of the second of relation is the combination
of all attributes.
• The name of the second relation should capture the meaning of
the multi-valued attribute.
Database Management
COP4540, SCS, FIU
Example
Street
Name
City
SSN
EMPLOYEE
Address
State
Skills
Zip
EMPLOYEE(SSN, Name, State, City, State, Zip)
EMPLOYEE_SKILL(SSN, Skill)
Database Management
COP4540, SCS, FIU
Map Binary One-Many Relationship
• Create a relation for each of the two entity types participating in the relationship.
• Include the primary key attribute (or attributes) of the entity on the one-side of
the relationship as a foreign key in the relation that is on the many-side of the
relationship ( a mnemonic you can use to remember this rule is this: The primary
key migrates to many side).
DeptName
SSN
N
STUDENT
1
Major_in
DEPT
Name
STUDENT(SSN, Name, DeptName)
DPET(DeptName)
Database Management
COP4540, SCS, FIU
Map Binary Many-Many Relationships
• Suppose that there is a binary relationship (M:N) between two entity types A
and B. For such a relationship, create a new relation C:
– Include as foreign key attributes in C the primary key for each of the two
participating entity type.
– These attributes become the primary key of C.
– Any attributes that are associated with the relationship are included with the
relation C.
Grade
Name
SID
taking
STUDENT
M
N
CID
COURSE
STUDENT(SID, NAME)
Text
COURSE(CID, Text)
Taking(SID, CID, Grade)
Database Management
COP4540, SCS, FIU
Map Binary One-One Relationship
• Binary 1:1 relationship can be viewed as a special case of 1:m relationship. The
process of mapping such a relationship to relations requires to steps:
– first, two relations are created, one for each of the participating entity type.
– Second, the primary key of one of the relations is included as foreign key in
the other relation.
Name
SSN
EMPLOYEE
DeptName
1
Manages
1
DEPT
EMPLOYEE(SSN, Name, DeptName)
EMPLOYEE(SSN, Name)
DEPT(DeptName)
DEPT(DeptName, ManagerSSN)
Database Management
COP4540, SCS, FIU
Map Unary One-Many Relationship
•
•
•
The entity type in the unary relationship is mapped to a relation using the procedure
described before. Then a foreign key attribute is added within the same relation that
references the primary key values.
Note that the foreign key attribute name should reflect the role name on the one-side.
A recursive foreign key is a foreign key in a relation that references the primary key
values of that same relation.
Name
SSN
EMPLOYEE
Supervisee
N
Manages
Supervisor
1
EMPLOYEE(SSN, Name, SupervisorSSN)
Database Management
COP4540, SCS, FIU
Map Unary Many-Many relationship
• With this type of relationship:
– Two relations are created: one to represent the entity type in the relationship and the
other an associative relation to represent the M:N relationship itself.
– The primary key of the associative relation consists of two parts: both take their
values from the primary key of the other relation.
– Any attribute of the relationship is included in the associative relation.
ID
Name
Item
PART
M
Contains
Quantity
N
Components
Unit_cost
COMPONENT(ItemID, ComponentID, Quantity)
PART(ID, Name, Unit_Cost)
Database Management
COP4540, SCS, FIU
Map Weak Entities
• For each weak entity type, create a new relation and include all of the
simple attributes (or simple components of composite attributes) as
attributes of this relation.
• Then, include the primary key of the owner relation as a foreign key
attribute in this relation.
• The primary key of the new relation is the combination of this primary key
of the owner and the partial key of the weak entity type.
SSN
name
name
addr.
1
salary
Employees
sex
DEPENDANT_
OF
N
Dependants
relationship
birthdate
birthdate
sex
Database Management
COP4540, SCS, FIU
Example Results
Employee( SSN, name, addr, salary sex, birthdate)
Dependants(name, birthdate, sex, relationship, empSSN)
• The relation for the weak entity not only has the attributes of itself, but
also has the key attributes of the other entity sets.
• Do not construct a relation for a “double-diamond” relationship.
Database Management
COP4540, SCS, FIU
Map Ternary (n-ary) Relationship
• It is recommended that you convert the ternary (n-ary) relationship to a
number of binary relationships, and then transform the diagram into
relationships.
SName
SUPPLIER
Quantity
SUPPLY
PART
ProjName
PROJECT
PartNo
Quantity
SName
ProjName
1
SUPPLIER
N
SS
SUPPLY
1
N
SPJ
PROJECT
N
SP
1
PART
PartNo
Database Management
COP4540, SCS, FIU
Example Result
SUPPLIER(SName)
PROJECT(ProjName)
SUPPLY(SName, ProjName, PartID, Quantity)
PART(PartID)
Database Management
COP4540, SCS, FIU
Map Supertype/Subtype Relationships
• The relational data model does not yet
directly support/subtype relationships.
• There are various strategies that database
designer can use to represent these
relationships.
• In this lecture, we introduce the most
commonly employed strategy.
Database Management
COP4540, SCS, FIU
Mapping Strategy
• Create a separate relation for the supertype and for each of
its subtypes.
• Assign to the relation created for the supertype the
attributes that are common to all memebers of the
supertype, including the primary key.
• Assign to the relation for each subtype the primary key of
the supertype, and this primary key is also a foreign key
that references the primary key in relation representing the
supertype.
• Assign to the relation for each subtype the attributes that
are unique to that subtype.
Database Management
COP4540, SCS, FIU
Example
Address
Name
SSN
EMPLOYEE
Date_hired
d
HOURLY
EMPLOYEE
Hourly_rate
SALARED
EMPLOYEE
Annual_salary
CONSULTANT
Stock_option
Contract_No.
Billing_rate
EMPLOYEE( SSN, Name, Address, Date_hired)
HOURLY_EMPLOYEE(SSN, Hourly_Rate)
SALARIED_EMPLOYEE(SSN, Annual_salary, Stock_Option)
CONSULTANT(SSN, Contract_No, Billing_rate)
Database Management
COP4540, SCS, FIU
Map Categories
• Problem
– Multiple supertypes may have different primary
keys.
• Solution
– The concept of surrogate key, a specified new
key attribute for the subtype.
Database Management
COP4540, SCS, FIU
Example
CName
Address
Name
SSN
PERSON
COMPANY
Total_Balance
U

ACCOUNT
HOLDER
BName
N
M
Has_Acct
BANK
Database Management
COP4540, SCS, FIU
Example Results
COMPANY(CName, Address, OwnerID)
PERSON(SSN, Name, OwnerID)
ACCOUNTHOLDER(OwnerID)
HASACCOUNT(OwnerID, BName, Total_Balance)
BANK(BName)
Database Management
COP4540, SCS, FIU