Class, Learning how to normalize a database can be tough and most experience a l
ID: 3684875 • Letter: C
Question
Class, Learning how to normalize a database can be tough and most experience a lot of uncertainty when trying the different normal forms, so let's practice this together:
HEALTH HISTORY REPORT
PET ID PET NAME PET TYPE PET AGE OWNER VISIT DATE PROCEDURE
246 ROVER DOG 12 SAM COOK JAN 13/2002 01 - RABIES VACCINATION
MAR 27/2002 10 - EXAMINE and TREAT WOUND
APR 02/2002 05 - HEART WORM TEST
298 SPOT DOG 2 TERRY KIM JAN 21/2002 08 - TETANUS VACCINATION
MAR 10/2002 05 - HEART WORM TEST
341 MORRIS CAT 4 SAM COOK JAN 23/2001 01 - RABIES VACCINATION
JAN 13/2002 01 - RABIES VACCINATION
519 TWEEDY BIRD 2 TERRY KIM APR 30/2002 20 - ANNUAL CHECK UP
APR 30/2002 12 - EYE WASH
UNF:
Pet [ pet_id, pet_name, pet_type, pet_age, owner, ( visitdate, procedure_no,
procedure_name ) ]
What would 1NF look like? Once we have this we can then move to 2NF and so on.......
Explanation / Answer
To follow the first normal form please follow the rules as below.
1) Define data Items i.e. Defining what type of data each column contains and placing related columns into their own tables.
2) There should not be present no repeating groups of data.
3) Create a primary key for each table.
1) pet_id number(5), pet_name varchar2(20), pet_type varchar2(20), pet_age number(3), pet_owner varchar2(20), procedure_no number(5), procedure_name varchar2(200, pet_id number(5), visit_date date.
2)Table : Pet
Pet [ pet_id, pet_name, pet_type, pet_age, pet_owner ]
Table Visists:
Visits [ procedure_no, procedure_name, pet_id, visit_date ]
3) pet_id primary key for table pet
procedure_no primary key for Visits and pet_id foreign key.