edited by
2,342 views
12 votes
12 votes

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).

edited by

1 Answer

Best answer
13 votes
13 votes
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}).$
edited by

Related questions

30 votes
30 votes
4 answers
4
Kathleen asked Sep 29, 2014
4,532 views
Let $\left(\{ p,q \},*\right)$ be a semigroup where $p*p=q$. Show that:$p*q=q*p$ and$q*q=q$