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.