CompTIA DataSys+ (DS0-001)Database FundamentalsMedium
A data analytics team frequently needs to view a combined dataset of `CustomerName`, `ProductName`, and `OrderDate` from the `Customers`, `Products`, and `Orders` tables. They want to avoid writing complex JOIN statements every time and also want to restrict access to only these specific columns. Which database object should the database administrator create to meet these requirements?
- AA database view
- BA stored procedure
- CA new physical table
- DA materialized view
Show answer & explanationAnswer & explanation
Correct answer: A. A database view
A database view is a virtual table based on the result-set of a SQL query. It allows users to access specific columns from multiple tables without seeing the underlying complex queries, providing both simplicity and a security layer for data access.
Why the other options are wrong
- B. A stored procedure could encapsulate the query, but it doesn't present the data as a virtual table or restrict column access inherently in the same way a view does.
- C. Creating a new physical table would involve data duplication and maintenance overhead, which is inefficient for a dynamic combined dataset.
- D. A materialized view stores the query result physically, which would be suitable if performance was paramount and data staleness acceptable, but the primary need here is simplification and access restriction, not necessarily pre-computation.
Database View
A virtual table based on the result-set of a SQL query. A view contains rows and columns, just like a real table, but it does not store data itself; it displays data stored in other tables.
- Simplifies complex queries by encapsulating JOINs and WHERE clauses.
- Provides a security mechanism by restricting access to specific columns or rows.
- Data in a view is always up-to-date as it's derived from the underlying tables dynamically.
Memory trick: VIEWS give you a WINDOW into data, simpler and safer!