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