The universe of discourse is a Home Airport. The database stores the details of all the flights departing from the Home Airport (in one day) including the destination airport and terminal, the take-off terminal, along with details about the passengers, crew, airplane and airline.
The requirements document
during the initial planning phase of the project, and the Entity-Relationship Diagram
that served as the template for the database are included in the files ER Diagram.pdf
and Requirements.pdf
.
$ cd M.A.D
$ mysql -h -p --port < info.sql
$ python3 cli.py
- Enter the directory M.A.D and run mysql as you would on a normal machine, but redirect the contents of info.sql into the command. (Eg: mysql -h -p --port < info.sql). This would load the transport database onto your local machine.
- Next run python3 cli.py
- Follow the prompts and enter the hostname, username, password and port name without any leading spaces. This would connect the database to the python interface.
- Enter a query number and follow the prompts to execute those queries.
We used MySQL to create the tables and insert the sample data. PyMySQL was used to implement the queries.
The following queries are available:
Selection
: Find all theairplanes
for a givenairline code
.Projection
: For a givenJourney ID
, display thefull names
of all the passengers.Aggregate
: Find thetotal number of flights departing
from home airport to a another aiport given itsairport code
.Search
: Search for passengers on a givenJourney ID
, by enteringname or part of name
.Insert
: Givenairplane details
, insert anew airplane
into the databaseUpdate
: Given theJourney ID
of a journey, update thevalue of the terminal
it must take off from to the value provided.Delete
: Given aPNR number
, delete the corresponding passenger from the database.Analysis
: Find thenumber of passengers
traveling on a journey given aJourney ID
.Analysis
: Find thetotal weight
of luggage on a given journey.
- All possible errors are handled and requisite error messages are displayed in order to detract from an inconvenient user experience and make the overall usage easier for the user.
- Whenever an incorrect prompt is entered users are given the option to re-enter their query along with an alert about the error so they do not make the same mistake.
- Errors relating to
incorrect query number
, incorrect queryformat
,non-existent data
, insertingduplicate data
, re-using aprimary key
, etc. are detected, stopped, and displayed to the user with the error message and information on how to correct it.
- Addition of an attribute called
home_terminal
to theJourney
table. This was done to indicate which terminal in the home airport the flight takes off from. The terminal and airport to which it is travelling was already included. - We have made
airline_code
inCodename
a foreign key toairline_code
inAirplane
, as this had been omitted earlier. - The data type for
airport_code
in Airport has been corrected tovarchar
. - We have made changes to the queries mentioned in our requirements document, as we found that those queries were flawed. The update, delete and insert operations remain the same.
- The attribute
number
of theterminal
entity has been renamed toterminal_number
for sake of clarity.
- We assume that a passenger can realistically only depart on one journey from the Home Airport in a day.
- Queries have restrictions that ensure no insert, update or deletion anomalies are caused keeping in mind the impact of incorrect management of foreign keys.