MIS3306 Database Management Systems
Module 6 Exercise
Required for Grading (Type Your Name Below):
I am
(type your full name) and I complete this assignment following the UHD
academic integrity policy.
Instructions:
⚫ Upload this document to Blackboard>CourseContent>Module 6> Module 6 Exercise as
⚫
⚫
your submission.
Use the following format to list dependencies (In MS Word, type –> and it will form a
→. Or you can simply use –>):
▪
A → B, C
▪
(W, X) → Y, Z
Use the following format to describe your database design:
TABLE (PrimaryKey, ForeignKey (FK), Attribute1, Attribute2, Attribute3)
Where
⚫
table and attributes are denoted by TABLE (Attribute),
primary key is denoted by underline, and
foreign key is denoted by (FK).
Refine your design so that it presents attribute atomicity and proper naming
conventions.
Questions and Answers:
1. (10 points. 2/2/2/4 in order.) The PROJECT database. Following is a relation:
PROJECT (Proj_Num, Proj_Name, Emp_Num, Emp_Name, Job_Class, Charge_Hour,
Hours_Billed)
Assumption:
▪
One project has several employees. One employee works in several projects.
▪
One employee is assigned to a Job_Class. One Job_Class has several employees.
▪
Charge_Hour means hourly rate for a particular Job_Class
▪
Hours_Billed means the work hours of an employee for a particular project
(1) List all functional dependencies.
▪
1
(2) Among the list of (1), list partial dependencies (if none, type N/A).
▪
(3) Among the list of (1), list transitive dependencies (if none, type N/A).
▪
(4) Describe the new database design in 3NF. Refine your design so that it presents
attribute atomicity and proper naming conventions.
TABLE (PrimaryKey, ForeignKey (FK), Attribute1, Attribute2, Attribute3)
▪
2. (10 points. 2/2/2/4 in order.) The ORDER database. Following is a relation:
ORDER (Order_ID, Order_Date, Customer_ID, Customer_Name, Customer_Address,
Product_ID, Product_Description, Product_Finish, Product_StdPrice,
OrderedQuantity)
Assumption:
▪
One customer has several orders. One order is associated with one customer.
▪
One order contains several products. One product is purchased in several orders.
▪
One order contains several order lines, each presenting a purchased product and
the ordered quantity for the product.
(1) List all functional dependencies.
▪
(2) Among the list of (1), list partial dependencies.
(3) Among the list of (1), list transitive dependencies (if none, type N/A).
(4) Describe the new database design in 3NF. Refine your design so that it presents
attribute atomicity and proper naming conventions.
TABLE (PrimaryKey, ForeignKey (FK), Attribute1, Attribute2, Attribute3)
▪
2
3. (10 points. 2/2/2/4 in order.) The COURSE database. Following is a relation:
STUDENT_COURSE_GRADE (Stu_ID, Stu_FName, Stu_LName, Course_ID,
Course_Name, Grade)
Assumption:
▪
One course has several students. One student takes several courses.
▪
Grade represents the grade of a student in a particular course.
(1) List all functional dependencies.
▪
(2) Among the list of (1), list partial dependencies (if none, type N/A).
▪
(3) Among the list of (1), list transitive dependencies (if none, type N/A).
▪
(4) Describe the new database design in 3NF. Refine your design so that it presents
attribute atomicity and proper naming conventions.
TABLE (PrimaryKey, ForeignKey (FK), Attribute1, Attribute2, Attribute3)
▪
4.
(10 points. 2/2/2/4 in order.) The PRESCRIPTION database. Following is a relation:
PRESCRIPTION (Prescription_ID, Prescription_WrittenDate, Prescription_DiscardDate,
Prescription_Instruction, Customer_ID, Customer_Name, Customer_Phone,
Customer_Email)
Assumption:
▪
One customer can have many prescriptions. One prescription belongs to only one
customer.
(1) List all functional dependencies.
▪
(2) Among the list of (1), list partial dependencies (if none, type N/A).
▪
(3) Among the list of (1), list transitive dependencies (if none, type N/A).
▪
3
(4) Describe the new database design in 3NF. Refine your design so that it presents
attribute atomicity and proper naming conventions.
TABLE (PrimaryKey, ForeignKey (FK), Attribute1, Attribute2, Attribute3)
▪
4
