Post date: Nov 22, 2018 8:12:52 AM
Basics of Functional Dependencies and Normalization for Relational Databases (Cont.)
Normal forms based on primary keys; General definitions of second and third normal form
Exercise 1: Normalize the following to the first, second and third normal form:
HEALTH HISTORY REPORT
Solution:
1NF: Health(Pet_ID, Pet_Name, Pet_Type, Pet_Age, Owner, Visit_Date, Procedure_ID, Procedure_Description)
2NF:
Pet(ID, Name, Type, Age, Owner)
Procedure(ID, Description)
Visit(#Pet_ID, #Procedure_ID, Visit_Date)
3NF: Same
Exercise 2: Normalize the following to the first, second and third normal form:
Solution:
1NF:
Study (UnitID, StudentID, Date, TutorID, Topic, Room, Grade, Book, TutEmail)
2NF:
Study ( #UnitID, StudentID, Grade )
Unit ( UnitID, Date, Topic, Room, Book, TutorID, TutEmail )
3NF:
Study ( #UnitID, StudentID, Grade )
Unit ( UnitID, Date, #Topic, Room, #TutorID )
Topic ( Topic, Book )
Tutor ( TutorID, TutEmail )
Exercise 3: Normalize the following to the first, second and third normal form:
Solution:
1NF:
Usage(Machine, Product, Operator)
OR
Usage(Machine, Product, Operator)
2NF:
Machine(Machine, Product)
Operator(#Product, Operator)
OR
Same
3NF:
Same
OR
Same
Exercise 4: Normalize the following to the first, second and third normal form:
Solution:
1NF:
Student ( Alpha, First_name, Last_name, email, MajorCode, Major, Course, GradePoint )
(Course and not Courses and GradePoint and not GradePoints)
2NF:
Student ( Alpha, First_name, Last_name, email, MajorCode, Major )
CourseDetails ( #Alpha, Course, GradePoint )
3NF:
Student ( Alpha, First_name, Last_name, email, #MajorCode )
Major ( MajorCode, Major )
CourseDetails ( #Alpha, Course, GradePoint )
Exercise 5: Normalize the following to the first, second and third normal form:
Solution:
1NF:
Sale(SupplierID, Supplier, ProductID, Product, Cost, Markup, Price, DeptCode)
2NF:
Same
3NF:
Supplier( SupplierID, Supplier, DeptCode )
Product( ProductID, Product, Cost, Markup, Price, #SupplierID )