Read Microsoft Word - college.docx text version

1. Using the STUDENT table structure shown below, write the relational schema, draw its dependency diagram, and identify all dependencies (including all transitive dependencies). Attribute Name 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 Sample Value 211343 Stephanos Accounting ACCT Accounting 4356 Business Admin Grastrand T201 Torre Building 2115 3.87 75 Junior Sample Value 200128 Smith Accounting ACCT Accounting 4356 Business Admin Grastrand T201 Torre Building 2115 2.78 45 Sophomore Sample Value 199876 Jones Marketing MKTG Marketing 4378 Business Admin Gentry T228 Torre Building 2123 2.31 117 Senior Sample Value 199879 Ortiz Marketing MKTG Marketing 4378 Business Admin Tillery T356 Torre Building 2159 3.45 113 Senior

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 "BS" like BS-4030, are in the Business/SPEA building)

Verify the relationships and add refinements We need to determine the relationship between the tables. The foreign keys will be helpful but the business rules take precedence. For example, if a student can have multiple majors then the 1:m relationship below becomes a m:n relationship (and would require an additional table to form this relationship). Also, additional attributes may still be necessary at this point to further clarify the design.

Information

Microsoft Word - college.docx

2 pages

Report File (DMCA)

Our content is added by our users. We aim to remove reported files within 1 working day. Please use this link to notify us:

Report this file as copyright or inappropriate

1272381