1,755 views

The following relations are used to store data about students, courses, enrollment of students in courses and teachers of courses. Attributes for primary key in each relation are marked by $‘\text{*’}.$

• $\text{students} (\text{rollno*},\text{sname},\text{saddr})$
• $\text{courses} (\text{cno*}, \text{cname})$
• $\text{enroll}(\text{rollno*},\text{cno*},\text{grade})$
• $\text{teach}(\text{tno*},\text{tname},\text{cao*})$

$(\text{cno}$ is course number, $\text{cname}$ is course name, $\text{tno}$ is teacher number, $\text{tname}$ is teacher name, $\text{sname}$ is student name, etc.)

For the relational database given above, the following functional dependencies hold:

• $\text{rollno} \rightarrow \text{sname}, \text{saddr}$
• $\text{cno} \rightarrow \text{cname}$
• $\text{tno} \rightarrow \text{tname}$
• $\text{rollno}, \text{cno} \rightarrow \text{grade}$
1. Is the database in $3^{rd}$ normal form $(3NF)$?

2. If yes, prove that it is in $\text{3NF}$. If not, normalize the relations so that they are in $\text{3NF}$ (without proving).

In table $\text{teach}$ we have Primary Key (which is automatically a candidate key as well) as $(\text{tno},\text{coa}).$ We have the functional dependency $\text{tno}\to \text{tname}$ which is a partial functional dependency (a proper subset of candidate key determining a non-key attribute) which violates $\text{2NF}$ requirement and hence $\text{3NF}$ too. So the relational database is not in $\text{3NF}.$

To make it in $\text{3NF}$ we have to break $\text{teach}$ table into $(\text{tno*},\text{coa*})$ and $(\text{tno*},\text{tname}).$

Also, one point to note is that if tno is the primary key to the original teach relation given, and cno is the FK in it referring to PK of courses, then one teacher cannot teach more than one course.

Incase someone faces any doubt about why Teach is not in 2NF:-

Here it’s said:

Attributes for primary key in each relation are marked by *

Hence the attributes marked by * together make up for the primary key because we know that in a relation there can be only 1 primary key. This is why in Teach relation, (tno*, cno*) together make up the primary key.