Advanced Database Management System - Tutorials and Notes: Entity relationship diagram to relational schema - Exercise 6

Search Engine

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

Sunday, 1 May 2016

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)


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













No comments:

Post a comment

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