use master go if db_id('MSSQL_1to1') > 0 begin drop database MSSQL_1to1 end create database MSSQL_1to1 go use MSSQL_1to1 go create table person ( pers_id int identity(1,1) not null, constraint PK_pers primary key (pers_id), pers_fname varchar(50) null, pers_mname varchar(50) null, pers_lname varchar(50) null, pers_natid varchar(25) not null, --Social Security Number, etc constraint UQ_pers_natid unique (pers_natid), pers_dob datetime not null ) create table [user] ( user_pers_id int not null, constraint PK_user primary key (user_pers_id), constraint FK_user foreign key (user_pers_id) references person(pers_id), user_handle varchar(25) not null, user_password varchar(25) not null ) create table clin_study_data ( cdat_id int identity(1,1) not null, constraint PK_cdat primary key (cdat_id), cdat_name varchar(50) not null, cdat_sponsor varchar(50) not null, cdat_start_date datetime not null, cdat_online_date datetime, cdat_end_date datetime, cdat_purge_date datetime ) create table clin_study_setup ( cset_cdat_id int not null, constraint PK_cset primary key (cset_cdat_id), constraint FK_cset foreign key (cset_cdat_id) references clin_study_data(cdat_id), cset_init_db_id int, cset_init_user_id int, cset_audit_on bit, cset_remote_access bit )