Chapter 6 Normalization of Database Tables 235 Problems 1 Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure P6.1 into a dependency diagram that is in at least 3NF. FIGURE P6.1 APPOINTMENT ERD FOR PROBLEM 1 APPOINTMENT PK App Num FK1 Doc EmpID App_Date App Time App_PatientID App_Name App Phone App Street App_City App_State App_Zip App_Status DOCTOR least in PK Doc EmplD Doc Fname Doc Lname Doc CellPhone cy dia- Doc EmpID: Employee ID for the doctor. Doc Fname: Doctor's first name. Doc Lname: Doctor's last name. Doc CellPhone: Doctor's cell phone number. many cy dia- dency App_Num: System-generated number. App Date: The date of the appointment. App_Time: The time of the appointment. App PatientID: The ID number of the patient. App_Name: The name of the patient. App_Phone: The contact phone number of the patient. App_Street: The street address for the patient. App_City: The city the patient lives in. App_State: The state the patient lives in. App_Zip: The zip code for the patient's address. App_Status: The status of the appointment (pending, closed, cancelled) 236 Part 2 Design Concepts 3. Using the INVOICE table structure shown in Table P6.3, do the following: TABLE P6.- ATTRIBUTE SAMPLE VALUE SAMPLE VALUE TABLE P6.3 NAME ATTRIBUTE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE STU NUM NAME 211348 211349 211347 STU LNAME 211347 GH-778345P INV NUM 211347 AA-E3422QW RU-995748G STU MAJO QD-300932X 15-Jan-2018 16-Jan-2018 PROD_NUM AA-E3422QW 15-Jan-2018 DEPT_COD SALE_DATE 15-Jan-2018 15-Jan-2018 Power drill Rotary sander DEPT_NAN Band saw PROD LABEL Rotary sander 0.25-in. drill bit 211 157 DEPT PHC 309 VEND_CODE 211 211 ToughGo, Inc. NeverFail, Inc. COLLEGE BeGood, Inc. VEND_NAME NeverFail, Inc. NeverFail, Inc. ADVISOR QUANT_SOLD 8. $39.99 $49.95 $87.75 ADVISOR PROD_PRICE $49.95 $3.45 ADVISOF ADVISOF a. Write the relational schema, draw its dependency diagram, and identify ai dependencies, including all partial and transitive dependencies. You can assume that the table does not contain repeating groups and that an invoice number rf erences more than one product. (Hint: This table uses a composite primary ke STU GP. STU HC STU_CL b. Remove all partial dependencies, write the relational schema, and draw the ne. dependency diagrams. Identify the normal forms for each table structure vo created. you Note assu bet You can assume that any given product is supplied by a single vendor, but a ven- dor can supply many products. Therefore, it is proper to conclude that the follow- ing dependency exists: PROD_NUM → PROD_LABEL, PROD_PRICE, VEND_CODE, VEND_NAME (Hint: Your actions should produce three dependency diagrams.) TA AT c. Remove all transitive dependencies, write the relational schema, and draw the new dependency diagrams. Also identify the normal forms for each table struc- ture you created. ITE IT d. Draw the Crow's Foot ERD. RO 4. Using the STUDENT table structure shown in Table P6.4, do the following: a. Write the relational schema and draw its dependency diagram. Identify an der dencies, including all transitive dependencies. b. Write the relational schema and draw the dependency diagram to meet the 3NF 2NF is appropriate. If necessary, add or modify attributes to create appropriate determinants and to adhere to the naming conventions. requirements to the greatest practical extent possible. If you believe that practical considerations dictate using a 2NF structure, explain why your decision to retain c. Using the results of Problem 4, draw the Crow's Foot ERD. ШИ 5.