Shulou(Shulou.com)05/31 Report--

This article is about what the four paradigms of mysql database are. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. 1NF (first normal form)

The first normal form means that each column in the database table is an indivisible basic data item, and there can be no more than one value in the same column, that is, an attribute in an entity cannot have multiple values or duplicate attributes.

If there are duplicate attributes, you may need to define a new entity, the new entity consists of duplicate attributes, and there is an one-to-many relationship between the new entity and the original entity. The pattern of the first normal form requires that the attribute value can no longer be split into smaller parts, that is, the attribute item cannot be a combination of attributes or composed of a set of attributes.

In short, the first paradigm is a column without repetition. For example, a table consisting of "employee number", "name" and "phone number" (a person may have an office phone and a mobile phone) can be normalized to 1NF to divide the phone number into two attributes: "office phone" and "mobile phone", that is, the 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 meet the second normal form (2NF) must first meet the first normal form (1NF). The second normal form (2NF) requires that each instance or row in the database table must be uniquely localized. To achieve differentiation, you usually need to add a column to the table to store the unique identity of each instance.

If the relational model R is the first normal form and every non-primary attribute in R completely depends on some candidate key of R, then R is called the second normal form pattern (if An is an attribute of the candidate key of the relational pattern R, then An is said to be the principal attribute of R, otherwise An is said to be the non-primary attribute of R.

For example, in the course selection relation table (student number, course number, grade, credit), the keyword is the combined keyword (student number, course number), but because the non-main attribute credit only depends on the course number, but only partially depends on the keywords (student number, course number), rather than completely dependent, so this approach will lead to problems such as data redundancy and abnormal updates. The solution is to divide it into two relationship patterns: student table (student number). The course number, score) and the course schedule (course number, credits). The new relationship is connected through the external keyword course number in the student table and connected when needed.

3. 3NF (third Paradigm)

If the relational model R is the second normal form and each non-primary attribute does not pass the candidate key that depends on R, then R is said to be the pattern of the third normal form.

Take the student table (student number, name, course number, grade) as an example, in which the student name has no duplicate name, so the table has two candidate codes (student number, course number) and (name, course number). Therefore, there is a functional dependency: student number-> name, (student number, course number)-- > grade, the only non-principal attribute score does not have partial dependence on the code, nor does it have transitive dependence, so the attribute belongs to the third paradigm.

4. BCNF (BC paradigm)

It is based on the third normal form. If the relational model R is the first normal form and each attribute does not transfer candidate keys dependent on R, then R is called the pattern of BCNF.

Suppose the warehouse management relationship table (warehouse number, storage item number, administrator number, quantity) satisfies that an administrator works in only one warehouse; if a warehouse can store multiple items, the relationship exists as follows:

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

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

Therefore, (warehouse number, storage item number) and (administrator number, storage item number) are candidates for warehouse management relationship table, and the only non-key field in the table is quantity, which conforms to the third paradigm. However, due to the following decisions:

(warehouse number)-- > (administrator number)

(administrator number)-- > (warehouse number)

That is, there is a situation in which the key field determines the key field, so it is not BCNF compliant. The warehouse management relational table is divided into two relational tables: warehouse management table (warehouse number, administrator number) and warehouse table (warehouse number, storage item number, quantity), so that the database table conforms to BCNF, and eliminates deletion exception, insert exception and update exception.

5. 4NF (fourth paradigm)

Let R be a relational model and D be a set of multi-valued dependencies on R. If there is a multivalued dependence on X-> Y in D, then X must be the superbond of R, then R is said to be the pattern of the fourth normal form.

For example, the employee table (employee number, employee child name, employee elective course), in this table, the same worker may have multiple employee child names, similarly, the same worker may have multiple employee elective courses, that is, there are multi-valued facts here, which are not in line with the fourth paradigm. If you want to conform to the fourth paradigm, you only need to divide the above table into two tables so that they have only one multi-valued fact, such as employee Table 1 (employee number, employee child name) and employee Table 2 (employee number, employee elective course). Both tables have only one multi-valued fact, so they are in line with the fourth paradigm.

1. The first normal form (1NF)

In any relational database, the first normal form (1NF) is the basic requirement of the relational schema, and the database that does not meet the first normal form (1NF) is not a relational database.

The so-called first normal form (1NF) means that each column (that is, each attribute) of a database table is an inseparable basic data item, and there can not be multiple values in the same column, that is, an attribute in an entity cannot have multiple values or duplicate attributes. In short, the first paradigm is a column without repetition.

2. The second normal form (2NF)

The second normal form (2NF) is established on the basis of the first normal form (1NF), that is, to meet the second normal form (2NF) must first meet the first normal form (1NF). The second normal form (2NF) requires that each instance or row in the database table must be uniquely localized. To achieve differentiation, you usually need to add a column to the table to store the unique identity 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 keyword or primary key, primary code.

The second normal form (2NF) requires that the attributes of the entity are completely dependent on the primary keyword. The so-called complete dependence means that there can not be an attribute that depends only on part of the primary keyword. If it exists, then this part of the attribute and the primary keyword should be separated to form a new entity. There is an one-to-many relationship between the new entity and the original entity. To achieve differentiation, you usually need to add a column to the table to store the unique identity of each instance. In short, the second paradigm is that non-primary attributes depend on the primary keyword.

3. The third normal form (3NF)

To meet the third normal form (3NF), we must first meet the second normal form (2NF). On the basis of satisfying the second paradigm, there is no transfer function dependency, so it is the third paradigm. In short, the third paradigm is that attributes do not depend on other non-primary attributes.

Thank you for reading! This is the end of the article on "what are the four paradigms of mysql database". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.