ER Diagrams to Tables-

 

Before you go through this article, make sure that you have gone through the previous article on ER Diagrams to Tables.

 

After designing an ER Diagram,

• ER diagram is converted into the tables in relational model.
• This is because relational models can be easily implemented by RDBMS like MySQL , Oracle etc.
• The rules used for converting an ER diagram into the tables are already discussed.

 

In this article, we will discuss practice problems based on converting ER Diagrams to Tables.

 

PRACTICE PROBLEMS BASED ON CONVERTING ER DIAGRAM TO TABLES-

 

Problem-01:

 

Find the minimum number of tables required for the following ER diagram in relational model-

 

 

Solution-

 

Applying the rules, minimum 3 tables will be required-

• MR1 (M1 , M2 , M3 , P1)
• P (P1 , P2)
• NR2 (P1 , N1 , N2)

 

Problem-02:

 

Find the minimum number of tables required to represent the given ER diagram in relational model-

 

 

Solution-

 

Applying the rules, minimum 4 tables will be required-

• AR1R2 (a1 , a2 , b1 , c1)
• B (b1 , b2)
• C (c1 , c2)
• R3 (b1 , c1)

 

Problem-03:

 

Find the minimum number of tables required to represent the given ER diagram in relational model-

 

 

Solution-

 

Applying the rules, minimum 5 tables will be required-

• BR1R4R5 (b1 , b2 , a1 , c1 , d1)
• A (a1 , a2)
• R2 (a1 , c1)
• CR3 (c1 , c2 , d1)
• D (d1 , d2)

 

Problem-04:

 

Find the minimum number of tables required to represent the given ER diagram in relational model-

 

 

Solution-

 

Applying the rules, minimum 3 tables will be required-

• E1 (a1 , a2)
• E2R1R2 (b1 , b2 , a1 , c1 , b3)
• E3 (c1 , c2)

 

Problem-05:

 

Find the minimum number of tables required to represent the given ER diagram in relational model-

 

 

Solution-

 

Applying the rules that we have learnt, minimum 6 tables will be required-

• Account (Ac_no , Balance , b_name)
• Branch (b_name , b_city , Assets)
• Loan (L_no , Amt , b_name)
• Borrower (C_name , L_no)
• Customer (C_name , C_street , C_city)
• Depositor (C_name , Ac_no)

 

Next Article- Constraints in DBMS

 

Get more notes and other study material of Database Management System (DBMS).

Watch video lectures by visiting our YouTube channel LearnVidFun.

Converting ER Diagrams to Tables-

 

After designing an ER Diagram,

• ER diagram is converted into the tables in relational model.
• This is because relational models can be easily implemented by RDBMS like MySQL , Oracle etc.

 

Following rules are used for converting an ER diagram into the tables-

 

Rule-01: For Strong Entity Set With Only Simple Attributes-

 

A strong entity set with only simple attributes will require only one table in relational model.

• Attributes of the table will be the attributes of the entity set.
• The primary key of the table will be the key attribute of the entity set.

 

Example-

 

 

Roll_no	Name	Sex

Schema : Student ( Roll_no , Name , Sex )

 

Also Read- Entity Sets in DBMS

 

Rule-02: For Strong Entity Set With Composite Attributes-

 

• A strong entity set with any number of composite attributes will require only one table in relational model.
• While conversion, simple attributes of the composite attributes are taken into account and not the composite attribute itself.

 

Example-

 

 

Roll_no	First_name	Last_name	House_no	Street	City

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

 

Also Read- Types of Attributes in DBMS

 

Rule-03: For Strong Entity Set With Multi Valued Attributes-

 

A strong entity set with any number of multi valued attributes will require two tables in relational model.

• One table will contain all the simple attributes with the primary key.
• Other table will contain the primary key and all the multi valued attributes.

 

Example-

 

 

Roll_no	City

 

Roll_no	Mobile_no

 

Rule-04: Translating Relationship Set into a Table-

 

A relationship set will require one table in the relational model.

Attributes of the table are-

• Primary key attributes of the participating entity sets
• Its own descriptive attributes if any.

Set of non-descriptive attributes will be the primary key.

 

Example-

 

 

Emp_no	Dept_id	since

Schema : Works in ( Emp_no , Dept_id , since )

 

NOTE-

 

If we consider the overall ER diagram, three tables will be required in relational model-

• One table for the entity set “Employee”
• One table for the entity set “Department”
• One table for the relationship set “Works in”

 

Rule-05: For Binary Relationships With Cardinality Ratios-

 

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

 

Also read- Cardinality Ratios in DBMS

 

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

 

 

Here, three tables will be required-

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-

1. A ( a1 , a2 )
2. BR ( a1 , b1 , b2 )

 

NOTE- Here, combined table will be drawn for the entity set B and relationship set R.

 

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

 

 

Here, two tables will be required-

1. AR ( a1 , a2 , b1 )
2. B ( b1 , b2 )

 

NOTE- Here, 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:

1. AR ( a1 , a2 , b1 )
2. B ( b1 , b2 )

 

Way-02:

1. A ( a1 , a2 )
2. BR ( a1 , b1 , b2 )

 

Thumb Rules to Remember   While determining the minimum number of tables required for binary relationships with given cardinality ratios, following thumb rules must be kept in mind- For binary relationship with cardinality ration m : n , separate and individual tables will be drawn for each entity set and relationship. For binary relationship with cardinality ratio either m : 1 or 1 : n , always remember “many side will consume the relationship” i.e. a combined table will be drawn for many side entity set and relationship set. For binary relationship with cardinality ratio 1 : 1 , two tables will be required. You can combine the relationship set with any one of the entity sets.

 

Rule-06: For Binary Relationship With Both Cardinality Constraints and Participation Constraints-

 

• Cardinality constraints will be implemented as discussed in Rule-05.
• Because of the total participation constraint, foreign key acquires NOT NULL constraint i.e. now foreign key can not be null.

 

Case-01: For Binary Relationship With Cardinality Constraint and Total Participation Constraint From One Side-

 

 

Because cardinality ratio = 1 : n , so we will combine the entity set B and relationship set R.

Then, two tables will be required-

1. A ( a1 , a2 )
2. BR ( a1 , b1 , b2 )

Because of total participation, foreign key a1 has acquired NOT NULL constraint, so it can’t be null now.

 

Case-02: For Binary Relationship With Cardinality Constraint and Total Participation Constraint From Both Sides-

 

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

 

 

Here, Only one table is required.

• ARB ( a1 , a2 , b1 , b2 )

 

Rule-07: For Binary Relationship With Weak Entity Set-

 

Weak entity set always appears in association with identifying relationship with total participation constraint.

 

 

Here, two tables will be required-

1. A ( a1 , a2 )
2. BR ( a1 , b1 , b2 )

 

Next Article- Practice Problems On Converting ER Diagrams to Tables

 

Get more notes and other study material of Database Management System (DBMS).

Watch video lectures by visiting our YouTube channel LearnVidFun.