During the interview, some interviewers will ask about the database paradigm, I have studied databases in college before, but I have not heard of the paradigm, or the teacher has mentioned it, and I have not paid attention?
There are currently six paradigms in relational databases: the first paradigm (1NF), the second paradigm (2NF), the third paradigm (3NF), the Bass-Cod paradigm (BCNF), the fourth paradigm (4NF) and the fifth paradigm (5NF, also known as the perfect paradigm).
Usually we use the first paradigm (1NF), the second paradigm (2NF), and the third paradigm (3NF), which is the "three paradigms" to be discussed in this article.
First Paradigm (1NF): Requires that each column of the database table be an indivisible atomic data item.
Illustrate:
In the table above, neither the "Family Information" nor "School Information" columns meet the atomicity requirements, so they do not meet the first paradigm, and are adjusted as follows:
It can be seen that each adjusted column is indivisible, so it satisfies the first paradigm (1NF);
Paradigm 2 (2NF): On the basis of 1NF, non-code attributes must be completely dependent on candidate codes (eliminate partial function dependence of non-primary attributes on master code on 1NF basis)
The second paradigm needs to ensure that every column in the database table is related to the primary key, not just a part of the primary key (mainly for federated primary keys).
Illustrate:
In the case shown in the above diagram, the same order may contain different products, so the primary key must be a combination of "order number" and "product number".
However, it can be found that the product quantity, product discount, and product price are all related to the "order number" and "product number", but the order amount and order time are only related to the "order number" and have nothing to do with the "product number".
This does not meet the requirements of the second paradigm, and is adjusted as follows, which needs to be divided into two tables:
Third paradigm (3NF): On the basis of 2NF, any non-main attribute does not depend on other non-main attributes (eliminating transmission dependencies on the basis of 2NF)
The third paradigm needs to ensure that each column of data in the data table is directly related to the primary key, not indirectly.
Illustrate:
In the above table, all attributes are completely dependent on the student number, so the second paradigm is met, but the "class teacher gender" and "class teacher age" directly depend on the "class teacher name".
Instead of the main key "student number", the following adjustments need to be made:
In this way, the requirements of the third paradigm are met.
ps:如果把上表中的班主任姓名改成班主任教工号可能更确切，更符合实际情况，不过只要能理解就行。
BCNF (Bovce Codd Normal Form Bacchus Paradigm)
The third paradigm of correction is to prevent one column of the primary key from depending on other columns of the primary key. When 3NF eliminates some function dependencies and transfer function dependencies on the main attribute code, it is called BCNF.
Characteristic:
1. All main attributes are completely functional dependencies on each code
2. All main attributes are completely function-dependent on each code that does not contain it
3. There are no attributes that are fully functional and dependent on any set of non-code properties
For example, the inventory table (warehouse name, administrator name, product name, quantity), the primary key is (warehouse name, administrator name, product name), which satisfies the previous 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, so BCNF is not satisfied.
4NF Fourth Paradigm
Non-primary attributes should not have multiple values. If there is a multi-value, it violates the fourth paradigm. 4NF is a restriction relational pattern that does not allow non-trivial and non-function-dependent multi-valued dependencies between properties.
For example, the user contact information table (user ID, landline, mobile phone), where the user ID is the primary key, which satisfies the BCNF, but a user may have multiple landline phones or multiple mobile phones, then this design is unreasonable and should be changed to (user ID, contact type, phone number).
Note: If only function dependencies are considered, the paradigm with the highest degree of normalization of relational patterns is BCNF. If you consider multi-value dependencies, it is 4NF.
5NF Fifth Paradigm
The fifth paradigm is the final paradigm, eliminating the connection dependency in 4NF, and the fifth paradigm needs to meet the following requirements:
1. The fourth paradigm must be met
2. Tables must be decomposable into smaller tables, unless those tables logically have the same primary key as the original table.
In general practical applications, the fifth paradigm does not have to be considered.
Transferred from:The hyperlink login is visible.
Reference:The hyperlink login is visible.
|
Posted on 3/21/2024 10:53:13 AM
|
|
|
|
