Normalization of Database Tables
Using the STUDENT table structure shown in Table, do the following:
Table: 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
|
TorreBuilding
|
TorreBuilding
|
TorreBuilding
|
TorreBuilding
|
JonesBuilding
|
|
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
|
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 decision to retain 2NF is appropriate. If necessary, add or modify attributes to create appropriate determinants and to adhere to the naming conventions.
Using the results of (b) Specify each table with necessary attributes, their type and size. Identify primary key