Database paradigm: 1nf, 2nf, 3nf

Database paradigm 1nf 2nf 3nf bcnf (example)

The design paradigm (paradigm, database design paradigm, and database design paradigm) is a set of relational patterns at a certain level. Database construction must follow certain rules. In relational databases, this rule is a paradigm. The relationships in relational databases must meet certain requirements, that is, they must meet different paradigms. Currently, relational databases have six paradigms: 1nf, 2nf, 3nf, 4nf, and 5nf) and the sixth paradigm (6nf ). The first paradigm (1nf) meets the minimum requirements ). The second Paradigm (2nf) that meets more requirements on the basis of the first paradigm, and the other paradigms are similar. Generally, databases only need to satisfy the 3nf. The following is an example of the first paradigm (1nf), the second Paradigm (2nf), and the third paradigm (3nf ).

Introduction to several design paradigms of relational databases

1. 1nf ):Each column of a database table is an inseparable basic data item.

In any relational database, the first paradigm (1nf) is the basic requirement for the relational model. databases that do not meet the first paradigm (1nf) are not relational databases.
The first paradigm (1nf) means that each column in the database table is an inseparable basic data item, and there cannot be multiple values in the same column.

2 second Paradigm (2nf): the database table does not have function dependencies of non-Keyword segments on any candidate keyword segments.

The second Paradigm (2nf) is established on the basis of the first paradigm (1nf), that is, to satisfy the second Paradigm (2nf) must satisfy the first paradigm (1nf) first ). The second Paradigm (2nf) requires that each instance or row in the database table be able to be distinguished by a unique region. To implement differentiation, you usually need to add a column to the table to store the unique identifier of each instance. 3-2 The employee ID (emp_id) column is added to the employee information table. Because each employee's employee ID is unique, each employee can be uniquely distinguished. This unique attribute column is called as the primary keyword, primary key, and primary code.
The second Paradigm (2nf) requires that the attributes of an object fully depend on the primary keyword. Full dependency is an attribute that does not depend only on a part of the primary key.

3. 3nf: the database table does not contain information about non-primary keywords already contained in other tables.

The third paradigm must first be a second paradigm

Database tables comply with the second paradigm, eliminating data redundancy, update exceptions, insertion exceptions, and deletion exceptions.

Database tables conform to the third paradigm, eliminating data redundancy, update exceptions, insertion exceptions, and deletion exceptions.

Let's get a forum database step by step, with the following information:
(1) User: user name, email, home page, phone number, and contact address
(2) post: post title, post content, reply title, reply content

For the first time, we designed the database to only exist tables:
User name email homepage phone contact address post title post content reply title reply content
This database table conforms to the first paradigm, but no set of candidate keywords can determine the entire row of the database table. The username of the unique keyword segment cannot completely determine the entire tuples. We need to add the "Post ID" and "Reply ID" fields to change the table:
User name email homepage phone contact address post ID post title post content reply id reply title reply content
In this way, the keywords (username, post ID, and reply ID) in the data table can determine the entire line:
(User name, post ID, reply ID) → (email, home page, phone number, contact address, post title, post content, reply title, reply content)
However, such a design does not conform to the second paradigm because of the following decision relationships:
(User Name) → (email, home page, phone number, contact address)
(Post ID) → (post title, post content)
(Reply ID) → (reply title, reply content)
That is, some functions of non-Keyword fields depend on the candidate keyword fields. Obviously, this design will cause a large amount of data redundancy and operation exceptions.

We break down a database table into (underlined keywords ):
(1) User information: user name, email, home page, phone number, and contact address
(2) post information: Post ID, title, content
(3) reply information: reply ID, title, content
(4) post: User Name, post ID
(5) reply: Post ID and reply ID

This design meets the requirements of the 1st, 2, 3 and bcnf paradigms. But is this the best design?
Not necessarily.

We can see that there is a 1: n relationship between the "user name" and "post ID" in the 4th "posts, therefore, we can merge the "post" into the "post information" of the 2nd items; the "Post ID" and "Reply ID" in the 5th items "reply" are also 1: therefore, we can merge the "reply" into the "Reply information" of the 3rd items. In this way, data redundancy can be reduced in a certain amount. The new design is as follows:
(1) User information: user name, email, home page, phone number, and contact address
(2) post information: User Name, post ID, title, content
(3) reply information: Post ID, reply ID, title, content

Database Table 1 clearly meets the requirements of all paradigms;

Database Table 2 contains some functional dependencies of non-keyword "title" and "content" on the "Post ID" of the keyword segment, that is, it does not meet the requirements of the second paradigm, however, this design does not cause data redundancy and operational exceptions;

In database table 3, some function dependencies of non-Keyword segments "title" and "content" on the keyword segment "Reply ID" do not meet the requirements of the second paradigm, however, similar to database table 2, this design does not cause data redundancy and operation exceptions.

From this we can see that it is not necessary to forcibly meet the requirements of the paradigm. For a 1: n relationship, when one side is merged to the other side of N, the other side of N will no longer meet the second paradigm, but this design is better!

For M: N relationships, one or n sides of M cannot be merged to the other, which may result in non-compliance with the paradigm requirements, Operation exceptions and data redundancy.

For a relationship, we can merge 1 on the left or 1 on the right to the other side. This design does not meet the requirements of the paradigm, but does not cause operation exceptions and data redundancy.

Conclusion

The database design that meets the requirements of the paradigm is clear in structure, while avoiding data redundancy and operational exceptions. This means that the design that does not meet the requirements of the paradigm must be incorrect. In the case of a database table with a or 1: n relationship, rather than conforming to the requirements of the paradigm, the merger is reasonable.

When designing databases, we must always consider the requirements of the paradigm.