Functional dependency

src: i.ytimg.com

In relational database theory, functional dependency is the constraint between the two sets of attributes in the relation of the database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation.

Given a relation of R , a set of X attributes in R is functionally specifies another set of attributes Y , also in R , if (and X -> Y ) if, and only if, respectively X the value in R is associated with exactly one Y value in R ; R is then said to be satisfactory functional dependency X -> Y . Equivalently, projection $Â\; Â{\displaystyle Â\; Â\; Â\; Â\; Â\; Â\; Â}$ _{          ?                 Â         Â mo moan,      Â¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯                          R               {\ displaystyle \ pi_ {X, Y} R}} is a function, ie Y is the function X . In simple words, if the values ​​for the X attribute are known (say they are x ), then the values ​​for the Y attribute corresponding to x can be determined by looking them in any tuple R that contains x . Usually X is called the determinant and Y dependent settings. FD functional dependency: X -> Y is called trivial if Y is part of X >

In other words, FD dependence: X -> Y means that the value of Y is determined by the value X . Two tuples sharing the same value X will always have the same value Y .

The determination of functional dependencies is an important part of designing databases in relational models, and in database normalization and denormalization. A simple application of functional dependencies is Heath Theorem ; he says that the relation R above the attribute that governs U and satisfies the functional dependency X -> Y can become split safely in two relations that have a decomposition property that joins without lossless, ie into $Â\; Â{\displaystyle Â\; Â\; Â\; Â\; Â\; Â\; Â}$ _{          ?                 Â      Â¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯                         (         R        )         ?                  ?                 Â      Â · Z                         (         R        )         =         R               {\ displaystyle \ pi_ {XY} (R) \ bowtie \ pi_ {XZ} (R) = R}} where Z = U - XY is the remaining attribute. (The set of attribute states is usually marked by only alignment in database theory.) An important notion in this context is the candidate key, which is defined as a minimal set of attributes that functionally define all attributes in a relation. Functional dependencies, along with attribute domains, are chosen so as to generate constraints that will exclude as much data as is inconsistent with the user domain of the system.

Gagasan tentang implikasi logis didefinisikan untuk dependensi fungsional dengan cara berikut: satu set dependensi fungsional ${\ displaystyle \ Sigma}  ÂÂ$ secara logis mengimplikasikan kumpulan dependensi lainnya ${\ displaystyle \ Gamma}  ÂÂ$ , jika ada relasi R yang memuaskan semua dependensi dari ${\ displaystyle \ Sigma}  ÂÂ$ juga memenuhi semua dependensi dari ${\ displaystyle \ Gamma}  ÂÂ$ ; ini biasanya ditulis ${\ displaystyle \ Sigma \ models \ Gamma}  ÂÂ$ . Gagasan implikasi logis untuk dependensi fungsional mengakui aksioaksi terbatas suara dan lengkap, yang dikenal sebagai aksioma Armstrong .

Video Functional dependency

Example

Car

Suppose someone is designing a system to track their vehicles and engine capacity. Each vehicle has a unique vehicle identification number (VIN). One will write VIN -> EngineCapacity because it's not appropriate for a vehicle engine to have more than one capacity. (Assuming, in this case, that the vehicle has only one machine.) Transversely, EngineCapacity -> VIN is wrong because there may be many vehicles with the same engine capacity.

This functional dependency may indicate that the EngineCapacity attribute is placed in association with the VIN of the key candidate. However, that may not always be right. For example, if that functional dependency occurs as a result of the transitive functional dependence of VIN -> VehicleModel and VehicleModel -> EngineCapacity then it will not produce a normal connection.

Lecture

This example illustrates the concept of functional dependency. The situation modeled is that students who visit one or more lectures in each are assigned as teaching assistants (TA). Let's assume that each student is within a few semesters and is identified by a unique integer ID.

We see that every time two rows in this table display the same StudentID, they must also have the same Semester value. This basic fact can be expressed by functional dependency:

• StudentID -> Semester.

Note that if the row is added where the student has a different value from the semester that the functional dependency, FD, will not exist anymore. This means that FD is implied by the data because it is possible to have a value that will cancel the FD.

Other trivial functional dependencies can be identified, for example:

• {StudentID, Lecture} -> TA
• {StudentID, Lecture} -> {TA, Semester}

The latter states the fact that the set {StudentID, Lecture} is the superkey of the relation.

Employee department model

The classic example of functional dependency is the employee department model.

This case represents an example where some functional dependencies are embedded in a single data representation. Note that since an employee can only be a member of one department, the unique ID of the employee specifies the department.

• Employee ID -> Employee Name
• Employee ID -> Department ID

In addition to these relationships, tables also have functional dependencies through non-key attributes

• Department ID -> Department Name

This example shows that even if there is an Employee ID FD -> Department ID - the employee ID will not be a logical key for the determination of department ID. The data normalization process will recognize all FDs and allow the designer to build more logical tables and relationships based on data.

Maps Functional dependency

Properties and axioms of functional dependencies

Given that X , Y , and Z is a collection of attributes in a relation R , one can obtain some functional dependency property. Among the most important are the following, usually called the Armstrong axiom:

• Reflexivity : If Y is part of X , then X -> Y
• Augmentation : If X -> Y , then XZ -> YZ
• Transitivity : If X -> Y and Y -> Z , then X -> Z

"Reflexivity" can be attenuated to only ${\ displaystyle X \ rightarrow \ varnothing}$ , it is a true axiom, in which the other two are the correct inference rules, more precisely resulting in the following syntax consequences:

${\ displaystyle \ vdash X \ rightarrow \ varnothing}  ÂÂ$
${\ displaystyle X \ rightarrow Y \ vdash XZ \ rightarrow YZ}  ÂÂ$
$            {\displaystyle         X        ->        Y        ,        Y        ->        Z        ?        X        ->        Z      }        \{\backslash \; displaystyle\; X\; \backslash \; rightarrow\; Y,\; Y\; \backslash \; rightarrow\; Z\; \backslash \; vdash\; X\; \backslash \; rightarrow\; Z\}\;  ÂÂ$ .

These three rules are the complete sound and axiom of functional dependence. This anxiomation is sometimes described as limited because of the limited number of inference rules, with the warning that axioms and rules of inference are all schemata, meaning that X , Y and Z line above all basic terms (set of attributes).

Dari aturan ini, kita dapat memperoleh aturan sekunder ini:

• Union : Jika X -> Y dan X -> Z , lalu X -> YZ
• Dekomposisi : Jika X -> YZ , maka X -> Y dan X -> Z
• Pseudotransitivity : Jika X -> Y dan WY -> Z , lalu WX -> Z

The rules of unification and decomposition can be combined in a logical equation which states that X -> YZ , holds iff X -> Y > and X -> Z . This is sometimes called the separation/merging rule.

Other rules that are sometimes useful are:

• Composition : If X -> Y /i>, then XZ -> YW

src: slideplayer.com

Functional closure closure

Closure is basically a complete set of values ​​that can be determined from a set of values ​​known for a particular relationship using its functional dependencies. One uses the Armstrong axiom to provide evidence - namely reflexivity, augmentation, transitivity.

Diberikan ${\ displaystyle R}  ÂÂ$ dan ${\ displaystyle F}  ÂÂ$ sekumpulan FD yang tersimpan dalam ${\ displaystyle R}  ÂÂ$ : Penutupan ${\ displaystyle F}  ÂÂ$ dalam ${\ displaystyle R}  ÂÂ$ (dilambangkan ${\ displaystyle F}  ÂÂ$ ) adalah himpunan semua FD yang secara logis tersirat oleh ${\ displaystyle F}  ÂÂ$ .

Penutupan sekumpulan atribut

Penutupan satu set atribut X sehubungan dengan ${\ displaystyle F}  ÂÂ$ adalah himpunan X dari semua atribut yang secara fungsional ditentukan oleh X menggunakan ${\ displaystyle F}  ÂÂ$ .

Contoh

Imagine the following FD list. We will calculate closing A of this relationship.

1. A -> B
2. B -> C
3. AB -> D

The closure is as follows:

a) A -> A (with Armstrong reflexivity) b) A -> AB (by 1. and (a))
c) A -> ABD (by (b), 3, and Armstrong transitivity) d) A -> ABCD (by (c), and 2)

Therefore the closure is A -> ABCD. By calculating closing A, we have validated that A is also a good candidate key because the closure is any data value in the relationship.

src: i.ytimg.com

Cover and equality

Include

Definisi : ${\ displaystyle F}  ÂÂ$ mencakup ${\ displaystyle G}  ÂÂ$ jika setiap FD dalam ${\ displaystyle G}  ÂÂ$ dapat disimpulkan dari ${\ displaystyle F}  ÂÂ$ . ${\ displaystyle F}  ÂÂ$ mencakup ${\ displaystyle G}  ÂÂ$ jika ${\ displaystyle G}  ÂÂ$ ? ${\ displaystyle F}  ÂÂ$
Setiap set dependensi fungsional memiliki penutup kanonik.

Kesetaraan dua set FD

Dua set FD ${\ displaystyle F}  ÂÂ$ dan ${\ displaystyle G}  ÂÂ$ melalui skema ${\ displaystyle R}  ÂÂ$ ekuivalen, ditulis ${\ displaystyle F}  ÂÂ$ ? ${\ displaystyle G}  ÂÂ$ , jika ${\ displaystyle F}  ÂÂ$ = ${\ displaystyle G}  ÂÂ$ . Jika ${\ displaystyle F}  ÂÂ$ ? ${\ displaystyle G}  ÂÂ$ , lalu ${\ displaystyle F}  ÂÂ$ adalah penutup untuk ${\ displaystyle G}  ÂÂ$ dan sebaliknya. Dengan kata lain, set dependensi fungsional setara disebut mencakup satu sama lain.

Sampul non-redundan

Properti penting (menghasilkan aplikasi langsung) dari dependensi fungsional adalah jika R adalah relasi dengan kolom yang dinamai dari beberapa himpunan attribut U dan R memenuhi beberapa ketergantungan fungsional X -> Y lalu ${\ displaystyle R = \ pi_ {XY} (R) \ bowtie \ pi_ {XZ} (R)} Â Â$ di mana Z = U - XY . Secara intuitif, jika ketergantungan fungsional X -> Y bertahan di R , maka relasi dapat dengan aman dibagi dalam second hubungan bersama dengan kolom X (yang merupakan kunci untuk ${\ displaystyle \ pi_ {XY} (R) \ bowtie \ pi_ {XZ} (R)} Â$ ) memastikan bahwa ketika dua bagian bergabung kembali tidak ada data yang hilang, yaitu ketergantungan fungsional menyediakan cara sederhana untuk membangun dekomposisi bergabung tanpa loss dari R dalam second hubungan yang lebih kecil. Fakta ini kadang-kadang disebut Theorem Heath ; Ini adalah salah satu hasil awal dalam teori basis data.

Theorem Heath secara efektif mengatakan kita dapat mengeluarkan nilai-nilai Y dari relasi besar R dan menyimpannya menjadi satu, ${\ displaystyle \ pi_ {XY} (R)} Â$ , yang tidak memiliki pengulangan nilai pada baris untuk X dan secara efektif adalah tabel pencarian untuk Y yang dikunci oleh X dan akibatnya hanya memiliki satu tempat untuk memperbarui Y yang sesuai dengan masing-masing X tidak seperti hubungan "besar" R di mana ada banyak salinan berpotensi dari masing-masing X , masing-masing dengan salinannya Y yang perlu tetap disinkronkan pada pembaruan. (Penghapusan redundansi ini merupakan keuntungan dalam konteks OLTP, di mana banyak perubahan yang diharapkan, tetapi tidak begitu banyak dalam konteks OLAP, yang melibatkan sebagian besar pertanyaan.) Dekomposisi Heath hanya menyisakan X untuk bertindak sebagai kunci asing di sisa tabel besar ${\ displaystyle \ pi_ {XZ} (R)} Â Â$ .

However, functional dependency should not be equated with the dependency of inclusion, which is a formalism for foreign keys; Although they are used for normalization, functional dependencies express the constraints on a single relation (schema), while the inclusion dependence expresses the constraint between the relation schema in the database schema. Furthermore, these two concepts are not even tangent in the classification of dependencies: functional dependencies are dependencies that produce equality while the inclusion dependency is the dependency of the tuple generator. Enforcing referential restrictions after the decomposition of the relation scheme (normalization) requires new formalism, ie inclusion dependencies. In the decomposition resulting from Heath's theorem, nothing prevents the insertion of tuples in $Â\; Â{\displaystyle Â\; Â\; Â\; Â\; Â\; Â\; Â}$ _{          ?                 Â      Â · Z                         (         R        )               {\ displaystyle \ pi_ {XZ} (R)}} has some value X not found in _{          ?                 Â      Â¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯                         (         R        )               {\ displaystyle \ pi_ {XY} (R)}  Â} .

Normal form

The normal form is the level of database normalization that determines the "goodness" of a table. Generally, the third normal form is considered a "good" standard for relational databases.

Normalization aims to free the database from renewal, insertion and deletion of anomalies. It also ensures that when new values ​​are introduced into the relation, it has minimal effect on the database, and thus minimal effect on the application using the database.

src: slideplayer.com

The function is not described depending on set

A set of functionally dependent S can not be reduced if the set has the following three properties:

1. Each right string of the functional dependency of S contains only one attribute.
2. Each left set of functional dependence S can not be reduced. This means subtracting one attribute from the left set will change the content S (S will lose some information).
3. Reducing any functional dependency will change the content of S.

The set of functional dependencies with this property is also called canonical or minimal .

src: i.ytimg.com

See also

• Chase (algorithm)
• Inclusion dependency
• Join dependency
• Multi-valued dependency (MVD)
• Normalization of databases
• The first normal form

src: i.ytimg.com

References

src: i.ytimg.com

External links

• Gary Burt (Summer 1999). "CS 461 (Database Management Systems) lecture notes". University of Maryland Baltimore County Department of Computer Science and Electrical Engineering.
• Jeffrey D. Ullman. "CS345 Lecture Notes" (PostScript) . Stanford University. Osmar Zaiane (June 9, 1998). "Chapter 6: Constraints of Integrity". CMPT 354 (Database Systems I) lecture notes . Department of Computational Sciences Simon Fraser University. Ã,

Source of the article : Wikipedia