-1

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

# Patient Nos
  Patient Name
  Ward Nos
  Ward Name
  Bed Name
# Drug Number
  (Drug Name)
  (Description)
  (Dosage)
  (Method)
  (Units Given)
  (Start Date)
  (Finish Date)

1NF

# Patient Nos
  Patient Name
  Ward Nos
  Ward Name
  Bed Name

# Patient Nos
# Drug Number
  Drug Name
  Description
  Dosage
  Method
  Units Given
  Start Date
  Finish Date

2NF

# Patient Nos
  Patient Name
  Ward Nos
  Ward Name
  Bed Name

# Patient Nos
# Drug Number
  Drug Name
  Description
  
# Patient Nos  
  Dosage
  Method
  Units Given
  Start Date
  Finish Date

3NF

# Patient Nos
  Patient Name
  Ward Nos
  Ward Name
  Bed Name

# Patient Nos
# Drug Number
  Drug Name
  Description
  
# Patient Nos
# Drug Number
  Dosage
  Method
  Units Given

# Patient Nos
  Start Date
  Finish Date
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
Borislav
  • 13
  • 4
  • Well, you're missing foreign keys and it's not clear what you're using as far as table names. It's a group of column names without primary keys and foreign keys. Create the tables in SQL DDL, i.e. create table dbo.TableName... Then the code could be updated. Right now it's just a picture – SteveC Oct 09 '20 at 20:30
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. – philipxy Oct 09 '20 at 21:06
  • Re "is this right": Show the steps of your work following your reference/textbook, with justification--not all terms/notations are standard & we don't know exactly what algorithm/method you are following & we want to check your work but not redo it & we need your choices when a process allows them & otherwise we can't tell you where you went right or wrong & we don't want to rewrite your textbook. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. If you're not sure it's right, ask 1 specific researched non-duplicate question re where you got stuck. – philipxy Oct 09 '20 at 21:07

1 Answers1

1

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

Patient
_______
Patient Nos
Patient 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.

Patient
_______
Patient Nos
Patient Name
Ward Nos (FK)

Ward
----
Ward Nos
Ward Name

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.

Patient
_______
Patient Nos
Patient Name
Ward Nos (FK)
Bed Nos (FK)

What do we know about a drug?

Drug
----
Drug Nos
Drug Name
Drug Description

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.

Dosage
------
Dosage Nos
Dosage Type
Dosage Method
Dosage Units Given
Dosage Start Date
Dosage End Date
Drug Nos (FK)
Patient Nos (FK)

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.

PatientDosage
-------------
PatientDosage Nos
PatientDosage Units Given
PatientDosage Start Date
PatientDosage End Date
Patient Nos (FK)
Dosage Nos (FK)

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.

Dosage
------
Dosage Nos
Dosage Type
Dosage Method
Drug Nos (FK)
Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111