banner
veritas

veritas

github
HomeArchives

How to quickly determine if it is 1NF, 2NF, or 3NF

91
AI Translation
This post is translated from Chinese into English through AI.View Original
AI-generated summary
This text discusses how to differentiate between different normalization forms in database design, such as 1NF, 2NF, 3NF, and BCNF. It explains the criteria for each form, including the atomicity of columns, functional dependencies, and the absence of transitive dependencies. The goal is to ensure data integrity and eliminate redundancy in the database.

Aside#

This type of question is generally accompanied by a method of determining candidate keywords, so this article will first introduce how to identify candidate keywords before discussing how to distinguish normal forms.

Method for finding candidate keys: Based on the set of dependencies, identify attributes that have never appeared on the right side; these must be one of the candidate keys. Based on this attribute, expand sequentially according to the set of dependencies to see if all attributes can be traversed, adding those that cannot be traversed to the candidate keys.

Next, we will discuss how to distinguish normal forms.

Related Concepts and Case Analysis#

  • 1NF: Emphasizes the atomicity of columns, meaning that columns cannot be further divided into other columns.
    For example: In the figure below, the book can be decomposed into book number and book title, which does not belong to 1NF.
    image

  • 2NF: 1. Must satisfy the first normal form. 2. Each non-prime attribute must be fully functionally dependent on the candidate key or the primary key (primary code).
    PS:Only when composite fields serve as the primary key can there be cases that do not satisfy 2NF.
    For example:
    In the figure below, we can determine that the candidate key for this relational model is EM, and there exists M→L in the functional dependency set, which is not directly derived from EM, so there is a partial functional dependency that does not belong to 2NF.
    image

  • 3NF: 1. First, it must satisfy the second normal form. 2. There should be no functional dependencies between non-prime attributes (or no transitive dependencies).
    This means that if the primary attribute is A, and A→B, A→D, then B→C cannot occur at this time.

  • BCNF: 1. Satisfies all the requirements of the previous lower normal forms. 2. There should be no dependencies between primary keys.
    For example:
    image

Summary#

Normal Form

Function

Characteristics

1NF:

Satisfies atomicity; fields cannot be further divided.

Fields are all single attributes.

2NF:

Satisfies full dependency; eliminates partial functional dependencies (columns cannot be mixed).

Requires the use of a unique primary key.

3NF:

Eliminates transitive dependencies of non-prime attributes.

There should be no functional dependencies between non-prime attributes.

BCNF:

Eliminates transitive dependencies of primary attributes.

There should be no dependencies between primary keys.

As long as it is a relational database table, it satisfies the first normal form. The essence of the first normal form is more of a limitation on relational databases.

The second and third normal forms are more of a requirement for reasonable design of database tables; understanding the second and third normal forms can better help us design database tables.

Loading...
Ownership of this post data is guaranteed by blockchain and smart contracts to the creator alone.