My understanding of relational database design paradigm

My understanding of relational database design paradigm

The so-called paradigm, is the standard of relational database relational model normalization, from the standardized relaxed to strict, respectively for different paradigms, usually used in the first normal form, the second normal form, the third normal form and the BC paradigm. The paradigm is based on the function dependencies.

function dependencies

Definition: With relation mode R (U), X and Y are subsets of the attribute set U, and the function dependency is a proposition of the form x→y, with T[x]=s[x] Yun T[y]=s[y for the two tuples in any R, and then the FD X→Y is established in the relational mode R (U). X→Y read as ' x function determines y ', or ' Y function depends on X '.

In layman's terms, if the value of a field y in a table is determined by the value of another field or a set of field x, it is called the Y function dependent on x.

function dependencies should be determined by understanding data items and enterprise rules, and functional dependencies based on the contents of the table may be incorrect.

First Paradigm (1NF)

Definition: The attribute of each relation r of the fruit relational mode R is an irreducible data item, then it is called R as the mode of the first normal form. Simply put, each attribute is an atomic term, indivisible. 1NF is the minimum condition that the relational pattern should have, if the database design cannot satisfy the first normal form, it is not called the relational database. The relationship normalization of relational database design is based on the 1NF.

Second Normal form (2NF)

Definition: If the relational schema R is 1NF and each non-primary attribute full function relies on the candidate key, then it is called R as the second normal form. In short, the second normal form satisfies the following conditions: first, to satisfy the first normal form, and then each non-primary attribute to be fully functional dependent on the candidate key or the primary key. That is, each non-primary attribute is determined by the entire primary key function, not by a part of the primary key.

As an example:

The primary key of a stock day table is the stock code and the date of the transaction. Non-primary attributes include closing and turnover, which are determined by the primary key, which is the stock code and the transaction date function, and the individual stock code or transaction date cannot function to determine these non primary attributes. If the table has a non-primary attribute stock abbreviation, the stock abbreviation can be determined by the stock code, so that the stock short name this non-primary attribute is not a complete function depends on the candidate key, so the design is not satisfied with the second normal form.

Third Paradigm (3NF)

Definition: If the relational schema R is 2NF and all the u,f attributes in the relational schema R (s) do not have a transitive dependency on any candidate keywords, then the relation R is called the third normal form. To put it simply, the third normal form satisfies the following conditions: first, to satisfy the second normal form, followed by the absence of functional dependencies between the non-primary attributes. Because the second normal form is satisfied, it means that each of the non-primary attributes is dependent on the primary key. If there is a function dependency between the non-primary attributes, there is a transitive dependency, which does not satisfy the third normal form.

As an example:

In the stock basic situation table, the primary key is the stock code, has the non main attribute to belong to the first class industry and belongs to two class industry. According to the business rules, the two-tier industry can determine the level of industry, which means that there is a relationship: the stock code function to decide to belong to the two-class industry, the two-class industry function decided to belong to the first class industry, which formed a transfer dependency, this design is not in line with the

However, in practical use, for the convenience of query and use, sometimes will violate the third paradigm. For example, if you do not have the attributes of the first level of industry, you need to query the relevant stocks of the first level of industry, you need to query the use of functions to from the two-tier industry to generate a function of the industry, the use of performance will be affected. Therefore, it is usually added to the attributes of the first level of the industry.

BC Paradigm (BCNF)

The BC Paradigm is an enhanced version of the third paradigm, but it is also said to have evolved directly from 1NF, where each attribute, including primary or non-primary, relies entirely on candidate keys, and there is no transitive dependency.