1. We have a DBMS for student course offering. There are relations in the database (keys are with underscore): Course(c#, title, credits, year, time, room), Instructor(dept, iid#, name), Student(dept, sid#, name), Enrols(sid#, c#, grade), Teaches(iid#, c#), TAs(sid#, c#). Please write down the answer for the following questions, each by (1) relational algebra, (2) tuple relational calculus, (3) SQL.

You may write the relations with the abbreviations: C(c#, t, cd, y, tm, r), I(d, iid#, n), S(d, sid#, n), E(sid#, c#, g), T(iid#, c#), TA(sid#, c#). Some principles: students can take many courses, and can be TAs in many courses. Professors can be instructors in many courses. You can assume all keys are unique, and course titles are unique.

(a) Find all courses(including all information) offered in 2005.

(b) Find all students’ names in the course “Database”.

(d) Find all people’s names who are students, but not TAs. For SQL, please list your answer by department.

(e) Find all courses’ titles which are taught by more than one (>1) teacher. (by SQL only)

(f) Find John’s course no. which he has grade above 80 (included).

(g) Find all students (sid#) who have average grades above 70 (included). (by SQL only)

(h) Find the students (names) who take all courses offered in 2005.

2. p.129, 3.13

3. p.185, 4.4.

(suggested solution)

Homework 3

Final Project

(Test Data)

Misc

1. Asnwer to textbook page 153

In textbook page 153:

select distinct S.customer-name

from depositor as S

where not exists ((select branch-name

from branch

where branch-city = 'Brooklyn')

except

(select R.branch-name

from depositor as T, account as R

where T.account-number = R.account-number and

S.customer-name = T.customer-name))

cannot be changed to

select distinct S.customer-name

from depositor as S

where not exists ((select branch-name

from branch

where branch-city = 'Brooklyn')

except

(select R.branch-name

from account as R

where S.account-number = R.account-number))

In the second one, for each depositor S, a check of account number equality is done; i.e.,

selecting all R's where S.account-number = R.account-number for a given depositor S.

Clearly, it is not what we want. We want to collect all accounts for a given name.

(Revised in Jun 10)