Relational data on Azure

Relational concepts

Features of relational data

• Data is stored in tables (relations). Each row (tuple, record) is one instance of an entity; each column (attribute, field) has one data type.
• Tables have a fixed schema and are linked by keys.
• Queried with SQL (Structured Query Language).
• Enforces integrity with constraints and supports ACID transactions.

Keys

Relationship types

• One-to-many (one customer, many orders) is the most common.
• Many-to-many needs a junction table with foreign keys to both sides (students and courses via an enrolment table).
• One-to-one is rare; used to split off rarely used columns.

Normalization

Normalization is organising tables to remove redundancy and avoid update anomalies.

• Each fact is stored once. Customer address lives in a Customer table, not repeated in every order row.
• Typical steps: no repeating groups or multi-valued columns (1NF); every non-key column depends on the whole key (2NF); non-key columns depend only on the key, not on each other (3NF).
• Benefits: less storage, consistent updates, fewer anomalies. Cost: more tables and joins, which can slow heavy read queries.
• OLTP databases are normalised; analytical stores are often deliberately denormalised (star schema) for fast reads.

SQL

SQL statements fall into groups:

Azure SQL uses T-SQL (Transact-SQL), Microsoft’s SQL dialect. PostgreSQL uses PL/pgSQL and MySQL has its own dialect, but the core statements are the same.

Statements to read fluently

SELECT FirstName, LastName FROM Customers WHERE City = 'Leeds' ORDER BY LastName;

INSERT INTO Customers (FirstName, LastName, City) VALUES ('Ana', 'Silva', 'Leeds');

UPDATE Customers SET City = 'York' WHERE CustomerId = 7;

DELETE FROM Customers WHERE CustomerId = 7;

SELECT c.LastName, SUM(o.Total) AS Spend
FROM Customers AS c
JOIN Orders AS o ON o.CustomerId = c.CustomerId
GROUP BY c.LastName
HAVING SUM(o.Total) > 1000;

• WHERE filters rows before grouping; HAVING filters groups after aggregation. GROUP BY groups; aggregates are COUNT, SUM, AVG, MIN, MAX.
• Joins: INNER JOIN returns matching rows only; LEFT JOIN keeps all rows from the left table; RIGHT keeps the right; FULL keeps both.
• UPDATE or DELETE without WHERE affects every row. DROP TABLE removes the table and its data; TRUNCATE TABLE empties it but keeps the structure; DELETE removes chosen rows and can be rolled back inside a transaction.

Database objects

Typical cues: “a saved query that behaves like a table” is a view; “improve read performance on a frequently filtered column” is an index; “reusable server-side logic called by applications” is a stored procedure.

Azure SQL family

All members run the SQL Server engine and use T-SQL. The trade-off is control versus management effort.

Azure SQL Database in more detail

• Deployment options: a single database (own resources), an elastic pool (databases share a resource pool; good for many databases with varying, unpredictable peaks), and Hyperscale (very large databases, fast scale and backup).
• Purchasing models: vCore (choose cores, memory, storage; General Purpose, Business Critical, Hyperscale tiers) and DTU (bundled measure of CPU, memory and I/O, tiers Basic, Standard, Premium).
• Serverless compute: auto-scales within a range and auto-pauses when idle, billing per second of use. Good for intermittent workloads.
• Built-in high availability with a financially backed SLA, automatic backups with point-in-time restore, geo-replication and failover groups for disaster recovery, Microsoft Entra ID authentication, Transparent Data Encryption by default, Advanced Threat Protection and auditing through Microsoft Defender for SQL.

Azure SQL Managed Instance in more detail

• Deployed into a virtual network, so it has a private IP and is isolated from the internet by default.
• Supports native backup/restore from .bak files, which makes migration easy.
• Tiers General Purpose and Business Critical; can be linked to an on-premises instance with the Managed Instance link for migration and DR.

SQL Server on Azure Virtual Machines

• You choose the VM size, OS, SQL version and edition, and manage patching (assisted by the SQL IaaS Agent extension).
• Pick it when you need OS-level access, features no PaaS option offers, or need to run other software beside SQL Server.

Quick chooser

Other names: Azure SQL Edge has been retired. SQL database in Microsoft Fabric is an autonomous SQL database for app developers inside Fabric, built on the Azure SQL Database engine and automatically replicated to OneLake for analytics.

Open-source relational services

Azure offers fully managed PaaS versions of popular open-source databases, with patching, backups, high availability and scaling handled by Microsoft.

• Both use the flexible server deployment model; the older single server model has been retired.
• Choose these when the app already uses PostgreSQL or MySQL, wants open-source portability, or needs engine-specific extensions. Use the SQL family when the app needs T-SQL and SQL Server features.
• Scale-out (distributed) PostgreSQL options exist, but the exam expects Azure Database for PostgreSQL and Azure Database for MySQL.