In this project you will develop a database system for the Airline booking system. Your finished product should contain information about passenger, flights, payments, bags etc.

Each project group should go through the following steps in completing the phase $1$ of the project:

$1.$ Describe constraints, including business related, and technical constraints, such as primary keys, foreign keys, check constraints, and not null constraints, for the tables and attributes, etc.

$2.$ Design the database, following an EER approach; then go through the normalization process to come up with a collection of tables that are in Third normal forms.

$3.$ Use any SQL database to create the normalized tables.

$4.$ Find out how the most recent version of your database implements the concept of triggers and then create at least one trigger for your database. For example, one such trigger could be that someone wants to book flight, your trigger must create ticket id automatically.

$5.$ Populate the database by using SQL insert statements or with some GUI interface such as PhpMySQLAdmin in case you are using MySQL$.$

Database Systems Project $($PHASE $1)$ High$-$Level Requirements

Due: Saturday, March $23,2024$

The list of requirements with constraints$/$business rules are as follows:

a$)$ A passenger should be able to search flights by dates, destination and source city.

b$)$ Each ticket will have a unique identification number and other details including flight dates, weight, time etc.

c$)$ Passenger can book a seat if it is not booked already.

d$)$ The system maintains information of assigned plane for a flight. The planes have various attributes including aircraft type, date of first flight, total seats, last and next dates for maintenance etc. The aircraft details are maintained separately including aircraft type, number of seats in first class, business class and economy and price of seats for each class.

e$)$ The system also maintains waitlisted passengers up to $10$ seats in economy and $3$ in other classes.

f$)$ There should be a maximum limit $(10)$ seats to be booked by passenger at any time in specific flight.

g$)$ There should be a maximum limit $(90)$ on how many days a member can reuse ticket whenever miss his$/$her flight.

h$)$ The system should identify fines for cancelation or missing flight.

i$)$ The system should be able to send notifications whenever the seat booked and ticket successfully purchased.

j$)$ Every passenger$$s payment details should be kept.

We have three main actors in our system:

$$ Admin: Mainly responsible for adding and modifying tickets.

$$ Passenger: All passengers can search the flight, destination and source, purchase ticket, postponed$/$cancel their flight, book seats etc.

$$ System: Mainly responsible for sending notifications etc.

Phase $1$: Deliverables

$$ Draw a conceptual EER and a logical Relational schema diagram of your design.

$$ Due: Sunday, March $23,2024.$

$$ Use draw.io$,$ Visio or ERDPlus or any other tool to draw the conceptual diagram. Hand drawn diagram will not be accepted.

$$ State all constraints and business rules identified.

$$ State clearly any assumptions that you added to the above requirements.

$$ List clearly the semantic requirements that cannot be captured in your EER model$($if any$).$

Please Use ERDPLUS to draw the EER diagram