Second, understanding database three paradigm, understanding database paradigm

Second, understanding database three paradigm, understanding database paradigm

Understanding the database paradigm is of great help to design a rational database. Based on my understanding of the database paradigm and specific examples, this article attempts to understand the three paradigm of the database in plain and easy-to-understand languages.

First Paradigm (Columns cannot be split)

If all the field values in the database are atomic values that cannot be split, the first paradigm is satisfied. To put it simply No tablesSo relational databases all meet the first paradigm. Example omitted.

Second Paradigm (completely dependent)

The second paradigm is based on the first paradigm, Make sure that all non-primary keys depend on the primary key. For the Union primary keys, non-primary keys must be fully dependent.(That is, it cannot depend on a part of the Union primary key ).

For example, to design an order information table, because there may be multiple commodities in the order, order numbers and commodity numbers should be used as the joint primary keys of the database table, as shown in the following table.

Order info table

In this case, a problem occurs: This table uses the order number and product number as the joint primary key. In this way, the product name, unit, and price information in the table are not completely related to the primary key of the table, but only related to the product number. This violates the design principles of the second paradigm.

The third paradigm (eliminating the dependence on key-based transmission of non-primary attributes) The third paradigm ensures that all non-primary keys are Directly related to the primary key rather than indirectly related. See the following table:

Order No.	Customer ID	CUSTOMER NAME	Contact info
001	1	Zhang San	1234567
002	2	Wang Mazi	1222222
003	3	Li Si	7654321

As shown in, a certain order has only one customer, and the order number is used as the primary key. Each attribute in the Table above depends on the order number, which means that when the order number is equal, other attributes will also be the same. Then there is an order number to determine the customer number, and the customer number can determine the customer name, which produces a transfer relationship, so it does not meet the three paradigm. To meet the three paradigms, we should split them into two tables, one is the order information table and the other is the customer information table. Generally, we design a database to meet the third paradigm.

Database three paradigm

Introduction to several design paradigms of relational databases
1. 1NF)
In any relational database, the first paradigm (1NF) is the basic requirement for the relational model. databases that do not meet the first paradigm (1NF) are not relational databases.
The first paradigm (1NF) means that each column in the database table is an inseparable basic data item. The same Column cannot contain multiple values, that is, an attribute in an object cannot have multiple values or duplicate attributes. If duplicate attributes exist, you may need to define a new object. A new object consists of duplicate attributes. The new object has one-to-multiple relationships with the original object. In the first paradigm (1NF), each row of the table contains only information of one instance. For example, for the employee information table in Figure 3-2, the employee information cannot be displayed in one column or two or more columns in one column; each row in the employee information table only represents the information of one employee. The information of one employee appears only once in the table. In short, the first paradigm is a non-repeated column.

2. 2NF)
The second Paradigm (2NF) is established on the basis of the first paradigm (1NF), that is, to satisfy the second Paradigm (2NF) must satisfy the first paradigm (1NF) first ). The second Paradigm (2NF) requires that each instance or row in the database table be able to be distinguished by a unique region. To implement differentiation, you usually need to add a column to the table to store the unique identifier of each instance. 3-2 The employee ID (emp_id) column is added to the employee information table. Because each employee's employee ID is unique, each employee can be uniquely distinguished. This unique attribute column is called as the primary keyword, primary key, and primary code.
The second Paradigm (2NF) requires that the attributes of an object fully depend on the primary keyword. The so-called full dependency refers to the fact that there cannot be an attribute that only depends on a part of the primary keyword. If so, this attribute and this part of the primary keyword should be separated to form a new entity, the relationship between the new object and the original object is one-to-multiple. To implement differentiation, you usually need to add a column to the table to store the unique identifier of each instance. In short, the second paradigm is that non-primary attributes are not partially dependent on primary keywords.

3. Third Paradigm (3NF)
The third paradigm (3NF) must satisfy the second Paradigm (2NF) first ). In short, the third paradigm (3NF) requires that a database table do not contain information about non-primary keywords already contained in other tables. For example, there is a department information table, where each department has a department ID (dept_id), department name, Department profile, and other information. After listing the Department numbers in the employee information table in Figure 3-2, you cannot add the Department name, Department profile, and other information related to the department to the employee information table. If the department information table does not exist, it should also be constructed based on the third paradigm (3NF), otherwise there will be a large amount of data redundancy. In short, the third paradigm is that attributes do not depend on other non-primary attributes.

Database three paradigm

A general understanding of the three paradigms is of great benefit to database design. In database design, in order to better apply the three paradigms, the three paradigms must be understood in a general sense (the general understanding is sufficient, not the most scientific and accurate understanding ):

1 paradigm: 1NF is an atomic constraint on attributes. Attributes must be atomic and cannot be decomposed;

Second paradigm: 2NF is the uniqueness constraint on the record and requires that the record have a unique identifier, that is, the uniqueness of the object;

Third paradigm: 3NF is a constraint on field redundancy, that is, any field cannot be derived from other fields, and it requires that the fields are not redundant.

No redundant database can be designed. However, databases without redundancy may not be the best. Sometimes, to improve operational efficiency, we must lower the paradigm standard and properly retain redundant data. The specific approach is to follow the third paradigm in conceptual data model design, and to reduce the paradigm standard to be considered in physical data model design. Reducing the paradigm is to add fields and allow redundancy.