Database and database Learning

Database and database Learning
BC Paradigm (BCNF)

Relational mode R <U, F> ε 1NF. If X → Y and y x are required to contain the code, R <U, F> ε BCNF. This definition indicates that, if the non-trivial fd x → Y contains no code, Y must be passed depending on the candidate key, so R is not the BCNF mode. It is equivalent to: Each attribute factor contains a code.

If the relational mode R is 1NF and each attribute (including the primary and non-primary attributes) does not pass a candidate code dependent on R, R is the BCNF mode.

If all non-primary attributes of rε BCNF are completely functional for each code, relying on all primary attributes for each code that does not contain it, it is also a full function dependency. There is no attribute. The function is completely dependent on any set of non-Code attributes rε BCNF rε 3NF.

Theorem: if R is in BCNF mode, R is also in 3NF mode. Proof: if R is set to BCNF, but not 3NF, there must be A transfer dependency between X → Y, Y → A on R. Here X is the code of R, A ε Y, Y → X. Obviously, Y does not contain the R code. Otherwise, Y → X is also valid. Therefore, Y → A violates the definition of BCNF and is in conflict with the assumption that R is BCNF. Thus, the theorem is proved.

[Example 5] relational mode C (Cno, Cname, Pcno) C ε 3NFC ε BCNF [Example 6] relational mode S (Sno, Sname, Sdept, Sage) assume that S has two codes: Sno, SnameS, and 3NF. S∈ BCNF

[Example 7] function dependency of link mode SJP (S, J, P): (S, J) → P; (J, P) → S (S, J) and (J, p) can all be used as candidate codes, with the property intersection SJP, 3NF, SJP, BCNF

[Example 8] in the Relationship Mode STJ (S, T, J), S indicates the student, T indicates the teacher, and J indicates the course. Function dependency: (S, J) → T, (S, T) → J, T → J (S, J) and (S, T) are both candidate codes.

Multi-value dependency

[Example 9] a course in a school is taught by multiple instructors who use the same set of reference books. Each instructor can teach multiple courses, and each reference can be used for multiple courses.
Teaching ε BCNF

Teaching has a unique candidate code (C, T, B), that is, full code
Problems in Teaching Mode
(1) large data redundancy
(2) Complicated insert operations
(3) Complicated deletion operations
(4) complex modification operations

Set R (U) to a relational mode on the property set U. X, Y, and Z are the subsets of U, and Z = U-X-Y. In the relational mode R (U), multiple values depend on X → Y. if and only for any relational R of r (U), the value of a pair (x, z) is given, there is a set of Y values. These values are only determined by the x value, but are not related to the z value. For example, Teaching (C, T, B)

Another equivalent formal definition of multi-value dependency:

In any relation R of r (U), if the tuples t, s make t [X] = s [X], then the tuples w, v r, (w, v can be the same as s, t), so that w [X] = v [X] = t [X], while w [Y] = t [Y], w [Z] = s [Z], v [Y] = s [Y], v [Z] = t [Z] (that is, switching s, the two new element groups obtained from the Y value of t tuples must be in r). Then, the Y value depends on X and is recorded as X → Y. Here, X and Y are the subset of U, and Z = U-X-Y.

Ordinary multi-value dependency and non-ordinary multi-value dependency
If X → Y, and Z = Phi, it is called
X → Y is an ordinary multi-value dependency
Otherwise, X → Y is a non-trivial multi-value dependency.
[Example 10] link mode WSC (W, S, C)
W indicates the warehouse, S indicates the custodian, and C indicates the product.
Assume that each warehouse has several custodians and there are several types of commodities.
Each custodian keeps all items in the warehouse
Each item is kept by all custodians

Multi-value dependency

(1) multi-value dependencies have symmetry
If X → Y, then X → Z, where Z = U-X-Y
(2) multi-value dependency is passed
If X → Y, Y → Z, then X → Z-Y
(3) function dependency is a special case of multi-value dependency.
If X → Y, then X → Y.
(4) If X → Y, X → Z, then X → y z.
(5) If X → Y, X → Z, then X → Y then Z.
(6) If X → Y, X → Z, then X → Y-Z, X → Z-Y.

Differences between multi-value dependency and function dependency

(1) The validity of multi-value dependency depends on the range of the attribute set.
(2)
If the function depends on X → Y on R (U), X → Y is true for any Y 'Y.
Multi-value dependency X → Y if it is true on R (U), it cannot be asserted that X → Y is true for any y' Y

4NF

Relational mode R <U, F> ε 1NF. If every non-trivial multi-value of R is dependent on X → Y (y x), and X contains code, then R is 4NF. If rε is 4NF, rε BCNF does not allow non-trivial and non-function-dependent multi-value dependencies. The non-trivial multi-value dependencies allowed are function dependencies.

For example, Teaching (C, T, B) ε 4NF has a non-trivial multi-value dependency C → T, and C is not a code. The projection decomposition method is used to decompose Teaching into the following two relational modes: CT (C, T) ε 4NF CB (C, B) ε 4NF C → T, C → B is an ordinary multi-value dependency

The Standardization Theory of relational databases is a tool for database logic design.

Objective: To eliminate insertion and deletion as much as possible, complex modification, and data redundancy

Basic Idea: gradually eliminate the inappropriate part of data dependency
Essence: simplification of concepts

The higher the degree of normalization, the better the relationship model.
When designing the Database pattern structure, we must further analyze the actual situation in the real world and the needs of user applications to determine a suitable pattern that can reflect the real world.
The preceding normalization steps can be terminated at any of these steps.