"Reprint" Database Paradigm those things

Database Paradigm Those things

Introduction

The status of database paradigm in database design has been very ambiguous, textbooks in the database paradigm has given the academic definition, but the practical application of the paradigm of the application is not very optimistic, This article uses a simple language and a simple database demo to implement a non-conforming database step from the first paradigm to the Quaternary 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, and other benefits are included)

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 will also bring drawbacks, which will be said later in the article.

what is a paradigm

Simply put, the paradigm is a standardized standard for eliminating redundant data deduplication, which allows for better organization of data within the database and for more efficient use of disk space, and the prerequisites for meeting high-level paradigms are to meet low-level paradigms. (such as satisfying 2nf must meet 1NF)

DEMO

Let's start with a non-normalized table that looks like this:

First to make a brief description of the table, EmployeeID is the employee Id,departmentname is the department name, job representative post, Jobdescription is job description, skill is the staff skills, Departmentdescription is a department note, address is an employee's address

First normal form (1NF) for a table

R∈1NF If all the properties of a relational mode R are non-divided base data items.

Simply put, the first paradigm is that every attribute is no longer divided. A non-conforming first paradigm is not called a relational database. For the above table, it is not difficult to see that address can be divided, such as "xx Road xx District xx", which obviously does not conform to the first paradigm, the application of the first paradigm needs to decompose this attribute to another table, as follows:

Second normal form for a table (2NF)

If the relational schema is R∈1NF, and each non-principal attribute is Full function Dependency The code for R, then R∈2NF

Simply put, the attributes in the table must depend entirely on the primary key, not the partial primary key. So there is only one primary key table if it conforms to the first paradigm, it must be the second paradigm. This is done to further reduce insert exceptions and update exceptions. In the table above, Departmentdescription is determined by the primary key Departmentname, but is not determined by the primary key EmployeeID, so departmentdescription only relies on one of the two primary keys, Therefore, to departmentdescription the primary key is partially dependent on its application of the second paradigm as follows:

Third normal form for a table (3NF)

Relationship Mode No such code x, attribute group y, and non-primary attribute Z (z? r<u,f>) exist in the Y), making x→y,y→z, established, is called R<U,F>∈3NF.

Simply put, the third paradigm is to eliminate the dependencies between the keywords in the database, in the second normalized table above, it can be seen that jobdescription (job responsibility) is determined by the job (POST), then jobdescription depends on the job, It can be seen that this does not conform to the third paradigm, and the diagram after the third paradigm of the table is:

In the table above, there is no problem that database properties depend on each other, so it conforms to the third paradigm

BC Paradigm for tables (BCNF)

Set Relationship Mode R<U,F>∈1NF, if each function of R is dependent on x→y, if Y is not X, then x must contain candidate code, then r∈bcnf.

To put it simply, the BC Paradigm is a special case on the basis of the third paradigm, where there is only one candidate key in each table (which can be called a candidate key in a database where the values of each row are different), as can be seen in the nonf table of the third normal form above. Each employee's email is unique (is two people using the same email??). ), the table does not conform to the BC Paradigm, and its BC normalization diagram is:

Fourth normal form for a table (4NF)

Relationship Mode R<U,F>∈1NF, if for each non-trivial multivalued dependency of R x→→y (Y? x), X contains the candidate code, then r∈4nf.

To put it simply, the fourth paradigm is to eliminate multivalued dependencies in tables, which means that the work of maintaining data consistency can be reduced. For the above BC normalization table, for employee skill, the two possible values are "C#,sql,javascript" and "C#,uml,ruby", it can be seen that there are multiple values for this database attribute, which may result in inconsistent database content issues, such as the first value is written "C #", and the second value writes "C#.net", the workaround is to put the multivalued attribute into a new table, then the fourth normalized diagram is as follows:

The possible values for the skill table are:

Summary

It is not difficult to see the process of decomposition of the database paradigm, the higher the application paradigm registration, the more tables. There are many problems with the table:

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

2 query needs to connect multiple tables, reducing database query performance

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

Therefore, it is not the case that the higher the application paradigm, the better, depends on the actual situation. The third paradigm has largely reduced data redundancy and reduced the insertion exception, update exception, and deletion exception. My personal view is that the majority of cases are sufficient to apply to the third paradigm, and in some cases the second paradigm is also possible.

Because I am still in the primary stage of the database research, so the above if there are improper, but also look at the master feel free ...

by Careyson

"Reprint" Database Paradigm those things