Principle of database system

In relational mode R (U), for subsets X and y of U,
If x→y, but Y is not included in X, it is called x→y is a non-trivial function dependency
If x→y, but Y is contained in X, it is called x→y is trivial function dependent
Example: in the relationship SC (Sno, Cno, Grade),
Non-trivial function dependency: (Sno, Cno) →grade
Trivial function dependency: (Sno, Cno) →sno
(Sno, Cno) →cno
(Usually, we always talk about non-trivial function dependencies.)

In R (U), if x→y, and for any of the X
A true subset X ', which has X '-\->y, is called Y-to-X complete function according to
Lai, as x-f-> Y.
If x→y, but Y is not fully dependent on X, then y is called X part
The function is dependent and is recorded as X-p-> Y.
Cases:
Dependency {(SNO,CNO) →grade, (SNO,CNO) →sdept,sno→sdept}
The
① (SNO,CNO)-f->grade full function dependency,
②∵ (SNO,CNO) →sdept,sno→sdept and Sno really contained in (SNO,CNO)
∴ (SNO,CNO)-p->sdept partial function dependency

In R (U), if x→y, (Y is not included in X), y-\->x
Y→z, it is said that Z is dependent on the X pass function.
Recorded as: X-Pass, Z
Note: If y→x, or X←→y, Z is directly dependent on X.
Cases:
Sno→sdept,sdept→mname (Sdept-\->sno)
sno-Delivery->mname

Set K to a property or attribute combination in R (u,f). If K-f->u, then K is called the candidate code for R
If more than one candidate code, select one of them as the main code

A property contained in any candidate code, called the primary attribute

Properties that are not included in any code are called non-primary or non-code attributes

The entire attribute group is a code, called a full code
If all the properties of a relational mode R are non-divided, the basic data items are
Then R∈1NF

If R∈1NF, and each non-principal attribute full function relies on the candidate code,
The R∈2NF.

If R∈2NF, and any of its non-master properties are not passed on to any candidate code,
Then R∈3NF

Relationship mode R (u,f) ∈1NF, if X→y and Y are included in X, x must contain the code,
Then R (u,f) ∈bcnf
Equivalent to: Each decision factor contains the candidate code

Cases:
In the relational model SJP (S,J,P), S represents the student, J represents the course, and P represents the rank.
n f:{(s,j) →p, (j,p) →s}
Candidate Code: (s,j), (j,p);
S,j,p are all primary properties
No non-primary attribute partial dependency or pass dependent on candidate code
∴stj∈3nf
∵ to any of the x→y,x in F is a candidate code,
∴stj∈bcnf

If x→→y, and z is not an empty set, it is called x→→y
"Non-trivial" multi-valued dependency.

If x→→y, and Z is an empty set (i.e. u=x+y), it is called x→→y
is a trivial multi-valued dependency.

Relationship mode R (u,f) ∈1NF, if for each "non-trivial" multivalued dependent x→→y of R (Y is not included in X),
x all contain codes, then r∈4nf.
Example: Teaching (C,T,B) does not belong to 4NF
There are non-trivial multivalued dependent c→→t, c→→b
and C is not a code
The teaching is decomposed into the following two relational patterns by projection decomposition:
CT (C, T) ∈4nf
CB (C, B) ∈4nf

R (a,b,c,d), f{b→d,ab→c}
Candidate Code AB, non-principal attribute C,d
∵ partial dependency of non-primary attribute on candidate code
∴r∈1nf

Relationship mode R (s#, SD, SL)
Function dependency: {S#→SD,SD→SL}
Candidate code: s#, non-main attribute SD,SL
The ∵SL transfer function relies on s#,
∵ There is no partial function dependency in SL that exists in the non-principal attribute pair code.
∴r∈2nf

R (a,b,c,d), F{b→d,d→b, ab→c}
Candidate code: AB,AD, non-main attribute C
∵b→d,b is not a candidate code
∴ does not belong to BCNF
∵ there is no partial or transitive dependency of the non-primary attribute on the candidate code
∴r∈3nf

R (A,b,c), F{a→b,b→a, a→c}
Candidate code: A, b non-main attribute C
∵ is a candidate for any x→y,x
∴r∈bcnf

For an R (U,F), all that is implied by the F-logic
The function relies on a closure called F, which is recorded as f+

for R<u,f>, where: U={x,y, Z}, F={x→y, y→z}}
If f+={
X→φ, Y→φ, z→φ, xy→φ, xz→φ, yz→φ, Xyz→φ,
X→x, Y→y, Z→z, Xy→x, Xz→x, Yz→y, Xyz→x,
X→y, Y→z, Xy→y, Xz→y, Yz→z, Xyz→y,
X→z, Y→yz, Xy→z, Xz→z, Yz→yz, Xyz→z,
X→xy, Xy→xy, Xz→xy, Xyz→xy,
X→xz, Xy→yz, Xz→xz, Xyz→yz,
X→yz, Xy→xz, Xz→xy, XYZ→XZ,
X→XYZ, XY→XYZ,XZ→XYZ, xyz→xyz
}，

Cases:
has a relationship mode R (A, B, C, D),
F={b→a,d→c}, the left part of the f+ is (BC)
Number of dependencies there are 8
Bc+={b,c,a} 2^3=8

Relationship mode R (u,f), u={a,b,c},f={a→b,b→c}
If X={a}, the property set is closed:
X +f={a,b,c}
If X={b}, the property set closure
X +f={b,c}
If X={c}, the property set closure
X +f={c}

There is a relationship mode R (a,b,c,d) with a function dependency set on it:
F={A→C,C→A,B→AC,D→AC}, Trial calculation: (AD) +
Make X={ad},
X (0) ={ad},
X (1) ={ACD},
X (2) ={acd}= x (1),
therefore (AD) +={ACD}

Candidate keys for FM ={b→g, CE→B, C→a, Ce→g, b→d,c→d}.
1. Attributes only appearing on the left side: CE, (M=CE)
2. Properties that appear only on the right side: ADG, (N=ADG)
3, the remaining attributes: B, (W=B)
4. The candidate key for R may be CE or CEB only
5, the calculation Ce+f=u,ce is the candidate key

1. With relationship mode R (u,f), u={a,b,c,d,e},
F={ab→e,de→b,b→c,c→e,e→a}
1). Calculates the closure of all functions in F that depend on the left attribute set;
1), (AB) +={a,b,c,e}; (DE) +={a,b,c,d,e}
B+={a,b,c,e};c+={a,c,e};e+={a,e}
2). Determine all candidate codes on the relationship mode R
2), all candidate codes on the relationship mode R; DE,DB,DC

Set F and G are two function dependency sets:
① if f is contained in G +, then G is an F
Cover, or G cover f;
② if f is included in G + and G is contained in F + at the same time established, i.e. F +=g +,
is called F and g equivalent.

Set G and F are two function-dependent sets, and the necessary and sufficient conditions for the equivalence of F and G are
F contained in g+ and G contained in f+

The process of decomposing a relational pattern of a "low" rank into several "high" hierarchies is called pattern decomposition.

The method of pattern decomposition is not unique.

The goal of relational pattern decomposition: "Non-destructive connectivity" and "functional dependency persistence"

The so-called "lossless connectivity" of decomposition: if R (u,f) is decomposed into K-sub-mode: Ρ={R1 (U1,F1), R2 (U2,F2),..., Rk (UK,FK)},
This k sub-mode can also be reverted to mode R after the connection operation. That is, each relationship R that satisfies F in R, has
R=ΠR1 (R) πr2 (r) ... Πrk (R)

The so-called "functional dependency retention" of decomposition: if R (u,f) is decomposed into
K Sub-mode: Ρ={R1 (U1,F1), R2 (U2,F2),..., Rk (UK,FK)},
Then the FI (0<=i<=k) is set with F equivalence.

The decomposition method is only meaningful if it can guarantee that the relational pattern after decomposition is equivalent to the original relational model.

Cases:
Relationship R (u,f), u={s#,sd,mn},f={s#→sd,sd→mn}

1. Decomposition of R: Ρ1={r1 (s#), R2 (SD), R3 (MN)}
A problem exists:
A, the original "function dependency relationship does not exist".
B, R1XR2XR3 Cartesian product, the tuple increased, "No lossless connection."

2. Another decomposition of R: Ρ2={r1 (S#,SD), R2 (S#,MN)
Ρ2 decomposition of R is recoverable, "with non-destructive connectivity"
Problematic: sd→mn dependency loss, "no function dependency retention"

3. Decomposition of R: Ρ3={r1 (S#,SD), R2 (SD,MN)}
"Non-destructive connection" and "Keep function dependent" while resolving insert, delete, update exception

R (u,f), u={a,b,c,d,e},f={ab→c,c→d,d→e},
A decomposition of R to
R1 (A,b,c), R2 (C,d), R3 (d,e).
------A---B----C----D---E
R1-A1-A2---a3---b14-b15
R2 B21-B22--A3---a4--b25
R3 b31-b32-b33--a4--a5
by c→d "Note: Find the line with the same symbol in column C has r1,r2;
Then in R1,R2 see if there is a A4 in column D, there are r1,r2 rows of column D all changed to A4, otherwise instead b14 "
------A---B----C----D---E
R1--A1-A2---a3--a4---b15
R2 b21-b22-a3--A4---b25
R3 B31-B32-B33-A4---A5
by d→e "Note: Find the row with the same symbol in column D has r1,r2,r3;
Then in R1,R2,R3 see if there is a A5 in column E, there are R1,R2,R3 rows of column D all changed to A5, otherwise instead B15 "
------A---B----C----D---E
R1 A1--A2---a3---a4--a5
R2 b21-b22-a3---a4--a5
R3 b31-b32-b33--a4--A5
If a change is made, a row becomes a1,a2,......, an. Then ρ-tool
There is no non-destructive connectivity, otherwise ρ does not have a lossless connection.

has a relationship mode R (A, B, C, D, E), and has a function dependency
{ab→d, ac→e, bc→d,d→a,e→b}, now decompose R into off
Mode S (A, B, C) and some other relational modes, please
function dependent on the system mode S.
∵ab→d,d→aþab→a (trivial function dependency, delete)
∵ac→e, E→bþac→b
∵bc→d, D→aþbc→a}
∴fs={ac→b, Bc→a}

With relational mode R (ABC), Ρ={ab,ac} is a decomposition of R
。 Trial Analysis in F1={a→b},f2={a→c,b→c},f3={b→a}
, f4={c→b,b→a} In case the ρ has a lossless decomposition and retention
Decomposition characteristics of FD.
F1: is a lossless decomposition and maintains the decomposition of the FD set.
F2: is a lossless decomposition, but does not maintain the decomposition of the FD set. B→c lost.
F3: is lossy decomposition, but maintains the decomposition of the FD set.
F4: is a lossy decomposition and does not keep the decomposition of the FD set. C→b lost

The normalization theory provides a set of pattern decomposition algorithms, which can be done according to these algorithms:
⒈ if the decomposition has a "non-destructive connectivity", the mode decomposition "must" can Reach "4NF" (in the functional dependency category, must be reached bcnf).
⒉ if the request Decomposition "maintain function dependence", the mode decomposition "must" can reach "3NF", but not necessarily can reach bcnf.
⒊ if the requirement of decomposition is to "maintain the function of dependence", but also has a non-destructive connectivity, the mode decomposition "must" can reach "3NF", but not necessarily to reach bcnf.

A Decomposition algorithm that breaks down into "3NF" and "keeps the function dependent":
1) Minimize the F in R (u,f).
2) Identify the attributes that are not present in F and form a relational pattern.
3) The set of properties involved in the FK,FK by the principle of having the same left part of the F UK,
If the UI is contained in UJ and I≠j,
Then remove the UI, Merge fi, Fj, and form a sub-mode Ri for each UI.

Cases:
Example 2: Relational mode R (u,f), where F={be→g,bd→g,cde→ab,cd→a,ce→g,bc→a,b→d,c→d}, decomposes R to 3NF and has function dependency persistence.
1) Minimize the F in R (u,f). FM={B→G,CE→B,C→A,B→D,C→D}
The candidate key is CE.
2) Identify the attributes that do not appear in FM and form a relational pattern.
3) The set of attributes that the FM FK,FK is grouped by the principle of having the same left part UK,
Decomposed into three modes, RI∈3NF and has a function dependency persistence.
R1:U1=BDG,F1={B→G,B→D}
R2:U2=ACD,F2={C→A,C→D}
R3:U3=BCE,F3={CE→B}

Two. Decomposition into "3NF", both "non-destructive connectivity" and "maintain function dependence" algorithm
(1) Call algorithm one produces R decomposition Ρ={r1 ... Rn}
(2) structure decomposition τ={r1,...,rn, RK}, where RK is a candidate key of R consisting of a relationship of K

The relational Mode R (u,f), where F={be→g,bd→g,cde→ab,cd→a,ce→g,bc→a,b→d,c→d}, decomposes R to 3NF and has both a lossless connectivity and a function dependency.
1) Fm={b→g,ce→b,c→a,b→d,c→d}, candidate key is CE,
"Annotation algorithm one gets the
R1:U1=BDG,F1={B→G,B→D}
R2:U2=ACD,F2={C→A,C→D}
R3:U3=BCE,F3={CE→B} "
2) "Comment: Construct decomposition τ={r1,...,rn, RK}, where RK is a candidate key of R consisting of K-form relationship"
R1:U1=BDG,F1={B→G,B→D}
R2:U2=ACD,F2={C→A,C→D}
R3:U3=BCE,F3={CE→B}
R4:u4=ce, because the R4 attribute is contained in the R3, it can be deleted.
ri={R1,r2,r3}∈3nf, and decomposition of both non-destructive
and maintain functional dependencies.

Three, BCNF decomposition algorithm with non-destructive connectivity
Input: Relationship mode R (u,f)
Algorithm: Ρ={r1 ... Rn}
Cycle:
If. (There is a non-bcnf relationship pattern in ρ)
{
Choose a non-bcnf mode rj∈ρ;
A function that violates BCNF requirements for the optional RJ x→y "Comment: X does not contain code/candidate/primary Key"
To decompose RJ into Ρ2={RT,RM}, ut=xy;um= uj-{y}
Use Ρ2={RT,RM} instead of RJ in ρ;
}
Stop. (There is no non-bcnf relationship pattern in ρ)

Example: STJ (student s, teacher T, course j) Each course can have a number of teachers in the class, each teacher only one course.
f:{(S,j) →t, (s,t) →j,t→j}
Candidate Keys: (S,j), (s,t),
∵STJ∈3NF, not belonging to BCNF.
Dependency on unsatisfied bcnf: t→j "NOTE: T does not contain code/candidate code, * Note to distinguish T included in code/candidate Code"
Decomposition to
{TJ (t,j), ST (s,t)}∈bcnf
This decomposition has a "lossless connectivity".
"Do not keep function dependencies" missing: (s,j) →t, (s,t) →j

Cases:
R (I,j,k,l,m,n)
F={k→m,j→n,jk→l,n→i}
Decomposition R makes it a BCNF and has non-destructive connectivity.
∵jk+=jklmni (j+=jni,k+=km)
∴JK is the primary key of R
Decomposition algorithm
---1, take n→i,n not primary key/candidate code
S1={N,I}∈BCNF, F1={n→i}
S2={JKLMN},F2={K→M,J→N,JK→L}
S2 JK+=JKLMN∴JK is the primary key/candidate code
---2, take k→m, K is not primary key/candidate code
S21={K,M}∈BCNF, F21={k→m}
S22={JKLN},F22={J→N,JK→L},JK is the primary key/candidate code
---3, take j→n,j not primary key/candidate code
S221={J,N}∈BCNF, F221={j→n}
S222={J,K,L}∈BCNF, F222={JK→L}JK is the primary key/candidate code
∴ decomposition of R ρ={s1, S21, S221, s222}∈bcnf
Have "lossless connectivity" and "keep function dependent."

Four 4NF decomposition algorithm with "non-destructive connectivity"
Decomposition algorithm: Ρ={r1 ... Rn}
Cycle:
If. (There is a non-4NF relationship in ρ)
{
Select a non-4NF mode s∈ρ
Select a multi-valued dependency x→→y that violates 4NF requirements for RJ
To decompose RJ into Ρ2={RT,RM}, ut=xy;um= uj-{y}
Use Ρ2={RT,RM} instead of RJ in ρ;
}
Stop. (no non-4NF relationship exists in ρ)

Cases:
R (U) =r (a,b,c), d={a→→b,a→→c}
Indicates that R does not belong to 4NF, decomposition R is 4NF, with non-destructive connectivity
⑴r (A,B,C) does not belong to 4NF
There are non-trivial multivalued dependent a→→b,a→→c
And a is not a code
⑵ to A→→b
R1 (A, B)
R2 (a,c) =u-b,
Ρ={r1,r2}∈4nf

Cases:
R= (A,b,c), d={ab→c,c→→a}
Ask R∈? NF, the R is decomposed to 4NF and has a non-destructive connection.
Primary Key AB, R∈BCNF
Decomposition CA (c,a),
CB (b,c) ∈4nf
The above sub-mode belongs to 4NF and has non-destructive connectivity.
function dependencies are missing.

F is called a minimum function dependency set if the function dependency set F satisfies the following conditions.
Also known as a minimum dependency set or minimum overwrite.
(1) The right side of any of the function dependencies in F contains only one property.
(2) There is no such function dependency x→a in F, X has a true subset Z makes
F-{x→a}∪{z→a} is equivalent to F.
(3) There is no such function dependency x→a in F, which makes
F is equivalent to F-{x→a}.

Theorem: Each function depends on set F equal price to a minimum function dependent set FM

Minimizing process

Example: Beg F={be→g,bd→g,cde→ab,cd→a,ce→g,bc→a,b→d,c→d}
The minimum function depends on the SET FM
Solution:
1) Break right end first, f={b→g,ce→b,cd→a,b→d,c→d}
2) C+F=CDA, contains A, c→a instead of cd→a
3) Check from left to right whether the function dependencies in F are superfluous

Order: Fm ={b→g,ce→b,c→a,b→d,c→d}

Example: Minimum function dependency set FM for F={a→bc,b→ac,c→a}
Solution:
1) Break the right end first,
f= {A→b,a→c,b→a,b→c,c→a}
2) left is a single attribute
3) Check from left to right whether the function dependencies in F are superfluous:
Set G=f-{a→b},
A+g =ac, not including B, a→b reserved
Set G=f-{a→c},
A+g =abc, containing C, a→c deleted,

Make F=g={a→b,b→a,b→c,c→a}

Principle of database system