Tuesday, May 3, 2016

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)




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








8 comments:

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

    ReplyDelete
  2. What about CONTAINS Relationship

    ReplyDelete
  3. Why don't we make another table for contain also just like any other many to many relationship , can any one explain ?

    ReplyDelete
    Replies
    1. Contains(order_num, part_num, quantity). As order is a weak entity totally participated on contains relationship we included part_num with order table. Hence, it would be redundant. So we removed contains and instead retained order with all attributes of contains.

      Delete
  4. why is ID a parts of the primary Keys in the order schema. shouldn't it be a foreign key?

    ReplyDelete
    Replies
    1. ID is both foreign key (as it is a key of strong entity set customer) and part of primary key (because ORDER is weak and depends on CUSTOMER, refer figure, 1-to-n relationship). As per the rule, the key for a weak entity set = Disciminator + Primary key of identifying strong entity set.

      Delete

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery