The most popular subd. A Brief Overview of Relational Database Management Systems

Databases are logically modeled repositories of any type of data. Every database that is not schemaless follows a model that defines a specific data processing structure. DBMS are applications (or libraries) that manage databases of various shapes, sizes and types.

To better understand the DBMS, check out.

Relational database management systems

Relational systems implement a relational data model, which defines all stored information as a set of related records and attributes in a table.

DBMS of this type use structures (tables) to store and work with data. Each column (attribute) contains its own type of information. Each record in the database that has a unique key is passed into a table row, and its attributes are displayed in the table columns.

Relationships and Data Types

Relations can be defined as mathematical sets containing sets of attributes that represent stored information.

Each element that forms a record must satisfy a certain data type (integer, date, etc.). Different RDBMSs use different data types, which are not always interchangeable.

These kinds of restrictions are common in relational databases. In fact, they form the essence of the relationship.

Popular RDBMS

In this article we will talk about the 3 most popular RDBMSs:

• SQLite: very powerful embedded RDBMS.
• MySQL: the most popular and frequently used RDBMS.
• PostgreSQL: the most advanced and flexible RDBMS.

SQLite

SQLite is an amazing library that is built into the application that uses it. Being a file database, it provides an excellent set of tools for easier (compared to server databases) processing of any type of data.

When an application uses SQLite, their communication is done through functional and direct calls to the files containing the data (such as SQLite databases), rather than some kind of interface, which improves the speed and performance of operations.

Supported data types

• NULL: NULL value.

• INTEGER: a signed integer stored in 1, 2, 3, 4, 6, or 8 bytes.

• REAL: A floating point number stored in IEEE 8-byte format.

• TEXT: text string with UTF-8, UTF-16BE, or UTF-16LE encoding.

• BLOB: a type of data stored exactly in the same form in which it was received.

Note: for more details, check out the documentation.

Advantages

• File: the entire database is stored in one file, making it easy to move.

• Standardized: SQLite uses SQL; Some functions are omitted (RIGHT OUTER JOIN or FOR EACH STATEMENT), however, there are some new ones.

• Great for development and even testing: During the development phase, most require a scalable solution. SQLite, with its rich feature set, can provide more than enough functionality while being simple enough to work with a single file and associated C library.

Flaws

• Lack of user control: advanced databases provide users with the ability to manage relationships in tables according to privileges, but SQLite does not have such a feature.

• Inability to further configure: again, SQLite cannot be made more productive by fiddling with the settings - that’s just the way it is designed.

When to use SQLite

• Built-in applications: All ported applications that are not designed to scale - for example, local single-user applications, mobile applications or games.

• Disk access system: In most cases, applications that frequently perform direct read/write operations to disk can be migrated to SQLite to improve performance.

• Testing: Perfect for most applications, part of the functionality of which is testing business logic.

When not to use SQLite

• Multi-user applications: If you are working on an application in which several people will simultaneously access the database, it is better to choose a full-featured RDBMS - for example, MySQL.

• Applications that record large amounts of data: One of the limitations of SQLite is write operations. This RDBMS allows only one write operation to be executed at a time.

MySQL

MySQL is the most popular of all major server databases. It’s very easy to understand, and you can find a lot of information about it online. Although MySQL does not attempt to fully implement SQL standards, it does offer a lot of functionality. Applications communicate with the database through a daemon process.

Supported data types

• TINYINT: very small whole.

• SMALLINT: small whole.

• MEDIUMINT: medium sized whole.

• INT or INTEGER: the whole is normal size.

• BIGINT: big whole.

• FLOAT: signed single-precision floating-point number.

• DOUBLE, DOUBLE PRECISION, REAL: signed double precision floating point number.

• DECIMAL, NUMERIC: signed floating point number.

• DATE: date of.

• DATETIME: combination of date and time.

• TIMESTAMP: timestamp.

• TIME: time.

• YEAR: year in YY or YYYY format.

• CHAR: a fixed-size string, padded on the right with spaces to a maximum length.

• VARCHAR: variable length string.

• TINYBLOB, TINYTEXT: A BLOB or TEXT column with a maximum length of 255 (2^8 – 1) characters.

• BLOB, TEXT: A BLOB or TEXT column with a maximum length of 65535 (2^16 – 1) characters.

• MEDIUMBLOB, MEDIUMTEXT: A BLOB or TEXT column with a maximum length of 16777215 (2^24 – 1) characters.

• LONGBLOB, LONGTEXT: A BLOB or TEXT column with a maximum length of 4294967295 (2^32 – 1) characters.

• ENUM: transfer.

• SET: multitudes.

Advantages

• Simplicity: MySQL is easy to install. There are many third-party tools, including visual ones, that make it easier to get started with the database.

• Many functions: MySQL supports most SQL functionality.

• Safety: MySQL has many security features built into it.

• Power and Scalability: MySQL can handle really large amounts of data and is well suited for scalable applications.

• Speed: Neglect of some standards allows MySQL to work more efficiently, sometimes cutting corners.

Flaws

• Known Limitations: By definition, MySQL can't do everything, and it has certain functionality limitations.

• Reliability issues: some operations are implemented less reliably than in other RDBMSs.

• Stagnation in development: Although MySQL is an open-source product, work on it is very slow. However, there are several databases that are entirely based on MySQL (for example, MariaDB). By the way, you can learn more about the relationship between MariaDB and MySQL from our conversation with the creator of both RDBMSs, James Bottomley.

When to use MySQL

• Distributed operations: When you need more functionality than SQLite can provide, you should use MySQL.

• High security: MySQL security features provide strong protection for data access and use.

• Websites and applications: Most web resources can work well with MySQL, despite the limitations. This tool is very flexible and easy to use, which is beneficial in the long run.

• Custom solutions: If you're working on a very specific product, MySQL will adapt to your needs with a wide range of settings and operating modes.

When not to use MySQL

• SQL Compatibility: Because MySQL does not attempt to fully implement SQL standards, it is not fully SQL compliant. Because of this, problems may arise when integrating with other RDBMSs.

• Competitiveness: Although MySQL handles read operations well, simultaneous read-write operations can cause problems.

• Lack of features: Depending on the choice of MySQL engine, some features may be missing.

PostgreSQL

PostgreSQL is the most advanced RDBMS, focusing primarily on full standards compliance and extensibility. PostgreSQL, or Postgres, attempts to fully comply with ANSI/ISO SQL standards.

PostgreSQL differs from other RDBMSs in that it has object-oriented functionality, including full support for the ACID (Atomicity, Consistency, Isolation, Durability) concept.

Being based on powerful technology, Postgres excels at processing multiple jobs simultaneously. Concurrency support is implemented using MVCC (Multiversion Concurrency Control), which also ensures ACID compatibility.

Although this RDBMS is not as popular as MySQL, there are many third-party tools and libraries to make working with PostgreSQL easier.

Supported data types

• bigint: signed 8-byte integer.

• bigserial: an automatically incremented 8-bit integer.

• bit[(n)]: fixed length bit string.

• bit varying [(n)]: variable length bit string.

• boolean: Boolean value.

• box: rectangle on a plane.

• bytea: binary data.

• character varying [(n)]: a fixed-length character string.

• character [(n)]:

• cidr: IPv4 or IPv6 network address.

• circle: circle on a plane.

• date: calendar date.

• double precision: double precision floating point number.

• inet: IPv4 or IPv6 host address.

• integer: signed 4-byte integer.

• interval [(p)]: period.

• line: an infinite straight line on a plane.

• lseg: a segment on a plane.

• macaddr: MAC address.

• money: monetary value.

• path: geometric path on a plane.

• point: geometric point on a plane.

• polygon: polygon on a plane.

• real: single precision floating point number.

• smallint: signed 2-byte integer.

• serial: an automatically incremented 4-bit integer.

• text: variable length character string.

• time [(p)] : time of day (no time zone).

• time [(p)] with time zone: time of day (with time zone).

• timestamp [(p)] : date and time (no time zone).

• timestamp [(p)] with time zone: date and time (with time zone).

• questions: text search query.

• tsvector: text search document.

• txid_snapshot: snapshot user transaction ID.

• uuid: unique identificator.

• xml: XML data.

Advantages

• Full SQL compatibility.

• Community: PostgreSQL is supported 24/7 by an experienced community.

• Third party support: Despite its very advanced features, PostgreSQL is used in many RDBMS related tools.

• Extensibility: PostgreSQL can be programmatically extended with stored procedures.

• Object Orientation: PostgreSQL is not only a relational, but also an object-oriented DBMS.

Flaws

• Performance: In simple read operations, PostgreSQL can be inferior to its competitors.

• Popularity: Due to its complexity, the tool is not very popular.

• Hosting: Due to the above factors, it is difficult to find a suitable provider.

When to use PostgreSQL

• Data integrity: If reliability and data integrity are a priority, PostgreSQL is the best choice.

• Complex procedures: if your database needs to perform complex procedures, you should choose PostgreSQL due to its extensibility.

• Integration: If in the future you need to move the entire database to another solution, PostgreSQL will have the least problems.

When not to use PostgreSQL

• Speed: If all you need is fast read operations, you shouldn't use PostgreSQL.

• Simple situations: unless you need increased reliability, ACID support and all that, using PostgreSQL is shooting flies with a cannon.

1. What trends in the development of server DBMSs could you note in 2015–2016?

Vitaly Chesnokov, QSOFT
The most important trends in the development of modern DBMSs: the use of virtualization and GRID technology, self-diagnosis and automatic correction, the use of NoSQL DBMS in Big Data, the use of NewSQL DBMS, execution of C/C++ code in the DBMS address space.

Over the past few years, the volume of data suitable for processing and storage in a database has grown exponentially. An amendment to the Law “On Personal Data” was adopted, stating that personal data of Russian citizens must be stored on the territory of the Russian Federation. Some Western countries also have similar laws. All this leads us to the need for clustering and breaking data into parts.

The percentage of using NoSQL DBMS is growing everywhere, where possible, due to the high speed of working with data and the possibility of relatively simple clustering. A new type of DBMS is becoming widespread - NewSQL. The main unprecedented features of NewSQL include: the possibility of asynchronous master-master replication, replacing the classic master-slave scheme and providing greater flexibility for high-load projects; simplification of administration and provision of dynamic database management; support for stored procedures in C/C++ and the ability to execute C/C++ code in the DBMS address space (provide virtually unlimited extensibility and incredible performance gains); improved diagnostic and debugging tools.

In addition, the use of virtualization in a DBMS provides the necessary fault tolerance and scalability.

Nikolay Fetyukhin,MST
Transition to NoSQL and database specialization. For example, you can pay attention to Redis and Tarantool. The latter even contains its own application server. An interesting trend is a combined DBMS and backend, like Parse from Facebook. Also smooth migration of databases to the clouds.

Petr Urvaev, SimbirSoft
Functions that have successfully proven themselves in some DBMSs are, after some time, implemented in other products. For example, materialized views, which first appeared in Oracle DBMS, were later implemented in MS SQL Server, and then appeared in PostgreSQL. The advantages that NoSQL solutions provide are gradually also being realized in relational DBMSs. For example, the latest versions of PostgreSQL implement support for working with data in JSON format.

Evgeniy Gusev ITECH
Changes in recent years in the DBMS segment have been both private - in relation to individual leading products, and structural in nature, so there are many trends. First, heterogeneity. The transition to a microservices model made it possible to flexibly select means of solving the problem of data storage, without limiting oneself to one. Secondly, the development of NoSQL, in-memory storages. Thirdly, Big Data is a revolution that requires a rethinking of both the data storage methodology and the very concept of “data”. Fourth, column-oriented databases.

2. In your opinion, is there a tendency for DBMS to move to the “cloud”? What are the pros and cons of this approach?

Vitaly Chesnokov, QSOFT
Yes, this trend certainly exists. First, you need to separate two fundamental approaches to operating a DBMS in the cloud.

The first is the deployment of a virtual machine with a DBMS in the cloud. You can upload your own image to it or use a pre-prepared one with an already optimized DBMS. In essence, such a virtual machine is not fundamentally different from a regular physical server. The main advantage compared to a physical server is the ease of scaling, both vertical (you can allocate more resources for a given “virtual machine” at any time) and horizontal (creating a new “virtual machine” takes only a few minutes). Another significant advantage is the high availability of cloud virtual machines (99.9%–99.99%). Cloud hosters also provide many additional services, such as monitoring, backup, server control panel, etc.

A fundamentally different approach is a cloud DBMS. In this case, the client does not buy a server, but simply the service of using a DBMS. The current market for public cloud DBMSs, amounting to $400 million, will increase to $1.2 billion by 2017. The main advantages of this approach: payment is not made for the resources provided (which may be “idle”), but only for those actually used: the volume of data stored, the number of processed DBMSs operations; there is no need to configure and administer the DBMS - these tasks are entirely the responsibility of the hoster; there is no need to think about scaling; The hoster provides many convenient and intuitive tools for managing the DBMS; high availability. The main disadvantage is the lack of ability to fine-tune the DBMS.

You can also separately distinguish such a subtype of cloud DBMS as DbaaS (Database as a Service). Almost always, a specific DbaaS is one specific DBMS provided in the cloud by the direct developers. This obviously leads to the difference in business models: cloud DBMSs are suitable for large-scale standard tasks, while DbaaS is suitable for specialized ones, for a specific brand of database engine, with the possibility of direct communication with its developers. In addition, DbaaS allows you to significantly more accurately select a system for the required load, in particular by regulating the number of client connections.

Nikolay Apurin, Artwell
There is a trend. Most large (all-Russian) systems already use cloud databases.

Nikolay Fetyukhin,MST
The trend is weak. The use of clouds, although it reduces some costs, leads to new expenses. Using the cloud can only be beneficial for projects with low traffic.

Advantages of clouds: easy scalability, high fault tolerance, availability of servers all over the world, easy cloning and deployment of data. Cons: while clouds, as a product, are “raw” - it is impossible to physically control the data, since they are under the control of the cloud provider.

Petr Urvaev, SimbirSoft
The trend of moving DBMS to the cloud exists as part of a general trend towards transferring the entire IT infrastructure of organizations to the cloud. The main advantage of this approach is the ability to transfer database maintenance. The disadvantages include storing important data on an uncontrolled site. If it is necessary to destroy stored data, a cloud storage user cannot be sure that the data is actually erased.

Evgeniy Gusev ITECH
Exists and is one of the primary ones. There are a lot of advantages to hosting a database in the cloud: ease of sharding and replication, the ability to effectively separate data from business logic in terms of performance, easy and understandable management of computing power, and a number of others. Based on experience, we do not see any critical disadvantages. The larger volumes of data you have to operate, the more horizontal scaling is required, the more justified the use of clouds becomes.

3. What factors influence the choice of DBMS? For which projects are SQL databases more suitable, and for which - NoSQL?

Vitaly Chesnokov, QSOFT
The main factor when choosing between SQL and NoSQL DBMS is the needs of the application. SQL is better suited for some tasks, NoSQL for others.

There are several key differences between these types of DBMS. The data format in SQL is very strict, there are clear table schemas indicating where which data type is used. NoSQL has no predefined document schemas - any information can be added to any document.
In SQL, there are complex relationships between different tables. Data in one table is often a reference to data in another (the principle of data normalization). In NoSQL, as a rule, each document is an isolated information unit and stores all available data (denormalization principle).
SQL has built-in mechanisms to maintain data integrity (for example, you cannot delete a record from a table if it is referenced in other tables). NoSQL does not have such mechanisms, so denormalization of data is important (ideally, each document stores absolutely all the information about an object).
SQL has a transaction mechanism that allows you to execute multiple SQL queries on an all-or-nothing basis. In NoSQL, a similar mechanism exists only within a single document.
Ideally, NoSQL is faster than SQL due to its simpler data storage method, which allows you to get all the information about an object with a simple query on a single document. However, there is a problem related to the fact that the most popular NoSQL DBMSs appeared quite recently. The consequence of this is a smaller amount of information on these DBMSs and a larger number of as yet unresolved problems.
NoSQL is much easier to scale due to the absence of complex logical relationships between documents. In addition, most NoSQL DBMS were initially created with an emphasis on scaling mechanisms.

As a result, NoSQL is better suited for projects with a large amount of data that can be easily divided into separate independent objects. Gives high speed and scalability. SQL is suitable for projects where various data have complex logical relationships with each other and their integrity is extremely important.

Nikolay Apurin, Artwell
NoSQL - for non-standard calculations with huge amounts of data. But as practice has shown, volumes of up to 20 million records are perfectly processed by SQL databases.

Nikolay Fetyukhin,MST
NoSQL technologies are actively used by well-known companies, including in high-load projects. Saving data and simple retrievals using NoSQL will be really fast. In the case of more complex queries, the problem will have to be solved on the product side, which increases the complexity of the product itself. In its pure form, we do not choose NoSQL. Increasing complexity of the product logic and emulation of basic SQL things leads to an increase in the cost of the project. And not every NoSQL solution provides data security in critical situations.

Petr Urvaev, SimbirSoft
The choice of database often depends on the preferences of the architect, the possible load, and the required functionality. SQL databases allow you to clearly define data storage schemes and retrieve data using complex queries, NoSQL databases allow you to store data in a less organized format and support horizontal scaling. Often, distributed systems use SQL and NoSQL databases simultaneously, each of which solves its own problems.

Evgeniy Gusev ITECH
In its current state, SQL / NoSQL are rather not competing, but complementary entities. Using SQL solutions in one application when you need to work with complex data in their interrelationships, and NoSQL when the speed of working with unstructured information comes to the fore, is a completely natural practice.

4. How do you assess the extent to which paid DBMS licenses are distributed among users? In what cases does it make sense to buy a license?

Vitaly Chesnokov, QSOFT
There are two different options for dividing the DBMS into paid and free ones.

The first is free versions of commercial DBMSs (MS SQL, Oracle, etc. are available). Essentially, this is a stripped-down version of the DBMS, which lacks some functionality. Here the main choice factor is very simple - does this project need this functionality? Less often there is a free version, which does not differ from the commercial version in functionality, but is updated less frequently (Couchbase Server).

The second is free DBMS, for which there are similar commercial products (MySQL from Oracle, Percona Server or MariaDB). In this case, the advantage of a commercial product is usually the availability of more serious technical support. In terms of functionality, paid and free DBMSs differ little from each other, although both may have their own tools that are not available in other versions.

As a result, there are two main reasons to choose a paid DBMS: the availability of functionality that is not available in free analogues, and the ability to seek help from the manufacturer’s technical support.

Nikolay Apurin, Artwell
Why pay when there are free ones? However, there are many solutions that can only work with paid databases. Basically, these are foreign practices.

Nikolay Fetyukhin,MST
The question is ambiguous. Often, paid DBMSs have free versions, and free ones have components that can be purchased for money. The difference most often lies in the built-in analytics and database monitoring tools. Therefore, paid DBMSs are more suitable for large projects with large distributed systems.

Petr Urvaev, SimbirSoft
Paid DBMS licenses are preferable to free ones when the project relies on the capabilities of a specific database, and it is important for it that the capabilities used work as stated, and problems in their operation are promptly eliminated. Nowadays, commercial and free databases are used equally often, and most new projects choose free databases, since the capabilities for working with data and stability are approximately at the same level as paid and free DBMSs.

Microsoft Access is an interactive relational database management system (RDBMS) for WINDOWS. This is a program that you can use to store and retrieve data based on the relationships you have established. Working with it is simplified using the mouse manipulator. The graphical capabilities of the shell make a great impression when producing high-quality reports and printouts. All this thanks to support for True-type fonts and embedding OLE objects (Object Linking and Embeding) within the WINDOWS environment. An OLE object is a link to specific information that remains in its original form. An OLE object can be an EXCEL table, a Paintbrush illustration, or a Sound file.

New to the Access program are Cue Cards – a prompting system. This is a training system that provides the user with recommendations on how to overcome real situations when solving applied problems. If you know English, the user will always be able to get a hint about what to do next.

Access also provides the user with mechanisms for working with databases of various formats. For example, you can directly access dBASE, Paradox, or Btrieve databases without converting them to the format used by Access. The Access package also includes the Access Basic language (a built-in dialect of the Visual Basic language), which makes it possible to create specialized database management systems.

Now let's move on to a more detailed look at the Access system. The main concepts or objects of this system are: tables, queries, forms, reports, macros and modules. And, of course, the main concept will be the database.

To create a database, you must complete the following steps:

Activate the Microsoft Access window and select the New Database directive from the File menu (or click the New Database button in the icon line). Microsoft Access displays the New Database dialog box and automatically gives your database a name that you can change.

In the File Name line, type your choice of database name. It can contain up to 8 characters without spaces. Microsoft Access automatically adds the extension. MDB to your database name if you haven't done so.

If you want to store the database in a specific location, select the appropriate directory from the Directories list.

Click on the OK button.

Microsoft Access creates an empty database file and opens the Database window.

The database can be filled with objects of various kinds and operations can be performed on them. But you can perform operations on the database as if it were an indivisible entity. All operations of this kind—database management operations—are concentrated in the File menu of the Access application window or in the database window.

When opening a database, you can limit possible operations with the database to read-only and limit the circle of users who have access to the database only to themselves. If you only want to view the database and the objects it contains, but not change them, you must enable the Read Only control indicator when opening the database. If it is necessary to prevent changes to the data and objects of this database by other users, then when opening the database, you must set the Exlusive option.

If the user has finished working with the database, it should be closed using the Close Database directive in the File menu, by double-clicking the button for calling up the control menu of the database window, or by pressing the keys (Ctrl + F4).

Tables.

The next step in building a database will be filling it, that is, creating objects and assigning the necessary properties to them. Whatever type of data you have, you will have to store it in one or more tables.

Tables are the main form of presentation of information contained in a database. It is impossible to design a form without a table; queries and reports are compiled on the basis of tables.

Each field included in a record is assigned a data type, which determines the type of information that will be stored in that field. The data type is entered in the Data Type column, and it can be selected from the list of available types.

Changes can be made to the finished specification. But you should try to make all corrections to the specification before starting to fill the database, since an attempt to change the parameters of the fields of a filled database may result in loss or corruption of data.

You can embed objects from other OLE-enabled applications into Access tables and link those objects to their parent programs.

When you embed an object, Access stores the object in a table. By double-clicking on an object, the application program with which it was created is launched - you can make changes to the object in it. After finishing work with the parent program, the object in the modified form will be saved in the table.

Tables located in a database created by another program can be imported into Access, and Access tables can also be exported, saving them in formats that other database management applications can understand. But before you start directly importing or exporting, you must make the appropriate import or export settings.

Requests.

Queries are used to select and filter a data set. They allow you to select from the database only the necessary information, i.e., that which meets a certain criterion (condition) and is needed to solve a specific problem. For example, you may need information about suppliers and the goods they supply. You can create a query like “What goods are supplied by Moscow suppliers” or “Which of the St. Petersburg suppliers of cigarettes sold the largest batch in the last quarter.” The result of Access processing such a query is a table called Dynaset. This table includes data blocks selected from the main table (or several tables) that satisfy the query criteria. Dynaset is a dynamic, temporary data set, so each time a query is executed, it is built again based on “fresh” tabular data.

There are two types of requests:

QBE queries (Query by Example). The user defines them by specifying individual parameters in the design window using hints (samples).

SQL queries (Structured Query Language). The user formulates them using instructions and functions, building a description. An Access QBE query can easily be translated into a corresponding SQL query. The reverse operation is also easy. In general, Access doesn't care what type of query the user is working with.

Queries can be created using the Query Builder, as well as without the Query Builder. However, the first method speeds up the design of several special types of queries.

Selection criteria are instructions by which the user tells Access which blocks of data should be selected upon request and displayed in Dynaset. Criteria can be specified for one or more request fields.

The user can use the query to perform calculations on blocks of data. He can set in each field some function that processes the contents of this field. The processing result is displayed in Dynaset. The processing function is specified in the Total line, which appears after clicking the button with the Greek letter “sigma” in the icon menu. The function itself can be selected in this line by expanding the list of possible values.

Once a query has been designed, it can be executed by clicking on the button with an exclamation mark in the icon bar or by calling the Run directive in the Query menu. Access displays the blocks selected by query in Dynaset in the form of a table.

Using the structured SQL query language within Access, the user can formulate queries that are as complex in the structure of criteria and calculations as they like. The same language allows you to control the processing of requests. An SQL query is a sequence of statements that can include expressions and calls to aggregate functions.

If the user wants to have the developed query project at his disposal during subsequent work sessions, he must save it using the Save directive in the File menu. If the project is being saved for the first time, the Save As dialog box will appear after accessing this directive. In it, you should assign the request a name under which it will be saved and included in the list of requests in the data bank window. The query name must not be the same as the table name.

If this query has already been saved once and the user selects the Save directive, then the old version will be replaced by a new, modified query draft. If the changes should not spoil the previous version, but should be saved in another request, then you should call the Save As directive and assign a new name to the saved project. To save the tabular representation of the query, select the Save Query directive from the File menu. This directive saves changes to the existing request's draft and replaces the previous version of the request with the active version. To save a query for the first time or to create a copy of an active query whose Dynaset is visible on the screen, select the Save Query As directive.

In addition to selection queries, Access can also implement action queries, parametric queries, and crosstab queries.

Forms.

Viewing the database as a table in fill mode gives the user the opportunity to evaluate the database as a whole, compare records, etc. Often, however, there is a need to work with individual database records. In this case, the presence of other entries on the screen (as is the case in fill mode) only interferes and distracts. Working with individual records using forms allows you to focus only on the relevant information.

A form is a form that needs to be filled out, or a mask that is placed over a data set. The form allows you to simplify the process of filling out the database, making it possible to entrust the entry of information to low-skilled personnel. The form mask allows you to limit the amount of information available to the user accessing the database.

The appearance, structure and operating modes of individual control elements and forms are determined by the values ​​of the characteristics of these objects. The characteristics of objects are available to the user through the characteristics window. To set or change the characteristics of an element, you must mark it and call the Properties directive from the View menu.

To display a tool window, set the Toolbox option in the View menu. Each tool, or more precisely, the control/design element created with its help, has its own characteristics window, and in it the standard settings for the corresponding control element are presented in the form of a list.

The fields included in the form can be selected from the list of fields that will be visible on the screen after accessing the Field List directive in the View menu.

When designing a form, you can insert control elements into it. Each control element can be associated with some object in the table on which the form is based.

Reports.

A report is information that you have formatted according to your specifications. The report allows you to extract and present data as meaningful information that you can use and share. Examples of reports include mailing addresses, invoices, sales amounts, or telephone lists. Using Microsoft Access, you can design a report that will present information in the form you want. You can use many different design elements such as text, data, pictures, lines, fields, and graphics to create your report. It is up to you how and what elements to use to build the report.

Microsoft Access provides several ways to retrieve information from your database - using a query, form, or report. You can choose the method that best suits your problem.

Reports are also an effective means of printing out regularly used information. You can create a draft report and save it for future use. The draft report will remain unchanged, but you will receive a printout of the current data each time.

Before you create a report, think about where your report will pull its data from. If all the data is from one table, then your report will be based on this table. If you need data from more than one table, then your report should be based on a query.

You can create reports both with and without the help of the Report Designer. The designer will greatly speed up the process of creating a report, since it will do the main work for you.

You can embed controls into the report prototype during the design process. Controls built into the report prototype can be used to design headings, labels, and illustrations.

To embed control elements in a report, the Toolbox tool menu must be present on the screen (View/Toolbox option). In the tool menu, select the button of the control element to be created. After this, you should click with the mouse in the position of the report prototype where this control element should be placed. Access places the selected element, assigning it default characteristic values ​​(size, color, etc.).

To change the standard values ​​of the characteristics of a control element, you can access the characteristics window, having previously marked the custom element.

A dependent control in a report is associated with a field in the corresponding table. Using a dependent control element, you can enter data into a field or display and update it in a report.

Binding a control element can be done in one of two ways:

If the user wants Access to automatically create a control element associated with the base table for this report, then they should use the list of table fields. It is visible on the screen if the View/Field List option is set. A control made using a field list has the same characteristic values ​​as the table field it is associated with. To perform linking, you must drag the selected field or fields from the list to the report. There you must place the mouse cursor in the position where the upper left corner of the control element (but not the field of the named given control element) should be located, and then release the mouse button. Access creates an associated control (text box) for each field selected in the Field List.

If, in retrospect, it is necessary to link an existing, unlinked control element to a field, this can be done using the characteristics window. In this window, after labeling the control element in the report prototype, the characteristics of this element are listed. By entering the name of the field to be linked into this window as the value of the ControlSource characteristic, the linking is performed.

Saving only its project, report structure, without data.

From the File menu, select the Save directive.

If the report has not been named, type the name in the Report Name line.

Click OK.

By saving the result, the complete report - the project along with the data - in a file that you can use in other Windows-supported applications, such as Microsoft Excel.

From the File menu, select Output To.

Follow the instructions in the Output To dialog boxes.

All human life activities are inextricably linked with many information catalogs and databases. A library register, a notebook, addresses on a mobile phone or tablet - this is not a complete list of structured information that we use. But computers love precision, so let's give a definition.

So, database (DB)- a set of logically interrelated data that describes the information state of objects in various subject areas and processed by computer technology.

Database management system is a software and language environment for creating, managing and processing information databases. Purpose of the DBMS:

• working with databases on external (disks, tapes, etc.) and RAM;
• user sharing;
• control of changes, archiving and restoration of databases;
• providing an access language for ;
• utilities for creating, modifying and managing databases.

To put it simply, the database determines information storage technique, and the DBMS provides means for its processing. Additionally, database management systems are divided into systems general use, which can process various data and specialized, developed for a specific subject area or type of information. An example of specialized database management systems are object-oriented ones.

Relational management systems

Since the advent of computer databases, many models of their operation have been created, but relational model turned out to be the most universal. It is a connected set of information tables that guarantees the integrity and minimal redundancy of information. The tabular model turned out to be applicable to most subject areas, and the market for relational database management systems began to develop rapidly. The figure shows an example of the relational structure “Student Database”.

All relational DBMSs support the ANSI SQL language standard and the basic principles of the relational model, which ensures that applications can work on different DBMSs. Additionally, large DBMSs have their own SQL extensions. Examples of management systems for large projects include ORACLE.

The relational model is also successfully used in the development of Internet projects. Examples are MySQL and PostgreSQL DBMS.

NoSQL technology

The increase in volumes of information and the complexity of relationships has led to the emergence of new database management systems. Information is no longer independent from each other, and sometimes it is generally impossible to strictly describe its structure. Relational databases can no longer cope with such tasks. This led to the emergence of a new type of information databases and their management systems, collectively called NoSQL (“no SQL”).

1. "Key-Value". The most popular DBMSs are Redis, Voldemort, Tokyo Cabinet and Dynomite.
2. BigTable DBMS clones. Developed by Google for internal use in the search engine. BigTable is not officially offered, but there is a DBMS based on it. These are Hadoop, Hypertable and Cassandra.
3. Document-oriented. The most popular are Berkeley DB XML, MongoD, eXist and CouchDB.
4. Bases based Used in Neo4j, Sones graphDB and AllegroGraph.

The future of DBMS

The market continues to develop and is increasingly inclined towards the use of NoSQL solutions. Of course, a huge number of software systems of varying complexity are implemented in “classical” SQL, and it is backed by such market giants as Microsoft and ORACLE. But with a high degree of confidence we can assume that NoSQL in the coming years will be able to significantly displace the leaders in the development of complex information systems.

In this article we will look at the types of database management systems.Database – a shared set of logically related data (and a description of that data) designed to meet the information needs of an organization. The following types of databases are distinguished, for example, server and local, relational and non-relational, supporting transactions and without them, etc.

Let's get acquainted with the most popular and relevant currently database management systems.

Based on the method of accessing the database, the following types of database management systems are distinguished:file-server (this technology is now little used, it is based on access to a DBMS via a local network, with the advantage of low load on the server’s CPU; this type of database includes Microsoft Access, Paradox, dBase, FoxPro, Visual FoxPro),client-server (access to the database is carried out directly, since the DBMS is hosted on the server together with the database, the disadvantage is increased hardware requirements; this type of database includes Oracle, Firebird, Interbase, IBM DB2, Informix, MS SQL Server, Sybase Adaptive Server Enterprise, PostgreSQL, MySQL, Caché, LINTER),built-in (they are part of a software product, do not need to be installed independently, are not designed for a large number of users, are intended for local storage of information, this type of database includes OpenEdge, SQLite, BerkeleyDB, Firebird Embedded, Sav Zigzag, Microsoft SQL Server Compact, LINTER).

Basic and popular relational databases:SQLite, MySQL, PostgreSQL . However, let's look at the main differences between them and the functionality of each, depending on what development goals you are pursuing.

Let's start withSQLite– a fast and powerful database management system that can be easily integrated into applications. It has a large set of tools for working with files, compared to other DBMSs, since it itself is essentially a file. Uses technology of serving libraries, resulting in a very fast system. The system is open source - Open Source.

SQLite data types: NULL, INTEGER, REAL, TEXT, BLOB. The advantages of SQLite include: the ability to quickly and easily transfer a database from machine to machine, because SQLite consists of a file; SQL standards are used, although some of their capabilities are not taken into account; perfectly demonstrates its functions during development and testing. But like any other system SQLite has disadvantages: there is no user system, which is used by large DBMSs; There is no function to increase productivity.

Why use SQLite? First of all, for built-in applications, if you need them to be easily portable and scalability is not important, for example, these could be games or mobile applications; to have direct access to the disk; for testing to slow down the application.

Everyone who has at least some connection to the web industry has probably heard about this system -MySQL – very popular and full-fledged DBMS, which is distinguished by its wide functionality. The server DBMS works perfectly with different types of sites and applications. However, the system is very easy to use as there is a lot of information and documentation provided on learning MySQL. In addition, many plugins and extensions have been developed that further facilitate working with this system. All SQL functionality is not implemented in MySQL, but there are plenty of tools for developing web applications. Data types: TINYINT, SMALLINT, MEDIUMINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL and others.

Advantages of MySQL: simplicity and ease of working with this DBMS; many useful functions; ease of scaling; fast and safe work.

Disadvantages of MySQL: slow development of the project, which often causes complaints from users; Sometimes there are reliability problems and there are some restrictions on the functionality that individual applications need.

What is MySQL for?? If your project lacks the functionality of SQLite, MySQL will be an excellent solution for you, as it has free access to the source code and many functions. It has a good data access protection system, which ensures security. MySQL is time-tested and well suited for developing custom solutions.

The third, but no less popular in its circles -compared to the previous ones, a more professional DBMS, which is freely distributed and meets SQL according to the maximum criteria. The system supports a relational approach to the database. For example: Atomicity, Consistency, Isolation, Durability (ACID). Postgre offers great performance, and the system is easy to extend using so-called stored procedures. This DBMS is not as popular as MySQL, but it is very easy to install using standard OS package managers. Data types in Postgre: bigint, bigserial, bit, bit varying, boolean, box, bytea, character varying, circle and others.

Pros of using PostgreSQL: free and open source software; a large community where you can get the necessary support on issues of interest; a huge number of additions; In addition to the fact that this DBMS is relational, it is also object-oriented.

Disadvantages of PostgreSQL: may slow down the server compared to competing databases; Not every hosting supports working with this DBMS, since it is not so popular. But in company Hyper Host™ this database is supported, so you can safely choose ours if you use PostgreSQL.

For what purposes is PostgreSQL used?

1)When your information must be intact and stored in a safe place.

2)If you are using complex custom procedures.

3) If you are planning to switch to paid DBMSs, from this system this transition can be done without much effort, since Postgre integrates well with other DBMSs.

4) Postgre has a complex data structure, which gives more opportunities for working with them.

We hope this information has become useful to you and you have already decided which DBMS is needed for your project. But if you still have questions, please contact us, we will be happy to help! We would also like to remind you that the hosting company Hyper Host™ supports work with all of the above systems 😉

When working with the server, the instructions “” and “?” will be useful.

7961 times 2 Viewed times today