DIS Database Administration 2 — Questions and Answers
Question 1: In a digital imaging database, which indexing strategy best supports range queries on image capture dates?
- Hash index
- B-tree index (Correct answer)
- Bitmap index
- Full-text index
Correct answer: B-tree index
B-tree indexes maintain sorted order, making them ideal for range queries on date fields.
Question 2: A DIS database stores EXIF metadata alongside image files. Which normal form violation occurs when camera model and manufacturer are stored in the same image record without a separate camera table?
- 1NF
- 2NF
- 3NF (Correct answer)
- BCNF
Correct answer: 3NF
Storing manufacturer derived from camera model in the same table violates 3NF due to a transitive dependency.
Question 3: Which PostgreSQL feature allows querying image metadata stored as JSON without extracting it into separate columns?
- HSTORE
- JSONB operators (Correct answer)
- XML columns
- ARRAY types
Correct answer: JSONB operators
PostgreSQL's JSONB operators like -> and ->> allow direct querying of JSON fields without restructuring the schema.
Question 4: When archiving high-resolution images, a DBA implements table partitioning by year. What is the primary benefit for query performance?
- Reduces index size globally
- Enables partition pruning to scan only relevant data (Correct answer)
- Allows cross-partition joins
- Increases write throughput
Correct answer: Enables partition pruning to scan only relevant data
Partition pruning allows the query planner to skip irrelevant partitions entirely, reducing I/O for date-filtered queries.
Question 5: A digital imaging database uses a connection pool. What happens when all pool connections are in use and a new request arrives?
- The request is dropped immediately
- The request waits in queue until a connection is released (Correct answer)
- A new connection is created outside the pool
- The database restarts
Correct answer: The request waits in queue until a connection is released
Connection pools queue incoming requests until an active connection is returned to the pool.
Question 6: In the context of DIS certification, what does ACID stand for in database transactions?
- Atomicity, Consistency, Isolation, Durability (Correct answer)
- Accuracy, Completeness, Integrity, Distribution
- Atomicity, Compression, Indexing, Durability
- Authentication, Consistency, Isolation, Distribution
Correct answer: Atomicity, Consistency, Isolation, Durability
ACID defines the four properties that guarantee reliable database transactions: Atomicity, Consistency, Isolation, and Durability.
Question 7: A DBA notices that a query retrieving thumbnail paths for a specific photographer is performing a full table scan despite an index on photographer_id. What is the most likely cause?
- The index is on the wrong column
- Statistics are stale and the planner underestimates selectivity (Correct answer)
- Thumbnails are stored in a separate schema
- The query uses DISTINCT
Correct answer: Statistics are stale and the planner underestimates selectivity
Stale statistics cause the query planner to misjudge row counts, sometimes choosing a full scan over an index seek.
DIS Database Administration 2
In a digital imaging database, which indexing strategy best supports range queries on image capture dates?