4FrseQyU

1DROP TABLE IF EXISTS Booking;
2DROP TABLE IF EXISTS Trip;
3DROP TABLE IF EXISTS Person;
4DROP TABLE IF EXISTS Location;
5 
6CREATE TABLE Location (
7        LocationID int NOT NULL AUTO_INCREMENT,
8        Name varchar(255) NOT NULL,
9        Code varchar(255),
10       
11        PRIMARY KEY (LocationID)
12);
13 
14INSERT INTO Location (Name, Code)
15VALUES ('luchthaven antwerpen','ant');
16INSERT INTO Location (Name, Code)
17VALUES ('luchthaven zaventem','zvt');
18INSERT INTO Location (Name, Code)
19VALUES ('luchthaven oostende','oos');
20 
21 
22 
23CREATE TABLE Person (
24    PersonID int NOT NULL AUTO_INCREMENT,
25    FirstName varchar(255),
26    LastName varchar(255),
27    Email varchar(255),
28    PassKey varchar(255),
29    TypeUser int NOT NULL,
30   
31    PRIMARY KEY (PersonID)
32);
33 
34INSERT INTO Person (FirstName, LastName, Email, PassKey, TypeUser)
35VALUES ('Nick','Vandepaer', 'nick@test.com', 'azerty', '0');
36INSERT INTO Person (FirstName, LastName, Email, PassKey, TypeUser)
37VALUES ('Jef','Pet', 'jef@test.com', 'azerty', '0');
38INSERT INTO Person (FirstName, LastName, Email, PassKey, TypeUser)
39VALUES ('Vincent','Vanhoek', 'vincent@test.com', 'java', '1');
40 
41CREATE TABLE Trip (
42    TripID int NOT NULL AUTO_INCREMENT,
43    Transport varchar(255),
44    Price double,
45    StartDate Date,
46    EndDate Date,
47    Places int,
48    Country varchar(255),
49    City varchar(255),
50    Title varchar(255),
51   
52    ArrivalLocationID int,
53    DepartLocationID int,
54 
55    FOREIGN KEY (arrivalLocationID) REFERENCES Location(LocationID),
56    FOREIGN KEY (DepartLocationID) REFERENCES Location(LocationID),
57   
58    PRIMARY KEY (TripID)
59   
60);
61 
62INSERT INTO Trip (Transport, Price, StartDate, EndDate, Places, Country, City, Title, ArrivalLocationID, DepartLocationID)
63VALUES ('Vliegtuig','150', '2018-11-15 00:00:00', '2018-11-20 00:00:00', '8', 'Frankrijk', 'Parijs', '5 daagse naar disneyland', '1', '2');
64INSERT INTO Trip (Transport, Price, StartDate, EndDate, Places, Country, City, Title, ArrivalLocationID, DepartLocationID)
65VALUES ('Bus','100', '2018-11-20 00:00:00', '2018-11-25 00:00:00', '18', 'Frankrijk', 'Parijs', '5 daagse naar disneyland', '1', '2');
66INSERT INTO Trip (Transport, Price, StartDate, EndDate, Places, Country, City, Title, ArrivalLocationID, DepartLocationID)
67VALUES ('Vliegtuig','244', '2018-11-4 00:00:00', '2018-11-11 00:00:00', '5', 'Spanje', 'Barcelona', 'Zonvakantie', '2', '3');
68 
69CREATE TABLE Booking (
70    BookingID int NOT NULL AUTO_INCREMENT,
71    Persons int NOT NULL,
72    Payed boolean,
73    Note varchar(255),
74   
75    TripID int,
76    PersonID int,
77   
78    PRIMARY KEY (BookingID),
79   
80    FOREIGN KEY (TripID) REFERENCES Trip(TripID),
81    FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
82);
83 
84INSERT INTO Booking (Persons, Payed, Note, TripID, PersonID)
85VALUES ('2', false, 'Dit is een opmerking', '1', '1');
86INSERT INTO Booking (Persons, Payed, Note, TripID, PersonID)
87VALUES ('4', true, '2 personen eten geen vlees', '2', '2');
88INSERT INTO Booking (Persons, Payed, TripID, PersonID)
89VALUES ('5', false, '3', '3');