Database Modelling And SQL Concepts: Creating A Data Dictionary For MPoly Airlines

Creating a Data Dictionary for an ER Diagram

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)

);

CREATE TABLE Bookingtable (

 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’);