Airline Database Design

Air India Database Design
Contains:
1. ER-Diagram
2. Database Schema Diagram
3. Common Queries

1. Air India Database Design
DBMS (CSN-351)



Group Number - 1

Abhishek Jaisingh 14114002
Amandeep 14114008
Tirth Patel 14114036

​Table of Contents
ER Diagram 2
Assumptions 2
Initial Schema Diagram 3
Tables with FDs 4
Final Schema Diagram 8
Basic Operations with SQL queries and results 9
1

2. ER diagram




Assumptions
1.Thereisaroutecodetablenotlistedasseparatetableinourdesign,whichhas
information about all the intermediate airports in a journey with specific route code.
2.Apersoncanonlybook1ticketpertransaction.Anewtransactionisrequiredagainto
book more tickets.
3.FarefortheeachflightinAirFaretableiscalculatedseparatelyusingtheRouteofthe
flight.

2

3. Initial Schema diagram








3

4. Tables with functional dependencies
Country [BCNF]

Primary Key: CtID

CtID → CountryName

CREATE TABLE ​`Country`
(
`CtID`​ INT NOT NULL AUTO_INCREMENT,
`CountryName`​ varchar​(​32​)​ NOT NULL,
PRIMARY KEY ​(​`CtID`)
);
State [BCNF]

Primary Key: StID

StID → StateName | Country

CREATE TABLE ​`State`
(
`StID`​ INT NOT NULL AUTO_INCREMENT UNIQUE,
`StateName`​ varchar​(​32​)​ NOT NULL,
`Country`​ INT NOT NULL,
PRIMARY KEY ​(​`StID`​),
FOREIGN KEY ​(​`Country`​)​ REFERENCES ​`Country`​(​`CtID`)
);
Contact_Details [BCNF]

Primary Key: CnID

CnID → Email | Mobile | State

CREATE TABLE ​`Contact_Details`
(
`CnID`​ INT NOT NULL AUTO_INCREMENT,
`Email`​ varchar​(​50​)​ NOT NULL,
`Mobile`​ varchar​(​16​)​ NOT NULL,
`State`​ INT NOT NULL,
PRIMARY KEY ​(​`CnID`​),
FOREIGN KEY ​(​`State`​)​ REFERENCES ​`State`​(​`StID`)
4

5. );
Passenger [BCNF]

Primary Key: PsID

PsID → Name | Address | Age | Nationality | Contacts

CREATE TABLE ​`Passenger`
(
`PsID`​ INT NOT NULL AUTO_INCREMENT,
`Name`​ varchar​(​32​)​ NOT NULL,
`Address`​ varchar​(​64​)​ NOT NULL,
`Age`​ INT NOT NULL,
`Nationality`​ varchar​(​16​)​ NOT NULL,
`Contacts`​ INT NOT NULL,
PRIMARY KEY ​(​`PsID`​),
FOREIGN KEY ​(​`Contacts`​)​ REFERENCES ​`Contact_Details`​(​`CnID`)
);
Transaction [BCNF]

Primary Key: TsID

TsID → BookingDate | Passenger | Flight

CREATE TABLE ​`Transaction`
(
`TsID`​ INT NOT NULL AUTO_INCREMENT,
`BookingDate`​ DATETIME NOT NULL,
`Passenger`​ INT NOT NULL,
`Flight`​ INT NOT NULL,
PRIMARY KEY ​(​`TsID`​),
FOREIGN KEY ​(​`Passenger`​)​ REFERENCES ​`Passenger`​(​`PsID`​),
FOREIGN KEY ​(​`Flight`​)​ REFERENCES ​`Flight_Schedule`​(​`FlID`)
);
Flight_Schedule [2NF]

Primary Key: FlID

FlID → FlightDate | Departure | Arrival | AirCraft | NetFare
Departure → FlightDate

5

6. CREATE TABLE ​`Flight_Schedule`
(
`FlID`​ INT NOT NULL AUTO_INCREMENT,
`FlightDate`​ DATE NOT NULL,
`Departure`​ DATETIME NOT NULL,
`Arrival`​ DATETIME NOT NULL,
`AirCraft`​ INT NOT NULL,
`NetFare`​ INT NOT NULL,
PRIMARY KEY ​(​`FlID`​),
FOREIGN KEY ​(​`AirCraft`​)​ REFERENCES ​`AirCraft`​(​`AcID`​),
FOREIGN KEY ​(​`NetFare`​)​ REFERENCES ​`AirFare`​(​`AfID`)
);

Note:Thistableisin2NFduetothederivationofFlightDatefromDepartureattribute
whichistrivial.RemovingthisdependencybyaddingextratableonlyforFlightDate
enhancestheoverheadforqueries.Keepingitinthisformresultsinverylittlereduction
in memory efficiency.
AirFare [BCNF]

Primary Key: AfID

AfID → Route | Fare

CREATE TABLE ​`AirFare`
(
`AfID`​ INT NOT NULL AUTO_INCREMENT,
`Route`​ INT NOT NULL,
`Fare`​ INT NOT NULL,
PRIMARY KEY ​(​`AfID`​),
FOREIGN KEY ​(​`Route`​)​ REFERENCES ​`Route`​(​`RtID`)
);
Route [BCNF]

Primary Key: RtID

RtID → Airport | Destination | RouteCode

CREATE TABLE ​`Route`
(
`RtID`​ INT NOT NULL AUTO_INCREMENT,
`Airport`​ varchar​(​32​)​ NOT NULL,
`Destination`​ varchar​(​32​)​ NOT NULL,
`RouteCode`​ varchar​(​16​)​ NOT NULL UNIQUE,
PRIMARY KEY ​(​`RtID`)
6

7. );

Note:HereRouteCodewillbeisuniquenumberrecognizingtherouteoftheflight
between given 2 airports.
AirCraft [2NF]

Primary Key: AcID

AcID → Ac_Type | Capacity | Mfg_Date
Ac_Type → Capacity

Note:Toconvertinto​BCNF​,thistableisdecomposedinto2newfollowingtables
AirCraft​ and ​AirCraft_Type​.
AirCraft [BCNF] (from decomposition)

Primary Key: AcID

AcID → Ac_Type | Ac_Type | Mfg_Date

CREATE TABLE ​`AirCraft`
(
`AcID`​ INT NOT NULL AUTO_INCREMENT,
`Ac_Type`​ INT NOT NULL,
`Mfg_Date`​ DATE NOT NULL,
PRIMARY KEY ​(​`AcID`​),
FOREIGN KEY ​(​`Ac_Type`​)​ REFERENCES ​`AirCraft_Type`​(​`ActID`)
);
AirCraft_Type [BCNF] (from decomposition)

Primary Key: ActID

ActID → Type | Capacity

CREATE TABLE ​`AirCraft_Type`
(
`ActID`​ INT NOT NULL AUTO_INCREMENT,
`Type`​ varchar​(​32​)​ NOT NULL,
`Capacity`​ INT NOT NULL,
PRIMARY KEY ​(​`ActID`)
);
7

8. Final Schema Diagram

Thisisfinalschemadiagramafterdecomposition.These10tablesaremadeinourAirIndia
database design.








8

9. Basic Operations
Herearesomebasicoperationslistedthatourdatabaseprovides.Eachfunctionalityisshown
with corresponding sample SQL query and screenshot.

1.Listalltheaircraftsolderthan'2'years(canbeusedforexpirydatesor
maintenance date)

SELECT ​AcID​,​ ​Mfg_Date​,​ ​Type
FROM ​AirCraft​ INNER JOIN ​AirCraft_Type​ ON ​(​AirCraft​.​Ac_Type​ ​=​ ​AirCraft_Type​.​ActID​)
WHERE YEAR​(​CURDATE​())​ ​-​ YEAR​(​Mfg_Date​)​ ​>​ 2




2.List all the flights in database from airport 'New Delhi' to airport 'Bangalore'

SELECT ​FlId​,​ ​FlightDate​,​ ​Fare​,​ ​Departure​,​ ​Arrival
FROM ​((​Flight_Schedule​ INNER JOIN ​AirFare​ ON ​(​Flight_Schedule​.​NetFare​ ​=​ ​AirFare​.​AfID​)))
INNER JOIN ​Route​ ON ​(​Route​ ​=​ ​RtID​)
WHERE ​Airport​ ​=​ ​'New Delhi'​ AND ​Destination​ ​=​ ​'Bangalore'




9

10. 3.List all the passengers for flight no. '5'

SELECT NAME​,​ ​Age​,​ ​Mobile
FROM ​Transaction​ INNER JOIN ​Passenger​ ON ​(​Transaction​.​Passenger​ ​=​ ​PsID​)
INNER JOIN ​Contact_Details​ ON ​(​Contacts​ ​=​ ​CnID​)
WHERE ​Flight​ ​=​ ​5
ORDER BY NAME




4.List all the minor (age less than 18) travelling in flight no. '5'

SELECT ​Name​,​ ​Age​,​ ​Mobile​,​ ​Address​,​ ​StateName​,​ ​CountryName
FROM ​Transaction​ INNER JOIN ​Passenger​ ON ​(​Transaction​.​Passenger​ ​=​ ​PsID​)
INNER JOIN ​Contact_Details​ ON ​(​Contacts​ ​=​ ​CnID​)
INNER JOIN ​State​ ON ​(​State​ ​=​ ​StID​)
INNER JOIN ​Country​ ON ​(​CtID​ ​=​ ​Country)
WHERE ​Flight​ ​=​ ​5​ AND ​Age​ ​<​ ​18
ORDER BY NAME​,​ ​Age








10

11. 5.Total Fare collected from date '2016-10-01' to date '2016-10-20'

SELECT SUM​(​Fare​)
FROM ​Transaction​ INNER JOIN ​Flight_Schedule​ ON ​(​Flight​ ​=​ ​FlID​)
INNER JOIN ​AirFare​ ON ​(​NetFare​ ​=​ ​AfID​)
WHERE DATE ​(​BookingDate​)​ BETWEEN ​'2016-10-01'​ AND ​'2016-10-20'




6.Total number of tickets booked from date '2016-10-10' to date '2016-11-25'

SELECT COUNT​(​PsID​)​ AS ​Tickets
FROM ​Transaction​ INNER JOIN ​Passenger​ ON ​(​Transaction​.​Passenger​ ​=​ ​PsID​)
INNER JOIN ​Contact_Details​ ON ​(​Contacts​ ​=​ ​CnID​)
WHERE DATE ​(​BookingDate​)​ BETWEEN ​'2016-10-10'​ AND ​'2016-11-25';




7.Numberofpersonstravellingairport'NewDelhi'fromdate'2016-11-1'todate
'2016-11-30'

SELECT COUNT​(​PsID​)​ AS NUM_CUSTOMERS
FROM ​Route​ INNER JOIN ​AirFare​ ON ​(​RtID​ ​=​ ​Route​)
INNER JOIN ​Flight_Schedule​ ON ​(​AfID​ ​=​ ​NetFare​)
INNER JOIN ​Transaction​ ON ​(​Flight​ ​=​ ​FlID​)
INNER JOIN ​Passenger​ ON ​(​Transaction​.​Passenger​ ​=​ ​PsID​)
INNER JOIN ​Contact_Details​ ON ​(​Contacts​ ​=​ ​CnID​)
WHERE DATE ​(​FlightDate​)​ BETWEEN ​'2016-11-1'​ AND ​'2016-11-30'​ AND ​Airport​ ​=​ ​'New Delhi';




11

12. 8.Numberofpersonsreachingat'NewDelhi'fromdate'2016-11-1'todate
'2016-11-30'

SELECT COUNT​(​PsID​)​ AS NUM_CUSTOMERS
FROM ​Route​ INNER JOIN ​AirFare​ ON ​(​RtID​ ​=​ ​Route​)
INNER JOIN ​Flight_Schedule​ ON ​(​AfID​ ​=​ ​NetFare​)
INNER JOIN ​Transaction​ ON ​(​Flight​ ​=​ ​FlID​)
INNER JOIN ​Passenger​ ON ​(​Transaction​.​Passenger​ ​=​ ​PsID​)
INNER JOIN ​Contact_Details​ ON ​(​Contacts​ ​=​ ​CnID​)
WHERE DATE ​(​FlightDate​)​ BETWEEN ​'2016-11-1'​ AND ​'2016-11-30'​ AND ​Destination​ ​=​ ​'New Delhi';




9.Numberofpersonswithflightsfrom'NewDelhi'to'Bangalore'fromdate
'2016-11-1' to date '2016-11-30'

SELECT COUNT​(​PsID​)​ AS NUM_CUSTOMERS
FROM ​Route​ INNER JOIN ​AirFare​ ON ​(​RtID​ ​=​ ​Route​)
INNER JOIN ​Flight_Schedule​ ON ​(​AfID​ ​=​ ​NetFare​)
INNER JOIN ​Transaction​ ON ​(​Flight​ ​=​ ​FlID​)
INNER JOIN ​Passenger​ ON ​(​Transaction​.​Passenger​ ​=​ ​PsID​)
INNER JOIN ​Contact_Details​ ON ​(​Contacts​ ​=​ ​CnID​)
WHERE DATE ​(​FlightDate​)​ BETWEEN ​'2016-11-1'​ AND ​'2016-11-30'​ AND ​Airport​ ​=​ ​'New Delhi'
AND ​Destination​ ​=​ ​'Bangalore';




10. Provide State wise analysis of sales of flight tickets

SELECT ​StateName​,​ ​Count​(​PsID​)​ AS ​Tickets
FROM ​Transaction​ INNER JOIN ​Passenger​ ON ​(​Transaction​.​Passenger​ ​=​ ​PsID​)
INNER JOIN ​Contact_Details​ ON ​(​Contacts​ ​=​ ​CnID​)
INNER JOIN ​State​ ON ​(​State​ ​=​ ​StID​)
GROUP BY ​StateName
ORDER BY ​Count​(​PsID​)​ DESC;

12

13. 13