DIS Database Administration 3 — Questions and Answers
Question 1: Which database isolation level prevents dirty reads but still allows non-repeatable reads?
- Read Uncommitted
- Read Committed (Correct answer)
- Repeatable Read
- Serializable
Correct 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.
Question 2: A digital imaging system stores image vectors for similarity search. Which database extension or type is best suited for nearest-neighbor queries?
- PostGIS geometry
- pgvector (Correct answer)
- Full-text tsvector
- JSONB arrays
Correct answer: pgvector
pgvector is a PostgreSQL extension specifically designed for storing and querying high-dimensional vectors with similarity search operators.
Question 3: 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?
- Child tables cannot use foreign keys
- Foreign key constraints must reference all columns of the composite key (Correct answer)
- Composite keys prevent indexing
- Auto-increment becomes unavailable
Correct 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.
Question 4: A DBA wants to enforce that every image record must have a non-null file_path. Which constraint type should be applied?
- CHECK constraint
- UNIQUE constraint
- NOT NULL constraint (Correct answer)
- DEFAULT constraint
Correct answer: NOT NULL constraint
A NOT NULL constraint prevents the column from accepting null values, enforcing that every row must supply a file path.
Question 5: In a high-availability digital imaging database cluster, what is the purpose of a WAL (Write-Ahead Log)?
- Compressing image blobs before storage
- Ensuring changes are logged before being applied for crash recovery (Correct answer)
- Routing queries to replica nodes
- Managing user authentication
Correct 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.
Question 6: Which SQL command reclaims storage space and updates planner statistics in PostgreSQL after bulk deletion of obsolete image records?
- REINDEX
- VACUUM ANALYZE (Correct answer)
- TRUNCATE
- CLUSTER
Correct answer: VACUUM ANALYZE
VACUUM ANALYZE reclaims space from dead tuples and refreshes table statistics used by the query planner.
Question 7: 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?
- TEXT
- BYTEA (Correct answer)
- BLOB reference
- UUID
Correct answer: BYTEA
PostgreSQL's BYTEA type stores arbitrary binary data including raw image bytes directly in the database.
DIS Database Administration 3
Which database isolation level prevents dirty reads but still allows non-repeatable reads?