Skip to content
Advertisement

Have I normalized this database to a 3NF level the right way? [closed]

I had a lecture at college recently about normalizing databases, but since we are online I can’t really ask a lot of questions, so here I am.

Can someone tell me if I’m doing the conversion right, if not where am I mistaking?

The Task:

The Task

My solution:

UNF

1NF

2NF

3NF

Advertisement

Answer

Let’s start with the Patient. The only information we have about a patient is his number and name.

Next, we have a Ward. Based on the report, a ward can have multiple patients, while a patient is in one ward. We model a one to many relationship with a foreign key.

Finally, we have a bed. A ward has multiple beds, while a patient has one bed. Since all we know about a bed is the bed number, we’ll add that to the patient table as another foreign key to a table we don’t have any information to define.

What do we know about a drug?

What else do we know? A dosage is a relationship between a drug and a patient. So let’s make our first attempt. I’m assuming that the start and end date has to do with the drug, since it’s on the same line as the drug.

Now, we’ve tied the dosage to the drug and the patient. However, we haven’t tied the patient to the dosages. A patient can have multiple dosages, and dosages can belong to more that one patient. When we have a many to many relationship, we use a junction table to tie them together.

We moved the units given, start date, and end date to the PatientDosage junction table, since these fields have to do with both the patient and the dosage. So the final Dosage table would look like this.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement