Relational Data Concepts

Relational data is data organized into tables (relations) where each table represents an entity (e.g., Customer, Order) and rows represent instances. The relational model was proposed by E.F. Codd in 1970 and is based on set theory and predicate logic. In Azure, relational databases include Azure SQL Database, Azure SQL Managed Instance, and Azure Database for PostgreSQL/MySQL. The exam expects you to understand the logical structure, not physical storage.

A table consists of columns (attributes) and rows (tuples). Each column has a data type (e.g., INT, VARCHAR, DATE). In a well-designed table, every row is unique — typically enforced by a primary key. For example, a Customer table might have columns: CustomerID (INT, PK), FirstName (VARCHAR(50)), LastName (VARCHAR(50)), Email (VARCHAR(100)).

A primary key uniquely identifies each row in a table. It must contain unique values and cannot be NULL. A table can have only one primary key, which may consist of a single column or multiple columns (composite key). In Azure SQL Database, you define a primary key using:

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

The exam tests that a primary key ensures entity integrity. Common wrong answer: 'A primary key can be NULL.' That is false — primary keys enforce NOT NULL implicitly.

A foreign key is a column (or set of columns) in one table that references the primary key of another table. It establishes a link between the two tables and enforces referential integrity. For example, an Order table might have CustomerID as a foreign key referencing Customer.CustomerID.

CREATE TABLE Order (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

The exam tests that foreign keys prevent orphan records: you cannot insert an order with a CustomerID that doesn't exist in Customer. Also, you cannot delete a customer that has orders unless you handle it (CASCADE, SET NULL, etc.).

There are three types of relationships between tables: - One-to-One (1:1): Each row in Table A relates to exactly one row in Table B, and vice versa. Example: Employee and EmployeeDetails. Rare in practice; often merged. - One-to-Many (1:N): A row in Table A can relate to many rows in Table B, but a row in Table B relates to exactly one row in Table A. Example: Customer and Order. This is the most common relationship. - Many-to-Many (M:N): Rows in Table A can relate to many rows in Table B, and vice versa. Implemented via a junction table. Example: Student and Course with Enrollment table.

The exam may ask you to identify the relationship type given a scenario. For example, 'A book can have multiple authors, and an author can write multiple books' — that's many-to-many.

Constraints enforce rules on data. Common constraints: - NOT NULL: Column cannot have NULL values. - UNIQUE: All values in a column must be different. - PRIMARY KEY: Combination of NOT NULL and UNIQUE. - FOREIGN KEY: Ensures referential integrity. - CHECK: Validates data based on a logical expression (e.g., Age >= 0). - DEFAULT: Provides a default value if none is specified.

The exam expects you to know which constraint ensures uniqueness (UNIQUE or PRIMARY KEY) and which prevents NULLs (NOT NULL).

Indexes speed up data retrieval but slow down writes. They are not explicitly part of the relational model but are crucial for performance. In Azure SQL, you can create a clustered index (determines physical order) or nonclustered index. Primary keys automatically create a clustered index by default. The exam may ask: 'What is the primary purpose of an index?' Answer: To improve query performance by reducing data scans.

Normalization is the process of organizing data to reduce redundancy and improve integrity. There are several normal forms: - 1NF (First Normal Form): Each column contains atomic (indivisible) values, and each row is unique. Example: A 'PhoneNumbers' column with multiple numbers violates 1NF. - 2NF (Second Normal Form): Must be in 1NF and all non-key columns depend on the entire primary key (no partial dependency). Relevant for composite keys. - 3NF (Third Normal Form): Must be in 2NF and no transitive dependency (non-key column depends on another non-key column). Example: Customer table with City and State — State depends on City, not CustomerID, so it violates 3NF.

The exam typically tests 1NF and 3NF. You might be asked: 'Which normal form removes transitive dependencies?' Answer: 3NF.

Denormalization intentionally introduces redundancy for performance (fewer joins). It is the opposite of normalization. In Azure data warehousing (e.g., Synapse), star schemas use denormalized fact and dimension tables. The exam may compare normalized OLTP vs denormalized OLAP.

A view is a virtual table based on a SELECT query. It does not store data but presents data from underlying tables. Views can simplify complex queries and provide security by restricting access to specific columns. In Azure SQL, you create a view with:

CREATE VIEW CustomerOrders AS
SELECT c.CustomerID, c.FirstName, o.OrderID, o.OrderDate
FROM Customer c JOIN Order o ON c.CustomerID = o.CustomerID;

The exam may ask: 'Does a view store data?' Answer: No, it's a saved query.

Stored procedures are precompiled SQL code that can accept parameters and perform operations. They improve performance and security. Example:

CREATE PROCEDURE GetCustomerOrders @CustomerID INT
AS
SELECT * FROM Order WHERE CustomerID = @CustomerID;

The exam expects you to know that stored procedures can return multiple result sets and are executed with EXEC.

A transaction is a unit of work that is ACID (Atomic, Consistent, Isolated, Durable). In Azure SQL, transactions are used to ensure data integrity. BEGIN TRANSACTION, COMMIT, ROLLBACK. The exam tests ACID properties: Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent transactions don't interfere), Durability (committed changes persist).

NULL represents unknown or missing data. It is not zero or empty string. Comparisons with NULL use IS NULL or IS NOT NULL, not = NULL. Aggregate functions like COUNT(*) count all rows, but COUNT(column) excludes NULLs. The exam often tests that NULL is not equal to anything, even another NULL.

Common SQL data types: - Exact Numeric: INT, BIGINT, SMALLINT, TINYINT, DECIMAL(p,s), NUMERIC, MONEY, SMALLMONEY - Approximate Numeric: FLOAT, REAL - Date and Time: DATE, TIME, DATETIME, DATETIME2, SMALLDATETIME, DATETIMEOFFSET - Character Strings: CHAR(n), VARCHAR(n), TEXT (deprecated), NCHAR(n), NVARCHAR(n), NTEXT (deprecated) - Binary: BINARY, VARBINARY, IMAGE (deprecated) - Other: UNIQUEIDENTIFIER, XML, GEOGRAPHY, GEOMETRY

The exam may ask which type to use for large Unicode text: NVARCHAR(MAX).

Relational databases use structured schema, ACID transactions, and SQL. Non-relational (NoSQL) databases like Cosmos DB use flexible schemas, eventual consistency, and different query languages. The exam compares these: 'Which Azure data service is best for structured data with complex relationships?' Answer: Azure SQL Database (relational).

A mid-sized e-commerce company migrates from on-premises SQL Server to Azure SQL Database. They have tables: Customer, Product, Order, OrderItem, Category. The relational design ensures that each order is linked to a customer via CustomerID foreign key, and each order item links to order and product. The database uses 3NF to avoid data duplication. In production, they handle millions of orders per month. They create indexes on OrderDate and CustomerID for common queries. A common misconfiguration: not indexing foreign keys, leading to slow joins. They also use elastic pools to scale. The problem solved: ensuring data integrity across transactions (e.g., no orphan order items).

A hospital uses Azure SQL Managed Instance for patient records. Tables: Patient, Appointment, Doctor, Diagnosis. The relationship between Patient and Appointment is one-to-many. The Diagnosis table links to Appointment (one-to-one). They enforce referential integrity to prevent appointments without patients. They use CHECK constraints on Age (0-120) and UNIQUE on Patient.SSN. They also use views to restrict access: doctors see only their patients. A common issue: forgetting to set cascading deletes, causing errors when deleting a patient with appointments. They use transactions to ensure atomicity when updating multiple tables.

A financial firm uses Azure Synapse Analytics (dedicated SQL pool) for large-scale reporting. They use a star schema: a central FactTransaction table (millions of rows) with foreign keys to dimension tables: DimCustomer, DimProduct, DimDate. This denormalized design (3NF is not used) optimizes for read performance. They use clustered columnstore indexes for compression and fast scans. A misconfiguration: not distributing fact tables correctly (e.g., using ROUND_ROBIN when hash distribution on CustomerID would reduce data movement). They also use materialized views to pre-aggregate daily totals.