CSCI 305: Introduction to Database Systems

Activities on Relational Algebra

Acknowledgment: This set of exercises and the related lecture notes is adopted from Professor Shiyong Lu of Wayne State University with permission.

You are to complete the following exercises on relational algebra. Refer to the lecture notes from Friday April 6th through Monday April 9th, as well as the examples discussed on Wednesday April 11th.

Given following three relations,

• Professor(ssn, profname, status)
• Course(crscode, crsname, credits)
• Taught(crsname, semester, ssn)

come up the queries the following questions in two forms, relational algebra and SQLite.

Here is the SQLite commands that create the database and populate it with some information that you can use to test your queries.

1. Return those professors who taught 'CSCI203' and 'CSCI204' in the same semester.
2. Return those professors who taught 'CSCI204' or 'CSCI315' but not both.
3. Return those courses that have never been taught.
4. Return those courses that have been taught in two semesters.
5. Return those courses that have been taught at least twice.
6. Return those courses that have been taught by at least three different professors.
7. Return the names of the professors who ever taught 'CSCI204'.
8. Return the names of the senior professors who ever taught 'CSCI204'.

Note: Do the activities for the remaining class period. Complete as much as you can. Submit the work by Wednesday April 18th.