Similar presentations:
Analysis and Design of Data Systems. ER to Relational Mapping. (Lecture 10)
1.
IE301Analysis and Design of Data Systems
Lecture 10
ER to Relational Mapping
Aram Keryan
2. Phases of Database Design
MiniworldRelational
DBMS
Requirements
Collection and
Analysis
Conceptual
Design
Relational
Database
Schema
We are here
3. Entity-Relationship Diagram
4. Relational Database Schema
5. Step 1: Mapping Strong Entity Types
For each strong entity type E in the ER schema create acorresponding relation R and include:
1.1) Include all the simple attributes of E:
Simple attributes of EMPLOYEE entity type are:
Bdate, Address, Salary, Sex
EMPLOYEE (Bdate, Address, Salary, Sex)
6. Step 1: Mapping Strong Entity Types
1.2) Include only the simple component attributes of a composite attribute:Simple component attributes of a composite attribute Name of
EMPLOYEE entity type are: Fname, Minit, Lname
EMPLOYEE (Bdate, Address, Salary, Sex, Fname, Minit, Lname)
7.
Step 1: Mapping Strong Entity Types1.3) Choose one of the key attributes of E as the primary key for R. If the
chosen key of E is a composite, then the set of simple attributes
form it will together form the primary key of R.
that
The key attribute of EMPLOYEE is Ssn
EMPLOYEE (Bdate, Sex, Salary, Address, Fname, Minit, Lname, Ssn)
8. Step 1: Mapping Strong Entity Types
DEPARTMENT (Dname, Dnumber)PROJECT (Pname, Pnumber, Plocation)
9. Step 2: Mapping Weak Entity Types
EMPLOYEE (Bdate, Sex, Salary, Address,Fname, Minit, Lname, Ssn)
1
For each weak entity type W in the ER
schema with owner entity type E,
create a relation R and include:
2.1) Include all the simple attributes (or
simple components of composite attributes)
of W as attributes of R.
DEPENDENT(Sex, Bdate, Relationship)
10. Step 2: Mapping Weak Entity Types
EMPLOYEE (Bdate, Sex, Salary, Address,Fname, Minit, Lname, Ssn)
For each weak entity type W in the ER
schema with owner entity type E,
create a relation R and include:
1
2.2) Include as foreign key attributes of R, the
primary key attribute(s) of the relation(s) that
correspond to the owner entity type(s) E. Also
include the attributes of partial key of W. These
are the attributes of the primary key of W.
DEPENDENT(Sex, Bdate, Relationship,
Essn, Dependent_name)
11.
Step 3: Mapping ofBinary 1:1 Relationships
Let’s say S and T are entity types participating in 1:1 Relationship R
K
S
1
R
1
T
Case 1: One of the entity types, say T, has total participation and the
second entity type has partial participation: Then Include as a foreign key
in T the primary key of S. Include in S all the simple attributes of R.
Case 2: Both entity types have partial participations: Then arbitrary select
one of the entity types, say T, and include its primary key in the relation
corresponding to the second entity type (S) as a foreign key. Also, include
in the S all the simple attributes of the 1:1 relation (K).
Case 3: Both entity types have total participations: Then merge two entity
types in a single relation
12. Step 3: Mapping of Binary 1:1 Relationships
Start_date1
1
EMPLOYEE (Bdate, Sex, Salary, Address, Fname, Minit, Lname, Ssn)
DEPARTMENT (Dname, Dnumber, Mgr_ssn, Mgr_start_date)
13. Step 4: Mapping of Binary 1:N Relationships
Let’s say S and T are entity types participating in 1:N Relationship RK
S
1
R
N
T
Identify the relation that represents the participating entity type at the
N-side of the relationship type (T)
Include as foreign key in T the primary key of the relation S
Include any simple attributes (K) (or simple components of composite
attributes) of the 1:N relationship type as attributes of T
14. Step 4: Mapping of Binary 1:N Relationships
N1
EMPLOYEE (Bdate, Sex, Salary, Address, Fname, Minit, Lname, Ssn, Dno)
DEPARTMENT (Dname, Dnumber, Mgr_ssn, Mgr_start_date)
15. Step 5: Mapping of Binary M:N Relationships
KS
M
R
N
T
• For each binary M:N relationship R, create a new relation F to represent R.
• Include as foreign key attributes in F the primary keys of the relations that
represent the participating entity types (S and T); their combination will
form the primary key of F.
• Include any simple attributes of the M:N relationship (K)(or simple
components of composite attributes) as attributes of F
16. Step 5: Mapping of Binary M:N Relationships
SexEMPLOYEE (Bdate, Sex, Salary, Address, Fname, Minit, Lname, Ssn, Dno)
PROJECT (Pname, Pnumber, Plocation)
WORKS_ON (Pno, Hours, Essn)
17. Step 6: Mapping of Multivalued Attributes
AS
• For each multivalued attribute A, create a new relation R.
• Include in R an attribute corresponding to A
• Include as a foreign key in R the primary key attribute of the
relation that represents the entity type or relationship that has
A as a multivalued attribute.
• The primary key of R is the combination of A and K.
• If the multivalued attribute is composite, we include its simple
components.
18. Step 6: Mapping of Multivalued Attributes
DEPT_LOCATIONS (Dnumber, Dlocation)19. Step 7: Mapping of N-ary Relationships
• For each n-ary relationship type R, where n > 2, create a newrelation S to represent R.
• Include as foreign key attributes in S the primary keys of the
relations that represent the participating entity types.
• Include any simple attributes of the n-ary relationship type
(or simple components of composite attributes) as attributes
of S
• The primary key of S is usually a combination of all the
foreign keys that reference the relations representing the
participating entity types.
• if the cardinality constraints on any of the entity types E
participating in R is 1, then the primary key of S should not
include the foreign key attribute that references the relation
E’ corresponding to E