Conversion of ER-Diagram into Table

Reduction of ER-Diagram into Table

This topic will describe how to reduce the Entity-Relationship (ER) diagram into Tables.

Example

An enterprise has different departments. Each department has supervisors and partly one employee. Employees have to be assigned to at least one, however, possibly more departments. At least one employee is authorized to a project, but an employee can be on holiday and not authorize to any project. The essential information areas are the name of the departments, projects, supervisors, and employees, including the supervisor and employee-no and the specific project-no.

Solution

We now informally represent the steps of an algorithm for Entity-Relationship to relational mapping. Entity-Relationship diagram for a company database is shown below:

Now we can convert this to a similar schema

The COMPANY relational schema display below can be derived from the Entity-Relationship schema by following these steps. We illustrate every step by using the use of examples from the COMPANY schema

Employee

Department

Dept Locations

Project

Works on

Dependent

Steps for Reduction of Entity-Relationship Diagram into Tables

Step1

Create a relation R that contains all the simple attributes of E for each regular entity type E. In the Entity-Relationship schema, provide only the simple element attributes of a composite attribute.

In this example, we generate the relation EMPLOYEE, PROJECT, and DEPARTMENT.

The foreign key and relationship attributes are not protected yet; they will be inserted during subsequent steps. These sometimes are known as ‘Entity Relations.’

Step2

Create a relation R, and contain all simple attributes of W as attributes of R, for each weak entity type W in the entity-relationship schema with owner entity type E. Also, provide as foreign key attributes of R in the primary key attribute (s) of the relation (s) that correlates to the owner entity type (s); the primary key of R is the consolidation of the partial key of the weak entity type W if any and the primary key (s) of the owner (s).

In this example, contain the primary key of the EMPLOYEE relation, which correlates to the owner entity type-as a foreign key attribute of DEPENDENT.

Step3

Identify the relation S and T that corresponds to the entity type participating in R, for every binary relationship one to one relation type R in the entity-relationship schema. It is always good to select an entity type with total participation in R in the appearance of S. Also, to contain all the simple attributes (or simple elements of composite attributes) of the one to one relationship type R as an attribute of S.

An alternative mapping of one to one relationship type is feasible by combining the relationship and the two entity types and into a single location. This is particularly suitable when the entity types do not participate in any other relationship type and when both participations are TOTAL.

Step4

Recognize the association that define the participating entity type at the N-side of the relationship technique for every regular (strong) binary association one to many relationship type R. It contains any simple attributes (simple elements of composite attributes) of the one to many relationship types as attributes of S.

Step5

Create a new relation S to define R for each binary relation many to many relationship type R. It contains any simple attributes of the many relationship types (simple elements of composite attributes) as attributes of S and also contains as a foreign key attribute in S the primary keys of the relation that defines the participating entity type and there combination will structure the primary keys of S.

Step6

Create a new relation R that contains an attribute correlating to A plus the primary key attribute K (as a foreign key in R) of the relationships that describe the entity type or relationship type, which has A as an attribute. For each multivalued attribute A, and if the multivalued attribute is complex, we contain its easy elements. The primary key of R is the mixture of A and K.

Step7

Create a new relation S to symbolize R, for each n-ary relationship type R n>2. It contains any simple attributes of the n-ary relationship type (simple elements of composite attributes) as attributes of S and also contains as a foreign key attribute in S the primary keys of the relation that define the participating entity types. The primary key of S is generally a combination of all the foreign keys that define the relations describing the participating entity types.

Apply now for Advanced DBMS Course