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 digital imaging database must comply with GDPR. A user requests deletion of all their images. Which database operation ensures related metadata is also removed via referential integrity?
Answer: ON DELETE CASCADE
ON DELETE CASCADE automatically removes all child rows (metadata, tags, thumbnails) when the parent image record is deleted.
In a sharded digital imaging database, images are distributed across shards by photographer_id. What problem arises when querying all images taken at a specific location?
Answer: A cross-shard scatter-gather query is required, increasing latency
When the query predicate doesn't match the shard key, all shards must be queried in parallel and results merged, increasing latency.
What is the recommended approach for backing up a live PostgreSQL database used by a DIS imaging platform without taking it offline?
Answer: Use pg_dump or pg_basebackup with WAL archiving
pg_dump and pg_basebackup create consistent backups of a live database by using MVCC snapshots and WAL streaming.
A DIS imaging database table has columns: image_id, album_id, tag_id. The business rule states each image can appear in many albums and have many tags. Which schema best represents this?
Answer: Two junction tables: image_albums(image_id, album_id) and image_tags(image_id, tag_id)
Junction tables properly represent many-to-many relationships while maintaining referential integrity and allowing efficient indexed lookups.
Which SQL window function would a DIS DBA use to rank images within each album by upload date, resetting the rank for each album?
Answer: RANK() OVER (PARTITION BY album_id ORDER BY upload_date)
PARTITION BY album_id resets the ranking for each album, while ORDER BY upload_date determines the rank order within each partition.
A digital imaging platform experiences slow INSERT performance when adding thousands of images in batch. What technique most improves throughput?
Answer: Using COPY command or multi-row INSERT instead of single-row inserts
COPY and multi-row INSERT reduce per-row overhead by batching multiple rows per round-trip and minimizing transaction commit frequency.
In a DIS imaging database, a CHECK constraint is defined as CHECK (file_size_mb > 0 AND file_size_mb <= 500). What happens if a user tries to insert a record with file_size_mb = 0?
Answer: The INSERT is rejected with a constraint violation error
A CHECK constraint violation causes the statement to be rejected with an error, and the row is not inserted.