Third normal form

Third normal form (3NF) is a level of database normalization defined by English computer scientist Edgar F. Codd. A relation (or table, in SQL) is in third normal form if it is in second normal form and also lacks non-key dependencies, meaning that no non-prime attribute is functionally dependent on (that is, contains a fact about) any other non-prime attribute. In other words, each non-prime attribute must depend solely and non-transitively on each candidate key. William Kent summarised 3NF with the dictum that "a non-key field must provide a fact about the key, the whole key, and nothing but the key". An example of a violation of 3NF would be a Patient relation with the attributes PatientID, DoctorID and DoctorName, in which DoctorName would depend first and foremost on DoctorID and only transitively on the key, PatientID (via DoctorID's dependency on PatientID). Such a design would cause a doctor's name to be redundantly duplicated across each of their patients. A database compliant with 3NF would store doctors' names in a separate Doctor relation which Patient could reference via a foreign key. 3NF was defined, along with 2NF (which forbids dependencies on proper subsets of composite keys), in Codd's paper "Further Normalization of the Data Base Relational Model" in 1971, which came after 1NF's definition in "A Relational Model of Data for Large Shared Data Banks" in 1970. 3NF was itself followed by the definition of Boyce–Codd normal form in 1974, which seeks to prevent anomalies possible in relations with several overlapping composite keys.