401/410 Assignment 2 Solution

Your task in this assignment is to write a script to create a small database (three linked tables).

Steps:

Write a script to do the following. (Do this one part at a time, testing the partial results by inspecting the tables in SQLDeveloper, and not going on the next part until you have the previous part working.)

1. Define the following database, representing hotels, travelers who may stay in the hotels, and reservations that the travelers make at hotels.

TRAVELER(ID, FirstName, LastName, Phone)

HOTEL(ID, HotelName, City)

RESERVATION(TravelerID, HotelID, StartDate, EndDate, Confirmation)

Traveler IDs should be exactly nine characters long, hotel IDs should be exactly four characters long, and phone numbers should be exactly ten characters long; reservation confirmation codes should be at most ten characters long. You may decide on appropriate maximum lengths for names of customers, hotels, and cities.

Define the primary keys as indicated in the schemas above. Also define TravelerID in RESERVATION to be a foreign key referencing ID in TRAVELER, and HotelID in RESERVATION to be a foreign key referencing ID in HOTEL. (Thus you will have to create TRAVELER and HOTEL before RESERVATION.) We can’t assume that the confirmation codes are unique, since they are supplied by the hotels when reservations are made we can’t be sure that different hotels won’t use the same codes. (All of the confirmation codes supplied by a single hotel will be distinct, but verifying that would require a more complicated constraint than we can implement right now…)

In order to avoid conflicts, start your script file with DROP TABLE commands for all three tables (since RESERVATION contains foreign keys, you will have to drop it first). Run your script and look at the columns and constraints of each table to verify that they have been created correctly. Now you can set up trips for yourself and a couple of your friends…

2. Populate the HOTEL table with names and cities of at least four actual hotels anywhere in the world (you can make up the IDs, as they are internal to the database). Then populate the TRAVELER table with information for yourself and at least two of your friends. (For privacy reasons, IDs and phone numbers should be made up.) Look at the data in each table to verify that they have been populated correctly.

3. Next, insert at least five records to the RESERVATION table. There should be at least one record for each traveler, and at least one traveler should have two or more records in the RESERVATION table. Be sure that each traveler is staying at only one hotel at a time – that is, a traveler’s reservation at a second hotel can’t begin before their previous reservation at the first hotel ends. (This particular constraint – insuring that reservations do not overlap – is also too complicated for us to implement right now.) Every record in RESERVATION must have a starting date, but the ending date can be NULL for the traveler’s last reservation in the table. Look at the data in the table to verify that it has been populated correctly.

4. Display the contents of each table by adding commands of the form SELECT * FROM TABLENAME ; to the end of your script file. Run the complete script and save the complete contents of the output window to a text file. (Clear the window before running the script for the last time so that only the output of the last run of the script is displayed and saved.)

5. Include a comment at the top of your script file giving your name, the course number and your section number, the assignment number, and the date of submission, e.g.: