uPUQXMu5

1DROP DATABASE IF EXISTS qms;
2CREATE DATABASE qms;
3USE qms;
4
5CREATE TABLE staff (
6    id         INT PRIMARY KEY AUTO_INCREMENT,
7    firstname  VARCHAR(20),
8    secondname VARCHAR(20),
9    position   VARCHAR(20),
10    dob        DATE
11);
12
13CREATE TABLE passengers (
14    id         INT PRIMARY KEY AUTO_INCREMENT,
15    firstname  VARCHAR(20),
16    secondname VARCHAR(20),
17    dob        DATE,
18    staff_id   INT,
19    FOREIGN KEY (staff_id) REFERENCES staff(id)
20);
21
22CREATE TABLE flights (
23    id          INT PRIMARY KEY AUTO_INCREMENT,
24    destination VARCHAR(3),
25    departure   TIME,
26    arrival     TIME
27);
28
29CREATE TABLE bookings (
30    id          VARCHAR(20) PRIMARY KEY,
31    passenger_id INT NOT NULL,
32    flight_id   INT NOT NULL,
33    FOREIGN KEY (passenger_id) REFERENCES passengers(id),
34    FOREIGN KEY (flight_id)   REFERENCES flights(id)
35);
36
37-- dropping
38DROP TABLE staff, passengers, flights, bookings;
39DROP DATABASE qms;