How much do you know about the four database paradigms?

The content this article brings to you is about the four paradigms of databases. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

1. 1NF (First Normal Form)

The first normal form means that each column in the database table is indivisible basic data Items cannot have multiple values ​​in the same column, that is, an attribute in the entity cannot have multiple values ​​or duplicate attributes.

If repeated attributes appear, you may need to define a new entity. The new entity is composed of repeated attributes. There is a one-to-many relationship between the new entity and the original entity. The first normal form pattern requires that the attribute value cannot be split into smaller parts, that is, the attribute item cannot be a combination of attributes or consist of a set of attributes.

In short, the first normal form is non-duplicate columns. For example, if a table consists of "employee number", "name" and "phone number" (a person may have an office phone and a mobile phone), then normalizing it to 1NF can divide the phone number into "office phone" and "phone number". "Mobile phone" has two attributes, namely employee (employee number, name, office phone, mobile phone).

2. 2NF (Second Normal Form)

The second normal form (2NF) is established on the basis of the first normal form (1NF) , that is, to satisfy the second normal form (2NF), you must first satisfy the first normal form (1NF). Second Normal Form (2NF) requires that each instance or row in a database table must be uniquely distinguishable. To achieve differentiation, it is usually necessary to add a column to the table to store the unique identifier of each instance.

If the relational model R is in the first normal form, and each non-primary attribute in R is completely functionally dependent on a candidate key of R, then R is called the second normal form schema (if A is the relational schema R If it is an attribute of the candidate key, A is said to be the primary attribute of R, otherwise A is said to be a non-primary attribute of R).

For example, in the course selection relationship table (student number, course number, grades, credits), the keyword is a combination keyword (student number, course number), but since the non-primary attribute credit only depends on the course number, There is only partial dependence on keywords (student ID, course ID), but not complete dependence. Therefore, this method will lead to problems such as data redundancy and update anomalies. The solution is to divide it into two relational models: student table (student table) number, course number, scores) and course schedule (course number, credits), the new relationship is connected through the foreign keyword course number in the student table, and is connected when needed.

3. 3NF (Third Normal Form)

If the relational model R is in the second normal form, and each non-primary attribute is not transferred If the candidate key depends on R, R is said to be in third normal form.

Take the student table (student number, name, course number, grades) as an example. There are no duplicate student names, so the table has two candidate codes (student number, course number) and (name, course number). number), so there is functional dependence: student number ——> name, (student number, course number) ——> grade, the only non-primary attribute grade has no partial dependence on the code, and there is no transitive dependence, so the attribute Belongs to the third paradigm.

4. BCNF (BC normal form)

It is built on the basis of the third normal form. If the relational model R is the first normal form, And each attribute does not pass a candidate key that depends on R, then R is called a BCNF schema.

Assuming that the warehouse management relationship table (warehouse number, storage item number, administrator number, quantity) satisfies an administrator to only work in one warehouse; a warehouse can store multiple items, then the following relationship exists:

(warehouse number, storage item number)——>(administrator number, quantity)

(administrator number, storage item number)——>(warehouse number, quantity)

So, (warehouse number, storage item number) and (administrator number, storage item number) are candidate codes for the warehouse management relationship table. The only non-key field in the table is quantity, which is consistent with the third paradigmatic. However, due to the following decision relationship:

(warehouse number)——>(administrator number)

(administrator number)——>(warehouse number)

That is, there is a situation where the key field determines the key field, so it does not comply with BCNF. Decompose the warehouse management relationship table into two relationship tables: warehouse management table (warehouse number, administrator number) and warehouse table (warehouse number, storage item number, quantity), so that this database table complies with BCNF and eliminates deletion exceptions , insertion exception and update exception.

5. 4NF (Fourth Normal Form)

Suppose R is a relational model, and D is a set of multi-valued dependencies on R. If there is a multi-valued dependency on X->Y in D, X must be a superkey of R, then R is said to be in the fourth normal form.

For example, employee table (employee number, employee's child name, employee elective courses). In this table, the same employee may have multiple employee child names. Similarly, the same employee may also have multiple employee names. An elective course for employees, that is, there are multi-valued facts here, which does not conform to the fourth paradigm. If you want to comply with the fourth normal form, you only need to divide the above table into two tables so that they have only one multi-valued fact, such as employee table one (employee number, employee's child name), employee table two (employee number, employee elective course) , both tables have only one multi-valued fact, so they conform to the fourth normal form.

1. First Normal Form (1NF)
In any relational database, the first normal form (1NF) is the basic requirement for the relational model. If it does not meet the first normal form (1NF) 1NF) database is not a relational database.
The so-called first normal form (1NF) means that each column (that is, each attribute) of the database table is an indivisible basic data item, and there cannot be multiple values ​​in the same column, that is, in the entity An attribute of can not have multiple values ​​or duplicate attributes. In short, first normal form is a column without duplicates.
2. Second Normal Form (2NF)
The second normal form (2NF) is established on the basis of the first normal form (1NF), that is, it satisfies the The second normal form (2NF) must first satisfy the first normal form (1NF). Second Normal Form (2NF) requires that each instance or row in a database table must be uniquely distinguishable. To achieve differentiation, it is usually necessary to add a column to the table to store the unique identifier of each instance. The employee number (emp_id) column is added to the employee information table, because each employee's employee number is unique, so each employee can be uniquely distinguished. This unique attribute column is called the primary key or primary key or primary key.
Second Normal Form (2NF) requires that the attributes of an entity completely depend on the primary key. The so-called complete dependence means that there cannot be an attribute that only depends on part of the primary key. If it exists, then this attribute and this part of the primary key should be separated to form a new entity. The new entity and the original entity are one-to-many. relation. To achieve differentiation, it is usually necessary to add a column to the table to store the unique identifier of each instance. In short, the second normal form is that non-primary attributes depend on the primary key.
3. Third Normal Form (3NF)
To satisfy the third normal form (3NF), you must first satisfy the second normal form (2NF). On the basis of satisfying the second normal form, there must be no transitive functional dependence, then it is the third normal form. In short, the third normal form is that attributes do not depend on other non-primary attributes.

The above is the detailed content of How much do you know about the four database paradigms?. For more information, please follow other related articles on the PHP Chinese website!