Shulou(Shulou.com)06/01 Report--

This article mainly explains the use of the three paradigms of Mysql database design, the content is clear, interested friends can learn, I believe it will be helpful after reading.

Three paradigms

1NF: fields cannot be divided

2NF: there is a primary key, and non-primary key fields depend on the primary key

3NF: non-primary key fields cannot depend on each other

Explanation:

1NF: atomic fields can no longer be divided, otherwise they are not relational databases

2NF: uniqueness A table shows only one thing

3NF: each column is directly related to the primary key, and there is no transitive dependency

The first normal form (1NF)

That is, the column of the table is atomic and can not be decomposed, that is, the information of the column can not be decomposed, as long as the database is a relational database (mysql/oracle/db2/informix/sysbase/sql server), it automatically satisfies 1NF. Each column of a database table is an indivisible atomic data item, not a collection, array, record, or other non-atomic data item. If an attribute in an entity has more than one value, it must be split into different attributes. It is commonly understood that only one piece of information is stored in a field.

Relational database: mysql/oracle/db2/informix/sysbase/sql server non-relational database: (characteristic: object-oriented or collection) NoSql database: MongoDB/redis (characterized by document-oriented)

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 a database table must be uniquely localized. In order to achieve the distinction, we usually need to design a primary key to implement (the primary key here does not contain business logic).

That is, to meet the premise of the first paradigm, when there are multiple primary keys, the situation that does not conform to the second paradigm will occur. For example, if there are two primary keys, such an attribute cannot exist, and it depends only on one of the primary keys, which is not in line with the second normal form. The popular understanding is that any field depends on only the same field in the table. (when it comes to splitting tables)

Look at the following list of students' course choices:

Student number course scores course credits 10001 Mathematics 100610001 Chinese 90210001 English 85310002 Mathematics 90610003 Mathematics 99610004 Chinese 892

The primary key in the table is (student number, course), which can be expressed as (student number, course)-> (grades, course credits), indicating that all non-primary key columns (grades, course credits) depend on the primary key (student number, course). However, there is another dependency in the table: (courses)-> (course credits). In this way, the non-primary key column 'course credits' depend on part of the primary key column 'course', so the above table does not satisfy the second paradigm.

Let's break it down into the following two tables:

Student elective schedule:

Student number course score 10001 Mathematics 10010001 Chinese 9010001 English 8510002 Mathematics 9010003 Mathematics 9910004 Chinese 89

Course information table:

Course credits Mathematics 6 Chinese 3 English 2

Then in the above two tables, the primary key of the student elective table is (student number, course), the primary key of the course information table is (course), and all the non-primary key columns in the table are completely dependent on the primary key. It not only accords with the second paradigm, but also accords with the third paradigm.

Let's take a look at this student information table:

Student number name gender class teacher 10001 Zhang three male Class one Xiao Wang 10002 Li four male Class one Xiao Wang 10003 Wang five male Class two Xiao Li 10004 Zhang Xiao San male Class two Xiao Li

In the above table, the primary key is: (student number), all fields (name, gender, class, head teacher) are dependent on the primary key (student number), there is no partial dependence on the primary key. So it satisfies the second paradigm.

The third normal form (3NF)

To meet the third normal form (3NF), we must first meet the second normal form (2NF). In short, the third normal form (3NF) requires that a database table does not contain non-primary key fields that are already contained in other tables. That is, if the information of the table can be deduced, it should not be designed to store a separate field (foreign key join should be used as foreign key join as possible). In many cases, we tend to divide a table into multiple tables in order to satisfy the third paradigm.

That is to say, if an attribute depends on other non-primary key attributes, and other non-primary key attributes depend on the primary key, then this attribute is indirectly dependent on the primary key, which is called transitive dependence on the primary attribute. The popular explanation is that at most two layers of the same type of information are stored in a table.

Anti-three paradigms

A database without redundancy is not necessarily the best database. Sometimes in order to improve operational efficiency and read performance, it is necessary to lower the standard of paradigm and retain redundant data properly. The specific approach is to follow the third normal form in the conceptual data model design, and the work of reducing the normal form standard is considered in the physical data model design. To reduce the normal form is to increase the fields, reduce the relevance of the query, and improve the query efficiency, because the proportion of queries in the database operation is much larger than that of DML. However, the anti-paradigm must be moderate and be adjusted on the basis of meeting the three paradigms.

After reading the above content, do you have a better understanding of the usage of the three paradigms of Mysql database design? if you want to learn more, you are welcome to follow the industry information channel.

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.