1. Using the STUDENT table structure shown in Table P6.4, do the following. Tabl
ID: 3563439 • Letter: 1
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.Explanation / Answer
1NF, 2NF :
The single attribute PK (STU_NUM) automatically places this table in 2NF, because it is not possible to
have partial dependencies when the PK consists of a single attribute.
The relational schema for the dependency diagram shown may be written as:
STUDENT(STU_NUM, STU_LNAME, STU_MAJOR, DEPT_CODE, DEPT_NAME,
DEPT_PHONE, DEPT_NAME, DEPT_PHONE, COLLEGE_NAME
ADVISOR_LNAME, ADVISOR_OFFICE, ADVISOR_BLDG, ADVISOR_PHONE,
STU_GPA, STU_HOURS, STU_CLASS)
3NF :
Identify the transitive dependencies (round #1):
STUDENT(STU_NUM, STU_LNAME, STU_MAJOR, STU_GPA, STU_HOURS, STU_CLASS,
DEPT_CODE, DEPT_NAME, DEPT_PHONE,
ADVISOR_NUM, ADVISOR_LNAME, ADVISOR_OFFICE, ADVISOR_BLDG, ADVISOR_PHONE)
(Note that ADVISOR_NUM has been added to serve as a FK to the advisor attributes because there are
(potentially) many advisors who have the same last name.)
STUDENT(STU_NUM, STU_LNAME, STU_MAJOR, STU_GPA, STU_HOURS, STU_CLASS,
DEPT_CODE (FK), ADVISOR_NUM (FK))
DEPARTMENT(DEPT_CODE, DEPT_NAME, DEPT_PHONE, COLLEGE_CODE)
ADVISOR(ADVISOR_NUM, ADVISOR_LNAME, ADVISOR_OFFICE, ADVISOR_BLDG,
ADVISOR_PHONE)
Identify the transitive dependencies (round #2):
There may still be transitive dependencies in the newly created tables. As in the first attempt, we may need
to add attributes to serve as keys or to provide more detail. If there are attributes we still need to add, the
added attributes may create transitive dependencies. For example, if we add more college information like
college address or college dean, then these attributes would be dependent on college_name rather than
dept_code. In this case, because the new college table will need a key, college_code has been added rather
than use college_name).
STUDENT(STU_NUM, STU_LNAME, STU_MAJOR (FK), STU_GPA, STU_HOURS (FK),
STU_CLASS, DEPT_CODE, ADVISOR_NUM)
MAJOR(STU_MAJOR, DEPT_CODE)
CLASS(STU_HOURS, STU_CLASS)
(For example, 1 through 36 denotes freshman status, 37 to 72 sophomore, etc.)
DEPARTMENT(DEPT_CODE, DEPT_NAME, DEPT_PHONE, COLLEGE_CODE (FK))
COLLEGE (COLLEGE_CODE, COLLEGE_NAME)
(For example, BUS denotes business in course listings)
ADVISOR(ADVISOR_NUM, ADVISOR_LNAME, ADVISOR_OFFICE (FK), ADVISOR_BLDG,
ADVISOR_PHONE)
BUILDING (ADVISOR_OFFICE, ADVISOR_BLDG)
(For example, offices start with
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.