Written Homework Assignments

Written homework assignments are primarily to give you some practice answering exam-like questions. They are due at the beginning of class, and I will not accept late written homework assignments. However, I encourage you to try these even if you don't submit them for a grade as they are similar to the types of questions you will see on exams.

For all written homework assignments you are welcome, and encouraged, to work in small groups (2 or 3 students) either trying to solve the problems together or trying to verify each other's solutions prior to submitting them. If you solve the problems as a group, you should submit your own write-up of the assignment and list the other students with whom you worked.

Homework 1

Due: at the beginning of class on Wednesday September 26

Do the following problem from Chapter 9 of the text (p.335):

9.4, 9.5, 9.6, 9.7, 9.17

Homework 2

Due: at the beginning of class on Wednesday October 3

Do the following problem from Chapter 2 of the text (p.52):

2.2, 2.5

Homework 3

Due: at the beginning of class on Wednesday October 10

Do problem 3.10 from Chapter 3 of the text (p.96):
Show the SQL stmts for converting the following ER diagram to relations. Indicate any constraints in the ER diagram that you cannot capture in the SQL statements.

Homework 4

Due: at the beginning of class on Friday November 9

Do the following problems from the book page 127-128:

4.2
4.3 (parts 1-9, and just express each query in Relational Algebra)
try parts 10-12 for fun/as an extra challange

Homework 5

Due: at the beginning of class on Wed December 5

Overview

This assignment is designed to give you some practice writing SQL queries. You will use Postgress, which is a relational database management system. Postgress has an SQL interface for running queries.

The Database Schema

The relations for this assignment have the following schema (the primary key is in italics):

Student(Snum, Name, Major, Level, Age)
Class(Name, Time, Room, Fid)
Enrolled(Snum, ClassName)
Faculty(Fid, Name, Dept)

Postgress's SQL interface

To use Postgress's SQL interface:

Start by running the postgress interactive terminal, 'psql' and attaching to the postgress server on milk and the cs44db (the password is cs44):

	% psql -U your_user_name -h milk cs44db 

	# I'd do the following:
	% psql -U newhall -h milk cs44db 

	Welcome to psql 7.4.17, the PostgreSQL interactive terminal.

	Type:  \copyright for distribution terms
	       \h for help with SQL commands
				 \? for help on internal slash commands
				 \g or terminate with semicolon to execute query
				 \q to quit

You can List the contents of the 4 Relations by issuing Select queries on them:

	cs44db=> SELECT * 
	            FROM Student;
	cs44db=> SELECT * 
	            FROM Class;
	cs44db=> SELECT * 
	            FROM Enrolled;
	cs44db=> SELECT * 
	            FROM Faculty;

You can look at on-line documentation about using postgress: Postgress Documentation
or I think it is just as easy to use the help feature:
```
  # to see syntax for the SELECT SQL command:
  cs44db=> \h select

  # to see list all SQL commands:
  cs44db=> \h 
```
To exit postgress enter '\q' in the psql interactive terminal:
```
	cs44db=> \q
```

The Queries

Write the following queries in SQL and run them on the cs44db on Postgress. You can capture Postgress's output (all terminal input and output) by first running script, then running psql .... After running all your queries and exiting psql, type exit to quit script. You now have a file named typescript that you then clean up with dos2unix and edit with comments indicating which query corresponds to each chunk of output.

Queries 1, 3, 6 and 9 should also be written in Relational Algebra. If a query cannot be expressed in one or both of the query languages explain why it cannot.

Find the names of all CS Majors (Major = 'CS') who are enrolled in the course 'Math06'.
Find the name and age of all Junior (Level = 'JR') Math Majors.
Find the names of all classes that either meet in room R300 or are taught by 'Kelemen'
Find the names of all CS Majors (Major = 'CS') who are enrolled in the course 'Math21' and are older than some Math freshman (Level='FR').
Find the names of all pairs of students who are enrolled in some class together.
Find the names of faculty members who teach in every room.
Find the names of faculty members who teach in every room in which some class is taught in the time period 'MWF10-11'.
For each Level, print the Level and the average age of students of that Level.
Find the names of all students who are not enrolled in any class taught by 'Kelemen'.

What to Turn In

At the beginning of class, hand in a printout of your script file of Postgress's output for the SQL queries and your written answers to the relational algebra queries. Clean-up the typescript file and add comments indicating which query is which in the ouput:

################################################################
# Query 1: Find the names of all CS Majors (Major = 'CS') who 
#          are enrolled in the course 'Math06':
#
cs44db=> SELECT name
 ...