Tag: Database objects

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.

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.