Microsoft Access Instructions ----------------------------- The Microsoft Access file is compressed using the ZIP format. It is called "Historical Congressional Statistics.zip" When uncompressed, the file will occupy 883 megabytes. Oracle Instructions ------------------- The Oracle file is called "CONG010408.dmp." Installation of the Congressional History database will, in most cases, be done by an experienced Oracle database administrator (DBA). The procedure is to use the Oracle import utility program. Information the DBA will need to run the utility follows: Estimated tablespace sizes needed for import: TEMP 500 Megabytes DATA 800 Megabytes ROLLBACK 500 Megabytes Table information: Table Rows Size COMMITTEE 116962 10,739,712 ELECTION 94640 14,508,032 ISSUE 77928 2,383,872 MEMBER 11364 2,793,472 PARTY 40924 52,428,800 RCTEXT 281576 52,428,800 REQUEST 12500 10,252,288 ROLLCALL 77927 7,544,832 ROLLREQ 6854 253,952 SERVES 42304 5,251,072 VOTES 16021696 419,430,400 Sample import runstring for HPUX to load ALL tables: imp user/password fromuser=fac_rgb touser=newuser ignore=y commit=y log=import.log file=/cdrom/cong01~1.dmp where "user/password" refers to the user running the utility (usually "system") "newuser" is the user for whom the tables are being imported "/cdrom/cong01~1.dmp" refers to the database path and file name on the CDROM Sample import runstring for HPUX to load one or more tables: imp user/password fromuser=fac_rgb touser=newuser ignore=y commit=y log=import.log tables=tablename,tablename file=/cdrom/cong01~1.dmp where all parameters are as above, and "tablename" refers to the name(s) of the tables to be imported Notes: Under Unix, the distribution CD-ROM must be mounted using PFS mount, not the standard Unix mount. It takes a long time to import the data. Please be patient. It may take several hours to import all the tables. Installation will require probably 1.5 to 2 gigabtyes of disk space. After installation, the size of the database can be reduced. The SQL used to create the tables follows: CREATE TABLE COMMITTEE (CONGRESS NUMBER NOT NULL, CONGRESS_SESSION NUMBER NOT NULL, MEMBER_ID NUMBER, COMMITTEE NUMBER NOT NULL, CHAMBER CHAR(1) NOT NULL, PARTY NUMBER, TYPE NUMBER, NAME CHAR(25) NOT NULL, PARTY_STATUS NUMBER, RANK NUMBER, MONTH_APPOINT NUMBER NOT NULL, DAY_APPOINT NUMBER NOT NULL, YEAR_APPOINT NUMBER NOT NULL, MONTH_TERM NUMBER, DAY_TERM NUMBER, YEAR_TERM NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 10M NEXT 1M MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) ; REM ... 116962 rows CREATE UNIQUE INDEX ON COMMITTEE (CONGRESS , NAME , COMMITTEE , CHAMBER , CONGRESS_SESSION , MONTH_APPOINT , DAY_APPOINT , YEAR_APPOINT ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 8M NEXT 1M MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1) ; CREATE TABLE ELECTION (CAND_ID CHAR(11) NOT NULL, STATE NUMBER(2, 0), YEAR NUMBER(4, 0), OFFICE NUMBER(1, 0), DISTRICT NUMBER(3, 0), ELECTYPE CHAR(1) NOT NULL, VOTE NUMBER(8, 0), PARTY NUMBER(4, 0) NOT NULL, CONGRESS NUMBER(3, 0), TOTAL NUMBER(8, 0), CANDS NUMBER(2, 0), PERCENT1 NUMBER(5, 0), PERCENT2 NUMBER(5, 0), MARGIN NUMBER(5, 0), CANDPCT NUMBER(5, 0), PCTDIFF NUMBER(5, 0), OUTCOME NUMBER(1, 0), INCUMB NUMBER(1, 0), NAME CHAR(64)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 14M NEXT 1M MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) ; REM... 94640 rows CREATE TABLE ISSUE (CONGRESS NUMBER(3, 0), ROLLCALL NUMBER(5, 0), CHAMBER CHAR(1), CLAUSEN NUMBER(2, 0), PELTZ1 NUMBER(2, 0), PELTZ2 NUMBER(2, 0), ISSUE1 NUMBER(2, 0), ISSUE2 NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 2M NEXT 65536 MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) ; REM ... 77928 rows CREATE TABLE MEMBER (MEMBER_ID NUMBER NOT NULL, FULL_NAME CHAR(25), SEX NUMBER, ICPSR_STATE_BORN NUMBER, BORN_AND_REP_SAME_STATE NUMBER, BORN_AND_REP_SAME_REGION NUMBER, ICPSR_RELATIVES_CODE NUMBER, COLLAPSED_RELATIVES NUMBER, SECONDARY_EDUCATION NUMBER, COLLEGE_ATTENDED NUMBER, COLLAPSED_COLLEGE NUMBER, MILITARY_SERVICE NUMBER, COLLAPSED_MILITARY NUMBER, LAST_OCCUPATION NUMBER, COLLAPSED_LAST_OCCUPATION NUMBER, LAST_NONJ_MUN_OFFICE NUMBER, COLLAPSED_LAST_NONJ_MUN_OFF NUMBER, ANY_MUNICIPAL_OFFICE_PRE NUMBER, LAST_NONJ_CNTY_OFFICE NUMBER, COLLAPSED_LAST_NONJ_CNTY_OFF NUMBER, ANY_COUNTY_OFFICE_PRE NUMBER, ANY_LOCAL_OFFICE_PRE NUMBER, LAST_NONJ_STATE_OFFICE NUMBER, COLLAPSED_LAST_NONJ_STATE_OFF NUMBER, ANY_STATE_OFFICE_PRE NUMBER, LAST_NONJ_FEDERAL_OFFICE NUMBER, COLLAPSED_LAST_NONJ_FED_OFF NUMBER, ANY_FEDERAL_OFFICE_PRE NUMBER, LAST_JUDICIAL_OFFICE NUMBER, COLLAPSED_LAST_JUDICIAL_OFF NUMBER, PRIOR_5LEVELS_OF_GOVERNMENT NUMBER, PRIOR_3LEVELS_OF_GOVERNMENT NUMBER, SEQUENCE_OF_CONG_SERVICE NUMBER, AGE_FIRST_ENTERED_CONGRESS NUMBER, FIRST_CONGRESS_ELECTED NUMBER, YEAR_FIRST_ELECTED_CONGRESS NUMBER, YEAR_LAST_SERVED_CONGRESS NUMBER, TOTAL_YEARS_SERVED_CONGRESS NUMBER, WHY_LEFT_CONGRESS NUMBER, FIRST_HOUSE_SERVED NUMBER, YEAR_FIRST_ELECTED_HOUSE NUMBER, YEAR_LAST_SERVED_HOUSE NUMBER, TOTAL_YEARS_SERVED_HOUSE NUMBER, WHY_LEFT_HOUSE NUMBER, TOTAL_YRS_HOUSE_PRE_SENATE NUMBER, FIRST_SENATE_SERVED NUMBER, YEAR_FIRST_ELECTED_SENATE NUMBER, YEAR_LAST_SERVED_SENATE NUMBER, TOTAL_YEARS_SERVED_SENATE NUMBER, WHY_LEFT_SENATE NUMBER, FIRST_OCCUPATION_POST NUMBER, FIRST_NONJ_MUN_OFF_POST NUMBER, COLLAPSED_FIRST_NONJ_MUN_OFF NUMBER, ANY_MUN_OFFICE_POST NUMBER, FIRST_NONJ_CNTY_OFF_POST NUMBER, COLLAPSED_FIRST_NONJ_CNTY_OFF NUMBER, ANY_COUNTY_OFFICE_POST NUMBER, ANY_LOCAL_OFFICE_POST NUMBER, FIRST_NONJ_STATE_OFF_POST NUMBER, COLLAPSED_FIRST_NONJ_STATE_OFF NUMBER, ANY_STATE_OFFICE_POST NUMBER, FIRST_NONJ_FED_OFFICE_POST NUMBER, COLLAPSED_FIRST_NONJ_FED_OFF NUMBER, ANY_FEDERAL_OFFICE_POST NUMBER, FIRST_JUDICIAL_OFFICE_POST NUMBER, COLLAPSED_FIRST_JUDICIAL_OFF NUMBER, POST_5LEVELS_OF_GOVERNMENT NUMBER, POST_3LEVELS_OF_GOVERNMENT NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 2M NEXT 65536 MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) ; REM ... 11364 rows CREATE UNIQUE INDEX ON MEMBER (MEMBER_ID ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 253952 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1) ; CREATE TABLE FAC_RGB.PARTY (STALPHA CHAR(2), STATE NUMBER, MCNAME CHAR(13), BIOGDIR CHAR(18), GUIDE CHAR(16), CONGRESS NUMBER, DIST NUMBER NOT NULL, PARTY NUMBER, BELL CHAR(5), DAUER CHAR(17), HOADLEY CHAR(10), HENDERSN CHAR(8), FIRSTFED CHAR(7), CUNNINGH CHAR(6), LONGPTY CHAR(5), NILES CHAR(13), HICKEY CHAR(1), NIELSEN CHAR(35), GLOBE CHAR(8), LYNN CHAR(7), LINDEEN CHAR(4), HATZENBU CHAR(9), MCLAUGHL CHAR(5), BOWLING CHAR(33), LAMPI CHAR(6), PRESVOTE CHAR(4), KYGAZETT CHAR(5), MATTHIAS CHAR(4), GUIDEOPP CHAR(12), CONGDIR CHAR(14), WORLD CHAR(8), TRIBUNE CHAR(20), SPEAKER CHAR(20), BIOD1911 CHAR(7), ICPSR2 CHAR(15), CONGLOBE CHAR(5), INTELLIG CHAR(4), MCPHERS CHAR(5), BLAINE CHAR(2), BENEDICT CHAR(27), CURRY CHAR(4), LEXGAZZ CHAR(2), RICHWHIG CHAR(2), SMITH CHAR(3), MEMBER_ID NUMBER, GUIDECLS VARCHAR2(8), GIUNTA VARCHAR2(18), RYAN VARCHAR2(4), SWANSTRM VARCHAR2(2), PHNXGAZ VARCHAR2(8), SILBEY VARCHAR2(8), BOGUE VARCHAR2(10), BIOD1913 VARCHAR2(13), STEGMAIR VARCHAR2(10)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 50M NEXT 5M MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) ; ... 40942 rows CREATE UNIQUE INDEX ON PARTY (CONGRESS , MEMBER_ID , DIST ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 50M NEXT 5M MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1) ; CREATE TABLE RCTEXT (ROLLCALL NUMBER NOT NULL, LINENUM NUMBER NOT NULL, CHAMBER VARCHAR2(1), CONGRESS NUMBER NOT NULL, CONGRESS_SESSION NUMBER NOT NULL, SEQNUM NUMBER, ROLL_CALL_TEXT VARCHAR2(70)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 50M NEXT 5M MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) ; REM ... 281567 rows CREATE UNIQUE INDEX ON RCTEXT (CONGRESS , ROLLCALL , LINENUM , CHAMBER ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 50M NEXT 5M MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1) ; CREATE TABLE REQUEST (REQUEST_NUMBER VARCHAR2(7), PRESIDENT VARCHAR2(4), DATE VARCHAR2(8), DESCRIPTION VARCHAR2(255), REPEAT_REQUEST VARCHAR2(255), MESSAGE VARCHAR2(80), CITATION VARCHAR2(80)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 10M NEXT 1M MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) ; REM ... 12500 rows CREATE TABLE ROLLCALL (ROLLCALL NUMBER NOT NULL, CITATION VARCHAR2(29), BILLNO VARCHAR2(10), ROLL_CALL_DATE VARCHAR2(18), CHAMBER VARCHAR2(1), CONGRESS NUMBER NOT NULL, CONGRESS_SESSION NUMBER, SEQNUM NUMBER, YEAVOTES NUMBER, NAYVOTES NUMBER, SPNAME VARCHAR2(20), ICPNUM NUMBER(4, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 7M NEXT 65536 MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) ; REM ... 77927 rows CREATE UNIQUE INDEX ON ROLLCALL (CONGRESS , ROLLCALL , CHAMBER ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 2M NEXT 65536 MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1) ; CREATE TABLE FAC_RGB.ROLLREQ (CHAMBER CHAR(1) NOT NULL, CONGRESS NUMBER NOT NULL, PRES_POS CHAR(1), SUPPORTED CHAR(1), VOTETYPE NUMBER, REQUEST_NUMBER NUMBER NOT NULL, ROLLCALL NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 253952 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) ; REM ... 6854 rows CREATE TABLE SERVES (MEMBER_ID NUMBER NOT NULL, CONGRESS NUMBER NOT NULL, CHAMBER_CLASS NUMBER, ICPSR_STATE NUMBER, DISTRICT NUMBER, ICPSR_PARTY NUMBER, MCKIBBIN_PARTY NUMBER, REGION NUMBER, ATTAIN_OFFICE NUMBER, OCCUPANCY NUMBER, TERMINATION_OF_SERVICE NUMBER, FIRST_HOUSE_2ND_CNTG_SVC NUMBER, FIRST_SENATE_2ND_CNTG_SVC NUMBER, TERMS_SEATED_IN_HOUSE NUMBER, TERMS_SEATED_IN_SENATE NUMBER, WHY_LEFT_THIS_CONGRESS NUMBER, MONTH_ENTERED NUMBER, DAY_ENTERED NUMBER, YEAR_ENTERED NUMBER, MONTH_LEFT NUMBER, DAY_LEFT NUMBER, YEAR_LEFT NUMBER, STATE CHAR(2), PARTY CHAR(4), TENURE_THIS_CONGRESS NUMBER, BEG_CUM_HOUSE_SERVICE NUMBER, BEG_CUM_SENATE_SERVICE NUMBER, BEG_CUM_CONGRESS_SERVICE NUMBER, END_CUM_HOUSE_SERVICE NUMBER, END_CUM_SENATE_SERVICE NUMBER, END_CUM_CONGRESS_SERVICE NUMBER, NUMBER_OF_TERMS_IN_HOUSE NUMBER, NUMBER_OF_TERMS_IN_SENATE NUMBER, AGE_BEGINNING_THIS_CONGRESS NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 5M NEXT 65536 MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) ; REM ... 42304 rows CREATE UNIQUE INDEX ON SERVES (MEMBER_ID , CONGRESS , CHAMBER_CLASS , OCCUPANCY ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 1155072 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1) ; CREATE TABLE VOTES (CONGRESS NUMBER NOT NULL, CHAMBER_CLASS NUMBER NOT NULL, MEMBER_ID NUMBER NOT NULL, ROLLCALL NUMBER NOT NULL, VOTE NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 400M NEXT 50M MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) ; REM ... 16021696 rows