# GATE1993-24

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

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

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.
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?
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\}$