Learning and thinking -- Analyzing function dependencies and multi-value dependencies in Databases

Learning and thinking -- Analyzing function dependencies and multi-value dependencies in Databases

Let's first review what is a function: A function corresponds to a set (I like to understand it in graphs)

The figure shows single, full, and double shots in the function. In short, it is the correspondence relationship of the set. The values in the set generally meet the one-to-one and many-to-one relationships, and there cannot be one-to-many relationships.

Based on the above understanding of functions, we provide the definition of function dependencies:

If R (U) is the relational mode on the property set U, X and Y are the subsets of U, in r, the attribute values of two ancestor members on X cannot be the same, but the values in Y cannot be the same, which is recorded as X → Y. In short, function dependency is a property set that determines another property set. It is called that another property set depends on this property set. (In fact, there is no one-to-many relationship)

First, we need to distinguish between function dependency and attribute set attributes.

Set R (U) to the relational mode on the property set U, and X and Y to the subset of U:

● If the relationship between X and Y is (one-to-one), for example, if the relationship between the school and the principal is, the function depends on X → Y and Y → X.

● If the relationship between X and Y is 1: n (one-to-multiple relationship), for example, if the relationship between age and name is 1: n, the function depends on Y → X.

● If the relationship between X and Y is m: n (many-to-many relationship), if the relationship between students and courses is m: n, then X and Y

Use mathematical ing relationships, such as f (x) = 2x; f (x, y) = x ^ + 2y; f (x, y, z) = x + y-z, etc. The variable element in the function corresponds to X in U, and all the values in X form Y after calculation of the function relationship.

Secondly, function dependencies include the following types:

1. trivial function dependency

When the property set Y in a link is a subset of the property set X, the function depends on X → Y, that is, a group of property functions determine all its subsets. Such function dependency is called the trivial function dependency.

2. non-trivial function dependency

If function dependency X → Y exists when the property set Y in the link is not a subset of the property set X, the function dependency is called non-trivial function dependency.

3. Full function dependency

Set X and Y to the two Property sets of relational R. x' is the real subset of X, and there is X → Y, but there is X for every x '! → Y, which means that the complete function of Y depends on X.

4. Some function dependencies

If X and Y are the two Property sets of relational R and exist in X → Y, if x' is the true subset of X and there is X' → Y, then some functions of Y depend on X.

5. Pass function dependency

Set X, Y, and Z to an attribute set that is different from each other in relational R. X → Y (Y! → X), Y → Z, that is, the Z-passing function depends on X. (If Y → X, then X → Z is true)

Use an example to understand the above definition (A-\ → B indicates that A does not depend on B)

Table 1 -- Student (student ID, name, gender, age, school)

Assume that duplicate names are not allowed for table creation.

Student ID → gender, student ID → age, student ID → School, student ID → name, name → gender, name → age, name → school, but gender-\ → Sage

Bytes -----------------------------------------------------------------------------------------------------------------

Table 2-Student Course (student ID, course, score)

Student ID → course, (student ID, course) → score

Bytes -----------------------------------------------------------------------------------------------------------------

Table 3-Faculty (student ID, Department name, Department Head)

Student ID → department name, Department name → department head

In the above example:

Ordinary function dependency: (student ID, course) → student ID, (student ID, course) → course.

Because the student ID and course are both a subset of the student ID and course.

Non-trivial function dependency: (student ID, course) → score (non-trivial function dependency is discussed in the database, but it is of little significance)

Because the score is not a subset of (student ID, course.

Some function dependencies: (student ID, name) → gender

Because (student ID, name) → gender and student ID → gender

Full function dependency: (student ID, course number) → score

Because (student ID, course number) → score, student ID-\ → score, course number-\ → score.

Transfer Function dependency: Student ID → department head

Because the student ID → department name, Department name → department head and department name-\ → student ID.

What about multi-value dependency? Define

Multi-value dependency definition:
Let R (U) be a relational pattern on an attribute set U, X, Y, and Z are subsets of U, and Z = U-X-Y, multi-value dependency X-> Y is true. If only one of the R relations is r, each value of r on (X, Z) corresponds to a group of Y values, this set of values is only determined by the X value and is not related to the Z value.
If X-> Y and Z = empty set, X-> Y is an ordinary multi-value dependency. Otherwise, X-> Y is a non-trivial multi-value dependency.

We still use the ing relationship of the function to understand and introduce f (x, y). Suppose f (x, y) = +-(x ^), if z = f (x, y), then an x value can be used to obtain two z pairs with opposite numbers. In this binary function relationship, the value of y does not actually affect the result. Similar to the relational mode, x is the property value of X set, y is the property value of Y set, and z is the property value of Z set.

Let's look at an example.

(Course C) -- (instructor T)-(reference B)

Database probability-instructor Gu Guosheng-Mathematical Analysis
Database probability-instructor Gu Guosheng-Higher Algebra
Database probability-instructor Gu Guosheng-Differential Equations

In the preceding example, U {C, T, B}, where course C and instructor T uniquely determine a group of B, but it has nothing to do with T. It is determined by C. However, T is not an empty set, therefore, it is a non-trivial multi-value dependency.

In fact, when T is null, if every value C in c corresponds to a definite value in B, it becomes a function dependency. Therefore, function dependency is a special case of multi-value dependency.

Total:

1. function dependency is not a constraint that is met by one or some relational instances in relational mode R, but a constraint that must be met by all relational instances in R.

2. function dependency is the concept of semantic category. Function dependencies can only be determined based on the data semantics. (For example, the function "name → age" is dependent only when the same celebrity is not allowed)

3. function dependencies are usually discussed in a collection.

4. Database designers define functional relationships as needed. For example, "Name> Age" is considered feasible in a specific table.

5. Database designers make mandatory requirements according to actual regulations. For example, if the same celebrity is not allowed to appear, the function depends on "Name> age. The inserted tuples must meet the requirements.

Function dependency. If the same celebrity exists, this tuple is not loaded.