- Posted By: freeproject
- Comments: 0
Posted By freeproject on December 10, 2017
Airlines Ticket Booking System is an application of Database Management System which is used for booking and schedule information. This project Airline Ticket Booking System is to provide help for the user to book their flight tickets without visiting booking counter or to any other booking vendors. This system provides options for viewing different flights available with different timings for a particular date and provides customers with the facility to book a ticket, modify or cancel a particular reservation but it does not provide the customers with details of cost of the ticket. It can also manages all the information about customer, booking enquiry, reservation. Users can view the status and schedule of a flight directly, from the online. It also provides time to time current information related to airlines schedules. It tracks all the details about the airlines booking, ticket booking. It can keep to record airlines employee detail, daily attendance and salary calculation of the employee.
Airlines Ticket Booking System Database Structure
-- -- Table structure for table `airlines` -- CREATE TABLE `airlines` ( `airlines_id` int(11) NOT NULL, `airlines_at_id` int(11) NOT NULL, `airlines_name` varchar(255) NOT NULL, `airlines_no` varchar(255) NOT NULL, `airlines_from` int(11) NOT NULL, `airlines_deaprture` varchar(255) NOT NULL, `airlines_to` int(11) NOT NULL, `airlines_arrival` varchar(255) NOT NULL, `airlines_travel_time` varchar(255) NOT NULL, `airlines_total_distance` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT; -- -- Table structure for table `airlines_type` -- CREATE TABLE `airlines_type` ( `at_id` int(11) NOT NULL, `at_name` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT; -- -- Table structure for table `booking` -- CREATE TABLE `booking` ( `booking_id` int(11) NOT NULL, `booking_user_id` varchar(255) NOT NULL, `booking_route_id` varchar(255) NOT NULL, `booking_date` varchar(255) NOT NULL, `booking_total_fare` varchar(255) NOT NULL, `booking_journey_date` varchar(255) NOT NULL, `booking_seat_type` varchar(255) NOT NULL, `booking_status` varchar(255) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Table structure for table `city` -- CREATE TABLE `city` ( `city_id` int(10) UNSIGNED NOT NULL, `city_name` varchar(45) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Table structure for table `passengar` -- CREATE TABLE `passengar` ( `passengar_id` int(11) NOT NULL, `passengar_booking_id` varchar(255) NOT NULL, `passengar_type` varchar(255) NOT NULL, `passengar_name` varchar(255) NOT NULL, `passengar_gender` varchar(255) NOT NULL, `passengar_age` varchar(255) NOT NULL, `passengar_seat_no` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Table structure for table `route` -- CREATE TABLE `route` ( `route_id` int(11) NOT NULL, `route_airlines_id` varchar(255) NOT NULL, `route_from_city` varchar(255) NOT NULL, `route_from_arrival` varchar(255) NOT NULL, `route_from_departure` varchar(255) NOT NULL, `route_to_city` varchar(255) NOT NULL, `route_economy_fare` varchar(255) NOT NULL, `route_business_fare` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
Indexs and Primary Keys
-- Indexes for table `airlines` ALTER TABLE `airlines` ADD PRIMARY KEY (`airlines_id`); -- Indexes for table `airlines_type` ALTER TABLE `airlines_type` ADD PRIMARY KEY (`at_id`); -- Indexes for table `booking` ALTER TABLE `booking` ADD PRIMARY KEY (`booking_id`); -- Indexes for table `city` ALTER TABLE `city` ADD PRIMARY KEY (`city_id`); -- Indexes for table `latest_booking` ALTER TABLE `latest_booking` ADD PRIMARY KEY (`lb_id`); -- Indexes for table `passengar` ALTER TABLE `passengar` ADD PRIMARY KEY (`passengar_id`); -- Indexes for table `route` ALTER TABLE `route` ADD PRIMARY KEY (`route_id`); -- AUTO_INCREMENT for dumped tables ALTER TABLE `airlines` MODIFY `airlines_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29; -- AUTO_INCREMENT for table `airlines_type` ALTER TABLE `airlines_type` MODIFY `at_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- AUTO_INCREMENT for table `booking` ALTER TABLE `booking` MODIFY `booking_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9; -- AUTO_INCREMENT for table `city` ALTER TABLE `city` MODIFY `city_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21; -- AUTO_INCREMENT for table `latest_booking` ALTER TABLE `latest_booking` MODIFY `lb_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2; -- AUTO_INCREMENT for table `passengar` ALTER TABLE `passengar` MODIFY `passengar_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=41; -- AUTO_INCREMENT for table `route` ALTER TABLE `route` MODIFY `route_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
Select 10 functions in SQL Statement of your choice
-- Select total number of records of Airlines: SELECT COUNT(*) AS 'Total Count' FROM `airlines` -- Select maximum travel distance airlines record: SELECT MAX(`airlines_total_distance`) AS 'Maximum Travel Distance', airlines.* FROM `airlines` -- Select minimum travel distance airlines record: SELECT MIN(`airlines_total_distance`) AS 'Maximum Travel Distance', airlines.* FROM `airlines` -- Select average travel distance airlines record: SELECT AVG(`airlines_total_distance`) AS 'Average Travel Distance', airlines.* FROM `airlines` -- Select all the airlines name and convert it inot upper case: SELECT UCASE(`airlines_name`) FROM `airlines` WHERE 1 -- Select all the airlines name and convert it into lower case: SELECT LCASE(`airlines_name`) FROM `airlines` WHERE 1 -- Select all the airlines name and number, concat with ",": SELECT CONCAT(`airlines_name`,',',`airlines_no`) FROM `airlines` WHERE 1 -- Removes leading and trailing spaces from Airlines Name: SELECT TRIM(`airlines_name`) FROM `airlines` WHERE 1 -- Fetch only starting 3 characters of the airlines name: SELECT SUBSTR(`airlines_name`,1,3) FROM `airlines` WHERE 1 -- Reverse the name of each airlines: SELECT REVERSE(`airlines_name`) FROM `airlines` WHERE 1
Use of SELECT-FROM-WHERE-GROUP BY-HAVING-ORDER BY-LIMIT
SELECT COUNT(*) as `TotalCout`, airlines.* FROM `airlines` WHERE `airlines_from` = 2 GROUP BY airlines_at_id HAVING TotalCout >= 3 ORDER BY airlines_name LIMIT 0,3
Perform JOIN Operations including INNER, LEFT, RIGHT, FULL and CROSS Join
-- Inner Join Example: SELECT * FROM airlines INNER JOIN route ON airlines.airlines_id = route.route_airlines_id; -- Left Join Example: SELECT * FROM airlines LEFT JOIN route ON airlines.airlines_id = route.route_airlines_id; -- Right Join Example: SELECT * FROM airlines RIGHT JOIN route ON airlines.airlines_id = route.route_airlines_id; -- Cross Join Example: SELECT * FROM airlines FULL JOIN route ON airlines.airlines_id = route.route_airlines_id; -- FUll Join Example: SELECT * FROM airlines FULL JOIN route ON airlines_id = route_airlines_id;
Perform Queries and Sub Queries Operation and display the result
SELECT * FROM `airlines` WHERE airlines_id IN (SELECT route_airlines_id FROM route)
Create 4 different users ie. admin, tester, developer, user with different privilege setting
-- Create User Admin with all permission: CREATE USER 'admin'@'localhost' IDENTIFIED BY 'test'; GRANT ALL PRIVILEGES ON * . * TO 'admin'@'localhost'; FLUSH PRIVILEGES; -- Create User Tester with SELECT, UPDATE, INSERT permission: CREATE USER 'tester'@'localhost' IDENTIFIED BY 'test'; GRANT SELECT, UPDATE, INSERT, DELETE ON airlines_reservation_system_dbms.* TO 'tester'@'localhost'; FLUSH PRIVILEGES; REVOKE DELETE ON airlines_reservation_system_dbms.* FROM 'tester'@'localhost'; -- Create User Developer with SELECT, UPDATE, INSERT, DELETE, CREATE, ALTER permission: CREATE USER 'developer'@'localhost' IDENTIFIED BY 'test'; GRANT SELECT, UPDATE, INSERT, DELETE, CREATE, ALTER ON airlines_reservation_system_dbms.* TO 'developer'@'localhost'; FLUSH PRIVILEGES; -- Create User User with SELECT permission: CREATE USER 'user'@'localhost' IDENTIFIED BY 'test'; GRANT SELECT ON airlines_reservation_system_dbms.* TO 'user'@'localhost'; FLUSH PRIVILEGES; -- Drop All Users: DROP USER 'admin'@'localhost'; DROP USER 'tester'@'localhost'; DROP USER 'developer'@'localhost'; DROP USER 'user'@'localhost';
Create 2 example of Store Procedure
-- Store Procedure for Get Airlines Details: DELIMITER // CREATE PROCEDURE get_all_arilines (IN id CHAR(20)) BEGIN SELECT * FROM `airlines` WHERE airlines_id = id; END // DELIMITER ; CALL get_all_arilines(2) -- Store Procedure for Get Booking Details of Airlines: DELIMITER // CREATE PROCEDURE get_all_arilines_booking (IN id CHAR(20)) BEGIN SELECT * FROM `airlines`,`booking`,`route` WHERE route_id = booking_route_id AND route_airlines_id = airlines_id AND airlines_id = id; END // DELIMITER ; CALL get_all_arilines_booking(2)
Perform Commit and Rollback Operations
-- Commit and Transaction Example: START TRANSACTION; UPDATE `airlines` SET airlines_total_distance=9000 WHERE 1; COMMIT; -- Rollback Example: START TRANSACTION; UPDATE `airlines` SET airlines_total_distance=7000 WHERE 1; Rollback
Perform Triggers Operations Using INSERT, UPDATE and DELETE
-- Trigger for Insert Statement:
DELIMITER $$
CREATE TRIGGER insert_booking
AFTER INSERT ON booking
FOR EACH ROW
BEGIN
INSERT INTO latest_booking
SET
lb_booking_id = NEW.booking_id,
lb_date = NOW(),
lb_action = "Booking Details Inserted";
END$$
DELIMITER ;
-- Trigger for Delete Statement:
DELIMITER $$
CREATE TRIGGER delete_airlines
BEFORE DELETE ON airlines
FOR EACH ROW
BEGIN
DELETE FROM route WHERE route_airlines_id = OLD.airlines_id;
END$$
DELIMITER ;
-- Trigger for Update Statement:
DELIMITER $$
CREATE TRIGGER update_booking
AFTER UPDATE ON booking
FOR EACH ROW
BEGIN
UPDATE latest_booking
SET
lb_booking_id = NEW.booking_id,
lb_date = NOW(),
lb_action = "Booking Details Updated"
;
END$$
DELIMITER ;
Perform 3 Examples of View Operations
-- View 1: CREATE VIEW `get_all_airlines` AS SELECT * FROM `airlines`; SHOW CREATE VIEW `get_all_airlines`; SELECT * FROM get_all_airlines; -- View 2: CREATE VIEW `get_all_airlines_route` AS SELECT * FROM airlines LEFT JOIN route ON airlines.airlines_id = route.route_airlines_id; -- View 3: CREATE VIEW `get_all_airlines_bookings` AS SELECT * FROM `airlines`,`booking`,`route` WHERE route_id = booking_route_id AND route_airlines_id = airlines_id AND airlines_id = id;
ER Diagram of Project:
