What's changed: Deepened DP-900 Chapter 2 to the AZ-900 baseline (tables/types/keys/indexes/views/normalization + SQL category table & code, Azure relational services + IaaS/PaaS table, selection scenarios, FAQ). Localized 2 figures
2.1Relational Data Concepts
Understand relational data fundamentals—tables, rows, columns, data types, primary/foreign keys, indexes, views—plus normalization and the four SQL categories (DDL/DML/DQL/DCL).
A relational database stores data in tables and relates tables to each other using keys. Proposed in the 1970s, each table has a clear schema (column definitions), making it the most widely used way to manage consistent business data. It is the classic system for the "structured data" from the previous chapter, and it is queried with the common language SQL.
2.1.1Core concepts: tables, rows, columns
A table consists of rows (records) and columns (attributes/fields). Each column has a data type (integer, string, date, boolean, etc.) that constrains the values it can hold. One row is one item of data (e.g., one customer, one order), and columns are its fields (name, address, amount). Defining types prevents bad data and keeps search and aggregation accurate.
2.1.2Keys, indexes, and views
- Primary key: a column (or set) that uniquely identifies each row; no duplicates or nulls. E.g., customer ID.
- Foreign key: references another table’s primary key to relate tables, preserving referential integrity (no references to nonexistent rows).
- Index: speeds up search on specific columns—like a book index; reads get faster but writes cost more to maintain.
- View: a virtual table derived from one or more tables; reuse common queries by name and expose only needed columns.
2.1.3Normalization
Normalization splits and designs tables to reduce duplication and keep integrity. For example, writing a customer’s address in every "order" row means changing all rows when the address changes—risking contradictions from missed updates (an update anomaly). Splitting customer info into its own table referenced by a foreign key means the address is edited in one place. Normalization underpins correctness for OLTP; analytics (OLAP) sometimes denormalizes deliberately for easier aggregation.
2.1.4SQL basics
SQL (Structured Query Language) is the common language for relational databases. Its statements fall into four broad categories.
| Category | Role | Typical statements |
|---|---|---|
| DDL (definition) | Define/change structure | CREATE / ALTER / DROP |
| DML (manipulation) | Modify data | INSERT / UPDATE / DELETE |
| DQL (query) | Retrieve data | SELECT |
| DCL (control) | Manage access | GRANT / REVOKE |
SELECT customer_id, name
FROM customers
WHERE city = 'Tokyo'
ORDER BY name;Scenario: order management. Have two tables, customers and orders, with customer_id in orders as a foreign key referencing customers, avoiding duplicate customer info (normalization). "List Tokyo customers" uses SELECT (DQL), a new order is INSERT (DML), and a table change is ALTER (DDL). An index on customer_id speeds joins and lookups.
Watch the mix-ups: (1) primary key (uniquely identifies rows in its own table) vs foreign key (references another table’s primary key to relate). (2) SELECT = DQL (retrieve) / INSERT,UPDATE,DELETE = DML (modify) / CREATE,ALTER,DROP = DDL (define). (3) Indexes speed reads but add maintenance cost on every write—more is not always better.
Q. Primary key vs UNIQUE constraint? Both forbid duplicates, but a primary key is one per table and cannot be NULL, while UNIQUE can be set multiple times and may allow NULLs. Q. Does a view hold real data? No—a view is a query definition (virtual table) computed from base tables when referenced. Q. Why define types? To block invalid values and make search/aggregation/sorting correct and fast.
Common points: primary key = unique identity / foreign key = relate tables / index = speed search / view = virtual table, and SELECT = DQL / INSERT etc. = DML / CREATE etc. = DDL / GRANT etc. = DCL. Normalization = reduce duplication to prevent update anomalies.
2.1.5Section summary
- Relational = structured data via tables (rows/cols/types) + keys
- Primary key (unique) / foreign key (relate, referential integrity) / index (speed) / view (virtual) / normalization (reduce duplication)
- SQL: DDL (define) / DML (modify) / DQL = SELECT (retrieve) / DCL (permissions)
Sign in to track progress — Log in.
Quick check
(just a quick review)Q1. In a relational database, which column uniquely identifies each row?
Q2. Which SQL statement queries and retrieves data?
Q3. Which column references another table’s primary key to relate tables?
Q4. Which is used to speed up searching on specific columns?
Q5. Which SQL category creates, alters, and drops table definitions (structure)?
Q6. What is the main purpose of normalization?