2. Consider textbook question 4, page 236. Give short names to the at- tributes in Table P6.4, from top to bottom: let A the short name for 1 STU_NUM, B the short name for STULLNAME, ..., N be the short name for STU-CLASS. Let's change things around a little: also add 0: STU GPA IN MAJOR and P: STU_SCHOLARSHIP IN MAJOR. This is to reflect a university where a student can have more than one major. A student with several majors has a separate advisor (and, if the major is in a different department, the student is associated with each department) for each major. Besides the overall GPA calculated by the university for the student (attribute L), based on all coursework, the student's GPA is assessed differently for each major (based only on courses that are required for that major). Each department has a undisclosed mapping from GPAs in their majors to a level of scholarship support (attribute P). Another sample value in the table could be Stephanos (who already majors in Accounting in the School of Business Admin), who majors in Software Engineering in the School of Engineering and CS. (a) Determine the functional dependencies and code them into the Aus- tralian tool. Take a screenshot. At this university, each advisor has their own unique phone number, assigned when they are hired and kept until they leave the university. (b) Produce a 2NF decomposition using the tool. Write down the tables. (I got 3.) (c) Produce a 3NF decomposition and write down the tables. Do they seem to correspond directly to your input functional dependencies? If so, do you think this always happens? ATTRIBUTE NAME SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE INV_NUM 211347 211347 211347 211348 211349 PROD_NUM AA-E3422QW QD-300932X RU-995748G AA-E3422QW GH-778345P SALE_DATE 15-Jan-2018 15-Jan-2018 15-Jan-2018 15-Jan-2018 16-Jan-2018 PROD_LABEL Rotary sander Band saw 0.25-in. drill bit Rotary sander Power drill VEND_CODE 211 211 309 211 157 VEND_NAME NeverFail, Inc. NeverFail, Inc. BeGood, Inc. NeverFail, Inc. ToughGo, Inc. QUANT_SOLD 1 8 1 2 1 PROD_PRICE $49.95 $3.45 $39.99 $49.95 $87.75 2. Consider textbook question 4, page 236. Give short names to the at- tributes in Table P6.4, from top to bottom: let A the short name for 1 STU_NUM, B the short name for STULLNAME, ..., N be the short name for STU-CLASS. Let's change things around a little: also add 0: STU GPA IN MAJOR and P: STU_SCHOLARSHIP IN MAJOR. This is to reflect a university where a student can have more than one major. A student with several majors has a separate advisor (and, if the major is in a different department, the student is associated with each department) for each major. Besides the overall GPA calculated by the university for the student (attribute L), based on all coursework, the student's GPA is assessed differently for each major (based only on courses that are required for that major). Each department has a undisclosed mapping from GPAs in their majors to a level of scholarship support (attribute P). Another sample value in the table could be Stephanos (who already majors in Accounting in the School of Business Admin), who majors in Software Engineering in the School of Engineering and CS. (a) Determine the functional dependencies and code them into the Aus- tralian tool. Take a screenshot. At this university, each advisor has their own unique phone number, assigned when they are hired and kept until they leave the university. (b) Produce a 2NF decomposition using the tool. Write down the tables. (I got 3.) (c) Produce a 3NF decomposition and write down the tables. Do they seem to correspond directly to your input functional dependencies? If so, do you think this always happens? ATTRIBUTE NAME SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE INV_NUM 211347 211347 211347 211348 211349 PROD_NUM AA-E3422QW QD-300932X RU-995748G AA-E3422QW GH-778345P SALE_DATE 15-Jan-2018 15-Jan-2018 15-Jan-2018 15-Jan-2018 16-Jan-2018 PROD_LABEL Rotary sander Band saw 0.25-in. drill bit Rotary sander Power drill VEND_CODE 211 211 309 211 157 VEND_NAME NeverFail, Inc. NeverFail, Inc. BeGood, Inc. NeverFail, Inc. ToughGo, Inc. QUANT_SOLD 1 8 1 2 1 PROD_PRICE $49.95 $3.45 $39.99 $49.95 $87.75
