Entities And Attributes For Flight Reservation System

Flight

The business rules from the case study are;

• MPloy airline has many flights.
• A flight has many passengers on board.
• A flight uses one and only one airplane.
• A flight has one and only one pilot who flies the single plane.
• The airline has many maintenance procedures that are done on airplanes.
• A maintenance event involves one airplane undergoing one procedure.

Entities and attributes

Based on the business rules and the case study, the following entities with their attributes can be derived;

• Flight (flightNO, date, departureTime, arrivalTime, pilotNO, serialNO)
• Passenger (passengerNO, name, address, telNO)
• Flight_passengers (passengerNO, flightNO, fare, reservationDate)
• Pilot (pilotNO, firname, lastName, dob, dateOfHire)
• Airplace (serialNO, model, manufacturer, yearBuilt)
• Procedure (procedureNO, procedureName, frequency )
• Maintenance_event (eventID, location, duration, procedureNO, serialNO)

Entity relationship diagram

Table Name	Attribute	Attribute description	Date type	Date format	range	mandatory	Primary key/ foreign key	Foreign key reference table
Flight	FlightNO	Unique value identifying a flight	CHAR(5)	F001		Y	PK
	Date	The date the flight is set to happen	Date	dd/mm/yyyy		Y	PK
	departureTime	The time will the plane will take off	CHAR(10)	12:00pm	6-10	Y
	arrivalTime	The time the plane will land at the destination	CHAR(10)	02:00AM	6-10	Y
	pilotNO	Unique identifier of the pilot who will fly the plane	CHAR(4)	E001		Y	FK	Pilot
	serialNO	Unique identifier of the plane that will be used	CHAR(4)	A001		Y	FK	Airplane
Passenger	PasserngerNO	Unique identifier of a passenger	CHAR(4)	P001		Y	PK
	Name	The names of a passenger	VARCHAR(100)	John Doe	25-50	Y
	Address	The address of the passenger	VARCHAR(250)	1st street kings landing	50-100	Y
	telNO	The tel of the passenger	VARHCHAR(15)	+ 23 23 323	10-15	Y
Flight_passengers	passengerNO	Unique identifier of the passenger in the flight	CHAR(4)			Y	PK, FK	Passenger
	flightNO	Unique identifier of the flight	CHAR(4)			Y	PK,FK	Flight
	flightDate	Unique date of a flight					PK,FK	flight
	Fare	The fare paid by the passenger	DECIMAL	100.99		Y	P
	reservationDate	The date the passenger made a reservation	date	DD/MM/YYYY		Y
Pilot	PilotNO	Unique identifier of a pilot	CHAR(4)	E001		Y	PK
	firstName	The first name of the pilot	VARCHAR(50)	John	8-15	Y
	lastName	The last name of the pilot	VARCHAR(50)	Doe	8-15	Y
	dob	The date of birth of the pilot	Date	DD/MM/YYYY		Y
	dateOfHire	The date the pilot was hired	date	DD/MM/YYYY		Y
Airplane	serialNO	Unique identifier of an airplane	CHAR(4)	A001		Y	PK
	Model	The model of the airplace	VARCHAR(25)	Boeing 737 F	15-20	Y
	Manufacturer	Manufacturer name	VARCHAR(50)	Tesla Motors	15-35	Y
	yearBuilt	The year the airplane was manufactured	INTEGER(4)	2005	4	Y
procedures	procedureNO	Unique identifier of a procedure	CHAR(4)	PR01		Y	PK
	procedureName	Name of the procedure	VARCHAR(50)	Engine checkup	10-30	Y
	frequency	Frequency the procedure has to be done on a plane	INTEGER(9)	5	1-5	Y
Maintenance_event	eventID	Unique key identifying a maintenance event	CHAR(4)	M001		Y	PK
	Location	Location the maintenance event took place	VARCHAR(25)	Melbourne	5-25	Y
	Duration	The duration in hours it took to complete the event	INTEGER(5)	24		N
	procedureNO	Foreign key referencing a unique procedure	CHAR(4)			Y	FK	Procedure
	serialNO	Foreign key referencing a unique plane	CHAR(4)			Y	FK	airplane

• Table flight

Primary key: (flightNO, date)

Foreign key: pilotNO, serialNO

Normal Form:3NF

• Table passenger

Primary key : (passengerNO)

Foreign key: None

Normal Form: 3NF

• Table flight_passengers

Primary key: (passengerNO, flightNO, date)

Foreign key: passengerNO, flightNO, date

Normal Form : 3NF

• Table pilot

Primary key : ( pilotNO )

Foreign key : None

Normal Form : 3NF

• Table airplane

Primary key : SerialNO

Foreign key: None

Normal Form: 3NF

• Table procedure

Primary key: procedureNO

Foreign key: None

Normal form: 3NF

• Table maintenance_event

Primary key: eventID

Foreign key:procedureNO, serialNO

Normal Form: 3NF

SQL: DDL and DML statements

DDL

create table passenger(

     passengerNO char(4) primary key,

     name varchar(100) not null,

     address varchar(250) not null,

     telNO varchar(15) not null

);

create table pilot(

     pilotNO char(4) primary key,

     firstName varchar(50) not null,

     lastName varchar(50) not null,

     dob date not null,

     dateOfHire date not null

create table airplane (

     serialNO char(4) primary key,

     model varchar(25) not null,

     manufatucturer varchar(50) not null,

     yearBuilt integer(4) not null

create table procedures (

     procedureNO char(4) primary key,

     procedureName varchar(50) not null,

     frequency integer(9) not null

create table flight(

     flightNO char(4) not null,

     flightDate date not null,

     departureTime char(10) not null,

     arrivalTime char(10) not null,

     pilotNO char(4) not null,

     serialNO char(4) not null,

     primary key (flightNO,flightDate),

     foreign key (pilotNO) references pilot (pilotNO),

     foreign key (serialNO) references airplane (serialNO)

 create table flight_passengers (

     passengerNO char(4)  not null,

     flightNO char(4) not null,

     flightDate date not null,

     fare decimal not null,

     reservationDate date not null,

     primary key (passengerNO,flightNO,flightDate),

     foreign key (passengerNO) references passenger (passengerNO),

     foreign key (flightNO,flightDate) references flight (flightNO,flightDate)

 create table maintenance_event (

     eventID char(4) primary key,

     location varchar(25) default ‘melbourne’ not null,

     duration integer(5) not null,

     procedureNO char(4) not null,

     serialNO char(4) not null,

     foreign key (procedureNO) references procedures (procedureNO),

     foreign key (serialNO) references airplane (serialNO)

DML Scripts

INSERT INTO `airplane` (`serialNO`, `model`, `manufatucturer`, `yearBuilt`) VALUES

(‘A001’, ‘Boieng 737’, ‘Boieng’, 2012),

(‘A002’, ‘Airbus 3243’, ‘Airbus’, 2012),

(‘A003’, ‘Boeing 335’, ‘Boeing ‘, 2014)

INSERT INTO `passenger` (`passengerNO`, `name`, `address`, `telNO`) VALUES

(‘P001’, ‘Jon Snow’, ‘winterfell’, ‘+ 434 3434’),

(‘P002’, ‘Arya stark’, ‘winterfell’, ‘+34432 324324’),

(‘P003’, ‘Brianne of Tarth’, ‘kings landing’, ‘+3 433443’)

INSERT INTO `pilot` (`pilotNO`, `firstName`, `lastName`, `dob`, `dateOfHire`) VALUES

(‘E001’, ‘Peter’, ‘Griffin’, ‘2018-05-22’, ‘2019-02-23’),

(‘E002’, ‘Cleveland’, ‘Brown’, ‘2018-03-05’, ‘2018-09-11’),

(‘E003’, ‘Lois’, ‘Griffin’, ‘2012-03-05’, ‘2014-03-05’)

INSERT INTO `procedures` (`procedureNO`, `procedureName`, `frequency`) VALUES

(‘PR01’, ‘Engine checkup’, 36),

(‘PR02’, ‘Seats checkup’, 24),

(‘PR03’, ‘Pilot bay controls checkup’, 48);

INSERT INTO `flight` (`flightNO`, `flightDate`, `departureTime`, `arrivalTime`, `pilotNO`, `serialNO`) VALUES

(‘F001’, ‘2018-09-11′, ’10:00PM’, ‘)8:00AM’, ‘E001’, ‘A001’),

(‘F002’, ‘2018-09-18′, ’12:00AM’, ’12:00PM’, ‘E002’, ‘A002’),

(‘F003’, ‘2018-09-18′, ’03:00AM’, ’12:00PM’, ‘E003’, ‘A003’);

INSERT INTO `flight_passengers` (`passengerNO`, `flightNO`, `flightDate`, `fare`, `reservationDate`) VALUES

(‘P001’, ‘F001’, ‘2018-09-11’, ‘234’, ‘2018-09-12’),

(‘P002’, ‘F002’, ‘2018-09-18’, ‘433’, ‘2018-09-18’),

(‘P003’, ‘F003’, ‘2018-09-18’, ‘2434’, ‘2018-09-18’);

INSERT INTO `maintenance_event` (`eventID`, `location`, `duration`, `procedureNO`, `serialNO`) VALUES

(‘M001’, ‘melbourne’, 24, ‘PR01’, ‘A001’),

(‘M002’, ‘melbourne’, 45, ‘PR02’, ‘A002’),

(‘M003’, ‘melbourne’, 34, ‘PR03’, ‘A003’);

Default clause

Using default for a column means that the column will always have a default value during data insertion unless the default vaue is changed. From the database the location column in maintenance_events table is set to default meaning if it is not specified then the default value is inserted