X-Dragon cloud (X-Dragon cloud) database paradigm

Brief Introduction: the database paradigm has always been ambiguous in database design. in textbooks, the database paradigm has been defined academic, but the application of the paradigm in practical applications is not optimistic, this article uses a simple language and a simple database DEMO to implement a database that does not conform to the paradigm step by step from the first paradigm to the fourth paradigm. View of Paradigm

Brief Introduction: the database paradigm has always been ambiguous in database design. in textbooks, the database paradigm has been defined academic, but the application of the paradigm in practical applications is not optimistic, this article uses a simple language and a simple database DEMO to implement a database that does not conform to the paradigm step by step from the first paradigm to the fourth paradigm. View of Paradigm

Introduction

The database paradigm has always been ambiguous in database design. textbooks provide academic definitions for the database paradigm, but the application of the paradigm in practical application is not optimistic, this article uses a simple language and a simple database DEMO to implement a database that does not conform to the paradigm step by step from the first paradigm to the fourth paradigm.

Goal of the paradigm

The application database paradigm can bring many benefits, but the most important benefits are as follows:

1. reduce data redundancy (this is the main benefit, and other benefits are included here)

2. Eliminate exceptions (insert exceptions, update exceptions, and deletion exceptions)

3. Make data organization more harmonious...

However, the sword is a dual-blade, and the application database paradigm also brings about drawbacks. This will be discussed later in the article.

What is paradigm

To put it simply, the paradigm is a standardized standard to eliminate duplicate data and reduce redundant data, so that data in the database can be better organized and disk space can be used more effectively, A prerequisite for meeting a high-level paradigm is to meet the low-level paradigm. (For example, 2nf must satisfy 1nf)

DEMO

Let's start with a non-paradigm table. The table is as follows:

First, let's give a brief description of the table. The employee id is the employee id, The departmentName is the department name, the job represents the position, the jobDescription is the job description, the skill is the employee skill, the departmentDescription is the department description, and the address

Perform the first paradigm (1NF) on the table)

If all attributes of a relational mode R are basic data items that cannot be divided, rε 1NF.

Simply put, the first paradigm means that every attribute cannot be further divided. Relational databases cannot be considered as non-conforming to the first paradigm. For the above table, it is not difficult to see that the Address can be further divided. For example, "No. XX, XX community, XX Road, Beijing" obviously does not conform to the first paradigm, to apply the first paradigm, you need to break this attribute into another table, as shown below:

2NF)

If the relational mode rε 1NF, and each non-primary attribute hasFull function dependencyIn the R code, then R is 2NF

Simply put, the attribute in a table must be completely dependent on all primary keys, rather than some primary keys. Therefore, if only one primary key table conforms to the first paradigm, it must be the second paradigm. The purpose is to further reduce insertion and update exceptions. In the preceding table, departmentDescription is determined by the primary key DepartmentName, but not by the primary key EmployeeID. Therefore, departmentDescription only depends on one of the two primary keys. Therefore, departmentDescription is partially dependent on the primary key, the second paradigm of its application is as follows:

Perform the third paradigm (3NF) on the table)

Link ModeR If such code X, attribute group Y, and non-master attribute Z (Z? Y), so that X → Y, Y → Z, is true, it is called R In 3NF.

Simply put, the third paradigm aims to eliminate the dependency between keywords in the database. In the above table, we can see that jobDescription is a job) as determined, jobDescription depends on the job. It can be seen that this is not in line with the third paradigm. The relationship diagram after the third paradigm is:

As shown in the preceding table, database attributes are not dependent on each other, so they conform to the third paradigm.

Perform the BC Paradigm (BCNF) on the table)

SetLink ModeR ε 1NF. If each function in R depends on X → Y, and Y does not belong to X, X must contain a candidate code, rε BCNF.

Simply put, the bc paradigm is a special case based on the third paradigm. Each table has only one candidate key (the values of each row in a database are different, it can be called a candidate key). In the noNf table of the third paradigm above, we can see that every employee's email is unique (do two people use the same email ??) Then, this table does not conform to the bc paradigm, and the Relationship Diagram after the bc paradigm is:

Perform the fourth paradigm (4NF) on the table)

Link ModeR ε 1NF. If every non-trivial multi-value of R is dependent on X → Y (Y? X), X contains the candidate code, then R is 4NF.

Simply put, the fourth paradigm is to eliminate the multi-value dependency in the table, that is, to reduce the effort to maintain data consistency. For the above bc paradigm table, two possible values for employee skill are "C #, SQL, javascript" and "C #, UML, Ruby". We can see that, this database attribute has multiple values, which may result in inconsistent database content. For example, the first value is "C #", the second value is written as "C #. ", the solution is to put the multi-value attribute into a new table, then the diagram after the fourth paradigm is as follows:

For the skill table, the possible values are:

Summary

The above process of decomposing the database paradigm shows that the higher the application paradigm level, the more tables there are. Multiple tables cause many problems:

1. Multiple tables need to be connected during query, increasing the query complexity.

2. Multiple tables need to be connected during query, reducing the database query performance.

In the current situation, the cost of disk space is negligible, so the problems caused by data redundancy are not the reason for the application database paradigm.

Therefore, the higher the application paradigm, the better. It depends on the actual situation. The third paradigm has greatly reduced data redundancy and reduced insertion exceptions, update exceptions, and deletion exceptions. In my personal opinion, it is sufficient to apply the third paradigm in most cases. In some cases, the second paradigm is also acceptable.