Features of Relational Data

5 minutes 5 Questions

Relational data is one of the most common and widely used data models, organized into structured tables consisting of rows and columns. Here are the key features of relational data: **1. Tables (Relations):** Data is stored in tables, where each table represents a specific entity such as customers, orders, or products. Each table has a defined structure known as a schema. **2. Rows and Columns:** Each row (also called a record or tuple) represents a single instance of an entity, while each column (also called an attribute or field) represents a specific property of that entity. For example, a 'Customer' table may have columns like CustomerID, Name, and Email. **3. Primary Keys:** Every table has a primary key, which is a unique identifier for each row. This ensures that no two rows in the same table have identical values for the primary key, maintaining data integrity. **4. Foreign Keys:** Relationships between tables are established through foreign keys. A foreign key in one table references the primary key of another table, enabling connections between related data across multiple tables. **5. Normalization:** Relational data follows normalization principles to minimize data redundancy and dependency. Data is divided into multiple related tables to avoid duplication and ensure consistency. **6. ACID Compliance:** Relational databases support ACID properties (Atomicity, Consistency, Isolation, Durability), ensuring reliable transaction processing and data integrity even during system failures. **7. SQL (Structured Query Language):** Relational data is queried and manipulated using SQL, a standardized language for creating, reading, updating, and deleting data (CRUD operations). **8. Referential Integrity:** The database enforces rules to ensure that relationships between tables remain consistent. For instance, you cannot reference a non-existent primary key through a foreign key. **9. Scalability and Indexing:** Relational databases support indexing for faster query performance and can scale to handle large volumes of structured data efficiently. These features make relational data ideal for transactional systems, business applications, and scenarios requiring structured, consistent data management.

Features of Relational Data – A Complete Guide for DP-900

Why Is Understanding Features of Relational Data Important?

Relational data is the foundation of most enterprise data solutions and a core topic on the Microsoft DP-900: Azure Data Fundamentals exam. Understanding the features of relational data helps you distinguish it from non-relational (NoSQL) data, choose the right Azure service for a given scenario, and confidently answer exam questions that test your knowledge of data concepts.

What Is Relational Data?

Relational data is data that is organized into tables (also called relations). Each table consists of:

• Rows (also called records or tuples) – each row represents a single entity instance (e.g., one customer, one order).
• Columns (also called fields or attributes) – each column stores a specific piece of information about the entity (e.g., CustomerName, OrderDate).

The relational model was introduced by E.F. Codd in 1970 and remains the most widely used data model today. In Azure, relational data is managed by services such as Azure SQL Database, Azure SQL Managed Instance, Azure Database for MySQL, Azure Database for PostgreSQL, and Azure Database for MariaDB.

Key Features of Relational Data

1. Structured Schema
Relational databases enforce a predefined schema. Before inserting data you must define tables, columns, data types, and constraints. This ensures that every row in a table conforms to the same structure, providing consistency and predictability.

2. Tables, Rows, and Columns
Data is stored in two-dimensional tables. Each table has a fixed set of columns, and each column has a defined data type (e.g., INT, VARCHAR, DATE). Rows hold the actual data values.

3. Primary Keys
Every table should have a primary key – a column (or combination of columns) that uniquely identifies each row. Primary keys enforce entity integrity, ensuring no duplicate or NULL key values exist.

4. Foreign Keys and Relationships
Tables are related to each other through foreign keys. A foreign key in one table references the primary key of another table, establishing relationships such as:
• One-to-One – one row in Table A relates to exactly one row in Table B.
• One-to-Many – one row in Table A relates to many rows in Table B (the most common relationship).
• Many-to-Many – implemented via a junction table (linking table) that contains foreign keys to both related tables.

5. Referential Integrity
Foreign key constraints enforce referential integrity, ensuring that relationships between tables remain consistent. You cannot insert a foreign key value that does not exist as a primary key in the referenced table, and you cannot delete a referenced row without first handling dependent rows.

6. Normalization
Relational data is typically normalized – a process of organizing data to reduce redundancy and improve integrity. Common normal forms include:
• 1NF – eliminate repeating groups; each cell contains a single value.
• 2NF – meet 1NF and remove partial dependencies.
• 3NF – meet 2NF and remove transitive dependencies.
Normalization minimizes data duplication but may require joins to reconstruct the full picture of an entity.

7. SQL (Structured Query Language)
Relational databases are queried and managed using SQL. SQL includes:
• DDL (Data Definition Language) – CREATE, ALTER, DROP (define and modify schema objects).
• DML (Data Manipulation Language) – SELECT, INSERT, UPDATE, DELETE (work with data).
• DCL (Data Control Language) – GRANT, REVOKE (manage permissions).
SQL is a declarative language: you specify what data you want, and the database engine determines how to retrieve it.

8. ACID Transactions
Relational databases support ACID properties, ensuring reliable transaction processing:
• Atomicity – a transaction is all-or-nothing; if any part fails, the entire transaction is rolled back.
• Consistency – a transaction brings the database from one valid state to another, honoring all constraints.
• Isolation – concurrent transactions do not interfere with each other.
• Durability – once a transaction is committed, the changes persist even in the event of a system failure.

9. Indexes
Indexes improve query performance by providing fast lookup paths to rows. Common types include clustered indexes (determine the physical order of data) and non-clustered indexes (separate structures that point back to the data). Proper indexing is critical for performance in relational systems.

10. Views
A view is a virtual table based on a SQL query. Views simplify complex queries, restrict access to specific columns or rows, and present data in a particular format without storing duplicate data.

How Relational Data Works in Practice

1. A database administrator (DBA) designs the schema: tables, columns, data types, primary keys, and foreign keys.
2. Applications insert, update, and delete data using SQL statements wrapped in transactions.
3. The relational database management system (RDBMS) enforces constraints, manages concurrency, and ensures ACID compliance.
4. Users and applications query data with SELECT statements, often joining multiple tables to produce meaningful results.
5. Indexes, query optimizers, and caching mechanisms work behind the scenes to return results efficiently.

Relational Data on Azure

Azure offers several managed relational database services:
• Azure SQL Database – fully managed PaaS, based on SQL Server engine.
• Azure SQL Managed Instance – near 100% compatibility with on-premises SQL Server, ideal for lift-and-shift migrations.
• SQL Server on Azure VMs – IaaS option for full control over the SQL Server instance.
• Azure Database for PostgreSQL – managed PostgreSQL.
• Azure Database for MySQL – managed MySQL.
• Azure Database for MariaDB – managed MariaDB (note: being retired in favor of MySQL Flexible Server).

All of these services support the core features of relational data discussed above.

Exam Tips: Answering Questions on Features of Relational Data

1. Know the vocabulary: The exam may use terms like relation (table), tuple (row), attribute (column). Make sure you recognize all synonyms.

2. Understand primary keys vs. foreign keys: Primary keys uniquely identify rows within a table. Foreign keys create relationships between tables. Expect scenario-based questions that ask you to identify which column should be the primary key or how two tables are related.

3. Remember ACID: You may be asked what guarantees a relational database provides for transactions. Memorize all four properties: Atomicity, Consistency, Isolation, Durability.

4. Distinguish relational from non-relational: If a question describes structured data with a fixed schema and relationships between entities, the answer will point to a relational solution. If the question describes flexible schemas, document-based, key-value, or graph data, it points to a non-relational (NoSQL) solution.

5. Normalization vs. denormalization: The exam may ask about reducing redundancy (normalization) or improving read performance by combining tables (denormalization). Know that normalization is a hallmark of relational data design.

6. SQL is the language of relational databases: If a question asks which language is used to query relational data, the answer is SQL. Know the difference between DDL, DML, and DCL.

7. Recognize relationship types: Be able to identify one-to-one, one-to-many, and many-to-many relationships from a diagram or scenario description. Many-to-many always requires a junction table.

8. Views are virtual, not physical: If asked whether a view stores data, the answer is no – it is a saved query that presents data from underlying tables.

9. Indexes improve read performance: If a question asks how to speed up queries on a relational database, indexing is a key answer. But be aware that too many indexes can slow down write operations.

10. Look for keywords in the question: Words like structured, tabular, schema-enforced, joins, referential integrity, and ACID transactions all strongly indicate relational data.

11. Practice elimination: On multiple-choice questions, eliminate options that describe NoSQL characteristics (flexible schema, eventual consistency, document storage) when the scenario clearly describes relational features.

By mastering these features and exam strategies, you will be well-prepared to tackle any DP-900 question related to relational data concepts.
Test mode:
Start practice test

DP-900 - Features of Relational Data Example Questions

Test your knowledge of Features of Relational Data

Question 1

Which internal mechanism guarantees the durability of committed transactions in a relational database management system during a sudden power failure?

Correct Answer: Flushing transaction records to stable sequential storage prior to modifying the actual data pages

The correctly identified mechanism represents Write-Ahead Logging, which guarantees durability by ensuring all transaction changes are safely recorded on non-volatile sequential storage before updating the main database files.
Maintaining redundant memory buffers is insufficient because RAM remains volatile and clears during power loss. Relying on periodic backups leaves a time gap where recently committed transactions are unrecoverable. Replicating schema definitions only protects database structures rather than the actual row-level transaction data.
The key concept tested is ACID transaction durability mechanisms in relational databases.

Question 2

A team is building an Azure SQL Database to track employee assignments across departments. Each employee belongs to one department, and each department can have many employees. A junior developer suggests storing department names repeatedly in the employee table instead of creating a separate department table. What relational data feature best addresses the concern with this approach?

Correct Answer: Referential integrity through foreign keys links employees to a department table, reducing redundant data storage

Referential integrity through foreign keys is the correct relational data feature that addresses this concern because it allows you to create a separate department table and link employees to it via a foreign key, eliminating the need to store department names repeatedly and ensuring data consistency.

Clustered indexing improves query performance but does not solve the underlying problem of data redundancy. Domain constraints validate allowed values but still permit the same department name to be stored many times across rows. One option incorrectly states that normalization distributes employee records across multiple tables to improve query performance, when normalization's primary goal is reducing redundancy, not improving performance.

Key concept tested: referential integrity and normalization in relational databases.

Question 3

What is the primary operational function of the transaction log within a relational database engine?

Correct Answer: It captures all data modifications prior to committing to facilitate database recovery during system failures.

The transaction log records all modifications made to the database before they are permanently written to the main data files, ensuring strict transactional consistency and enabling full recovery in the event of an unexpected system crash.
Archiving historical execution paths describes a query store operational feature rather than a transaction log. Maintaining dedicated indexing structures relates primarily to relational foreign keys instead of system logging. Caching extracted datasets refers to a memory buffer pool rather than sequential transaction tracking.
The key concept tested is relational database recovery mechanisms and transactional integrity.

More Features of Relational Data questions
42 questions (total)
Start 42 question test

Other Identify Considerations for Relational Data on Azure concepts

Azure Database for MySQLAzure Database for PostgreSQLAzure SQL DatabaseAzure SQL Managed InstanceChoosing Among Azure SQL Family ProductsCommon Database ObjectsDatabase Normalization ConceptsPrimary Keys, Foreign Keys, and RelationshipsSQL Data Definition Language StatementsSQL Data Manipulation Language StatementsSQL Joins and SubqueriesSQL Query Filtering, Sorting, and AggregationSQL Server on Azure Virtual Machines
All Identify Considerations for Relational Data on Azure questions · DP-900 practice test