create table state
(
  state 	char(2)      	PRIMARY KEY,
  description 	varchar(30)	NOT NULL
);

create table zipcode 
(
  zipcode 	varchar(5)	PRIMARY KEY,
  city 		varchar(30)	NOT NULL,
  state 	char(2)		NOT NULL REFERENCES state,
  longitude float4,
  latitude  float4
);

create table county
(
  county	varchar(30),
  state		char(2)		REFERENCES state,
  PRIMARY KEY(county,state)
);

create table country
(
  country       varchar(50) PRIMARY KEY
);

create group locale;
grant select, insert, update, delete on state,zipcode,county,country to group locale;
grant select on state,zipcode,county,country to public;

INSERT INTO state (state, description) VALUES('AL','Alabama');
INSERT INTO state (state, description) VALUES('AK','Alaska');
INSERT INTO state (state, description) VALUES('AZ','Arizona'); 
INSERT INTO state (state, description) VALUES('AR','Arkansas'); 
INSERT INTO state (state, description) VALUES('CA','California'); 
INSERT INTO state (state, description) VALUES('CO','Colorado'); 
INSERT INTO state (state, description) VALUES('CT','Connecticut'); 
INSERT INTO state (state, description) VALUES('DE','Delaware'); 
INSERT INTO state (state, description) VALUES('DC','District of Columbia');
INSERT INTO state (state, description) VALUES('FL','Florida');
INSERT INTO state (state, description) VALUES('GA','Georgia'); 
INSERT INTO state (state, description) VALUES('GU','Guam');
INSERT INTO state (state, description) VALUES('HI','Hawaii');
INSERT INTO state (state, description) VALUES('ID','Idaho'); 
INSERT INTO state (state, description) VALUES('IL','Illinois'); 
INSERT INTO state (state, description) VALUES('IN','Indiana'); 
INSERT INTO state (state, description) VALUES('IA','Iowa'); 
INSERT INTO state (state, description) VALUES('KS','Kansas'); 
INSERT INTO state (state, description) VALUES('KY','Kentucky'); 
INSERT INTO state (state, description) VALUES('LA','Louisiana'); 
INSERT INTO state (state, description) VALUES('ME','Maine'); 
INSERT INTO state (state, description) VALUES('MD','Maryland'); 
INSERT INTO state (state, description) VALUES('MA','Massachusetts'); 
INSERT INTO state (state, description) VALUES('MI','Michigan'); 
INSERT INTO state (state, description) VALUES('MN','Minnesota'); 
INSERT INTO state (state, description) VALUES('MS','Mississippi'); 
INSERT INTO state (state, description) VALUES('MO','Missouri'); 
INSERT INTO state (state, description) VALUES('MT','Montana');
INSERT INTO state (state, description) VALUES('NE','Nebraska'); 
INSERT INTO state (state, description) VALUES('NV','Nevada'); 
INSERT INTO state (state, description) VALUES('NH','New Hampshire'); 
INSERT INTO state (state, description) VALUES('NJ','New Jersey'); 
INSERT INTO state (state, description) VALUES('NM','New Mexico'); 
INSERT INTO state (state, description) VALUES('NY','New York'); 
INSERT INTO state (state, description) VALUES('NC','North Carolina'); 
INSERT INTO state (state, description) VALUES('ND','North Dakota'); 
INSERT INTO state (state, description) VALUES('OH','Ohio'); 
INSERT INTO state (state, description) VALUES('OK','Oklahoma'); 
INSERT INTO state (state, description) VALUES('OR','Oregon'); 
INSERT INTO state (state, description) VALUES('PA','Pennsylvania'); 
INSERT INTO state (state, description) VALUES('PR','Puerto Rico');
INSERT INTO state (state, description) VALUES('RI','Rhode Island'); 
INSERT INTO state (state, description) VALUES('SC','South Carolina'); 
INSERT INTO state (state, description) VALUES('SD','South Dakota'); 
INSERT INTO state (state, description) VALUES('TN','Tennessee'); 
INSERT INTO state (state, description) VALUES('TX','Texas');
INSERT INTO state (state, description) VALUES('UT','Utah'); 
INSERT INTO state (state, description) VALUES('VT','Vermont'); 
INSERT INTO state (state, description) VALUES('VA','Virginia'); 
INSERT INTO state (state, description) VALUES('VI','Virgin Islands');
INSERT INTO state (state, description) VALUES('WA','Washington'); 
INSERT INTO state (state, description) VALUES('WV','West Virginia'); 
INSERT INTO state (state, description) VALUES('WI','Wisconsin');
INSERT INTO state (state, description) VALUES('WY','Wyoming'); 


insert into country values ('Afghanistan');
insert into country values ('Albania');
insert into country values ('Algeria');
insert into country values ('American Samoa');
insert into country values ('Andorra');
insert into country values ('Angola');
insert into country values ('Anguilla');
insert into country values ('Antarctica');
insert into country values ('Antigua and Barbuda');
insert into country values ('Argentina');
insert into country values ('Armenia');
insert into country values ('Arctic Ocean');
insert into country values ('Aruba');
insert into country values ('Ashmore and Cartier Islands');
insert into country values ('Atlantic Ocean');
insert into country values ('Australia');
insert into country values ('Austria');
insert into country values ('Azerbaijan');
insert into country values ('Bahamas');
insert into country values ('Bahrain');
insert into country values ('Baker Island');
insert into country values ('Bangladesh');
insert into country values ('Barbados');
insert into country values ('Bassas da India');
insert into country values ('Belarus');
insert into country values ('Belgium');
insert into country values ('Belize');
insert into country values ('Benin');
insert into country values ('Bermuda');
insert into country values ('Bhutan');
insert into country values ('Bolivia');
insert into country values ('Bosnia and Herzegovina');
insert into country values ('Botswana');
insert into country values ('Bouvet Island');
insert into country values ('Brazil');
insert into country values ('British Indian Ocean Territory');
insert into country values ('British Virgin Islands');
insert into country values ('Brunei');
insert into country values ('Bulgaria');
insert into country values ('Burkina Faso');
insert into country values ('Burma');
insert into country values ('Burundi');
insert into country values ('Cambodia');
insert into country values ('Cameroon');
insert into country values ('Canada');
insert into country values ('Cape Verde');
insert into country values ('Cayman Islands');
insert into country values ('Central African Republic');
insert into country values ('Chad');
insert into country values ('Chile');
insert into country values ('China');
insert into country values ('Christmas Island');
insert into country values ('Clipperton Island');
insert into country values ('Cocos (Keeling) Islands');
insert into country values ('Colombia');
insert into country values ('Comoros');
insert into country values ('Congo');
insert into country values ('Congo, Democratic Rep.');
insert into country values ('Cook Islands');
insert into country values ('Coral Sea Islands');
insert into country values ('Costa Rica');
insert into country values ('Cote d''Ivoire');
insert into country values ('Croatia');
insert into country values ('Cuba');
insert into country values ('Cyprus');
insert into country values ('Czech Republic');
insert into country values ('Denmark');
insert into country values ('Djibouti');
insert into country values ('Dominica');
insert into country values ('Dominican Republic');
insert into country values ('Ecuador');
insert into country values ('Egypt');
insert into country values ('El Salvador');
insert into country values ('Equatorial Guinea');
insert into country values ('Eritrea');
insert into country values ('Estonia');
insert into country values ('Ethiopia');
insert into country values ('Europa Island');
insert into country values ('Falkland Islands');
insert into country values ('Faroe Islands');
insert into country values ('Fiji');
insert into country values ('Finland');
insert into country values ('France');
insert into country values ('French Guiana');
insert into country values ('French Polynesia');
insert into country values ('French Southern and Antarctic Lands');
insert into country values ('Gabon');
insert into country values ('Gambia');
insert into country values ('Gaza Strip');
insert into country values ('Georgia');
insert into country values ('Germany');
insert into country values ('Ghana');
insert into country values ('Gibraltar');
insert into country values ('Greece');
insert into country values ('Greenland');
insert into country values ('Grenada');
insert into country values ('Guadeloupe');
insert into country values ('Guam');
insert into country values ('Guatemala');
insert into country values ('Guernsey');
insert into country values ('Guinea');
insert into country values ('Guinea-Bissau');
insert into country values ('Guyana ');
insert into country values ('Haiti');
insert into country values ('Heard Island and McDonald Islands');
insert into country values ('Holy See (Vatican City)');
insert into country values ('Honduras');
insert into country values ('Hong Kong');
insert into country values ('Howland Island');
insert into country values ('Hungary ');
insert into country values ('Iceland');
insert into country values ('India');
insert into country values ('Indian Ocean');
insert into country values ('Indonesia');
insert into country values ('Iran');
insert into country values ('Iraq');
insert into country values ('Ireland');
insert into country values ('Israel');
insert into country values ('Italy ');
insert into country values ('Jamaica');
insert into country values ('Jan Mayen');
insert into country values ('Japan');
insert into country values ('Jarvis Island');
insert into country values ('Jersey');
insert into country values ('Johnston Atoll');
insert into country values ('Jordan');
insert into country values ('Juan de Nova Island');
insert into country values ('Kazakstan');
insert into country values ('Kenya');
insert into country values ('Kingman Reef');
insert into country values ('Kiribati');
insert into country values ('Korea, North');
insert into country values ('Korea, South');
insert into country values ('Kuwait');
insert into country values ('Kyrgyzstan');
insert into country values ('Laos');
insert into country values ('Latvia');
insert into country values ('Lebanon');
insert into country values ('Lesotho');
insert into country values ('Liberia');
insert into country values ('Libya');
insert into country values ('Liechtenstein');
insert into country values ('Lithuania');
insert into country values ('Luxembourg');
insert into country values ('Macau');
insert into country values ('Macedonia');
insert into country values ('Madagascar');
insert into country values ('Malawi');
insert into country values ('Malaysia');
insert into country values ('Maldives');
insert into country values ('Mali');
insert into country values ('Malta');
insert into country values ('Man, Isle of');
insert into country values ('Marshall Islands');
insert into country values ('Martinique');
insert into country values ('Mauritania');
insert into country values ('Mauritius');
insert into country values ('Mayotte');
insert into country values ('Mexico');
insert into country values ('Micronesia, Federated States of');
insert into country values ('Midway Islands');
insert into country values ('Moldova');
insert into country values ('Monaco');
insert into country values ('Mongolia');
insert into country values ('Montserrat');
insert into country values ('Morocco');
insert into country values ('Mozambique ');        
insert into country values ('Namibia');
insert into country values ('Nauru');
insert into country values ('Navassa Island');
insert into country values ('Nepal');
insert into country values ('Netherlands');
insert into country values ('Netherlands Antilles');
insert into country values ('New Caledonia');
insert into country values ('New Zealand');
insert into country values ('Nicaragua');
insert into country values ('Niger');
insert into country values ('Nigeria');
insert into country values ('Niue');
insert into country values ('Norfolk Island');
insert into country values ('Northern Mariana Islands');
insert into country values ('Norway ');
insert into country values ('Oman');
insert into country values ('Pacific Ocean');
insert into country values ('Pakistan');
insert into country values ('Palau');
insert into country values ('Palmyra Atoll');
insert into country values ('Panama');
insert into country values ('Papua New Guinea');
insert into country values ('Paracel Islands');
insert into country values ('Paraguay');
insert into country values ('Peru');
insert into country values ('Philippines');
insert into country values ('Pitcairn Islands');
insert into country values ('Poland');
insert into country values ('Portugal');
insert into country values ('Puerto Rico');
insert into country values ('Qatar');
insert into country values ('Reunion');
insert into country values ('Romania');
insert into country values ('Russia');
insert into country values ('Rwanda');
insert into country values ('Saint Helena');
insert into country values ('Saint Kitts and Nevis');
insert into country values ('Saint Lucia');
insert into country values ('Saint Pierre and Miquelon');
insert into country values ('Saint Vincent and the Grenadines');
insert into country values ('Samoa');
insert into country values ('San Marino');
insert into country values ('Sao Tome and Principe');
insert into country values ('Saudi Arabia');
insert into country values ('Senegal');
insert into country values ('Serbia and Montenegro');
insert into country values ('Seychelles');
insert into country values ('Sierra Leone');
insert into country values ('Singapore');
insert into country values ('Slovakia');
insert into country values ('Slovenia');
insert into country values ('Solomon Islands');
insert into country values ('Somalia');
insert into country values ('South Africa');
insert into country values ('South Georgia and So. Sandwich Isles');
insert into country values ('Southern Ocean');
insert into country values ('Spain');
insert into country values ('Spratly Islands');
insert into country values ('Sri Lanka');
insert into country values ('Sudan');
insert into country values ('Suriname');
insert into country values ('Svalbard');
insert into country values ('Swaziland');
insert into country values ('Sweden');
insert into country values ('Switzerland');
insert into country values ('Syria');
insert into country values ('Taiwan');
insert into country values ('Tajikistan');
insert into country values ('Tanzania');
insert into country values ('Thailand');
insert into country values ('Togo');
insert into country values ('Tokelau');
insert into country values ('Tonga');
insert into country values ('Trinidad and Tobago');
insert into country values ('Tromelin Island');
insert into country values ('Tunisia');
insert into country values ('Turkey');
insert into country values ('Turkmenistan');
insert into country values ('Turks and Caicos Islands');
insert into country values ('Tuvalu');
insert into country values ('Uganda');
insert into country values ('Ukraine');
insert into country values ('United Arab Emirates');
insert into country values ('United Kingdom');
insert into country values ('United States');
insert into country values ('Uruguay');
insert into country values ('Uzbekistan');
insert into country values ('Vanuatu');
insert into country values ('Vatican City');
insert into country values ('Venezuela');
insert into country values ('Vietnam');
insert into country values ('Virgin Islands');
insert into country values ('Wake Island');
insert into country values ('Wallis & Futuna');
insert into country values ('West Bank');
insert into country values ('Western Sahara');
insert into country values ('The World');
insert into country values ('Yemen');
insert into country values ('Zambia');
insert into country values ('Zimbabwe');