Database Management System (DBMS)

A database management system (DBMS) is an electronic system that enables users to create and manage a database containing up-to-date and relevant information.

While users can access data from a database directly, the process is not intuitive. The information may be segregated across multiple fields and structured in a way that forces the user to search through a number of fields—which is tedious and error prone.

A DBMS enables users to run queries to find the data they’re after, which facilitates the processes of describing, constructing, operating, and sharing databases among the users in an organization as well as the applications that need access to the data. Examples of commonly used DBMSs include:

• Microsoft SQL

• Oracle SQL

• PostgreSQL

• MongoDB

• RedShift

Following are the highlights of DBMSs:

• A DBMS offers a centralized view of data that can be accessed by multiple users or applications (via connectors or APIs).

• A DBMS abstracts the physical and logical construct of data from the users; that is, it allows users to interact with the database without knowing where it is located.

• A DBMS acts as a broker between the user and the database. DBMS enables database administrators to implement authentication, authorization, and logging, thereby limiting what each type of user can see and do with the data they have access to.

DBMSs offer the following functionalities for users and administrators, depending on the rights they are assigned:

• Create databases

• Backup and restore databases

• Connect to and manage multiple local and remote databases

• Create tables in databases

• Create, read, update, and delete (CRUD) data in databases

• Query data in databases

• Set up multiple user accounts and roles as well as apply permissions to access data objects

A DBMS also stores the database definition in the form of a database catalog or dictionary. This descriptive information, called metadata, is data about data. (Refer to Chapter 4 for more information on metadata.) A data catalog is an organized inventory of all data assets, such as structured and unstructured data, data visualizations, and data reports. A data dictionary contains the metadata.

Figure 1.1 illustrates a DBMS, including users, applications, databases, and the flow of information.

As Figure 1.1 illustrates, there are two main types of connectivity to DBMSs:

• User/application/program: Primarily focused on queries made to the DBMS for extracting and changing/updating data from the underlying databases by the end users and apps

• Administrative: Primarily focused on management of data as well as databases via DBMS

Next, we will discuss relational and non-relational databases and how records may be arranged.

Relational Database

A relational database is quite simply defined as a database in which data is stored in tables. Tables consist of columns and rows. Table 1.2, which expands on Table 1.1, illustrates what a relational database looks like in the real world.

In many ways, a relational database might seem similar to an Excel spreadsheet; however, there are some key differences, including the following:

• A relational database breaks data into multiple tables.

• A table consists of rows and columns.

• The data within each row is referred to as a record.

• A column defines the data types, such as char, varchar, and integer.

• Each row is uniquely identified by the respective primary key column.

• Rows in one table are related to rows in another table via a foreign key.

In the example shown in Table 1.2, Employee_id is the primary key for the Employee_Details table. As part of the Salary table, Employee_id is the foreign key, and the primary key is Salary_Code. These keys relate the two tables—hence the term relational database.

Relational databases can be operated using Structured Query Language (SQL). SQL isn’t a database type itself; rather, it is a language that is used to create, modify, and query data in a relational database. SQL is the basic language used in database management systems.

The tables in a relational database can be joined to make it easier for a user to understand the relationships in the data. SQL has the ability to do basic mathematical operations such as counting, adding, grouping, subtotaling functions, and making logical transformations. Data analysts can view a table in different orders, such as by date, name, or any other column name available.

Common examples of relational databases include:

• Oracle

• MySQL

• PostgreSQL

• Microsoft SQL Server

DBMSs That manage relational databases are known as relational database management systems (RDBMSs).

Some advantages of RDBMSs are as follows:

• Relational databases work well for structured data and offer quick lookups and query times that are typically very fast.

• RDBMSs offer flexibility as SQL is a well-known standard language, and almost every RDBMS tool supports SQL queries.

• There are multiple on-premises and cloud RDBMS solutions available, and making the transition from an on-premises to a cloud-based RDBMS is not very complex.

• RDBMSs offer reduced redundancy by offering data normalization.

• RDBMSs offer ease of backup and disaster recovery by automating data backup jobs.

Relational databases are ideal for transactional processing, such as website user authentication or e-commerce transactions. You’ll learn more about this later in the chapter, in the section “Online Transactional Processing (OLTP).”

Non-relational Databases

Non-relational databases (also called NoSQL databases) do not use tables to store data. Instead, they employ a different method of storing data.

Because there are no tables in non-relational databases, there is no need to connect tables. The simple design of a non-relational database increases scalability. Social media, mobile applications, and data analytics are some of the most common examples of where NoSQL databases are often leveraged.

There are four types of non-relational databases:

• Document data stores: In a document data store, the data is stored in document format. Usually, the data is stored as Java Script Object Notation (JSON) documents in a semi-structured format. This type of data store keeps the data together and available for developers to access as part of their code (since it’s already in JSON or JSON-like format such as YAML or XML), thereby decreasing the processing required to make data insights. The data structures can be changed within a document to offer more flexibility to developers. Common examples of NoSQL document data stores are MongoDB, DocumentDB, and Cosmos DB.

• Columnar data stores: A columnar data store is a NoSQL database in which the data is stored as columns. The columns are grouped together into column families, each of which consists of a set of columns that are logically related and can be retrieved or manipulated as a unit. Whereas in a relational database the data is optimized across rows, in a columnar data store the data is optimized for fast retrieval of columns. A columnar database can store unstructured or semi-structured data. Figure 1.2 shows a columnar database using the Employee database example from earlier in this chapter. Examples of columnar data stores include MariaDB, RedShift, and Apache HBase.

• Key/value data stores: Key/value data stores are the simplest form of non-relational (or NoSQL) databases. Such a data store is organized as a dictionary with key/value pairs. Each unique record consists of a key and value. A dictionary contains a collection of records, which are stored and retrieved using a unique key.

  Figure 1.3 provides an example of a key/value data store in which major Indian cities are listed as keys pointing to specific cities (values) across northern and southern India.

  

• Graph data store: In a graph data store, the data is stored as knowledge graphs. The nodes of a graph can be persons, objects, or places. Graph data stores work by storing data alongside relationships; by retrieving a relationship, you can describe the related data. This may seem quite different from the concept of a relational database, with data stored based on relationships. Graph data is usually used for storing and managing the network of connections between the elements in the graph. Figure 1.4 illustrates a graph database. In this graph, the nodes (colored bubbles) represent people and may contain data about their attributes (such as Employee_id), and the lines connecting the nodes show the relationships.

The key advantages of non-relational databases are as follows:

• They provide a very flexible structure for storing semi-structured or unstructured data as documents, graphs, keys and values, and so on.

• NoSQL databases are recommended for big data operations as well as analytics as the speed of data retrieval is very fast.

• NoSQL databases can be very cost-effective compared to structured relational databases as not all information needs to be processed, which means data can be stored in raw format.

• Non-relational databases are designed to be reliable and continuously available. Many modern cloud first databases are NoSQL.

Table 1.3 summarizes the differences between relational and non-relational databases.

Data Warehouses

A data warehouse is a centralized repository of data that is predominantly used for business analytics and business intelligence. The key purpose of a data warehouse is to offer insights for future decision making based on historical data.

It is key to note that a data warehouse only supports structured data and well-defined schemas. A data warehouse supports a schema-on-write approach that follows a predefined schema for the data.

A data warehouse may contain information from multiple organizational sources. The key functions of a data warehouse are as follows:

• Data extraction

• Data cleansing

• Data transformation

• Data loading/refreshing

Figure 1.5 illustrates the concept of a data warehouse.

As shown in Figure 1.5, a data warehouse consists of multiple components:

• Data sources: The source of data to be processed and analyzed; the data is typically online transactional processing (OLTP) data.

• ETL tools: Extract, transform, and load (ETL) tools are used to extract data from data sources and cleanse the data as well as transform it before loading it in the data warehouse.

• Database: Data warehouses leverage RDBMSs as data management tools.

• Data: The data in a data warehouse is the raw data from OLTP systems, and this raw data is used to derive metadata and summary data.

• Query tools: Tools such as query and reporting tools as well as data mining applications enable users to analyze data and interact with a data warehouse.

• Analytics and reporting: Users can leverage reports to drive decision making.

The data in data warehouses is structured, processed data that results from ETL processes and can be leveraged by queries and data mining tools.

Data warehouses can be architectured in three ways:

• Single-tier architecture: A single-tier architecture can store a minimum amount of data and is focused on getting data only from a restricted number of sources as well as data de-duplication. This architecture is not widely used at an enterprise level.

• Two-tier architecture: A two-tier architecture separates the physically available data source(s) from the data warehouse. Such an architecture is not expandable and hence is not commonly used by large organizations focusing on multiple users and multiple data sources.

• Three-tier architecture: This architecture consists of three tiers: top, middle, and bottom. The top tier is a front-end client layer that contains tools such as query, reporting, analysis, and data mining tools. The middle tier is an OLAP server that acts as a mediator between an end user and the database. The bottom tier is a relational database system in which data is cleansed, transformed, and loaded using tools from the back end.

Some common examples of data warehouse solutions are:

• AWS RedShift

• Azure SQL Data Warehouse

• Oracle Data Warehouse

The key advantages of leveraging a data warehouse are as follows:

• Data warehouses support quick access to critical data and metadata.

• Data warehouses integrate data from multiple data sources.

• Data warehouses reduce turnaround time in the process of analyzing the information.

• Data warehouses enables users to analyze data from different time periods to make future predictions.

• Data warehouses provide consistent information by virtue of their dependency on RDBMSs.

Data warehouses also have some key disadvantages:

• As data warehouses are based on RDBMSs, unstructured data cannot be stored in them.

• It is difficult to alter data types and data source schema, as data warehouses can only ingest structured data that fits predefined schemas.

There is a subset of a data warehouse called a data mart that is typically used by a specific set of users in an organization. The core purpose of a data mart is to partition a smaller set of data to provide easier data access for specific targeted groups within an organization. For example, a sales data mart could be used by a sales department to know the details about sales statistics related to a newly launched product. No other department would have access to the sales data mart.

Figure 1.6 illustrates the various data marts that could be created to cater to specific lines of business.

Three types of data marts can be created, depending on an organization’s requirements:

• Dependent data mart: This type of data mart is created from the existing enterprise data warehouse and follows a top-down approach, with all pertinent data extracted according to the analysis requirements. Because it leverages the existing data warehouse and extracts only meaningful data partitions, the only additional work is related to defining the data required by the respective line of business. A dependent data mart can be seen as a subset of a data warehouse.

• Independent data mart: This type of data mart is a standalone system that is created without the use of the enterprise data warehouse. It follows a bottom-up approach in that it has its own ETL specifics for extracting, processing, and storing or retrieving data as and when needed. An independent data mart can be seen as a much smaller-scale data warehouse.

• Hybrid data mart: This type of data mart is a combination of a dependent data mart and an independent data mart. It uses both top-down and bottom-up approaches for managing the data, with existing interesting data captured from the data warehouse and new interesting data extracted from source systems directly.

The next section covers the insights to data lakes.

Data Lakes

A data lake is a large data storage repository that can store structured, semi-structured, and unstructured data from multiple data sources. It can also contain raw data as well as processed data. Think of a data lake as a data warehouse on steroids. The major advantage of a data lake is that the data need not be processed before it is stored, as it must be with a data warehouse.

However, the data from a data lake cannot be used directly and requires processing on the fly. Data lakes leverage a “schema-on-read” approach, which means the data is transformed at query time. In other words, whereas data warehouses use ETL, data lakes thrive on ELT (extract, load, and transform). Therefore, the resource consumption and extraction of data from data sources (such as blogs, logs, searches, and image and multimedia repositories) is extremely fast compared to consumption and extraction with traditional ETL processes, thereby reducing the cost and complexity associated with the ETL processes.

Figure 1.7 illustrates the typical structure and components of a data lake.

As shown in Figure 1.7, a data lake consists of multiple components:

• Data sources: Multiple data sources from which data can be loaded to be processed and analyzed.

• Data ingestion layer: This layer is used to ingest data (in its native format) from multiple data sources. For example, media files can be imported as MP4 or JPEG files, and well-structured data from SQL databases can be imported consequently.

• Data transformation tools: A variety of tools are used for transform functions to normalize the schema and ready the data for consumption by analysts.

• Query tools: Tools such as query and reporting tools as well as data mining applications enable users to analyze the data and interact with the data warehouse.

• Analytics and reporting: Users can leverage reports or dashboards to drive decision making. Reports and dashboards are created based on analysis on the data loaded in the data lake in line with one or more use cases.

Data lake solutions have become increasingly prevalent due to the large volumes of data that need to be analyzed and the many types of people who use them, for example, data engineers, data analysts, data architects, and data scientists. A data lake is often used as part of a cloud service offering or in a hybrid arrangement with a cloud-hosted data lake with on-premises data sources. This combination of downstream analytics and reporting as well as dashboarding tools offers state-of-the-art data-based decision-making ability.

Some of the most common and popular examples of data lakes are:

• Google BigQuery

• AWS Lake Formation

• Azure Synapse

• Snowflake

The key advantages of leveraging a data lake are as follows:

• Data lakes support raw, processed, structured, semi-structured, and unstructured data from a variety of sources. They eliminate silos by offering a single repository for all data types and types of data. More on data types and types of data in Chapter 3, “Data Types and Types of Data.”

• Data lakes require no schema lock in and are therefore great for data port-ability from a variety of data types and types of data.

• Data lake solutions enable data analysts to query data in new ways and create new use cases as the business demands.

Data lakes also have some disadvantages, including:

• Data lakes are meant primarily for data analysts, data engineers, and data scientists. A much higher-than-average skillset is required to leverage the true potential of data lakes.

• Non-selective data storage from multiple data sources may imply higher storage costs.

Table 1.4 summarizes the differences between data warehouses and data lakes.

Online Transactional Processing (OLTP)

You have certainly used a point-of-sale (POS) system to make a card transaction using a debit/credit card or reserved a plane ticket to go on a dream vacation. It is possible, however, that you haven’t recognized that those systems leverage OLTP. Think about it: You wouldn’t wait hours for a merchant to process your transaction; rather, you can complete a purchase by making a payment to a merchant in moments, which implies that these transactions are done in real time. OLTP transactions—including those already mentioned as well as online banking, online shopping, and ATM transactions—typically occur in milliseconds.

OLTP is a software system that is specifically built to process transaction-focused data in a database. OLTP data stores enable large numbers of concurrent real-time transactions. It is important to note that OLTP data stores take an all-succeed or all-fail approach; that is, the transactions either succeed or fail and do not remain in an intermediate state. Figure 1.8 provides an overview of an OLTP system.

With OLTP, it is important that the transactions go as expected and that there are no hiccups because any transactional issues (including data corruption or data inconsistency) can lead to undesirable outcomes. OLTP data stores offer reliability and consistency by implementing the ACID (atomicity, consistency, isolation, and durability) design principle. ACID is a transaction management process that uses concurrency controls to ensure that consistency is maintained.

The ACID design principle has these components:

• Atomicity: This implies that all transactions always succeed or fail completely. In other words, if there’s an incomplete transaction, it is not valid.

• Consistency: This ensures that a database that was in a consistent state remains in a consistent state following a successful transaction.

• Isolation: This ensures that one transaction does not intervene or disrupt another transaction.

• Durability: This ensures that the results of a successful transaction are committed and cannot be rolled back.

As with any other database system, there are tools available to manage OLTP systems. End users and applications can manage OLTP systems via Data Manipulation Language (DML) using update, insert, and delete operations on an ongoing basis.

Following are the key tenets of OLTP:

• OLTP can process multiple concurrent simple transactions.

• OLTP maintains the integrity of the data, even when the same data is accessed by multiple users at the same time.

• OLTP provides indexed datasets, and thus, the data can be rapidly searched, retrieved, and queried.

• A complete data backup must be maintained because large transactions are processed concurrently by the users.

The next section gives insights to OLAP.

Online Analytical Processing (OLAP)

If your organization launched a new product last year and is about to launch a new version of that product, how do you know if the previous product did well in the market and how it fared against competition? How do you identify the demand patterns? How do you identify the customer sentiments? While you might have all this data available across a range of systems (such as sales records and ERP and CRM systems), there isn’t a single source of truth unless you have performed analytics on the historical data and arrived at a conclusion that can drive the success of the new release. This is where you can leverage an online analytical processing (OLAP) system for data analysis. Planning, analyzing, forecasting, and budgeting are some of the common functions that are performed by business analysts using OLAP. OLAP systems process large volumes of data from data warehouses, data marts, or any other centralized data stores and are important in business intelligence and data mining processes.

OLAP systems are used for various analytics, such as diagnostic, predictive, and prescriptive analytics. Unlike with OLTP systems, where the datasets are not so large, OLAP systems execute complex queries against large multidimensional databases and data lakes. Figure 1.9 depicts an OLAP system.

Following are the key tenets of OLAP:

• OLAP supports multidimensional conceptual views of datasets.

• OLAP acts as a middle layer between a data warehouse and a front end.

• Data can be represented in advanced presentations such as 3D cubes, pivot tables, and cross tabs.

• OLAP can perform advanced computational functions and advanced data modeling functions.

Table 1.5 lists the key differences between OLTP and OLAP systems.

Finally, it is important to know that OLTP and OLAP are not competing systems; rather, they are complementary systems. Typically, an organization has transactional data and batch data undergoing analytics and reporting. While from a business transaction perspective OLTP is key, analyzing the data is an aspect of OLAP, and this is where the two systems come together to give a holistic view into the organizational processes leading to business insights and feeding back into the process for continuous improvement.

To simplify this, let’s consider an example of a new product to be launched and look at historical performance of an earlier version of the product. An OLTP system would have the historical data based on consumer transactions and key operational insights (such as inventory management and customer surveys). This data could be leveraged by OLAP and a data warehouse/data lake to get deeper insights into what changes are required from the previous product as well as what will make the new product sell like hotcakes in the market—whether it be a launch campaign or social media publicity/ads. Figure 1.10 depicts this arrangement at a high level and on an architectural level.

The integration of OLTP and OLAP includes the following components:

• Business processes: These are the processes that the business follows to achieve the goals/outcomes set forth by the business owners/leaders.

• Customer transactions: This is information about the customers, products, and transactions stored in the OLTP database.

• ETL (extract, transform, and load): The transactional data from various relational database management systems is retrieved, transformed, and loaded in the data warehouse/data lake.

• Data warehouse/data lake: Data is accessed by the data mart from the data warehouse or from the data lake that is used by OLAP to store processed data.

• Data mining, analytics, and decision making: The data is extracted from the data warehouse/data lake through data mining, and after analysis, it is used for decision making.