Database Paradigm __ Database

 Brief Introduction

The status of database paradigm in database design has been very ambiguous, textbooks in the database paradigm is given an academic definition, but the application of the paradigm in practical application is not very optimistic, This article will be a simple language and a simple database demo will be an inconsistent paradigm of the database step-by-step from the first paradigm to achieve the four paradigm.

The goal of the paradigm

There are many benefits to applying the database paradigm, but the most important benefit boils down to three points:

1. Reduce data redundancy (this is the main benefit, other benefits are the result of this)

2. Eliminate exceptions (insert exception, update exception, delete exception)

3. Make the data organization more harmonious ...

But the sword is double-edged, the application of the database paradigm also brings drawbacks, which will be said later in the article.

what is a paradigm

Simply put, the paradigm is to eliminate redundant data, so that data in the database better organization, to make disk space more efficient use of a standardized standard, to meet the high level of the paradigm of the prerequisite is to meet the low class paradigm. (for example, meet 2NF must meet 1NF)

DEMO

Let's start with a table that is not normalized, as follows:

First on the table to do a simple explanation, EmployeeID is the staff Id,departmentname is the department name, job representative post, Jobdescription is the job description, skill staff skills, Departmentdescription is the department description, address is the employee residence

first form of the table (1NF)

R∈1NF if all of the properties of a relational schema R are basic data items that are not divided.

Simply put, the first paradigm is that every attribute is not divided. Failure to conform to the first paradigm cannot be called a relational database. For the above table, it is not difficult to see that address can be divided, such as "Beijing xx Road xx Community xx", obviously does not conform to the first normal form, the application of the first paradigm you need to decompose this property to another table, as follows:

second normal form to the table (2NF)

If the relational schema is R∈1NF, and each non-primary attribute is fully functional dependent on the code of R, then R∈2NF

Simply put, the attributes in a table must be completely dependent on all primary keys, not some primary keys. So the table with one primary key, if it conforms to the first paradigm, must be the second normal form. This is done to further reduce the insert exception and update exception. In the table above, Departmentdescription is determined by the primary key Departmentname, but not by the primary key EmployeeID, so departmentdescription relies only on one of the two primary keys. So to Departmentdescription is partial dependence on the primary key, apply the second normal form to the following table:

third normal form of table (3NF)

If there is no such code x, attribute group Y and r<u,f> attribute Z (zy) in the relational mode, the X→Y,Y→Z is established, which is called R<U,F>∈3NF.

In short, the third paradigm is to eliminate the dependencies between the keywords in the database, in the table above the second paradigm, you can see that jobdescription (Job responsibility) is determined by the job (position), then jobdescription depends on the job, It can be seen that this does not conform to the third normal form, and the diagram after the third paradigm of the table is:

In the table above, there is no problem with the dependency of the database properties, so it conforms to the third normal form

BC Paradigm on a table (BCNF)

Set the relational pattern R<U,F>∈1NF, if for each function of R depends on X→y, if Y does not belong to X, then x must contain the candidate code, then r∈bcnf.

To put it simply, the BC Paradigm is a special case on the basis of the third paradigm, where each table has only one candidate key (the value of each row in a database is different, which is called a candidate key), as can be seen in the nonf table of the third normal form above. Every employee's email is unique (do two people use the same email??) ), this table does not conform to the BC Paradigm, and the graph after the BC normalization is:

fourth normal form for a table (4NF)

Relational schema R<U,F>∈1NF, if X contains candidate code for each nontrivial multivalued dependency x→→y (yx) for R, then R∈4NF.

Simply put, the fourth paradigm is to eliminate multivalued dependencies in a table, which means that you can reduce the effort to maintain data consistency. For the above BC Paradigm table, for employees of the skill, two possible values are "C#,sql,javascript" and "C#,uml,ruby", you can see that the database attributes have multiple values, which may cause the database content inconsistency, such as the first value is written "C #" while the second value is "C#.net", the solution is to put multi-valued attributes into a new table, the fourth normalized diagram is as follows:

The possible values for the skill table are:

Summary

The above for the database paradigm decomposition process is not difficult to see, the application of a higher level of paradigm, the table more. There are many problems with tables:

1 query to connect multiple tables, increase the complexity of the query

2 query needs to connect multiple tables, reduce the performance of database query

The current situation, disk space costs can be negligible, so the data redundancy caused by the problem is not the reason to apply the database paradigm.

Therefore, not the higher the application paradigm, the better, depends on the actual situation. The third paradigm has largely reduced data redundancy and reduced the number of insertions, updates, and deletions. My personal view is that most of the cases applied to the third paradigm are sufficient, and in some cases the second paradigm is also possible.