View the step-by-step solution to:

Question

How and what would the design look like for the conceptual design for this database look like??? It is due

tomorrow and I'm having trouble with it.



Design a database for a School of Pharmacy. The main application of the database will be to manage projects that test the effects of medications [medication = "a substance used in treating disease or relieving pain"]. The School personnel consists of researchers and staff workers. Staff workers can be business specialists, field workers, or clerical staff. A researcher may have multiple specialties.

 

The school has research projects in various stages of development: current, pending, complete. A project studies the effects of one medication on subjects (but there may be several projects testing the same medication for different purposes. Each project has one principal investigator (who is a senior researcher), and may involve several other researchers. Each project, identified by Project_Id, is funded by an initial grant, providing an initial funding amount.

 

For a complete project, subjects and "effects" will not be kept in the database, but the project findings will be summarized and analyzed in a report, whose main author is the principal investigator, and which sometimes has some other researchers as co-authors.

 

A pending project has a PI. No other personnel have been assigned to it. No subjects have been assigned to it.

 

A subject is involved in the current testing of one medication only for one specific purpose. Besides the usual data for a subject (Subject_id, last name, first name, bdate, sex...), we record the regimen of the medication tested. For simplicity, a regimen consists of a frequency (for example once a day, or two times a month), a dosage (for example 10 mg), a form (for example, pill, caplet, liquid). Different subjects involved in the testing of the same medication for the same project may have different regimens. For a subject, we also record the effects of the medication at specific dates [explain why it is difficult to provide other attributes than the date for the entity type EFFECT]; we also keep track of the stipend payments to the subject at regular dates (date and amount).

 

Field workers are assigned to work with subjects, clerical workers are assigned to projects (possibly more than one).  Business specialists are involved in getting extra funding from funding organizations for specific projects. When such extra funding is obtained, we keep track of the amount, the date, and the business specialist involved.

 

personnel: Pid (PK), Lname, fname, bdate, sex, job_title

 

funding organizations: Org_Name (PK), city, state

Subjects: : Subj_id (PK), Lname, fname, bdate, sex, weight, height)

Medication: MedName (PK), MedDate (initial patent date), "owner"

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask Expert Tutors You can ask 0 bonus questions You can ask 0 questions (0 expire soon) You can ask 0 questions (will expire )
Answers in as fast as 15 minutes