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.

Leave a comment