First paradigm, second normal form, third normal form, BC Paradigm __ Database

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 ... 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's description, address is the first form of employee residence (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 paradigm, the application of the first paradigm you need to decompose this property into another table, as follows:

Second, each record must be identified by using a unique primary key.

There are repeating groups, which also do not satisfy the first normal form, because the lack of a unique identifier can be changed to

second Normal form (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 paradigm (3NF)

If there is no such code x, attribute group Y and non-primary attribute Z (zy) in the relational schema r<u,f> R, the X→Y,Y→Z is established, which is called R<u,f>∈3nf r∈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 (BCNF)

Set the relational pattern R<U,F>∈1NF R∈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: