Normalization Practice Questions
Exercise 1: Staff and Departments
A company tracks its staff and their departments in the following table:
StaffID StaffName DeptID DeptName ManagerID ManagerName
1 John Doe D01 HR M01 Alice Brown
2 Jane Smith D02 Finance M02 Bob White
3 Bob Harris D01 HR M01 Alice Brown
4 Alice Green D02 Finance M02 Bob White
Table 1: Staff and Departments Table
1. Identify the functional dependencies in this table.
2. Is the table in 1NF? If not, why?
3. Normalize the table to 2NF.
1
Exercise 2: Products and Categories
A retailer tracks its inventory using the following table:
ProductID ProductName CategoryID CategoryName SupplierID SupplierName UnitPrice
P01 Notebook C01 Stationery S01 Office Supplies Inc. $2.50
P02 Ballpoint Pen C01 Stationery S01 Office Supplies Inc. $1.00
P03 Paper Clips (100) C01 Stationery S02 ABC Stationery Co. $1.25
P04 USB Flash Drive 16GB C02 Electronics S03 Tech Warehouse $10.00
Table 2: Products and Categories Table
1. Identify the functional dependencies.
2. Is this table in 2NF? Justify your answer.
3. Normalize the table to 3NF.
2
Exercise 3: Movies and Theaters
A movie theater chain maintains the following table to track its shows:
ShowID MovieID MovieTitle TheaterID TheaterName Location ShowTime
1 M01 The Matrix T01 Grand Cinema New York 2023-11-20 18:00
2 M01 The Matrix T02 Cineplex Los Angeles 2023-11-21 20:00
3 M02 Inception T01 Grand Cinema New York 2023-11-22 19:00
4 M03 Toy Story 4 T03 Galaxy Movies Chicago 2023-11-22 14:00
Table 3: Movies and Theaters Table
1. Identify the primary key for this table.
2. Determine all functional dependencies.
3. Normalize the table to 3NF and list the resulting relations.
3
Exercise 4: Employee Training Records
A company tracks its employee training as follows:
TrainingID EmployeeID EmployeeName CourseID CourseName TrainerID TrainerName TrainingDate
T001 E001 John Doe C101 Leadership Skills TR01 Alice Brown 2023-10-10
T002 E002 Jane Smith C102 Advanced Excel TR02 Bob White 2023-10-15
T003 E001 John Doe C102 Advanced Excel TR02 Bob White 2023-10-20
T004 E003 Alice Green C103 Workplace Safety TR03 Charlie Brown 2023-10-25
Table 4: Employee Training Records
1. Identify all functional dependencies.
2. Identify any partial or transitive dependencies.
3. Normalize the table to 3NF and list the resulting relations.
4
Exercise 5: Hotel Bookings
A hotel tracks its bookings in the following table:
BookingID GuestID GuestName RoomID RoomType CheckInDate CheckOutDate TotalAmount
B001 G001 Alice Carter R101 Deluxe 2023-11-15 2023-11-20 $500
B002 G002 Bob Harris R102 Standard 2023-11-16 2023-11-18 $200
B003 G003 Charlie Brown R103 Suite 2023-11-17 2023-11-19 $700
B004 G001 Alice Carter R101 Deluxe 2023-11-21 2023-11-25 $600
Table 5: Hotel Bookings Table
1. Identify the primary key for the table.
2. Analyze and list all functional dependencies.
3. Normalize the table to 3NF and describe the resulting tables.
5
Exercise 6: School Timetable
A school maintains its timetable as follows:
ClassID CourseID CourseName TeacherID TeacherName RoomID RoomName Schedule
C101 MATH101 Calculus I T001 Dr. Alice Brown R001 Math Hall Monday 10:00-12:00
C102 PHYS101 Physics I T002 Dr. Bob Smith R002 Physics Lab Tuesday 13:00-15:00
C103 CHEM101 Chemistry I T003 Dr. Charlie Green R003 Chemistry Lab Wednesday 09:00-11:00
C104 MATH101 Calculus I T001 Dr. Alice Brown R001 Math Hall Thursday 10:00-12:00
Table 6: School Timetable
1. Identify all functional dependencies.
2. Is this table in 1NF? Why or why not?
3. Normalize the table to 3NF and explain your reasoning.
6
Exercise 7: Bank Transactions
A bank tracks its transactions in the following table:
TransactionID AccountID AccountHolderName TransactionDate TransactionType Amount BranchID BranchLocation
T001 A001 Alice Carter 2023-11-15 Deposit $1000 B001 New York
T002 A002 Bob Harris 2023-11-16 Withdrawal $500 B002 Los Angeles
T003 A001 Alice Carter 2023-11-17 Withdrawal $200 B001 New York
T004 A003 Charlie Brown 2023-11-18 Deposit $1500 B003 Chicago
Table 7: Bank Transactions
1. List all functional dependencies.
2. Normalize the table to 3NF and explain the decomposition.
7
Exercise 8: Online Marketplace
An online marketplace maintains the following table:
OrderID SellerID SellerName BuyerID BuyerName ProductID ProductName Category OrderDate Price
O001 S001 Alice Carter B001 John Doe P001 Laptop Electronics 2023-11-15 $1200
O002 S002 Bob Harris B002 Jane Smith P002 Smartphone Electronics 2023-11-16 $800
O003 S001 Alice Carter B003 Charlie Brown P003 Headphones Accessories 2023-11-17 $150
O004 S002 Bob Harris B001 John Doe P002 Smartphone Electronics 2023-11-18 $800
Table 8: Online Marketplace Orders
1. Determine the functional dependencies.
2. Normalize the table to BCNF.