MyPoly Airlines - Database

MyPoly Airlines - Database
Table of Contents
1. ER Modelling
...........................................................................2
1.1. Business rules..............................................................................2
1.2. Entitles and attributes....................................................................2
1.3. ER diagram................................................................................3
2. Data dictionary
.......................................................................... 4
3. Dependency diagram
.................................................................... 8
4. SQL statements – DDL and DML
......................................................9
4.1. CREATE and INSERT statements.......................................................9
4.2. Screenshots
.........................................................................11
1

MyPoly Airlines - Database
1. ER Modelling
1.1. Business rules
Following are key business rules of the proposed database system of MPoly Airlines:
 A passenger cannot take more than one flight at the same time.
 A pilot cannot fly more than one flight at the same time.
 The date of flight cannot be less than the actual flight date of the passenger.
 An airline cannot fly on more than one route at the same time.
 An airline can have only one maintenance at a time.
1.2. Entitles and attributes
AIRPLANE
Attributes
airplaneNumber
airplaneModel
passengerCapacity
manufatureName
yearOfBuilt
MAINTENANCE
Attributes
procedureNumber
procedureName
maintenancefrequency
AIRPLANE_ MAINTENANCE
Attributes
procedureNumber
airplaneNumber
eventDate
eventLocation
eventDuration
FLIGHT
Attributes
flightNumber
actuaDepartureTime
actualArrivalTime
FLIGHT_AIRPLANE
Attributes
airplaneNumber
flightNumber
PILOT
Attributes
pilotNumber
pilotFirstName
2

MyPoly Airlines - Database
pilotLastName
birthDate
hireDate
PILOT_FLIGHT
Attributes
flightNumber
pilotNumber
dateOfFlight
PASSENGER
Attributes
passengerNumber
passengerName
passengerAddress
passengerTelephone
FLIGHT_PASSENGER
Attributes
flightNumber
passengerNumber
flightFare
dateOfFlight
1.3. ER diagram
3

MyPoly Airlines - Database
2. Data dictionary
Table
Name
Attribute Attribute
Description
Data Type Data
Form
at
Rang
e
Manda
tory
Primary
Key/Forei
gn key
Foreign key
reference
table
AIRP
LAN
E
airplaneNumb
er
Unique
identifier of
Airplane
INTEGER 1111 Y PK
airplaneMode
l
Model of the
airplane
VARCHAR
(10)
Model
-1
Y
passengerCap
acity
Number
passengers
accommodate
d at a time
INTEGER 30 Y
manufatureNa
me
Name of the
manufacturer
of the airplane
VARCHAR
(30)
A&A Y
yearOfBuilt Year in which
airplane is
built
INTEGER 1994 0000-
2019
Y
Table Name Attribute Attribute
Description
Data Type Data
Forma
t
Rang
e
Ma
nda
tory
Primary
Key/Forei
gn key
Foreign
key
referen
ce table
MAINTENA
NCE
procedureNum
ber
Unique
identifier of
maintenanc
e
INTEGER 1111 Y PK
procedureNam
e
Name of the
procedure
VARCHAR
(20)
Proced
ure-1
Y
maintenancefr
equency
Number of
times a
procedure
can be
implemente
d
INTEGER 5 Y
Table
Name
Attribute Attribute
Description
Data Type Data
Form
at
Rang
e
Manda
tory
Primary
Key/Forei
gn key
Foreign key
reference
table
AIRP
LAN
E_M
AINT
ENA
NCE
procedureNu
mber
Identifier of
procedure
INTEGER 1111 Y PK, FK MAINTEN
ANCE
airplaneNumb
er
Identifier of
airplane
INTEGER 2222 Y PK, FK AIRPLANE
4