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.
- Considering the above constraints, identify the functional /multivalued dependencies present and normalize the relations.
- 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.
