Data modification requests. Databases - Integrity

Windows 10

One of the fundamental concepts in database technology is the concept of integrity. In the general case, this concept is primarily due to the fact that the database reflects in information form a certain object of the real world or a set of interconnected objects of the real world. In the relational model, real-world objects are represented as a set of interrelated relationships. The integrity of the database is the rules and means that ensure the reliable implementation of established inter-table communications between all the data contained in the database. Maintaining data integrity is a serious and complex issue. When a database is used, its damage can occur for several reasons: during a computer crash, due to errors in the software, due to incorrect user actions.

Any change in the subject area that is significant for the constructed model should be reflected in the database, and at the same time, an unambiguous interpretation of the information model in terms of the subject area should be preserved.

Supporting integrity in a relational data model in its classical sense includes 3 aspects.

Firstly, it is support for structural integrity, which is interpreted as the fact that a relational DBMS should only allow work with homogeneous data structures of the “relational relation” type. Moreover, the concept of “relational relation” must satisfy all the restrictions imposed on it in the classical theory of relational database. The absence of duplicates of tuples (row of relations), respectively, the mandatory presence of a primary key (one or more columns (attributes) that uniquely identify each record in the table, i.e., clearly distinguish one record from another), the absence of the concept of tuple ordering.

In addition to structural integrity, it is necessary to consider the problem of undefined Null values. An undefined value in a relational database is interpreted as a value that is currently unknown. This value, when additional information appears at any time, can be replaced by some specific value.

Secondly, this is support for language integrity, which consists in the fact that a relational DBMS should provide languages \u200b\u200bfor describing and manipulating data no lower than the SQL standard. Other low-level data manipulation tools that do not comply with the standard should not be available.

That is why access to the information stored in the database, and any changes to this information can only be done using SQL statements.

Thirdly, this is support for referential Integrity (DRI), which means providing one of the specified principles for the relationship between instances of tuples of interrelated relationships:

    tuples of a subordinate relation are destroyed when the tuple of the main relation associated with it is deleted;

    tuples of the main relation are modified when the tuple of the main relation associated with it is deleted, and an undefined Null value is put in the place of the key of the parent relation.

Referential integrity provides support for the consistent state of the database during data modification during add or delete operations.

In addition to the indicated integrity constraints, which in general do not determine the semantics of the database, the concept of semantic integrity support is introduced.

Structural, linguistic and referential integrity determine the rules of the DBMS with relational data structures. Support requirements for these three types of integrity indicate that each DBMS should be able to do this, and developers should take this into account when building a database using a relational model. These three aspects have nothing to do with the contents of the database. To determine some of the restrictions that are associated with the content of the database, other methods are required. It is these methods that are brought together in support of semantic integrity.

Semantic support can be provided in two ways:

    declarative, executed by means of the SQL language;

    procedural, performed through triggers and stored procedures.

The declarative path is associated with the presence of mechanisms within the DBMS that ensure the verification and implementation of a number of declaratively defined restriction rules, most often called “Business Rules” or declarative integrity constraints.

The following types of declarative integrity constraints are distinguished:

    attribute integrity constraints: default value, specifying mandatory or optional values \u200b\u200b(Null), setting conditions for attribute values. Setting a default value means that each time a new row is entered into a relation, in the absence of data in the specified column, this attribute is assigned exactly the default value;

    integrity constraints set at the domain level, supported by the domain structure. These restrictions are convenient if the database contains several columns of different relations that take values \u200b\u200bfrom the same set of valid values;

    integrity constraints specified at the relationship level. Some semantic rules cannot be converted to expressions that will only apply to one column;

    integrity constraints set at the level of communication between relationships: setting binding relationships, principles of cascading deletion (when deleting an entry in the main table, deletes all related records in the subordinate table) and cascading data changes (when changing the value of the key field in the main table, updates all related records in the subordinate table), the job of supporting constraints on communication power. These types of restrictions can be expressed by setting the mandatory or optional value of foreign keys (one or more fields in the table that contain a link to the primary key field (s) in another table in interrelated relationships).

Access Data Integrity

Access uses a system of rules designed to maintain stable and correct relationships between records in related tables, as well as providing protection against accidental deletion or modification of related data. The following restrictions are set for this:

    the linked field of the main table is a key field or has a unique index;

    linked fields have one data type;

    if the tables are related, then they must be Access database tables. It is not possible to establish data integrity for linked tables from databases of other formats. To ensure integrity, the database in which the tables are located must be open.

To comply with these rules, Access monitors and blocks the following actions:

    you cannot enter a value not contained in the key field of the main table in the foreign key field of the linked table. However, you can enter Null values \u200b\u200bin the foreign key field to indicate that the records are not related. For example, you cannot save a record registering a book written by a non-existent author, but you can create a record for a book that has not yet been assigned to any of the authors by entering Null in the id_aвtopa key field;

    you cannot delete a record from the master table if there are records related to it in the subordinate table. For example, it is not possible to delete an entry from the Publishers table if there are books published by this publisher in the Documents table;

    you cannot change the primary key value in the main table if there are records associated with this record. For example, it is not possible to change the employee code in the Authors table if there are books by that author in the Documents table.

Database integrity

Database integrity

Database integrity is a database property, which means that the database contains complete and consistent information necessary for the correct functioning of applications. To ensure integrity, impose integrity constraints.

See also:  Database

Finam Financial Dictionary.


See what "Database Integrity" is in other dictionaries:

    database integrity  - The database property, determined by the ability of the database management system to protect database components and communications from distortion as a result of incorrect operations and hardware failures. [GOST 20886 85] Topics data organization in ... ... Technical Translator Reference

    Database integrity  - 2. Database integrity Database property, determined by the ability of the database management system to protect database components and communications from distortion as a result of incorrect operations and hardware failures Source: GOST 20886 85 ... Glossary of terms of normative and technical documentation

      - (database integrity) correspondence of the information available in the database to its internal logic, structure and all clearly defined rules. Each rule that imposes a certain restriction on the possible state of the database is called a restriction ... ... Wikipedia

    The database server performs maintenance and management of the database and is responsible for the integrity and safety of data, and also provides input-output operations when the client accesses information. The client-server architecture consists of clients and servers. ... ... Wikipedia

    The hierarchical model of the database consists of objects with pointers from parent objects to descendants, connecting together related information. Hierarchical databases can be represented as a tree consisting of objects of various levels. ... ... Wikipedia

    There are other meanings for this term, see Presentation. A view (English view, a more consonant non-standard name “view”, is often used in the slang of programmers as a loan from the English “view”, “view”) ... ... Wikipedia

    Relational database A database based on a relational data model. The word "relational" comes from the English. relation To work with relational databases, relational DBMSs are used. Using relational databases was ... ... Wikipedia

    There are other meanings for this term, see Index. An index is a database object created to improve data retrieval performance. Tables in a database can have a large number of rows that are stored in ... Wikipedia

    There are other meanings for this term, see Trigger (s). A trigger is a stored procedure of a special type that the user does not directly call, and the execution of which is caused by the modification action ... ... Wikipedia

    There are other meanings for this term, see Cursor (s). The cursor is a link to the contextual memory area [source not specified 126 days]. In some implementations of the logical information language SQL (Oracle, ... ... Wikipedia

Books

  • The core of Oracle. An internal device for administrators and data developers, Lewis Jonotan. In this book, the author provides only the most necessary information about the internal structure of the Oracle DBMS, which every database administrator must know in order to successfully deal with ...
  • The core of Oracle. An internal device for administrators and database developers, Lewis Jonathan. In this book, the author provides only the most necessary information about the internal structure of the Oracle DBMS, which every database administrator must know in order to successfully deal with ...

bank base given case technology

The concept of integrity is one of the fundamental in the theory of databases. Any database contains information about real-world objects that are in communication with each other. Database integrity is understood as the correspondence of the domain model stored in the database to real-world objects and their relationships at each time point. Integrity is physical and logical.

Physical integrity refers to the possibility of physical access to data at any given time, as well as the fact that this data is not lost. Logical value means the absence of logical errors in the database - non-disruption of the structure of the entire database or individual objects, deletion or incorrect change of relationships between objects, etc.

Maintaining the integrity of the database includes checking the integrity and its restoration in case of detection of contradictions. In the relational model, there are three main criteria that provide support for the logical integrity of the database.

  • 1. Support for structural integrity. This means that any relational database is built only on data structures that satisfy the properties of relational relations.
  • 2. Support for language integrity. Any relational DBMS should be able to describe and manipulate data in a format not lower than the SQL standard. This means that access to database information can only be done using SQL statements.
  • 3. Support for referential integrity. This principle states that when data is changed in an interconnected relationship, only one of the following actions can occur:
    • - tuples of a subordinate relation must be destroyed when the tuple of the main relation associated with them is deleted;
    • - tuples of the main relation can be deleted only if there are no tuples of the subordinate relation associated with them;
    • - tuples of a subordinate relation when deleting a tuple of the main relation associated with them, are not deleted, but are modified in such a way that the value of the parent relation key is set to NULL.

Referential integrity keeps the database in a consistent state when data is modified during the addition or deletion.

In addition to the considered criteria, the concept of the semantic integrity of the database is also introduced. Ensuring the semantic integrity of the database model is one of the most important tasks. This mechanism is called the integrity constraint mechanism. Integrity restrictions mean, in essence, the possibility, or vice versa, the inability of individual information units contained in the database to accept certain values \u200b\u200band participate in relations with other information units.

Limitations of semantic integrity can be specified either declaratively (when describing the database) or procedurally (in data processing programs) in ways. The following types of declarative integrity constraints are available:

1. Attribute integrity restrictions - restrictions are set on the default value, the uniqueness of the value, on the type and format of values, the range of values, on indicating the sign of a specific or undefined value, on the domain corresponding to this attribute.

When setting the default value, each time a new record is entered into the table, if there is no direct indication of the attribute value, this attribute is assigned the specified value.

The uniqueness constraint is usually set when the unique value is the identifier of some object. Therefore, the sign of uniqueness often corresponds to the primary key of the relationship (in case the key is not composite). When establishing a limit on uniqueness, the validity of this value is checked by viewing the entire table.

The type of value determines the valid characters for the attribute (numbers, letters, logical variables, etc.), and the format sets more stringent restrictions on the possible values \u200b\u200b(for example, the “date” format).

The range limit is set in most cases for numeric data types. Ranges are one-sided (only the lower or upper boundaries are indicated) or bilateral. The one-way range is always open, and the two-way range can be either open or closed.

The sign of a certain value (mandatory) does not allow an empty attribute value.

A domain restriction means that an attribute can only accept values \u200b\u200bfrom a specific set of values.

  • 2. Restrictions on the integrity of the tuple. This implies possible restrictions on the ratio of the values \u200b\u200bof individual attributes within one row of the table. For example, the date the book was returned to the library cannot precede the date the reader took it.
  • 3. Restrictions on the integrity of the relationship. In this case, restrictions are set on the ratio of data located in different rows of the table. An example is the uniqueness of a primary key.
  • 4. Restrictions on the integrity of interrelated relationships - limitations on the integrity of communications and restrictions on existence.

The restriction of the integrity of the relationship means that the value of the foreign keys of the table reflecting the relationship between the two objects must always correspond to one of the primary key values \u200b\u200bof the tables describing these objects.

The existence restriction means that for the existence of a given object in this table, it is necessary that it be associated with a specific object in another table. This restriction is stronger than the restriction on communication, since in this case the obligatory class of communication is established. Deleting records from a subordinate table should never violate a constraint on integrity or communication.

Sometimes the so-called existence feedback is implemented in the database, when a record in the main table cannot exist without related records in the subordinate table. This type of integrity constraint is more complicated from the point of view of its control, since when deleting a record from a subordinate table, you need to check whether there are more records in it with the same foreign key value.

In addition, in some cases, there are limitations to the integrity of the cardinality of the connection: the number of elements in the communication should be in a certain range - closed or open. For example, one record in the main table must correspond to at least a certain number of records in the subordinate table.

There are also integrity constraints defined at the level of more than two tables. These restrictions maintain logical consistency between data stored in related tables.

  • 5. Integrity constraints of algorithmic dependencies. If the database contains derivative data that is obtained as a result of some operations performed on other data (source data), then a change in the derived data should either be a consequence of a change in the source data, or generally prohibited.
  • 6. Prohibition of updates. This prohibition can apply to any object — an attribute, row, or table. So, in many DBMSs this prohibition applies to primary key values.

Integrity constraints are instantaneous and delayed. Deferred integrity constraints in the process of performing any actions are not respected, but must be observed after they are completed. An example of deferred constraints can be transactions — a set of actions that translate a database from one consistent state to another consistent state. During the transaction, the database may be in an inconsistent state for some time, however, after the transaction is completed, the agreed state of the database must be restored.

According to the database validation verification mode, the integrity check can be performed at the time of data operations (online mode) or independently at a given point in time (database audit).

The integrity constraint can apply not only to data, but also to service information. In particular, in relational DBMS, this means maintaining consistency between index files and their corresponding indexed database files.

There is also the concept of information integrity of a data bank, which consists in ensuring the co-ordination of the joint functioning of all its components, which include database files, application program files, input-output formats, reports, etc.

Different DBMSs have different capabilities to support integrity constraints. If any integrity constraint is not automatically supported by a specific DBMS, then this task should be solved by the DBA.

To maintain integrity, a trigger mechanism can be used - actions that are activated when a specific event occurs.

A number of integrity constraints follows directly from the description of the subject area within the framework of the ER-model.

  • 1. Limit on uniqueness. Table keys are unique identifiers.
  • 2. There are functional dependencies between primary keys (unique identifiers) and other attributes.
  • 3. If there is a relationship between entities, there may be restrictions on communication. The type of connection and the class of its membership determines the integrity constraint on the relationship between entities.
  • 4. For static properties of an entity, you can set a ban on updating. If a property is a condition, then the attribute values \u200b\u200bmay be undefined.

Summing up, it must be emphasized that when designing a database, it is necessary to determine all integrity constraints that follow both from the specifics of the subject area and from the features of the applied programs, and develop mechanisms for their maintenance.

Database Integrity

By database integrity we mean a certain set of requirements, violation of which will lead to a contradiction between the database and the subject area that it reflects.

Modern DBMSs support the ability to comply with certain integrity requirements.

Let's consider the types of integrity and the DBMS mechanisms supporting these types.

1) Essential integrity or integrity according to the primary key; by its definition, the value of the primary key in the table must be unique since The basic principle of RBD it sounds that the data in the table is accessed by chain. DB Nameà Table name à   Column name à primary key (value).

All modern DBMSs support the uniqueness of the primary key. The mechanism for maintaining the uniqueness of the primary key is based on the concept of an index, see below.

When determining the primary key, the DBMS automatically creates an index to support its uniqueness and to search for the primary key.

2) Referential integrity - this integrity is maintained in the relations between tables. In order for the contents of two related tables to correctly reflect the subject area. Certain restrictions (integrity constraints) must be imposed on operations that change the contents of tables. This applies to three operations:

Delete records (from the main table)

Editing records (in the main table)

Insert records into secondary tables.

They introduce restrictions on the execution of operations: deletion from the main table, Updates in the main table, insertion of rows in the secondary table.

Delete from the main table

DBMS supports 4 types of script when deleted from the main table.

1) when deleted from the main table, it automatically deletes the related records from the subordinate table (cascade).

2) A ban on deleting a record from the master table if it is associated with any records in the subordinate table.

3) After deleting an entry from the main table, the foreign keys of the related records in the subordinate table are assigned the valuesNULL

4) When a record is deleted from the main table, the foreign keys of the secondary records are assigned default values.

Updating records in the main table

(This applies to updating the primary key)

1) When updating the primary key of the main table, the foreign key values \u200b\u200bof the subordinate tables are cascaded.

2) When changing the primary key in the main table, the corresponding values \u200b\u200bare assigned to the keys in the subordinate tableNULL

3) When changing the primary key in the main table, the corresponding values \u200b\u200bin the subordinate table are assigned the default value.

4) (2) The prohibition on updating the primary keys of those records of the main table that are associated with any records in the subordinate table is established.

The default is when defining the table structure for each column.

A) a specific default value

B) value assignment is allowedNULL

A simple mechanism for maintaining database integrity is the table column type. The DBMS automatically monitors write operations to the table and cases of type mismatch, either rejects the record — generates an error or performs implicit type conversion. Each DBMS has its own rules for generating errors and converting to an implicit type.

For example, in some DBMSs 32 bits are possible integerà 16bit integer, while in others it is impossible because of the possibility of data loss over 16 bits.

4) Domain integrity maintenance mechanism. A domain in the RDB is understood as a set of possible values \u200b\u200bthat accept the data stored in this column. In particular, if the domain matches the set of values \u200b\u200bof this type, but often the domain is an additional limitation. If the column contains the month numbers 1..12, then the domain contains 12 values, and the type is integer, that is, the domain is already. In most DBMSs, a domain is defined by a set of conditions that must be satisfied for the data stored in the column. If the recorded data does not satisfy the condition, an error is generated. (condition (1<= =>12 )), preventing the incorrect indication of the month.

5) Maintaining the presence of possible keys in the table.

The uniqueness of the possible keys as primary is supported by indexing. the unique index attribute guarantees the uniqueness of any column or group of columns; in addition, the index speeds up the search for the corresponding key. Slows down editing operations.

6) Some DBMSs support the creation of new data types. These includeSQL Server   2005tam based on technology.Net   you can create new data types that are actually objects with their own properties and methods. (Already the translation of the RDB into the Object RBD).

7) Triggers (see the lecture on triggers) are the most powerful means of maintaining the integrity of databases. Triggers are procedures stored in a database written in the language corresponding to the DBMS and automatically launched when an event occurs. Using triggers, you can check any conditions and prohibit the execution of any operation, or make additional changes to other tables. Triggers have three main functions:

1) Prohibit certain actions that contradict the integrity of the database.

2) Along with the operation performed, make changes to other database tables or database structure.

3) the function of logging certain actions in the database.

Execution of data modification statements in the INSERT, DELETE, and UPDATE database tables may result in a violation data integrity  and their correctness, i.e. to the loss of their credibility and consistency.

In order for the information stored in the database to be unambiguous and consistent, some restrictive conditions are established in the relational model — rules that determine the possible values \u200b\u200bof the data and provide a logical basis for maintaining the correct values. Integrity constraints  to minimize errors that occur when updating and processing data.

In a database built on a relational model, a number of integrity rules are set, which, in fact, are restrictions for all valid states of the database and guarantee the correctness of the data. Consider the following types of restrictions data integrity:

  • required data;
  • restrictions for field domains;
  • corporate restrictions;
  • entity integrity;
  • referential integrity.

Mandatory data

Some fields must always contain one of the valid values, in other words, these fields cannot have an empty value.

Limitations for Field Domains

Each field has its own domain, which is a set of valid values.

Corporate Integrity Constraints

There is a concept of "corporate integrity constraints  "as additional support rules data integritydefined by users accepted by the enterprise or database administrators. Enterprise restrictions are called business rules.

Entity integrity

it integrity constraint  concerns primary keys  base tables. By definition,   primary key  - The minimum identifier (one or more fields) that is used to uniquely identify records in the table. Thus, no subset primary key  may not be sufficient to uniquely identify records.

Entity integrity  determines that no field is in the base table primary key  cannot contain missing NULL values.

Assuming the presence of a NULL qualifier in any part primary key, this is equivalent to the statement that not all of its fields are necessary for the unique identification of records, and contradicts the definition primary key.

Referential integrity

Indicated integrity constraint  concerns foreign keys.   External key - this is a field (or many fields) of one table, which is the key of another (or the same) table. Foreign keys are used to establish logical relationships between tables. A relationship is established by assigning the foreign key values \u200b\u200bof one table to the key values \u200b\u200bof another.

Between two or more database tables may exist relationship of subordinationwhich determine that for each record of the main table (also called   parent) one or more entries may exist in subordinate table  (also called subsidiary).

There are three types of relationships between database tables:

  • one-to-many;
  • "one to one";
  • many-to-many.

The one-to-many relationship occurs when a single record parent table  may correspond to multiple records of a child. The one-to-many relationship is sometimes called the many-to-one relationship. In both cases, the essence of the relationship between the tables remains unchanged.

The one-to-many relationship is most common for relational databases. It also allows you to model hierarchical data structures.

A one-to-one relationship occurs when a single entry in parent table  corresponds to one entry in the child. This relationship is much less common than the one-to-many relationship. It is used if you do not want the database table to “swell” from secondary information. Using the one-to-one relationship leads to the fact that in order to read related information in several tables, it is necessary to perform several read operations instead of one when the data is stored in one table.

The many-to-many relationship occurs in the following cases:

  • single entry in parent table child table ;
  • single entry in child table  matches more than one entry in parent table.

It is believed that any many-to-many relationship can be replaced by a one-to-many relationship (one or more).

Often the relationship between tables is established by primary key, i.e. foreign key values \u200b\u200bof one table are assigned values primary key  other. However, this is not necessary - in general, communication can also be established using secondary keys. In addition, when establishing relations between tables, the indispensable uniqueness of a key that ensures the establishment of a connection is not required. Foreign key fields are not required to have the same names as the key names to which they correspond. A foreign key can refer to its own table - in this case, the foreign key is called recursive.

Referential integrity determines: if a foreign key exists in the table, then its value must either correspond to the value primary key  some record in the base table, or set by the NULL determinant.

There are several important points related to foreign keys. First, you should consider whether it is permissible to use null values \u200b\u200bin foreign keys. Generally, if participation child table  Since it is mandatory, it is recommended that you prohibit the use of null values \u200b\u200bin the corresponding foreign key. At the same time, if there is partial participation child table  in connection, the placement of empty values \u200b\u200bin the foreign key field should be allowed. For example, if you need to specify a buyer in the transaction fixing transaction of a certain trading company, then the Client Code field should have the NOT NULL attribute. If the sale or purchase of goods is allowed without specifying a customer, then for the ClientCode field you can specify the NULL attribute.

The next problem is with the organization referential integrity support  when performing data modification operations in the database. The following situations are possible here:

  1. Insert a new line into child table. To provide referential integrity  you must ensure that the foreign key value of the new line child table primary key  one of the lines parent table.
  2. Delete row from child table. No violations referential integrity  not happening.
  3. Update foreign key in line child table. This case is similar to the first situation described above. To save referential integrity  you must ensure that the foreign key value in the updated row child table  equal to an empty value or some specific value present in the field primary key  one of the lines parent table.
  4. Insert row in parent table. Such an insert cannot cause a violation. referential integrity. The added row simply becomes the parent with no children.
  5. Delete row from parent table. Referential integrity  will be violated if in child table  there will be lines referring to the deleted line parent table. In this case, one of the following strategies may be used:
    • NO ACTION. Delete row from parent table  prohibited if in child table  there is at least one line referencing it.
    • CASCADE. When deleting a row from parent table  all lines referring to it are automatically deleted child table. If any of the rows to delete child table  acts as the parent side in some other way, then the delete operation is applied to all rows child table  this connection, etc. In other words, deleting a line parent table  automatically applies to any child tables.
    • SET NULL When deleting a row from parent table in all lines of the child relation referring to it in the foreign key field corresponding to primary key  deleted line, an empty value is written. Therefore, deleting rows from parent table  will cause the entry of an empty value in the corresponding field child table. This strategy can only be used when in a foreign key field child table  allowed to put empty values.
    • SET DEFAULT. When deleting a row from parent table  in the foreign key field of all lines referring to it child table  the value specified for this field as the default value is automatically placed. So removing a line from parent table  causes the default value to be placed in the foreign key field of all rows
Do you like the article? Share with friends:
You may also be interested.