Answer:
Check the explanation
Explanation:
As we know that any relation will be in 2NF if it satisfies following two conditions
1> It should be in 1NF.
2> It should not contain any partial dependency i.e. all the non-prime attributes should fully functionally dependent on the relation key(s).
In the given table,
The primary key is (Student#,class#).
And one of functional dependency is
Class# determines Classroom (Class# ------> Classroom)
This functional dependency is a partial functional dependency because in this dependency a proper subset(Class#) of a relation key(Student#,Class#) functionally determines a non-prime attribute(Classroom).
Since this table contains a partial dependency therefore as per the definition of 2NF this table is not in 2NF.
Now we can decompose it into 2NF by splitting the table into the following two tables(R1 and R2).
R1(Student#, Class#, Advisor, Advisor-office, Department) and R2(Class#, Classroom)
R1 table is shown below.
Student# Class# Advisor Advisor-office Department
1011 101-07 Jones 212 CSCI
1011 201-02 Jones 212 CSCI
1011 310-11 Jones 212 CSCI
2010 101-07 Anderson 308 MATH
2010 201-02 Anderson 308 MATH
2010 120-12 Anderson 308 MATH
R2 table is shown below
Class# Classroom
101-07 WIL230
201-02 OVR212
310-11 WIL224
120-12 WIL306
Now these tables(R1 and R2) are in 2NF.
Argument For 3NF
As we know that any relation will be in 3NF if it satisfies following two conditions
1> It should be in 2NF.
Student# Class# Advisor
1011 101-07 Jones
1011 201-02 Jones
1011 310-11 Jones
2010 101-07 Anderson
2010 201-02 Anderson
2010 120-12 Anderson
R12 is shown below
Advisor Advisor-office
Jones 212
Anderson 308
R13 is shown below
Advisor Advisor-office
Jones CSCI
Anderson MATH
So these four tables (R11, R12, R13, R2) correctly represent the third normal form of the given table.
2> It should not contain any transitive dependency i.e. all the non-prime attributes should be non-transitively dependent on the relation key(s).
Now from the question, we come to know that
1> Each advisor is assigned an office, which means that following functional dependency exists in table R1
Advisor determines Advisor-office (Advisor------> Advisor-office).
2>Each advisor belongs to a particular department, which means that following functional dependency also exists in table R1
Advisor determines Department (Advisor------> Department).
So we have following two dependencies in table R1
1> Advisor determines Advisor-office (Advisor------> Advisor-office).
2> Advisor determines Department (Advisor------> Department).
Since we know that (Student#,Class#) is a primary key, so it should determine Advisor functionally i.e.
(Student#,Class#)----> Advisor
and from above two conditions we have
Advisor--->Advisor-office and Advisor----->Department
We can clearly see that Advisor---->Advisor-office and Advisor----->Department are two transitive dependency because Advisor itself is functionally dependent on (Student#, Class#) .
So here we can see that two non prime attributes (Advisor-office and Department) are transitively dependent on the Primary key(Student#,Class#).
Therefore as per definition R1 is not in 3NF. But R2 is in 3NF as per definition.
So we can decompose R1 into 3NF by splitting the table into the following three tables(R11, R12, and R13).
R11(Student#, Class#, Advisor), R12 (Advisor, Advisor-office), and R13(Advisor, Department)
R11 is shown below
Student# Class# Advisor
1011 101-07 Jones
1011 201-02 Jones
1011 310-11 Jones
2010 101-07 Anderson
2010 201-02 Anderson
2010 120-12 Anderson
R12 is shown below
Advisor Advisor-office
Jones 212
Anderson 308
R13 is shown below
Advisor Advisor-office
Jones CSCI
Anderson MATH
So these four tables (R11, R12, R13, R2) correctly represent the third normal form of the given table.
