I've been learning how to normalise data into a normalisation table and I'm struggling to get my head around this example question.

I have this 'clinical record' card: enter image description here

Would anyone able to explain to me as to how to convert this into UNF and then to 3NF?

  • Patient, exam, vet tables – paparazzo Feb 23 '16 at 11:29

The card is UNF. Everything is one row. 3NF is when you make a list of the things, and each list of things has a unique item list, and you link them using thing-item-id.

[list] Form [items] FormID, FormName, FormTemplate (definition of    report) 
[list] Address [items] AddressID, StreetNumber, StreetName...    (address data) 
[list] Vet [items] VetID, VetName 
[list] Species    [items] SpeciesID, SpeciesName 
[list] ExaminationType [items]    ExaminationTypeID, ExaminationTypeName, Cost   
[list] Patient [items]    PatientID, PatientName, PatientSpeciesID REFERENCES Species (SpeciesID)
[list] Appointment [items] PatientID REFERENCES Patient(PatientID), ExaminationTypeID REFERENCES ExaminationType(ExaminationTypeID), Date, VetID REFERENCES Vet(VetID)

Data is uniquely stored, referenced in table containing transactional data. Form template would have RDL for creating the card, from the relevant list data. The idea behind 3NF is store related information in the same list, only once, and reference that data wherever applicable. Gets complicated when Vet changes name, but you want to reprint previous statement with Vet's previous name ... :)

| improve this answer | |
  • @Olehi, Alocyte has it right, the only thing I see that can be changed in his example, is that I would remove the ExaminationTypeId from the Appointment table and create a many to many relationship as in my mind an appointment can have multiple examinations. So an AppointmentExaminationType table that would contain AppointmentId and ExaminationTypeID. otherwise +1 to Alocyte. – JohnG Feb 23 '16 at 13:12

First of all, on normalization you need to eliminate redundant data. From what I see, VetName and VetID are redundant data, so, you need to create another table which will contain this two column, VetName and VetID and then you need to create a primary and foreign key for the new table that you will create, a foreign key pointing to main table.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.