Databases and subd. Concept and components of information support for control systems

Let's forget about it for a couple of minutes modern technologies and remember how data was processed and stored twenty years ago. In those days, the most popular types of computers were mainframes of the IBM-360/370 family (many of you have probably still seen their domestic analogues of the EU series, produced by the CMEA countries) and minicomputers such as DEC PDP-11 (which also had a domestic analogue - SM-4/SM-1420). As a rule, when working with such computers, non-intelligent terminals were used, controlled by the same mainframe or minicomputer (we probably won’t remember such exotic devices as punched card and punched tape readers as a means of providing a user interface).

It must be said that data processing using mainframes and minicomputers had its advantages, which were, to a certain extent, lost later, in the era of personal computers and desktop DBMSs. These included, in particular:

• the ability to share resources and equipment, for example central processor, random access memory, external devices(printers, plotters, magnetic tape drives and other data storage devices, etc.);
• centralized data storage.

Serious disadvantage similar systems there was virtually no personalization working environment- all software, including text editors, compilers, DBMS, was also stored centrally and used collectively.

This drawback was one of the reasons for the rapid growth of the personal computer industry - along with ease of operation and low cost compared to mainframes and minicomputers, users were attracted by the possibilities of personalizing the working environment, especially the ability to select the most suitable software for a given user. It was during that period that the rapid growth in popularity of desktop DBMSs began, such as dBase (REBUS) and, a little later, FoxBASE, Paradox, as well as some others, now safely forgotten. It must be said that at that time there were processes of borrowing and standardizing successful ideas and approaches, which was especially noticeably reflected in the fate of such a product as dBase, whose programming language and principles of data organization were borrowed by many other manufacturers in their products. However, we will talk about dBase and products compatible with it a little later.

Desktop DBMSs as such do not contain special applications and services that manage data; interaction with them is carried out using file services of the operating system. Often, such DBMSs also include development tools that are focused on working with data in a format specific to this DBMS and allow you to create a more or less comfortable user interface. As for data processing, it is entirely carried out in the user (client) application.

The next step in the development of desktop DBMSs was the emergence of their networked multi-user versions, allowing several users to process data located in public storage (for example, on a network drive) simultaneously. Their multi-user versions differ from purely desktop DBMSs by the presence of a mechanism for locking parts of data files (containing one or more table records), which allows several users to access the same file simultaneously.

The disadvantages of such DBMSs are not obvious and become noticeable, as a rule, with an increase in stored data volumes and an increase in the number of users. They usually manifest themselves as decreased performance and data processing failures after using client applications for some time. Cause similar problems lies in the basic principle of operation of such DBMSs and those based on them information systems, which consists of processing data within the user application. For example, if such a system needs to run a query according to some criterion (for example, select orders processed in the last two hours from an order table), then, in the best case (if this table is indexed by order arrival time), the application should read the entire index from a network drive, find in it information about the location of records in the files containing the table, and then read these parts of the files. In the general case, when the table is not indexed by this field, it must be loaded from a network drive and analyzed.

Another problem with desktop DBMSs is the possibility of violating referential integrity of data, since the only mechanism that controls it is the user application. Therefore, all user applications must contain the appropriate code and access to database files from any other applications must be prohibited. In the most popular desktop DBMSs (for example, Microsoft Access, Corel Paradox) code that controls standard referential integrity is contained in libraries used by all applications that work with this database, and the database itself may contain a description of referential integrity rules.

The next stage in the development of DBMS for personal computers was the so-called server DBMS. We will dedicate the next article in this series to them, and here we will only briefly explain how server DBMSs differ from desktop ones.

The “client/server” architecture for which server DBMSs are designed is, to a certain extent, a return to the previous “mainframe” model, based on the centralization of data storage and processing on one dedicated computer where the special application or a service called a database server. The database server is responsible for working with database files, maintaining referential integrity, backup, providing authorized access to data, logging operations and, of course, for fulfilling user requests for selecting and modifying data and metadata. The client applications that source these requests run on personal computers on the network.

Without dwelling in detail on the advantages and disadvantages of such an architecture, we only note that when using server DBMSs, queries are executed by the server itself, so client applications receive from the server only the results of the query itself and do not require transfer of the entire index or the entire table, which significantly reduces network traffic when processing requests. Note also that many objects designed to implement business rules, such as stored procedures and triggers, are available only in server DBMSs.

Having considered what types of databases there are, let’s return to desktop DBMSs and talk about the most popular of them.

The most popular desktop DBMSs

Today, more than two dozen desktop DBMS data formats are known, but the most popular, based on the number of copies sold, are dBase, Paradox, FoxPro and Access. Among the recently appeared DBMSs, Microsoft Data Engine should also be noted - essentially a server DBMS, which is a “light” version of Microsoft SQL Server, but intended, nevertheless, for use mainly in desktop systems and small working groups.

Information about the manufacturers of the DBMS listed above is presented in the following table.

DBMS	Manufacturer	URL
Visual dBase	dBase, Inc.	http://www.dbase2000.com
Paradox	Corel	http://www.corel.com
Microsoft Access 2000	Microsoft	http://www.microsoft.com
Microsoft FoxPro	Microsoft	http://www.microsoft.com
Microsoft Visual FoxPro	Microsoft	http://www.microsoft.com
Microsoft Visual FoxPro	Microsoft	http://www.microsoft.com
Microsoft Data Engine	Microsoft	http://www.microsoft.com

Next we will look at each of these DBMSs separately. Let's start with dBase - a DBMS that was once extremely popular and today is still not forgotten, despite the fact that during its existence it has changed several owners and at present its fate is not fully determined.

dBase and Visual dBase

The first industrial version of the dBase DBMS - dBase II (then owned by Ashton-Tate, later acquired by Borland) appeared in the early 80s. Thanks to its ease of use, low demands on computer resources and, no less important, the competent marketing policy of the manufacturer, this product gained considerable popularity, and with the release of its next versions - dBase III and dBase III Plus (1986), equipped with very comfortable at that time, a development environment and data manipulation tools, quickly took a leading position among desktop DBMSs and tools for creating applications using them.

Data storage in dBase is based on the principle of “one table - one file” (these files usually have the extension *.dbf). MEMO fields and BLOB fields (available in later versions of dBase) are stored in separate files (usually with a *.dbt extension). Indexes for tables are also stored in separate files. At the same time, in earlier versions of this DBMS a special reindexing operation was required to bring the indexes into line with the current state of the table.

The dBase data format is open, which has allowed a number of other manufacturers to borrow it to create dBase-like DBMSs that are partially compatible with dBase in data formats. For example, the once very popular FoxBase DBMS (developed by Fox Software, Inc. and now owned by Microsoft) used the dBase data format for tables, but the formats for storing MEMO fields and indexes were their own, incompatible with dBase. Very popular in the early 90s (and still used in some places today), the Clipper development tool from Nantucket Corp (later acquired by Computer Associates) manipulated both dBase III format data (including index files and files for MEMO fields), as well as with index files of its own format.

Besides popular format dBase data is the founder of the once popular family of programming languages, called xBase. All languages ​​in this family, used in FoxBase, Clipper, and some later development tools, such as Computer Associates' now-defunct CA Visual Objects, contain a similar set of commands for data manipulation and are essentially interpreted languages. The role of xBase command interpreter is usually either an application development environment in this language, or a runtime environment that can be supplied with the application. Note that in order to hide the source code of xBase applications, such DBMSs usually contain utilities for pseudo-compiling the code, which is then supplied with the runtime environment. In the case of Clipper, the runtime environment is contained in the executable itself (and Clipper itself is technically considered a compiler), but nevertheless, the language is also essentially interpreted.

Having considerable similarities in syntax and supported set of commands at the time of widespread use of DOS, the languages ​​of the xBase family, however, have many differences, especially in later versions of the “successors” that used their DBMS. As a rule, they all have their own object extensions, and therefore at present there is practically no need to talk about their compatibility with each other.

Note, however, that to work with data in the dBase format (or other dBase-like DBMSs) it is absolutely not necessary to use xBase dialects. Access to this data is possible using the ODBC API (and corresponding drivers) and some other data access mechanisms (for example, Borland Database Engine, some libraries from other manufacturers such as CodeBase from Sequenter), and this allows you to create applications using the dBase data format, almost using any development tool that supports one of these data access mechanisms.

Following the purchase of dBase by Borland, the product, which later became known as Visual dBase, acquired a set of additional features, typical for the development tools of this company and for its other desktop DBMS - Paradox. Among these features were special field types for graphical data, supported indexes, storage of referential integrity rules within the database itself, and the ability to manipulate data in other formats, in particular server-side DBMSs, through the use of the BDE API and SQL Links.

Visual dBase is currently owned by dBase, Inc. Its latest version - Visual dBase 7.5 has the following features:

• Data manipulation tools dBase and FoxPro of all versions.
• Tools for publishing data on the Internet and creating Web clients.
• Data access core Advantage Database Server from Extended Systems and ODBC driver for accessing data from this DBMS.
• Tools for publishing reports on the Web.
• Tools for generating executable files and distributions.

Currently, the dConnections component can be purchased as an add-on to Visual dBase, allowing access to according to Oracle, Sybase, Informix, MS SQL Server, DB2, InterBase from Visual dBase 7.5 and applications created with it.

dBase, Inc also announced the dBASE project Open Source, the goal of which is for the dBase user community to develop new components and classes for inclusion in the next version of dBase (dubbed dBase 2000). In other words, there is a tendency to turn dBase (or parts of it) into a non-commercial product with available source code.

Paradox

Paradox was developed by Ansa Software, and its first version was released in 1985. This product was subsequently acquired by Borland. Since July 1996 it has been owned by Corel and is integral part Corel Office Professional.

In the late 80s and early 90s, Paradox, then owned by Borland International, was very popular DBMS, including in our country, where at one time it occupied a strong position in the market for development tools for desktop applications with databases.

The principle of data storage in Paradox is similar to the principles of data storage in dBase - each table is stored in its own file (extension *.db), MEMO and BLOB fields are stored in a separate file (extension *.md), as are indexes (extension *. px).

However, unlike dBase, the Paradox data format is not open, so special libraries are required to access data in this format. For example, applications written in C or Pascal used the once popular Paradox Engine library, which became the basis of the Borland Database Engine. This library is now used in applications created using Borland development tools (Delphi, C++Builder), in some report generators (for example, Crystal Reports) and in Paradox itself. There are also ODBC drivers for databases created different versions this DBMS.

Note, however, that the lack of “openness” of the data format also has its advantages. Since in this situation, access to data is carried out only with the help of libraries that “know” this format, simple editing of such data compared to data open formats dBase type is significantly difficult. In this case, services that are not available when using “open” data formats are possible, such as protecting tables and individual fields with a password, storing some referential integrity rules in the tables themselves - all these services are provided by Paradox, starting with the first versions of this DBMS.

Compared to similar versions of dBase early versions Paradox typically provided database developers with significantly more advanced capabilities, such as the use of business graphics in DOS applications, updating data in applications when working with multi-user users, visual aids building queries based on the QBE - Query by Example interface, tools for statistical data analysis, as well as tools for visually building user application interfaces with automatic code generation in the PAL (Paradox Application Language) programming language.

Windows versions of the Paradox DBMS, in addition to the services listed above, also made it possible to manipulate data in other formats, in particular dBase and data stored in server DBMSs. Paradox users received this opportunity through the use of the Borland Database Engine library and SQL Links drivers. This allowed Paradox to be used as a universal tool for managing various databases (a much lighter version of Paradox 7, called Database Desktop, is still included with Borland Delphi and Borland C++Builder for precisely this purpose). As for the basic data format used in this product, it has the same disadvantages as all desktop DBMS data formats, and therefore, if possible, they try to replace it with a server DBMS, even while maintaining Paradox itself as a tool for application development and data manipulation.

The current version of this DBMS is Paradox 9, available in two versions - Paradox 9 Standalone Edition and Paradox 9 Developer’s Edition. The first of them is intended for use as a desktop DBMS and is included in Corel Office Professional, the second - as both a desktop DBMS and a tool for developing applications and manipulating data in server DBMSs. Both versions contain:

• Paradox and dBase data manipulation tools.
• Tools for creating forms, reports and applications.
• Tools for visual query construction.
• Tools for publishing data and reports on the Internet and creating Web clients.
• Corel Web server.
• ODBC driver for accessing Paradox format data from Windows applications.
• Tools for accessing Paradox format data from Java applications.

In addition, Paradox 9 Developer’s Edition contains:

• Run-time version of Paradox for delivery along with applications.
• Tools for creating distributions.
• SQL Links drivers for accessing server DBMS data.

Note, however, that the popularity of this product as a development tool in Lately has decreased somewhat, although many information systems created with its help are still in use in the world.

Microsoft FoxPro and Visual FoxPro

FoxPro traces its origins to the desktop DBMS FoxBase from Fox Software. When developing FoxBase in the late 80s, this company pursued the goal of creating a DBMS that was functionally compatible with dBase in terms of file organization and programming language, but significantly superior in performance. One of the ways to increase productivity was to organize index files more efficiently than in dBase - in terms of the format of index files, these two DBMSs are incompatible with each other.

Compared to similar versions of dBase, FoxBase and more later version This product, called FoxPro, provided its users with slightly broader capabilities, such as the use of business graphics, application code generation, automatic generation of application documentation, etc.

This product was subsequently acquired by Microsoft. Its latest versions (since version 3.0, released in 1995) are called Visual FoxPro. With each new version this product has been increasingly integrated with other Microsoft products, particularly Microsoft SQL Server, within Visual FoxPro for several recent years includes tools for migrating FoxPro data to SQL Server and tools for accessing that server's data from Visual FoxPro and applications created with it. Although the FoxPro data format has also been modified with each new version, gaining features such as storing referential integrity rules and some business rules in the database itself, much more emphasis has been placed on migrating Visual FoxPro applications to server platforms.

The latest version of this product is Visual FoxPro 6.0, available both separately and as part of Microsoft Visual Studio 6.0. A distinctive feature of this desktop DBMS from the two discussed above is the integration of this product with Microsoft technologies, in particular support for COM (Component Object Model - a component object model that is the basis for the functioning of 32-bit versions of Windows and the organization of distributed computing in this operating system), integration with Microsoft SQL Server, the ability to create distributed applications based on the Windows DNA (Distributed interNet Applications) concept.

Visual Fox Pro 6.0 provides the following features:

• Tools for publishing data on the Internet and creating Web clients.
• Tools for creating ASP components and Web applications.
• Tools for creating COM objects and objects for Microsoft Transaction Server, allowing you to create scalable multi-tier data processing applications.
• Tools for accessing data from server DBMSs, based on the use of OLE DB (a set of COM interfaces that allows unified access to data from a variety of sources, including non-relational databases and other sources, such as Microsoft Exchange).
• Tools for accessing Microsoft SQL Server and Oracle data, including the ability to create and edit tables, triggers, stored procedures
• Tools for debugging Microsoft SQL Server stored procedures.
• A tool for visual modeling of components and objects that are components of an application - Visual Modeller.
• A tool for managing application components so that they can be reused.

So, the development trends of this product are obvious: from a desktop DBMS, Visual FoxPro is gradually turning into a tool for developing applications in the client/server architecture and distributed applications in the Windows DNA architecture. However, these trends are to a certain extent characteristic of all the most popular desktop DBMSs - we have already seen that both dBase and Paradox also allow access to the most popular server DBMSs.

Microsoft Access

The first version of the Access DBMS appeared in the early 90s. It was the first desktop relational DBMS for 16-bit Windows versions. The popularity of Access increased significantly after the inclusion of this DBMS in the Microsoft Office.

Unlike Visual FoxPro, which has actually turned into an application development tool, Access is aimed primarily at Microsoft Office users, including those not familiar with programming. This, in particular, manifested itself in the fact that all information related to a specific database, namely tables, indexes (supported, of course), referential integrity rules, business rules, a list of users, as well as forms and reports are stored in one file , which is generally convenient for novice users.

The latest version of this DBMS - Access 2000 is included in Microsoft Office 2000 Professional and Premium, and is also available as a standalone product. Access 2000 includes:

• Tools for manipulating Access data and data accessible via ODBC (the latter can be “attached” to an Access database).
• Tools for creating forms, reports and applications; in this case, reports can be exported to Microsoft Word format or Microsoft Excel, and is used to create applications Visual Basic for Applications, common to all components of Microsoft Office.
• Tools for publishing reports on the Internet.
• Tools for creating interactive Web applications for working with data (Data Access Pages).
• Means of accessing server DBMS data via OLE DB.
• Tools for creating client applications for Microsoft SQL Server.
• Microsoft SQL Server Administration Tools.

Access's COM support means you can use ActiveX controls in forms and Web pages created with using Access. Unlike Visual FoxPro, you cannot create COM servers using Access.

In other words, Microsoft Access can be used, on the one hand, as a desktop DBMS and an integral part of the office suite, and on the other hand, as a client of Microsoft SQL Server, allowing its administration, manipulation of its data and creation of applications for this server.

In addition to manipulating Microsoft SQL Server data, Access 2000 also allows you to use the Microsoft Data Engine (MSDE), which is essentially a desktop database server compatible with Microsoft SQL Server, as a data store. This product, which is relatively new compared to the others discussed in this review, will be discussed in the next section of this article.

Microsoft Data Engine

MSDE is a DBMS based on Microsoft SQL Server technologies, but intended for use in desktop systems or network applications with a data volume of up to 2 GB and a small number of users. Essentially MSDE is lightweight Microsoft version SQL Server, which does not contain administration tools, can be classified as a desktop DBMS very conditionally.

In Microsoft Access, the user can choose which data access mechanism to use: Microsoft Jet - standard set data access libraries or MSDE (in which case the database is managed through a separate process). It is possible to convert existing Access databases to an MSDE database from within the Access development environment.

MSDE databases are fully compatible with Microsoft SQL Server databases and can be managed by that server if necessary. Like most server DBMSs, these databases support transactions, allow you to create triggers and stored procedures (not available in Access databases), and use the data protection mechanisms provided by operating system. Additionally, when there are a large number of users and a large amount of data, applications using MSDE have better performance because query processing occurs inside the process that manages the database, rather than inside client application, which allows you to reduce network traffic associated with data transfer from server to client.

MSDE is included with Microsoft Office 2000 Premium or Developer and is also available on the Microsoft Web site for registered users of Visual Studio 6.0 Professional, Enterprise Edition, or any of the development tools that are part of Visual Studio 6.0 Professional or Enterprise Edition. MSDE can be freely distributed as part of applications created using any of the development tools included with Visual Studio 6.0 or Office 2000 Developer.

Conclusion

In this article, we examined the most popular desktop DBMSs today and traced the history of their development. We saw that the development of those desktop DBMSs that managed to maintain their popularity over the years followed very specific patterns. All these DBMS:

• acquired visual tools for designing forms, reports and applications when early Windows versions appeared;
• began to provide access to server DBMS data by the time the first 32-bit versions appeared;
• purchased tools for publishing data on the Internet and, to one degree or another, support the creation of applications for editing data using Web browsers;
• began to provide the ability to store descriptions of referential integrity rules within the database.

In addition, all modern DBMSs, with the exception of Corel Paradox, as an alternative to their own data format, allow the use of lightweight database servers for creating desktop applications, intended for use on one computer or within a small working group. In other words, the history of the development of desktop DBMS reflects modern trends in the development of information systems, such as the creation distributed systems using the Internet or Intranet, the use of rapid application development tools, and the wholesale migration of database applications, including desktop applications, to a client/server architecture.

The next article in this series will focus on client/server architecture and server DBMSs such as IB Database, Microsoft SQL Server, Oracle, Sybase and Informix.

ComputerPress 4"2000

In the broad sense of the word database (DB)- this is a collection of information about specific objects of the real world in any subject area.

For comfortable work with data, they need to be structured, i.e. introduce certain conventions about the way they are presented.

Database(in the narrow sense of the word) - a named collection of structured data related to a certain subject area

In real activities, database systems are mainly used.

Database system (DBS) is a computerized structured data storage system whose main purpose is to store information and make it available on demand.

Database systems also exist in small, smaller powerful computers, and on larger, more powerful ones. Large ones mainly use multi-user systems, while small ones use single-user systems.

Single-user system(single-user system) is a system in which no more than one user can access the database at the same time.

Multi-user system(multi-user system) is a system in which several users can access the database at the same time.

The main purpose of most multi-user systems is to allow each individual user to operate the system as if it were a single-user system.

The differences between single-user and multi-user systems are in their internal structure and are practically invisible to the end user.

The database system contains four main elements: data, hardware, software And users .

Data in the database are integrated And general.

Integrated - this means that the data can be represented as a combination of several, possibly overlapping, separate files data. (For example, there is a file containing data about students - last name, first name, patronymic, date of birth, address, etc., and another about the sports section. The necessary data about students attending the section can be obtained by accessing the first file. )

Are common - this means that individual data areas can be used by different users, i.e. each of these users can have access to the same data area, even at the same time. (For example, the same database data about students can be used simultaneously by the student HR department and the dean’s office.)

TO hardware relate:

Drives for storing information along with connected input/output devices, input/output channels, etc.

A processor (or processors) together with main memory that is used to support the operation of system software.

THERE ARE QUESTIONS HERE

In life, we are often faced with the need to store some information, and therefore we often deal with databases. For example, we use a notebook to store our friends' phone numbers and organize our time. A phone book contains information about people living in the same city. It's all kind of Database. Well, since this Database, then let's see how data is stored in them. For example, the phone book is a table (Table 10.1).

In this table, the data is the actual phone numbers, addresses and full names, i.e. the lines “Ivanov Ivan Ivanovich”, “32-43-12”, etc., and the names of the columns of this table, i.e. the lines “Full name”, “Phone number” and “Address” specify the meaning of this data, their semantics.

Now imagine that there are not two, but two thousand entries in this table, you are creating this directory and somewhere an error occurred (for example, a typo in the address). Apparently, it will be difficult to find and fix this error manually. You need to use some kind of automation.

To manage large amounts of data, programmers (with the help of mathematicians) came up with control systems databases (DBMS). Compared to text databases electronic DBMS have a huge number of advantages, from the ability to quickly search for information, the interconnection of data with each other, to the use of this data in various application programs ah and simultaneous access to data by several users.

To be precise, let us define Database, offered by Glossary.ru

Database is a collection of related data organized according to certain rules, providing general principles description, storage and manipulation, independent of application programs. Database is information model subject area. Appeal to databases carried out using a control system databases (DBMS). DBMS provides support for creating databases, centralized management and organizing access to them for various users.

So, we came to the conclusion that it is advisable to store data independently of programs, so that they are interconnected and organized according to certain rules. But the question of how to store data and by what rules it should be organized remained open. There are many ways (by the way, they are called data representation or data storage models). The most popular are object and relational data models.

The basis object model based on the concept of object-oriented programming, in which data is represented as a set of objects and classes interconnected by related relationships, and work with objects is carried out using hidden (encapsulated) methods in them.

Examples of object DBMS: Cache, GemStone (from Servio Corporation), ONTOS (ONTOS).

Recently, manufacturers DBMS strive to combine these two approaches and advocate an object-relational model of data representation. Examples of such DBMS- IBM DB2 for Common Servers, Oracle8.

Since we are going to work with mysql, then we will discuss aspects of working only with relational databases. We have two more important concepts left to consider in this area: keys And indexing, after which we can start learning the query language SQL.

Keys

First, let's think about this question: what information needs to be given about a person so that the interlocutor can definitely say that this is the right person, there can be no doubt, there is no other like him? Giving a last name is obviously not enough, since there are namesakes. If the interlocutor is a person, then we can approximately explain who we are talking about, for example, remember the action that that person committed, or something else. The computer will not understand such an explanation; it needs clear rules on how to determine who we are talking about. In control systems databases To solve this problem, the concept was introduced primary key .

Primary key (PK) is the minimum set of fields that uniquely identifies a record in a table. Means, primary key- this is, first of all, a set of table fields, secondly, each set of values ​​of these fields must define a single record (row) in the table and, thirdly, this set of fields must be the minimum of all those with the same property. Because the primary key defines only one unique record, then no two table entries can have the same values primary key.

For example, in our table (see above), full name and address make it possible to uniquely highlight a record about a person. Speaking in general, without connection with the problem being solved, then such knowledge does not allow us to accurately indicate a single person, since there are namesakes living in different cities at the same address. It's all about the boundaries we set for ourselves. If we believe that knowing the full name, phone number and address without indicating the city is enough for our purposes, then everything is fine, then the fields Full Name and Address can form primary key.

In any case, the problem of creating primary key falls on the shoulders of the one who designs database(develops a data storage structure). The solution to this problem can be either the selection of characteristics that naturally define a record in the table (setting the so-called logical, or natural, PK), or the creation of an additional field intended specifically for uniquely identifying records in the table (setting the so-called surrogate, or artificial, PK).

An example of a logical primary key is the passport number in database about passport details of residents or full name and address in phone book(table above). To specify a surrogate primary key You can add an id (identifier) ​​field to our table, the value of which will be an integer unique for each row of the table. The use of such surrogate keys makes sense if natural primary key represents a large set of fields or its selection is non-trivial.

In addition to unique identification of the record, primary keys are used to organize relationships with other tables.

For example, we have three tables: one containing information about historical figures (Persons), one containing information about their inventions (Artifacts), and one containing images of both persons and artifacts (Images) (Figure 10.1).

Primary key in all these tables there is an id (identifier) ​​field. The Artifacts table has an author field, which records the identifier assigned to the author of the invention in the Persons table. Each value of this field is foreign key For primary key Persons tables. Additionally, the Persons and Artifacts tables have a photo field that references an image in the Images table.

These fields are also foreign keys For primary key Images tables and establish an unambiguous logical connection between Persons-Images and Artifacts-Images. That is, if the value foreign key photo in the personality table is equal to 10, this means that the photo of this person has id=10 in the image table. Thus, foreign keys used to organize relationships between tables Database(parent and child) and to maintain referential integrity constraints.

What is a database in computer science

In computer science, the concept of a database is a set of data for information networks and users, stored in a special, organized form. The type of data storage is determined by the given structure (scheme) of the database and the rules for its management.

By themselves, databases are useless if there is no way to manage them. By database management we mean the ability to individually or collectively add information, sort it, partially or completely copy and move it, and combine two or more databases. To manage databases, software products have been created that are database software. They are called DBMS – database management systems.

What is DBMS and SQL

This is what consumers, that is, you and I, are dealing with. Modern DBMSs allow you to process not only texts or graphics, but also media files (audio and video files).

Any software product has its own language with which it is controlled. DBMS is no exception. One of the main languages ​​for communicating with a DBMS is SQL (structured query language).

It is worth noting that, according to the nature of their use, DBMSs are divided into single-user (for one user - a local computer) and multi-user (for networks).

I'm sure you don't think that there is one universal DBMS. And that’s right, there are dozens of them. In this section, we will limit ourselves to working with the free and most common MySQL DBMS.

MySQL DBMS

The MySQL DBMS only works with relational databases data. Relational databases are the easiest to learn initially. In addition, they are used on all hosting and servers for mass use.

It remains to define the concept of a relational database. This simple tables, which have information rows and columns. The intersection of a row and a column is called a cell. The entire database consists of several or many tables, and all tables interact with each other.

This article focuses on the concept of a database. Various databases are actively used today when working with PCs - personal computers. For brevity, we will introduce the accepted abbreviation for the database - DB.

What is a database: definition and functions

To the question of what a database is, computer science gives a very clear answer.

A database (DB) is a collection of materials that are systematized in such a way that they are easy to find and process using a PC or other computer (electronic computer). Materials can mean anything: articles, various documents, reports, etc.

A database is also a collection of data that is stored on a PC in accordance with a specific scheme.

There is another definition: a database is a set of permanent data that is used by various software systems any organization.

Based on these definitions, it is not difficult to guess why a PC database is needed. Various databases are used to systematize and store a large number of similar documents and quick access to them. Remember when you used to go to some large library, how long did it take the librarian to find the book you were interested in? But it’s not always possible to immediately even remember whether there is any material in the file cabinet. This problem has now been resolved. All you need to do is open the database you are interested in on your computer and type the name you are interested in in the search. Quickly finding and processing information of interest from a list is what the database and programs that operate on various databases are designed for. Modern databases and the programs with which they are processed provide invaluable assistance to people of various professions who are forced to work with large volumes the same type of information. These are primarily sales workers, librarians, medical workers, and accountants.

What is a relational database

A relational database is a database that is based on a relational data model. We will give a more precise definition, so to speak, from first-hand experience. Let us recall that the relational data model was proposed and subsequently implemented by the American mathematician Codd in 1970. And in an article from 1985, he formulated 12 rules by which one can determine that a given database is relational. Let's list them here.

1. Rule of information. All information in the database should be presented only in the form of values ​​contained in tables, that is, at the logical level.
2. Guaranteed access. Each element of the database must be accessed using the table name, column name and primary key.
3. Support for invalid values. Null values ​​in the database must be used to represent missing data and must be distinguished from any numeric characters and the space character.
4. Dynamic directory. The description of the database at the logical level is presented in the same form as the main data.
5. Comprehensive data sublanguage. A relational database must have a specific symbolic language with clear syntax that supports various elements the database itself: processing, data definition, transaction boundaries, etc.
6. Updating views. Makes available all views that can be updated.
7. Adding, updating and deleting. Ability to work with the database during these operations.
8. Independence of physical data.
9. Independence of logical data.
10. Independence of integrity conditions. There is the possibility of defining integrity conditions that are specific to each relational database.
11. Independence of distribution. That is, independence from the conditions of a particular user.
12. Rule of uniqueness. This is the lack of ability to use a low-level language to process data in the language high level(in the case of several records at once, for example).

Every website owner knows that for the website to function properly, you need not only files with page code, but also databases. Database management systems (DBMS) are used to interact with databases. In this article I want to talk about databases and DBMSs, what types exist, and how they differ from each other.

Database

A database is a specific set of data, which, as a rule, are connected by a unifying feature or property (or several). This data is organized, for example, alphabetically. The abundance of different data that can be placed in a single database leads to many variations in what can be recorded: user personal data, records, dates, orders, and so on. For example, if you have an online store, then your website database may contain price lists, a catalog of goods or services, reports, statistics and customer information.

First of all, this is convenient because information can be quickly entered into a database and just as quickly retrieved if necessary. If at the dawn of the development of web development all the necessary data had to be written in the page code, now there is no such need - necessary information can be queried from the database using scripts. Special algorithms for storing and retrieving information that are used in databases make it possible to find the necessary information literally in a fraction of seconds - and when working in virtual space The speed of a resource is more important than anything else.

The relationship of information in the database is also important: changing one line can lead to significant changes in other lines. Working with data this way is much easier and faster than if the changes affected only one place in the database.

However, this does not mean that every site must have a database - for example, if you have a business card site and you do not post any new information on the site, then you simply will not need a database. Most easy way make a simple website - create .

Database Management System

As you can guess from the name, a database management system (or DBMS for short) is software that is used to create and work with databases. Main function DBMS is the management of data (which can be either in external or in RAM). The DBMS necessarily supports database languages, and is also responsible for copying and restoring data after any failures.

As for the classification of databases, various options are possible.
For example, you can divide the databases by data models: hierarchical (have a tree structure), network (similar in structure to hierarchical ones), relational (used to manage relational databases), object-oriented (used for the object data model) and object-relational (some kind of fusion of relational and object-oriented type of databases).

Or, if the division is based on where is the DBMS located?, they can be divided into local - the entire DBMS is located on one computer, and distributed - parts of the database management system are located on several computers.

File-server, client-server and embedded - these are the names DBMSs bear if we divide them by way to access databases. File server DBMSs are currently considered obsolete; Basically, client-server systems (DBMSs located on the server along with the database itself) and embedded systems (which do not require separate installation) are used.

The information stored in databases is not limited to text or graphic files - modern versions of DBMS also support audio and video file formats.

In this article I will focus on DBMSs that are used to store information from various web resources.

Why are these DBMSs needed? In addition to their main function - storing and systematizing a huge amount of information - they allow you to quickly process client requests and provide fresh and relevant information.

This also applies to changes you make - instead of changing information in every file on the site, you can change it in the database, and then the correct information will immediately be displayed on each page.

Relational DBMS and SQL language

Relational and object-relational DBMSs are among the most common systems. They are tables in which each column (called a “field”) is ordered and has a specific unique name. The sequence of rows (they are called “records” or “records”) is determined by the sequence in which information is entered into the table. In this case, processing of columns and rows can occur in any order. Tables with data are interconnected by special relationships, thanks to which you can work with data from different tables - for example, combine them - using one query.

To manage relational databases, a special programming language is used - SQL. The abbreviation stands for “Structured query language”, translated into Russian as “structured query language”.

The commands that are used in SQL are divided into those that manipulate data, those that define data, and those that manipulate data.

The scheme for working with the database looks like this:

MySQL

MySQL is one of the most popular and widespread DBMS, which is used in many companies (for example, Facebook, Wikipedia, Twitter, LinkedIn, Alibaba and others). MySQL is a relational DBMS that is free software: it is distributed under the terms of the GNU Public License. Typically, this database management system is defined as a good, fast and flexible system recommended for use in small or medium-sized projects. MySQL has many different advantages. For example, it supports various types of tables: both the well-known MyISAM and InnoDB, and the more exotic HEAP and MERGE; in addition, the number of supported types is constantly growing. MySQL executes all commands quickly - perhaps now it is the fastest DBMS in existence. An unlimited number of users can work with this database management system at the same time, and the number of rows in tables can be equal to 50 million.

Since, in comparison with some other DBMS, MySQL supports fewer features, it is much easier to work with it than, for example, with PostgreSQL, which will be discussed below.

The first version of MySQL was released back in 1995, and since then there have been several subsequent releases, each of which brought significant changes.

To work with MySQL, not only text, but also graphical mode is used. This is possible thanks to the phpMyAdmin application: you don’t even need to know SQL commands to work in the application, and you can administer your database directly through your browser.

In general, it can be noted that MySQL is the choice of those who need a DBMS for a small or medium-sized project, fast and easy to use and without administration difficulties.

PostgreSQL

This freely distributed database management system belongs to the object-relational type of DBMS. As with MySQL, working with PostgreSQL is based on SQL language however, unlike MySQL, PostgreSQL supports the SQL-2011 standard. This DBMS has no restrictions on maximum size database, nor the maximum of records or indexes in the table.

If we talk about the advantages of PostgreSQL, then, of course, these are the reliability of transactions and replications, the possibility of inheritance and easy extensibility. PostgreSQL supports various extensions and programming language options such as PL/Perl, PL/Python, and PL/Java. It is also possible to load C-compatible modules.

Many people note that, unlike MySQL, this DBMS has good and detailed documentation that provides answers to almost all questions.

The fact that it is a larger DBMS than MySQL is also indicated by the fact that PostgreSQL is periodically compared to such a powerful data management system as Oracle.

All this allows us to talk about PostgreSQL as one of the most advanced DBMS at the moment.

SQLite

At the moment this is one of the most compact DBMS; it is also embedded and relational. SQLite allows you to store all data in one file and, due to its small size, is distinguished by enviable performance. SQLite differs significantly from MySQL and PostgreSQL in its structure: the engine and interface of this DBMS are in the same library - and this is what allows you to execute all queries very quickly. Other DBMSs (MySQL, PostgreSQL, Oracle, etc.) use the client-server paradigm, when interaction occurs through a network protocol.

Disadvantages include the lack of a user system and the possibility of increasing productivity.

SQLite can be recommended for use in projects where you need to be able to quickly migrate an application and there is no need for scalability.

Oracle

This DBMS is of the object-relational type. The name comes from the name of the company that developed this system, Oracle. Along with SQL, the DBMS uses a procedural extension called PL/SQL, as well as the Java language.

Oracle is a system that has been stable for decades, so it is chosen by large corporations for which reliability of recovery after failures, a streamlined backup procedure, the ability to scale and other valuable features are important. In addition, this DBMS provides excellent security and effective data protection.

Unlike other DBMSs, the cost of purchasing and using Oracle is quite high, and this is often a significant obstacle to its use in small companies. This is probably also the reason why Oracle is only in 6th place in the 2016 DBMS ranking in Russia.

MongoDB

This DBMS is different in that it is designed to store hierarchical data structures, and therefore it is called document-oriented (it is a document storage without using tables or schemas). MongoDB is open source.

Using an identifier, you can perform quick operations on an object; This DBMS also performs well in complex interactions. First of all we're talking about about performance - in some cases, an application written in MongoDB will run faster than the same application using SQL, because MongoDB belongs to the NoSQL DBMS class and uses SQL instead object language queries, which is much lighter than SQL.

However, this language also has its limitations, and therefore MongoDB should be used in cases where there is no need for complex and non-trivial selections.

Instead of a conclusion

Choosing a DBMS is important point when creating your resource. Start from your tasks and capabilities, try and experiment to find exactly the option that will be most suitable.