Category Describe relational concepts

Describe features of transactional workloads

Transactional workloads play a critical role in ensuring the smooth operation of businesses and maintaining data integrity. These workloads encompass activities such as capturing customer orders, processing financial transactions, and updating inventory levels.

Transactional workloads are designed to handle business operations that involve data modifications, ensuring the accuracy, consistency, and reliability of data. Let’s consider an e-commerce platform that processes customer orders. Each customer order represents a trans-action that requires capturing the order details, updating inventory levels, and recording the financial transaction. These transactions must be executed reliably and in an atomic manner, meaning they should either complete successfully or be rolled back entirely if an error occurs.

Transactional workloads offer several advantages for business. First, they ensure data con-sistency and integrity. The ACID properties guide transactional processing, ensuring that data remains in a consistent state even in the event of failure or concurrent access. This integrity is crucial for financial systems, inventory management, and other critical business functions.

Second, transactional workloads support concurrency control and isolation in a multiuser environment, where multiple transactions can occur simultaneously. Transactional processing mechanisms ensure that transactions are executed independently and do not interfere with each other, maintaining data integrity and preventing conflicts.

Furthermore, transactional workloads facilitate data durability and reliability. Transactional systems employ techniques such as write-ahead logging and database recovery mechanisms to ensure that committed transactions persist even in the face of system failures. This durability ensures that critical business operations can be restored and recovered without data loss.

Transactional workloads are supported by various database systems such as relational databases, where ACID properties are typically enforced. These systems provide transac-tion management features that guarantee data consistency, durability, and isolation. We can say transactional workloads are essential for maintaining accurate data, supporting reliable business operations, and ensuring data integrity. By executing operations in an atomic and consistent manner, businesses can confidently process customer orders, handle financial trans-actions, and manage inventory levels, fostering trust and reliability in their operations.

What are MySQL, MariaDB, and PostgreSQL?

My SQL, MariaDB, and PostgreSQL are all popular open-source relational database manage-ment systems (RDBMSs). Each has unique features and benefits that make them suitable for different applications and use cases.

■■ MySQL: MySQL is a widely used open-source RDBMS. It is known for its speed, reli-ability, and ease of use. MySQL is often used in web applications and online publishing and is a central component of the popular LAMP open-source web application software stack (Linux, Apache, MySQL, PHP/Python/Perl).

■■ MariaDB: MariaDB is a fork of MySQL created by the original developers of MySQL, and it’s intended to remain open-source. MariaDB is designed to be highly compatible with MySQL, meaning that, in most cases, data and code can be switched seamlessly between the two. MariaDB includes more storage engines than MySQL, and it includes several features not found in MySQL.

■■ PostgreSQL: PostgreSQL is a powerful, open-source object-relational database sys-tem. It supports both SQL (relational) and JSON (non-relational) querying. It’s highly extensible and can be customized via functions and stored procedures. PostgreSQL is renowned for its performance, advanced features, and standards compliance.

While all three systems can manage relational data, they each have unique advantages. MySQL is known for its speed and efficiency, MariaDB offers rich features and compatibility with MySQL, and PostgreSQL is known for its standards compliance and versatility. All three are supported by Azure, allowing you to leverage these benefits in a fully managed cloud environment.

Azure Database for MySQL

Azure Database for MySQL is a relational database service in the Microsoft cloud based on MySQL Community Edition (available version 5.6/5.7/8.0). As a fully managed service, it allows developers to focus more on developing applications and less on managing infrastructure.

■■ Fully managed: This service is fully managed by Microsoft. What does that mean for you? It means that common maintenance operations such as patching, backups, and handling failovers are automated. These features help save time and reduce the com-plexity of administrative tasks, freeing you to focus on what matters: developing your applications and improving functionality.

■■ Performance and scalability: Performance is a crucial aspect of any application. With Azure Database for MySQL, you have various options to match your performance requirements. Azure offers a range of compute sizes, so you can select the right amount of compute power you need. It allows you to independently scale compute and storage. This means you can adjust the compute power and storage space to match the demand of your application. This independent scaling is especially helpful as it provides flexibility and helps to manage costs.

56 CHAPTER 2 Identify considerations for relational data on Azure

■■ Security and compliance: Security and compliance are top priorities for any business. Azure Database for MySQL integrates well with Azure’s advanced security and compli-ance services. It supports Azure Active Directory authentication so that identities can be managed in one central location. It also provides encryption at rest and in transit, adding a layer of protection to your data.

In addition, Azure offers a comprehensive set of compliance offerings. Whether you are dealing with general data protection regulations or industry-specific ones, Azure has you covered.

■■ Developer productivity: Azure Database for MySQL integrates seamlessly with popu-lar open-source applications and Azure services. This makes it a natural choice if you are developing apps with Azure App Service, building microservices with Azure Kubernetes Services, or running analytics with Power BI.

■■ Data protection: Data is the heart of your business. To protect it, Azure Database for MySQL offers automatic backups, which are stored in geo-redundant storage. This allows you to restore your database to any point within the backup retention period. Additionally, the service provides the option to increase your retention period and to use long-term backup retention.

■■ Global availability and industry-leading service-level agreements (SLAs): With Azure’s global footprint, you can run your applications closer to your customers. And with industry-leading SLAs, you can have peace of mind knowing your app will run smoothly and reliably.

In the following section, we will delve deeper into the business benefits and use cases of Azure Database for MySQL.

Describe features of analytical workloads

Analytical workloads play a pivotal role in extracting valuable insights and patterns from data to support informed decision-making and strategic planning within businesses. These work-loads involve complex data analysis, aggregations, and transformations to uncover meaningful information.

18 CHAPTER 1   Describe core data concept

Analytical workloads encompass a range of activities, such as data exploration, statistical analysis, data mining, and predictive modeling. The process begins by identifying relevant data sources and extracting the required data. The advanced analytics techniques, such as data visualization, machine learning, and statistical algorithms, are applied to gain insights and patterns from the data. The results are interpreted and translated into actionable business intelligence, which results in data-driven decision-making.

Analytical workloads serve different data personas within organizations.

• Data analysts: Data analysts leverage analytical workloads to explore and analyze data, uncovering trends, correlations, and patterns that provide valuable insights. They use statistical techniques and data visualization tools to communicate their findings effec-tively to stakeholders, resulting in evidence-based decision-making.

• Data scientists: Data scientists go beyond analyzing data and utilize advanced analyti-cal methods to develop predictive models, machine learning algorithms, and data-driven solutions. They leverage analytical workloads to build models that forecast future trends, identify opportunities, and optimize business processes.

• Business executives: Business executives rely on analytical workloads to gain high-level insight and make strategic decisions. They rely on reports, dashboards, and visual-izations generated by analytical workloads to monitor key performance indicators, track business metrics, and assess the effectiveness of strategies.

• Data engineers: Data engineers support analytical workloads by designing and implementing the data infrastructure necessary for data analysis. They ensure that data is ingested, processed, and made available in a format that facilitates efficient analysis.

They collaborate with data analysts and scientists to ensure data quality and reliability.

Analytical workloads are supported by various technologies and tools, including data plat-forms, machine learning frameworks, and business intelligence tools. These solutions provide capabilities for data exploration, modeling, visualization, and advanced analytics.

Describe responsibilities for database administrators

As a database administrator (DBA), your role is crucial in the management and maintenance of databases, ensuring their smooth operation, integrity, and performance. You are the guard-ian of data within your organization, responsible for various tasks that contribute to efficiently storing, retrieving, and securing data.

You are involved in the entire life cycle of databases, starting from the initial design and creation to ongoing maintenance and optimization. You work closely with stakeholders to understand data requirements and design database structures that optimize performance and scalability. You determine data models, create database schemas, and define relationships between tables.

Ensuring data security is a critical aspect of your role. You implement access controls, user authentication, and encryption mechanisms to protect sensitive data from unauthorized access or malicious activities. You establish backup and recovery procedures to safeguard against data loss, ensuring the continuity of business operations.

20 CHAPTER 1   Describe core data concept

Monitoring databases and optimizing performance are essential responsibilities. You constantly monitor database performance, identifying and resolving bottlenecks to enhance system efficiency. You analyze query performance, tune database configurations, and optimize indexing strategies to improve overall performance and ensure timely data retrieval.

Your expertise also extends to backup and recovery. You design and implement robust backup and recovery strategies to protect data from system failure, human errors, or disasters. You schedule regular backups, perform restoration tests, and maintain disaster recovery plans to ensure data availability and minimize downtime.

Keeping databases up to date is another aspect of your role. You oversee database upgrades and apply patches, ensuring that the database systems are equipped with the latest features, bug fixes, and security updates. You perform compatibility tests and ensure seamless transitions to new versions or releases.

Your role as a DBA is instrumental in maintaining data integrity, ensuring system availabil-ity, and supporting business continuity. Your expertise ensures that databases operate effi-ciently, adhere to industry standards, and meet regulatory requirements. With your skills and knowledge, you contribute to the data-driven systems within your organization functioning smoothly.

Describe responsibilities for data analysts

As a data analyst, your role is crucial in uncovering valuable insight and patterns within data to support informed decision-making within your organization. You play a pivotal role in analyz-ing, interpreting, and visualizing the data to derive meaningful information that drives business strategies. Let’s explore your comprehensive role as a data analyst from a business perspective.

Your primary responsibility is to explore and analyze data to uncover trends, correlations, and patterns that provide valuable insights. You work with various data sources, ranging from structured databases to unstructured text files, and use statistical techniques and analytical tools to extract meaningful information.

Data exploration is an essential part of your role. You dive deep into the data, examining its structure, quality, and relationships. You identify relevant variable and metrics to analyze, ensuring that the data is appropriate for the questions or problems at hand.

Once you have gathered and cleaned the data, you apply statistical analysis tech-niques to identify patterns and relationships. You may perform tasks such as descriptive statistics, hypothesis testing, regression analysis, or clustering to extract insights from the data. These analyses help you uncover trends, anomalies, and relationships that can guide decision-making.

Data visualization is another crucial aspect of your role. You use visual tools and techniques to present data in a clear and concise manner. By creating charts, graphs, and dashboards, you transform complex datasets into easily understandable visual representations. These visualiza-tions help stakeholders to grasp insights quickly and make informed decisions.

22 CHAPTER 1 Describe core data concept

An example of your role as a data analyst is analyzing customer behavior data for an e-commerce company. You would examine the data to understand customer preferences, identify purchasing patterns, and segment customers based on their buying behaviors. These insights would then inform marketing strategies, product recommendations, and customer retention efforts.

Your expertise as a data analyst contributes to evidence-based decision-making within your organization. By analyzing and interpreting data, you provide insights that support strategic planning, optimize operations, and drive business growth.

EXAM TIP

When preparing for the exam, you should focus on understanding the core data concepts and their practical application. Familiarize yourself with different data representation for-mats, storage options, and common data workloads. Pay attention to the roles and respon-sibilities of database administrators, data engineers, and data analysts. Additionally, practice relating these concepts to real-world scenarios to reinforce your understanding. Being able to apply your knowledge to practical situations will help you excel on the exam and in real-world data management scenarios.

Skill 2.1: Describe relational concepts

When exploring relational concepts, you will find yourself thinking about data manage-ment within the cloud-centric world of Microsoft Azure. Data management handling is

29

indispensable for leveraging Azure’s robust data services, which require a keen understanding of relational database intricacies.

Envision organizing data in a structured manner, akin to tables with rows signifying unique records and columns delineating specific attributes. This visualization encapsulates the essence of relational data, which serves as the bedrock for building, querying, and manipulating data-bases in a coherent and systematic fashion. Mastery of relational data concepts is imperative, as it fosters data integrity and efficiency.

Delving deeper, you encounter normalization, a methodical strategy aimed at minimizing redundancy and fortifying data integrity. Normalization is about dissecting your data, dis-tributing it across related tables, and ensuring each piece of information resides in its rightful place. Grasping normalization’s significance is paramount, as it directly influences your data-base’s structural optimization, enhancing maintainability and expediting query performance.

As you navigate through this data-centric realm, SQL emerges as your primary tool of communication with relational databases. Learning SQL means learning the lexicon of data-bases, where you can retrieve, insert, update, and delete data with precision and efficiency. A standardized language across relational databases, SQL is vital for effective data management and manipulation.

Lastly, your journey brings you to database objects—diverse elements within a database that you can create, modify, and interact with. Objects range from tables and views to indexes and stored procedures, each playing a unique and crucial role in how data is structured, stored, and accessed. Understanding these components is like solving a complex puzzle; recogniz-ing how each piece connects allows you to forge a complete, efficient, and effective database solution.

This skill covers how to:

• Identify features of relational data

• Describe normalization and why it is used

• Identify common structured query language (SQL) statements

• Identify common database objects

Challenges of normalization

While database normalization is a fundamental practice for optimizing database design, it brings with it a set of challenges that can impact the performance and manageability of your database systems. Understanding these challenges is crucial for database designers and administrators:

• Complex queries: Sometimes normalized tables lead to complex queries that might degrade performance.
• Design complexity: Achieving higher normal forms may result in a complex database design.

When to use normalization

Normalization should be used during the design phase of the database to ensure that the data is stored efficiently and the database can be maintained easily.

As you learn to work with databases in Azure and other platforms, understanding the principles and benefits of normalization will be invaluable. It’s a tool that can help you create efficient, reliable, and manageable databases.

Identify common structured query language (SQL)

statements

SQL is a standardized programming language that is used to manage and manipulate rela-tional databases, as pictured in Figure 2-1. Developed in the 1970s at IBM by Donald D. Chamberlin and Raymond F. Boyce, SQL has been the de facto standard for interacting with relational databases for decades. Despite being more than a half-century old, SQL’s power and ubiquity have ensured its relevance even in today’s big data age.

34 CHAPTER 2 Identify considerations for relational data on Azure

FIGURE 2-1  Showing a SQL relational database

At its core, SQL allows you to create, manipulate, and query relational databases, which store data in structured tables. But beyond these basic capabilities, SQL is used in a variety of real-world scenarios. It powers business intelligence and drives decision-making in companies, and organizations can use it to gain insights from massive datasets. Whether you’re a data ana-lyst, a data scientist, a back-end developer, or even a machine learning engineer, understand-ing SQL is an invaluable skill.

However, you’ll notice that different database systems support various SQL dialects. These dialects, while fundamentally the same, often come with their own set of proprietary exten-sions that are specific to a particular database system. These are some popular SQL dialects:

■■ Transact-SQL (T-SQL): This is Microsoft’s proprietary extension of SQL, which includes a set of programmable functions and procedural programming. T-SQL is primarily used with Microsoft SQL Server.

■■ PL/SQL: This stands for Procedural Language/SQL, a dialect developed by Oracle. PL/ SQL includes procedural language constructs, and it supports structured programming.
■■ MySQL: MySQL uses a dialect of SQL that is rich in functions, including string pro-cessing, date and time processing, and advanced features such as replication and partitioning.

■■ PostgreSQL: PostgreSQL supports a version of SQL that includes many features not available in other database systems, such as window functions and common table expressions.

Understanding these different dialects is valuable, especially when migrating from one database system to another for working with multiple database systems.

DATA CONTROL LANGUAGE

You will use DCL to create roles, permissions, and referential integrity; it is also used to con-trol access to databases by securing them. The common statements are GRANT, REVOKE, and DENY, as shown in Table 2-9.

TABLE 2-9  DCL statements

Figure 2-12 shows how to grant User1 permission to execute SELECT and INSERT operations on the Students table.

FIGURE 2-12  DCL GRANT statement

Skill 2.1: Describe relational concepts     CHAPTER 2   39

Figure 2-13 shows how to revoke User1’s permission to perform INSERT operations on the Students table.

FIGURE 2-13  DCL REVOKE statement

Figure 2-14 shows how to deny User1 the permission to perform DELETE operations on the Students table. Even if another rule grants User1 the DELETE permission, this DENY rule will override it.

FIGURE 2-14  DCL DENY statement

Understanding these DCL statements is important for managing access control and ensur-ing data security in your database. Practice using these statements to gain a clear under-standing of their impact on database operations. Make sure to always carefully consider

the implications of granting, revoking, or denying permissions to maintain the integrity and security of your data.

TRANSACTION CONTROL LANGUAGE

TCL commands are used to manage transactions in the database. They include COMMIT, ROLL-BACK, and SAVEPOINT, as shown in Table 2-10.

TABLE 2-10  TCL statements

Figure 2-15 shows how you initialize a new transaction. Any SQL statements following this will be part of this transaction.

40 CHAPTER 2 Identify considerations for relational data on Azure

FIGURE 2-15  TCL BEGIN TRANSACTION statement

Figure 2-16 shows how to start a transaction, make an update to the Students table, and then commit the transaction. This means the update is saved and cannot be rolled back.

FIGURE 2-16  TCL COMMIT statement

Figure 2-17 shows a transaction started that is making an update to the Students table. However, you can decide to roll back the transaction, meaning the update to the Students table will not be saved and the data remains as it was before the transaction began.

FIGURE 2-17  DCL ROLLBACK statement

Figure 2-18 shows a transaction started that is making an update to the Students table, cre-ating a savepoint, and making another update. Then, you decide to roll back the transaction to the savepoint. The first update will be saved, but the second update will not.

FIGURE 2-18  TCL SAVEPOINT statement

These TCL commands are crucial for maintaining the integrity of your data, especially in situations where you need to perform multiple related operations as a single atomic unit. If any of these operations fails, the TCL commands allow you to roll back the entire transaction, preventing your data from ending up in an inconsistent state.
Skill 2.1: Describe relational concepts     CHAPTER 241

Skill 2.2: Describe relational Azure data services

Having gained some familiarity with relational concepts and SQL, your next step is to explore relational data services within Azure. The Microsoft Azure cloud platform offers a suite of com-prehensive and robust data services, catering to a wide array of relational database needs.

In this section, you will uncover the specifics of Azure’s relational data offerings. You’ll delve into the Azure SQL family of products, learning how each product fits into different scenarios based on the specific requirements. You’ll explore the capabilities and uses of SQL Server on Azure Virtual Machines and how it offers flexibility in the migration process.

Furthermore, you will get acquainted with Azure database services for open-source data-base systems. Open-source databases have their unique features and community support, and

44 CHAPTER 2 Identify considerations for relational data on Azure

Azure provides first-class support for these systems, allowing you to leverage their capabilities in a cloud environment.

Upon completion of this section, you will have a good understanding of Azure’s relational data services, empowering you to make informed decisions about which service best suits your data requirements. Let’s begin this exciting journey into the world of Azure relational data services.

This skill covers how to:

• Describe the Azure SQL family of products including Azure SQL Database, Azure SQL, Azure Managed Instance, and SQL Server on Azure Virtual Machines
• Identify Azure database services for open-source database systems

Describe the Azure SQL family of products including Azure SQL Database, Azure SQL, Azure Managed Instance, and SQL Server on Azure Virtual Machines

Microsoft Azure provides a robust suite of SQL offerings, known collectively as the Azure SQL family. This portfolio of SQL-based services in Azure caters to a variety of needs, ranging from managing relational data to intelligent, cloud-native relational databases.

• Azure SQL Database: Azure SQL Database is a fully managed relational database service that provides the broadest SQL Server engine compatibility. It’s an intelligent, scalable service that offers seamless integration with Azure services such as Azure Active Directory and Power BI. Azure SQL Database supports built-in intelligence that learns your unique database patterns and automatically tunes the database for improved performance and protection. You can use it to build data-driven applications and websites in your programming language of choice, without needing to manage any infrastructure.

• Azure SQL Managed Instance: Azure SQL Managed Instance provides the broadest SQL Server engine compatibility and native virtual network (VNet) support. It’s a fully managed service, which allows you to migrate your SQL Server workloads to Azure with zero code changes. SQL Managed Instance is best for most migrations to the cloud as it provides a high degree of compatibility with the SQL Server programming surface area.

• Azure SQL Server on Virtual Machines: Azure SQL Server on Virtual Machines lets you run SQL Server inside a virtual machine in the cloud. This service is your best choice when you require full control over the SQL Server engine and the VM it runs on. It is suitable for migrating existing applications to the cloud with minimal changes or when you need SQL Server features that are not supported in Azure SQL Database or Azure SQL Managed Instance.

Skill 2.2: Describe relational Azure data services      CHAPTER 2    45

■■ Azure Synapse Analytics: Formerly SQL Data Warehouse, Azure Synapse Analytics is an analytics service that brings together big data and data warehousing. It gives you the freedom to query data on your terms, using serverless on-demand or provisioned resources.

■■ Azure SQL Edge: Azure SQL Edge is an optimized relational database engine geared toward Internet of Things (IoT) and edge computing scenarios. It offers a small footprint that can run a variety of devices from low-power edge devices to high-performance edge servers.

Azure SQL Edge provides the same SQL engine that powers SQL Server and Azure SQL, making it fully compatible with your existing SQL tools and skills. It supports data streaming through built-in stream analytics, machine learning scoring, and storage tiering.

What’s even more exciting is that it allows data to be processed close to the source, mini-mizing latency and bandwidth usage. It can operate in connected, disconnected, and hybrid environments, synchronizing data with Azure SQL Database or Azure SQL Managed Instance whenever connectivity is available.

Figure 2-24 illustrates how Azure SQL Edge can fit into an IoT solution architecture.

FIGURE 2-24  Azure SQL Edge

Comparing Azure SQL services

In the ever-evolving landscape of cloud services, Microsoft Azure presents a suite of SQL service offerings, each tailored to meet different operational and technological needs. From platform-as-a-service (PaaS) solutions designed for seamless management to infrastructure-as-a-service (IaaS) options that provide full control over your environment, Azure caters to a wide spectrum of data management scenarios. Table 2-12 compares the distinct characteristics, compatibilities, and use cases for each Azure SQL service offering, providing you with a clear overview to make informed decisions aligned with your business requirements.

46 CHAPTER 2 Identify considerations for relational data on Azure

TABLE 2-12  Comparative overview of Azure SQL services

This table serves as a guide to help you discern which Azure SQL service is best suited for your specific data management needs, whether you’re looking to modernize applications, manage big data, or extend SQL capabilities to the edge.