Assignment 2

CS 640 Winter 2013

Due: Tuesday, March 5, 2013
Hand in: via email

Overview

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

Schema for the example database

Part 0 (optional)

To test your solutions, you may want to install DB2 (or another RDBMS). 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 following command (which may take quite a while to execute):

create database cs640

Then, to connect to that database, issue the command:

connect to cs640

You will next need to create and populate the database tables. DB2 commands for creating and populating the tables for our schema can be downloaded from the course web site. You may change the test data in any way you wish.

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 - SQL Queries (31 points)

Write SQL queries that implement each of the following questions. You may find the following links useful:

For readability you must use line breaks and indentation in all your queries. (When using DB2, to indicate 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.

  1. For all students (reported by their name) list all terms during which they were (and are) enrolled in classes. For each student each relevant term must be reported only once (assuming that students can be enrolled in multiple classes during the same term). (1 point)
  2. Print a list of student numbers, student names, and final grades for each past class taught by Smith, ordered by term and, within each term, by course name (both of which should also be reported in the results). (2 points)
  3. List the course numbers for those courses that were taught at some time but not in W13. (2 points)
  4. How many students received a mark in CS246? (2 points)
  5. List the classes (course number, term, section) for which at most 3 students have been enrolled. (4 points)
  6. Give a list of rooms that are used in W13 fewer than 3 times on Mondays or more than twice on Tuesdays. (4 points)
  7. What was the average grade in any past class when someone named Lee was enrolled in that class? (4 points)
  8. What are the names of professors who have taught the most number of distinct courses, and how many courses did they teach? (4 points)
  9. Print a list of student numbers, student names, and cumulative average grades in decreasing order of cumulative average grade for all students who have taken at least three courses and have a cumulative average grade over 85. (4 points)
  10. Give the course data for a student transcript showing the course number, term, and grade (if completed) of each course (completed or in progress) taken by the student whose number is 4457, ordered by term. That is, the result must include all courses in progress (in which case the reported grade is null, denoted by a dash by DB2) (4 points)

Part 2 - Relational Algebra (14 points)

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

  1. Print a list of student numbers, student names, and final grades for each past class taught by Smith. (2 points)
  2. Which students (reported by snum, sname, and year) received a mark in both courses, CS341 and CS466? (2 points)
  3. List the course numbers for courses that were taught at some time but not taught in W13. (2 points)
  4. Which third year students have taken all classes offered by Smith but have not enrolled in any classes offered by Jones? (4 points)
  5. 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 whose student number is 4680. Show the value -1 for courses in progress. (4 points)

Acknowledgement: This assignment has been adopted from Frank Tompa's earlier version of this course.

Feb. 14, 2013. Olaf Hartig