Converting ER Diagrams to Tables in DBMS

Converting ER Diagrams to Tables

Following rules are used for converting an ER diagram into tables.

 Rule-01: Convert strong entity into a table

• A strong entity set with only simple attributes will directly convert into one table.
• All Attributes of the entity set will be the attributes of the table.
• The primary key of the entity set will be the key attribute of the table.

 

Rule-02: Convert Composite Attributes of a strong entity

All composite attributes convert as simple attributes in the table.

                

Schema: Student ( Roll_no, First_name, Last_name , House_no, Street , City )

 

Rule-03: Convert multivalued attributes of strong entity into table.

• ·         Split the relations of multivalued strong entity set into two tables.
• ·         One table define all the simple attributes with the primary key and other table define multi valued attributes with primary key.

Example

Rule-04: Convert Relationship Set into a Table

• A relationship set will convert into one table.
• Relationship table have primary keys of connected entities and own attributes.
• Set of non-descriptive attributes will be the primary key.

Example

   

 

Schema: Works in ( Emp_no , Dept_id , since )

 Rule-05: Convert Binary Relationships (Cardinality Ratios) into the table.

The following four cases are possible

Case-01: Binary relationship with cardinality ratio m:n

Case-02: Binary relationship with cardinality ratio 1:n

Case-03: Binary relationship with cardinality ratio m:1

Case-04: Binary relationship with cardinality ratio 1:1

 

Case-01: For Binary Relationship With Cardinality Ratio m:n

Here, three tables will be required[1]1. A ( a1 , a2 ) 2. R ( a1 , b1) 3. B ( b1 , b2 )

 Case-02: For Binary Relationship With Cardinality Ratio 1:n

Here, two tables will be required A ( a1, a2 ) 2. BR ( a1 , b1 , b2 )

 NOTE- The combined table will be drawn for entity set B and relationship set R.

 

Case-03: For Binary Relationship With Cardinality Ratio m:1

Here, two tables will be required AR ( a1 , a2 , b1 ) 2. B ( b1 , b2 )

 NOTE-  Combined table will be drawn for the entity set A and relationship set R.

 

Case-04: For Binary Relationship With Cardinality Ratio 1:1

Here, two tables will be required. Either combine ‘R’ with ‘A’ or ‘B’

Way-01: AR ( a1 , a2 , b1 ) 2. B ( b1, b2 )

Way-02: A ( a1 , a2 ) 2. BR ( a1 , b1 , b2 )

 Rule-06: Convert Binary Relationship With Both Cardinality Constraints and Participation Constraints into the table.

The total participation constraint acquires NOT NULL constraint foreign key.

Case-01: In Binary Relationship (Cardinality Constraint and Total Participation Constraint) From One Side (cardinality ratio = 1 : n)

 

We will combine entity set B and relationship set R.

Then, two tables will be required 1. A ( a1 , a2 ). 2. BR ( a1, b1 , b2 )

 Case-02: In Binary Relationship (Cardinality Constraint and Total Participation Constraint) From Both Sides

If a key constraint from both the sides of an entity is set with total participation, then the binary relationship is represented only single table.

 

Here, Only one table is required ARB ( a1, a2, b1 , b2 )

 Rule-07: Convert Binary Relationship with weak Entity Set into the table

Weak entity sets are always used in association with identifying relationship and total participation constraints.

Here, two tables will be required 1. A ( a1 , a2 ) 2. BR ( a1 , b1 , b2 ).

============================================================