DROP TABLE city;
DROP TABLE region;
DROP TABLE country;

CREATE TABLE country (iso_code CHAR(2), name VARCHAR(30));
CREATE TABLE region (name VARCHAR(20), area REAL, population INTEGER, date_of_poll DATE);


ALTER TABLE country ADD PRIMARY KEY (iso_code);


ALTER TABLE region ADD COLUMN country_iso_code VARCHAR(2);
ALTER TABLE region ADD FOREIGN KEY (country_iso_code) REFERENCES country(iso_code);
ALTER TABLE region ADD PRIMARY KEY (name, country_iso_code);


CREATE TABLE city (
name VARCHAR(20),
region_name VARCHAR(20),
country_iso_code VARCHAR(30),
area REAL, 
population INTEGER, 
date_of_poll DATE,
is_capital BOOLEAN,
foreign key (region_name, country_iso_code) REFERENCES region(name, country_iso_code),
primary key (name, region_name, country_iso_code)
);

INSERT INTO country (name, iso_code) VALUES ('Italy', 'it');
INSERT INTO region (name, country_iso_code) VALUES ('Lazio', 'it');
INSERT INTO city (name, region_name, country_iso_code, is_capital) VALUES ('Rome', 'Lazio', 'it', true);

INSERT INTO country (name, iso_code) VALUES ('France', 'fr');
INSERT INTO region (name, country_iso_code, area, population, date_of_poll) VALUES ('Ile-de-France', 'fr', 12011.9, 11226000, '2003-01-01');

INSERT INTO city (name, region_name, country_iso_code, area, population, date_of_poll, is_capital) VALUES ('Paris', 'Ile-de-France', 'fr', 86.9, 2142800, '2004-01-01', true);


UPDATE region SET population = 11291020, date_of_poll = now() WHERE name = 'Ile-de-France' and country_iso_code = 'fr';

DELETE FROM city where name ='Paris';
DELETE FROM region where name = 'Ile-de-France';

ALTER TABLE city ADD CHECK (area > 0.0);
ALTER TABLE region ADD CHECK (area > 0.0);
ALTER TABLE city ADD CHECK (population > 0.0);
ALTER TABLE region ADD CHECK (population > 0.0);

UPDATE city SET area = 1284.9, population = 2547677, date_of_poll = '2006-01-01' WHERE name = 'Rome';
UPDATE region SET area = 17208, population = 5304778, date_of_poll = '2006-01-01' WHERE name = 'Lazio';

ALTER TABLE city ALTER COLUMN date_of_poll SET NOT NULL;
ALTER TABLE region ALTER COLUMN date_of_poll SET NOT NULL;
ALTER TABLE city ALTER COLUMN area SET NOT NULL;
ALTER TABLE city ALTER COLUMN population SET NOT NULL;
ALTER TABLE region ALTER COLUMN area SET NOT NULL;
ALTER TABLE region ALTER COLUMN population SET NOT NULL;
