Solutions to Practice Exercises from Lecture 5 (Normalization)

1. Solutions to Practice Exercises from Lecture 5 (Normalization)

2. INV_NUM PROD_NUM SALE_DATE PROD_DESCRIPTION VEND_CODE VEND_NAME NUM_SOLD PROD_PRICE Partial dependency Transitive Dependency Partial dependency 3NF 3NF INV_NUM PROD_NUM NUM_SOLD INV_NUM SALE_DATE 2NF (Contains a PROD_NUM PROD_DESCRIPTION PROD_PRICE VEND_CODE VEND_NAME transitive dependency) Transitive Dependency Q1a

3. 3NF 3NF INV_NUM PROD_NUM NUM_SOLD INV_NUM SALE_DATE 3NF 3NF PROD_NUM PROD_DESCRIPTION PROD_PRICE VEND_CODE VEND_CODE VEND_NAME Q1b

4. 1 M M contains INVOICE LINE (1,N) (1,1) (1,1) references (0,N) 1 M 1 VENDOR supplies PRODUCT (0,N) (1,1) LINE PRODUCT VENDOR INVOICE INV_NUM INV_NUM PROD_NUM PROD_NUM INV_NUM INV_NUM VEND_CODE VEND_CODE PROD_DESCRIPTION VEND_NAME INV_DATE PROD_NUM PROD_NUM PROD_PRICE NUM_SOLD VEND_CODE Q1c

5. STU_NUM STU_LNAME STU_MAJOR DEPT_CODE DEPT_NAME DEPT_PHONE COLLEGE_NAME Transitive Dependencies ADV_LASTNAME ADV_OFFICE ADV_BUILDING ADV_PHONE STU_CLASS STU_GPA STU_HOURS Transitive Dependency Transitive Dependency Q2a Note 1: The ADV_LASTNAME is not a determinant of ADV_OFFICE or ADV_PHONE, because there are (potentially) many advisors who have the same last name. Note 2: ADV_OFFICE is a determinant of ADV_BUILDING if the ADV_OFFICE is , in effect, a code. For example, if offices such as HE-201 and HE-324 use the prefix HE to indicate their location in the Heinz building, the office locators determine the building.

6. ADV_NUM STU_CLASS STU_GPA STU_HRS STU_NUM STU_LNAME STU_MAJOR DEPT_CODE Transitive Dependency Transitive Dependency MAJOR_CODE DEPT-CODE MAJOR_DESCRIPTION BLDG_CODE BLDG_NAME BLDG_MANAGER DEPT_CODE DEPT_NAME DEPT_PHONE COLL_CODE COLL_CODE COLL_NAME Note: One might assume that a department has several phones, so the DEPT_PHONE is not a determinant of the DEPT_CODE. If each department has only one phone, knowing the phone number means that you know the DEPT_CODE, too …. thus creating a condition in which BCNF requirements are not met. attributes. ADV_NUM ADV_LASTNAME ADV_OFFICE ADV_BUILDING ADV_PHONE Transitive Dependency Note: If an office number is prefaced by a building designation, I.e., office HKB201 indicates the Howard Kallenberger Building, ADV_OFFICE is a determinant of ADV_BUILDING. Q2b Note: If several advisors share a phone, the ADV_PHONE is not a determinant of the other advisor

7. 1 M 1 M 1 COLLEGE DEPARTMENT offers MAJOR owns (1,N) (1,1) (1,N) (1,1) 1 (1,N) (1,N) employs attracts M (1,1) (1,1) 1 M 1 M M BUILDING ADVISOR STUDENT houses has (1,1) (1,1) (1,N) (1,N) ADVISOR BUILDING ADV_NUM ADV_NUM STUDENT BLDG_CODE BLDG_CODE ADV_LNAME STU_NUM STU_NUM BLDG_NAME STU_LNAME ADV_OFFICE BLDG_MANAGER STU_CLASS DEPT_CODE ADV_NUM ADV_PHONE DEPARTMENT COLLEGE STU_GPA BLDG_CODE DEPT_CODE DEPT_CODE COLL_CODE COLL_CODE STU_HOURS DEPT_NAME MAJOR COLL_NAME MAJ_CODE DEPT_PHONE MAJ_CODE MAJ_CODE COLL_CODE MAJOR_NAME DEPT_CODE Q2c

8. Q3a

9. 3b

10. M DEPENDENT (1,1) 1 1 1 manages has (1,1) (0,1) (1,N) 1 M 1 M M employs EMP_EDUC DEPARTMENT EMPLOYEE (0,N) (1,1) (1,N) (1,1) (1,1) (1,1) M (1,N) classifies 1 EDUCATION (1,N) 1 JOB DEPARTMENT DEPT _CODE _CODE EMPLOYEE EMP_EDUC DEPT_NAME EMP_CODE EDUCATION EMP_CODE EMP_CODE EMP_LNAME EDUC_CODE EDUC_CODE DEPT_CODE DEPENDENT EDU_DATE_EARNED EDUC_DESCRIPTION JOB_CLASS EMP_CODE JOB EMP_HIRE_DATE JOB_CLASS DEPT_NUM DEPT_CODE JOB_TITLE DEPT_FNAME JOB_BASE_SALARY DEPT_TYPE Q3c

11. Q4a

12. Q4b

13. 1 EMPLOYEE (1,N) is basis for (1,1) 1 M M 1 M MEMBER DINNER INVITATION (1,N) (1,1) (1,1) M (1,1) accompanies (1,N) INVITATION 1 DESSERT INVITE_NUM MEMBER INVITE_DATE DINNER ENTREE MEM_NUM DIN_CODE DIN_CODE ENT_CODE MEM_NAME MEM_NUM DIN_DATE ENT_DESCRIPTION MEM_ADDRESS INVITE_ACCEPT DIN_DESCRIPTION MEM_STATE DESSERT INVITE_ATTEND ENT_CODE MEM_ZIP DES_CODE DES_CODE DES_DESCRIPTION Q4c (1,N)