## 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