->> please note that * = PK for that tabe

TStudies

intStudyID* (1,2)

,strStudyDesc

TSites

intSiteID * (1,2,3,4, etc.)

,intSiteNumber (101, 102, 103, etc.)

,intStudyID

,strName

,strAddress

,strCity

,strState

,strZip

,strPhone

TPatients

intPatientID * (1,2,3,4, etc.)

,intPatientNumber (101001, 102001, etc)

,intSiteID

,dtmDOB

,intGenderID

,intWeight

,intRandomCodeID allow Nulls

TVisitTypes

intVisitTypeID * (1,2,3)

,strVisitDesc (Screening, Randomization, Withdrawal)

TVisits

intVisitID * (1,2,3,etc.)

,intPatientID

,dtmVisit

,intVisitTypeID

,intWithdrawReasonID allow Nulls

TRandomCodes

intRandomCodeID * (1,2,3,4, etc.)

,intRandomCode (1000, 1001, 1002, etc.)

,intStudyID

,strTreatment (A-active or P-placebo)

,blnAvailable (T or F) use a varchar data type

TDrugKits

intDrugKitID * (1,2,3,4, etc.)

intDrugKitNumber (10000, 10001, 10002, etc.)

,intSiteID

,strTreatment (A-active or P-placebo)

,intVisitID (if a Visit ID entered it is already assigned and therefore not available) allow Nulls

TWithdrawReasons

intWithdrawReasonID * (1,2,3,etc.)

,strWithdrawDesc

TGenders

intGenderID* (1,2)

strGender

using the tables above and the inserts below ..

1. Create the view that will show all patients at all sites for both studies. You can do this together or 1 view for each study. 2. Create the view that will show all randomized patients, their site and their treatment for both studies. You can do this together or 1 view for each study. 3. Create the view that will show the next available random codes (MIN) for both studies. You can do this together or 1 view for each study. 4. Create the view that will show all available drug at all sites for both studies. You can do this together or 1 view for each study. 5. Create the view that will show all withdrawn patients, their site, withdrawal date and withdrawal reason for both studies. 6. Create other views as needed. Put as much as possible into Views so you are pulling from them instead of from tables. 7. Create the stored procedure(s) that will screen a patient for both studies. You can do this together or 1 for each study. 8. Create the stored procedure(s) that will randomize a patient for both studies. You can do this together or 1 for each study. ----. This will include a stored procedure for obtaining a random code as well as a drug kit. 9. Create the stored procedure(s) that will withdraw a patient for both studies. You can do this together or 1 for each study. Remember a patient can go from Screening Visit to Withdrawal without being randomized. This will be up to the Doctor. Your code just has to be able to do it. 10. The last item on the list is the calls to the stored procedures. You need to provide these on a SEPARATE .sql file called CallsToStoredProcs.sql. In this script, you will have the following calls to your stored procs. a) 8 patients for each study for screening. b) 5 patients randomized for each study. (including assigning drug kit) c) 4 patients (2 randomized and 2 not randomized patients) withdrawn from each study. d) These calls are like what you did in the stored procedures assignment. DECLARE @intPatientID AS INTEGER = 0; EXECUTE uspAddPatient @intPatientID OUTPUT, 111001, 2, '1/1/1962', 2, 205

------------------------------------------------------------------------------------------------------------------

INSERT INTO TStudies

(intStudyID, strStudyDesc)

VALUES

( 12345, 'Study 1')

,( 54321, 'Study 2');

INSERT INTO TSites ( intSiteID, intSiteNumber, intStudyID, strName, strAddress, strCity, strState, strZip, strPhone )

VALUES

(1, 101, 12345, 'Dr. Stan Heinrich', '123 E. Main St', 'Atlanta', 'GA', '25869', '1234567890')

,(2, 111, 12345, 'Mercy Hospital', '3456 Elmhurst Rd.', 'Secaucus', 'NJ', '32659', '5013629564')

,(3, 121, 12345, 'St. Elizabeth Hospital', '976 Jackson Way', 'Ft. Thomas', 'KY', '41258', '3026521478')

,(4, 501, 54321, 'Dr. Robert Adler', '9087 W. Maple Ave', 'Cedar Rapids', 'IA', '42365,', '6149652574')

,(5, 511, 54321, 'Dr. Tim Schmitz', '4539 Helena Run', 'Johnson City', 'TN', '34785', '5066987462')

,(6, 521, 54321, 'Dr. Lawrence Snell', '9201 NW. Washington Blvd', 'Bristol', 'VA', '20163,', '3876510249')

insert into TVisitTypes (intVisitTypeID, strVisitDesc)

values

(1, 'Screening')

,(2, 'Randomization')

,(3, 'Withdrawal')

insert into TRandomCodes (intRandomCodeID, intRandomCodeID, intStudyID, strTreatment, blnAvailable)

values

( 1 , 1000 , 12345 , 'A' , 'T' )

,( 2 , 1001 , 12345 , 'P' , 'T' )

,( 3 , 1002 , 12345 , 'A' , 'T' )

,( 4 , 1003 , 12345 , 'P' , 'T' )

,( 5 , 1004 , 12345 , 'P' , 'T' )

,( 6 , 1005 , 12345 , 'A' , 'T' )

,( 7 , 1006 , 12345 , 'A' , 'T' )

,( 8 , 1007 , 12345 , 'P' , 'T' )

,( 9 , 1008 , 12345 , 'A' , 'T' )

,( 10 , 1009 , 12345 , 'P' , 'T' )

,( 11 , 1010 , 12345 , 'P' , 'T' )

,( 12 , 1011 , 12345 , 'A' , 'T' )

,( 13 , 1012 , 12345 , 'P' , 'T' )

, ( 14 , 1013 , 12345 , 'A' , 'T' )

,( 15 , 1014 , 12345 , 'A' , 'T' )

,( 16 , 1015 , 12345 , 'A' , 'T' )

,( 17 , 1016 , 12345 , 'P' , 'T' )

,( 18 , 1017 , 12345 , 'P' , 'T' )

,( 19 , 1018 , 12345 , 'A' , 'T' )

,( 20 , 1019 , 12345 , 'P' , 'T' )

,( 21 , 5000 , 54321 , 'A' , 'T' )

,( 23 , 5001 , 54321 , 'A' , 'T' )

,( 24 , 5002 , 54321 , 'A' , 'T' )

,( 25 , 5003 , 54321 , 'A' , 'T' )

,( 26 , 5004 , 54321 , 'A' , 'T' )

,( 27 , 5005 , 54321 , 'A' , 'T' )

,( 28 , 5006 , 54321 , 'A' , 'T' )

,( 29 , 5007 , 54321 , 'A' , 'T' )

,( 30 , 5008 , 54321 , 'A' , 'T' )

,( 31 , 5009 , 54321 , 'A' , 'T' )

,( 32 , 5010 , 54321 , 'P' , 'T' )

,( 33 , 5011 , 54321 , 'P' , 'T' )

,( 34 , 5012 , 54321 , 'P' , 'T' )

,( 35 , 5013 , 54321 , 'P' , 'T' )

,( 36 , 5014 , 54321 , 'P' , 'T' )

,( 37 , 5015 , 54321 , 'P' , 'T' )

,( 38 , 5016 , 54321 , 'P' , 'T' )

,( 39 , 5017 , 54321 , 'P' , 'T' )

,( 40 , 5018 , 54321 , 'P' , 'T' )

,( 41 , 5019 , 54321 , 'P' , 'T' )

insert into TDrugKits (intDrugKitID, intDrugKitID, intSiteID, strTreatment, intVisitID)

values

( 1 , 10000 , 101 , 'A' )

, ( 2 , 10001 , 101 , 'A' )

, ( 3 , 10002 , 101 , 'A' )

, ( 4 , 10003 , 101 , 'P' )

, ( 5 , 10004 , 101 , 'P' )

, ( 6 , 10005 , 101 , 'P' )

, ( 7 , 10006 , 111 , 'A' )

, ( 8 , 10007 , 111 , 'A' )

, ( 9 , 10008 , 111 , 'A' )

, ( 10 , 10009 , 111 , 'P' )

, ( 11 , 10010 , 111 , 'P' )

, ( 12 , 10011 , 111 , 'P' )

, ( 13 , 10012 , 121 , 'A' )

, ( 14 , 10013 , 121 , 'A' )

, ( 15 , 10014 , 121 , 'A' )

, ( 16 , 10015 , 121 , 'P' )

, ( 17 , 10016 , 121 , 'P' )

, ( 18 , 10017 , 121 , 'P' )

, ( 19 , 10018 , 501 , 'A' )

, ( 20 , 10019 , 501 , 'A' )

, ( 21 , 10020 , 501 , 'A' )

, ( 23 , 10021 , 501 , 'P' )

, ( 24 , 10022 , 501 , 'P' )

, ( 25 , 10023 , 501 , 'P' )

, ( 26 , 10024 , 511 , 'A' )

, ( 27 , 10025 , 511 , 'A' )

, ( 28 , 10026 , 511 , 'A' )

, ( 29 , 10027 , 511 , 'P' )

, ( 30 , 10028 , 511 , 'P' )

, ( 31 , 10029 , 511 , 'P' )

, ( 32 , 10030 , 521 , 'A' )

, ( 33 , 10031 , 521 , 'A' )

, ( 34 , 10032 , 521 , 'A' )

, ( 35 , 10033 , 521 , 'P' )

, ( 36 , 10034 , 521 , 'P' )

, ( 37 , 10035 , 521 , 'P' )

insert into TWithdrawReasons (intWithdrawReasonID, strWithdrawDesc)

values

( 1 , 'Patient withdrew consent' )

, ( 2 , 'Adverse event' )

, ( 3 , 'Health issue-related to study' )

, ( 4 , 'Health issue-unrelated to study' )

, ( 5 , 'Personal reason' )

, ( 6 , 'Completed the study' )

insert into TGenders (intGenderID, strGender)

values

( 1 , 'Female' )

, ( 2 , 'Male' )