The rules or guidelines required to design the relational database schema attempt to provide a step-by-step procedure that transforms ER design into Relational Data Model design to constitute the desired database.
The following specific steps are required to cause its transformation into relational data model.
(i) Create a Relation for Every Strong Entity For each strong entity type (which has primary key) in ER schema, a separate relation that includes all the simple attributes of that entity is created. Either choose one of the key attributes of such an entity as the primary key for this relation, or choose a set of simple attributes that uniquely identify this entity as the primary key of the relation so created e.g., employee entity is strong because it finds it primary key in Empid which is one of its unique attribute. Therefore, a separate relation for Employee has been created as shown below.
Employee (Empid, Fname, Minit, Lname, Address, Phone No, SuperId)
Similarly, separate relations need to be created for the following strong entities whose primary Key attribute have been underlined.
Accounts (Code, Name)
Vouchers (VNo, vDate, amount, narration)
Accounts Tye, Type (CatId Category)
(ii) Create a Separate Relation for Each Weak Entity Type Every weak entity has an owner entity and identifying relationship through which such weak entity type is identified. For every weak entity type, a separate relation is created by including its attributes. The primary key of this new relation is the combination of its unique attribtute(s) for a particular tuple of the owner relation along with primary key attribute of such owner relation.
Furthermore, the primary key of owner entity is included as foreign key in such a relation key of owner entity and the partial key of weak entity e.g., Support Entity, with Vouchers as its owner Entity, does not have a primary key of its own. It has partial key which is the Sno assigned to each document. Therefore, the Primary key of Vouchers, Vno along with Sno is designed as composite key for support entity and the relation so formed is shown below as:
Support (vNo, Sno, dName, sDate)
(iii) Identify Entity Types Participating in Binary 1: N Relationship Type Identify the first relation in n-side of relationship and second on 1-side of such relationship. The primary key of second relation should be included in first relation as its foreign key e.g., An employee can authorize a number of vouchers. It implies that Vouchers entity participates in same relationship on 1-side.Therefore, the vouchers relation as already formed above in step 1 must also include as foreign key the primary key of Employees which EmpId.
(iv) Identify Entity Types Participating in Binary M: N Relationship Type For each binary M:N relationship type, create a new relation to represent such relationship. This new relation should include as foreign keys, the primary keys of the relations that represent the participating entity types.