undefined

Normalization Tutorial Question 1 Given a report of sales by SQuare Mas shown below: SQuare M Sales Report salespersonNo salespersonName sales Area custNo custName outletNo outletAdd sales Amt S111 Ally Bangi c101 0121 Kajang RM1250.00 S111 C141 0144 RM2500.00 Ally Terry Semenyih Melawati S222 C122 Lily Florist Teleflorist Sweet Florist Litz Florist 0177 Bangi Ukay Perdana Ukay Perdana RM1800.00 S222 Terry C444 0185 Hulu Klang RM2000.00 a) Normalize the above relation to Third Normal Form (3NF). Relations can be shown in the form of relational database schema. b) Identify all the functional dependency for each attribute towards its primary key. Question 2 Normalize the following relation into Third Normal Form (3NF). Relations can be shown in the form of relational database schema. Student Violation Record licenseNo name state Code type Violation fine date ICode location D0675 Leman Alif Perak 2 Parking violation RM25 10/7/10 L110 Malay Street D0675 Leman Alif Perak 3 Speed violation RM50 12/12/10 H120 KM 10, NKVE D1211 Sarah Deli Johor 3 Speed violation RM50 3/5/10 H200 KM 9, PLUS D2006 Linda Amri Melaka 1 Inspection overdue RM15 31/7/09 L567 10, Malacca St D2006 Linda Amri Melaka 2 Parking violation RM25 20/9/09 L110 Malay Street D2006 Linda Amri Melaka 3 Speed violation RM50 31/8/10 H120 KM 10, NKVE Question 3 Given details of patient's appointment card as shown below: Arinas Clinic Appointment Card Name : Azhari Ahmad Patient ID : P1110 IC No. : 871201-01-1051 Address : No. 18, Parkway Street, Kajang Perdana. Phone No. : 03-87623411 Appointment Date Appointment Time Doctor ID Doctor Name Surgery No Surgery Name 12/06/06 11.00 am D103 Azlin S102 Scaling 10/12/08 2.00 pm D105 Emi S105 Crowning 15/07/10 10.00 am D102 Azhar S102 Scaling a) Normalize the above relation to Third Normal Form (3NF). Relations can be shown in the form of relational database schema. b) Identify all the functional dependency for each attribute towards its primary key. Question 4 Given data as follows :- a) From the table given, normalized the table to 3NF. For INF, use the first approach. Show all the steps and name the relations and mark the primary keys and foreign keys at the end. Relations can be shown in the form of relational database schema. b) Identify all the functional dependency for each attribute towards its primary key. hours projNum 15 projName VR6 empNum 103 empName Mike Sally jobCode A1 charge Hour $84.50 $105.50 23.8 101 A2 19.4 105 Alice A2 $105.50 35.7 jobClass Programmer Database Designer Database Designer App Designer Electrical Engineer Programmer 18 Tower Link 114 Jones A4 $48.10 24.6 115 Darlene AS $90.00 23.8 106 George A1 $84.50 45.6