Understanding of the relationship pattern design paradigm

The so-called paradigm is the standard for standardization of relational database relationship models, from the standard to the strict standard, respectively
It is a different paradigm. The first paradigm, the second paradigm, the third paradigm, and the BC paradigm are usually used. The paradigm is the establishment
Based on function dependencies.
Function dependency
Definition: relational mode R (u) is set. X and Y are the subsets of the property set U. function dependency is a proposition in the form of X → y,
For the two tuples T and S in any R, t [x] = s [x] contains t [y] = s [Y], therefore, fd x → y becomes in the relational mode R (u ).
Li. X → Y: The 'x function determines y', or the 'Y function depends on x '.
In general, if the value of Y in a table is determined by another field or the value of X in a group of fields
The y function depends on X.
Function dependencies are determined by understanding data items and enterprise rules. functions derived from the table content
The dependency may be incorrect.
1nf)
Definition: If the attribute of each relational R in the relational mode R is an inseparable data item, r is the model of the first paradigm.
.
In short, each attribute is an atomic item and cannot be separated.
1nf is the minimum condition that the relational model should possess. If the database design cannot meet the first paradigm, it is not called
Is a relational database. The relationship normalization of relational database design research is based on 1nf.
2nf)
Definition: If the relational mode R is 1nf and each non-primary attribute fully function depends on the candidate key, r is
Second paradigm.
Simply put, the second paradigm must meet the following conditions: first, the first paradigm must be met, and then each non-primary attribute must be completed.
Full function dependency and candidate keys, or primary keys. That is to say, each non-primary attribute is determined by the entire primary key function.
But not part of the primary key.
For example:
The primary key of a stock statement is the stock code and the transaction date. Non-primary attributes include closing price and transaction volume
And so on, are determined by the primary key, that is, the stock code and the trading date function.
The function cannot determine these non-primary attributes. If this table has a non-primary attribute stock abbreviation, the stock abbreviation can be
The stock code is determined by the function. In this way, the non-primary attribute of the stock is not completely dependent on the candidate key.
The sample design does not meet the second paradigm.
3nf)
Definition: If the relational mode R is 2nf and all non-primary attributes in the relational mode R (u, f) are critical to any candidate
If there is no transfer dependency, the relation R belongs to the third paradigm.
Simply put, the third paradigm must meet the following conditions: first, it must meet the second paradigm, and second, it does not exist between non-primary attributes.
In function dependencies. Because the second paradigm is satisfied, it means that each non-primary attribute function depends on the primary key. If not
If there is a function dependency between features, there will be a transfer dependency, which will not satisfy the third paradigm.
For example:
In the basic stock information table, the primary key is the stock code, which has the primary and secondary industries of non-primary attributes. Root
According to business rules, the second-level industry can function to determine its level-1 industry, which indicates that there is such a relationship:
The stock code function determines the level-2 industry, and the level-2 industry function determines the level-1 industry.
Delivery dependency. This design does not conform to the third paradigm.
However, in practice, for the convenience of query and use, sometimes it violates the third paradigm. For example
To query the attributes of a level-1 industry, you need to query the relevant stocks of the level-1 industry. to query the attributes of a level-1 industry, use the Function
The performance of function generation in the industry is affected. Therefore, the primary industry is usually added
.
BC Paradigm (bcnf)
BC paradigm is the enhanced version of the third paradigm, but some people say that it has developed directly from 1nf, that is, each attribute, packet
Both primary and non-primary attributes depend on the candidate keys, and the dependency is not transmitted.
This article is from 51cto. com technical blog