Understanding Paradigms in Database Design: A Comprehensive Guide

Database normalization and paradigm

Normalization in database design aims to reduce data redundancy, enhance data integrity, and avoid data exceptions (such as insertion, update, and deletion exceptions). This is done by breaking large data tables into smaller, more manageable tables and defining their relationships.

Different paradigms represent different normalization levels, each level is based on the previous level and follows specific rules. Here are several commonly used paradigms:

---

The first normal form (1NF)

1NF is the basic level of normalization, and its core goal is to eliminate duplicate data and ensure that each field in the table contains a single, indivisible value (atomic value).

• 1NF rules:
  1. Each field must contain an atomic value, that is, a value that cannot be subdivided.
  2. Each row of data must be unique.
  3. Each column has the same data type.
  4. Duplicate columns or fields are not allowed to contain multiple values.

1NF Example:

Non-1NF:

Order ID	commodity	quantity
1	Apples, bananas	2, 3
2	orange	5

---

Convert to 1NF:

Order ID	commodity	quantity
1	apple	2
1	banana	3
2	orange	5

---

Second Normal Form (2NF)

2NF On the basis of 1NF, part of the function dependency is further eliminated. Partial function dependence means that non-primary key attributes depend on part of the primary key (for composite primary keys). To meet 2NF, 1NF must be met first.

• 2NF rules:
  1. Satisfied 1NF.
  2. Each non-primary key attribute must be completely dependent on the entire primary key.

2NF example:

Non-2NF (partial function dependencies):

Order ID	commodity	Customer Name	price
1	apple	John	10
1	banana	John	5
2	orange	Jane	8

---

Convert to 2NF:
surface:

• Order (order ID, customer name)
• Order details (order ID, item, price)

Order form:

Order ID	Customer Name
1	John
2	Jane

---

Order details list:

Order ID	commodity	price
1	apple	10
1	banana	5
2	orange	8

---

The third normal form (3NF)

3NF On the basis of 2NF, transfer function dependencies are eliminated. Transfer function dependence means that non-primary key attributes depend on other non-primary key attributes. To meet 3NF, 2NF must be met first.

• 3NF rules:
  1. Meet 2NF.
  2. No non-primary key attribute should depend on other non-primary key attributes.

3NF example:

Non-3NF (transfer function dependency):

Order ID	commodity	category	supplier
1	apple	fruit	XYZ
2	carrot	vegetable	ABC

---

Convert to 3NF:
surface:

• Order (order ID, item, category)
• Category (category, supplier)

Order form:

Order ID	commodity	category
1	apple	fruit
2	carrot	vegetable

---

Category table:

category	supplier
fruit	XYZ
vegetable	ABC

---

Boyce-Codd Paradigm (BCNF)

BCNF is stricter than 3NF. If the left part of each function depends on is a candidate key, the table satisfies BCNF.

• BCNF rules:
  1. Meet 3NF.
  2. Each determinant is a candidate key.

Fourth Paradigm (4NF)

4NF solves the multi-value dependency problem, in which one attribute determines multiple values ​​of another attribute, and these values ​​are independent of each other.

• 4NF rules:
  1. Meet BCNF.
  2. There is no multi-value dependency.

Summarize

Database normalization is a key step in ensuring data quality. Different paradigms provide different levels of normalization, and choosing the right paradigm requires trade-offs on data integrity and performance requirements. Although high paradigms can guarantee data integrity, in some cases, a lower paradigm level may be chosen for performance considerations.

The above is the detailed content of Understanding Paradigms in Database Design: A Comprehensive Guide. For more information, please follow other related articles on the PHP Chinese website!