Analysis of the second paradigm of "database Paradigm"

2, the second paradigm 2NF

Definition: A non-critical field in a database table does not have a partial function dependency on any of the candidate key fields, which conforms to the second paradigm.

Simply put, no field redundancy.

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

If the value of a 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 insert exceptions, delete exceptions, and modify exceptions.

Simply put, 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, that is, when there is a part of the table that is not a primary key or that depends on the part of the primary key, the 2NF is usually violated .

We're going to look at the above. table 1-2 to meet 1NF

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

We see, in this table, through cardno studentnostudentname ,sex,academy,< Span lang= "en-US" >major,class,card< Span lang= "en-US" >cash,userid,date< Span lang= "ZH-CN" >,timecardnostudentno

However, we found that cardCash is not entirely dependent on Cardno and Studentno, andonly through Cardno can you determine the cardCash , because a card will definitely have the amount of the card inside. This creates a partial dependency. In this case, the second paradigm is not satisfied .

Modified to:

Let's look at another example, and the student-to-computer record table will be more obvious. Table 2-1

Cardno

Studentno

Studentname

Sex

Department

Major

Class

Ondate

OnTime

Offdate

Offtime

Consumetime

Consumemoney

001

0211

Xiao ming

Man

Faculty of Education

Department of Psychology

1

2011/10/14

09:00

2011/10/14

10:00

1

2

As we can see, in this table,studentname,sex,department,Major,class are directly dependent on Studentno, and do not depend on other fields in the table , such a design does not conform to 2NF non-primary key information when it is not determined by the entire primary key function.

We can optimize the 1-2 and the 2-1 to:

3-1

studentno	cardno	userid	userlevel	date	time
021101	001	Operator	Operator	2011/10/03	09:00

3-2

Cardno	Cardcash
001	98

3-3

cardno	ondate	ontime	offdate	offtime	consumetime	consumemoney
001	2011/10/14	09:00	2011/10/14	10:00	1	2

3-4

studentno	studentname	sex	academy	major	class
021101	Xiao ming	Man	Faculty of Education	Department of Psychology	1

----------------------------------------

Second Paradigm

The data for each row can only be related to one of the columns, that is, a single row of data does one thing. As long as there is data duplication in the data column, you need to split the table.

A person to order a few rooms at the same time, will come out an order number of data, this way contacts are duplicated, resulting in data redundancy. We should tear him apart.

This enables a single piece of data to do one thing without complex relational logic. Update maintenance of table data is also easier to operate.

----------------------------------------

2 . Second paradigm (ensure that each column in the table is related to the primary key)

The second paradigm is based on the first paradigm in a more advanced layer. The second paradigm needs to ensure that each column in a database table is related to the primary key, not just one part of the primary key (primarily for the Federated primary key). In other words, in a database table, only one data can be saved in a table, and multiple data cannot be saved in the same database table.

For example, to design an order information table, because there may be more than one item in the order, the order number and the product number are used as the federated primary key for the database table, as shown in the following table.

order Information Form

This creates a problem: The table is the Union primary key with the order number and the product number. In this table, the product name, unit, commodity price and other information is not related to the table's primary key, but only related to the product number. So this violates the design principle of the second paradigm.

And if the Order Information table is split, the product information is separated into another table, the Order Item table is also separated into another table, it is perfect. as shown below.

This design, to a large extent, reduces the redundancy of the database. If you want to get the product information for an order, use the item number to inquire in the product information sheet.

Analysis of the second paradigm of "database Paradigm"