Lecture №10. Database systems.



Purpose: to provide an overview about Database Management Systems (DBMS) and introduce one of DBMS: SQL

 

Plan:

1. Query tuning and their processing.

2.Fundamentals of SQL. Parallel processing of data and their restoration.

3.Design and development of databases. Technology of programming of ORM. The distributed, parallel and heterogeneous databases.

 

Query tuning and their processing.

Query tuning

The DBMS SQL component which defines how to realize navigation on physical data structures for access to the required data, is called the query optimizer (query optimizer).

The navigation logic (algorithm option) for access to the required data is called way or the access method (access path).

The sequence of the operations performed by the optimizer which provide the selected access paths is called the execution plan (execution plan).

Process, the used query optimizer for determination of an access path, is called query tuning (query optimization).

During process of optimization of requestsways of access for all types of the SQL DML teams are determined. However the SQL SELECT team represents the greatest complexity in the solution of a task of the choice of a way of access. Therefore this process is usually called optimization of a request, but not optimization of ways of access to data. Further, it should be noted that the term "optimization of requests" is not absolutely exact — in the sense that there is no guarantee that in the course of optimization of a request the optimum way of access will be really received.

Thus, optimization of requests can be determined as the amount of all techniques which are applied to increase in efficiency of handling of requests.

 

Fundamentals of SQL. Parallel processing of data and their restoration.

The structured language of requests (Structured Query Language) – the standard of communication with the database which is supported by ANSI. The majority of databases solid adheres to the ANSI-92 standard. Almost each separate database uses some unique set of syntax, though very strongly similar to the ANSI standard. In most cases, this syntax is extension of the basic standard though there are cases when such syntax results in different results for different databases.

In the general terms, "SQL the database" is the general name for the relational database management system (RDMS). For some systems, "database" also concerns to group of tables, data, the configuration information which are essentially separate part from other, similar constructions. In this case, each installation of SQL of the database can consist of several databases. In other systems, they are mentioned as tables.

The table – construction of the database which consists of the columns containing lines of data. Usually tables are created to contain the connected information. Within the same database several tables can be created.

Each column represents attribute or set of objects attributes, for example identification numbers of employees, growth, color of machines, etc. Often concerning a column the term a field with specifying of a name, for example "in the field of Name" is used. The field of a line is the minimum element of the table. Each column in the table has a certain name, data type and the size. Column names shall be unique within the table.

Every line (or record) represents set of attributes of a specific object, for example, the line can contain identification number of the employee, the size of its salary, year of its birth, etc. Lines of tables have no names. To address a specific line, the user needs to specify some attribute (or a set of attributes), it is unique it identifying.

One of the major operations which are executed by dataful operation is selection of the information which is stored in the database. For this purpose the user shall execute a request (query).

Types of requests of data

There are four main types of requests of data in SQL which relate to so-called language of a manipulation data (DataManipulationLanguage or DML):

• SELECT – to choose lines from tables;

INSERT – to add a line to the table;

UPDATE – to change lines in the table;

DELETE – to remove lines in the table;

Each of these requests has different operators and functions which are used to make some dataful actions. The SELECT QUERY has the most large number of options. There are also additional types of the requests used together with SELECT, the JOIN and UNION type. But so far, we will concentrate only on the main requests.

Use of a Select query for selection of the necessary data

To obtain information which is stored in the database the Select query is used. Basic action of this request is restricted to one table though there are constructions providing selection from several tables at the same time. To receive all lines of data for specific columns, the request of such look is used:

SELECT column1, column2 FROM table_name;

Also, it is possible to receive all columns from the table, using the podstanovochny sign "*":

SELECT * FROM table_name;

It can be useful in that case when you are going to choose data with a certain condition of WHERE. The following inquiry will return all columns from all lines where "column1" contains 3 value:

SELECT * FROM table_name WHERE column1=3;

 


Дата добавления: 2018-04-15; просмотров: 2043; Мы поможем в написании вашей работы!

Поделиться с друзьями:






Мы поможем в написании ваших работ!