Class Notes (837,610)
Canada (510,370)
CS 348 (13)
Ihab Ilyas (11)

Assignment #1 Winter 2010 - solutions available in other note

3 Pages
Unlock Document

Computer Science
CS 348
Ihab Ilyas

CS348: Introduction to Database Systems (Winter 2010) Assignment 1 (due Friday, February 12 at 5:00 pm) 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. Instructions Please read these instructions carefully, as not following them may result in mark deductions. Your submission should include online answers for Part 1 and paper-based answers for Parts 1 through 3. Queries 1 through 15 are worth 5 marks each. Part 1 - 50 Marks For the first part, you must use your Unix accounts and DB2 to compose and evaluate several SQL queries. Commands for defining and partially populating the tables for this schema can be downloaded from the course web site. You are to submit online a listing of SQL queries that implement each of the requests for information given below. You are also required to submit offline in the course assignment boxes a printed copy of your queries that you submitted on the online submission system (so that you can receive written feedback on your queries). As is true for any programs, you must use line breaks and indentation appropriately in all your queries for readability. For the online submission, put all your queries (in the order specified in this assignment) in one file named A1.sql. Do not include any set schema commands. We should be able to run the queries using the command db2 -f A1.sql. To submit your assignment from your Unix account, use submit cs348 A1 A1.sql Write SQL queries that implement each of the following. 1. Print a list of student numbers, student names, and final grades for each past class taught by Smith, ordered by term and then course name (both of which should also be printed in the list). 2. How many students have received a mark in CS246? 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 whose student number is 4321, ordered by term. Show the value -1 for courses in progress. 4. List the course numbers for courses that were taught at some time but not taught in W07. 5. Give a list of rooms that are used in W07 fewer than 3 times on Mondays or more than twice on Tuesdays. 6. What was the average grade in the section of any past course when Lee was enrolled in that section? (Note that a section is distinguished by its term and
More Less

Related notes for CS 348

Log In


Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.