Relational Data, Normalization, SQL, and Database Objects
Relational tables, keys, normalization, SQL dialects, DDL, DCL, DML, joins, views, stored procedures, and index performance trade-offs
Suggested study time: 70 minutes • Beginner level • Aligned with the DP-900 study guide and official Microsoft Learn documentation
By João Ricardo Dutra••Complete material
1. Why the relational model exists
Early applications often stored data in proprietary structures that were difficult to share, maintain, and optimize. The relational model replaced those isolated formats with a standard representation that many applications can query. Tables make structured information intuitive, flexible, and efficient.
Organizations use relational databases for inventory, ecommerce, financial operations, and mission-critical customer records. They work especially well when related facts must follow explicit rules and remain consistent.
Topic summary
The relational model standardizes structured data so applications can store, relate, and query it consistently.
2. Entities, tables, rows, and columns
An entity is a real-world object or event worth recording, such as a customer, product, order, or order line. Each entity type is modeled as a table, every row represents one instance, and columns hold its attributes.
Relational data is structured: rows in the same table share the same columns. An optional value, such as a middle name, can be represented by a nullable column; NULL means the value is absent or unknown, not an empty string or zero.
Topic summary
Tables represent entity types, rows represent instances, and columns store consistently defined attributes.
3. Data types and column constraints
A column has a data type that limits valid values and guides storage and operations. Text may use fixed- or variable-length character types, prices use decimal numeric types, quantities use integers, and dates use date/time types.
Exact type names vary by database engine, although ANSI-standard concepts are broadly supported. Constraints such as NOT NULL make a value mandatory; omitting that constraint permits NULL.
Topic summary
Data types and constraints protect the meaning and validity of values stored in each column.
4. Relationships, keys, and a retail schema
Tables become relational through keys. A primary key uniquely identifies a row. A foreign key stores the primary-key value of a related row, such as CustomerID in an order or ProductID in an order line.
A retail schema can separate Customer, Product, SalesOrder, and LineItem while preserving their connections. This avoids embedding complete customer and product details in every transaction.
Figure 1 - Relationships, keys, and a retail schema.
Topic summary
Primary keys identify rows; foreign keys connect related rows across normalized tables.
5. Normalization principles
Normalization is a schema-refactoring process that reduces duplication and supports integrity. A practical sequence is to place each entity in its own table, place each discrete attribute in its own column, give every row a primary key, and connect entities with foreign keys.
A denormalized sales sheet repeats customer addresses and product prices for every line. After normalization, an address is changed once in Customer and a price once in Product, reducing update anomalies and inconsistent copies.
Figure 2 - Normalization principles.
Topic summary
Normalization separates entities and attributes so facts are stored once and connected by keys.
6. Referential integrity and composite keys
An RDBMS can enforce referential integrity by rejecting a foreign-key value that has no corresponding primary-key row. This prevents an order from referencing a customer that does not exist.
A key can contain more than one column. For example, OrderID plus LineNumber can uniquely identify an order line; together they form a composite primary key. Third normal form aims for every non-key attribute to depend on the key, the whole key, and nothing but the key.
Topic summary
Referential integrity preserves valid relationships, while composite keys identify rows through a unique column combination.
7. SQL standards, engines, and dialects
SQL is the standard language for communicating with relational database management systems. It is used by Microsoft SQL Server, ,, SQL Server on Azure , MySQL, PostgreSQL, Oracle, and many other engines.
ANSI standardized SQL in 1986 and ISO followed in 1987. Vendors subsequently added extensions, creating dialects: Transact-SQL for Microsoft SQL platforms, PostgreSQL procedural extensions, and Oracle PL/SQL. also offers AI assistance for writing and understanding queries in natural language.
Topic summary
SQL is standardized, but each database platform adds a dialect whose details matter in production.
8. Three SQL statement families
SQL statements are commonly grouped by intent. Data Definition Language (DDL) changes database objects; Data Control Language (DCL) manages permissions; Data Manipulation Language (DML) reads and changes table rows.
CREATE, ALTER, DROP, and RENAME are DDL; GRANT, DENY, and REVOKE are DCL; SELECT, INSERT, UPDATE, and DELETE are DML. Classifying the verb is a fast way to interpret DP-900 scenarios.
Figure 3 - Three SQL statement families.
CREATE TABLE Product (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(40) NOT NULL,
Price DECIMAL(10,2) NULL
);
Topic summary
DDL defines objects, DCL controls access, and DML works with the rows stored in tables.
9. DDL and table creation
DDL creates, modifies, renames, and removes objects such as tables, views, and stored procedures. A table definition supplies column names, data types, nullability, and keys. SQL recommends a primary key even though an engine may allow a table without one.
DROP is destructive: dropping a table removes its rows with the object. Recovery normally depends on a valid backup, so object-removal statements require deliberate review.
Topic summary
DDL shapes the schema; CREATE and ALTER build it, while DROP can permanently remove objects and data.
10. DCL and permissions
Database administrators use DCL to grant, explicitly deny, or revoke access for users and groups. Permissions can target actions such as reading, inserting, or updating a specific table.
GRANT adds permission, DENY blocks an action, and REVOKE removes a previously granted or denied permission, depending on the platform. This is separate from manipulating business rows.
GRANT SELECT, INSERT, UPDATE
ON Product
TO analyst1;
Topic summary
DCL expresses who may perform which actions on database objects.
11. DML queries, filters, and sorting
SELECT retrieves rows. Listing columns returns only the required fields, while an asterisk requests every column. WHERE limits the result to rows that satisfy a predicate, and ORDER BY produces a defined sort order.
Without ORDER BY, row order is not guaranteed. In the assessment pattern, SELECT ProductName, Price FROM Products WHERE Price < 10 is correct because the column list precedes FROM and the predicate follows WHERE.
SELECT ProductName, Price
FROM Product
WHERE Price < 10
ORDER BY ProductName;
Topic summary
SELECT reads data, WHERE filters it, and ORDER BY makes presentation order explicit.
12. Joining related tables
JOIN combines columns from related tables by matching values, typically a foreign key to its referenced primary key. Table aliases shorten qualified column names and remove ambiguity.
A join between SalesOrder and Customer can return order identifiers and dates together with delivery addresses without duplicating the address inside every order row.
Figure 4 - Joining related tables.
SELECT o.OrderID, o.OrderDate, c.LastName, c.City
FROM SalesOrder AS o
JOIN Customer AS c ON o.CustomerID = c.CustomerID;
Topic summary
JOIN follows key relationships to present related facts from multiple tables in one result.
13. INSERT, UPDATE, DELETE, and safe predicates
INSERT names the target table and columns, then supplies matching values. Some dialects accept multiple value groups, while the portable basic form inserts one row. UPDATE changes existing values and DELETE removes rows.
UPDATE and DELETE affect every row when no WHERE predicate is present. SQL provides no universal confirmation prompt, so predicates, transactions, backups, and change review are essential safeguards.
INSERT INTO Product (ProductID, ProductName, Price)
VALUES (99, 'Cordless drill', 49.90);
UPDATE Customer
SET Address = '123 High Street'
WHERE CustomerID = 1;
DELETE FROM Product
WHERE ProductID = 162;
Topic summary
INSERT adds rows; UPDATE and DELETE require carefully scoped predicates to avoid broad unintended changes.
14. Views as reusable virtual tables
A view is a virtual table defined by a SELECT query. It can present selected rows and columns from one or more underlying tables as a simpler, reusable object.
Applications can query and filter a view much like a table. A Deliveries view can hide join complexity and expose the order and customer fields needed for fulfillment without storing another independent copy of the data.
CREATE VIEW Deliveries AS
SELECT o.OrderID, o.OrderDate, c.FirstName, c.LastName, c.Address, c.City
FROM SalesOrder AS o
JOIN Customer AS c ON o.CustomerID = c.CustomerID;
Topic summary
A view packages a query as a reusable virtual table and can simplify access to joined or filtered data.
15. Stored procedures and parameters
A stored procedure is a named set of SQL statements that runs on command. It encapsulates database logic used repeatedly by applications.
Parameters make procedures reusable. A RenameProduct procedure can accept a product identifier and a new name, then update only the matching row. Execution syntax varies by dialect.
CREATE PROCEDURE RenameProduct
@ProductID INT,
@NewName VARCHAR(40)
AS
UPDATE Product
SET ProductName = @NewName
WHERE ProductID = @ProductID;
Topic summary
Stored procedures centralize repeatable database actions and accept parameters for flexible behavior.
16. Indexes: speed and cost
An index stores selected column values in an ordered structure with references to table rows. The query optimizer can use it to locate matching rows without scanning the entire table. On a small table, a scan may still be cheaper, so the optimizer can ignore the index.
Indexes can dramatically improve retrieval on large tables, especially for frequently filtered columns such as SaleDate. They also consume storage and must be maintained during INSERT, UPDATE, and DELETE, which adds write overhead. The right design balances read performance against maintenance cost.
Figure 5 - Indexes: speed and cost.
CREATE INDEX idx_ProductName
ON Product(ProductName);
Topic summary
Indexes accelerate selective reads but consume space and add work to every affected data modification.
17. Assessment reasoning and chapter recap
The nine assessment scenarios test syntax, normalization, and object selection. A nullable column fits an optional middle name; redundancy and update anomalies indicate denormalization; an index provides sorted lookup paths and mitigates large scans; and the number of indexes must balance query speed with write maintenance.
Create an index on SaleDate for frequent filtering; third normal form keeps non-key attributes dependent only on the key; SELECT returns matching rows and does not update or delete them. Relational databases combine normalized tables and key relationships with SQL, views, procedures, and indexes.
Topic summary
For exam questions, identify whether the requirement concerns structure, permission, row manipulation, reusable logic, or access performance.
