BIT231 Database Systems: MPoly Airlines Database Design Report

This report provides a comprehensive database design solution, including ER modeling, normalization, and SQL commands. It begins by outlining the business rules for the MPoly Airlines database, followed by identifying the necessary entities and attributes for tables such as Flight, Passenger, Employee, Booking, Maintenance, and Airplane. An ERD diagram visually represents the relationships between these entities. The report further details the attributes of each table, including data types, formats, and primary/foreign key designations. A dependency diagram illustrates the functional dependencies within the database. Finally, the report presents the SQL CREATE TABLE commands for each table and INSERT commands to populate the tables with sample data. This document is available on Desklib, a platform providing AI-based study tools and a repository of solved assignments for students.

Running head: BIT231: DATABASE SYSTEMS
BIT231: Database Systems
Name of the Student
Name of the University
Authors note

1BIT231: DATABASE SYSTEMS
Section 4Section 4
Section 1:
1. Business rules for the ERD
A booking can contain more than one passenger with multiple booking.
Each booking should have a maximum number of passengers for which booking on a
flight can be done.
There will be only one employee/pilot for a flight at an instance.
An employee may have multiple flights but not on same date.
The employees with training status could not be assigned to a flight.
The booking for a flight would have a highest limit as per the capacity of the airplane
model.
The departure and arrival time for a flight should not be the same.
The number of maintenance of an airplane should not cross the frequency (the
airplanes should not be assigned for maintenance in certain time periods).
Flight source and the destination never be same as it may lead to inconsistency of the
data.
Flights under maintenance should not be available for booking for certain time period
i.e. the event date and the flight date should not be same for a unique serial airplane number
to avoid the confusion for the availability of data.
2. Entities and attributes
From the given case study, it can be stated that following will be the required entities,

2BIT231: DATABASE SYSTEMS
Section 4Section 4
Flight table, passenger table, employee table, booking table, maintenance table,
airplane table.
Required attributes for the tables
Employee table
Passenger table
PassengerName
PassengerContact
PassengerAddress
Passenger table
Bookingtable
BookingID
PassengerID
BookingtypeID
BookingTypeID
FlightID
BookingDate

3BIT231: DATABASE SYSTEMS
Section 4Section 4
Maintenance table
Procedure Number
Procedurename
Frequency
AirplaneSerial
Eventname
EventDate
Location
Airplanetable
Airplanemodel number
Manufacturer
PassengerCapacity
Builtyear
Status
Flight table
FlightSource
EmployeeID
FlightDestination
ArrivalTime
Depurturetime
AirplaneSerial

4BIT231: DATABASE SYSTEMS
Section 4Section 4
Employee table
FirstName
LastName
DateofBirth
Hiringdate
Pilotstatus

5BIT231: DATABASE SYSTEMS
Section 4Section 4
3. ERD Diagram for the MPoly Airlines
Figure 1: ERD diagram for Mpoly Airlines

6BIT231: DATABASE SYSTEMS
Section 4Section 4
Section 2:
Table Name Attribute Data
type
Attr
ibut
e
desc
ripti
on
For
mat
Range Man
dato
ry
PK/FK Reference
table
Passenger
table
PassengerID Number uniq
ue
pass
enge
r
num
ber
P00
0
yes Primary
Key
PassengerNa
me
Varchar Pass
enge
r
nam
alen 50 yes

7BIT231: DATABASE SYSTEMS
Section 4Section 4
e
PassengerCo
ntact
number Cont
act
detai
ls
9563
4784
11
yes
PassengerAd
dress
varchar Add
ress
of
pass
enge
r
13th
stree
t
100 yes
FlightID Varchar Boo
ked
fligh
t id
1254
2
Table Name Attribute Data type Attribut
e
descripti
on
Format R
a
n
g
e
Man
dato
ry
PK/FK Reference
table

8BIT231: DATABASE SYSTEMS
Section 4Section 4
Booking table
BookingI
D
Number Unique
booking
ID
1
2545
yes Primary
Key
PassengerI
D
Number ID of the
passenge
r
1
12255
yes Foreign
Key
passengera
ble
Bookingty
peID
Varchar Type of
the
booking
5
0
yes
FlightID Number Unique
ID for
flight
1
2345
yes Foreign
Key
Flightable
BookingD
ate
Datetime Date of
booking
03/11/2
015
yes

9BIT231: DATABASE SYSTEMS
Section 4Section 4
Table Name Attribute Data type Attribut
e
descripti
on
Format R
a
n
g
e
Man
dato
ry
PK/FK Reference
table
Employeetable
EmployeeI
D
Number Unique
employe
e number
4
521
yes Primary
Key
Employee
FirstName
Varchar Name of
employe
e
Helen 5
0
yes
LastName varchar 5
0
yes
DateofBirt
h
date Date of
birth of
Employe
yes

10BIT231: DATABASE SYSTEMS
Section 4Section 4
e
Hiringdate Date Date of
Hiring
yes
Pilotstatus Varchar S
tatus of
the pilot
2
0
yes
Table Name Attribute Data type Attribut
e
descripti
on
For
mat
Ra
ng
e
Man
dato
ry
PK/FK Reference
table
Flight table
FlightID Unique
flight
identifica
tion
number
yes Primary
key
Fli
ght table

11BIT231: DATABASE SYSTEMS
Section 4Section 4
FlightSour
ce
Source
of the
flight
50 yes
EmployeeI
D
Number yes Foreign
key
Em
ployee
table
FlightDest
ination
Varchar Detinatio
n of the
flight
50 yes
ArrivalTi
me
datetime Flight
arrival
time
yes
Depurturet
ime
datetime Flight
departure
time
yes
AirplaneS
erial
Number
Number Unique
serial
number
of
yes