Description
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 different
courses.
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 2
1. 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 3
2. 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 4
3. Relational Algebra and SQL
For 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? Result
should have only one column (and one tuple). Hint. Make sure you average each
student’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 tuples
for each instructor, one for each course they teach. For instance, your result should look
something like this (three columns).
iid iname cid
342 M. Zastre Seng 365
342 M. Zastre CSC 360
123 D. German CSC 370
456 D. German CSC 225
(c) [4] List the sId and sname of the students who are enrolled in the fewest courses. Your
result should include three columns: sid, sname and total number of courses. Make
sure you consider students who might not be taking any course (in that case they are
enrolled to zero courses).
End of examination
Total pages: 5
Total marks: 30
CSC 370 PAGE 5
