[Database review] function dependency

Document directory

• Example:

If we want to design the table mode of a relational database, there may be redundancy. To avoid this situation, we need some rules called dependencies.

Function dependency is simply to say that attribute set a exports Attribute Set B, such

Given these rules, if a link can satisfy the given function dependency, the relational R satisfies the function dependency F;

 

Below we will introduce a series of paradigms and decomposition algorithms;

 

Decomposition and merging rules of function dependencies

 

And

 

It is equivalent (which can be converted to each other). The first sub-statement replaces the second sub-statement, and the second sub-statement replaces the first sub-statement with the decomposition rule;

Ordinary function dependency: If a --> B, A is the superset of B, this function dependency is called ordinary.

Ordinary dependency rules: If a --> B, it can be changed to a --> (B-A ipvb), this can eliminate redundancy;

 

Function dependency definition of the key:

 

(1) If an attribute set exists, all attributes can be contained;

(2) Any real subset of this property set cannot contain all attributes;

This attribute set is called a key;

 

Function dependency exercise

 

 

Ordinary dependency rules

 

 

 

Example:Name, age ---> name, course, can be simplified to name, age --> course.

 

Exception

 

If the mode of a link is not well designed, an exception occurs. The exception type is:

(1) redundancy: the value of an attribute appears multiple times, for example:

(2) Update exception: There is an update exception in the relationship, because if you need to update the database as Database concept, you need to update all the databases on the left to Database concept;

(3) Deletion exception: the link deletion exception exists because the course Avi, Hank, and sudarshan will disappear, but in fact, the database course will never disappear, but the teacher resigned.

Attribute closure algorithm

 

 

 

Example:

 

 

 

Closure Application

 

1. Given the function dependency set F1, can I infer the function dependency F: A --> B?

 

You only need to determine whether {A} + contains B. If yes, it means that F1 can deduce F; otherwise, it cannot;

Example:

 

2. Check whether an attribute set is a key?

 

To prove that an attribute set is a superkey, you only need to calculate the closure of this attribute set to see if the closure contains all the attributes;

However, if you need to prove that the attribute set is a candidate key, the first step needs to prove that it is a superkey. The second step needs to prove that an attribute is removed from the attribute set, and this attribute set is not a superkey;

 

3. Some terms are provided.

 

Given a function dependency set;

Basic set of A: function dependency set equivalent to;

Minimal basic set of A: (1) only a single attribute is on the right side of the function dependency. (2) deleting any function dependency is not a basic set. (3) deleting any attribute from the left of the function dependency is not a basic set;

 

 

 

4. Projection Function dependency

 

Given a relationship and a satisfied function dependency F, the function dependency is satisfied after partial property projection.

For example, which function dependencies are established after the original attributes are A, B, and C are projected to A and B?

Simply put:

(1) Calculate the closure of a single attribute. For example, the closure of {A} is {B, c}. If our projection is {a, B }, you need to include a --> B, but not a --> C. (If an attribute set can contain all attributes, we do not need to evaluate the closure of this attribute set)

(2) Calculate double attributes, three attributes, and four attributes .....;

(3) The basic set is minimized;

-- If a projection function dependency can be introduced by other projection function dependencies, it will be deleted;

For example: {A --> C, a --> B, B --> C}, delete a --> C, because this function dependency can be deduced through other function dependencies;

-- If a function depends on a --> B, it is deleted if an attribute in A is still in the projected function dependency;

For example, if the projected function dependency is {ac --> B, A --> B}, delete {ac --> B, if C is deleted, that is, a --> B can be introduced from the projection function dependency;

 

Example:

 

5. Determine lossless Decomposition

 

If R1 ipvr2 is the supercode of R1 or R2, the decomposition (R1, R2) on R is lossless decomposition.

Armstrong Principle

 

Next we will talk about bcnf decomposition and 3nf decomposition, which are also judged by criteria:

(1) eliminate exceptions;

(2) lossless connection: whether the connection after decomposition is consistent with the original relationship;

Use Chase to test the rule;

(3) Maintain dependency: the projection of FD is established in the decomposed relationship, but the connected relationship does not meet the original FD;

Bcnf satisfied (1) and (2), 3nf satisfied (1) (2) (3)

Chase test law

 

Determine whether the natural connection is the original link after decomposition;

Thought: Every tuples after the natural connection belong to the original relationship;

If the link is projected to Si and then connected to each other, a tuple with no lower-mark is obtained. If this tuple is not in R, the join is lossless;

 

Example:

 

Maintain dependency

 

Idea: FD projection is established in the relationship of decomposition, but it cannot satisfy the original FD after natural connection.

 

Example:

 

Bcnf

 

If the relationship is met, if the left side of the non-trivial function dependency in R is a superkey, the relationship R belongs to bcnf;

 

Nature: Any binary relationship satisfies bcnf

 

Proof: If a --> B exists in the function dependency set, we can say a --> AB, so a must be a superkey;

 

Bcnf Decomposition

 

Note: bcnf decomposition is lossless decomposition.

 

While (find function dependencies that violate bcnf ){

Find the function that violates bcnf depends on a --> B. Calculate the closure of a and replace B with the closure of a (except, and divide it into {A +} and {Au (R-(A +)}; // For example, a --> B, and {A} + = {A, B, c}, use a --> BC to replace a --> B;

Find the projection FD set that satisfies the decomposed relationship;

Check whether the FD set of the decomposed relationship meets bcnf. If not, continue the decomposition.

}

Example:

3nf

 

Note: 3nf is capable of lossless connection and dependency persistence;

 

Relaxed bcnf requirements, one more condition: If a --> B, then the B-A is part of the candidate key (even in different candidate keys), then 3nf is satisfied;

Here we will explain the red annotation in the above sentence. Let's look at an example:

For example: function dependency a --> BC, {AB} {AC} are candidate keys, while BC-A = {BC}, B belongs to the candidate key {AB }, c is the candidate key {AC}, but a --> BC still meets 3nf;

 

Primary attribute: A property belongs to a candidate key;

For example, if {AC} is the candidate key, a is the primary attribute and C is the primary attribute;

 

3nf decomposition algorithm

 

 

Example:

1nf

 

Each attribute maintains atomicity;

 

2nf

 

1 NF is satisfied, and each non-primary attribute fully depends on the primary code of R;

Multi-value dependency

 

Reasons for introducing multi-value dependency

 

 

 

It can be seen that the candidate key for this relationship is (name, street, city, title, year), so there is no non-trivial function dependency, so follow bcnf, however, we can easily see that (street, city) and (title, year) are independent; they are redundant in a relationship;

Therefore, the generation of multi-value dependencies stems from this. We can break down the relationship according to the 4nf decomposition algorithm;

 

Multi-value dependency (MVPs) Definition

 

A1a2... An --> b1b2... Bm, it indicates

(1) Given the value of a1a2... An, The b1b2... Bm attribute is independent of the (U-A-B) attribute;

(2) given the two tuples A and B in R, their A attributes are the same, there must be a third tuple C, make C [a] = A [a] = B [a], C [B] = A [B], C [U-A-B] = B [U-A-B];

 

 

 

Multi-value dependency

 

(0) multi-value dependency is actually an upgraded FD, that is, if a --> B is true, a --> B is also true;

(1) Ordinary MVPs: If B is a subset of a, a --> B is true;

(2) append the ordinary MVPs: if the relationship R (a, B), then a --> B is true;

(3) passing the MVPs: If a --> B, B --> C, a --> C is true;

(4) The MVPs do not meet the decomposition rules:

(5) If a --> B, then a --> (R-A-B) is true;

4nf

 

It is used to apply the value of each parameter to the value of each parameter, rather than the general FD. The condition is similar to that of bcnf;

Definition: If a --> B exists and A must be a superkey, It is 4nf;

 

4nf decomposition algorithm

 

1. Find the 4 NF violation of the MVPs;

2. According to a --> B into {AB} {R-A-B };

3. Computing projection FD;

4. Continue to find the offending MVPs and break them down .....;

 

Summary: