The rules or guidelines required to design the relational database scheme 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.
1. 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 bn 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,
eg. employee entity is strong because its finds its primary key in Empld which is one of its unique attribute. Therefore, a separate relation for employee has been created as shown below
Employee (Empld, Fname, Minit, Lname, Address, PhoneNo, Superld). Similarly, separate relations need be created for the following strong entities whose Primary key attribute have been underlined.
- Accounts (Code, Name, Type)
- Vouchers (VNo, vDate, amount, narration)
- Accounts Type (Catld, Category)
2. Create a separate relation for each weak entity type:
Every weak entity has an owner entity and an 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 attribute(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 foriegn key in such a relation key of owner entity and the partial key of weak entity, eg. 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)
3. Identify Entity Types Participating in Binary 1: N Relationship Type:
Identify the first relation on 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. eg. An employee can authorize a number of vouchers. It implies that Vouchers entity participates in Auth.
By relationship on n-side while Employees 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 is Empld.
4. 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.
