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

This article discusses the ERD diagram, entities, attributes, SQL commands and insert commands for BIT231: Database Systems. It covers business rules for the ERD, required entities and attributes, dependency diagram, and create table SQL commands with insert commands for Employee, Flight, Airplane, Passenger, Booking, Maintenance Event and Maintenance tables.

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

12BIT231: DATABASE SYSTEMS
Section 4Section 4
airplane
Table Name Attribute Data type Attribut
e
descripti
on
For
mat
Ra
ng
e
Man
dato
ry
PK/FK Reference
table
Airplanetable yes
Airplane
Serial
nuber
int Unique
serial
number
yes Primary
key
Airplanem
odel
Varchar Model of
the
airplane
50 yes
Manufactu
rer
Varchar Manufac
turer of
the
airplane
50 yes

13BIT231: DATABASE SYSTEMS
Section 4Section 4
Passenger
Capacity
Number Capacity
f airplane
O yes
Builtyear Datetime Year of
built of
the
airplane
yes
Status Varchar Status of
the
airplane
20 yes
Table Name Attribute Data type Attribut
e
descripti
on
For
mat
Ra
ng
e
Man
dato
ry
PK/FK Reference
table
Maintenance
table

14BIT231: DATABASE SYSTEMS
Section 4Section 4
Procedure
Number
Varchar Unique
number
of the
procedur
e
yes Primary
Key
Procedure
name
Varchar Name of
procedur
e
50 yes
Frequency Varchar Frequenc
y of the
procedur
e
yes
AirplaneS
erial
number
Varchar Serial
number
of the
airplane
yes Foreign
Key
airplane
Table
Table Name Attribute Data type Attribut
e
descripti
on
For
mat
Ra
ng
e
Man
dato
ry
PK/FK Reference
table

15BIT231: DATABASE SYSTEMS
Section 4Section 4
Maintenance
event table
EventID Varchar Unique
ID for
event
yes Primary
key
Eventnam
e
Varchar Name of
the event
50 yes
EventDate Datetime Date for
the event
yes
Location Varchar Location
of the
even
50 yes
Duration Varchar Duration
of the
procedur
e
yes

16BIT231: DATABASE SYSTEMS
Section 4Section 4
Section 3
Dependency diagram
Here, passenger ID-Primary Key
Primary keyPassengerID
PassengerID + PasssnegerNamePassengerContact
PassengerID + PasssnegerNameBookingID
Primary key Booking ID
BookingID+FlightID  Booking Date
Primary key Event ID

17BIT231: DATABASE SYSTEMS
Section 4Section 4
EventID + EventName  Location
Eventname Location
Airplane serial number +Airplane modelPassenger Capacity
AirplaneModel+Manufacturer- Built year

18BIT231: DATABASE SYSTEMS
Section 4Section 4
Flight ID+ Flight Source- Flight Destination
Flight ID-Arrival time
FlightID +Source-Flighgt Destination
Section 4
Create table SQL Commands
CREATE TABLE Passengertable (
PassengerID int,
PassengerName varchar (50),
PassengerContact int,
PassengerAddress varchar (100),
FlightID int,
PRIMARY KEY (PassengerID),
FOREIGN KEY (FlightID) REFERENCES Flighttable (FlightID)
);
CREATE TABLE Bookingtable (
BookingID int,
PassengerID int,

19BIT231: DATABASE SYSTEMS
Section 4Section 4
BookingtypeID int,
FlightID int,
BookingDate Datetime,
PRIMARY KEY (BookingID),
FOREIGN KEY (PassengerID) REFERENCES Passengertable (PassengerID)
);
CREATE TABLE Maintenanceeventtbl (
EventID number,
Eventname varchar,
EventDate Datetime,
Location Varchar (50),
Duration number,
PRIMARY KEY (EventID)
);
CREATE TABLE Maintenancetbl (
Procedure Number int,
Procedurename varchar (50),
Frequency int,
AirplaneSerial number,

20BIT231: DATABASE SYSTEMS
Section 4Section 4
PRIMARY KEY (Procedure Number),
FOREIGN KEY (AirplaneSerialnumber) REFERENCES Airplanetable
(AirplaneSerialnumber)
);
CREATE TABLE Airplanetable (
AirplaneSerialnumber int,
Airplanemodel int,
Manufacturer varchar (50),
PassengerCapacity int,
Builtyear datetime,
Status varchar (20),
PRIMARY KEY (AirplaneSerialnumber),
);
CREATE TABLE Flighttable (
FlightID number,
FlightSource varchar (50),
EmployeeID number,
FlightDestination varchar (50),
ArrivalTime datetime,
Depurturetime date time ,

21BIT231: DATABASE SYSTEMS
Section 4Section 4
PRIMARY KEY (FlightID), FOREIGN KEY (EmployeeID) REFERENCES
employeetable(EmployeeID),
);
CREATE TABLE Employeetable (
EmployeeID int,
Employee FirstName varchar (50),
LastName varchar (50),
DateofBirth (Datetime),
Hiringdate (Datetime),
Pilotstatus varchar (50),
PRIMARY KEY (EmployeeID)
);

22BIT231: DATABASE SYSTEMS
Section 4Section 4
Insert commands for the tables
For Employee table
INSERT INTO Employeetable (EmployeeNumber, Employee FirstName, LastName,
DateofBirth, Hiringdate, Pilotstatus) VALUES ('00145', 'Erich', 'Skage’, ‘18-12-1988',
'Training');
INSERT INTO Employeetable (EmployeeNumber, Employee FirstName, LastName,
DateofBirth, Hiringdate, Pilotstatus) VALUES ('00215', 'Alan', 'walker', '11-02-1985',
'Working');
INSERT INTO Employeetable (EmployeeNumber, Employee FirstName, LastName,
DateofBirth, Hiringdate, Pilotstatus) VALUES ('00512', 'Justin', 'Trudo’, ‘8-12-1990',
'Training');
For Flight table
INSERT INTO Employeetable (FlightID, FlightSource, EmployeeID,
FlightDestination, ArrivalTime, Depurturetime, AirplaneSerial) VALUES ('12512', '',
'Sydney’, ‘00215', 'Canberra', “30-06-2018 13:15:00”, “30-06-2018 14:40:00”, “MPL1234”);
INSERT INTO Employeetable (FlightID, FlightSource, EmployeeID,
FlightDestination, ArrivalTime, Depurturetime, AirplaneSerial ) VALUES ('12542', '',
'NSW’, ‘00174', 'Sydney', “3-08-2018 13:25:00”, “03-08-2018 1:40:00”, “MPL2422”);
INSERT INTO Employeetable (FlightID, FlightSource, EmployeeID,
FlightDestination, ArrivalTime, Depurturetime, AirplaneSerial ) VALUES ('3214', '',
'Gabba’, ‘00124', 'Victoria', “30-06-2018 16:15:00”, “30-06-2018 19:40:00”, “MPL1477”);

23BIT231: DATABASE SYSTEMS
Section 4Section 4
For Airplanetable
INSERT INTO Airplanetable (Airplanemodel , Manufacturer , PassengerCapacity,
Builtyear ,Status) VALUES ('00512', 'Justin', 'Trudo’, ‘8-12-1990', 'Training');
INSERT INTO Airplanetable (Airplanemodel, Manufacturer, PassengerCapacity,
Builtyear, Status) VALUES ('00512', 'Justin', 'Trudo’, ‘8-12-1990', 'Training');
INSERT INTO Airplanetable (Airplanemodel, Manufacturer, PassengerCapacity ,
Builtyear ,Status ) VALUES ('00512', 'Justin', 'Trudo’, ‘8-12-1990', 'Training');
For Maintenancetbl
INSERT INTO Maintenancetbl
(Procedure, Procedurename, Frequency, AirplaneSerial ) VALUES ('P142',
'Hydraulic check', 'Twice’, “MPL1477”);
INSERT INTO Maintenancetbl
(Procedure, Procedurename, Frequency, AirplaneSerial) VALUES ('P521', 'Engine
Efficiency check', 'once’, ‘MPL2422');
INSERT INTO Maintenancetbl
(Procedure, Procedurename, Frequency, AirplaneSerial) VALUES ('P401', 'Throttle
checking', 'once’, ‘MPL2422');
For Maintenanceeventtbl
INSERT INTO Maintenanceeventtbl (EventID ,Eventname ,EventDate, Location,
Duration ) VALUES ('E345', 'Engine Check', '8-12-2018', 'Sydney', ’50 mins’);
INSERT INTO Maintenanceeventtbl (EventID ,Eventname ,EventDate, Location,
Duration ) VALUES ('E405', 'Performance check ', '22-0-2018', 'NSW', ‘90 mins’);

24BIT231: DATABASE SYSTEMS
Section 4Section 4
INSERT INTO Maintenanceeventtbl (EventID ,Eventname ,EventDate, Location,
Duration ) VALUES ('E333', 'Propeller check', '8-09-2018', 'Sydney', ‘150mins’);
For Passengertable
INSERT INTO Employeetable (PassengerID,PassengerName , PassengerContact,
PassengerAddress, FlightID) VALUES ('P11512', 'Justin', '4587963211’, ‘Sydney', ‘F1412’);
INSERT INTO Employeetable (PassengerID,PassengerName , PassengerContact,
PassengerAddress, FlightID) VALUES ('P1323', 'Julian', '1287922211’, ‘NSW', ‘F1478’);
INSERT INTO Employeetable (PassengerID,PassengerName , PassengerContact,
PassengerAddress, FlightID) VALUES ('P4252', 'Julia', '4586663771’, ‘Sydney', ‘F2452’);
For Bookingtable
INSERT INTO Employeetable (BookingID, PassengerID ,BookingtypeID ,FlightID,
BookingDate ) VALUES ('B00412', 'P4252', ‘Economy’, ‘12512’, ’12-04-2018');
INSERT INTO Employeetable (BookingID, PassengerID ,BookingtypeID ,FlightID,
BookingDate ) VALUES ('B00147', 'P42211', ‘Economy’, ‘12512’, ’12-04-2018');
INSERT INTO Employeetable (BookingID, PassengerID ,BookingtypeID ,FlightID,
BookingDate ) VALUES ('B00458', 'P66551', ‘Economy’, ‘12512’, ’12-04-2018');