COM 330 Chapter 6 Assignment
1. Using the STUDENT table structure shown in Table P6.4, do the following.
Table P6.4 Sample STUDENT Records
Attribute Name Sample Value Sample Value Sample Value Sample Value Sample Value
STU_NUM 211343 200128 199876 199876 223456
STU_LNAME Stephanos Smith Jones Ortiz McKulski
STU_MAJOR Accounting Accounting Marketing Marketing Statistics
DEPT_CODE ACCT ACCT MKTG MKTG MATH
DEPT_NAME Accounting Accounting Marketing Marketing Mathematics
DEPT_PHONE 4356 4356 4378 4378 3420
COLLEGE_NAME Business Admin Business Admin Business Admin Business Admin Arts & Sciences
ADVISOR_LNAME Grastrand Grastrand Gentry Tillery Chen
ADVISOR_OFFICE T201 T201 T228 T356 J331
ADVISOR_BLDG Torre Building Torre Building Torre Building Torre Building Jones Building
ADVISOR_PHONE 2115 2115 2123 2159 3209
STU_GPA 3.87 2.78 2.31 3.45 3.58
STU_HOURS 75 45 117 113 87
STU_CLASS Junior Sophomore Senior Senior Junior
c. 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 decision 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.
Load the data from table P6.4 into an Access database. Add 5 additional students (make up your data) following the table structure.
Create a query in Access that list the students who have a Statistics major.
List the students who have a GPA of 3.45 or higher in a report.
Report the students who have the same advisor.
Submit c and d as an attachment, attach your Access database.