Page 472 - Computer_Science_F5

(an attribute that is not part of any candidate key) on any super key (single key
or a group of multiple keys that can uniquely identify tuples in a table) should be
removed.

In other words, a table can be explained as in 3NF if it is in 2NF, and for each Chapter Eight: Databases and Database Management Systems
FOR ONLINE READING ONLY
functional dependency X-> Y, at least X is a super key of the table or Y is a prime
attribute of the table. For example, suppose a company wants to store the complete
address of each employee; they create a table named employee_ details, as indicated
in Table 8.14.

Table 8.14: An example of a table not conforming to 3NF.
emp_id emp_name emp_pc emp_reg emp_city emp_district
1001 John Miti 282005 AR Arusha Arumeru

1002 Muhidini Kata 222008 DS DSM Kinondoni
1006 Lora Misitu 282007 DS DSM Temeke
1101 Lilly Nyavu 292008 TG Tanga Muheza
1201 Steve Milango 222999 MZ Mwanza Ilemela

In Table 8.14 you can see that Super keys are: {emp_id}, {emp_id, emp_name},
{emp_id, emp_name, emp_pc}and so on; Candidate Keys: {emp_id}, Non-prime
attributes: all attributes except emp_id are non-prime as they are not part of any
candidate keys.

Here, emp_reg, emp_city and emp_ district depend on emp_pc. and, emp_pc is
dependent on emp_id that makes non-prime attributes (emp_reg, emp_city & emp_
district) transitively dependent on super key (emp_id). This violates the rule of 3NF.
To make this table comply with 3NF, we have to break the table into two tables to
remove the transitive dependency, as shown in Table 8.15 and Table 8.16, which
conform to 3NF.

Table 8.15: Employees’ details

emp_id emp_name emp_pc
1001 John Miti 282005
1002 Muhidini Kata 222008
1006 Lora Misitu 282007
1101 Lilly Nyavu 292008
1201 Steve Milango 222999

463
Student’s Book Form Five

Computer Science Form 5.indd 463 23/07/2024 12:34