辅导22C:4400、讲解database留学生、SQL编程语言辅导、讲解SQL 辅导留学生Prolog|解析Haskell程序
- 首页 >> 其他 Group 7
22C:4400
April 2, 2019
Project Plan v3
Functions list:
1. Book Flight - Round Trip ; Book Flight - 1 way ; Book Flight - Multi City , Flight Status
a. Methods Overview:
Given the inputs of date of departure and date of return from the user, look
at flight table and first query by departure date. This information must first
be served to the user, who makes a choice for the departure flight, then
allow the user to pick the return flight. For testing, we will populate our
database with test information in several tables, and add data as further
testing of the system is needed.
i. Query 1 Find flights:
When the user queries the system to find a flight that matches their
needs, we use a process of refinement to help them find the perfect
flight. If multiple criteria are specified, then we pose all of those
criteria to the server, which forms intelligent queries for the
database.
1. First, we select all flights where the departure date, departure
location, and arrival location criteria are met. All flight types rely on
this first query.
Input: departure date (datetime), departure location (varchar),
arrival location (varchar)
Output: All matching flight records
SQL: SELECT * FROM Flights WHERE DepartureDate = {user’s
chosen departure date, formatted} AND DepartureLocation = {user’s
chosen departure location, formatted};
2. Next, if we want a round trip or multi-city trip, we make additional
queries as enumerated below:
a. Round Trip: find all flights with an arrival date equal to the
user’s specified return date, with an arrival location equal to
their original departure location, and with a departure
location equal to their original arrival location.
Input: return date (datetime)
Output: All matching flight records
SQL: SELECT * FROM Flights WHERE ArrivalDate = {return
date} AND ArrivalLocation = {original departure location}
AND DepartureLocation = {original arrival location};a. Multi-city: find all flights where the arrival destination is
equal to the user’s specified destination, where the departure
date is equal to the user’s specified departure date, and
where the departure destination is equal to the previous
flight’s arrival location..
Input: departure date (datetime), arrival date (datetime),
departure location (varchar), arrival location (varchar)
Output: success / failure
SQL: SELECT * FROM Flights WHERE Departure = {user’s
chosen departure date, formatted} AND Airport = {user’s
chosen departure location, formatted};
ii. Query 2 Book a flight (create booking):
For each flight that the customer boards, they will have an individual
booking. Tickets can have multiple flight bookings, such as
round-trip flights or multi-city flights.
Input: FlightID (int), PassengerID (int), BaggageID (int), Price (float),
Seat (varchar), Class (varchar)
Output: Success / failure
SQL: INSERT INTO Bookings VALUES (FlightID, BaggageID, Price,
Seat, Class);
iii. Query 3 Remove booking
Input: BookingID (int), FlightID (int)
Output: success / failure
SQL: DELETE FROM Tickets WHERE BookingID = {user’s booking #}
AND FlightID = {user’s flight #};
iv. Query 4 Update bookings on a ticket
Input: BookingID (int), update field
Output: Success/failure
SQL: UPDATE Booking SET {field} = {new value} WHERE BookingID
= {booking ID};
2. Aircraft
a. Create
i. Input: Type of Aircraft (varchar), Number of First Class Seats (int), Number
of Economy Seats (int), Vin Number (varchar)
ii. Output: Success or Failure
iii. SQL: INSERT INTO Airplanes VALUES ({VIN}, {class A capacity number}, …,
{class D capacity number}, {model});
b. Update
i. Input: Type of Aircraft (varchar), Number of First Class Seats (int), Number
of Economy Seats (int)
ii. Output: Success or Failureiii. SQL: UPDATE Airplanes SET {class A capacity number} = {new value}, …,
{class D capacity number} = {new value}, {model} = {new value}) WHERE
VIN = {plane’s VIN #};
c. Read
i. Input: Vin Number
ii. Output: Type of Aircraft (varchar), Number of First Class Seats (int),
Number of Economy Seats (int)
iii. SQL: SELECT * FROM Airplanes WHERE VIN = {vin #};
d. Delete
i. Input: Vin Number (varchar)
ii. Output: Success or Failure
iii. SQL: DELETE FROM Airplanes WHERE VIN = {vin #};
3. Flight
a. Create
i. Input: Date of Departure (datetime), Date of Return (datetime), Gate
(varchar), Terminal (varchar), Aircraft Vin (varchar)
ii. Output: Flight Number
iii. SQL: INSERT INTO Flights VALUES ({departure date}, {return date}, {gate},
{terminal}, {aircraft VIN #});
b. Update
i. Input: Flight Number (int), Changes to be made (various)
ii. Output: Success or Failure Updating
iii. SQL: UPDATE Flights SET {corresponding columns} = {corresponding
values};
c. Read
i. Input: Flight Number (int)
ii. Output: Available First Class Seats (int), Available Economy Seats (int), Gate
(varchar), Terminal (varchar), Type of Aircraft (varchar), Date of Departure
and Return (datetimes)
iii. SQL: SELECT * FROM Flights WHERE FlightID = {flight ID #};
d. Delete
i. Input: Flight Number (int)
ii. Output: Success or Failure
iii. SQL: DELETE FROM Flights WHERE FlightNo = {flight ID #};
4. Advantage Account
a. Create
i. Input: Username (varchar), Password (varchar), First Name (varchar), Last
Name (varchar), Email (varchar), Registration Date (datetime)
ii. Output: UserID (varchar), Success or Failure
iii. SQL: INSERT INTO Accounts VALUES ({username}, {hashed password}, {first
name}, {last name}, {email address}, {registration date});
b. Update
i. Input: Name (varchar), Password (varchar)ii. Output: Success or Failure
iii. SQL: UPDATE Accounts SET {corresponding columns} = {corresponding
values} WHERE UserID = {userID};
c. Read
i. Input: UserID (varchar)
ii. Output: Username (varchar), Password (varchar), First Name (varchar),
Last Name (varchar), Email (varchar), Registration Date (datetime)
iii. SQL: SELECT * FROM Accounts WHERE UserID = {userID};
d. Delete
i. Input: Name (varchar), Password (varchar)
ii. Output: Success or Failure
iii. SQL: DELETE FROM Accounts WHERE UserID = {userID};
5. Reserve Seat/Cancel Reservation
a. Add
i. Input: Flight Number (int), Seat Number (varchar), Ticket Number (int)
ii. Output: Success or Failure
iii. SQL: INSERT INTO Flights
b. Update
i. Input: Flight Number (int), Seat Number (varchar), Ticket Number (int)
ii. Output: Success or Failure
iii. SQL:
c. Delete
i. Input: Flight Number (int), Seat Number (varchar)
ii. Output: Success or Failure
iii. SQL:ER Diagram
Account
These are customers who have actually made an account on our website. There can be an account
for a passenger, but passengers don’t require an account.
Passenger
Just the basic info so we know who’s flying on our planes. This info is referenced in the Booking.
We also store an optional foreign key to an Account in case this is a customer who has signed up for
an account with us. There’s a little redundancy here, but it’s the best way to do it if we don’t want to
require people to make an account with us.
Baggage
Simple baggage tags. These records don’t identify individual pieces of luggage used by the
customer, but rather the total of their baggage weight.
Booking
This is what gets ties a customer to a flight. One-way tickets have one booking, round-trip tickets
have two bookings, and multi-city tickets have multiple bookings. Flights can have as many
bookings as they have seats.
Flight
This decides who goes on what plane, where the plane is, the prices for different seats on the plane,
etc.Employee
An airline employee, such as pilot, copilot, or flight attendant. We have a field to assign employees
to their next flight, but not any future flights beyond their next. Realistically, we would have them
booked for multiple flights in advance and have multiple fields here to convey as much.
Location
Every airport has a code (primary key) and the full name of the location, including the city, state,
and country. We’re only doing domestic flights in this phase so the country is unnecessary, but it’s
good practice for future expansion.
SeatChart
This is where the status of the flight’s occupancy is kept. We have a column for each for each row of
seating in the aircraft stored as a VARCHAR. The default value for each row is ‘ABCDEFGHJK’
(skipped the I on purpose because that’s what real planes do). If all seats in a row are vacant, all of
these letters will show in that row. If one of those seats is reserved, we remove that letter from that
row, and that signifies to the server that it is not available anymore.
Aircraft
We use the aircraft’s VIN number as its ID, and keep the model, maker, etc., as well as the range of
rows that belong to each class. The class rows are stored as VARCHAR, so we can input values like
“1-20” as our first class seating range.
22C:4400
April 2, 2019
Project Plan v3
Functions list:
1. Book Flight - Round Trip ; Book Flight - 1 way ; Book Flight - Multi City , Flight Status
a. Methods Overview:
Given the inputs of date of departure and date of return from the user, look
at flight table and first query by departure date. This information must first
be served to the user, who makes a choice for the departure flight, then
allow the user to pick the return flight. For testing, we will populate our
database with test information in several tables, and add data as further
testing of the system is needed.
i. Query 1 Find flights:
When the user queries the system to find a flight that matches their
needs, we use a process of refinement to help them find the perfect
flight. If multiple criteria are specified, then we pose all of those
criteria to the server, which forms intelligent queries for the
database.
1. First, we select all flights where the departure date, departure
location, and arrival location criteria are met. All flight types rely on
this first query.
Input: departure date (datetime), departure location (varchar),
arrival location (varchar)
Output: All matching flight records
SQL: SELECT * FROM Flights WHERE DepartureDate = {user’s
chosen departure date, formatted} AND DepartureLocation = {user’s
chosen departure location, formatted};
2. Next, if we want a round trip or multi-city trip, we make additional
queries as enumerated below:
a. Round Trip: find all flights with an arrival date equal to the
user’s specified return date, with an arrival location equal to
their original departure location, and with a departure
location equal to their original arrival location.
Input: return date (datetime)
Output: All matching flight records
SQL: SELECT * FROM Flights WHERE ArrivalDate = {return
date} AND ArrivalLocation = {original departure location}
AND DepartureLocation = {original arrival location};a. Multi-city: find all flights where the arrival destination is
equal to the user’s specified destination, where the departure
date is equal to the user’s specified departure date, and
where the departure destination is equal to the previous
flight’s arrival location..
Input: departure date (datetime), arrival date (datetime),
departure location (varchar), arrival location (varchar)
Output: success / failure
SQL: SELECT * FROM Flights WHERE Departure = {user’s
chosen departure date, formatted} AND Airport = {user’s
chosen departure location, formatted};
ii. Query 2 Book a flight (create booking):
For each flight that the customer boards, they will have an individual
booking. Tickets can have multiple flight bookings, such as
round-trip flights or multi-city flights.
Input: FlightID (int), PassengerID (int), BaggageID (int), Price (float),
Seat (varchar), Class (varchar)
Output: Success / failure
SQL: INSERT INTO Bookings VALUES (FlightID, BaggageID, Price,
Seat, Class);
iii. Query 3 Remove booking
Input: BookingID (int), FlightID (int)
Output: success / failure
SQL: DELETE FROM Tickets WHERE BookingID = {user’s booking #}
AND FlightID = {user’s flight #};
iv. Query 4 Update bookings on a ticket
Input: BookingID (int), update field
Output: Success/failure
SQL: UPDATE Booking SET {field} = {new value} WHERE BookingID
= {booking ID};
2. Aircraft
a. Create
i. Input: Type of Aircraft (varchar), Number of First Class Seats (int), Number
of Economy Seats (int), Vin Number (varchar)
ii. Output: Success or Failure
iii. SQL: INSERT INTO Airplanes VALUES ({VIN}, {class A capacity number}, …,
{class D capacity number}, {model});
b. Update
i. Input: Type of Aircraft (varchar), Number of First Class Seats (int), Number
of Economy Seats (int)
ii. Output: Success or Failureiii. SQL: UPDATE Airplanes SET {class A capacity number} = {new value}, …,
{class D capacity number} = {new value}, {model} = {new value}) WHERE
VIN = {plane’s VIN #};
c. Read
i. Input: Vin Number
ii. Output: Type of Aircraft (varchar), Number of First Class Seats (int),
Number of Economy Seats (int)
iii. SQL: SELECT * FROM Airplanes WHERE VIN = {vin #};
d. Delete
i. Input: Vin Number (varchar)
ii. Output: Success or Failure
iii. SQL: DELETE FROM Airplanes WHERE VIN = {vin #};
3. Flight
a. Create
i. Input: Date of Departure (datetime), Date of Return (datetime), Gate
(varchar), Terminal (varchar), Aircraft Vin (varchar)
ii. Output: Flight Number
iii. SQL: INSERT INTO Flights VALUES ({departure date}, {return date}, {gate},
{terminal}, {aircraft VIN #});
b. Update
i. Input: Flight Number (int), Changes to be made (various)
ii. Output: Success or Failure Updating
iii. SQL: UPDATE Flights SET {corresponding columns} = {corresponding
values};
c. Read
i. Input: Flight Number (int)
ii. Output: Available First Class Seats (int), Available Economy Seats (int), Gate
(varchar), Terminal (varchar), Type of Aircraft (varchar), Date of Departure
and Return (datetimes)
iii. SQL: SELECT * FROM Flights WHERE FlightID = {flight ID #};
d. Delete
i. Input: Flight Number (int)
ii. Output: Success or Failure
iii. SQL: DELETE FROM Flights WHERE FlightNo = {flight ID #};
4. Advantage Account
a. Create
i. Input: Username (varchar), Password (varchar), First Name (varchar), Last
Name (varchar), Email (varchar), Registration Date (datetime)
ii. Output: UserID (varchar), Success or Failure
iii. SQL: INSERT INTO Accounts VALUES ({username}, {hashed password}, {first
name}, {last name}, {email address}, {registration date});
b. Update
i. Input: Name (varchar), Password (varchar)ii. Output: Success or Failure
iii. SQL: UPDATE Accounts SET {corresponding columns} = {corresponding
values} WHERE UserID = {userID};
c. Read
i. Input: UserID (varchar)
ii. Output: Username (varchar), Password (varchar), First Name (varchar),
Last Name (varchar), Email (varchar), Registration Date (datetime)
iii. SQL: SELECT * FROM Accounts WHERE UserID = {userID};
d. Delete
i. Input: Name (varchar), Password (varchar)
ii. Output: Success or Failure
iii. SQL: DELETE FROM Accounts WHERE UserID = {userID};
5. Reserve Seat/Cancel Reservation
a. Add
i. Input: Flight Number (int), Seat Number (varchar), Ticket Number (int)
ii. Output: Success or Failure
iii. SQL: INSERT INTO Flights
b. Update
i. Input: Flight Number (int), Seat Number (varchar), Ticket Number (int)
ii. Output: Success or Failure
iii. SQL:
c. Delete
i. Input: Flight Number (int), Seat Number (varchar)
ii. Output: Success or Failure
iii. SQL:ER Diagram
Account
These are customers who have actually made an account on our website. There can be an account
for a passenger, but passengers don’t require an account.
Passenger
Just the basic info so we know who’s flying on our planes. This info is referenced in the Booking.
We also store an optional foreign key to an Account in case this is a customer who has signed up for
an account with us. There’s a little redundancy here, but it’s the best way to do it if we don’t want to
require people to make an account with us.
Baggage
Simple baggage tags. These records don’t identify individual pieces of luggage used by the
customer, but rather the total of their baggage weight.
Booking
This is what gets ties a customer to a flight. One-way tickets have one booking, round-trip tickets
have two bookings, and multi-city tickets have multiple bookings. Flights can have as many
bookings as they have seats.
Flight
This decides who goes on what plane, where the plane is, the prices for different seats on the plane,
etc.Employee
An airline employee, such as pilot, copilot, or flight attendant. We have a field to assign employees
to their next flight, but not any future flights beyond their next. Realistically, we would have them
booked for multiple flights in advance and have multiple fields here to convey as much.
Location
Every airport has a code (primary key) and the full name of the location, including the city, state,
and country. We’re only doing domestic flights in this phase so the country is unnecessary, but it’s
good practice for future expansion.
SeatChart
This is where the status of the flight’s occupancy is kept. We have a column for each for each row of
seating in the aircraft stored as a VARCHAR. The default value for each row is ‘ABCDEFGHJK’
(skipped the I on purpose because that’s what real planes do). If all seats in a row are vacant, all of
these letters will show in that row. If one of those seats is reserved, we remove that letter from that
row, and that signifies to the server that it is not available anymore.
Aircraft
We use the aircraft’s VIN number as its ID, and keep the model, maker, etc., as well as the range of
rows that belong to each class. The class rows are stored as VARCHAR, so we can input values like
“1-20” as our first class seating range.