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.
