Databases. Normalization. Functional dependencies of attributes. Examples. Building functional dependency diagrams

Normalization. Functional dependencies of attributes. Examples. Building functional dependency diagrams

Before studying this topic, it is recommended that you familiarize yourself with the topic:

First normal form (1NF). Converting the table to first normal form. Examples

Search other websites:

1. The concept of functional dependence. Definition. Examples

After the table is reduced to the first normal form 1NF, it is necessary to determine the functional relationship between the attributes (fields, columns) of the table. This is necessary to ensure that the database tables are built as rationally as possible.

A functional dependency is a relationship that can arise between entities stored in a database. If entity A functionally defines entity B, then such a dependence is usually denoted as follows:

A→B

here

A – determinant of relationship;
B – dependent part.

Example 1. Let the relation be given, which determines the official salaries of the employees of a certain enterprise.

In the above table, an employee’s salary is determined by the position he holds. If an employee moves to another position, then his salary also changes. So, the attribute Position functionally defines the attribute Salary. In symbolic form, such dependence is denoted as follows

Position → Salary

here Position is a determinant, Salary is a dependent part.

Example 2. The dependence of an attribute on a composite key is demonstrated. Figure 1 shows a table of production of various car models in different years.

The relationship key is the Brand–Model-“Year of release” attributes. The “Number of released models” attribute depends on the key.

Figure 1. Functional dependence of the “Number of released models” attribute on the key

⇑

2. The degrees of functional dependence. Classification

There are the following degrees of functional dependence between attributes:

partial dependence. This dependency can occur when the table contains a composite key. A composite key is a table key that consists of several attributes. If a key consists of one attribute, then this key is simple. In case of partial dependence, one attribute of the table is dependent on a part of the key, that is, on a separate attribute included in the key of the relation;
complete dependence. This is the case when there is a dependency between attributes;
transitive dependency. It is a dependency when two attributes are related through a third attribute. This third attribute mediates;
multivalued dependence. This is the case when one value of one attribute corresponds to several values of another attribute.

⇑

3. Partial dependence. Examples

Example 1. Let the following table be given.

Suppose that the primary key in the table is a combination of attributes Employee number – Position. This key is compound. There is a functional relationship between the compound key Employee number – Position and the Salary attribute. This is due to the fact that the salary of an employee depends on the position held. Symbolically, this dependence can be designated as follows: Position → Salary.

Since the Salary attribute depends only on a part of the key (the Position attribute) and not on the entire key (Employee number – Position), this functional dependence is partial.

Conclusion: a separate attribute Salary depends on a certain part of the composite key, which is a pair of attributes Employee number – Position.
Figure 2 schematically shows a partial dependence.

Figure 2. Partial dependence of the Salary attribute on the relation key

Example 2. Let there be given a table of accounting of the classes held in the educational institution.

To ensure uniqueness, the attributes “Classroom number” – “Lesson number” – “Date” – “Teacher” are selected as the key of the relationship. Since a specific discipline is assigned to the teacher, the Discipline attribute is dependent on the Teacher attribute. This means that the Discipline attribute depends on the part of the relationship key, since the Teacher attribute is part of the relationship key. Figure 3 shows this relationship.

Figure 3. Partial dependence of the attribute Subject on the key of the relationship

⇑

4. Complete functional dependence. Examples

A complete functional relationship between two attributes is the case when there is a direct (A→B) and an inverse (B→A) relationship between two attributes A and B. With complete functional dependence, one value of attribute A corresponds to only one value of attribute B. Conversely, one value of attribute B corresponds to the value of attribute A.
The complete functional relationship between the two attributes A and B is denoted A↔B.

Example 1. Let there be given a database of accounting of the educational process, the table of which contains the attributes:

Year – academic year;
Course – a course of study in which a student (group) is studying.

The snippet of the database table is as follows

There is a complete relationship between these attributes. This means that the course in which the student is studying can be determined for the academic year. And, conversely, according to the course of study, you can determine the academic year (provided that the student has successfully passed all the sessions and has no debts in the discipline “Organization of databases and knowledge” :).

In the dependency diagram, such a relationship is indicated as follows

Figure 4. Full dependence between the attributes Year and Course

Example 2. The relationship between the identification number and the citizen’s name. An identification number can be identified by a citizen’s name. Conversely, the identity number can be used to determine the name of a citizen.

⇑

5. Examples of transitive dependency

Example 1. A database containing information about the course of the educational process in the educational institution is set. In relation (table), attributes (fields) are used that have transitive dependencies.

There is a transitive relationship between the Teacher and Group attributes (Figure 5).

Figure 5. The transitive relationship between the Teacher and Group attributes

Reasoning. A subject is assigned to each teacher. That is, the Subject attribute is functionally dependent on the Teacher attribute. According to the curriculum, each group has a list of subjects that it must study. By subject, you can determine the group (or several groups) in which this subject is taught. For example, the subjects of the computer cycle (Databases) will study groups of computer specialties. Therefore, there is also a functional relationship between discipline and group.

Since a specific teacher is assigned to each subject, there is a transitive relationship between the attributes of the Teacher and the Group (the Teacher teaches the subject in a group).

Example 2. Let there be given a table of scholarships for students of a certain group. Students can study on a government order (budget) or under a contract.

The type of study contract depends on the student: budget or contract. Therefore, there is a functional relationship between the attributes Student and the Type of contract (Figure 6).

Figure 6. Functional dependence between the attributes Student and Type of contract

The type of contract affects the amount of the scholarship. If a student studies under a contract, then the scholarship is not awarded. The size of the scholarship cannot determine the type of contract, since a student may study on a budget and not receive a scholarship due to poor academic performance. Therefore, the following functional relationship exists between the attributes Type of contract and the Scholarship (Figure 7).

Figure 7. Functional relationship between attributes Type of contract and Scholarship

This means that there is a transitive relationship between the attributes Student and Scholarship (Figure 8).

Figure 8. The transitive relationship between the attributes Student and Scholarship

⇑

6. Examples of multivalued dependency

Example 1. In an educational institution, a teacher can teach not one but several related subjects.

There is a multivalued relationship between the Teacher and Subject attributes, because one value of the Teacher attribute corresponds to several values of the Subject attribute. In this example, it is agreed that one discipline cannot be taught by several teachers.

Example 2. A table of the cost of new cars has been set.

There is a multivalued relationship between the brand and model attributes. This is because for one brand (Renault) there may be several model values (Logan, Megane, Koleos).

⇑

7. Task. Build a diagram of functional dependencies between relationship attributes

Task. The table of the database “Accounting of goods in the autoshop” is given, which is reduced to the first normal form 1NF. The table identifies goods arriving at the warehouse and has the following structure

You need to build a diagram of dependencies between attributes.

Solution. The diagram of dependencies between the attributes of the accounting table of goods received at the warehouse is shown in Figure 9.

Figure 9. Attribute dependency diagram

⇑

8. Task. Build a diagram of functional dependencies between relationship attributes

A store database table is specified, which displays the accounting of auto parts (goods). The structure of the table is as follows

The attributes Code, Item, Group, Number, and Date define the relationship key. Since an item with the same code can be received several times (on different dates and on different order numbers), choosing a key with one Code attribute is impractical.

The sequence of reasoning when constructing a diagram of functional dependencies is as follows. The goods arrive at the store based on the order for which the number and date are recorded. Each item received is identified by the code and name of the item. This means that we have a dependence of the attributes Code, Product on the attributes Number, Date.

Products are grouped into groups (categories). For example, for an auto parts store, such product categories could be Tires, Batteries, Transmission, and the like. Therefore, there is a functional dependency Group → Name.

The received product arrives in a certain quantity and has a cost. This means that the attributes Count and Cost are functionally dependent on the ordered product (pairs of attributes Code–Product).
Taking into account the above considerations, the diagram of functional dependencies is shown in Figure 10.

Figure 10. Functional dependency diagram. Accounting for auto parts in the store

⇑

9. The concept of independent attributes

There may be no functional dependencies between individual database attributes. Such attributes are called independent of each other.

Example. A table with data about the teacher is given.

In the above table, there is no functional relationship between the following attributes:

Expirience and Subject. It is designated as Experience ¬= Subject;
Address – Subject. It is designated Address ¬= Subject;
Experience – Address. It is designated Experience ¬= Address.

BestProg