Functional Dependency and Relational Databases

Lectures No. 8-9.

Functional dependence. Normal forms.

Purpose of the lesson: to introduce students to the definition of the functional dependence of attributes, to the concept of normalization of the original relationship, to talk about the reasons leading to the need to normalize record files, to introduce ways to ensure the required level of normality of a table, to define normal forms using a specific example.

Functional dependencies

Normalization theory, like database theory in general, is based on mathematical apparatus, which is based on set theory and elements of algebra.

The same data can be grouped into tables (relationships) in various ways. The grouping of attributes in relationships should be rational (i.e., data duplication should be minimal) and simplify the procedures for processing and updating them. Eliminating data redundancy is one of the most important tasks in database design and is ensured by normalization.

Normalization of tables (relations)- this is a formal apparatus of restrictions on the formation of tables (relationships), which eliminates duplication, ensures consistency of data stored in the database, and reduces labor costs for maintaining (entering, adjusting) the database. The normalization process consists of decomposition (decomposition) of the original database relations into simpler relations. Each stage of this process brings the pattern of relationships into successive normal forms. For each stage of normalization, there are sets of constraints that the database relationships must satisfy. Normalization allows you to remove redundant non-key information from database tables.

First, let's remember some concepts:

Simple attribute is an attribute whose values ​​are indivisible. In other words, the table does not have fields like Full Name or Address - they are divided into fields Last Name, First Name, Patronymic in the first case and into fields Index, City, etc. in the second.

Complex (composite) attribute is obtained by combining several atomic attributes, otherwise it is called vector or aggregate of data.

Definition of functional dependency: Let X and Y are attributes of some relation. If at any time an arbitrary value X corresponds to a single value Y, then Y is functionally dependent from X (X→Y)

If the key is composite, then any attribute must depend on the key as a whole, but cannot be functionally dependent on any part of the composite key, i.e. the functional dependence has the form (X 1 , X 2 , ..., X)→Y.

Functional dependence may be complete or incomplete.

Incomplete dependence the dependence of a non-key attribute on part of a composite key is called .

Full functional dependence The dependence of a non-key attribute on the entire composite key, rather than on its parts, is called.

Definition of transitive functional dependency: Let X, Y, Z- three attributes of some relationship. At thistom X→Y and Y→Z, but there is no inverse correspondence, that is, Y does not depend on Z, and X does not depend on Y. Then they say that Z depends transitively on X.

Definition of multi-valued dependency: Let X and Y be attributes of some relation. Attribute Y depends a lot from attribute X, if. Each X value corresponds to a set of Y values ​​that are not associated with other attributes from the relation. Multivalued dependencies can be of a “one-to-many” (1:M), “many-to-one” (M:1) or “many-to-many” (M:M) nature, denoted accordingly: X=>Y, Y<=X и X<=>Y. For example, a teacher teaches several subjects, and each subject can be taught by several teachers, then there is a dependence of full name <=> Item.

Consider the following example: Suppose that for the educational part of the faculty a database about teachers is created, which includes the following attributes:

Full name - surname and initials of the teacher (coincidence of surnames and initials are excluded).

Position is the position held by the teacher.

Salary - teacher's salary.

Experience - teaching experience. D_Experience - bonus for length of service.

Department - the number of the department in which the teacher is registered.

Subject - the name of the subject (discipline) taught by the teacher.

Group - the number of the group in which the teacher conducts classes.

Type of lesson - type of lesson conducted by the teacher in the study group.

Initial attitude TEACHER

Database normalization or functional dependency is a situation in which a value allows a smooth transition to the next value in a sequence without any interruption. For this type of situation, there is a flow of information in the database that occurs without any delays or problems, and the integrity of the data itself is maintained. Functional dependency is critical to the creation and operation of relational databases because the process involves light associations with a single value or a data type with corresponding values.

One of the easiest ways to understand how functional dependency gets the job done is to consider using a government identification number, such as a Social Security number, which is regularly issued to every Russian citizen. By using this number as a means of identification, it is possible for employers to gain access to information about the owner of this number; Potential lenders and other creditors can use the access number to access relevant financial information about the applicant, and the number allows access to information such as taxes, accruals and taxes paid, income from one year to the next, and for retirement calculations when the person will eventually enjoy what he deserves after retirement. In many cases, employers may actually use the same number as the employee's primary identification number or some portion of the number of relational tools to access the rest of the employee's electronic file.

As part of database design and operation, a functional dependency serves to allow users to enter certain values ​​that can then be used to obtain information that is desired. For example, a salesperson might enter a company name value to retrieve all contact records associated with a corporate customer. Likewise, a salesperson who plans to sell can enter a city name as a value and as a means of accessing the name and contact information of all clients located near his or her destination, making it easier for him or her to arrange meetings with those clients.

While the exact structure, such as the system that provides the functional dependency, may vary depending on the application, the end result will still be the same. One meaning is linked to another, allowing you to access the information you need with relative ease. With so many records being stored in databases rather than relying on the old method of hard copying files, this type of relational dependency is very important for finding and using relevant data.

The information always had adequate dynamic interest. The development of programming languages, relational databases and information technologies has radically changed the content and structure of interest. A certain strict system of ideas has developed. Formalization, exact mathematics and binary relations have become a successful and rapidly developing area of ​​knowledge and experience.

The natural world of information has not changed its dynamics and, developing content and structure, has risen to new heights. It has smooth shapes, and there is nothing in nature "rectangular". Information, of course, lends itself to formalization, but it has dynamics; not only the data and algorithms for processing them change, the tasks themselves and the areas of their application change.

Information > formalization >> data

Information turns into an information structure, a database...) as the programmer sees it. There is no guarantee that this vision is correct, but if his program solves the problem, then the data has been presented as appropriately as possible.

The question of how correctly the information was formalized is a matter of time. Until now, the concept of dynamics (self-adaptation to changing conditions of use) is just a programming dream.

Functional dependence: “correct solution = program (programmer)” and condition: “continuous compliance with the task” are valid in most cases, but only together. But it's not the same mathematical basis, which is used when creating databases.

A direct statement: the natural and continuous dynamics of information and problem-solving algorithms are always real. And these are binary relations + strict mathematics + precise formal constructions, + ...

and databases

How data is stored has long been unimportant: whether RAM or external device. The hardware component has reached a steady pace of development and provides good quality in large quantities.

The main storage options, differing in data use options:

• files;
• databases.

The first is left to the programmer (what to write down, in what format, how to do it, how to read...), the second immediately brings the need to understand a simple functional relationship.

The speed of retrieving and writing information when working with files (of a reasonable size, not astronomical) is very fast, but the speed of similar operations with a database can sometimes be noticeably slow.

Personal experience and collective wisdom

In history there have been attempts to go beyond the achieved limits, but to this day they dominate relational databases data. Great theoretical potential has been accumulated, application practice is extensive, and the developers are highly qualified.

Database developers impose the concept of functional dependence on the programmer, even if he does not intend to use the rich mathematical and logical experience in constructing complex information structures, the processes of working with them, retrieving and recording information.

Even in the simplest case, the programmer depends on the database logic he chooses to work with. There is no desire to follow the canons, you can use files, you will get a lot of files and a lot personal experience. A lot of personal time will be spent and the problem will be solved over a long period of time.

No matter how complex examples of functional dependence may seem, it is not at all necessary to dive into the depths of meaning and logic. It should often be recognized that the collective mind has been able to create excellent databases, various sizes and functionality:

• solid Oracle;
• demanding MS SQL Server;
• popular MySQL.

Excellent relational databases with a good reputation, easy to use, fast in the right hands. Their use saves time and eliminates the need to write further sheets of auxiliary code.

Programming and Data Features

For a long time, programming has had the disease of constantly rewriting something, repeating the work of predecessors in order to somehow adapt something to changed information, a task or the conditions of its use.

The thing about functional dependency is that, just like in programming, a mistake can be very costly. The task is rarely simple. Usually, during the formalization of information, a complex representation of the data is obtained. Usually their elements are isolated, then they are linked by keys into certain relationships, then algorithms for forming tables, queries, and algorithms for retrieving information are adjusted.

Often great value has a binding to the encoding. Not all databases offer mobile solutions, you can often come across how a perfectly configured MySQL, on which a dozen databases lie, working perfectly and stably, forces the developer to make the eleventh database similar to those that already exist.

There are times when shared hosting limits the functionality of PHP and this leaves its mark on programming access to the database.

IN modern programming responsibility for the program algorithm is equivalent to responsibility for creating the data model. Everything should work, but you shouldn’t always plunge into the jungle of theory.

DB: simple data dependency

First of all, the concept of a database is both a database, both a management system (for example, MySQL) and a certain information structure that reflects task data and the connections between them. One MySQL database“holds” any number of information structures in various fields of application. One Oracle database, can provide information processes large company or bank, control security issues and data integrity on the highest level, located on many computers located at different distances, in different tool environments.

It is generally accepted that attitude is fundamental in relational model. An elementary relation is a set of columns with names and rows with values. Classical "rectangle"(table) - simple and effective achievement of progress. The complexity and functional dependencies of a database begin when "rectangles" begin to enter into relationships with each other.

The name of each column in each table must be unique within the context of the task. The same data cannot be in two tables. Know the meaning of the concepts:

• “define entities”;
• “eliminate redundancy”;
• “fix relationships”;
• “to ensure authenticity.”

An elementary necessity for using a database and building a data model for a specific task.

Violation of any of these concepts means low efficiency of the algorithm, slow data sampling, data loss, and other troubles.

Functional dependence: logic and meaning

You don’t have to read about tuples of relations, about the fact that a function is a correspondence between a set of arguments and a set of values, and a function is not only a formula or a graph, but can be specified by a set of values ​​- a table.

It's not necessary, but it doesn't hurt to think of a functional dependency as:

F(x1, x2, …, xN) = (y1, y2, …, yN).

But it is imperative to understand that the input is a table, and the output is also a table or a specific solution. Typically, a functional dependency establishes the logic of relationships between tables, queries, privileges, triggers, stored procedures and other aspects (components) of the database.

Typically, tables are converted to each other, then to the result. But the use of functional dependency is not limited to just this idea. The programmer himself builds his own representation of the data picture, information structure... it doesn’t matter what you call it, but if it works on a specific database, it must be built according to its logic, take into account its meaning and the dialect of the language used, usually SQL.

It can be argued that the properties of a database's functional dependencies are accessible through the dialect used SQL language. But it is much more important to understand: after all the vicissitudes of development, not many databases survived, but there are many dialects of this language and features of internal structures in the databases too.

About good old Excel

When the computer showed itself on the positive side, the world was immediately divided into programmers and users. As a rule, the first ones use:

• PHP, Perl, JavaScript, C++, Delphi.
• MySQL, Oracle, Visual FoxPro.

• Word.
• Excel.

Some users manage to create databases in Word on their own (without the help of programmers) - this is real nonsense.

User experience in Excel to create databases is practical and interesting. The important thing is that Excel itself is functional, colorful and practical.

The tabular idea defined the concept of functional dependence in a clear and accessible way, but each database has nuances. Each has its own “face,” but everyone from Excel to Oracle manipulates simple squares, that is, tables.

If you consider that Excel is not a database at all, but many users (not programmers) use it that way, and Oracle is the most complex and powerful achievement of a large team of developers in the field of databases, then it becomes natural to admit that a database is a representation a specific programmer (team) about a specific problem and its solution.

What is functional dependence, with what, where, why... is obvious only to the author or a team of them.

About Where Relational Relationships Are Going

Scientific and technological progress is a very painful procedure, and sometimes cruel. If you remember how databases began, what *.dbf is, how cybernetics was branded, then they fell in love with computer science and began to create obstacles to movement high technology at the country level, it becomes clear why relational databases are so resilient and good. Why the classical style of programming still lives on today, and object-oriented programming is simply appreciated, but does not yet reign.

No matter how beautiful functional dependence in the context of mathematics:

This is not a binary relationship, or rather, it is a reason to rethink the idea of ​​​​establishing relationships between many attributes, exploring “one-to-many”, “many-to-one”, “many-to-many” or “many in general and some in particular” relationships.

You can come up with a great variety of relationship options. It's math with logic, and it's rigorous! Information is its own mathematics, special. In it, one can only talk about formality with a very big minus.

You can formalize the work of the HR department, write an automated control system for oil production or the production of milk, bread, make a selection in the huge database of Google, Yandex or Rambler, but the result will always be static and the same at every moment of time!

If functional dependence = strict logic and mathematics = the basis for databases, then what kind of dynamics can we talk about? Any solution will be formal, any formal data model + strict algorithm = exact and unambiguous solution. The information and scope of any program is always changing.

Sample search engine on the same search phrase cannot be the same in an hour or two and, definitely, in a day - if search phrase refers to the field of information in which the number of sites, resources, knowledge, and other elements is constantly changing.

Even if the program is purely mathematical and its database does not even think about dynamics, everything is always lines. And the string has a length. And it cannot be endless. It cannot even be a variable, only a conditional variable. Among other things, any database with its mathematical and binary bureaucratic apparatus imposes a lot of formalities, and this is the speed + quality of sampling and processing of information.

And if certain fields in the database are numbers, especially real ones, then the following restrictions will be added: number digit capacity, the presence of the letter “e”, representation format - in short, everywhere and always we have important database functional dependency properties: strings of conditionally variable length with a lot of binary formalities and strict mathematical restrictions.

If you change the tone and listen to the pulse of the dynamics, then everything can be painted into objects. To a first approximation, the name of a column in a table is an object, a list of names is also an object, in short, a table is a header object and in it the names of the columns in the header. And there may be no hat at all...

But there may be rows in the table. And there can be values ​​in the string. And why should there always be the same number of them? Complete square table- this is a particular thing, and in most cases, a private one.

If you represent all the constructs in the database as objects, then perhaps you won't have to build strict binary relationships. This has a natural and real meaning, if only because it, according to objective (definitely not mathematical) logic, reflects the dynamics of information and the environment in which problems exist.

Functional dependencies

Functional dependence describes the relationship between attributes and is one of the basic concepts of normalization. Let's assume that the relational schema has attributes (A, B, C,..., Z) and the entire base can be represented as one universal relation R=(A, B, C,..., Z). Therefore, each attribute in the database has a unique name.

If A and B are attributes of some relation R, and each value of A is associated with one and only one value of B (and each of the attributes can consist of one or more attributes), then attribute B functionally dependent from attribute A (ВАА).

A functional dependence that is valid under any conditions is called trivial. Nontrivial dependencies define integrity constraints on relationships.

Transitive dependency for attributes A, B and C of some relation means the following: if AàB and BàC, then C transitively depends on attribute A through attribute B (provided that A is functionally independent of B or C).

To avoid data redundancy, which can lead to loss of integrity, it is necessary to use a minimum sufficient set of dependencies.

Database design using normalization begins with defining functional dependencies that are semantically obvious, i.e. reduction to first normal form.

A table in first normal form must meet the following requirements:

1) the table should not have duplicate records;

2) the table should not contain duplicate groups of fields;

3) each field must be semantically indivisible.

A table in second normal form must meet all the requirements of 1NF; any non-key field is uniquely identified by a full set of key fields, that is, each attribute of the relationship is fully or partially functionally dependent on another attribute.

The functional dependence of AàB is full functional dependence if the removal of any attribute from A leads to the loss of this dependence. The functional dependence of AàB is called partial, if in A there is a certain attribute, when removed, this dependency remains.

A table that is in third normal form must meet all the requirements of 2NF, no non-key field is identified by another non-key field, that is, a relation that is in first and second normal form and has no attributes that are not in the primary key of the attributes , which would be in a transitive functional dependence on this primary key.

Boyce Code Normal Form (BCF) is based on functional dependencies that take into account all potential keys of a relation, but with stricter restrictions.

Determinant of functional dependence is an attribute (or group of attributes) on which some other attribute fully functionally depends.

To check whether a relation belongs to the BCNF, it is necessary to find all its determinants and make sure that they are potential keys.

The difference between 3NF and BCNF is that the functional dependence AàB is allowed in relation to 3NF if the attribute B is primary key, and attribute A is not necessarily a candidate key. For BCNF, this dependency is only allowed when attribute A is a candidate key. Therefore, BCNF is a stricter version of 3NF, since every BCNF relation is 3NF, but not every 3NF relation is BCNF.

A relationship is in BCNF only if each of its determinants is a potential key.

Fourth normal form (4NF) is a relation in BCNF that does not contain non-trivial multivalued dependencies.

Multivalued dependency represents a relationship between attributes of a relation (for example, A, B, and C) such that each value of A represents a set of values ​​for B and a set of values ​​for C. However, the sets of values ​​for B and C are independent of each other.

A multivalued dependency can be further defined as either trivial or non-trivial. A multivalued dependence AàB of some relation R is defined as trivial if attribute B is a subset of attribute A or . Conversely, a multivalued dependency is defined as nontrivial if neither condition is met. A trivial multivalued dependence does not impose any restrictions on this relationship, but a nontrivial one does.

When partitioning a relation using the projection operation, the decomposition method used is determined precisely. It is necessary that when the resulting relations are reconnected, the original relation can be restored. This decomposition is called lossless connection decomposition(or a win-win or non-additive join) because it preserves all the data in the original relation and eliminates the creation of additional dummy rows.

Fifth normal form (5NF), also called projective connective normal form, means that a relation in this form has no join dependencies. A relation R with a subset of attributes A,B,...,Z satisfies a join dependency if each admissible value of R is equal to the join of its projections onto the subsets A,B,...,Z.

Combining multiple attributes into one relation is not done randomly. The data to be stored in this regard is interconnected. This relationship is determined by a set of functional dependencies between the attributes of the relationship. This means that the values ​​of one attribute depend on the values ​​of other attributes, i.e., not any combination of attribute values ​​is allowed. These dependencies arise from the limitations of the subject area. For example, in relation to Supplies the following restrictions apply:

Each supplier has only one address,

Each supplier supplies goods at a certain price,

· goods supplied by different suppliers can be distributed to different warehouses, but goods of the same name supplied by one supplier must be stored only in one warehouse,

· each warehouse has its own volume.

These constraints are dependencies, which can be formulated as follows:

· the address is functionally dependent on the supplier,

· the price functionally depends on the product and supplier,

· the warehouse number functionally depends on the product and supplier,

· the volume functionally depends on the warehouse number.

Functional dependence occurs when the values ​​of a tuple on one set of attributes uniquely determine the values ​​of a tuple on another set of attributes (or on one attribute).

Let the attitude r has a diagram R, X And Y– subsets R. Attitude r satisfies functional dependence X→Y, If π Y (σ X=x (r)) has at most one tuple for each value xÎX, i.e. the values ​​of the attributes X uniquely determine the values ​​of the attributes Y.

We will denote functional dependence as follows:

· Supplier → Address,

· (Product, Supplier)→ Price,

· (Product, Supplier)→ Warehouse,

· Warehouse → Volume.

And they read like this:

· The supplier determines the Address,

· Product and Supplier determine the Price,

· Product and Supplier determine the Warehouse,

· Warehouse determines Volume.

In functional dependency language, the key for the schema R is a subset KÍR, such that K→R, and no proper subset K¢ÍK does not have this property.

Normal forms

Let us formulate the rules by which the decomposition of a relationship should be carried out. This process is called normalization, i.e. bringing the relationship to normal form.

Normal forms represent restrictions on a relational schema that free it from the undesirable properties that were listed above. Before reducing relationships to normal form, you should build all the functional dependencies between the attributes that exist in the domain.

Relationship diagram R is in first normal form (1NF) if the values ​​of all attributes are atomic (not composite), that is, the value of each attribute is neither a list nor a set of values.

For example, attribute Full name is composite, consisting of three data: last name, first name and patronymic.

To convert a schema into 1NF, you need to replace all compound attributes with simple ones.

To get rid of the redundancy of information stored in the database, there are second and third normal forms.

Relationship diagram R is in second normal form (2NF) if it is in first normal form and each non-primary attribute is fully functionally dependent on the primary key.

What is an incomplete functional key dependency? Such a dependency exists in a relationship if any attribute not included in the key is functionally dependent on parts attributes included in the key. Any non-primary attribute is necessarily functionally dependent on all primary attributes as defined by the relation key. And if any non-primary attribute, in addition, functionally depends not on all, but on part of the primary attributes, then this is an incomplete functional dependence.

For example, in relation to Supply the primary attributes are Product And Supplier. Attribute Price functionally fully depends on the key, and the attribute Address depends on part of the key, i.e. only on the attribute Supplier, this is an incomplete functional dependence. So the scheme Supplies is not in 2NF.

To convert a circuit in 1NF to 2NF, you need to split it into several circuits:

· perform a diagram projection R on primary attributes and attributes that are functionally fully dependent on the key, i.e., exclude non-primary attributes that are not fully dependent on the key,

· for each incomplete functional dependency, perform a diagram projection R on the attributes included in this dependency, i.e. leave part of the relation key R and attributes that are functionally dependent on this part.

In the relation example Supplies As a result of reducing the scheme to 2NF, two relations will be obtained:

Supplies_1(Product, Supplier, Price, Warehouse, Volume),

Supplies_2(Supplier, Address).

However, information about the warehouse volume continues to be duplicated. To eliminate this shortcoming of the scheme, there is a third normal form.

Relationship diagram R is in third normal form (3NF), if it is in second normal form and there are no transitive dependencies of non-primary attributes on the key.

What are transitive dependencies? A transitive dependency occurs when a nonprimary attribute is functionally dependent on another nonprimary attribute, which in turn is functionally dependent on a key.

Relationship diagram Supplies_1(Product, Supplier, Price, Warehouse, Volume) is not in 3NF, since it contains a transitive dependency:

{Product, Supplier} → Warehouse, Warehouse → Volume.

To convert a circuit in 2NF to 3NF, you need to:

· perform a diagram projection R on primary attributes and attributes that are transitively independent of the key, i.e., exclude non-primary attributes that are transitively dependent on the key,

· for each transitively dependent non-primary attribute, perform a schema projection R to the attributes included in the second part of the transitive dependency, i.e., leave only the non-primary attributes of the relationship R, between which there is a functional dependence.

In the relation example Supplies_1 As a result of reducing the circuit to 3NF, two relations will be obtained:

Supplies_1_1(Product, Supplier, Price, Warehouse),

Supplies_1_2(Warehouse, Volume).

Thus, by sequentially dividing the original relation schema into several other schemas in accordance with the rules discussed, we obtain a 3NF schema that is free from anomalies in updating and duplicating information, as discussed at the beginning of the section.

The process of dividing a relation schema into several other schemas is called decomposition relationship schemes. A decomposition that reduces a relation to one of the normal forms is called normalization.

In the considered example, as a result of decomposition, instead of one relation Supplies we got three new relations:

Supplies_1_1(Product, Supplier, Price, Warehouse),

Supplies_1_2(Warehouse, Volume),

Supplies_2(Supplier, Address).

With such a scheme, consisting of three relationships connected by foreign keys, there will be no duplication of information about the supplier’s address and the volume of the warehouse; if the warehouse is empty, then its volume will remain in the database; if the supplier does not supply goods, then his address will still be stored in the database data.

As you noticed, the 3NF schema saves the database from duplication of information and update anomalies, but not always.

Consider the relation Lectures(Student, Item, Teacher), which stores information about what subjects students study and who teaches these subjects. Subject area imposes the following restrictions:

Each student studying a given subject is taught by only one teacher,

· each teacher teaches only one subject, but each subject can be taught by several teachers.

The following functional dependencies follow from these restrictions:

· (Student, Subject) → Teacher;

· Teacher → Subject.

From the functional dependencies it follows that the key is the relationship Lectures there will be a set of attributes ( Student, Item}.

Attitude Lectures is in 3NF. But it suffers from update anomalies. If you want to delete information that Petrov is studying Physics, then the information that Professor Serov teaches Physics will be lost. At the same time, the information that Professor Bely teaches Algebra is duplicated.

These difficulties are caused by the fact that there is a functional dependence of the primary attribute on the non-primary one. This problem is solved in Boyce–Codd normal form.

The relationship is in Boyce–Codd normal form (BCNF), if it is in 3NF and there are no dependencies of primary attributes on non-primary ones. An equivalent definition requires that all left-hand sides of functional dependencies be candidate keys.

Reducing the relation to BCNF, we get two relations: Lectures_1(Student, Teacher) And Lectures_2(Teacher, Subject).

Multivalued dependencies

Attribute X defines many-valuedly attribute Y V R(or Y depends significantly on X), if each attribute value X corresponds to a set (possibly empty) of attribute values Y, not related in any way to other attributes R. That is, for a relationship to have a multivalued dependency, it must have at least three attributes.

A multivalued relationship is indicated by a double arrow: X→→Y.

Consider the relation Teacher(Number , Child_name , Item ,Job title). The subject area imposes the following restrictions:

Each teacher can have several children,

Each teacher can teach several subjects,

Each teacher can hold only one position,

· each subject can be taught by several teachers.

Then the attitude Teacher has two multivalued dependencies and one functional:

· Number→→Child_name,

· Number→→Item,

· Number→Position.

Attitude Teacher, firstly, contains redundant information - the teaching position is repeated several times. Secondly, it is not free from update anomalies: if a teacher has another child, it is necessary to add not one tuple to the relation, but as many as the number of subjects this teacher teaches. Similarly, when adding another subject, you need to add as many tuples as the number of children the teacher has. And if the teacher does not have children, then information about what subjects he teaches cannot be included in the relationship at all.

To get rid of these anomalies, it is necessary to bring the relation to the fourth normal form.

The relationship is in fourth normal form (4NF), if it is in Boyce–Codd normal form and there are no multivalued dependencies that are not functional.

After bringing the relationship Teacher to 4NF we get three relations:

Teacher_1(Number , Job title),

Teacher_2(Number , Child_name ),

Teacher_3(Number , Item ).

Properties of Decomposition