Description
You will be creating a database to keep track of Tour that vacationers have reserved. The data is located in the
spreadsheet attached to the assignment, CS3431-A23 Assignment 1.xlsx. There are 4 tables, one on each
spreadsheet tab: ReservedTour, Customer, Tour and Guide. The first tab is an example of how to automatically
generate the insert statements so you do not manually type every line.
Use a text editor to create your SQL commands below.
Note: because of the referencing done, there is a specific order that you need to follow in the creation of the
tables. Likewise, there needs to be a specific order in which the tables are dropped when the code is re-run.
1. Creation of the tables and inserting of data. Create a file named tour1a.sql for the following SQL
commands.
a. (4 points) The first four commands will delete the ReservedTour, Customer, Tour, and Guide
tables (not necessarily in that order) so you can run your SQL files over and over.
b. (10 points) Write the SQL commands to create the four tables following the instructions below. All
constraints must be named. For each table, the field name and datatypes are given in the
spreadsheet. Use the exact given table and field names.
i. The first column of each table is the primary key.
ii. In the Guide table, the driverLicense field is unique and must be non-null.
iii. The ReservedTour table contains 3 foreign keys referencing the other three tables.
iv. In the Tour table, the VehicleType is constrained to be boat, bus, or car.
v. For the Customer, Tour, and Guide tables, the referential integrity should be set so if a
record is referenced by the ReservedTour table, the referring field in ReservedTour will be
set to null when the record is deleted.
c. (2 points) Create sequences for the primary keys of the four tables. Be careful with the values for
the ReservedTour table.
d. (4 points) Insert the spreadsheet data into the tables. Remember to use sequences for the values
for the primary keys.
2. Create a file named tour1b.sql for the following SQL commands. When displaying the order of the fields in
the result tables, follow the order given in the instructions. For your own use, create the database schema
so you can more easily see what fields are in which tables. PK indicates it is part of the primary key. FK
indicates it is part of the foreign key. For example:
Artist(firstField PK, secondField FK, thirdField, etc.)
Artwork(firstField, secondField, etc.)
a. (20 points) Write an SQL command that decreases the price of tours that are $100 or more by
$100 but only in the states of California and New York.
b. (20 points) List all of the guides who are doing either a Freedom Trail tour or are a junior tour
guide. Include the guide’s first name and last name. Sort the results in alphabetical order by last
name and then by first name. Only for this part, use natural joins. Use the command
select distinct
to remove duplicates.
c. (20 points) List tours that have customers who are over 65 OR tours that use boats. Include the
travel date, customer first name and last name as a single field called ‘fullName’, customer age,
and tour name. Sort by tour name and then by customer full names. Use theta joins (join … on …)
d. (20 points) List the five tours and the names (first and last) of the guides who will be giving those
tours. Include the tour name and the guide’s first and last name. Sort by tour name and make
sure there are no duplicate listings of the guides for the same tour. For example, the Alcatraz Tour
should not list Liam Rodriguez twice, but Liam can also appear as the tour guide for one of the
other tours. Use theta joins (join … on …) and use the command
select distinct
to remove duplicates.
