Description
You will be creating an expanded version of the database from assignment 1. The
data is located in the spreadsheet CS3431-A23 Assignment2.xlsx. There are now 5
tables of data, one on each spreadsheet tab: ReservedTours, Customers, Tours,
Locations, and Guides. Changes from the previous assignment are
highlighted with a red background and consist of the following:
1. ReservedTours now has a price field
2. There is a new Locations table
3. Guides now has a licenseType field
Part 1
Creation of the tables and inserting of data. Create a file named tour2a.sql for the
following SQL commands.
1. (2.5 points) The first five commands will drop the ReservedTours, Customers,
Tours, Locations and Guides tables so you can run your tours2.sql file over and
over. Do not use “Cascade Constraints” with your drop commands.
2. (2.5 points) The next set of commands will drop the sequences you created.
3. (15 points) Write the SQL commands to create the 5 tables following the
instructions below:
a. Create the tables with the given table names, field names, and datatypes
given in the spreadsheet.
b. The first column of each table is the primary key.
c. Create sequences for primary keys except for the Location table.
d. Include all of the constraints from assignment 1 (given below for your
convenience):
i. In the Guides table, the driverLicense field is unique and must be
non-null.
ii. The ReservedTours table contains 3 foreign keys referencing the
other three tables.
iii. In the Tours table, the vehicleType is constrained to be boat, bus,
or car.
iv. For the Customers, Tours, and Guides tables, the referential
integrity should be set so if a record is referenced by the
ReservedTours table, the referring field in ReservedTours will be set
to null when the record is deleted.
e. The new Locations table contains 1 foreign key referencing the Tours
table. If a tour is deleted then the records referencing the deleted records
are automatically deleted as well. Examine the data and fill in the values
for the blank TourID fields.
f. ReservedTours now has a new price field that is initially null for every
record.
g. In the Customers table, the age field must be non-null and the phone
number must be unique.
h. The licenseType field in the Guides table can only have the values ‘land’,
‘sea’, or ‘both’. Land licenses permit the use of buses and cars. Sea
licenses permit the use of boats. ‘Both’ licenses permit the use of buses,
cars, and boats.
Before you proceed to the next section, you will want to create a high level
summary of the database schema (refer to section 2.2.2 in your textbook) so you
can see all of the tables and its attributes in one place. You use PK and FK after a
field name to indicate primary and foreign keys:
Books (ISBN PK, FirstName, LastName, Title, PubID FK)
Part 2
Use a text editor to create tour2b.sql that will include the SQL commands below.
You may want to build the queries part-by-part and seeing the intermediate results
before putting together the entire query. Create a single SQL query for each of the
5 problems listed below. Use theta joins, not natural joins for your solutions.
1. a. (10 points) List for each level of guide – junior guide, guide or senior guide –
how many mismatches there are between the required tour’s vehicle type and
the guide’s license type.
b. (5 points) Include as a comment below your SQL code for 1a, why using
natural joins gives you the wrong answer for this problem.
2. (10 points) Update the ReservedTours prices based on the prices in Tours.
Note: You will need to create a nested SELECT statement in the SET clause after
the equal sign.
3. (20 points) For each customer, list the first name, last name, and total amount
being spent for land-based tours. Format the price so it is displayed with two
decimals and with the heading TotalLandPrice. If the price is less than 1.00,
then display a leading zero. For example, display 0.75 instead of .75. Sort by
price in descending order so we can see who has paid the most for land-based
tours. Sort by lastname and then first name.
4. (15 points) For each vehicle type needed for a tour, list the number of locations
(name the column Places) that vehicle type will be used for a given location
type. For example, how many historic locations will require a boat or how many
museums will require a bus? Only display those results where the number of
places is greater than one.
5. (20 points) List the full name of a guide (name the column GuideName), and the
total price with a tax of 10% of the tours that guide is responsible for (name the
column TotalRevenue). The TotalRevenue should be formatted to display a
comma for the thousands place, only two decimal places, and for amounts less
than a dollar there will not be a zero to the left of the decimal.
