Selection of subd. · maintaining database languages

Youtube

The choice of a DBMS is one of the most important moments in the development of a database project, since it fundamentally affects the entire process of designing a database and implementing an information system.

In theory, there are dozens of factors to consider when making this choice. But in practice, developers are guided only by their own intuition and several of the most important criteria, which, in particular, include:

the type of data model that this DBMS supports, the adequacy of the data model to the structure of the software in question;

DBMS performance characteristics;

reserve of functionality for further development of the information system;

the degree to which the DBMS is equipped with tools for data administration personnel;

convenience and reliability of the DBMS in operation;

cost of the DBMS and additional software.

DBMS MS Access

Access DBMS is a relational database management system. Data is stored in such a database in the form of tables, the rows (records) of which consist of sets of fields of certain types. Each table can be associated with indexes (keys) that specify the user-required orders on multiple rows. Tables can have fields (columns) of the same type, and this allows you to establish connections between them and perform relational algebra operations. Typical operations on databases are defining, creating and deleting tables, modifying definitions (structures, schemas) of existing tables, searching for data in tables according to certain criteria (executing queries), creating reports on content Database. The DBMS allows you to specify data types and methods for storing them. You can also set criteria (conditions) that the DBMS will subsequently use to ensure the correctness of data entry. In the simplest case, the condition on the value must ensure that an alphabetic character is not accidentally entered into a numeric field. Other conditions may define an area or ranges acceptable values input data.

Microsoft Access provides maximum freedom in specifying the data type (text, numeric data, dates, times, monetary values, pictures, sound, spreadsheets). You can also specify storage formats for the presentation of this data when displayed on screen or printed. To make sure that only correct values are stored in the database, you can set conditions for values of varying degrees of complexity. Because Microsoft Access is a modern Windows application, you can use all the features of DDE (Dynamic Data Exchange) and OLE (Object Linking and Embedding) in your work. DDE allows data exchange between Access and any other supporting DDE application Windows. In Microsoft Access, you can use macros or Access Basic to dynamically exchange data with other applications. OLE is more sophisticated Windows tool, which allows you to link to objects in another application or embed objects in an Access database. These objects can be pictures, charts, spreadsheets, or documents from other OLE-enabled Windows applications.

Microsoft Access uses the powerful SQL (Structured Query Language) to process base table data. Using SQL, you can extract from one or more tables the information necessary to solve a specific problem. Access greatly simplifies the task of data processing. It is not at all necessary to know the SQL language. Whenever processing data from multiple tables, Access uses once-specified relationships between tables.

Microsoft Access also has a simple, yet powerful, graphical query tool called “query by example,” which is used to specify the data needed to solve a problem. Using standard Windows mouse techniques and a few keys on the keyboard to select and move elements on the screen, you can build a fairly complex query in literally seconds. Microsoft Access is designed in such a way that it can be used both as an independent DBMS on a separate workstation, and on a network - in client-server mode. Because Microsoft Access can allow multiple users to access data at the same time, it provides strong security and data integrity features. You can specify in advance which users or user groups can have access to database objects (tables, forms, queries). Microsoft Access automatically protects data from being edited by different users at the same time. Access also recognizes and takes into account the security features of other structures connected to the database.

Almost all existing DBMSs have application development tools that can be used by programmers or qualified users to create procedures to automate data management and processing.

Microsoft Access provides additional application development tools that can work not only with its own data formats, but also with the formats of other most common DBMSs. Perhaps the most strong point Access is its ability to process data spreadsheets, text files, dBASE, Paradox, Btrieve, FoxPro files and any other SQL database that supports the ODBE standard. This means that you can use Access to create a Windows application that can process data coming from network server SQL or SQL databases on the host computer.

Data is always structure and content, syntax and semantics. In context, these are tables, relationships between tables, queries and their results. This is not to say that the dominant idea is an ideal, but it is practical, convenient and allows you to describe any field of application.

If a database is a collection of tables, then a management system is the support of several databases at once and the provision of appropriate functionality for each of them in terms of administration, operation and reading. Over time, DBMSs acquired many very specific functions, which are considered to be a de facto standard, and received their own language for description, operation and selection.

Basic DBMS functionality

They allow you to present sets of data through a system of tables, indicate connections between tables, determine the necessary queries, the form of the desired results, and provide two options for work:

change;
only reading.

Actually, nothing more is required from the DBMS; you need to provide access to the program code for administration or operation purposes (changes or reading). The user does not have direct access to the data, but through certain code he has access to a wide range of functionality implemented by the DBMS.

The format, protocol and general algorithm for using a database are always known, although the existing DBMS classification system indicates a wide variety of concepts and implementation options.

Data Management Systems Concepts

The main concept, which, of course, has been in the lead since its birth and is being improved to this day, is the foundation of the design of database management systems - relational relationships. A database is a set of tables and relationships between them. So it was, so it is, but it will not be so for too long.

Other data models:

hierarchical;
network;
ER model (entity - relationship);
object-oriented;
object-relational, etc.

They have their own niches, but each of them is based on the same relational relationships. In fact, across different concepts of data organized into data systems, only one thing is indisputable and obvious: all data always has meaning.

How to capture meaning in a formal computer model of a database? Judging by the few names of database models, there is no particular problem here, but still “pure relational relationships” find the most practical application: what to call a solved data processing problem, what adjective to attach to the name of its database - it doesn’t matter, what matters is that problem solved.

Classification of data management systems

data model;
distribution;
access methods;
level of versatility.

This is a general classification modern DBMS.

The concept of distribution is important, although from a semantic point of view it does not matter how the database is distributed, what is important is that it has the desired access option.

Methods of accessing data are also important: the site may require information from a database managed by Oracle, but getting/writing here will not be structured at all like when using MySQL.

The level of universality is a relative criterion, but in most cases it should be taken into account. Not every project requires dynamics and support high level access security, storage reliability, etc. Many tasks need to be developed according to the field of application. Selecting a DBMS with limited functionality may lead to unnecessary costs in the future to replace a system that has limited capabilities.

DBMS functionality

Following the established tradition, the classification and functions of a DBMS play a significant role in the development of technical specifications or an IT project that involves large amounts of data. In this case, the term “large” can mean the level of a specific data (image processing) or the number of records (text processing).

The functionality of the problem and the expected solution can set clear requirements. In particular, the choice of DBMS (classification by data):

data presentation (video, audio, text, various combinations);
structuring/formalization (structured, unstructured);
nature/source (hierarchical, relational, network);
format and storage location (local, distributed);
users (one, many).

This side of the issue affects only some of the important points for choosing one DBMS over another. There are many application areas in which classification by any criterion does not matter when choosing a DBMS. For example, choosing a content management system for website development purposes will force the developer to clearly select only one specific database.

Large DBMS and complex connect

Modern information level DBMS (classification by importance and responsibility):

terabytes of information (one large file, many small files);
megabytes (several files describing one database and the data it contains).

But the significance and responsibility here are always great, not only in the first case. There are many critical projects where small amounts of information require critical decisions to be made.

Usually the first criterion determines Oracle as the undisputed leader, the second - MySQL. They have a lot in common, but a lot of fundamental differences. When the task arises of connecting a web resource to a database Oracle data without using its own tools and technologies, many questions arise. Complex connect is no longer uncommon, and is often simply a condition for achieving a solution.

An equally large number of problems with data delivery arise when they are located on a local network to which the connection is accessible through several hardware routers.

In fact, in real practice, all components are important: the architecture of the DBMS, the classification of the DBMS by functionality, connection options and throughput of communication channels.

Access security and data storage

Knowledge of DBMS, classification, database theory in general, practical experience and other conceptual points are undoubtedly important. The reliability of the hardware component today is very high, but the issue of code quality, and especially its semantics, is still relevant to this day.

All DBMSs can provide secure access to a database, but what about the common practice of copying databases for backup purposes?

This vicious idea is typical for databases located either in one file or in many files. In the first case, the loss of one byte or bit will ruin the entire file, and in the second case, incomplete copying of the database description or files containing data will also lead to unpredictable consequences.

It is strange that the DBMS developers are not concerned with these facts, but if they took the necessary steps and closed once and for all the issue of data availability outside the data management system, a dilemma would arise: according to the DBMS, classification would be simplified to the limit:

makes sense to use (safe, reliable, everything is always available);
cannot be used (everything is controlled by the DBMS developer).

You can’t control everything; the more experienced the programmer, the more options he leaves to the customer. To close data to external control and change means to ensure that the solved problem does not have a long life.

The issue of data security and availability lies beyond any solution. It refers to the company’s infrastructure, local network, security perimeter, etc.

The data themselves, databases and their management systems should be as open and accessible as possible, subject to established rules and natural requirements, proven by long-term practice.

Social aspect of the DBMS

Considering different ways classification of DBMS, should Special attention pay attention to the social component in the context of theory and its applicability in practice.

When local networks appeared and databases were located on a server, and DBMSs provided access to many users, everything was extremely simple: file server architecture is very practical, today there is:

file server;
client-server;
built-in database.

Three sides of the same coin. It doesn’t matter where the database itself is located, it doesn’t matter which DBMS is chosen. It is important that the data and the code that uses them should be as mobile and accessible as possible, but located within the perimeter of general security under close protection not only from technological factors (attacks, threats, destructive interference), but also from the behavioral moment in the sense of employees, who develop code or use data.

Relational Relationships: Perspectives

The prevailing ideas about DBMS, their classification, and the accumulated unique potential in theory and practical application are undeniable. DBMS developers and information consumers passed long haul, and every day the dynamics of improvement rapidly accelerated.

The relational concept still occupies a strong position and is not going to yield anything to any other architecture or idea. But is its plot true: a table is a relationship between data, and relationships between tables are also relationships? Why should a table have a header, and if there is no data, then there is no table? Why is the table always rectangular, and the data in it has a strict type and size?

The world of information is characterized by fluid shapes, not just rectangles. Isn’t it time to admit a surprisingly simple idea: there is a table, but whether there is a header in it or not is a matter of a specific case. How many rows there will be in a table is always clear: from zero to the limitations of a particular DBMS, but why can’t this positive be attributed to the number of columns?

If we apply the abstraction that modern object-oriented programming has been pursuing for so long, to relational relationships, we get a very promising next step: a DBMS in which it doesn’t matter whether it’s a table or just a data, and if it’s a table, what kind of it will it be and whether there will be rows there or columns and how they will be interconnected at its level is a matter of application. How everything will be linked across all the data and tables is also a matter of scope, and not the competence of the developer making the DBMS or the code using it.

A wide range of applications for software tools for accumulating and storing information dictates the need to develop DBMSs that differ from each other in functionality and are intended for a wide range of users: from a beginner to a system programmer.

Modern databases can be divided into three categories:

1. Corporate software products - Oracle and MS SQL Server;

2. DBMS designed for working with information arrays in small companies - MS Access and Borland Interbase;

3. DBMS for the Web, implementing the creation of websites with small databases - MySQL and, again, Borland Interbase.

What properties should a DBMS have depending on these categories?

Enterprise DBMS must be reliable, which is ensured backup; safe - have protection from unauthorized access; work with huge volumes of data and have broad functionality.

For small companies, database programs must not only be reliable and functional, but also work without a dedicated server.

DBMS for the Web is inherent high speed data processing, low resource requirements and convenient remote administration.

Today the most popular DBMS are Oracle, MS SQL Server, Borland Interbase, MySQL and MS Access.

know

Database- an organized collection of data intended for long-term storage in external computer memory, regular updating and use.

Database management system (DBMS) is a software tool designed for organizing and maintaining logically interrelated data on computer media, as well as providing access to data.

Database represents information model specific subject area.

Classification databases are possible according to the nature of the information: factual and documentary databases; by data structure: hierarchical, network, relational databases; According to the method of data storage: centralized and distributed databases, according to the method of accessing database data, there are file-server and client-server systems.

Relational databases (RDBs)- the most common type of database that uses a tabular presentation of data.

Basic concepts of data organization in a RDB: table, record, field, field type, main table key.

Work technology with databases has several stages, namely:

Ø construction of mythological database models,

Ø creating a database table structure,

Ø processing of data contained in tables,

Ø and outputting information from the database.

Control questions

1. Define a database.

2. Define a DBMS.

3. How do you understand the structure of a database?

4. What are the main requirements for organizing a DBMS?

5. How are DBMS classified depending on processing technologies data?

6. How are DBMS classified depending on way to access data?

7. What information-logical database models do you know?

8. Define hierarchical, network and relational database models?

9. What options exist for classifying databases?

10.Why is the relational database type the most common?

11. What is a database entry?

12. How to select a DBMS to create an automation system?

13. List the stages of a generalized technology for working with a database.

15. List the capabilities, advantages and disadvantages of MS Access.

16. List modern DBMSs for corporate use.

Modern databases can be divided into three categories:

1. Corporate software products - Oracle and MS SQL Server;

2. DBMS designed for working with information arrays in small companies - MS Access and Borland Interbase;

3. DBMS for the Web, implementing the creation of websites with small databases - MySQL.

Corporate DBMSs must be reliable, which is ensured by backup; safe - have protection from unauthorized access; work with huge volumes of data and have broad functionality.

For small companies, database programs must not only be reliable and functional, but also work without a dedicated server.

DBMS for the Web is characterized by high data processing speed, low requirements for resources and convenient remote administration.

Today the most popular DBMS are Oracle, MS SQL Server-2000, Borland Interbase, MySQL and MS Access-2000.

Oracle DBMS - one of the most powerful modern DBMSs designed for implementing corporate-level databases, which places serious demands on the server.

Oracle can run on most operating systems: Windows-NT, -2000, Linux, UNIX, AIX, Nowell Netware. And this, in turn, allows you to choose the most convenient server platform for corporate tasks. For example, if an organization prefers to use non-Windows operating systems, then it can easily afford it. Some organizations may focus on solutions based on UNIX systems, which are renowned for their reliability and stability as a server platform, while continuing to use the familiar Windows users on client computers.

Using Oracle as a DBMS it allows you to choose a programming language. Traditionally, the language used for this is PL/SQL, but the much more powerful Java programming language can also be used.

No DBMS can be considered good if it does not have powerful and convenient administration tools. Oracle fully satisfies these requirements and has the means to administer not only one server, but also a group of servers located in different parts of the planet.

The main advantages of Oracle include support for very large databases (up to 64 GB), powerful development and administration tools, support for multiprocessing and two language environments, as well as integration with the Web. At the same time, the program imposes serious hardware requirements and a high price.

DBMS MS SQL Server-2000 has become widespread because it offers a wide range of administration services and is easily scalable. This allows it to be used in information systems ah for medium-sized businesses and large computer information systems (CIS).

The MS SQL Server platform is based on the Windows environment. The main advantage of the program is its close integration with Microsoft software products and the ability to export/import data into most common data formats, which allows you to use MS SQL Server as a central data storage.

DBMS Borland Interbase contains everything that is required from a DBMS designed for the needs of small and medium-sized businesses. In addition, starting from version 6.0 the program became free, which is also significant. The program is undemanding in terms of hardware - minimum configuration: Pentium 100, RAM - 32 MB, RAM (volume disk memory), required for storing the database, is 50 MB. Borland Interbase supported Windows platforms and Linux, as well as UNIX, NetBSD, FreeBSD.

Borland Interbase is one of the fastest DBMSs, second only to MySQL.

MySQL DBMS has earned popularity due to its high performance when creating Web applications. It has become widespread as a tool for working with databases on the Internet. The program is completely undemanding to the resources of the server on which it runs, is very fast and, moreover, completely free: source codes and distributions for various platforms available on the Internet site. MySQL is designed primarily for use on the web.

Initially, the program was focused on the Linux operating system, but now there are already versions of the program for operating systems Windows systems, UNIX, NetBSD, FreeBSD, AIX. Recently, the program has been gaining popularity among Macintosh users using the Mac OSX operating system. And this happens despite the fact that MySQL does not boast a very rich set of features.

DBMS MS Access-2000. The purpose of this well-known software product is to solve local office tasks with a limited amount of data and generating reports based on the results of work, while the reports can be presented in a standard format for office applications form.

Most users know MS Access as a component of MS Office designed for working with databases.

MS Access is a software product implemented on the “all in one” principle. It is both a development environment in two programming languages (Visual Basic and a highly truncated dialect of SQL), and a CASE tool, as well as a powerful and visual means of creating reports on the results of work.

Using only MS Access, you can produce full cycle works from design to implementation of the finished program. Only MS Access allows you to create programs consisting of a single file containing both the program text and a relational database of complex structure.

The program allows you to create the necessary elements as in in electronic format, and in print. Among other things, MS Access easily integrates with other Microsoft solutions. The disadvantages of the program include the limited amount of data processed and slowness, since MS Access is one of the slowest DBMSs. This imposes restrictions on the use of the program - it is not recommended to use MS Access for a database that can grow over 100 MB. Therefore, it should be used carefully in projects that are likely to be expanded in the future.

All the DBMSs considered have their advantages and disadvantages, and in order to make a choice, you need to clearly understand for what purposes the program will be used.

If you need to develop an automation system for a large holding company and real-time data processing is required, then Oracle is the best solution. After all, it is he who is capable of processing huge amounts of data and has convenient means administration of remote servers scattered around the world.

In a large organization that already has many disparate information systems implemented on a variety of technologies, there is a need to combine them into unified system automation. Such an association would allow the company's management to analyze the results of the enterprise as a whole. A good choice in this case would be MS SQL Server-2000 and its ability to export and import data into different formats. In addition, the capabilities of this DBMS as a basis for decision support systems should be taken into account.

For small businesses and small organizations, a good choice would be Borland Interbase 6.0, which supports databases up to 1 GB without requiring a dedicated server.

MS Access-2000 is ideal for solving small office tasks or for automation in small companies with up to 20 employees. This tool does not require special development costs, since even a person with little programming knowledge can work with it.

Electronic presentations

©2015-2019 site
All rights belong to their authors. This site does not claim authorship, but provides free use.
Page creation date: 2016-04-02

Send your good work in the knowledge base is simple. Use the form below

Students, graduate students, young scientists who use the knowledge base in their studies and work will be very grateful to you.

Posted on http://www.allbest.ru/

MOSCOW PHYSICAL AND TECHNICAL INSTITUTE

(STATE UNIVERSITY)

Department/specialization

"Computational models of technological processes"

Graduate work/

final qualifying work

SELECTION OF A DBMS FOR BUILDING INFORMATION SYSTEMS

4th year student

Anosov Andrey Alexandrovich

Direction: 511600 - "Applied mathematics and physics"

Specialty: 511656 - "Mathematical and information technologies"

Scientific supervisor: Ph.D. - M.Sc., p. n. With. Obukhov I.A.

Moscow - 2001

annotation

The work is a review of existing approaches to the issue of choosing a Database Management System when building information systems.

The concept of an Information System is introduced, issues of the specifics and organization of such systems are considered, as well as the classification of information application architectures. An overview of file-server, client-server, Intranet applications and data warehouses is given.

The concept of a DBMS is introduced and the need for its use in Information Systems is discussed. The issues of the standard organization of a DBMS and the main approaches to constructing database models are covered. The criteria are considered, as well as the comparison methodology various systems in order to select the optimal software product under the given conditions of the requirements placed on it.

Keywords: DBMS, IS, SQL, OLAP, server, client, trigger, procedure, query, data model.

Content

1. Introduction
2. Information systems
2.2.3 Intranet applications
3. DBMS
3.1 File systems
3.3.1 Basic functions of the DBMS
3.4.1 Hierarchical systems
3.4.2 Network systems
3.4.3 Advantages and disadvantages of early DBMSs
3.5 Relational approach to DBMS
3.5.1 Basic concepts
3.5.2 Fundamental Properties relations
3.5.3 Relational data model
3.5.3.1 General characteristics
3.5.3.2 Entity and reference integrity
3.5.3.3 Basic tools for manipulating relational data
3.5.3.4 Relational algebra
3.5.3.5 Relational calculus
3.6 Future development of the database
3.7 DBMS comparison criteria. Selection methodology
4. Conclusion
5. Glossary of terms
6. List of literature and Internet resources

1. Introduction

Building an information system is indeed a problem that most modern enterprises have to solve, regardless of what type of business they are engaged in. The term "information system" refers to a class of software products that facilitate, or "automate," doing business. A system is called “information” if it provides information support for a business. The corresponding program is called a “system” if it performs more than one function (one common example is information systems that support warehouse management: they track the receipt of goods into the warehouse, the release of goods to the buyer, and also control the availability of the required quantity of each product in the warehouse).

In most cases, when creating your own information system, it is impossible to do without using databases. How is a “database” different from any kind of data store maintained in operating system files? The main difference is that the set of data included in the database is managed by a special system program, usually called a “database management system (DBMS)”, which has knowledge about the relationships between heterogeneous data. For example, in the case of a warehouse system, the DBMS that manages the corresponding database must know that for all units of any product listed in the general warehouse list, the correct number must be entered in the document regulating the receipt of goods into the warehouse. This type of property is called database integrity. When creating a database for an information system, the developer tells the DBMS what kind of integrity constraints the system should support in the database, and then the DBMS takes responsibility, without requiring application program intervention.

The second important feature of a DBMS is the execution of so-called “unscheduled” queries to the database. Let's imagine that when designing an information system designed to automate warehouse management, it was planned to carry out queries about the availability of goods in the warehouse, data modification operations when releasing goods from the warehouse and receiving them, and subsequently needed information about the total volume of supplies from a given supplier. In the absence of a DBMS, the information system would need to be redesigned. However, a DBMS, having sufficient knowledge about the subject area (for example, about the structure and meaning of data in a warehouse information system), can provide a universal query language (for example, SQL language) that allows you to formulate custom request to retrieve information from the relevant database. Such a request can be submitted at any time from the terminal or built into one of the application programs included in the information system.

Finally, another important feature of most modern DBMSs is the provision of the so-called “multi-access mode”. Today developed computer architectures usually fall into one of two categories (or a combination of them): a mainframe with more than one terminal connected to it, or a network of servers and client workstations that allows for the sharing of resources. In any case, each of the potential users may want to use the services of the information system at any time. Accordingly, the information system must be able to parallel (or quasi-parallel - the main thing is that the end user does not experience a sensitive response delay) to perform operations specified by several users simultaneously. Moreover, such “parallel” execution is correct, i.e. the result is the same as if several parallel transactions were executed sequentially. The vast majority of modern developed DBMSs support this feature, relieving information system developers from having to worry about providing multi-access mode.

2. Information systems

2.1 General information about information systems

2.1.1 Specifics of information software systems

Depending on the specific area of application, information systems can vary greatly in their functions, architecture, and implementation. However, we can distinguish at least two properties that are common to all information systems. Firstly, any information system is designed to collect, store and process information. Therefore, the basis of any information system is the environment for storing and accessing data. The environment must provide a level of storage reliability and access efficiency that corresponds to the application of the information system.

Secondly, information systems are focused on the end user, for example, a bank clerk. Such users may be very far from the world of computers. For them, a terminal, personal computer or work station are merely instruments of their own professional activity. Therefore, the information system must have a simple, convenient, easy-to-learn interface, which should provide the end user with all the functions necessary for his work, but at the same time not allow him to perform any unnecessary actions.

2.1.2 Organization of information systems

In corporate information systems there is often a need for distributed storage common base data. For example, it makes sense to keep some information as close as possible to the jobs where it is most often used. With a homogeneous construction of a distributed database (based on the same type of database servers), this problem can usually be solved at the DBMS level (most manufacturers of developed DBMSs support tools for managing distributed databases). If the system is heterogeneous (that is, different servers are used to manage separate parts of the distributed database), then you have to resort to using auxiliary tools integration of heterogeneous databases.

The traditional method of organizing information systems is a two-tier client-server architecture (Figure 2.1).

Fig.2.1 Traditional two-tier client-server architecture

In this case, the entire application part of the information system is executed on the system workstations, and on the server side (s) only access to the database is provided. Each workstation must have a sufficient set of resources to be able to perform application processing of data coming from the user and/or from the database. In order to reduce client resource requirements, and often to increase overall system efficiency, three-tier client-server architectures are increasingly being used (Figure 2.2). In this architecture, in addition to the client part of the system and the database server(s), an intermediate application server is introduced. On the client side, only interface actions are performed, and all information processing logic is supported in the application server.

Fig.2.2 Three-tier client-server architecture with dedicated application server

As a rule, the basis of an information system is a relational database. Despite the obvious attractiveness of object-oriented (ObjectStore, Objectivity, O2, Jasmin, etc.) and object-relational (Illustra, UniSQL) DBMSs, in the coming years we will have to work with well-functioning, developed, supported systems that support the SQL standard 92 (for example, Oracle, Informix, CA-OpenIngres, Sybase, DB2). Simply because time must pass for these systems to become established, acquire the necessary reliability, begin to rely on any standards, etc. .

2.2 General classification of information application architectures

All approaches to organizing information systems are based on a common client-server architecture. The only difference is what the clients and servers do. However, a narrower division into “file-server”, “client-server” itself, and “Intranet” is widely used.

It should be noted that, like any classification, the classification of information system architectures discussed below is not absolutely rigid. In the architecture of any given information system, the influences of several general architectural decisions can often be found.

information system base management

2.2.1 File server applications

The organization of information systems based on the use of dedicated file servers is still the most common due to the presence of a large number of personal computers of different levels of development and the comparative cheapness of connecting PCs to local networks. This organization is attractive because, when relying on file-server architectures, the autonomy of application (and most of the system) software running on each computer in the network is preserved. In fact, information system components running on different computers interact only due to the presence of a common file storage, which is located on a file server. In the classic case, not only application programs are duplicated on each computer, but also database management tools. The file server is a disk memory extension shared by all computers of the complex (Figure 2.3).

Fig.2.3 Classic performance information system in the "file-server" architecture

The main advantage of the file-server architecture is its simplicity of organization; in addition, there are convenient and developed tools for developing a graphical user interface, easy-to-use tools for developing database systems and/or DBMS. An example of a system that meets these conditions, but is based on a file-server architecture, is the formerly popular “database server” Informix SE.

In a file-server organization, the client works with deleted files, which causes significant traffic overload (since the DBMS-FS operates on the client side, in order to retrieve useful data, in the general case, it is necessary to view the entire corresponding file on the client side).

In general, in a file-server architecture we have a "thick" client and a very "thin" server in the sense that almost all the work is done on the client side, and the server only requires sufficient disk storage capacity (Figure 2.4).

Fig.2.4 "Thick" client and "thin" server in file-server architecture

So we see that simple, working with small volumes information and designed for use in single-user mode, a file server application can be designed, developed and debugged very quickly. Very often, in a small company, to maintain, for example, personnel records, it is enough to have an isolated system running on a separate computer. However, in more complex cases (for example, when organizing an information system to support a project carried out by a group), file-server architectures become insufficient.

2.2.2 Client-server applications

By client-server application we will understand an information system that operates according to the following scheme (Fig. 2.5):

· On the client side, the application code is executed, which necessarily includes components that support the end-user interface, produce reports, and perform other application-specific functions.

· The client part of the application interacts with the client part of the database management software, which, in fact, is the individual representative of the DBMS for the application.

Fig.2.5 General overview information system in the client-server architecture

Note that the interface between the client part of the application and the client part of the database server is usually based on the use of the SQL language. Therefore, functions such as, for example, pre-processing of forms intended for queries to the database, or the generation of resulting reports are performed in the application code, and all calls to the database server are reduced to transmitting the text of SQL statements.

Since all work with the database (selecting, adding, executing triggers and procedures) occurs on the server side, in a client-server organization clients can be quite “thin”, and the server must be “thick” so as to be able to satisfy the needs of everyone clients (Figure 2.6).

Fig. 2.6 "Thin" client and "thick" server in client-server architecture

However, developers and users of information systems based on a client-server architecture are often dissatisfied with the constant network overhead that results from the need to contact the client with each request. In practice, a common situation is when for the effective operation of a separate client component of an information system, only a small part of the overall database is actually required. This leads to the idea of maintaining a local cache (part of the shared database) on each client side.

In fact, the concept of local database caching is a special case of the concept of replicated (or, as they are sometimes called, replicated) databases. As in general, to support a local database cache, workstation software must contain a database management component - a simplified version of the database server, which, for example, may not provide multi-user access. A separate issue is ensuring consistency between the caches and the shared database. Possible here various solutions- from automatically maintaining consistency through basic database management software to completely shifting this task to the application level. In any case, the clients become thicker, although the server does not become thinner (Figure 2.7).

Fig. 2.7 "Fat" client and "fat" server in a client-server architecture with support for local cache on the client side

At first glance, the client-server architecture seems much more expensive than the file-server architecture. More powerful hardware is required (at least for the server) and significantly more advanced database management tools. However, this is only partially true. The enormous advantage of the client-server architecture is its scalability and, in general, ability to develop, since increasing the scale of the information system does not give rise to fundamental problems. Even when replacing server hardware, the application part of the information system is practically not affected.

2.2.3 Intranet applications

The emergence and introduction into widespread practice of high-level services of the World Health Organization Internet networks(e-mail, http, ftp, telnet, WWW, etc.) naturally influenced the technology for creating corporate information systems, giving rise to a direction now known as Intranet. In fact, informationalIntranet-system is a corporate system that uses Internet methods and tools.

Although in general an Intranet system can use all possible Internet services, the hypertext service WWW (World Wide Web) attracts the most attention. Apparently, there are two main reasons for this. First, using language hypertext markup HTML documents it is relatively easy to develop an easy-to-use information structure, which will subsequently be served by one of the ready-made Web servers. Secondly, the presence of several ready-to-use client parts - browsers - eliminates the need to create your own interfaces with users, providing them with convenient and developed mechanisms for accessing information. In some cases, such an organization of a corporate information system (Figure 2.8) turns out to be sufficient to meet the needs of the company.

However, with all its advantages (simplicity of organization, ease of use, standard interfaces, etc.), this scheme has strong limitations. First of all, as can be seen from Figure 2.8, the information system lacks applied data processing. All the user can do is view the information supported by the Web server. Further, hypertext structures are difficult to modify. In order to change the content of the Web server, you need to pause the system, make changes to the HTML code and only then continue normal functioning. Finally, searching for information in the style of browsing hypertext is not always sufficient. Databases and related data retrieval tools are still often needed.

Fig. 2.8 Simple organization of an Intranet system using WWW tools

A similar technique is widely used to provide unified access to databases in Intranet systems. The HTML language allows you to insert forms into hypertext documents. When the browser encounters a form, it prompts the user to fill it out and then sends a message to the server containing the entered parameters. Typically, some external server procedure is assigned to the form. When receiving a message from a client, the server calls this external procedure passing user parameters. It is clear that such an external procedure can, in particular, play the role of a gateway between the Web server and the database server. In this case, the parameters must specify the user's request to the database. The result is a configuration of the information system, schematically depicted in Figure 2.9.

Fig.2.9 Access to the database in the Intranet system

The principles of using external procedures also underlie the possibility of modifying documents supported by a Web server and creating temporary “virtual” HTML pages.

Even a basic introduction to Intranets would be incomplete without mentioning the capabilities of the Java language. Java is an interpreted object-oriented programming language based on the C++ language. Mobile codes (applets) obtained as a result of compiling a Java program can be linked to an HTML document. In this case, they arrive on the client side along with the document and are executed either automatically or by explicit instructions. An applet may, in particular, be specialized as a gateway to a database server (or some other server). When using a similar technique for accessing databases, the organization diagram of the Intranet system becomes like in Figure 2.10.

Fig.2.10. Access to the database on the client side of the Intranet system

Brief conclusions: Intranet is convenient and a powerful tool development and use of information systems. As noted above, the only relative disadvantage of the approach can be considered the constant change in mechanisms and the natural lack of standards. On the other hand, if the information system is created using the current level of technology and turns out to satisfy the needs of the corporation, then no one will be obliged to change anything in the system due to the emergence of more advanced mechanisms.

2.2.4 Data Warehousing and operational analytical data processing systems

So far, we have considered methods and possible architectures of information systems designed for operational processing data, i.e. to obtain current information to help solve day-to-day problems of the corporation. However, the analytical departments of the corporation and the senior management team have other tasks: having analyzed the behavior of the corporation in the market, taking into account the associated external factors and, having predicted at least the near future, develop tactics and, possibly, a strategy for the corporation. It is clear that solving such problems requires data and application programs that are different from those used in operational information systems. In the last few years, an approach based on the concepts of a data warehouse and a system for operational analytical data processing has become increasingly popular.

Let's consider the main features and differences of operational and analytical information applications from the point of view of providing the required data. We are talking about the so-called OLAP systems (from On-Line Analytical Processing), i.e. analytical systems that help make business decisions through analysis, modeling and/or forecasting of data.

1. The main source of information entering the operational database is the activities of the corporation. To carry out data analysis, the involvement of external sources of information (for example, statistical reports) is required. Thus, the data warehouse should include both internal corporate data and external data characterizing the market as a whole.

2. If, as a rule, fresh data is required for operational processing (as a rule, information is stored in operational databases for no more than several months), then the data warehouse must maintain the storage of information about the activities of the corporation and the state of the market for several years (for reliable analysis and forecasting). As a result, analytical databases have a volume that is at least an order of magnitude larger than operational ones.

3. Many fairly large corporations simultaneously have several operational information systems with their own databases. Operational databases may contain semantically equivalent information presented in different formats, with different indications of the time of its receipt, sometimes even contradictory (for example, due to data entry errors). A corporation's data warehouse must contain uniformly presented data from all operational databases. This information should correspond as closely as possible to the current content of operational databases and be consistent. This necessitates the need for a data warehouse component that retrieves information from operational databases and “cleanses” that information.

4. Operational information systems are designed and developed to solve specific problems. Typically, a set of queries to an operational database becomes known already at the system design stage. Information from the database is selected frequently and in small portions. Therefore, when designing an operational database, it is possible and necessary to take into account this previously known set of queries (with certain reservations in connection with possible alterations of the information system). The set of queries to an analytical database cannot be predicted. Data warehouses exist to respond to unexpected requests from analysts. You can only count on the fact that requests will not come too often and will involve large amounts of information. The size of the analytical database encourages the use of queries with aggregates (sum, minimum, maximum, average, etc.).

Fig.2.11. Schematic representation of the architecture of an analytical information system

5. Operational databases are by nature highly volatile. Analytical databases change only when operational or external information is loaded into them. As a result, it turns out to be reasonable to use other indexing methods that are faster when performing mass sampling operations, maintain the orderliness of information arrays, save pre-calculated values of aggregate functions, etc.

6. For operational information systems, information protection at the table level is usually sufficient, while the information of analytical databases is so critical for the corporation that more subtle techniques are required to protect it (for example, when using relational databases, setting individual access privileges for individual rows and/or columns of the table).

Taking into account the above comments, the overall architecture of a data warehouse and analytical data processing system may look like shown in Figure 2.11.

Generally speaking, the data warehouse approach is still too young for a circle of generally accepted concepts, terms, and technological techniques to develop around it. However, it seems so important and promising that many companies (including leading DBMS manufacturers) are active work to be at the forefront of this trend.

3. DBMS

From the very beginning of the development of computer technology, two main directions of its use emerged. The first direction is the use of computer technology to perform numerical calculations that take too long or are impossible to perform manually. The emergence of this direction contributed to the intensification of methods for numerically solving complex mathematical problems, the development of a class of programming languages focused on convenient recording of numerical algorithms, and the establishment of feedback from developers of new computer architectures.

The second direction is the use of computer technology in automatic or automated information systems. Typically, the volumes of information that such systems have to deal with are quite large, and the information itself has a rather complex structure. One of the natural requirements for such systems is the average speed of operations and the safety of information.

3.1 File systems

File systems were the first attempt to computerize the familiar manual file cabinets. A similar file cabinet (or file of documents) in some organization could contain all the external and internal documentation associated with a project, product, task, client or employee. Since there are usually a lot of such folders, to search for any information, we need to look through the entire file cabinet from beginning to end. A more sophisticated approach involves using some kind of indexing algorithm in such a system to speed up the search for the necessary information. For example, you can use dividers or separate folders for different logically related types of objects.

Manual filing cabinets allow you to successfully cope with the assigned tasks if the number of stored objects that only need to be stored and retrieved is small. However, they are completely unsuitable for cases where cross-linking or information processing needs to be done. File systems were developed in response to the need for more efficient ways to access data. However, instead of organizing a centralized repository for all enterprise data, a decentralized approach was used, in which employees in each department work with their own data and store it in their department.

It is quite obvious that a large amount of data is duplicated in departments, which is quite typical for any file systems. This is accompanied by a waste of resources, since time and money must be spent on entering redundant data. Moreover, for their storage it is necessary extra bed in external memory, which is associated with increased overhead. Although in many cases duplication can be avoided by sharing files, this approach is not always implemented due to the impossibility of accessing them simultaneously. Even more important is the fact that duplication of data can lead to a violation of its integrity. In other words, data in different departments may become contradictory. For example, an employee receives a promotion with a corresponding increase in salary. If this change is recorded only in the information of the HR department, without being carried out in the files of the settlement sector, then this employee will erroneously receive the previous salary. And even if the settlement sector employees make the necessary changes on time, there is still a possibility that they will be entered incorrectly.

In addition, the physical structure and method of storing data file records are hardwired into the application program code. This means that changing the existing data structure is quite difficult. For example, increasing the length of a field in a file by one character seems like a completely insignificant change in its structure, but to implement this change you will need, at a minimum, to create a program to convert the file to a new format. In addition, all programs accessing this file must be modified to conform to the new file structure. Moreover, there can be a lot of such programs. Therefore, the programmer must first identify them all, and then check and make the necessary changes. This feature of file systems is called program and data dependence.

In short, file systems usually provide storage of loosely structured information (for example, text data: documents, program texts, etc.), leaving further structuring to application programs. In some cases, this is even good, since when developing any new application system, relying on simple, standard and relatively cheap file system tools, it is possible to implement those storage structures that most naturally correspond to the specifics of a given application area.

3.2 Information systems needs

However, the situation is fundamentally different for the information systems mentioned above. These systems are mainly focused on storing, retrieving and modifying permanently existing information. The structure of information is often very complex, and although data structures are different in different information systems, there are often many similarities between them. At the initial stage of using computer technology for information management, problems of data structuring were solved individually in each information system. The necessary add-ons were made to file systems (program libraries), similar to how this is done in compilers, editors, etc.

But because information systems require complex data structures, these individual additional data controls were an essential part of information systems and were practically repeated from one system to another. The desire to identify and generalize the general part of information systems responsible for managing complexly structured data was, apparently, the first motivating reason for the creation of a DBMS. Very soon it became clear that it was impossible to get by with a common library of programs that implemented more than a standard base file system. complex methods data storage, for example, storing information in several files.

In fact, if an information system supports the consistent storage of information in multiple files, it can be said to support a database. If some assistance system data management allows you to work with multiple files, ensuring their consistency; you can call it a database management system. The mere requirement of maintaining data consistency across multiple files does not allow for a library of functions: such a system must have some of its own data (metadata) and even knowledge that determines the integrity of the data.

But this is not all that is usually required from a DBMS. As mentioned above, file systems have a strict and very limited set of requests, “hardwired” into the control program. Modern DBMSs are capable of implementing arbitrarily formulated queries in a language that is familiar to the user. Such languages are called database query languages. Currently, the most common query language is SQL.

Next, imagine that our initial implementation of an information system based on the use of advanced file access libraries handles the operation of adding information to several files at once. Following the requirements of coordinated file changes, the information system inserted a new record into the first file and was about to modify the record of another, but at that very moment a power failure occurred. Obviously, after restarting the system, its database will be in an inconsistent state. You will need to figure this out and bring the information into a consistent state. Real DBMSs take on this kind of work. The application system is not required to care about the correctness of the database state.

Finally, imagine that we want to provide parallel (for example, multi-terminal) work with the employee database. If you rely only on the use of files, then to ensure correctness for the entire time of modification of any of the two files, access to this file by other users will be blocked. Real DBMSs provide much finer synchronization of parallel data access.

Thus, DBMSs solve many problems that are difficult or even impossible to solve when using file systems. However, there are applications for which files are sufficient; applications that need to decide what level of data manipulation in external memory they require; and applications that, of course, require databases.

3.3 DBMS functions. Typical DBMS organization

As was shown earlier, the traditional capabilities of file systems are not enough to build even simple information systems. We identified several needs that are not covered by file management systems: maintaining a logically consistent set of files; providing a data manipulation language; recovery of information after various types of failures; real parallel work of several users. We can assume that if an application information system relies on some data management system that has these properties, then this data management system is database management system (DBMS).

3.3.1 Basic functions of the DBMS

More precisely, the DBMS functions usually include the following:

1. Direct data management in external memory. This function includes providing the necessary external memory structures both for storing data directly included in the database and for service purposes, for example, to reduce data access time in some cases (usually indexes are used for this). Some DBMS implementations actively use the capabilities of existing file systems, while others work down to the level of external memory devices. But we emphasize that in developed DBMSs, users in any case are not required to know whether the DBMS uses a file system, and if it does, how the files are organized. In particular, the DBMS supports its own naming system for database objects.

2. Managing RAM buffers. DBMSs usually work with databases of significant size; at least this size is usually significantly larger than the available amount of RAM. It is clear that if, when accessing any data element, an exchange is made with external memory, then the entire system will operate at the speed of the external memory device. Almost the only way to really increase this speed is to buffer the data in RAM. At the same time, even if operating system produces system-wide buffering (as in the case of UNIX OS), this is not enough for the purposes of the DBMS, which has much more more information about the usefulness of buffering one or another part of the database. Therefore, developed DBMSs support their own set of RAM buffers with their own buffer replacement discipline.

3 . Transaction management A transaction is a sequence of operations on a database, considered by the DBMS as a single whole. Either the transaction executes successfully and the DBMS records the database changes made by this transaction in external memory, or none of these changes have any effect on the state of the database. The concept of a transaction is necessary to maintain the logical integrity of the database. For example, in the case of a human resources information system, when a new employee is hired, new information is required both in the employee records file and in the file of the department to which the employee was hired. The only way not to violate the integrity of the database in this case, when performing the operation of hiring a new employee, it is to combine elementary operations on the EMPLOYEES and DEPARTMENTS files into one transaction.

4. Logging. One of the main requirements for a DBMS is the reliability of data storage in external memory. Storage reliability means that the DBMS must be able to restore the last consistent state of the database after any hardware or software failure. Typically, two possible types of hardware failures are considered: so-called soft failures, which can be interpreted as a sudden stop of the computer (for example, an emergency power off), and hard failures, characterized by the loss of information on external memory media. Examples of software failures can be: a DBMS crash (due to an error in the program or as a result of some hardware failure) or a user program crash, as a result of which some transaction remains incomplete. The first situation can be thought of as a special type of soft hardware failure; when the latter occurs, it is necessary to eliminate the consequences of only one transaction.

It is clear that in any case, to restore the database you need to have some additional information. In other words, maintaining the reliability of data storage in a database requires data storage redundancy, and the part of the data that is used for recovery must be stored especially reliably. The most common method of maintaining such redundant information is to maintain a database change log.

A journal is a special part of the database, inaccessible to DBMS users and maintained with special care, which receives records of all changes to the main part of the database. In different DBMSs, database changes are logged at different levels: sometimes a log entry corresponds to some logical operation of changing the database (for example, an operation to delete a row from a relational database table), sometimes to a minimal internal operation of modifying an external memory page; some systems use both approaches simultaneously.

In all cases, the strategy of “proactive” logging is followed (the so-called Write Ahead Log - WAL protocol). Roughly speaking, this strategy is that a record of a change in any database object must enter the external memory of the log before the changed object enters the external memory of the main part of the database. It is known that if the WAL protocol is correctly observed in the DBMS, then using the log you can solve all the problems of restoring the database after any failure.

The most simple situation recovery - individual transaction rollback. Strictly speaking, this does not require a system-wide database change log. It is enough for each transaction to maintain a local log of database modification operations performed in this transaction, and to roll back the transaction by performing reverse operations, following from the end of the local log.

5. Support for database languages. To work with databases, special languages are used, generally 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 define the logical structure of the database, 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.

Modern DBMSs usually support a single integrated language that contains all the necessary tools for working with the database, starting from its creation, and providing basic user interface with databases. The standard language of the currently most common relational DBMS is SQL (Structured Query Language). Let us list the main functions of a relational DBMS supported at the “language” level, i.e. functions supported by the implementation of the SQL interface (if the reader is not familiar with the basics of the relational data model, you should first familiarize yourself with it in Chapter 3.5 before considering the basics of the SQL language).

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. The internal part of the DBMS (kernel) does not work with the names of tables and their columns at all.

SQL language contains special tools for determining 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.

Finally, authorization of access to database objects is also performed on the basis of a special set of SQL statements. The idea is that in order to execute certain SQL statements, the user must have various permissions, or, in other words, rights. The user who created the database table has full set rights to work with this table. These powers include the right to transfer all or part of the powers to other users, including the right to delegate powers. User rights are described in special catalog tables, and permission control is supported at the language level.

3.3.2 Typical organization of a modern DBMS

Naturally, the organization of a typical DBMS and the composition of its components corresponds to the set of functions we have considered. Let us recall that we have identified the following main functions of the DBMS:

· data management in external memory;

· management of RAM buffers;

· transaction management;

· logging and database recovery after failures;

· maintaining database languages.

Logically, in a modern relational DBMS, one can distinguish the most internal part - the DBMS core (often called the Data Base Engine), the database language compiler, the runtime support subsystem, and a set of utilities. In some systems these parts are clearly distinguished, in others they are not, but logically such a division can be carried out in all DBMSs.

The DBMS kernel is responsible for managing data in external memory, managing RAM buffers, managing transactions, and logging. Accordingly, the kernel components can be identified (at least logically, although some systems explicitly identify these components) as a data manager, a buffer manager, a transaction manager, and a log manager. The functions of these components are interrelated, and to ensure correct operation of the DBMS, all these components must interact using carefully thought out and proven protocols. The DBMS kernel has its own interface, not accessible to users directly and used in programs produced by the SQL compiler (or in the execution support subsystem for such programs) and database utilities. The DBMS kernel is the main resident part of the DBMS. When using a client-server architecture, the kernel is the main component of the server part of the system.

The main function of a database language compiler is to compile database language statements into some executable program. The main problem with relational DBMSs is that the languages of these systems (usually SQL) are non-procedural, i.e. in the operator of such a language, some action on the database is specified, but this specification is not a procedure, but only describes in some form the conditions for performing the desired action. Therefore, the compiler must decide how to execute a language statement before running a program to execute it. Quite sophisticated operator optimization techniques are used, and the result of compilation is an executable program, represented in some systems in machine code, but more often in executable internal machine-independent code. In the latter case actual execution operator is performed using the runtime support subsystem, which is, in fact, an interpreter of this internal language.

Finally, separate database utilities usually separate out procedures that are too expensive to perform using the database language, for example, loading and unloading a database, collecting statistics, checking global database integrity, etc. Utilities are programmed using the DBMS kernel interface, and sometimes even penetrating into the kernel.

3.4 Early approaches to database organization

Almost all database products created since the late 70s are based on an approach called relational; Moreover, the vast majority of database research over the past 25 years has been conducted (perhaps indirectly) in this direction. In fact, the relational approach is a major trend in today's market, and relational model- the single most significant development in the history of database development.

However, before moving on to consideration of relational database systems, let us briefly dwell on early (pre-relational) DBMSs. This makes sense for three reasons: first, these systems historically preceded relational ones, and for correct understanding reasons for the widespread transition to relational systems, you need to know at least something about their predecessors. Secondly, the internal organization of relational systems is largely based on the use of methods from earlier systems. Third, some knowledge of early systems will be helpful in understanding how post-relational DBMSs have evolved.

Note that here we limit ourselves to considering only general approaches to the organization of two types of early systems, namely, hierarchical and network systems database management. We won't go into the specifics of any specific implementation; this would lead to the presentation of many technical details which, although interesting, are somewhat aside from the main purpose of this work.

Let's start with some of the most general characteristics early systems:

a. These systems have been in active use for many years, longer than any relational database management system has been in use. In fact, some of the early systems are in use even today, huge databases have been accumulated, and one of the pressing problems of information systems is their use in conjunction with modern systems.