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

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)

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

1. This is the good example!!!!!!!!!!!!!!

2. good work