· 6 years ago · Jan 24, 2020, 08:28 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`.`Airplane`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `mydb`.`Airplane` (
21 `airplaneid` INT(5) NOT NULL AUTO_INCREMENT,
22 `airline` VARCHAR(45) NOT NULL,
23 `departure` VARCHAR(45) NOT NULL,
24 `destination` VARCHAR(45) NOT NULL,
25 `seats_amount` VARCHAR(45) NOT NULL,
26 PRIMARY KEY (`airplaneid`))
27ENGINE = InnoDB
28DEFAULT CHARACTER SET = utf8;
29
30
31-- -----------------------------------------------------
32-- Table `mydb`.`Departure`
33-- -----------------------------------------------------
34CREATE TABLE IF NOT EXISTS `mydb`.`Departure` (
35 `departurenr` INT(3) NOT NULL AUTO_INCREMENT,
36 `departurecountry` VARCHAR(255) NOT NULL,
37 `airportdept` VARCHAR(45) NOT NULL,
38 `Airplane_airplaneid` INT(11) NOT NULL,
39 PRIMARY KEY (`departurenr`, `Airplane_airplaneid`))
40ENGINE = InnoDB
41DEFAULT CHARACTER SET = utf8;
42
43
44-- -----------------------------------------------------
45-- Table `mydb`.`Destination`
46-- -----------------------------------------------------
47CREATE TABLE IF NOT EXISTS `mydb`.`Destination` (
48 `destinationnr` INT(3) NOT NULL AUTO_INCREMENT,
49 `destinationcountry` VARCHAR(255) NOT NULL,
50 `airportdest` VARCHAR(45) NOT NULL,
51 `Airplane_airplaneid` INT(11) NOT NULL,
52 PRIMARY KEY (`destinationnr`, `Airplane_airplaneid`))
53ENGINE = InnoDB
54DEFAULT CHARACTER SET = utf8;
55
56
57-- -----------------------------------------------------
58-- Table `mydb`.`Ticket`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS `mydb`.`Ticket` (
61 `ticketnr` INT(10) NOT NULL AUTO_INCREMENT,
62 `class` VARCHAR(45) NOT NULL,
63 `Airplane_airplaneid` INT(5) NOT NULL,
64 `Destination_destinationnr` INT(3) NOT NULL,
65 `Destination_Airplane_airplaneid` INT(11) NOT NULL,
66 `Departure_departurenr` INT(3) NOT NULL,
67 `Departure_Airplane_airplaneid` INT(5) NOT NULL,
68 `Departure_departurenr1` INT(3) NOT NULL,
69 `Departure_Airplane_airplaneid1` INT(11) NOT NULL,
70 `Destination_destinationnr1` INT(3) NOT NULL,
71 `Destination_Airplane_airplaneid1` INT(11) NOT NULL,
72 PRIMARY KEY (`ticketnr`, `Airplane_airplaneid`, `Destination_destinationnr`, `Destination_Airplane_airplaneid`, `Departure_departurenr`, `Departure_Airplane_airplaneid`, `Departure_departurenr1`, `Departure_Airplane_airplaneid1`, `Destination_destinationnr1`, `Destination_Airplane_airplaneid1`),
73 INDEX `fk_Ticket_Airplane1_idx` (`Airplane_airplaneid` ASC) VISIBLE,
74 INDEX `fk_Ticket_Departure1_idx` (`Departure_departurenr1` ASC, `Departure_Airplane_airplaneid1` ASC) VISIBLE,
75 INDEX `fk_Ticket_Destination1_idx` (`Destination_destinationnr1` ASC, `Destination_Airplane_airplaneid1` ASC) VISIBLE,
76 CONSTRAINT `fk_Ticket_Airplane1`
77 FOREIGN KEY (`Airplane_airplaneid`)
78 REFERENCES `mydb`.`Airplane` (`airplaneid`),
79 CONSTRAINT `fk_Ticket_Departure1`
80 FOREIGN KEY (`Departure_departurenr1` , `Departure_Airplane_airplaneid1`)
81 REFERENCES `mydb`.`Departure` (`departurenr` , `Airplane_airplaneid`)
82 ON DELETE NO ACTION
83 ON UPDATE NO ACTION,
84 CONSTRAINT `fk_Ticket_Destination1`
85 FOREIGN KEY (`Destination_destinationnr1` , `Destination_Airplane_airplaneid1`)
86 REFERENCES `mydb`.`Destination` (`destinationnr` , `Airplane_airplaneid`)
87 ON DELETE NO ACTION
88 ON UPDATE NO ACTION)
89ENGINE = InnoDB
90DEFAULT CHARACTER SET = utf8;
91
92
93-- -----------------------------------------------------
94-- Table `mydb`.`Passenger`
95-- -----------------------------------------------------
96CREATE TABLE IF NOT EXISTS `mydb`.`Passenger` (
97 `passengerid` VARCHAR(45) NULL,
98 `firstname` VARCHAR(45) NOT NULL,
99 `lastname` VARCHAR(45) NOT NULL,
100 `destination` VARCHAR(45) NOT NULL,
101 `departure` VARCHAR(45) NOT NULL,
102 `Ticket_ticketnr` INT(10) NOT NULL,
103 `Ticket_Airplane_airplaneid` INT(5) NOT NULL,
104 `Ticket_Destination_destinationnr` INT(3) NOT NULL,
105 `Ticket_Destination_Airplane_airplaneid` INT(11) NOT NULL,
106 `Ticket_Departure_departurenr` INT(3) NOT NULL,
107 `Ticket_Departure_Airplane_airplaneid` INT(5) NOT NULL,
108 PRIMARY KEY (`passengerid`, `Ticket_ticketnr`, `Ticket_Airplane_airplaneid`, `Ticket_Destination_destinationnr`, `Ticket_Destination_Airplane_airplaneid`, `Ticket_Departure_departurenr`, `Ticket_Departure_Airplane_airplaneid`),
109 INDEX `fk_Passenger_Ticket1_idx` (`Ticket_ticketnr` ASC, `Ticket_Airplane_airplaneid` ASC, `Ticket_Destination_destinationnr` ASC, `Ticket_Destination_Airplane_airplaneid` ASC, `Ticket_Departure_departurenr` ASC, `Ticket_Departure_Airplane_airplaneid` ASC) VISIBLE,
110 CONSTRAINT `fk_Passenger_Ticket1`
111 FOREIGN KEY (`Ticket_ticketnr` , `Ticket_Airplane_airplaneid` , `Ticket_Destination_destinationnr` , `Ticket_Destination_Airplane_airplaneid` , `Ticket_Departure_departurenr` , `Ticket_Departure_Airplane_airplaneid`)
112 REFERENCES `mydb`.`Ticket` (`ticketnr` , `Airplane_airplaneid` , `Destination_destinationnr` , `Destination_Airplane_airplaneid` , `Departure_departurenr` , `Departure_Airplane_airplaneid`)
113 ON DELETE NO ACTION
114 ON UPDATE NO ACTION)
115ENGINE = InnoDB
116DEFAULT CHARACTER SET = utf8;
117
118
119SET SQL_MODE=@OLD_SQL_MODE;
120SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
121SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
122
123-- -----------------------------------------------------
124-- Data for table `mydb`.`Airplane`
125-- -----------------------------------------------------
126START TRANSACTION;
127USE `mydb`;
128INSERT INTO `mydb`.`Airplane` (`airplaneid`, `airline`, `departure`, `destination`, `seats_amount`) VALUES (85321, 'KLM', 'Schiphol', 'Paris aéroport', '200');
129INSERT INTO `mydb`.`Airplane` (`airplaneid`, `airline`, `departure`, `destination`, `seats_amount`) VALUES (59099, 'Corendon', 'LAX', 'Prague airport', '289');
130INSERT INTO `mydb`.`Airplane` (`airplaneid`, `airline`, `departure`, `destination`, `seats_amount`) VALUES (76336, 'Turkish Airlines', 'Warsaw intl.', 'Warsaw intl.', '127');
131INSERT INTO `mydb`.`Airplane` (`airplaneid`, `airline`, `departure`, `destination`, `seats_amount`) VALUES (54532, 'Corendon', 'Prague airport', 'LAX', '312');
132INSERT INTO `mydb`.`Airplane` (`airplaneid`, `airline`, `departure`, `destination`, `seats_amount`) VALUES (77875, 'KLM', 'Paris aéroport', 'Schiphol', '110');
133
134COMMIT;
135
136
137-- -----------------------------------------------------
138-- Data for table `mydb`.`Departure`
139-- -----------------------------------------------------
140START TRANSACTION;
141USE `mydb`;
142INSERT INTO `mydb`.`Departure` (`departurenr`, `departurecountry`, `airportdept`, `Airplane_airplaneid`) VALUES (356, 'Netherlands', 'Schiphol', DEFAULT);
143INSERT INTO `mydb`.`Departure` (`departurenr`, `departurecountry`, `airportdept`, `Airplane_airplaneid`) VALUES (543, 'USA', 'LAX', DEFAULT);
144INSERT INTO `mydb`.`Departure` (`departurenr`, `departurecountry`, `airportdept`, `Airplane_airplaneid`) VALUES (233, 'Poland', 'Warsaw intl.', DEFAULT);
145INSERT INTO `mydb`.`Departure` (`departurenr`, `departurecountry`, `airportdept`, `Airplane_airplaneid`) VALUES (386, 'Czech Republic', 'Prague airport', DEFAULT);
146INSERT INTO `mydb`.`Departure` (`departurenr`, `departurecountry`, `airportdept`, `Airplane_airplaneid`) VALUES (967, 'France', 'Paris aéroport', DEFAULT);
147
148COMMIT;
149
150
151-- -----------------------------------------------------
152-- Data for table `mydb`.`Destination`
153-- -----------------------------------------------------
154START TRANSACTION;
155USE `mydb`;
156INSERT INTO `mydb`.`Destination` (`destinationnr`, `destinationcountry`, `airportdest`, `Airplane_airplaneid`) VALUES (356, 'Netherlands', 'Schiphol', DEFAULT);
157INSERT INTO `mydb`.`Destination` (`destinationnr`, `destinationcountry`, `airportdest`, `Airplane_airplaneid`) VALUES (542, 'USA', 'LAX', DEFAULT);
158INSERT INTO `mydb`.`Destination` (`destinationnr`, `destinationcountry`, `airportdest`, `Airplane_airplaneid`) VALUES (233, 'Poland', 'Warsaw intl.', DEFAULT);
159INSERT INTO `mydb`.`Destination` (`destinationnr`, `destinationcountry`, `airportdest`, `Airplane_airplaneid`) VALUES (386, 'Czech Republic', 'Prague airport', DEFAULT);
160INSERT INTO `mydb`.`Destination` (`destinationnr`, `destinationcountry`, `airportdest`, `Airplane_airplaneid`) VALUES (967, 'France', 'Paris aéroport', DEFAULT);
161
162COMMIT;
163
164
165-- -----------------------------------------------------
166-- Data for table `mydb`.`Ticket`
167-- -----------------------------------------------------
168START TRANSACTION;
169USE `mydb`;
170INSERT INTO `mydb`.`Ticket` (`ticketnr`, `class`, `Airplane_airplaneid`, `Destination_destinationnr`, `Destination_Airplane_airplaneid`, `Departure_departurenr`, `Departure_Airplane_airplaneid`, `Departure_departurenr1`, `Departure_Airplane_airplaneid1`, `Destination_destinationnr1`, `Destination_Airplane_airplaneid1`) VALUES (3315977564, 'Business', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
171INSERT INTO `mydb`.`Ticket` (`ticketnr`, `class`, `Airplane_airplaneid`, `Destination_destinationnr`, `Destination_Airplane_airplaneid`, `Departure_departurenr`, `Departure_Airplane_airplaneid`, `Departure_departurenr1`, `Departure_Airplane_airplaneid1`, `Destination_destinationnr1`, `Destination_Airplane_airplaneid1`) VALUES (910405466, 'First', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
172INSERT INTO `mydb`.`Ticket` (`ticketnr`, `class`, `Airplane_airplaneid`, `Destination_destinationnr`, `Destination_Airplane_airplaneid`, `Departure_departurenr`, `Departure_Airplane_airplaneid`, `Departure_departurenr1`, `Departure_Airplane_airplaneid1`, `Destination_destinationnr1`, `Destination_Airplane_airplaneid1`) VALUES (7465627178, 'Business', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
173INSERT INTO `mydb`.`Ticket` (`ticketnr`, `class`, `Airplane_airplaneid`, `Destination_destinationnr`, `Destination_Airplane_airplaneid`, `Departure_departurenr`, `Departure_Airplane_airplaneid`, `Departure_departurenr1`, `Departure_Airplane_airplaneid1`, `Destination_destinationnr1`, `Destination_Airplane_airplaneid1`) VALUES (5345456235, 'Economy', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
174INSERT INTO `mydb`.`Ticket` (`ticketnr`, `class`, `Airplane_airplaneid`, `Destination_destinationnr`, `Destination_Airplane_airplaneid`, `Departure_departurenr`, `Departure_Airplane_airplaneid`, `Departure_departurenr1`, `Departure_Airplane_airplaneid1`, `Destination_destinationnr1`, `Destination_Airplane_airplaneid1`) VALUES (9385923755, 'First', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
175
176COMMIT;
177
178
179-- -----------------------------------------------------
180-- Data for table `mydb`.`Passenger`
181-- -----------------------------------------------------
182START TRANSACTION;
183USE `mydb`;
184INSERT INTO `mydb`.`Passenger` (`passengerid`, `firstname`, `lastname`, `destination`, `departure`, `Ticket_ticketnr`, `Ticket_Airplane_airplaneid`, `Ticket_Destination_destinationnr`, `Ticket_Destination_Airplane_airplaneid`, `Ticket_Departure_departurenr`, `Ticket_Departure_Airplane_airplaneid`) VALUES ('1', 'Kees', 'de Vries', 'Perth', 'Amsterdam', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
185INSERT INTO `mydb`.`Passenger` (`passengerid`, `firstname`, `lastname`, `destination`, `departure`, `Ticket_ticketnr`, `Ticket_Airplane_airplaneid`, `Ticket_Destination_destinationnr`, `Ticket_Destination_Airplane_airplaneid`, `Ticket_Departure_departurenr`, `Ticket_Departure_Airplane_airplaneid`) VALUES ('2', 'Jan', 'Roos', 'Warsaw', 'Amsterdam', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
186INSERT INTO `mydb`.`Passenger` (`passengerid`, `firstname`, `lastname`, `destination`, `departure`, `Ticket_ticketnr`, `Ticket_Airplane_airplaneid`, `Ticket_Destination_destinationnr`, `Ticket_Destination_Airplane_airplaneid`, `Ticket_Departure_departurenr`, `Ticket_Departure_Airplane_airplaneid`) VALUES ('3', 'Els', 'van Os', 'Los Angeles', 'Amsterdam', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
187INSERT INTO `mydb`.`Passenger` (`passengerid`, `firstname`, `lastname`, `destination`, `departure`, `Ticket_ticketnr`, `Ticket_Airplane_airplaneid`, `Ticket_Destination_destinationnr`, `Ticket_Destination_Airplane_airplaneid`, `Ticket_Departure_departurenr`, `Ticket_Departure_Airplane_airplaneid`) VALUES ('4', 'Ben', 'Bakker', 'Prague', 'Eindhoven', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
188INSERT INTO `mydb`.`Passenger` (`passengerid`, `firstname`, `lastname`, `destination`, `departure`, `Ticket_ticketnr`, `Ticket_Airplane_airplaneid`, `Ticket_Destination_destinationnr`, `Ticket_Destination_Airplane_airplaneid`, `Ticket_Departure_departurenr`, `Ticket_Departure_Airplane_airplaneid`) VALUES ('5', 'Berend', 'Thijs', 'Paris', 'Amsterdam', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
189
190COMMIT;