Andmebaasi deklaratsioon.sql
1.15 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
CREATE TABLE `Flights`(
`destination` CHAR(255) NOT NULL,
`time` CHAR(255) NOT NULL,
`seats` INT NOT NULL,
primary key(destination)
);
CREATE TABLE `Bookings`(
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`flightNum` CHAR(255) NOT NULL,
`bookingNumber` BIGINT NOT NULL,
primary key(id)
);
CREATE TABLE `Users`(
`documentNum` BIGINT NOT NULL,
`firstName` CHAR(255) NULL,
`lastName` CHAR(255) NULL,
`dateOfBirth` CHAR(255) NULL,
`email` CHAR(255) NULL,
`residency` CHAR(255) NULL,
`checkedIn` TINYINT(1) NOT NULL,
`seat` CHAR(255) NOT NULL,
`luggageClass` INT NOT NULL,
primary key(documentNum)
);
ALTER TABLE
`Bookings` ADD CONSTRAINT `bookings_flightnum_foreign` FOREIGN KEY(`flightNum`) REFERENCES `Flights`(`destination`);
ALTER TABLE
`Bookings` ADD CONSTRAINT `bookings_bookingnumber_foreign` FOREIGN KEY(`bookingNumber`) REFERENCES `Users`(`documentNum`);
INSERT INTO flights(destination, time, seats)
VALUES
("Helsinki", "050950JUN23", 72),
("Kuressaare", "021440JUN23", 72),
("Kärdla", "081700JUN23", 72),
("Stockholm", "071230JUN23", 72),
("Riga", "122200JUN23", 72);