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:


Contents


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.

Database. Table salaries

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 BrandModel-“Year of release” attributes. The “Number of released models” attribute depends on the key.

Database. Functional dependence of an attribute on a 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.

Database. Partial dependence. Employee salary table

Suppose that the primary key in the table is a combination of attributes Employee numberPosition. This key is compound. There is a functional relationship between the compound key Employee numberPosition 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: PositionSalary.

Since the Salary attribute depends only on a part of the key (the Position attribute) and not on the entire key (Employee numberPosition), 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 numberPosition.
Figure 2 schematically shows a partial dependence.

Database. Partial dependence. Example

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.

Database. Partial dependence. Example

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.

Database. Normalization. Partial dependence of an attribute on a key of a relation

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 (AB) and an inverse (BA) 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 AB.

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

Database. Normalization. Complete functional dependence. Example

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

Database. Normalization. Complete functional dependency between attributes

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.

Database. Normalization. Transitive dependency

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

Database. Normalization. Transitive dependency between attributes

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.

Database. Functional dependencies. Source table

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).

Database. Functional dependency between attributes. ExampleFigure 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).

Database. Normalization. Functional dependency between attributes

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).

Database. Normalization. An example of a transitive dependency

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.

Database. Normalization. Multi-valued dependence. Source table

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.

Database. Functional dependencies. Car cost table

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

Database. Building forms of functional dependencies. Source table

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.

Database. Normalization. Building dependency diagrams

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

Database. Building diagrams of functional dependencies. Source table

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 GroupName.

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 CodeProduct).
Taking into account the above considerations, the diagram of functional dependencies is shown in Figure 10.

Database. Normalization. Functional dependency diagram. Example

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.

Database. Functional dependencies. Independent attributes

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

  • Expirience and Subject. It is designated as Experience ¬= Subject;
  • AddressSubject. It is designated Address ¬= Subject;
  • ExperienceAddress. It is designated Experience ¬= Address.

 


Related topics