Re: Identifying candidate keys and primary keys

From: Philipp Post <>
Date: Tue, 15 Apr 2008 08:01:04 -0700 (PDT)
Message-ID: <>

> 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.


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


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


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.


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


PatientNumber \
DrugNumber --- composite primary key
StartDate /
FinishDate /

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


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

Original text of this message