-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `airline`
--

-- --------------------------------------------------------

--
-- Table structure for table `AddOn`
--

CREATE TABLE `AddOn` (
  `AddOnID` int(3) NOT NULL,
  `AddOnName` text NOT NULL,
  `AddOnPrice` float NOT NULL,
  `Description` text NOT NULL,
  `ClassName` varchar(8) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `AddOn`
--

INSERT INTO `AddOn` (`AddOnID`, `AddOnName`, `AddOnPrice`, `Description`, `ClassName`) VALUES
(1, 'FirstClass_Flex', 10000, 'Checked baggage: 20 kg,Cabin baggage: 2x7 kg,Seat Selection: Complimentary,Refund fee: Allow', 'First'),
(2, 'FirstClass_ExtraFlex', 15000, 'Checked baggage: 25 kg,Cabin baggage: 2x7 kg,Seat Selection: Complimentary,Refund fee: Allow', 'First'),
(3, 'Business_Flex', 5000, 'Checked baggage: 10 kg,Cabin baggage: 2x7 kg,Seat Selection: Complimentary,Refund fee: Allow', 'Business'),
(4, 'Business_ExtraFlex', 7500, 'Checked baggage: 15 kg,Cabin baggage: 2x7 kg,Seat Selection: Complimentary,Refund fee: Allow', 'Business'),
(5, 'Economy_Flex', 250, 'Checked baggage: 5 kg,Cabin baggage: 2x7 kg,Seat Selection: Complimentary,Refund fee: Allow', 'Economy'),
(6, 'Economy_ExtraFlex', 400, 'Checked baggage: 7 kg,Cabin baggage: 2x7 kg,Seat Selection: Complimentary,Refund fee: Allow', 'Economy');

-- --------------------------------------------------------

--
-- Table structure for table `Airplane`
--

CREATE TABLE `Airplane` (
  `AirplaneID` int(6) NOT NULL,
  `RegisterDate` text NOT NULL,
  `PayLoad` float NOT NULL,
  `Status` enum('N','A') NOT NULL,
  `ModelNo` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `AirplaneSeat`
--

CREATE TABLE `AirplaneSeat` (
  `Row` varchar(3) NOT NULL,
  `AirplaneID` int(6) NOT NULL,
  `ClassName` varchar(8) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `Airport`
--

CREATE TABLE `Airport` (
  `AirportID` varchar(3) NOT NULL,
  `AirportName` text NOT NULL,
  `Address` text NOT NULL,
  `AirportTax` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Airport`
--

INSERT INTO `Airport` (`AirportID`, `AirportName`, `Address`, `AirportTax`) VALUES
('AKL', 'Auckland Airport', 'Auckland,New Zealand', 444),
('AMS', 'Amsterdam Airport Schiphol', 'Amsterdam,Netherlands', 444),
('ARN', 'Stockholm-Arlanda Airport', 'Stockholm,Sweden', 444),
('ATH', 'Athens International Airport', 'Athens,Greece', 444),
('AUH', 'Abu Dhabi International Airport', 'Abu Dhabi,United Arab Emirates', 444),
('BKK', 'Suvarnabhumi Airport', 'Bangkok,Thailand', 444),
('BOG', 'El Dorado International Airport', 'Bogota,Columbia', 444),
('BRU', 'Brussels Airport', 'Brussels,Belgium', 444),
('CDG', 'Charles de Gaulle Airport', 'Paris,France', 444),
('CGK', 'Soekarno Hatta International Airport', 'Jakarta,Indonesia', 444),
('CPH', 'Copenhagen Airport', 'Copenhagen,Denmark', 444),
('DEL', 'Indira Gandhi International Airport', 'Delhi,India', 444),
('DME', 'Domodedovo International Airport', 'Moscow,Russia', 444),
('DOH', 'Hamad International Airport', 'Doha,Qatar', 444),
('DUB', 'Dublin Airport', 'Dublin,Ireland', 444),
('FCO', 'Leonardo da Vinci-Fiumicino Airport', 'Rome,Italy', 444),
('GRU', 'Sao Paulo-Guarulhos International Airport', 'Sao Paulo,Brazil', 444),
('HKG', 'Hong Kong International Airport', 'Hong Kong,Hong Kong', 444),
('ICN', 'Incheon International Airport', 'Incheon,South Korea', 444),
('JFK', 'John F. Kennedy International Airport', 'New York City,USA', 444),
('JNB', 'OR Tambo International Airport', 'Johannesburg,South Africa', 444),
('KUL', 'Kuala Lumpur International Airport', 'Kuala Lumpur,Malaysia', 444),
('LAX', 'Los Angeles International Airport', 'Los Angeles,USA', 444),
('MAD', 'Mad-Barajas Airport', 'Madrid,Spain', 444),
('MAN', 'Manchester Airport', 'Manchester,UK', 444),
('MEX', 'Mexico City International Airport', 'Mexico City,Mexico', 444),
('MNL', 'Ninoy Aquino International Airport', 'Manila,Philippines', 444),
('MUC', 'Munich Airport', 'Munich,Germany', 444),
('NRT', 'Narita International Airport', 'Tokyo\\Yokohama,Japan', 444),
('OSL', 'Oslo Airport-Gardermoen', 'Oslo,Norway', 444),
('PEK', 'Beijing Capital International Airport', 'Beijing,China', 444),
('RUH', 'King Khalid International Airport', 'Riyadh,Saudi Arabia', 444),
('SAW', 'Sabiha Gokken International Airport', 'Istanbul,Turkey', 444),
('SGN', 'Tan Son Nhat International Airport', 'Ho Chi Minh City,Vietnam', 444),
('SIN', 'Singapore Changi Airport', 'Singapore,Singapore', 444),
('SYD', 'Sydney Airport', 'Sydney,Australia', 444),
('TPE', 'Taiwan Taoyuan International Airport', 'Taoyuan,Taiwan', 444),
('VIE', 'Vienna International Airport', 'Vienna,Austria', 444),
('YVR', 'Vancouver International Airport', 'Vancouver,Canada', 444),
('ZRH', 'Zurich Airport', 'Zurich,Switzerland', 444);

-- --------------------------------------------------------

--
-- Table structure for table `Billing`
--

CREATE TABLE `Billing` (
  `BillingID` int(10) NOT NULL,
  `CardNo` varchar(16) NOT NULL,
  `MemberID` int(6) NOT NULL,
  `MemberPromotionID` int(6) NOT NULL,
  `PromotionCode` int(8) NOT NULL,
  `PayDate` date NOT NULL,
  `AmountPaid` float NOT NULL,
  `Status` enum('P','U') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `Booking`
--

CREATE TABLE `Booking` (
  `BookingID` int(6) NOT NULL,
  `FlightID` int(6) NOT NULL,
  `PassengerID` int(8) NOT NULL,
  `BillingID` int(10) NOT NULL,
  `SeatID` varchar(3) NOT NULL,
  `AddOnID` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `Class`
--

CREATE TABLE `Class` (
  `ClassName` varchar(8) NOT NULL,
  `ServiceDescription` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Class`
--

INSERT INTO `Class` (`ClassName`, `ServiceDescription`) VALUES
('Business', ''),
('Economy', ''),
('First', '');

-- --------------------------------------------------------

--
-- Table structure for table `Flight`
--

CREATE TABLE `Flight` (
  `FlightID` int(6) NOT NULL,
  `DepartureDate` datetime NOT NULL,
  `ArrivalDate` datetime NOT NULL,
  `RouteID` int(7) NOT NULL,
  `AirplaneID` int(6) NOT NULL,
  `Gate` varchar(3) NOT NULL,
  `Status` enum('N','A') NOT NULL,
  `Price` float NOT NULL,
  `Seat` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `MemberPromotion`
--

CREATE TABLE `MemberPromotion` (
  `MemberID` int(6) NOT NULL,
  `MemberPromotionID` int(6) NOT NULL,
  `MemberPromotionDescription` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `Passenger`
--

CREATE TABLE `Passenger` (
  `PassengerID` int(8) NOT NULL,
  `Sex` varchar(1) NOT NULL,
  `FirstName` varchar(50) NOT NULL,
  `LastName` varchar(50) NOT NULL,
  `DOB` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `Promotion`
--

CREATE TABLE `Promotion` (
  `PromotionCode` int(8) NOT NULL,
  `PromotionDescription` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `Route`
--

CREATE TABLE `Route` (
  `RouteID` int(7) NOT NULL,
  `Origin` varchar(3) NOT NULL,
  `Destination` varchar(3) NOT NULL,
  `Miles` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Route`
--

INSERT INTO `Route` (`RouteID`, `Origin`, `Destination`, `Miles`) VALUES
(1, 'BKK', 'LAX', 8275),
(2, 'BKK', 'ICN', 2315),
(3, 'BKK', 'NRT', 2898),
(4, 'BKK', 'MUC', 5472),
(5, 'NRT', 'AUH', 5041),
(6, 'BRU', 'CGK', 7103),
(7, 'PEK', 'CDG', 5111),
(8, 'MEX', 'CPH', 5915),
(9, 'JFK', 'DME', 4672),
(10, 'ICN', 'GRU', 11410),
(11, 'FCO', 'GRU', 5894),
(12, 'JNB', 'KUL', 5313),
(13, 'MAN', 'SYD', 10573),
(14, 'MNL', 'OSL', 6026),
(15, 'SAW', 'VIE', 793),
(16, 'TPE', 'HKG', 505),
(17, 'ZRH', 'YVR', 5169),
(18, 'MAD', 'MAN', 906),
(19, 'GRU', 'BKK', 10200),
(20, 'DOH', 'JNB', 3891),
(21, 'ARN', 'CPH', 325),
(22, 'ATH', 'AKL', 10865),
(23, 'AMS', 'MAN', 307),
(24, 'JFK', 'ICN', 6875),
(25, 'PEK', 'DEL', 2349),
(26, 'DUB', 'MUC', 857),
(27, 'RUH', 'DME', 2198),
(28, 'DME', 'SGN', 857),
(29, 'BOG', 'JFK', 2497),
(30, 'SIN', 'NRT', 3340),
(31, 'CGK', 'SGN', 1872),
(32, 'JFK', 'DUB', 3182),
(33, 'AUH', 'CGK', 4121),
(34, 'TPE', 'ARN', 5193),
(35, 'VIE', 'SYD', 9938),
(36, 'SYD', 'HKG', 4586),
(37, 'NRT', 'LAX', 5051);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `AddOn`
--
ALTER TABLE `AddOn`
  ADD PRIMARY KEY (`AddOnID`),
  ADD KEY `ClassName` (`ClassName`);

--
-- Indexes for table `Airplane`
--
ALTER TABLE `Airplane`
  ADD PRIMARY KEY (`AirplaneID`);

--
-- Indexes for table `AirplaneSeat`
--
ALTER TABLE `AirplaneSeat`
  ADD PRIMARY KEY (`Row`,`AirplaneID`),
  ADD KEY `AirplaneID` (`AirplaneID`),
  ADD KEY `ClassName` (`ClassName`);

--
-- Indexes for table `Airport`
--
ALTER TABLE `Airport`
  ADD PRIMARY KEY (`AirportID`);

--
-- Indexes for table `Billing`
--
ALTER TABLE `Billing`
  ADD PRIMARY KEY (`BillingID`),
  ADD KEY `CardNo` (`CardNo`),
  ADD KEY `MemberID` (`MemberID`),
  ADD KEY `MemberPromotionID` (`MemberPromotionID`),
  ADD KEY `PromotionCode` (`PromotionCode`);

--
-- Indexes for table `Booking`
--
ALTER TABLE `Booking`
  ADD PRIMARY KEY (`BookingID`),
  ADD KEY `BillingID` (`BillingID`),
  ADD KEY `FlightID` (`FlightID`),
  ADD KEY `PassengerID` (`PassengerID`),
  ADD KEY `SeatID` (`SeatID`),
  ADD KEY `AddOnID` (`AddOnID`);

--
-- Indexes for table `Class`
--
ALTER TABLE `Class`
  ADD PRIMARY KEY (`ClassName`);

--
-- Indexes for table `Flight`
--
ALTER TABLE `Flight`
  ADD PRIMARY KEY (`FlightID`),
  ADD KEY `AirplaneID` (`AirplaneID`),
  ADD KEY `RouteID` (`RouteID`);

--
-- Indexes for table `MemberPromotion`
--
ALTER TABLE `MemberPromotion`
  ADD PRIMARY KEY (`MemberPromotionID`),
  ADD KEY `MemberID` (`MemberID`);

--
-- Indexes for table `Passenger`
--
ALTER TABLE `Passenger`
  ADD PRIMARY KEY (`PassengerID`);

--
-- Indexes for table `Promotion`
--
ALTER TABLE `Promotion`
  ADD PRIMARY KEY (`PromotionCode`);

--
-- Indexes for table `Route`
--
ALTER TABLE `Route`
  ADD PRIMARY KEY (`RouteID`),
  ADD KEY `Origin` (`Origin`),
  ADD KEY `Destination` (`Destination`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `AddOn`
--
ALTER TABLE `AddOn`
  MODIFY `AddOnID` int(3) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `Airplane`
--
ALTER TABLE `Airplane`
  MODIFY `AirplaneID` int(6) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `Billing`
--
ALTER TABLE `Billing`
  MODIFY `BillingID` int(10) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `Booking`
--
ALTER TABLE `Booking`
  MODIFY `BookingID` int(6) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `Flight`
--
ALTER TABLE `Flight`
  MODIFY `FlightID` int(6) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `Passenger`
--
ALTER TABLE `Passenger`
  MODIFY `PassengerID` int(8) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `Route`
--
ALTER TABLE `Route`
  MODIFY `RouteID` int(7) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=38;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `AddOn`
--
ALTER TABLE `AddOn`
  ADD CONSTRAINT `addon_ibfk_1` FOREIGN KEY (`ClassName`) REFERENCES `Class` (`ClassName`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `AirplaneSeat`
--
ALTER TABLE `AirplaneSeat`
  ADD CONSTRAINT `airplaneseat_ibfk_1` FOREIGN KEY (`AirplaneID`) REFERENCES `Airplane` (`AirplaneID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `airplaneseat_ibfk_2` FOREIGN KEY (`ClassName`) REFERENCES `Class` (`ClassName`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `Billing`
--
ALTER TABLE `Billing`
  ADD CONSTRAINT `billing_ibfk_1` FOREIGN KEY (`CardNo`) REFERENCES `PaymentMethod` (`CardNo`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `billing_ibfk_2` FOREIGN KEY (`MemberID`) REFERENCES `Member` (`MemberID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `billing_ibfk_3` FOREIGN KEY (`MemberPromotionID`) REFERENCES `MemberPromotion` (`MemberPromotionID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `billing_ibfk_4` FOREIGN KEY (`PromotionCode`) REFERENCES `Promotion` (`PromotionCode`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `Booking`
--
ALTER TABLE `Booking`
  ADD CONSTRAINT `booking_ibfk_1` FOREIGN KEY (`BillingID`) REFERENCES `Billing` (`BillingID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `booking_ibfk_2` FOREIGN KEY (`FlightID`) REFERENCES `Flight` (`FlightID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `booking_ibfk_3` FOREIGN KEY (`PassengerID`) REFERENCES `Passenger` (`PassengerID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `booking_ibfk_4` FOREIGN KEY (`AddOnID`) REFERENCES `AddOn` (`AddOnID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `Flight`
--
ALTER TABLE `Flight`
  ADD CONSTRAINT `flight_ibfk_2` FOREIGN KEY (`AirplaneID`) REFERENCES `Airplane` (`AirplaneID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `flight_ibfk_3` FOREIGN KEY (`RouteID`) REFERENCES `Route` (`RouteID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `MemberPromotion`
--
ALTER TABLE `MemberPromotion`
  ADD CONSTRAINT `memberpromotion_ibfk_1` FOREIGN KEY (`MemberID`) REFERENCES `Member` (`MemberID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `Route`
--
ALTER TABLE `Route`
  ADD CONSTRAINT `route_ibfk_1` FOREIGN KEY (`Origin`) REFERENCES `Airport` (`AirportID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `route_ibfk_2` FOREIGN KEY (`Destination`) REFERENCES `Airport` (`AirportID`);
COMMIT;

