Database System for MPoly Airlines: Business Rules, ER Diagram, Data Dictionary, SQL Statements and Output Screens

Running Head: DATABASE SYSTEM
DATABASE SYSTEM
Name of the Student:
Name of the University:
Author Note:

1DATABASE SYSTEM
Table of Contents
Answer for question 1:.....................................................................................................................2
Answer to question 2:......................................................................................................................4
Answer to question 3:....................................................................................................................12
Answer to question 4:....................................................................................................................12
Output screens:..............................................................................................................................16
Bibliography..................................................................................................................................19

2DATABASE SYSTEM
Answer for question 1:
The database is for the MPoly airlines operation and stores the flight and all the airplane history.
Business Rules:
 Mpoly Airlines has one or more airplanes.
 One airplane is used for one flight.
 One flight is identified by the combination of flight number and the date.
 The airlines conduct one or more flights in a day.
 Every flight has arrival and departure time.
 The database keeps track of the price of the ticket and the data of reservation for a
particular passenger.
 A passenger has identified by a unique passenger number.
 A passenger can take N number of flights.
 One flight can have many passengers.
 A pilot is identified by a unique pilot number.
 One flight for a particular date has exactly one pilot.
 Each pilot can fly many airplane.
 A pilot may not fly any airplane.
 One airplane is used for one flight for a particular date.
 One airplane may be used for one or more flights.
 Some airplanes may not be used for any flight.
 The maintenanceprocedure is identified by unique maintenance procedure number.
 Maintenance is performed on every plane.

3DATABASE SYSTEM
 The database also stores the maintenance of date, location and the duration.
List of all the entities and attributes
Firstly there will be the Flight Entity which contains a composite key using flight_no and
flight_date. The flight_departure and flight_arrival are also included. The Pilot Entity has one
primary key which is the pilot_id. Besides that there are two foreign keys which are flight_no
and flight_date which are referenced from the table Flight. Other attributes of the Pilot entity are
pilot_fname, pilot_lname, pilot_dob and pilot_hiredate. After that there is Maintenance table.
There is one primary key process_no. Other attributes are process_name, process_frequency and
serial_no which are a foreign key. MaintainEvent is another entity that is used with the
Maintenance entity. The attributes of the MaintainEventare process_no, which is known as a
foreign key and also acts like the primary key for this table. The other attributes are event_date,
event_location and duration. After that comes Airplane entity. This table contains one primary
key which is the serial_no. There are two foreign keys also, flight_date and flight_no which are
present in the Flight entity. Other attributes of the Airplane table are model, manufacturer_name,
capacity and built_year. The PassengerEnity has one primary key which is passenger_id and two
foreign keys, which are flight_date and flight_no. The Passenger table also has passenger_name,
passenger_phone, passenger_address, passenger_fare and reserve_date attributes. Lastly, the
Fare entity has two attributes passenger_fare and reserve_date.
ER DIAGRAM