CS640: Introduction to Database Systems

(Winter 2012)

Assignment 2 (due Tuesday, February 28)

Overview:

For this assignment you must compose and evaluate several queries for a database that records information about courses. The schema for the database is illustrated by the following relational database diagram that includes an indication of primary and foreign key constraints in the manner discussed in class. Note that the schema stores information about both ongoing and past classes for a course. Also note that no marks are recorded for any enrollment of an ongoing class, and that, for a past class, a mark is recorded for each of its enrollments. Finally, you may assume that each class has at least one enrollment.

Data Diagram

Part 0 - optional

If you wish to test your solutions, you may want to first install DB2. After installing DB2 on your machine (which takes a little time), open the DB2 command window. To create a new database named cs640, issue the command

create database cs640

(This will take a little time to execute.) Then to connect to that database, issue the command

connect to cs640

You will next need to create and populate several database tables. DB2 commands for defining and populating the tables for this schema can be downloaded from the course web site. You may change the test data in any way you wish. (Henry Zaccak has kindly provided equivalent commands to work with MySQL.)

Having copied and pasted these commands into the DB2 window so that they execute successfully, you can see the list of defined tables by using the command

list tables

You can now enter any SQL statements. For example, to examine the contents of the table class:

select * from class

To exit DB2, type

quit

Part 1 - 50 Marks

Write SQL queries that implement each of the following. (An overview of SQL can be found at < http://www.firstsql.com/tutor2.htm>.) As is true for any programs, you must use line breaks and indentation appropriately in all your queries for readability. (When using DB2, to signal that a command is continued on a subsequent line, end the line with a backslash (\), such as was done for the commands to create and initialize the tables.) You are to submit the queries only, not the answers that you get for the test database.

(5 marks each)

1.             Print a list of student names, course names, terms, and final grades for each past class taught by Chan, ordered by increasing course name and, within each course, by grades from largest to smallest.

2.             How many students were enrolled in each section of each course offered in the W07 term?

3.             Give the course data for a student transcript showing the course number, term, and grade (if completed) of each course taken by the student named King. Show the value -1 for courses in progress.

4.             How many courses were scheduled but never taken by any student in year 1?

5.             Give a list of rooms that were used in S08 at least twice on Mondays and never used on Fridays (in any term).

6.             Which courses have had more sections offered Mondays than on Tuesdays? (Note that a section is distinguished by its term and section number. It is "offered on a specific day" if there is at least one class scheduled on that day. )

7.             What was the average grade in each section of any past course taught either on Tuesday or at 7:00pm?

8.             What are the names of professors who have taught the most number of distinct courses, and how many courses did each of them teach?

9.             Which third year students have taken all CO courses but have not enrolled in any CS classes?

10.         Print a list of student numbers, student names, and cumulative average grades in decreasing order of average grade for all students who have taken at least one CS course and have a cumulative average between 70 and 80.

Part 2 - 25 Marks

Write queries in relational algebra that implement each of the following. Again, please use multiple lines and indentation for long queries to improve readability. (5 marks each)

 

11.         Print a list of student numbers, student names, and final grades for each past class taught by Chan.

12.         Which professors (name and department) have assigned marks below 50 and above 90 (possibly in different classes)?

13.         Assuming that the relation Minus1 has a single attribute named val and a single tuple with value -1, give the course data for a student transcript showing the course number, term, and grade (if completed) of each course taken by the student named King. Show the value -1 for courses in progress.

14.         List the course numbers for courses that were scheduled but never taken by any student in year 1.

15.         Which third year students have taken all classes offered by Smith in F07 but have not enrolled in any classes offered by Anderson (regardless of term)?