Re: Identifying candidate keys and primary keys

From: Philipp Post <Post.Philipp_at_googlemail.com>
Date: Tue, 15 Apr 2008 08:01:04 -0700 (PDT)
Message-ID: <f958bd65-e067-41f8-8e6a-faeb6df1aaa0_at_w8g2000prd.googlegroups.com>

> We have a patient medication form from an hospital with these:
> Heading: Patient number, Full name, Bed number, Ward number, Ward
> name.
> Then a table with this columns: drug number, name, description,
> dosage, method of admin, units per day, start date, finish date.
>
> I have to find all the candidate keys and primary keys.

I would assume it will make it easier first to define the tables, and then the keys. As the requirements were quite vague, my guess (without prejudice) here. Play with it a bit and see if it leads to an answer.

Patients

---

PatientNumber - Primary key
FirstName \ bad candidate key if two persons have the same name LastName /

Wards

---

WardNumber - Primary key
WardName - candidate key (?) No idea if the name is unique, but it is likely.

PatientLocations

---

PatientNumber --- \
WardNumber --- composite primary key
BedNumber --- /

I could imagine a patient can change the ward during its stay in hospital, so might add StartDate, EndDate pair, but that was not mentioned in the specification.

Drugs

---

DrugNumber - Primary key
DrugName - candidate key (?) Is a drug name always unique? DrugDescription

Treatments

---

PatientNumber \
DrugNumber --- composite primary key
StartDate /
FinishDate /
Dosage
MethodOfAdmin
UnitsPerDay

I would assume that the same drug can be given through out several periods of time, therefore I put the dates into the PK.

Brgds

Philipp Post Received on Tue Apr 15 2008 - 17:01:04 CEST