CREATE TABLE COUNTRY (
	CODE CHAR(3) NOT NULL,
	NAME CHAR(52) NOT NULL,
	CONTINENT VARCHAR(15),
	REGION CHAR(26) NOT NULL,
	SURFACE_AREA DECIMAL(10,2) NOT NULL,
	INDEP_YEAR SMALLINT,
	POPULATION INTEGER NOT NULL,
	LIFE_EXPECTANCY DECIMAL(3,1),
	GNP DECIMAL(10,2),
	LOCALNAME CHAR(45),
	GOVERNMENT_FORM CHAR(45),
	CAPITAL INTEGER,
	CODE2 CHAR(2),
	CONSTRAINT PK_COUNTRY PRIMARY KEY (CODE)
);

CREATE TABLE CITY (
	ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
	NAME CHAR(35) NOT NULL,
	COUNTRY_CODE CHAR(3) NOT NULL,
	DISTRICT CHAR(25),
	POPULATION INTEGER NOT NULL,
	CONSTRAINT PK_CITY PRIMARY KEY (ID)
);

CREATE TABLE CONTINENT (
	CODE CHAR(2) NOT NULL,
	NAME VARCHAR(15) NOT NULL,
	HIGHEST_POINT VARCHAR(20) NOT NULL,
	HIGHEST_ELEVATION INTEGER NOT NULL,
	LOWEST_POINT VARCHAR(20) NOT NULL,
	LOWEST_ELEVATION INTEGER NOT NULL,
	SURFACE_AREA INTEGER NOT NULL,
	CONSTRAINT CSTR_CONTINENT_NAME UNIQUE (NAME),
	CONSTRAINT PK_CONTINENT PRIMARY KEY (CODE)
);

CREATE TABLE COUNTRY_LANGUAGE (
	COUNTRY_CODE CHAR(3) NOT NULL,
	LANGUAGE_NAME CHAR(30) NOT NULL,
	IS_OFFICIAL CHAR(1) NOT NULL,
	PERCENTAGE DECIMAL(4,1) NOT NULL,
	CONSTRAINT PK_COUNTRYLANGUAGE PRIMARY KEY (COUNTRY_CODE,LANGUAGE_NAME)
);

CREATE TABLE ORGANIZATION (
	CODE VARCHAR(7) NOT NULL,
	NAME VARCHAR(50) NOT NULL,
	YEAR_FOUNDED INTEGER,
	CONSTRAINT PK_ORGANIZATION PRIMARY KEY (CODE)
);

CREATE TABLE COUNTRY_ORGANIZATION (
	COUNTRY_CODE CHAR(3) NOT NULL,
	ORGANIZATION_CODE VARCHAR(7) NOT NULL,
	CONSTRAINT PK_COUNTRY_ORGANIZATION PRIMARY KEY (COUNTRY_CODE,ORGANIZATION_CODE)
);