代做22C:4400、SQL程序语言调试、代写SQL设计、代做system留学生

日期：2019-04-13 11:52

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 Failure

iii. 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.