CREATE TABLE Country 
(Name VARCHAR(32) NOT NULL, 
 Code VARCHAR(4) NOT NULL, 
 Capital VARCHAR(35), 
 Province VARCHAR(32), 
 Area INTEGER, 
 Population INTEGER, 
 PRIMARY KEY (Code) 
);

CREATE TABLE Province 
(Name VARCHAR(32) NOT NULL , 
 Country  VARCHAR(4) NOT NULL, 
 Population INTEGER, 
 Area INTEGER, 
 Capital VARCHAR(35), 
 CapProv VARCHAR(32), 
 PRIMARY KEY (Name,Country), 
FOREIGN KEY (Country) REFERENCES Country (Code) 
);

CREATE TABLE City 
(Name VARCHAR(35) NOT NULL, 
 Country VARCHAR(4) NOT NULL, 
 Province VARCHAR(32) NOT NULL, 
 Population INTEGER,  
 Longitude INTEGER, 
 Latitude INTEGER, 
 PRIMARY KEY (Name, Country, Province), 
FOREIGN KEY (Country) REFERENCES Province (Country), 
FOREIGN KEY (Province) REFERENCES Province (Name) 
);

CREATE TABLE Economy 
(Country VARCHAR(4)  NOT NULL, 
 GDP INTEGER, 
 Agriculture INTEGER, 
 Service INTEGER, 
 Industry INTEGER, 
 Inflation INTEGER,  
PRIMARY KEY (Country) 
);

CREATE TABLE Population 
(Country VARCHAR(4)  NOT NULL, 
 Population_Growth INTEGER, 
 Infant_Mortality INTEGER,  
PRIMARY KEY (Country) 
);

CREATE TABLE Politics 
(Country VARCHAR(4) NOT NULL, 
 Independence VARCHAR(20), 
 Government VARCHAR(120),  
PRIMARY KEY (Country) 
);

CREATE TABLE Language 
(Country VARCHAR(4) NOT NULL, 
 Name VARCHAR(50) NOT NULL, 
 Percentage INTEGER, 
PRIMARY KEY (Name, Country), 
FOREIGN KEY (Country) REFERENCES Country (Code)
);

CREATE TABLE Religion 
(Country VARCHAR(4) NOT NULL, 
 Name VARCHAR(50) NOT NULL, 
 Percentage INTEGER , 
 PRIMARY KEY (Name, Country),  
FOREIGN KEY (Country) REFERENCES Country (Code)
);

CREATE TABLE Ethnic_Group 
(Country VARCHAR(4) NOT NULL, 
 Name VARCHAR(50) NOT NULL, 
 Percentage INTEGER, 
 PRIMARY KEY (Name, Country), 
FOREIGN KEY (Country) REFERENCES Country (Code)
);

CREATE TABLE Continent 
(Name VARCHAR(20) NOT NULL, 
 Area INTEGER, 
PRIMARY KEY (Name)
);

CREATE TABLE borders 
(Country1 VARCHAR(4) NOT NULL, 
 Country2 VARCHAR(4) NOT NULL, 
 Length INTEGER, 
PRIMARY KEY (Country1,Country2) 
);
 
CREATE TABLE encompasses 
(Country VARCHAR(4) NOT NULL, 
 Continent VARCHAR(20) NOT NULL, 
 Percentage INTEGER, 
 PRIMARY KEY (Country,Continent), 
FOREIGN KEY (Country) REFERENCES Country (Code), 
FOREIGN KEY (Continent) REFERENCES Continent (Name)
);

CREATE TABLE Organization 
(Abbreviation VARCHAR(12)  NOT NULL, 
 Name VARCHAR(80) NOT NULL, 
 City VARCHAR(35) , 
 Country VARCHAR(4) ,  
 Province VARCHAR(32) , 
 Established VARCHAR(20), 
 PRIMARY KEY (Abbreviation)
);

CREATE TABLE is_member 
(Country VARCHAR(4) NOT NULL, 
 Organization VARCHAR(12) NOT NULL, 
 Type VARCHAR(30), 
 PRIMARY KEY (Country, Organization), 
FOREIGN KEY (Country) REFERENCES Country (Code), 
FOREIGN KEY (Organization) REFERENCES Organization (Abbreviation)
);

CREATE TABLE Mountain 
(Name VARCHAR(20) NOT NULL, 
 Height INTEGER, 
 CoordinatesLatitute INTEGER, 
 CoordinatesLongitute INTEGER,  
PRIMARY KEY (Name) 
);

CREATE TABLE Desert 
(Name VARCHAR(25)  NOT NULL, 
 Area INTEGER, 
PRIMARY KEY (Name)
);

CREATE TABLE Island 
(Name VARCHAR(25)  NOT NULL, 
 Islands VARCHAR(25), 
 Area INTEGER, 
 CoordinatesLong INTEGER,  
 CoordinatesLat  INTEGER,  
PRIMARY KEY (Name)
);

CREATE TABLE Lake 
(Name VARCHAR(25) NOT NULL,  
 Area INTEGER,  
PRIMARY KEY (Name)
);


CREATE TABLE Sea 
(Name VARCHAR(25) NOT NULL, 
 Depth INTEGER,  
PRIMARY KEY (Name)
);

CREATE TABLE River 
(Name VARCHAR(20) NOT NULL, 
 River VARCHAR(20), 
 Lake VARCHAR(20), 
 Sea VARCHAR(25), 
 Length INTEGER,  
PRIMARY KEY (Name)
);

CREATE TABLE geo_Mountain 
(Mountain VARCHAR(20)  NOT NULL, 
 Country VARCHAR(4) NOT NULL , 
 Province VARCHAR(32)  NOT NULL, 
 PRIMARY KEY (Province,Country,Mountain), 
FOREIGN KEY (Mountain) REFERENCES Mountain (Name), 
FOREIGN KEY (Province) REFERENCES Province (Name),
FOREIGN KEY (Country) REFERENCES Country (Code)
);

CREATE TABLE geo_Desert 
(Desert VARCHAR(25)  NOT NULL, 
 Country VARCHAR(4)  NOT NULL, 
 Province VARCHAR(32)  NOT NULL, 
 PRIMARY KEY (Province, Country, Desert), 
FOREIGN KEY (Desert) REFERENCES Desert (Name), 
FOREIGN KEY (Province) REFERENCES Province (Name),
FOREIGN KEY (Country) REFERENCES Country (Code)
);


CREATE TABLE geo_Island 
(Island VARCHAR(25)  NOT NULL,  
 Country VARCHAR(4)  NOT NULL, 
 Province VARCHAR(32)  NOT NULL, 
 PRIMARY KEY (Province, Country, Island), 
FOREIGN KEY (Island) REFERENCES Island (Name), 
FOREIGN KEY (Province) REFERENCES Province (Name),
FOREIGN KEY (Country) REFERENCES Country (Code)
 );

CREATE TABLE geo_River 
(River VARCHAR(20)  NOT NULL,  
 Country VARCHAR(4)  NOT NULL, 
 Province VARCHAR(32)  NOT NULL, 
 PRIMARY KEY (Province ,Country, River), 
FOREIGN KEY (River) REFERENCES River (Name), 
FOREIGN KEY (Province) REFERENCES Province (Name),
FOREIGN KEY (Country) REFERENCES Country (Code)
 );

CREATE TABLE geo_Sea 
(Sea VARCHAR(25)  NOT NULL, 
 Country VARCHAR(4)   NOT NULL, 
 Province VARCHAR(32)  NOT NULL, 
 PRIMARY KEY (Province, Country, Sea),
FOREIGN KEY (Sea) REFERENCES Sea (Name), 
FOREIGN KEY (Province) REFERENCES Province (Name),
FOREIGN KEY (Country) REFERENCES Country (Code) 
);

CREATE TABLE geo_Lake 
(Lake VARCHAR(25)  NOT NULL, 
 Country VARCHAR(4)  NOT NULL, 
 Province VARCHAR(32)  NOT NULL, 
 PRIMARY KEY (Province, Country, Lake),
FOREIGN KEY (Lake) REFERENCES Lake (Name), 
FOREIGN KEY (Province) REFERENCES Province (Name),
FOREIGN KEY (Country) REFERENCES Country (Code) 
);

CREATE TABLE merges_with 
(Sea1 VARCHAR(25)  NOT NULL, 
 Sea2 VARCHAR(25)  NOT NULL, 
 PRIMARY KEY (Sea1,Sea2),
FOREIGN KEY (Sea1) REFERENCES Sea (Name),
FOREIGN KEY (Sea2) REFERENCES Sea (Name)  
);

CREATE TABLE located 
(City VARCHAR(35) , 
 Province VARCHAR(32) , 
 Country VARCHAR(4) , 
 River VARCHAR(20), 
 Lake VARCHAR(25), 
 Sea VARCHAR(25)
);