Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "what is the six paradigms of database table design". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Preface

Six paradigms of database design

In order to establish a database with less redundancy and reasonable structure, certain rules must be followed when designing the database. In a relational database, this rule is called a paradigm. Paradigm is a summary that meets a certain design requirement. If you want to design a well-structured relational database, you must meet a certain paradigm.

There are three most common design paradigms in actual development:

1. The first normal form (1NF) has no repeating columns (ensure that each column remains atomic)

The first paradigm is the most basic paradigm. If all the field values in the database table are indecomposable atomic values, the database table satisfies the first normal form. Databases that do not satisfy the first normal form (1NF) are not relational databases.

The reasonable follow of the first paradigm needs to be determined according to the actual needs of the system. For example, some database systems need to use the "address" attribute, the "address" attribute should be directly designed as a field of a database table. However, if the system often accesses the "city" part of the "address" attribute, it is necessary to re-split the "address" attribute into provinces, cities, detailed addresses, and other parts for storage. this will be very convenient when operating on a certain part of the address. Only in this way can the design satisfy the first normal form of the database, as shown in the following table.

The user information shown in the above table follows the requirements of the first paradigm, which makes it very convenient for users to classify cities and improve the performance of the database.

2. The second normal form (2NF) attribute is completely dependent on the primary key (eliminating some subfunction dependencies and ensuring that each column in the table is related to the primary key)

The second paradigm goes a step further on the basis of the first paradigm. The second paradigm needs to ensure that each column in the database table is related to the primary key, not only to some part of the primary key (mainly for the federated primary key). That is to say, in a database table, only one kind of data can be saved in a table, and multiple data can not be saved in the same database table. The second paradigm requires that each instance or row in the database table must be uniquely localized. To achieve differentiation, you usually need to add a column to the table to store the unique identity of each instance. This unique attribute column is called the primary keyword or primary key, primary code.

For example, to design an order information table, because there may be multiple items in the order, use the order number and item number as the joint primary key of the database table, as shown in the following table.

Order information table

This creates a problem: the order number and item number are used as joint primary keys in this table. In this way, the information such as commodity name, unit, commodity price and so on in the table is not related to the primary key of the table, but only related to the commodity number. So it violates the design principle of the second paradigm here.

Problems that may arise if they do not conform to the second paradigm:

(1) data redundancy: for orders placed by the same customer, the units and contact information are redundant.

(2) abnormal update: if the price of a commodity is adjusted, all the price lists of the goods that place an order in the table need to be adjusted, otherwise the price of the same commodity will be inconsistent.

(3) insertion exception: if the merchant adds a new item, such information cannot be inserted into the table because no one has bought it without an order number.

(4) deletion exception: if the customer has completed the order, or if the customer returns and cancels the order, you need to delete the order number, which will lead to the deletion of the product information, which makes the problem even bigger.

It would be perfect to split this order information table, separate the product information into another table, and separate the order item table into another table.

This design reduces the redundancy of the database to a great extent. If you want to get the product information of the order, you can use the item number to query it in the commodity information table.

3. The third normal form (3NF) attribute does not depend on other non-primary attributes (eliminate transitive dependencies and ensure that each column is directly related to the primary key column, not indirectly)

The third paradigm needs to ensure that each column of data in the data table is directly related to the primary key, not indirectly. If there is no transfer function dependency of non-key fields on any candidate key fields in the data table, it conforms to the third normal form. In short, the third paradigm is that attributes do not depend on other non-primary attributes. The so-called transfer function dependency means that if there is a determinant relationship of "A → B → C", then the C transfer function depends on A.

Therefore, database tables that meet the third normal form should not have the following dependencies:

Key field → non-key field x → non-key field y

For example, when designing an order data table, the customer number can be used as a foreign key to establish a corresponding relationship with the order table. You cannot add fields about other customer information (such as name, company, etc.) to the order table. The design shown in the following two tables is a database table that satisfies the third normal form.

In this way, when querying the order information, the customer number can be used to refer to the records in the customer information table, and it is not necessary to enter the contents of the customer information in the order information table many times, which reduces the data redundancy.

Find another picture for your reference:

4. BCNF Boyce-Cod normal form (for a column related to a column in the compound primary key, but independent of other primary keys)

The so-called BCNF means to further eliminate some of the functional and transitive dependencies of the principal attribute on the code on the basis of the third normal form. BCNF needs to be 3NF compliant, and the primary attribute does not depend on the primary attribute.

Suppose the warehouse management relationship table is StorehouseManage (warehouse ID, storage items ID, administrator ID, quantity), and there is an administrator who works in only one warehouse; a warehouse can store multiple items. The following decision relationships exist in this database table:

(warehouse ID, store goods ID) → (administrator ID, quantity)

(administrator ID, store goods ID) → (warehouse ID, quantity)

Therefore, (warehouse ID, stored item ID) and (administrator ID, stored item ID) are candidate keywords for StorehouseManage. The only non-keyword field in the table is quantity, which is in line with the third normal form. However, due to the following decisions:

(warehouse ID) → (administrator ID)

(administrator ID) → (warehouse ID)

That is, there is a situation where the key field determines the key field, so it does not conform to the BCNF paradigm. It will have the following exception:

(1) Delete an exception:

When the warehouse is emptied, all "stored item ID" and "quantity" information is deleted, "warehouse ID" and "administrator ID" information is also deleted.

(2) insert exception:

An administrator cannot be assigned to the warehouse when no items are stored in the warehouse.

(3) update exception:

If the warehouse changes the administrator, the administrator ID for all rows in the table will be modified.

Decompose the warehouse management relationship table into two relationship tables:

Warehouse management: StorehouseManage (warehouse ID, administrator ID)

Warehouse: Storehouse (warehouse ID, store goods ID, quantity).

Such database tables conform to the BCNF paradigm, eliminating delete exceptions, insert exceptions, and update exceptions.

For example, there is such a spare parts management table WPE (WNO,PNO,ENO,QNT), in which WNO represents warehouse number, PNO represents parts number, ENO represents employee number, and QNT represents quantity.

There are the following constraints:

(1) there are multiple employees in a warehouse

(2) an employee works in only one warehouse.

(3) A special person is responsible for one type of accessories in each warehouse, but one person can manage several kinds of accessories.

(4) parts of the same type can be placed in several warehouses.

By analyzing the functional dependencies in the table, you can get:

(1) ENO- > WNO

(2) (WNO,PNO)-> QNT

(3) (WNO,PNO)-> ENO

(4) (ENO,PNO)-> QNT

As you can see, the candidate keys are: (ENO,PNO); (WNO,PNO). Therefore, ENO,PNO,WNO is the primary attribute and QNT is the non-primary attribute. Obviously, non-primary attributes are directly dependent on candidate keys. So this table satisfies the third normal form.

Let's take a look at the main attribute: (WNO,PNO)-> ENO;ENO- > WNO. Obviously, WNO has a transitive dependence on the candidate key (WNO,PNO), so it does not conform to BCNF.

The solution to this problem is to split into two tables: the management table EP (ENO,PNO,QNT) and the worksheet EW (ENO,WNO). But doing so will result in the loss of functional dependencies (WNO,PNO)-> ENO.

Although it does not satisfy the BCNF, it can also lead to some redundancy and consistency problems. However, shredding a table into tables that satisfy BCNF may lose some functional dependencies. Therefore, in general, relational tables are not required to meet BCNF.

5. 4NF (fourth paradigm)

For the fourth normal form, theoretically speaking, the relational pattern R ∈ 1NF, if for every nontrivial multivalued dependency X →→ Y (Y does not belong to X) of R, X contains candidate codes, then R ∈ 4NF. 4NF is a multi-valued dependency that restricts the attributes of relational schemas to be non-trivial and non-functional dependencies. Obviously, if a relational schema is 4NF, it must be BCNF.

That is, when non-primary attributes in a table are independent of each other (3NF), these non-primary attributes should not have multiple values. If there is multiple value, it violates the fourth paradigm.

There is such a user contact form Telephone (CUSTOMERID,PHONE,CELL). CUSTOMERID is the fixed phone of the user ID,PHONE is the user, and CELL is the mobile phone of the user.

Originally, this is a very simple third paradigm table. The primary key is CUSTOMERID and there is no transitive dependency. But in some cases, such a watch is unreasonable. For example, the user has two fixed phones and two mobile phones. At this point, the table is represented as follows:

CUSTOMERID PHONE CELL

1000 8828-1234 149088888888

1000 8838-1234 149099999999

Because PHONE and CELL are independent of each other, some users have two or more values. At this point, this table violates the fourth paradigm.

In this case, the design of this table will bring a lot of maintenance trouble. For example, if the user abandons the landline in the first line and the mobile phone in the second line, will the two lines merge? Wait

The solution to the problem is to design a new table NEW_PHONE (CUSTOMERID,NUMBER,TYPE). In this way, multiple phone numbers of different types can be processed for each user without violating the fourth paradigm.

Obviously, the scope of application of the fourth paradigm is relatively small, because only in some special cases, it is necessary to consider standardizing the table to the fourth normal form. Therefore, in practical application, the table is generally not required to meet the fourth normal form.

6. 5NF (fifth paradigm)

The fifth paradigm (5NF): is the final paradigm. Eliminates the connection dependency in 4NF.

The fifth paradigm has the following requirements:

(1) the fourth paradigm must be satisfied.

(2) tables must be decomposed into smaller tables unless those tables logically have the same primary key as the original table.

The fifth paradigm is further standardized on the basis of the fourth paradigm. The fourth paradigm deals with independent multi-valued cases, while the fifth paradigm deals with interdependent multi-valued cases.

There is a sales information table SALES (SALEPERSON,VENDOR,PRODUCT). SALEPERSON represents the salesperson, VENDOR represents the supplier, and PRODUCT represents the product.

In some cases, there is some redundancy in this table. Tables can be decomposed into PERSON_ VENDOR table (SALEPERSON,VENDOR), PERSON_ product table (SALEPERSON,PRODUCT), VENDOR ­_ PRODICT table (VENDOR,PRODUCT)

This is the end of the content of "what is the six paradigms of database table design". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.