Contents Introduction: Current Business Activities

Contentsintroduction2current Business Activities2business Rules3

Contentsintroduction2current Business Activities2business Rules3

Contents Introduction 2 Current business activities 2 Business rules 3 Entities & their relevant attributes 5 Entity list 5 Entity structures with relevant attributes: 5 Primary Keys & Foreign Keys 9 Data Definition Language implementations: 10 Relationship between all entities 15 Cardinality 16 Final ER Diagram 18 References 19 Introduction FHTS is a new and specialised travel agency who cater for Australian companies with high travel needs. Their market niche is in low cost business travel packages by airlines. They keep their prices low by dealing directly with the airlines, who provide combined cheap flights and accommodation. FHTShas several branches in different countries .Most branches are in Nepal on many states .

Its dedicated employees are keen to prove the quality service often recommended by its customers. FHTSis well known for low airfare for both way routes services across domestic customers. Highly dedicated in customer services, FHTShoffers several discount schemes for children and people with disabilities.FHTShas won multiple awards for its safety & reliability records of flying from national and international organizations. Due to its high reliability, safety records and highly gained trusts from customers, FHTSis having more transactions , causing day to day expanding business activities hard to manage its operations. To ensure more flexible service for customers, it’s highly recommended to implement and Airlines Reservation System, a computerized system that will help manage all information related to flight, passengers , their contact details , reservation, transactions, schedule publishing, air fare payments etc.

Current business activities FHTSis operating on spot airline reservation, flight booking services with help of several travel agencies.It’s not only causing inconvenience for customers but also raises the actual tariff that customer has to pay. Though having branches in multiple cities targeting high range of customers, employee something fails to satisfy customers in remote areas when they have to travel for reservation also when they need to travel for cancellation or flight day extension.Followings are the key operations of Buddha Air’s day to day business: 1. Sales Transactions All sales transactions are related to flight ticket sales; advance reservations, reservation cancellations etc. a. Reservation FHTSprovides flight pre-reservation to its customers. All customers are open to reserve flights to travel in future. Reservations are taken before 11 hour of flight. Pre-reservation insuring future sales forecast. b. Cancellation No hard rules, as far as company does not have to lose anything. A customer is always can cancel their flights. Cancelled seats are released for re-sell instantly. Company denies canceling a sold ticket, if cancellation request is made within 6 hour of flight. 2. Flight scheduling Day to day flight scheduling, new flight arrangements according to sales potentiality, flight departure delay decisions all takes rooms in its daily flight scheduling activities etc. 3. Ticketing On spot ticket sales, online reservation sales, urgent sales, VVIP ticketingis done in its several branches, authorized agents from different travel agencies. Business rules Quality services to customers aren’t possible with defined business principles. FHTShas several business rules that apply from its own staffs to third party sales agents and potential customers. For each, business rules are as per listed below: 1. Customers · They are welcome to those from different flight schedules those are available throughout a day depending upon their comfort, willingness and flexibility and of course upon flight availability. · All customers are required to submit valid contact details. · All customers must reserve a flight to travel. No on the gate sales are available. Also customers can’t buy tickets at airports, instead they need to visit online store or a sales counter to get a ticket or reserve for future plans. · Full payments are necessary in-order to confirm a booking. Yes, there can be several discounts schemes which can be claimed by customers & is given upon proper alignment with discount descriptions. · Customers must be penalized for cancellation. How much charges they have to pay, depends when they are canceling the flights. If it’s before 11 hour of flight they need to pay 10% of sales and if it’s within 11 hour of flight – 33.33% will be deducted from actual sales amount. · Customers can demand the cancellation & 100% refund of flight is cancelled due to technical reason, bad weather. · All customers are allowed up to 5 KG cargo free with each ticket. Exceeding 5 KG causes the additional fees. · Each customer is eligible for $20,000 lifeinsurance. · Wheel chairs & oxygen is available for special passengers. 2. Employees (including sales agents) · All employees must be dressed according to company dress code with an ID card, easily visible to guests. · Employees are expected to be presence on their seat within duty hours. · All employees are assigned to serve client based on first come first services. Yes- there is a provision for special cure for urgent & national security matters. · Employees are hereby responsible for serving customers first, second their own jobs. · Employees are not allowed to take flight reservations within 11 hour of flight. Database Design for Airline Reservation Entities & their relevant attributes Entity list 1. AirCrafts 2. Route 3. AirFare 4. Flight_Schedule 5. Discounts 6. Charges 7. Countries 8. State 9. Contact_Details 10. Passengers 11. Branches 12. Employee 13. Transactions Entitystructures with relevant attributes: AirCrafts Field DataType Description Constraints AcID INT Field will store unique row number. Primary Key AcNumber Varchar(32) Aircraft number that identifies the plane. NOT ULL Capacity INT No. of seats available. NOT NULL MfdBy Varchar(128) Manufacturing company. NOT NULL MfdOn DATETIME Manufactured date of aircraft. NOT NULL Route Field Data Type Description Constraints RtID INT Stores unique row id. Primary Key Airport Varchar(32) From where the flight will take off. NOT NULL Destination Varchar (32) Flight destinations. NOT NULL RouteCode Varchar(16) A unique Route code generated using Source & Destination of flight. NOT NULL UNIQUE AirFare Field Data Type Description Constraints AfID INT Stores unique row id. Primary Key Route INT Route id from Route table. Foreign Key Fare Currency Stores service charge amount. NOT NULL FSC Currency Stores fuel surcharge amount. NOT NULL Flight_Schedule Field Data Type Description Constraints FlID INT Unique number to identify the flight. Primary Key FlightDate DATETIME Date of flight. NOT NULL Departure DATETIME Stores the departure time of flight. Arrival DATETIME Stores the arrival time of flight on destination. AirCraft INT Aircraft number that will fly, a number from Aircraft table. Foreign Key NetFare INT To determine total fare of flight, an ID from Air_Fare table. Foreign Key Discounts Field Data Type Description Constraints DiID INT Unique row id. Primary Key Title Varchar(32) Label to know discount. NOT NULL Amount INT Discount amount in % NOT NULL Description Varchar(255) Discount remarks & details. Charges Field Data Type Description Constraints ChID INT Unique row id. Primary Key Title Varchar(32) Label for charge. NOT NULL Amount INT Amount of charge in %. NOT NULL Description Varchar(255) Describe cause of charge. Countries Field Data Type Description Constraints CtID INT Unique row id. Primary Key CountryName Varchar(32) Room to store country name NOT NULL State Field Data Type Description Constraints StID INT Unique row id. Primary Key StateName Varchar(32) State name will take place here. Country INT PK from Country table. Foreign Key Contact_Details Field Data Type Description Constraints CnID INT Unique row id. Primary Key Email Varchar(16) Passenger’s contact email for transaction about flights. NOT NULL Cell Varchar(16) Passenger’s contact cell no for transaction about flights. NOT NULL Tel Varchar(16) Passenger’s contact telephone no. for transaction about flights. Street Varchar(64) Street address of the passengers. NOT NULL State INT PK from State table. Foreign Key Passengers Field Data Type Description Constraints PsID INT Unique row id. Primary Key Name Varchar (32) Passenger’s name NOT NULL Address Varchar (64) Passenger’s address NOT NULL Age INT Passenger’s age NOT NULL Nationalities Varchar (16) Nationality of the passenger. NOT NULL Contacts INT ContactID from Contact_Details table. Foreign Key Branches Field Data Type Description Constraints BrID INT Unique id for each branches Primary Key Center Varchar(16) Branch Title NOT NULL Address Varchar(32) Address of the branch NOT NULL State INT State ID from state table Foreign Key Employees Field Data Type Description Constraints EmpID INT Unique number to identity employee, unique on entire system. Primary Key Name Varchar (32) Employee name NOT NULL Address Varchar (32) Employee address NOT NULL Branch INT Associated branch id from Branch Table Foreign Key Designation Varchar(32) Working duty position. NOT NULL Email Varchar(32) Contact email of the employee NOT NULL Tel Varchar(16) Contact telephone number. Ext INT Ext number of employee cabinet, if applicable. Transactions Field Data Type Description Constraints TsID INT Unique row id Primary Key BookingDate Date/Time Keeps the booking date. NOT NULL DepartureDate Date/Time Keeps the departure date. NOT NULL Passenger INT Transaction creator passengers row id to associate booking/cancellation, payments etc. Foreign Key Flight INT Flight no, a PK of Flight_Schedule to determine flying details & costs. Foreign Key Type BIT Reservation/Cancellation NOT NULL Employee INT Reservation agent, a row id of employee who helps the passenger to make transaction. Foreign Key Charges INT If transaction is cancellation, charges may apply as per business rules. Foreign Key Discount INT Discount offers may apply based on scheme criteria. Foreign Key Total INT Calculated value of actual payable cost by customer to make a transaction. NOT NULL PrimaryKeys& Foreign Keys SN Table Primary Key Foreign Keys Column References 1 AirCraft AcID - - 2 Flight_Schedule FlID AirCraft AirCraft.AcID Route Route.RtID AirFare AirFare.AfID 3 Route RtID - - 4 AirFare AfID Route Route.RtID 5 Discounts DiID - - 6 Charges ChID - - 7 Passengers PsID Contacts Contact_Details.CnID 8 Contact_Details CnID State State.StID 9 State StID Country Country.CtID 10 Country CtID - - 11. Transaction TsID Passenger Passengers.PsID Flight Flight_Schedule.FlID Employee Employee.EmpID Charge Charges.ChID Discount Discounts.DiID 12 Employee EmpID Branch Branch.BrID 13. Branch BrID Data Definition Language implementations: / 0. Create Database & use it / CREATE DATABASE BuddhAirBase; USE BuddhaAirBase; / 1. Create AirCrafts table/ CREATE TABLE AirCrafts( AcID INT Primary Key, AcNumber Varchar(32) NOT NULL, Capacity INT NOT NULL, MfdBy Varchar(128) NOT NULL, MfdOn Datetime NOT NULL ); / 1.1 Insert data into AirCrafts table/ INSERT INTO AirCrafts (AcID, AcNumber, Capacity, MfdBy, MfdOn) VALUES (1, "ATR 72-500", 75, "Alenia Aeronotica", "23 April 1998"); / 2. Create Route table/ CREATE TABLE Route( RtID INT, Airport Varchar(32) NOT NULL, Destination Varchar(32) NOT NULL, RouteCode Varchar(16) NOT NULL UNIQUE, PRIMARY KEY (RtID) ); / 2.1 Insert data into Route table/ INSERT INTO Route Values (1, "Kathmandu", "Pokhara", "KTM-PKR"); / 3. Create AirFare table/ CREATE TABLE AirFare( AfID INT, Route INT, Fare Currency, FSC Currency, PRIMARY KEY (AfID), CONSTRAINT fk_Route FOREIGN KEY (Route) REFERENCES Route(RtID) ); / 3.1. Insert DATA into AirFare table/ INSERT INTO AirFare VALUES (1, 1, 86, 12); / 4. Create Flight_Schedule table / CREATE TABLE Flight_Schedule( FlID INT, FlightDate DATETIME, Departure DATETIME, Arrival DATETIME, AirCraft INT, NetFare INT, PRIMARY KEY (FlID), CONSTRAINT fk_AirCraft FOREIGN KEY (AirCraft) REFERENCES AirCrafts(AcID), CONSTRAINT fk_NetFare FOREIGN KEY (NetFare) REFERENCES AirFare(AfID) ); / 4.1 Insert DATA into Flight_Schedule / INSERT INTO Flight_Schedule VALUES (1, 'January 23, 2012', '23:20', '1:20', 1, 1); / 5. Create Discounts table / CREATE TABLE Discounts( DiID INT PRIMARY KEY, Title Varchar(32), Amount INT, Description Varchar (255) ) / 5.1 Insert data into Discounts table / INSERT INTO Discounts VALUES (1,'Childrens', 10, 'Discount is provide all childrens under age of 10.'); / 6. Create Charges table / CREATE TABLE Charges( ChID INT PRIMARY KEY, Title Varchar(32), Amount INT, Description Varchar (255) ) / 6.1 Insert data into Charges table / INSERT INTO Charges VALUES (2,'Urgent Cancellation', 33.33, '33.3% will be charged for cancellation for booking within 11 hrs from flight time'); / 7. Crate Country table/ CREATE TABLE Countries ( CtID INT PRIMARY KEY, CountryName Varchar (32) NOT NULL ); / 7.1 Insert data into Country table / INSERT INTO Countries VALUES (1, 'Nepal'); / 8. Create State table/ CREATE TABLE State( StID INT, StateName Varchar (32), Country INT, PRIMARY KEY (StID), CONSTRAINT fk_Country FOREIGN KEY (Country) REFERENCES Countries(CtID) ); / 8.1. Insert data into State table/ INSERT INTO State VALUES (1, 'Bagmati', 1); / 9. Create Contact_Details table/ CREATE TABLE Contact_Details( CnID INT PRIMARY KEY, Email Varchar (16) NOT NULL, Cell Varchar (16) NOT NULL, Tel Varchar(16), Street Varchar(64), State INT NOT NULL, CONSTRAINT fk_State FOREIGN KEY (State) REFERENCES State(StID) ); / 9.1 Insert data into Contact_Details / INSERT INTO Contact_Details VALUES (1,' [email protected] ', '', '', 'Gandaki Marga', 1); / 10. Create Passengers table / CREATE TABLE Passengers( PsID INT PRIMARY KEY, Name Varchar (32) NOT NULL, Address Varchar (64) NOT NULL, Age INT NOT NULL, Nationalities Varchar(16) NOT NULL, Contacts INT NOT NULL, CONSTRAINT fk_Contacts FOREIGN KEY (Contacts) REFERENCES Contact_Details(CnID) ); / 10.1 Insert data into Passengers table / INSERT INTO Passengers VALUES (1,'Shekhar Kumar Sharma', 'Sinamanga-39, KTM', 23, 'Nepalese', 1); / 11. Create Branch table / CREATE TABLE Branches( BrID INT PRIMARY KEY, Center Varchar(16) NOT NULL, Address Varchar(32) NOT NULL, State INT, CONSTRAINT fk_StateOfEmployee FOREIGN KEY (State) REFERENCES State(StID) ); / 11.1 Insert data into branches table / INSERT INTO Branches VALUES (1, 'Kathmandu', 'New Road, Kathmandu', 1); / 12. Create Employee table / CREATE TABLE Employee ( EmpID INT PRIMARY KEY, Name Varchar (32) NOT NULL, Address Varchar (32) NOT NULL, Branch INT NOT NULL, Designation Varchar(32) NOT NULL, Email Varchar(16) NOT NULL, Tel Varchar(16) NOT NULL, Ext INT, CONSTRAINT fk_Branch FOREIGN KEY (Branch) REFERENCES Branches(BrID) ); / 12.1 Insert data into Employee table / INSERT INTO Employee VALUES (1, 'Diwan Adhikari', 'Bagbazaar - 11, KTM', 1, 'Sales Executive', ' [email protected] ', '', 12); / 13. Create table Transactions / CREATE TABLE Transactions( TsID INT PRIMARY KEY, BookingDate DATETIME, DepartureDate DATETIME, Passenger INT, Flight INT, Type BIT, Employee INT, Charges INT, Discount INT, CONSTRAINT fk_Passenger FOREIGN KEY (Passenger) REFERENCES Passengers(PsID), CONSTRAINT fk_Flight FOREIGN KEY (Flight) REFERENCES Flight_Schedule(FlID), CONSTRAINT fk_Employee FOREIGN KEY (Employee) REFERENCES Employee(EmpID), CONSTRAINT fk_Charges FOREIGN KEY (Charges) REFERENCES Charges(ChID), CONSTRAINT fk_Discount FOREIGN KEY (Discount) REFERENCES Discounts(DiID) ); / 13.1 Insert data into Transactions / INSERT INTO Transactions VALUES (1,'12 November 2011', '21 December 2011', 1, 1, 0, 1, NULL, NULL);

Paper For Above instruction

The development of an effective airline reservation system necessitates a comprehensive understanding of the entities involved, their attributes, and the relationships that interconnect them. This paper explores the design of such a system for FHTS, a specialized travel agency, emphasizing its core entities, attributes, integrity constraints, and a detailed Entity-Relationship (ER) model. The goal is to facilitate efficient management of reservations, flights, passengers, and related operations, improving service delivery and operational efficiency.

Introduction

FHTS operates in the highly competitive airline industry, catering to Australian companies with high travel demands. Their business model revolves around providing low-cost business travel packages by establishing direct dealings with airlines. The system's core purpose is to streamline booking, scheduling, and transaction management across multiple branches in Nepal and other countries. To achieve this, the airline reservation system is designed as a comprehensive database that captures all essential data and relationships involved in airline operations.

Core Entities and Attributes

The primary entities in the system include Aircrafts, Routes, Air Fares, Flight Schedules, Discounts, Charges, Countries, States, Contact Details, Passengers, Branches, Employees, and Transactions. Each entity has specific attributes that define its characteristics and constraints, ensuring data integrity and meaningful relationships.

Aircrafts

Stores data about aircrafts, including a unique identifier, aircraft number, capacity, manufacturer, and manufacturing date. The primary key is AcID, and the aircraft number AcNumber is unique for identification.

Routes

Captures flight routes, including a route ID, origin airport, destination, and a unique route code. The route code, generated from source and destination, ensures efficient route identification and management.

Air Fares

Associates route information with financial aspects, including service charges and fuel surcharges. These provide the net fare calculations necessary for ticket