search
Log In
5 votes
927 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 ‘*’.

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 927 views

1 Answer

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

0
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

18 votes
4 answers
1
1.5k 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 *'. Students (rollno*, sname, saddr) courses (cno*, cname) enroll(rollno* ... number and name of students who got A grade in at least one course taught by teacher names Ramesh for the above relational database.
asked Sep 30, 2014 in Databases Kathleen 1.5k views
42 votes
2 answers
2
7.3k views
Consider the following relational schema. Students(rollno: integer, sname: string) Courses(courseno: integer, cname: string) Registration(rollno: integer, courseno: integer, percent: real) Which of the following queries are equivalent to this query in English? Find the distinct names of all students who ... I, II, III and IV I, II and III only I, II and IV only II, III and IV only
asked Sep 24, 2014 in Databases Arjun 7.3k views
28 votes
3 answers
3
2.7k views
An $\text{ISAM}$ (indexed sequential) file consists of records of size $64$ $bytes$ each, including key field of size $14$ $bytes$. An address of a disk block takes $2$ $bytes$. If the disk block size is $512$ $bytes$ and there are $16$ $K$ records, compute the size of the data and index areas in terms of number blocks. How many levels of $\text{tree}$ do you have for the index?
asked Sep 30, 2014 in Databases Kathleen 2.7k views
34 votes
4 answers
4
4.1k views
Which of the rational calculus expression is not safe? $\left\{t \mid \exists u \in R_1\left(t[A] = u[A]\right) \land \neg \exists s \in R_2 \left(t[A] = s[A]\right)\right\}$ ... $\left\{t \mid \exists u \in R_1\left(t[A]=u[A]\right) \land \exists s \in R_2 \left(t[A] = s[A]\right)\right\}$
asked Sep 15, 2014 in Databases Kathleen 4.1k views
...