3DbkFUqT

1    sql_create_airports_table = """ CREATE 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 
10    
11    sql_create_flights_table = """ CREATE TABLE IF NOT EXISTS Flights (
12                                        ID_Flight int PRIMARY KEY,
13                                        Delay_prediction real NOT NULL
14                                    ); """
15
16 
17    sql_create_flights_airports_dep_table = """ CREATE TABLE IF NOT EXISTS Flights_Airports_dep (
18                                        ID_Airport text,
19                                        ID_Flight text,
20                                        Actual_departure text,
21                                        Scheduled_departure text NOT NULL,
22                                        PRIMARY KEY (ID_Airport, ID_Flight, Actual_departure, Scheduled_departure),
23                                        FOREIGN KEY (ID_Airport) REFERENCES Airports (ID_Airport) 
24                                        ON DELETE CASCADE ON UPDATE CASCADE,
25                                        FOREIGN KEY (ID_Flight) REFERENCES Flights (ID_Flight) 
26                                        ON DELETE SET NULL ON UPDATE CASCADE
27                                    ); """
28
29    
30    sql_create_flights_airports_ar_table = """ CREATE TABLE IF NOT EXISTS Flights_Airports_ar (
31                                        ID_Airport text,
32                                        ID_Flight text,
33                                        Actual_arrival text,
34                                        Scheduled_arrival text NOT NULL,
35                                        PRIMARY KEY (ID_Airport, ID_Flight, Actual_arrival, Scheduled_arrival),
36                                        FOREIGN KEY (ID_Airport) REFERENCES Airports (ID_Airport) 
37                                        ON DELETE CASCADE ON UPDATE CASCADE,
38                                        FOREIGN KEY (ID_Flight) REFERENCES Flights (ID_Flight) 
39                                        ON DELETE SET NULL ON UPDATE CASCADE
40                                    ); """
41
42    
43    sql_create_airlines_table = """ CREATE TABLE IF NOT EXISTS Airlines (
44                                        ID_Airline text PRIMARY KEY,
45                                        Airline_name text NOT NULL UNIQUE,
46                                        Airline_country text NOT NULL
47                                    ); """
48    
49    
50    sql_create_airplanes_table = """ CREATE TABLE IF NOT EXISTS Airplanes (
51                                        ID_Airplane text PRIMARY KEY,
52                                        ID_Airline text,
53                                        FOREIGN KEY (ID_Airline) REFERENCES Airlines (ID_Airline) 
54                                        ON DELETE SET NULL ON UPDATE CASCADE
55                                    ); """
56
57
58CREATE TABLE IF NOT EXISTS Airplanes_Flights (
59ID_Airplane text,
60ID_Flight text,
61PRIMARY KEY (ID_Airplane, ID_Flight),
62FOREIGN KEY (ID_Airplane) REFERENCES Airplanes (ID_Airplane) 
63ON DELETE SET NULL ON UPDATE CASCADE,
64FOREIGN KEY (ID_Flight) REFERENCES Flights (ID_Flight) 
65ON DELETE SET NULL ON UPDATE CASCADE,
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        UNIQUE(ID_Airline, ID_Author, ID_Flight, Date)
85);
86    
87CREATE TRIGGER IF NOT EXISTS validate_feedback_rec_before_insert BEFORE UPDATE ON Feedbacks
88    BEGIN
89    SELECT CASE 
90    WHEN ((SELECT Feedbacks.ID_Author FROM Feedbacks WHERE Feedbacks.ID_Author = NEW.ID_Author ) ISNULL) 
91    THEN RAISE(ABORT, 'This is an User Define Error Message - This Author_id does not exist.') 
92    END; 
93    END;