Database review (function dependency)

If we are going to design the table schema of a relational database, there is a good chance of redundancy, and in order to avoid this, we need rules that are called dependencies.

The function dependency is simply that attribute set a derives the attribute set B, for example

Given these rules, if a relationship satisfies a given function dependency, the relationship R satisfies the function dependency F;

In the following we will introduce a series of paradigms and decomposition algorithms;

Decomposition merge rules for function dependencies

And

are equivalent (can be converted to each other), the first formula replaces the second is called the merge rule, and the second formula replaces the first one as the decomposition rule;

Trivial function dependency: if A-->b,a is a superset of B, then this function is called trivial.

Trivial dependency rule: if a-->b, it can be changed to a--> (B-A∩B), which can eliminate redundancy;

The function dependency definition of the key:

(1) If there is an attribute set, it can contain all the attributes;

(2) Any true subset of this attribute set cannot contain all attributes;

The attribute set is called the key;

Function Dependent Exercises

Trivial dependency rules

Example:name,age--->name,course, according to the trivial dependency rules, can be simplified to name,age-->course.

Exception

If the pattern of a relationship is not well designed, an exception occurs. The types of exceptions are:

(1) Redundancy: The value of a property occurs multiple times, such as:

(2) Update exception: There is an update exception to the relationship, because if you need to update the database concept, you need to update all the database concept to the left.

(3) Delete exception: The relationship exists delete exception, because will be AVI, Hank, Sudarshan Teacher Delete, then will appear database this course disappears, but in fact the library course will never disappear, but only the teacher resigned.

Attribute closure algorithm

Example:

Application of closures

1. Given the function dependency set F1, is it possible to infer the function dependent f:a-->b?

Just ask if {a}+ contains B, if included, it means that F1 can infer F, otherwise it cannot;

Example:

2. Determine if a property set is a key?

If you want to prove that the property set is a super-key, you only need to calculate the closure of this property set, and see if the closure contains all the properties;

However, if it is necessary to prove that the attribute set is a candidate key, the first step needs to prove that it is a super-key, and the second step is to prove that a property is arbitrarily removed from the attribute set, which is not a super-key;

3. Give some terminology.

Given a function dependency set A;

The basic set of a: a function dependency set with a equivalence;

Minimization of a basic set: satisfying (1) function dependent on the right side only a single attribute (2) Deleting any of the function dependencies will not be the base set (3) Delete any attribute from the left side of the function dependency, then it will not be the base set;

4. Projection function Dependency

Given a relationship and the satisfied function dependency F, which function depends on the partial attribute projection, which functions depend on the satisfied.

For example: The original attribute of the a,b,c relationship, projection to a, B, and what functions are dependent on the establishment?

To put it simply:

(1) First calculate the closure of the single attribute, for example {A} 's closure is {b,c}, if our projection is {A, b}, you need to include a-->b, without a-->c; ( if there is a property set can contain all properties, Then we don't need to ask for the closure of this attribute set.)

(2) then calculate two properties, three attributes, four attributes ..... ;

(3) Minimizing the basic concentration in the finding ;

-- If a function dependency after projection can be rolled out by other functions after projection, it is deleted;

For example: {a-->c,a-->b,b-->c}, delete a-->c, because this function relies on other functions to derive the dependency;

-- If a function relies on a-->b, deleting a property in A is still established in the projection's function dependency;

For example: After the projection of the function depends on {ac-->b,a-->b}, the {ac-->b} is deleted, because if the deletion of C is a-->b can be launched from the projection of the function dependencies;

Example:

5. Judging non-destructive decomposition

If the R1∩R2 is a R1 or R2, the decomposition (R1,R2) on R is a lossless decomposition.

Armstrong Axiom

Here we will talk about BCNF decomposition and 3NF decomposition, and decomposition is also a criterion:

(1) Elimination of anomalies;

(2) Non-destructive connection: After the decomposition and re-connection is consistent with the original relationship;

Using the Chase test law;

(3) to remain dependent: the projection of FD is established after the decomposition of the relationship, but the relationship after the connection does not satisfy the original FD;

BCNF meet (1) and (2), 3NF satisfied (1) (2) (3)

Chase Test Law

Determine whether the natural connection is still the original relationship after decomposition;

Thought: Each tuple after the natural connection belongs to the original relationship;

If the relationship is projected into the SI and then the natural connection, you will get a tuple of all components without the subscript, the tuple is not in R, the connection is lossless;

Example:

Stay dependent

Thought: FD projection is established in the relationship of decomposition, but the natural connection cannot satisfy the original FD

Example:

BCNF

This relationship R belongs to bcnf when the relationship satisfies if the left side of the non-trivial function dependency of R is a super-key;

Nature: Any one two-dollar relationship satisfies BCNF

Proof: If the function depends on the concentration of a-->b, then we can say a-->ab, so a must be a super-key;

BCNF decomposition

Note: BCNF decomposition is non-destructive decomposition

while (find a function dependency that violates BCNF) {

Find out the function dependency a-->b that violates BCNF, calculate A's closure first, and replace B with A's closure (minus a), and decompose it into {A +} and {AU (R (A)}; For example A-->b, and {a}+={a,b,c}, replace a-->b; with A-->BC

The projection fd set that satisfies the decomposed relationship is obtained.

Then see if the FD set of the decomposed relationship satisfies the bcnf, and if it is not satisfied, it continues to decompose.

}

Example:

3NF

Note: 3NF is capable of non-destructive connection and remains dependent;

Relaxed the requirements of the BCNF, one more condition is: if a-->b, then b-a as part of the candidate key ( even in different candidate keys), then satisfies 3NF;

Here we explain the red part of the above sentence to see an example:

For example: There is a function dependent A-->BC,{AB}{AC} is a candidate key, and Bc-a={bc},b belongs to the candidate key {Ab},c belongs to the candidate key {AC}, but a-->bc still satisfies 3NF;

Main attribute: A property belongs to a candidate key;

For example: {AC} is a candidate key, then A is the main attribute, C is also the main attribute;

3NF Decomposition algorithm

Example:

1NF

Each attribute remains atomic;

2NF

Satisfies 1NF, and each non-principal attribute is fully functional dependent on the main code of R;

Multi-valued dependency

Reasons for introducing multi-valued dependencies

As you can see, the candidate key for this relationship is (name,street,city,title,year), so there is no nontrivial function dependency, so obey bcnf, but we can easily see (street, City) and (title, Year) is independent, and is redundant in a relationship;

Therefore, the generation of multi-valued dependence is derived from this; we can decompose the relationship according to the 4NF decomposition algorithm;

Multi-valued dependency (MVD) definition

A1A2 .... An-->-->b1b2 .... Bm, the description

(1) Given a1a2 ... The value of an, b1b2 ... The BM attribute is independent of the (u-a-b) attribute;

(2) given r two tuples A, B, their A attribute is the same, there must be a third tuple C, making c[a]=a[a]=b[a],c[b]=a[b],c[u-a-b]=b[u-a-b];

Multi-valued dependency property

(0) Multi-valued dependence is actually the escalation of FD, that is, if A-->B is established, then A-->-->B is also established;

(1) Trivial MVD: If B is a subset of a, then A-->-->B is established;

(2) Attaching trivial MVD: if the relationship R (A, b), then A-->-->b was established;

(3) Transmission MVD: If a-->-->b,b-->-->c, then a-->-->c established;

(4) MVD does not satisfy the decomposition rule:

(5) If a-->-->b, then a-->--> (R-a-b) is established;

4NF

Applied to MVD, not general FD; the condition and bcnf are similar;

Definition: If there is a non-trivial a-->-->b, and a must be a super-key, then 4NF;

4NF Decomposition algorithm

1. To find a violation of 4NF MVD;

2. Decompose to {AB} {R-a-b} according to A-->-->b;

3. Calculate projection fd;

4. Continue to find the violation of MVD, decomposition ..... ；

Summarize:

Database review (function dependency) (GO)