DROP TABLE USState;
CREATE TABLE USState (
	id			char(2) NOT NULL,
	name		char(30) NOT NULL,

	PRIMARY KEY (id)
);

DROP TABLE County;
CREATE TABLE County (
	id			bigint NOT NULL,
	usstateFK char(2) NOT NULL,
	coname	char(40),

	PRIMARY KEY (id)
);

DROP TABLE City;
CREATE TABLE City (
	id			bigint NOT NULL,
	countyFK	bigint NOT NULL,
	cityname	char(40),

	PRIMARY KEY (id),
	FOREIGN KEY (countyFK) REFERENCES County (id)
);

DROP TABLE ZipCode;
CREATE TABLE ZipCode (
	zip5			char(5) NOT NULL,

	PRIMARY KEY (zip5)
);

DROP TABLE ZipCounty;
CREATE TABLE ZipCounty (
	zip5			char(5) NOT NULL,
	countyFK		bigint NOT NULL,
	percentInCounty	decimal(10,2),

	PRIMARY KEY (zip5,countyFK),
	FOREIGN KEY (countyFK) REFERENCES County (id)
);

DROP TABLE Utility;
CREATE TABLE Utility (
	id			bigint NOT NULL,
	utiltype	varchar(100),
	uname		varchar(100),
	abbr		char(10),
	address	varchar(255),
	municity char(40) NULL,
	phone		varchar(50),
	web		varchar(100),
	email		varchar(50),
	
	PRIMARY KEY (id),
	FOREIGN KEY (municity) REFERENCES City (cityname)
);

DROP TABLE UtilityCounty;
CREATE TABLE UtilityCounty (
	utilFK	bigint NOT NULL,
	countyFK bigint NOT NULL,
	
	FOREIGN KEY (utilFK) REFERENCES Utility (id),
	FOREIGN KEY (countyFK) REFERENCES County (id)
);

DROP TABLE WBAN;
CREATE TABLE WBAN (		/* Weather Bureau Army Navy (station) */
	id	int,
	city		varchar(40),
	state		char(10),
	latitude float,
	longitude float,
	elevation int, /* in meters */
	pressure  int,				/* in millibars */
	stationtype	varchar(40),	/* e.g. Primary, Secondary */
	
	PRIMARY KEY (id)
);

DROP TABLE NRELRedbook;
CREATE TABLE NRELRedbook (
	wbanid	int,
	collectortype	char(40),
	tilt		char(40),
	stat		char(10),	/* e.g. Average, Minimum, Maximum */
	uncertainty	tinyint,	/* percentage */
	jan		decimal(4,2),
	feb		decimal(4,2),
	mar		decimal(4,2),
	apr		decimal(4,2),
	may		decimal(4,2),
	jun		decimal(4,2),
	jul		decimal(4,2),
	aug		decimal(4,2),
	sep		decimal(4,2),
	oct		decimal(4,2),
	nov		decimal(4,2),
	dcm		decimal(4,2),
	yearavg	decimal(4,2),

	PRIMARY KEY(wbanid,collectortype,tilt,stat),
	FOREIGN KEY(wbanid) REFERENCES WBAN (id)
);

DROP TABLE WBANClimate;
CREATE TABLE WBANClimate (
	wbanid	int,
	element	char(15),
	base		char(10),	/* either units or a numerical value */
	jan		decimal(10,2),
	feb		decimal(10,2),
	mar		decimal(10,2),
	apr		decimal(10,2),
	may		decimal(10,2),
	jun		decimal(10,2),
	jul		decimal(10,2),
	aug		decimal(10,2),
	sep		decimal(10,2),
	oct		decimal(10,2),
	nov		decimal(10,2),
	dcm		decimal(10,2),
	yearavg	decimal(10,2),
	
	PRIMARY KEY(wbanid,element),
	FOREIGN KEY(wbanid) REFERENCES WBAN (id)
);

DROP TABLE PGETerritoryZip;	
CREATE TABLE PGETerritoryZip (
	zip5				char(5) NOT NULL,
	territory		char(2) NOT NULL,
	percentofzip	decimal(4,2),
	opencustomers	int,
	
	PRIMARY KEY (zip5,territory),
	FOREIGN KEY (zip5) REFERENCES ZipCode(zip5)
);	

DROP TABLE SCECityTerritory;
CREATE TABLE SCECityTerritory (
	city				varchar(255) NOT NULL,
	territory		char(2) NOT NULL,
	
	PRIMARY KEY (city,territory)
);