· 6 years ago · Jan 03, 2020, 01:26 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema mydb
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
15USE `mydb` ;
16
17-- -----------------------------------------------------
18-- Table `mydb`.`Passenger`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `mydb`.`Passenger` (
21 `Passengernr` VARCHAR(255) NOT NULL,
22 `firstname` VARCHAR(255) NOT NULL,
23 `lastname` VARCHAR(255) NOT NULL,
24 `city` VARCHAR(255) NOT NULL,
25 `address` VARCHAR(255) NOT NULL,
26 PRIMARY KEY (`Passengernr`))
27ENGINE = InnoDB;
28
29
30-- -----------------------------------------------------
31-- Table `mydb`.`airportArr`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `mydb`.`airportArr` (
34 `Airportcode` INT NOT NULL,
35 `city` VARCHAR(255) NOT NULL,
36 `date_time` VARCHAR(255) NOT NULL,
37 `Passenger_Passengernr` VARCHAR(255) NOT NULL,
38 PRIMARY KEY (`Airportcode`, `Passenger_Passengernr`),
39 INDEX `fk_airportArr_Passenger1_idx` (`Passenger_Passengernr` ASC) VISIBLE,
40 CONSTRAINT `fk_airportArr_Passenger1`
41 FOREIGN KEY (`Passenger_Passengernr`)
42 REFERENCES `mydb`.`Passenger` (`Passengernr`)
43 ON DELETE NO ACTION
44 ON UPDATE NO ACTION)
45ENGINE = InnoDB;
46
47
48-- -----------------------------------------------------
49-- Table `mydb`.`AirportDep`
50-- -----------------------------------------------------
51CREATE TABLE IF NOT EXISTS `mydb`.`AirportDep` (
52 `Airportcode` INT NOT NULL,
53 `city` VARCHAR(255) NOT NULL,
54 `date_time` VARCHAR(255) NOT NULL,
55 `Passenger_Passengernr` VARCHAR(255) NOT NULL,
56 `airportArr_Airportcode` INT NOT NULL,
57 `airportArr_Passenger_Passengernr` VARCHAR(255) NOT NULL,
58 PRIMARY KEY (`Airportcode`, `Passenger_Passengernr`, `airportArr_Airportcode`, `airportArr_Passenger_Passengernr`),
59 INDEX `fk_AirportDep_Passenger1_idx` (`Passenger_Passengernr` ASC) VISIBLE,
60 INDEX `fk_AirportDep_airportArr1_idx` (`airportArr_Airportcode` ASC, `airportArr_Passenger_Passengernr` ASC) VISIBLE,
61 CONSTRAINT `fk_AirportDep_Passenger1`
62 FOREIGN KEY (`Passenger_Passengernr`)
63 REFERENCES `mydb`.`Passenger` (`Passengernr`)
64 ON DELETE NO ACTION
65 ON UPDATE NO ACTION,
66 CONSTRAINT `fk_AirportDep_airportArr1`
67 FOREIGN KEY (`airportArr_Airportcode` , `airportArr_Passenger_Passengernr`)
68 REFERENCES `mydb`.`airportArr` (`Airportcode` , `Passenger_Passengernr`)
69 ON DELETE NO ACTION
70 ON UPDATE NO ACTION)
71ENGINE = InnoDB;
72
73
74-- -----------------------------------------------------
75-- Table `mydb`.`Airplane`
76-- -----------------------------------------------------
77CREATE TABLE IF NOT EXISTS `mydb`.`Airplane` (
78 `AirplaneID` INT NOT NULL,
79 `seats` INT NOT NULL,
80 `Manufacturer` VARCHAR(255) NOT NULL,
81 `Airline` VARCHAR(255) NOT NULL,
82 PRIMARY KEY (`AirplaneID`))
83ENGINE = InnoDB;
84
85
86-- -----------------------------------------------------
87-- Table `mydb`.`Ticket`
88-- -----------------------------------------------------
89CREATE TABLE IF NOT EXISTS `mydb`.`Ticket` (
90 `Ticketnr` VARCHAR(255) NOT NULL,
91 `airport of dep` VARCHAR(255) NOT NULL,
92 `des airport` VARCHAR(255) NOT NULL,
93 `date_time_dep` VARCHAR(255) NOT NULL,
94 `Passenger_Passengernr` VARCHAR(255) NOT NULL,
95 `AirportDep_Airportcode` INT NOT NULL,
96 `AirportDep_Passenger_Passengernr` VARCHAR(255) NOT NULL,
97 `airportArr_Airportcode` INT NOT NULL,
98 `airportArr_Passenger_Passengernr` VARCHAR(255) NOT NULL,
99 `Airplane_AirplaneID` INT NOT NULL,
100 PRIMARY KEY (`Ticketnr`, `Passenger_Passengernr`, `AirportDep_Airportcode`, `AirportDep_Passenger_Passengernr`, `airportArr_Airportcode`, `airportArr_Passenger_Passengernr`, `Airplane_AirplaneID`),
101 INDEX `fk_Ticket_Passenger_idx` (`Passenger_Passengernr` ASC) VISIBLE,
102 INDEX `fk_Ticket_AirportDep1_idx` (`AirportDep_Airportcode` ASC, `AirportDep_Passenger_Passengernr` ASC) VISIBLE,
103 INDEX `fk_Ticket_airportArr1_idx` (`airportArr_Airportcode` ASC, `airportArr_Passenger_Passengernr` ASC) VISIBLE,
104 INDEX `fk_Ticket_Airplane1_idx` (`Airplane_AirplaneID` ASC) VISIBLE,
105 CONSTRAINT `fk_Ticket_Passenger`
106 FOREIGN KEY (`Passenger_Passengernr`)
107 REFERENCES `mydb`.`Passenger` (`Passengernr`)
108 ON DELETE NO ACTION
109 ON UPDATE NO ACTION,
110 CONSTRAINT `fk_Ticket_AirportDep1`
111 FOREIGN KEY (`AirportDep_Airportcode` , `AirportDep_Passenger_Passengernr`)
112 REFERENCES `mydb`.`AirportDep` (`Airportcode` , `Passenger_Passengernr`)
113 ON DELETE NO ACTION
114 ON UPDATE NO ACTION,
115 CONSTRAINT `fk_Ticket_airportArr1`
116 FOREIGN KEY (`airportArr_Airportcode` , `airportArr_Passenger_Passengernr`)
117 REFERENCES `mydb`.`airportArr` (`Airportcode` , `Passenger_Passengernr`)
118 ON DELETE NO ACTION
119 ON UPDATE NO ACTION,
120 CONSTRAINT `fk_Ticket_Airplane1`
121 FOREIGN KEY (`Airplane_AirplaneID`)
122 REFERENCES `mydb`.`Airplane` (`AirplaneID`)
123 ON DELETE NO ACTION
124 ON UPDATE NO ACTION)
125ENGINE = InnoDB;
126
127
128SET SQL_MODE=@OLD_SQL_MODE;
129SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
130SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;