Database design Third Paradigm---123 Paradigm Introduction __ Database

Database design paradigm and its significance and deficiencies

The database design paradigm is the norm which the database design needs to meet, the normalization of the database is the way to optimize the table structure and to organize the data into the table, so that the data is clearer and simpler. In practice, a database is usually divided into two or more tables and the relationships between tables are defined to achieve data isolation. Adding, deleting, and modifying a field only needs to be done in one table, and can then be passed through the defined relationships to the remaining tables in the database (similar to the meaning of layered ideas). This allows us to eliminate many errors or opportunities for spam and reduce the amount of effort necessary to update the information.

At present, there are six kinds of paradigms: first normal form, second normal form, third normal form, BC Paradigm, fourth normal form and fifth normal form. Meet the minimum requirements of the first normal form, referred to as 1NF. On the basis of the first paradigm to further meet some of the requirements of the second normal form, referred to as 2NF. The rest and so forth

Things often have multiple facets, and the design paradigm also brings some trouble: difficult to operate, because you need to contact multiple tables to get the data you need, and the higher the paradigm, the worse it will be. So the use of the high paradigm needs to weigh the pros and cons, generally in the project, the use of the third paradigm is sufficient, good performance and easy to manage data.

Second, we will give an example of database design three paradigms

The example uses "the school computer room fee system" the "Student Information table", "Student up and down machine record table" partial fields

1, the first normal form 1NF

Definition: A field in a database table is a single property and cannot be divided.

Simply put, each attribute is an atomic term, indivisible.

1NF is the minimum condition that the relational pattern should have, if the database design cannot satisfy the first normal form, it is not called the relational database. In other words, as long as the relational database, it must meet the first paradigm.

Let's take a look at a table 1-1 that does not conform to 1NF.

Cardno	Studentno	Studentname	Sex	Department	Cardcash	Userid	UserLevel	Time
001	021101	Xiao ming	Man	School of Education, Department of psychology, Class 1	100	Operator	Operator	2011/10/03,09:00

The reason this table does not conform to 1NF is because the department and Time fields can be divided, so you should change to table 1-2:

Cardno	Studentno	Studentname	Sex	Academy	Major	Class	Cardcash	Userid	UserLevel	Date	Time
001	021101	Xiao ming	Man	College of Education	Psychology Department	1	100	Operator	Operator	2011/10/03	09:00

2, the second normal form 2NF

Definition: A partial function dependency of a non-critical field on any candidate key field is not present in the database table, which conforms to the second normal form.

"Note: What is functional dependence, see Baidu Encyclopedia (http://baike.baidu.com/view/40008.htm)."

If the value of one field a in a table is determined by the value of another field or a set of field B, it is called a function dependent on B. 》

2NF can reduce insertion exceptions, remove exceptions, and modify exceptions.

To put it simply, on the one hand, the second paradigm must satisfy the first paradigm, otherwise there is no need to talk about the second paradigm.

On the other hand, when the non primary key information in a table is not determined by the entire primary key function, there is usually a violation of 2NF when there is a dependency on the part of the table that is not a primary key or is dependent on a part of the primary key.

Let's look at the top 1NF table 1-2.

Cardno	Studentno	Studentname	Sex	Academy	Major	Class	Cardcash	Userid	UserLevel	Date	Time
001	021101	Xiao ming	Man	College of Education	Psychology Department	1	100	Operator	Operator	2011/10/03	09:00

We see that in this table, through Cardno and Studentno, we can determine studentname,sex,academy,major,class,cardcash,userid,date