IT-112 Pharmaceutical DB Phase 1

Scenario: You have been tasked to create a database for a company that specializes in experimental drug studies for pharmaceutical companies. These drug studies help to get the drugs FDA approved for going to market. The studies are double blind meaning the patient and the doctor both do not know whether the patient is on active or placebo drug.

The front-end information will be gathered by phone or web site and passed to your database. You only have to handle the data and record it in the proper tables.

Your database will handle 2 studies being conducted simultaneously for the same pharmaceutical company, Acme Pharmaceuticals. The study identifiers will be 12345 and 54321. Study 12345 will use a random pick list for assigning treatment to patients. Study 54321 will use a random generator to assign treatment but each treatment, active and placebo, cannot outnumber the other by more than 2 patients. IE if there are 6 patients on active and 4 on placebo the next patient must be put on placebo so the numbers are 6 and 5. If the random generator picks active in this case the numbers would go to 7 and 4 which is more than 2.

Each study will have 5 sites in which patients can be enrolled. The site data collected will be Site Number (3-digit pre-assigned code), Name (Doctor or Hospital name), Address, City, State, Zip, phone number

The following information must be stored for both studies. For patients, the Patient ID (this will be a 6-digit number in the site number-sequential format, IE first patient from site 251 will be 251001, 2nd will be 251002, etc.), DOB, Gender, Weight will be collected.

The visits for both studies will be as follows: The Doctor will perform testing on the patient(Screening). When the test results are returned, the patient can continue in the study (Randomization), or if test results are not as desired will be removed from the study (Withdrawal).

• Screening
  • At Screening, the following data will be inserted into the DB. Patient ID (site number sequential from above), DOB, Gender and Weight in lbs. will be collected along with a visit date-does not have to be current date.
• Randomization
  • At Randomization, a Randomization number will be associated with the patient per the study guidelines above. The visit date will be recorded as the current date. A drug kit of the correct type (active or placebo) will also be assigned at this visit.
• Withdrawal
  • At Withdrawal, a visit date and withdrawal reason will be collected for the patient. The visit date does not have to be the current date but must be after the prior visit whether it was screening or randomization.

Step 1: Using MS Word, create a document showing the tables needed and the PK for each table. Do not create the FKs or anything else at this point. ONLY A WORD DOCUMENT WITH THE TABLES AND PK'S.