Question

ATTRIBUTE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE NAME STU_NUM 211343 200128 199876 198648 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 117 113 STU_CLASS Junior Sophomore Senior Senior Junior 75 45 87

Normalize the dataset into 3NF tables.

Use this format:

1 NF:

PROJECT (ProjectNum, Proj_Name, Start_Date, Emp_Num, Emp_Name, Job_Class)

2 NF:

3 NF:

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

First Normal Form (1NF) :

• Given table is in the first normal form because all columns are automic in nature.
• Also no duplicate columns and multivalued columns exists in the table.

1NF :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_CLASS)

Second Normal Form (2NF) :

• Second normal form says table should be in the first normal form.
• All non key columns in the table should depends upon primary key columns.
• In 2NF partial dependencies are not allowed.

Here above table needs to normalize into 2NF to remove partial dependencies.Here needs to identify new tables like

• Student (STU_NUM,STU_LNAME,STU_MAJOR,COLLEGE_NAME,STU_CLASS)
• Department (DEPT_CODE,DEPT_NAME,DEPT_PHONE)
• Advisor (ADVISOR_LNAME ,ADVISOR_OFFICE, ADVISOR_BLDG ,ADVISOR_PHONE)

Third Normal Form (3NF) :

• Third Normal Form (3NF) says table should be in the 2NF.
• In 3NF transitive dependency is not allowed.
• This means non key columns in the table should depends upon non key column in the table.
• Below are tables in 3NF.

Student (STU_NUM,STU_LNAME,STU_MAJOR,COLLEGE_NAME,STU_CLASS)

Department (DEPT_CODE,DEPT_NAME,DEPT_PHONE)

Advisor (ADVISOR_LNAME ,ADVISOR_OFFICE, ADVISOR_BLDG ,ADVISOR_PHONE)

StudentDepartmentAdvisor (STU_NUM,DEPT_CODE,ADVISOR_OFFICE,STU_HOURS,STU_GPA)

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.