· 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 5 the folder will be "lab5".
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 goal of this assignment is to learn embedded SQL to interact with the Hotel Management database 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:
Please see Recording Your Session below for a guide to preparing output to be submitted for this and subsequent project parts.
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. |
| program.x |
Your program code (C, C++, Java) |
| Makefile |
A makefile to compile your program |
| execution.log |
A script log illustrating a successful run of your program from the console using your database. Your script should be sufficient to convince us that all of your commands run successfully. |
To submit these files use the syntax:
submit cmps180-pk.f03 lab5 README program.x Makefile execution.log
or lab5-late if your project part is late.
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.
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 |