The Gateway to Computer Science Excellence
+4 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 ‘*’.

Students (rollno*, sname, saddr)
courses (cno*, cname)
enroll(rollno*, cno*, grade)
teach(tno*, tname, cao*)

(cno is course number cname is course name, tno is teacher number, tname is teacher name, sname is student name, etc.)

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

rollno $\rightarrow$ sname, sdaddr

cno $\rightarrow$ cname

tno $\rightarrow$ tname

rollno, cno $\rightarrow$ grade

  1. Is the database in 3rd normal form (3NF)?

  2. If yes, prove that it is in 3 NF. If not normalize, the relations so that they are in 3NF (without proving)?

in Databases by Veteran (105k points) | 432 views

1 Answer

+4 votes

Here Attributes for primary key means they combined are PK. So, in table teach we have PK as tno,coa. We have FD as tno-->tname which violates 2NF condition,3NF too. So it is not in 3NF. 

To make it in 3NF we have to break teach table as(tno,coa) and (tno,tname).

by Active (2.3k points)
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.

Related questions

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
50,737 questions
57,375 answers
105,290 users