I've been learning how to normalise data into a normalisation table and I'm struggling to get my head around this example question.
Would anyone able to explain to me as to how to convert this into UNF and then to 3NF?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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 ... :)
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.