EduConceptHub

Start MCQs

Advanced Database Management Systems

Advanced Database Management Systems

Introduction

Build on introductory database concepts with transactions, optimisation, administration and distributed systems.

Topic-Wise Overview

Advanced Models

Object-oriented and object-relational models; file organisation

Transactions

Concurrency control, transactional processing and recovery

Query and Database Programming

Query optimisation, SQL procedural extensions and integrity

Administration

Permissions, access management, physical design and performance tuning

Distributed and Emerging Databases

Distributed systems, document databases and NoSQL

Study Guidance

Study the complete collection of 200 Advanced Database Management Systems MCQs below, organized into four sets of 50 questions. Every question has four choices, a correct answer, and a short explanation. Topics include ACID transactions, concurrency control, recovery, query optimization, indexing, physical database design, distributed databases, replication, NoSQL, security, data warehousing, and performance tuning.

Explore Other Subjects

Browse Computer Science subjects

Advanced Database Management Systems MCQs — Set 1 (Questions 1–50)

Choose an answer, then reveal the correct option and explanation.

Question 1. What does the ACID atomicity property require?

  1. A transaction’s operations take effect as a whole or not at all
  2. Every query executes in one CPU cycle
  3. Every table has one column
  4. Every user has administrator rights
Show correct answer and explanation

Correct answer: A. A transaction’s operations take effect as a whole or not at all

Atomicity prevents partially committed transactions.

Question 2. What does the consistency property of ACID require?

  1. All concurrent reads see the newest uncommitted value
  2. A transaction preserves declared database integrity constraints when starting from a valid state
  3. Every index uses the same format
  4. All SQL is case sensitive
Show correct answer and explanation

Correct answer: B. A transaction preserves declared database integrity constraints when starting from a valid state

Successful transactions must preserve the database’s defined invariants.

Question 3. What does isolation aim to provide?

  1. Each transaction gets a separate computer
  2. All data must be encrypted
  3. Concurrent transactions behave according to the selected isolation guarantees
  4. No transaction may read data
Show correct answer and explanation

Correct answer: C. Concurrent transactions behave according to the selected isolation guarantees

Isolation governs interference between concurrent operations.

Question 4. What does transaction durability mean?

  1. Every read is faster than a write
  2. Every transaction is read-only
  3. Backups are never needed
  4. Committed changes survive relevant subsequent failures under the DBMS guarantees
Show correct answer and explanation

Correct answer: D. Committed changes survive relevant subsequent failures under the DBMS guarantees

Durability protects acknowledged commits through recovery mechanisms.

Question 5. Which SQL statement normally starts an explicit transaction in many DBMSs?

  1. BEGIN
  2. SELECT
  3. EXPLAIN
  4. CREATE INDEX
Show correct answer and explanation

Correct answer: A. BEGIN

BEGIN initiates an explicit transaction scope in systems supporting that syntax.

Question 6. Which statement makes an active transaction’s changes permanent according to its durability guarantees?

  1. ROLLBACK
  2. COMMIT
  3. SAVEPOINT
  4. EXPLAIN
Show correct answer and explanation

Correct answer: B. COMMIT

COMMIT completes a successful transaction.

Question 7. Which command discards an uncommitted transaction’s changes?

  1. COMMIT
  2. GRANT
  3. ROLLBACK
  4. ANALYZE
Show correct answer and explanation

Correct answer: C. ROLLBACK

ROLLBACK undoes changes in the active transaction.

Question 8. What is a savepoint?

  1. A disk backup stored offsite
  2. A table primary key
  3. A database replica
  4. A named position to which a transaction can partially roll back
Show correct answer and explanation

Correct answer: D. A named position to which a transaction can partially roll back

Savepoints support partial rollback within a transaction.

Question 9. What is a dirty read?

  1. Reading another transaction’s uncommitted data
  2. Reading a row twice after it commits
  3. Reading an indexed column
  4. Reading a stored procedure’s source
Show correct answer and explanation

Correct answer: A. Reading another transaction’s uncommitted data

Dirty reads can expose values later rolled back.

Question 10. What is a nonrepeatable read?

  1. A read returns exactly one row
  2. A repeated read of the same row observes a changed committed value
  3. A query reads an encrypted column
  4. An index is rebuilt
Show correct answer and explanation

Correct answer: B. A repeated read of the same row observes a changed committed value

Concurrent updates may change a row between reads.

Question 11. What is a phantom read?

  1. Reading an uncommitted field only
  2. Accessing nonexistent physical memory
  3. Re-executing a predicate query observes a changed set of qualifying rows
  4. A corrupted database page
Show correct answer and explanation

Correct answer: C. Re-executing a predicate query observes a changed set of qualifying rows

Concurrent inserts or deletes can change a query’s result membership.

Question 12. Which SQL isolation level permits dirty reads under the standard model?

  1. READ COMMITTED
  2. REPEATABLE READ
  3. SERIALIZABLE
  4. READ UNCOMMITTED
Show correct answer and explanation

Correct answer: D. READ UNCOMMITTED

Read uncommitted allows exposure to uncommitted changes.

Question 13. Which SQL isolation level is intended to prevent dirty reads?

  1. READ COMMITTED
  2. READ UNCOMMITTED
  3. Any custom storage engine without locks
  4. A read-only SQL view by itself
Show correct answer and explanation

Correct answer: A. READ COMMITTED

Read committed disallows reading uncommitted changes.

Question 14. Which standard isolation level offers the strongest serial-execution equivalence?

  1. READ UNCOMMITTED
  2. SERIALIZABLE
  3. READ COMMITTED
  4. REPEATABLE READ in every implementation
Show correct answer and explanation

Correct answer: B. SERIALIZABLE

Serializable executions are equivalent to some serial transaction order.

Question 15. What is serial execution of transactions?

  1. All transactions run in parallel on many cores
  2. Every transaction uses one SQL command
  3. One complete transaction runs before the next without interleaving
  4. A transaction reads only its own table
Show correct answer and explanation

Correct answer: C. One complete transaction runs before the next without interleaving

A serial schedule executes transactions sequentially.

Question 16. A schedule is conflict-serializable when?

  1. Every transaction runs simultaneously
  2. Every query uses a clustered index
  3. No transaction writes data
  4. Its conflicting operations can be reordered into a serial schedule without changing their relative order
Show correct answer and explanation

Correct answer: D. Its conflicting operations can be reordered into a serial schedule without changing their relative order

Conflict serializability can be tested using a precedence graph.

Question 17. What does a cycle in a schedule’s precedence graph indicate?

  1. The schedule is not conflict-serializable
  2. The schedule is necessarily serial
  3. Every transaction has committed
  4. The database is normalized
Show correct answer and explanation

Correct answer: A. The schedule is not conflict-serializable

A cycle prevents a consistent serial transaction ordering.

Question 18. What does an edge T1 to T2 in a precedence graph represent?

  1. T1 and T2 have no shared records
  2. A conflicting operation of T1 precedes one of T2 on the same data item
  3. T2 committed before T1 began always
  4. A foreign key relationship
Show correct answer and explanation

Correct answer: B. A conflicting operation of T1 precedes one of T2 on the same data item

Edges express transaction ordering constraints.

Question 19. Which pair of operations on the same data item conflicts?

  1. Two reads by different transactions
  2. Two reads by the same transaction
  3. A read by one transaction and a write by another
  4. Two table descriptions
Show correct answer and explanation

Correct answer: C. A read by one transaction and a write by another

At least one write by a different transaction creates a conflict.

Question 20. What is a lost update anomaly?

  1. An index entry is stored twice
  2. A table becomes read-only
  3. A query optimizer chooses a scan
  4. One transaction’s write overwrites another update without properly accounting for it
Show correct answer and explanation

Correct answer: D. One transaction’s write overwrites another update without properly accounting for it

Uncoordinated updates can erase an earlier change.

Question 21. What is two-phase locking?

  1. A locking protocol with a growing phase followed by a shrinking phase
  2. A transaction must run on two servers
  3. Every query has exactly two locks
  4. A backup is stored twice
Show correct answer and explanation

Correct answer: A. A locking protocol with a growing phase followed by a shrinking phase

2PL prohibits acquiring new locks after releasing one.

Question 22. What does strict two-phase locking typically retain until commit or abort?

  1. All read locks necessarily in every variant
  2. Exclusive write locks
  3. Only metadata locks
  4. No locks
Show correct answer and explanation

Correct answer: B. Exclusive write locks

Holding write locks prevents exposure to uncommitted modifications.

Question 23. What does a shared lock normally permit?

  1. Unlimited concurrent writers to the same item
  2. Only schema deletion
  3. Concurrent compatible readers
  4. Exclusive ownership for writes
Show correct answer and explanation

Correct answer: C. Concurrent compatible readers

Shared locks allow reading without conflicting writes.

Question 24. What does an exclusive lock normally permit?

  1. All writers simultaneously
  2. Only read-only access
  3. No ownership restrictions
  4. Controlled modification while excluding conflicting reads and writes
Show correct answer and explanation

Correct answer: D. Controlled modification while excluding conflicting reads and writes

Exclusive locks protect updates against conflicting operations.

Question 25. What is a lock conversion from shared to exclusive called?

  1. Upgrade
  2. Downgrade
  3. Checkpoint
  4. Compaction
Show correct answer and explanation

Correct answer: A. Upgrade

An upgrade requests stronger access rights.

Question 26. What is a database deadlock?

  1. A committed transaction starts another
  2. A set of transactions wait cyclically for locks or resources
  3. A table has no primary key
  4. A SELECT scans all rows
Show correct answer and explanation

Correct answer: B. A set of transactions wait cyclically for locks or resources

Circular waits can prevent any participating transaction from advancing.

Question 27. Which structure can help identify transaction deadlocks?

  1. B+ tree leaf list
  2. Data dictionary only
  3. Wait-for graph
  4. Foreign key catalog
Show correct answer and explanation

Correct answer: C. Wait-for graph

Cycles in a wait-for graph reveal deadlocks under suitable models.

Question 28. A deadlock victim is usually?

  1. Every table with a foreign key
  2. The fastest query
  3. A readonly replica
  4. A transaction chosen for abort to release resources
Show correct answer and explanation

Correct answer: D. A transaction chosen for abort to release resources

Aborting one transaction can break a wait cycle.

Question 29. What is lock granularity?

  1. The size of the protected resource such as row page or table
  2. The number of data centers
  3. How often backups run
  4. Only index key length
Show correct answer and explanation

Correct answer: A. The size of the protected resource such as row page or table

Fine and coarse locking have different overhead and concurrency tradeoffs.

Question 30. What is lock escalation?

  1. Always deleting transaction logs
  2. Replacing many finer-grained locks with a coarser lock under a DBMS policy
  3. Creating one lock per byte
  4. Changing primary keys
Show correct answer and explanation

Correct answer: B. Replacing many finer-grained locks with a coarser lock under a DBMS policy

Escalation reduces lock-management overhead at the cost of concurrency.

Question 31. What is multiversion concurrency control?

  1. Requiring every writer to lock the entire database
  2. Keeping exactly one database copy
  3. Maintaining multiple row versions so transactions can read suitable snapshots
  4. Storing all data in a blockchain
Show correct answer and explanation

Correct answer: C. Maintaining multiple row versions so transactions can read suitable snapshots

MVCC uses version visibility rules to support concurrency.

Question 32. A snapshot read generally observes?

  1. All uncommitted writes from every connection
  2. Only deleted records
  3. Only the oldest backup
  4. A consistent set of row versions determined by the transaction or statement snapshot
Show correct answer and explanation

Correct answer: D. A consistent set of row versions determined by the transaction or statement snapshot

Snapshot visibility depends on system isolation rules.

Question 33. Which anomaly may still occur under common snapshot isolation implementations?

  1. Write skew
  2. Dirty read by design
  3. Every read becomes a phantom necessarily
  4. No committed update can occur
Show correct answer and explanation

Correct answer: A. Write skew

Transactions updating different rows can violate a cross-row constraint.

Question 34. What is write skew?

  1. Two reads of the same row
  2. Concurrent transactions read a shared condition and update different rows in a way that violates an invariant
  3. One query using two indexes
  4. A failed disk mirror
Show correct answer and explanation

Correct answer: B. Concurrent transactions read a shared condition and update different rows in a way that violates an invariant

Snapshot isolation may permit incompatible decisions from the same snapshot.

Question 35. What is optimistic concurrency control?

  1. Transactions hold all table locks forever
  2. All changes skip validation
  3. Transactions proceed with limited locking and validate conflicts before commit
  4. Every write is immediately permanent
Show correct answer and explanation

Correct answer: C. Transactions proceed with limited locking and validate conflicts before commit

Optimistic protocols detect conflicts and may abort at validation.

Question 36. What is a timestamp-ordering protocol?

  1. A rule to sort table names alphabetically
  2. A time-zone conversion algorithm
  3. A full backup method
  4. A concurrency scheme ordering conflicting operations by transaction timestamps
Show correct answer and explanation

Correct answer: D. A concurrency scheme ordering conflicting operations by transaction timestamps

Timestamps constrain the logical serialization order.

Question 37. What is a transaction log used for?

  1. Recording changes or intentions needed for recovery and durability
  2. Only displaying SQL syntax
  3. Sorting index leaves
  4. Assigning user passwords
Show correct answer and explanation

Correct answer: A. Recording changes or intentions needed for recovery and durability

Logs allow DBMS recovery after faults.

Question 38. What does write-ahead logging require?

  1. All data pages are written before logs
  2. Relevant log records reach stable storage before affected data pages are persisted
  3. Logs are deleted before commit
  4. Only indexes are logged
Show correct answer and explanation

Correct answer: B. Relevant log records reach stable storage before affected data pages are persisted

WAL records changes before their pages are flushed.

Question 39. A redo operation during recovery?

  1. Always reverses committed work
  2. Deletes all logs
  3. Reapplies changes that should be reflected after a failure
  4. Rebuilds hardware caches only
Show correct answer and explanation

Correct answer: C. Reapplies changes that should be reflected after a failure

Redo restores effects that may not yet exist in data pages.

Question 40. An undo operation during recovery?

  1. Repeats every committed update
  2. Increases data size
  3. Writes a new SQL parser
  4. Reverses changes of transactions that must not remain committed
Show correct answer and explanation

Correct answer: D. Reverses changes of transactions that must not remain committed

Undo removes effects of incomplete or aborted transactions.

Question 41. What does a checkpoint help reduce?

  1. Recovery work needed to determine and replay relevant log records
  2. The need for any transaction log
  3. Every database query cost to zero
  4. The number of SQL tables
Show correct answer and explanation

Correct answer: A. Recovery work needed to determine and replay relevant log records

Checkpoints establish recovery reference information.

Question 42. What is a steal buffer policy?

  1. Uncommitted pages can never leave cache
  2. Modified pages of uncommitted transactions may be written to disk
  3. Pages are never evicted
  4. Only committed transactions can hold locks
Show correct answer and explanation

Correct answer: B. Modified pages of uncommitted transactions may be written to disk

Steal policies make undo recovery necessary.

Question 43. What is a no-force commit policy?

  1. Every dirty data page must be forced at commit
  2. Logs cannot be written
  3. Commit need not flush every modified data page immediately
  4. No recovery is possible
Show correct answer and explanation

Correct answer: C. Commit need not flush every modified data page immediately

No-force policies require redo capability.

Question 44. Why can redo and undo both be required?

  1. No transactions ever commit
  2. Every table is temporary
  3. Indexes cannot be rebuilt
  4. Steal and no-force buffer choices permit uncommitted disk changes and delayed committed page writes
Show correct answer and explanation

Correct answer: D. Steal and no-force buffer choices permit uncommitted disk changes and delayed committed page writes

Undo removes unfinished writes and redo restores committed writes.

Question 45. What is a fuzzy checkpoint?

  1. A checkpoint taken while transactions and updates may continue
  2. A checkpoint requiring a database permanently shut down
  3. A corrupted disk image
  4. An unordered table scan
Show correct answer and explanation

Correct answer: A. A checkpoint taken while transactions and updates may continue

Fuzzy checkpoints avoid fully stopping workload.

Question 46. What does the ARIES recovery algorithm use?

  1. Only full-table copies
  2. Write-ahead logging with analysis redo and undo phases
  3. Only in-memory snapshots without logs
  4. Only a distributed hash table
Show correct answer and explanation

Correct answer: B. Write-ahead logging with analysis redo and undo phases

ARIES is a recovery approach based on repeating history and undoing losers.

Question 47. What is a compensation log record?

  1. A user’s login record
  2. Only a row version
  3. A log record describing an undo action for crash-safe recovery
  4. A backup checksum
Show correct answer and explanation

Correct answer: C. A log record describing an undo action for crash-safe recovery

CLRs make recovery restartable during rollback.

Question 48. What is a transaction’s commit record?

  1. A foreign key declaration
  2. A query plan node
  3. Only a cache tag
  4. Log evidence that a transaction reached commit according to the logging protocol
Show correct answer and explanation

Correct answer: D. Log evidence that a transaction reached commit according to the logging protocol

Recovery can classify committed versus incomplete work.

Question 49. What is a stable log?

  1. A log stored with durability sufficient for the DBMS recovery guarantee
  2. A text file stored only in a volatile register
  3. A log that is never read
  4. A table without indexes
Show correct answer and explanation

Correct answer: A. A log stored with durability sufficient for the DBMS recovery guarantee

Recovery assumes required log entries survive relevant crashes.

Question 50. Why are database recovery tests important?

  1. They guarantee no future hardware failure
  2. They verify that failures can be handled and data restored correctly
  3. They replace SQL constraints
  4. They remove the need for monitoring
Show correct answer and explanation

Correct answer: B. They verify that failures can be handled and data restored correctly

Validated restores and crash tests establish practical recovery confidence.

Advanced Database Management Systems MCQs — Set 2 (Questions 51–100)

Choose an answer and reveal its explanation.

Question 51. What is a query execution plan?

  1. Only a list of SQL keywords
  2. A database backup schedule
  3. A sequence or tree of physical operations chosen to evaluate a query
  4. A table access policy
Show correct answer and explanation

Correct answer: C. A sequence or tree of physical operations chosen to evaluate a query

Plans specify scans joins and other execution operators.

Question 52. What does a cost-based query optimizer do?

  1. Guarantee the globally fastest runtime for every query
  2. Only check SQL syntax
  3. Automatically rewrite business requirements
  4. Estimate costs of alternative plans and choose a promising plan
Show correct answer and explanation

Correct answer: D. Estimate costs of alternative plans and choose a promising plan

Optimizers compare estimated resource costs.

Question 53. What is a cardinality estimate?

  1. A prediction of the number of rows produced by an operator
  2. Only the number of column names
  3. A disk block checksum
  4. A transaction identifier
Show correct answer and explanation

Correct answer: A. A prediction of the number of rows produced by an operator

Row-count estimates influence plan selection.

Question 54. Why are database statistics used by optimizers?

  1. To encrypt every row
  2. To estimate selectivity and operator costs
  3. To replace all indexes
  4. To avoid reading SQL
Show correct answer and explanation

Correct answer: B. To estimate selectivity and operator costs

Histograms and row counts guide cost estimates.

Question 55. What is predicate selectivity?

  1. The number of users connected
  2. The physical page size
  3. The fraction of rows satisfying a filter condition
  4. Only query execution time
Show correct answer and explanation

Correct answer: C. The fraction of rows satisfying a filter condition

Low selectivity fraction means few rows qualify.

Question 56. What is predicate pushdown?

  1. Performing filters after every join
  2. Disabling indexes
  3. Sending every row to the client
  4. Applying filters as early as possible in a query plan when semantics permit
Show correct answer and explanation

Correct answer: D. Applying filters as early as possible in a query plan when semantics permit

Early filtering can reduce intermediate data.

Question 57. What is projection pushdown?

  1. Reading or carrying only required columns early in processing
  2. Adding all columns to every result
  3. Removing foreign keys
  4. Performing only DISTINCT queries
Show correct answer and explanation

Correct answer: A. Reading or carrying only required columns early in processing

Fewer columns reduce data movement and memory.

Question 58. A table scan ordinarily?

  1. Uses only primary-key lookup
  2. Examines all table pages or rows in its scan path
  3. Never accesses disk
  4. Always sorts data
Show correct answer and explanation

Correct answer: B. Examines all table pages or rows in its scan path

Scans may be efficient when much of a table is needed.

Question 59. An index seek generally?

  1. Reads every table row necessarily
  2. Creates an index from scratch
  3. Navigates an index to locate a subset of matching keys
  4. Disables buffering
Show correct answer and explanation

Correct answer: C. Navigates an index to locate a subset of matching keys

Selective indexed access can avoid full scans.

Question 60. What is a covering index for a query?

  1. An index on every table in the database
  2. Only a clustered primary key
  3. An index with no leaf entries
  4. An index containing all columns needed by that query
Show correct answer and explanation

Correct answer: D. An index containing all columns needed by that query

Covering can avoid extra base-table lookups.

Question 61. What is a clustered index?

  1. An index whose key order determines or closely follows the physical row arrangement according to the engine design
  2. Every secondary index
  3. An index of network clients
  4. A hash of usernames
Show correct answer and explanation

Correct answer: A. An index whose key order determines or closely follows the physical row arrangement according to the engine design

Clustered organization makes nearby keys physically adjacent or co-located.

Question 62. What is a nonclustered index?

  1. A table that cannot be queried
  2. An index maintained separately from base-row organization
  3. Only an in-memory transaction
  4. A disk backup
Show correct answer and explanation

Correct answer: B. An index maintained separately from base-row organization

Secondary indexes refer to base rows or keys.

Question 63. Why is a B+ tree useful for database indexing?

  1. It requires scanning every key
  2. It stores no data at leaf level
  3. It supports balanced searches and ordered range traversal
  4. It only works for strings
Show correct answer and explanation

Correct answer: C. It supports balanced searches and ordered range traversal

Balanced leaves facilitate exact and range lookups.

Question 64. Where are data entries commonly stored in a B+ tree?

  1. Only at the root
  2. Only in internal nodes
  3. Outside the tree always
  4. At leaf nodes
Show correct answer and explanation

Correct answer: D. At leaf nodes

Leaf nodes carry searchable key entries or pointers.

Question 65. What is a composite index?

  1. An index on more than one column
  2. An index without keys
  3. A table with two rows
  4. A transaction log
Show correct answer and explanation

Correct answer: A. An index on more than one column

Composite keys support multicolumn lookup patterns.

Question 66. Why does the leading-column order of a composite B-tree index matter?

  1. All index column permutations have identical access plans
  2. It affects which predicates and orderings can exploit the index efficiently
  3. Only the index name matters
  4. It determines database password length
Show correct answer and explanation

Correct answer: B. It affects which predicates and orderings can exploit the index efficiently

Index order shapes useful key prefixes.

Question 67. A unique index enforces?

  1. That every indexed value is non-NULL in all systems
  2. That the table has only one column
  3. No duplicate indexed key combinations under the DBMS NULL semantics
  4. That only one transaction runs
Show correct answer and explanation

Correct answer: C. No duplicate indexed key combinations under the DBMS NULL semantics

Unique constraints restrict duplicate keys.

Question 68. A hash index is especially suited for?

  1. Arbitrary ordered range scans
  2. Lexicographic ordering queries
  3. Finding all adjacent values
  4. Equality lookups
Show correct answer and explanation

Correct answer: D. Equality lookups

Hash organization maps exact keys to buckets.

Question 69. Why are hash indexes often poor for ordered range queries?

  1. Hash values do not preserve key order
  2. They cannot handle collisions
  3. They require infinite memory
  4. They cannot index numbers
Show correct answer and explanation

Correct answer: A. Hash values do not preserve key order

Range traversal needs order relationships.

Question 70. An index maintenance cost arises because?

  1. Indexes never consume storage
  2. Inserts updates and deletes may require index changes
  3. Every query skips the index
  4. Only the SQL parser uses indexes
Show correct answer and explanation

Correct answer: B. Inserts updates and deletes may require index changes

Additional indexes add write and space overhead.

Question 71. What is a nested-loop join?

  1. Only a sort operation
  2. A full-table delete
  3. A join that iterates rows from one input and searches or scans the other
  4. An equality constraint
Show correct answer and explanation

Correct answer: C. A join that iterates rows from one input and searches or scans the other

Nested loops find matches for each outer row.

Question 72. What makes an index nested-loop join attractive?

  1. Both tables have no indexes and are enormous in all cases
  2. Only non-equality data types
  3. The query must return every row
  4. Small outer input with efficient indexed probes on the inner relation
Show correct answer and explanation

Correct answer: D. Small outer input with efficient indexed probes on the inner relation

Indexed lookups can make each outer-row probe cheap.

Question 73. What is a hash join primarily designed for?

  1. Matching rows by hashable equality join keys
  2. Sorting every row before matching always
  3. Only joining on less-than predicates
  4. Checking foreign-key existence without reading data
Show correct answer and explanation

Correct answer: A. Matching rows by hashable equality join keys

Hash tables match compatible equality keys.

Question 74. What is a sort-merge join?

  1. A join that always needs a hash table
  2. A join that merges inputs ordered on compatible join keys
  3. A method for removing all duplicates only
  4. A graph shortest path algorithm
Show correct answer and explanation

Correct answer: B. A join that merges inputs ordered on compatible join keys

Ordered streams can be matched efficiently.

Question 75. When can a merge join avoid an explicit sort?

  1. When the inputs are arbitrary unordered strings only
  2. When no join predicate exists
  3. When suitable input orders already exist
  4. When the optimizer lacks statistics
Show correct answer and explanation

Correct answer: C. When suitable input orders already exist

Existing indexes or order-producing operators may supply sorted inputs.

Question 76. What is an equijoin?

  1. A join without a condition
  2. A join only using less than
  3. A join that duplicates every table
  4. A join whose matching predicate uses equality
Show correct answer and explanation

Correct answer: D. A join whose matching predicate uses equality

Equijoins equate specified columns.

Question 77. What does join ordering affect?

  1. Intermediate result sizes and total execution cost
  2. The semantics of all valid inner joins necessarily
  3. Only the SQL keyword spelling
  4. Password permissions
Show correct answer and explanation

Correct answer: A. Intermediate result sizes and total execution cost

Poor join order may produce enormous intermediates.

Question 78. Why does skewed data challenge an optimizer?

  1. Skew guarantees constant time
  2. Uniform distribution assumptions may produce inaccurate selectivity estimates
  3. Histograms are always exact
  4. Every join uses the same algorithm
Show correct answer and explanation

Correct answer: B. Uniform distribution assumptions may produce inaccurate selectivity estimates

Skew can distort cardinality assumptions.

Question 79. What is a histogram in database statistics?

  1. A backup log
  2. An executable stored procedure
  3. A summary of value distribution used for estimates
  4. Only a syntax tree
Show correct answer and explanation

Correct answer: C. A summary of value distribution used for estimates

Histograms approximate how values are distributed.

Question 80. What is a query-plan operator called a materialization?

  1. Always eliminating all intermediate storage
  2. Encrypting a transaction
  3. Changing the query’s answer
  4. Storing an intermediate result for later consumption
Show correct answer and explanation

Correct answer: D. Storing an intermediate result for later consumption

Materialization trades memory or I/O for reusable intermediate data.

Question 81. What is pipelined query execution?

  1. Passing tuples between operators without fully materializing every intermediate result
  2. Always reading all rows into a final temporary file first
  3. Only executing one operator at a time without streaming
  4. Deleting staging buffers
Show correct answer and explanation

Correct answer: A. Passing tuples between operators without fully materializing every intermediate result

Operators can process tuples incrementally.

Question 82. What is a blocking operator?

  1. An operator that never reads data
  2. An operator needing substantial or all input before producing certain output
  3. Only an index seek
  4. A user access restriction
Show correct answer and explanation

Correct answer: B. An operator needing substantial or all input before producing certain output

Sort and some aggregation implementations are blocking.

Question 83. What is an external merge sort?

  1. Only quicksort for CPU registers
  2. A network replication protocol
  3. A sorting method designed for data exceeding available main memory
  4. A query authorization rule
Show correct answer and explanation

Correct answer: C. A sorting method designed for data exceeding available main memory

It produces sorted runs and merges them using disk I/O.

Question 84. What is a sort run?

  1. A deadlocked transaction
  2. A database role
  3. A row version
  4. A sorted subset or sequence produced during external sorting
Show correct answer and explanation

Correct answer: D. A sorted subset or sequence produced during external sorting

Runs are merged into larger sorted sequences.

Question 85. Why is available buffer memory important for external sorting?

  1. It affects run length and merge fan-in
  2. It changes SQL semantics
  3. It eliminates all disk access
  4. It changes numeric data types
Show correct answer and explanation

Correct answer: A. It affects run length and merge fan-in

More buffers can reduce the number of merge passes.

Question 86. What is physical database design?

  1. Only drawing conceptual ER diagrams
  2. Selecting storage organization indexes partitions and related structures
  3. Only specifying user stories
  4. Only choosing a website name
Show correct answer and explanation

Correct answer: B. Selecting storage organization indexes partitions and related structures

Physical design supports workload efficiency.

Question 87. What is table partitioning?

  1. Splitting each row into individual bytes necessarily
  2. Removing all foreign keys
  3. Dividing a logical table’s data among smaller physical partitions
  4. Making every partition independent of SQL
Show correct answer and explanation

Correct answer: C. Dividing a logical table’s data among smaller physical partitions

Partitions can aid pruning and administration.

Question 88. What is partition pruning?

  1. Deleting partitions after every SELECT
  2. Reading all partitions before filters
  3. Reorganizing a database schema only
  4. Skipping partitions known not to satisfy a query’s predicates
Show correct answer and explanation

Correct answer: D. Skipping partitions known not to satisfy a query’s predicates

Pruning reduces unnecessary partition access.

Question 89. What is horizontal partitioning?

  1. Distributing subsets of rows across partitions
  2. Dividing columns among tables only
  3. Splitting a key into characters
  4. Duplicating every row globally
Show correct answer and explanation

Correct answer: A. Distributing subsets of rows across partitions

Horizontal partitioning divides by rows.

Question 90. What is vertical partitioning?

  1. Splitting only by row range
  2. Separating subsets of columns while retaining a means to reconstruct rows
  3. Duplicating every column into every partition
  4. Changing a SQL query to a view
Show correct answer and explanation

Correct answer: B. Separating subsets of columns while retaining a means to reconstruct rows

Vertical partitioning groups attributes.

Question 91. A materialized view differs from a normal view because it?

  1. Cannot be refreshed
  2. Has no defining query
  3. Stores precomputed query results or data physically
  4. Is always a base table without derived data
Show correct answer and explanation

Correct answer: C. Stores precomputed query results or data physically

Materialized views trade storage and refresh cost for faster reads.

Question 92. What is incremental materialized-view maintenance?

  1. Always dropping and recreating all tables
  2. Executing only one query per day
  3. Removing every index
  4. Updating derived results using relevant changes rather than fully recomputing
Show correct answer and explanation

Correct answer: D. Updating derived results using relevant changes rather than fully recomputing

Incremental refresh applies deltas.

Question 93. A database trigger is?

  1. A database routine invoked by specified data or schema events
  2. Only a manual SQL report
  3. A disk hardware interrupt
  4. A client-side CSS script
Show correct answer and explanation

Correct answer: A. A database routine invoked by specified data or schema events

Triggers can act on INSERT UPDATE or DELETE events depending on engine.

Question 94. A stored procedure is?

  1. Only an index page
  2. A named database program that can execute SQL and procedural logic
  3. Only a transaction log record
  4. A non-executable comment
Show correct answer and explanation

Correct answer: B. A named database program that can execute SQL and procedural logic

Procedures encapsulate server-side operations.

Question 95. A database function often differs from a procedure in that it?

  1. Never accepts arguments
  2. Cannot read tables in any system
  3. Returns a value usable in expressions according to DBMS rules
  4. Always runs as a separate OS process
Show correct answer and explanation

Correct answer: C. Returns a value usable in expressions according to DBMS rules

Function semantics and restrictions vary across platforms.

Question 96. What is a cursor in procedural SQL?

  1. A disk address mapping table
  2. An encryption certificate
  3. A replication channel
  4. A mechanism for iterating through query result rows
Show correct answer and explanation

Correct answer: D. A mechanism for iterating through query result rows

Cursors provide row-by-row access when needed.

Question 97. Why can set-based SQL outperform row-by-row cursor logic?

  1. The engine can optimize operations over many rows together
  2. Set operations always use no RAM
  3. Every cursor is illegal
  4. Only cursor code can use indexes
Show correct answer and explanation

Correct answer: A. The engine can optimize operations over many rows together

Set-based execution reduces per-row overhead.

Question 98. What is a common table expression (CTE)?

  1. A separate operating system file
  2. A named query expression usable within a SQL statement
  3. A transaction’s log sequence number
  4. Only a user-defined index
Show correct answer and explanation

Correct answer: B. A named query expression usable within a SQL statement

WITH clauses define statement-scoped named results.

Question 99. What is a recursive CTE useful for?

  1. Only password storage
  2. Only building static B+ trees
  3. Traversing hierarchies or recursively defined relations
  4. Disabling all joins
Show correct answer and explanation

Correct answer: C. Traversing hierarchies or recursively defined relations

Recursive CTEs iterate a base case and recursive step.

Question 100. Why use prepared statements?

  1. To concatenate all untrusted input
  2. To avoid every transaction
  3. To guarantee a fixed execution plan forever
  4. To separate query structure from bound values and potentially reuse execution work
Show correct answer and explanation

Correct answer: D. To separate query structure from bound values and potentially reuse execution work

Preparation can improve safety and repeated execution.

Advanced Database Management Systems MCQs — Set 3 (Questions 101–150)

Choose an answer and reveal the explanation.

Question 101. What is a distributed database system?

  1. Data distributed over networked nodes
  2. One disk only
  3. One spreadsheet only
  4. No computers
Show correct answer and explanation

Correct answer: A. Data distributed over networked nodes

A distributed database stores and manages data across multiple networked computers while presenting coordinated access. Its design must address communication delays, partial failure, and consistency among nodes.

Question 102. What does location transparency provide in a distributed database?

  1. Eliminating network delays
  2. Hiding data placement from clients
  3. Encrypting all SQL
  4. Removing replicas
Show correct answer and explanation

Correct answer: B. Hiding data placement from clients

Location transparency hides the physical node or site holding data from application queries. Placement may change without requiring clients to hard-code every storage address.

Question 103. What is database replication?

  1. Splitting columns only
  2. Deleting duplicates
  3. Keeping copies at several nodes
  4. Only indexing
Show correct answer and explanation

Correct answer: C. Keeping copies at several nodes

Replication keeps multiple copies of data on separate nodes. It may improve read availability and resilience, but updates must be propagated and consistency rules defined.

Question 104. What is sharding in a distributed database?

  1. Copying everything everywhere
  2. Only archiving backups
  3. Only encrypting tables
  4. Splitting a dataset across nodes
Show correct answer and explanation

Correct answer: D. Splitting a dataset across nodes

Sharding partitions a dataset into subsets stored on different nodes. Unlike replication, each shard ordinarily holds a distinct portion of the logical dataset.

Question 105. What does a shard key determine?

  1. Which shard receives data
  2. Only schema names
  3. Only CPU speed
  4. Only passwords
Show correct answer and explanation

Correct answer: A. Which shard receives data

A shard key is used by the partitioning rule to choose a destination shard. Poor key selection can produce uneven load or expensive cross-shard queries.

Question 106. What is a hot shard?

  1. Stores temperatures
  2. Receives disproportionate traffic
  3. Is a backup
  4. Cannot be queried
Show correct answer and explanation

Correct answer: B. Receives disproportionate traffic

A hot shard receives much more data or traffic than other shards. One overloaded shard can limit throughput even when the rest of the cluster is underused.

Question 107. How does consistent hashing help when cluster membership changes?

  1. Remove all network faults
  2. Enforce SQL consistency
  3. Limit key remapping when nodes change
  4. Replace indexes
Show correct answer and explanation

Correct answer: C. Limit key remapping when nodes change

Consistent hashing uses an assignment scheme that can reduce the number of keys needing relocation when nodes join or leave. It does not eliminate rebalancing costs entirely.

Question 108. How does primary-replica replication normally handle writes?

  1. Uses no network
  2. Requires separate uncoordinated databases
  3. Never copies changes
  4. Writes through a designated primary
Show correct answer and explanation

Correct answer: D. Writes through a designated primary

Primary-replica designs direct authoritative writes through a designated primary node, which propagates changes to followers. Failover must manage outdated leaders carefully.

Question 109. What is replication lag?

  1. Time before replicas see source changes
  2. Disk seek time
  3. CPU queue length
  4. Password expiry
Show correct answer and explanation

Correct answer: A. Time before replicas see source changes

Replication lag is the delay between an update at the source and its appearance on a replica. Reads from lagging replicas can return stale data.

Question 110. What is failover in a database cluster?

  1. Delete all tables
  2. Move service responsibility after a failure
  3. Rename columns
  4. Convert SQL syntax
Show correct answer and explanation

Correct answer: B. Move service responsibility after a failure

Failover moves database responsibility to another healthy node following a failure. Correct recovery often requires leadership rules and safeguards against conflicting writers.

Question 111. What is synchronous replication?

  1. Never contacts replicas
  2. Always has zero latency
  3. Waits for specified replica acknowledgments
  4. Only supports reads
Show correct answer and explanation

Correct answer: C. Waits for specified replica acknowledgments

Synchronous replication waits for acknowledgments from a required set of replicas before reporting success. This may increase write latency but strengthen the configured durability guarantee.

Question 112. What is asynchronous replication?

  1. Always gives latest replicas
  2. Uses no communication
  3. Guarantees no loss
  4. May acknowledge before replicas catch up
Show correct answer and explanation

Correct answer: D. May acknowledge before replicas catch up

Asynchronous replication can acknowledge a write before followers receive it. This often reduces latency but introduces potential staleness and a risk of losing recent updates during failover.

Question 113. What does eventual consistency guarantee under suitable conditions?

  1. Replicas converge when changes stop under suitable conditions
  2. Every read is immediately newest
  3. There is never divergence
  4. All writes must be local
Show correct answer and explanation

Correct answer: A. Replicas converge when changes stop under suitable conditions

Eventual consistency permits temporary disagreement. If updates cease and communication and reconciliation continue successfully, replicas converge toward a common state.

Question 114. What does linearizability require?

  1. Every query has one index
  2. Operations appear atomic and respect real-time ordering
  3. All nodes complete instantly
  4. Only serial transactions are allowed
Show correct answer and explanation

Correct answer: B. Operations appear atomic and respect real-time ordering

Linearizability makes each operation appear atomic between invocation and response while preserving real-time precedence. It rules out observations inconsistent with completed earlier operations.

Question 115. What does serializability require of concurrent transactions?

  1. Every operation finishes in wall-clock order
  2. Only one node exists
  3. Transaction outcomes match some serial order
  4. No concurrency occurs
Show correct answer and explanation

Correct answer: C. Transaction outcomes match some serial order

Serializability means the effects of concurrent transactions are equivalent to some serial execution order. It does not automatically enforce real-time order.

Question 116. What is a network partition?

  1. Only a table partition
  2. Disk formatting
  3. SQL injection
  4. Nodes cannot communicate reliably across a separation
Show correct answer and explanation

Correct answer: D. Nodes cannot communicate reliably across a separation

A network partition interrupts communication between some nodes or groups although those nodes may remain operational. The system must determine how to handle requests during isolation.

Question 117. What tradeoff does the CAP theorem address during a partition?

  1. Consistency versus availability during partitions
  2. Cache versus disk speed
  3. CPU versus memory
  4. Compression versus encryption
Show correct answer and explanation

Correct answer: A. Consistency versus availability during partitions

CAP states that under a communication partition, a system cannot simultaneously guarantee linearizable consistency and full availability for all relevant requests. The tradeoff is conditional on partition occurrence.

Question 118. What does availability mean in the CAP theorem?

  1. Every read is newest
  2. Every request to a nonfailed node eventually receives a response
  3. Latency is zero
  4. Nodes cannot fail
Show correct answer and explanation

Correct answer: B. Every request to a nonfailed node eventually receives a response

In the CAP model, availability requires each request to a nonfailed node to eventually receive a response. That response need not reflect the globally newest value.

Question 119. How can a CP-oriented system respond during a network partition?

  1. Always processes every request
  2. Never checks versions
  3. May deny some requests to preserve consistency
  4. Eliminates all partitions
Show correct answer and explanation

Correct answer: C. May deny some requests to preserve consistency

A CP-oriented system may reject or delay operations that cannot be coordinated safely during a partition. This protects its consistency guarantee at the expense of some availability.

Question 120. How can an AP-oriented system respond during a network partition?

  1. Always guarantees linearizability
  2. Stops every request
  3. Requires one node
  4. May serve requests with temporary divergence
Show correct answer and explanation

Correct answer: D. May serve requests with temporary divergence

An AP-oriented system may continue accepting operations in separated partitions and reconcile divergent replicas later. This preserves response availability but may expose temporary differences.

Question 121. What is a quorum in replicated database operations?

  1. Required count of replica responses
  2. Database login role
  3. A SQL aggregate
  4. Only a disk block
Show correct answer and explanation

Correct answer: A. Required count of replica responses

A quorum is a specified minimum number of replicas that must participate or acknowledge an operation. Quorum choices influence latency, fault tolerance, and overlap.

Question 122. What condition guarantees that read and write quorums intersect for N replicas?

  1. R plus W less than N
  2. R plus W greater than N
  3. R and W both zero
  4. R times W equals one
Show correct answer and explanation

Correct answer: B. R plus W greater than N

When R + W > N, any read set of size R intersects every write set of size W among N replicas. This intersection alone does not determine which version is returned.

Question 123. Does quorum intersection by itself guarantee linearizability?

  1. Eliminates failures
  2. Always permits no replicas
  3. Does not guarantee linearizability without full protocol controls
  4. Eliminates concurrency
Show correct answer and explanation

Correct answer: C. Does not guarantee linearizability without full protocol controls

No. Even overlapping quorums can serve stale versions without correct timestamp, ordering, or concurrency rules. Full protocol guarantees are necessary for linearizability.

Question 124. What is distributed consensus?

  1. Only SQL syntax
  2. Only compression
  3. Only indexing
  4. Agreement among distributed nodes on shared decisions
Show correct answer and explanation

Correct answer: D. Agreement among distributed nodes on shared decisions

Consensus protocols let distributed participants agree on a common decision despite faults permitted by the protocol’s assumptions. Agreement and termination are distinct properties.

Question 125. What does the leader do in the Raft consensus algorithm?

  1. Coordinates log replication and commitment
  2. Only runs queries
  3. Never sends messages
  4. Only handles caches
Show correct answer and explanation

Correct answer: A. Coordinates log replication and commitment

In Raft, the elected leader coordinates log entries and replicates them to followers. Its term, majority acknowledgments, and commitment rules protect the replicated log.

Question 126. What is leader election?

  1. Selecting a query index
  2. Selecting a coordinator
  3. Sorting a table
  4. Selecting a password
Show correct answer and explanation

Correct answer: B. Selecting a coordinator

Leader election chooses a coordinator after startup or a leader failure. The protocol must prevent conflicting authoritative leaders from committing incompatible histories.

Question 127. What is split brain in a distributed database?

  1. Two table columns
  2. Two CPU caches
  3. Multiple groups act as authority
  4. Two file formats
Show correct answer and explanation

Correct answer: C. Multiple groups act as authority

Split brain occurs when multiple disconnected nodes or groups each behave as an authoritative primary. Concurrent conflicting writes can threaten correctness.

Question 128. What is the purpose of fencing tokens?

  1. Sort all tables
  2. Encrypt all values
  3. Increase database size
  4. Reject stale leaders’ actions
Show correct answer and explanation

Correct answer: D. Reject stale leaders’ actions

Fencing tokens are increasing identifiers attached to a leader’s operations. A storage service can reject older tokens, preventing a stale former leader from continuing to write.

Question 129. What are the two phases of two-phase commit?

  1. Prepare voting then a final decision
  2. Two SELECT statements
  3. Two SQL indexes
  4. Two backups
Show correct answer and explanation

Correct answer: A. Prepare voting then a final decision

Two-phase commit has a prepare/vote phase and a decision phase. The coordinator asks participants whether they can commit, then sends a final commit or abort decision.

Question 130. Why can two-phase commit block after a coordinator failure?

  1. No transaction is logged
  2. Prepared participants can wait after coordinator failure
  3. There are no writes
  4. All failures are impossible
Show correct answer and explanation

Correct answer: B. Prepared participants can wait after coordinator failure

Prepared participants may retain locks while waiting for a coordinator that has failed before conveying the decision. Traditional 2PC can therefore block without recovery or extra mechanisms.

Question 131. What is a saga in distributed transaction processing?

  1. Always one global ACID transaction
  2. Only backups
  3. Local transactions with compensations
  4. Only key-value lookup
Show correct answer and explanation

Correct answer: C. Local transactions with compensations

A saga coordinates a multi-step business workflow using local transactions, often with compensating actions for completed steps if a later step fails. It is not equivalent to one fully isolated ACID transaction.

Question 132. What does a compensating transaction do?

  1. Always reverses CPU clock
  2. Only drops an index
  3. Only logs errors
  4. Semantically reverses an earlier completed action
Show correct answer and explanation

Correct answer: D. Semantically reverses an earlier completed action

A compensating transaction applies a business-level action intended to reverse or offset an earlier completed step. It may not restore the exact earlier physical state.

Question 133. What makes a database request idempotent?

  1. Repeated execution has the intended effect of one execution
  2. Every retry doubles effects
  3. Always read-only
  4. Cannot return data
Show correct answer and explanation

Correct answer: A. Repeated execution has the intended effect of one execution

An idempotent operation has the same intended effect after repeated applications as after one application. Idempotency is valuable when clients retry after uncertain network responses.

Question 134. Why use an idempotency key?

  1. An encryption key only
  2. Recognizes repeated logical requests
  3. A CPU opcode
  4. A shard’s IP only
Show correct answer and explanation

Correct answer: B. Recognizes repeated logical requests

An idempotency key identifies attempts of the same logical request. The server can recognize duplicates and avoid repeating side effects such as charging a payment twice.

Question 135. How does the transactional outbox pattern work?

  1. Only sends logs
  2. Only caches data
  3. Stores change and event together in a local transaction
  4. Only creates snapshots
Show correct answer and explanation

Correct answer: C. Stores change and event together in a local transaction

The transactional outbox stores a data change and an outgoing event record within one local transaction. A separate publisher later sends the event, reducing the risk of losing it after commit.

Question 136. What does the term NoSQL describe?

  1. Only databases without queries
  2. Only CSV files
  3. All systems without consistency
  4. Nontraditional relational or flexible database models
Show correct answer and explanation

Correct answer: D. Nontraditional relational or flexible database models

NoSQL is a broad term for databases outside conventional relational-only models, including key-value, document, graph and wide-column stores. Their query and consistency models vary.

Question 137. What is a key-value database?

  1. Maps keys to associated values
  2. Always runs relational joins
  3. Only stores SQL views
  4. Only stores graphs
Show correct answer and explanation

Correct answer: A. Maps keys to associated values

A key-value database retrieves values by unique keys. The stored value may be opaque to the database, making keyed access simple and efficient.

Question 138. What is a document database?

  1. Only stores images
  2. Stores structured documents with potentially nested fields
  3. Requires fixed relational schema always
  4. Only stores text files
Show correct answer and explanation

Correct answer: B. Stores structured documents with potentially nested fields

Document databases store records as structured documents, commonly with nested fields. They can index document attributes and support flexible record shapes.

Question 139. What is a wide-column database?

  1. Only one relational row
  2. Only a spreadsheet
  3. Uses partitioned columns or column families
  4. Only a graph matrix
Show correct answer and explanation

Correct answer: C. Uses partitioned columns or column families

Wide-column databases organize data around partitioned rows and flexible column families. Their design often favors distributed storage and predictable access patterns.

Question 140. What does a graph database emphasize?

  1. Only sorts arrays
  2. Only stores video
  3. Only runs XML parsing
  4. Emphasizes entities and relationship traversal
Show correct answer and explanation

Correct answer: D. Emphasizes entities and relationship traversal

Graph databases represent entities and relationships explicitly, often as nodes and edges. Traversal queries efficiently follow connected relationships.

Question 141. How can sharding improve database scalability?

  1. Distributes storage and workload across nodes
  2. Only increases password length
  3. Only adds table columns
  4. Only compresses disk
Show correct answer and explanation

Correct answer: A. Distributes storage and workload across nodes

Sharding spreads storage and request processing across multiple nodes. It can increase capacity, but poorly balanced shards and cross-shard transactions can become bottlenecks.

Question 142. Why can a replica return stale data?

  1. Only missing SQL indexes
  2. Results from delayed propagation of updates
  3. Only table locking
  4. Only disk capacity
Show correct answer and explanation

Correct answer: B. Results from delayed propagation of updates

Stale reads occur when updates have not yet reached the replica serving a request. Asynchronous propagation and network delays are common causes.

Question 143. What is meant by strong consistency in distributed storage?

  1. Means all data are encrypted
  2. Only means high availability
  3. Restricts stale or conflicting observations under a defined model
  4. Prohibits indexing
Show correct answer and explanation

Correct answer: C. Restricts stale or conflicting observations under a defined model

Strong consistency requires observations to obey a precisely specified model, often restricting stale or conflicting reads. Linearizability is one particularly strong model.

Question 144. How can a hot shard be mitigated?

  1. Delete all logs
  2. Disable monitoring
  3. Use only one node
  4. Rebalance data or change shard strategy
Show correct answer and explanation

Correct answer: D. Rebalance data or change shard strategy

Changing shard keys, splitting partitions, adding capacity or rebalancing requests can reduce a hotspot. The best intervention depends on the source of skew.

Question 145. What is a distributed transaction?

  1. Coordinates updates across more than one participant
  2. Only one local SELECT
  3. Only one index lookup
  4. Only an in-memory function
Show correct answer and explanation

Correct answer: A. Coordinates updates across more than one participant

A distributed transaction coordinates operations involving multiple nodes, shards or services. It must consider partial failures and the atomicity requirements of the application.

Question 146. What is read repair?

  1. Deletes a shard
  2. Updates stale replicas discovered during reads
  3. Rewrites query syntax
  4. Changes passwords
Show correct answer and explanation

Correct answer: B. Updates stale replicas discovered during reads

Read repair identifies a stale replica during a read and updates it to a newer eligible version. It is one mechanism for gradual replica convergence.

Question 147. What is an anti-entropy process?

  1. Disables all replication
  2. Always forces one leader
  3. Compares and reconciles replicas over time
  4. Only performs backups
Show correct answer and explanation

Correct answer: C. Compares and reconciles replicas over time

Anti-entropy protocols periodically compare replica states or summaries and exchange missing updates. They help replicas converge even when earlier messages were missed.

Question 148. What is a conflict-free replicated data type (CRDT)?

  1. Always requires a single central lock
  2. Means a SQL primary key
  3. Prevents network partitions
  4. Supports convergent replicated updates under defined merge rules
Show correct answer and explanation

Correct answer: D. Supports convergent replicated updates under defined merge rules

A CRDT is designed so concurrent replica updates can be merged deterministically without conflicts under its defined operations. Appropriate merge properties support eventual convergence.

Question 149. What is a consistent read snapshot?

  1. Presents related data versions from a coherent logical point
  2. Always shows only newest uncommitted data
  3. Never uses versions
  4. Forces all databases offline
Show correct answer and explanation

Correct answer: A. Presents related data versions from a coherent logical point

A consistent snapshot presents mutually compatible data versions from a logical point in the execution history. It avoids mixing changes that violate the snapshot’s visibility rules.

Question 150. Why does data locality matter in distributed database systems?

  1. Only sorting names
  2. Placing data close to where it is processed or accessed
  3. Only deleting replicas
  4. Only using random sharding
Show correct answer and explanation

Correct answer: B. Placing data close to where it is processed or accessed

Data locality reduces the network and storage overhead of moving records to remote processors. Placing work near frequently accessed data can improve latency and throughput.

Advanced Database Management Systems MCQs — Set 4 (Questions 151–200)

Choose an answer and reveal the correct option and explanation.

Question 151. What is database authentication?

  1. Determining how many rows a table contains
  2. Choosing an index type
  3. Verifying the identity of a user or connecting service
  4. Only compressing files
Show correct answer and explanation

Correct answer: C. Verifying the identity of a user or connecting service

Authentication establishes who is connecting.

Question 152. What is database authorization?

  1. Only logging SQL statements
  2. Determining table size
  3. Only checking passwords
  4. Controlling operations an authenticated principal may perform
Show correct answer and explanation

Correct answer: D. Controlling operations an authenticated principal may perform

Authorization enforces privileges.

Question 153. What does the SQL GRANT statement do?

  1. Assign specified privileges to users or roles
  2. Delete all user rows
  3. Create a full database backup
  4. Rebuild every index
Show correct answer and explanation

Correct answer: A. Assign specified privileges to users or roles

GRANT confers allowed operations.

Question 154. What does SQL REVOKE typically do?

  1. Restore dropped tables
  2. Withdraw previously granted privileges according to DBMS rules
  3. Commit all connections
  4. Encrypt every value
Show correct answer and explanation

Correct answer: B. Withdraw previously granted privileges according to DBMS rules

REVOKE removes permissions.

Question 155. What is a database role?

  1. A disk sector
  2. Only an index page
  3. A named collection or identity for privilege assignment
  4. Only a SQL join
Show correct answer and explanation

Correct answer: C. A named collection or identity for privilege assignment

Roles simplify administration of permissions.

Question 156. Which principle limits database access to required operations only?

  1. Full access for all users
  2. Implicit trust
  3. Public write access
  4. Least privilege
Show correct answer and explanation

Correct answer: D. Least privilege

Restricting privileges reduces impact of compromise.

Question 157. What is row-level security?

  1. Policies restricting visible or modifiable rows for different principals
  2. Only file encryption
  3. Only table sorting
  4. Only a database backup
Show correct answer and explanation

Correct answer: A. Policies restricting visible or modifiable rows for different principals

Row-level policies filter access by user context.

Question 158. What is dynamic data masking?

  1. Permanently deleting all sensitive columns
  2. Presenting obscured values to certain users according to policy
  3. Encrypting all network links automatically
  4. Changing table names
Show correct answer and explanation

Correct answer: B. Presenting obscured values to certain users according to policy

Masking limits exposed sensitive values but is not equivalent to encryption.

Question 159. What does encryption at rest protect?

  1. Every query against SQL injection
  2. All account misuse
  3. Stored data against selected unauthorized storage-access threats
  4. All incorrect backups
Show correct answer and explanation

Correct answer: C. Stored data against selected unauthorized storage-access threats

At-rest encryption addresses storage exposure.

Question 160. What does encryption in transit protect?

  1. Only data stored offline
  2. Only query optimization
  3. Only table integrity constraints
  4. Data exchanged over connections from interception and tampering under its security model
Show correct answer and explanation

Correct answer: D. Data exchanged over connections from interception and tampering under its security model

TLS and related protocols protect transport.

Question 161. Why are prepared statements recommended for untrusted SQL inputs?

  1. They keep bound data separate from query syntax
  2. They eliminate all authorization checks
  3. They disable query optimizers
  4. They guarantee no performance cost
Show correct answer and explanation

Correct answer: A. They keep bound data separate from query syntax

Parameterized queries reduce SQL injection risk.

Question 162. What is a database audit log?

  1. Only a cache table
  2. A record of relevant operations or access for investigation and accountability
  3. A copy of SQL documentation
  4. A physical data page
Show correct answer and explanation

Correct answer: B. A record of relevant operations or access for investigation and accountability

Audit trails help detect inappropriate activity.

Question 163. What is the purpose of column-level permissions?

  1. Only speed up sorting
  2. Always improve normalization
  3. Restrict access to selected columns
  4. Choose partition sizes
Show correct answer and explanation

Correct answer: C. Restrict access to selected columns

Column privileges limit field-level exposure.

Question 164. What is data anonymization?

  1. Reversibly replacing names with IDs in all cases
  2. Only encrypting a table
  3. Creating a secondary index
  4. Removing practical means of linking data to individuals under a specified context
Show correct answer and explanation

Correct answer: D. Removing practical means of linking data to individuals under a specified context

Effective anonymization reduces reidentification potential.

Question 165. What is data retention management?

  1. Applying rules about how long records are kept and when they are disposed
  2. Always keeping everything forever
  3. Only performing joins
  4. Only moving rows to RAM
Show correct answer and explanation

Correct answer: A. Applying rules about how long records are kept and when they are disposed

Retention reduces unnecessary storage and exposure.

Question 166. A full database backup normally contains?

  1. Only changes since the last backup
  2. Data needed to restore the complete selected database state at a backup point
  3. Only transaction log headers
  4. Only user account names
Show correct answer and explanation

Correct answer: B. Data needed to restore the complete selected database state at a backup point

Full backups provide a broad restore baseline.

Question 167. An incremental backup normally contains?

  1. Every historical row regardless of prior copies
  2. Only system catalog definitions
  3. Changes since the preceding relevant backup
  4. Only deleted indexes
Show correct answer and explanation

Correct answer: C. Changes since the preceding relevant backup

Incremental chains store changes between backup points.

Question 168. A differential backup typically contains?

  1. Changes only since the latest incremental
  2. Only table names
  3. Only transaction metadata without pages
  4. Changes since the most recent full backup
Show correct answer and explanation

Correct answer: D. Changes since the most recent full backup

Differentials accumulate changes from the full baseline.

Question 169. What is point-in-time recovery?

  1. Restoring a database to a chosen recoverable moment using backups and logs
  2. Always restoring only the newest full backup
  3. Only rebuilding statistics
  4. Converting the schema to third normal form
Show correct answer and explanation

Correct answer: A. Restoring a database to a chosen recoverable moment using backups and logs

Logs can replay to a specified point.

Question 170. What is an RPO?

  1. Only the number of replicas
  2. The maximum acceptable potential data loss measured in time
  3. The exact size of a database page
  4. Only a password length
Show correct answer and explanation

Correct answer: B. The maximum acceptable potential data loss measured in time

Recovery Point Objective sets a data-loss target.

Question 171. What is an RTO?

  1. The number of disk blocks per index
  2. A transaction timestamp
  3. Target maximum time to restore service following disruption
  4. Only the backup retention interval
Show correct answer and explanation

Correct answer: C. Target maximum time to restore service following disruption

Recovery Time Objective sets a restoration target.

Question 172. Why is RAID not a substitute for backups?

  1. RAID is always slower than tape
  2. RAID cannot store SQL data
  3. RAID prevents all hardware failures
  4. It does not protect against all deletion corruption or ransomware events
Show correct answer and explanation

Correct answer: D. It does not protect against all deletion corruption or ransomware events

Independent backups address other failure modes.

Question 173. What does immutable backup retention aim to provide?

  1. Resistance to modification or deletion during a specified period
  2. Automatic rewriting after every SELECT
  3. Unrestricted overwrite access
  4. No need to test restores
Show correct answer and explanation

Correct answer: A. Resistance to modification or deletion during a specified period

Immutability strengthens recovery against tampering.

Question 174. What is database high availability?

  1. A database that cannot be updated
  2. Designing service continuity despite supported failures
  3. Only a fast query plan
  4. A table with high row count
Show correct answer and explanation

Correct answer: B. Designing service continuity despite supported failures

Redundancy and failover support availability.

Question 175. Why should backups be restore-tested?

  1. Every backup is corrupted by default
  2. Restoring is always instantaneous
  3. A created backup file does not by itself prove recoverability
  4. Backups remove transaction requirements
Show correct answer and explanation

Correct answer: C. A created backup file does not by itself prove recoverability

Restore tests validate procedures and data integrity.

Question 176. What is a data warehouse?

  1. Only a transactional write buffer
  2. Only a query optimizer hint
  3. An operating-system swap file
  4. A system optimized for integrated historical analytics and reporting
Show correct answer and explanation

Correct answer: D. A system optimized for integrated historical analytics and reporting

Warehouses integrate data for analysis.

Question 177. What does OLTP stand for?

  1. Online Transaction Processing
  2. Ordered Logical Table Partitioning
  3. Offline Large Table Parsing
  4. Online Log Transfer Protocol
Show correct answer and explanation

Correct answer: A. Online Transaction Processing

OLTP handles operational transactions.

Question 178. What does OLAP stand for?

  1. Ordered Lock Access Protocol
  2. Online Analytical Processing
  3. Optimized Log Archive Procedure
  4. Only Local Application Processing
Show correct answer and explanation

Correct answer: B. Online Analytical Processing

OLAP supports multidimensional analysis.

Question 179. What is a fact table in dimensional modeling?

  1. Only a user login record
  2. A system catalog index
  3. A table storing measures and keys to analysis dimensions
  4. Only a table with a primary key
Show correct answer and explanation

Correct answer: C. A table storing measures and keys to analysis dimensions

Facts capture measurable events.

Question 180. What is a dimension table?

  1. Only numeric aggregation results
  2. An encrypted transaction log
  3. A B+ tree internal node
  4. A table describing analysis contexts such as time product or location
Show correct answer and explanation

Correct answer: D. A table describing analysis contexts such as time product or location

Dimensions provide descriptive attributes.

Question 181. What is a star schema?

  1. One central fact table linked to denormalized dimension tables
  2. A directed graph with only five vertices
  3. Only a fully normalized OLTP schema
  4. A database requiring exactly five tables
Show correct answer and explanation

Correct answer: A. One central fact table linked to denormalized dimension tables

Star schemas support analytic joins.

Question 182. What is a snowflake schema in data warehousing?

  1. A schema without fact tables
  2. A dimensional schema where dimension attributes are further normalized into related tables
  3. Only a single key-value record
  4. A storage replication protocol
Show correct answer and explanation

Correct answer: B. A dimensional schema where dimension attributes are further normalized into related tables

Snowflaking normalizes dimension hierarchies.

Question 183. What does ETL stand for?

  1. Execute Transaction Lock
  2. Encrypt Transfer Log
  3. Extract Transform Load
  4. Estimate Table Layout
Show correct answer and explanation

Correct answer: C. Extract Transform Load

ETL prepares source data for target systems.

Question 184. What does ELT stand for?

  1. Encrypt Link Transfer
  2. Execute Local Transaction
  3. Evaluate Log Table
  4. Extract Load Transform
Show correct answer and explanation

Correct answer: D. Extract Load Transform

ELT transforms data after loading it to the target platform.

Question 185. What is a slowly changing dimension?

  1. A design for preserving or managing changes in descriptive dimension attributes over time
  2. A table that never changes
  3. Only an index rebuilt slowly
  4. A network replica
Show correct answer and explanation

Correct answer: A. A design for preserving or managing changes in descriptive dimension attributes over time

SCD techniques track evolving attributes.

Question 186. What is a Type 2 slowly changing dimension?

  1. Always overwriting the only row
  2. A method creating new versioned dimension rows to preserve historical states
  3. Deleting all prior records
  4. Only updating an index key
Show correct answer and explanation

Correct answer: B. A method creating new versioned dimension rows to preserve historical states

Type 2 maintains history with version records.

Question 187. What is a data cube?

  1. Only a cube-shaped disk
  2. A three-column SQL table exactly
  3. A multidimensional organization of measures across dimensions
  4. Only a graph database
Show correct answer and explanation

Correct answer: C. A multidimensional organization of measures across dimensions

Cubes support analytic aggregation.

Question 188. What is roll-up in OLAP?

  1. Filtering to one specific detail record only
  2. Deleting historical facts
  3. Locking transactions
  4. Aggregating data to a higher level of a dimension hierarchy
Show correct answer and explanation

Correct answer: D. Aggregating data to a higher level of a dimension hierarchy

Roll-up summarizes details.

Question 189. What is drill-down in OLAP?

  1. Moving from aggregated data to more detailed levels
  2. Converting all data to one total
  3. Encrypting measure columns
  4. Rebuilding a B+ tree
Show correct answer and explanation

Correct answer: A. Moving from aggregated data to more detailed levels

Drill-down reveals finer granularity.

Question 190. What is a materialized aggregate?

  1. Only a virtual schema name
  2. A stored precomputed summary used to accelerate analytic queries
  3. A transaction lock
  4. A user privilege
Show correct answer and explanation

Correct answer: B. A stored precomputed summary used to accelerate analytic queries

Persisted aggregates reduce repeated computation.

Question 191. What is a column-oriented storage format useful for?

  1. Only workloads reading every column for one row
  2. Only transaction logging
  3. Analytic queries reading a few columns across many rows
  4. Always avoiding compression
Show correct answer and explanation

Correct answer: C. Analytic queries reading a few columns across many rows

Columnar storage reduces irrelevant field reads.

Question 192. Why can columnar databases compress well?

  1. Every value is unique by definition
  2. No values are stored
  3. All pages are encrypted
  4. Similar values are stored together allowing effective encodings
Show correct answer and explanation

Correct answer: D. Similar values are stored together allowing effective encodings

Column similarity favors dictionary and run-length encodings.

Question 193. What is vectorized query execution?

  1. Processing batches of values per operator invocation
  2. Only executing one row per instruction
  3. A graph visualization feature
  4. A table partitioning rule
Show correct answer and explanation

Correct answer: A. Processing batches of values per operator invocation

Batch execution reduces interpretation overhead.

Question 194. What is a window function in SQL?

  1. A function that must collapse every group to one row
  2. A function computing results over a partition of related rows while retaining input row granularity
  3. Only a stored procedure
  4. Only a memory cache function
Show correct answer and explanation

Correct answer: B. A function computing results over a partition of related rows while retaining input row granularity

Window functions support ranks and running totals.

Question 195. What does PARTITION BY do in an SQL window specification?

  1. Physically shards tables necessarily
  2. Creates new disk partitions automatically
  3. Defines groups of rows within which window functions operate
  4. Deletes duplicates
Show correct answer and explanation

Correct answer: C. Defines groups of rows within which window functions operate

Window partitions organize computation logically.

Question 196. What does ROW_NUMBER assign?

  1. The same rank to every tied value
  2. The total table size in bytes
  3. A random primary key
  4. A unique sequence number within each ordered window partition
Show correct answer and explanation

Correct answer: D. A unique sequence number within each ordered window partition

ROW_NUMBER distinguishes rows according to window ordering.

Question 197. How does DENSE_RANK differ from RANK?

  1. It does not leave gaps in rank values after ties
  2. It always produces unique numbers
  3. It ignores ORDER BY
  4. It deletes duplicate rows
Show correct answer and explanation

Correct answer: A. It does not leave gaps in rank values after ties

Dense ranking advances by one distinct value.

Question 198. What is query performance tuning?

  1. Only changing database passwords
  2. Improving workload latency and resource use through evidence-based changes
  3. Always adding every possible index
  4. Only renaming tables
Show correct answer and explanation

Correct answer: B. Improving workload latency and resource use through evidence-based changes

Tuning considers plans indexes and workload metrics.

Question 199. What is a query regression?

  1. A table loses a foreign key always
  2. A database switches time zones
  3. A previously acceptable query becomes slower after a change
  4. A query is syntactically invalid
Show correct answer and explanation

Correct answer: C. A previously acceptable query becomes slower after a change

Plan changes or data growth can degrade performance.

Question 200. What is a workload benchmark?

  1. Only a backup snapshot
  2. Only a row-level lock
  3. A database constraint
  4. A controlled set of operations used to evaluate system performance
Show correct answer and explanation

Correct answer: D. A controlled set of operations used to evaluate system performance

Benchmarks provide comparative performance measurements.

Privacy Policy
Powered by CookieAdmin