Solutions to Practice Exercises from Lecture 5 (Normalization)

# Solutions to Practice Exercises from Lecture 5 (Normalization)

Télécharger la présentation

## Solutions to Practice Exercises from Lecture 5 (Normalization)

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
##### Presentation Transcript

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

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)