CS 1555/2055 – Database Management Systems (Fall 2020)
Dept. of Computer Science, University of Pittsburgh
Assignment #5: Triggers, Functions, Procedures, and Transactions
Release: Oct. 15, 2020 Due: 8:00 PM, Oct. 29, 2020
Goal
The goal of this assignment is to gain familiarity with Triggers, Functions, Procedures, and
Transactions. In this assignment, your team will complete the first milestone of the term
project by setting up the database for the project schema and inserting provided sample-data
into your database.
The goal of your term project is to implement a flight reservation system for Pitt Tours,
the imaginary multi-billion travel agency company of the University of Pittsburgh. The core
of such a system is a database system and a set of triggers and ACID transactions. The
secondary goal is to learn how to work as a member of a team which designs and develops a
relatively large, real database application. In this first project milestone / assignment (HW5),
you will be given the descriptions and schemas of the database on which the system is based.
Description:
The database for Pitt Tours contains 7 tables plus an auxiliary table, explained as follows. You
are required to define all of the structural and semantic integrity constraints and their modes of
evaluation. For both structural and semantic integrity constraints (e.g., UNIQUE, NOT NULL,
CHECK), you must state your assumptions as comments in your database creation script.
Pitt Tours Tables
• Airline information
Airline (airline id, airline name, airline abbreviation, year founded)
PK (airline id)
Datatype
– airline id integer
– airline name varchar(50)
– airline abbreviation varchar(10)
– year founded integer
Here are some example tuples:
1 United Airlines UAL 1931
2 All Nippon Airways ANA 1952
3 Delta Air Lines DAL 1924
• Flight schedule information
Flight (flight number, airline id, plane type, departure city, arrival city, departure time,
arrival time, weekly schedule)
PK (flight number)
FK (plane type, airline id) → (Plane.plane type, Plane.owner id)
FK (airline id) → Airline.airline id
Datatype
– flight number integer
– airline id integer
– plane type char(4)
– departure city char(3)
– arrival city char(3)
– departure time varchar(4)
– arrival time varchar(4)
– weekly schedule varchar(7)
Here are some example tuples:
153 1 A320 PIT JFK 1000 1120 SMTWTFS
154 3 B737 JFK DCA 1230 1320 S-TW-FS
552 3 E145 PIT DCA 1100 1150 SM-WT-S
For departure and arrival cities we use the three letter airport code (e.g., PIT is for Pittsburgh,
DCA for Regan Washington D.C.). Departure and arrival times are given in “military”
notation (i.e., 10 pm is written as 2200). Weekly schedule is a seven-character string with
the days of the week (starting from Sunday). If instead of the corresponding letter there is a
- for a day, then there is no flight that day.
• Plane information
Plane (plane type, manufacturer, plane capacity, last service date, year, owner id)
PK (plane type, owner id)
FK (owner id) → Airline.airline id
Datatype
– plane type char(4)
– manufacturer varchar(10)
– plane capacity integer
– last service date
– year integer
– owner id integer
Here are some example tuples:
B737 Boeing 125 09/09/2018 2006 1
A320 Airbus 155 10/01/2020 2011 1
E145 Embraer 50 06/15/2019 2018 2
B737 Boeing 125 17/08/2018 2007 2
• Flight pricing information
Price (departure city, arrival city, airline id, high price, low price)
PK (departure city, arrival city)
FK (airline id) → Airline.airline id
Datatype
– departure city char(3)
– arrival city char(3)
– airline id integer
– high price integer
– low price integer
Here are some example tuples:
PIT JFK 001 250 120
JFK PIT 003 250 120
JFK DCA 003 220 100
DCA JFK 003 210 90
PIT DCA 002 200 150
DCA PIT 001 215 150
Prices are in dollars. High price corresponds to same-day travel which is traditionally over-
priced, whereas low price corresponds to travel where the departure and arrival dates are not
the same day. For simplicity, we do not deal with the multiple classes of service (e.g., First,
Business, Economy) or discounts that airlines typically have. In the general case, the price
for flying in one direction between a pair of cities is not the same as the price for flying in the
opposite direction. So to find the price of a round-trip ticket between DCA and JFK with
low price, we would add $90 and $100, which gives $190. If a flight is composed of multiple
legs, we only consider the end-points. For example, a round-trip flight from PIT to DCA with
a connection in JFK will cost $200 plus $215, which gives $415 under a high price policy.
Note: Any direct (individual) flight of a round-trip (e.g., PIT-JFK, JFK-PIT) on the same
day is charged at high price. In the case of a trip over different dates with a connection on
the same day, you consider low price for the two legs.
• Customer information
Customer (cid, salutation, first name, last name, credit card num, street, credit card expire,
city, state, phone, email, frequent miles)
PK (cid)
FK (frequent miles) → Airline.airline abbreviation
Datatype
– cid integer
– salutation varchar(3)
– first name varchar(30)
– last name varchar(30)
– credit card num varchar(16)
– credit card expire date
– street varchar(30)
– city varchar(30)
– state varchar(2)
– phone varchar(10)
– email varchar(30)
– frequent miles varchar(10)
PittTours must keep information on all its customers. Cid is the unique customer identifica-
tion number. A salutation can be one of three values Mr, Mrs and Ms. If the customer is a
frequent mile member of some airline then the frequent miles attribute will hold the abbre-
viation of that airline. As a frequent mile member, the customer will get 10% discount on
the advertised price when booking with their frequent airline. For simplicity, we assume each
customer can have at most one frequent airline. When booking flights involves connections,
this discount can still apply to the part of flight that belongs to the customer’s frequent airline
(e.g., for flights that have one connection and one of the two connecting flights belongs to
the frequent airline of the customer then the connecting flight that belongs to the frequent
airline will receive a 10% discount).
Here is an example tuple:
123 Mr John Walker ‘2 Pitt Ave’ Pittsburgh PA 4123456789 [email protected] ‘UAL’
• Reservation information
Reservation (reservation number, cid, cost, credit card num, reservation date, ticketed)
PK (reservation number)
FK (cid) → Customer.cid
FK (credit card num) → Customer.credit card num
Datatype
– reservation number integer
– cid integer
– cost decimal
– credit card num varchar(16)
– reservation date timestamp
– ticketed boolean
• Reservation Detail (reservation number, flight number, flight date, leg)
PK (reservation number, leg)
FK (reservation number) → Reservation.reservation number
FK (flight number) → Flight.flight number
Datatype
– reservation number integer
– flight number integer
– flight date timestamp
– leg integer
After verifying that a particular route has available seats, a reservation can be made for a par-
ticular trip for a customer. Reservation information for a particular reservation is maintained
in two tables. Information in Reservation table includes the total cost of the trip, the date
on which day the reservation was placed and a flag (ticketed) indicating whether a ticket has
been issued (the value is either ‘Y’ or ‘N’). Information in Reservation detail table includes
all the legs (starting from 0 and increased by 1 for consecutive legs) of the flight along with
the dates they are performed. Note that each reservation covers an entire trip, i.e., includes
the return portion for round-trip reservations. For simplicity, we do not allow a customer
to cancel or modify an existing reservation, although unpaid/non-ticketed reservations are
cancelled by the system automatically 12 hours prior to the departure (see trigger below).
Auxiliary Table
• Our Time Information
OurTimestamp (c timestamp)
PK (c timestamp)
Datatype
– c timestamp timestamp
You must maintain our system time (not the real system time) in this (auxiliary) table. The
reason for making our system time different from the real system time is to make it easy to
generate scenarios (time traveling) to debug and test your project. All timing should be based
on our system time (c timestamp). No attribute in the Pitt Tours tables should refer to the
OurTimestamp table. OurTimestamp has only one tuple, inserted as part of initialization
and is updated during time traveling.
Sample data: the provided example tuples above and in the sample-data file are intended for
clarification of the description, and are not sufficient for testing the correctness of your solution.
You need to add or modify the sample-data accordingly in order to test your solution.
Tasks:
1. [20 Points] Use SQL CREATE TABLE statement to create tables for the relations given in
the Description above. You need to define primary keys foreign keys (if any) alternate keys
(if any), as well as any additional semantic constraints, such default values and whether an
attribute can take a NULL as a value or not.
2. [10 Points] Create a function, called getCancellationTime, that accepts as input a reser-
vation number and returns the time when the reservation should be canceled (i.e., 12 hours
before the flight is scheduled to depart). After the function creation, add the necessary code
to execute the function getCancellationTime.
3. [10 Points] Create a function, called isPlaneFull, that checks if a specific flight has no
available seats (i.e., the flight is fully booked). This function should return true if the plane
is full and false if the plane is not full. You need to come up with all the necessary arguments
(input parameters) for isPlaneFull. After the function creation, add the necessary code to
execute the function isPlaneFull.
4. [15 Points] Create a procedure, called makeReservation, that records the details of a segment
of a trip. The input to the makeReservation procedure is reservation number, flight num, de-
parture date and order (leg number within the trip). Note that the flight time for flight date is
calculated based on a flight number and departure date using the substr(string, from [,
count]) function (this could be another SQL function). To test the procedure makeReservation,
add the necessary code to call it and form a trip (i.e., inserting an appropriate tuple in the
Reservation table and manually generate the necessary information of a trip using the sample
data).
5. [20 Points] Create a trigger called planeUpgrade, that changes the plane (type) of a flight to
an immediately higher capacity plane (type), if it exists, when a new reservation is made on
that flight and there are no available seats. The function, isPlaneFull, should be used when
checking if a flight has no available seats. A change of plane will fail only if the currently
assigned plane for the flight is the one with the biggest capacity. For simplicity, we assume
that there are always available planes for a switch to succeed. After the trigger definition,
add the necessary code to fire the trigger planeUpgrade.
6. [20 Points] Write a trigger, called cancelReservation, that cancels (deletes) all non-ticketed
reservations for a flight at the flight’s cancellation time. The function, getCancellationTime,
should be used to obtain a flight’s cancellation time (i.e., 12 hours before the flight is sche-
duled to depart). If the number of ticketed passengers fits in a smaller capacity plane, then
the plane for that flight should be switched to the smaller-capacity plane. Each reservation
cancellation should be a single transaction, that is, the deletion of a reservation from all
effected tables should be handled as a single atomic operation. After the trigger definition,
add the necessary code to fire the trigger cancelReservation.
What to submit [5 points]
• -hw5-db.sql
(e.g., team14-hw5-db.sql)
In this file, please submit the answers to Task 1. In addition to providing the answers,
you are expected to:
– include your team number/name, your names and pitt usernames at the
top of the SQL script as an SQL comment.
The entire SQL script file should be composed of valid SQL statements.
• -hw5-query.sql
(e.g., team14-hw5-query.sql)
In this file, please submit the answers to Tasks 2, 3, and 4. In addition to providing the
answers, you are expected to:
– include your team number/name, your names and pitt usernames at the
top of the SQL script as an SQL comment and
– identify the task number before each as an SQL comment.
The entire SQL script file should be composed of valid SQL statements.
• -hw5-triggers.sql
(e.g., team14-hw5-triggers.sql)
In this file, please submit the answers to Tasks 5 and 6. In addition to providing the
answers, you are expected to:
– include your team number/name, your names and pitt usernames at the
top of the SQL script as an SQL comment, and
– identify the task number before each as an SQL comment.
• -hw5-insert.sql
(e.g., team14-hw5-insert.sql)
In this file, please submit the insert statement you used for populating the data in your
database. In addition to providing the answers, you are expected to:
– include your team number/name, your names and pitt usernames at the
top of the SQL script as an SQL comment.
• -hw5-output.txt>
(e.g., team14-hw5-output.txt)
In this file, please submit the output of your SQL scripts (i.e., the above files. To do
that in PSQL you can use the command “\o” as seen in the recitation video.
In addition to providing the answers, you are expected to:
– include your team number/name, your names and pitt usernames at the
top of the text file.
How to submit
The project including this assignment (HW5) will be collected also by the TAs via your team’s
private GitHub repository that is shared only with the TAs (GitHub username: ralseghayer,
GitHub username: nixonb91).
• Email your Git commit ID and the full web link to your Git repository to
cs1555-staff with all team members CC’d. For this milestone, each group can only
submit once. In addition to submitting the Git commit ID via email to cs1555-staff
with all team members CC’d, you must add the TAs as collaborators (Github
usernames: nixonb91, ralseghayer) to the Github repository. In your GitHub repository
you are required to have the four files specified in What to Submit.
• Submit your files (i.e., four SQL script files and a text output file) that contain your
solution through the Web-base submission interface you have used for previous Assig-
nments. Note: Only one team member should submit the files through the
Web-base submission interface since this is a group assignment. It is your
responsibility to make sure the assignment was properly submitted.
• Submit your files by the due date (8:00pm Oct. 29, 2020). There is no late
submission.
Group Rules
• It is expected that all members of a group will be involved in all aspects of the pro-
ject development. Division of labor to data engineering (db component) and software
engineering (java component) is not acceptable since each member of the group will be
evaluated on both components.
• The work in this assignment is to be done independently by each group. Discussions
with other groups on the assignment should be limited to understanding the statement
of the problem. Cheating in any way, including giving your work to someone else will
result in an F for the course and a report to the appropriate University authority.

欢迎咨询51作业君