Entity relationship diagram to relational schema - Exercise 7

Solved Exercise - Reducing Entity Relationship Diagram into Tables, Convert ER diagram to tables, relational schemas, ER model to relational model, Descriptive attributes into relational schema, Weak entity set into relational schema - How to?

Entity Relationship Diagram Exercise 7

Question:

Convert/reduce the ER Diagram given in figure 1 below;

Figure 1 - ER diagram with Weak Entity set

Solution:

Given in the figure;

Entity sets and relationship sets

Name	Entity set / Relationship set	Type
Manufacturer	Entity set	Strong entity set
Part	Entity set	Strong entity set
Order	Entity set	Weak entity set
Customer	Entity set	Strong entity set
Made-by	Relationship set	One-to-Many from Manufacturer to Part
Contains	Relationship set	Many-to-Many between Order and Part with descriptive attribute Quantity
Order	Weak Relationship set	One-to-Many from Customer to Order

Strong Entity set Manufacturer

Attributes	Attribute Type	Description
Name	Simple and Primary key	Manufacturer name – Primary key
Address	Composite	Manufacturer address with street and city as the component attributes

Strong Entity set Part

Attributes	Attribute Type	Description
Part_num	Simple and Primary key	Part number – primary key
Description	Simple	Part description

Strong Entity set Customer

Attributes	Attribute Type	Description
ID	Simple and Primary key	Customer ID – primary key
Name	Simple	Name of the customer

Weak Entity set Order

Attributes	Attribute Type	Description
Order_num	Simple and Discriminator	Order number. It is the discriminator (primary key of weak entity set)

Reduction into relational schema

I have explained the reduction of the given ER diagram in the table given below. Each component like Strong entity sets, relationship sets, composite attributes etc are converted into the appropriate relational schemas and the fourth column shows the changes in the schema at every stage. Final relational schema of an entity set is highlighted in green color and foreign keys in blue color.

ER Component	Type	Reduction Rule	Relational schema after reduction
Manufacturer	Strong Entity Set	Name of the entity set as name of the relation schema and attributes of entity set as attributes of relation schema	Manufacturer (name, address)
address	Composite attribute of Manufacturer	Include the component attributes to the relation schema, and remove the composite attributes	Manufacturer (name, street, city)
Part	Strong Entity Set	Refer above	Part (part_num, description)
made-by	One-to-many relationship	Include the primary key of one side as the foreign key of the other side	Hence, the many side relation schema Part becomes as follows; Part (part_num, description, name) Here, name is the foreign key and refers Manufacturer.
Customer	Strong entity set	Refer above.	Customer (ID, Name)
Order	Weak entity set	Relation schema for a weak entity set is created by including the primary key of strong entity set on which it depends.	Order depends on Customer. Hence, primary key of customer has to be included as the foreign key in Order as follow; Order (order_num, ID) Here, ID is foreign key and (order_num, ID) is the primary key.
Contains	Many-to-many relationship between Part and Order.	For a many-to-many relationship, the relationship will be converted as a table with the primary keys of all participating entity sets as attributes.	Contains (order_num, part_num)
Quantity	Descriptive attribute of the relationship set Contains	Descriptive attributes will become the part of the relationship table.	Hence, Contains become; Contains (order_num, part_num, Quantity)
Total participation between Order (Weak ES) and Contains (M-M relationship)	Total participation.	The primary key of other side table, and descriptive attributes (if any) of the relationship should be included as attributes of table which totally participates on the given relationship.	The participation of weak entity set Order in the relationship Contains is total. Hence, the primary key of Part and the descriptive attribute of Contains have to be added with Order schema as follows; Order (order_num, ID, part_num, Quantity)
order	One-to-many weak relationship	Need not be converted into table	-

Total participation – “The participation of an entity set E in a relationship set R is said to be total if every entity in E participates in at least one relationship in R”. – Database System Concepts by Silberschatz et al.

Final relational schema after reduction is as follows;

Manufacturer (name, street, city)

Part (part_num, description, name)

Customer (ID, Name)

Order (order_num, ID, part_num, Quantity)

*******************

Go to ER model - Solved Exercises page