-- acs_geo_parents.sql
-- Modified for ACS by DRB 1/30/2000

-- counties
-- used to be in acs/www/install/counties.dmp (with EDF tablespace)
-- data now in counties.ctl
CREATE TABLE COUNTIES (
       FIPS_COUNTY_CODE     VARCHAR(5) NOT NULL,
       FIPS_COUNTY_NAME     VARCHAR(35) NOT NULL,
       FIPS_STATE_CODE      VARCHAR(2) NOT NULL,
       USPS_ABBREV          VARCHAR(2) NOT NULL,
       STATE_NAME           VARCHAR(50) NOT NULL,
       PRIMARY KEY (FIPS_COUNTY_CODE)
);


-- countries
-- used to be in acs/www/install/country-codes.dmp (with EDF tablespace)
-- data now in country_codes.ctl
CREATE TABLE COUNTRY_CODES (
       ISO                  CHAR(2) NOT NULL,
       COUNTRY_NAME         VARCHAR(150),
       PRIMARY KEY (ISO)
);

-- states
-- used to be in acs/www/install/states.dmp (with EDF tablespace)
-- data now in states.ctl
CREATE TABLE STATES (
       USPS_ABBREV          CHAR(2) NOT NULL,
       STATE_NAME           VARCHAR(25),
       FIPS_STATE_CODE      CHAR(2),
       PRIMARY KEY (USPS_ABBREV)
);

-- US EPA regions
-- used to be in acs/www/install/epa-regions.dmp (with EDF tablespace)
-- data now in bboard_epa_regions.ctl
CREATE TABLE BBOARD_EPA_REGIONS (
       STATE_NAME           VARCHAR(30),
       FIPS_NUMERIC_CODE    CHAR(2),
       EPA_REGION           NUMERIC,
       USPS_ABBREV          CHAR(2),
       DESCRIPTION          VARCHAR(50)
);

-- currency codes
-- only the currencies with supported_p equal to t will be shown in the currency widget
CREATE TABLE CURRENCY_CODES (
	ISO		CHAR(3) PRIMARY KEY,
	CURRENCY_NAME	VARCHAR(200),
	SUPPORTED_P	CHAR(1) DEFAULT 'f' CHECK(SUPPORTED_P in ('t','f'))
);