Relational DB Schema: ER to Relations

For each regular (strong) entity type E in the ER schema, you should create a relation R
Relation R includes all the simple attributes of E
Only include simple component attributes of a composite attribute
Set the key attribute of E as the primary key for R
If the chosen key of E is a composite, use its set of simple components
Relations EMPLOYEE, DEPARTMENT, and PROJECT correspond to regular entity types EMPLOYEE, DEPARTMENT, and PROJECT in the example
Ssn, Dnumber, and Pnumber are primary keys for the relations EMPLOYEE, DEPARTMENT, and PROJECT

For each weak entity type W in the ER schema with owner entity type E, create a relation R
Relation R should include all simple attributes (or simple components of composite attributes) of W as attributes of R
Include as foreign key attributes of R, the primary key attribute(s) of the relation(s) that correspond to the owner
The relation DEPENDENT corresponds to the weak entity type DEPENDENT, for example
The primary key Ssn of the EMPLOYEE relation corresponds to the owner as a foreign key attribute of DEPENDENT which is renamed as Essn
A primary key of the DEPENDENT relation is the combination {Essn, Dependent_name

For each binary 1:1 relationship type R in the ER schema, identify the relations S and T that correspond to the entity types participating in R
Choose one of the relations (S say), and include the primary key of T as a foreign key in S
It is better to choose an entity type with total participation in R in the role of S
The primary key of the EMPLOYEE relation is included as foreign key in the DEPARTMENT
In the example, the 1:1 relationship type MANAGES is mapped by choosing DEPARTMENT to serve in the role of S, its participation in the MANAGES relationship type is total

For each regular binary 1:N relationship type R, identify the relation S that represents the participating entity type at the N-side of the relationship type
Includes the primary key of the relation T as a foreign key in S
The relation T represents the other entity type participating in R
The 1:N relationship types WORKS_FOR, CONTROLS, and SUPERVISION can be mapped
For WORKS_FOR include the primary key Dnumber of the DEPARTMENT relation as foreign key in the EMPLOYEE relation and call it Dno
For SUPERVISION include the primary key of the EMPLOYEE relation as foreign key in the EMPLOYEE relation itself (is recursive) and call it Super_ssn
The CONTROLS relationship is mapped to the foreign key attribute Dnum of PROJECT, referencing primary key Dnumber of the DEPARTMENT

For each binary M:N relationship type R, create a new relation S to represent R
Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types
Their combination forms the primary key of S
Also, include any simple attributes of the M:N relationship type as attributes of S
An M:N relationship type cannot be represented by a single foreign key attribute in one of the participating relations.
A separate relationship relation S must be created due to the M:N cardinality ratio
Relationship type WORKS_ON is mapped by creating the relation WORKS_ON
The primary keys of the PROJECT and EMPLOYEE relations are included as foreign keys in WORKS_ON, and renamed them as Pno and Essn
The primary key is a combination of both of these values
Attribute Hours is included in WORKS_ON to represent the Hours attribute of the relationship type

For each multivalued attribute A, a new relation R is created
This relation Rincludes an attribute corresponding to A, along with the primary key attribute K
The primary key K acts as a foreign key in R
The relation represents the entity type or relationship type that possesses A as a multivalued attribute
The relation DEPT_LOCATIONS is made
The attribute Dlocation is a multivalued attribute because LOCATIONS of DEPARTMENT is one
Dnumber (the foreign key) represents the primary key of the DEPARTMENT relation
The primary key of DEPT_LOCATIONS is the combination of Dnumber and Dlocation

For each n-ary relationship type R (n > 2), create a new relation S to represent R
Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types
Simple attributes of the n-ary relationship type should be included
The primary key of S is usually a combination of all the foreign keys that reference the relations representing the participating entity types
The relationship type SUPPLY is mapped to the relation SUPPLY
SUPPLY's primary key is the combination of the three foreign keys {Sname, Part_no, Proj_name}