ITD 256 Advanced Database Management

ERD: Physical Entity Relationship Diagram
• Choose one of the below scenarios and Create the physical level ERD. (You may choose the same or different
scenario to the one you worked on in Exercise 1). You must create the ERD using Microsoft Visio or other
diagram drawing software as indicated by your professor. Make sure to submit a PDF version of your diagram
along with the actual diagram file (in case your professor can’t open your Visio file, they can use the PDF file
to grade). Make sure to submit the diagram by the due date as specified by your professor.
Scenario 1:
This is a simple list table of a company trying to keep track of parts that they sell and orders that came in purchasing
those parts (in other words, not a database but a flat one table file). You will design a database for this company so that
they won’t be relying on a simple 1 table list system to keep track of their data. Looking at the table below, identify the
separate entities and attributes and design a physical level Entity Relationship Diagram for this company.
OrderNum OrderDate PartNum Description NumOrdered QuotedPrice
21608 10/20/2010 AT94 Iron 11 $21.95
21610 10/20/2010 DR93 Gas Range 1 $495.00
21610 10/20/2010 DW11 Washer 1 $399.99
21613 10/21/2010 KL62 Dryer 4 $329.95
21614 10/21/2010 KT03 Dishwasher 2 $595.00
21617 10/23/2010 BV06 Home Gym 2 $794.95
21617 10/23/2010 CD52 Microwave Oven 4 $150.00
21619 10/23/2010 DR93 Gas Range 1 $495.00
21523 10/23/2010 KV29 Treadmill 2 $1290.00
OR
Scenario 2:
This is a simple list table of a company trying to keep track of employee assignment to company projects (in other
words, not a database but a flat one table file). You will design a database for this company so that they won’t be
relying on a simple 1 table list system to keep track of their data. Looking at the table below, identify the separate
entities and attributes and design a physical level Entity Relationship Diagram for this company.
Project
Code
Project Name Project
Manager
Project
Budget
Employee
No.
Employee
Name
Department
No.
Department
Name
Hourly
Rate
PC010 Reservation
System
Mr. Ajay 120500 S100 Mohan D03 Database 21.00
PC010 Reservation
System
Mr. Ajay 120500 S101 Vipul D02 Testing 16.50
PC010 Reservation
System
Mr. Ajay 120500 S102 Riyaz D01 IT 22.00
PC011 HR System Mrs. Charu 500500 S103 Pavan D03 Database 18.50
PC011 HR System Mrs. Charu 500500 S104 Jitendra D02 Testing 17.00
PC011 HR System Mrs. Charu 500500 S315 Pooja D01 IT 23.50
PC012 Attendance
System
Mr. Rajesh 710700 S137 Rahul D03 Database 21.50
PC012 Attendance
System
Mr. Rajesh 710700 S218 Avneesh D02 Testing 15.50
PC012 Attendance
System
Mr. Rajesh 710700 S109 Vikas D01 IT 20.50

Northern Virginia Community College
Annandale Campus

ITD 256 Advanced Database Management
2 | P a g e
Grading Rubric:
Work is Complete 10 Points
Physical ERD is in correct format/uses correct
symbols/notations
15 Points
Correct Attribute Data Types/Constraints Identified 15 Points
Correct Relationship between Entities Identified 5 Points
Cardinality is correctly identified between entities 5 Points
Total: 50 Points

 

 

ITD256_Online_Exercise2(2) (2)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.