49 | Page Chapter 6 : Database Fundamental 3) Normalize the table below to 3 NF. No Name Addr Tel Order Tech No Tech Date of Prod Prod Qty Qty No Date Name Work No Name On Hand 13 Isma Klang 8100 12/2/2002 124 Hassan 8/5/1998 13 CD 3 30 3/3/2003 576 Dean 3/2/1999 21 Jumper 10 20 37 Cabel 15 13 CD 30 21/1/2001 578 Dean 3/2/1999 $7 Pin 20 100 Hani Shah 5622 31/1/2002 654 Man 1/1/1990 57 Pin 10 100 Alam 10 RAM 8 45 99 Intan Klang 6983 4/5/2001 124 Hassan 8/5/1998 13 CD 10 30 654 Man 1/1/1990 37 Cabel 10 15 Table 6-67 The Table of Normalization 4) Normalize to 3 NF. DREAMHOME PROPERTY INSPECTION REPORT Property No : PG4 Property Address : 6, Taman Melor Inspection ID Inspection Inspection Comment Staff ID Staff Name Date Time 31/12/2000 10.00 AM Need to place SG137 Nina crockery 2 31/01/2001 9.00 AM In good order SG14 Ahmad Table 6-68 The Property Inspection Report Answer the following questions 1) Order No Order Date Product No Description Quantity 123 3/8/95 A3 Red Pen 3/8/95 A4 Green Pen 3/8/95 A2 Yellow Pen 234 5/7/95 Al Blue Pen 12 A3 Red Pen 345 12/7/95 A3 Red Pen 144 Table 6-69 The ORDER Table a) Explain the anomalies that exist when the name for Product_No is going to change to Black Pen? b) Explain the anomalies that exist when we are going to add new product but we don't receive any order yet? 50 | Page Chapter & : Database Fundamental c) Explain the anomalies that exist when we are going to delete Order_No 123? a) Convert the following table to 3 NF. This is a table concerning information about patients of a dentist. Each patient belongs to a household. The head of the household is designated as HH in the table. The following dependencies exist in Patient. Patient (HouseholdNumber, HouseholdName, HouseholdStreet, HouseholdCity, HouseholdState, HouseholdZip, Balance, PatientName, ServiceCode, ServiceName, ServiceDescription, ServiceFee) PatientNumber HouseholdNumber, HouseholdName, HouseholdCity, HouseholdState, HouseholdStreet, HouseholdZip, Balance, PatientName HouseholdNumber HouseholdName, HouseholdStreet, HouseholdCity, HouseholdState, HouseholdZip, Balance, Service Code ServiceDescription, ServiceFee 3) Using your knowledge of a college environment, determine the functional dependencies that exist in the following table. After these have been determined, convert this table to 3 NF