1nf 2nf 3nf bcnf

1. Relationship

Bcnf belongs to 3nf, 3nf belongs to 2nf, and 2nf belongs to 1nf.

 

Ii. Definitions and Examples

1nf:All attributes cannot be further divided.

Example of 1nf:

The table with two fields is as follows,

Surname, name age

Zhang, 3, 20

Li, 421

The "Last Name, name" field can be divided into the last name and name, so it does not belong to 1nf.

 

To conform to 1nf:

The "Last Name and name" field is divided into two fields:

Last name age

Zhang San 20

Li Si 21

 

2nf:1nf is met, and all non-candidate codes fully depend on the master code.

Example of 2nf non-conformity:

Student ID, course score, course code

001 language 80 01

002 language 90 01

 

The primary attribute is (student ID, course). You can see that (student ID, course) determines the score, that is, the score depends on (student ID, course), which is expressed as (student ID, course)-> score, therefore, you can also enter the course code (student ID, course)-> course code. However, there is another relationship. The course code is determined by the course code, that is, the course code. The so-called non-primary attribute "course code" does not depend entirely on the primary attribute (student ID, course ), but partly depends on the primary attribute. That is to say, although the primary attribute (student ID, course) determines the course code, its subset of courses can also determine the score, that is, the course-> course code. Therefore, it does not belong to 2nf.

 

Modify to 2nf:

Divided into two tables,

Student ID course score

001 language 80

002 language 90

 

Course code

Language 01

Set the primary key to student ID or ID number. At this point:

If the student ID is the primary key, the student ID is displayed, and the student ID is displayed;

If the ID card is a primary key, then ID card-> Student ID, ID card-> score;

 

3nf:2nf-compliant, eliminating the function dependency on code passing for the fat pigs?

In 2nf, only the dependency between the non-primary attribute and the primary attribute is taken into account, but the dependency between the non-primary attribute is not taken into account. The example in 2nf is used as an example:

Student ID, course score

001 language 80 B

002 language 90

The primary attribute is (student ID, course ). Assume that the value of 80 is B and the value of 90 is C:

(Student ID, course)-> score, score-> score. Therefore, there is a dependency transmission relationship. 3nf is used to eliminate dependency transmission caused by dependency between non-primary attributes.

 

Split the table into two tables:

Student ID course score

001 language 80

002 language 90

 

Score

80 B

90

 

Bcnf: 3nf compliant, eliminating the part of the primary attribute code and passing function dependencies

Student ID score

001 80 B

002 90

The primary attribute is (student ID, score) or (student ID, score), with (student ID, score)-> score, with (student ID, score)-> score, score-> score, the score belongs to the primary attribute, but there is a transfer relationship.

Split the table into two tables:

 

Student ID score

001 80

002 90

 

Score

80 B

90