BIT231: Database Systems - ERD Diagram, Entities, Attributes, SQL Commands and Insert Commands

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