Current location - Training Enrollment Network - Mathematics courses - Standardization theory of online, peer-to-peer and relational databases
Standardization theory of online, peer-to-peer and relational databases
8.2 Standardization theory

Godd put forward the relational model in 1970, and at the same time, it also put forward the problem of relationship standardization in relational database. According to the degree of dependence between attributes, the normalization of relations can be divided into first normal form, the second paradigm, the third paradigm, the Boyce-Codd paradigm and the fourth paradigm. People's understanding of normalization is a process, and functional dependencies between attributes have been found in 1970. Therefore, the first, second, third and Boyce-Codd paradigms related to functional dependency are defined. During 1976~ 1978, Fagin, Delobe and Zanjolo discovered multivalued dependency, thus defining the fourth normal form related to multivalued dependency.

The definition of paradigm is closely related to the discovery of dependencies between attributes. In this section, we introduce the concepts of functional dependency and multivalued dependency, and then define first normal form, the second normal form, the third normal form, the Boyce-Codd normal form and the fourth normal form.

8.2. 1 functional dependency

Functional dependency is the most common dependency between attributes in relational schema. For example, in the relational schema S, there is a dependency between S# and Sd. That is, once the value of S# is determined, the value of Sd is uniquely determined. At this time, the function called S# determines Sd or the Sd function depends on S#, which can be represented by the following symbols:

S# → SD

Similarly, we can also have:

S# → Sa

Serial number → serial number

However, there is no functional dependency between S# and G in the relational model SC, because a student number S# can allow multiple grades (corresponding to different courses respectively), so the grade G cannot be uniquely determined, but there is a functional dependency between (S#, C#) and G, that is:

(S#,C#)→G

The concept of functional dependence belongs to the semantic category, and we can only determine whether there is such dependence between attributes according to semantics, and there is no other way to follow.

Define 8- 1 and set the relational patterns R (U), X, Y, where X and Y are subsets of U. If the attribute value of any tuple in any relation R is determined in X, it is said that the Y function depends on X or X function to determine Y, which is denoted as X→Y, where X is called the determining factor and Y is called the dependent factor. For functional dependence, we always use a method called non-dependence.

Define 8-2 A functional dependency X→Y If Y(X) is satisfied, then this functional dependency is called nontrivial.

In order to deeply study the functional dependence and meet the needs of standardization, we have to introduce several different types of functional dependence.

First of all; The concept of complete functional dependence is introduced, which lays the foundation for real functional dependence. For example, we have S#→Sd in the S application, so we will also have:

(S#, Sn)→ SD

(S#, Sa)→ SD

After comparing these three functional dependencies, we will find that the functional dependencies that actually work are:

S #→ SD

The other two functional dependencies are derived from it, that is to say, it is S# that really plays the role of functional dependency, not Sn or s a and so on. In this way, when we study functional dependence, we should distinguish between these two different types of functional dependence. The former is called complete functional dependence and the latter is called incomplete functional dependence.

In the definition of 8-3 R( U), if X, Y(U) satisfies X→Y, and there is X'→Y' for any proper subset X', then the Y-complete function is said to depend on X, which is recorded as:

X Y

Definition 8-4 If X, Y(U) exists and satisfies X→Y in R( U), but the incomplete function of Y depends on X, it is said that Y is partially dependent on X, and it is recorded as:

X Y

As can be seen from the above, Sd complete function depends on S#, while Sd incomplete function depends on (S#, Sn), namely:

Standard deviation

(serial number) SD

(S#, Sa) SD

In terms of functional dependence, we should also distinguish between direct functional dependence and indirect functional dependence. For example, in S#→Sd, Sd is a direct functional dependence of S#, but if there is a telephone number DT of a department in the attribute (if each department has a unique telephone number), there is: Sd→DT, which can be obtained from S#→Sd and Sd→DT:

S# →DT

In this functional dependence, DT is not directly dependent on S#, but on S# through the intermediate attribute Sd, that is, DT is directly dependent on Sd, and Sd is directly dependent on S#, thus forming the functional dependence of DT on S#, which is an indirect dependence, or transitive dependence. We can define it like this.

Definition 8-5 If x, y and Z(U) exist in R( U) and satisfy:

X→Y,(Y(X ) Y / X,Y→Z

Then the z transfer function depends on x, otherwise it is called non-transfer function dependence.

Note that the transfer function dependency and non-transfer function dependency here are only different in concept, and there is no difference in formal representation, that is, the Z transfer function dependency on X or the Z non-transfer function dependency on X are all represented by X → Z. The purpose of this is to consider the whole and make the representation as simple and convenient as possible.

After defining several different functional dependencies, we continue to define some very important basic concepts, that is, some concepts about keY.

Definition 8-6 If there is K(U) in R(U) and it satisfies:

K U

Then k is called the keyword of R.

A relational schema can have several keywords, so it is enough for us to choose one of them in use. The selected keyword is called the primary key of this relational schema, while the common keyword is called the candidate key.

In the relational models S, C and s C, the keyword of S is S#, the keyword of C is C#, and the keyword of SC is (S #, C #), because we have:

S# (S#,Sn,Sd,Sa)

C# (C#、CN、P#)

(S#,C#) (S#,C#,G)

In S, (S#, Sn) and (S#, Sd) are not keywords, because we have:

(serial number) (serial number, serial number, SD, serial number)

(serial number, SD) (serial number, SD, Sa)

In a relational model, all the attributes in keywords form a set, while all the other attributes form another set, which are called the main attribute set and the non-main attribute set of this relational model respectively. The attributes in the main attribute set are called prime attributes, and the attributes in the non-main attribute set are called non-prime attributes. For example, in the relational model S (S#, Sn, Sd, Sa), for example,

(S#)。

The non-primary attribute set is:

(serial number, Sd, Sa)

In SC(S#, C#, G), the main attribute sets are:

{ S#,C# }

Non-primary attributes are:

{G}

Below we give their definitions:

8-7 The set P composed of attributes in all keywords in R (U) is defined as the main attribute set of R (u).

The set n composed of all non-critical attributes in 8-8 R (U) is defined as the non-main attribute set of R(U). A series of concepts related to functional dependency are established above. With these concepts, we can discuss several issues related to functional dependency.

There are three paradigms, namely, first normal form, the second paradigm and the third paradigm (in fact, first normal form has nothing to do with all dependencies, but for convenience, it can be regarded as related to functional dependencies). As for the discussion of functional dependency theory, it will be introduced in detail later in this chapter.

8.2.2 Examples related to functional dependency

In this part, we discuss four paradigms, namely first normal form, the second paradigm, the third paradigm and Boyce-Codd paradigm.

Firstly, it is introduced that first normal form first normal form is the basic condition to be followed in the relationship model, that is, each attribute value in the relationship must be an inseparable amount of data. If a relational schema satisfies this condition, it is called first normal form (or abbreviated as lNF), and if a relational schema R satisfies first normal form, it can be recorded as R∈lNF.

First normal form stipulated that the attribute values in a relationship must be inseparable data, which ruled out the possibility that the attribute values are tuples, arrays or some kind of composite data, so that the attribute values of all relationships in the relational database are the simplest, so that the structure can be simple and the discussion can be convenient. Generally speaking, every relationship model must satisfy first normal form, because this is the most basic requirement of every relationship.

Let's begin to discuss three paradigms that are really related to functional dependence. In order to discuss these examples, besides attributes, we usually determine all functional dependencies on relational schemas according to semantics. If there is a relational schema R with an attribute set U and a functional dependency set F, then a relational schema can be determined by triples R, U and F, which can be written as:

R ( U,F)

Note that this expression only represents a triple, and it does not represent a predicate or relationship. For example, the aforementioned student relationship model S can be expressed as:

S ({S#,Sn,Sd,Sa},{S#→Sn,S#→Sd,S#→Sa})

Another example is a relational model named SCG', which consists of attributes S#, Sn, Sd, s a, C#, G, where S represents the majors that students have studied, and other meaning is the same as before. There are some semantic information in this relational schema, which are:

(1) Each student belongs to only one department and one major;

(2) Each student has one and only one grade in each course;

(3) The majors of different departments are different.

According to the above semantic information and other basic common sense, we can express them in the form of functional dependence, which are:

Serial number → serial number

S #→ SD

S#→Ss

Ss→Sd

(S#,C#)→G

Therefore, the information about this relational model can be written as:

SCG'({S#,Sn,Sd,Ss,C#,G},{ S#→Sn,S#→Sd,S#→Ss,Ss S#→Sd,(S#,C# ) →G}

After the relational schema has functional dependence, we can discuss the normalization problem. Each layer of the relational paradigm puts forward the constraints that the relational model should follow, in order to make the relational model less abnormal and redundant, that is, to make the relational model better.

Let's discuss the second paradigm.

Definitions 8-9 let R(U)∈lNF, and each non-principal complete function depends on keywords, so that R(U) satisfies the second normal form (which can be abbreviated as 2NF) or written as R∈2NF.

In fact, not every relational model that satisfies first normal form must satisfy the second formula. For example, the relational schema SCG' in the previous example does not satisfy the second normal form. This is because in SCG', its keyword is (S#, C#), and its non-main attribute set is:

(SD, G, Sn, Ss)

Although we have:

(S#、C#) G

However, Sn, SD and SS are not completely dependent on (S#, C#), so they do not meet the conditions of the second normal form.

If a relational schema satisfies the second normal form, then it must have fewer anomalies and redundancies. Therefore, if a relational pattern only satisfies first normal form, it must decompose a relational pattern into several relational patterns to satisfy the second normal form, so that the decomposed relational pattern can satisfy the second normal form. For example, the relational schema SCG' can be decomposed into two relational schemas, which are:

SCG'l ({S#,C#,G},{( S#,C#)→G})

SCG'2 ({S#,Sn,Sd,Ss},{S#→Sn,S#→Sd,S#→Ss→Sd})

These two modes SCG' can be represented by the schematic diagram shown in Figure 8- 1.

Patterns SCG' 1 and SCG'2 both meet the second normal form, and both of them are less abnormal and less redundant, while SCG' 1 can also realize the occurrence of non-insertion and deletion exceptions, while SCG' does not meet the second normal form, so both insertion exceptions and deletion exceptions exist, and the data redundancy is also great. Please verify it yourself.

(a) SCG' schematic diagram (b)SCG' 1 and SCG'2 schematic diagram.

Figure 8- 1 Functional Dependence Diagram of Three Relation Patterns

However, the second normal form cannot completely avoid the occurrence of anomalies. For example, although SCG'2 satisfies the second normal form, there are still insertion exceptions and deletion exceptions. For example, in SCG'2, as shown in Table 8-4.

Table 8-4 Relationship Mode of SCG' 2

S#

tin

storage card

suspended matter

SCG'2:

In this mode, it is difficult to insert this situation into the mode if you want to register the professional setting of a department without enrollment. In this way, if we want to delete some students, we may delete the relevant professional settings together. The reason is nothing more than that Sd depends on S# and Ss, and Ss depends on S#, which leads to the emergence of transfer function dependence. Therefore, it seems that we should eliminate this abnormal phenomenon.

The third paradigm requires that the relational schema must first satisfy the second paradigm, and each non-main attribute does not depend on keywords. It can be seen that if the third normal form is satisfied, each non-main attribute is neither partially dependent nor dependent on keywords.

Definition 8- 10 If each non-main attribute of the relational schema R(U) does not partially depend on keywords or its transmission depends on keywords, it is said that R satisfies the third normal form (which can be abbreviated as 3NF) and is denoted as R∈3NF.

The third paradigm divides the attributes in the relational schema into two categories, one is the non-main attribute set, and the other is the main attribute set, but each attribute in the non-main attribute set is complete, and the keywords that depend on the main attribute set are not passed, so as to straighten out the complex dependencies in the relational schema, simplify and standardize the dependencies, and then try to avoid the occurrence of anomalies. Its schematic diagram is shown in Figure 8-2, in which the relational schema can be compared to an atom, in which the main attribute set.

If a relational pattern does not meet the third normal form, it can be decomposed into several patterns through pattern decomposition, so that the decomposed patterns meet the third normal form. For example, in the relational schema SCG', SCG'2 satisfies the second paradigm, but does not satisfy the third paradigm. At this time, it can be decomposed into the following two modes:

SCG'2 1(S#,Sn,Ss)

SCG'22 (Ss,Sd)

Figure 8-2 "Atomic" Model of the Third Paradigm

See Figure 8-3 for the dependency diagram.

(a)SCC ' l(b)SCG ' 2 1(c)SCG ' 22

Figure 8-3 exploded view of modular solution

After several decomposition in SCG', three relational patterns are obtained:

SCG ' 1,SCG'2 1,SCG'22

These three modes all meet the third normal form, with no abnormal phenomena and small redundancy.

In 1972, Boyce, Codd and others studied the paradigm from another angle, and found that the relationship between the decisive factors and keywords in functional dependence was related to the paradigm, thus creating another third paradigm called Boyce-Codd paradigm.

The general meaning of Boyce-Codd paradigm is that if every determinant in the relational schema is a keyword, it satisfies Boyce-Codd paradigm. We know that, generally speaking, the determinant in each functional dependency is not necessarily a keyword, so only if the determinant in R is a keyword can it be considered as satisfying the Boyce-Codd paradigm.

8- 1l is defined as x, Y(U) in R(U), assuming that R∈lNF is satisfied, and if X → Y (x) must contain keywords, it is said that r satisfies the Boyce-Codd paradigm (abbreviated as BCNF), and is denoted as R∈BCNF.

The next question we need to study is the relationship between BCNF and 3NF. After careful study, we think that BCNF is stricter than 3NF. The following theorem gives the answer.

Theorem 8- 1 If BCNF is satisfied, the relational schema R(U) must satisfy 3NF.

Please try to prove this theorem yourself (note: it can be obtained through the definitions of BCNF and 3NF).

This theorem tells us that if a relational schema satisfies BCNF, it must satisfy 3NF. However, does a relational schema satisfy 3NF and BCNF? That is to say, does the sufficient condition of Theorem 8- 1 hold? The answer is no, that is, there must be a R(U) that satisfies 3NF, but does not satisfy BCNF, which can be illustrated by examples.

Example 8- 1 has a relational model R(S, c, t), where s and c have the same meanings as before, t stands for teacher, and r has the following semantic information: (1) Each teacher only has one class;

(2) After the students and courses are determined, the teachers are determined.

Thus, r has the following functional dependencies:

(S,C ) →T

T→C

This relational schema satisfies 3NF, because its main attribute set is (s, c), its non-main attribute set is (t), and t completely depends on (s, c) and has no transitive dependency. However, this relational model cannot satisfy BCNF, because T is the decisive factor, but not the keyword.

The schematic diagram of this mode is shown in Figure 8-4.

Fig. 8 Schematic diagram of 4 cases, 8 cases and 1.

As can be seen from this example, in fact, the third paradigm can't avoid deformity. For example, if you don't start a course this semester, no students will choose. At this time, it is impossible to express the information about the teacher's fixed offering of a certain course. Therefore, in order to avoid this anomaly, it is necessary to further decompose the relational model into BCNF. For example, r can be further decomposed into:

R 1 (S,T)

R2

Its schematic diagram is shown in Figure 8-5, while R 1 and R2 are BCNF, and neither of these two modes will produce abnormal phenomena.

R 1 R 2

Figure 8-5 R is decomposed into two BCNF

As can be seen from the above, BCNF is stricter than 3NF, and it divides the attributes in relational schema into two categories, one is determinant set, and the other is non-determinant set. The attributes in a nondeterministic set depend entirely on each determinant in the determinant set. The schematic diagram of this metaphor is shown in Figure 8-6.

So far, the abnormal phenomenon caused by functional dependence can be solved by decomposing it into BCNF. In BCNF, the functional dependencies in each relational schema are simple and regular, and they closely depend on each other to form a whole, thus avoiding abnormal phenomena and excessive redundancy.

Figure 8-6 Atomic Model of BCNF

8.2.3 Multivalued Dependence and the Fourth Normal Form

We have studied functional dependency and several paradigms related to it, but are there no other dependencies between attributes in the relational schema except functional dependency? That was not the case. Functional dependence is an obvious dependence, but with the deepening of people's understanding of relational schema, it is found that there are other dependencies, and multivalued dependence is one of them. Let's give an example to illustrate the existence of multivalued dependency.

Example 8-2 has a curriculum relationship C, which can be expressed in Table 8-5. The table shows that this advanced mathematics course can have three teachers and two reference books. General physics class can also have three teachers and three reference books. If it is expressed by relational expression, see Table 8-6.

Table 8-5 Schematic Diagram of Relationship C

Course name c

Teacher's name t

Select reference books

Advanced mathematics

Li Huamin

Tianhua Wang

Lin Jing

Advanced mathematics

Advanced mathematics course

general physics

Wu tiegang

thank

Xu

physics

general physics

General physics foundation

Table 8-6 C relationship

C

T

L

Advanced mathematics

Li Huamin

Advanced mathematics

Advanced mathematics

Li Huamin

Advanced mathematics course

Advanced mathematics

Tianhua Wang

Advanced mathematics

Advanced mathematics

Tianhua Wang

Advanced mathematics course

Advanced mathematics

Lin Jing

Advanced mathematics

Advanced mathematics

Lin Jing

Advanced mathematics course

general physics

Wu tiegang

physics

general physics

Wu tiegang

general physics

general physics

Wu tiegang

General physics foundation

general physics

thank

physics

general physics

thank

general physics

general physics

thank

General physics foundation

general physics

allow

physics

general physics

Xu

general physics

general physics

Xu

General physics foundation

Two things can be seen from this relationship.

(1) The data redundancy of this relationship is very large.

(2) There is a dependency between the attributes of this relationship, which is different from functional dependency.

After carefully analyzing this special dependence, we find that it has two characteristics:

(1) Assuming that X and Y in R(U) have this dependency, once the value of X is determined, a set of Y values can correspond to X.. If it is determined that C is advanced mathematics, there are a set of t values: Li Huamin, Tianhua Wang and Lin Jing. Similarly, C and L have similar dependencies.

(2) When the value of X is determined, its corresponding set of Y values has nothing to do with U-X-Y. For example, in C, a group of teachers corresponding to advanced mathematics class has nothing to do with the reference books of this course, which means that C and T have this dependence, and the determination of T value has nothing to do with U-C-T = L. 。

Obviously, this dependence is not a functional dependence, so we call it a multivalued dependence. If y is multivalued and depends on x, it can be written as x X→→Y Y y.

From the above-mentioned multi-value dependence on X →→→ Y, its first feature shows that the corresponding relationship between X and Y is very arbitrary, and the number of Y values corresponding to a value of X can be set without any mandatory provisions, that is, the value of Y can be from 0 to any number, and the second condition is the main mandatory constraint, that is, the value of Y corresponding to X has nothing to do with U-X-Y X-Y. More precisely, if there is R(U), For any relation R of R(U), if there are tuples S, t∈R, s[X]=t[X] (indicating that the projections of S and T in X are equal), if their projections in U-X-Y (denoted as s[U-X-Y] and T [U-X-Y]).

This situation can be seen from Table 8-7.

Table 8-7 Schematic Diagram of Multivalued Dependencies

X

Y

U-X-Y

s s [X]

t t [X]

s [Y]

t [Y]

X-axis y-axis

U-X-Y

s [X]

t [X]

s [Y]

t [Y]

U-X-Y

X-axis y-axis

…………

…………

…………

…………

…………

…………

After fully understanding the multi-valued dependency, we can define it as follows:

Definition 8- 12 Let R(U) have x and Y (u). If R(U) has anything to do with a certain value of x, there is a set of values of y corresponding to it, and this set of values of y has nothing to do with the attribute values in Z=U-x-y, then it is said that the multi-value of y depends on x, and it is recorded as x →→→ y. 。

In multivalued dependency, if X→→Y y y and Z=U-x-Y≠O, it is called nontrivial multivalued dependency, otherwise it is called trivial multivalued dependency.

A multivalued dependency can have the following properties:

(1) If there is X→→Y Y y in R(U), there must be x→→→ u → u-x-y. 。

(2) If there is X→→→ Y in R(U), there must be X→→→→ Y. 。

Readers should note that when we discuss multivalued dependencies in R(U), it doesn't mean that we don't need to discuss functional dependencies in R(U).

On the contrary, we generally have to find out not only all the multivalued dependencies in R(U), but also all the functional dependencies. Therefore, a complete R(U) should include a functional dependency set F' and a multivalued dependency set F', which can be expressed by R(U, f, F').

As mentioned earlier, the relationship of multi-valued dependence has a particularly large amount of data redundancy. How to reduce data redundancy? It can be seen from the relation C in Example 8-2 that if C(C, t, l) is decomposed into two relations C 1, C2, their redundancy will be obviously reduced.

C 1 (C,T)

C2

The relationship between C 1 and C 2 is shown in table 8-8.

Table 8-8 Relationship C is decomposed into relationships C 1 and C2.

C

T

Advanced mathematics

Advanced mathematics

Advanced mathematics

general physics

general physics

general physics

Li Huamin

Tianhua Wang

Lin Jing

Wu tiegang

thank

allow

C

L

Advanced mathematics

Advanced mathematics

general physics

general physics

general physics

general physics

Advanced mathematics

Highly equivalent curriculum

physics

general physics

General physics foundation

(a) relation C 1 (b) relation C2.

As can be seen from Table 8-8, the reduction of data redundancy is extremely obvious.

From the perspective of multivalued dependency, C 1 and C2 each correspond to a multivalued dependency C C→→→ T and C →→→→→ L, both of which are trivial multivalued dependencies. Therefore, the way to reduce data redundancy in multivalued dependencies is to decompose the relationships into trivial multivalued dependencies.

In this way, we can define a higher normal form than BCNF, which is called the fourth normal form and can be abbreviated as 4NF. The characteristic of this paradigm is that it must meet the following requirements in the relational model:

(1) Only trivial multivalued dependencies are allowed (nontrivial multivalued dependencies are not allowed);

(2) Functional dependency should satisfy BCNF.

Because functional dependency is a special case of multivalued dependency, Unity can define the fourth normal form with the concept of multivalued dependency.

In the definition of 8- 13 R(U), if x →→→ y is a nontrivial multi-valued dependency, then x: must contain keywords. At this time, it is said that R satisfies the fourth normal form, and it is recorded as R∈4NF.

As can be seen from the definition of the fourth normal form, the relationship C defined earlier is BCNF, but not 4NF, because in C(C, t):

C→→T

C→→L

Its keyword is (c, t, l).

Although C∈BCNF, c is not a keyword, so it is obvious that C 1 and C2 are generated by decomposing it by c (4NF. ) there is C →→→ T, so there is no nontrivial multivalued dependence, so there is C 1∈4NF, and similarly there is C2.

knot

We have defined five paradigms in the standardization discussion, and our understanding of these paradigms is gradually deepening. Generally speaking, we can summarize as follows:

The purpose of (1) normalization is to solve the problems of insertion, modification and high data redundancy.

(2) Normalization method: Starting from the dependency relationship (functional dependency and multi-valued dependency) between attributes in patterns, try to make each pattern represent a "thing" in the objective world.

(3) Realization of standardization: adopting the method of pattern decomposition.

The process from first normal form to the fourth paradigm is actually a process of constantly eliminating some disadvantages in dependence. Figure 8-7 shows this process.

Readers should pay attention to the fact that normalization is a theory. To study how to solve anomalies and redundancies through normalization, we need to consider this factor when constructing relational schema in actual database design. However, the objective world is complex, and many other factors need to be considered when constructing the schema, such as excessive schema decomposition, which will inevitably lead to more join operations in data query and affect the query speed. Therefore, it is necessary to integrate all kinds of positive and negative factors in the actual construction schema.

Figure 8-7 Standardization Process

8.3 Some Problems Caused by Standardization

Standardization has caused further research on some problems, which are:

Research on 1. Functional Dependence Theory

Functional dependence and multivalued dependence between attributes are the basic basis of normalization, so it is necessary to further study them, including:

All functional dependencies on (1) relational schema can be obtained from some functional dependencies on relational schema through some axiomatic systems (called Armstrong axioms). Therefore, all functional dependencies on a relational schema can be composed of two parts: the basic part can be obtained directly from semantics, and the other parts can be deduced from axiomatic systems.

(2) The concepts of functional dependency set equivalence and minimum functional dependency set are introduced, that is, if two functional dependency sets can derive the same set, they are said to be equivalent, and the minimum equivalent functional dependency set is called minimum functional dependency set.

These studies provide more basic information for standardization.

2. Research on pattern decomposition.

The realization of standardization mainly depends on continuous pattern decomposition. In the pattern decomposition, the following problems need to be studied:

(1) Whether the information in the relationship will be lost after decomposition is called lossless connection.

(2) Whether the functional dependencies in the relationship will be lost after decomposition is called dependency retention.

(3) Under the condition of lossless connectivity and dependency preservation, which paradigm can be decomposed?

After research, we can get the following facts:

If lossless connection is required, mode decomposition can reach BCNF.

If dependency reservation is needed, the pattern decomposition can reach 3NF, but not necessarily BCNF.

If both lossless connection and dependency preservation are needed, then pattern decomposition can be realized.

3NF, but it may not reach BCNF.

The above three points can be achieved by three algorithms.

Because the detailed discussion of these two issues caused by standardization is complicated, I don't intend to elaborate in this book, but only state the results in the paper for readers' reference.

Exercise 8

1. Please define the following terms:

Functional dependence; (2) keywords; (3) the main attribute set; (4) Multi-valued dependence; (5)2NF; (6)3NF;

(7)BCNF; 4NF。

2. Is S # ((C#) correct in relation SC(S#, C #, G)? Please explain the reason.

3. Is the best pattern structure standardized to be the best structure? Why?

4. Try to prove that if R(BCNF), there must be R(3NF.

5. What is the highest paradigm of the following relational model, and explain the reasons.

R (A,B,C,D),F: {B(D,AB(C };

R (A,B,C),F: {A(B,B(A,A(C };

R (A,B,C,D),F: {A(C,D(B };

R (A,B,C,D),F: {A(C,CD(B}。

s

t

f

p

f

p

p

f

f

f

f

p

p

f

G

S#

C#

storage card

suspended matter

tin

S#

C#

G

S#

storage card

suspended matter

tin

Non-primary attribute set n

zero

zero

Main attribute set p

K 1

Peak. Also known as DAPSANG

K3

K4

zero

zero

zero

S#

c#

G

S#

tin

suspended matter

suspended matter

storage card

S

C

T

C

T

T

S

Non-decisive factor

decision

factor

r:

Eliminate the nontrivial multi-valued dependence of non-keywords as decisive factors

1NF

Eliminate the partial dependence of non-main attributes on keywords

2NF

Eliminate the transmission dependence of non-primary attributes on keywords

3NF

Eliminate the partial dependence and transmission dependence of the main attribute on keywords.

BCNF

Eliminate important and nonfunctional multivalued dependencies.

4NF