Michigan Database Administrator
Advanced Exam Practice Questions And
Correct Answers (Verified Answers) Plus
Rationale 2026 Q&A| Instant Download
Pdf
A database administrator is tasked with designing a high-availability
architecture for a mission-critical OLTP system. Which solution best ensures
minimal downtime and automatic failover?
A. Scheduled full backups with nightly restore testing
B. Log shipping with manual role transition
C. Database mirroring in high-performance mode
D. Failover clustering with synchronous replication and automatic failover
configuration
Failover clustering combined with synchronous replication provides
automatic failover and minimal data loss, ensuring high availability for
mission-critical OLTP systems without requiring manual intervention.
2. In a relational database, which normalization form eliminates
transitive dependencies?
A. First Normal Form (1NF)
B. Second Normal Form (2NF)
C. Third Normal Form (3NF)
D. Boyce-Codd Normal Form (BCNF)
Third Normal Form removes transitive dependencies by ensuring that non-
key attributes depend only on the primary key and not on other non-key
attributes.
, 3. Which isolation level prevents dirty reads, non-repeatable reads, and
phantom reads?
A. Read Uncommitted
B. Read Committed
C. Repeatable Read
D. Serializable
The Serializable isolation level provides the highest transaction isolation,
preventing dirty reads, non-repeatable reads, and phantom reads by
effectively serializing transaction execution.
4. A database experiences frequent deadlocks. What is the most
effective long-term solution?
A. Increase server RAM
B. Disable locking mechanisms
C. Implement consistent resource access ordering and optimize
indexing
D. Set isolation level to Read Uncommitted
Deadlocks often arise from inconsistent locking sequences; enforcing
consistent access order and optimizing indexes reduces lock contention and
deadlock frequency.
5. Which index type is most appropriate for columns with high cardinality
and frequent equality searches?
A. Bitmap index
B. B-tree index
C. Hash cluster
D. Full-text index
B-tree indexes efficiently support equality and range queries on high-
cardinality columns, making them ideal for frequently searched unique
values.
6. What is the primary purpose of a database transaction log?
A. Store user credentials
, B. Improve query speed
C. Ensure durability and enable point-in-time recovery
D. Maintain foreign key relationships
Transaction logs record all data modifications, ensuring durability under
ACID properties and enabling recovery to a specific point in time.
7. In database performance tuning, what does “index fragmentation”
primarily affect?
A. Data redundancy
B. Query performance due to inefficient page utilization
C. Schema validation
D. User authentication
Fragmented indexes cause inefficient page reads and additional I/O
operations, negatively impacting query performance.
8. A DBA needs to secure sensitive columns without modifying
application code. Which approach is most suitable?
A. Create a view
B. Transparent Data Encryption (TDE)
C. Drop unnecessary columns
D. Use Read Uncommitted isolation
Transparent Data Encryption encrypts data at rest without requiring
application changes, protecting sensitive data files.
9. Which backup strategy minimizes data loss in a system with
continuous updates?
A. Weekly full backups only
B. Daily differential backups
C. Full backups with frequent transaction log backups
D. Snapshot backups once per month
Combining full backups with frequent transaction log backups allows
recovery with minimal data loss in highly active systems.
Advanced Exam Practice Questions And
Correct Answers (Verified Answers) Plus
Rationale 2026 Q&A| Instant Download
A database administrator is tasked with designing a high-availability
architecture for a mission-critical OLTP system. Which solution best ensures
minimal downtime and automatic failover?
A. Scheduled full backups with nightly restore testing
B. Log shipping with manual role transition
C. Database mirroring in high-performance mode
D. Failover clustering with synchronous replication and automatic failover
configuration
Failover clustering combined with synchronous replication provides
automatic failover and minimal data loss, ensuring high availability for
mission-critical OLTP systems without requiring manual intervention.
2. In a relational database, which normalization form eliminates
transitive dependencies?
A. First Normal Form (1NF)
B. Second Normal Form (2NF)
C. Third Normal Form (3NF)
D. Boyce-Codd Normal Form (BCNF)
Third Normal Form removes transitive dependencies by ensuring that non-
key attributes depend only on the primary key and not on other non-key
attributes.
, 3. Which isolation level prevents dirty reads, non-repeatable reads, and
phantom reads?
A. Read Uncommitted
B. Read Committed
C. Repeatable Read
D. Serializable
The Serializable isolation level provides the highest transaction isolation,
preventing dirty reads, non-repeatable reads, and phantom reads by
effectively serializing transaction execution.
4. A database experiences frequent deadlocks. What is the most
effective long-term solution?
A. Increase server RAM
B. Disable locking mechanisms
C. Implement consistent resource access ordering and optimize
indexing
D. Set isolation level to Read Uncommitted
Deadlocks often arise from inconsistent locking sequences; enforcing
consistent access order and optimizing indexes reduces lock contention and
deadlock frequency.
5. Which index type is most appropriate for columns with high cardinality
and frequent equality searches?
A. Bitmap index
B. B-tree index
C. Hash cluster
D. Full-text index
B-tree indexes efficiently support equality and range queries on high-
cardinality columns, making them ideal for frequently searched unique
values.
6. What is the primary purpose of a database transaction log?
A. Store user credentials
, B. Improve query speed
C. Ensure durability and enable point-in-time recovery
D. Maintain foreign key relationships
Transaction logs record all data modifications, ensuring durability under
ACID properties and enabling recovery to a specific point in time.
7. In database performance tuning, what does “index fragmentation”
primarily affect?
A. Data redundancy
B. Query performance due to inefficient page utilization
C. Schema validation
D. User authentication
Fragmented indexes cause inefficient page reads and additional I/O
operations, negatively impacting query performance.
8. A DBA needs to secure sensitive columns without modifying
application code. Which approach is most suitable?
A. Create a view
B. Transparent Data Encryption (TDE)
C. Drop unnecessary columns
D. Use Read Uncommitted isolation
Transparent Data Encryption encrypts data at rest without requiring
application changes, protecting sensitive data files.
9. Which backup strategy minimizes data loss in a system with
continuous updates?
A. Weekly full backups only
B. Daily differential backups
C. Full backups with frequent transaction log backups
D. Snapshot backups once per month
Combining full backups with frequent transaction log backups allows
recovery with minimal data loss in highly active systems.
