Functional dependencies. Functional dependencies and relational databases

When designing a database in a relational DBMS, the main goal of developing a logical data model is to create an accurate representation of the data, the relationships between them, and the required constraints. For this it is necessary to determine, first of all, a suitable set of relations. The method used is called normalization. Normalization is a bottom-up approach to database design that begins by establishing relationships between attributes.

The goal of normalization

Normalization -a method for creating a set of relationships with specified properties based on the data requirements established in an organization.

Normalization is often performed in the form of a series of tests for a certain relation in order to check its conformity (or nonconformity) with the requirements of a given normal form.

The normalization process is a formal method that allows one to identify relationships based on their primary keys (or potential keys, as in the case of NFBC) and the functional dependencies that exist between their attributes. Database designers can use normalization in the form of test suites that are applied to individual relationships to normalize the relational schema to a given specific form, thus preventing possible update anomalies.

The main design goal of a relational database is to group attributes and relationships in such a way as to minimize data redundancy and thus reduce the amount of memory required to physically store tabular relationships.

Functional dependencies

Functional dependency describes the relationship between attributes and is one of the basic concepts of normalization. This section provides a definition of this concept, and the following sections describe its relationship with the processes of normalizing database relations.

Functional dependence - describes the relationship between the attributes of a relationship. For example, if in relation to. R containing attributes A and B, attribute B functionally depends on attribute A (which is denoted as AB), then each value of attribute A is associated with only one value of attribute B. (Moreover, each of attributes A and B can consist of one or multiple attributes.)

Functional dependence is a semantic (or semantic) property of the attributes of a relationship. The semantics of a relation indicates how its attributes can be related to each other, and also defines the functional dependencies between attributes in the form of restrictions imposed on some attributes.

The relationship between attributes A and B can be schematically represented in the form of a diagram shown in Figure 5.

Determinant- the determinant of functional dependence is an attribute or a group of attributes located in the functional dependence diagram to the left of the arrow symbol.

Figure 5 - Diagram of functional dependence

When there is a functional dependency, the attribute or attribute group that is located to the left of the arrow symbol in its diagram is called a determinant. For example, in Fig. 6.1 Attribute A is a determinant of Attribute B.

The concept of functional dependence is central to the normalization process.

Combining multiple attributes into one relationship is not done randomly. The data to be stored in this respect are interconnected. This relationship is determined by many functional dependencies between the attributes of the relationship. This means that the values \u200b\u200bof one attribute depend on the values \u200b\u200bof other attributes, that is, not all combinations of attribute values \u200b\u200bare allowed. These dependencies arise from restrictions subject area... For example, regarding Supplies the following restrictions apply:

Each supplier has only one address,

Each supplier delivers goods at a specific price,

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

· Each warehouse has its own volume.

These constraints are dependencies that can be summarized as follows:

The address functionally depends on the supplier,

The price functionally depends on the product and the supplier,

Warehouse number functionally depends on the product and the supplier,

· The volume functionally depends on the warehouse number.

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

Let the attitude r has a scheme R, X and Y - subsets R... Attitude r satisfies functional dependence X → Y, if a π Y (σ X \u003d x (r)) has at most one tuple for each value xÎXthat is, the values \u200b\u200bof the X attributes uniquely determine the values \u200b\u200bof the Y attributes.

The functional dependence will be denoted as follows:

Supplier → Address,

(Product, Supplier) → Price,

(Product, Supplier) → Warehouse,

· Warehouse → Volume.

And they read like this:

Supplier determines the Address,

· The Product and the Supplier determine the Price,

· Item and Supplier define Warehouse,

· The warehouse determines the volume.

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

Normal forms

Let us formulate the rules according to which the decomposition of the relation should be carried out. This process is called normalization, that is, bringing the relationship to normal form.

Normal forms are constraints on the relationship schema that rid it of the undesirable properties listed above. Before bringing relations to normal form, all functional dependencies between attributes that exist in the domain should be built.

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

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

To bring the schema into 1NF, you need to replace all composite attributes with simple ones.

To get rid of the redundancy of the 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 functionally dependent on the primary key.

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

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

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

Perform a schematic projection R to primary attributes and attributes that are functionally fully dependent on the key, i.e., exclude non-primary attributes that are incompletely dependent on the key,

For each incomplete functional dependence, perform a schematic projection R on the attributes included in this dependency, i.e., leave part of the key of the relation R and attributes functionally dependent on that part.

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

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

Supplies_2(Provider, Address).

However, information about the volume of the warehouse continues to be duplicated. There is a third normal form to overcome this circuit flaw.

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? Transitive dependency occurs when a non-primary attribute functionally depends on another non-primary attribute, which in turn is functionally dependent on a key.

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

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

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

Perform a schematic projection R to 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 relationship.

In the example with the relation Supplies_1 as a result of reducing the scheme to 3NF, two relations will be obtained:

Supplies_1_1(Product, Provider, Price, Warehouse),

Supplies_1_2(Warehouse, Volume).

Thus, sequentially performing the division of the original relation scheme into several other schemes according to the considered rules, we obtain a scheme in 3NF, free from anomalies of updating and duplication of information, which was mentioned at the beginning of the section.

The process of splitting a relationship schema into several other schemas is called decomposition relationship schemes. A decomposition that brings 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 relationships:

Supplies_1_1(Product, Provider, Price, Warehouse),

Supplies_1_2(Warehouse, Volume),

Supplies_2(Provider, Address).

With such a scheme, consisting of three relations linked by foreign keys, there will be no duplication of information about the address of the supplier 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 can see, the schema in 3NF saves the database from duplicate information and update anomalies, but not always.

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

Each student studying this 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 functional dependencies it follows that the key of the relationship Lectures there will be a set of attributes ( Student, Subject}.

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

These difficulties are due to the fact that there is a functional dependence of the primary attribute on the non-primary. This problem is solved in Boyes – Codd normal form.

The attitude is in boyes-Codd normal form (NFBK)if it is in 3NF and there are no dependencies between primary and non-primary attributes. The equivalent definition requires all left sides of functional dependencies to be potential keys.

By citing the attitude towards NFBC, we get two relations: Lectures_1(Student, Teacher) and Lectures_2(Teacher, Subject).

Multivalued dependencies

Attribute X defines ambiguously attribute Y in R (or Y multiply depends on X) if each attribute value X matches a set (possibly empty) of attribute values Ynot related to other attributes R... That is, in order to have a multi-valued relationship, you must have at least three attributes.

Multivalued dependence is indicated by a double arrow: X → → Y.

Consider the relationship Teacher(room , Child_name , Subject , Position). The subject area imposes the following restrictions:

· Each teacher can have several children,

· Each teacher can teach several subjects,

Each teacher can only hold one position,

· Each subject can be taught by several teachers.

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

Number → → BabyName,

· Number → → Item,

· Number → Position.

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

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

The attitude is in fourth narmal form (4NF) if it is in Boyes – 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(room , Position),

Teacher_2(room , Child_name ),

Teacher_3(room , Subject ).

Decomposition properties

Normal forms method

Teacher

Figure: 6.4.Original Attitude TEACHER

Implicit redundancymanifests itself in the same salaries for all teachers and in the same salary increments for the same length of service. If the salary changes from 500 rubles. up to 510 rubles, then this value must be changed for all teachers. If Sidorov is missed at the same time, the base will become contradictory. This is an example of an implicit redundancy relationship editing anomaly.

The elimination of redundancy is the normalization of relations.

Normal forms is a classic relational database design technique. It is based on the fundamental concept of dependency between the attributes of a relationship.

Attribute B functionally dependent from attribute A, if exactly one value of B corresponds to each value of A. Mathematically, the functional dependence of B on A is denoted by the notation A ® B. This means that in all tuples with the same value of the attribute a ATTRIBUTE in WILL HAVE ALSO THE SAME VALUE. Attributes A and B can be composite - consist of two or more attributes. In relation to the Teacher, the functional dependencies are as follows: Full name ® Kaf, Full name ® Dolzh, Dolzh ® Salary, etc.

Functional interdependence.If there is a functional dependence of the form A ® B and B ® A, then between A and B there is a one-to-one correspondence, or functional interdependence. Mathematically, the relationship is denoted as A "B or B" A.

Example.The N attribute (passport series and number) is in functional interdependence with the full name attribute (last name, first name and patronymic), if it is assumed that the situation of having a complete coincidence of last names, first names and patronymics in two people is excluded.

Partial functional dependenceis the dependence of a non-key attribute on a part of a composite key. In relation to the Teacher, the key is composite and consists of the attributes Name, Subject and Group. All non-key attributes are functionally key dependent with varying degrees of dependency. For example, the Title attribute is functionally dependent on the full name attribute that is part of the key, i.e. is partially dependent on the key.

Full functional dependency -the dependence of a non-key attribute on the entire composite key. For example, the VidZan attribute is fully functional depending on the composite key.

Attribute C depends on Attribute A transitively (exist transitive dependency ), if for attributes А, В, С conditions А ® В and В ® С are fulfilled, but there is no inverse relationship. In the example, the attributes are transitively related:

Full name ® Debt ® Salary

In relation to R, attribute B multiply depends from attribute A, if each value of A corresponds to a set of values \u200b\u200bof B that are not related to other attributes from R. Multivalued dependencies can be "one to many" (1: M), "many to one" (M: 1) or "many to many "(M: M), denoted respectively: A Þ B, A Ü B and A Û B.

In the example under consideration, there is a multivalued relationship M: M between the attributes of full name Û Subject (one teacher can teach several subjects and one subject can be taught by several teachers).

Since the relationship between attributes is the cause of anomalies, they try to divide such relationships into several relationships. As a result, a set of related relationships (tables) with relationships of the form 1: 1, 1: M, M: 1 and M: M is formed. Relationships between tables reflect dependencies between attributes of different relationships.

Mutually independent attributes.Two or more attributes are said to be mutually independent if none of these attributes is functionally dependent on other attributes. Mathematically, the absence of dependence of attribute A on attribute B is denoted as A Ø® B. If A Ø® B and B Ø® A take place, then mutual independence is denoted A Ø \u003d B.

Revealing dependencies between attributes.Revealing the dependencies between attributes is necessary to perform normal forms database design.

Example.Let the relation R be given with the scheme R (A1, A2, A3) of the form:

It is known a priori that there are one hundred functional dependencies:

A1®A2 and A2®A3.

The analysis shows that there are still dependencies in relation:

А1®А3, А1А2®А3, А1А2А3®А1А2, А1А2®А2А3, etc.

In relation there is no functional dependence of the A1 attribute on the A2 attribute and on the A3 attribute, i.e.

А2 Ø® А1, А3 Ø® А1.

The absence of dependence of A1 on A2 is explained by the fact that the same value of the A2 attribute (21) corresponds to different meanings attribute A1 (12 and 17).

All existing functional dependencies in relation are full set of functional dependencies , which we denote by F +. The full set of functional dependencies can be derived on the basis of 8 inference axioms: reflectivity, completion, transitivity, extension, continuation, pseudotransitivity, union and decomposition.

In relation to the Teacher, the following functional dependencies can be derived:

Full name ® Salary

Full name ® Must

Full name ® Experience

Full name ® Nadb

Full name ® Cafe

Experience ® Nadb

Debt ® Salary

Salary ® Must

FULL NAME. Item Group ® Salary

Figure: 6.5. Dependencies between attributes.

It is assumed that one teacher in one group can conduct one type of class (lectures or laboratory works). Full name - are unique. There is a dependence FULL NAME ® Experience, and the converse statement is not true, because Several teachers have the same experience. The reasoning is similar for other dependencies. A one-to-one relationship is established between the position and the salary.

One teacher in one group in different subjects can conduct different types classes. The definition of the Type of Occupation is associated with the indication of the full name, Subject and Group. Indeed, Petrov M.I. in the 256th group he lectures and conducts laboratory studies, but lectures are given on DBMS, and laboratory work on Graphics.

The dependencies between the attributes Name, Subject and Group are not displayed, since they form a composite key and are not taken into account in the normalization of the relationship (table).

Normal forms. The database design process using normal forms is iterative and consists of sequentially translating relations from first normal form to higher normal forms. Each subsequent form restricts a certain type of functional dependencies, eliminates corresponding anomalies when performing operations on database relations, and retains the properties of previous forms.

The following sequence of normal forms is distinguished:

° First normal form (1NF);

° Second normal form (2NF);

° Third normal form (3NF);

° Strengthened third normal form, or Boyes-Codd normal form (BCNF);

° Fourth normal form (4NF);

° Fifth normal form (5NF).

First normal formA ratio is in 1NF if all its attributes are simple (have a single value). The initial ratio is built in such a way that it was in 1NF.

The transformation of the relation into the next normal form is carried out by the method of “lossless decomposition”, i.e. queries (fetching data by condition) to the original relation and to the relations obtained as a result of decomposition should give the same result.

The main operation of the decomposition method is the projection operation.

Example. Let in relation R (A, B, C, D, E, ...) there is a functional dependence С ® D. Decomposition of the relation R into two new relations R1 (A, B, C, E, ...) and R2 (C, D) removes the functional dependence of the attributes and transforms the relation R into the next normal form. The R2 relation is the projection of the R relation onto the C and D attributes.

Initial relation Teacher has a composite key Name, Subject, Group and is in 1NF. The attributes Experience, Nadb, Kaf, Dolzh, Salary are functionally dependent on the part of the composite key - the attribute Full name... This partial dependency leads to explicit and implicit redundancy in the data, which creates editing problems. Part of the redundancy is eliminated when the relationship is converted to 2NF.

Second normal form.A relationship is in 2NF if it is in 1NF and each non-key attribute is functionally fully dependent on the primary key (composite).

To eliminate partial dependence, it is necessary to use the projection operation, expanding the original relation not to several relations as follows:

° Build a projection without attributes that are partially dependent on the primary key;

° Build projections on parts of a composite primary key and attributes depending on those parts.

Let's translate the Teacher relation into 2NF. As a result, we get two ratios R1 and R2.

R1

Figure: 6.6. Database Relationship TEACHER in 2 NF

In relation to R1, the primary key is composite Name, Subject, Group , in relation to R2 the key is FULL NAME. As a result, the obvious redundancy of data on teachers is eliminated. In R2, implicit data duplication still occurs.

For further improvement, we will transfer relations to 3NF.

Lecture 3. General concepts and definitions. Classification of functions. Function limit. Infinitesimal and Infinitely Large Functions. Basic theorems on infinitesimal functions.

Function

When solving various problems, one usually has to deal with constant and variable quantities.

Definition

A constant value is a value that retains the same value either in general or in this process: in the latter case, it is called a parameter.

A variable is a quantity that can take on various numerical values.

Function concept

When studying various phenomena, we usually deal with a set of variable quantities that are interconnected in such a way that the values \u200b\u200bof some quantities (independent variables) completely determine the values \u200b\u200bof others (dependent variables and functions).

Definition

A variable value y is called a function (single-valued) of a variable value x if they are interconnected so that each considered value of x corresponds to a single well-defined value of the value y (formulated by NI Lobachevsky).

Designation y \u003d f (x) (1)

x - independent variable or argument;

y - dependent variable (function);

f - characteristic of the function.

The collection of all values \u200b\u200bof the independent variable for which the function is defined is called the domain of definition or the domain of existence of this function. The domain of the function can be: segment, half-interval, interval, whole numerical axis.

Each radius value corresponds to the area of \u200b\u200bthe circle. Area - a function of radius, defined in an infinite interval

2. Function (2). The function is defined at

To visualize the behavior of a function, a function graph is plotted.

Definition

Function graph y \u003d f (x) called the set of points M (x, y) plane OXY, the coordinates of which are related by this functional dependence. Or the graph of a function is a line whose equation is the equality that defines the function.

For example, the graph of function (2) is a semicircle of radius 2 centered at the origin.

Simplest functional dependencies

Let's look at some of the simplest functional dependencies

1. Direct functional dependence

Definition

Two variable quantities are called directly proportional if, when one of them changes in a certain ratio, the other changes in the same ratio.

y \u003d kxwhere k - coefficient of proportionality.

Function graph

1. Linear dependence

Definition

Two variables are linearly related, if, where are some constants.

Function graph

1. Inverse proportional relationship

Definition

Two variable quantities are called inversely proportional if, when one of them changes in some ratio, the other changes in the opposite ratio.

1. Quadratic dependence

In the simplest case, the quadratic dependence has the form, where k is some constant value. The function graph is a parabola.

1. Sinusoidal dependence.

Sinusoidal dependence plays an important role in the study of periodic phenomena.

- the function is called harmonic.

A - amplitude;

Frequency;

Initial phase.

The function is periodic with a period. Function values \u200b\u200bin points xand x + Tdiffering by the period are the same.

The function can be reduced to the form where. Hence, we find that the harmonic graph is a deformed sinusoid with amplitude A and period T, shifted along the ОХ axis by the value

Ways to set a function

Usually, three ways of defining a function are considered: analytical, tabular, graphical.

1. An analytical way of defining a function

If a function is expressed using a formula, then it is given analytically.

for instance

If the function y \u003d f (x) is given by the formula, then its characteristic f denotes the set of actions that must be performed in a certain order on the value of the argument xto get the corresponding function value.

Example ... Three actions are performed on the argument value.

1. Tabular way of defining a function

This method establishes correspondence between variables using a table. Knowing the analytical expression of the function, you can represent this function for the argument values \u200b\u200bof interest to us using a table.

Is it possible to switch from a table function to an analytical expression?

Note that the table does not give all the values \u200b\u200bof the function, and the intermediate values \u200b\u200bof the function can be found only approximately. This so-called interpolation functions. Therefore, in the general case, it is impossible to find the exact analytical expression of a function from tabular data. However, you can always build a formula, and more than one, which for the values \u200b\u200bof the argument available in the table will give the corresponding table values \u200b\u200bof the function. This kind of formula is called interpolation.

1. Graphical way to define a function

Analytical and tabular methods do not provide a visual representation of the function.

The graphical way of defining the function is devoid of this drawback. y \u003d f (x)when the match between the argument x and function y set using the schedule.

Implicit function concept

A function is called explicit if it is specified by a formula whose right side does not contain a dependent variable.

Function y from argument xis called implicit if it is given by the equation

F (x, y) \u003d 0(1) unresolved with respect to the dependent variable.

Inverse function concept

Let the function y \u003d f (x) (1). By setting the values \u200b\u200bof the argument x, we obtain the values \u200b\u200bof the function y.

It is possible, considering yargument and x - function, set values y and get values x... In this case, equation (1) will determine xas an implicit function of y... This last function is called reverse in relation to this function y.

Assuming that equation (1) is resolved with respect to x, we obtain an explicit expression for the inverse function

(2), where the function for all admissible values ysatisfies the condition

Functional dependence.

Attribute B functionally depends on attribute A if one value of A defines exactly one value of B.

In the event that for a given relationship all its attributes functionally depend on one attribute, then this attribute - potential simple key, if its values \u200b\u200bare unique within the relationship. One of the potential keys is assigned by the relationship key.

In a relation, it is sometimes possible to single out a collection of several attributes on which all other attributes functionally depend. If its values \u200b\u200bare unique in the aggregate within the relation, then this aggregate is relationship superkey ,

If attribute B functionally depends on the superkey, but there is no functional dependence on any subset of the superkey, then complete functional dependence In off the superkey.

If all the attributes of one relation functionally depend on a given superkey, but there is no functional dependence on any subset of this superkey, then the superkey is potential key .

Composite key relationship is selected from potential keys.

Please note that the term functional dependencecorresponds to the concept of a function in mathematics. If a non-keythe attribute depends on the entire composite key and does not depend on its parts, then we speak of the complete functional dependence of the attribute on the composite key.

If attribute A depends on attribute B, and B depends on attribute C, but there is no inverse relationship, then attribute C is said to depend on A transitively.

Relationship types in relational databases

In fact, the records of different DB relations are linked, but it is customary to talk about linking these relations. When linking, links of tuples of one relation are established to tuples of another relation belonging to the same database.

In total, four types of links (links) are supported: "one to one", "many to one", "one to many", "many to many".

One-to-many relationship

Attitude X related to attitude Have"One to many" if each tuple from X matches multiple tuples from Have... In this case, it is indicated on which field x of X referenced field at of Have.

To establish links, the DBMS has a link design mode. For the DBMS to work correctly with a linked database, the links must satisfy conditions that protect the integrity of the database. Constraints are set on the properties of the bound fields. In this case, in relation to X (from the "one" side) binding field x must have unique values, and the field at of Have should not contain values \u200b\u200bmissing in x... Field x called primary key and the field at – foreign key ... In this regard, the attitude Xin which the primary key is placed is called main attitude and the ratio Havein which the foreign key resides is called subordinate attitude .

An example of one-to-many relationships:

the Orders relationship (subordinate) and the Products relationship (main);

the Orders relationship (subordinate) and the Customers relationship (main).

FOR ORDERS foreign keys for communication with the relationship PRODUCTS and CLIENTS: Product_order and Client_order.In relations: GOODS and CLIENTS primary keys Product_code and Customer_code, which foreign keys refer to.

One-to-one relationship

If in connection One-to-many external the key y contains only unique values, then this is a one-to-one relationship type - each record in Have there is one entry in X and each entry in X matches at most one entry in Have... Moreover, the foreign key y is not, as x, the primary key of the relationship, since in the field x there may be values \u200b\u200bthat are not in at... And in the field at values \u200b\u200bthat are not in the field x, it can not be. In a relationship X and Have there may be a different number of tuples.

Many-to-one relationship

Defined as a one-to-many relationship, but the relationship X and Have swap places in the definition.

Many-to-many relationship

Is established between two relationships X and U,if each of them contains primary key connection with the third relation FROM,which hosts two foreign keys one-to-many relationships between Xand FROMand one-to-many between FROMand W.Attitude FROMcalled binder . In a relationship FROM you must assign a composite key (not a simple one). This composite key must include the foreign keys of two relationships (or more, if pairs such as X and U,linked through FROM,there are several).

Two basic requirements (constraints) are fixed to maintain integrity. Integrity constraints by entities and by links must be supported by a relational DBMS.

The first entity integrity . A domain object (or entity in a domain model) in relational databases corresponds to tuples of relations. The specific requirement is that any tuple of any relation is distinguishable from any other tuple of the same relation. In other words: any relationship must have a key. This requirement is automatically satisfied if the basic properties of the relations are not violated in the system.

To comply integrity by entity it is sufficient to guarantee that there are no tuples with the same key value in any relation.

Second a requirement is called a requirement integrity by reference and is somewhat more complex. It is obvious that complex entities of a domain model are represented in a relational database as several tuples of several related relations.

Integrity requirement by reference, or requirement foreign key, is that for each value of the foreign key of the subordinate relation, in the main relation there must be a tuple with this value of the primary key, or the value of the foreign key must be undefined (do not indicate anything). For the example of relationships between employee and department relationship records, this means that if a department number is specified for an employee in the employee relation in the department field, then this department must exist in the department relation.

When updating subordinate relations (inserting new tuples or modifying a foreign key value in existing tuples), the DBMS only needs to ensure that incorrect foreign key values \u200b\u200bdo not appear (those values \u200b\u200bthat are not in the primary key field of the main relation). When deleting a tuple from a master relationship, when referenced by a subordinate relationship, the DBMS has several of the following techniques, each of which maintains referential integrity.

1) It is forbidden to delete a tuple to which references exist (i.e., first you need to either delete the referenced tuples, or change the values \u200b\u200bof their foreign key accordingly).

2) When deleting a referenced tuple, the foreign key value is automatically undefined in all referenced tuples.

3) A cascading delete is created, which means that when a tuple is deleted from the main relation, all referencing tuples are automatically deleted from the subordinate relation.

In advanced relational DBMSs, you can choose how to maintain referential integrity for each specific situation. To make a decision, it is necessary to analyze the requirements of a specific subject area.

Relational database design. Normalization.

Normalization concept

The classic approach will be considered, in which the entire design process is carried out in terms of relational model data by the method of successive approximations to a satisfactory set of relationship schemes.

The starting point is the representation of the subject area in the form of one or more relations, and at each design step, the initial scheme of relations is transformed into a certain set with better properties.

The design process is a process normalization of relationship schemes , bringing relations to "Normal forms", moreover, each next normal form possesses better properties than the previous one. In reality, the normalization process is carried out through the decomposition of relations, following certain rules, which will be discussed below. It is the decomposition that brings the relation to the next normal form.

Each normal form corresponds to some definite set of constraints, and the relation is in some normal form if it satisfies its inherent set of constraints.

The first normal form requirement is a common baseline requirement of the classic relational data model. A significant limitation of the first normal form is that the attributes of a relation are atomic, that is, the attributes are not relations themselves, and are not further divided (like atoms).

In the theory of relational databases, theoretically 7 normal forms are known, here the following sequence of 6 normal forms is distinguished:

· The first normal form (1NF);

· Second normal form (2NF);

· Third normal form (3NF);

Boyes-Codd normal form (BCNF);

· Fourth normal form (4NF);

· The fifth normal form, or the normal form of the projection-join (5NF or PJ / NF).

The first three normal forms are of practical importance.

Basic properties of normal forms

The design process is based on the method of decomposing a relationship in the previous normal form into two or more relationships that satisfy the requirements of the next normal form.

The most important normal forms of relations in practice are based on the concept of functional dependence, which is fundamental in relational database theory. This concept was discussed in lecture # 4. Let us refine the definitions by extending them to sets of fields.