airline database design
TRANSCRIPT
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
ER diagram
Assumptions 1. There is a route code table not listed as separate table in our design, which has
information about all the intermediate airports in a journey with specific route code.
2. A person can only book 1 ticket per transaction. A new transaction is required again to
book more tickets.
3. Fare for the each flight in AirFare table is calculated separately using the Route of the
flight.
2
Initial Schema diagram
3
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
);
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
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 : This table is in 2NF due to the derivation of FlightDate from Departure attribute which is trivial. Removing this dependency by adding extra table only for FlightDate enhances the overhead for queries. Keeping it in this form results in very little reduction 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
);
Note : Here RouteCode will be is unique number recognizing the route of the flight between given 2 airports.
AirCraft [2NF]
Primary Key: AcID
AcID → Ac_Type | Capacity | Mfg_Date Ac_Type → Capacity
Note : To convert into BCNF , this table is decomposed into 2 new following tables 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
Final Schema Diagram This is final schema diagram after decomposition. These 10 tables are made in our Air India database design.
8
Basic Operations Here are some basic operations listed that our database provides. Each functionality is shown with corresponding sample SQL query and screenshot.
1. List all the aircrafts older than '2' years (can be used for expiry dates or 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
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
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. Number of persons travelling airport 'New Delhi' from date '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';
11
8. Number of persons reaching at 'New Delhi' from date '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 Destination = 'New Delhi';
9. Number of persons with flights from 'New Delhi' to 'Bangalore' from date
'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