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
- Create a table that uses a primary key constraint, foreign key constraint and check constraint. Please include in table and column names varchar2, number and
- SELECT student.snum, student.sname, AVG(mark.grade) as avgcs FROM student,mark WHERE substring(m1.cnum,1,2) = 'CS' GROUP BY mark.snum I am trying to get the
- Hello, I need some help with creating a relational and logical ERD Model. The following ERD diagram is in Chen Notation and it tracks information about votes