Database three-paradigm Analysis

Many rules need to be considered during the database establishment process. A good database design not only saves data storage space, facilitates the development of database applications, but also ensures data integrity. Defining a good database requires so many rules, it is not a headache for us to design. Therefore, in order to establish a good database, we must ensure database standardization during development-three major paradigms.

Database three paradigm

In order to establish a database with low redundancy and reasonable structure, certain rules must be followed during design. In relational databases, these rules are referred to as the database paradigm. Dr E. F. codd initially defined three levels of normalization. These paradigms are:

1st NF)

2nd NF)

3rd NF)

There is a progressive relationship between the three paradigms. The High-level paradigm should be designed on the basis of the underlying paradigm. During database design, we should follow the three-tier paradigm step by step. A good database must follow the three paradigm, but the database designed by the three paradigm is not necessarily the best, however, the paradigm is a table structure with minimal redundancy.

First paradigm:

The most basic paradigm ensures that each column is the smallest unit that cannot be further divided. It is relatively simple to design.

The design of the first paradigm needs to be determined according to the design requirements of the system. For example, if the "time" attribute is required in some database systems, you can directly design the "time" attribute as a field in a database table, however, if the system frequently accesses the "year" section in the "time" attribute, the "time" attribute will be re-split into years, months, days, hours, and other parts for storage, so that the operation on a part of the address will be very.

Student ID	Course name	Name	Age	Score	Credits
1	Mathematics	Han	23	77	4
2	English	Zhang	22	70	3

Second paradigm:

First, we must satisfy the first paradigm. The database table does not have a part of the dependency of non-Keyword fields on any candidate key fields (mainly for the joint primary key). Make sure that each column in the database table is related to the primary key.
For example, assume that the following table is a course selection relation table, where the student ID and Course name are combined keywords.
Although the above table looks neat, it does not meet the second paradigm because of the following dependency:
(Course name) --> (credits)
(Student ID) --> (name, age)
That is, there is a partial dependency between a non-Keyword segment and a certain key segment.
Because 2nf is not met, this course selection relation table may cause data redundancy and update exceptions during use.
When designing, we should try to avoid using composite keywords, because all database tables with keywords comply with the second paradigm, because it is impossible to have composite keywords.

Third paradigm:

First, it must conform to the second paradigm. The data table does not have a function dependency on any candidate key fields for non-Keyword fields. That is, there is no deciding relation between "A → B → C. Each column of data is directly related to the primary key, but not indirectly.
Assume that the student relationship table is (student ID, name, age, school, school location, school phone number), and the keyword is single keyword "student ID", because the following decision relationship exists:
(Student ID) → (name, age, school, school location, school phone number)
This database complies with 2nf but does not comply with 3nf because of the following decision relationships:
(Student ID) → (school location, school phone number)
That is, the transfer function dependency of the non-Keyword section "school location" and "college phone" on the keyword section "student ID" exists.
The student relationship table should be divided into the following two tables:
Student (student ID, name, age, school );
School: (school, location, phone number ).

Bcnf)

Based on the third paradigm, if no field in the database table is dependent on the transfer function of any candidate keyword segment, it is in line with the BC paradigm. It mainly considers the relationship between two primary keys in the primary key combination. After the design of the third paradigm, there is basically no dependency on the transmission of candidate keywords from non-primary attributes. However, the relationship between candidate keywords is not considered in the third paradigm.

Assume that the warehouse management relationship table is (warehouse ID, storage item ID, administrator ID, and quantity), and one administrator only works in one warehouse. One warehouse can store multiple items. This database table has the following decision relationships:
(Repository ID, storage item ID) → (administrator ID, quantity)
(Administrator ID, storage item ID) → (warehouse ID, quantity)
Therefore, both (warehouse ID, storage item ID) and (administrator ID, storage item ID) are candidate keywords in the warehouse management relation table. The unique non-Keyword segment in the table is the quantity, it conforms to the third paradigm. However, the following decision relationships exist:
(Repository ID) → (administrator ID)
(Administrator ID) → (repository ID)
That is, the keyword segment determines the keyword segment, so it does not conform to the bcnf paradigm.
It has the following exceptions:
(1) Deletion exception:
When the respiratory is cleared, all the "Storage item ID" and "quantity" information are deleted, and the "warehouse ID" and "administrator ID" information are also deleted.
(2) insertion exception:
When a Warehouse does not store any items, an administrator cannot be assigned to the warehouse.
(3) Update exception:
If the repository is changed to an administrator, the administrator ID of all rows in the table must be modified.
Break down the warehouse management relationship table into two Relational Tables:
Warehouse Management: storehousemanage (warehouse ID, administrator ID );
Repository: storehouse (warehouse ID, storage item ID, quantity ).
Such database tables conform to the bcnf paradigm and eliminate deletion, insertion, and update exceptions.
The three paradigm is the initial database design rule. It aims to avoid data redundancy. However, the more application paradigms, the more tables are divided, this will inevitably lead to data redundancy, so the paradigm only sets a standard, but it is not the most accurate. Some redundancy is also required when using the paradigm.

Summary:

In fact, simply put, the three paradigm is the most basic rule in database design. The first paradigm does not need to be considered too much, because relational databases have already helped us control it. The second paradigm is to have a primary key, other attributes depend on this primary key, and the timing should be minimized to avoid the primary key of the combination. The primary key combination often violates the second paradigm. The third paradigm is that there cannot be redundancy. A table can only have a primary key, the attribute that depends on the primary key. The foreign key cannot contain the non-primary key attribute of the foreign key table.