1zMCaM73

1CREATE TABLE IF NOT EXISTS Airports (
2    ID_Airport text PRIMARY KEY,
3    Airport_name text NOT NULL UNIQUE,
4    City text NOT NULL,
5    State text NOT NULL,
6    Longitude real NOT NULL,
7    Latitude real NOT NULL
8);
9     
10CREATE TABLE IF NOT EXISTS Flights (
11    ID_Flight int PRIMARY KEY,
12    Delay_prediction real NOT NULL
13);
14 
15CREATE TABLE IF NOT EXISTS Flights_Airports_dep (
16    ID_Airport text,
17    ID_Flight text,
18    Actual_departure text,
19    Scheduled_departure text NOT NULL,
20    PRIMARY KEY (ID_Airport, ID_Flight, Actual_departure, Scheduled_departure),
21    FOREIGN KEY (ID_Airport) REFERENCES Airports (ID_Airport) 
22    ON DELETE CASCADE ON UPDATE CASCADE,
23    FOREIGN KEY (ID_Flight) REFERENCES Flights (ID_Flight) 
24    ON DELETE SET NULL ON UPDATE CASCADE
25);
26    
27CREATE TABLE IF NOT EXISTS Flights_Airports_ar (
28    ID_Airport text,
29    ID_Flight text,
30    Actual_arrival text,
31    Scheduled_arrival text NOT NULL,
32    PRIMARY KEY (ID_Airport, ID_Flight, Actual_arrival, Scheduled_arrival),
33    FOREIGN KEY (ID_Airport) REFERENCES Airports (ID_Airport) 
34    ON DELETE CASCADE ON UPDATE CASCADE,
35    FOREIGN KEY (ID_Flight) REFERENCES Flights (ID_Flight) 
36    ON DELETE SET NULL ON UPDATE CASCADE
37);
38    
39CREATE TABLE IF NOT EXISTS Airlines (
40    ID_Airline text PRIMARY KEY,
41    Airline_name text NOT NULL UNIQUE,
42    Airline_country text NOT NULL
43);    
44    
45CREATE TABLE IF NOT EXISTS Airplanes (
46    ID_Airplane text PRIMARY KEY,
47    ID_Airline text,
48    FOREIGN KEY (ID_Airline) REFERENCES Airlines (ID_Airline) 
49    ON DELETE SET NULL ON UPDATE CASCADE
50);
51
52CREATE TABLE IF NOT EXISTS Airplanes_Flights (
53    ID_Airplane text,
54    ID_Flight text,
55    PRIMARY KEY (ID_Airplane, ID_Flight),
56    FOREIGN KEY (ID_Airplane) REFERENCES Airplanes (ID_Airplane) 
57    ON DELETE SET NULL ON UPDATE CASCADE,
58    FOREIGN KEY (ID_Flight) REFERENCES Flights (ID_Flight) 
59    ON DELETE SET NULL ON UPDATE CASCADE,
60    UNIQUE(ID_Airplane, ID_Flight)
61);    
62    
63CREATE TABLE IF NOT EXISTS Authors (
64    ID_Author text PRIMARY KEY,
65    Country text
66);
67     
68CREATE TABLE IF NOT EXISTS Feedbacks (
69    ID_Feedback integer PRIMARY KEY AUTOINCREMENT,
70    ID_Airline text,
71    ID_Author text,
72    ID_Flight text,
73    Date text NOT NULL,
74    Score integer,
75    Recommendation integer,
76    Class text,
77    Content text,
78    FOREIGN KEY (ID_Airline) REFERENCES Airlines (ID_Airline) 
79    ON DELETE CASCADE ON UPDATE CASCADE,
80    FOREIGN KEY (ID_Author) REFERENCES Authors (ID_Author) 
81    ON DELETE SET NULL ON UPDATE CASCADE,
82    FOREIGN KEY (ID_Flight) REFERENCES Flights (ID_Flight) 
83    ON DELETE SET NULL ON UPDATE CASCADE
84);    
85    
86CREATE TRIGGER IF NOT EXISTS validate_feedback_rec_before_insert BEFORE INSERT ON Feedbacks
87    BEGIN
88    SELECT
89    CASE
90    WHEN NEW.Recommendation != '0' AND NEW.Recommendation != '1' AND NEW.Recommendation != NULL THEN
91    RAISE (ABORT, 'Invalid recommendation')
92    END;
93    END;