Question

Define first, second, and third normal forms when only primary keys are considered. How do the general definitions of $2\mathrm{NF}$ and $3\mathrm{NF}$, which consider all keys of a relation, differ from those that consider only primary keys?

Short answer

First Normal Form (1NF) dictates that every record's attribute be atomic, and that a primary key exists to uniquely identify all records. Second Normal Form (2NF) expands on 1NF, requiring all non-prime attributes be dependent on the entire primary key, with no partial dependencies. Third Normal Form (3NF) goes further by requiring no transitive dependencies among non-prime attributes. When considering only primary keys, 2NF and 3NF focus solely on dependencies with the primary key. However, their general definitions consider all keys in the relation.

Step-by-step solution

First Normal Form (1NF)

A relation is in First Normal Form (1NF) if every attribute of every record is atomic, meaning it contains no repeating groups or sets. It stipulates the existence of a primary key, defining that every row of data must be uniquely identifiable.

Second Normal Form (2NF)

A table is in the Second Normal Form (2NF) if it is in 1NF and every non-prime attribute (attributes that are not a part of any candidate key) are dependent on the 'whole' primary key. Thus, there should be no partial dependency of any set of attributes on a primary key.

Third Normal Form (3NF)

A table is in the Third Normal Form (3NF) if it is in 2NF and if there is no transitive dependency for non-prime attributes. In other words, every non-prime attribute must depend directly on the primary key.

Difference in General Definitions

General definitions of 2NF and 3NF consider all keys of a relation, not just the primary keys. In general 2NF, each non-key attribute should be a function of every candidate key. For general 3NF, no non-key attribute is a function of any of other non-key attributes. In definitions that consider only primary keys, these dependencies are tied exclusively to the primary key, ignoring other potential keys in the relation.

Key concepts

First Normal Form (1NF)

In database normalization, achieving the First Normal Form (1NF) is the foundational step towards organizing a database efficiently. It revolves around ensuring that each field contains atomic values. In simpler words, every table cell should hold a single value and not a set or list.
A crucial requirement of 1NF is the presence of a primary key. This primary key must ensure each row is unique, aiding in eliminating duplication.
Achieving 1NF involves:

• Ensuring each column has only one value per record
• Eliminating repeating groups within a table
• Selecting a suitable primary key for uniqueness

By conforming to 1NF, databases are structured more logically, preventing anomalies and redundancy.

Second Normal Form (2NF)

Once a table is in First Normal Form, the next step is to achieve Second Normal Form (2NF). This form focuses on eliminating partial dependency, which occurs when non-prime attributes depend only on a segment of a composite primary key.
For a table to be in 2NF, it must first meet all the criteria of 1NF. It must then ensure that every non-prime attribute is fully functionally dependent on the entire primary key, not just a part of it.
Key aspects of 2NF include:

• No partial dependency: All attributes must be fully dependent on the entire key
• Applies only to tables with composite keys

By achieving 2NF, you minimize redundancy and enhance data integrity by ensuring each attribute is functionally dependent on the full key.

Third Normal Form (3NF)

The Third Normal Form (3NF) further refines a database by removing transitive dependencies. This means that a non-prime attribute should not depend on another non-prime attribute.
To be in 3NF, a table must first satisfy all the conditions of 2NF. Then, it should ensure that non-prime attributes rely solely on the primary key.
Steps to ensure 3NF include:

• Eliminating transitive dependency: Non-prime attributes should directly depend on the prime key
• Further eliminating redundancy

Adhering to 3NF results in a database that is highly efficient and free from unwanted dependencies, bolstering data consistency and simplicity.

Primary Key

A primary key is a specific choice of a minimal set of attributes that uniquely identifies an instance of an entity in a database. It is central to database normalization by ensuring each record can be distinctly retrieved.
Characteristics of a primary key include:

• Uniqueness: No two rows can have the same primary key value
• Non-nullability: A primary key column cannot contain null values
• Immutable: The value should not change over time

The primary key plays a critical role in establishing 1NF and serves as the pivot for subsequent normal forms. It is the backbone for uniquely identifying and accessing data efficiently.

Candidate Key

Candidate keys are possible choices for the primary key of a table. They possess all the properties of a primary key—uniqueness and non-nullability. However, not all candidate keys are chosen as the primary key.
Features of candidate keys include:

• Uniqueness: Like the primary key, candidate keys uniquely identify records
• Minimality: A candidate key contains no extra attributes than necessary
• Diversity: A table can have multiple candidate keys, but only one primary key

Choosing the right candidate key as the primary key is crucial. It ensures efficient data retrieval and maintains the structural integrity of the table. Understanding candidate keys is essential for recognizing alternative ways to access data effectively.