Languages ​​subd. Languages ​​for describing data and manipulating data

Data description languages ​​are languages high level declarative (non-procedural) type, intended for a formalized description of data types, their structures and relationships. The source texts of the data description in this language, after translation, are displayed in control tables that specify the placement in the computer memory and the connections between the data in question. In accordance with these descriptions, the DBMS finds the required data in the database, transforms it and transfers it, for example, to the user's application program that required it. When writing data to the database, the DBMS uses these descriptions to determine the location in the computer memory where it needs to be placed, converts it to a given form, and establishes the necessary connections.

The first of these functions is provided by a data description language (DDL). It is often also called a data definition language. The description of a database using LDB is called a database schema. It includes a description of the database structure and the data integrity constraints placed on it. In addition to these functions, the DML of some DBMSs also provides the ability to set restrictions on data access or user permissions in the schema. The database schema represents the intensional model subject area in a database system environment. Data Manipulation Language (DML) allows you to perform data manipulation operations in a database. The nature of these operations depends on specific model data. But in any case, such languages ​​provide for the operations of inserting new data into the database, deleting or updating existing data. Some data models provide additional navigation operations that allow you to position yourself on the desired data instance in the database before performing an operation. This data instance becomes the current one. Various possible side effects propagation of an operation across the database structure, automatic formation of new relationships between data instances, calculation of derived data, etc. In the so-called graph data models (hierarchical, network), the argument of each data manipulation operation is a single instance of the data. At the same time, operations in relational model have a multiple character.

DL and ML are not always syntactically formalized as independent languages. They may be components a unified data language that combines data definition and data manipulation capabilities. There are numerous examples of DBMS languages ​​that combine data description and data manipulation capabilities within a single syntactic framework. The most common among languages ​​of this kind is SQL language.

Data Definition Language (DDL).

Creating a table

The CREATE statement is used to create any type of objects that make up the database, including tables.

The syntax for creating a table is:

CREATE TABLE table_name(

field1 type1 [constraints],

[field2 type2 [constraints], ...]);

Possible restrictions in tables:

• * NOT NULL - the attribute value must be defined (NOT NULL option);
• * UNIQUE - attribute values ​​are unique ( unique key);
• * PRIMARY KEY - the attribute is the primary key ( primary key);
• * CHECK - defines a condition that attribute values ​​must satisfy (domain);
• * DEFAULT - assigning default values ​​for attributes.

For example:

CREATE TABLE Dealers1(

Name VARCHAR2(30),

Percent NUMBER(4,2),

Comments VARCHAR2(50) DEFAULT `no comments");

Data Definition Language (DDL) provides users with a means of specifying the type of data and its structure, as well as a means of specifying restrictions on the information stored in the database.

Operators: CREATE, ALTER (see 14), DROP.

Rice. 1.4. DBMS structure

Dictionary controller– provides access to the system catalog and work with it.

File controller– manipulates data files and is responsible for distribution disk space. It does not manage physical I/O, but rather passes requests to the appropriate OS access methods.

Database controller– interacts with programs and requests launched by users. It accepts queries and examines external and conceptual schemas to determine those conceptual records that are needed to execute a database query. The database controller then calls the file controller to execute the request. The database controller includes the following: software components:

access rights control - checks whether given user authority to perform the requested operation;

· command processor – executes the request;

· integrity control tools – check integrity support restrictions when performing data modification operations;

· Query optimizer – determines the optimal query execution strategy;

· transaction controller – processes operations received during the transaction process;

· scheduler – is responsible for the conflict-free execution of parallel operations with the database and manages the relative order of execution of operations defined in different transactions;

· recovery controller – responsible for restoring the database in case of failures to a consistent state;

· Buffer controller – responsible for transferring data between the RAM and the hard drive.

Many DBMSs support the ability to implement own operators in high-level languages ​​(COBOL, Fortran, Pascal, Ada, C). But the DBMS supports 2 specialized languages ​​for developing applications with a database - DDL (DDL - Data Definition Language) and DMD (Data Manipulation Language). YaOD– descriptive language for defining logic circuit DB. It consists of a set of statements that define the database schema. The result of compiling such descriptions (conceptual and external circuits) is a set of tables stored in a special system directory DB. (It stores metadata). DDL is not used to work with data. For this purpose it is used YaMD, which contains a set of operators that perform data processing: search, add, change and delete. Nuclear MD support is one of the main functions of a DBMS. There are 2 approaches to implementing NMD: procedural and declarative.

When using procedural nuclear medicine the user determines the sequence of actions that must be performed to obtain the desired result. This approach is similar to how implemented procedural languages programming (Pascal, C, etc.). In this case, YMD provides the user with a set of operators on the data. This type includes languages ​​based on relational algebra.

Declarative NMD allow you to define all the requirements for the resulting data using a single operator. In this case, there is no need to know the details of the internal implementation of data structures and the features of the algorithms used to retrieve them. This type includes languages ​​based on relational calculus SQL and QBE. SQL(Structured Query Language) is based on relational calculus and is supported by all relational DBMSs. An international standard has been defined for it. QBE(Query By Example) – a simple language with graphical interface, which allows non-professional users to formulate a query (for example, in the Access DBMS).

Modern DBMSs also contain a set of tools that facilitate the development of database applications - various types of generators:

• screen forms– to create data entry templates;
• reports;
• graphical representation data in the form of charts;
• applications for creating data processing programs.

Database management system (DBMS) - specialized program(usually a set of programs) designed to organize and maintain a database. To create and manage information system A DBMS is necessary to the same extent as for developing a program in algorithmic language a translator is needed.

Main functions of the DBMS:

· data management in external memory(on disks);

· data management in RAM using disk cache;

· logging changes, backup and database recovery after failures;

· support for database languages ​​(data definition language, data manipulation language).

Typically a modern DBMS contains following components:

· the kernel, which is responsible for managing data in external and RAM memory and logging;

· a database language processor that optimizes queries for retrieving and changing data and creating, as a rule, machine-independent executable internal code;

· a runtime support subsystem that interprets data manipulation programs that create user interface with DBMS;

· service programs(external utilities) providing a number of additional features information system maintenance.

Classification of DBMS.

According to the data model:

· Hierarchical;

· Network;

· Relational;

· Object-relational;

· Object-oriented.

According to the architecture of data storage organization:

· local DBMS (all parts of the local DBMS are located on one computer);

· distributed DBMS (parts of the DBMS can be located on two or more computers).

By method of accessing the database:

· File server.

In file server DBMSs, data files are located centrally on a file server. The DBMS kernel is located on each client computer. Data is accessed via local network. Synchronization of reads and updates is carried out using file locks. The advantage of this architecture is the low load on the server CPU, but the disadvantage is high load local network.

Examples: Microsoft Access, Borland Paradox.

· Client-server.

Such DBMSs consist of a client part and a server. Client-server DBMSs, unlike file-server ones, provide access control between users and have little load on the network and client machines. The server is a program external to the client, and can be replaced by another if necessary. The disadvantage of client-server DBMS is the very fact of the existence of a server and large computing resources, consumed by the server.

Examples: Firebird, Interbase, MS SQL Server, Sybase, Oracle, PostgreSQL, MySQL, LINTER.

· Built-in.

An embedded DBMS is a library that allows you to store large amounts of data on a local machine in a unified manner. Data can be accessed through SQL or through special DBMS functions. Embedded DBMSs are faster than conventional client-server systems and do not require server installation, therefore they are in demand in local software that deals with large volumes data (for example, geographic information systems).

Examples: OpenEdge, SQLite, BerkeleyDB, one of the Firebird variants, one of the MySQL variants, Sav Zigzag, Microsoft SQL Server Compact, LINTER.

Database language support

Used to work with databases special languages, collectively called database languages. Early DBMSs supported several languages ​​specialized in their functions. Most often, two languages ​​were distinguished - the database schema definition language (SDL - Schema Definition Language) and the data manipulation language (DML - Data Manipulation Language). SDL served mainly to determine logical structure DB, i.e. the structure of the database as it appears to users. The DML contained a set of data manipulation operators, i.e. operators that allow you to enter data into the database, delete, modify or select existing data.

IN modern DBMS usually a single integrated language is supported that contains all necessary funds for working with the database, starting from its creation, and providing a basic user interface with databases. The standard language most widely used today relational DBMS is the SQL (Structured Query Language) language.

The main functions of a relational DBMS supported when implementing the SQL interface are:

First of all, the SQL language combines the tools of SDL and DML, i.e. allows you to define a relational database schema and manipulate data. At the same time, naming database objects (for a relational database - naming tables and their columns) is supported at the language level in the sense that the SQL compiler converts object names into their internal identifiers based on specially supported service catalog tables. Interior The DBMS (kernel) does not work with the names of tables and their columns at all.

SQL language contains special means defining database integrity constraints. Again, integrity constraints are stored in special catalog tables, and database integrity control is ensured at the language level, i.e. When compiling database modification operators, the SQL compiler, based on the integrity constraints existing in the database, generates the corresponding program code.

Special SQL operators allow you to define so-called database views, which are actually queries stored in the database (the result of any query to a relational database is a table) with named columns. For the user, a view is the same table as any base table stored in the database, but with the help of views you can limit or, conversely, expand the visibility of the database for a specific user. Representations are also maintained at the linguistic level.

Authorization of access to database objects is also carried out on the basis special set SQL statements. The idea is that to execute SQL statements different types the user must have various permissions. The user who created the database table has full set permissions to work with this table. These powers include the power to delegate all or part of the power to other users, including the transfer power. User permissions are described in special catalog tables, and permission control is supported at the language level.

The functionality of the data model supported by the DBMS becomes accessible to the user thanks to its linguistic means. Language means DBMSs are used to perform two main functions - to describe the presentation of the database at manageable levels of the system architecture and to perform data manipulation operations.

Data description language(DL) is intended to specify a database schema, which includes a description of the database structure and the integrity constraints imposed on it within the framework of the rules regulated by the data model that is supported by the DBMS in question. In addition to these functions, DDLs also provide the ability to set restrictions on data access or user permissions.

Data manipulation language(YMD) allows you to query the operations provided in the system on data from the database. After executing the statement written on the NMD, the information content of the database changes

Query language(YAZ) allows you to select data from the database, aggregate it and subject it to all kinds of analytical processing.

Similar to the data definition language, DML does not necessarily appear in the form of a syntactically independent DBMS language. In practice, the separation of nuclear and nuclear weapons plays a rather methodological role or is used for technological purposes.

LDL, YMD and LL are not always syntactically formalized as independent languages. On the contrary, they are now all part of a single relational language, SQL. A number of versions have been adopted since 1986 international standard SQL. It is used in most commercial relational DBMSs, including those on personal computers.

Some DBMSs have languages ​​that not only implement the functions of data definition and data manipulation, but also have features characteristic of universal languages programming. Thanks to this, they can be used as a functionally complete tool for creating database system applications. As an example, we give the languages ​​of the dBase, Clipper, and Paradox systems.

In order to have advanced application development tools, the DBMS provides application programming interfaces. Applications for such systems can be developed by extending the traditional programming language with operators (commands, functions, procedures, etc.) of the specified interface. Thanks to this, the functional incompleteness of the languages ​​of this system will be filled. The programming language acts as an inclusive language in relation to the DBMS application programming interface language, and application systems are implemented in such an extended language. Application programming interfaces are provided in many DBMSs.

Security questions on the topic.

1. In what forms can the language tools of the data model be implemented in a DBMS?

2.What are the two main functions of data model language tools?

3.What are the purposes of data definition languages ​​in a DBMS?

4.What is a database schema, what relationship exists between the schema and the data definition language?

5.What languages ​​are used to define inter-level data mappings in a DBMS?

6.What functions do data manipulation languages ​​perform?

7.Give an example of a language that performs both data definition and data manipulation functions.

8.What is the purpose of DBMS query languages?

9.For what purposes were database programming languages ​​developed?

DBMS language tools are used to perform the functions of describing the presentation of a database (data description language), to perform data manipulation operations (data manipulation language) and to provide data to users about their requests (query language).

DBMS functions

• 1. Data management in external memory;
• 2. management of buffers in external memory:

buffering is to smooth out differences speed work between external and internal memory.

When working with a database, the DBMS can determine the greater or lesser relevance of some data. The most current data can be stored in a buffer at all times. This feature improves data processing efficiency.

3 . transaction management:

transaction - a sequence of operations on data from a database, which is considered by the DBMS as a logical whole. By logical whole we mean that the DBMS records the beginning of a set of operations and its end. This function is required to maintain data integrity and logical consistency, as well as safe management data. The transaction must end either by committing a new state or by restoring the previous state (rollback). There is a possibility parallel execution multiple transactions (but sometimes this is not possible). Serial execution of transactions - execution in accordance with the so-called serial plan. Serial plan - a transaction execution plan in which the effect of a mixture of transactions is equivalent to their sequential execution. Serialization algorithms are based on serial (synchronization) captures. The purpose of such captures is to build a serial plan.

4. logging - keeping some kind of logs:

There are two types of failures:

• - soft, associated with a power failure, emergency stop of the machine, OS failure;
• - hard - characterized by loss of information on external media(HD accident).

In case of any type of failure, it is necessary to provide for information recovery. This involves storing excess additional information. Such information required for recovery is stored in logs. The log is a part of the DBMS that is not accessible to the user and is maintained with special care. The journal is stored either in several copies or on several media. The log is used to record all changes that are made to the data. From this point of view, two versions of the magazine can be considered:

• - magazine local changes- records individual operations associated with changing external memory pages;
• - global change log - records the results of transactions or transaction sets.

When using the log, the WAL (Write Ahead Log) strategy is used. The point of this strategy is to record the changes before the changes themselves.

In case of a hard failure, a copy of the log and an archive copy of the database are needed, and the archive copy and the log must be consistent - this is important task DB.

5. support for data languages ​​(Data Language)

There are two tasks of information systems:

• - description of the data structure;
• - manipulation of this data.

Previously, these problems were solved using language. Those. The data description language SDL (Schema Definition Language) and the data manipulation language DML (Data Manipulation Language) were supported. The task of SDL was to provide tools for naming database objects, typing individual elements and descriptions of relationships between data elements. DML supports the description of actions to change those objects that are described in SDL.

In modern DBMSs, the functions of these languages ​​are combined into structured language SQL queries (Structured Queried Language). The SQL compiler converts descriptions and queries into an internal representation of the data. Thanks to this, the central control part of the DBMS works only with internal views, which ensures efficient operation.

SQL contains:

• - means for describing integrity constraints;
• - means for authorizing access to various objects:

the essence is that each object is associated with a set acceptable actions and a range of users with different powers. Powers and actions are described in different tables. The language tool allows you to control this.

By functionality The following categories of languages ​​are distinguished:

• 1. Languages ​​that only have query capabilities. They ensure that the required data is displayed on the screen or printed in in the required format. Currently rarely used.
• 2. Complex languages requests/updates. These are more advanced languages; they allow you to formulate complex queries across multiple related records, and updating data is as easy as querying. Using them, users can create their own files.
• 3. Report generators. They make it possible to select the desired data from files or databases and format it into the required document forms.
• 4. Graphic languages. Usage graphic tools is currently constantly expanding. With their help, you can display data in the form of various graphs and charts, as well as use other visual capabilities. Like report generators, graphic languages allow you to select information from files or databases according to various criteria, as well as perform arithmetic and logical manipulations with data.
• 5. Decision support tools. Languages ​​of this type are designed for creating decision-making systems. These can be “what-if” systems, systems that perform time or trend analysis, etc. It is possible to use both universal and problem-oriented tools.
• 6. Application generators. Provide the ability to describe non-procedurally the required information processing and further automatic generation of programs.
• 7. Machine-oriented specification languages. In fact, they are application generators, their further development. Unlike application generators, specification languages ​​are more universal and allow you to specify applications of different types.
• 8. Very high level languages. In most cases, applications are built using non-procedural languages. However, some languages ​​are procedural (for example, NOMAD), but programming in them is much shorter than, for example, in Cobol.
• 9. Parameterized packages application programs(PPP). This category software has been known for a long time, and “4th generation” refers to those PPPs that allow easy modification of the package itself, allow users to generate their own reports, database queries, etc.
• 10. Application languages. Many 4th generation languages ​​are general-purpose, while others are designed for specific applications. Examples of such languages ​​are languages ​​for financial management, control of the operation of machine tools with program controlled etc.