Database-Paradigm (normal Form, NF)

Code

设K为R<U,F>中的属性或属性组合。若K    U，  则K称为R的侯选码，或候选键（Candidate Key）。     若候选码多于一个，则选定其中的一个做为主码，或主键（Primary Key）。

Primary and non-primary properties
A property contained in any candidate code, called the main attribute (Prime attribute)
Properties that are not included in any code are called non-primary properties (Nonprime attribute) or non-code attributes (Non-key attribute)
All yards
Entire attribute group is code, called Full Code (ALL-KEY)

[例2]    关系模式S(Sno,Sdept,Sage)，单个属性Sno是码，    SC（Sno，Cno，Grade）中，（Sno，Cno）是码[例3]       关系模式R（P，W，A）       P：演奏者     W：作品    A：听众       一个演奏者可以演奏多个作品       某一作品可被多个演奏者演奏       听众可以欣赏不同演奏者的不同作品       码为(P，W，A)，即All-Key  

key）也称外码如在SC（Sno，Cno，Grade）中，Sno不是码，但Sno是关系模式S（Sno，Sdept，Sage）的码，则Sno是关系模式SC的外部码 主码与外部码一起提供了表示关系间联系的手段

Paradigm (normal Form, NF)

A paradigm is a collection of relational patterns that conform to a certain level
Relationships in a relational database must meet certain requirements. Different paradigms to meet different levels of requirements
Types of Paradigms:
First Paradigm (1NF)
Second Paradigm (2NF)
Third Paradigm (3NF)
BC Paradigm (BCNF)
Paradigm Four (4NF)
Model Five (5NF)
There is a connection between the various paradigms:

A relational mode R is the nth paradigm, which can be précis-writers to R∈NNF.
The relational pattern of a low-level paradigm, which can be transformed into a set of relational patterns of several high-level paradigms through pattern decomposition, is called normalization

2NF

Definition of 1NF
If all the properties of a relational mode R are non-R∈1NF basic data items, the
The first paradigm is the minimum requirement for the relationship model. Database schemas that do not meet the first paradigm are not called relational database schemas
But the relationship model satisfying the first paradigm is not necessarily a good relational model

[例4]S-L-C(Sno, Sdept, Sloc, Cno, Grade)    Sloc为学生住处，假设每个系的学生住在同一个地方函数依赖包括：           (Sno, Cno) F  Grade           Sno → Sdept           (Sno, Cno)  P  Sdept           Sno → Sloc           P   Sloc           Sdept → Sloc (语义：每个系的学生住在同一个地方)

S-l-c's code is (Sno, Cno)
S-l-c satisfies the first paradigm.
Non-primary properties sdept and SLOC partial functions depend on code (SNO, Cno)
(1) Insert exception
(2) Delete exception
(3) Large redundancy of data
(4) Modify complex
S-l-c is not a good relational model.

-l  -c  is decomposed into two relational patterns to eliminate these partial functions dependent SC (Sno, Cno, Grade) s-l  (Sno, sdept, Sloc)  

2NF的定义    定义6.6  若R∈1NF，且每一个非主属性完全函数依赖于码，则R∈2NF。    例：S-L-C(Sno, Sdept, Sloc, Cno, Grade) ∈1NF           S-L-C(Sno, Sdept, Sloc, Cno, Grade) ∈2NF                2NF            S-L2NF

The decomposition of a 1NF relationship into a number of 2NF relationships can alleviate some of the problems in the original 1NF relationship, such as insertion anomaly, deletion anomaly, large redundancy of data, and complicated modification.

Decomposing a 1NF relationship into a number of 2NF relationships does not completely eliminate the various anomalies and data redundancy in the relational schema.
Algorithms for decomposing into 2NF pattern sets
Set the relationship mode R (U), the primary key is the FD x→z on the W,r, and Z is the non-primary attribute and X? W, then W→z is a local dependency. The R should now be decomposed into two modes
R1 (XZ), the primary key is x;
R2 (Y), where y=u-z, the primary key is still W,
The foreign key is x (REFERENCES R1).
The connection between the foreign key and the primary key can be re-obtained from R1 and R2.
If R1 and R2 are not 2NF, repeat the process until each relational pattern in the database schema is 2NF.
Consider: A relational pattern of R (A,B,C,D), assuming that the relationship has the following function dependencies: A→b,a→c,c→d, then the relationship pattern is the first paradigm? Why?

该关系模式属于2NF，因为键是A，不存在部分函数依赖。但存在非主属性对键的传递函数依赖，故不属于3NF

3NF

3NF的定义    定义6.7  关系模式R<U，F> 中若不存在这样的码X、属性组Y及非主属性Z（Z ∈ Y）, 使得X→Y，Y→Z成立，     Y → X，则称R<U，F> ∈ 3NF。若R∈3NF，则每一个非主属性既不部分依赖于码也不传递依赖于码。(可以证明)

Theorem: If R is a 3NF mode, then R is also 2NF mode.
Proof: As long as the existence of local dependencies in the model implies transitive dependence. Set A is a non-principal property of R, K is a candidate key for R, and K→a is a local dependency. So there must be some K ' in R? K, with K ' →a established. Because A is a non-primary property, A∩kk ' =φ. From K '? K, known as K ' →k, but K→k ' established. Thus from K→k ' and K ' →a know k→a is a transitive dependence.
Local dependencies and transitive dependencies are two important reasons for patterns to produce redundancy and exceptions. Because there is no local dependency and transitive dependency of the non-primary attribute on the candidate key in 3NF mode, a large part of the storage exception is eliminated and has better performance.

The 3NF also has the following equivalent definition, as described below.
Define 6.7.1 set F is the FD set of the relational mode R, if the X contains code for each non-trivial FD x→y in F, or if each property of Y is a primary attribute, then R is the 3NF pattern.
This definition shows that if the non-trivial fd x→y y is the main attribute, then x→y does not violate the 3NF condition; if Y is a non-primary attribute and X does not contain a code, there must be a candidate code W has w→x, there is a w→y is a transitive dependency, that is, R is not 3NF mode.

例：2NF关系模式S-L(Sno, Sdept, Sloc)中函数依赖：          Sno→Sdept          Sdept → Sno          Sdept→Sloc          可得：                   Sno→Sloc，即S-L中存在非主属性对码的传递函数依         赖，S-L3NF

A 2NF relationship is decomposed into several 3NF relationships, which can solve some problems, such as inserting exception, deleting exception, large redundancy of data and complicated modification in the original 2NF relationship.

After decomposing a 2NF relationship into multiple 3NF relationships, it is still not possible to completely eliminate the various exception conditions and data redundancy in the relational schema.
Algorithms for decomposing into 3NF pattern sets
Set the relationship mode R (U), and the primary key is the FD x→z on the w,r. And Z is a non-primary attribute, Z? X,x is not a candidate key, so w→z is a transitive dependency. The r should be decomposed into two modes at this time:
R1 (XZ), the primary key is x;
R2 (Y), where y=u-z, the primary key is still W,
The foreign key is x (REFERENCES R1). Using the foreign key and the primary key matching mechanism, the R1 and R2 can be re-obtained by connecting the R.
If R1 and R2 are not 3NF, repeat the process until each relational pattern in the database schema is 3NF.

Comprehensive examples

There is a relationship mode R (Employee name, project name, project fee, department name, department manager), if each employee can participate in a number of projects, each to participate in a project, there is a project fee; Each project belongs to only one department management; Each department has only one manager.
The basic function of relational mode R is dependent on FD and candidate key.
There are 3 basic FD for R:
(Employee name, project name) → Project fee
Project name → department name
Department name → Department manager
The candidate keys for the relationship mode R are: (Employee name, project name)
is r 2NF mode? If not, break R into a 2NF schema set.
R has the following two FD:
(Employee name, project name) → (department name, department manager)
Project name → (department name, department manager)
Because a non-primary attribute group (department name, department manager) has a local function dependency on the candidate key (employee name, project name), R is not 2NF. R should be decomposed into the following two modes:
R1 (employee name, project name, Project fee) R2 (project name, department name, department manager) R1 and R2 are all 2NF.
is the R1,R2 3NF mode? If not, break it down into a 3NF pattern set.
The R1 is already 3NF.
In R2, there is a non-main attribute ' department manager ' to the candidate key ' project name ' transfer function dependency, so r is not 3NF. R2 should be further decomposed into the following two modes:
R21 (project name, department name) R22 (department name, department manager)
Both R21 and R22 are 3NF. Finally, R is decomposed into {r1,r21,r22}.

Database-Paradigm (normal Form, NF)