DROP DATABASE IF EXISTS Geography;
CREATE DATABASE Geography COLLATE latin1_general_cs;
USE Geography;
create table political_unit (
	unitname						 	varchar(30) not null,
	unitcode						 	char(2),
	unitpop 							decimal(6,2),
	PRIMARY KEY(unitcode));
create table boundary (
	boundid 							INTEGER,
	boundpath						 	polygon NOT NULL,
	unitcode						 	char(2),
	PRIMARY KEY(boundid),
	constraint fk_boundary_polunit FOREIGN KEY(unitcode)
		REFERENCES political_unit(unitname)) ENGINE MyISAM;
create table city (
	cityname						 	varchar(30),
	cityloc						 	point not null,
	unitcode						 	char(2),
	PRIMARY KEY(unitcode,cityname),
	constraint fk_city_polunit FOREIGN KEY(unitcode) 
		REFERENCES political_unit(unitname)) ENGINE MyISAM;	
INSERT INTO political_unit VALUES ('Republic of Ireland','ie', 3.9);
INSERT INTO political_unit VALUES ('Northern Ireland','ni', 1.7);
INSERT INTO boundary VALUES
	(1,GeomFromText('polygon((9 8, 9 3, 4 1, 2 2, 1 3, 3 5, 3 6, 2 6,
	 2 9, 5 9, 5 10, 6 11, 7 11, 7 10, 6 9, 7 8, 7 9, 8 9, 8 8, 9 8))'),'ie');
INSERT INTO boundary VALUES
	(2,GeomFromText('polygon((7 11, 9 11, 10 9, 10 8, 8 8, 8 9, 7 9,
	 7 8, 6 9, 7 10, 7 11))'),'ni');
INSERT INTO city VALUES ('Dublin',GeomFromText('POINT(9 6)'),'ie');
INSERT INTO city VALUES ('Cork',GeomFromText('POINT(5 2)'),'ie');
INSERT INTO city VALUES ('Limerick',GeomFromText('POINT(4 4)'),'ie');
INSERT INTO city VALUES ('Galway',GeomFromText('POINT(4 6)'),'ie');
INSERT INTO city VALUES ('Sligo',GeomFromText('POINT(5 8)'),'ie');
INSERT INTO city VALUES ('Tipperary',GeomFromText('POINT(5 3)'),'ie');
INSERT INTO city VALUES ('Belfast',GeomFromText('POINT(9 9)'),'ni');
INSERT INTO city VALUES ('Londonderry',GeomFromText('POINT(7 10)'),'ni');