Functional Dependencies in SQL Databases Normal Forms

Question

There are two functional dependencies in SQL databases.

a) Partial functional dependency: an implicit column depends on some, but not all, columns in a composite primary key.

b) Transitive functional dependencies: Any implicit column depends on other non-key columns.

For a good SQL database.

Rule 1: Columns only contain atomic values

Rule 2: No Duplicate Data Groups

Rule 3: Do Not Have Partial Dependencies

Rule 4: no transitive dependencies

I understood the requirements of rules 1 and 2, why we need the 3rd and 4th rules, instead of saying that the column no should not depend on other columns. Why are there two separate rules (3 and 4)?

Source: Head First SQL

Thanks in advance!

Answer 1

Good question. It is solely for historical and pedagogical reasons that the two are often separated.

The second normal form (2NF) is associated with the elimination of only partial dependencies. 2NF alone is not particularly important because the Third Normal Form, Normal Boyce Codd Form, and higher normal forms also eliminate the same partial key key dependencies, and these NF (> 2NF) are usually the desired goal in developing a database. However, it is common practice to train normalization using the decomposition process. By the decomposition method, partial key relationships are usually considered first. In fact, this is rarely done by most practitioners who will often look at all the addictions at once.

Normal form definitions higher than 2NF do not necessarily mean partial key dependencies as a special case. The normal form of Boyce Codd can be summarized briefly as meaning that every non-trivial functional determinant is a superkey - in other words, there are no non-trivial FDs (of any kind) except for keys.