Question

Attribute Name	Sample Value	Sample Value	Sample Value	Sample Value
STU_NUM	211343	200128	199876	199876
STU_LNAME	Stephanos	Smith	Jones	Ortiz
STU_MAJOR	Accounting	Accounting	Computer Science	Computer Science
DEPT_CODE	ACCT	ACCT	MKTG	MKTG
DEPT_NAME	Accounting	Accounting	Computer Science	Computer Science
DEPT_PHONE	4356	4356	4378	4378
COLLEGE_NAME	Business Admin	Business Admin	EAST	EAST
ADVISOR_LNAME	Grastrand	Grastrand	Gentry	Tillery
ADVISOR_OFFICE	T201	T201	T228	T356
ADVISOR_BLDG	TE Building	TE Building	TE Building	TE Building
ADVISOR_PHONE	2115	2115	2123	2159
STU_GPA	3.87	2.78	2.31	3.45
STU_HOURS	75	45	117	113
STU_CLASS	Junior	Sophomore	Senior	Senior

1. Is this in 1NF? Why or why not? If it isn’t, then what change(s) need to be made to ensure it’s in 1NF?
2. Break this down to 2NF, and eventually 3NF by clearly working through the functional dependencies, partial dependencies and any transient dependencies to arrive at your final tables.
3. Show the new tables with at least 3 rows of sample data for each

Answer 1

Dear Student ,

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

Normalization :

• Normalization is process of defragmenting large and complex data into simple and smaller form.
• Normalization helps to avoid duplicate and inconsistent data.

First Normal Form (1NF) :First Normal Form (1NF) says all the columns in the table should be automic in nature.No duplicate columns and multivalued columns are allowed in first normal form.

Is this in 1NF? Why or why not? If it isn’t, then what change(s) need to be made to ensure it’s in 1NF?

Given table is in the first normal form because all columns in the table are automic in nature.Here given relation does not contains any duplicate column or multivalued column.

Second Normal Form (2NF) :

Second normal form says table should be in the first normal form(1NF).All non key columns in the table should depends upon primary key column.Here in 2NF partial dependencies are not allowed.

Here above table given in the question needs to normalize in 2NF to remove partial dependencies.Here need to identity new tables like

• Student :Here columns like STU_LNAME,STU_CLASS,are depends on STU_NUM hence all these details needs to store into new table called as student.
• Major :Here table need to create to store major details.
• Department :Here columns like DEPT_NAME,DEPT_PHONE depends upon DEPT_CODE hence all these details needs to store in to new table called as department.
• College :Here need to store details for college like college id and college name
• Advisor :Here to store details like ADVISOR_LNAME, ADVISOR_OFFICE, ADVISOR_BLDG, ADVISOR_PHONE into new table.

Below are table in second normal form.

1.Table Name :Student

Description :This table store student details like STU_NUM,STU_LNAME,STU_CLASS.

Schema :Student (STU_NUM,STU_LNAME,STU_CLASS)

FD :STU_NUM STU_LNAME,STU_CLASS

Below is the table data

STU_NUM	STU_LNAME	STU_CLASS
211343	Stephanos	Junior
200128	Smith	Sophomore
199876	Jones	Senior
199877	Ortiz	Senior

Here STU_NUM is primary key hence student number 199876 can not be duplicate.Need to change that number.For normalization purpose changing it to 199877.

2.Table Name :Major

Description :This table store major details like MAJOR_ID,STU_MAJOR,

Schema :Major(MAJOR_ID,STU_MAJOR)

FD :MAJOR_ID STU_MAJOR,

Below is the table data

MAJOR_ID	STU_MAJOR
1	Accounting
2	Computer Science

3.Table Name :Department

Description :This table store department details like DEPT_CODE,DEPT_NAME,DEPT_PHONE

Schema :Department(DEPT_CODE,DEPT_NAME,DEPT_PHONE)

FD :DEPT_CODE DEPT_NAME,DEPT_PHONE

Below is the table data

DEPT_CODE	DEPT_NAME	DEPT_PHONE
ACCT	Accounting	4356
MKTG	Computer Science	4378

4.Table Name :College

Description :This table store college details like COLLEGE_ID,COLLEGE_NAME

Schema :College(COLLEGE_ID,COLLEGE_NAME)

FD :COLLEGE_ID COLLEGE_NAME

Below is the table data

COLLEGE_ID	COLLEGE_NAME
1	Business Admin
2	EAST

5.Table Name :Advisor

Description :This table store advisor details like ADVISOR_LNAME, ADVISOR_OFFICE, ADVISOR_BLDG, ADVISOR_PHONE,COLLEGE_ID

Schema :Advisor(ADVISOR_OFFICE,ADVISOR_LNAME,ADVISOR_BLDG, ADVISOR_PHONE , COLLEGE_ID)

FD :ADVISOR_OFFICE,COLLEGE_ID ADVISOR_LNAME,ADVISOR_BLDG, ADVISOR_PHONE

Below is the table data

ADVISOR_OFFICE	ADVISOR_LNAME	ADVISOR_BLDG	ADVISOR_PHONE	COLLEGE_ID
T201	Grastrand	TE Building	2115	1
T228	Gentry	TE Building	2123	2
T356	Tillery	TE Building	2159	2

Third Normal Form (3NF) :Third Normal Form says table should be in the second normal form and all non key columns in the table should depends upon non key column in the table.Here in 3NF transitive dependencies are not allowed.

Here table needs to normalize into 3NF to remove transitive dependencies.Below are table in 3NF.

1.Table Name :Student

Description :This table store student details like STU_NUM,STU_LNAME,STU_CLASS.

Schema :Student (STU_NUM,STU_LNAME,STU_CLASS)

FD :STU_NUM STU_LNAME,STU_CLASS

Below is the table data

STU_NUM	STU_LNAME	STU_CLASS
211343	Stephanos	Junior
200128	Smith	Sophomore
199876	Jones	Senior
199877	Ortiz	Senior

Here STU_NUM is primary key hence student number 199876 can not be duplicate.Need to change that number.For normalization purpose changing it to 199877.

2.Table Name :Major

Description :This table store major details like MAJOR_ID,STU_MAJOR,

Schema :Major(MAJOR_ID,STU_MAJOR)

FD :MAJOR_ID STU_MAJOR,

Below is the table data

MAJOR_ID	STU_MAJOR
1	Accounting
2	Computer Science

3.Table Name :Department

Description :This table store department details like DEPT_CODE,DEPT_NAME,DEPT_PHONE

Schema :Department(DEPT_CODE,DEPT_NAME,DEPT_PHONE)

FD :DEPT_CODE DEPT_NAME,DEPT_PHONE

Below is the table data

DEPT_CODE	DEPT_NAME	DEPT_PHONE
ACCT	Accounting	4356
MKTG	Computer Science	4378

4.Table Name :College

Description :This table store college details like COLLEGE_ID,COLLEGE_NAME

Schema :College(COLLEGE_ID,COLLEGE_NAME)

FD :COLLEGE_ID COLLEGE_NAME

Below is the table data

COLLEGE_ID	COLLEGE_NAME
1	Business Admin
2	EAST

5.Table Name :Advisor

Description :This table store advisor details like ADVISOR_LNAME, ADVISOR_OFFICE, ADVISOR_BLDG, ADVISOR_PHONE,COLLEGE_ID

Schema :Advisor(ADVISOR_OFFICE,ADVISOR_LNAME,ADVISOR_BLDG, ADVISOR_PHONE , COLLEGE_ID)

FD :ADVISOR_OFFICE,COLLEGE_ID ADVISOR_LNAME,ADVISOR_BLDG, ADVISOR_PHONE

Below is the table data

ADVISOR_OFFICE	ADVISOR_LNAME	ADVISOR_BLDG	ADVISOR_PHONE	COLLEGE_ID
T201	Grastrand	TE Building	2115	1
T228	Gentry	TE Building	2123	2
T356	Tillery	TE Building	2159	2

6.Table Name :StudentAllDetails

Description :This table store student details along with Department,advisor , major , hours and GPA.

Schema :StudentAllDetails (STU_NUM,MAJOR_ID,DEPT_CODE,ADVISOR_OFFICE,STU_HOURS,STU_GPA)

Below is the table data

STU_NUM	MAJOR_ID	DEPT_CODE	ADVISOR_OFFICE	STU_HOURS	STU_GPA
211343	1	ACCT	T201	75	3.87
200128	1	ACCT	T201	45	2.78
199876	2	MKTG	T228	117	2.31
199877	2	T356	T356	113	3.45

STU_NUM 199877 is used for demonstration purpose.

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