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
Entity
Relationship Diagram Exercise 6
Question:
Convert/reduce the ER Diagram given
in figure 1 below;
Figure 1 - ER diagram with descriptive attributes |
Solution:
Given in the figure;
Entity
sets, relationship sets, and other components as given in the diagram
Name
|
Entity set /
Relationship set
|
Type
|
Customer
|
Entity set
|
Strong entity set
|
Item
|
Entity set
|
Strong entity set
|
Order
|
Entity set
|
Strong entity set
|
Supplier
|
Entity set
|
Strong entity set
|
Buys
|
Relationship set
|
One-to-Many from Customer
to Order
|
Contains
|
Relationship set
|
Many-to-Many
between Order and Item with
descriptive
attribute Quantity
|
Supplies
|
Relationship set
|
Many-to-Many
between Supplier and Item descriptive attribute
unitCost
|
Entity
set Customer
Attributes
|
Attribute Type
|
Description
|
custID
|
Simple and
Primary key
|
Customer ID
|
custName
|
Composite
|
Name of the
customer with lastName and firstName as the component attributes
|
custAdd
|
Composite
|
Customer address
with street, city, state, and zip as components
|
creditLimit
|
Simple
|
Credit limit
value
|
Phone
|
Composite
|
Phone number with
areaCode and phone number as components
|
Entity
set Item
Attributes
|
Attribute Type
|
Description
|
ItemNo
|
Simple and
Primary key
|
Item number
|
ItemName
|
Simple
|
Name of the item
|
unitPrice
|
Simple
|
Price of single
unit of the item
|
qtyOnHand
|
Simple
|
Quantities of
item available
|
reorderPoint
|
Simple
|
Minimum quantity
value of an item where we need to go for purchasing more stock.
|
Entity
set Order
Attributes
|
Attribute Type
|
Description
|
orderNo
|
Simple and
Primary key
|
Order number
|
Date
|
Simple
|
Order date
|
totalAmount
|
Simple
|
Total amount of a
single order
|
Tax
|
Simple
|
Tax for the
current order
|
deliverToAddress
|
Composite
|
Delivery address
for any order with street, city, state and zip as the components
|
deliverToPhone
|
Composite
|
Delivery phone
number with areaCode and the number as the components
|
Entity
set Supplier
Attributes
|
Attribute Type
|
Description
|
supplierNo
|
Simple and Primary
key
|
Supplier number
|
supName
|
Simple
|
Name of the supplier
|
supAdd
|
Composite
|
Address of the
supplier with street, city, state, zip, and country as components
|
contactName
|
Simple
|
Contact person of
a supplier
|
Phone
|
Composite
|
Phone number of
the supplier with countryCode, areaCode, and number as the component
attributes.
|
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 colour.
ER Component
|
Type
|
Reduction Rule
|
Relational schema after reduction
|
Customer
|
Strong Entity Set
|
Name of the entity set as name of the relation schema
and attributes of entity set as attributes of relation schema
|
Customer (custId,
custName, custAdd, creditLimit, Phone)
|
custName, custAdd, phone
|
Composite
attributes of Customer
|
Include the component attributes to the relation
schema, and remove the composite attributes
|
Customer
(custID, lastName, firstName, street, city, state, zip, creditLimit,
areaCode, number)
|
Order
|
Strong Entity Set
|
Refer above
|
Order (orderNo,
date, totalAmount, tax, deliverToAddress, deliverToPhone)
|
deliverToAddress, deliverToPhone
|
Composite
attributes of Order
|
Refer above
|
Order (orderNo,
date, totalAmount, tax, street, city, state, zip, areaCode, number)
|
Item
|
Strong Entity Set
|
Refer above
|
Item
(ItemNo, ItemName, unitPrice, qtyOnHand, reorderPoint)
|
Supplier
|
Strong Entity Set
|
Refer above
|
Supplier (supplierNo,
supName, supAdd, contactName, phone)
|
supAdd, phone
|
Composite
attributes of Supplier
|
Refer above
|
Supplier
(supplierNo, supName, street, city, state, zip, country, contactName, countryCode,
areaCode, number)
|
Buys
|
One-to-many
relationship
|
Include the primary key of one side as the foreign
key of the other side
|
Hence, Order
becomes as follows;
Order
(orderNo, date, totalAmount, tax, street, city, state, zip, areaCode,
number, custId)
|
Contains
|
Many-to-many
relationship
|
Create a separate table for the many-to-many
relationship. Primary keys of participating entity sets are the attributes.
|
Contains (orderNo,
ItemNo)
|
Quantity
|
Descriptive
attribute of the relationship set Contains
|
Descriptive attributes will become the part of the
relationship table.
|
Hence, Contains
become;
Contains
(orderNo, ItemNo, Quantity)
|
Supplies
|
Many-to-many
relationship
|
Refer above
|
Supplies (supplierNo,
ItemNo)
|
unitCost
|
Descriptive
attribute of the relationship set Supplies
|
Refer above
|
Hence, Supplies
become;
Supplies
(supplierNo, ItemNo, unitCost)
|
Final relational
schema after reduction is as follows; (keys are underlined)
- Customer (custID, lastName, firstName, street, city, state, zip, creditLimit, areaCode, number)
- Order (orderNo, date, totalAmount, tax, street, city, state, zip, areaCode, number, custId)
- Supplier (supplierNo, supName, street, city, state, zip, country, contactName, countryCode, areaCode, number)
- Item (ItemNo, ItemName, unitPrice, qtyOnHand, reorderPoint)
- Contains (orderNo, ItemNo, Quantity)
- Supplies (supplierNo, ItemNo, unitCost)
************************
Go to ER model - Solved Exercises page
No comments:
Post a Comment