CSc 4710/6710 Homework Assignment 2
Spring 2001; Due: February 1, 2001
-----------------------------------

(1) Convert the ER-design for the student database of assignment 1 
    to relational database design using the 7-step algorithm presented
    in class. Submit the SQL Schema (which includes create table 
    statements along with all primary and foreign key constraints).
    You do not have to create the tables under Oracle.

(2) Create the tables of the company database under your Oracle
    account. For your convenience, the create table statements
    for this database can be downloaded from the course homepage.
    Note: The create table statements will not compile since there
    is a circular reference! You need to modify these statements
    (Hint: Use alter table statement) to make it work. The
    tables should be created with all constraints enforced.
    Submit the final SQL schema.  Do not populate the database yet!

(3) Once (2) is done, populate the database with the data. Once again,
    for convenience, I am providing the insert statements in the course
    home page. Once again, these statements will not execute properly
    due to the circular referential constraint. You need to modify these
    statements (Hint: use null values and update statements) so that
    all data is loaded. Submit the final SQL statements.

Important Note: You should not modify the schema after populating the
  database.

(4) Consider the following relational database scheme:
 
    drinker(dname)
    bar(bname)
    beer(rname)
    frequents(drinker,bar)
    serves(bar,beer)
    likes(drinker,beer)
 
   where frequents indicates the bars a drinker visits,
   serves indicates what beer each bar serves, and likes tells
   which beers each drinker likes to drink.
 
   Write relational algebra expressions to answer the following queries:
 
   1. Find bars that serve a beer that Charles Chugamug likes.
   2. Find drinkers who frequent at least one bar that serves a 
      beer they like.
   3. Find drinkers who frequent at least all those bars that Charles
      Chugamug frequents.
   4. Find drinkers who frequent no bar that serves a beer they like.  
   5. Find drinkers who frequent only bars that serve at least
      one beer they like.