Contents:

Free Course: "Quick Start in Python"
Learn MoreImagine a situation where a table contains tens of thousands of records. A valuable client calls the database and informs it of a change in its phone number. Moreover, the client has, for example, a hundred unresolved issues. This means that the phone number needs to be updated in a hundred records, simultaneously making changes to many rows of the Messages table. This process requires significant time and effort, which highlights the importance of optimizing data management in CRM systems. Updating information must be simple and efficient to maintain customer loyalty and promptly resolve their issues.
To optimize the process, it is important to use a query rather than performing operations manually. However, even when using a query, numerous redundant operations may be necessary due to duplication of information. Effective data management requires minimizing such redundant actions, which will improve performance and reduce query processing time.
Concern for such aspects begins at the database design stage. Effective prevention of such problems is achieved through the process of data normalization, which ensures the correct organization of relationships between tables. Normalization helps minimize data redundancy and improve the integrity of the database, which in turn contributes to more stable and efficient system operation.
What is normalization
To optimize the size of a relational database and prevent inconsistencies that arise when working with it, it is necessary to normalize relations. This process involves dividing data into related tables, which helps avoid redundancies and anomalies. Normalization, or data decomposition, promotes more efficient information management and improves database performance.
Data redundancy is a situation where identical data is stored in multiple locations in the database. This can lead to a variety of problems, including an increase in the volume of stored information, difficulties in maintaining data relevance, and the risk of errors when changing it. Managing data redundancy is an important task for ensuring the integrity and efficiency of the database. Reducing redundancy helps optimize data storage and improve system performance.
The Messages table contains information about clients and operators, including their names and phone numbers. The first and third rows represent calls from the same client, and the first and second rows display responses from the same manager. Therefore, the first and third rows duplicate the name and phone number of the client, Vasya, and the first and second rows show the manager's nickname, "Operator1." This allows for efficient tracking of interactions between clients and operators.
To eliminate duplication of information, it is necessary to separate the Client and Operator entities. Specific attributes for each of these entities should be placed in separate tables. This will optimize the database structure, improve its performance, and simplify data management. Creating separate tables for Clients and Operators will ensure clearer organization of information and simplify the processing process.
The first table (Clients) will store the names and phone numbers of clients, and the second table (Operators) will store information about operators. Each record in these tables will be assigned a unique identifier (id), which serves as the primary key and cannot be duplicated within the table. Using this identifier, we will establish a link to the records in the Messages table. This will allow for efficient data organization and ensure its integrity, which is especially important for managing interactions with clients and operators.
As part of the database structure optimization, two new attributes (foreign keys) will be added to each record in the Messages table, which still represents the "call" entity: id_client and id_oper. These keys will reference the primary keys from the Clients and Operators tables, respectively. Due to this improvement, the name and phone number columns will be removed from the Messages table. This change will improve data integrity and simplify the relationships between entities, which will have a positive impact on query performance and information management.
So, the result will be as follows:

To update the client's phone number in all records, just change one field in the Clients table. This simplifies the data management process and ensures that information remains current and consistent across the entire database.
There are six normalization forms of relational databases, which are ordered by the degree to which they reduce data redundancy. Each normalization form is described by formal rules aimed at optimizing the database structure. In our case, we have brought the relation to the second normal form, which minimizes redundancy and improves data integrity. The use of normalization contributes to more efficient data management and reduces the likelihood of anomalies occurring when updating and deleting information.
Relational Database Management Systems
Data in tables is actively used by database management systems (DBMS). These systems provide efficient storage, processing, and management of data, which allows users to retrieve the necessary information and perform analysis. DBMS play a key role in organizing and structuring data, ensuring its availability and integrity.
Using metaschemas, databases are created, which are then populated with the necessary data. Using these databases, various operations are performed, such as adding, deleting, changing the structure and analyzing information. Effective database management allows you to optimize processes and improve the quality of working with data.
Data Manipulation Languages
Structured Query Language, known as SQL, is the primary tool for interacting with relational databases. SQL allows users to perform various operations, such as creating, reading, updating and deleting data, thereby ensuring efficient information management in relational systems. This language is the standard in the field of databases and is widely used for processing and analyzing data in various applications and systems.
SQL is a declarative programming language, which means that instructions in it are not executed sequentially, as in imperative languages. In SQL, each statement defines a specific action, and the database management system (DBMS) independently decides how to implement this action. This approach allows developers to focus on what they want to achieve, rather than how it should be accomplished, making SQL a powerful tool for working with data.
To retrieve all records from the Messages table for November 10, 2020, you would execute the following SQL query:
Database queries allow you to retrieve information based on specified criteria. An example of such a query is an SQL command that retrieves all messages from a table where the date is November 10, 2020. Using the correct date format and SQL syntax is critical to the successful execution of the query. In this case, the SELECT statement is used to retrieve all columns from the messages table, which can be useful for data analysis or obtaining information for reports. Optimizing queries, including filtering by date, can significantly improve database performance and simplify the process of working with it.
Structured Query Language (SQL) consists of several key components that include different groups of operators. This language provides powerful tools for working with databases, allowing you to perform tasks such as creating, updating, retrieving, and deleting data. Using SQL allows you to effectively manage information, ensuring high performance and query accuracy. Dividing into groups of operators simplifies the process of writing and optimizing queries, making it accessible to both novice and experienced developers.
- define data (DDL),
- manipulate them (DML),
- control access to data (DCL),
- and manage transactions (TCL).
SQL does not natively provide built-in tools for creating printed reports, screen forms, and other tools for software development. Although SQL is not a full Turing-complete programming language, its standard allows for the creation of procedural extensions. These extensions significantly increase the functionality of SQL, allowing for the implementation of more complex algorithms and logic, making it closer to full-fledged programming languages.
SQL syntax may vary depending on the database management system (DBMS) used. In some cases, unique dialects of the SQL language are used, which may affect the structure and functionality of queries.
- T-SQL — for working with Microsoft SQL Server;
- PL / SQL is used to write stored procedures and functions in Oracle;
- PL / pgSQL — in PostgreSQL.
What are object-relational databases?
Object-oriented databases are systems that offer tools for working with object data types. These databases arose as a result of the development of object-oriented programming languages, which allowed for better integration of data and methods into a single structure. Object-oriented databases provide more flexible and efficient data management, making them relevant for modern applications that require complex information processing.
Modern databases and query languages actively use object-oriented programming concepts such as classes, objects, and inheritance. These principles allow for more efficient data organization and simplify interaction with them. Examples of such systems include Oracle and PostgreSQL, which continue to develop and implement these technologies to improve the performance and flexibility of database work.
Which relational databases are popular in web development?
This is an open source database management system (DBMS), which was acquired by Oracle along with Sun Microsystems. According to a 2020 StackOverflow.com survey of 65,000 developers, more than half (55.6%) of respondents use this DBMS in their work.
MySQL has a number of key advantages, including being free and boasting high data processing speeds. This database management system was designed to handle large volumes of information on an industrial scale, making it popular among developers. Thanks to its accessibility and speed, MySQL has become a leading online platform and earned the reputation of "the DBMS for the entire internet." Today, MySQL remains one of the most convenient database management systems for creating web pages and web applications, making it an ideal choice for developers seeking effective data management.
MySQL enjoys significant support among programming language developers. Almost every popular language provides an interface for interacting with this database management system. This makes MySQL a universal tool for application development, providing easy access to database functionality and the ability to integrate with various projects.
The official MySQL website provides comprehensive information about the popular database management system. Here you will find documentation, installation guides, and resources for developers and administrators. The site also offers up-to-date news about MySQL versions, updates, and new features. Users can access forums and communities where questions and problems related to MySQL are discussed. Visit the official MySQL website to learn more about the capabilities of this powerful DBMS and start using it in your projects.
This database management system (DBMS) uses the basic principles and syntax of the standard SQL language. This ensures compatibility with other systems and simplifies the process of working with data. Using SQL allows you to efficiently perform selection, insertion, update, and deletion operations, making this DBMS a universal tool for managing information in various applications.
The main advantage of SQlite is its embeddability. SQlite is not a client-server application, unlike other relational database management systems (RDBMS). Instead, SQlite is a library that can be directly integrated into software. This allows developers to use the database without setting up a separate server, significantly simplifying the development and deployment process. SQlite is ideal for mobile applications and embedded systems where resources are limited and high performance is required. SQLite is extremely popular, as evidenced by its presence on every smartphone. On Android devices, this database management system is responsible for storing contacts and media files. On iOS, many applications also use SQLite for efficient data management. SQLite is a powerful and lightweight library for managing relational databases. The official SQLite website offers access to documentation, downloads, and information about the functionality and features of this DBMS. The site contains current versions of SQLite, installation and configuration guides, and code samples for various programming languages. SQLite is ideal for embedding in applications, providing reliable and fast data storage without the need for complex server configurations. For developers and database administrators, the official SQLite website will be an indispensable resource for obtaining necessary information and support. This database management system (DBMS) can be considered one of the most advanced. It is not simply a relational DBMS, but an object-relational open-source DBMS, allowing for efficient data processing and flexibility in database design. PostgreSQL offers a wide range of data types, different from those available in other relational database management systems. In addition to standard numeric, text, and Boolean types, PostgreSQL allows you to store and process geometric and monetary data, network addresses, as well as JSON and XML formats. Users can also work with arrays and create their own user-defined data types, making PostgreSQL one of the most flexible data management tools. This diversity of data types enables more efficient modeling and storage of information, which is essential for modern applications and business solutions.
The official PostgreSQL website offers comprehensive information about the PostgreSQL database management system. Here you will find the latest software versions, documentation, guides, and developer resources. PostgreSQL is a powerful, open-source, object-relational database management system that supports advanced features such as transactions, complex queries, and scalability. The site offers guides on installing, configuring, and using PostgreSQL, as well as information about the community and the latest news. Visit the official PostgreSQL website to access useful resources and learn more about the capabilities of this reliable DBMS.
The official MySQL website provides comprehensive information about this popular database management system. Here you will find the latest MySQL versions, documentation, installation and configuration guides, and developer resources. Users can also find the latest news, updates, and announcements. The site also offers access to the MySQL community, where you can ask questions and share experiences with other experts. MySQL is known for its reliability, performance, and ease of use, making it one of the most popular data management solutions.
The official SQLite website is the primary resource for users to find up-to-date information about the SQLite database. The site features the latest software versions, documentation, installation and usage guides, and helpful tips and tricks for optimizing your work with SQLite. SQLite is a lightweight yet powerful library for database management, making it an ideal choice for developers who need to embed a database into their applications. By visiting the official site, you can learn about new features, access support forums and the developer community, and find information about SQLite compatibility and licensing.
Limitations of Relational DBMS
Relational database management systems (DBMS) are characterized by simplicity, convenience, and predictability. The relational DBMS market is one of the most stable and conservative in the IT industry. Despite the growing popularity of NoSQL solutions, relational databases continue to be the most in-demand tool across various industries. Their structure and capabilities ensure reliable data storage and processing, making them the preferred choice for organizations seeking effective information management.
According to DB-Engines data for February 2021, relational database management systems (DBMS) account for 74% of the global market. This indicates the continued widespread use of relational technologies across various industries. Relational DBMSs provide reliable data storage and efficient data management, making them the preferred choice for many organizations. With data volumes and analytics needs constantly growing, relational DBMSs continue to be a key business tool.

Relational databases have their drawbacks. Despite their widespread use and many advantages, such as structured nature and the ability to use SQL for queries, they can face scalability and performance issues. For example, as the volume of data and the number of users increases, system performance may slow down. Relational databases can also be less flexible than NoSQL solutions, making it difficult to work with unstructured data. Furthermore, the need to maintain a strict schema can limit the speed of application development and adaptation. Relational databases have significant difficulties with horizontal scaling, which involves distributing tables across multiple servers. This creates difficulties when formulating queries and establishing relationships between tables. Such limitations can negatively impact system performance and flexibility, making relational databases less suitable for scalable solutions in the face of rapid data growth. A growing database must be hosted on more powerful and expensive servers, which means vertical scaling. This solution allows for increased server performance and resources, ensuring more efficient data management and improving system responsiveness. Vertical scaling is an important aspect of optimizing work with large volumes of information and ensures the reliability of applications.
Despite the power of modern machines, relational databases have their limitations, making them ineffective for storing and processing large volumes of data. These systems are not always able to adequately handle big data tasks due to their structure and approach to information organization. As a result, effective work with big data requires the use of alternative technologies and approaches that provide better scalability and performance.
Normalization of relational databases leads to the creation of a complex structure. The speed of query processing in relational databases directly depends on the number of tables being accessed. The more tables involved in a query, the longer it may take to execute. This should be taken into account when designing the database and optimizing queries for increased efficiency.
As the number of tables increases to 100, 200, or even 1000, the performance of the database management system (DBMS) decreases significantly. This leads to slow queries, and the code itself becomes cumbersome and difficult to understand. Optimizing the database structure and using efficient queries can help improve performance and simplify data management.
Relational database management systems (RDBMS) are ideal for working with data that has a clearly defined structure. They efficiently process categories of information such as messages, product data, and user information. However, RDBMSs face difficulties in organizing the storage and processing of entities that have an arbitrary set of attributes or hierarchical data structures. This limitation can be a significant obstacle for projects that require flexibility in data management and its complex processing.
NoSQL as an alternative to traditional databases
The world is undergoing significant changes. As part of the digital transformation, businesses are faced with new challenges that require effective solutions. Companies are increasingly turning to modern databases. This is necessary not only to prevent existing systems from becoming overloaded, but also because classic relational database management systems (RDBMS) are not always capable of meeting the demands of modern tasks. New databases provide flexibility, scalability, and high performance, making them ideal for working with large volumes of data and implementing complex analytical processes.
In the early 2000s, non-relational databases appeared on the market, radically changing the approach to data management. These databases not only responded to new challenges but also sought to address the key shortcomings of relational systems, such as limited flexibility, low performance, and scalability issues. Non-relational databases offer more adaptive data structures, enabling efficient processing of large volumes of information and ensuring high performance in the face of rapidly changing business requirements.
NoSQL lacks traditional elements of relational databases, such as rows, columns, tables, and their relationships. In non-relational databases, data is stored as objects with arbitrary attributes. These can be key-value pairs, JSON documents, graph structures, and other formats. Using NoSQL allows for flexible data management, ensuring high performance and scalability for modern applications.
Types of Non-Relational Databases
NoSQL databases are classified into four main categories depending on the tasks they solve. Each category is responsible for different data types and use cases, providing flexibility and scalability for modern applications.
A database is an extensive table in which each cell stores data of various types. Each value in this table is assigned a unique key, which makes it easy and quick to find the necessary information. This structure ensures efficient storage and management of data, which makes databases an indispensable tool in modern information systems.
This database management system (DBMS) does not provide the ability to establish relationships between objects. It is limited to performing basic operations, such as searching for values by key, adding new records, and deleting existing ones.
Key-value databases are widely used for efficient data caching and queue management. These storage systems allow for fast access to information, which is especially important for improving application performance. Using key-value databases helps optimize response times and reduce the load on underlying databases, making them an ideal choice for modern web applications and distributed systems.
The main advantages are high search speed and ease of scaling.
The disadvantage of key-value databases is the inability to perform operations on values, such as sorting or analysis. This limits their use in more complex tasks.
Key-value databases are widely used in various applications, including the Google search engine, the Wikipedia online encyclopedia, the Facebook social network, and the Amazon online store. These databases provide high-speed data access and efficient storage of information, making them ideal for processing large volumes of data and quickly executing queries. Key-value databases allow companies to optimize their systems and improve user experience.
One of the most popular in-memory databases is Redis. This system is actively used by companies such as Uber, Slack, Stack Overflow, as well as various travel sites and the social network Twitter. Redis is known for its high performance, ease of use, and scalability, making it an ideal choice for applications requiring fast data processing and low latency.
Data is stored in hierarchical structures represented as documents with an arbitrary set of fields and values. These documents are organized into collections, which allows for convenient access and management of information. This approach allows for efficient structuring and processing of data, which is especially important for modern applications and management systems.
In relational database management systems (RDBMS), collections can be compared to tables, and documents to rows within those tables. This comparison helps better understand the structure and organization of data in NoSQL systems, where collections serve as containers for storing documents, similar to how tables store records in relational databases.
Movies are an important part of culture and entertainment. Every year, many new films are released, covering a variety of genres and themes. Viewers can enjoy dramas, comedies, thrillers, and science fiction films. Cinema continues to evolve, incorporating modern technologies such as CGI, which allows for the creation of stunning visual effects.
Beyond being spectacular, films often touch on relevant social and cultural themes, challenging viewers to consider critical issues. Importantly, successful films not only entertain but also educate, evoking emotion and discussion. Film festivals play a significant role in promoting talented directors and actors, as well as opening new horizons for film fans.
In recent years, streaming platforms have significantly changed the way we consume films, providing access to vast libraries of content. This allows viewers to discover both classic and new films at any time. Given all these factors, films remain an integral part of our lives, uniting people of different ages and interests.
Document-oriented databases are actively used in content management systems (CMS) for the efficient storage of catalogs and user profiles. Their structure allows for convenient organization and retrieval of data, making them an ideal solution for web applications that require a high degree of flexibility and scalability. The use of document-oriented databases improves performance and simplifies working with unstructured information, significantly increasing the functionality of CMS.
MongoDB is one of the most popular databases, offering the ability to create procedures using JavaScript. This functionality makes MongoDB particularly attractive to developers seeking flexible data solutions. Thanks to its document-based structure and support, MongoDB enables efficient management of large volumes of information, ensuring high performance and scalability. These databases differ from relational systems primarily in the way they store information on the storage medium. Unlike relational databases, which organize data into tables, non-relational databases use various formats, such as documents, graphs, or key-value stores, allowing for more flexible data management. This makes them ideal for working with large volumes of information and dynamic data structures, which is especially relevant in modern applications and web services. Relational databases create a separate file for each table, while columnar databases create a separate file for each column. This difference in data storage architecture impacts the performance and efficiency of query processing. Columnar databases are optimized for analytical tasks that require fast processing of large volumes of data and aggregation operations. Relational databases, in turn, are better suited for transactional systems where data integrity and consistency are important.
A relational table is a structure that organizes data into rows and columns. Each column in the table corresponds to a separate attribute, and each row represents a record containing specific values for these attributes. Relational tables are the foundation of relational databases, allowing for efficient data storage and management. They enable complex queries by combining data from different tables using key fields. This allows users to retrieve, update, and analyze information with a high degree of accuracy and reliability. Relational tables also maintain data integrity, ensuring consistency between related records.
Records in a column-oriented database would be represented as follows:
What does this mean? Imagine that you only need the names of objects, and their characteristics are irrelevant. This approach allows you to focus on key elements, simplifying the process of searching and processing information. This is especially useful in cases where only the identification of objects is important without in-depth analysis of their properties.
When executing a query in a relational database, each record is analyzed, from which the necessary data is extracted. In contrast, in a column-oriented database, only one column with the names is read from disk. This approach significantly speeds up query execution, making column-oriented databases more efficient for working with large volumes of information.
Column-oriented databases are widely used in catalogs and data archives, where working with selections is a key aspect. These databases provide efficient storage and fast retrieval of information, making them ideal for analytics and processing large volumes of data. Using column-oriented databases significantly optimizes data searching and filtering processes, which is especially important for business analytics and working with large data sets.
One of the most popular database management systems (DBMS) is Apache Cassandra. This distributed NoSQL database is known for its high availability and scalability, making it an ideal choice for processing large volumes of data. Cassandra provides reliable data storage and fast query execution, which attracts many companies working with big data and cloud solutions. Thanks to its architecture, Cassandra is capable of processing data in real time, which is especially important for modern applications that require high data processing speed.
In a number of subject areas, data is effectively represented as graphs. Graph databases are the optimal solution for storing this data. Using graph databases allows for the easy modeling of complex relationships and interactions between objects, making them ideal for tasks related to network analysis, social graphs, and other similar applications. Graph databases provide high performance when executing queries related to data relationships and facilitate deeper information analysis.
Graph vertices, also known as nodes, represent objects or entities, while graph edges indicate the relationships between these objects. Graphs are an important tool for data visualization and analysis, allowing for the identification of relationships and structures in various fields, such as computer science, social sciences, and biology. Understanding the structure of a graph and its components, such as vertices and edges, is key to effective analysis and algorithm development.
Information about friends on social networks lends itself well to visualization in graph format. This approach allows for a clear representation of connections between users, the identification of common interests and groups, and the analysis of interactions between participants. Graphical data representation facilitates rapid comprehension and simplifies the analysis of social structures. Using graphs in social networks helps to better understand the dynamics of communication and identify key participants in various online communities.

Graph databases are used in social networks, recommendation systems, as well as in fraud detection applications and other similar services. These databases allow for the efficient storage and processing of data on complex relationships between users, products, and services, making them indispensable in modern digital platforms. Neo4j is one of the most popular open-source graph database management systems. It offers its own query language, making working with graph data more efficient and intuitive. This DBMS is ideal for tasks involving the analysis of complex relationships and large volumes of data. Neo4j allows users to create, store, and process graph structures, which greatly simplifies working with data.

