Database and database Learning

Database and database Learning
Code

Set K to the attribute or Attribute combination in R <U, F>. If k u is used, K is called the R Candidate code or the Candidate Key ). If there are more than one candidate code, select one of them as the master code or Primary Key ).

Primary and non-primary attributes
Attribute contained in any candidate code, called Prime attribute)
Attributes not contained in any code are called Non-primary attributes or Non-key attributes)
Full code
The entire Attribute Group is a code, called a full-key)

[Example 2] link Mode S (Sno, Sdept, Sage). A single property Sno is a code, SC (Sno, Cno, Grade), (Sno, Cno) yescode [Example 3] relational mode R (P, W, A) P: Player W: Work: one listener can play multiple works. A single piece of work can be played by multiple performers. The audience can enjoy different pieces of work (P, W, A), that is, All-Key.

In relational mode R, attribute or attribute group X is not the R code, but X is the code of another relational mode. X is the R's external code (Foreign key) it is also called an external code. For example, in SC (Sno, Cno, Grade), Sno is not a code, but Sno is a code of the relational mode S (Sno, Sdept, Sage, sno is the external code of SC in relational mode. The primary code and the external code provide a means to indicate the link.

Normal Form, NF)

A paradigm is a set of relational patterns at a certain level.
The relationship in the relational database must meet certain requirements. Different paradigms meet different requirements
Paradigm types:
1NF)
2NF)
3NF)
BC Paradigm (BCNF)
4NF)
5NF)
There are links between various paradigms:

The R of a relational model is the nth paradigm, which can be abbreviated as rε nNF.
A relational model of a lower-level paradigm can be converted into a set of Relational Models of several higher-level paradigms through Mode Decomposition. This process is called standardization.

2NF

1NF Definition
If all the attributes of a relational mode R are basic data items that cannot be divided, then rε 1NF
The first paradigm is the minimum requirement for the relational model. The database mode that does not meet the first paradigm cannot be called the relational database mode.
However, the relational model that satisfies the first paradigm is not necessarily a good relational model.

[Example 4] relational model S-L-C (Sno, Sdept, Sloc, Cno, Grade) Sloc for students living in the same place where function dependencies include: (Sno, cno) F Grade Sno → Sdept (Sno, Cno) P Sdept Sno → Sloc (Sno, Cno) P Sloc Sdept → Sloc (meaning: students in each department live in the same place)

The S-L-C code is (Sno, Cno)
S-L-C satisfies the first paradigm.
Some functions of non-primary attributes Sdept and Sloc depend on codes (Sno, Cno)
(1) insertion exception
(2) Deletion exception
(3) high data redundancy
(4) complex modification
S-L-C is not a good relationship pattern

The reason is that some Sdept and Sloc functions depend on codes. Solution S-L-C is broken down into two relational patterns to eliminate these partial function dependencies SC (Sno, Cno, Grade) S-L (Sno, Sdept, Sloc)

Definition of 2NF 6.6 If rε 1NF and every non-primary attribute fully depends on code, rε 2NF. Example: S-L-C (Sno, Sdept, Sloc, Cno, Grade) in 1NF S-L-C (Sno, Sdept, Sloc, Cno, Grade) in 2NF SC (Sno, Cno, Grade) 2NF S-L (Sno, Sdept, Sloc)

The relationship of one 1NF is divided into multiple 2NF relationships, which can reduce insertion exceptions, deletion exceptions, large data redundancy, and complex modification problems in the original 1NF relationship to some extent.

Breaking down a 1NF relationship into multiple 2NF relationships does not completely eliminate various exceptions and data redundancy in the relational mode.
Algorithms decomposed into 2NF mode Sets
If the relational mode R (U) is set, the primary key is W, and fd x → Z exists on R, and Z is a non-primary attribute and X ⊂ W, then W → Z is a local dependency. In this case, R should be divided into two modes.
R1 (XZ), 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 used to obtain the R from R1 and R2 again.
If R1 and R2 are not 2NF, repeat the above process until every relational mode in database mode is 2NF.
Think about it: A relational model is R (A, B, C, D). Assume that the link has the following functional dependencies: A → B, A → C, C → D, what kind of paradigm does the relational model belong? Why?

The link mode belongs to 2NF because the key is A and some function dependencies do not exist. However, there is a function dependency on the transfer of keys for non-primary attributes, so it does not belong to 3NF.

3NF

Definition of 3NF 6.7 relational mode R <U, F> if such code X, attribute group Y, and non-primary attribute Z (Z, Y) do not exist, make X → Y, Y → Z is established, and Y → X is called R <U, F> ε 3NF. If rε is 3NF, each non-primary attribute does not depend on code or code. (Verifiable)

Theorem: if R is in 3NF mode, R is also in 2NF mode.
Proof: as long as the existence of local dependencies in the pattern implies passing dependencies. If A is A non-primary attribute of R, K is A candidate key of R, and K → A is A local dependency. Then there must be A K' then K in R, with K' →. Because A is A non-primary attribute, A between KK '= Phi. K' → K, but K → K' is valid. Therefore, from K → K' and K' → A, we can see that K → A is A transfer dependency.
Local dependency and transmission dependency are two important reasons for redundancy and exceptions in the mode. Because the 3NF mode does not have a local dependency on the candidate key and the transfer dependency of non-primary attributes, a large part of storage exceptions are eliminated, which provides better performance.

3NF also has the following equivalent definition, as described below.
Define 6.7.1 set F to the FD set of relational mode R. If every non-trivial fd x → Y in F has X containing code, or every attribute of Y is a primary attribute, R is called 3NF.
This definition indicates that if Y in non-trivial fd x → Y is the primary attribute, X → Y does not violate the 3NF condition. If Y is a non-primary attribute and X does not contain a code, then, the candidate code W has W → X, and W → Y is a transfer dependency, that is, R is not in 3NF mode.

Example: 2NF relational model S-L (Sno, Sdept, Sloc) function dependency: Sno → Sdept → Sno Sdept → Sloc can get: Sno → Sloc, that is to say, the transfer function of non-primary attribute to code exists in the S-L.

The relationship of 2NF is divided into multiple 3NF relationships, which can solve the problems of insertion exceptions, deletion exceptions, large data redundancy, and complex modification in the original 2NF relationship to a certain extent.

After a 2NF relationship is divided into multiple 3NF relationships, various exceptions and data redundancy in the relational mode cannot be completely eliminated.
Algorithms decomposed into 3NF mode Sets
Set the relational mode R (U). The primary key is W, and fd x → Z still exists in R. In addition, Z is a non-primary attribute. ZX and X are not candidate keys, so W → Z is a transfer dependency. In this case, R should be divided into two modes:
R1 (XZ), 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 primary key matching mechanism, R1 and R2 can obtain R again through connection.
If R1 and R2 are not 3NF, repeat the above process until every relational mode in database mode is 3NF.

Integrated instance

There is a relationship mode R (employee name, project name, project fee, Department name, Department Manager). If it is required that each employee can participate in multiple projects, each project will have a project fee; each project belongs to only one department. Each department has only one manager.
The basic function dependency FD and candidate key of relational mode R are given.
There are three basic FD for R:
(Employee name, project name) → project fee
Project name → department name
Department name → department manager
The candidate key for link mode R is: (employee name, project name)
Is R in 2NF mode? If not, R is decomposed into 2NF mode sets.
R has the following two FD:
(Employee name, project name) → (Department name, Department Manager)
Project name → (Department name, Department Manager)
Because there is a local function dependency on the candidate key (employee name, project name) of a non-primary attribute group (Department name, department manager), R is not 2NF. R should be divided into the following two modes:
R1 (employee name, project name, project fee) R2 (project name, Department name, department manager) R1 and R2 are 2NF.
Is R1 and R2 in 3NF mode? If not, it is divided into 3NF mode sets.
R1 is already 3NF.
In R2, there is a transfer function dependency between the non-master attribute 'department manager' and the candidate key 'Project name', so R is not 3NF. R2 should be further divided 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 }.