-
Notifications
You must be signed in to change notification settings - Fork 5
/
mondial-schema-h2.sql
276 lines (243 loc) · 7.27 KB
/
mondial-schema-h2.sql
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
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
CREATE TABLE Country
(Name VARCHAR(35) NOT NULL UNIQUE,
Code VARCHAR(4),
Capital VARCHAR(35),
Province VARCHAR(35),
Area FLOAT,
Population INT,
CONSTRAINT CountryKey PRIMARY KEY(Code),
CONSTRAINT CountryArea CHECK (Area >= 0),
CONSTRAINT CountryPop CHECK (Population >= 0));
CREATE TABLE City
(Name VARCHAR(35),
Country VARCHAR(4),
Province VARCHAR(35),
Population INT,
Longitude FLOAT,
Latitude FLOAT,
CONSTRAINT CityKey PRIMARY KEY (Name, Country, Province),
CONSTRAINT CityPop CHECK (Population >= 0),
CONSTRAINT CityLon CHECK ((Longitude >= -180) AND (Longitude <= 180)),
CONSTRAINT CityLat CHECK ((Latitude >= -90) AND (Latitude <= 90)));
CREATE TABLE Province
(Name VARCHAR(35) NOT NULL,
Country VARCHAR(4) NOT NULL ,
Population INT,
Area FLOAT,
Capital VARCHAR(35),
CapProv VARCHAR(35),
CONSTRAINT PrKey PRIMARY KEY (Name, Country),
CONSTRAINT PrPop CHECK (Population >= 0),
CONSTRAINT PrAr CHECK (Area >= 0));
CREATE TABLE Economy
(Country VARCHAR(4),
GDP FLOAT,
Agriculture FLOAT,
Service FLOAT,
Industry FLOAT,
Inflation FLOAT,
CONSTRAINT EconomyKey PRIMARY KEY(Country),
CONSTRAINT EconomyGDP CHECK (GDP >= 0));
CREATE TABLE Population
(Country VARCHAR(4),
Population_Growth FLOAT,
Infant_Mortality FLOAT,
CONSTRAINT PopKey PRIMARY KEY(Country));
CREATE TABLE Politics
(Country VARCHAR(4),
Independence DATE,
Dependent VARCHAR(4),
Government VARCHAR(120),
CONSTRAINT PoliticsKey PRIMARY KEY(Country));
CREATE TABLE Language
(Country VARCHAR(4),
Name VARCHAR(50),
Percentage FLOAT,
CONSTRAINT LanguageKey PRIMARY KEY (Name, Country),
CONSTRAINT LanguagePercent
CHECK ((Percentage > 0) AND (Percentage <= 100)));
CREATE TABLE Religion
(Country VARCHAR(4),
Name VARCHAR(50),
Percentage FLOAT,
CONSTRAINT ReligionKey PRIMARY KEY (Name, Country),
CONSTRAINT ReligionPercent
CHECK ((Percentage > 0) AND (Percentage <= 100)));
CREATE TABLE EthnicGroup
(Country VARCHAR(4),
Name VARCHAR(50),
Percentage FLOAT,
CONSTRAINT EthnicKey PRIMARY KEY (Name, Country),
CONSTRAINT EthnicPercent
CHECK ((Percentage > 0) AND (Percentage <= 100)));
CREATE TABLE Continent
(Name VARCHAR(20),
Area FLOAT(10),
CONSTRAINT ContinentKey PRIMARY KEY(Name));
CREATE TABLE borders
(Country1 VARCHAR(4),
Country2 VARCHAR(4),
Length FLOAT,
CONSTRAINT BorderLength CHECK (Length > 0),
CONSTRAINT BorderKey PRIMARY KEY (Country1,Country2));
CREATE TABLE encompasses
(Country VARCHAR(4) NOT NULL,
Continent VARCHAR(20) NOT NULL,
Percentage FLOAT,
CONSTRAINT EncompassesPercentage CHECK ((Percentage > 0) AND (Percentage <= 100)),
CONSTRAINT EncompassesKey PRIMARY KEY (Country,Continent));
CREATE TABLE Organization
(Abbreviation VARCHAR(12) PRIMARY KEY,
Name VARCHAR(80) NOT NULL,
City VARCHAR(35) ,
Country VARCHAR(4) ,
Province VARCHAR(35) ,
Established DATE,
CONSTRAINT OrgNameUnique UNIQUE (Name));
CREATE TABLE isMember
(Country VARCHAR(4),
Organization VARCHAR(12),
Type VARCHAR(35) DEFAULT 'member',
CONSTRAINT MemberKey PRIMARY KEY (Country,Organization) );
CREATE TABLE Mountain
(Name VARCHAR(35),
Mountains VARCHAR(35),
Height FLOAT,
Type VARCHAR(10),
Longitude FLOAT,
Latitude FLOAT,
CONSTRAINT MountainKey PRIMARY KEY(Name),
CONSTRAINT MountainCoord CHECK ((Longitude >= -180) AND (Longitude <= 180)
AND (Latitude >= -90) AND (Latitude <= 90)));
CREATE TABLE Desert
(Name VARCHAR(35),
Area FLOAT,
Longitude FLOAT,
Latitude FLOAT,
CONSTRAINT DesertKey PRIMARY KEY(Name),
CONSTRAINT DesertCoord
CHECK ((Longitude >= -180) AND (Longitude <= 180)
AND (Latitude >= -90) AND (Latitude <= 90)));
CREATE TABLE Island
(Name VARCHAR(35),
Islands VARCHAR(35),
Area FLOAT,
Height FLOAT,
Type VARCHAR(10),
CONSTRAINT IslandKey PRIMARY KEY(Name),
CONSTRAINT IslandAr check (Area >= 0),
Longitude FLOAT,
Latitude FLOAT,
CONSTRAINT IslandCoord
CHECK ((Longitude >= -180) AND (Longitude <= 180)
AND (Latitude >= -90) AND (Latitude <= 90)));
CREATE TABLE Lake
(Name VARCHAR(35),
Area FLOAT,
Depth FLOAT,
Altitude FLOAT,
Type VARCHAR(10),
River VARCHAR(35),
Longitude FLOAT,
Latitude FLOAT,
CONSTRAINT LakeKey PRIMARY KEY(Name),
CONSTRAINT LakeAr CHECK (Area >= 0),
CONSTRAINT LakeDpth CHECK (Depth >= 0),
CONSTRAINT LakeCoord
CHECK ((Longitude >= -180) AND (Longitude <= 180)
AND (Latitude >= -90) AND (Latitude <= 90)));
CREATE TABLE Sea
(Name VARCHAR(35),
Depth FLOAT,
CONSTRAINT SeaKey PRIMARY KEY(Name),
CONSTRAINT SeaDepth CHECK (Depth >= 0));
CREATE TABLE River
(Name VARCHAR(35),
River VARCHAR(35),
Lake VARCHAR(35),
Sea VARCHAR(35),
Length FLOAT,
SourceLongitude FLOAT,
SourceLatitude FLOAT,
Mountains VARCHAR(35),
SourceAltitude FLOAT,
EstuaryLongitude FLOAT,
EstuaryLatitude FLOAT,
CONSTRAINT RiverKey PRIMARY KEY(Name),
CONSTRAINT RiverLength CHECK (Length >= 0),
CONSTRAINT SourceCoord
CHECK ((SourceLongitude >= -180) AND
(SourceLongitude <= 180) AND
(SourceLatitude >= -90) AND
(SourceLatitude <= 90)),
CONSTRAINT EstCoord
CHECK ((EstuaryLongitude >= -180) AND
(EstuaryLongitude <= 180) AND
(EstuaryLatitude >= -90) AND
(EstuaryLatitude <= 90)));
CREATE TABLE geo_Mountain
(Mountain VARCHAR(35) ,
Country VARCHAR(4) ,
Province VARCHAR(35) ,
CONSTRAINT GMountainKey PRIMARY KEY (Province,Country,Mountain) );
CREATE TABLE geo_Desert
(Desert VARCHAR(35) ,
Country VARCHAR(4) ,
Province VARCHAR(35) ,
CONSTRAINT GDesertKey PRIMARY KEY (Province, Country, Desert) );
CREATE TABLE geo_Island
(Island VARCHAR(35) ,
Country VARCHAR(4) ,
Province VARCHAR(35) ,
CONSTRAINT GIslandKey PRIMARY KEY (Province, Country, Island) );
CREATE TABLE geo_River
(River VARCHAR(35) ,
Country VARCHAR(4) ,
Province VARCHAR(35) ,
CONSTRAINT GRiverKey PRIMARY KEY (Province ,Country, River) );
CREATE TABLE geo_Sea
(Sea VARCHAR(35) ,
Country VARCHAR(4) ,
Province VARCHAR(35) ,
CONSTRAINT GSeaKey PRIMARY KEY (Province, Country, Sea) );
CREATE TABLE geo_Lake
(Lake VARCHAR(35) ,
Country VARCHAR(4) ,
Province VARCHAR(35) ,
CONSTRAINT GLakeKey PRIMARY KEY (Province, Country, Lake) );
CREATE TABLE geo_Source
(River VARCHAR(35) ,
Country VARCHAR(4) ,
Province VARCHAR(35) ,
CONSTRAINT GSourceKey PRIMARY KEY (Province, Country, River) );
CREATE TABLE geo_Estuary
(River VARCHAR(35) ,
Country VARCHAR(4) ,
Province VARCHAR(35) ,
CONSTRAINT GEstuaryKey PRIMARY KEY (Province, Country, River) );
CREATE TABLE mergesWith
(Sea1 VARCHAR(35) ,
Sea2 VARCHAR(35) ,
CONSTRAINT MergesWithKey PRIMARY KEY (Sea1, Sea2) );
CREATE TABLE located
(City VARCHAR(35) ,
Province VARCHAR(35) ,
Country VARCHAR(4) ,
River VARCHAR(35),
Lake VARCHAR(35),
Sea VARCHAR(35) );
CREATE TABLE locatedOn
(City VARCHAR(35) ,
Province VARCHAR(35) ,
Country VARCHAR(4) ,
Island VARCHAR(35) ,
CONSTRAINT locatedOnKey PRIMARY KEY (City, Province, Country, Island) );
CREATE TABLE islandIn
(Island VARCHAR(35) ,
Sea VARCHAR(35) ,
Lake VARCHAR(35) ,
River VARCHAR(35) );
CREATE TABLE MountainOnIsland
(Mountain VARCHAR(35),
Island VARCHAR(35),
CONSTRAINT MntIslKey PRIMARY KEY (Mountain, Island) );