Description

Objective:
The objective of this course project is to provide a realistic experience in the design process of a relational database and corresponding applications. We will focus on conceptual design, logical design, implementation, operation, maintenance of a relational database. We will also implement an associated web-based application to communicate with the database (retrieve information, store information etc.).
Project Overview:
The course project for this semester is online Air Ticket Reservation System. There will be two types of users of this system – Customers, and Airline Staff (Administrator). Using this system, customers can search for flights (one way or round trip), purchase flights ticket, view their future flight status or see their past flights etc. Airline Staff will add new airplanes, create new flights, and update flight status. In general, this will be a simple air ticket reservation system.

Project Description
There are several airports (Airport), each airport has a unique code, a name, a city, a country, number of terminals, and an airport type (domestic/international/both).

There are two types of users for this system: Customer, and Airline Staff.
Customer:
Each Customer has a name (composite attribute consisting of first name and last name), email address, password, address (composite attribute consisting of building number, street name, Apartment number, city, state, zip code), at least one contact phone_number, passport_number, passport_expiration, passport_country, and date_of_birth. Each Customer’s email address is unique, and they will sign into the system using their email address and password.
Customers must be logged in to purchase a flight ticket.
Customer will be able to rate and comment on their previous flights only.
Airline Staff:
Airline Staff will set flight statuses in the system.
Each Airline Staff can create new flights only for the particular airline that they work for by inserting all necessary information and will set the ticket base price for flight. They will also be able to see all ontime, future, and previous flights for the airline that they work for, as well as a list of passengers for the flights.
In addition, Airline Staff will be able to see a list of all flights a particular Customer has taken only on that particular airline.
Airline Staff will be able to see each flight’s average ratings and all the comments and ratings of that flight given by the customers.
Airline Staff will also be able to see the most frequent customer within the last year, see the number of tickets sold each month, see the total amount of revenue earned etc.
Airline Staff can query for how many flights get delayed/on-time etc.
What You Should Do for Part 1:
When you do this, think about: which information should be represented as attributes, which as entity sets or relationship sets? Are any of the entity sets weak entity sets? If so, what is the identifying strong entity set? What is the primary keys (or discriminant) of each entity set? What are the cardinality constraints on the relationship sets? Do you need to use ternary relationship sets or aggregation?

What You Should Do for Part 2
1. Following the techniques we studied, derive a relational schema diagram from the Part 1’s ER diagram. Remember to underline primary keys and use arrows from the referencing schema to the referenced schema to indicate foreign key constraints.
2. Write and execute SQL CREATE TABLE statements to create the tables. Choose reasonable types for the attributes.
3. Write and execute INSERT statements to insert data representing one airline’s air ticket reservation system. As for example, you can insert data in the appropriate tables as follows or you can insert data for another airline or your own make up airline:
a. One Airline name “Jet Blue”.
b. At least Two airports named “JFK” in NYC and “PVG” in Shanghai.
c. Insert at least three customers with appropriate names and other attributes.
d. Insert at least three airplanes.
e. Insert At least One airline Staff working for Jet Blue.
f. Insert several flights with on-time, and delayed statuses.
g. Insert some tickets for corresponding flights and insert some purchase records (customers bought some tickets).
4. Write SQL queries for executing following queries and show the results in your file (SQL query and corresponding answers):
a. Show all the future flights in the system.
b. Show all of the delayed flights in the system.
c. Show the customer names who bought the tickets.
d. Show all the airplanes owned by the airline Jet Blue.
Submit a PDF file for Relational Schema diagram and one .SQL file for 2 (create table statements), one .SQL file for 3 (inserting data in the database), one .SQL file for 4 (SQL queries and corresponding results) on Gradescope as the solution of “Part 2 of Course Project” Assignment (will be available later).
What You Should Do for Part 3
In Part 3, you’ll implement Air Ticket Reservation System as a web-based application. You must use the table definitions that you created for part 2 (derived from the E-R diagram) unless you need to make some small additions/modifications to support your additional features. If you do modify the table definitions, you will be responsible for translating the test data/test scenarios (in case we provide) so that it matches your table definitions.

REQUIRED Application Use Cases (aka features):

Home page when not logged-in:
When the user is not logged-in, the following cases should be available in the home page:

1. View Public Info: All users, whether logged in or not, can

2. Register: 2 types of user registrations (Customer, and Airline Staff) option via forms as mentioned in the part 1 of the project.

3. Login: 2 types of user login (Customer, and Airline Staff). Users enters their username (email address will be used as username for customer)) – x, and password – y, via forms on login page. This data is sent as POST parameters to the login-authentication component, which checks whether there is a tuple in the corresponding user’s table with username=x and the password = md5(y) :

A. If so, login is successful. A session is initiated with the member’s username stored as a session variable. Optionally, you can store other session variables. Control is redirected to a component that displays the user’s home page.
B. If not, login is unsuccessful. A message is displayed indicating this to the user.

Once a user has logged in, reservation system should display his/her home page according to user’s role. Also, after other actions or sequences of related actions, are executed, control will return to component that displays the home page. The home page should display an error message if the previous action was not successful.

Some mechanism for the user to choose the use case he/she wants to execute:

Any other information you’d like to include:

Customer use cases:

2. Search for flights: Search for future flights (one way or round trip) based on source city/airport name, destination city/airport name, dates (departure or return).

4. Cancel Trip: Customer chooses a purchased ticket for a flight that will take place more than 24 hours in the future and cancel the purchase. After cancellation, the ticket will no longer belong to the customer. The ticket will be available again in the system and purchasable by other customers.

5. Give Ratings and Comment on previous flights: Customer will be able to rate and comment on their previous flights (for which he/she purchased tickets and already took that flight) for the airline they logged in.
6.Track My Spending: Default view will be total amount of money spent in the past year and a bar chart/table showing month wise money spent for last 6 months. He/she will also have option to specify a range of dates to view total amount of money spent within that range and a bar chart/table showing month wise money spent within that range.

7.Logout: The session is destroyed and a “goodbye” page or the login page is displayed.

Airline Staff use cases:

1. View flights: Defaults will be showing all the future flights operated by the airline he/she works for the next 30 days. He/she will be able to see all the current/future/past flights operated by the airline he/she works for based range of dates, source/destination airports/city etc. He/she will be able to see all the customers of a particular flight.

2. Create new flights: He or she creates a new flight, providing all the needed data, via forms. The application should prevent unauthorized users from doing this action. Defaults will be showing all the future flights operated by the airline he/she works for the next 30 days.

3. Change Status of flights: He or she changes a flight status (from on-time to delayed or vice versa) via forms.

4. Add airplane in the system: He or she adds a new airplane, providing all the needed data, via forms. The application should prevent unauthorized users from doing this action. In the confirmation page, she/he will be able to see all the airplanes owned by the airline he/she works for.

5. Add new airport in the system: He or she adds a new airport, providing all the needed data, via forms. The application should prevent unauthorized users from doing this action.

6. View flight ratings: Airline Staff will be able to see each flight’s average ratings and all the comments and ratings of that flight given by the customers.

8. View frequent customers: Airline Staff will also be able to see the most frequent customer within the last year. In addition, Airline Staff will be able to see a list of all flights a particular Customer has taken only on that particular airline.

9. View Earned Revenue: Show total amount of revenue earned from ticket sales in the last month and last year.

10. Logout: The session is destroyed and a “goodbye” page or the login page is displayed.

Additional Requirements:
You should implement Air ticket reservation system as a web-based application. If you want to use a
DBMS other than MySQL, SQLserver, Oracle, MongoDB or to use a programming language other than Python/Flask, Java/JDBC/Servlets, PHP, C#, node.js, or JavaScript please check with me first. You will need to bring the host computer to the demo/test session at the end of the semester or make the application available remotely over the web.

Session Management: When a user logs in, a session should be initiated; relevant session variables should be stored. When the member logs out, the session should be terminated. Each component executed after the login component should authenticate the session and retrieve the user’s pid from a stored session variable. (If you’re using Python/Flask, you can follow the model in the Flask examples presented to do this.)

You must use prepared statements if your programming language supports them. (This is the style used in Flask; if you’re using PHP, use the MySQLi interface; if you’re using Java/JDBC, use the PreparedStmt class.) If your programming language does not support prepared statements, Free form inputs (i.e., text entered through text boxes) that is incorporated into SQL statements should be validated or cleaned to prevent SQL injection.

You should take measures to prevent cross-site scripting vulnerabilities, such as passing any text that comes from users through htmlspecialchars or some such function, before incorporating it into the html that air ticket reservation system produces.

The user interface should be usable, but it does not need to be fancy. For each type of users, you need to implement different home pages where you only show relevant use cases for that type of users and you should not show/combine all the use cases in one page.

Tips and suggestions for Part 3:
1. Use the tables you defined/created in Part 2. (If you defined extra tables or attributes, for additional features, merge them into Part 2.)

2. Before you start coding, think about what each component will do. If there are commonalities among many of the use cases, think about how you will modularize your code.

3. Implement the web-based application for Air Ticket Reservation System. For each component:
a. Using some sample data, write the queries executed by the component, and test them.
b. Write the application code for the component.

4. Test the component with additional values, including values that are not valid input.

5. Implement and test the components one at a time. When a component is ready, add links to it to your home page (or enhance the home page with the interface of the new component.) You will get partial credit if some of your features work, even if others have not been implemented.

6. For testing/debugging you will probably find it useful to execute your SQL queries directly through PHPmyAdmin (if you use MySQL) or using your database provided client program, before incorporating them in your application code.

Complete Final Project Hand in instructions: You will hand in:
• Your source codes. (Details about whether to zip it, etc., will be provided.)
• A list of the files in your application and what’s in each file. (E.g. “homepage.phpscript to generate home page”.)
• A separate file that lists all of the use cases and the queries executed by them (with brief explanation). This should be well organized and readable. It should be detailed enough to give readers a good idea of how your application works, without making them dig through all the code.
• For team projects: A summary of who did what.
• More detailed instructions on what to hand in, how to hand it in, and test data to load into your tables will be provided later.