Assignment
Question 1
Sales
|
CUS_ID
|
Name
|
Ord_NO
|
Ord_Date
|
Prod_ID
|
Description
|
Qty_Ord
|
|
C001
|
Gold
|
O81
|
15-Apr
|
P005
|
Chisel
|
6
|
|
C001
|
Gold
|
O81
|
15-Apr
|
P004
|
Plane
|
14
|
|
C075
|
Red
|
O99
|
16-Apr
|
P015
|
Saw
|
3
|
|
C009
|
Blue
|
O56
|
16-Apr
|
P033
|
Punch
|
24
|
|
C009
|
Blue
|
O56
|
16-Apr
|
P004
|
Plane
|
9
|
|
C001
|
Gold
|
O88
|
17-Apr
|
P015
|
Saw
|
10
|
Using the above Sales table structure, perform the followings:
Exercise 1:
Write the relational schema for the above table, draw the dependency diagram, and identify all dependencies, including all partial and transitive dependencies. You may assume that an order number references more than one product and that the table does not contain repeating groups.
Exercise 2:
Write the relational schemas after removing all partial dependencies and draw the new dependency diagrams. Identify the normal form for each created table structure.
Exercise 3:
Write the relational schemas after removing all transitive dependencies and draw the new dependency diagrams. Identify the normal form for each created table structure.
Exercise 4:
Draw the Entity Relationship Diagram (ERD) according to the result of Exercise 3.
Question 2:
You are given the following table structure with sample data in it.
|
PET ID
|
PET NAME
|
PET TYPEPET AGEOWNERVISIT DATE PROC_ID PROCEDURE
|
|
246
|
ROVER
|
DOG
|
12
|
SAM COOK
|
JAN 13/2002 01 RABIES VACCINATION
|
|
|
|
|
|
|
MAR 27/2002 10 EXAMINE and TREAT WOUND
|
|
|
|
|
|
|
APR 02/2002 05 HEART WORM TEST
|
|
|
|
|
|
|
|
|
298
|
SPOT
|
DOG
|
2
|
TERRY KIM
|
JAN 21/2002 08 TETANUS VACCINATION
|
|
|
|
|
|
|
MAR 10/2002 05 HEART WORM TEST
|
|
|
|
|
|
|
|
|
341
|
MORRIS
|
CAT
|
4
|
SAM COOK
|
JAN 23/2001 01 RABIES VACCINATION
|
|
|
|
|
|
|
JAN 13/2002 01 RABIES VACCINATION
|
|
|
|
|
|
|
|
|
519
|
TWEEDY
|
BIRD
|
2
|
TERRY KIM
|
APR 30/2002 20 ANNUAL CHECK UP
|
|
|
|
|
|
|
APR 30/2002 12 EYE WASH
|
Exercise 1:
Draw the dependency diagram of the table, and normalize the table to ensure all generated tables are in 3NF. Present all tables generated from the normalization.
Exercise 2:
Write SQL code to create the generated tables.