--
-- Host: localhost    Database: flightclub
-- ------------------------------------------------------


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

CREATE TABLE `Airplane` (
  `AirplaneID` int(11) NOT NULL,
  `Brand` text DEFAULT NULL,
  `Star_rating` double DEFAULT NULL,
  PRIMARY KEY (`AirplaneID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `Airplane`
--

INSERT INTO `Airplane` VALUES (111111,'Delta Air Lines',4),(123456,'United Airlines',3),(222222,'American Airlines',3),(333333,'Delta Air Lines',4),(444444,'EVA Air',4.5),(555555,'AirAsia X',3.5),(666666,'AirAsia X',3.5),(777777,'Cape Air',3.5);

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

CREATE TABLE `Airport` (
  `AirportID` int(11) NOT NULL,
  `Name` text DEFAULT NULL,
  `Address` text DEFAULT NULL,
  `Star_rating` double DEFAULT NULL,
  PRIMARY KEY (`AirportID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

INSERT INTO `Airport` VALUES (111111,'John F. Kennedy International Airport','Queens, NY 11430, USA',4),(123456,'Bakersfield Municipal Airport','2000 S Union Ave, Bakersfield, CA 93307, USA',4.6),(222222,'Dallas/Fort Worth Internation Airport','2400 Aviation Dr, DFW Airport, TX 75261, USA',4.1),(333333,'Los Angeles International Airport','1 World Way, Los Angeles, CA 90045, USA',3.9),(444444,'Southwest Florida International Airport','11000 Terminal Access Rd, Fort Myers, FL 33913, USA',4.5),(555555,'Hilo International Airport','2450 Kekuanaoa St, Hilo, HI 96720, USA',4.3),(666666,'Indira Gandhi International Airport','New Delhi, Delhi, 110037, India',4.5),(777777,'Nirata International Airport','1-1 Furugome, Narita, Chiba 282-0004, Japan',4.2),(888888,'Paris Charles de Gaulle Airport','95700 Roissy-en-France, France',3.7),(999999,'Beijing Capital Airport','Shunyi District, Beijing, China',3.6);

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

CREATE TABLE `Class` (
  `ClassID` int(11) NOT NULL,
  `Name` text DEFAULT NULL,
  PRIMARY KEY (`ClassID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

INSERT INTO `Class` VALUES (1,'Economy'),(2,'Premium Economy'),(3,'Business'),(4,'First-Class');

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

CREATE TABLE `Flight` (
  `FlightID` int(11) NOT NULL,
  `AirplaneID` int(11) NOT NULL,
  `Departure_time` datetime DEFAULT NULL,
  `Boarding_time` time DEFAULT NULL,
  `Depart_airportID` int(11) DEFAULT NULL,
  `Arrival_time` datetime DEFAULT NULL,
  `Arrive_airportID` int(11) DEFAULT NULL,
  PRIMARY KEY (`FlightID`,`AirplaneID`),
  KEY `AirplaneID` (`AirplaneID`),
  KEY `Arrive_airportID` (`Arrive_airportID`),
  KEY `Depart_airportID` (`Depart_airportID`),
  CONSTRAINT `Flight_ibfk_1` FOREIGN KEY (`AirplaneID`) REFERENCES `Airplane` (`AirplaneID`),
  CONSTRAINT `Flight_ibfk_2` FOREIGN KEY (`Arrive_airportID`) REFERENCES `Airport` (`AirportID`),
  CONSTRAINT `Flight_ibfk_3` FOREIGN KEY (`Depart_airportID`) REFERENCES `Airport` (`AirportID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `Flight`
--

INSERT INTO `Flight` VALUES (1,123456,'2021-11-05 06:45:00','06:30:00',123456,'2021-11-05 10:31:00',111111),(2,111111,'2021-09-05 00:22:00','00:00:00',123456,'2021-09-05 03:14:00',222222),(3,222222,'2022-05-13 09:22:00','09:00:00',123456,'2022-05-13 12:33:00',555555),(4,111111,'2022-07-18 12:07:00','11:30:00',888888,'2022-07-19 23:25:00',333333),(5,222222,'2021-11-05 12:14:00','11:40:00',777777,'2021-11-06 05:37:00',555555),(6,111111,'2021-09-19 06:15:00','05:50:00',555555,'2021-09-19 10:58:00',222222),(7,444444,'2021-04-15 02:38:00','02:10:00',444444,'2021-04-17 10:17:00',777777),(8,555555,'2022-08-08 23:38:00','23:10:00',111111,'2022-08-10 04:25:00',777777),(9,666666,'2021-04-20 17:47:00','17:22:00',666666,'2021-04-21 21:46:00',111111),(10,555555,'2022-02-08 15:20:00','14:50:00',999999,'2022-02-10 21:56:00',333333),(11,111111,'2022-05-30 04:31:00','04:00:00',222222,'2022-05-31 22:18:00',888888),(12,666666,'2021-11-24 20:29:00','20:00:00',222222,'2021-11-26 20:35:00',999999),(13,123456,'2021-10-24 22:41:00','22:10:00',444444,'2021-10-24 00:52:00',555555),(14,111111,'2022-08-06 02:05:00','01:25:00',555555,'2022-08-06 05:04:00',333333),(15,333333,'2022-04-01 18:30:00','18:00:00',111111,'2022-04-01 23:04:00',444444),(16,333333,'2021-04-23 17:21:00','16:49:00',111111,'2021-04-14 11:47:00',333333),(17,777777,'2021-03-21 22:13:00','21:40:00',333333,'2021-03-21 21:06:00',111111),(18,444444,'2021-07-03 12:39:00','12:10:00',333333,'2021-07-06 01:08:00',888888),(19,555555,'2022-12-17 07:12:00','06:40:00',333333,'2022-12-19 11:55:00',666666);

--
-- Table structure for table `Reserves`
--

CREATE TABLE `Reserves` (
  `FlightID` int(11) NOT NULL,
  `CusID` int(11) NOT NULL,
  `AirplaneID` int(11) NOT NULL,
  `Seat_num` int(11) NOT NULL,
  PRIMARY KEY (`FlightID`,`CusID`,`AirplaneID`,`Seat_num`),
  KEY `FlightID` (`FlightID`,`AirplaneID`),
  KEY `CusID` (`CusID`),
  KEY `AirplaneID` (`AirplaneID`,`Seat_num`),
  CONSTRAINT `Reserves_ibfk_1` FOREIGN KEY (`FlightID`, `AirplaneID`) REFERENCES `Flight` (`FlightID`, `AirplaneID`),
  CONSTRAINT `Reserves_ibfk_2` FOREIGN KEY (`CusID`) REFERENCES `Customer` (`CusID`),
  CONSTRAINT `Reserves_ibfk_3` FOREIGN KEY (`AirplaneID`, `Seat_num`) REFERENCES `Seat` (`AirplaneID`, `Seat_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `Reserves`
--

INSERT INTO `Reserves` VALUES (1,3,123456,3),(1,4,123456,98),(1,5,123456,53),(1,10,123456,32),(2,6,111111,1),(2,8,111111,88),(4,7,111111,35),(5,4,222222,22),(5,4,222222,47),(5,4,222222,81),(5,5,222222,70),(5,8,222222,8),(6,5,111111,1),(6,5,111111,61),(6,8,111111,55),(6,9,111111,35),(6,9,111111,88),(6,10,111111,35),(7,7,444444,11),(8,7,555555,80),(9,2,666666,2),(9,4,666666,56),(10,4,555555,24),(12,3,666666,23),(13,5,123456,79),(13,7,123456,3),(14,2,111111,61),(14,8,111111,1),(14,9,111111,61),(15,2,333333,14),(15,2,333333,20),(15,3,333333,48),(15,4,333333,85),(15,6,333333,99),(15,8,333333,21),(16,3,333333,60),(16,4,333333,60),(16,8,333333,5),(17,8,777777,52),(17,9,777777,30),(17,9,777777,62),(18,4,444444,49),(18,5,444444,5),(18,10,444444,53),(19,1,555555,24),(19,2,555555,98),(19,3,555555,36),(19,4,555555,76),(19,8,555555,80),(19,10,555555,96);

--
-- Table structure for table `Seat`
--

CREATE TABLE `Seat` (
  `Seat_num` int(11) NOT NULL,
  `AirplaneID` int(11) NOT NULL,
  `Price` int(11) DEFAULT NULL,
  `ClassID` int(11) DEFAULT NULL,
  PRIMARY KEY (`Seat_num`,`AirplaneID`),
  KEY `AirplaneID` (`AirplaneID`),
  KEY `ClassID` (`ClassID`),
  CONSTRAINT `Seat_ibfk_1` FOREIGN KEY (`AirplaneID`) REFERENCES `Airplane` (`AirplaneID`),
  CONSTRAINT `Seat_ibfk_2` FOREIGN KEY (`ClassID`) REFERENCES `Class` (`ClassID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `Seat`
--

INSERT INTO `Seat` VALUES (1,111111,231,1),(2,666666,1644,4),(3,123456,1453,1),(5,333333,233,2),(5,444444,189,1),(6,777777,686,3),(8,222222,277,1),(11,444444,1333,3),(14,333333,111,2),(16,666666,1123,2),(20,333333,1543,3),(21,333333,1342,3),(22,222222,653,4),(23,666666,1449,1),(24,555555,255,3),(28,555555,1176,3),(30,777777,551,1),(32,123456,543,3),(34,666666,89,2),(35,111111,111,3),(36,555555,178,2),(46,444444,1233,3),(47,222222,1342,3),(48,333333,324,3),(49,444444,234,3),(51,666666,765,4),(52,666666,299,1),(52,777777,481,3),(53,123456,66,3),(53,444444,1444,3),(55,111111,436,2),(56,666666,395,3),(57,333333,1765,4),(59,777777,311,3),(60,333333,1133,1),(61,111111,1169,2),(62,777777,611,3),(69,444444,263,2),(70,222222,322,2),(76,555555,666,4),(77,777777,1542,4),(79,123456,127,3),(80,555555,432,4),(81,222222,777,2),(85,333333,166,3),(88,111111,123,3),(96,555555,445,3),(98,123456,1101,1),(98,555555,1305,3),(99,333333,654,1);


/*
CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `ClassID` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
*/
