CREATE TABLE Country
(Name VARCHAR(50) NOT NULL UNIQUE,
 Code VARCHAR(4) CONSTRAINT CountryKey PRIMARY KEY,
 Capital VARCHAR(50),
 Province VARCHAR(50),
 Area INTEGER CONSTRAINT CountryArea
   CHECK (Area >= 0),
 Population INTEGER CONSTRAINT CountryPop
   CHECK (Population >= 0));

CREATE TABLE City
(Name VARCHAR(50),
 Country VARCHAR(4),
 Province VARCHAR(50),
 Population INTEGER CONSTRAINT CityPop
   CHECK (Population >= 0),
 Latitude DOUBLE PRECISION CONSTRAINT CityLat
   CHECK ((Latitude >= -90) AND (Latitude <= 90)) ,
 Longitude DOUBLE PRECISION CONSTRAINT CityLon
   CHECK ((Longitude >= -180) AND (Longitude <= 180)) ,
 Elevation INTEGER ,
 CONSTRAINT CityKey PRIMARY KEY (Name, Country, Province));

CREATE TABLE Province
(Name VARCHAR(50) CONSTRAINT PrName NOT NULL ,
 Country  VARCHAR(4) CONSTRAINT PrCountry NOT NULL ,
 Population INTEGER CONSTRAINT PrPop
   CHECK (Population >= 0),
 Area INTEGER CONSTRAINT PrAr
   CHECK (Area >= 0),
 Capital VARCHAR(50),
 CapProv VARCHAR(50),
 CONSTRAINT PrKey PRIMARY KEY (Name, Country));

CREATE TABLE Economy
(Country VARCHAR(4) CONSTRAINT EconomyKey PRIMARY KEY,
 GDP INTEGER CONSTRAINT EconomyGDP
   CHECK (GDP >= 0),
 Agriculture DOUBLE PRECISION,
 Service DOUBLE PRECISION,
 Industry DOUBLE PRECISION,
 Inflation DOUBLE PRECISION,
 Unemployment DOUBLE PRECISION);

CREATE TABLE Population
(Country VARCHAR(4) CONSTRAINT PopKey PRIMARY KEY,
 Population_Growth INTEGER,
 Infant_Mortality DOUBLE PRECISION);

CREATE TABLE Politics
(Country VARCHAR(4) CONSTRAINT PoliticsKey PRIMARY KEY,
 Independence DATE,
 WasDependent VARCHAR(50),
 Dependent  VARCHAR(4),
 Government VARCHAR(120));

CREATE TABLE Language
(Country VARCHAR(4),
 Name VARCHAR(50),
 Percentage DOUBLE PRECISION CONSTRAINT LanguagePercent 
   CHECK ((Percentage > 0) AND (Percentage <= 100)),
 CONSTRAINT LanguageKey PRIMARY KEY (Name, Country));

CREATE TABLE Religion
(Country VARCHAR(4),
 Name VARCHAR(50),
 Percentage DOUBLE PRECISION CONSTRAINT ReligionPercent 
   CHECK ((Percentage > 0) AND (Percentage <= 100)),
 CONSTRAINT ReligionKey PRIMARY KEY (Name, Country));

CREATE TABLE EthnicGroup
(Country VARCHAR(4),
 Name VARCHAR(50),
 Percentage DOUBLE PRECISION CONSTRAINT EthnicPercent 
   CHECK ((Percentage > 0) AND (Percentage <= 100)),
 CONSTRAINT EthnicKey PRIMARY KEY (Name, Country));

CREATE TABLE Countrypops
(Country VARCHAR(4),
 Jahr INTEGER CONSTRAINT CountryPopsJahr
   CHECK (Jahr >= 0),
 Population INTEGER CONSTRAINT CountryPopsPop
   CHECK (Population >= 0),
 CONSTRAINT CountryPopsKey PRIMARY KEY (Country, Jahr));

CREATE TABLE Countryothername
(Country VARCHAR(4),
 othername VARCHAR(50),
 CONSTRAINT CountryOthernameKey PRIMARY KEY (Country, othername));

CREATE TABLE Countrylocalname
(Country VARCHAR(4),
 localname VARCHAR(300),
 CONSTRAINT CountrylocalnameKey PRIMARY KEY (Country));

CREATE TABLE Provpops
(Province VARCHAR(50),
 Country VARCHAR(4),
 Jahr INTEGER CONSTRAINT ProvPopsJahr
   CHECK (Jahr >= 0),
 Population INTEGER CONSTRAINT ProvPopsPop
   CHECK (Population >= 0),
 CONSTRAINT ProvPopKey PRIMARY KEY (Country, Province, Jahr));

CREATE TABLE Provinceothername
(Province VARCHAR(50),
 Country VARCHAR(4),
 othername VARCHAR(50),
 CONSTRAINT ProvOthernameKey PRIMARY KEY (Country, Province, othername));

CREATE TABLE Provincelocalname
(Province VARCHAR(50),
 Country VARCHAR(4),
 localname VARCHAR(300),
 CONSTRAINT ProvlocalnameKey PRIMARY KEY (Country, Province));

CREATE TABLE Citypops
(City VARCHAR(50),
 Country VARCHAR(4),
 Province VARCHAR(50),
 Jahr INTEGER CONSTRAINT CityPopsJahr
   CHECK (Jahr >= 0),
 Population INTEGER CONSTRAINT CityPopsPop
   CHECK (Population >= 0),
 CONSTRAINT CityPopKey PRIMARY KEY (Country, Province, City, Jahr));

CREATE TABLE Cityothername
(City VARCHAR(50),
 Country VARCHAR(4),
 Province VARCHAR(50),
 othername VARCHAR(50),
 CONSTRAINT CityOthernameKey PRIMARY KEY (Country, Province, City, othername));

CREATE TABLE Citylocalname
(City VARCHAR(50),
 Country VARCHAR(4),
 Province VARCHAR(50),
 localname VARCHAR(300),
 CONSTRAINT CitylocalnameKey PRIMARY KEY (Country, Province, City));

CREATE TABLE Continent
(Name VARCHAR(20) CONSTRAINT ContinentKey PRIMARY KEY,
 Area NUMERIC(10));

CREATE TABLE borders
(Country1 VARCHAR(4),
 Country2 VARCHAR(4),
 Length INTEGER 
   CHECK (Length >= 0),  -- Original > 0
 CONSTRAINT BorderKey PRIMARY KEY (Country1,Country2) );

CREATE TABLE encompasses
(Country VARCHAR(4) NOT NULL,
 Continent VARCHAR(20) NOT NULL,
 Percentage DOUBLE PRECISION,
   CHECK ((Percentage > 0) AND (Percentage <= 100)),
 CONSTRAINT EncompassesKey PRIMARY KEY (Country,Continent));

CREATE TABLE Organization
(Abbreviation VARCHAR(12) Constraint OrgKey PRIMARY KEY,
 Name VARCHAR(100) NOT NULL,
 City VARCHAR(50) ,
 Country VARCHAR(4) , 
 Province VARCHAR(50) ,
 Established DATE,
 CONSTRAINT OrgNameUnique UNIQUE (Name));

CREATE TABLE isMember
(Country VARCHAR(4),
 Organization VARCHAR(12),
 Type VARCHAR(60) DEFAULT 'member',
 CONSTRAINT MemberKey PRIMARY KEY (Country,Organization) );

CREATE TABLE Mountain
(Name VARCHAR(50) CONSTRAINT MountainKey PRIMARY KEY,
 Mountains VARCHAR(50),
 Elevation INTEGER,
 Type VARCHAR(10),
Latitude DOUBLE PRECISION,
Longitude DOUBLE PRECISION
);

CREATE TABLE Desert
(Name VARCHAR(50) CONSTRAINT DesertKey PRIMARY KEY,
 Area INTEGER,
Latitude DOUBLE PRECISION,
Longitude DOUBLE PRECISION
);

CREATE TABLE Island
(Name VARCHAR(50) CONSTRAINT IslandKey PRIMARY KEY,
 Islands VARCHAR(50),
 Area INTEGER CONSTRAINT IslandAr check (Area >= 0),
 Elevation INTEGER,
 Type VARCHAR(10),
Latitude DOUBLE PRECISION,
Longitude DOUBLE PRECISION
);

CREATE TABLE Lake
(Name VARCHAR(50) CONSTRAINT LakeKey PRIMARY KEY,
 River VARCHAR(50),
 Area INTEGER CONSTRAINT LakeAr CHECK (Area >= 0),
 Elevation INTEGER,
 Depth INTEGER CONSTRAINT LakeDpth CHECK (Depth >= 0),
 Height INTEGER CONSTRAINT DamHeight CHECK (Height > 0),
 Type VARCHAR(12),
Latitude DOUBLE PRECISION,
Longitude DOUBLE PRECISION
);

CREATE TABLE Sea
(Name VARCHAR(50) CONSTRAINT SeaKey PRIMARY KEY,
 Area INTEGER CONSTRAINT SeaAr CHECK (Area >= 0),
 Depth INTEGER CONSTRAINT SeaDepth CHECK (Depth >= 0));

CREATE TABLE River
(Name VARCHAR(50) CONSTRAINT RiverKey PRIMARY KEY,
 River VARCHAR(50),
 Lake VARCHAR(50),
 Sea VARCHAR(50),
 Length INTEGER CONSTRAINT RiverLength
   CHECK (Length >= 0),
 Area INTEGER CONSTRAINT RiverArea
   CHECK (Area >= 0),
Latitude2 DOUBLE PRECISION,
Longitude2 DOUBLE PRECISION
,
 Mountains VARCHAR(50),
 SourceElevation INTEGER,
Latitude DOUBLE PRECISION,
Longitude DOUBLE PRECISION
,
 EstuaryElevation INTEGER,
 CONSTRAINT RivFlowsInto 
     CHECK ((River IS NULL AND Lake IS NULL)
            OR (River IS NULL AND Sea IS NULL)
            OR (Lake IS NULL AND Sea is NULL)));

CREATE TABLE RiverThrough
(River VARCHAR(50),
 Lake  VARCHAR(50),
 CONSTRAINT RThroughKey PRIMARY KEY (River,Lake) );

CREATE TABLE geo_Mountain
(Mountain VARCHAR(50) ,
 Country VARCHAR(4) ,
 Province VARCHAR(50) ,
 CONSTRAINT GMountainKey PRIMARY KEY (Province,Country,Mountain) );

CREATE TABLE geo_Desert
(Desert VARCHAR(50) ,
 Country VARCHAR(4) ,
 Province VARCHAR(50) ,
 CONSTRAINT GDesertKey PRIMARY KEY (Province, Country, Desert) );

CREATE TABLE geo_Island
(Island VARCHAR(50) , 
 Country VARCHAR(4) ,
 Province VARCHAR(50) ,
 CONSTRAINT GIslandKey PRIMARY KEY (Province, Country, Island) );

CREATE TABLE geo_River
(River VARCHAR(50) , 
 Country VARCHAR(4) ,
 Province VARCHAR(50) ,
 CONSTRAINT GRiverKey PRIMARY KEY (Province ,Country, River) );

CREATE TABLE geo_Sea
(Sea VARCHAR(50) ,
 Country VARCHAR(4)  ,
 Province VARCHAR(50) ,
 CONSTRAINT GSeaKey PRIMARY KEY (Province, Country, Sea) );

CREATE TABLE geo_Lake
(Lake VARCHAR(50) ,
 Country VARCHAR(4) ,
 Province VARCHAR(50) ,
 CONSTRAINT GLakeKey PRIMARY KEY (Province, Country, Lake) );

CREATE TABLE geo_Source
(River VARCHAR(50) ,
 Country VARCHAR(4) ,
 Province VARCHAR(50) ,
 CONSTRAINT GSourceKey PRIMARY KEY (Province, Country, River) );

CREATE TABLE geo_Estuary
(River VARCHAR(50) ,
 Country VARCHAR(4) ,
 Province VARCHAR(50) ,
 CONSTRAINT GEstuaryKey PRIMARY KEY (Province, Country, River) );

CREATE TABLE mergesWith
(Sea1 VARCHAR(50) ,
 Sea2 VARCHAR(50) ,
 CONSTRAINT MergesWithKey PRIMARY KEY (Sea1, Sea2) );

CREATE TABLE located
(City VARCHAR(50) ,
 Province VARCHAR(50) ,
 Country VARCHAR(4) ,
 River VARCHAR(50),
 Lake VARCHAR(50),
 Sea VARCHAR(50) );

CREATE TABLE locatedOn
(City VARCHAR(50) ,
 Province VARCHAR(50) ,
 Country VARCHAR(4) ,
 Island VARCHAR(50) ,
 CONSTRAINT locatedOnKey PRIMARY KEY (City, Province, Country, Island) );

CREATE TABLE islandIn
(Island VARCHAR(50) ,
 Sea VARCHAR(50) ,
 Lake VARCHAR(50) ,
 River VARCHAR(50) );

CREATE TABLE MountainOnIsland
(Mountain VARCHAR(50),
 Island   VARCHAR(50),
 CONSTRAINT MountainIslKey PRIMARY KEY (Mountain, Island) );

CREATE TABLE LakeOnIsland
(Lake    VARCHAR(50),
 Island  VARCHAR(50),
 CONSTRAINT LakeIslKey PRIMARY KEY (Lake, Island) );

CREATE TABLE RiverOnIsland
(River   VARCHAR(50),
 Island  VARCHAR(50),
 CONSTRAINT RiverIslKey PRIMARY KEY (River, Island) );

CREATE TABLE Airport
(IATACode VARCHAR(3) PRIMARY KEY,
 Name VARCHAR(100) ,
 Country VARCHAR(4) ,
 City VARCHAR(50) ,
 Province VARCHAR(50) ,
 Island VARCHAR(50) ,
 Latitude DOUBLE PRECISION CONSTRAINT AirpLat
   CHECK ((Latitude >= -90) AND (Latitude <= 90)) ,
 Longitude DOUBLE PRECISION CONSTRAINT AirpLon
   CHECK ((Longitude >= -180) AND (Longitude <= 180)) ,
 Elevation INTEGER ,
 gmtOffset INTEGER );
