· 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 1 the folder will be "lab1".
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:
Lab assignment work must be submitted electronically by midnight at the end of the day that it is due. Programming work submitted after the deadline but before the "late deadline" -- midnight 48 hours after the deadline -- will be accepted but penalized 50%. No lab assignment work will be accepted after the late deadline.
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 develop a familiarity with PostgreSQL, create tables and populate them with data. A database schema is given to you.
(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.
Create your first database using the shell command:
createdb username
where "username" is your cats login username.
Then you can access your database with the command:
psql -a
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.)
You may create other database names, but they must all start with your username. (By the way, username "wan" should not create databases starting with "wang"!)
To start a command line interface to PostgreSQL connecting to a different database, use the command:
psql -a databasename
where "databasename" is the name of your database (which should begin with your cats username).
(b) The schema provided to you is of a hotel management
database. The following are the main assumptions:
Main Assumptions:
a) Today is Feb 10, 2001.
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:
Turn in a listing showing the contents of the files you created, the successful loading of the data into PostgreSQL, and the execution of "SELECT *" commands to show the contents of each relation.
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. |
|
create.log |
A record of your session creating your tables. |
|
data.script |
The execution script file(s) you used to populate your database. If you used a separate file for each table use the table name to begin the file name: like product_data.script (for relation product). Also clearly indicate the new data records that you have added. |
|
data.log |
script log (i.e., a recording) of your session using data.script to populate your DB and your execution of SELECT queries on each table. |
To submit these files use the syntax:
submit cmps180-pk.f03 lab1 create.log data.script data.log
or lab1-late if your project part is late.
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;
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 |
The execution script file you create can consist of most any series of commands which you could enter following the psql prompt. This includes all of the SQL commands which you will be using to create, modify and test your data base. Examples include the CREATE TABLE and SELECT commands. Just like when using the psql command line interface you must terminate each SQL command in your script file with a semicolon. \i and \q do not need semicolons.
If you are recording your session using the script command described above into a script log then it is useful to start psql using the -a option so that all commands included in your execution script file will be echoed to the console and thus to your script log file. Here is an example script file that creates a table (relation) named products:
CREATE TABLE products (
productID INT,
name VARCHAR(80),
price NUMERIC(10,2),
retailPrice NUMERIC(10,2)
);
Here is an example script file that loads four tuples into the table (relation) named products created using the previous script file:
COPY products FROM stdin USING DELIMITERS '|';
1419|American Greetings CreataCard Gold V4.0|21.49|25.24
1424|Barbie(R) Nail Designer(TM)|20.74|25.99
1427|Panzer Commander|21.99|30.24
1431|Riven: The Sequel to Myst|31.99|40.24
\.
This is the format you will use for the files that load data into your tables. The USING DELIMITERS '|' and the use of '|' as a delimiter is optional. The default delimiter is the tab character. The delimited data on each line must match the attributes and their types in your table in a one to one manner and in the order they were defined in your CREATE TABLE commands. The COPY data must be terminated with '\.'
For testing, some students have found it convenient to have a separate script file to populate each of the tables. I also find it convenient to have a single script file to create all of my tables and another one to drop all of my tables. Examples follow.
-- Sample Script file to Create and
-- Populate a BEERS DB
-- print out the current time
SELECT timeofday();
CREATE TABLE Beers (
name VARCHAR(30),
manf VARCHAR(50)
);
COPY Beers FROM stdin USING DELIMITERS '|';
Coors|Adolph Coors
Coors Lite|Adolph Coors
Miller|Miller Brewing
Miller Lite|Miller Brewing
MGD|Miller Brewing
Bud|Anheuser-Busch
Bud Lite|Anheuser-Busch
Michelob|Anheuser-Busch
Anchor Steam|Anchor Brewing
\.
CREATE TABLE Bars (
name VARCHAR(30),
addr VARCHAR(50),
license VARCHAR(50)
);
COPY Bars FROM stdin USING DELIMITERS '|';
Joe's|123 Any Street|B7462A
Sue's|456 My Way|C5473S
\.
CREATE TABLE Sells (
bar VARCHAR(20),
beer VARCHAR(30),
price REAL
);
COPY Sells FROM stdin USING DELIMITERS '|';
Joe's|Coors|2.50
Joe's|Bud|2.50
Joe's|Bud Lite|2.50
Joe's|Michelob|2.50
Joe's|Anchor Steam|3.50
Sue's|Coors|2.00
Sue's|Miller|2.00
\.
CREATE TABLE Drinkers (
name VARCHAR(30),
addr VARCHAR(50),
phone CHAR(16)
);
COPY Drinkers FROM stdin USING DELIMITERS '|';
Bill Jones|180 Saint St.|831-459-1812
Kelly Arthur|180 Alto Pl.|650-856-2002
Fred|1234 Fifth St.|831-426-1956
\.
CREATE TABLE Likes (
drinker VARCHAR(30),
beer VARCHAR(30)
);
COPY Likes FROM stdin USING DELIMITERS '|';
Bill Jones|Miller
Bill Jones|Michelob
Kelly Arthur|Anchor Steam
Fred|MGD
\.
CREATE TABLE Frequents (
drinker VARCHAR(30),
bar VARCHAR(30)
);
COPY Frequents FROM stdin USING DELIMITERS '|';
Bill Jones|Joe's
Bill Jones|Sue's
Kelly Arthur|Joe's
\.
-- Execute some SELECT queries--
SELECT * FROM Bars;
SELECT * FROM Drinkers;
-- print out the current time
SELECT timeofday();
DROP TABLE Beers;
DROP TABLE Bars;
DROP TABLE Sells;
DROP TABLE Likes;
DROP TABLE Frequents;
DROP TABLE Drinkers;