Paradigm NF (normal forms)

Reference Http://www.cnblogs.com/chaowei119/articles/148157.html

In the design of relational databases, how can we evaluate the merits of a relational model design?

This standard is the schema paradigm NF (normal forms ).

Relationship Pattern paradigm

1. 1nf)

If the attribute values of each link R in the relational mode R are atomic values that cannot be divided, r is the first paradigm.

(First Normal Form) mode.

1nf is the minimum condition for the relational model.

In general, the tables to be designed should be at least pulled from the first paradigm, as long as you do not have to add two attributes to a field.

2. 2nf)

In the first paradigm, data redundancy and exceptions usually exist.One-step standardization.

If the relational mode is 1nf, and each non-primary attribute fully function depends on the candidate key, it is called R.Is the second paradigm.

3. Third Paradigm (3nf)

Based on 1nf, each non-primary attribute does not pass a candidate key dependent on R. Therefore, r is the third paradigm.Mode.

Example

Relationship Model of a student's Elective Course
Student_no, course_no, grade (score), credit (credit)

Undoubtedly, in this relational mode, each attribute value is an atomic value that cannot be divided.First paradigm!

The second paradigm defines that each non-primary attribute fully function depends on the candidate key.

The candidate keys in this link mode are undoubtedly student_no and course_no,

However, credit is fully dependent on course_no and partly dependent on (student_no, course_no ).The full function dependency mentioned above.

So this is not the second paradigm.

How does it become the second paradigm? Break it down! How can we break it down?

It is to split the one that is fully dependent and partially dependent. The primary key is, of course, the one that is fully dependent on.

In this exampleCourse_no and credit are separated to form a relational model. student_no, course_no, and grade are used as a relational model.Link mode.

This is the real 2nf.

For example, each attribute of S1 (SNO, sname, DNO, dname, location) represents the student ID, name, and location respectively.Department, Department name, Department address.

Sno is the primary key and can determine any non-primary attribute. That is to say, the student ID of a person can determine his/herThe name of the system,You can determine the name of his system and the address of his system.

But is the system address directly determined by SnO?
Sno determines DNO, and DNO determinesLocation, so the sno determines the locationIt is a transmission dependency, so the relational model S1 can only be a second paradigm, not a third paradigm.

The third paradigm is S1 (SNO, sname, DNO) S2 (DNO, dname, location). In S1, DNOIt is a foreign key and uses it to connect two relationships.Mode.

Finally, we will talk about bcnf, which is based on 1nf. Each attribute (Note: it contains the primary attribute and non-primary attributes.) Do not pass candidates that depend on R.Then, r is the bcnf mode.

Example (book_no, book_name, author), wow! Such a streamlined relational model,

First, it must be 1nf. Then book_no can decide book_name and author,That is to say, all non-primary attributes are completely

If the dependency (not the pass dependency) is on the primary attribute, the above link mode is 3nf.

The following shows whether it complies with bcnf. book_no determines the title, and the author and the title determine book_no.

It can be seen that the transfer of book_name depends on

Book_name and author. Therefore, it is not bcnf.

Well, we finally use the experience we just heard: Generally, the most common relational model is 1nf. If the relational model is

The mode is completely dependent.

2nf. If the transfer dependency does not exist (generally, the link mode is quite simplified at this time ),

3nf, that is very abnormal.

And the points are basically beyond the scope of bcnf, haha !!