Problems solved by the five Paradigms of database table design

The concept of database system was learned at school, and was uniformly indefinitely, and remained at the definition and certification level. Recently in the project, seriously understand the next database paradigm problem, only the subconscious understanding of its principles and applications to quickly design a reasonable table.

First, clear concepts are as follows:

The main code is also the primary key

Candidate code if the value of an attribute group in a relationship can uniquely identify a tuple, and any of its true subsets can no longer be identified, the attribute group is called a candidate code. Candidate code is not unique, the main code is one of them.

The main attribute is contained in any of the candidate keywords for the property called the main attribute

Secondly, it is also the main play, combined with examples, to explain the major paradigm of the former improvement and application scenarios.

Problems solved by paradigm in reality

1. Data redundancy

Data redundancy refers to repeated occurrences of multiple or more attributes when one or more of the attributes in a table change. That is, there is a one-to-many or many-to-many data relationship between attributes and attributes, between attributes and attribute groups, or between attribute groups and attribute groups. As the following table:

Course C Teacher T reference book B
------------------------
Physics Li Yong General Physics
Physics Li Yong Optics principle
Physics of Li Yong Physics problem sets
General physics of the physics of June
Physics Wang June Optics principle
Physical Wang June physics problem sets
Mathematical analysis of Mathematics Li Yong
Differential equation of mathematical Li Yong
Mathematics Li Yong Advanced algebra
Mathematical analysis of Mathematics Zhang Ping
Differential equation of mathematics Zhang Ping
Mathematics Zhang Ping Advanced algebra
. . .
2. Insert exception

The so-called insert exception, which refers to an attribute, especially a primary key, is empty, and cannot be inserted even though other properties have data.

Assume that the selection relationship table is Selectcourse (school number, name, age, course name, score, credit), keyword for the combination of keywords (study number, course name), namely (school number, course name) → (name, age, score, credits). At the same time there is a connection:

(course name) → (credits)

(school number) → (name, age)

For this table selectcourse, it is assumed that a new course will be opened and no one has yet been enrolled. Thus, the course name and credits cannot be recorded in the database because there is no "learning number" keyword.

3. Delete exception

The so-called Delete exception, is that for some reason, you need to delete some useless properties in the table, but also will be some other useful properties of the data also deleted.

The same is true for the above table Selectcourse, when students graduate, they need to remove all the information from the system, as well as the information of the course will be deleted.

4. Update exception

The so-called update exception, that is, if the data of a property changes, it is necessary to modify the corresponding properties of a lot of data.

Also for the above table Selectcourse, if the credit of a course is adjusted, the "credits" value of all the rows in the data table should be updated, otherwise the same course credit will appear differently.

In short, these attributes of the data between these problems, can take up too much space, affect the efficiency of the query, and also for the subsequent programming brings unnecessary trouble, Sunline also affect the integrity of the database. For this reason, it is necessary to optimize the paradigm of these tables.

Two or six description of the large paradigm

The overall paradigm optimization diagram:

Elimination of determining factors

| 1NF

Non-code non-trivial | ↓ elimination of partial function dependency of non-principal attribute pair code

function dependency

| 2NF

| ↓ Elimination of the transfer function dependency of the non-principal attribute pair code

| 3NF

| ↓ eliminate the partial and transitive function dependencies of the main attribute pair code

| BCNF

| ↓ Elimination of non-trivial and non-function dependent multi-valued dependencies

| 4NF

| ↓ eliminate connection dependencies that are not implied by the candidate code

| 5NF

1.1NF (first paradigm)

The so-called first paradigm is that each attribute in a table is non-divided, that is, it cannot be divided into two columns. This is the basis for a relational database.

2.2NF (second normal)

The so-called second paradigm is that all non-principal attributes are completely dependent on the keyword. From this definition, it can be seen that the second paradigm does not have a partial dependency of the non-primary attribute on some candidate keywords, although there is a transitive dependency between the non-primary attributes.

The following is an example of an optimization of the second paradigm:

Assume that the selection relationship table is Selectcourse (school number, name, age, course name, score, credits), keyword for the combination of keywords (study number, course name), because of the following decision relationship:

(School number, course name) → (name, age, score, credits)

This database table does not meet the second normal form because of the following decision relationship:

(course name) → (credits)

(school number) → (name, age)

That is, the presence of a field in the combo key determines the non-keyword situation.

Because it does not conform to 2NF, the following questions exist for this class selection relationship:

(1) Data redundancy:

The same course by N students elective, "credit" repeated n-1 times, the same student elective m courses, name and age repeated m-1 times.

(2) Update exception:

If the credit of a course is adjusted, the "credits" value of all the rows in the data sheet should be updated, otherwise the same course credit will be different.

(3) Insert exception:

Suppose a new course is to be opened and no one has yet been enrolled. Thus, the course name and credits cannot be recorded in the database because there is no "learning number" keyword.

(4) Delete exception:

Assuming that a group of students has completed elective courses, these elective records should be removed from the database table. At the same time, however, the course name and credit information were also removed. Obviously, this can also lead to an insertion exception.

Change the course of the elective selectcourse to the following three tables: