Solutions to Practice Exercises from Lecture 5 Normalization

Solutions to Practice Exercises from Lecture 5 (Normalization)

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

Q 1 b INV_NUM PROD_NUM NUM_SOLD PROD_DESCRIPTION 3 NF INV_NUM PROD_PRICE SALE_DATE VEND_CODE 3 NF VEND_CODE VEND_NAME 3 NF

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

Q 2 a STU_NUM STU_LNAME STU_MAJOR DEPT_CODE DEPT_NAME DEPT_PHONE COLLEGE_NAME Transitive Dependencies ADV_LASTNAME ADV_OFFICE ADV_BUILDING ADV_PHONE Transitive Dependency STU_CLASS STU_GPA STU_HOURS Transitive Dependency 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.

STU_NUM STU_LNAME STU_MAJOR Q 2 b DEPT_CODE ADV_NUM Transitive Dependency MAJOR_CODE DEPT-CODE DEPT_NAME DEPT_PHONE BLDG_NAME ADV_OFFICE BLDG_MANAGER COLL_CODE ADV_BUILDING COLL_NAME Note: If several advisors share a phone, the ADV_PHONE is not a determinant of the other advisor attributes. ADV_LASTNAME STU_HRS MAJOR_DESCRIPTION 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. ADV_NUM STU_GPA Transitive Dependency BLDG_CODE DEPT_CODE STU_CLASS ADV_PHONE Transitive Dependency Note: If an office number is prefaced by a building designation, I. e. , office HKB 201 indicates the Howard Kallenberger Building, ADV_OFFICE is a determinant of ADV_BUILDING.

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

Q 3 a

3 b

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

Q 4 a

Q 4 b

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