Showing posts with label ER Model. Show all posts
Showing posts with label ER Model. Show all posts

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)


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








Popular Posts