Question

Using tables and charts below, explain the criteria for 3rd Normal Form and normalize the table you obtained for the previous question to meet them. Also , is the table you obtained for the previous question in BCNF?

School_ID Course_ID Grade Student_Name Department Tuition Fee Instructor COP01, COPO2, COPO3 A, A, B John Smith BU01, BU02 B, B Roger Hicks CISE 4114123 3124234 250 James, Andrew, Peter Alan, Alan Business 300

Answer 1

Given Table is not in 1NF.

For a table to consider as 1NF, it should have atomic values i.e values should be single

But for Course-ID and instructor there are multiple values. We have to split them into individual values. Then the table will look like below which will be in 1NF.

School_ID	Course_ID	Grade	Student_Name	Departmen	Tution Fee	Instructor
4114123	COP01	A	John Smith	CISE	250	James
4114123	COP02	A	John Smith	CISE	250	Andrew
4114123	COP03	B	John Smith	CISE	250	Peter
3124234	BU01	B	Roger Hicks	Business	300	Alan
3124234	BU02	B	Roger Hicks	Business	300	Alan

Above table is not in 2NF. Points to remember for considering a table to be in 2NF is there should be no partial dependency.

which means non-primary attributes should not depend on the part of prime attributes.

In above table course_ID and Department will be the primary key , But instructor always depends upon the course_ID or Department. So its not satisfying the 2NF rules . For making it happen we have to split the table removing the partial dependency.

Course
Course_ID	Instructor	Tution Fee
COP01	James	250
COP02	Andrew	250
COP03	Peter	250
BU01	Alan	300
BU02	Alan	300

Department
Course_ID	Department
COP01	CISE
COP02	CISE
COP03	CISE
BU01	Business
BU02	Business

Student
School_ID	Student_Name
4114123	John Smith
3124234	Roger Hicks

Grade
School_ID	Course_ID	Grade
4114123	COP01	A
4114123	COP02	A
4114123	COP03	B
3124234	BU01	B
3124234	BU02	B

After splitting the table to make it to 2NF, all the tables except grade table are in 3NF.

Rules for a table to be in 3NF is there should not be any transitive dependency.

That means there should be only one and only primary key that defines each and every row uniquely.

So Grade table should be decomposed to make it 3NF

Student Course Mapping
ID	School_ID	Course_ID
1	4114123	COP01
2	4114123	COP02
3	4114123	COP03
4	3124234	BU01
5	3124234	BU02

Grade Course Mapping
ID	Grade
1	A
2	A
3	B
4	B
5	B

All the tables are in BCNF there are strictly functional dependant.

Result is

Course
Course_ID	Instructor	Tution Fee
COP01	James	250
COP02	Andrew	250
COP03	Peter	250
BU01	Alan	300
BU02	Alan	300

Department
Course_ID	Department
COP01	CISE
COP02	CISE
COP03	CISE
BU01	Business
BU02	Business

Student
School_ID	Student_Name
4114123	John Smith
3124234	Roger Hicks

Student Course Mapping
ID	School_ID	Course_ID
1	4114123	COP01
2	4114123	COP02
3	4114123	COP03
4	3124234	BU01
5	3124234	BU02

Grade Course Mapping
ID	Grade
1	A
2	A
3	B
4	B
5	B

Feel free to ask any doubts, if you face any difficulty in understanding.

Please upvote the answer if you find it helpful