->> please note that * = PK for that tabe TStudies intStudyID* (1,2) ,strStudyDe

ID: 3919432 • Letter: #

Question

->> 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' )

Explanation / Answer

I have created the tables using the description that given in the question and also added the relationship based on the PK and FK

Below is the alter script for adding foreign key to the tables and i have added them in the format as you suggested

S.No

Child

Parent

Column(s)

TDrugKits

TSites

intSiteID

TDrugKits

TVisits

intVisitID

TPatients

TGenders

intGenderID

TPatients

TRandomCodes

intRandomCodeID

TPatients

TSites

intSiteID

TRandomCodes

TStudies

intStudyID

TSites

TStudies

intStudyID

TVisits

TPatients

intPatientID

TVisits

TVisitTypes

intVisitTypeID

TVisits

TWithdrawReasons

intWithdrawReasonID

1--

ALTER TABLE TDrugKits WITH CHECK ADD CONSTRAINT FK_TDrugKits_TSites FOREIGN KEY(intSiteID)

REFERENCES TSites (intSiteID);

2--

ALTER TABLE TDrugKits WITH CHECK ADD CONSTRAINT FK_TDrugKits_TVisits FOREIGN KEY(intVisitID)

REFERENCES TVisits (intVisitID);

3--

ALTER TABLE TPatients WITH CHECK ADD CONSTRAINT FK_TPatients_TGenders FOREIGN KEY(intGenderID)

REFERENCES TGenders (intGenderID);

4--

ALTER TABLE TPatients WITH CHECK ADD CONSTRAINT FK_TPatients_TRandomCodes FOREIGN KEY(intRandomCodeID)

REFERENCES TRandomCodes (intRandomCodeID);

5--

ALTER TABLE TPatients WITH CHECK ADD CONSTRAINT FK_TPatients_TSites FOREIGN KEY(intSiteID)

REFERENCES TSites (intSiteID);

6--

ALTER TABLE TRandomCodes WITH CHECK ADD CONSTRAINT FK_TRandomCodes_TStudies FOREIGN KEY(intStudyID)

REFERENCES TStudies (intStudyID);

7--

ALTER TABLE TSites WITH CHECK ADD CONSTRAINT FK_TSites_TStudies FOREIGN KEY(intStudyID)

REFERENCES TStudies (intStudyID);

8--

ALTER TABLE TVisits WITH CHECK ADD CONSTRAINT FK_TVisits_TPatients FOREIGN KEY(intPatientID)

REFERENCES TPatients (intPatientID);

9--

ALTER TABLE TVisits WITH CHECK ADD CONSTRAINT FK_TVisits_ TVisitTypes FOREIGN KEY(intVisitTypeID)

REFERENCES TVisitTypes (intVisitTypeID);

10--

ALTER TABLE TVisits WITH CHECK ADD CONSTRAINT FK_TVisits_TWithdrawReasons FOREIGN KEY(intWithdrawReasonID)

REFERENCES TWithdrawReasons (intWithdrawReasonID);

And if needed you can use the below DDL script for creating the tables in the database

**********************************Create table Script********************************

TDrugKits

CREATE TABLE TDrugKits(

intDrugKitID int IDENTITY(1,1) NOT NULL,

intDrugKitNumber int NOT NULL,

intSiteID int NOT NULL,

strTreatment varchar(150) NOT NULL,

intVisitID int NULL,

PRIMARY KEY (intDrugKitID))

TGenders

CREATE TABLE TGenders(

intGenderID int IDENTITY(1,1) NOT NULL,

strGender varchar(50) NOT NULL,

PRIMARY KEY (intGenderID))

TPatients

CREATE TABLE TPatients(

intPatientID int IDENTITY(1,1) NOT NULL,

intPatientNumber int NOT NULL,

intSiteID int NOT NULL,

dtmDOB datetime NOT NULL,

intGenderID int NOT NULL,

intWeight int NOT NULL,

intRandomCodeID int NULL,

PRIMARY KEY (intPatientID))

TRandomCodes

CREATE TABLE TRandomCodes(

intRandomCodeID int IDENTITY(1,1) NOT NULL,

intRandomCode int NOT NULL,

intStudyID int NOT NULL,

strTreatment varchar(150) NOT NULL,

blnAvailable varchar(50) NOT NULL,

PRIMARY KEY (intRandomCodeID))

TSites

CREATE TABLE TSites(

intSiteID int IDENTITY(1,1) NOT NULL,

intSiteNumber int NOT NULL,

intStudyID int NOT NULL,

strName varchar(100) NOT NULL,

strAddress varchar(150) NOT NULL,

strCity varchar(100) NOT NULL,

strState varchar(100) NOT NULL,

strZip varchar(10) NOT NULL,

strPhone varchar(20) NOT NULL,

PRIMARY KEY (intSiteID))

TStudies

CREATE TABLE TStudies(

intStudyID int IDENTITY(1,1) NOT NULL,

strStudyDesc varchar(100) NOT NULL,

PRIMARY KEY (intStudyID))

TVisits

CREATE TABLE TVisits(

intVisitID int IDENTITY(1,1) NOT NULL,

intPatientID int NOT NULL,

dtmVisit datetime NOT NULL,

intVisitTypeID int NOT NULL,

intWithdrawReasonID int NULL,

PRIMARY KEY (intVisitID))

TVisitTypes

CREATE TABLE TVisitTypes(

intVisitTypeID int IDENTITY(1,1) NOT NULL,

strVisitDesc varchar(200) NOT NULL,

PRIMARY KEY (intVisitTypeID))

TWithdrawReasons

CREATE TABLE TWithdrawReasons(

intWithdrawReasonID int IDENTITY(1,1) NOT NULL,

strWithdrawDesc varchar(150) NOT NULL,

PRIMARY KEY (intWithdrawReasonID))

***********************************END***************************************

S.No

Child

Parent

Column(s)

TDrugKits

TSites

intSiteID

TDrugKits

TVisits

intVisitID

TPatients

TGenders

intGenderID

TPatients

TRandomCodes

intRandomCodeID

TPatients

TSites

intSiteID

TRandomCodes

TStudies

intStudyID

TSites

TStudies

intStudyID

TVisits

TPatients

intPatientID

TVisits

TVisitTypes

intVisitTypeID

TVisits

TWithdrawReasons

intWithdrawReasonID

Navigate

->> Introduction In this assignment, you will implement malloc() and free() libr

->given table -When we mixed solution 1 & 2 together (did this 6 times therefore

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

->> please note that * = PK for that tabe TStudies intStudyID* (1,2) ,strStudyDe

Question

Explanation / Answer

Related Questions

Navigate