USA: +1(669)289-8683 
Sign in
Exercise 1
Choose one of the scenarios listed in the attached document and Create the logical level ERD. 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).
Exercise 2
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).
Exercise 3
Choose one of the below scenarios (can be the same or different scenario then the one you chose for previous exercises), treat the table in the scenario you chose as 1NF (first normal form) and use the normalization technique to produce the 3NF (third normal form) of the data. You may submit a Microsoft Word document as the deliverable for this assignment.
Exercise 4
Choose a scenario as listed in Practice Exercise 1 – 3. (You may choose a different scenario then the one you chose before or choose the same scenario from previous exercises – I recommend choosing the same scenario that you created the ERD for).
Northern Virginia Community College
Annandale Campus
ITD 256 Advanced Database Management
Practice Exercise 1
Individual Assignment
ERD: Logical Entity Relationship Diagram
•
Choose one of the below scenarios and Create the logical level ERD. 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 logical level Entity Relationship Diagram for this company.
OrderNum
21608
21610
21610
21613
21614
21617
21617
21619
21523
OrderDate
10/20/2010
10/20/2010
10/20/2010
10/21/2010
10/21/2010
10/23/2010
10/23/2010
10/23/2010
10/23/2010
PartNum
AT94
DR93
DW11
KL62
KT03
BV06
CD52
DR93
KV29
Description
Iron
Gas Range
Washer
Dryer
Dishwasher
Home Gym
Microwave Oven
Gas Range
Treadmill
NumOrdered
11
1
1
4
2
2
4
1
2
QuotedPrice
$21.95
$495.00
$399.99
$329.95
$595.00
$794.95
$150.00
$495.00
$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 logical level Entity Relationship Diagram for this company.
Project
Code
PC010
Project Name
PC011
Reservation
System
Reservation
System
Reservation
System
HR System
PC011
HR System
PC011
HR System
PC012
Attendance
System
Attendance
System
Attendance
System
PC010
PC010
PC012
PC012
Project
Project
Manager
Budget
Mr. Ajay 120500
Employee Employee Department Department Hourly
No.
Name
No.
Name
Rate
S100
Mohan
D03
Database
21.00
Mr. Ajay
120500
S101
Vipul
D02
Testing
16.50
Mr. Ajay
120500
S102
Riyaz
D01
IT
22.00
Mrs.
Charu
Mrs.
Charu
Mrs.
Charu
Mr.
Rajesh
Mr.
Rajesh
Mr.
Rajesh
500500
S103
Pavan
D03
Database
18.50
500500
S104
Jitendra
D02
Testing
17.00
500500
S315
Pooja
D01
IT
23.50
710700
S137
Rahul
D03
Database
21.50
710700
S218
Avneesh
D02
Testing
15.50
710700
S109
Vikas
D01
IT
20.50
1|Page
Northern Virginia Community College
Annandale Campus
ITD 256 Advanced Database Management
Grading Rubric:
Work is Complete
Logical ERD is in correct format/uses correct
symbols/notations
Correct Entities and Attributes Identified
Correct Relationship between Entities Identified
Cardinality is correctly identified between entities
Total:
10 Points
10 Points
10 Points
10 Points
10 Points
50 Points
2|Page
Northern Virginia Community College
Annandale Campus
ITD 256 Advanced Database Management
Practice Exercise 2
Individual Assignment
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
21608
21610
21610
21613
21614
21617
21617
21619
21523
OrderDate
10/20/2010
10/20/2010
10/20/2010
10/21/2010
10/21/2010
10/23/2010
10/23/2010
10/23/2010
10/23/2010
PartNum
AT94
DR93
DW11
KL62
KT03
BV06
CD52
DR93
KV29
Description
Iron
Gas Range
Washer
Dryer
Dishwasher
Home Gym
Microwave Oven
Gas Range
Treadmill
NumOrdered
11
1
1
4
2
2
4
1
2
QuotedPrice
$21.95
$495.00
$399.99
$329.95
$595.00
$794.95
$150.00
$495.00
$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 Project Name
Code
PC010 Reservation
System
PC010 Reservation
System
PC010 Reservation
System
PC011 HR System
PC011 HR System
PC011 HR System
PC012 Attendance
System
PC012 Attendance
System
PC012 Attendance
System
Project
Manager
Mr. Ajay
Project Employee Employee Department Department Hourly
Budget
No.
Name
No.
Name
Rate
120500 S100
Mohan
D03
Database
21.00
Mr. Ajay
120500 S101
Vipul
D02
Testing
16.50
Mr. Ajay
120500 S102
Riyaz
D01
IT
22.00
Mrs. Charu
Mrs. Charu
Mrs. Charu
Mr. Rajesh
500500
500500
500500
710700
S103
S104
S315
S137
Pavan
Jitendra
Pooja
Rahul
D03
D02
D01
D03
Database
Testing
IT
Database
18.50
17.00
23.50
21.50
Mr. Rajesh
710700 S218
Avneesh
D02
Testing
15.50
Mr. Rajesh
710700 S109
Vikas
D01
IT
20.50
1|Page
Northern Virginia Community College
Annandale Campus
ITD 256 Advanced Database Management
Grading Rubric:
Work is Complete
Physical ERD is in correct format/uses correct
symbols/notations
Correct Attribute Data Types/Constraints Identified
Correct Relationship between Entities Identified
Cardinality is correctly identified between entities
Total:
10 Points
15 Points
15 Points
5 Points
5 Points
50 Points
2|Page
Northern Virginia Community College
Annandale Campus
ITD 256 Advanced Database Management
Practice Exercise 3
Normalization
•
Choose one of the below scenarios (can be the same or different scenario then the one you chose for
previous exercises), treat the table in the scenario you chose as 1NF (first normal form) and use the
normalization technique to produce the 3NF (third normal form) of the data. You may submit a Microsoft
Word document as the deliverable for this assignment. Make sure to submit the work by the due date as
indicated 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, produce the
3NF of the data.
OrderNum
21608
21610
21610
21613
21614
21617
21617
21619
21523
OrderDate
10/20/2010
10/20/2010
10/20/2010
10/21/2010
10/21/2010
10/23/2010
10/23/2010
10/23/2010
10/23/2010
PartNum
AT94
DR93
DW11
KL62
KT03
BV06
CD52
DR93
KV29
Description
Iron
Gas Range
Washer
Dryer
Dishwasher
Home Gym
Microwave Oven
Gas Range
Treadmill
NumOrdered
11
1
1
4
2
2
4
1
2
QuotedPrice
$21.95
$495.00
$399.99
$329.95
$595.00
$794.95
$150.00
$495.00
$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, produce the 3NF of the
data.
Project
Code
PC010
PC010
PC010
PC011
PC011
PC011
PC012
PC012
PC012
Project
Name
Reservation
System
Reservation
System
Reservation
System
HR System
HR System
HR System
Attendance
System
Attendance
System
Attendance
System
Project
Manager
Mr. Ajay
Project Employee Employee Department Department Hourly
Budget
No.
Name
No.
Name
Rate
120500 S100
Mohan
D03
Database
21.00
Mr. Ajay
120500 S101
Vipul
D02
Testing
16.50
Mr. Ajay
120500 S102
Riyaz
D01
IT
22.00
Mrs. Charu
Mrs. Charu
Mrs. Charu
Mr. Rajesh
500500
500500
500500
710700
S103
S104
S315
S137
Pavan
Jitendra
Pooja
Rahul
D03
D02
D01
D03
Database
Testing
IT
Database
18.50
17.00
23.50
21.50
Mr. Rajesh
710700 S218
Avneesh
D02
Testing
15.50
Mr. Rajesh
710700 S109
Vikas
D01
IT
20.50
1|Page
Northern Virginia Community College
Annandale Campus
ITD 256 Advanced Database Management
Grading Rubric:
Work is Complete
Work shows understanding of the normalization technique
Correct Entities identified in the 3NF
Correct Attributes identified in the 3NF
15 Points
15 Points
10 Points
10 Points
Total:
50 Points
2|Page
Northern Virginia Community College
Annandale Campus
ITD 256 Advanced Database Management
Practice Exercise 4
SQL Commands to create the database
•
Choose a scenario as listed in Practice Exercise 1 – 3. (You may choose a different scenario then the one you
chose before or choose the same scenario from previous exercises – I recommend choosing the same
scenario that you created the ERD for).
•
Create the database in the actual database application as indicated by your professor (i.e Microsoft Access,
MySQL or Oracle, etc.). You must use SQL commands to create the tables in the database (i.e Create Table
command). Submit the SQL commands with the completed database as part of the assignment. The tables
should be populated with records from the scenario you chose.
Grading Rubric:
Work is Complete
Used correct SQL commands to create tables
Used correct SQL commands to set constraints on attributes
Correctly set the relationships between entities in the database
Tables are populated with records from the original scenario
15 Points
15 Points
10 Points
5 Points
5 Points
Total:
50 Points
1|Page
