edited by
545 views
1 votes
1 votes

A school database maintains the following relations for its students, teachers and subjects:

  • Student(st_name, st_address, class, section, roll_no, regn_no)
  • Teacher(t_name, t_address, tel_no)
  • Subject(s_name, t_name, text_book, class)
    

Consider the following constraints on the existing data.

  • A student after admission to the school is assigned with a unique regn no. However, a student also gets a roll no that starts from $1$ for each class and section. A class can have many sections and a student is placed in only one class and section as expected in a school.
  • In the school a teacher’s name (t_name) has been found to be unique. However, more than one teacher may stay at the same address and the tel no is a land line connection where an address will have only one such telephone.
  • A subject name (s_name) is unique but the same subject may be taught in many classes (for example, History may be taught in many classes with different contents but s name remains the same). Every subject has a set of standard text books for a class and there may be more than one teacher who can teach the subject. Any teacher may use any of the standard text books to teach a subject.
  1. Considering the above constraints, identify the functional /multivalued dependencies present and normalize the relations.
  2. Using the normalized set of relations answer the following query using relational algebra or SQL: List all the teachers (t_name) who can teach History in Class V and reside in “Baranagar” (name of a locality). Consider that any address offers a locality name.
edited by

Please log in or register to answer this question.

Related questions

1 votes
1 votes
0 answers
1
go_editor asked Jun 1, 2016
512 views
A connected, simple, undirected planar graph $G(V, E)$ is given where $V$ denotes the set of vertices and E denotes the set of edges. In $V$, there is a designated source...
2 votes
2 votes
2 answers
2
go_editor asked Jun 1, 2016
1,050 views
A block of bits with $n$ rows and $m$ columns uses horizontal and vertical parity bits for error detection. If exactly 4 bits are in error during transmission, derive an ...
16 votes
16 votes
1 answer
3
go_editor asked Jun 1, 2016
1,660 views
A system has $4$ processes $A$, $B$, $C$, $D$ and $5$ allocatable resources $R_1, \: R_2,\: R_3, \: R_4,\: R_5$. The maximum resource requirement for each process and its...