Database paradigm experiences

Paradigm description

1nf: fields in the database table are single attributes and cannot be divided. This single attribute is composed of basic types, including integer, real number, complex type, logical type, and date type.

For example, the following database tables conform to the first paradigm:

Field 1 Field 2 Field 3 field 4
Such database tables do not conform to the first paradigm:

Field 1 Field 2 Field 3 field 4 field 31 field 32
Obviously, in any current relational database management system (s), dummies cannot make databases that do not conform to the first paradigm, because the S does not allow you to divide a column in the database table into two or more columns. Therefore, it is impossible for you to create a database that does not conform to the first paradigm in the existing S.

2nf ): the database table does not have some function dependencies between non-Keyword fields and any candidate keyword fields (some function dependencies refer to the condition where some fields in the composite keywords determine non-Keyword fields ), that is, all non-Keyword fields depend entirely on any set of candidate keywords.

Assume that the course selection relation table is SS (student ID, name, age, course name, score, and credits), and the keywords are combined keywords (student ID, course name), because the following decides the relation:

(Student ID, course name) → (name, age, score, credits)

This database table does not meet the second paradigm because of the following decision relationships:

(Course name) → (credits)

(Student ID) → (name, age)

That is, fields in the combined keywords determine non-keywords.

Because 2nf is not met, this course selection relation table has the following problems:

(1) data redundancy:

The same course is selected by N students, and "Credits" are repeated for n-1 times. If the same student chooses a course, the name and age are repeated-1 time.

(2) Update exception:

If the credits of a course are adjusted, the "Credits" value of all rows in the data table must be updated. Otherwise, different credits may occur for the same course.

(3) insertion exception:

For example, if you want to open a new course, no one will take the course. In this way, the course name and credits cannot be recorded in the database because the "student ID" keyword is not yet available.

(4) Deletion exception:

Assuming that a group of students have completed their electives, These electives should be deleted from the database table. However, the course name and credit information are also deleted. Obviously, this will also cause insertion exceptions.

Change the course selection relation table ss to the following three tables:

Student: Sn (student ID, name, age );

Course: S (Course name, credits );

Course Selection relationship: SS (student ID, course name, score ).

Such database tables conform to the second paradigm, eliminating data redundancy, update exceptions, insertion exceptions, and deletion exceptions.

In addition, all database tables with single keywords comply with the second paradigm, because it is impossible to have a combination of keywords.

Third Paradigm (3nf): Based on the second paradigm, if there is no transfer function dependency for any candidate keyword segment in the data table, it complies with the third paradigm. The so-called pass function dependency means that if there is a "A →" Deciding relation, the pass function depends on. Therefore, database tables that meet the third paradigm should not have the following dependency:

Keyword field → non-Keyword field X → non-Keyword field y

Assume that the student relationship table is Sn (student ID, name, age, school [], school location, school phone number), and the keyword is single keyword "student ID ", the following decision relationships exist:

(Student ID) → (name, age, [] school [], school [] location, [] school [] Phone number)

This database complies with 2nf but does not comply with 3nf because of the following decision relationships:

(Student ID) → ([] school []) → ([] school [] location, [] school [] Phone number)

That is, there is a transfer function dependency between non-Keyword segments "[] [] "and "[] []" on the keyword segment "student ID.

It can also cause data redundancy, update exceptions, insertion exceptions, and deletion exceptions. You can analyze and learn this information on your own.

The student relationship table is divided into the following two tables:

Student: (student ID, name, age, school []);

[] School []: ([] school [], location, phone number ).

Such database tables conform to the third paradigm, eliminating data redundancy, update exceptions, insertion exceptions, and deletion exceptions.

Bao yice-ked Paradigm (NF): Based on the third paradigm, if no field in the database table is dependent on the transfer function of any candidate keyword segment, it complies with the third paradigm.
Assume that the warehouse management relationship table is ssanag (warehouse, storage item, administrator, and quantity), and one administrator only works in one warehouse. One warehouse can store multiple items. This database table has the following decision relationships:

(Warehouse, storage item) → (Administrator, quantity)

(Administrator, storage item) → (warehouse, quantity)

Therefore, both (repository, storage item) and (Administrator, storage item) are candidate Keywords of ssanag. The only non-Keyword segment in the table is the number, which conforms to the third paradigm. However, the following decision relationships exist:

(Warehouse) → (Administrator)

(Administrator) → (repository)

That is, the keyword segment determines the keyword segment, so it does not conform to the NF paradigm. It has the following exceptions:

(1) Deletion exception:

When the Warehouse is cleared, all "Storage items" and "quantity" information are deleted, and the "warehouse" and "Administrator" information are also deleted.

(2) insertion exception:

When a Warehouse does not store any items, an administrator cannot be assigned to the warehouse.

(3) Update exception:

If the repository is changed to an administrator, the administrator of all rows in the table must modify the settings.

Break down the warehouse management relationship table into two Relational Tables:

Repository management: ssanag (repository, Administrator );

Warehouse: SS (warehouse, storage item, quantity ).

Such database tables conform to the NF paradigm and eliminate deletion, insertion, and update exceptions.

Paradigm Application

Let's get a forum database step by step, with the following information:

(1) User: User Name, A, home page, phone number, and contact address

(2) post: post title, post content, reply title, reply content

For the first time, we designed the database to only exist tables:
User name a homepage phone contact address post title post content reply title reply content
This database table conforms to the first paradigm, but no set of candidate keywords can determine the entire row of the database table. The username of the unique keyword segment cannot completely determine the entire tuples. We need to add the "post" and "reply" fields to change the table:

User name a homepage phone contact address post title post content reply title reply content
In this way, the keywords (username, post, and reply) in the data table determine the entire line:

(User name, post, reply) → (A, home page, phone number, contact address, post title, post content, reply title, reply content)

However, such a design does not conform to the second paradigm because of the following decision relationships:

(User Name) → (A, home page, phone number, contact address)

(Post) → (post title, post content)

(Reply) → (reply title, reply content)

That is, some functions of non-Keyword fields depend on the candidate keyword fields. Obviously, this design will cause a large amount of data redundancy and operation exceptions.

We break down a database table into (underlined keywords ):

(1) User information: User Name, A, home page, phone number, and contact address

(2) post information: Post, title, content

(3) reply information: reply, title, content

(4) post: User Name, post

(5) reply: Post, reply

Such a design meets the requirements of the 1st, 2, 3, and NF paradigms. But is such a design the best?

Not necessarily.

We can see that there is a 1: n relationship between the "user name" and "Post" in the 4th "Post, therefore, we can merge the "post" into the "post information" of the 2nd items; the "post" and "reply" of the 5th items "reply" are also 1: N, therefore, we can merge the "reply" into the "Reply information" of the 3rd items. In this way, data redundancy can be reduced in a certain amount. The new design is as follows:

(1) User information: User Name, A, home page, phone number, and contact address

(2) post information: User Name, post, title, content

(3) reply information: Post, reply, title, content

Database Table 1 clearly meets the requirements of all paradigms;

Database Table 2 contains some functional dependencies of non-Keyword segments "title" and "content" on keyword segments "Post", that is, it does not meet the requirements of the second paradigm, however, this design does not cause data redundancy and operational exceptions;

In database table 3, some function dependencies of non-Keyword segments "title" and "content" on "reply" do not meet the requirements of the second paradigm, however, similar to database table 2, this design does not cause data redundancy and operation exceptions.

From this we can see that it is not necessary to forcibly meet the requirements of the paradigm. For a 1: n relationship, when one side is merged to the other side of N, the other side of N will no longer meet the second paradigm, but this design is better!

For the relationship: N, one side or N side cannot be merged to the other side. This will cause non-compliance with the paradigm requirements, Operation exceptions and data redundancy.
For a relationship, we can merge 1 on the left or 1 on the right to the other side. This design does not meet the requirements of the paradigm, but does not cause operation exceptions and data redundancy.

/*

 

Database Table 2 contains some functional dependencies of non-Keyword segments "title" and "content" on keyword segments "Post", that is, it does not meet the requirements of the second paradigm, however, this design does not cause data redundancy and operational exceptions;

-->

At this time, the keyword is "Post", and there is no function dependency, satisfying the second paradigm.

*/