Any modern organization dealt with a huge amount of data that are entered by users of various corporate applications, and then apply to employees to obtain reference information, printing documents, analyzing certain aspects of the company. Often the competitiveness of the company and the effectiveness of its activities directly depend on the relevance and availability of this data both for employees of the company itself and for its customers and business partners, therefore the task of storing corporate data is now relevant as ever.
Corporate data of most companies are usually stored in databases managed by server DBMS. It is about them that will be discussed in this article.

What are the database management systems

akaya Category of Software, as a database management systems, there has been no longer one ten years - the storage task has occurred almost simultaneously with the advent of first data carriers, and the principles on which the work of most modern DBMS was based on the 60s of the past century. This product category exists almost for all platforms, starting with pocket computers and mobile phones and ending with mainframes and supercomputers, and is used absolutely in all enterprise management systems and in other corporate applications, as well as in many other applications.

Database management systems can be classified in various ways - according to the principle of data organization (relational, hierarchical, network ...), according to the access method (single-user and multiplayer), architecture (desktop and server), according to the functionality and ability associated with scalability.

Currently, relational DBMSs with the client-server architecture are most often used, although it is still possible to meet applications using desktop DBMS. Databases controlled by desktop DBMSs usually represent a file or file set, and the DBMS itself is a set of libraries with which applications that apply them can make changes to these files. Sometimes desktop DBMSs are also supplied with a shell to create tables and other objects and even to develop simple applications, for example in the case of Microsoft Access., most popular at present, it is the shell, and not a library is a commercial product.

However, corporate applications for large and even for medium enterprises, as a rule, use server DBMS due to their greater reliability and scalability. In the simplest case information system, based on the client-server architecture, consists of two main components: database servers, managing data and performing requests from client applications, and client applications themselves providing user interface and sending requests to the server. It is the database server that can manipulate the files in which the data is stored, perform user requests, maintain the reference data integrity, provide access to them, back up data and log operations associated with their change. In more complex cases, an intermediate link application (middleware) can be located between the client and the server, the functions of which are very diverse - from the distinction of access to data before implementing a sufficiently complex business logic.

Requirements for modern DBMS

the following requirements are presented with modern DBMS:

scalability - the lack of a significant reduction in the speed of execution of user requests with a proportional increase in the number of requests and hardware resources used by this DBMS (such as the amount of RAM, the number of processors and servers);
availability - the ability to always execute the request;
reliability - minimum probability of failures, availability of data recovery tools after failures, tools reserve copy and duplication of data (Recently, the DBMS manufacturers often offer tools that allow such operations, without interrupting users' operation);
handling - Easy Administration, Availability of automatic configuration (Typical Modern Administration Tools Includes Databases and Object Tools, Data Replication Rules Tools Between Different Servers, User Management Utilities, Groups and Rights, Event Monitoring Tools, Execution Plans requests, migration utilities from other DBMS);
availability of data protection from loss and unauthorized access;
support for data access using Web services;
support for standard data access mechanisms (such as ODBC, JDBC, OLE DB, ADO .NET), which allows you to create applications for DBMS using various development tools.

The discrepancy between the DBMS of any of these requirements leads to the fact that even a good-to-other consumer properties of the DBMS field of application turns out to be very limited. Thus, the DBMS with poor scalability, which has successfully used with a small amount of data being processed, is unsuitable in the event of an increase in their quantity. The absence of the necessary administrative utilities also often leads to the abandonment of the use of some DBMS. Bad scalability and accessibility entail an additional cost of working time employees, downtime, as well as loss by customers, desperately wait for a response to a question set by phone or a request entered on the Web site. The lack of support for the server code leads to the fact that the DBMS, successfully applied on the corporate Web site, cannot be used to create other internal corporate applications. The lack of support for standard data access mechanisms can automatically lead to the fact that using the development facilities focused on these mechanisms will refuse to apply this DBMS.

That is why the leaders of the corporate DBMS market seek to produce products that meet all the above requirements. In addition, as a rule, such products exist for several platforms, and often in different editions (including even free) intended for solving various tasks.

DBMS of leading manufacturers

this series of analytical reports (see, for example: Strange K. Magic Quadrant for Data Warehouse DBMSS // Gartner Research Note M-22-2154. 2004), DBMS market leaders on this moment They have long and successfully presented on the Russian market of IBM, Oracle, Microsoft, Sybase and Teradata, as well as a relatively young Netemza company, which is almost unknown on the domestic market. Next, we will briefly consider the features of these companies.

DB2 Universal Database (IBM)

The IBM Server DBMS family, known as the DB2 Universal Database (DB2 UDB), first appeared in 1996 and was intended for UNIX, OS / 2 and Microsoft Windows NT platforms. Latest version This product, DB2 UDB 8.2, operates running operating systems AIX, AIX, LINUX, HP-UX, SUN and Windows.

The features of this DBMS are developed means of self-restoration and automatic execution of operations related to database support (such as creating backup copies and data defragmentation). From DB2 access mechanisms, DB2 UDB supports ODBC, JDBC, ADO / OLE DB, ADO .NET. This DBMS allows you to create a server code on CLR compatible languages, such as C # and Visual Basic .NET.

DB2 Universal Database supports the creation of data warehouses and in addition to the data transfer tools in the store contains a data warehousing tool to manage all processes related to creating and supporting data warehousing, including the creation of a repository scheme, defining source data sources for it, creating Schedules of data update in the repository, checking and correcting incorrect data.

To other most important technical specifications, DB2 Universal Database include support for relational and comprehensive data using object extensions, the ability to work on multiprocessor platforms, cluster support, 64-bit memory architecture, parallelization of requests, availability of means for distributed administration and data processing, support for distributed transactions. Some of these technologies were borrowed from the INFORMIX Dynamic Server DBMS acquired by IBM with Informix a few years ago.

Oracle 10G (Oracle)

Oracle, which appeared in 1979, was the first commercial relational DBMS, which supported the SQL language, which now became an industrial standard. Early versions of this DBMS were created for mainframes, but in 1985, Oracle versions appeared, intended for use in the Client-Server architecture. It was the first DBMS used by the means of parallel computing provided by some server platforms.

Currently, its last version is Oracle 10G. This DBMS meets the most stringent service quality requirements, has clustering capabilities (for this purpose, the Oracle Real Application Clusters module), developed safety facilities.

Oracle 10G DBMS provides parallel processing tools, built-in OLAP tools, quick removal tools, convert and download data, portal services, business analysis tools, distribution of reports and analyzing user actions, DBMS performance analysis tools, ensures cluster support.

ORACLE 10G data access mechanisms support ODBC, JDBC and ADO / OLE DB, in addition, for this DBMS there is a .Net provider and a corresponding set of classes from Microsoft. The Oracle client part also contains a set of COM objects to access data (Oracle Objects for Ole). Note that many other companies produce ODBC drivers and OLE DB providers to access Oracle - this DBMS support all the leading manufacturers of developments, report generators, analytical data processing tools.

Both in the West and in Russia are often used ready corporate solutions From Oracle Corporation, united under the general name of Oracle Applications, such as Oracle Financials, Oracle Human Resources, Oracle Market Management, Oracle Project Systems, etc., - Actually, selling and supporting these applications and make up the main part of the company's business.

SQL Server 2000 and SQL Server 2005 (Microsoft)

First version of Microsoft. SQL Server - Joint product of Microsoft and Sybase, released in 1988, was designed for the OS / 2 platform. The subsequent versions of this database server were intended for the Windows platform and over time were closely integrated with this operating system. For other version platforms this server Not issued and not produced.

The most frequently used version of this DBMS - Microsoft SQL Server 2000 is characterized by increased scalability and performance. It allows you to use several simultaneously running servers on one computer, supports the creation of clusters, parallel calculations in multiprocessor systems, distributed requests. Microsoft SQL Server 2000 administration tools are considered one of the easiest and most convenient. In addition to the means of solving standard administration tasks, such as creating databases and their objects, user privileges management and data privileges, backup and recovery, data replication, they include data exchange utilities with other DBMS and data warehouses. This product includes an OLAP server - it was from its appearance as part of SQL Server that the OLAP funds began into the DBMS of other manufacturers and move them from the category of expensive, elite products to the product category, widely used even in small companies.

From data access mechanisms, this product supports ODBC, OLE DB / ADO, ADO .NET. There is also a JDBC driver manufactured by Microsoft. It is impossible not to note the magnificent means of integrating this DBMS with Microsoft Office., Up to the instruments of the manipulation of OLAP data from these applications.

Subsequent version of this DBMS - SQL Server 2005, the release of which is expected this year will support asynchronous messaging between various server processes, contain customer notification services about events in the database, allow you to create clusters-resistant failures and make database mirrors, produce replication With databases managed by the DBMS of other manufacturers, perform operations such as changing the number of processors and memory volumes without stopping the server.

Another major technological innovations of the expected version of SQL Server is integration with Microsoft .NET Framework 2.0 at the Common Language Runtime level in the DBMS itself, which will create a server code on any CLR-compatible programming language, such as C # or Visual Basic. We also note the substantial expansion of the analytical capabilities of this DBMS, for example, the emergence of new Data Mining algorithms.

SQL Server 2005 will be created as not only 32-, but also a 64-bit version that supports Intel ITANIUM and AMD Opteron processors. And to decide on simple tasks using small databases, the free version of Microsoft SQL Server 2005 Express Edition will be released.

Adaptive Server Enterprise and Adaptive Server IQ (Sybase)

Sybase server products come from one of the early Microsoft SQL Server versions created by Microsoft and Sybase. Since 1994, Microsoft and Sybase are developing their server products independently of each other, and the result of Sybase's activities in this direction is the Adaptive Server Enterprise product (ASE), the latest version of which, 12.5.2, exists for Windows NT / 2000 and some versions UNIX and LINUX.

Currently, Adaptive Server Enterprise supports proactive asynchronous reading, which increases the speed of complicated requests; Creating clusters; the presence of a 64-bit version; distributed request processing, including other manufacturers; Extended stored procedures allowing easy access to the functions that are written in languages \u200b\u200bother than SQL; parallel processing of queries in multiprocessor systems; parallel operation of administration utilities; Dynamic server configuration and backup data with compression. It is especially worth noting the Sybase Real-Time Data Services services, intended for the organization of real-time messages when any events occur in databases, including in databases managed by the DBMS of other manufacturers.

From Universal Access Mechanisms ASE Supports ODBC, JDBC, OLE DB / ADO. Note that often together with ASE uses the development and design of the data of Sybase itself.

To create multidimensional data warehouses, Sybase has another server product - Adaptive Server IQ, which allows you to create storage based data from the production DBMS not only Sybase, but also from other manufacturers. The high speed of queries in this product is due to a non-standard storage method. We also note that there are a number of products under the general name of Sybase Industry Warehouse Studio, serviced by the maintenance of specific subject areas.

TeraData Database V2R6.0 (Teradata)

Over the past few years, Teradata's DBMS is considered to be leading products among DBMSs intended for creating data warehouses. The latest version of this DBMS, Teradata Database V2R6.0, is available for Microsoft Windows, OS / 2 and a number of UNIX and Linux versions.

The difference of this DBMS from other products is a special mechanism for parallel processing of requests, which involves breaking the task to a number of individual subtasks and their parallel execution. At the same time, even operations such as sorting or grouping data can be separated into several tasks and are executed in parallel - this is achieved due to a special storage method, in which parts of the database are also divided between subtasks. The subtasks themselves can be distributed among the available processors, which may be more than one. Thanks to the mechanism of parallel solving tasks, TeraData Database V2R6.0 is characterized by high scalability.

To create client applications using Teradata Database, there are an ODBC driver, a documented client API, compatible with IBM DB2, and a number of other software interfaces. Tools of this DBMS include backup and recovery, viewing and editing data, performance monitoring and load, as well as the administration utility to manage all the following tasks available for different versions Windows.

Netezza Performance Server 8000 Series (NETEZZA)

NETEZZA Performance Server is a hardware and software solution, which is a server with a data storage system and a Linux operating system pre-installed on it, as well as an open source DBMS created on the basis of Postgress. NETEZZA declares that this solution is not inferior to the performance of TERADATA-based solutions at a noticeable cost. Among the features of this hardware-software solution - support for parallel data processing, separation of tasks by separating the parts of the database between subtasks, high scalability.

From the programming interfaces, this solution supports universal access mechanisms for ODBC and JDBC.

***

In conclusion, we note that the possibilities of modern DBMS leading manufacturers reflect the current needs of corporate applications. These include the ability to form clusters, maintain parallel data processing in multiprocessor systems, provide software interfaces for the most popular tools for developing applications, create analytical applications. An important features of modern DBMS of leading manufacturers are also simplicity of administration and the ability to perform a number of database support operations without interruption, quite high performance and availability indicators, as well as the presence of several editions to solve various problems.

Among modern Systems Database management allocate relational DBMS to which include:

MS Access,
Visual FoxPro,
MySQL,
PostgreSQL,
Sybase
SQL Server
Oracle
and etc.

Consider the most common of them.

DBMS MS ACCESS.

The Access program operates running the Windows operating system and has a standardized Windows application interface.

The main component is databasewhich can contain tables, reports, requests, forms, modules and macros.

Processing information in the process of working with database is carried out using macros or VBA. programs.

An open database can exchange data with external databases. An external database may be any database that supports the ODBC protocol and is located on a remote server, or one of the DBDD DBDD ACCESS, DBASE or PARADOX.

Access allows you to create and perform inquiries To select, add data, delete and update.

Request can be created using QBE or SQL. Access program supports mechanism Ole. (binding and embedding objects) and mechanism DDE (Dynamic data exchange).

DBMS Visual Foxpro.

Visual FoxPro DBMS contains developing databases, organizing requests to them, creating applications with visual, object-oriented programming. Visual FoxPro DBMS is running Windows.

Database Visual FoxPro is a set of related tables. The database defines the conditions for its integrity through primary and external keys Tables. All changes that occur in the database are detected and centrally processed with triggers and embedded procedures programs.

Visual FoxPro characterizes high database service.

With the help of Standard ODBC. and SQL- Visual FoxPro data sampling can work with dBASE, Paradox, Access databases, etc., with database servers - Oracle MS SQL Server, etc.

There is simultaneous operation of Visual FoxPro application with its own and network tables that are located on other computers on the local network.

Visual FoxPro supports mechanisms Ole. and DDE Windows applications.

Visual FoxPro allows you to create network applicationsthat operate in networks running MS LAN Manager, MS Windows, etc.

MS SQL Server

Microsoft SQL Server is widely used in the database area and to analyze data, allows you to quickly create scalable solutions E-commerce, business applications and data warehouses. SQL Server supports language Xml and protocol Http., Means of increasing availability and speeds that allow you to ensure uninterrupted operation and distribute the load, configuration and control functions.

Analysis platform sQL data Server, which is integrated with MS Office, allows you to open access to the required business information using the MS Word and MS Excel interface.

SQL Server includes a developed, convenient and functional programming environment, which includes tools for working with web services, data access technologies.

Oracle

Oracle includes DBMS and data development and data analysis.

Oracle includes a database, an integration platform, application server, management tools unstructured data and analysts.

The Oracle Database DBMS allows you to automate administrative tasks, ensures security and compliance with the regulatory and legal acts of information protection, contains control functions and self-diagnostics. The characteristics of the system include managing large amounts of data by using compression and distributed tables, effective protection data, the ability to integrate geophysical data and complete recovery, etc.

Review of modern DBMS

A wide need for automated processing of the mass media has multiple the need for specialized data processing languages. Such tools are usually included in the database management systems. DBMS packages make it possible to manage data directly in interactive mode, as well as allow programmers to develop more perfect software Their processing are software applications.

In the most complete variant DBMS packages must have the following components:

1. User Wednesday, Giving the Opportunity Directly Database Management.

2. Algorithmic language for programming data processing applications.

3. Compiler to give the completed program of the finished commercial view, as an EXE file.

4. Quick programming programs routine operations, such as Form, Menu.

The DBMS is a custom shell-focused on the immediate management of user requests. However, for the domestic user, this represents a smaller significance due to the difficulty of mastering the English-language interface. The presence of a programming language in the DBMS allows you to create complex data processing systems for specific tasks. A group of relational DBMS is presented in the market of software products wide enough. This, for example, systems like Paradox, Clipper.

The main feature of the DBMS is the presence of procedures for entering and storing not only the data themselves, but also descriptions of their structure. Files equipped with a description of the data stored in them and managed by the DBMS began to call data banks, and then "databases" (dB).

Databases (database) are a set of data structured in a certain way on a certain subject applied in various sectors of the economy, industry, business and science. Access speed K. necessary informationThe efficiency of obtaining it is determined, ultimately, successful business management and reduce the costs of relevant income articles. Methods that facilitate work with a large amount of data are developed: Creating databases, search for search, editing, extracting various samples, as well as conducting data analysis and calculation of calculations that contain final documents and data in the form of tables, charts and graphs.

These methods are implemented using a software package that provide work with the database system - the database management system (DBMS).

There are many software products that allow you to create and work with the database, for example, Access, Clipper, Excel and others. Among the great variety of programs, the FoxPro DBMS is most popular, which according to its characteristics satisfies the highest requirements for such type systems both by level and volume and in terms of information processing.

At the moment, Visual FoxPro is developed and widely used for Windows versions 3.0 and 5.0. However, working with these packages for non-programmers is a rather complicated task. Therefore, to create a database for users who have little experience in programming, are very successful versions 2.5 and 2.6 under Windows and 2.0 under DOS.

Database structure:

The database is a set of homogeneous and, as a rule, ordered by some criterion of information. The database can be represented both in "paper" and in computer form.

A typical example of a "paper" database is a library catalog of paper cards, containing information about books. The information in this database is homogeneous (contains information only about books) and ordered (cards are placed in alphabetical order the names of the authors). Other examples of a paper database are telephone directory and train schedule.

Computer database is a file (or set related Files) containing information that is often called the data file. The data file consists of entries, each of which contains information about one instance. Records consist of fields. Each field contains information about one instance characteristic. It should be noted that each entry consists of the same fields. Some fields may not be filled, but still present in the record. On paper, a database is convenient to represent in the form of a table. Computer database information is usually displayed on the table as tables. Therefore, often instead of the phrase "data file" use the phrase "data table" or simply "Table".

Borland C ++ Builder Development Environment.

To create an autonomous workplace, you can select the C ++ Builder language software, which is one of the most famous DBMS. The software products market have many tools for programming automation. But only Borland Delphi and Microsoft Visual Basic can compete with the capacity and ease of use with the Builder environment.

"C ++ Builder" is a powerful system of visual object-oriented programming, which allows you to work both with simple local databases, and with multi-sized database distributed databases. She herself and supplied with her software products Allow to solve the next range of tasks:

1. Quickly create professionally looking window interfaces for applications even novice programmers.

2. Create applications of any complexity and any destination, be it office, accounting, engineering, information search applications.

3. Create a convenient interface to any previously created programs.

4. Create your own DL-component libraries, which can then be used in other programming languages.

5. Create systems for working with local and remote databases of any types.

6. Create a database of various types using the C ++ Builder (DatabaseDesktop) toolkit.

7. Format and print from the application complex reports, including tables, graphics, most of the same designation.

8. To contact your application with Microsoft products like Word, Excel, etc.

9. Create a help system, both for its applications and for others.

Borland C ++ Builder 6 is a program created to manage data - cataloging, support, information processing and much more. Although you can perform many database operations through the menu and interface system, mastering the extensive capacity of Borland C ++ Builder 6 requires some knowledge of the programming language underlying.

Applications in the Borland C ++ Builder 6 environment are built in the form of special designs - projects that look for the user as a set of multiple files. No program can exist outside the project structure. Project Management Actions is carried out by a special software package - project manager.

Justification of the choice of technical means

Minimum system requirements:

2. Microsoft Windows 98 operating system, Windows Millennium (ME), Windows 2000 and Late versions of Microsoft Windows operating systems.

3. The amount of RAM should be at least 128 MB (256 MB recommended).

4. 115 MB free disk space.

5. VGA or more a high resolution monitor.

6. Mouse, keyboard.

7. Hard disk space required for full installation: 675 MB (ENTERPRISE EDITION); 580 MB (Professional); 480 MB (Personal)

Today it is quite difficult to imagine any application that would not use the database, be it server, personal computers or mobile devices. From simple games to serious business applications. All of them handle, read and write down a specific set of data.

Database Management System (DBMS / DBMS) - softwareintended for storing and managing data. To solve various problems, more and more different DBMS (relational and NOSQL) and programs for working with them (MYSQL, PostgreSQL, MONGODB, REDIS, etc.) were developed

Models of database
1. Relational model
2. Bevelless approach (NOSQL)
Popular DBMS
1. Relational DBMS
2. NOSQL (Newsql) DBMS
Comparison SQL and NOSQL database

Database Management Systems

Term DBMS Includes a rather large number of tools that differ from each other to work with databases (individual programs and connected libraries). Since these data are different types and types, starting from the second half of the 20th century a huge number of different DBMSs and other applications for working with the database.

Comparison SQL and NOSQL database management systems

To present the general picture, let's compare these two types of DBMS:

Data role structures and their types - relational databases use strict data schemes, NOSQL databases allow any data type
Requests - regardless of the type of license, relational base Data to one way or another comply with SQL standards, so these of them can be obtained using the SQL language. The NOSQL database use specific ways of requests to data.
Scalability - both of these types of DBMS are quite easy to vertical scaling (i.e., an increase in system resources). Nevertheless, since Nosql is a more modern product, it is such a DBMS that offer more simple ways Horizontal scaling (i.e., creating a cluster of several machines).
Reliability - When it comes to the preservation of data and guarantees the execution of SQL transactions, the database still occupy a leading position.
Support - relational DBMSs have a non-small history by the shoulders. They are very popular and offer both paid and free solutions. If there are problems, it is still much easier to find an answer if it comes to relational systems than NOSQL, especially if the solution is quite complicated by nature (for example, MongoDB).
Storage and access to complex data structures - initially relational systems assumed work with complex structures, which is why they are superior to the remaining performance solutions.

Relational databases have long been used in programming. At one time, they have gained popularity due to the simplicity and convenience of relational model of working with data.

This article analyzes the differences between the most popular relational database management systems (DBMS): SQLite, MySQL and PostgreSQL.

Database Management Systems

Databases are logically modeled storage various information (data) of all kinds. Each SQL database is based on a model that provides the structure for data stored in it. Database management systems are applications (or libraries) that control databases of various shapes, sizes and species.

Relational database management systems

Relational DBMS to work with data use relational model. This model stores any information in the tables in the form of related entries with attributes.

This type of DBMS requires the presence of structures-tables. Columns (attributes) of such a table contain various types of data. Each database entry is perceived as a string in the table, the attributes of which are represented as columns.

Relationships and types of data

Relationships can be considered as mathematical kits containing a number of attributes that together are databases and information stored in it.

By adding an entry into the table, you need to distribute all its components (attributes) by data types. Different relational DBMSs use different types data, and they are not always interchangeable.

Such limitations (as, for example, with data types) are typical for relational DBMS, because, in fact, relationships between data and are built on the basis of restrictions.

Note: NOSQL databases do not have such strict restrictions, since they do not build such relationships between the data. To learn more about Nosql, read.

Popular relational databases

In this article, we will look at the three most important and popular DBMS with open source.

SQLite: Built-in powerful database management system.
MySQL: the most popular and widespread database.
PostgreSQL: Advanced SQL-compatible Open Source Object DBMS.

Note: Open source applications almost always give users the right to free use and changing the code. Branch code, you can create a completely new application. One of the MYSQL branches, for example, is MariaDB.

SQLite.

SQLite is a productive library that can be embedded in applications. The full database based on SQLite files offers a wide range of tools for processing all types of data and imposes much less restrictions than other relational databases.

Applications using SQLite do not interact using the interface (ports, sockets), and send direct requests to the file in which data is stored (for example, SQLite database). Thanks to this, the SQLite application is very fast and productive.

SQLITE data types

NULL: Empty value.
Integer: An integer value (depending on the volume, the value is stored in 1, 2, 3, 4, 6 or 8 bytes).
Real: The floating point number is stored as an 8-byte IEEE.
Text: text string, stored in encrypted form (UTF-8, UTF-16BE or UTF-16LE).
Blob: binary data is stored in the form in which they were introduced.

The advantages of sqlite

Simple File-based Building: The entire database consists of only one file, which increases its portability.
Standards: Despite the simplicity, the SQLite system is based on SQL. Some functions are omitted (Right Outer Join or for Each Statement), but others have been added instead.
SQLite is great for developing or testing. At these stages, it is almost always a simple, but scalable solution.

Disadvantages of SQLite

No user management. More complex DBMS support user management (their relationships, privileges, etc.). Simple SQLite DBMS such a function does not provide.
It is impossible to increase productivity. The SQLite library is easy to configure and use. However, it is designed in such a way that it does not allow thin setting Get additional performance. That is, make SQLite more productive technically impossible.

When it is better to use sqlite

Simple embedded applications that need portability, such as single-user local applications, mobile applications, games.
Replacing the disk. Usually applications that need to read or record files to disk can use SQLite to obtain additional functions.
Testing.

When it is better not to use sqlite

Multiplayer applications. If the application is built in such a way that a large number of customers simultaneously uses one database, then the application is better to implement a full-featured relational DBMS (for example, MySQL).
Applications that record a large amount of data. Record operation is one of SQLite restrictions. This DBMS allows you to perform only one recording operation in one point in time, therefore, it limits the bandwidth.

Mysql

MySQL is the most popular DBMS. This is a multifunctional open application that supports the work of a huge number of sites. The MySQL system is pretty easy to work and can store large data arrays.

Note: Given the popularity of MySQL, a large number has been developed for this system. third-party applications, Tools and libraries.

MySQL does not implement a complete SQL standard. Despite this, MySQL offers many functionality for users: a standalone database server, interaction with applications and sites, etc.

MYSQL Data Types

Tinyint: An integer in the range from -128 to 127 (1 byte).
Smallint: An integer from -32768 to 32767 (2 bytes).
Mediumint: number from -8388608 to 8388608 (3 bytes).
INT or INTEGER: The range ranging from -2147683648 to 2147683648 (4 bytes).
Bigint: Number from -2 63 to 2 63 -1 (8 bytes).
Float: Floating point number (4 bytes).
Double, Double Precision, Real: Double accuracy and floating point number.
Decimal, Numeric: Increased values.
Date: date.
DateTime: Date and time.
TimeStamp: Temporary label.
Time: HH: MM: SS time.
Year: year (default is stored as 4 digits, but you can configure 2).
Char: String fixed length.
Varchar: Lines of variables.
TinyBlob, TinyText: Type Text allows you to store text, and blob - images, sound, electronic documents etc. Maximum length - 225 characters.
BLOB, TEXT: Large text volumes, maximum 65535 characters.
MediumBlob, MediumText: similar to the previous one, but maximum up to 16777215 characters.
LongBlob, LongText: similar to the previous one, but maximum up to 4294967295 characters.
ENUM: Takes only one of the values \u200b\u200bof the specified set.
SET: accepts any or all elements from the values \u200b\u200bof the specified set.

The advantages of mysql

Easy to work: MySQL is very easy to install and configure. Third-party tools, including visualizers (interfaces) greatly simplify work with data.
Functionality: MySQL supports a huge number of SQL functions.
Security: MySQL provides many built-in advanced data protection features.
Scalability and performance: MySQL can work with large data volumes.

Disadvantages of MySQL

Restrictions: MySQL structure imposes some restrictions, due to which advanced applications will not be able to work.
Vulnerabilities: The data processing method used in MySQL makes this DBMS a little less reliable compared to other DBMS.
Slow Development: Although MySQL is an open source product, it develops very slowly. However, it should be noted that several full-fledged databases are based on MySQL (for example, MariaDB).

When use MySQL

Distributed operations: autonomous database server mySQL data Supports many operations and provides several additional functions.
High Data Security: MySQL offers high defense data.
Websites and web applications: Despite the MYSQL constraints can support almost any site and web application. This flexible and scalable tool is easy to use.
Custom solutions: MySQL can be adjusted under the strict requirements of the site or application.

When it is better not to use mySQL

Conflicts with SQL: Since MySQL still does not fully implement SQL standards, it is not fully compatible with SQL. Because MySQL can not always be integrated with another DBMS.
Weak support for parallelism: despite the fact that MySQL performs the reading operations well, simultaneous read and write operations can cause problems.
Lack of some functions (for example, full-text search).

PostgreSQL

PostgreSQL is an advanced open object-oriented DBMS. PostgreSQL implements ANSI / ISO SQL standards.

Unlike other DBMS, PostgreSQL supports very important object-oriented and relational database functions: Reliable Acid transactions (atomicity, consistency, isolation, durability), etc.

Based on reliable technology POSTGRESQL DBMS can simultaneously process a large number of tasks. Support for consistency is achieved without blocking read operations thanks to MVCC.

Although PostgreSQL DBMS is not as popular as MySQL, for it also developed a large number of additional tools and libraries that simplify work with data and increase the performance of the DBMS.

POSTGRESQL data types

bigint: An iconic eight-bit integer.
bigSerial: eight-bind an integer with auto-chip.
bit [(n)]: Bit row of fixed length.
bit Varying [(n)]: Bit row with variable length.
boolean: Logical value (True / False).
box: Quadril on the plane.
bytea: binary data.
character Varying [(N)]: String of characters with variable length.
character [(N)]: String symbols with fixed length
cIDR: IPv4 or IPv6 network address.
circle: Circle on the plane.
date: Date (year, month, day).
double Precision: a floating point of double accuracy (8 bytes).
inet: IPv4 or IPv6 host address.
integer: Markded four-binded integer.
interval [(P)]: time lapse.
line: endless line on the plane.
lSEG: Line segment on the plane.
macaddr: Mac (Media Access Control) Address.
money: Currency.
numeric [(P, S)]: Accurate numeric value with selected accuracy.
pATH: geometric path on the plane.
point: geometric point on the plane.
polygon: Closed geometric path on the plane (landfill)
real: A floating point of single accuracy (4 bytes).
smallint: Signal two-byte integer.
serial: four-bit integer with auto-chip.
tEXT: String symbols with variable length.
time [(P)]: time of the day (no time zone).
time [(P)] WITH TIME ZONE: Time and time zone.
timeStamp [(P)]: Time label (date and time) without a time zone.
timeStamp [(P)] With Time Zone: time zone temporary label.
tSQuery: Text Search request.
tsvector: Text Search document.
tXID_SNAPSHOT: user level ID-transaction snapshot.
uUID: Universal Unique Identifier.
xML: XML data.

Advantages PostgreSQL

PostgreSQL open database management system, SQL-compatible, free.
The active community PostgreSQL will help to find a solution to any problem associated with DBMS, at any time of the day.
Support for third-party tools: In addition to embedded advanced features, PostgreSQL supports many open third-party tools for designing, data management, etc.
Scalability and extensibility.
Object-oriented.

Disadvantages PostgreSQL

Performance: In some situations, PostgreSQL performance is lower than that of MySQL.
Low popularity.
In connection with the above disadvantages, not all hosting providers support PostgreSQL.

When to use PostgreSQL

If the application requires the integrity of the data.
To perform complex user tasks.
If in the future the application will need a more reliable database, with PostgreSQL it will be easier to go.
To support applications with a complex POSTGRESQL structure offers a special feature set.

When it is better not to use PostgreSQL

If the application needs quick read operations.
If the application does not need absolute data integrity, ACID or complex structure, PostgreSQL can become too complex solution.
Data replication is more difficult than in MySQL, because in PostgreSQL clusters it is better not to use.

Tags: