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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
|
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
AUTOCOMMIT OFF;
CREATE TABLE AIRLINES
(
AIRLINE CHAR(2) NOT NULL ,
AIRLINE_FULL VARCHAR(24),
BASIC_RATE DOUBLE PRECISION,
DISTANCE_DISCOUNT DOUBLE PRECISION,
BUSINESS_LEVEL_FACTOR DOUBLE PRECISION,
FIRSTCLASS_LEVEL_FACTOR DOUBLE PRECISION,
ECONOMY_SEATS INTEGER,
BUSINESS_SEATS INTEGER,
FIRSTCLASS_SEATS INTEGER
);
ALTER TABLE AIRLINES
ADD CONSTRAINT AIRLINES_PK Primary Key (
AIRLINE);
-- \************************************************************\
CREATE TABLE COUNTRIES
(
COUNTRY VARCHAR(26) NOT NULL,
COUNTRY_ISO_CODE CHAR(2) NOT NULL ,
REGION VARCHAR(26)
);
ALTER TABLE COUNTRIES
ADD CONSTRAINT COUNTRIES_PK Primary Key (
COUNTRY_ISO_CODE);
ALTER TABLE COUNTRIES
ADD CONSTRAINT COUNTRIES_UNQ_NM Unique (
COUNTRY);
ALTER TABLE COUNTRIES
ADD CONSTRAINT COUNTRIES_UC
CHECK (country_ISO_code = upper(country_ISO_code) );
-- \************************************************************\
CREATE TABLE CITIES
(
CITY_ID INTEGER NOT NULL ,
CITY_NAME VARCHAR(24) NOT NULL,
COUNTRY VARCHAR(26) NOT NULL,
AIRPORT VARCHAR(3),
LANGUAGE VARCHAR(16),
COUNTRY_ISO_CODE CHAR(2)
);
ALTER TABLE CITIES
ADD CONSTRAINT CITIES_PK Primary Key (
CITY_ID);
ALTER TABLE CITIES
ADD CONSTRAINT COUNTRIES_FK Foreign Key (
COUNTRY_ISO_CODE)
REFERENCES COUNTRIES (
COUNTRY_ISO_CODE);
-- \************************************************************\
CREATE TABLE FLIGHTS
(
FLIGHT_ID CHAR(6) NOT NULL ,
SEGMENT_NUMBER INTEGER NOT NULL ,
ORIG_AIRPORT CHAR(3),
DEPART_TIME TIME,
DEST_AIRPORT CHAR(3),
ARRIVE_TIME TIME,
MEAL CHAR(1),
FLYING_TIME DOUBLE PRECISION,
MILES INTEGER,
AIRCRAFT VARCHAR(6)
);
CREATE INDEX DESTINDEX ON FLIGHTS (
DEST_AIRPORT) ;
CREATE INDEX ORIGINDEX ON FLIGHTS (
ORIG_AIRPORT) ;
ALTER TABLE FLIGHTS
ADD CONSTRAINT FLIGHTS_PK Primary Key (
FLIGHT_ID,
SEGMENT_NUMBER);
ALTER TABLE FLIGHTS
ADD CONSTRAINT MEAL_CONSTRAINT
CHECK (meal IN ('B', 'L', 'D', 'S'));
-- \************************************************************\
CREATE TABLE FLIGHTAVAILABILITY
(
FLIGHT_ID CHAR(6) NOT NULL ,
SEGMENT_NUMBER INTEGER NOT NULL ,
FLIGHT_DATE DATE NOT NULL ,
ECONOMY_SEATS_TAKEN INTEGER DEFAULT 0,
BUSINESS_SEATS_TAKEN INTEGER DEFAULT 0,
FIRSTCLASS_SEATS_TAKEN INTEGER DEFAULT 0
);
ALTER TABLE FLIGHTAVAILABILITY
ADD CONSTRAINT FLIGHTAVAIL_PK Primary Key (
FLIGHT_ID,
SEGMENT_NUMBER,
FLIGHT_DATE);
ALTER TABLE FLIGHTAVAILABILITY
ADD CONSTRAINT FLIGHTS_FK2 Foreign Key (
FLIGHT_ID,
SEGMENT_NUMBER)
REFERENCES FLIGHTS (
FLIGHT_ID,
SEGMENT_NUMBER);
-- \************************************************************\
CREATE TABLE MAPS
(
MAP_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
MAP_NAME VARCHAR(24) NOT NULL,
REGION VARCHAR(26),
AREA DECIMAL(8,4) NOT NULL,
PHOTO_FORMAT VARCHAR(26) NOT NULL,
PICTURE BLOB(102400),
UNIQUE (MAP_ID, MAP_NAME)
);
-- \************************************************************\
CREATE TABLE FLIGHTS_HISTORY
(
FLIGHT_ID CHAR(6),
SEGMENT_NUMBER INTEGER,
ORIG_AIRPORT CHAR(3),
DEPART_TIME TIME,
DEST_AIRPORT CHAR(3),
ARRIVE_TIME TIME,
MEAL CHAR(1),
FLYING_TIME DOUBLE PRECISION,
MILES INTEGER,
AIRCRAFT VARCHAR(6),
STATUS VARCHAR (20)
);
-- \************************************************************\
CREATE TRIGGER TRIG1 AFTER UPDATE ON FLIGHTS REFERENCING OLD AS UPDATEDROW FOR EACH ROW INSERT INTO FLIGHTS_HISTORY VALUES (UPDATEDROW.FLIGHT_ID, UPDATEDROW.SEGMENT_NUMBER, UPDATEDROW.ORIG_AIRPORT,UPDATEDROW.DEPART_TIME, UPDATEDROW.DEST_AIRPORT,UPDATEDROW.ARRIVE_TIME,UPDATEDROW.MEAL, UPDATEDROW.FLYING_TIME, UPDATEDROW.MILES, UPDATEDROW.AIRCRAFT,'INSERTED FROM TRIG1');
CREATE TRIGGER TRIG2 AFTER DELETE ON FLIGHTS FOR EACH STATEMENT
INSERT INTO FLIGHTS_HISTORY (STATUS) VALUES ('INSERTED FROM TRIG2');
COMMIT;
|
