Convert ERD to database schema

Convert the Entity Relationship Diagram to Relational Database Schema, How to convert ERD to conceptual schema, Reduce ER diagram to relational schema

Reduction of ERD to Relational Schema - Solved Exercise

Question:

Reduce the following ER diagram to relational database schema

Solution:

Notations used in the ERD for relationships

Step 1: First let us reduce the strong entity sets into schema. We have the strong entity sets LOT, RAW_MATERIALS and PRODUCTION_UNITS. They can be converted into schemas as follows;

Rules to convert: Strong Entity Set Name of the schema = Name of the strong entity set           Attributes of the schema = attributes of the strong entity set           Underline the primary key attribute in the resultant schema

• Lot (LotNumber, CreateDate, Cos-of-Materials)

• Raw_Materials (material-ID, UnitCost, type)

• Production_Units (serial#, exactWeight, productType, productDesc, qualityTest)

Step 2: To decide on whether relationships to be converted into separate tables or not. We have two relationship sets in our problem.

(1) Includes is a one-to-many relationship from entity set Lot to Production_Units.

Rules to convert: One-to-Many relationship set Insert the primary key attribute of one side entity set as a foreign key in the many side entity set.

LotNumber attribute to be inserted into Prodution_Units schema as a foreign key. So, the schema Production_Units is updated as follows;

• Production_Units (serial#, exactWeight, productType, productDesc, qualityTest, LotNumber)

(2) Created_From is a many-to-many relationship set between Lot and Raw_Materials.

Rules to convert: Many-to-Many relationship set Create a separate table for many-to-many relationship set with primary keys of participating entity sets as attributes. All the primary key attributes of participating entity sets will form a composite key in the resultant relation.

Hence, create a separate schema for Created_From as follows;

• Created_From(LotNumber, material-ID)

Step 3: To reduce Descriptive attributes into schema.

Rules to convert: Descriptive attribute Descriptive attribute is an attribute attached to the relationship directly. While reducing the relationship into schema, insert the descriptive attributes with that schema.

Hence, Created_From is updated as follows;

• Created_From(LotNumber, material-ID, Units)

Resultant schema:

After reduction, the schema looks like the following; [primary keys are underlined, foreign keys are connected with the concerned primary keys with headed arrows]

***********

Click here to go to ERD solved exercises page

Click here to go to DBMS solved exercises page

Go to Normalization - Solved Exercises page

Reduce ERD to relation schema exercise

Convert entity relationship diagram into relation schema

Solved problems on ERD in DBMS

How to convert entity relationship diagram into relational schema?

Solved Entity relationship exercises in DBMS

DBMS solved exercises in entity relationship model