Database Administration Flashcards
7 cards from real DIS practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 7 Database Administration flashcards as text
A DIS database administrator needs to grant a reporting user read-only access to the images table without write privileges. Which SQL statement is correct?
Answer: GRANT SELECT ON images TO reporter
GRANT SELECT restricts the user to read-only access without allowing INSERT, UPDATE, or DELETE operations.
When implementing a materialized view to cache aggregated image statistics, what must a DBA do to reflect new underlying data?
Answer: Run REFRESH MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW repopulates the view's stored data from the underlying query without dropping the view object.
In a multi-tenant digital imaging SaaS database, row-level security (RLS) is enabled. What does a policy on the images table enforce?
Answer: Filters rows so users only see their own tenant's images
RLS policies add automatic WHERE clause conditions so each user's queries are transparently restricted to their permitted rows.
A database experiences lock contention when multiple photographers upload images simultaneously. Which transaction isolation level change would most reduce contention while accepting some read anomalies?
Answer: Using Read Committed instead of Repeatable Read
Read Committed holds locks for shorter durations than Repeatable Read, reducing contention at the cost of allowing non-repeatable reads.
Which database design pattern allows a DIS system to track full history of image status changes (e.g., draft → approved → published)?
Answer: Event sourcing with an audit table
Event sourcing records every state change as an immutable event row, preserving the complete history of an image's lifecycle.
A DBA needs to find all queries running longer than 30 seconds in PostgreSQL. Which system view provides this information?
Answer: pg_stat_activity
pg_stat_activity shows currently running queries along with their start time, allowing calculation of query duration.
What is the purpose of a covering index in a digital imaging database query that always retrieves (image_id, file_path, upload_date)?
Answer: It allows the query to be satisfied entirely from the index without a heap fetch
A covering index includes all columns needed by a query so the database can return results from the index alone, eliminating table lookups.