Lecture №5. Database systems.

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

Plan:

1.Bases of database systems: concept, characteristic, architecture. Data models. Normalization. Integrity constraint on data. 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.

1.Bases of database systems: concept, characteristic, architecture. Data models. Normalization. Integrity constraint on data. Query tuning and their processing.

Introduction to Databases.A database is a structured collection of records or data. A computer database is a kind of software to organize the storage of data. Databases help you organize this related information in a logical fashion for easy access and retrieval. To develop a database, there are several models used such as Hierarchical model, Network model, Relational model, Object-Oriented model etc.

Hierarchical model.In a hierarchical model, data is organized into an inverted tree-like structure. This structure arranges the various data elements in a hierarchy and helps to establish logical relationships among data elements of multiple files. Each unit in the model is a record which is also known as a node. Each record has a single parent.

Figure 1- Hierarchical Model

Network model.The network model tends to store records with links to other records. Each record in the database can have multiple parents, i.e., the relationships among data elements can have a many to many relationships. So this model is an expansion to the hierarchical structure, allowing many-to-many relationships in a tree-like structure that allows multiple parents.

The network model provides greater advantage than the hierarchical model in that it promotes greater flexibility and data accessibility.

Figure 2- Network Model

Relational model.The relational model for the database management is a database model based on relations. The basic data structure of the relational model is a table where information about a particular entity (say, a student) is represented in columns and rows. The columns enumerate the various attributes(i.e. characteristics) of anentity(e.g. student name, address, registration _number).The rows (also called records) represent instances of an entity (e.g. specific student).

Object –Oriented model.In this Model we have to discuss the functionality of the object oriented Programming. It takes more than storage of programming language objects. It provides full-featured database programming capability, while containing native language compatibility. It adds the database functionality to object programming languages. This approach is the analogical of the application and database development into a constant data model and language environment. Applications require less code, use more natural data modeling, and code bases are easier to maintain. Object developers can write complete database applications with a decent amount of additional effort. But object-oriented databases are more expensive to develop.

Database Management System.A Database Management System (DBMS) is computer software designed for the purpose of managing databases based on a variety of data models. A DBMS is a complex set of software programs that controls the organization, storage, management, and retrieval of data in a database. DBMS are categorized according to their data structures or types, sometime DBMS is also known as a Database Manager. Data management tasks fall into one of four general categories as given below:

Entering data into the database.

Housekeeping tasks such as updating data, deleting obsolete records, and backing up the database.

Sorting the data: arranging or re-arranging the database‟s records.

Obtaining subsets of data.

There are several advantages in DBMS such as reduced data redundancy and inconsistency, enhanced data integrity, improved security etc.

Normalization of databases is a process of transformation of database to the kind answering the normalized forms.

Classification of limitations of integrity

In the theory of relational databases it is accepted to distinguish four types of limitations of integrity:

· Limitation of database is name a limit on values that it is let to accept to the indicated database.

· Limiting to the variable of relation is name a limit on values that it is let to accept to the indicated variable of relation.

· Limitation of attribute is name a limit on values that it is let to accept to the indicated attribute.

· Limitation of type is not that another, as determination of great number of values this type consists of that.

Example of widespread restriction of level of a variable of the relation is the potential key; an example of widespread restriction of level of the database is the foreign key.

Integrity and truth of data in a DB

Integrity of a DB doesn't guarantee reliability (truth) of the information which is contained in it, but provides at least plausibility of this information, rejecting certainly improbable, impossible values. Thus, it isn't necessary to confuse integrity (consistency) of a DB to truth of a DB. Truth and consistency — not same.

Reliability (or truth) is compliance of the facts which are stored in the database, to the real world. It is obvious that determination of reliability of a DB requires possession of full knowledges both about DB contents, and about the real world. Determination of integrity of a DB requires only possession of knowledge of contents of a DB and about the rules set for it. Therefore the DBMS can't guarantee existence in the database only of the true expressions; all that she can make, are to guarantee absence of any data causing violation of integrity constraints (that is to guarantee that she doesn't contain any data not compatible to these restrictions).

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.

2.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:

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

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:

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

Design of databases — process of creation of the database scheme and determination of necessary integrity constraints.

Main objectives of design of databases:

• Support of storage in a DB of all necessary information.

• A data acquisition possibility on all necessary requests.

• Abbreviation of redundance and duplicating of data.

• Support of integrity of the database.

Main design stages of databases

Conceptual design

Conceptual design — creation of a semantic domain model, that is information model of the highest level of abstraction. Such model is created without orientation to any specific DBMS and a data model. The terms "semantic model", "conceptual model" are synonyms.

The specific type and the maintenance of conceptual model of the database is defined by the formal device selected for this purpose. Graphic notations, similar to ER charts are usually used.

Most often the conceptual model of the database includes:

• description of information objects or concepts of data domain and communications in between.

• description of integrity constraints, that is requirements to admissible values of data and to communications in between.

Logic design

Logic design — creation of the database scheme on the basis of a specific data model, for example, a relational data model. For a relational data model data logical model — a set of diagrams of the relations, it is normal with specifying of primary keys, and also the "communications" between the relations representing foreign keys.

Conversion of conceptual model to a logical model is, as a rule, carried out by the formal rules. This stage can be substantially automated.

At a stage of logic design specifics of a specific data model are considered, but specifics of specific DBMS can not be considered.

Physical design

Physical design — creation of the database scheme for specific DBMS. Specifics of specific DBMS can include restrictions for naming of database objects, restrictions for the supported data types, etc. Besides, specifics of specific DBMS in case of physical design include a choice of the decisions connected to a physical medium of data storage (a choice of methods of management of disk memory, division of a DB according to files and devices, data access methods), creation of indexes etc.

What is ORM?

ORM or Object-relational mappingis a technology of programming which allows to transform incompatible types of models to OOP, in particular, between the data store and subjects to programming. ORM is used for simplification of process of saving objects in a relational database and their extraction, in case of this ORM itself cares for data transformation between two incompatible statuses. The majority of ORM tools considerably rely on meta data of the database and objects so objects need to know nothing about a database structure, and the database — nothing about how data are organized in the application. ORM provides complete division of tasks in well programmed applications in case of which both the database, and the application can work dataful everyone in the root form.

Fugure3- ORM`s work