Mondial Database

From MCS Wiki
Jump to: navigation, search

The Mondial Database has been compiled by The University of Göttingen from several geographic data sources.

ER Diagram

An ER diagram is available in PDF format.


Organization

Information about political and economical organizations.

Field Descriptions

name
The full name of the organization
abbreviation
its abbreviation
city
the city where the headquarter is located
country
the country code where the headquarter is located
province
the province where the headquarter is located
established
date of establishment

Schema

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

City

Information about cities.

Field Descriptions

name
the name of the city
country
the country code where it belongs to
province
the name of the province where it belongs to
population
population of the city
longitude
geographic longitude
latitude
geographic latitude

Schema

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

Province

Information about administrative divisions.

Field Descriptions

name
the name of the administrative division
country
the country code where it belongs to
area
the total area of the province
population
the population of the province
capital
the name of the capital
capprov
the name of the province where the capital belongs to

note that capprov is not necessarily equal to name. E.g., the municipality of Bogota (Columbia) is a province of its own, and Bogota is the capital of the surrounding province Cudinamarca

Schema

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

isMember

Memberships in political and economical organizations.

Field Descriptions

organization
the abbreviation of the organization
country
the code of the member country
type
the type of membership

Schema

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

Country

Information about countries and similar areas of the world.

Field Descriptions

name
The country name
code
The car code
capital
The name of the capital
province
The province where the capital belongs to
area
the total area
population
the population number

Schema

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

borders

Information about neighboring countries.

Field Descriptions

country1
a country code
country2
a country code
length
length of the border between country1 and country2

Note that in this relation, for every pair of neighboring countries (A,B), only one tuple is given – thus, the relation is not symmetric.

Schema

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

Economy

Economical information about the countries.

Field Descriptions

country
the country code
GDP
gross domestic product (in million $)
agriculture
percentage of agriculture of the GDP
service
percentage of services of the GDP
industry
percentage of industry of the GDP
inflation
inflation rate (per annum)

Schema

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

Population

Information about the population of the countries.

Field Descriptions

country
the country code
population growth
population growth rate (per annum)
infant mortality
infant mortality (per thousand)

Schema

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

Politics

Political information about countries.

Field Descriptions

country
the country code
independence
date of indepenedence (if independent)
dependent
the country code where the area belongs to
government
type of government

Schema

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

Language

Information about the languages spoken in a country.

Field Descriptions

country
the country code
name
name of the language
percentage
percentage of the language in this country

Schema

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

Religion

Information about the religions in a country.

Field Descriptions

country
the country code
name
name of the religion
percentage
percentage of the language in this country

Schema

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

EthnicGroup

Information about the ethnic groups in a country.

Field Descriptions

country
the country code
name
name of the religion
percentage
percentage of the language in this country

Schema

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

encompasses

Information about to which continent a country belongs.

Field Descriptions

country
the country code
continent
the continent name

Schema

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

Continent

Information about continents.

Field Descriptions

name
name of the continent
area
total area of the continent

Schema

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

geo_lake

Geographic information about lakes

Field Descriptions

lake
the name of the lake
country
the country code where it is located
province; the province of this country

Schema

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

Lake

Information about lakes.

Field Descriptions

name
the name of the lake
area
the total area of the lake
depth
the depth of the lake
altitude
the altitude of the lake
river
the river that flows out of the lake (may be null)
type
the type of the lake, e.g., salt, caldera, ...
coordinates
its geographical coordinates as (longitude, latitude)

Schema

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

geo_river

Geographic information about rivers

Field Descriptions

river
the name of the river
country
the country code where the sea is located
province
the province of this country

Schema

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

River

information about rivers.

Field Descriptions

name
the name of the river
length
the length of the river
river
the river where it flows to
lake
the lake where it flows to
sea
the sea where it flows to
source
the coordinates of its source
sourceAltitude
the altitude of its source
mountains
the montains where its source is located
source
the coordinates of its estuary

Schema

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

geo_source

Geographic information about the source of a river

Field Descriptions

river
the name of the river
country
the country code where the sea is located
province
the province of this country

Schema

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

geo_estuary

Geographic information about estuaries

Field Descriptions

river
the river flowing into the estuary
country
the country code where the sea is located
province
the province of this country

Schema

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

geo_sea

Geographic information about seas

Field Descriptions

sea
the name of the sea
country
the country code where the sea is located
province
the province of this country

Schema

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

Sea

Information about seas.

Field Descriptions

name
the name of the sea
depth
the maximal depth of the sea

Schema

CREATE TABLE Sea
(Name VARCHAR(35),
 Depth FLOAT,
 CONSTRAINT SeaKey PRIMARY KEY(Name),
 CONSTRAINT SeaDepth CHECK (Depth >= 0));

mergesWith

information about neighboring seas. Note that in this relation, for every pair of neighboring seas (A,B), only one tuple is given – thus, the relation is not symmetric.

Field Descriptions

sea1
a sea
sea2
a sea

Schema

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

located

Information about cities located at rivers, lakes, and seas.

Field Descriptions

city
the name of the city
country
the country code where the city belongs to
province
the province where the city belongs to
river
the river where it is located at
lake
the lake where it is located at
sea
the sea where it is located at

Note that for a given city, there can be several lakes/seas/rivers where it is located at.

Schema

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

geo_island

Geographic information about islands

Field Descriptions

island
the name of the island
country
the country code where the island is located
province
the province of this country

Schema

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

islandIn

Information the waters where the islands are located in.

Field Descriptions

island
the name of the island
sea
the sea where the island is located in
lake
the sea where the island is located in
river
the sea where the island is located in

Note that an island can have coasts to several seas.

Schema

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

Island

Information about islands

Field Descriptions

name
the name of the island
islands
the group of islands where it belongs to
area
the area of the island
height
the height of the highest point on the island
type
the type of the island, e.g. volcanic, coral, atoll, ...
coordinates
its geographical coordinates as (longitude, latitude)

Schema

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

locatedOn

Information about cities located in islands.

Field Descriptions

city
the name of the city
country
the country code where the city belongs to
province
the province where the city belongs to
island
the island it is (maybe only partially) located on

Note that for a given city, there can be several islands where it is located on.

Schema

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

geo_mountain

Geographical information about mountains

Field Descriptions

mountain
the name of the mountain
country
the country code where it is located
province
the province of this country

Note that for a country there can be several provinces where the mountain is located in. analogous for geo island, geo desert, geo river, geo lake, geo sea. Analogously, there are also geo source and geo estuary.

Schema

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

mountainOnIsland

Information which mountains are located in islands.

Field Descriptions

mountain
the name of the mountain
island
the name of the island

Schema

CREATE TABLE MountainOnIsland
(Mountain VARCHAR(35),
 Island  VARCHAR(35),
 CONSTRAINT MntIslKey PRIMARY KEY (Mountain, Island) );

Mountain

information about mountains

Field Descriptions

name
the name of the mountain
mountains
the montains where the it belongs to
height
the height of the mountain
type
the type of the mountain, e.g. volcanic, (active) volcano, ...
coordinates
its geographical coordinates as (longitude, latitude)

Schema

CREATE TABLE Mountain
(Name VARCHAR(35), 
 Mountains VARCHAR(35),
 Height FLOAT,
 Type VARCHAR(10),
 Longitude FLOAT,
 Latitude FLOAT,
 CONSTRAINT MountainKey PRIMARY KEY(Name),
 CONSTRAINT CHECK ((Longitude >= -180) AND (Longitude <= 180) 
              AND  (Latitude >= -90) AND (Latitude <= 90)));

geo_desert

Geographic information about deserts

Field Descriptions

desert
the name of the desert
country
the country code where it is located
province
the province of this country

Schema

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

Desert

information about deserts.

Field Descriptions

name
the name of the desert
area
the total area of the desert
coordinates
its geographical coordinates as (longitude, latitude)

Schema

CREATE TABLE Desert
(Name VARCHAR(35),
 Area FLOAT,
 Longitude FLOAT,
 Latitude FLOAT,
 CONSTRAINT DesertKey PRIMARY KEY(Name),
 CONSTRAINT DesCoord 
   CHECK ((Longitude >= -180) AND (Longitude <= 180) 
     AND  (Latitude >= -90) AND (Latitude <= 90)));
Personal tools