ALTER SESSION SET NLS_DATE_FORMAT = 'DD MM SYYYY';
-- SYYYY means 4-digit-year, S prefixes BC years with "-"

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

CREATE TABLE City
(Name VARCHAR2(40),
 Country VARCHAR2(4),
 Province VARCHAR2(40),
 Population NUMBER CONSTRAINT CityPop
   CHECK (Population >= 0),
 Longitude NUMBER CONSTRAINT CityLon
   CHECK ((Longitude >= -180) AND (Longitude <= 180)) ,
 Latitude NUMBER CONSTRAINT CityLat
   CHECK ((Latitude >= -90) AND (Latitude <= 90)) ,
 Elevation NUMBER ,
 CONSTRAINT CityKey PRIMARY KEY (Name, Country, Province));

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

CREATE TABLE Economy
(Country VARCHAR2(4) CONSTRAINT EconomyKey PRIMARY KEY,
 GDP NUMBER CONSTRAINT EconomyGDP
   CHECK (GDP >= 0),
 Agriculture NUMBER,
 Service NUMBER,
 Industry NUMBER,
 Inflation NUMBER);

CREATE TABLE Population
(Country VARCHAR2(4) CONSTRAINT PopKey PRIMARY KEY,
 Population_Growth NUMBER,
 Infant_Mortality NUMBER);

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

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

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

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

CREATE TABLE Continent
(Name VARCHAR2(20) CONSTRAINT ContinentKey PRIMARY KEY,
 Area NUMBER(10));

CREATE TABLE borders
(Country1 VARCHAR2(4),
 Country2 VARCHAR2(4),
 Length NUMBER 
   CHECK (Length > 0),
 CONSTRAINT BorderKey PRIMARY KEY (Country1,Country2) );

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

CREATE TABLE Organization
(Abbreviation VARCHAR2(12) PRIMARY KEY,
 Name VARCHAR2(80) NOT NULL,
 City VARCHAR2(40) ,
 Country VARCHAR2(4) , 
 Province VARCHAR2(40) ,
 Established DATE,
 CONSTRAINT OrgNameUnique UNIQUE (Name));

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

CREATE OR REPLACE TYPE GeoCoord AS OBJECT
(Longitude NUMBER,
 Latitude NUMBER);
/

CREATE TABLE Mountain
(Name VARCHAR2(40) CONSTRAINT MountainKey PRIMARY KEY,
 Mountains VARCHAR2(40),
 Elevation NUMBER,
 Type VARCHAR2(10),
 Coordinates GeoCoord CONSTRAINT MountainCoord
     CHECK ((Coordinates.Longitude >= -180) AND 
            (Coordinates.Longitude <= 180) AND
            (Coordinates.Latitude >= -90) AND
            (Coordinates.Latitude <= 90)));

CREATE TABLE Desert
(Name VARCHAR2(40) CONSTRAINT DesertKey PRIMARY KEY,
 Area NUMBER,
 Coordinates GeoCoord CONSTRAINT DesCoord
     CHECK ((Coordinates.Longitude >= -180) AND 
            (Coordinates.Longitude <= 180) AND
            (Coordinates.Latitude >= -90) AND
            (Coordinates.Latitude <= 90)));

CREATE TABLE Island
(Name VARCHAR2(40) CONSTRAINT IslandKey PRIMARY KEY,
 Islands VARCHAR2(40),
 Area NUMBER CONSTRAINT IslandAr check (Area >= 0),
 Elevation NUMBER,
 Type VARCHAR2(10),
 Coordinates GeoCoord CONSTRAINT IslandCoord
     CHECK ((Coordinates.Longitude >= -180) AND 
            (Coordinates.Longitude <= 180) AND
            (Coordinates.Latitude >= -90) AND
            (Coordinates.Latitude <= 90)));

CREATE TABLE Lake
(Name VARCHAR2(40) CONSTRAINT LakeKey PRIMARY KEY,
 Area NUMBER CONSTRAINT LakeAr CHECK (Area >= 0),
 Depth NUMBER CONSTRAINT LakeDpth CHECK (Depth >= 0),
 Elevation NUMBER,
 Type VARCHAR2(10),
 River VARCHAR2(40),
 Coordinates GeoCoord CONSTRAINT LakeCoord
     CHECK ((Coordinates.Longitude >= -180) AND 
            (Coordinates.Longitude <= 180) AND
            (Coordinates.Latitude >= -90) AND
            (Coordinates.Latitude <= 90)));

CREATE TABLE Sea
(Name VARCHAR2(40) CONSTRAINT SeaKey PRIMARY KEY,
 Depth NUMBER CONSTRAINT SeaDepth CHECK (Depth >= 0));

CREATE TABLE River
(Name VARCHAR2(40) CONSTRAINT RiverKey PRIMARY KEY,
 River VARCHAR2(40),
 Lake VARCHAR2(40),
 Sea VARCHAR2(40),
 Length NUMBER CONSTRAINT RiverLength
   CHECK (Length >= 0),
 Source GeoCoord CONSTRAINT SourceCoord
     CHECK ((Source.Longitude >= -180) AND 
            (Source.Longitude <= 180) AND
            (Source.Latitude >= -90) AND
            (Source.Latitude <= 90)),
 Mountains VARCHAR2(40),
 SourceElevation NUMBER,
 Estuary GeoCoord CONSTRAINT EstCoord
     CHECK ((Estuary.Longitude >= -180) AND 
            (Estuary.Longitude <= 180) AND
            (Estuary.Latitude >= -90) AND
            (Estuary.Latitude <= 90)));

CREATE TABLE geo_Mountain
(Mountain VARCHAR2(40) ,
 Country VARCHAR2(4) ,
 Province VARCHAR2(40) ,
 CONSTRAINT GMountainKey PRIMARY KEY (Province,Country,Mountain) );

CREATE TABLE geo_Desert
(Desert VARCHAR2(40) ,
 Country VARCHAR2(4) ,
 Province VARCHAR2(40) ,
 CONSTRAINT GDesertKey PRIMARY KEY (Province, Country, Desert) );

CREATE TABLE geo_Island
(Island VARCHAR2(40) , 
 Country VARCHAR2(4) ,
 Province VARCHAR2(40) ,
 CONSTRAINT GIslandKey PRIMARY KEY (Province, Country, Island) );

CREATE TABLE geo_River
(River VARCHAR2(40) , 
 Country VARCHAR2(4) ,
 Province VARCHAR2(40) ,
 CONSTRAINT GRiverKey PRIMARY KEY (Province ,Country, River) );

CREATE TABLE geo_Sea
(Sea VARCHAR2(40) ,
 Country VARCHAR2(4)  ,
 Province VARCHAR2(40) ,
 CONSTRAINT GSeaKey PRIMARY KEY (Province, Country, Sea) );

CREATE TABLE geo_Lake
(Lake VARCHAR2(40) ,
 Country VARCHAR2(4) ,
 Province VARCHAR2(40) ,
 CONSTRAINT GLakeKey PRIMARY KEY (Province, Country, Lake) );

CREATE TABLE geo_Source
(River VARCHAR2(40) ,
 Country VARCHAR2(4) ,
 Province VARCHAR2(40) ,
 CONSTRAINT GSourceKey PRIMARY KEY (Province, Country, River) );

CREATE TABLE geo_Estuary
(River VARCHAR2(40) ,
 Country VARCHAR2(4) ,
 Province VARCHAR2(40) ,
 CONSTRAINT GEstuaryKey PRIMARY KEY (Province, Country, River) );

CREATE TABLE mergesWith
(Sea1 VARCHAR2(40) ,
 Sea2 VARCHAR2(40) ,
 CONSTRAINT MergesWithKey PRIMARY KEY (Sea1, Sea2) );

CREATE TABLE located
(City VARCHAR2(40) ,
 Province VARCHAR2(40) ,
 Country VARCHAR2(4) ,
 River VARCHAR2(40),
 Lake VARCHAR2(40),
 Sea VARCHAR2(40) );

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

CREATE TABLE islandIn
(Island VARCHAR2(40) ,
 Sea VARCHAR2(40) ,
 Lake VARCHAR2(40) ,
 River VARCHAR2(40) );

CREATE TABLE MountainOnIsland
(Mountain VARCHAR2(40),
 Island  VARCHAR2(40),
 CONSTRAINT MntIslKey PRIMARY KEY (Mountain, Island) );