Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the knowledge of the six paradigms of the database". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what are the six paradigms of the database?"

1. The role of database paradigm

Database paradigm is a design concept introduced to solve the problems of data redundancy, update exception, insert exception and delete exception in relational database. To put it simply, the database paradigm can avoid data redundancy, reduce the storage space of the database, and reduce the cost of maintaining data integrity. It is not only one of the core technologies of relational database, but also the necessary knowledge for database developers.

2. Introduction of database paradigm classification.

Paradigm is to evaluate the degree of database schema standardization from low to high: 1NF, 2NF, 3Nf, BCNF, 4NF, 5NF.

2.1 1NF first Paradigm

The atomicity constraint of attributes is emphasized, and attributes are required to be atomic and can not be decomposed again.

For example:

Student form (student number, name, age, gender, address). Address can be subdivided into countries, provinces, cities, urban areas, streets, then the model does not reach the first paradigm.

The first paradigm has some problems: large redundancy, inconsistency of modification operation, data insertion exception and data deletion exception.

2.2 2NF second normal form

The second paradigm emphasizes the uniqueness constraint of the record, the data table must have a primary key, and the columns that are not contained in the primary key must be completely dependent on the primary key, not just a part of the primary key.

For example:

Version table (version code, version name, product code, product name), where the primary key is (version code, product code). In this scenario, the database design does not conform to the second paradigm, because the product name only depends on the product code. There is partial dependence. Therefore, in order to satisfy the second normal form, it can be transformed into two tables: version table (version code, product code) and product table (product code, product name).

2.3 3NF third Paradigm

The third paradigm emphasizes the constraint of data attribute redundancy, that is, non-primary key columns must be directly dependent on the primary key. In other words, the transfer function dependence of non-primary attributes on the code is eliminated.

For example:

Order table (order code, customer code, customer name), in which the primary key is (order code). In this scenario, the customer code and customer name are completely dependent on the primary key, so they conform to the second paradigm, but the customer name depends on the customer code, thus indirectly depends on the primary key, so it can not satisfy the third paradigm. If you want to meet the third paradigm, you need to split into two tables: the order table (order code, customer code) and the customer table (customer code, customer name).

Description: the mode of 3NF definitely satisfies 2NF. Two important reasons for redundancy and exceptions are partial dependency and transitive dependency. In 3NF mode, there are no partial functional dependencies and transfer functional dependencies of non-principal attributes on codes, so the performance is good. 1NF and 2NF are generally not suitable as database schemas and usually need to be converted to 3NF or higher-level normal form, which is called relational schema normalization processing.

2.4 BCNF (Bovce Codd Normal Form Bacon Paradigm)

The modified third normal form is to prevent one column of the primary key from relying on other columns of the primary key. When 3NF eliminates some of the functional and transitive functional dependencies of the main attribute on the code, it is called BCNF.

Properties:

1. All main attributes are completely functional dependent on each code.

2. All main attributes are also completely functional dependent on every code that does not contain it.

3. There are no attributes that are functionally dependent and non-code dependent on any set of attributes.

For example: inventory table (warehouse name, administrator name, commodity name, quantity), the primary key is (warehouse name, administrator name, commodity name), which meets the first three paradigms, but there is a dependency between the warehouse name and the administrator name, so deleting a warehouse will cause the administrator to be deleted, which does not meet BCNF.

2.5 4NF fourth normal form

Non-primary attributes should not have multiple values. If there are multiple values, it violates the fourth paradigm. 4NF is a multi-valued dependency that restricts the attributes of the relational schema and does not allow non-trivial and non-functional dependencies.

For example: user contact table (user id, landline, mobile phone), in which user id is the primary key, which satisfies BCNF, but a user may have multiple fixed phones or multiple mobile phones, then this design is unreasonable and should be changed to (user id, contact type, phone number).

Note: if only functional dependencies are considered, the paradigm with the highest degree of normalization of relational schema is BCNF;. If multivalued dependencies are considered, it is 4NF.

2.6 5NF Fifth Paradigm

The fifth paradigm belongs to the final paradigm, which eliminates the join dependency in 4NF. The fifth paradigm needs to meet 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.

In general, it is not necessary to consider the fifth paradigm in practical application.

At this point, I believe you have a deeper understanding of "what is the knowledge of the six paradigms of the database". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.