CS180 - Database Systems
Fall 2003

Lab Assignment #4
Due Friday November 7, 2003 (Midnight)

LOGISTICS AND LATE POLICY

· Turning in your work: The project for CS 180, Fall 2003 will consist of a sequence of lab assignments involving database programming (including such tasks as create databases, populate them with data, modify data, write SQL queries). Unless otherwise specified, this lab assignment and all subsequent ones will be turned in electronically. We will use submit. Submit is accessed from your cats account. For information on using submit, enter "submit -m" from the console. The class directory is "cmps180-pk.f03". The folder for each assignment will be "lab" followed by the assignment number. For lab assignment 4 the folder will be "lab4".

Please keep in mind that the deadlines for submitting lab assignments are separate from the deadlines for written homework assignments. The following late policy applies to this and subsequent lab assignments:

One caveat: students must use ssh (both versions 1 and 2 will work), as telnet is not enabled. SSH is an encrypted (safer) method of connecting to a remote host, as opposed to telnet which transmits and receives data unencrypted. Free and legitimate SSH software is available for almost all computing platforms.

Once connected to db.ic.ucsc.edu, students will have full access to their CATS home directories via AFS, much the same way as when logged onto teach.ic.ucsc.edu and hawking.ic.ucsc.edu.

THE PROJECT

Lab Assignment 4

The goal of this assignment is to learn to write complex SQL queries that may involve aggregate operator and the GROUP BY clause. Here we revisit the Hotel Management database schema of Assignment 1.

(a) Familiarize yourself with the PostgreSQL relational DBMS by reading the document Managing a Database in the Interactive PostgreSQL documentation, logging into PostgreSQL, trying some of the examples in the document, and experimenting with the various commands. You don't need to turn anything in for this part. The PostgreSQL web page has links to documentation and other information. There's also a PostgreSQL At A Glance document describing PostgreSQL's features.

Access the database that you created for Assignment 1 with the command: 

psql -a <database-name>

which will start a command line interface to PostgreSQL connected to the database with your username. (The -a is so input is echoed to the console for saving a script log. If you don't want your input echoed, you can omit the -a, but please do include it when you are running an execution script to hand in.)

(b) The details of the hotel management database schema are repeated here for convenience. The following are the main assumptions:

Main Assumptions:

a) Today is Feb 10, 2001. The database stores information concerning hotels belonging to the Hilton hotel chain.

b) The following changes occur in the hotel world.

b1) Reservations are made for n consecutive days for a particular type of Room (given yes/no as an answer and returning the room price to the costumer, if the reservation was successful).

b2) Reservations are cancelled.

b3) Guests check in (with and without reservations)

b4) Guests check out, also paying their bills.

b5) Room prices change daily;

b6) Reservations that refer to the past and information concerning completed stays are deleted from the database

c) Other important information includes:

· prices are the same for the same room category for the same hotel on the same day of reservation; however, if you make a reservation on different days you may get a different price.

· guests can reserve / stay in multiple rooms, and have to pay for all of those, when they checkout.

· reservations are performed for n consecutive days.


The Relational Schema of the Hotel Management Database:

Category (cname)

Hotel (hid, city, state, country)

Hotel_Menu (hid, cname, day, rate)

//cname: category name, hid: hotel id,

//rate: unit price, only good for room rate, not for service. The charge for service is provided //in Service_to

Hotel_Room (hid, no, cat)

//no: room no

//cat: category

Person (ssn, name)

Room_Stay (hid, no, ssn, from_d, to_d)

//from_d: starting date; to_d: end date

Room_Reserve (hid, no, ssn, from_d, to_d)

Service_To (ssn, hid, stype, day, charge)

//stype: type of services

//charge: one time charge for this service to this person at that day


Note: The underlined attributes indicate the primary key. In some cases, the primary key is composed of a combination of attributes e.g. See table 'Hotel_Menu' where the primary key is a combination of attributes hid, cname and day. The attributes which are primary keys and that also occur in other tables should be defined as foreign keys for the other tables. e.g. in 'Hotel_Menu', cname is a 'foreign key' whereas it is a 'primary key' in table 'Category'. The attribute 'cat' in 'Hotel_Room' is a foreign key and refers to attribute 'cname' in table 'Category'. Also 'stype' in 'Service_To' is a foreign key and refers to attribute 'cname' in table 'Category'. Define the foreign key such that deleting the record in the table where the attribute occurs as primary key will result in deletion of the records from the tables where the same attribute occurs as a foreign key (Hint: See the different options for defining foreign keys during table creation).


Assume the following for the datatypes of the attributes:


(a) Check if all the relations are populated with the data provided (one file for each relation of the relational schema): ins_category,ins_hotel, ins_hotelMenu,ins_room, ins_person,ins_stay, ins_reserve,ins_service . Flush off an extra data present in the relations. For example, records that you may have inserted. Alternatively, flush off all the data currently present and reload the data into the relations.

(b) Write SQL queries for the following:

1) How many single and double-one bed rooms does the Hilton in Houston have, and how many of those rooms are reserved for March 1, 2001. (Note: This should be a single query)
2) Print the bill for Mr. John --- when he checks out from the Hilton Houston.
3) What were the total charges of the Hilton in Houston for the following items for Feb 10, 2001: double-one rooms, single rooms, telephone, restaurant, movie, and bar charges.
 Extra Credit: What were the "total earnings" of the Hilton in Houston for the following items for Feb 10, 2001: double-one rooms, single rooms,
                    telephone, restaurant, movie and bar charges.
4) List all current guests that spend more than $20 on the hotel services of the Hilton Houston (exclude room charges).
5) Find out how much Tom will have to pay if he extended his stay at Hilton Kingsville by a week and used all the hotel services during that week. Assume the room charges and the service charges for the whole extra week are the same as Feb 10, 2001.

Turn in your queries and the results of your queries.

What to turn in

Please see Recording Your Session below for a guide to preparing output to be submitted for this and subsequent project parts.

Components (a) and (b) of this project part each tell you what should be recorded in the script log that you turn in. In this and all subsequent project parts, the material you turn in should be clearly formatted and delineated, and should include comments for any aspects that are not crystal clear. Poorly assembled or documented material will not receive full credit, even if it is correct. Other than comments, truncation, and simple formatting, it is Academic Dishonesty to edit scripts before turning them in.

For this assignment the following files should be turned in electronically using your cats account and the submit program:

README

Please give your name, project part number, course number, date, the title of your project, a list and description of the files you are submitting, and any other information that will be useful for the grader.

Queries
  A file containing the SQL queries.
query.log
 A file(s) containing the results of the queries.

To submit these files use the syntax: 

  submit cmps180-pk.f03 lab4 README Queries query.log

or lab4-late if your project part is late.

Maintaining your databases

You will be using this database for a later assignment. We suggest that you keep the database after submitting this assignment.

For the duration of the project, we suggest that you establish some kind of routine that includes reloading your database from the files created in this project part each time you want to get a "fresh" start with PostgreSQL. To get rid of a table called T, issue the command: 

  drop table T;

If you want to get rid of all tuples in T without deleting the table itself, issue the command: 

  delete from T;

Recording Your Session

There are several methods for creating a typescript to turn in for your programming assignments. The most primitive way is to cut and paste your terminal output and save it in a file (if you have windowing capabilities). Another method is to use the Unix command script to record the terminal interaction. The script command records everything printed on your screen. The syntax for the command is 

    script [ -a ] [ filename ]

The record is written to filename. If no file name is given, the record is saved in the file typescript. The -a option allows you to append the session record to filename, rather than overwrite it. To end the recording, type 

    exit

For more information about script, check out its man page.

Creating and running script files from psql

You will be using the psql command line interface to interact with your data base. See Managing a Database for information on starting using psql. Script files are text files of psql commands which can be executed like a batch file using the the \i command in psql. The syntax is \i filename entered following the psql prompt, where file name is the complete (case sensitive) name of the script file you desire to run.

To run your execution script data.script and save the script log in the file data.log, do the following:

script data.log

to start saving the script log

psql -a databasename

to run PostgreSQL's command line inteface using your database

\i data.script

to import an execution script

\q

to exit pqsl (PostgreSQL's command line interface)

exit

to stop saving the script log