Entity relationship diagram to relational schema - Exercise 6

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