## TOPICS (Click to Navigate)

Please visit, subscribe and share 10 Minutes Lectures in Computer Science

## 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]

***********

Go to Normalization - Solved Exercises page