prompt This file creates the table and sequence			../
prompt definitions for the AC survey number 1  			../
--
-- AC_S1 :  The primary key field is IDNUM, an integer assigned by Oracle 
--	using the sequence IDNUM_SEQ 
--      EDATE is the initial record entry date
--  	MDATE is the last modification date MDATE of this record.
-- 	EMAIL is an email address that may be entered by the user
-- 	Fields R1PHY through SPCOM have values restricted between
-- 		integers 1 through 5
-- 	Fields Q001 through Q024 are text fields of maximum length
-- 		2000 characters answering questions 1-24 in the survey
--
-- AC_S1_ID : is a table to store information to make it possible
--	to revise a survey at a later date (a code entered by the user)
--      and keeps a history of modification dates.
--
-- IDNUMC_SEQ is a sequence in both tables is used to 
--	1. prevent duplicate submission of the same survey 
--		into new id's (IDNUM)
--	2. keep a count of failed attempts to input a survey 
-- 	3. keep a count of number of modifications.
--
-- only run this sql script to reinitialize the tables,sequences.

-- all records will be deleted if these drop statements execute:
-- drop table ac_s1;
-- drop sequence idnum_seq;
-- drop table ac_s1_id;
-- drop sequence idnumc_seq;
--
-- Create the main table AC_S1:
create table ac_s1 (
	idnum	number(10,0)	not null,
	idnumc	number(10,0)	not null,
	edate   date        ,
	mdate   date        ,
	email   varchar(30) ,
	r1phy	number(1,0) ,
	r1npi	number(1,0) ,
	pubpp	number(1,0) ,
	plr2p	number(1,0) ,
	mgupg	number(1,0) ,
	upgdt	number(1,0) ,
	upgsf	number(1,0) ,
	olpln	number(1,0) ,
	r2com	number(1,0) ,
	d0rsc	number(1,0) ,
	d0rpb	number(1,0) ,
	cardv	number(1,0) ,
	disyp	number(1,0) ,
	webin	number(1,0) ,
	srvwk	number(1,0) ,
	coleg	number(1,0) ,
	iinit	number(1,0) ,
	spcom	number(1,0) ,
	dad0 	number(3,0) ,
	q001 varchar(2000) ,
	q002 varchar(2000) ,
	q003 varchar(2000) ,
	q004 varchar(2000) ,
	q005 varchar(2000) ,
	q006 varchar(2000) ,
	q007 varchar(2000) ,
	q008 varchar(2000) ,
	q009 varchar(2000) ,
	q010 varchar(2000) ,
	q011 varchar(2000) ,
	q012 varchar(2000) ,
	q013 varchar(2000) ,
	q014 varchar(2000) ,
	q015 varchar(2000) ,
	q016 varchar(2000) ,
	q017 varchar(2000) ,
	q018 varchar(2000) ,
	q019 varchar(2000) ,
	q020 varchar(2000) ,
	q021 varchar(2000) ,
	q022 varchar(2000) ,
	q023 varchar(2000) ,
	q024 varchar(2000) 
);
ALTER TABLE ac_s1  ADD (
	PRIMARY KEY (IDNUM)
);
CREATE SEQUENCE idnum_seq INCREMENT BY 1;

CREATE TABLE ac_s1_id (
	idnumc	number(10,0)	not null,
        idnum   number(10,0),
	psscode varchar(30),
	edate   date        
);
ALTER TABLE ac_s1_id  ADD (
	PRIMARY KEY (IDNUMC)
);
CREATE SEQUENCE idnumc_seq INCREMENT BY 1;


