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.
From the given case study, it can be stated that following will be the required entities,
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 |
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 |
Employee table
FirstName |
LastName |
DateofBirth |
Hiringdate |
Pilotstatus |
ERD Diagram for the MPoly Airlines
Table Name |
Attribute |
Data type
|
Attribute description |
Format |
Range |
Mandatory |
PK/FK |
Reference table |
Passenger table |
||||||||
PassengerID |
Number |
unique passenger number |
P000 |
yes |
Primary Key |
|||
PassengerName |
Varchar |
Passenger name |
alen |
50 |
yes |
|||
PassengerContact |
number |
Contact details |
9563478411 |
yes |
||||
PassengerAddress |
varchar |
Address of passenger |
13th street |
100 |
yes |
|||
FlightID |
Varchar |
Booked flight id |
12542 |
Table Name |
Attribute |
Data type
|
Attribute description |
Format |
Range |
Mandatory |
PK/FK |
Reference table |
Booking table |
||||||||
BookingID |
Number |
Unique booking ID |
12545 |
yes |
PrimaryKey |
|||
PassengerID |
Number |
ID of the passenger |
112255 |
yes |
Foreign Key |
passengerable |
||
BookingtypeID |
Varchar |
Type of the booking |
50 |
yes |
||||
FlightID |
Number |
Unique ID for flight |
12345 |
yes |
Foreign Key |
Flightable |
||
BookingDate |
Datetime |
Date of booking |
03/11/2015 |
yes |
Table Name |
Attribute |
Data type
|
Attribute description |
Format |
Range |
Mandatory |
PK/FK |
Reference table |
Employeetable |
||||||||
EmployeeID |
Number |
Unique employee number |
4521 |
yes |
Primary Key |
|||
Employee FirstName |
Varchar |
Name of employee |
Helen |
50 |
yes |
|||
LastName |
varchar |
50 |
yes |
|||||
DateofBirth |
date |
Date of birth of Employee |
yes |
|||||
Hiringdate |
Date |
Date of Hiring |
yes |
|||||
Pilotstatus |
Varchar |
Status of the pilot |
20 |
yes |
Table Name |
Attribute |
Data type
|
Attribute description |
Format |
Range |
Mandatory |
PK/FK |
Reference table |
Flight table |
||||||||
FlightID |
Unique flight identification number |
yes |
Primary key |
Flight table |
||||
FlightSource |
Source of the flight |
50 |
yes |
|||||
EmployeeID |
Number |
yes |
Foreign key |
Employee table |
||||
FlightDestination |
Varchar |
Detination of the flight |
50 |
yes |
||||
ArrivalTime |
datetime |
Flight arrival time |
yes |
|||||
Depurturetime |
datetime |
Flight departure time |
yes |
|||||
AirplaneSerial Number |
Number |
Unique serial number of airplane |
yes |
Table Name |
Attribute |
Data type
|
Attribute description |
Format |
Range |
Mandatory |
PK/FK |
Reference table |
Airplanetable |
yes |
|||||||
Airplane Serial nuber |
int |
Unique serial number |
yes |
Primary key |
||||
Airplanemodel |
Varchar |
Model of the airplane |
50 |
yes |
||||
Manufacturer |
Varchar |
Manufacturer of the airplane |
50 |
yes |
||||
PassengerCapacity |
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
|
Attribute description |
Format |
Range |
Mandatory |
PK/FK |
Reference table |
Maintenance table |
||||||||
Procedure Number |
Varchar |
Unique number of the procedure |
yes |
Primary Key |
||||
Procedure name |
Varchar |
Name of procedure |
50 |
yes |
||||
Frequency |
Varchar |
Frequency of the procedure |
yes |
|||||
AirplaneSerial number |
Varchar |
Serial number of the airplane |
yes |
ForeignKey |
airplane Table |
Table Name |
Attribute |
Data type
|
Attribute description |
Format |
Range |
Mandatory |
PK/FK |
Reference table |
Maintenance event table |
||||||||
EventID |
Varchar |
Unique ID for event |
yes |
Primary key |
||||
Eventname |
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 procedure |
yes |
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
EventID + EventName à Location
Eventnameà Location
Airplane serial number +Airplane modelàPassenger Capacity
AirplaneModel+Manufacturer-à Built year
Flight ID+ Flight Source-à Flight Destination
Flight ID-Arrival time
FlightID +Source-àFlighgt Destination
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)
);
BookingID int,
PassengerID int,
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,
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 ,
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)
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”);
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’);
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’);
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order formOnce we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignmentAs soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download