Requirements for Second Normal Form

Second Normal Form (2NF) is an important step in the normalization process for relational databases. For a table to be in 2NF, it must satisfy two primary requirements:
Requirements for Second Normal Form (2NF)

1. First Normal Form (1NF) Compliance:
   • The table must already be in First Normal Form. This means:
   • All columns contain only atomic (indivisible) values.
   • Each column contains values of a single type.
   • Each row in the table must be unique, often enforced by a primary key.
   • There must be no repeating groups or arrays in the columns.
2. No Partial Dependency:
   • All non-prime attributes (i.e., attributes that are not part of any candidate key) must be fully functionally dependent on the entire primary key.
   • In simpler terms:
   • If the primary key is composite (consisting of two or more columns), no non-prime attribute should depend on only a part of the composite key. The non-prime attributes should depend on the full composite key.
   • Example:
   • Consider a table Orders with the columns: OrderID (part of a composite primary key), ProductID (part of a composite primary key), ProductName, and SupplierID.
   • If ProductName depends only on ProductID and not on the entire composite key (OrderID and ProductID), then there is a partial dependency, violating 2NF. To resolve this, you would decompose the table into smaller tables, where each table’s non-prime attributes depend fully on the entire primary key.

Summary

• 1NF Compliance: The table must be in 1NF.
• Full Functional Dependency: Non-prime attributes must depend on the whole primary key, not just part of it (no partial dependencies).

By achieving 2NF, the database structure becomes more organized, reducing data redundancy and improving data integrity. If you have any specific scenarios or examples you'd like to discuss, feel free to share!

Relations in 1NF can have significant design flaws resulting in reduced efficiency. The goal of second normal form (2NF)^[1] is to correct these flaws. Consider the following table, which might be used to list cars available for sale at different dealerships. The relation assumes that a given car can only be stocked by a single dealer.

Note that, in this example, values in the CarID field are equivalent to book ISBN's. The CarID denotes a particular make and model of car, but not a specific instance of that make and model.
In relational notation, this table would be written as:

Car (CarID, Color, Make, Model, Year, DealerID, DealerName)

The problem with this relation is that the fields relating to a car's dealer are not functionally dependent on the primary key (CarID). If any car can be acquired from more than one dealer, you will waste space by creating a second relation to save information about that car and its second dealer.
For a relation to be in 2NF, every descriptor (non-key column) must be functionally dependent on every primary-key column in the relation.
The next lesson defines functional dependencies^[2] .

The solution to anomalies in a first normal form relation is to break down the relation so there is one relation for each entity in the 1NF relation. The orders relation, for example, will break down into four relations (customers, items, orders, and line items).
Such relations are in at least second normal form (2NF).
In theoretical terms, second normal form relations are defined as follows:

1. The relation is in first normal form.
2. All non-key attributes are functionally dependent on the entire primary key.

The new term in the preceding is functionally dependent, a special relationship between attributes.

Before moving on to the next lesson, click the Exercise link below to reinforce your understanding of the requirements of 1NF and 2NF.
Two Relations - Exercise

[1]second normal form (2NF): A table is in second normal form when every attribute is functionally dependent on the entire primary key.

[2]functional dependency: A one-way relationship between two attributes so that, for any unique value of one attribute, there is only one value of the other attribute.