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
Which database isolation level prevents dirty reads but still allows non-repeatable reads?
Answer: Read Committed
Read Committed prevents dirty reads by only seeing committed data, but the same row can return different values if read twice within a transaction.
A digital imaging system stores image vectors for similarity search. Which database extension or type is best suited for nearest-neighbor queries?
Answer: pgvector
pgvector is a PostgreSQL extension specifically designed for storing and querying high-dimensional vectors with similarity search operators.
When designing a DIS database schema, a composite primary key on (session_id, image_sequence) is used. What potential issue arises for child tables referencing this key?
Answer: Foreign key constraints must reference all columns of the composite key
Foreign keys referencing a composite primary key must include all component columns in the same order.
A DBA wants to enforce that every image record must have a non-null file_path. Which constraint type should be applied?
Answer: NOT NULL constraint
A NOT NULL constraint prevents the column from accepting null values, enforcing that every row must supply a file path.
In a high-availability digital imaging database cluster, what is the purpose of a WAL (Write-Ahead Log)?
Answer: Ensuring changes are logged before being applied for crash recovery
WAL records every change before it is applied to data files, enabling the database to recover to a consistent state after a crash.
Which SQL command reclaims storage space and updates planner statistics in PostgreSQL after bulk deletion of obsolete image records?
Answer: VACUUM ANALYZE
VACUUM ANALYZE reclaims space from dead tuples and refreshes table statistics used by the query planner.
A digital imaging platform needs to store images in the database itself rather than on a file system. Which PostgreSQL data type is appropriate for binary image data?
Answer: BYTEA
PostgreSQL's BYTEA type stores arbitrary binary data including raw image bytes directly in the database.