[Solved] CSC 370 (Database Systems) Mid-Midterm Exam

For this exam, consider the following schema of a simple university database. It includes information about instructors, students, and the courses offered. Feel free to remove this page from the exam. The Students table contains the id of the student (sid), his/her name (sname), age (in years), and gpa.Students(sid: integer, sname: string, age: integer, gpa: real)– Key: sid The Instructors table contains information about instructors of the courses: their id (iid),name (iname) and department they belong to (dept). An instruct can teach many differentcourses.Instructors(iid: string, iname: string, dept: string)– Key: iid The Courses table contains information about courses: their id (cid), their name (cname), the department that offers it (dept), the id of its instructor (iid), and the maximum number of students who can take it (maxenrol). Every iid in this table is also found in the table Instructors.Courses(cid: string, cname: string,dept: string, iid: string,maxenrol:integer)– Key: cid The table Enrolled contains what students are registered to which courses, and the grade they receive (NULL if they have not received one yet). A student can only register once to any given course, but he/she can register to as many courses as necessary. Neither sid nor cid can be NULL. Every sid in this table is also found in the table Students, and every cid in this table is also found in the table Courses.Enrolled(sid: integer, cid: string,grade: integer)– Key: (sid,cid)CSC 370 PAGE 21. Functional Dependencies(a) [2] Assume a relation R(T;C;M). T corresponds to the name of the Theater. C corresponds to the name of the City M corresponds to the name of the Movie. The name of the Theater is unique across all Cities. There are several Theaters per City. We only show a given Movie in one Theater per City.What are the functional dependencies that apply to this relation?(b) [2] Given the functional dependencies: A ! B, CH ! A, B ! E, BD ! C,EG ! H, DE ! F, is it possible to generate ADE ! CH? Why?(c) [4] Consider relation R(A,B,C,D) with functional dependencies: D ! C,CB !A,D ! A,AB ! D. Compute all its candidate keys.CSC 370 PAGE 32. Normalization(a) [2] Given the relation R(ABC) with functional dependencies A ! C and C ! B. Is the decomposition into relations AC and BC lossless join? Explain.(b) [2] Is the previous decomposition FD preserving? Why?(c) [2] Assume R is a relation with two or more attributes, and that it has one non-trivial functional dependency. Is R always BCNF? Explain.(d) [6] Consider the relation R(A,B,C,D) with functional dependencies: A ! B, C ! D, AD ! C, BC ! A. This table is not BCNF. Decompose this relation into a set of BCNF relations that are functional dependency preserving.CSC 370 PAGE 43. Relational Algebra and SQLFor each of the following questions, provide a relational algebra expression to answer them, and its equivalent SQL query:(a) [2] What is the average age of the students who are taking at least one course? Resultshould have only one column (and one tuple). Hint. Make sure you average eachstudent’s age only once.(b) [4] For every instructor that is teaching exactly two courses, list the iid of the instructor,their name iname and the course cid they are teaching. There are going to be two tuplesfor each instructor, one for each course they teach. For instance, your result should looksomething like this (three columns).iid iname cid342 M. Zastre Seng 365342 M. Zastre CSC 360123 D. German CSC 370456 D. German CSC 225(c) [4] List the sId and sname of the students who are enrolled in the fewest courses. Yourresult should include three columns: sid, sname and total number of courses. Makesure you consider students who might not be taking any course (in that case they areenrolled to zero courses).End of examinationTotal pages: 5Total marks: 30CSC 370 PAGE 5