Question

Project 1

must contain the following:
ER Diagram.
All schemas before and after normalization.
All SQL statements of:
Creating tables.
Inserting data in tables.
Queries.
Screenshots from MySQL (or any other software you use) of all the tables after population and queries results. Reservation System

For this project, following assumptions have been made:

The booking is only open for the next seven days from the current date.

Only two categories of the plane can be booked i.e. Economy and Business.

The total number of tickets that can be booked in each category is 10 only.

By ‘user’, we mean the person who is booking the ticket for the passenger(s). For example, an employee of Saudi Airline.

Your project should include information about the following entities and their respective attributes:

Airline Reservation System

User

User_id

User_password

First_name

Last_name

Age

Gender

Email_address

Contact_number

City

Passenger

Passenger_ID

Name

Gender

Age

Phone_num

BookingRef_num

Seat_no

Status

Flight

Flight_number

Flight_name

Departure_airport

Arrival_airport

Departure_time

Arrival_time

Availabilty_of_seats

Airport

Number

Name

Flight_number

Arrival_time

Departure_time

Ticket

Ticket_id

Flight_number

Booked_user

Status

Number_of_passengers

Required SQL Queries:

Write a query that displays the User_id and names of all those passengers who booked ticket on any particular flight.

Write a query to display details (ex. Passenger_ID, name, etc.) of all passengers travelling under a particular ticket.

Write a query to display the time at which any Flight_number reaches any Airport_name. For example, EK233, Dammam.

Write a query to display all flights in ascending order of their departure time.

Write a query to display the flight_number that stops for the longest time at any airport (e.g., Dammam).

Answer 1

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`user` (
`User_id` VARCHAR(16) NOT NULL,
`User_password` VARCHAR(32) NOT NULL,
`First_Name` VARCHAR(45) NOT NULL,
`Last_Name` VARCHAR(45) NOT NULL,
`Age` INT NULL,
`Gender` ENUM('Male', 'Female') NULL,
`Email_address` VARCHAR(45) NULL,
`contact_number` INT(10) NULL,
PRIMARY KEY (`User_id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Passenger`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Passenger` (
`Passenger_ID` VARCHAR(16) NOT NULL,
`Name` VARCHAR(45) NOT NULL,
`Gender` ENUM('Male', 'Female') NULL,
`Age` INT NULL,
`Phone_num` INT(10) NULL,
`Booking_Ref_num` VARCHAR(45) NULL,
`Seat_No` VARCHAR(45) NULL,
`user_User_id` VARCHAR(16) NOT NULL,
PRIMARY KEY (`Passenger_ID`),
INDEX `fk_Passenger_user_idx` (`user_User_id` ASC),
CONSTRAINT `fk_Passenger_user`
    FOREIGN KEY (`user_User_id`)
    REFERENCES `mydb`.`user` (`User_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Airport`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Airport` (
`Number` VARCHAR(10) NOT NULL,
`Name` VARCHAR(45) NOT NULL,
`Flight_number` VARCHAR(45) NULL,
`Arrival_time` TIME NULL,
`Departure_time` TIME NULL,
PRIMARY KEY (`Number`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Flight`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Flight` (
`Flight_number` VARCHAR(45) NOT NULL,
`Flight_name` VARCHAR(45) NOT NULL,
`Departure_airport` VARCHAR(45) NULL,
`Arrival_airport` VARCHAR(45) NULL,
`Departure_time` TIME NULL,
`Arrival_time` TIME NULL,
`Airport_Number` VARCHAR(10) NOT NULL,
PRIMARY KEY (`Flight_number`),
INDEX `fk_Flight_Airport1_idx` (`Airport_Number` ASC),
CONSTRAINT `fk_Flight_Airport1`
    FOREIGN KEY (`Airport_Number`)
    REFERENCES `mydb`.`Airport` (`Number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Ticket`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Ticket` (
`Ticket_id` VARCHAR(16) NOT NULL,
`Flight_number` VARCHAR(45) NOT NULL,
`Booked_user` NOT NULL,
`status` VARCHAR(45) NOT NULL,
`Number_of_passengers` INT NULL,
`user_User_id` VARCHAR(16) NOT NULL,
`Flight_Flight_number` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Ticket_id`),
INDEX `fk_Ticket_user1_idx` (`user_User_id` ASC),
INDEX `fk_Ticket_Flight1_idx` (`Flight_Flight_number` ASC),
CONSTRAINT `fk_Ticket_user1`
    FOREIGN KEY (`user_User_id`)
    REFERENCES `mydb`.`user` (`User_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
CONSTRAINT `fk_Ticket_Flight1`
    FOREIGN KEY (`Flight_Flight_number`)
    REFERENCES `mydb`.`Flight` (`Flight_number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Data for table `mydb`.`user`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`user` (`User_id`, `User_password`, `First_Name`, `Last_Name`, `Age`, `Gender`, `Email_address`, `contact_number`) VALUES ('u1', 'pass', 'abcF1', 'abcL1', 22, 'Male', '[email protected]', 9999988888);
INSERT INTO `mydb`.`user` (`User_id`, `User_password`, `First_Name`, `Last_Name`, `Age`, `Gender`, `Email_address`, `contact_number`) VALUES ('u2', 'password', 'defF1', 'defF2', 33, 'Female', '[email protected]', 88888999990);

COMMIT;

-- -----------------------------------------------------
-- Data for table `mydb`.`Passenger`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`Passenger` (`Passenger_ID`, `Name`, `Gender`, `Age`, `Phone_num`, `Booking_Ref_num`, `Seat_No`, `user_User_id`) VALUES ('P1', 'sam', 'Male', 22, 9999988889, 'T1', '22', 'U1');
INSERT INTO `mydb`.`Passenger` (`Passenger_ID`, `Name`, `Gender`, `Age`, `Phone_num`, `Booking_Ref_num`, `Seat_No`, `user_User_id`) VALUES ('P2', 'man', 'Female', 18, 8989898989, 'T2', '23', 'U1');

COMMIT;

-- -----------------------------------------------------
-- Data for table `mydb`.`Airport`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`Airport` (`Number`, `Name`, `Flight_number`, `Arrival_time`, `Departure_time`) VALUES ('A1', 'Dammam', 'EK233', '05:00:00', '12:00:00');
INSERT INTO `mydb`.`Airport` (`Number`, `Name`, `Flight_number`, `Arrival_time`, `Departure_time`) VALUES ('A2', 'Manila', 'EK420', '13:30:00', '22:00:00');

COMMIT;

-- -----------------------------------------------------
-- Data for table `mydb`.`Flight`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`Flight` (`Flight_number`, `Flight_name`, `Departure_airport`, `Arrival_airport`, `Departure_time`, `Arrival_time`, `Airport_Number`) VALUES ('EK233', 'BOENG', 'Dammam', 'Manila', '02:00:00', '05:00:00', '');
INSERT INTO `mydb`.`Flight` (`Flight_number`, `Flight_name`, `Departure_airport`, `Arrival_airport`, `Departure_time`, `Arrival_time`, `Airport_Number`) VALUES ('Ek420', 'VISTARA', 'Manila', 'Dammam', '07:00:00', '12:00:00', DEFAULT);

COMMIT;

-- -----------------------------------------------------
-- Data for table `mydb`.`Ticket`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`Ticket` (`Ticket_id`, `Flight_number`, `Booked_user`, `status`, `Number_of_passengers`, `user_User_id`, `Flight_Flight_number`) VALUES ('T1', 'EK233', U1, 'confirmed', 3, '', '');
INSERT INTO `mydb`.`Ticket` (`Ticket_id`, `Flight_number`, `Booked_user`, `status`, `Number_of_passengers`, `user_User_id`, `Flight_Flight_number`) VALUES ('T2', 'Ek420', U2, 'waiting', 2, '', 'Ek420');

COMMIT;

QUERY1:
SELECT User_id,Name
FROM user,Passenger,Ticket
WHERE Flight_number = 'EK233' AND Booked_user = User_id AND User_id = Passenger.user_User_id

QUERY2:
SELECT Passenger_ID,Name,Gender,Age,Phone_Num
FROM Passenger,Ticket,user
WHERE User_id = Booked_user and user_id = Passenger.user_User_id AND Ticket_id = 'T1'

QUERY3:
SELECT Arrival_time,Flight_number
FROM Airport
WHERE Name='Dammam'

Query4:
SELECT Flight_name
FROM Flight
ORDER BY Departure_time ASC

QUERY5:
SELECT Flight_number
FROM Flight
WHERE TIMEDIFF(Departure_time,Arrival_time) IN (SELECT MAX(TIMEDIFF(Departure_time,Arrival_time) FROM Flight WHERE Departure_airport = 'Dammam'