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. To do 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 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 sets of tests 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 relational base data is to group attributes and relationships so as to minimize data redundancy and thus reduce the amount of memory required to physically store relationships presented in the form of tables.
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 AB), 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 relationship indicates how its attributes can be related to each other and also defines 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 dependency is central to the normalization process.
Lecture 3. General concepts and definitions. Classification of functions. Function limit. Infinitely small and infinitely great 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: v 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 of some quantities (independent variables) completely determine the values of 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 in such a way that each considered value of x corresponds to a single well-defined value of the value y (formulated by NI Lobachevsky).
Designation y = f (x) (1)
x- independent variable or argument;
y- dependent variable (function);
f- characteristic of the function.
The collection of all values of 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 value of the radius corresponds to the value of the area of the 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 = 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
- 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 = kx, where k- coefficient of proportionality.
Function graph
- Linear dependence
Definition
Two variables are related linear relationship, if, where are some constants.
Function graph
- Inverse proportional relationship
Definition
Two variable quantities are called inversely proportional if, when one of them changes in a certain ratio, the other changes in the opposite ratio.
- Quadratic dependence
In the simplest case, the quadratic dependence has the form, where k is some constant value. The function graph is a parabola.
- Sinusoidal dependence.
When studying periodic phenomena important role plays sinusoidal dependence
- the function is called harmonic.
A- amplitude;
Frequency;
Initial phase.
The function is periodic with a period. Function values in points x and x + T differing by the period are the same.
The function can be reduced to the form 
, where . Hence, we obtain that the harmonic graph is a deformed sinusoid with amplitude A and period T, shifted along the ОХ axis by the value
|
| |
Methods for setting a function
Usually, three ways of defining a function are considered: analytical, tabular, graphical.
- 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 = 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 x to get the corresponding function value.
Example 
... Three actions are performed on the argument value.
- Tabular way of defining a function
This method establishes correspondence between variables using a table. Knowing analytic expression function, you can represent this function for the argument values of interest to us using a table.
Is it possible to switch from a table function definition to an analytical expression?
Note that the table does not give all the values of the function, and the intermediate values of the function can be found only approximately. This so-called interpolation functions. Therefore, in 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 of the argument available in the table will give the corresponding table values functions. This kind of formula is called interpolation.
- Graphical way to define a function
Analytical and tabular methods do not provide a visual representation of the function.
This disadvantage is deprived graphical way function assignments y = f (x) when the match between the argument x and function y set using the graph.
Implicit function concept
A function is called explicit if it is specified by a formula, the right side of which does not contain a dependent variable.
Function y from argument x is called implicit if it is given by the equation
F (x, y) = 0(1) unresolved with respect to the dependent variable.
Concept inverse function
Let the function y = f (x)(one). By specifying the values of the argument x, we obtain the values of the function y.
It is possible, considering y argument and X- function, set values y and get values x... In this case, equation (1) will determine x as an implicit function of y... This last function 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 y satisfies the condition
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, denoted as A<->B or B<->A.
If the relation is in 1NF, then all non-key attributes functionally depend on the key with varying degrees of dependence.
Partial function cash dependence (partial FZ) is called the dependence of a non-key attribute on a part of a composite key. In this respect, the Must attribute is functionally dependent on the full name attribute, which is part of the key. Thus, the Must attribute is partially dependent on the key of the relationship.
An alternative option is full functional dependency bridge a non-key attribute from the entire composite key. In our example, the VidZan attribute is fully functional depending on the composite key.
Attribute C depends on Attribute A transitively (exists transitive dependence), if for attributes A, B, C the conditions A-> B and B-> C are met, but there is no inverse relationship. In relation to Fig. 4.4 attributes are transitively related:
F & O -> D o lzhn -> Salary
There can be a multivalued relationship between attributes.
In relation to R, attribute B multiply depends from attribute A, if each value of A corresponds to a set of values of 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.
For example, let a teacher teach several subjects, and each subject can be taught by several teachers, then the name is dependent<=>Thing. So, from the table shown in Fig. 4.4, it is clear that the teacher Ivanov I.M. conducts classes in two subjects, and the DBMS discipline is read by two teachers: Ivanov I.M. and Petrov M.I.
Comment . In the general case, there can be dependencies between two attributes of the same relationship: 1: 1, 1: M, M: 1 and M: M. Since the dependency between attributes is the cause of the anomalies, one tries to dismember the relationship with the attribute dependencies into multiple relationships. As a result, a set of related relations (tables) is formed with links of the form 1: 1, 1: M, M: 1 and M: M (subsection 3.2). 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. In the case of two attributes, the absence of dependence of attribute A on attribute B can be denoted as follows: A¬-> B. The case when A¬-> B and B¬-> A can be denoted by A¬<->V.
4.3.3 Armstrong's Axioms
To determine the keys and understand the logical consequences of functional dependencies in the general case, it is necessary to calculate the closure F + from F or at least know for a given F and functional dependence X Y, is it contained X Y v F + . To do this, you need to have inference rules that specify how to infer other dependencies from one or more dependencies.
Many of these rules are called by Armstrong's axioms... Suppose given some relationship scheme with multiple attributes M, universal set attributes, and many functional dependencies F, linking only the attributes belonging to M. Then we have the following inference rules (axioms):
A1: (reflexivity). If Y ≤ X ≤ M thenX Y logically follows from F. Note that this rule gives trivial dependencies, that is, dependencies, the right side of which is contained in the left side. Its use is independent of F.
A2: (replenishment). If XY and Z≤ M, then X U Z Y U Z . It is important to remind that this addiction XY either belongs F, or can be deduced from belonging F dependencies using the described axioms.
A3: (transitivity). If XY and YZ, then XZ.
It is relatively easy to prove that Armstrong's axioms are reliable, that is, they lead only to true conclusions. This means that using them, we cannot deduce from F any dependency that doesn't belong F + . It is more difficult to prove their completeness, which means that these axioms can be used to obtain every fair consequence of the dependencies. This means that for a given set of dependencies F rules allow us to output all dependencies belonging to F + .
From axioms Armstrong 5 more axioms (extension, continuation, pseudotransitivity, union, and decomposition) are derived that are used to construct a complete family FZ.
Functional dependencies
Functional dependency describes the relationship between attributes and is one of the basic concepts of normalization. Let's pretend that 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 А (ВаА).
Functional dependence, valid under any conditions, is called trivial... Non-trivial dependencies define integrity constraints for relationships.
Transitive dependency for attributes A, B and C some relation means the following: if АаВ and ВаС, then С transitively depends on the attribute А through the attribute В (provided that А functionally does not depend on В or С).
To eliminate data redundancy, which can lead to loss of integrity, it is necessary to use the minimum sufficient set of dependencies.
Database design using normalization starts 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) there should be no repeating groups of fields in the table;
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 full set key fields, that is, each attribute of the relationship is in full or partial functional dependence on another attribute.
The functional dependence of AàB is complete functional dependence, if the removal of any attribute from A leads to the loss of this dependence. The functional dependence AàB is called partial, if there is a certain attribute in A, upon deletion of which this dependence is preserved.
A table in third normal form must meet all the requirements of 2NF, none of the non-key fields is identified by another non-key field, that is, a relationship that is in the first and second normal forms and does not have attributes that are not included in the primary key of attributes that would be in a transitive functional dependence on this primary key.
Boyes-Code Normal Form (NFBK) is based on functional dependencies, which takes into account all potential relationship keys, but with more stringent constraints.
Determinant of functional dependence is an attribute (or group of attributes) on which some other attribute depends fully functionally.
To verify that an attitude belongs to the NBFK, it is necessary to find all its determinants and make sure that they are potential clues.
The difference between 3NF and NFBK is that the functional dependence AàB is allowed in relation to 3NF if attribute B is the primary key and attribute A is not necessarily a potential key. For NBFK, this dependence is allowed only when attribute A is a candidate key. Therefore, NFBK is a stricter version of 3NF, since every NFBK is 3NF, but not every 3NF is NFBK.
A relationship is in NBFK only if each of its determinants is a potential key.
Fourth normal form(4NF) is a relation in NFBC that does not contain nontrivial multivalued dependencies.
Multi-valued dependence represents such a relationship between the attributes of a relationship (eg, A, B, and C) such that each value of A is a set of values for B and a set of values for C. However, the set of values for B and C are independent of each other.
Multivalued dependence can be additionally defined as trivial or non-trivial. Multivalued dependence АаВ of some relation R is defined as trivial if attribute В is a subset of attribute А or. Conversely, a multivalued relationship is defined as non-trivial if neither condition is met. Trivial multivalued dependency does not impose any restrictions on given attitude, and non-trivial - imposes.
When splitting a relation using a projection operation, the decomposition method used is precisely determined. It is necessary that when you reconnect the resulting relationships, you can restore the original relationship. This decomposition is called lossless join decomposition(or a win-win or non-additive join), since it preserves all the data of the original relationship, and also avoids the creation of additional dummy rows.
Fifth normal form (5NF), which is also called projective-connective normal form, means that a relation in this form has no connection dependencies. A relation R with a subset of attributes A, B, ..., Z satisfies the connection dependency if each permissible value R is equal to the union of its projections onto subsets A, B, ..., Z.
Attribute B functionally dependent from attribute A, if exactly one value B corresponds to each value of A.
Designation: A → B. This means that in all tuples with the same value for attribute A, attribute B will also have the same value.
If there is a functional dependence of the form A → B and B → A, then between A and B there is one-to-one correspondence, or functional dependence... O
Designation: A↔B or B↔A.
If the relation is in 1NF, then all non-key attributes functionally depend on the key with varying degrees of dependence.
Partial dependence(partial functional dependence) - dependence of a non-key attribute on a part of a composite key.
Complete functional dependence- dependence of a non-key attribute on the entire composite key.
Transitive dependency
Attribute C depends on Attribute A transitively(exists transitive dependency), if the conditions A → B and B → C are satisfied for the attribute А, В, С, there are no inverse relationships.
Multiple addiction
In relation to R, attribute B multiply depends from attribute A, if each value of A corresponds to a set of values of B that are not related to other attributes of R.
Designations: A => B, A<=B, A<=>B.
Mutually independent attributes
Two or more attributes are called mutually independent if none of these attributes is functionally dependent on other attributes.
Designations: А → В, А = В.
Normal forms:
First normal form(1NF). A ratio is in 1NF if all its attributes are simple (have a single value).
Second normal form(2NF). A relationship is in 2NF if it is in 1NF and each non-key attribute is functionally dependent on a primary key (composite).
Third normal form(3NF). A relationship is in 3NF if and only if all the attributes of the relationship are mutually independent and completely dependent on the primary key.
Boyes-Codd normal form(NFBK). A relationship is in NFBK if it is in 3NF and there are no dependencies of keys (composite key attributes) on non-key attributes in it.
Fourth normal form(4NF). A relationship is in 4NF if and only if there is a multivalued relationship A => B, and all other attributes of the relationship functionally depend on A.
Fifth normal form(5NF). A relationship is in 5NF if it is in 4NF and satisfies the connection dependencies with respect to its projections.
Sixth normal form(6NF). The ratio is in 6NF if and only if it cannot be subjected to further decomposition without loss.
Ensuring the consistency and integrity of data in the database
Answer :
Integrity- this is a property of the database, which means that it contains complete, consistent and adequately reflecting the subject area information.
Distinguish:
Physical integrity- Availability physical access to the data and the fact that the data is not lost.
Logical integrity- the absence of logical errors in the database, which include violation of the structure of the database or its objects, deletion or change established links between objects, etc.
Maintaining database integrity includes:
Integrity check (control)
Recovery in case of detection of inconsistencies in the database.
Integral state is set using integrity constraints(conditions that the data must satisfy). Two type of integrity constraints:
Constraining Relationship Attribute Values. for instance: Requirement of invalid NULL values, inadmissibility of duplicate values in attributes, control of belonging of attribute values of a given range.
Structural Constraints on Relation Tuples... Defines requirements entity integrity and link integrity.
Requirement entity integrity is that any tuple of a relation must be different from any other tuple of this relation, in other words, any relation must have primary key.
Requirement link integrity is that for each foreign key value of the parent table, there must be a row in the child table with the same primary key value.
Entity-relationship method
Answer :
Entity-relationship method(method "ER-diagrams") is a method based on the use of diagrams, called ER-instance diagrams and ER-type diagrams, respectively.
Basic concepts
The essence Is an object, information about which is stored in the database.
Attribute Is a property of an entity.
Entity key Is an attribute (set of attributes) used to identify an entity instance.
Connection between entities Is the relationship between the attributes of these entities.
Graphic tools used to obtain clarity and ease of design:
DiagramER-copies;
DiagramER-type or ER-diagram.
Based on the analysis of ER diagrams, the relationship of the designed database is formed. This takes into account the degree of relationship between entities and their class of belonging.
Degree of connection- this is a characteristic of the relationship between entities (1: 1, 1: M; M: 1; M: M).
Class of affiliation entities can be: compulsory and optional.
Required- if all instances of the entity necessarily participate in the considered relationship.
Optional- not all instances are involved in the relationship in question.
Database design stages
Answer :
I... Concept design- collection, analysis and editing of data requirements.
Target: creating a conceptual data model based on the user's understanding of subject area.
Procedures:
Definition of entities and their documentation;
Determination of relationships between entities and their documentation;
Creation of a domain model;
Determination of attribute values;
Defining primary keys for entities.
II... Logical design- based on the conceptual model, a data structure is created.
Target: transformation of the conceptual model based on the selected data model into a logical model, independent of the features of the DBMS used in the future for the physical implementation of the database.
Procedures:
Choosing a data model;
Defining a set of tables and documenting them;
Normalization of tables;
Determine and document requirements for maintaining data integrity.
III... Physical design- definition of data features and access methods.
Purpose: description of a specific implementation of the database, placement in external memory computer.
Procedures:
Database tables design;
Design physical organization DB;
Development of a database protection strategy.
Database life cycle
Answer :
DB life cycle Is the process of designing, implementing and maintaining database systems.
Database lifecycle stages:
Analysis- analysis of the subject area and identification of requirements for it, assessment of the relevance of the system.
Design- creating a logical DB structures, functional description program models and information requests.
Implementation- development of software for databases, testing is in progress.
Exploitation and escort.
Stages of the life cycle of a database:
Preliminary planning- database planning, execution strategic plan database development (what applications are used, what functions they perform, what files are associated with each of these applications, and what new files and applications are under development).
Feasibility check- verification of technological, operational and economic feasibility.
Defining requirements- selection of the purpose of the database, identification of information requirements for the database, requirements for hardware and software, definition of user requirements.
Concept design- creation of a conceptual scheme.
Implementation- bringing the conceptual model to a functional database.
Selection and purchase of the required DBMS.
Converting a conceptual model to a logical and physical model.
Based on the infological model, a data schema is built for a specific DBMS.
It is determined which application processes should be implemented as stored procedures.
Implement data integrity constraints.
Design triggers.
Develop an indexing and clustering strategy, perform an assessment table sizes, clusters and indexes.
Define user access levels, develop and implement security rules.
Develop a network topology of the database.
Creation of a data dictionary.
Filling the database.
Application software development, management control.
User training.
Evaluation and improvement of the database schema.
Relationship Formation Rules
Answer :
Formation rules relationships are based on the following:
The degree of connection between entities (1: 1, 1: M, M: 1, M: M);
The class of ownership of the entity instances (required and optional).
