connect to Mondial

-- dropping the tables
drop table   Country 
drop table   City 
drop table   Province 
drop table   Economy 
drop table   Population 
drop table   Politics 
drop table   Language 
drop table   Religion 
drop table   Ethnic_Group 
drop table   Continent 
drop table   borders 
drop table   encompasses 
drop table   Organization 
drop table   is_member 
drop table   Mountain 
drop table   Desert 
drop table   Island
drop table   Lake 
drop table   Sea 
drop table   River 
drop table   geo_Mountain 
drop table   geo_Desert 
drop table   geo_Island 
drop table   geo_River 
drop table   geo_Sea 
drop table   geo_Lake 
drop table   merges_with 
drop table   located 

echo --------------------------------     Starting creating the DB


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), \
FOREIGN KEY (Country) REFERENCES Country \
)

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 (Province) REFERENCES Province \
)

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 \
)

CREATE TABLE Religion \
(Country VARCHAR(4) not null, \
 Name VARCHAR(50) not null, \
 Percentage INTEGER , \
 PRIMARY KEY (Name, Country), \ 
FOREIGN KEY (Country) REFERENCES Country \
)

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 \
)

CREATE TABLE Continent \
(Name VARCHAR(20) not null, \
 Area INTEGER, \ 
PRIMARY KEY (Name))
 echo -----------------------1 -------------------------------------------

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, \
FOREIGN KEY (Continent) REFERENCES Continent \
)

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, \
FOREIGN KEY (Organization) REFERENCES Organization \
)

CREATE TABLE Mountain \
(Name VARCHAR(20) not null, \
 Height INTEGER, \
 CoordinatesLatitute INTEGER, \ 
 CoordinatesLongitute INTEGER, \ 
PRIMARY KEY (Name) )
 echo ----------------------- 2 -------------------------------------------

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) )

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, \
FOREIGN KEY (Province) REFERENCES Province \
)

echo ----------------------- 3 -------------------------------------------

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, \
FOREIGN KEY (Province) REFERENCES Province \
 )

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, \
FOREIGN KEY (Province) REFERENCES Province \
 )

CREATE TABLE geo_Sea \
(Sea VARCHAR(25)  not null, \
 Country VARCHAR(4)   not null, \
 Province VARCHAR(32)  not null, \
 PRIMARY KEY (Province, Country, Sea) )

CREATE TABLE geo_Lake \
(Lake VARCHAR(25)  not null, \
 Country VARCHAR(4)  not null, \
 Province VARCHAR(32)  not null, \
 PRIMARY KEY (Province, Country, Lake) )

CREATE TABLE merges_with \
(Sea1 VARCHAR(25)  not null, \
 Sea2 VARCHAR(25)  not null, \
 PRIMARY KEY (Sea1,Sea2) )

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

connect reset
terminate