Question

Discuss the steps you would take to transform an ER Model into various relations of Relational Data Model. Give suitable examples.

Answer 1

The requisite to transform on ER model into various relations of the Relational Data Model is to have an ER design. Let following be the example of our ER model that we need to transform into Relational Data Model.

In the above diagram, the rectangular boxes are used to represent entities. The diamond shaped boxes are used to describe the relation type between the two entities. The double lined rectangular boxes are used to show the weak entities. The double-lined diamond shaped box is used to describe the identifying relationship type. In ovals, attribute names are enclosed and attached to their respective entity type through the straight lines.

The following section describes the procedure to transform ER model into Relational Data Model.

1. Creation of Relation for Every Strong Entity

A separate relation that includes all the simple attributes is to be created for each strong entity type in the ER schema. A primary key out of these attributes is chosen arbitrarily for easy and unique identification of the strong entity. For example, in the above ER design, Employee, Vouchers, Accounts and Account Type are the strong entities as they have primary key which is one of their unique attributes. These attributes are shown in the ovals and are attached to their respective entity type by the straight lines.

Separate relation is created for each strong entity. This is represented as:

Employee (EmpId, Name, Type)

Vouchers (VNo, SNo. Narration)

Accounts (Type, Name, Code)

Accounts Type (CatId, Category)

2. Creation of Relation for each Weak Entity Type

Weak entities do not have their own identities and are identified through the identifying relationship. So, we can say that every weak entity has its own entity that helps in its identification. A separate relation that includes the attributes is to be created for each weak entity. The primary key of this new relation is the combination of its unique attributes, along with the primary key attribute of the owner relation. For example, Support entity is the weak entity as it does have its own primary key; Voucher is the owner entity of Support entity.

Support entity has a partial key which is SNo Assigned to each document. Therefore, VNo, the primary key of the vouchers along with SNo is designed as composite key for the Support entity. The relation so formed can be represented as:

Support (VNo, SNo, dName sDate)

3. Identity Entity Types Participating in Binary 1 : N Relationship Type

First of all, the first relation on n-side of the relationship and second on the 1-side of such relationships is to be identified. The primary key of the second relation should be included in the first relation as its foreign key. For example, in the example, an employee can authorize a number of vouchers. It means that Voucher entity participates in AuthBy relationship on the n-side, on the other hand, the Employee entity participates in the same relationship on the 1-side. Similarly, Prep. by relationship between employees and vouchers participates in binary 1 : N relationship.

4. Identify entity types participating in binary M : N relationship type:

A new relation is to be created for each binary M : N relationship type. This new relation should include foreign keys to represent primary key of the new relation. For example, consider two entities namely, Voucher entity and Account entity. These entities have two relation debit and credit. The debit relationship has cordially ratio of N : 1 i.e. many debit voucher relates to one accounts. On the other hand, the credit relationship has cardinality ratio of M : N. For example, many credit vouchers are related to many accounts.

So, based on the diagram, the following two relations can be formed as:

(i) Credit (VNo, SNo, Code, Amount, Narration)

(ii) Debit (VNo. SNo, Code, Amount, Narration)

In the Credit relation, Credit Code is included as foreign key to represent the primary key of the Accounts relation. VNo is included as foreign key to represent the primary key of the Vouchers relation. Both VNo. and the Code together constitute the primary key of the new relation Credit.

At the end, the following relations have been formed to formulate the relational data model for the above example of ER design.

Employee (EmpId, Name, Type)

Vouchers (VNo, SNo, Naration)

Accounts (Type, Name, Code)

Accounts Type (CatId, Category)