Category: Databases

Databases, DP-900, Microsoft Certification

Identify common database objects (DP-900 Exam Prep)

 
This post is a part of the DP-900: Microsoft Azure Data Fundamentals Exam Prep Hub. 
This topic falls under these sections:
Identify considerations for relational data on Azure (20–25%)
   --> Describe relational concepts
      --> Identify common database objects

Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.

Relational databases are composed of several key database objects that define how data is stored, accessed, secured, and optimized. For the DP-900 exam, you should understand the purpose of these objects and how they support relational data systems.

What Are Database Objects?

Database objects are logical structures within a database used to:

  • Store data
  • Organize data
  • Enforce rules
  • Improve performance
  • Control access

They are created and managed using Structured Query Language (SQL).

Core Database Objects You Need to Know

1. Tables

A table is the primary object used to store data.

  • Organized into rows (records) and columns (fields)
  • Each table represents an entity (e.g., Customers, Orders)
  • Data is physically stored in tables

Example:

CustomerIDNameCity
1JohnSeattle

✔ Tables are the foundation of relational databases.

2. Views

A view is a virtual table based on a SQL query.

  • Does not store data physically (in most cases)
  • Displays data from one or more tables
  • Simplifies complex queries
  • Can restrict access to sensitive data

Example Use Case:

  • Show only customer names and cities, hiding confidential columns

✔ Views provide abstraction and security.

3. Indexes

An index is used to improve query performance.

  • Speeds up data retrieval
  • Works like an index in a book
  • Created on one or more columns
  • Improves SELECT performance but may slightly slow writes

Example:

  • Index on CustomerID for fast lookups

✔ Indexes are critical for performance optimization.

4. Stored Procedures

A stored procedure is a saved collection of SQL statements.

  • Stored and executed in the database
  • Can accept parameters
  • Can include logic (conditions, loops)
  • Improves performance and reusability

Example Use Case:

  • Retrieve all orders for a specific customer

✔ Stored procedures enable automation and reusable logic.

5. Schemas

A schema is a logical container for database objects.

  • Organizes tables, views, and other objects
  • Helps manage permissions
  • Improves structure and maintainability

Example:

  • Sales.Customers
  • HR.Employees

✔ Schemas help with organization and security management.

6. Keys

Keys define relationships and ensure data uniqueness.

Primary Key

  • Uniquely identifies each row
  • Cannot contain NULL values

Foreign Key

  • Links one table to another
  • Enforces referential integrity

✔ Keys are essential for relationships and data integrity.

7. Constraints

Constraints enforce rules on data to maintain accuracy.

Common constraints include:

  • PRIMARY KEY → unique identifier
  • FOREIGN KEY → enforces relationships
  • NOT NULL → requires a value
  • UNIQUE → prevents duplicates
  • CHECK → enforces conditions

✔ Constraints ensure data validity and consistency.

How These Objects Work Together

In a typical relational database:

  • Tables store the data
  • Keys and constraints enforce rules
  • Indexes improve performance
  • Views simplify access
  • Stored procedures automate operations
  • Schemas organize everything

Database Objects in Azure

These objects are used in Azure relational services such as:

  • Azure SQL Database
  • Azure Database for PostgreSQL
  • Azure Database for MySQL

These platforms support standard SQL-based database objects and functionality.

Why This Matters for DP-900

On the exam, you may be asked to:

  • Identify different database objects
  • Match objects to their purpose
  • Distinguish between tables, views, and indexes
  • Understand how objects support performance, security, and organization

Summary — Exam-Relevant Takeaways

✔ Tables → store data
✔ Views → virtual representation of data
✔ Indexes → improve query performance
✔ Stored procedures → reusable SQL logic
✔ Schemas → organize objects
✔ Keys → define relationships
✔ Constraints → enforce data rules

✔ Together, these objects ensure:

  • Efficient data storage
  • Fast data retrieval
  • Strong data integrity
  • Secure and organized systems

Go to the Practice Exam Questions for this topic.

Go to the DP-900 Exam Prep Hub main page.

azure, Databases, DP-900, Microsoft Certification

Practice Questions: Describe the Azure SQL family of products including Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure Virtual Machines (DP-900 Exam Prep)

Practice Questions

Question 1

Which Azure SQL offering is fully managed and requires the least administrative effort?

A. SQL Server on Azure Virtual Machines
B. Azure SQL Managed Instance
C. Azure SQL Database
D. Azure Synapse Analytics

✅ Answer: C

Explanation:
Azure SQL Database is a fully managed PaaS service with minimal administration.

Question 2

Which Azure SQL service provides the highest level of compatibility with on-premises SQL Server while still being a PaaS solution?

A. Azure SQL Database
B. Azure SQL Managed Instance
C. SQL Server on Azure Virtual Machines
D. Azure Cosmos DB

✅ Answer: B

Explanation:
Azure SQL Managed Instance offers near 100% compatibility with SQL Server.

Question 3

Which Azure SQL option allows full control over the operating system?

A. Azure SQL Database
B. Azure SQL Managed Instance
C. SQL Server on Azure Virtual Machines
D. Azure SQL Elastic Pool

✅ Answer: C

Explanation:
SQL Server on Azure VM is an IaaS offering, giving full OS-level control.

Question 4

Which service is BEST suited for a cloud-native application with minimal management overhead?

A. SQL Server on Azure Virtual Machines
B. Azure SQL Managed Instance
C. Azure SQL Database
D. Azure Data Lake

✅ Answer: C

Explanation:
Azure SQL Database is optimized for modern cloud applications.

Question 5

Which Azure SQL service supports instance-level features such as SQL Agent?

A. Azure SQL Database
B. Azure SQL Managed Instance
C. SQL Server on Azure Virtual Machines
D. Azure Blob Storage

✅ Answer: B

Explanation:
Managed Instance supports many instance-level features not available in Azure SQL Database.

Question 6

A company wants to migrate an existing SQL Server database with minimal changes. Which service should they choose?

A. Azure SQL Database
B. Azure SQL Managed Instance
C. SQL Server on Azure Virtual Machines
D. Azure Synapse Analytics

✅ Answer: B

Explanation:
Managed Instance is designed for lift-and-shift migrations with high compatibility.

Question 7

Which Azure SQL option requires you to manage backups, updates, and patching?

A. Azure SQL Database
B. Azure SQL Managed Instance
C. SQL Server on Azure Virtual Machines
D. Azure SQL Elastic Pool

✅ Answer: C

Explanation:
In IaaS (Azure VM), the customer is responsible for management tasks.

Question 8

Which of the following best describes Platform as a Service (PaaS) in the Azure SQL family?

A. Full control over hardware and OS
B. No database management required at all
C. Azure manages infrastructure and database maintenance
D. Only supports non-relational data

✅ Answer: C

Explanation:
PaaS handles infrastructure, patching, backups, and high availability.

Question 9

Which Azure SQL service is MOST appropriate when you need maximum control and customization?

A. Azure SQL Database
B. Azure SQL Managed Instance
C. SQL Server on Azure Virtual Machines
D. Azure SQL Elastic Pool

✅ Answer: C

Explanation:
SQL Server on Azure VM provides full control over configuration and environment.

Question 10

Which statement best describes the relationship between the Azure SQL family products?

A. They use completely different database engines
B. They all use the SQL Server engine with different management levels
C. Only Azure SQL Database supports SQL
D. Only SQL Server on Azure VM supports relational data

✅ Answer: B

Explanation:
All Azure SQL offerings are based on the SQL Server engine, differing mainly in management and control.

✅ Quick Exam Takeaways

✔ Azure SQL Database

  • Fully managed (PaaS)
  • Best for cloud-native apps

✔ Azure SQL Managed Instance

  • Near full SQL Server compatibility
  • Best for migrations

✔ SQL Server on Azure VM

  • Full control (IaaS)
  • You manage everything

✔ Key concept:
👉 More control = more responsibility
👉 More automation = less control

Go to the DP-900 Exam Prep Hub main page.

Databases, DP-900, Microsoft Certification

Practice Questions: Identify common database objects (DP-900 Exam Prep)

Practice Questions

Question 1

Which database object is used to store data in rows and columns?

A. View
B. Table
C. Index
D. Schema

✅ Answer: B

Explanation:
Tables are the primary objects used to store structured data.

Question 2

Which database object provides a virtual representation of data without storing it physically?

A. Table
B. Index
C. View
D. Constraint

✅ Answer: C

Explanation:
Views display data based on a query but typically do not store data themselves.

Question 3

What is the primary purpose of an index?

A. Store data
B. Enforce relationships
C. Improve query performance
D. Organize database objects

✅ Answer: C

Explanation:
Indexes speed up data retrieval operations.

Question 4

Which database object allows you to store and reuse a set of SQL statements?

A. View
B. Stored procedure
C. Schema
D. Index

✅ Answer: B

Explanation:
Stored procedures contain reusable SQL logic and can include parameters and control flow.

Question 5

Which database object is used to logically group other database objects?

A. Table
B. Schema
C. Index
D. Constraint

✅ Answer: B

Explanation:
Schemas organize database objects and help manage permissions.

Question 6

Which object ensures that each row in a table is uniquely identified?

A. Foreign key
B. Index
C. Primary key
D. View

✅ Answer: C

Explanation:
A primary key uniquely identifies each record in a table.

Question 7

Which database object enforces relationships between tables?

A. Schema
B. Foreign key
C. Index
D. Stored procedure

✅ Answer: B

Explanation:
Foreign keys link tables and enforce referential integrity.

Question 8

Which constraint prevents duplicate values in a column?

A. NOT NULL
B. CHECK
C. UNIQUE
D. FOREIGN KEY

✅ Answer: C

Explanation:
The UNIQUE constraint ensures all values in a column are distinct.

Question 9

Which database object is MOST useful for restricting access to specific columns of data?

A. Table
B. Index
C. View
D. Primary key

✅ Answer: C

Explanation:
Views can limit which columns or rows are exposed to users.

Question 10

Which object may slightly decrease write performance due to maintenance overhead?

A. View
B. Index
C. Schema
D. Constraint

✅ Answer: B

Explanation:
Indexes improve read performance but can slow down inserts and updates.

✅ Quick Exam Takeaways

For DP-900, remember:

✔ Tables → store data
✔ Views → virtual tables (security + simplicity)
✔ Indexes → improve performance (reads ↑, writes ↓ slightly)
✔ Stored procedures → reusable SQL logic
✔ Schemas → organize objects
✔ Primary keys → unique identifiers
✔ Foreign keys → relationships
✔ Constraints → enforce rules (NOT NULL, UNIQUE, etc.)

Go to the DP-900 Exam Prep Hub main page.

Data Development, Data Education & Training, Data Modeling, Databases, DP-900, SQL, Uncategorized

Identify common Structured Query Language (SQL) statements (DP-900 Exam Prep)

 
This post is a part of the DP-900: Microsoft Azure Data Fundamentals Exam Prep Hub. 
This topic falls under these sections:
Identify considerations for relational data on Azure (20–25%)
   --> Describe relational concepts
      --> Identify common Structured Query Language (SQL) statements

Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.

Understanding basic SQL statements is essential for working with relational data and is a key requirement for the DP-900 exam. You are not expected to be an advanced SQL developer, but you should recognize common SQL commands, their purpose, and when they are used.

What Is SQL?

Structured Query Language (SQL) is the standard language used to:

  • Query data
  • Insert new data
  • Update existing data
  • Delete data
  • Define database structures

SQL is used across relational database systems, including Azure services like:

  • Azure SQL Database
  • Azure Database for PostgreSQL
  • Azure Database for MySQL

Categories of SQL Statements

SQL statements are typically grouped into categories:

CategoryPurpose
DDL (Data Definition Language)Define and modify database structures
DML (Data Manipulation Language)Work with data in tables
DQL (Data Query Language)Retrieve data
DCL (Data Control Language)Manage permissions

For DP-900, focus primarily on DDL, DML, and DQL.

1. Data Query Language (DQL)

SELECT

Used to retrieve data from a table.

SELECT Name, City
FROM Customers;

You can filter results:

SELECT Name
FROM Customers
WHERE City = 'Seattle';

💡 Key Points:

  • Most commonly used SQL statement
  • Can include filtering, sorting, and grouping

2. Data Manipulation Language (DML)

INSERT

Adds new rows to a table.

INSERT INTO Customers (Name, City)
VALUES ('John', 'Seattle');

UPDATE

Modifies existing data.

UPDATE Customers
SET City = 'Austin'
WHERE Name = 'John';

DELETE

Removes rows from a table.

DELETE FROM Customers
WHERE Name = 'John';

💡 Important:
Always use a WHERE clause with UPDATE and DELETE to avoid affecting all rows.

3. Data Definition Language (DDL)

CREATE

Creates new database objects such as tables.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    City VARCHAR(50)
);

ALTER

Modifies an existing table.

ALTER TABLE Customers
ADD Email VARCHAR(100);

DROP

Deletes a table or database object.

DROP TABLE Customers;

💡 Warning:
DROP permanently removes the object and its data.

4. Additional Common SQL Clauses

WHERE

Filters rows:

SELECT * FROM Orders
WHERE Amount > 100;

ORDER BY

Sorts results:

SELECT * FROM Orders
ORDER BY Amount DESC;

GROUP BY

Aggregates data:

SELECT City, COUNT(*)
FROM Customers
GROUP BY City;

JOIN

Combines data from multiple tables:

SELECT Orders.OrderID, Customers.Name
FROM Orders
JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

💡 DP-900 Tip:
You don’t need deep JOIN knowledge — just understand that JOINs combine related tables.

SQL in Azure

SQL is used across many Azure services:

Azure SQL Database

  • Fully managed relational database
  • Uses T-SQL (Microsoft’s SQL variant)

Azure Synapse Analytics

  • Used for analytical queries on large datasets

Azure Database for PostgreSQL

  • Uses PostgreSQL SQL dialect

Why This Matters for DP-900

On the exam, you may be asked to:

  • Identify what a SQL statement does
  • Match commands to their purpose (SELECT, INSERT, etc.)
  • Recognize DDL vs DML
  • Understand basic query concepts like filtering and sorting

Summary — Exam-Relevant Takeaways

✔ SELECT → Retrieve data
✔ INSERT → Add new data
✔ UPDATE → Modify existing data
✔ DELETE → Remove data

✔ CREATE / ALTER / DROP → Define and modify structures
✔ WHERE → Filter results
✔ ORDER BY → Sort data
✔ GROUP BY → Aggregate data
✔ JOIN → Combine tables

✔ SQL is the standard language for relational databases

Go to the Practice Exam Questions for this topic.

Go to the Additional Practice Questions for this topic.

Go to the DP-900 Exam Prep Hub main page.

Data Modeling, Databases, DP-900, Microsoft Certification

Describe normalization and why it is used (DP-900 Exam Prep)

 
This post is a part of the DP-900: Microsoft Azure Data Fundamentals Exam Prep Hub. 
This topic falls under these sections:
Identify considerations for relational data on Azure (20–25%)
   --> Describe relational concepts
      --> Describe normalization and why it is used

Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.

Normalization is a foundational concept in relational database design. For the DP-900 exam, you are expected to understand what normalization is, why it is important, and how it improves data quality and efficiency.

What Is Normalization?

Normalization is the process of organizing data in a relational database to:

  • Reduce data redundancy (duplicate data)
  • Improve data integrity
  • Ensure logical data relationships

This is done by splitting data into multiple related tables and defining relationships between them using keys.

Why Normalization Is Used

Normalization is used to solve common data problems in poorly designed tables.

1. Reduce Data Redundancy

Without normalization, the same data may be repeated across multiple rows.

Example (Unnormalized Table):

OrderIDCustomerNameCustomerCityProduct
1JohnSeattleLaptop
2JohnSeattleMouse

Here, customer information is duplicated.

After Normalization:

Customers Table

CustomerIDNameCity
1JohnSeattle

Orders Table

OrderIDCustomerIDProduct
11Laptop
21Mouse

Now, customer data is stored once and referenced using a key.

2. Improve Data Integrity

Normalization ensures that data remains accurate and consistent.

Without normalization:

  • Updating a customer’s city requires changing multiple rows
  • Missing one update leads to inconsistent data

With normalization:

  • Data is updated in one place only
  • Consistency is maintained automatically

3. Prevent Data Anomalies

Normalization helps prevent common issues:

  • Insert anomaly: Cannot add data without unrelated data
  • Update anomaly: Inconsistent updates across rows
  • Delete anomaly: Deleting one record removes important data

Normalized designs eliminate these problems.

4. Improve Data Organization

Normalized databases:

  • Clearly separate different entities (customers, orders, products)
  • Use relationships to connect data logically
  • Make databases easier to maintain and scale

Understanding Normal Forms (Simplified for DP-900)

Normalization is often described in stages called normal forms. For DP-900, you only need a basic understanding:

First Normal Form (1NF)

  • No repeating groups or multi-valued fields
  • Each column contains atomic (single) values

Second Normal Form (2NF)

  • Meets 1NF
  • All non-key columns depend on the entire primary key

Third Normal Form (3NF)

  • Meets 2NF
  • No dependency between non-key columns

💡 DP-900 Tip:
You do NOT need to memorize formal definitions — just understand that normalization reduces redundancy and improves integrity.

Trade-Offs of Normalization

While normalization has many benefits, there are trade-offs:

Advantages

✔ Reduces duplicate data
✔ Improves consistency
✔ Simplifies updates
✔ Enhances data integrity

Disadvantages

✖ Requires more tables
✖ Queries may require joins
✖ Can slightly impact performance for complex queries

Normalization vs Denormalization

Understanding this comparison is important for the exam:

FeatureNormalizationDenormalization
Data RedundancyReducedIncreased
Data IntegrityHighLower
Query ComplexityHigher (joins required)Lower
PerformanceSlower for readsFaster for analytics
Use CaseTransactional systems (OLTP)Analytical systems (OLAP)

Where Normalization Is Used in Azure

Normalization is commonly applied in relational database services such as:

  • Azure SQL Database
  • Azure Database for PostgreSQL
  • Azure Database for MySQL

These services are typically used for transactional workloads, where data integrity is critical.

Why This Matters for DP-900

On the exam, you may be asked to:

  • Identify why normalization is used
  • Recognize normalized vs unnormalized structures
  • Understand how normalization affects data integrity
  • Distinguish normalization from denormalization

Summary — Exam-Relevant Takeaways

✔ Normalization organizes data into multiple related tables
✔ It reduces data redundancy
✔ It improves data integrity and consistency
✔ It prevents insert, update, and delete anomalies
✔ It is commonly used in transactional (OLTP) systems
✔ It may require joins when querying data

Go to the Practice Exam Questions for this topic.

Go to the DP-900 Exam Prep Hub main page.

Data Modeling, Databases, DP-900, Microsoft Certification, SQL

Identify features of relational data (DP-900 Exam Prep)

 
This post is a part of the DP-900: Microsoft Azure Data Fundamentals Exam Prep Hub. 
This topic falls under these sections:
Identify considerations for relational data on Azure (20–25%)
   --> Describe relational concepts
      --> Identify features of relational data

Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.

Relational data is one of the most fundamental concepts in data management and a core focus area for the DP-900 exam. Understanding how relational data is structured, stored, and accessed will help you confidently answer questions related to databases, querying, and Azure data services.

What Is Relational Data?

Relational data is data that is organized into tables (relations) consisting of:

  • Rows (records)
  • Columns (attributes or fields)

Each table represents a specific entity, such as customers, orders, or products. Relationships between tables are defined using keys.

Core Features of Relational Data

1. Tabular Structure (Rows and Columns)

Relational data is stored in a structured, tabular format:

  • Each row represents a single record
  • Each column represents a specific attribute

Example:

CustomerIDNameCity
1JohnSeattle
2MariaAustin

This structure makes relational data easy to query and understand.

2. Predefined Schema

Relational databases enforce a fixed schema, which defines:

  • Table structure
  • Column names
  • Data types (e.g., INT, VARCHAR, DATE)

This ensures:

  • Data consistency
  • Data validation
  • Predictable structure

3. Use of Keys

Keys are essential for uniquely identifying records and linking tables.

Primary Key

  • Uniquely identifies each row in a table
  • Cannot contain duplicate or null values

Example: CustomerID

Foreign Key

  • Links one table to another
  • Establishes relationships between tables

Example: Order.CustomerID → Customer.CustomerID

4. Relationships Between Tables

Relational data supports relationships such as:

  • One-to-One
  • One-to-Many
  • Many-to-Many

Example:

  • One customer can have many orders (one-to-many)

These relationships allow complex data models to be built efficiently.

5. Structured Query Language (SQL)

Relational data is accessed and manipulated using Structured Query Language (SQL).

SQL is used to:

  • Query data (SELECT)
  • Insert data (INSERT)
  • Update data (UPDATE)
  • Delete data (DELETE)

Example:

SELECT Name FROM Customers WHERE City = 'Seattle';

6. Data Integrity and Constraints

Relational databases enforce data integrity through constraints such as:

  • PRIMARY KEY
  • FOREIGN KEY
  • NOT NULL
  • UNIQUE
  • CHECK

These rules ensure that:

  • Data is accurate
  • Relationships remain valid
  • Invalid data is prevented

7. Normalization

Relational data is often normalized to reduce redundancy and improve consistency.

Normalization involves:

  • Splitting data into multiple related tables
  • Eliminating duplicate data
  • Ensuring dependencies are logical

Example:

Instead of storing customer details in every order row, store them in a separate Customers table.

8. ACID Transactions

Relational databases support ACID properties, ensuring reliable transactions:

  • Atomicity → All or nothing
  • Consistency → Valid state maintained
  • Isolation → Transactions don’t interfere
  • Durability → Changes persist

This is especially important for transactional workloads.

Relational Data in Azure

Azure provides several services for working with relational data:

Azure SQL Database

  • Fully managed relational database
  • Supports SQL queries
  • High availability and scalability
  • Ideal for OLTP applications

Azure Database for PostgreSQL

  • Managed open-source relational database
  • Supports PostgreSQL features and extensions

Azure Database for MySQL

  • Managed MySQL database service
  • Suitable for web and application workloads

These services support structured data, relationships, and SQL-based querying.

Why This Matters for DP-900

On the exam, you may be asked to:

  • Identify characteristics of relational data
  • Recognize table-based structures
  • Understand keys and relationships
  • Distinguish relational data from non-relational data
  • Match relational workloads to Azure services

Summary — Exam-Relevant Takeaways

✔ Relational data is stored in tables (rows and columns)
✔ It uses a fixed schema with defined data types
✔ Primary and foreign keys define relationships
✔ Data is accessed using SQL
✔ Supports data integrity constraints
✔ Often normalized to reduce redundancy
✔ Ensures reliability with ACID transactions

✔ Common Azure services:

  • Azure SQL Database
  • Azure Database for PostgreSQL
  • Azure Database for MySQL

Go to the Practice Exam Questions for this topic.

Go to the DP-900 Exam Prep Hub main page.

Data Modeling, Databases, DP-900, Microsoft Certification

Practice Questions: Identify features of relational data (DP-900 Exam Prep)

Practice Questions

Question 1

Which structure is used to store relational data?

A. Key-value pairs
B. Graph nodes and edges
C. Tables with rows and columns
D. JSON documents

✅ Answer: C

Explanation:
Relational data is organized in tables consisting of rows and columns.

Question 2

What is the purpose of a primary key in a relational table?

A. To link tables together
B. To uniquely identify each row
C. To store duplicate values
D. To define column data types

✅ Answer: B

Explanation:
A primary key uniquely identifies each record and cannot contain duplicates or null values.

Question 3

Which element is used to create relationships between tables?

A. Index
B. Column constraint
C. Foreign key
D. Schema

✅ Answer: C

Explanation:
A foreign key links one table to another by referencing a primary key.

Question 4

Which statement best describes a schema in a relational database?

A. A collection of unstructured files
B. A visual report of data
C. The structure defining tables, columns, and data types
D. A backup of the database

✅ Answer: C

Explanation:
A schema defines how data is structured, including tables, columns, and data types.

Question 5

Which language is used to query relational databases?

A. Python
B. JSON
C. SQL
D. HTML

✅ Answer: C

Explanation:
Structured Query Language (SQL) is used to query and manage relational data.

Question 6

Which constraint ensures that a column cannot contain null values?

A. UNIQUE
B. CHECK
C. NOT NULL
D. FOREIGN KEY

✅ Answer: C

Explanation:
The NOT NULL constraint ensures that a value must be provided for that column.

Question 7

Which concept reduces data redundancy by organizing data into multiple related tables?

A. Indexing
B. Normalization
C. Partitioning
D. Replication

✅ Answer: B

Explanation:
Normalization reduces redundancy and improves data integrity.

Question 8

Which type of relationship allows one record in a table to relate to many records in another table?

A. One-to-one
B. One-to-many
C. Many-to-one
D. Many-to-many

✅ Answer: B

Explanation:
One-to-many relationships are common in relational databases (e.g., one customer → many orders).

Question 9

Which property ensures that all parts of a transaction succeed or fail together?

A. Consistency
B. Isolation
C. Atomicity
D. Durability

✅ Answer: C

Explanation:
Atomicity ensures transactions are completed fully or not at all.

Question 10

Which Azure service is specifically designed for relational data?

A. Azure Blob Storage
B. Azure Cosmos DB
C. Azure SQL Database
D. Azure Data Lake Storage

✅ Answer: C

Explanation:
Azure SQL Database is a fully managed relational database service.

✅ Quick Exam Takeaways

For DP-900, remember:

✔ Relational data = tables (rows + columns)
✔ Schema defines structure
✔ Primary keys uniquely identify rows
✔ Foreign keys create relationships
✔ Use SQL for querying
✔ Constraints enforce data integrity
✔ Normalization reduces redundancy
✔ Supports ACID transactions

Go to the DP-900 Exam Prep Hub main page.

Databases, DP-900, Microsoft Certification

Describe Types of Databases (DP-900 Exam Prep)

 
This post is a part of the DP-900: Microsoft Azure Data Fundamentals Exam Prep Hub. 
This topic falls under these sections:
Describe core data concepts (25–30%)
   --> Identify options for data storage
      --> Describe types of databases

Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.

Databases are systems that store and manage data so applications can retrieve, update, and organize it efficiently. For DP-900, you should be familiar with the major types of databases, how they differ, and common use cases — especially in relation to Azure services.

What Is a Database?

A database is an organized collection of data that enables efficient access, management, and update of information. Databases may differ in how they model, structure, and query data depending on the data type, scale, and workload requirements.

Primary Types of Databases

At a high level, databases fall into two broad categories:

  1. Relational Databases
  2. Non-Relational Databases (NoSQL)

1. Relational Databases

Relational databases (RDBMS) store data in tables with rows and columns.

Key Features

  • Structured schema: Tables have defined columns with data types.
  • Relationships: Tables can be linked using keys (e.g., primary and foreign keys).
  • SQL Queries: Use Structured Query Language (SQL) to retrieve and manipulate data.
  • ACID transactions: Support atomicity, consistency, isolation, and durability for reliable data operations.

When to Use

  • Applications requiring strong data integrity
  • Banking, accounting, inventory systems
  • Workloads where relationships among data matter

Examples

  • Azure SQL Database
  • Azure Database for PostgreSQL
  • Azure Database for MySQL

2. Non-Relational Databases (NoSQL)

Non-relational databases, often called NoSQL databases, store data in ways that differ from traditional tables. They are generally schema-less and more flexible, which helps with scalability and handling varied data types.

Key Characteristics

  • No fixed schema
  • Designed for horizontal scaling and large data volumes
  • Support for semi-structured and unstructured data
  • Often optimized for specific access patterns

The most common NoSQL models include:

a. Key-Value Databases

Key-value stores are the simplest type of NoSQL database.

  • Data stored as pairs: key (identifier) and value (data).
  • Efficient for simple lookups when the key is known.

Use cases: Session state, caching, user preferences.

b. Document Databases

Document databases store data as documents, typically in JSON format.

  • Each document is a self-describing object with a unique ID.
  • Supports nested fields and flexible attributes.

Use cases: Content management, user profiles, web apps.

c. Column-Family (Wide-Column) Databases

Column-family databases use tables with column families — groups of related columns that can vary by row.

  • Designed for wide tables where columns are sparse.
  • Good for distributed data and analytical workloads.

Use cases: Time-series data, analytics, event logging.

d. Graph Databases

Graph databases focus on relationships between data elements.

  • Use nodes (entities) and edges (relationships).
  • Optimized for queries involving deep connections (e.g., social networks).

Use cases: Recommendation engines, fraud detection, network analysis.

Relational vs Non-Relational: A Quick Comparison

FeatureRelationalNon-Relational (NoSQL)
SchemaFixedFlexible / Schema-less
Data ModelTablesVaries (documents, keys, graphs)
Query LanguageSQLVaries by database
ScalabilityVertical scalingHorizontal scaling
Typical UseStrong consistency & relationshipsLarge, evolving, semi/unstructured data

How Azure Supports These Databases

Relational Database Services

Azure provides managed relational services:

  • Azure SQL Database: Managed SQL service
  • Azure Database for MySQL and PostgreSQL: Managed open-source options

These are ideal for structured data and transactional workloads.

Non-Relational Database Services

Azure supports NoSQL and other flexible databases:

  • Azure Cosmos DB: A globally distributed, multi-model NoSQL database service that supports document, key-value, column-family, and graph models.

This makes Cosmos DB unique in supporting multiple non-relational data models from a single service.

Why Understanding Types of Databases Matters for DP-900

On the DP-900 exam, you may be asked to:

  • Classify a database type based on a description of its structure.
  • Choose the best database model for a given business scenario.
  • Identify Azure services that match a database type.

Knowing relational vs non-relational databases, and the sub-types of NoSQL models, will help you answer these questions with confidence.

Summary — Exam-Relevant Takeaways

✔ Relational databases store structured data using tables, enforce schemas, and use SQL.
✔ NoSQL databases store non-relational data and include key-value, document, column-family, and graph types.
✔ Azure SQL Database and open-source relational offerings support structured workloads.
✔ Azure Cosmos DB supports multiple non-relational models for schema-flexible data.

Go to the Practice Exam Questions for this topic.

Go to the DP-900 Exam Prep Hub main page.

Databases, DP-900, Microsoft Certification

Practice Questions: Describe Types of Databases (DP-900 Exam Prep)

Practice Questions

Question 1

You need to store customer orders in tables with fixed columns and enforce relationships between customers and orders.

Which type of database should you use?

A. Graph
B. Document
C. Relational
D. Key-value

✅ Answer: C

Explanation:
Relational databases store structured data in tables with defined schemas and support relationships via keys.

Question 2

Which characteristic best describes a relational database?

A. Schema-less storage
B. Data stored as JSON documents
C. Tables with rows and columns
D. Nodes and edges

✅ Answer: C

Explanation:
Relational databases organize data into tables (rows and columns) and use SQL for querying.

Question 3

An application must store user profiles in flexible JSON documents where each user may have different attributes.

Which database type is most appropriate?

A. Column-family
B. Document
C. Relational
D. Graph

✅ Answer: B

Explanation:
Document databases store data as JSON-like documents and allow flexible schemas — ideal for user profiles.

Question 4

Which Azure service supports multiple NoSQL data models such as Core (SQL) API, Table API, Cassandra API, and Gremlin API?

A. Azure SQL Database
B. Azure Table Storage
C. Azure Cosmos DB
D. Azure Database for PostgreSQL

✅ Answer: C

Explanation:
Azure Cosmos DB is a globally distributed, multi-model NoSQL database service.

Question 5

You are designing a recommendation engine that analyzes relationships between users and products.

Which database type is best suited?

A. Relational
B. Key-value
C. Graph
D. Column-family

✅ Answer: C

Explanation:
Graph databases specialize in relationship-heavy data using nodes and edges.

Question 6

Which statement about NoSQL databases is TRUE?

A. They always require fixed schemas
B. They primarily use SQL
C. They are optimized for horizontal scaling
D. They cannot store structured data

✅ Answer: C

Explanation:
NoSQL databases are designed for horizontal scaling and flexible schemas.

Question 7

You need extremely fast lookups using a unique identifier, and the data structure is simple.

Which NoSQL model should you choose?

A. Document
B. Graph
C. Column-family
D. Key-value

✅ Answer: D

Explanation:
Key-value databases store data as key/value pairs and provide very fast retrieval.

Question 8

Which Azure service is best suited for structured transactional workloads using SQL?

A. Azure Blob Storage
B. Azure Cosmos DB
C. Azure SQL Database
D. Azure Data Lake Storage

✅ Answer: C

Explanation:
Azure SQL Database is a managed relational database service optimized for structured transactional data.

Question 9

Which feature is typically associated with relational databases but not guaranteed in NoSQL systems?

A. Global distribution
B. Flexible schemas
C. ACID transactions
D. Horizontal scaling

✅ Answer: C

Explanation:
Relational databases traditionally provide full ACID transaction support.

Question 10

A company collects massive volumes of time-series telemetry data where columns may vary across rows.

Which database type fits this scenario best?

A. Relational
B. Document
C. Column-family
D. Graph

✅ Answer: C

Explanation:
Column-family (wide-column) databases are well suited for large, sparse datasets such as time-series data.

✅ Key Exam Reminders

For DP-900, make sure you can confidently:

  • Distinguish relational vs non-relational
  • Recognize NoSQL models (key-value, document, column-family, graph)
  • Match Azure services to database types (especially Azure SQL vs Azure Cosmos DB)
  • Choose the right database type for a scenario

Go to the DP-900 Exam Prep Hub main page.

Analytics, Artificial Intelligence (AI), Business Intelligence, Business Intelligence (BI) Development, Data Analysis, Data Cleaning, Data Development, Data Governance, Data Integration, Data Integration (ETL), Data Modeling, Data Security, Data Strategy, Data Visualization, Data Warehousing, Data Wrangling, Databases, DP-600, Microsoft Certification, Microsoft Fabric, Microsoft OneLake, Performance Tuning, Power BI, Power Query, Python, SQL

Exam Prep Hub for DP-600: Implementing Analytics Solutions Using Microsoft Fabric

This is your one-stop hub with information for preparing for the DP-600: Implementing Analytics Solutions Using Microsoft Fabric certification exam. Upon successful completion of the exam, you earn the Fabric Analytics Engineer Associate certification.

This hub provides information directly here, links to a number of external resources, tips for preparing for the exam, practice tests, and section questions to help you prepare. Bookmark this page and use it as a guide to ensure that you are fully covering all relevant topics for the exam and using as many of the resources available as possible. We hope you find it convenient and helpful.

Why do the DP-600: Implementing Analytics Solutions Using Microsoft Fabric exam to gain the Fabric Analytics Engineer Associate certification?

Most likely, you already know why you want to earn this certification, but in case you are seeking information on its benefits, here are a few:
(1) there is a possibility for career advancement because Microsoft Fabric is a leading data platform used by companies of all sizes, all over the world, and is likely to become even more popular
(2) greater job opportunities due to the edge provided by the certification
(3) higher earnings potential,
(4) you will expand your knowledge about the Fabric platform by going beyond what you would normally do on the job and
(5) it will provide immediate credibility about your knowledge, and
(6) it may, and it should, provide you with greater confidence about your knowledge and skills.

Important DP-600 resources:

DP-600: Skills measured as of October 31, 2025:

Here you can learn in a structured manner by going through the topics of the exam one-by-one to ensure full coverage; click on each hyperlinked topic below to go to more information about it:

Skills at a glance

  • Maintain a data analytics solution (25%-30%)
  • Prepare data (45%-50%)
  • Implement and manage semantic models (25%-30%)

Maintain a data analytics solution (25%-30%)

Implement security and governance

Maintain the analytics development lifecycle

Prepare data (45%-50%)

Get Data

Transform Data

Query and analyze data

Implement and manage semantic models (25%-30%)

Design and build semantic models

Optimize enterprise-scale semantic models

Practice Exams:

We have provided 2 practice exams with answers to help you prepare.

DP-600 Practice Exam 1 (60 questions with answer key)

DP-600 Practice Exam 2 (60 questions with answer key)

Good luck to you passing the DP-600: Implementing Analytics Solutions Using Microsoft Fabric certification exam and earning the Fabric Analytics Engineer Associate certification!