Airlines Reservation

System
Entity- Relationship Diagram
Normalization & File Mapping
Rashmi Singh ---- 060341049
Abhishek Sonkamble ----
060341059
Paras Verma ----
060341063
Abhijeet Meshram ----
 Agenda
 Problem Statement – Airlines Reservation
System
 What to do List
 Definitions
 Entities & Attributes Identified
 Relationship Types between different
Entities
 Entity - Relationship Diagram
 Normalization
 Dependencies
 Table Design for Entities
 Airlines Reservation System
Company Detail:
Jet Airlines offer numerous flights between various
destinations across India every day. Information is
maintained about flights, which are Flight Number,
Departure Time, Arrival Time, Departure City,
Arrival City, Number of Seats etc.
Operational Structure:
 To reserve a seat on any of the Flight, a Customer
calls a toll-free Number and gives the Ticketing
Agent his/her detail. Which include the personal
details and credit card details.
 If the seat is available the Ticketing Agent issues
the Ticket stating the Ticket Number, Flight Details,
Ticketing Conditions, Fare.
 The Jet Ticketing policies requires that each
 What to do List

 Identify the main Entity type

 Identify the Relationship type
 Draw Entity-Relationship Diagram
 Normalize the Data
 Give the detailed Table Design for the
System
 Definitions
 Entity:
 A “Thing” or “Object” in the Real world
that is distinguishable from all other
Objects.

 Attribute:
 Property of an Entity or which describes
an Entity.

 Entity Set:
 A set of Entity of same type that share
the same properties or attributes.
 Definitions
 Primary Key:
 An Attribute that is unique and is used
to identify a single instance of Entity set.

 Foreign Key:
 An Attribute or set of Attributes within
one relation that matches the candidate
key of some relation.

 Candidate Key:
 The set of attributes that uniquely
identifies each occurrences of entity
 Definitions E-R Diagram
 Domain:
 Set of allowable values for one or more
attributes.
 Relation:
 A Relation is Table with Rows and Columns.
 Degree:
 Number of Attributes it Contains.
 Cardinality:
 Cardinality of relation is the number of
Attributes it contains.
 Relationship:
 Entities Identified

 Flight_Detail
 Ticket_Detail
 Cust_Detail
 Trav_Agent
 Boarding_Pass
 Attributes Identified
 Flight_Detail  Boarding_P  Cust_Detail
 Flight_No (PK) ass  Cust_Id (PK)
 Dep_Time  BP_Id (PK)  Cust_Name
 Arr_Time  Flight_No  Cust_Age
 Dep_City (FK)  Cust_Add
 Arr_City
 Cust_Phone
 No_Seats
 Cust_CardNo
 Ticket_No
 Ticket_Detail
 Ticket_No (PK)  Trav_Agent
 Fare  TravAg_Id
 Seat_No (PK)
 Ticket_Type  TravAg_Nam
 Ticket_Cond e
 Flight_No (FK)  TravAg_Add
 TravAg_Id (FK)  TravAg_City
 E-R Symbol Set
 Entity

 Attributes
 Primary Key

 Foreign Key

 Relationship

 Connectors
 Relationship Types

Flight_Deta Ha Ticket_Deta
il s il

Flight_Id Ticket_No Flight_Id

Flight_Deta Ha Broading_P
il s ass

Flight_Id BP_Id Flight_Id

 Relationship Types

Ticket_Detai Ha Cust_Detail
l s

Ticket_No Cust_Id Cust_Id

Ticket_Detai Ha Trav_Agent
l s

Ticket_No TravAg_Id TravAg_Id

 Entity-Relationship Diagram

Flight_Det Ha Trav_Agen
s
ail t

Flight_Id Flight_Id TranAg_Id

Ha Ticket_Det Ha
s s
ail
Ticket_Id TranAg_Id

Boarding_P Ha Cust_Detai
s
ass l
Cust_Id Ticket_Id
Flight_Id BP_Id Cust_Id
 Normalization
Normalization is a process of simplifying the
relationship between data in a record. It carried
out for following reasons.
Purpose of Normalization.
• To Simplify the maintenance of data through updates,
insertions, & deletions
• To allow simple retrieval of data in response to query
and requests
• To avoid restructuring of data when new application
requirements arises
• To structure the data so that any relationship can be
easily represented
• To eliminate redundant data and ensures data
 Normal Forms
 First Normal Form:
 Eliminate duplicative columns from same table
 Create separate table for each group of related data
 Identify each row with a unique column or set of
columns
 Second Normal Form:
 Meets requirements of First Normal Form
 Remove subsets of data that apply to multiple rows of
a table and place them in a separate table
 Create relationships between new tables through the
use of foreign the use of foreign keys
 Third Normal Form:
 Meet requirement of 2nd Normal Form
 It removes columns that are not dependent upon
primary key
 Unnormalized Data
Flight D …… No_S Ticke S …… BP_I TravAg Trav …… Trav Cust Cust …… Cust
_No ep_T …… eats t_No eat_ …… d _Id Ag_N …… Ag_C _Id _Na …… _Pho
ime …… No …… ame …… ity me …… ne

FD FD FD FD
FD

FD
 Functional Dependencies
 Flight_Detail
 Primary key - Flight_No
 Flight_No  Dep_Time, Arr_Time, Dep_City,
Arr_City
No_Seats, Ticket_No, BP_Id
 Ticket_Detail
 Primary key - Ticket_No
 Ticket_No  Fare, Seat_No, Ticket_Type,
Ticket_Cond
 Trav_Agent
 Primary key – TravAg_Id
 TravAg_Id  TravAg_Name, TravAg_Add,
TravAg_City, TravAg_City,
Continued…..
 Functional Dependencies
(Cont..)
 Cust_Detail
 Primary Key – Cust_Id
 Cust_Id  Cust_Name, Cust_Age, Cust_Add,
Cust_Phone, Cust_CardNo

After Removal of Functional Dependencies we obtain tables in First Normal Form

Note:
There is no Partial Dependency.
There is no Transitive Dependency.
As there is no Partial & Transitive Dependencies we obtain tables in Third Normal Form
 Table Design for Entities
 Flight_Detail
Attribute Type Size Key Constraint
Flight_No Varchar 10 PK Unique number

Dep_Time Time --- Time in 24 hour

clock
Arr_Time Time --- Time in 24 hour
clock
Dep_City Varchar 20 Valid city name

Arr_City Varchar 20 Valid city name

No_Seats Number 3
 Table Design for Entities
 Ticket_Detail
Attribute Type Size Key Constraint
Ticket_No Number 10 PK Unique Number

Fare Number 6

Seat_No Number 3

Ticket_Type Varchar 20

Ticket_Cond Varchar 50 Conditions

Flight_No Varchar 10 FK

TravAg_Id Number 6 FK
 Table Design for Entities
 Cust_Detail
Attribute Type Size Key Constraint
Cust_Id Number 10 PK Unique ID

Cust_Name Varchar 30 Customer Name

Cust_Age Number 3

Cust_Add Varchar 50 Address

Cust_Phone Number 11 11 Digit Number

Cust_CardNo Number 16
Ticket_No Number 10 FK
 Table Design for Entities
 Boarding_Pass
Attribute Type Size Key Constraint
BP_Id Varchar 10 PK Unique ID

Flight_No Varchar 10 FK
 Table Design for Entities
 Trav_Agent
Attribute Type Size Key Constraint
TravAg_Id Varchar 10 PK Unique ID

TravAg_Name Varchar 20

TravAg_Add Varchar 50

TravAg_City Varchar 20

TravAg_Phone Number 11 Should be Their