SubjectTrackingDBDesign.DDL

/*    This SQL DDL script was generated by Microsoft Visual Studio (Release Date: LOCAL BUILD). */

/*    Driver Used : Microsoft Visual Studio - Microsoft SQL Server Driver.                    */
/*    Document    : \\home\pardre1\Visual Studio Projects\CHCRTracker\SubjectTrackingDBDesign.vsd. */
/*    Time Created: October 09, 2004 7:27 AM.                                                 */
/*    Operation   : From Visio Generate Wizard.                                               */
/*    Connected data source : No connection.                                                  */
/*    Connected server      : No connection.                                                  */
/*    Connected database    : Not applicable.                                                 */



/* Create CHCR database.                                                                      */
use master  

go

create database "CHCR"  

go

use "CHCR"  

go

/* Create new table "MailTo".                                                                 */
/* "MailTo" : A table for storing the RecruitIDs of people for whom we want letters.          */
/* 	"AddedBy" : AddedBy partly identifies MailTo                                              */
/* 	"RecruitID" : RecruitID identifies MailTo                                                 */
/* 	"LetterDate" : The date that should show on the letter.                                   */  
create table "MailTo" ( 
	"AddedBy" char(10) default current_user not null,
	"RecruitID" int not null,
	"LetterDate" datetime not null)  

go

alter table "MailTo"
	add constraint "MailTo_PK" primary key ("RecruitID", "AddedBy")   


go

/* Create new table "AccessCodes".                                                            */
/* "AccessCodes" : The complete list of Access Codes used to authenticate Project Quit recruits to the umich website. */
/* 	"AccLetter" : The letter portion of the Access code                                       */
/* 	"AccNumber" : The numeric portion of the Access Code                                      */
/* 	"Randy" : A random number used to assign access codes.                                    */
/* 	"RecruitID" : RecruitID is of AccessCodes                                                 */
/* 	"AssignedOn" : Date/time the code was assigned to this Recruit.                           */
/* 	"AssignedBy" : AssignedBy is of AccessCodes                                               */  
create table "AccessCodes" ( 
	"AccLetter" char(1) not null,
	"AccNumber" char(4) not null,
	"Randy" float not null,
	"RecruitID" int null,
	"AssignedOn" datetime null,
	"AssignedBy" char(10) null)  

go

alter table "AccessCodes"
	add constraint "AccessCodes_PK" primary key ("AccLetter", "AccNumber")   


go

/* Create new table "CHCRP1_20040422".                                                        */
/* "CHCRP1_20040422" : Table of CHCRP1_20040422                                               */  
create table "CHCRP1_20040422" ( 
	"CONSUMNO" char(8) null,
	"RANDY" float null,
	"BDATE" smalldatetime null,
	"SEX" char(1) null,
	"LASTVISTDATE" smalldatetime null,
	"LASTNAME" char(15) null,
	"FIRSTNAME" char(12) null,
	"MIDDLENAME" char(12) null,
	"NAMESUFFIX" char(7) null,
	"ADDRESSLINE1" char(25) null,
	"ADDRESSLINE2" char(25) null,
	"CITY" char(13) null,
	"STATE" char(2) null,
	"ZIP" char(9) null,
	"COUNTRY" char(2) null,
	"COUNTY" char(3) null,
	"ALTADDRESSLINE1" char(25) null,
	"ALTADDRESSLINE2" char(25) null,
	"ALTCITY" char(13) null,
	"ALTSTATE" char(2) null,
	"ALTZIP" char(9) null,
	"ALTCOUNTRY" char(2) null,
	"AREACODE" char(3) null,
	"PHONENUMBER" char(7) null,
	"EXTENSION" char(4) null,
	"ALTAREACODE" char(3) null,
	"ALTPHONENUMBER" char(7) null,
	"ALTEXTENSION" char(4) null,
	"LASTENROLLED" datetime null,
	"SEQNUM" float null,
	"ACCNUM" float null,
	"ACCLETTER" char(1) null)  

go

/* Create new table "ContactTypes".                                                           */
/* "ContactTypes" : A lookup table listing the permissible types of Contacts.                 */
/* 	"ContactType" : Code signifying the type of the contact                                   */
/* 	"ContactTypeDescription" : Description of the contact type                                */  
create table "ContactTypes" ( 
	"ContactType" int identity not null,
	"ContactTypeDescription" varchar(20) not null)  

go

alter table "ContactTypes"
	add constraint "ContactTypes_PK" primary key ("ContactType")   


go

/* Create new table "SamplingProgramVersions".                                                */
/* "SamplingProgramVersions" : Table of SamplingProgramVersions                               */
/* 	"RecruitProgVersion" : The version of P1RecruitLetter.sas used to generate                */
/* 	"Notes" : Notes is of SamplingProgramVersions                                             */  
create table "SamplingProgramVersions" ( 
	"RecruitProgVersion" varchar(6) not null,
	"Notes" varchar(2000) null)  

go

alter table "SamplingProgramVersions"
	add constraint "SamplingProgramVersions_PK" primary key ("RecruitProgVersion")   


go

/* Create new table "Studies".                                                                */
/* "Studies" : Table of Studies                                                               */
/* 	"Study" : Code for the study                                                              */
/* 	"StudyName" : The name of the study                                                       */  
create table "Studies" ( 
	"Study" int identity not null,
	"StudyName" varchar(20) not null)  

go

alter table "Studies"
	add constraint "Studies_PK" primary key ("Study")   


go

/* Create new table "PhoneNumberTypes".                                                       */
/* "PhoneNumberTypes" : A lookup table listing the permissible types of PhoneNumbers.         */
/* 	"Type" : The type of phone number.                                                        */  
create table "PhoneNumberTypes" ( 
	"Type" varchar(20) not null)  

go

alter table "PhoneNumberTypes"
	add constraint "PhoneNumberTypes_PK" primary key ("Type")   


go

/* Create new table "Statuses".                                                               */
/* "Statuses" : Table of Statuses                                                             */
/* 	"Status" : Status                                                                         */
/* 	"StatusDescription" : Description of this Status code                                     */  
create table "Statuses" ( 
	"Status" int identity not null,
	"StatusDescription" varchar(30) not null)  

go

alter table "Statuses"
	add constraint "Statuses_PK" primary key ("Status")   


go

/* Create new table "Recruits".                                                               */
/* "Recruits" : A list of the Recruits for the CHCR studies.                                  */
/* 	"RecruitID" : Arbitrary ID for the Recruit                                                */
/* 	"AccLetter" : Letter portion of the Access Code                                           */
/* 	"AccNumber" : Numeric portion of the Access code                                          */
/* 	"ConsumNo" : GHC Consumer Number                                                          */
/* 	"Sex" : Gender                                                                            */
/* 	"Randy" : A randomly generated number used to assign Access Code                          */
/* 	"BDate" : Date of Birth                                                                   */
/* 	"LastVistDate" : The date of the recruit's last 'smoker' visit                            */
/* 	"LastName" : Surname                                                                      */
/* 	"FirstName" : Given Name                                                                  */
/* 	"MiddleName" : Middle Name (or initial)                                                   */
/* 	"NameSuffix" : Name suffix (Jr., III, etc.)                                               */
/* 	"EMailAddress" : The recruit's e-mail address.                                            */
/* 	"SeqNum" : Sequence number used to assign Access Code                                     */
/* 	"RecruitProgVersion" : The version of P1RecruitLetter.sas used to generate                */
/* 	"Study" : Study is of Recruits                                                            */
/* 	"AddedBy" : The username of the user who added this row.                                  */
/* 	"AddedDate" : The date/time the record was added.                                         */
/* 	"ModifiedBy" : The username of the person who last modified this row.                     */
/* 	"ModifiedDate" : The date/time the record was modified.                                   */
/* 	"AccCode" : A calculated column concatenating AccNumber and AccLetter into the Access Code. */  
create table "Recruits" ( 
	"RecruitID" int identity not null,
	"AccLetter" char(1) null,
	"AccNumber" char(4) null,
	"ConsumNo" char(8) not null,
	"Sex" char(1) not null,
	"Randy" float not null,
	"BDate" datetime not null,
	"LastVistDate" datetime not null,
	"LastName" varchar(20) not null,
	"FirstName" varchar(20) not null,
	"MiddleName" varchar(20) null,
	"NameSuffix" varchar(7) null,
	"EMailAddress" varchar(50) null,
	"SeqNum" int null,
	"RecruitProgVersion" varchar(6) not null,
	"Study" int not null,
	"AddedBy" char(10) default current_user not null,
	"AddedDate" datetime default getdate() not null,
	"ModifiedBy" char(10) null,
	"ModifiedDate" datetime null,
	"AccCode" varchar(6) null)  

go

alter table "Recruits"
	add constraint "Recruits_PK" primary key ("RecruitID")   


go

/* Create new table "Addresses".                                                              */
/* "Addresses" : The addresses where Recruits may be found/contacted by mail.                 */
/* 	"AddressID" : AddressID partly identifies Addresses                                       */
/* 	"RecruitID" : RecruitID identifies Addresses                                              */
/* 	"PreferenceRank" : The higher this number is, the more we prefer sending mail to this address. */
/* 	"Line1" : First line                                                                      */
/* 	"Line2" : Second line                                                                     */
/* 	"City" : City                                                                             */
/* 	"State" : State                                                                           */
/* 	"Zip" : Zip                                                                               */
/* 	"Country" : Country                                                                       */
/* 	"County" : County is of Addresses                                                         */
/* 	"BounceDate" : Date we recieved a bounced mailing to this address.                        */
/* 	"AddedBy" : The username of the user who added this row.                                  */
/* 	"AddedDate" : The date/time the record was added.                                         */
/* 	"ModifiedBy" : The username of the person who last modified this row.                     */
/* 	"ModifiedDate" : The date/time the record was modified.                                   */  
create table "Addresses" ( 
	"AddressID" int identity not null,
	"RecruitID" int not null,
	"PreferenceRank" int not null,
	"Line1" varchar(25) not null,
	"Line2" varchar(25) null,
	"City" varchar(15) not null,
	"State" char(2) not null,
	"Zip" varchar(9) not null,
	"Country" char(2) null,
	"County" char(3) null,
	"BounceDate" datetime null,
	"AddedBy" char(10) default current_user not null,
	"AddedDate" datetime default getdate() not null,
	"ModifiedBy" char(10) null,
	"ModifiedDate" datetime null)  

go

alter table "Addresses"
	add constraint "Addresses_PK" primary key ("AddressID")   


go

/* Create new table "RecruitStatuses".                                                        */
/* "RecruitStatuses" : Table of RecruitStatuses                                               */
/* 	"RecruitID" : RecruitID partly identifies RecruitStatuses                                 */
/* 	"StatusDate" : The date this status became effective.                                     */
/* 	"Status" : Status identifies RecruitStatuses                                              */
/* 	"AddedBy" : The username of the user who added this row.                                  */
/* 	"AddedDate" : The date/time the record was added.                                         */
/* 	"ModifiedBy" : The username of the person who last modified this row.                     */
/* 	"ModifiedDate" : The date/time the record was modified.                                   */  
create table "RecruitStatuses" ( 
	"RecruitID" int not null,
	"StatusDate" datetime default getdate() not null,
	"Status" int not null,
	"AddedBy" char(10) default current_user not null,
	"AddedDate" datetime default getdate() not null,
	"ModifiedBy" char(10) null,
	"ModifiedDate" datetime null)  

go

alter table "RecruitStatuses"
	add constraint "RecruitStatuses_PK" primary key ("RecruitID", "StatusDate")   


go

/* Create new table "PhoneNumbers".                                                           */
/* "PhoneNumbers" : The phone numbers where Recruits may be reached.                          */
/* 	"RecruitID" : The ID of the recruit whose phone number this is                            */
/* 	"Type" : The type of phone number                                                         */
/* 	"AreaCode" : Area code                                                                    */
/* 	"PhoneNumber" : The phone number                                                          */
/* 	"Extension" : Extension is of PhoneNumbers                                                */
/* 	"Source" : The source of this phone number (alternate, primary, hand-entered)             */
/* 	"AddedBy" : The username of the user who added this row.                                  */
/* 	"AddedDate" : The date/time the record was added.                                         */
/* 	"ModifiedBy" : The username of the person who last modified this row.                     */
/* 	"ModifiedDate" : The date/time the record was modified.                                   */  
create table "PhoneNumbers" ( 
	"RecruitID" int not null,
	"Type" varchar(20) not null,
	"AreaCode" char(3) default '206' not null,
	"PhoneNumber" varchar(7) not null,
	"Extension" varchar(10) null,
	"Source" varchar(15) default '''Hand-entered''' not null,
	"AddedBy" char(10) default current_user not null,
	"AddedDate" datetime default getdate() not null,
	"ModifiedBy" char(10) null,
	"ModifiedDate" datetime null)  

go

alter table "PhoneNumbers"
	add constraint "PhoneNumbers_PK" primary key ("PhoneNumber", "RecruitID", "AreaCode")   


go

/* Create new table "RecruitContacts".                                                        */
/* "RecruitContacts" : A list of contacts between study staff and the recruit.                */
/* 	"RecruitID" : RecruitID partly identifies RecruitContacts                                 */
/* 	"ContactDate" : The date of the contact                                                   */
/* 	"Initiator" : Who initiated the contact?                                                  */
/* 	"Result" : The end-result of the contact                                                  */
/* 	"Notes" : Notes about the contact                                                         */
/* 	"ContactType" : ContactType partly identifies RecruitContacts                             */
/* 	"AddedBy" : The username of the user who added this row.                                  */
/* 	"AddedDate" : The date/time the record was added.                                         */
/* 	"ModifiedBy" : The username of the person who last modified this row.                     */
/* 	"ModifiedDate" : The date/time the record was modified.                                   */  
create table "RecruitContacts" ( 
	"RecruitID" int not null,
	"ContactDate" datetime not null,
	"Initiator" varchar(20) not null,
	"Result" varchar(20) null,
	"Notes" varchar(500) null,
	"ContactType" int not null,
	"AddedBy" char(10) default current_user not null,
	"AddedDate" datetime default getdate() not null,
	"ModifiedBy" char(10) null,
	"ModifiedDate" datetime null)  

go

alter table "RecruitContacts"
	add constraint "RecruitContacts_PK" primary key ("RecruitID", "ContactDate", "ContactType")   


go

/* Create new table "QueriedAccessCodeList".                                                  */
/* "QueriedAccessCodeList" : A table for storing the RecruitIDs of people for whom we want letters. */
/* 	"AddedBy" : AddedBy partly identifies QueriedAccessCodeList                               */
/* 	"AccLetter" : AccLetter partly identifies QueriedAccessCodeList                           */
/* 	"AccNumber" : AccNumber partly identifies QueriedAccessCodeList                           */
/* 	"RecruitID" : RecruitID is of QueriedAccessCodeList                                       */  
create table "QueriedAccessCodeList" ( 
	"AddedBy" char(10) default current_user not null,
	"AccLetter" char(1) not null,
	"AccNumber" char(4) not null,
	"RecruitID" int null)  

go

alter table "QueriedAccessCodeList"
	add constraint "QueriedAccessCodeList_PK" primary key ("AddedBy", "AccLetter", "AccNumber")   


go

/* Create new table "QueriedNames".                                                           */
/* "QueriedNames" : A table for storing the RecruitIDs of people for whom we want letters.    */
/* 	"AddedBy" : AddedBy partly identifies QueriedNames                                        */
/* 	"RecruitID" : RecruitID identifies QueriedNames                                           */  
create table "QueriedNames" ( 
	"AddedBy" char(10) default current_user not null,
	"RecruitID" int not null)  

go

alter table "QueriedNames"
	add constraint "QueriedNames_PK" primary key ("RecruitID", "AddedBy")   


go

/* Add the remaining keys, constraints and indexes for the table "Recruits".                  */
alter table "Recruits" add constraint "consumno_uk" unique (
	"ConsumNo")  


go

create unique index "acc_code_uk" on "Recruits" (
	"AccLetter",
	"AccNumber")  


go

create index "Study_IX" on "Recruits" (
	"Study")  


go

/* Add the remaining keys, constraints and indexes for the table "Addresses".                 */
alter table "Addresses" add constraint "Line1_uk" unique (
	"Line1",
	"City")  


go

alter table "Addresses" add constraint "Recruit_PrefRank_uk" unique (
	"RecruitID",
	"PreferenceRank")  


go

/* Add the remaining keys, constraints and indexes for the table "RecruitStatuses".           */
create index "RecruitID_IX" on "RecruitStatuses" (
	"RecruitID")  


go

create index "Status_IX" on "RecruitStatuses" (
	"Status")  


go

/* Add the remaining keys, constraints and indexes for the table "PhoneNumbers".              */
create index "RecruitID_IX" on "PhoneNumbers" (
	"RecruitID")  


go

/* Add the remaining keys, constraints and indexes for the table "RecruitContacts".           */
create index "RecruitID_IX" on "RecruitContacts" (
	"RecruitID")  


go

create index "ContactType_IX" on "RecruitContacts" (
	"ContactType")  


go

/* Add foreign key constraints to table "MailTo".                                             */
alter table "MailTo"
	add constraint "Recruits_MailTo_FK1" foreign key (
		"RecruitID")
	 references "Recruits" (
		"RecruitID") on update no action on delete no action  

go

/* Add foreign key constraints to table "AccessCodes".                                        */
alter table "AccessCodes"
	add constraint "Recruits_AccessCodes_FK1" foreign key (
		"RecruitID")
	 references "Recruits" (
		"RecruitID") on update no action on delete no action  

go

/* Add foreign key constraints to table "Recruits".                                           */
alter table "Recruits"
	add constraint "Studies_Recruits_FK1" foreign key (
		"Study")
	 references "Studies" (
		"Study") on update no action on delete no action  

go

alter table "Recruits"
	add constraint "SamplingProgramVersions_Recruits_FK1" foreign key (
		"RecruitProgVersion")
	 references "SamplingProgramVersions" (
		"RecruitProgVersion") on update no action on delete no action  

go

/* Add foreign key constraints to table "Addresses".                                          */
alter table "Addresses"
	add constraint "Recruits_Addresses_FK1" foreign key (
		"RecruitID")
	 references "Recruits" (
		"RecruitID") on update no action on delete no action  

go

/* Add foreign key constraints to table "RecruitStatuses".                                    */
alter table "RecruitStatuses"
	add constraint "Statuses_RecruitStatuses_FK1" foreign key (
		"Status")
	 references "Statuses" (
		"Status") on update no action on delete no action  

go

alter table "RecruitStatuses"
	add constraint "Recruits_RecruitStatuses_FK1" foreign key (
		"RecruitID")
	 references "Recruits" (
		"RecruitID") on update no action on delete no action  

go

/* Add foreign key constraints to table "PhoneNumbers".                                       */
alter table "PhoneNumbers"
	add constraint "PhoneNumberTypes_PhoneNumbers_FK1" foreign key (
		"Type")
	 references "PhoneNumberTypes" (
		"Type") on update no action on delete no action  

go

alter table "PhoneNumbers"
	add constraint "Recruits_PhoneNumbers_FK1" foreign key (
		"RecruitID")
	 references "Recruits" (
		"RecruitID") on update no action on delete no action  

go

/* Add foreign key constraints to table "RecruitContacts".                                    */
alter table "RecruitContacts"
	add constraint "ContactTypes_RecruitContacts_FK1" foreign key (
		"ContactType")
	 references "ContactTypes" (
		"ContactType") on update no action on delete no action  

go

alter table "RecruitContacts"
	add constraint "Recruits_RecruitContacts_FK1" foreign key (
		"RecruitID")
	 references "Recruits" (
		"RecruitID") on update no action on delete no action  

go

/* Add foreign key constraints to table "QueriedNames".                                       */
alter table "QueriedNames"
	add constraint "Recruits_QueriedNames_FK1" foreign key (
		"RecruitID")
	 references "Recruits" (
		"RecruitID") on update no action on delete no action  

go

/* Create/Recreate user defined triggers for all the newly create and changed tables.         */

/* Create table level triggers for table PhoneNumbers.                                        */
create trigger AU_PhoneNumbers_TRG
on PhoneNumbers
for update
as
begin
   update PhoneNumbers
   set ModifiedBy = current_user
   , ModifiedDate = getdate() 
   FROM PhoneNumbers as p INNER JOIN
        Inserted as i
   on   p.RecruitID = i.RecruitID and
        p.PhoneNumber = i.PhoneNumber ;
end



go

/* Create table level triggers for table Addresses.                                           */
create trigger AU_Addresses_TRG
on Addresses
for update
as
begin
   update Addresses
   set ModifiedBy = current_user
   , ModifiedDate = getdate() 
   where AddressID in (SELECT AddressID from INSERTED) ;
end



go

/* Create table level triggers for table RecruitContacts.                                     */
create trigger AU_RecruitContacts_TRG
on RecruitContacts
for update
as
begin
   update RecruitContacts
   set ModifiedBy = current_user
   , ModifiedDate = getdate() 
   FROM RecruitContacts as r INNER JOIN
        INSERTED as i
   on   r.RecruitID = i.RecruitID AND
        r.ContactDate = i.ContactDate ;
end



go

/* Create table level triggers for table RecruitStatuses.                                     */
create trigger AU_RecruitStatuses_TRG
on RecruitStatuses
for update
as
begin
   update RecruitStatuses
   set ModifiedBy = current_user
   , ModifiedDate = getdate() 
   FROM RecruitStatuses as r INNER JOIN
        INSERTED as i
   on   r.RecruitID = i.RecruitID AND
        r.StatusDate = i.StatusDate ;
end



go

/* Create table level triggers for table Recruits.                                            */
create trigger AU_RECRUITS_TRG
on Recruits
for update
as
begin
   update Recruits
   set ModifiedBy = current_user
   , ModifiedDate = getdate() 
   where RecruitID in (SELECT RecruitID from INSERTED) ;
end



go


/* This is the end of the Microsoft Visual Studio generated SQL DDL script.                   */