Question

1. Using the STUDENT table structure shown in Table P6.4, do the following. Table P6.4 Sample STUDENT Records Attributes Name Sample Value Sample Value Sample Value Sample Value Sample Value STU_NUM STU_LNAME STU_MAJOR DEPT_CODE DEPT_NAME DEPT_PHONE COLLEGE_NAME ADVISOR_LNAME ADVISOR_OFFICE ADVISOR_BLDG ADVISOR_PHONE STU_GPA STU_HOURS STU_CLASS 211343 stephanos Accounting ACCT Accounting 4356 Business Admin Grastrand T201 Torre Building 2115 3.87 75 junior 200128 Smith Accounting ACCT Accounting 4356 Business Admin Grastrand T201 Torre Building 2115 2.78 45 Sophomore 199876 Jones Marketing MKTG 4378 Business Admin Gentry T228 Torre Building 2123 2.31 117 Senior 199876 Ortiz Marketing MKTG Marketing 4378 Business Admin Tillery T356 Torre Building 2159 3.45 113 Senior 223456 McKulsi Statistics MATH Mathematics 3420 Arts & Sciences Chen J331 Jones Building 3209 3.58 87 Junior a. Write the relational schema, draw its dependency diagram, and identify all dependencies, including all transitive dependencies. b. Write the relational schema and draw the dependency diagram to meet the 3NF requirements to the greatest extent possible. If you believe that practical considerations dictate using a 2NF structure, explain why your de3cision to retain 2NF is appropriate. If necessary, add or modify attributes to create appropriate determinants and to adhere to the naming conventions. d. Draw the Crow's Foot ERD.

Answer 1

1. The relational schema is as follows STUDENT (STU_NUM, STU LNAME, STU_MAJOR, DEPT CODE, DEPT_NAME DEPT PHONE, COLLEGE NAME, ADVISOR LNAME, ADVISOR OFFICE ADVISOR_BLDG, ADVISOR PHONE, STU_GPA, STU HOURS, STU_CLASSI The primary key is (STU NUM, STU LNAME) The fiunctional dependencies are (STU NUM, STU LNAME) → STU MAJOR, DEPT. CODE, DEPT NAME. ---- DEPT PHONE, COLLEGE_NAME, ADVISOR_LNAME ADVISOR_OFFICE, ADVISOR_BLDG, ADVISOR_PHONE STU_GPA, STU_HOURS, STU_CLASS DEPT-CODE→ DEPT NAME, DEPT PHONE, COLLEGE NAME ADVISOR OFFICE →ADVISOR BUILDING STU HOURS-> STU CLASS

STU NUMSTU LNAME STU MAJOR DEPT CODE DEPT NAMEDEPT PHONECOLLEGEADVISRADVISORADVISORADVISOSTU GPA LNAME OFFICE BLDG PHONE Transitive dependency Transitive dependency Transitive dependency

As there are partial dependencies, the relation STUDENT is in second normal form 2) Based on the above dependency diagram, the following considerations are made . ADV_LASTNAME is not a determinant of ADV_OFFICE or ADV_PHONE. There may be many advisors with the same nameIn rder to create a proper primary key for ADVISOR table, ADV NUM is adde COLLEGE_CODE is added to identify COLLEGE_NAME MAJOR_CODE and MAJOR_DESCRIPTION are added to identify student major Decomposethe relation STUDENT as shown below COLLEGE COLLEGE_CODE, COLLEGE_NAME) DEPARTMENT (DEPT_CODE, DEPT_NAME, DEPT_PHONE, COLLEGE_CODE) MAJOR(MAJOR_CODE, MAJOR_DESCRIPTION, DEPT_CODE) ADV_OFFICE(ADVISOR_OFFICE,ADVISOR_BUILDING) ADVISOR(ADVISOR_NUM, ADVISOR_LNAME, ADVISOR_OFFICE,ADVISOR PHONE) STUDENT (STU_NUM, STU_LNAME, MAJOR_CODE ADVISOR NUM, STU GPA, STU HOURS, STU CLASS) The dependency diagram is as shown below

ADVISOR OFFICE ADVISOR BLDG COLLEGE CODE COLLEGE NAME DEPT CODE DEPT NAME DEPT PHONE COLLEGE CODE MAJOR CODE MAJOR NAME DEPT CODE ADVISOR NUM ADVISOR LNAME ADVISOR I ADVISOR OFFICE DEPT-CODE PHONE

c) The Crow's Foot ERD diagram for the relation schema is as shown below: COLLEGE DEPARTMENT MAJOR PK COLLEGE CODEhas PK DEPT CODE FKI DEPT NAME offers PK MAJOR CODE FKI MAJOR NAME COLLEGE NAME DEPT CODE DEPT_PHONE COLLEGE CODE employs enrolls ADV OFF ADVISOR STUDENT PK | ADVISOR NUM FKI DEPT_CODE PK ADVISOR OFFICE PK STU NUM advises *Y ADVISOR BLDGHOFK AJOR CODE M ADVISORLNAMEH+--な1 FKI FK2 ADVISOR NUM accomidates STU LNAME STU CLASS STU HOURS STU_GPA ADVISOR OFFICE ADVISOR PHONE