Computer Science Exit Exam Study Guide · 239 questions
Read the question first. Select the eye beside the reading hint, study the cited module section, then reveal the answer. Each eye controls only its own block. All hints and answers start closed whenever this page opens.
Read the linked study-guide sections before revealing the answer. PDF page references count from the first page of the document.
239 of 239 questions
Question 1
_______ is the process of selecting appropriate query execution strategy?
A. Query graph
B. Query optimization
C. Query tree
D. Query processing
Hint / Module Reference (Where to Read)
Separate query representation, strategy selection and execution.
Selecting an efficient execution strategy is optimization. A tree or graph represents the query; processing is the broader transformation and execution activity.
Evidence in the complete study note
Parsing checks SQL grammar; semantic analysis resolves names, types and access permissions. Translation constructs a logical representation, typically relational algebra or a logical query tree. Optimization chooses equivalent operations, join order and physical access methods. Evaluation executes that plan and returns results. Not every implementation exposes exactly the same phase boundaries.ADB17
Query processing is the whole activity; selecting a suitable efficient strategy is optimization.ADB17
A query graph represents the predicate relationships of a relational-calculus expression without itself prescribing an operator execution order.ADB-S01
Choice by choice
A · incorrect — A graph corresponds to relational calculus, not strategy selection. (PDF pp. 27, 34.)
B · correct — The module explicitly defines optimization as choosing an efficient strategy. (PDF pp. 27, 34.)
C · incorrect — A query tree represents relational algebra and operations. (PDF pp. 27, 34.)
D · incorrect — Processing includes decomposition and execution as well as optimization. (PDF pp. 27, 34.)
Question 2
Which update-timing policy permits a transaction's changes to reach the persistent database before the transaction commits?
A. In-Place Update
B. Immediate update
C. Shadow update
D. Deferred update
Hint / Module Reference (Where to Read)
Separate update timing from update location and follow the commit boundary.
Immediate update permits a transaction's changes to be written to the persistent database before commit. If that transaction later aborts, recovery may need to undo those changes. Deferred update keeps uncommitted changes outside the persistent database. In-place update describes overwriting an existing location, while shadow update describes writing a separate version. Those location strategies do not, by their names alone, specify the timing policy requested here. Immediate update need not flush every cache write immediately.
Evidence in the complete study note
Deferred update keeps uncommitted changes outside the persistent database; in the usual no-force setting it is NO-UNDO/REDO. Redo-only and NO-UNDO/REDO mean the same thing, so those cannot be separate unique answers. Immediate update allows persistent changes before commit and therefore may require undo; it need not flush every write_item immediately.ADB29
Shadow/copy-on-write update writes a new location while preserving the committed version.ADB29
In-place update overwrites the original location.ADB29
Choice by choice
A · incorrect — Describes overwriting the existing location, not the commit-timing policy.
B · correct — Correct: immediate update permits persistent database changes before commit and may require undo.
C · incorrect — Describes writing a separate version while preserving the committed one, not the requested timing policy.
D · incorrect — Deferred update excludes uncommitted changes from the persistent database.
Question 3
Which query-optimization approach applies relational-algebra properties and equivalences to rewrite a query and reduce intermediate work?
A. Database storage without query rewriting
B. Heuristic approach
C. Deterministic execution without query rewriting
D. Physical-plan cost estimation
Hint / Module Reference (Where to Read)
Consider which equivalent transformation changes intermediate work and what assumptions preserve meaning.
Heuristic optimization uses algebraic rules to reduce intermediate work, for example applying selective filters earlier, replacing a product plus join predicate with a join and projecting away unneeded columns. Storage and execution without rewriting do not perform those transformations. Cost estimation predicts resources for candidate plans rather than naming the algebraic rewrite approach itself. A practical optimizer can combine rewriting and costing, but the question isolates the rule-based transformation role.
Evidence in the complete study note
Heuristics reduce intermediate work: push selective row filters toward base tables; replace product followed by a join predicate with a join; project unneeded columns away while retaining join/filter columns; reorder inner joins when equivalence permits; reuse common expressions when worthwhile. Split conjunctive selections when each predicate can be applied earlier to its own input.ADB18
A cost model estimates resources for a candidate plan: page I/O, CPU, memory, temporary storage and network traffic.ADB19
Choice by choice
A · incorrect — Incorrect: storage without rewriting does not transform the algebraic query representation.
B · correct — Correct: heuristic rewriting uses operator properties and equivalences to reduce intermediate work.
C · incorrect — Incorrect: repeatable execution without rewriting does not apply the requested query transformations.
D · incorrect — Incorrect: cost estimation predicts candidate-plan resource use rather than identifying the algebraic rewrite approach.
Question 4
In which phase of the two phase locking protocol can a transaction obtain locks?
A. Release phase
B. Roll back phase
C. Shrinking phase
D. Growing phase
Hint / Module Reference (Where to Read)
Track the first lock release and the transaction-completion boundary.
Two-phase locking acquires locks in the growing phase, then releases them in the shrinking phase without acquiring new ones.
Evidence in the complete study note
Basic 2PL has a growing phase that acquires/upgrades locks and a shrinking phase that releases/downgrades them. Once any lock is released, no new lock may be acquired. The lock point is the instant the final lock is obtained. 2PL guarantees conflict serializability for well-formed transactions, but can deadlock and basic 2PL alone need not prevent cascading aborts.ADB12
Two-phase locking orders access to data; two-phase commit coordinates a distributed commit decision.ADB12
Strict 2PL retains exclusive locks until commit/abort.ADB12
Choice by choice
A · incorrect — Release is the unlocking portion, not acquisition. (PDF pp. 86.)
B · incorrect — Rollback is not one of the two acquisition/release phases. (PDF pp. 86.)
C · incorrect — Shrinking forbids new locks. (PDF pp. 86.)
D · correct — Growing allows acquisition and forbids release. (PDF pp. 86.)
Question 5
Which one of the following is not problem if concurrency is not controlled properly?
A. Lost update problem
B. Consistent database
C. Incorrect summary
D. Dirty read
Hint / Module Reference (Where to Read)
Trace which transaction wrote each value and whether an overwrite or mixed-version read occurs.
Lost update, temporary update/dirty read, and incorrect summary are concurrency problems. A consistent database is the desired outcome of concurrency control.
Evidence in the complete study note
Lost update: T1 and T2 both read 500; T2 adds 200 and writes 700; T1 adds 100 to its stale value and writes 600. Correct serial execution yields 800. Dirty read: T2 reads a value written by uncommitted T1; if T1 aborts, dependent work must be reconsidered. Reading one's own changes is not a dirty read.ADB08
Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure.ADB06
A transaction is a logical unit of database work, including read-only work.ADB06
Choice by choice
A · incorrect — Lost update is explicitly a concurrency anomaly. (PDF pp. 68, 70, 79.)
B · correct — Consistency is a desired state, so this is the exception. (PDF pp. 68, 70, 79.)
C · incorrect — Incorrect summary mixes values from different update stages. (PDF pp. 68, 70, 79.)
D · incorrect — Dirty read exposes an update before its transaction has safely committed. (PDF pp. 68, 70, 79.)
Question 6
Which one of the following is not a condition for two operations in a schedule to be said to conflict?
A. If they access the same item X
B. If they belong to the same transactions
C. If Write_item (X) is at least one of the operations
D. If they belong to different transactions
Hint / Module Reference (Where to Read)
Distinguish transaction ordering, conflict conditions and full execution equivalence.
Correct answer: B — If they belong to the same transactions
Conflict requires different transactions, the same item, and at least one write. Membership in the same transaction is therefore the exception.
Evidence in the complete study note
A serial schedule executes each transaction's operations consecutively. A serializable schedule is equivalent to some serial order; it may interleave transactions. Equality of one final value is insufficient evidence because different executions can accidentally coincide. Two operations conflict when they belong to different transactions, access the same item and at least one writes. Read/read pairs do not conflict.ADB09
Choice by choice
A · incorrect — Accessing the same item is required. (PDF pp. 70, 71.)
B · correct — Same-transaction operations fail the different-transaction requirement. (PDF pp. 70, 71.)
C · incorrect — At least one write is required. (PDF pp. 70, 71.)
D · incorrect — Different transactions is required, so D is not the exception. (PDF pp. 70, 71.)
Question 7
During restart recovery after a crash, which operation removes the database effects of a transaction that had not committed?
A. View
B. Commit
C. Rollback
D. Redo
Hint / Module Reference (Where to Read)
Identify what failed, what durable resources survive and which restoration action is required.
Atomicity requires removing the effects of an incomplete transaction after a crash. Rollback performs that undo work, restoring before-images as needed. Commit instead accepts successful transaction effects, and a view is a query representation rather than a recovery action. Redo installs logged after-images to recover missing work; it does not name the removal of uncommitted effects requested here. These actions occur during restart recovery after the system is operating again.
Evidence in the complete study note
Restart recovery restores a valid database after a crash while preserving durable commits and removing incomplete effects. Rollback handles failed/uncommitted work; redo preserves committed work missing from pages; checkpoints reduce log-search work; backups provide another stored copy.ADB26
Undo restores before-images of incomplete effects, usually traversing that transaction's updates backward. Redo installs logged after-images when necessary, usually forward.ADB27
A view is a stored query definition, not encryption.ADB25
Choice by choice
A · incorrect — Incorrect: a view is not an operation that reverses transaction effects.
B · incorrect — Incorrect: commit accepts transaction effects rather than undoing the incomplete work.
C · correct — Correct: rollback undoes the effects of the uncommitted transaction.
D · incorrect — Incorrect: redo installs logged after-images; the requested removal requires undo/rollback.
Question 8
A system is in a ______ state if there exists a set of transactions such that every transaction in the set is waiting for another transaction in the set.
A. Idle
B. Waiting
C. Deadlock
D. Ready
Hint / Module Reference (Where to Read)
Distinguish a waiting cycle, a long wait and indefinite unfair postponement.
Mutual waiting among transactions for locks held by one another is a deadlock condition.
Evidence in the complete study note
Deadlock requires a waiting cycle: T1 holds X and wants Y; T2 holds Y and wants X. In a wait-for graph, an edge Ti→Tj means Ti waits for a lock held by Tj. A cycle indicates deadlock under the ordinary lock-wait model. Abort a suitable victim, undo its effects and release its locks; repeat if other cycles remain. Aborting every deadlocked transaction also breaks cycles but wastes more work.ADB13
Fair queues, sensible victim selection and retaining original transaction timestamps on retry reduce starvation; deadlock freedom alone does not prove fairness.ADB13
A timeout aborts long waits but may mistake slow progress for deadlock.ADB13
Choice by choice
A · incorrect — Idle does not express mutual lock waiting. (PDF pp. 87.)
B · incorrect — Waiting alone need not involve a cycle. (PDF pp. 87.)
C · correct — Deadlock is mutual waiting that prevents progress. (PDF pp. 87.)
D · incorrect — Ready does not describe transactions blocked on one another. (PDF pp. 87.)
Question 9
Users may request permitted aggregates but must not infer protected individual values by subtracting overlapping aggregate answers. Which security measure specifically addresses this indirect-disclosure threat?
A. Inference control
B. Access control
C. Integrity control
D. Encryption
E. None of the above
Hint / Module Reference (Where to Read)
Identify whether the scenario concerns identity, permission, disclosure or valid values.
Inference control targets learning protected individual facts from aggregate answers. For example, subtracting a sum excluding one person from a sum including that person can reveal their value even when their row is hidden. Ordinary access control grants or denies actions, integrity validates data and encryption protects encoded content. The specified indirect-disclosure threat identifies inference control specifically.
Evidence in the complete study note
Authentication establishes identity; authorization/access control determines permitted actions. Encryption protects stored or transmitted content; integrity constraints restrict valid data; auditing records actions. A user who can view but cannot change a record has restricted access privileges. A view can restrict visible rows/columns, but its privilege and execution rules must support that restriction.ADB24
Inference control concerns learning protected individual facts indirectly from allowed aggregate answers.ADB24
Releasing SUM for a group and SUM for the same group excluding one known person may disclose that person's value by subtraction.ADB24
Choice by choice
A · correct — This directly prevents disclosure of individual facts inferred from allowed aggregates.
B · incorrect — Authorization alone does not prevent inference from otherwise allowed answers.
C · incorrect — Integrity constrains valid data rather than indirect disclosure.
D · incorrect — Encryption does not stop an authorized user combining decrypted aggregate answers.
E · incorrect — Inference control directly matches the threat.
Question 10
Which approach rewrites a query's internal representation using relational-algebra equivalences, without relying on integrity-constraint deductions or comparison of estimated plan costs?
A. Systematic plan-cost estimation
B. Semantic query optimization
C. Cost Estimation
D. Heuristic rules
E. Both A and C
Hint / Module Reference (Where to Read)
Consider which equivalent transformation changes intermediate work and what assumptions preserve meaning.
Heuristic rules rewrite algebraic representations using equivalences, such as applying suitable selections early or replacing a product followed by a join condition with a join. Semantic optimization additionally reasons from trusted constraints. Cost estimation predicts resources for candidate plans, and doing that systematically remains a costing activity. The stem isolates equivalence-based rewriting without those other bases, so D is correct and combining the two cost-estimation descriptions does not change the answer.
Evidence in the complete study note
Heuristics reduce intermediate work: push selective row filters toward base tables; replace product followed by a join predicate with a join; project unneeded columns away while retaining join/filter columns; reorder inner joins when equivalence permits; reuse common expressions when worthwhile. Split conjunctive selections when each predicate can be applied earlier to its own input.ADB18
Semantic optimization additionally uses trusted constraints, such as a key proving a join cannot multiply matching rows. Cost-based optimization estimates candidate plans instead of relying on rules alone.ADB18
A cost model estimates resources for a candidate plan: page I/O, CPU, memory, temporary storage and network traffic.ADB19
Choice by choice
A · incorrect — Incorrect: systematic cost estimation predicts plan costs rather than performing the specified equivalence-based rewriting.
B · incorrect — Incorrect: semantic optimization additionally uses trusted constraints, which the stem excludes.
C · incorrect — Incorrect: cost estimation compares resource predictions for candidate plans.
D · correct — Correct: heuristic rules apply the stated relational-algebra equivalences.
E · incorrect — Incorrect: A and C both describe costing, not the isolated algebraic-rewrite approach.
Question 11
Which of the following is not true about locks?
A. Locks with large granularity are easier for the DBMS to administer.
B. Locks with small granularity cause more conflicts.
C. Locks with large granularity produce fewer details for the DBMS to track.
D. Locks may have a table-level granularity.
E. Locks may have a database-level granularity.
Hint / Module Reference (Where to Read)
Consider conflicts, private work and validation costs, or lock-management overhead.
Correct answer: B — Locks with small granularity cause more conflicts.
Fine granularity raises concurrency, whereas coarse granularity locks more data with fewer lock entries. Thus the claim that smaller units cause more conflicts reverses the tradeoff.
Evidence in the complete study note
Fine-grained row locks permit more independent operations but require tracking many locks. Coarse table/database locks simplify management but block unrelated rows and reduce concurrency. Intention locks advertise planned descendant locks in a hierarchy; escalation replaces many small locks with a coarser one. Small granularity does not inherently cause more logical conflicts. Choose granularity from contention and management overhead, not a universal rule.ADB16
Choice by choice
A · incorrect — A single coarse lock can replace many fine locks and reduce management work. (PDF pp. 83, 95.)
B · correct — Fine units allow more independent access, so this is the false claim. (PDF pp. 83, 95.)
C · incorrect — Coarse locks reduce the number of entries tracked. (PDF pp. 83, 95.)
D · incorrect — The file/relation granularity in the hierarchy permits table-level locking. (PDF pp. 83, 95.)
E · incorrect — Entire-database locking is expressly listed. (PDF pp. 83, 95.)
Question 12
For every transaction T participating in the schedule, if all the operations of T are
executed consecutively in the schedule. The schedule is
A. Cascaded schedule
B. Serial schedule
C. Cascadeless schedule
D. Recoverable schedule
E. None
Hint / Module Reference (Where to Read)
Distinguish transaction ordering, conflict conditions and full execution equivalence.
Consecutive execution of every transaction's operations is the definition of a serial schedule. Recoverability and cascadelessness instead constrain dependencies on commits.
Evidence in the complete study note
A serial schedule executes each transaction's operations consecutively. A serializable schedule is equivalent to some serial order; it may interleave transactions. Equality of one final value is insufficient evidence because different executions can accidentally coincide. Two operations conflict when they belong to different transactions, access the same item and at least one writes. Read/read pairs do not conflict.ADB09
Recoverable means if Tj reads Ti's value, Tj cannot commit before Ti commits.ADB10
Cascadeless means a transaction reads only values written by committed transactions, avoiding cascading aborts.ADB10
Choice by choice
A · incorrect — Cascading concerns dependent rollbacks, not contiguous execution. (PDF pp. 72, 71.)
B · correct — Serial exactly matches the consecutive-operations definition. (PDF pp. 72, 71.)
C · incorrect — Cascadeless means reading only committed writes. (PDF pp. 72, 71.)
D · incorrect — Recoverable constrains commit order after a read dependency. (PDF pp. 72, 71.)
E · incorrect — A listed definition does match, so None is wrong. (PDF pp. 72, 71.)
Question 13
A transaction property state about changes applied to the database by a committed
transaction must persist in the database?
A. Isolation
B. Durability or permanency
C. Atomicity
D. Consistency preservation
E. None
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
Persisting committed modifications despite subsequent failure is durability or permanency.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
Choice by choice
A · incorrect — Isolation regulates visibility between transactions. (PDF pp. 66.)
B · correct — Durability protects committed changes against loss. (PDF pp. 66.)
C · incorrect — Atomicity is all-or-nothing execution. (PDF pp. 66.)
D · incorrect — Consistency preserves valid states rather than persistence. (PDF pp. 66.)
E · incorrect — Durability is a listed matching property. (PDF pp. 66.)
Question 14
Which update-timing policy keeps a transaction's uncommitted changes outside the persistent database until commit?
A. Undo
B. In-place modification
C. Immediate-modification
D. Deferred-modification
Hint / Module Reference (Where to Read)
Separate update timing from update location and follow the commit boundary.
Deferred modification keeps uncommitted updates outside the persistent database. If the transaction fails before commit, those database pages do not require undo for its changes; redo can still be needed after commit in a no-force system. Immediate modification permits precommit persistent changes. In-place modification describes overwriting the original location without specifying the commit timing, while undo is an action that reverses applied effects. D therefore names the requested timing policy.
Evidence in the complete study note
Deferred update keeps uncommitted changes outside the persistent database; in the usual no-force setting it is NO-UNDO/REDO. Redo-only and NO-UNDO/REDO mean the same thing, so those cannot be separate unique answers. Immediate update allows persistent changes before commit and therefore may require undo; it need not flush every write_item immediately.ADB29
In-place update overwrites the original location.ADB29
Choice by choice
A · incorrect — Incorrect: undo reverses effects already applied rather than naming a policy postponing them.
B · incorrect — Incorrect: in-place modification specifies overwriting location, not postponement until commit.
C · incorrect — Incorrect: immediate modification permits persistent changes before commit.
D · correct — Correct: deferred modification keeps uncommitted changes outside the persistent database.
Question 15
Which one of the following is false?
A. In object-oriented database, data are stored as collections of rows and tables
B. In relational database, data are perceived by users as tables
C. Object oriented database allows object identification and communication
D. Object oriented database allows reusability of objects
E. None of the above
Hint / Module Reference (Where to Read)
Distinguish an object instance from its class, attribute values and persistent identity.
Correct answer: A — The rows-and-tables claim is false for the object-oriented model.
An OODBMS primarily models persistent objects with identity, state and behavior, rather than relational rows and tables. Objects communicate through methods and object references, and inheritance supports reuse. Relational presentation as tables is the contrasting model. Therefore A is the false statement.
Evidence in the complete study note
An object combines identity, state and behavior. A class specifies the attributes and operations of its instances; an object is one instance. The object identifier (OID) distinguishes objects independently of their attribute values and remains stable over that object's lifetime. Two objects can have equal values and different identities; changing a name does not create a new identity.ADB01
It does not primarily present objects as rows and tables.ADB01
An object-relational DBMS extends a relational system with richer types; ORM maps programming objects into relational tables.ADB01
Choice by choice
A · correct — This is false: an OODBMS primarily models persistent objects rather than relational rows and tables.
B · incorrect — Presenting relations as tables describes the relational contrast correctly.
C · incorrect — Object identity and method-based collaboration are supported object-model concepts.
D · incorrect — Inheritance and reusable object behavior support reuse, so this is not the false statement.
E · incorrect — A is false, so none of the above cannot answer this negative question.
Question 16
27.----------is a collection of multiple logically related database distributed over a computer
network.
A. Data Fragmentation
B. Distributed Database
C. Data Allocation
D. Data Replication
Hint / Module Reference (Where to Read)
Separate coordinated network placement from engine compatibility and semantic integration.
A distributed database is a coordinated collection of logically related data across networked sites. Fragmentation divides a relation into row or column subsets; replication creates copies; allocation chooses the sites storing fragments or copies. These are design decisions within a distributed database rather than alternative names for the whole collection. Distribution does not require every fragment to be replicated, and the question's definition identifies the database as a whole.
Evidence in the complete study note
A distributed database contains logically related data across networked sites, managed as a coordinated system.ADB33
Horizontal fragmentation selects row subsets by predicates; their union reconstructs the relation.ADB36
Full replication places the whole database at every relevant site; partial replication copies selected fragments to selected sites; nonredundant allocation stores each fragment once.ADB37
Choice by choice
A · incorrect — Incorrect: fragmentation splits relations into subsets rather than naming the entire networked collection.
B · correct — Correct: a distributed database is the coordinated logically related data collection across networked sites.
C · incorrect — Incorrect: allocation assigns fragments or copies to sites; it is a placement decision.
D · incorrect — Incorrect: replication creates copies and is a design choice rather than the definition of the collection.
Question 17
............... Means that a transaction must execute exactly once completely or not at all.
A. Consistency
B. Durability
C. Isolation
D. Atomicity
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
Atomicity means a transaction is performed in its entirety or not performed at all. 'Exactly once' is interpreted here as all-or-nothing completion, rather than exactly-once delivery semantics.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
Choice by choice
A · incorrect — Consistency concerns valid before/after states. (PDF pp. 66.)
B · incorrect — Durability concerns survival after commit. (PDF pp. 66.)
C · incorrect — Isolation concerns interaction with other transactions. (PDF pp. 66.)
D · correct — Atomicity supplies the all-or-nothing rule. (PDF pp. 66.)
Question 18
Checkpoints are a part of
A. Recovery measures
B. Concurrency measures
C. Authorization measures
D. Security measures
Hint / Module Reference (Where to Read)
Identify which history a recovery boundary can safely exclude and whether active work spans it.
A checkpoint synchronizes database and log so recovery need not search the entire history. Its purpose is recovery work reduction.
Evidence in the complete study note
A checkpoint records enough durable recovery state to avoid scanning all old history. In a simple quiescent/checkpoint exercise, specified flushing may make earlier committed work unnecessary to redo. Active transactions can span the checkpoint, so their relevant earlier updates can still need undo. Do not assume every transaction before a checkpoint needs no processing.ADB30
Recovery may start analysis at the checkpoint but redo from an earlier required log position.ADB30
Without checkpoints or another valid truncation boundary, long-running systems may need expensive log scans; deferred update does not avoid that problem.ADB30
Choice by choice
A · correct — Checkpointing is described in database recovery. (PDF pp. 105, 107.)
B · incorrect — Locking and scheduling are concurrency controls; this checkpoint has a recovery role. (PDF pp. 105, 107.)
C · incorrect — Privileges authorize access rather than synchronize recovery state. (PDF pp. 105, 107.)
D · incorrect — Checkpointing does not authenticate users or grant privileges. (PDF pp. 105, 107.)
Question 19
Which of the following is not a recovery technique?
A. ARIES Algorithm
B. Two-phase commit
C. Immediate update
D. Deferred update
Hint / Module Reference (Where to Read)
Distinguish coordinated transaction outcome from concurrent access and local storage.
Deferred update, immediate update, and ARIES are recovery approaches. Two-phase commit coordinates the distributed commit decision; it is the intended exception among the named recovery algorithms, though commit protocols also support distributed recovery.
Evidence in the complete study note
Two-phase commit (2PC) first asks participants to prepare and durably promise they can commit. If all vote yes, the coordinator records commit durably and tells participants; otherwise it decides abort. A prepared participant must retain enough state to obey the final decision. Coordinator/communication failure can leave it in doubt and block progress until the decision is learned. Timeout after preparation is not permission to invent a commit or abort.ADB39
2PC coordinates atomic outcome; locking/other concurrency protocols provide isolation.ADB39
Immediate update allows persistent changes before commit and therefore may require undo; it need not flush every write_item immediately.ADB29
Choice by choice
A · incorrect — ARIES is explicitly a recovery algorithm. (PDF pp. 107, 109, 123.)
B · correct — Two-phase commit is a distributed commit approach, not the listed update-recovery algorithm. (PDF pp. 107, 109, 123.)
C · incorrect — Immediate update appears under recovery techniques. (PDF pp. 107, 109, 123.)
D · incorrect — Deferred update is a recovery scheme. (PDF pp. 107, 109, 123.)
Question 20
Which of the following is not true about OODBMS (object-oriented database management
system).
A. The key concepts is objects
B. The object is persistent
C. The key concept is relation
D. Object Identity is unchangeable
Hint / Module Reference (Where to Read)
Distinguish an object instance from its class, attribute values and persistent identity.
Correct answer: C — Relation is not the defining concept of an OODBMS.
The defining unit of an object database is an object with identity, state and behavior. ADB01 explains both persistence beyond program execution and identifier stability throughout an object lifetime. Relation is instead the characteristic abstraction of the relational model. Thus C is the one statement that fails to describe an OODBMS.
Evidence in the complete study note
An object combines identity, state and behavior. A class specifies the attributes and operations of its instances; an object is one instance. The object identifier (OID) distinguishes objects independently of their attribute values and remains stable over that object's lifetime. Two objects can have equal values and different identities; changing a name does not create a new identity.ADB01
Persistence lets an object survive beyond the program execution that created it.ADB01
Complex CAD or multimedia structures can suit an object model, but many-to-many relationships alone do not require abandoning relational design.ADB01
Choice by choice
A · incorrect — Objects are the defining units of the object-oriented database model.
B · incorrect — An OODBMS provides persistence so objects survive the program execution that created them.
C · correct — This is false for OODBMS: relation is the central abstraction of the relational model.
D · incorrect — The object identifier remains stable throughout the lifetime of its object.
Question 21
Which optimization approach uses algebraic rules, such as pushing suitable selections before joins, to choose an operation order without exhaustively enumerating plans or comparing estimated costs?
A. Semantic query optimization based on trusted constraints
B. Heuristic- based
C. Cost estimation
D. Exhaustive search
Hint / Module Reference (Where to Read)
Consider which equivalent transformation changes intermediate work and what assumptions preserve meaning.
Heuristic optimization orders or rewrites operations using algebraic rules intended to reduce intermediate work. Applying suitable selections early is a typical example. Semantic optimization additionally uses trusted constraints, cost estimation predicts resource usage for candidate strategies, and exhaustive search enumerates alternatives rather than selecting a small set through rules alone. Although practical optimizers can combine these methods, the stated stem asks specifically for rule-based algebraic ordering, making B unique.
Evidence in the complete study note
Heuristics reduce intermediate work: push selective row filters toward base tables; replace product followed by a join predicate with a join; project unneeded columns away while retaining join/filter columns; reorder inner joins when equivalence permits; reuse common expressions when worthwhile. Split conjunctive selections when each predicate can be applied earlier to its own input.ADB18
Semantic optimization additionally uses trusted constraints, such as a key proving a join cannot multiply matching rows. Cost-based optimization estimates candidate plans instead of relying on rules alone.ADB18
Choice by choice
A · incorrect — Incorrect: this alternative bases deductions on trusted constraints rather than the isolated algebraic ordering rules.
B · correct — Correct: heuristic optimization orders operations through algebraic rules such as early selection.
C · incorrect — Incorrect: cost estimation predicts candidate-plan resources instead of the specified rule-only choice.
D · incorrect — Incorrect: exhaustive search enumerates alternatives, which the stem explicitly excludes.
Question 22
A transaction in which either all of the database actions occur or none of them does is called
A. Atomicity
B. Consistency
C. Isolation
D. Durability
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
All database actions occurring or none occurring is atomicity, the first ACID property.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
Choice by choice
A · correct — Atomicity is the entire-or-none requirement. (PDF pp. 66.)
B · incorrect — Consistency is preservation of valid states. (PDF pp. 66.)
C · incorrect — Isolation separates concurrent effects. (PDF pp. 66.)
D · incorrect — Durability preserves completed commits. (PDF pp. 66.)
Question 23
A transaction is still active when one of its changes is written into the persistent database. Which update-timing policy permits this?
A. Deferred-Update
B. In-place update
C. Immediate-Update
D. Undo
Hint / Module Reference (Where to Read)
Separate update timing from update location and follow the commit boundary.
Immediate update permits persistent database changes while the transaction is still active, before commit. An abort may therefore require undo. Deferred update keeps uncommitted changes outside the persistent database. In-place update describes overwriting a location rather than the commit-timing policy, and undo is the reversal of applied effects. The question concerns which timing policy permits the observed precommit write, not whether all immediate-update systems must flush every cache change immediately.
Evidence in the complete study note
Deferred update keeps uncommitted changes outside the persistent database; in the usual no-force setting it is NO-UNDO/REDO. Redo-only and NO-UNDO/REDO mean the same thing, so those cannot be separate unique answers. Immediate update allows persistent changes before commit and therefore may require undo; it need not flush every write_item immediately.ADB29
In-place update overwrites the original location.ADB29
Choice by choice
A · incorrect — Incorrect: deferred update excludes uncommitted changes from the persistent database.
B · incorrect — Incorrect: in-place update describes overwriting the existing location rather than the commit-timing policy.
C · correct — Correct: immediate update permits persistent changes before commit.
D · incorrect — Incorrect: undo reverses effects; it is not the update-timing policy.
Question 24
Which of the following occurs when a transaction cannot proceed for an indefinite period while
other transactions in the system continue normally?
A. Starvation
B. Deadlock
C. checkpoint
D. lock point
Hint / Module Reference (Where to Read)
Distinguish a waiting cycle, a long wait and indefinite unfair postponement.
Starvation means indefinite postponement of a transaction while other transactions continue. It can result from repeated waiting or repeated victim selection without any waiting cycle. Deadlock instead involves a cycle of mutually blocked transactions. A checkpoint records recovery state, and the lock point in two-phase locking is the instant a transaction acquires its final lock. Neither is the name of indefinite unfair postponement, so A remains the unique answer.
Evidence in the complete study note
Fixed resource ordering and conservative 2PL prevent cycles. A no-wait policy aborts immediately on an incompatible lock request and needs no age timestamp. A timeout aborts long waits but may mistake slow progress for deadlock. Starvation is indefinite postponement while other work proceeds, without a required cycle. Fair queues, sensible victim selection and retaining original transaction timestamps on retry reduce starvation; deadlock freedom alone does not prove fairness.ADB13
The lock point is the instant the final lock is obtained.ADB12
A checkpoint records enough durable recovery state to avoid scanning all old history.ADB30
Choice by choice
A · correct — Correct: indefinite postponement while others proceed is starvation.
B · incorrect — Incorrect: deadlock requires a waiting cycle, which is not required for starvation.
C · incorrect — Incorrect: a checkpoint records durable recovery state; it is not an indefinite waiting condition.
D · incorrect — Incorrect: the lock point is the instant the transaction acquires its final lock in 2PL.
Question 25
The atom constructor can represent the following basic atomic values except.
A. Integers
B. Real Numbers
C. Strings
D. Array
Hint / Module Reference (Where to Read)
Classify the shape of a value separately from an operation that creates an instance.
Correct answer: D — Array is not an atom constructor.
ADB04 lists integer, real, Boolean and string as examples of basic atomic values. An array instead supplies indexed positions and is a collection constructor, even when a programming variable holds it as one value. Consequently integers, reals and strings fit the atom category while an array is the requested exception.
Evidence in the complete study note
In a common object model, an object is described by (OID,type constructor,state). An atom is one basic value, such as an integer, real, Boolean or string. A tuple constructor groups named component values into one structured record, such as (Street:'A',City:'B'). It is not a collection of records.ADB04
An array is not an atomic constructor merely because stored as one programming variable.ADB04
A set is an unordered collection without duplicates; a bag allows duplicates; a list has order; an array provides indexed positions.ADB04
Choice by choice
A · incorrect — An integer is explicitly listed as a basic atomic value.
B · incorrect — A real number is explicitly included among atomic values.
C · incorrect — A string is a basic atomic value under the object-model convention used here.
D · correct — An array contains indexed positions and is a collection constructor rather than an atomic one.
Question 26
A system crashes again during ARIES recovery. Durable WAL, pageLSNs and compensation log records survive; storage is intact. No checkpoints were taken. Which statement correctly describes the second restart?
A. The same undo and redo list will be used while recovering again
B. The database will become inconsistent
C. Durable compensation records and pageLSNs preserve recovery progress, so already-installed actions can be skipped or safely redone as required.
D. The system cannot any further
Hint / Module Reference (Where to Read)
Trace durable recovery metadata and what progress survives a second crash.
Correct answer: C — C. Durable compensation records and pageLSNs preserve recovery progress, so already-installed actions can be skipped or safely redone as required.
ARIES records recovery progress in durable metadata. PageLSNs help determine whether a redo action is already installed, and compensation records preserve undo progress and its continuation. A second restart can therefore recover safely without assuming that the same undo and redo work lists recur. Lack of checkpoints may increase scanning work, but it does not erase this progress or make further recovery impossible.
Evidence in the complete study note
ARIES uses WAL, page log sequence numbers (pageLSN), a transaction table and a dirty-page table. Analysis reconstructs active/loser transactions and dirty-page information. Redo repeats history, starting from the relevant earliest dirty-page update; pageLSNs help skip already-installed actions. Undo reverses loser transactions.ADB31
This makes recovery restartable: after a second crash, durable undo progress need not be undone again as if no progress occurred.ADB31
Compensation log records (CLRs) describe undo actions and their continuation information.ADB31
Choice by choice
A · incorrect — Durable progress can change the required work, so identical lists are not guaranteed.
B · incorrect — Restartable recovery does not make inconsistency inevitable.
C · correct — PageLSNs and durable CLRs support safe continuation after repeated crashes.
D · incorrect — ARIES can restart using surviving durable recovery metadata.
Question 27
The rigorous two-phase locking protocol permits releasing all locks at the__________
A. Beginning of the transaction
B. During the execution of transaction
C. End of transaction
D. Never in the life-time of transaction
Hint / Module Reference (Where to Read)
Track the first lock release and the transaction-completion boundary.
Correct answer: C — At transaction end, on commit or abort.
Rigorous 2PL retains both shared and exclusive locks until commit or abort, unlike basic 2PL, which permits release in its shrinking phase. FDB-style early release would allow interference before completion and would not meet rigorous locking. The locks are eventually released at the transaction boundary, so never is also false.
Evidence in the complete study note
Strict 2PL retains exclusive locks until commit/abort. Rigorous 2PL retains both shared and exclusive locks until that point. Conservative/static 2PL obtains all needed locks before execution and does not begin while only a subset is available, preventing hold-and-wait deadlock. It requires knowing the lock set and can reduce concurrency. Two-phase locking orders access to data; two-phase commit coordinates a distributed commit decision.ADB12
Choice by choice
A · incorrect — Releasing locks at the beginning would not protect the transaction's later accesses.
B · incorrect — Rigorous 2PL does not permit early release during ongoing transaction execution.
C · correct — Both shared and exclusive locks remain held until commit or abort ends the transaction.
D · incorrect — Locks are released at completion; the protocol does not hold them forever.
Question 28
In which of the following state of the transaction executes all its operations successfully and all
the effects are now permanently saved on the database system?
A. Active
B. Partial committed
C. Committed
D. Failed
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
The committed state guarantees persistent changes. Partially committed only signals the end of read/write operations and does not yet ensure permanence.
Evidence in the complete study note
Active means operations are executing. Partially committed means the last operation finished but durable commitment is not yet assured. Committed means commitment succeeded. A failure leads to Failed, then Aborted after rollback; a transaction may terminate or be restarted as a new attempt. Concurrency control primarily enforces isolation; recovery supports atomicity and durability. Transaction management is the broader subsystem, so both it and recovery management can be legitimate answers to broad responsibility questions.ADB06
Choice by choice
A · incorrect — Active means execution is in progress. (PDF pp. 65.)
B · incorrect — Partially committed explicitly does not guarantee permanent modification. (PDF pp. 65.)
C · correct — Committed ensures persistent changes. (PDF pp. 65.)
D · incorrect — Failed is an aborted or unsuccessful transaction. (PDF pp. 65.)
Question 29
Let transaction T1 has obtained a shared mode lock S on data item Y and transaction T2 has
obtained an exclusive lock X on data item Z , according to the above statement which one is correct?
A. T1 can read Y but cannot write Y
B. T2 can read Z but cannot write Z
C. T1 can read Z but cannot write Z
D. T2 can read and write item Y
Hint / Module Reference (Where to Read)
Compare access permissions and same-item compatibility before granting a lock request.
Correct answer: A — T1 can read Y but cannot write Y
Shared locks permit reads but not updates. Exclusive locks permit both reading and updating while excluding other transactions' locks on that item.
Evidence in the complete study note
A shared (S) lock permits reading; multiple S locks on the same item can coexist. An exclusive (X) lock permits its holder to read and write and conflicts with another S or X on that item. Locks on different items do not conflict merely because one is exclusive. These are the basic lock-based rules; multiversion systems can permit additional snapshot-reader behavior.ADB11
A transaction must own the appropriate lock before access and can release only its own locks.ADB11
One transaction may request a lock on an already locked item: it is granted if compatible, otherwise waits or aborts under the policy.ADB11
Choice by choice
A · correct — T1's shared lock authorizes reading Y, not writing it. (PDF pp. 82, 83.)
B · incorrect — T2's exclusive lock permits both read and update of Z. (PDF pp. 82, 83.)
C · incorrect — T1 cannot read Z while another transaction holds its exclusive lock. (PDF pp. 82, 83.)
D · incorrect — T2 cannot obtain an exclusive write lock on Y while T1 holds its shared lock. (PDF pp. 82, 83.)
Question 30
Which claim about deadlock prevention and starvation is false?
A. In conservative 2PL, starvation may occur
B. Wound–wait guarantees starvation freedom regardless of restart priorities or scheduling fairness
C. Deadlock can be prevented by wait-die
D. Some deadlock-prevention methods may cause starvation
Hint / Module Reference (Where to Read)
Identify requester and holder ages before applying the protocol's action.
Correct answer: B — Wound–wait guarantees starvation freedom regardless of restart priorities or scheduling fairness
Deadlock freedom does not itself guarantee that each transaction eventually completes. Wound–wait uses transaction age to decide whether a requester waits or aborts a younger holder, but a starvation-freedom argument also needs appropriate restart priorities, fair scheduling and holders that eventually finish. Its unconditional guarantee in B is therefore false. Conservative 2PL can postpone a transaction indefinitely under unfair admission, and wait–die prevents waiting cycles. Some prevention policies can cause repeated waiting or victimization.
Evidence in the complete study note
Retain the original timestamp when restarting an aborted transaction so it gains relative priority as new transactions arrive. Starvation avoidance additionally assumes holders eventually finish and scheduling is fair.ADB14
Fixed resource ordering and conservative 2PL prevent cycles. A no-wait policy aborts immediately on an incompatible lock request and needs no age timestamp. A timeout aborts long waits but may mistake slow progress for deadlock. Starvation is indefinite postponement while other work proceeds, without a required cycle. Fair queues, sensible victim selection and retaining original transaction timestamps on retry reduce starvation; deadlock freedom alone does not prove fairness.ADB13
Choice by choice
A · incorrect — True: conservative 2PL prevents cycles but does not by itself guarantee fair admission.
B · correct — Correct selection: the unconditional starvation-freedom claim omits necessary progress and priority assumptions.
C · incorrect — True: wait–die is a timestamp-based deadlock-prevention policy.
D · incorrect — True: preventing deadlock can still leave a transaction repeatedly postponed or aborted.
Question 31
Concurrency control in DBMS is important for which of the following reasons?
A. To ensure data integrity when updates occur to the database in a single user environment
B. To ensure data integrity when reads occur to the database in a single user environment
C. To ensure data integrity when updates occur to the database in a multi user environment
D. To ensure data integrity when reads occur to the database in a multi user environment
Hint / Module Reference (Where to Read)
Trace which transaction wrote each value and whether an overwrite or mixed-version read occurs.
Correct answer: C — To ensure data integrity when updates occur to the database in a multi user environment
Concurrency control preserves consistency when multiple users' updates interleave and conflict. Read-only sharing alone does not cause lost updates; the intended scenario is a multiuser system with updates.
Evidence in the complete study note
Lost update: T1 and T2 both read 500; T2 adds 200 and writes 700; T1 adds 100 to its stale value and writes 600. Correct serial execution yields 800. Dirty read: T2 reads a value written by uncommitted T1; if T1 aborts, dependent work must be reconsidered. Reading one's own changes is not a dirty read.ADB08
Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure.ADB06
Read/read pairs do not conflict.ADB09
Choice by choice
A · incorrect — Single-user updates lack concurrent intertransaction conflicts. (PDF pp. 68, 70, 79.)
B · incorrect — Single-user reads do not create the cited conflicts. (PDF pp. 68, 70, 79.)
C · correct — Multiuser updates can create read/write and write/write conflicts requiring scheduling. (PDF pp. 68, 70, 79.)
D · incorrect — Reads can be involved when others update, but reads alone do not capture the source's motivating condition. (PDF pp. 68, 70, 79.)
Question 32
A lock wait-for graph may contain several independent deadlock cycles. Which termination methods guarantee eliminating all deadlock cycles? Assume each aborted transaction is undone and releases its locks. Select all that apply. 'Half' and 'single' mean arbitrary selections without a repeated cycle check.
A. Abort all deadlocked transaction
B. Abort half of the deadlocked transaction
C. Aborting single deadlocked transactio
D. Aborting one transaction at a time until the deadlock cycle is eliminated
Hint / Module Reference (Where to Read)
Distinguish a waiting cycle, a long wait and indefinite unfair postponement.
Correct answers (select all): A, D — A. Abort all deadlocked transaction; D. Aborting one transaction at a time until the deadlock cycle is eliminated
Aborting all deadlocked transactions removes every participating wait, although it wastes work. A more selective complete method aborts a suitable victim, releases its locks and repeats until no cycles remain. An arbitrary half of the participants can leave a cycle intact, and one arbitrary victim may resolve only one of several independent cycles. The guarantee therefore applies to A and D.
Evidence in the complete study note
Deadlock requires a waiting cycle: T1 holds X and wants Y; T2 holds Y and wants X. In a wait-for graph, an edge Ti→Tj means Ti waits for a lock held by Tj. A cycle indicates deadlock under the ordinary lock-wait model. Abort a suitable victim, undo its effects and release its locks; repeat if other cycles remain. Aborting every deadlocked transaction also breaks cycles but wastes more work.ADB13
A no-wait policy aborts immediately on an incompatible lock request and needs no age timestamp.ADB13
Starvation is indefinite postponement while other work proceeds, without a required cycle.ADB13
Choice by choice
A · correct — Removing all deadlocked participants eliminates all their cycles.
B · incorrect — An arbitrary half may miss an entire remaining cycle.
C · incorrect — One victim need not break other independent cycles.
D · correct — Repeated victim selection and graph checking continues until every cycle is gone.
Question 33
Among the components of the DBMS, which one is responsible for keeping the isolation of
transactions?
A. Recovery protocol
B. Application programmer
C. Concurrency -control protocol
D. Database administrator
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
Concurrency control regulates interactions between simultaneous transactions and enforces isolation. Recovery supports atomicity and durability by undoing incomplete effects and preserving committed work. Application programmers must supply correct transaction logic, and the DBA administers the database rather than serving as the executing concurrency-control mechanism. The question asks for that mechanism's primary responsibility, so C is the precise answer even though administration, applications and recovery all contribute to a working database.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
Active means operations are executing. Partially committed means the last operation finished but durable commitment is not yet assured. Committed means commitment succeeded. A failure leads to Failed, then Aborted after rollback; a transaction may terminate or be restarted as a new attempt. Concurrency control primarily enforces isolation; recovery supports atomicity and durability. Transaction management is the broader subsystem, so both it and recovery management can be legitimate answers to broad responsibility questions.ADB06
Choice by choice
A · incorrect — Incorrect: recovery primarily supports atomicity and durability, rather than scheduling concurrent interactions for isolation.
B · incorrect — Incorrect: an application programmer supplies transaction logic but is not the executing isolation-control mechanism.
C · correct — Correct: concurrency control regulates conflicting concurrent operations to enforce isolation.
D · incorrect — Incorrect: the DBA is an administrative human role, not the DBMS mechanism scheduling transaction interactions.
Question 34
_________Occurs when two transactions update the same data item, but both read the same
original value before update
A. Dirty read
B. Lost update
C. Incorrect summary
D. Unrepeatable read
Hint / Module Reference (Where to Read)
Trace which transaction wrote each value and whether an overwrite or mixed-version read occurs.
Both transactions reading the original value lets a later write overwrite the earlier transaction's update; that update is lost.
Evidence in the complete study note
Lost update: T1 and T2 both read 500; T2 adds 200 and writes 700; T1 adds 100 to its stale value and writes 600. Correct serial execution yields 800. Dirty read: T2 reads a value written by uncommitted T1; if T1 aborts, dependent work must be reconsidered. Reading one's own changes is not a dirty read.ADB08
Choice by choice
A · incorrect — Dirty read concerns uncommitted values from another transaction. (PDF pp. 68, 70.)
B · correct — Lost update matches the stale-original-value overwrite. (PDF pp. 68, 70.)
C · incorrect — Incorrect summary combines differently timed values in an aggregate. (PDF pp. 68, 70.)
D · incorrect — Unrepeatable read concerns a later reread seeing a changed value. (PDF pp. 68, 70.)
Question 35
Which of the DBMS component ensures that the concurrent execution of multiple operations on
the database results into consistent database state?
A. Log
B. Buffer manager
C. File manager
D. Transaction processing system
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
The transaction processing system contains the scheduler and recovery managers. Its scheduling ensures concurrent individual actions have a net effect equivalent to one-at-a-time execution.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
A serial schedule executes each transaction's operations consecutively.ADB09
A serializable schedule is equivalent to some serial order; it may interleave transactions.ADB09
Choice by choice
A · incorrect — The log records changes but does not itself schedule consistent execution. (PDF pp. 79, 99.)
B · incorrect — Buffers hold blocks and arrange writes, not the entire consistency protocol. (PDF pp. 79, 99.)
C · incorrect — File management is not the scheduler responsible for concurrent effects. (PDF pp. 79, 99.)
D · correct — Transaction processing contains the concurrency-control component implementing this guarantee. (PDF pp. 79, 99.)
Question 36
How do you prevent system privileges deletion and updating authority on PRICE
and YEAR column of the CATALOGUE table from user Mohammed
A. GRANT DELETE, UPDATE (price,year) ON catalogue From Mohammed
B. ALLOW DELETE, UPDATE (price,year) ON catalogue From Mohammed
C. REVOKE DELETE, UPDATE (price,year) ON catalogue From Mohammed
D. DENY DELETE, UPDATE (price,year) ON catalogue From Mohammed
Hint / Module Reference (Where to Read)
Track whether a privilege is being added or withdrawn and whether it applies to rows or columns.
Correct answer: C — REVOKE DELETE, UPDATE (price,year) ON catalogue From Mohammed
To withdraw Mohammed's DELETE and column-specific UPDATE privileges, use REVOKE DELETE, UPDATE(price,year) ON catalogue FROM Mohammed. This concerns object privileges, despite the stem's 'system privileges' wording. Other independent grants may remain.
Evidence in the complete study note
GRANT SELECT ON Catalogue TO Mohammed; permits reads. REVOKE DELETE, UPDATE (Price,Year) ON Catalogue FROM Mohammed; removes the specified table deletion and column update grants in SQL dialects supporting that form. DELETE applies to rows/table access rather than particular columns; UPDATE can be column-scoped. REVOKE removes permission, whereas GRANT gives it.ADB25
WITH GRANT OPTION permits a recipient to grant a privilege onward; removing an upstream grant can cascade or be restricted according to the chosen command and DBMS.ADB25
Removing one direct grant may leave effective access through another role or grant path.ADB25
Choice by choice
A · incorrect — GRANT adds privileges and also uses TO, not FROM. (PDF pp. 54, 55.)
B · incorrect — ALLOW is not the module's SQL privilege command. (PDF pp. 54, 55.)
C · correct — REVOKE with ON and FROM matches the module syntax. (PDF pp. 54, 55.)
D · incorrect — DENY is not the revocation command presented in this module. (PDF pp. 54, 55.)
Question 37
Inclient-server database architecture, what is the primary role of the server?
A. Provide the GUI for end users
B. Initiate all network connections and poll clients for updates
C. Store and manage shared data, enforce ACID properties, and process client requests
D. Cache all user sessions locally for offline access
Hint / Module Reference (Where to Read)
Identify which tier submits, mediates or executes a request.
Correct answer: C — Store and manage shared data, enforce ACID properties, and process client requests
The database server provides database functionality and manages local data, while clients submit requests and combine results. DBMS components enforce transaction properties.
Evidence in the complete study note
In client/server architecture, clients submit requests while servers manage shared data, execute operations and enforce supported integrity/transaction rules. In collaborating-server designs, servers cooperate on subqueries and transactions. Middleware can translate requests, locate data, coordinate operations and combine results across independent servers. These describe communication responsibilities, not fragmentation types.ADB34
State how much schema, transaction and administrative autonomy local sites retain.ADB34
A tightly integrated distributed system can present one global schema and shared control.ADB34
Choice by choice
A · incorrect — The client-facing GUI is not the server's primary data-management role. (PDF pp. 125, 126, 66.)
B · incorrect — Clients initiate requests; polling clients is not the described workflow. (PDF pp. 125, 126, 66.)
C · correct — Managing data and processing client requests matches server responsibilities. (PDF pp. 125, 126, 66.)
D · incorrect — Offline local session caching is not the described database server function. (PDF pp. 125, 126, 66.)
Question 38
In two-phase locking (2PL), during which phase may a transaction acquire locks but not
release any?
A. Growing phase
B. Commit phase
C. .
Validation phase
D. Shrinking phase
Hint / Module Reference (Where to Read)
Track the first lock release and the transaction-completion boundary.
The growing phase acquires locks without releasing any. Shrinking starts with release and prohibits new acquisitions.
Evidence in the complete study note
Basic 2PL has a growing phase that acquires/upgrades locks and a shrinking phase that releases/downgrades them. Once any lock is released, no new lock may be acquired. The lock point is the instant the final lock is obtained. 2PL guarantees conflict serializability for well-formed transactions, but can deadlock and basic 2PL alone need not prevent cascading aborts.ADB12
Two-phase locking orders access to data; two-phase commit coordinates a distributed commit decision.ADB12
Optimistic/validation control reads and computes privately, validates its read/write sets against overlapping transactions, then publishes if validation succeeds; otherwise it retries.ADB16
Choice by choice
A · correct — Growing has the acquisition-without-release rule. (PDF pp. 86.)
B · incorrect — Commit is not the phase name in basic 2PL. (PDF pp. 86.)
C · incorrect — Validation belongs to optimistic concurrency control. (PDF pp. 86.)
D · incorrect — Shrinking permits release, not acquisition. (PDF pp. 86.)
Question 39
Assume durable logs and serializable concurrency control, but no checkpoints or other valid log-truncation boundary. In a high-transaction system using deferred update, which recovery cost can grow after long uptime?
A. Recovery may require scanning a very long accumulated log
B. Increased disk I/O due to frequent page copying
C. Shared locks cannot be used
D. View serializability cannot be ensured
Hint / Module Reference (Where to Read)
Identify which history a recovery boundary can safely exclude and whether active work spans it.
Correct answer: A — Recovery may require scanning a very long accumulated log
Checkpoints record recovery state so recovery need not inspect the entire accumulated history. Without a checkpoint or another valid truncation boundary, the log that must be searched can grow throughout a long-running workload. Deferred update does not remove that problem. It specifies when persistent changes occur, not a requirement to copy shadow pages or a prohibition on shared locks. Serializability is enforced by the assumed concurrency-control mechanism, rather than by the existence of checkpoints.
Evidence in the complete study note
A fuzzy checkpoint permits ongoing transactions and records active-transaction and dirty-page information without forcing all pages simultaneously. Recovery may start analysis at the checkpoint but redo from an earlier required log position. A checkpoint is neither a full backup nor an automatic commit of active transactions. Without checkpoints or another valid truncation boundary, long-running systems may need expensive log scans; deferred update does not avoid that problem.ADB30
Deferred update keeps uncommitted changes outside the persistent database; in the usual no-force setting it is NO-UNDO/REDO.ADB29
Basic 2PL has a growing phase that acquires/upgrades locks and a shrinking phase that releases/downgrades them. Once any lock is released, no new lock may be acquired. The lock point is the instant the final lock is obtained. 2PL guarantees conflict serializability for well-formed transactions, but can deadlock and basic 2PL alone need not prevent cascading aborts.ADB12
Choice by choice
A · correct — Correct: without a recovery boundary, a long-running system may need to inspect a very large accumulated log.
B · incorrect — Incorrect: deferred update describes timing and does not require frequent shadow-page copying.
C · incorrect — Incorrect: deferring persistent writes does not prohibit shared-lock concurrency control.
D · incorrect — Incorrect: serializability is enforced by the assumed concurrency control; a checkpoint limits recovery work rather than determining schedule equivalence.
Question 40
Which listed techniques use transaction logs for recovery in the course model? Interpret deferred modification as the conventional logged, no-force deferred-update method. Select all that apply.
A. Recovery in Multi-database Systems
B. ARIES Recovery Algorithm
C. Shadow Paging Technique
D. Deferred Database Modification
Hint / Module Reference (Where to Read)
Separate update timing from update location and follow the commit boundary.
Correct answers (select all): B, D — B. ARIES Recovery Algorithm; D. Deferred Database Modification
ARIES uses write-ahead logging and log-driven recovery metadata. Conventional logged deferred modification also uses recorded updates to redo committed effects that have not reached persistent pages. Both are log-based techniques under the stated model. Shadow paging instead preserves committed page mappings and installs a new map at commit. Recovery in multidatabase systems names an environment rather than one particular algorithm.
Evidence in the complete study note
Deferred update keeps uncommitted changes outside the persistent database; in the usual no-force setting it is NO-UNDO/REDO. Redo-only and NO-UNDO/REDO mean the same thing, so those cannot be separate unique answers. Immediate update allows persistent changes before commit and therefore may require undo; it need not flush every write_item immediately.ADB29
ARIES uses WAL, page log sequence numbers (pageLSN), a transaction table and a dirty-page table.ADB31
Shadow paging keeps an unchanged committed page map and a modified map; commit durably installs a new root/map pointer atomically after required pages are durable.ADB29
Choice by choice
A · incorrect — Multidatabase recovery names an environment rather than identifying a particular log-based technique.
B · correct — ARIES explicitly relies on WAL and logged recovery metadata.
C · incorrect — Shadow paging preserves page maps as an alternative to conventional log-based recovery.
D · correct — Deferred modification can use a log to redo committed changes without undo.
Question 41
Which type of fragmentation in a distributed database divides a relation into subrelations defined by subsets of its columns, normally repeating a key to support reconstruction?
A. Replication
B. Location
C. Horizontal
D. Vertical
Hint / Module Reference (Where to Read)
Identify whether rows or columns are split and reverse the split to reconstruct the original relation.
Vertical fragmentation projects column subsets into separate fragments. Repeating an appropriate key or row identifier allows a join to reconstruct the original rows without spurious combinations. Horizontal fragmentation instead selects subsets of rows, reconstructed by union. Replication creates copies rather than dividing the relation by attributes. Location transparency hides where data resides; it does not name the column-splitting design operation. The corrected category therefore supports the existing vertical-fragmentation answer.
Evidence in the complete study note
Horizontal fragmentation selects row subsets by predicates; their union reconstructs the relation. Check completeness so every original row is represented and disjointness if no overlap is intended. Define an explicit fragment for NULL/other values when needed. Vertical fragmentation projects column subsets and repeats a key or row identifier so joining on it reconstructs the original rows without spurious matches.ADB36
Location transparency hides which site holds a named object.ADB35
Full replication places the whole database at every relevant site; partial replication copies selected fragments to selected sites; nonredundant allocation stores each fragment once.ADB37
Choice by choice
A · incorrect — Replication creates copies of data rather than splitting a relation into column subsets.
B · incorrect — Location transparency hides placement details; it is not a fragmentation method.
C · incorrect — Horizontal fragmentation partitions rows, not columns.
D · correct — Correct: vertical fragmentation splits columns and normally repeats a reconstruction key.
Question 42
In two-phase locking, which phase permits releasing existing locks but forbids acquiring new locks?
A. Expanding phase
B. Waiting phase
C. Shrinking phase
D. All of the above
Hint / Module Reference (Where to Read)
Track the first lock release and the transaction-completion boundary.
Two-phase locking divides lock use into a growing phase and a shrinking phase. During growing, a transaction may acquire or upgrade locks; once it releases a lock, it enters shrinking and cannot acquire any new lock. Waiting describes delay on a conflicting request, not a separate 2PL phase defined by releasing locks. Because the growing phase allows acquisition instead of release, the all-of-the-above alternative also fails.
Evidence in the complete study note
Basic 2PL has a growing phase that acquires/upgrades locks and a shrinking phase that releases/downgrades them. Once any lock is released, no new lock may be acquired. The lock point is the instant the final lock is obtained. 2PL guarantees conflict serializability for well-formed transactions, but can deadlock and basic 2PL alone need not prevent cascading aborts.ADB12
Choice by choice
A · incorrect — Incorrect: the growing/expanding phase acquires locks and does not release them.
B · incorrect — Incorrect: waiting on a conflicting request is a delay condition, not the formal 2PL release phase.
C · correct — Correct: shrinking permits release while forbidding new acquisitions.
D · incorrect — Incorrect: the growing phase does not meet the release/no-new-lock rule.
Question 43
Which one of the SQL keywords is used to provide access?
A. REVOKE
B. DISTINCT
C. GRANT
D. None of the above
Hint / Module Reference (Where to Read)
Track whether a privilege is being added or withdrawn and whether it applies to rows or columns.
GRANT gives specified users privileges on an object. REVOKE removes privileges.
Evidence in the complete study note
GRANT SELECT ON Catalogue TO Mohammed; permits reads. REVOKE DELETE, UPDATE (Price,Year) ON Catalogue FROM Mohammed; removes the specified table deletion and column update grants in SQL dialects supporting that form. DELETE applies to rows/table access rather than particular columns; UPDATE can be column-scoped. REVOKE removes permission, whereas GRANT gives it.ADB25
WITH GRANT OPTION permits a recipient to grant a privilege onward; removing an upstream grant can cascade or be restricted according to the chosen command and DBMS.ADB25
Roles group privileges.ADB25
Choice by choice
A · incorrect — REVOKE withdraws access privileges. (PDF pp. 54, 55.)
B · incorrect — DISTINCT is not the module's access-authorization command. (PDF pp. 54, 55.)
C · correct — GRANT supplies the privileges on tables or views. (PDF pp. 54, 55.)
D · incorrect — GRANT is listed, so None is false. (PDF pp. 54, 55.)
Question 44
is a tree data structure that corresponds to a relational algebra
expression.
A. a | Query graph x
B. i? Query tree WA
C. '@| Query optimizer x
D. | None of the above x
Hint / Module Reference (Where to Read)
Separate query representation, strategy selection and execution.
A query tree corresponds to a relational algebra expression, with relations as leaves and operations as internal nodes.
Evidence in the complete study note
Parsing checks SQL grammar; semantic analysis resolves names, types and access permissions. Translation constructs a logical representation, typically relational algebra or a logical query tree. Optimization chooses equivalent operations, join order and physical access methods. Evaluation executes that plan and returns results. Not every implementation exposes exactly the same phase boundaries.ADB17
A query tree corresponds to a relational-algebra expression: base relations appear at leaves and operators at internal nodes.ADB-S01
A query tree has base relations at leaves and operators at internal nodes; a query graph emphasizes relations and predicates.ADB17
Choice by choice
A · incorrect — Query graph corresponds to relational calculus. (PDF pp. 34.)
B · correct — Query tree is the exact defined algebraic tree. (PDF pp. 34.)
C · incorrect — The optimizer chooses an execution strategy; it is not this data structure. (PDF pp. 34.)
D · incorrect — The matching tree is explicitly listed. (PDF pp. 34.)
Question 45
Which listed approaches use both undo and redo? Interpret Immediate Update as the conventional steal/no-force logged model and Deferred Update as no-steal/no-force; use the course's ARIES definition. Select all that apply.
A. Immediate Update
B. Deferred Update
C. ARIES Algorithm
D. None
Hint / Module Reference (Where to Read)
Determine whether uncommitted changes or unflushed committed changes may exist on persistent pages.
Correct answers (select all): A, C — A. Immediate Update; C. ARIES Algorithm
Steal allows uncommitted changes to reach persistent pages, creating a possible need for undo. No-force allows committed changes to remain buffered, creating a possible need for redo. Immediate update under the specified combined policy therefore needs both. ARIES also performs redo and then undoes losers. The specified deferred no-steal/no-force model needs redo but no undo, so it does not qualify.
Evidence in the complete study note
Steal permits a page with uncommitted changes to be written to disk; hence recovery may need undo. No-steal prevents those uncommitted effects reaching the persistent database. Force writes a transaction's updated data pages at commit; no-force permits committed dirty pages to remain buffered, hence recovery may need redo.ADB28
For the conventional page/log model: steal/no-force needs undo and redo; steal/force needs undo but no redo; no-steal/no-force needs redo but no undo; no-steal/force needs neither data-page undo nor redo after a system crash.ADB28
Deferred update keeps uncommitted changes outside the persistent database; in the usual no-force setting it is NO-UNDO/REDO.ADB29
Choice by choice
A · correct — The stated steal/no-force policy requires undo and redo.
B · incorrect — The specified deferred policy prevents persistent uncommitted effects and needs redo only.
C · correct — ARIES repeats history with redo and reverses losers with undo.
D · incorrect — Two listed approaches use both actions.
Question 46
Which recovery mechanism preserves the committed page map while writing modified pages elsewhere, then atomically installs a new map at commit as an alternative to conventional log-based recovery?
A. Disk recovery
B. Shadow paging
C. Deferred update
D. Crash recovery
Hint / Module Reference (Where to Read)
Separate update timing from update location and follow the commit boundary.
Shadow paging preserves the committed page map and writes modified pages to separate locations. Once the required pages are durable, commit atomically installs the new map; before that point, recovery can use the old committed map. Deferred update instead describes postponing persistent database changes until commit and does not itself specify this page-map mechanism. Disk recovery and crash recovery name recovery domains or events, not the particular algorithm requested.
Evidence in the complete study note
In-place update overwrites the original location. Shadow/copy-on-write update writes a new location while preserving the committed version. Shadow paging keeps an unchanged committed page map and a modified map; commit durably installs a new root/map pointer atomically after required pages are durable. Before commit, a crash uses the old map. It is an alternative to conventional log-based recovery, with allocation, fragmentation and concurrency costs; it is not primarily a special action done only during recovery.ADB29
Deferred update keeps uncommitted changes outside the persistent database; in the usual no-force setting it is NO-UNDO/REDO.ADB29
Choice by choice
A · incorrect — Incorrect: disk recovery describes a recovery domain, not the specified preserved-page-map mechanism.
B · correct — Correct: shadow paging preserves the committed map and installs the new map atomically at commit.
C · incorrect — Incorrect: deferred update specifies commit timing, not this copy-on-write page-map algorithm.
D · incorrect — Incorrect: crash recovery describes recovery after an event rather than the particular page-map mechanism.
Question 47
Which specific DBMS component directly handles undo of incomplete persistent changes after failure to support transaction atomicity? Choose the specialized component rather than its broader parent subsystem.
A. Transaction management component of DBMS
B. Application Programmer
C. Concurrency control component of DBMS
D. Recovery management component of DBMS
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
Correct answer: D — D. Recovery management component of DBMS
Atomicity requires all transaction effects or none. After failure, recovery can undo incomplete persistent changes to enforce that outcome. Transaction management is the broader subsystem containing these responsibilities, but the question asks for the specialized component. Concurrency control primarily addresses isolation, and application code alone cannot provide the DBMS's crash-recovery undo of partially persisted work.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
Undo restores before-images of incomplete effects, usually traversing that transaction's updates backward.ADB27
Write-ahead logging requires the update log needed for recovery to reach stable storage before the corresponding changed data page reaches disk.ADB27
Choice by choice
A · incorrect — This is the parent subsystem, not the specialized failure-undo component requested.
B · incorrect — Application logic alone does not provide durable crash undo.
C · incorrect — Concurrency control primarily enforces isolation.
D · correct — Recovery directly supports atomicity by undoing incomplete effects.
Question 48
If a database server is referenced in a distributed transaction, the value of
its commit point strength determines which role it plays in the
________________.
A. Two-phase commit
B. Transaction locking
C. Two-phase locking
D. Checkpoints
Hint / Module Reference (Where to Read)
Distinguish coordinated transaction outcome from concurrent access and local storage.
ADB39 explicitly links Oracle COMMIT_POINT_STRENGTH to choosing the commit point site in distributed transactions. This is a role in two-phase commit, which coordinates an atomic outcome among participants. It is not a setting for acquiring or releasing locks, nor for checkpoints that bound recovery work. Higher eligible strength is preferred for the commit point.
Evidence in the complete study note
Two-phase commit (2PC) first asks participants to prepare and durably promise they can commit. If all vote yes, the coordinator records commit durably and tells participants; otherwise it decides abort. A prepared participant must retain enough state to obey the final decision. Coordinator/communication failure can leave it in doubt and block progress until the decision is learned. Timeout after preparation is not permission to invent a commit or abort.ADB39
Oracle's COMMIT_POINT_STRENGTH helps choose the commit point site in its distributed transaction mechanism, with higher eligible strength preferred.ADB39
Two-phase locking orders access to data; two-phase commit coordinates a distributed commit decision.ADB12
Choice by choice
A · correct — Commit-point strength selects an Oracle distributed-commit role within the two-phase commit mechanism.
B · incorrect — Transaction locking controls concurrent access rather than choosing the distributed commit point site.
C · incorrect — Two-phase locking governs lock acquisition and release, not the distributed commit decision.
D · incorrect — Checkpoints record recovery state and do not choose an Oracle commit point site.
Question 49
What is the distinctive purpose of query optimization within the broader query-processing pipeline? Select the most specific description.
A. Both are used to process SQL statements to return answers.
B. Both filter retrieved rows by a date/time condition.
C. Both improve the performance of the database by efficiently executing a
query.
D. Both are important in executing queries to access information from the DB.
Hint / Module Reference (Where to Read)
Separate query representation, strategy selection and execution.
Correct answer: C — C. Both improve the performance of the database by efficiently executing a
query.
Query optimization selects a suitable efficient execution strategy among equivalent ways of answering a query. C explicitly states that efficiency objective. Returning answers and accessing information are broader descriptions of processing and do not distinguish optimization's particular purpose. Filtering data by a date or time is a query condition rather than the general goal of choosing a plan.
Evidence in the complete study note
Parsing checks SQL grammar; semantic analysis resolves names, types and access permissions. Translation constructs a logical representation, typically relational algebra or a logical query tree. Optimization chooses equivalent operations, join order and physical access methods. Evaluation executes that plan and returns results. Not every implementation exposes exactly the same phase boundaries.ADB17
Query processing is the whole activity; selecting a suitable efficient strategy is optimization.ADB17
DDL is not a mandatory stage before each SELECT, so authentication→DDL→DML permutations do not describe the processing sequence.ADB17
Choice by choice
A · incorrect — Returning answers describes the broad pipeline but omits the distinctive efficiency objective.
B · incorrect — Temporal filtering is query logic, not the general purpose of optimization.
C · correct — Efficient execution is the specific objective of selecting a suitable plan.
D · incorrect — Accessing information describes the broad pipeline rather than optimization's efficiency role.
Question 50
What is the objective of a Serializable schedule?
A. Find a Non-serial schedule that allows the interleaving of a transaction and
produces the same result as a serial schedule.
B. Find a Serial schedule that allows the interleaving of a transaction and
produces the same result as a serial schedule.
C. Find a Serial schedule that doesn't allow the interleaving of a transaction
and produces the same result as a serial schedule.
D. Find a Non-serial schedule that doesn't allow the interleaving of a
transaction and produces the same result as a serial schedule.
Hint / Module Reference (Where to Read)
Distinguish transaction ordering, conflict conditions and full execution equivalence.
Correct answer: A — Find a Non-serial schedule that allows the interleaving of a transaction and
produces the same result as a serial schedule.
Serial schedules run each transaction consecutively. Serializability permits nonserial interleaving while preserving the effect of a complete serial schedule (p.72). A expresses this concurrency objective.
Evidence in the complete study note
A serial schedule executes each transaction's operations consecutively. A serializable schedule is equivalent to some serial order; it may interleave transactions. Equality of one final value is insufficient evidence because different executions can accidentally coincide. Two operations conflict when they belong to different transactions, access the same item and at least one writes. Read/read pairs do not conflict.ADB09
Choice by choice
A · correct — Interleaving plus serial-equivalent effect meets the objective.
B · incorrect — A serial schedule cannot interleave transactions as claimed.
C · incorrect — This describes serial execution, not the benefit of serializable interleaving.
D · incorrect — Nonserial execution is distinguished by departure from consecutive transaction execution; this denies the intended interleaving.
Question 51
Under the course data-model type-constructor convention, which statements are false? Select all that apply. Interpret type constructor as a data-model term, not an executable Java/C++ object constructor, and consider OID over one object's lifetime.
A. Object Identifier is immutable.
B. Type constructor is used to create a new object.
C. Tuple is a collection of records.
D. The atom constructor is a multi-value constructor.
Hint / Module Reference (Where to Read)
Classify the shape of a value separately from an operation that creates an instance.
Correct answers (select all): B, C, D — B. Type constructor is used to create a new object.; C. Tuple is a collection of records.; D. The atom constructor is a multi-value constructor.
An OID remains stable over the object's lifetime, so A is true. A data-model type constructor specifies the shape of a value rather than executing creation of a new object. A tuple groups named components into one structured record, while an atom is one basic value. B, C and D therefore state incorrect properties under the specified convention.
Evidence in the complete study note
In a common object model, an object is described by (OID,type constructor,state). An atom is one basic value, such as an integer, real, Boolean or string. A tuple constructor groups named component values into one structured record, such as (Street:'A',City:'B'). It is not a collection of records.ADB04
A type constructor defines the shape of a value; it is not the same as an executable constructor operation that creates a new object.ADB04
The object identifier (OID) distinguishes objects independently of their attribute values and remains stable over that object's lifetime.ADB01
Choice by choice
A · incorrect — OID stability over the object lifetime is true, so it does not answer the negative stem.
B · correct — This is false under the note's convention: a type constructor specifies shape rather than executing object creation.
C · correct — A tuple is one structured record of components, not a collection of records.
D · correct — An atom represents one basic atomic value rather than a multivalued collection.
Question 52
Assume transaction A holds a shared lock R. If transaction B also requests
for a shared lock on R
A. It will result in a deadlock situation
B. It will be granted as soon as it is released by A
C. It will immediately be granted
D. It will immediately be rejected
Hint / Module Reference (Where to Read)
Compare access permissions and same-item compatibility before granting a lock request.
Correct answer: C — It will immediately be granted
The shared-mode rule permits more than one transaction to hold a read lock on X, while excluding write locks (p.82). With only A’s shared lock specified, B’s shared request is compatible.
Evidence in the complete study note
A shared (S) lock permits reading; multiple S locks on the same item can coexist. An exclusive (X) lock permits its holder to read and write and conflicts with another S or X on that item. Locks on different items do not conflict merely because one is exclusive. These are the basic lock-based rules; multiversion systems can permit additional snapshot-reader behavior.ADB11
One transaction may request a lock on an already locked item: it is granted if compatible, otherwise waits or aborts under the policy.ADB11
The lock manager decides requests; the lock table stores granted locks and waiting requests.ADB11
Choice by choice
A · incorrect — Two compatible shared requests do not produce the stated deadlock.
B · incorrect — Waiting for A is unnecessary for compatible reads.
C · correct — The shared request is compatible and may be granted immediately under the stated conditions.
D · incorrect — The shared rule supplies no rejection condition here.
Question 53
In the dirty read problem, one transaction reads:
A. an uncommitted value of another transaction
B. the committed value for another transaction
C. another transaction
D. one transaction commits another transaction
Hint / Module Reference (Where to Read)
Trace which transaction wrote each value and whether an overwrite or mixed-version read occurs.
Correct answer: A — an uncommitted value of another transaction
The temporary-update/dirty-read definition involves another transaction accessing an updated item before the failed writer’s value is restored. The hazard is observing the other transaction’s uncommitted value.
Evidence in the complete study note
Lost update: T1 and T2 both read 500; T2 adds 200 and writes 700; T1 adds 100 to its stale value and writes 600. Correct serial execution yields 800. Dirty read: T2 reads a value written by uncommitted T1; if T1 aborts, dependent work must be reconsidered. Reading one's own changes is not a dirty read.ADB08
Choice by choice
A · correct — This identifies the unsafe value from the other transaction.
B · incorrect — A committed value lacks the defining uncommitted-update hazard.
C · incorrect — Transactions read data items; this does not identify the dirty value.
D · incorrect — One transaction committing another is not the read anomaly described.
Question 54
Which sequence matches the course's logical query-processing pipeline for a SELECT, from checking the request to returning its result?
A. Parsing and semantic analysis → logical translation → query optimization → evaluation → output
B. DDL->DML->query optimizer-> Authentication->output
C. DML->query optimizer-> Authentication-> DDL-> output
D. All of the mentioned
Hint / Module Reference (Where to Read)
Separate query representation, strategy selection and execution.
Correct answer: A — A. Parsing and semantic analysis → logical translation → query optimization → evaluation → output
The request is first checked for grammar and meaning, then translated into a logical representation. Optimization selects equivalent operations and physical access methods; evaluation executes that plan and returns the output. Option A follows those roles in order. The other sequences treat DDL and DML language categories as processing stages and place unrelated checks after optimization, so they do not fit.
Evidence in the complete study note
Parsing checks SQL grammar; semantic analysis resolves names, types and access permissions. Translation constructs a logical representation, typically relational algebra or a logical query tree. Optimization chooses equivalent operations, join order and physical access methods. Evaluation executes that plan and returns results. Not every implementation exposes exactly the same phase boundaries.ADB17
DDL is not a mandatory stage before each SELECT, so authentication→DDL→DML permutations do not describe the processing sequence.ADB17
Query processing is the whole activity; selecting a suitable efficient strategy is optimization.ADB17
Choice by choice
A · correct — This sequence follows checking, representation, strategy selection and execution.
B · incorrect — DDL/DML categories do not form these phases, and checking is misplaced.
C · incorrect — DDL is not a mandatory post-optimization phase of SELECT processing.
D · incorrect — Only option A follows the pipeline, so all sequences are not correct.
Question 55
In object-oriented database, the keyword "inverse" is used in which of the following?
A. Relationship
B. All choices are correct
C. Class
D. Attribute
Hint / Module Reference (Where to Read)
Distinguish schema definitions, queries and opposite navigation directions.
An inverse declaration connects two navigation directions of the same association, such as Department.employees and Employee.department. ADB05 explains that it maintains their relationship consistency. It does not declare a class, establish inheritance or name an ordinary scalar attribute. Thus Relationship is the applicable category, and the inclusive option is false.
Evidence in the complete study note
An inverse declaration pairs opposite directions of the same relationship. Conceptually, Department.employees is inverse to Employee.department: when an employee belongs to department D, the corresponding employee appears in D's collection. The declaration expresses relationship consistency; it is not an inheritance keyword or an ordinary scalar attribute. An MCQ calling ODL both a DDL and a language for developing logical schemas offers overlapping true descriptions, so its exact wording matters.ADB05
Choice by choice
A · correct — Inverse pairs the two directions of one relationship and expresses their consistency.
B · incorrect — The other categories are not all valid uses of the inverse declaration.
C · incorrect — Inverse is not the keyword that declares a class or establishes a class hierarchy.
D · incorrect — The declaration links relationship directions rather than declaring an ordinary scalar attribute.
Question 56
Which statements describe ODL? Select all that apply. In A, 'implement' specifically means choosing or carrying out physical storage implementation, rather than expressing a logical definition.
A. A method to implement a logical schema
B. All choices are correct
C. Used to develop logical schemas
D. A data definition language for OODB
Hint / Module Reference (Where to Read)
Distinguish schema definitions, queries and opposite navigation directions.
Correct answers (select all): C, D — C. Used to develop logical schemas; D. A data definition language for OODB
ODL is the schema-definition language for object databases and describes classes or interfaces, attributes, operations and relationships. It is therefore both a data definition language and a language used to develop logical schemas. The question defines implementation in A as physical storage work, which is a different task. Because A is false in that sense, All choices are correct is also false.
Evidence in the complete study note
Object Definition Language (ODL) is the ODMG schema-definition language for object databases; it describes interfaces/classes, attributes, operations and relationships. Object Query Language (OQL) expresses queries. Host-language bindings connect these definitions and operations to languages such as Java or C++. Defining a logical schema is different from implementing its storage.ADB05
Choice by choice
A · incorrect — Physical storage implementation is distinct from ODL logical definition.
B · incorrect — A is false under the specified implementation meaning.
C · correct — ODL develops logical object schemas.
D · correct — ODL is a data definition language for object databases.
Question 57
What are ACID properties of Transactions?
A. Automatically, Concurrency, Isolation, Durability
B. Atomicity, Consistency, Inconsistent, Durability
C. Atomicity, Consistency, Isolation, Database
D. Atomicity, Consistency, Isolation, Durability
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
Correct answer: D — Atomicity, Consistency, Isolation, Durability
Page 66 lists atomicity, consistency preservation, isolation, and durability/permanency. Only D preserves all four terms.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
Choice by choice
A · incorrect — Automatically and concurrency replace atomicity and consistency.
B · incorrect — Inconsistent replaces isolation.
C · incorrect — Database replaces durability.
D · correct — All four names match the listed properties.
Question 58
Which of the following commands is used to save any transaction permanently into the database?
A. Rollback
B. None of the choices are correct
C. Savepoint
D. Commit
Hint / Module Reference (Where to Read)
Distinguish completing a transaction from marking an intermediate recovery position.
COMMIT successfully completes the transaction and requests a durable committed outcome. ROLLBACK instead cancels uncommitted changes. SAVEPOINT marks an intermediate position to which later work can be rolled back, retaining earlier work within the same transaction; it neither completes that transaction nor makes the retained changes independently durable. Because COMMIT is offered, the none-of-the-choices alternative is false. D therefore identifies the permanent transaction-completion command.
Evidence in the complete study note
In SQL transaction-block notation, BEGIN starts a block, COMMIT completes it successfully, and ROLLBACK cancels its uncommitted changes. Committing requests a durable transaction outcome; it is distinct from merely finishing the last read or write operation.ADB-S02
SAVEPOINT marks an intermediate position inside the current transaction. ROLLBACK TO discards changes made after that position while retaining earlier work in the transaction. A savepoint is not an independent durable commit, and rolling back to it does not itself commit the retained changes.ADB-S02
Choice by choice
A · incorrect — Incorrect: ROLLBACK cancels uncommitted work rather than accepting it permanently.
B · incorrect — Incorrect: COMMIT is offered and is the requested completion command.
C · incorrect — Incorrect: SAVEPOINT marks an intermediate rollback position inside the still-open transaction; it does not commit.
D · correct — Correct: COMMIT completes the transaction successfully and requests a durable outcome.
Question 59
Which one of the following is NOT correct about transaction failure?
A. Transactions fail if logical errors are detected
B. Catastrophes can be a cause of transaction failure
C. Concurrency control enforcement avoids transaction failure
D. Transactions fail if disk blocks lose their data
Hint / Module Reference (Where to Read)
Identify what failed, what durable resources survive and which restoration action is required.
Correct answer: C — Concurrency control enforcement avoids transaction failure
The failure list includes logical programming errors, disk blocks losing data, and physical catastrophes. It also explicitly says concurrency enforcement may abort a transaction for serializability or deadlock (p.98). Thus claiming it avoids transaction failure is the incorrect statement.
Evidence in the complete study note
Transaction failures include invalid operations, constraint violations and concurrency-control aborts. Enforcing concurrency can deliberately abort work; it does not prevent every transaction failure. A system crash loses volatile memory while disks may survive. Media failure damages persistent storage and normally requires restoring a backup, then replaying available archived logs. Human mistakes may require point-in-time restoration before the bad committed operation.ADB26
Choice by choice
A · incorrect — Logical errors are explicitly a cause.
B · incorrect — Physical problems and catastrophes are explicitly listed.
C · correct — Concurrency enforcement itself may abort transactions, contradicting the absolute claim.
D · incorrect — Loss of disk-block data is explicitly listed.
Question 60
Which one of the data updates best describes: 'All modified data items in the cache is written
either after transaction ends its execution or after a pre-determined number of transactions have concluded their
execution'?
A. Immediate update
B. In-place update
C. Shadow update
D. Deferred update
Hint / Module Reference (Where to Read)
Separate update timing from update location and follow the commit boundary.
The deferred-update passage on p.110 almost exactly reproduces the stem: modified cache items are written after a transaction ends or a fixed number finish.
Evidence in the complete study note
Deferred update keeps uncommitted changes outside the persistent database; in the usual no-force setting it is NO-UNDO/REDO. Redo-only and NO-UNDO/REDO mean the same thing, so those cannot be separate unique answers. Immediate update allows persistent changes before commit and therefore may require undo; it need not flush every write_item immediately.ADB29
Shadow/copy-on-write update writes a new location while preserving the committed version.ADB29
In-place update overwrites the original location.ADB29
Choice by choice
A · incorrect — Immediate update writes as soon as an item changes in cache.
B · incorrect — In-place describes overwriting the disk version, not the delay policy.
C · incorrect — Shadow update writes a separate disk location.
D · correct — Deferred update has precisely the stated timing.
Question 61
T1 writes X but has not committed. T2 reads that value; T1 then fails and its write is rolled back. Which anomaly did T2 experience?
A. Nonrepeatable-read problem
B. It is dirty read
C. It is an incorrect summary problem
D. It is a lost update problem
Hint / Module Reference (Where to Read)
Trace which transaction wrote each value and whether an overwrite or mixed-version read occurs.
T2 consumes a value written by another transaction before that transaction commits. The writer's later failure makes this a dirty-read dependency requiring reconsideration of dependent work. A nonrepeatable read involves repeated reads seeing a committed change, while an incorrect summary mixes inconsistent versions in an aggregate. Lost update involves a stale overwrite, which this trace does not describe.
Evidence in the complete study note
Lost update: T1 and T2 both read 500; T2 adds 200 and writes 700; T1 adds 100 to its stale value and writes 600. Correct serial execution yields 800. Dirty read: T2 reads a value written by uncommitted T1; if T1 aborts, dependent work must be reconsidered. Reading one's own changes is not a dirty read.ADB08
Choice by choice
A · incorrect — No repeated read observing a committed change is described.
B · correct — T2 reads another transaction's uncommitted write.
C · incorrect — No aggregate combines inconsistent versions.
D · incorrect — No stale value is written over another update.
Question 62
When two transactions that access the same database items have their operations interleaved in
a way that makes the value of some database item incorrect. Which option correctly identifies this?
A. It is a temporary read problem
B. It is a dirty read problem
C. It is an incorrect summary problem
D. It is a lost update problem
Hint / Module Reference (Where to Read)
Trace which transaction wrote each value and whether an overwrite or mixed-version read occurs.
The lost-update definition on p.68 is the wording in the stem. The other anomalies concern reading a temporary failed update or mixing summary values.
Evidence in the complete study note
Lost update: T1 and T2 both read 500; T2 adds 200 and writes 700; T1 adds 100 to its stale value and writes 600. Correct serial execution yields 800. Dirty read: T2 reads a value written by uncommitted T1; if T1 aborts, dependent work must be reconsidered. Reading one's own changes is not a dirty read.ADB08
Choice by choice
A · incorrect — Temporary-update/dirty-read requires access to an uncommitted failed update, not merely the interleaving described.
B · incorrect — Dirty read is the temporary-update anomaly.
C · incorrect — Incorrect summary concerns aggregate evaluation over mixed versions.
D · correct — Lost update matches the stated definition.
Question 63
Which listed policy prevents lock-wait deadlock without comparing transaction-age timestamps? Here 'wait-wait' means allowing every conflicting request to wait with no ordering or cycle restriction.
A. Wait-wait
B. No-wait
C. Wound-wait
D. Wait-die
Hint / Module Reference (Where to Read)
Distinguish a waiting cycle, a long wait and indefinite unfair postponement.
No-wait aborts immediately when a lock request conflicts, so transactions cannot form a lock-wait cycle and no age comparison is needed. Wait–die and wound–wait use relative transaction ages to decide who waits or aborts. Under the explicitly defined wait-wait alternative, every conflicting request may wait without a cycle-prevention restriction, so a transaction can hold one item while waiting for another in a cycle. That alternative therefore fails the deadlock-prevention requirement.
Evidence in the complete study note
Fixed resource ordering and conservative 2PL prevent cycles. A no-wait policy aborts immediately on an incompatible lock request and needs no age timestamp. A timeout aborts long waits but may mistake slow progress for deadlock. Starvation is indefinite postponement while other work proceeds, without a required cycle. Fair queues, sensible victim selection and retaining original transaction timestamps on retry reduce starvation; deadlock freedom alone does not prove fairness.ADB13
A smaller timestamp means an older transaction. In wait–die, an older requester may wait for a younger holder; a younger requester needing an older holder aborts itself. In wound–wait, an older requester aborts the younger holder; a younger requester waits for the older holder. Always identify requester versus holder before applying the names.ADB14
Choice by choice
A · incorrect — Incorrect: unrestricted waiting permits a hold-and-wait cycle, so this defined policy does not prevent deadlock.
B · correct — Correct: immediate abort on a conflicting request prevents lock waits without comparing ages.
C · incorrect — Incorrect: wound–wait compares requester and holder ages to choose waiting or aborting a younger holder.
D · incorrect — Incorrect: wait–die compares ages to choose whether the requester waits or aborts.
Question 64
A database management system (DBMS) has ______ to control locks.
A. Lock manager
B. Lock table
C. Query optimizer
D. Query processor
Hint / Module Reference (Where to Read)
Compare access permissions and same-item compatibility before granting a lock request.
Page 83 distinguishes the manager that manages locks from the table it uses to store lock identities and modes. The controlling component is the lock manager.
Evidence in the complete study note
A transaction must own the appropriate lock before access and can release only its own locks. S→X is an upgrade, possible only after conflicting other holders leave. X→S is a downgrade. The lock manager decides requests; the lock table stores granted locks and waiting requests. One transaction may request a lock on an already locked item: it is granted if compatible, otherwise waits or aborts under the policy. No double-locking is not a prohibition on shared readers.ADB11
Query processing is the whole activity; selecting a suitable efficient strategy is optimization.ADB17
A physical execution plan adds scan/index choices and algorithms.ADB17
Choice by choice
A · correct — This component manages locks.
B · incorrect — The table stores lock information for the manager.
C · incorrect — Optimization selects execution strategies, not lock ownership.
D · incorrect — The named lock-control component is the manager, not query processing.
Question 65
In a columnar database, data is stored:
A. Row-wise
B. In a hierarchical structure
C. Column-wise
D. In a network model
Hint / Module Reference (Where to Read)
Distinguish operational transactions, analytical workloads and physical storage layout.
ADB41 defines columnar storage as grouping values by column, which avoids reading irrelevant columns and can improve compression for scans. Row-oriented storage instead groups each record's fields. Hierarchical and network models are logical representation alternatives rather than the physical column-oriented layout requested by the stem. Columnar layout alone does not determine the logical data model.
Evidence in the complete study note
OLTP handles frequent operational transactions, such as account updates; OLAP scans and summarizes large datasets for analysis. A warehouse integrates data for historical analysis, often through extraction, transformation and loading. These are workload tendencies: OLTP can retain history and warehouses can be updated.ADB41
Columnar storage groups values by column, reducing unnecessary column reads and aiding compression in scans; row-oriented storage groups each record's fields.ADB41
Neither physical layout alone determines whether the logical model is relational.ADB41
Choice by choice
A · incorrect — Row-wise grouping is row-oriented storage, the contrasting physical layout.
B · incorrect — A hierarchy describes parent-child organization, not the columnar physical storage arrangement.
C · correct — Columnar storage groups the values of each column together.
D · incorrect — A network model concerns linked records rather than this column-oriented physical layout.
Question 66
An online game wants extra read copies of player reference data near regional users while retaining the same logical data. Which concept specifically creates those additional copies, rather than dividing the only copy into fragments or choosing a model family?
A. Replication
B. NoSQL databases
C. Distributed databases
D. Partitioning
Hint / Module Reference (Where to Read)
Separate copy placement, propagation timing and update-maintenance cost.
Replication creates additional copies of selected data and can improve read locality by placing them near users. That directly satisfies the specified regional read-copy requirement. Distribution is the broader arrangement across sites, partitioning divides data into fragments, and NoSQL names a family of data models. Neither the broader architecture nor a model label specifically says that extra copies are created.
Evidence in the complete study note
Full replication places the whole database at every relevant site; partial replication copies selected fragments to selected sites; nonredundant allocation stores each fragment once. Copies improve read locality and can improve availability but require storage and update propagation. They can also spread mistakes, so replication does not replace independent backups.ADB37
Partitioning improves locality and parallelism but alone creates no spare copy.ADB36
Flexible schema does not mean no schema rules, and NoSQL does not imply no transactions.ADB42
Choice by choice
A · correct — Replication creates the required regional read copies.
B · incorrect — NoSQL selects a model family and does not itself create copies.
C · incorrect — Distribution is broader than the requested copy-creation policy.
D · incorrect — Partitioning divides data; by itself it creates no spare copy.
Question 67
In a generic DBMS component model applied to a MySQL deployment, which specialized role coordinates restoring valid data from stored backups and logs after data loss? Use generic functional roles, not claims about named MySQL engine internals.
A. Buffer Manager
B. Transaction Manager
C. Query Engine
D. Recovery Manager
Hint / Module Reference (Where to Read)
Identify what failed, what durable resources survive and which restoration action is required.
Recovery is the specialized responsibility for restoring valid database state after failure or loss. Backups supply a separate stored copy, and available logs can restore later recoverable changes. The recovery-manager role therefore matches the question's generic component model. Buffer management caches working pages; transaction management is the broader parent responsibility, while the query engine processes requests. This makes no claim about MySQL's internal module names.
Evidence in the complete study note
Transaction failures include invalid operations, constraint violations and concurrency-control aborts. Enforcing concurrency can deliberately abort work; it does not prevent every transaction failure. A system crash loses volatile memory while disks may survive. Media failure damages persistent storage and normally requires restoring a backup, then replaying available archived logs. Human mistakes may require point-in-time restoration before the bad committed operation.ADB26
Generic recovery-manager terminology should not be mistaken for an exact MySQL component specification.ADB26
A full backup copies the database to separate storage; scheduled backups plus archived logs support restoration to a chosen recoverable point.ADB26
Choice by choice
A · incorrect — Buffers cache working data and are not durable backup copies.
B · incorrect — Transaction management is broader than the specialized restoration role requested.
C · incorrect — A query engine processes queries rather than coordinating restoration.
D · correct — The generic recovery role coordinates restoration using durable recovery resources.
Question 68
Which listed sources can contain unstructured free text, images or audio? Select all that apply. Here Google search collections include free-text web pages; RDBMS systems may store text/BLOB attachments; the Apple Employee and Registrar student examples contain only fixed-field structured records.
A. Google search engine
B. RDBMS systems
C. Apple Employee database
D. Registrar student database
Hint / Module Reference (Where to Read)
Classify the actual content or pattern being analyzed rather than the storage product.
Correct answers (select all): A, B — A. Google search engine; B. RDBMS systems
Unstructured classification depends on the actual content rather than the storage product. Search collections can contain free text and images, and an RDBMS can store text or binary attachments, so A and B qualify under the stated contents. The employee and registrar examples are explicitly restricted to fixed-field structured records, excluding the otherwise possible attachments that would change their classification.
Evidence in the complete study note
Structured data follows explicit fields, such as a registrar's relational tables. Unstructured data includes free text, images and audio; web/search collections can include these alongside structured content. A DBMS can also store text or BLOBs, so a storage product does not prove all its contents are structured.ADB43
Clustering groups similar cases without target labels; classification predicts categories and regression predicts numeric outcomes.ADB43
If 75 of 100 shoe purchases include socks, confidence(shoes⇒socks)=0.75.ADB43
Choice by choice
A · correct — The specified web collection includes unstructured free text.
B · correct — The specified relational store may contain unstructured text/BLOB attachments.
C · incorrect — This example is restricted to structured fixed-field records.
D · incorrect — This example is restricted to structured fixed-field records.
Question 69
Business people always remodel their business by closely studying their customers' purchasing habits over the course of their purchase history.
Accordingly, a salesperson at Markato shop noticed that people who always buy shoes will also buy socks with probability of 0.75 and hence decided
to put shoes and socks side by side for ease of accessibility. Which big data analytics technique could this salesperson could employ?
A. Association rule
B. Clustering algorithm
C. Predictive modeling
D. Regression modeling
Hint / Module Reference (Where to Read)
Classify the actual content or pattern being analyzed rather than the storage product.
Correct answer: A — Association-rule mining: shoes ⇒ socks has confidence 0.75.
The observation is a conditional co-purchase pattern: among shoe purchases, socks also appear with probability 0.75. ADB43 defines this as the confidence of an association rule and explicitly distinguishes co-occurrence mining from clustering, classification and numeric regression. The store-layout decision uses that rule; it does not change the underlying technique or prove causation.
Evidence in the complete study note
An association rule X⇒Y describes co-occurrence. Support=count(X and Y)/N; confidence=count(X and Y)/count(X)=P(Y|X). If 75 of 100 shoe purchases include socks, confidence(shoes⇒socks)=0.75. This does not prove causation or specify support without total transaction count. Clustering groups similar cases without target labels; classification predicts categories and regression predicts numeric outcomes. Purchasing co-occurrence is association mining even when its result informs store layout.ADB43
Choice by choice
A · correct — The rule captures shoe/sock co-occurrence with conditional confidence 0.75.
B · incorrect — Clustering groups similar cases without target labels rather than directly discovering this co-purchase rule.
C · incorrect — The stated evidence is an association rule, not a separately specified predictive modeling task.
D · incorrect — Regression predicts numeric outcomes rather than identifying this purchasing co-occurrence rule.
Question 70
Using this convention, Model Testing means measuring a trained model on held-out test data; Model Evaluation means the subsequent release decision using those results and business success criteria. Which sequence applies before deploying this newly trained model?
A. Model Training, Model Evaluation, Model Testing, Model Deployment
B. Model Training, Model Testing, Model Deployment, Model Evaluation
C. Model Training, Model Testing, Model Evaluation, Model Deployment
D. Model Evaluation, Model Training , Model Testing, Model Deployment
Hint / Module Reference (Where to Read)
Identify the objective and the stated meanings of the workflow stages.
Correct answer: C — C. Model Training, Model Testing, Model Evaluation, Model Deployment
Under the stated convention, training first produces the model. Testing then measures it on held-out data, and evaluation uses those results to decide whether business criteria justify deployment. C follows that sequence. Evaluation during model selection can use a different ordering, but this question explicitly assigns evaluation to the subsequent release review, so the other sequences misplace that review.
Evidence in the complete study note
CRISP-DM starts with business understanding: define the decision and success criteria. Then understand data, prepare it, model, evaluate and deploy; the process is iterative. Cleaning, integration and feature selection occur during preparation, not before the business problem exists.ADB44
If an MCQ asks training→testing→evaluation→deployment, state that convention; another valid workflow can evaluate during model selection before final testing.ADB44
Within modeling, split data appropriately, train on training data, tune using validation and evaluate once on a held-out test set before deployment.ADB44
Choice by choice
A · incorrect — It puts the specified post-test release review before testing.
B · incorrect — It deploys before the required release review.
C · correct — Training, held-out testing, release evaluation and deployment follow the defined roles.
D · incorrect — It evaluates the newly trained model before that model has been trained.
Question 71
Identify the one that comes first in data mining process
A. Business understanding
B. Data integration
C. Data cleaning
D. Data selection
Hint / Module Reference (Where to Read)
Identify the objective and the stated meanings of the workflow stages.
ADB44 starts the data-mining workflow by defining the business decision and its success criteria. This establishes why data will be collected and modeled. Integration, cleaning and feature selection prepare data after the problem has been understood; they do not replace the initial business-understanding stage. Thus A precedes the other listed activities.
Evidence in the complete study note
CRISP-DM starts with business understanding: define the decision and success criteria. Then understand data, prepare it, model, evaluate and deploy; the process is iterative. Cleaning, integration and feature selection occur during preparation, not before the business problem exists.ADB44
Deployment includes monitoring and may trigger another preparation/modeling cycle.ADB44
If an MCQ asks training→testing→evaluation→deployment, state that convention; another valid workflow can evaluate during model selection before final testing.ADB44
Choice by choice
A · correct — Business understanding first defines the decision to support and the criteria for success.
B · incorrect — Data integration prepares sources after the business problem has been identified.
C · incorrect — Cleaning improves data quality during preparation rather than defining the initial business objective.
D · incorrect — Selecting relevant data depends on an already understood business problem and modeling objective.
Question 72
What is the defining purpose of a transaction in database management?
A. To secure the database from unauthorized access
B. To optimize query performance
C. To group database operations into one logical unit of work
D. To define the structure and organization of a database
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
Correct answer: C — C. To group database operations into one logical unit of work
A transaction groups database operations into one logical unit of work. The DBMS supports its atomicity, consistency, isolation and durability requirements, including controlled interference with concurrent transactions. Grouping is the defining concept, rather than query tuning, access authorization or schema construction. Read-only work can also form a transaction, so the definition is not limited to operations that change stored data.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
A transaction must own the appropriate lock before access and can release only its own locks.ADB11
One transaction may request a lock on an already locked item: it is granted if compatible, otherwise waits or aborts under the policy.ADB11
Choice by choice
A · incorrect — Authorization governs who may access data rather than defining transaction grouping.
B · incorrect — Optimization chooses query execution strategies.
C · correct — A transaction is one logical unit of database work.
D · incorrect — Schema definition describes data structures rather than a unit of work.
Question 73
A correctly implemented financial transaction must preserve a declared total-balance invariant when it completes. Which ACID property names preservation of such integrity rules? Focus on the invariant rather than partial execution, concurrent interference or survival after a crash.
A. Atomicity
B. Consistency
C. Isolation
D. Durability
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
Consistency means that correct transactional work preserves declared integrity rules. Maintaining the total-balance invariant is therefore the consistency requirement. Atomicity addresses whether the effects happen together, isolation controls interference among concurrent transactions and durability preserves committed effects after failure. These properties cooperate, but the revised question identifies invariant preservation as the particular guarantee to name.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
Choice by choice
A · incorrect — Atomicity names all-or-none effects rather than invariant preservation itself.
B · correct — Consistency names preservation of declared integrity rules by correct work.
C · incorrect — Isolation controls concurrent interference.
D · incorrect — Durability preserves committed effects after failure.
Question 74
In the object-oriented model, an object is defined as:
A. An instance of a class
B. A collection of attributes
C. A primary key in a database
D. A relationship between tables
Hint / Module Reference (Where to Read)
Distinguish an object instance from its class, attribute values and persistent identity.
ADB01 explicitly distinguishes a class specification from each object instantiated from it. A class defines available attributes and operations, while an object is one individual instance with identity, state and behavior. A collection of attributes omits identity and behavior; a key or an intertable relationship belongs to a different relational description.
Evidence in the complete study note
An object combines identity, state and behavior. A class specifies the attributes and operations of its instances; an object is one instance. The object identifier (OID) distinguishes objects independently of their attribute values and remains stable over that object's lifetime. Two objects can have equal values and different identities; changing a name does not create a new identity.ADB01
Complex CAD or multimedia structures can suit an object model, but many-to-many relationships alone do not require abandoning relational design.ADB01
An object-relational DBMS extends a relational system with richer types; ORM maps programming objects into relational tables.ADB01
Choice by choice
A · correct — An object is an individual instance of a class with its own identity and state.
B · incorrect — Attributes describe state but omit the identity and behavior that also characterize an object.
C · incorrect — A database primary key is an identifier constraint, not the definition of an object instance.
D · incorrect — A relationship between tables is a relational association rather than an object instance.
Question 75
In the object-oriented model, encapsulation refers to:
A. Combining multiple classes into a single entity
B. Hiding the internal details of an object
C. Establishing relationships between objects
D. Defining the inheritance hierarchy
Hint / Module Reference (Where to Read)
Separate the object boundary, the exposed contract and links between objects.
Correct answer: B — Hiding and controlling access to internal object details.
ADB02 describes encapsulation as grouping state with operations and controlling access to implementation details. A withdrawal method can enforce rules while hiding storage. B states the relevant information-hiding aspect. Combining classes, associating objects and defining subtype hierarchies are distinct activities; none describes the encapsulation boundary around an object's state and operations.
Evidence in the complete study note
Encapsulation groups state with operations and controls access to implementation details. Account.withdraw(amount) can enforce a balance rule while hiding its storage. Abstraction presents the relevant contract—what withdrawal does—without exposing how it is implemented. These ideas overlap in information hiding, but combining state and methods specifically describes encapsulation.ADB02
A class defines structure and behavior; an attribute describes state; a method implements an operation.ADB02
It is different from inheritance, which means an is-a relationship between types.ADB02
Choice by choice
A · incorrect — Encapsulation groups an object's state and operations, not multiple classes into one entity.
B · correct — Encapsulation controls access to the internal representation behind the object's operations.
C · incorrect — Links between objects are associations rather than encapsulation itself.
D · incorrect — Superclass/subclass structure concerns inheritance rather than information-hiding boundaries.
Question 76
In the object-oriented model, inheritance allows:
A. Objects to have multiple parents
B. Objects to inherit attributes and behaviors from a superclass
C. Objects to be created dynamically at runtime
D. Objects to establish relationships with other objects
Hint / Module Reference (Where to Read)
Distinguish the subtype relationship from use through a common interface.
Correct answer: B — Inherit applicable attributes and behavior from a superclass.
ADB03 defines inheritance as deriving a subtype from a superclass and reusing its applicable attributes and behavior. B captures that idea. It does not inherently require multiple parents, dynamically create objects, or merely link unrelated objects. Those alternatives concern particular inheritance forms, instantiation or association rather than the general meaning of inheritance.
Evidence in the complete study note
Inheritance derives a subtype from a superclass, reusing applicable attributes and behavior. GraduateStudent is a Student; association instead says Student belongs to Department. EER teaches inherited attributes and relationships, while a programming object model also specifies behavior.ADB03
Choice by choice
A · incorrect — Multiple parents are not a general requirement or guarantee of inheritance.
B · correct — A subtype inherits applicable superclass attributes and behavior.
C · incorrect — Dynamic object creation is instantiation, not the capability defined by inheritance.
D · incorrect — Relationships between separate objects are associations rather than the is-a subtype relationship.
Question 77
Which concept in the object-oriented model allows different classes to share the same
method name but with different implementations?
A. Polymorphism
B. Abstraction
C. Encapsulation
D. Inheritance
Hint / Module Reference (Where to Read)
Distinguish the subtype relationship from use through a common interface.
ADB03 illustrates Shape.area dispatching to Circle.area or Rectangle.area according to the actual object. The common operation name has different compatible implementations, which is polymorphism. Inheritance can provide the subtype structure used by that mechanism, but is not itself the runtime dispatch behavior. Abstraction and encapsulation concern contracts and implementation access instead.
Evidence in the complete study note
Subtype polymorphism lets a superclass/interface reference denote objects of different compatible subclasses. The same operation can dispatch to different implementations: Shape.area() calls Circle.area() or Rectangle.area() according to the actual object. This is not arbitrary conversion of an object into an unrelated class, nor dynamic class mutation. Inheritance establishes a subtype relationship; polymorphism uses that relationship through a common interface. Distinguish inheritance as a structural relationship from polymorphism as a use of that relationship at runtime.ADB03
Choice by choice
A · correct — Polymorphism lets the same interface operation invoke different implementations for compatible object types.
B · incorrect — Abstraction exposes a relevant contract rather than selecting among differing method implementations.
C · incorrect — Encapsulation controls internal access rather than providing type-dependent method dispatch.
D · incorrect — Inheritance supplies a structural subtype relation; polymorphism describes the differing implementations through a common interface.
Question 78
The process of combining data and methods into a single entity is known as:
A. Inheritance
B. Polymorphism
C. Abstraction
D. Encapsulation
Hint / Module Reference (Where to Read)
Separate the object boundary, the exposed contract and links between objects.
The question specifically asks about placing data and methods inside one entity. ADB02 identifies that grouping as encapsulation, along with controlled access to implementation details. Abstraction concerns what the contract exposes; inheritance derives compatible subtypes; polymorphism invokes different implementations through a common interface. None of those replaces the state-and-method grouping named in the stem.
Evidence in the complete study note
Encapsulation groups state with operations and controls access to implementation details. Account.withdraw(amount) can enforce a balance rule while hiding its storage. Abstraction presents the relevant contract—what withdrawal does—without exposing how it is implemented. These ideas overlap in information hiding, but combining state and methods specifically describes encapsulation.ADB02
It is different from inheritance, which means an is-a relationship between types.ADB02
A class defines structure and behavior; an attribute describes state; a method implements an operation.ADB02
Choice by choice
A · incorrect — Inheritance derives a subtype and reuses superclass features rather than naming state/method grouping.
B · incorrect — Polymorphism varies implementations behind a compatible common interface rather than grouping data and methods.
C · incorrect — Abstraction presents a relevant contract; the specific grouping asked for is encapsulation.
D · correct — Encapsulation packages state and operations together within the object boundary.
Question 79
The object-oriented model supports the concept of association, which represents:
A. Inheritance relationships between classes
B. Shared attributes among objects
C. The interactions between objects
D. Data duplication in the database
Hint / Module Reference (Where to Read)
Separate the object boundary, the exposed contract and links between objects.
Correct answer: C — Links and interactions between objects.
ADB02 defines association as links between objects, commonly implemented with references, and describes methods invoking operations on other objects. C is the closest description of that collaboration. An association is not an is-a inheritance hierarchy, shared attribute definitions or duplication. For example, Student belonging to Department links objects without making either a subtype of the other.
Evidence in the complete study note
An association links objects, often through references; Department.employees can reference Employee objects. It is different from inheritance, which means an is-a relationship between types. Methods can invoke operations on other objects, supporting collaboration. A class defines structure and behavior; an attribute describes state; a method implements an operation. Do not treat each of these as an interchangeable name for the whole object.ADB02
Choice by choice
A · incorrect — Inheritance is an is-a relationship between types, distinct from association links between objects.
B · incorrect — Having similar attributes does not itself define an association between particular objects.
C · correct — Associations link objects and support their interactions or collaboration through references.
D · incorrect — Association represents relationships rather than duplicated copies of the same database facts.
Question 80
Circle and Rectangle already inherit from Shape. A Shape reference can hold either object and dispatch area() to its actual implementation at runtime. Which concept names this common-interface use, rather than the structural subtype relationship?
A. Polymorphism
B. Inheritance
C. Abstraction
D. Encapsulation
Hint / Module Reference (Where to Read)
Distinguish the subtype relationship from use through a common interface.
Subtype polymorphism allows a common superclass or interface reference to denote different compatible subclass objects. Dispatching area() according to whether the actual object is a Circle or Rectangle is that runtime use. Inheritance establishes the subtype relationship already given in the stem. Abstraction exposes a contract and encapsulation controls representation, but neither names this particular substitutable runtime dispatch.
Evidence in the complete study note
Subtype polymorphism lets a superclass/interface reference denote objects of different compatible subclasses. The same operation can dispatch to different implementations: Shape.area() calls Circle.area() or Rectangle.area() according to the actual object. This is not arbitrary conversion of an object into an unrelated class, nor dynamic class mutation. Inheritance establishes a subtype relationship; polymorphism uses that relationship through a common interface. Distinguish inheritance as a structural relationship from polymorphism as a use of that relationship at runtime.ADB03
Encapsulation groups state with operations and controls access to implementation details.ADB02
These ideas overlap in information hiding, but combining state and methods specifically describes encapsulation.ADB02
Choice by choice
A · correct — Using one superclass interface with subclass-specific dispatch is subtype polymorphism.
B · incorrect — Inheritance is the established structural prerequisite, not the runtime use requested.
C · incorrect — Abstraction describes a contract rather than the dispatch behavior.
D · incorrect — Encapsulation controls internal representation and access.
Question 81
What is the purpose of abstraction in the object-oriented model?
A. To combine multiple classes into a single entity
B. To hide the internal details of an object
C. To establish relationships between objects
D. To define the attributes and behaviors of a class
Hint / Module Reference (Where to Read)
Separate the object boundary, the exposed contract and links between objects.
Correct answer: B — Hide implementation details while exposing a relevant contract.
ADB02 explains abstraction through an operation's relevant contract: what withdrawal does is exposed without showing how storage implements it. B captures hiding those irrelevant internal details. This overlaps encapsulation, but abstraction emphasizes the view or contract. Combining classes, creating associations or listing class members is not its defining purpose.
Evidence in the complete study note
Encapsulation groups state with operations and controls access to implementation details. Account.withdraw(amount) can enforce a balance rule while hiding its storage. Abstraction presents the relevant contract—what withdrawal does—without exposing how it is implemented. These ideas overlap in information hiding, but combining state and methods specifically describes encapsulation.ADB02
EER teaches inherited attributes and relationships, while a programming object model also specifies behavior.ADB03
An association links objects, often through references; Department.employees can reference Employee objects.ADB02
Choice by choice
A · incorrect — Abstraction does not mean merging several classes into one entity.
B · correct — Abstraction exposes relevant behavior while hiding implementation details from the client view.
C · incorrect — Establishing links between objects is association rather than abstraction's defining purpose.
D · incorrect — A class specifies attributes and behavior; abstraction chooses the relevant contract exposed to users.
Question 82
The concept of polymorphism allows objects to:
A. Inherit attributes from multiple classes
B. Change their class dynamically at runtime
C. Access private methods of other objects
D. Be treated as objects of different classes
Hint / Module Reference (Where to Read)
Distinguish the subtype relationship from use through a common interface.
Correct answer: D — Use an object through a compatible superclass or interface type.
ADB03 allows a superclass or interface reference to denote compatible subclass objects. D is correct in that sense: the same object can be treated through its actual type and a compatible supertype. This does not permit arbitrary conversion to unrelated classes. Multiple inheritance, runtime class mutation and access to another object's private methods are different concepts and are not granted by polymorphism.
Evidence in the complete study note
Subtype polymorphism lets a superclass/interface reference denote objects of different compatible subclasses. The same operation can dispatch to different implementations: Shape.area() calls Circle.area() or Rectangle.area() according to the actual object. This is not arbitrary conversion of an object into an unrelated class, nor dynamic class mutation. Inheritance establishes a subtype relationship; polymorphism uses that relationship through a common interface. Distinguish inheritance as a structural relationship from polymorphism as a use of that relationship at runtime.ADB03
Choice by choice
A · incorrect — Inheriting from multiple classes is multiple inheritance, not the operation described by polymorphism.
B · incorrect — Polymorphism does not change the actual class of an object dynamically.
C · incorrect — Polymorphism does not bypass private-access restrictions on other objects.
D · correct — An object can be used through compatible superclass/interface types; this does not mean arbitrary unrelated classes.
Question 83
Which concept in the object-oriented model defines the structure and behavior of objects?
A. Class
B. Object
C. Attribute
D. Method
Hint / Module Reference (Where to Read)
Distinguish an object instance from its class, attribute values and persistent identity.
A class is the specification that defines attributes and operations for its instances, as ADB01 states. An object is an instance conforming to that specification. An attribute accounts for part of state and a method for one operation, but neither individually defines the whole structure and behavior of the object type.
Evidence in the complete study note
An object combines identity, state and behavior. A class specifies the attributes and operations of its instances; an object is one instance. The object identifier (OID) distinguishes objects independently of their attribute values and remains stable over that object's lifetime. Two objects can have equal values and different identities; changing a name does not create a new identity.ADB01
Complex CAD or multimedia structures can suit an object model, but many-to-many relationships alone do not require abandoning relational design.ADB01
An object-relational DBMS extends a relational system with richer types; ORM maps programming objects into relational tables.ADB01
Choice by choice
A · correct — A class specifies the structure and available operations for its object instances.
B · incorrect — An object is one instance of the specification rather than the specification itself.
C · incorrect — An attribute represents part of an object's state rather than its whole structure and behavior.
D · incorrect — A method implements an operation but does not alone define the complete object type.
Question 84
Which recovery method aims to restore the database to a previous consistent state by
undoing the effects of incomplete or failed transactions?
A. Rollback recovery
B. Redo recovery
C. Checkpoint recovery
D. Restart recovery
Hint / Module Reference (Where to Read)
Track durable-write ordering and distinguish reversing incomplete work from installing logged effects.
Undo restores before images and removes after images; failed transactions that updated the database must roll back (p.107). This directly answers the specific action, although undo may be one phase of broader crash recovery.
Evidence in the complete study note
Undo restores before-images of incomplete effects, usually traversing that transaction's updates backward. Redo installs logged after-images when necessary, usually forward. Setting X to its logged after-value is idempotent; blindly replaying X=X+10 twice is not. In a simple undo/redo teaching model, undo losers and redo winners. ARIES has a different redo phase that repeats history before undoing losers.ADB27
Restart recovery restores a valid database after a crash while preserving durable commits and removing incomplete effects.ADB26
Recovery may start analysis at the checkpoint but redo from an earlier required log position.ADB30
Choice by choice
A · correct — Rollback is the direct undo operation.
B · incorrect — Redo restores after images instead.
C · incorrect — Checkpoints synchronize database and log; they do not name the undo action.
D · incorrect — A restart can include undo, but is broader than the requested failed-transaction reversal.
Question 85
Which recovery method involves reapplying the changes made by committed transactions
from a log file to restore the database?
A. Redo recovery
B. Checkpoint recovery
C. Restart recovery
D. Backup recovery
Hint / Module Reference (Where to Read)
Track durable-write ordering and distinguish reversing incomplete work from installing logged effects.
If a transaction committed but its buffers did not reach disk, recovery redoes its updates for durability (p.103). Redo restores after images (p.107), whose log records are required before commit (p.104).
Evidence in the complete study note
Undo restores before-images of incomplete effects, usually traversing that transaction's updates backward. Redo installs logged after-images when necessary, usually forward. Setting X to its logged after-value is idempotent; blindly replaying X=X+10 twice is not. In a simple undo/redo teaching model, undo losers and redo winners. ARIES has a different redo phase that repeats history before undoing losers.ADB27
Recovery may start analysis at the checkpoint but redo from an earlier required log position.ADB30
A checkpoint is neither a full backup nor an automatic commit of active transactions.ADB30
Choice by choice
A · correct — Redo is exactly reapplying logged committed updates.
B · incorrect — A checkpoint reduces the amount of recovery work; it is not the replay operation.
C · incorrect — Restart recovery can invoke redo but names a larger process.
D · incorrect — A backup restores a stored database copy, not this specific logged replay.
Question 86
Which recovery method involves creating checkpoints at regular intervals to save the current
state of the database and transaction logs?
A. Checkpoint recovery
B. Redo recovery
C. Rollback recovery
D. Restart recovery
Hint / Module Reference (Where to Read)
Identify which history a recovery boundary can safely exclude and whether active work spans it.
Checkpointing synchronizes database and log (p.105); its listed steps force modified buffers to disk, write a checkpoint record, and save the log (p.107). This is not necessarily a full database backup.
Evidence in the complete study note
A checkpoint records enough durable recovery state to avoid scanning all old history. In a simple quiescent/checkpoint exercise, specified flushing may make earlier committed work unnecessary to redo. Active transactions can span the checkpoint, so their relevant earlier updates can still need undo. Do not assume every transaction before a checkpoint needs no processing.ADB30
To acknowledge a durable commit, force the transaction's required log records, including its commit record, to stable storage.ADB27
Write-ahead logging requires the update log needed for recovery to reach stable storage before the corresponding changed data page reaches disk.ADB27
Choice by choice
A · correct — The named synchronization procedure matches.
B · incorrect — Redo reapplies after images, not periodic synchronization.
C · incorrect — Rollback restores before images.
D · incorrect — Restart follows failure, whereas checkpoints are established during operation.
Question 87
Which recovery method involves restarting the system and recovering the database by
applying the necessary redo and undo operations?
A. Restart recovery
B. Checkpoint recovery
C. Redo recovery
D. Rollback recovery
Hint / Module Reference (Where to Read)
Identify what failed, what durable resources survive and which restoration action is required.
Recovery from a non-catastrophic crash may combine undo and redo (pp.103,107). A is the best descriptive name for restarting and performing that combined recovery, although the module does not define four mutually exclusive method classes under these exact option names.
Evidence in the complete study note
Transaction failures include invalid operations, constraint violations and concurrency-control aborts. Enforcing concurrency can deliberately abort work; it does not prevent every transaction failure. A system crash loses volatile memory while disks may survive. Media failure damages persistent storage and normally requires restoring a backup, then replaying available archived logs. Human mistakes may require point-in-time restoration before the bad committed operation.ADB26
These are cooperating mechanisms, not four mutually exclusive complete alternatives.ADB26
Redo installs logged after-images when necessary, usually forward.ADB27
Choice by choice
A · correct — Restart recovery describes the complete post-crash recovery process stated in the stem.
B · incorrect — Checkpointing supplies a saved starting boundary rather than the whole restart process.
C · incorrect — Redo supplies only the forward part of the stated combination.
D · incorrect — Rollback supplies only the undo part.
Question 88
Which recovery method involves restoring the database from a backup copy taken prior to
the failure?
A. Backup recovery
B. Rollback recovery
C. Redo recovery
D. Restart recovery
Hint / Module Reference (Where to Read)
Identify what failed, what durable resources survive and which restoration action is required.
For catastrophic damage the module restores a past database copy from backup storage and reconstructs committed operations from the backup log (p.103). A names restoration of that backup copy; a backup alone may need subsequent log replay.
Evidence in the complete study note
Transaction failures include invalid operations, constraint violations and concurrency-control aborts. Enforcing concurrency can deliberately abort work; it does not prevent every transaction failure. A system crash loses volatile memory while disks may survive. Media failure damages persistent storage and normally requires restoring a backup, then replaying available archived logs. Human mistakes may require point-in-time restoration before the bad committed operation.ADB26
Rollback handles failed/uncommitted work; redo preserves committed work missing from pages; checkpoints reduce log-search work; backups provide another stored copy.ADB26
Redo installs logged after-images when necessary, usually forward.ADB27
Choice by choice
A · correct — Restoration from a stored copy is the stated backup operation.
B · incorrect — Rollback reverses updates using before images, not a backup copy.
C · incorrect — Redo replays logged changes, which may supplement backup restoration.
D · incorrect — Restart names an overall recovery process rather than its backup-copy source.
Question 89
Which recovery method is used to recover the database after a media failure, such as a disk
crash?
A. Backup recovery
B. Redo recovery
C. Checkpoint recovery
D. Rollback recovery
Hint / Module Reference (Where to Read)
Identify what failed, what durable resources survive and which restoration action is required.
Disk-head crash is media failure (p.97). Where that destroys database contents, the catastrophic-recovery strategy restores a past backup and then reconstructs committed operations from the backup log (p.103).
Evidence in the complete study note
Transaction failures include invalid operations, constraint violations and concurrency-control aborts. Enforcing concurrency can deliberately abort work; it does not prevent every transaction failure. A system crash loses volatile memory while disks may survive. Media failure damages persistent storage and normally requires restoring a backup, then replaying available archived logs. Human mistakes may require point-in-time restoration before the bad committed operation.ADB26
Recovery may start analysis at the checkpoint but redo from an earlier required log position.ADB30
Restart recovery restores a valid database after a crash while preserving durable commits and removing incomplete effects.ADB26
Choice by choice
A · correct — A backup supplies contents lost in the media failure.
B · incorrect — Redo can advance a restored copy but alone assumes data pages available to update.
C · incorrect — A checkpoint is not a replacement copy of a destroyed database.
D · incorrect — Undo cannot recreate arbitrary destroyed pages by reversing incomplete transactions alone.
Question 90
A crash-recovery system has a long log history and wants to reduce the log-search work compared with scanning all history. Which method adds durable recovery-state records for that purpose?
A. Redo recovery
B. Checkpoint recovery
C. Backup recovery
D. Rollback recovery
Hint / Module Reference (Where to Read)
Identify which history a recovery boundary can safely exclude and whether active work spans it.
Checkpoints record durable recovery state so recovery need not inspect all old history. That is the requested mechanism for reducing log-search work. Redo reinstalls missing changes, rollback removes incomplete effects and backup recovery starts from a separate copy. They may cooperate with checkpoints, but their defining actions do not name the added recovery-state boundary specified in this question.
Evidence in the complete study note
A checkpoint records enough durable recovery state to avoid scanning all old history. In a simple quiescent/checkpoint exercise, specified flushing may make earlier committed work unnecessary to redo. Active transactions can span the checkpoint, so their relevant earlier updates can still need undo. Do not assume every transaction before a checkpoint needs no processing.ADB30
Rollback handles failed/uncommitted work; redo preserves committed work missing from pages; checkpoints reduce log-search work; backups provide another stored copy.ADB26
Restart recovery restores a valid database after a crash while preserving durable commits and removing incomplete effects.ADB26
Choice by choice
A · incorrect — Redo applies logged after-images rather than defining the recovery-state boundary.
B · correct — Checkpoint information reduces how much old history must be searched.
C · incorrect — A backup is a separate stored copy rather than the stated history-bounding record.
D · incorrect — Rollback reverses incomplete effects rather than adding this recovery-state boundary.
Question 91
Which recovery method is focused on ensuring that all committed changes are durable and
can survive system failures?
A. Redo recovery
B. Restart recovery
C. Rollback recovery
D. Checkpoint recovery
Hint / Module Reference (Where to Read)
Track durable-write ordering and distinguish reversing incomplete work from installing logged effects.
Durability requires committed effects to survive crashes (p.97). The module specifically redoes committed updates lost between commit and buffer flushing (p.103), making redo the direct mechanism asked for.
Evidence in the complete study note
Undo restores before-images of incomplete effects, usually traversing that transaction's updates backward. Redo installs logged after-images when necessary, usually forward. Setting X to its logged after-value is idempotent; blindly replaying X=X+10 twice is not. In a simple undo/redo teaching model, undo losers and redo winners. ARIES has a different redo phase that repeats history before undoing losers.ADB27
Rollback handles failed/uncommitted work; redo preserves committed work missing from pages; checkpoints reduce log-search work; backups provide another stored copy.ADB26
Restart recovery restores a valid database after a crash while preserving durable commits and removing incomplete effects.ADB26
Choice by choice
A · correct — Redo reapplies the missing committed updates.
B · incorrect — Restart is a broader process that can invoke redo and undo.
C · incorrect — Rollback preserves atomicity by removing uncommitted effects.
D · incorrect — Checkpoints reduce replay work but are not the committed-update replay operation.
Question 92
Which recovery method uses the transaction log to identify the changes made by
transactions and undo the effects of incomplete or failed transactions?
A. Rollback recovery
B. Redo recovery
C. Checkpoint recovery
D. Restart recovery
Hint / Module Reference (Where to Read)
Track durable-write ordering and distinguish reversing incomplete work from installing logged effects.
Before images logged under WAL permit undo of failed transaction effects (p.104); rollback restores them (p.107). The stem specifies this particular operation rather than all crash-recovery phases.
Evidence in the complete study note
Undo restores before-images of incomplete effects, usually traversing that transaction's updates backward. Redo installs logged after-images when necessary, usually forward. Setting X to its logged after-value is idempotent; blindly replaying X=X+10 twice is not. In a simple undo/redo teaching model, undo losers and redo winners. ARIES has a different redo phase that repeats history before undoing losers.ADB27
Restart recovery restores a valid database after a crash while preserving durable commits and removing incomplete effects.ADB26
Recovery may start analysis at the checkpoint but redo from an earlier required log position.ADB30
Choice by choice
A · correct — Rollback is the direct log-based undo operation.
B · incorrect — Redo restores new values instead of removing failed updates.
C · incorrect — Checkpointing records a synchronization boundary.
D · incorrect — Restart can include rollback, but is broader than the named reversal.
Question 93
Which recovery method is used to recover the database after a system crash or power
failure?
A. Restart recovery
B. Backup recovery
C. Redo recovery
D. Checkpoint recovery
Hint / Module Reference (Where to Read)
Identify what failed, what durable resources survive and which restoration action is required.
For a recoverable system crash, recovery uses undo/redo to restore consistency (pp.103,107). A is the broad descriptive answer for restarting the failed system. If power failure also destroys media, backup restoration may additionally be required; the question supplies no such destruction.
Evidence in the complete study note
Transaction failures include invalid operations, constraint violations and concurrency-control aborts. Enforcing concurrency can deliberately abort work; it does not prevent every transaction failure. A system crash loses volatile memory while disks may survive. Media failure damages persistent storage and normally requires restoring a backup, then replaying available archived logs. Human mistakes may require point-in-time restoration before the bad committed operation.ADB26
Recovery may start analysis at the checkpoint but redo from an earlier required log position.ADB30
Restart recovery restores a valid database after a crash while preserving durable commits and removing incomplete effects.ADB26
Choice by choice
A · correct — Restart recovery denotes the overall post-crash process.
B · incorrect — Backup is required for damaged/lost storage, which is not specified here.
C · incorrect — Redo alone may be insufficient when incomplete transactions reached disk.
D · incorrect — A checkpoint assists recovery but does not itself execute the full crash recovery.
Question 94
What is the primary goal of database recovery after a failure?
A. Restoring the database to a previous state
B. Preventing future failures from occurring
C. Optimizing database performance
D. Enforcing data integrity constraints
Hint / Module Reference (Where to Read)
Identify what failed, what durable resources survive and which restoration action is required.
Correct answer: A — Restoring the database to a previous state
The explicit purpose is bringing the database into its last consistent state before failure (p.97). A is correct only with this consistency qualification; restoring an arbitrary earlier corrupt state is not the goal.
Evidence in the complete study note
Transaction failures include invalid operations, constraint violations and concurrency-control aborts. Enforcing concurrency can deliberately abort work; it does not prevent every transaction failure. A system crash loses volatile memory while disks may survive. Media failure damages persistent storage and normally requires restoring a backup, then replaying available archived logs. Human mistakes may require point-in-time restoration before the bad committed operation.ADB26
Restart recovery restores a valid database after a crash while preserving durable commits and removing incomplete effects.ADB26
Generic recovery-manager terminology should not be mistaken for an exact MySQL component specification.ADB26
Choice by choice
A · correct — Read previous state as the last consistent state, as stated.
B · incorrect — Recovery handles failures rather than preventing all future ones.
C · incorrect — Performance is not the stated recovery purpose.
D · incorrect — Integrity is relevant, but constraint enforcement is not the direct restoration objective stated here.
Question 95
Which recovery method involves periodically making a copy of the entire database onto a
separate storage medium?
A. Rollback recovery
B. Checkpoint recovery
C. Restart recovery
D. Backup recovery
Hint / Module Reference (Where to Read)
Identify what failed, what durable resources survive and which restoration action is required.
A full backup copies the database to separate storage so it can later be restored, often followed by replay of archived logs. Rollback reverses failed transaction work; restart recovery restores a valid state after a crash. A checkpoint records recovery state to reduce log-search and recovery work and is not a full independent database copy. The question's periodic separate copy is therefore the backup mechanism, represented by backup recovery.
Evidence in the complete study note
A full backup copies the database to separate storage; scheduled backups plus archived logs support restoration to a chosen recoverable point.ADB26
Rollback handles failed/uncommitted work; redo preserves committed work missing from pages; checkpoints reduce log-search work; backups provide another stored copy.ADB26
A checkpoint is neither a full backup nor an automatic commit of active transactions.ADB30
Choice by choice
A · incorrect — Incorrect: rollback reverses transaction effects rather than making an independent database copy.
B · incorrect — Incorrect: checkpointing records recovery state and limits recovery work; it is not a full backup.
C · incorrect — Incorrect: restart recovery is the crash-recovery process, not the periodic creation of a separate copy.
D · correct — Correct: backup recovery relies on a stored independent database copy.
Question 96
Which recovery method involves undoing the effects of incomplete or failed transactions?
A. Rollback recovery
B. Checkpoint recovery
C. Restart recovery
D. Backup recovery
Hint / Module Reference (Where to Read)
Track durable-write ordering and distinguish reversing incomplete work from installing logged effects.
The module calls undoing a failed transaction rollback and specifies restoring before images (p.107). A therefore directly names removal of incomplete effects.
Evidence in the complete study note
Undo restores before-images of incomplete effects, usually traversing that transaction's updates backward. Redo installs logged after-images when necessary, usually forward. Setting X to its logged after-value is idempotent; blindly replaying X=X+10 twice is not. In a simple undo/redo teaching model, undo losers and redo winners. ARIES has a different redo phase that repeats history before undoing losers.ADB27
A checkpoint is neither a full backup nor an automatic commit of active transactions.ADB30
Restart recovery restores a valid database after a crash while preserving durable commits and removing incomplete effects.ADB26
Choice by choice
A · correct — Rollback is the named undo operation.
B · incorrect — Checkpoint is synchronization, not transaction reversal.
C · incorrect — Restart can include undo but is the wider process.
D · incorrect — Backup restores a past copy instead of directly reversing this transaction.
Question 97
What is the purpose of a checkpoint in the recovery process?
A. To save the current state of the database
B. To recover from a system crash
C. To log all transaction operations
D. To enforce data integrity constraints
Hint / Module Reference (Where to Read)
Identify which history a recovery boundary can safely exclude and whether active work spans it.
Correct answer: A — To save the current state of the database
A checkpoint synchronizes database and log by forcing modified buffers and recording the checkpoint (pp.105,107), saving a durable recovery boundary and reducing later work. It is not necessarily a full snapshot copy.
Evidence in the complete study note
A checkpoint records enough durable recovery state to avoid scanning all old history. In a simple quiescent/checkpoint exercise, specified flushing may make earlier committed work unnecessary to redo. Active transactions can span the checkpoint, so their relevant earlier updates can still need undo. Do not assume every transaction before a checkpoint needs no processing.ADB30
Insert and delete also modify data; sorting is a query-processing operation rather than a primitive item update.ADB07
Real systems may use physical or logical records, and need not log every read.ADB07
Choice by choice
A · correct — Saving synchronized current state best matches the listed procedure.
B · incorrect — Crash recovery uses checkpoints, but this is a later use rather than checkpoint creation itself.
C · incorrect — Logging every operation is the transaction log’s role, not checkpoint’s distinguishing purpose.
D · incorrect — Constraint enforcement is not the checkpoint procedure.
Question 98
In a log-based crash restart that uses durable checkpoint state plus relevant transaction logs, which listed labels describe the checkpoint-assisted technique and its overall restoration process? Select all that apply.
A. Rollback recovery
B. Checkpoint recovery
C. Restart recovery
D. Backup recovery
Hint / Module Reference (Where to Read)
Identify which history a recovery boundary can safely exclude and whether active work spans it.
Correct answers (select all): B, C — B. Checkpoint recovery; C. Restart recovery
Checkpoint-assisted recovery uses recorded recovery state to limit the necessary log history. Restart recovery is the overall process that restores a valid state after the crash while preserving durable commits and removing incomplete effects. Both labels describe the specified procedure at different levels. Rollback names only undo work, and backup recovery would require starting from a separate stored copy.
Evidence in the complete study note
A checkpoint records enough durable recovery state to avoid scanning all old history. In a simple quiescent/checkpoint exercise, specified flushing may make earlier committed work unnecessary to redo. Active transactions can span the checkpoint, so their relevant earlier updates can still need undo. Do not assume every transaction before a checkpoint needs no processing.ADB30
Restart recovery restores a valid database after a crash while preserving durable commits and removing incomplete effects.ADB26
Rollback handles failed/uncommitted work; redo preserves committed work missing from pages; checkpoints reduce log-search work; backups provide another stored copy.ADB26
Choice by choice
A · incorrect — Rollback specifically undoes incomplete effects rather than naming the checkpoint-plus-log restart procedure.
B · correct — Checkpoint-assisted recovery uses durable checkpoint information with the relevant log history.
C · correct — Restart recovery can use checkpoints and transaction logs to reconstruct valid state.
D · incorrect — Backup recovery starts from a separate database copy, which the description does not mention.
Question 99
Which overall crash-recovery process preserves durable committed work and removes incomplete effects after a system failure, leaving any business-level retry of formerly active transactions to a new controlled attempt?
A. Rollback recovery
B. Checkpoint recovery
C. Restart recovery
D. Backup recovery
Hint / Module Reference (Where to Read)
Identify what failed, what durable resources survive and which restoration action is required.
Restart recovery restores a valid database state after a crash. It preserves durable committed effects and removes incomplete effects using the required recovery mechanisms. Transactions active at failure are not automatically rerun and committed; a business retry is a separate controlled attempt. Rollback and checkpoint processing can be components, while backup recovery starts from another stored copy when that is needed.
Evidence in the complete study note
Transaction failures include invalid operations, constraint violations and concurrency-control aborts. Enforcing concurrency can deliberately abort work; it does not prevent every transaction failure. A system crash loses volatile memory while disks may survive. Media failure damages persistent storage and normally requires restoring a backup, then replaying available archived logs. Human mistakes may require point-in-time restoration before the bad committed operation.ADB26
Restart recovery restores a valid database after a crash while preserving durable commits and removing incomplete effects.ADB26
Rollback handles failed/uncommitted work; redo preserves committed work missing from pages; checkpoints reduce log-search work; backups provide another stored copy.ADB26
Choice by choice
A · incorrect — Rollback names undo of incomplete work rather than the entire restart process.
B · incorrect — Checkpoint information guides recovery rather than naming its whole process.
C · correct — Restart recovery is the overall crash-restoration process described.
D · incorrect — Backup restoration starts from a separate copy, which this scenario does not require.
Question 100
After a power failure loses volatile memory but leaves persistent storage intact, which term names the overall process that restores a valid database while preserving durable commits and removing incomplete effects? Choose the umbrella process rather than one component.
A. Rollback recovery
B. Checkpoint recovery
C. Restart recovery
D. Backup recovery
Hint / Module Reference (Where to Read)
Identify what failed, what durable resources survive and which restoration action is required.
Restart recovery is the overall restoration process following a system crash or power loss when persistent storage survives. It preserves durable commits and removes incomplete effects using the appropriate logged recovery work. Rollback may supply undo and checkpoints can shorten log searches, but these are cooperating mechanisms within the process. Backup restoration addresses a separate stored copy and is not required by this stated failure.
Evidence in the complete study note
Transaction failures include invalid operations, constraint violations and concurrency-control aborts. Enforcing concurrency can deliberately abort work; it does not prevent every transaction failure. A system crash loses volatile memory while disks may survive. Media failure damages persistent storage and normally requires restoring a backup, then replaying available archived logs. Human mistakes may require point-in-time restoration before the bad committed operation.ADB26
Restart recovery restores a valid database after a crash while preserving durable commits and removing incomplete effects.ADB26
Recovery may start analysis at the checkpoint but redo from an earlier required log position.ADB30
Choice by choice
A · incorrect — Rollback is an undo component rather than the overall process.
B · incorrect — Checkpoint-assisted work guides restart rather than naming its umbrella process.
C · correct — Restart recovery names the complete restoration after this system crash.
D · incorrect — The stated intact persistent storage does not call for restoring a separate backup.
Question 101
In a conventional transaction-log system, which listed mechanisms use logged updates or checkpoint/log state for undo or restart? Select all that apply. Backup recovery in D means restoring only a standalone full backup, without replaying archived logs.
A. Rollback recovery
B. Checkpoint recovery
C. Restart recovery
D. Backup recovery
Hint / Module Reference (Where to Read)
Track durable-write ordering and distinguish reversing incomplete work from installing logged effects.
Correct answers (select all): A, B, C — A. Rollback recovery; B. Checkpoint recovery; C. Restart recovery
Logged rollback uses before-images to remove incomplete effects. Checkpoint-assisted recovery combines durable recovery state with relevant log history, and log-based restart uses that history to preserve commits and remove incomplete work. All three fit the specified model. A standalone backup restore instead copies a separately stored database image; the question excludes archived-log replay from D so that this alternative does not overlap.
Evidence in the complete study note
Write-ahead logging requires the update log needed for recovery to reach stable storage before the corresponding changed data page reaches disk. To acknowledge a durable commit, force the transaction's required log records, including its commit record, to stable storage. A buffer can be changed before that log flush; WAL orders durable writes, not every CPU assignment.ADB27
Undo restores before-images of incomplete effects, usually traversing that transaction's updates backward.ADB27
Active transactions can span the checkpoint, so their relevant earlier updates can still need undo.ADB30
Choice by choice
A · correct — The logged rollback uses before-images for undo.
B · correct — Checkpoint-assisted recovery uses recovery-state records and relevant log history.
C · correct — The specified log-based restart uses durable transaction history.
D · incorrect — A standalone image restore without log replay does not use the specified logged recovery mechanism.
Question 102
Which recovery method is used to recover from media failures, such as disk crashes?
A. Rollback recovery
B. Checkpoint recovery
C. Restart recovery
D. Backup recovery
Hint / Module Reference (Where to Read)
Identify what failed, what durable resources survive and which restoration action is required.
For disk media destruction, restore a backup copy and reconstruct committed operations from the backup log (p.103). D supplies the missing database contents.
Evidence in the complete study note
Transaction failures include invalid operations, constraint violations and concurrency-control aborts. Enforcing concurrency can deliberately abort work; it does not prevent every transaction failure. A system crash loses volatile memory while disks may survive. Media failure damages persistent storage and normally requires restoring a backup, then replaying available archived logs. Human mistakes may require point-in-time restoration before the bad committed operation.ADB26
Rollback handles failed/uncommitted work; redo preserves committed work missing from pages; checkpoints reduce log-search work; backups provide another stored copy.ADB26
Restart recovery restores a valid database after a crash while preserving durable commits and removing incomplete effects.ADB26
Choice by choice
A · incorrect — Undo alone does not recreate a destroyed database copy.
B · incorrect — Checkpoint synchronization alone is not an independent media backup.
C · incorrect — A restart without restoring lost storage is insufficient.
D · correct — Backup recovery is the required catastrophic/media restoration approach.
Question 103
What is a distributed database system?
A. A database system that is spread across multiple locations
B. A database system that uses multiple database management systems
C. A database system that supports multiple database schemas
D. A database system that is used for storing distributed data
Alternative version 1
A. A system that uses multiple database management systems
B. A system that supports multiple database schemas
C. A system that spans multiple locations or sites
D. A system that replicates data across multiple databases
Hint / Module Reference (Where to Read)
Separate coordinated network placement from engine compatibility and semantic integration.
Correct answer: A — A database system that is spread across multiple locations
A DDB is multiple logically related databases distributed over a computer network, managed transparently (p.114). A captures the physical distribution; logical relatedness and coordinated management are implicit qualifications.
Evidence in the complete study note
A distributed database contains logically related data across networked sites, managed as a coordinated system. A homogeneous system uses compatible DBMS technology across sites; a heterogeneous system integrates differing DBMSs, data models, schemas or interfaces. Different operating systems are possible but do not define the distinction. Same DBMS does not imply identical local schemas, automatic replication or immediate synchronization.ADB33
Distribution adds communication, coordination and security costs; a site outage can still make needed data unavailable if no usable replica exists.ADB33
Choice by choice
A · correct — Multiple locations captures the defining distribution.
B · incorrect — Different DBMS products are not required; homogeneous DDBs exist.
C · incorrect — Multiple schemas are not the physical distribution definition.
D · incorrect — Storing distributed data is circular and omits the network/location criterion.
Alternative version 1
Correct answer: C — A system that spans multiple locations or sites
Apply the same study-note rule to this alternative source set; the choice contrasts below show the derivation.
A distributed database contains logically related data across networked sites, managed as a coordinated system. A homogeneous system uses compatible DBMS technology across sites; a heterogeneous system integrates differing DBMSs, data models, schemas or interfaces. Different operating systems are possible but do not define the distinction. Same DBMS does not imply identical local schemas, automatic replication or immediate synchronization.ADB33
Distribution adds communication, coordination and security costs; a site outage can still make needed data unavailable if no usable replica exists.ADB33
A · incorrect — Multiple DBMS products are not required for distribution.
B · incorrect — Multiple schemas are not the spatial/network definition.
C · correct — Spanning sites matches the distributed placement definition on p.114.
D · incorrect — Replication is optional and narrower than a distributed database.
Question 104
What is the key advantage of a distributed database system?
A. Improved data security
B. Reduced data redundancy
C. Enhanced data availability
D. Simplified data querying
Hint / Module Reference (Where to Read)
Separate coordinated network placement from engine compatibility and semantic integration.
Multiple nodes allow others to do the job if one fails (p.116), improving availability. The module lists integrity/security difficulty and complexity among disadvantages (p.117).
Evidence in the complete study note
A distributed database contains logically related data across networked sites, managed as a coordinated system. A homogeneous system uses compatible DBMS technology across sites; a heterogeneous system integrates differing DBMSs, data models, schemas or interfaces. Different operating systems are possible but do not define the distinction. Same DBMS does not imply identical local schemas, automatic replication or immediate synchronization.ADB33
Full replication places the whole database at every relevant site; partial replication copies selected fragments to selected sites; nonredundant allocation stores each fragment once.ADB37
Distribution adds communication, coordination and security costs; a site outage can still make needed data unavailable if no usable replica exists.ADB33
Choice by choice
A · incorrect — Improved security is not guaranteed; distributed access complicates control.
B · incorrect — Replication stores multiple copies rather than necessarily reducing redundancy.
C · correct — Availability is explicitly improved through multiple nodes.
D · incorrect — Querying across sites adds network and optimization complexity rather than inherently simplifying it.
Question 105
What is a homogenous distributed database system?
A. A system that uses the same database management system across all sites
B. A system that stores the same type of data across all sites
C. A system that uses a single database schema across all sites
D. A system that shares the same network infrastructure across all sites
Alternative version 1
A. A system that uses the same database management system across all sites
B. A system that stores the same type of data across all sites
C. A system that uses a single database schema across all sites
D. A system that uses the same network infrastructure across all sites
Hint / Module Reference (Where to Read)
Separate coordinated network placement from engine compatibility and semantic integration.
Correct answer: A — A system that uses the same database management system across all sites
A homogeneous distributed database uses the same or compatible DBMS technology at its sites. Sharing data types, a schema or network infrastructure does not by itself establish that DBMS property. In particular, identical DBMS technology does not force all local schemas to be identical, and compatible structures alone do not prove identical DBMSs. The definition concerns the database-management technology, so A identifies the relevant criterion without adding unnecessary sameness requirements.
Evidence in the complete study note
A distributed database contains logically related data across networked sites, managed as a coordinated system. A homogeneous system uses compatible DBMS technology across sites; a heterogeneous system integrates differing DBMSs, data models, schemas or interfaces. Different operating systems are possible but do not define the distinction. Same DBMS does not imply identical local schemas, automatic replication or immediate synchronization.ADB33
Choice by choice
A · correct — Correct: the same DBMS technology across sites meets the homogeneous-system criterion.
B · incorrect — Incorrect: matching data types alone does not establish that the sites use the same DBMS technology.
C · incorrect — Incorrect: schema agreement alone does not establish DBMS homogeneity, and same DBMS technology need not imply identical local schemas.
D · incorrect — Incorrect: shared networking does not establish the database-management technology used at the sites.
Alternative version 1
Correct answer: A — A system that uses the same database management system across all sites
Apply the same study-note rule to this alternative source set; the choice contrasts below show the derivation.
A distributed database contains logically related data across networked sites, managed as a coordinated system. A homogeneous system uses compatible DBMS technology across sites; a heterogeneous system integrates differing DBMSs, data models, schemas or interfaces. Different operating systems are possible but do not define the distinction. Same DBMS does not imply identical local schemas, automatic replication or immediate synchronization.ADB33
A tightly integrated distributed system can present one global schema and shared control.ADB34
State how much schema, transaction and administrative autonomy local sites retain.ADB34
A · correct — Identical DBMS software matches p.118.
B · incorrect — Identical data types alone do not establish software homogeneity.
C · incorrect — A schema is not the software identity criterion.
D · incorrect — Network infrastructure is not the DBMS software criterion.
Question 106
What is a heterogeneous distributed database system?
A. A system that uses different database management systems across all sites
B. A system that stores different types of data across all sites
C. A system that uses different database schemas across all sites
D. A system that uses different network infrastructures across all sites
Hint / Module Reference (Where to Read)
Separate coordinated network placement from engine compatibility and semantic integration.
Correct answer: A — A system that uses different database management systems across all sites
Heterogeneous means at least one database is from a different vendor (p.119), supporting different DBMS software. It does not require every pair of sites to differ; A’s all sites must be read as across the participating sites, not pairwise uniqueness.
Evidence in the complete study note
A distributed database contains logically related data across networked sites, managed as a coordinated system. A homogeneous system uses compatible DBMS technology across sites; a heterogeneous system integrates differing DBMSs, data models, schemas or interfaces. Different operating systems are possible but do not define the distinction. Same DBMS does not imply identical local schemas, automatic replication or immediate synchronization.ADB33
Heterogeneous design must reconcile naming, types, units, identifiers, query capabilities and transaction semantics.ADB33
ODBC and JDBC provide connectivity interfaces; they do not automatically solve semantic differences or create one distributed transaction.ADB33
Choice by choice
A · correct — Different database-system vendors/software captures the defining distinction with the stated qualification.
B · incorrect — Different content/types alone do not establish vendor heterogeneity.
C · incorrect — Federated systems may have one conceptual schema despite heterogeneity.
D · incorrect — Network differences are not the database-vendor definition.
Question 107
What is data fragmentation in a distributed database system?
A. Storing data in multiple copies at different sites
B. Dividing data into smaller subsets and storing them at different sites
C. Merging data from different sites into a single copy
D. Replicating data across all sites in the system
Alternative version 1
A. Dividing data into smaller subsets and storing them at different sites
B. Replicating data across all sites for fault tolerance
C. Merging data from different sites into a single database
D. Encrypting data for secure transmission between sites
Hint / Module Reference (Where to Read)
Identify whether rows or columns are split and reverse the split to reconstruct the original relation.
Correct answer: B — Dividing data into smaller subsets and storing them at different sites
Fragmentation splits a relation into logically related correct parts (p.117); horizontal fragments select rows and vertical fragments select columns (p.118). B describes storing these subsets across sites.
Evidence in the complete study note
Horizontal fragmentation selects row subsets by predicates; their union reconstructs the relation. Check completeness so every original row is represented and disjointness if no overlap is intended. Define an explicit fragment for NULL/other values when needed. Vertical fragmentation projects column subsets and repeats a key or row identifier so joining on it reconstructs the original rows without spurious matches.ADB36
Full replication places the whole database at every relevant site; partial replication copies selected fragments to selected sites; nonredundant allocation stores each fragment once.ADB37
Encryption protects stored or transmitted content; integrity constraints restrict valid data; auditing records actions.ADB24
Choice by choice
A · incorrect — Multiple identical copies describe replication.
B · correct — Smaller subsets describe fragmentation.
C · incorrect — Merging is reconstruction/integration, the opposite direction from splitting.
D · incorrect — Copying data to all sites is replication rather than fragmentation.
Alternative version 1
Correct answer: A — Dividing data into smaller subsets and storing them at different sites
Apply the same study-note rule to this alternative source set; the choice contrasts below show the derivation.
Horizontal fragmentation selects row subsets by predicates; their union reconstructs the relation. Check completeness so every original row is represented and disjointness if no overlap is intended. Define an explicit fragment for NULL/other values when needed. Vertical fragmentation projects column subsets and repeats a key or row identifier so joining on it reconstructs the original rows without spurious matches.ADB36
Full replication places the whole database at every relevant site; partial replication copies selected fragments to selected sites; nonredundant allocation stores each fragment once.ADB37
Encryption protects stored or transmitted content; integrity constraints restrict valid data; auditing records actions.ADB24
A · correct — Splitting into subsets matches fragmentation on pp.117–118.
B · incorrect — Copies for availability describe replication.
C · incorrect — Merging combines rather than splits the relations.
D · incorrect — Encryption concerns protection rather than fragmentation.
Question 108
What is data replication in a distributed database system?
A. Storing data in multiple copies at different sites
B. Dividing data into smaller subsets and storing them at different sites
C. Merging data from different sites into a single copy
D. Deleting data that is no longer needed from the database
Alternative version 1
A. Storing multiple copies of data at different sites
B. Dividing data into smaller subsets for efficient storage
C. Merging data from different sites into a single database
D. Encrypting data to ensure security during transmission
Hint / Module Reference (Where to Read)
Separate copy placement, propagation timing and update-maintenance cost.
Correct answer: A — Storing data in multiple copies at different sites
Replication maintains several identical copies of a relation at different sites (p.117). This differs from dividing it into fragments.
Evidence in the complete study note
Horizontal fragmentation selects row subsets by predicates; their union reconstructs the relation. Check completeness so every original row is represented and disjointness if no overlap is intended. Define an explicit fragment for NULL/other values when needed. Vertical fragmentation projects column subsets and repeats a key or row identifier so joining on it reconstructs the original rows without spurious matches.ADB36
Full replication places the whole database at every relevant site; partial replication copies selected fragments to selected sites; nonredundant allocation stores each fragment once.ADB37
Encryption protects stored or transmitted content; integrity constraints restrict valid data; auditing records actions.ADB24
Choice by choice
A · correct — Multiple copies at different sites exactly matches.
B · incorrect — Subsets describe fragmentation.
C · incorrect — Merging into one copy is not maintaining multiple copies.
D · incorrect — Deletion removes data rather than replicating it.
Alternative version 1
Correct answer: A — Storing multiple copies of data at different sites
Apply the same study-note rule to this alternative source set; the choice contrasts below show the derivation.
Horizontal fragmentation selects row subsets by predicates; their union reconstructs the relation. Check completeness so every original row is represented and disjointness if no overlap is intended. Define an explicit fragment for NULL/other values when needed. Vertical fragmentation projects column subsets and repeats a key or row identifier so joining on it reconstructs the original rows without spurious matches.ADB36
Full replication places the whole database at every relevant site; partial replication copies selected fragments to selected sites; nonredundant allocation stores each fragment once.ADB37
Encryption protects stored or transmitted content; integrity constraints restrict valid data; auditing records actions.ADB24
A · correct — Identical copies at different sites match p.117.
B · incorrect — Subsets describe fragmentation, not replication.
C · incorrect — Merging to one database does not retain distributed copies.
D · incorrect — Encryption does not create replicated copies.
Question 109
Which transparency hides the physical site storing a named database object, so users can refer to that object without specifying its site?
A. Local-only data access
B. Fragmentation transparency
C. Location transparency
D. Replication transparency
Hint / Module Reference (Where to Read)
Identify exactly which detail is hidden from the user.
Location transparency hides the physical site holding a named logical object. Fragmentation transparency instead hides the existence and reconstruction of fragments, while replication transparency hides which copy is used and how copies are maintained. Those properties can coexist in a distributed database, so a vague single-logical-database experience would not identify only one. The revised stem isolates hidden site placement. Local-only access restricts what can be accessed rather than making remote placement transparent.
Evidence in the complete study note
Location transparency hides which site holds a named object. Fragmentation transparency hides the existence and reconstruction of fragments: users query the whole logical relation. Replication transparency hides which copy is used and how copies are maintained. The broad single-logical-database experience may involve all three.ADB35
Choice by choice
A · incorrect — Incorrect: restricting access to local data does not hide the placement of a named distributed object.
B · incorrect — Incorrect: fragmentation transparency hides splitting and reconstruction, not specifically the object's physical site.
C · correct — Correct: location transparency hides which physical site stores the named object.
D · incorrect — Incorrect: replication transparency hides copy selection and maintenance, not specifically site placement.
Question 110
A user explicitly names a fragment and can query it without specifying or knowing the physical site that stores it. Which transparency is being provided? The existence of the fragment is visible.
A. Local-only data access
B. Fragmentation transparency
C. Location transparency
D. Replication transparency
Hint / Module Reference (Where to Read)
Identify exactly which detail is hidden from the user.
Location transparency hides the physical site storing a named object. The user in this scenario still names the fragment, so fragmentation remains visible; fragmentation transparency would hide the split and reconstruction. Replication transparency hides which copy is selected or maintained. Restricting users to local data is an access limitation rather than concealment of a remote fragment's placement. C therefore identifies the particular hidden detail without treating all transparency properties as interchangeable.
Evidence in the complete study note
Location transparency hides which site holds a named object. Fragmentation transparency hides the existence and reconstruction of fragments: users query the whole logical relation. Replication transparency hides which copy is used and how copies are maintained. The broad single-logical-database experience may involve all three.ADB35
If users explicitly name a fragment but need not specify its site, fragmentation is visible while location can remain transparent. Distinguish whether fragment names, storage sites or both are hidden from the user.ADB35
Choice by choice
A · incorrect — Incorrect: local-only access restricts accessible data rather than hiding a named remote fragment's physical site.
B · incorrect — Incorrect: the user explicitly names the fragment, so its existence is not hidden.
C · correct — Correct: the fragment's physical site is hidden while its logical name remains usable.
D · incorrect — Incorrect: replica selection and maintenance are different hidden details from the specified site placement.
Question 111
Which data transparency level ensures that users are not aware of the presence of multiple
copies of data in different sites?
A. Local data transparency
B. Fragmentation transparency
C. Location transparency
D. Replication transparency
Hint / Module Reference (Where to Read)
Identify exactly which detail is hidden from the user.
The module combines transparent distribution (p.114) with replication as storing copies at multiple sites (pp.116–117). Hiding this copying is replication transparency.
Evidence in the complete study note
Location transparency hides which site holds a named object. Fragmentation transparency hides the existence and reconstruction of fragments: users query the whole logical relation. Replication transparency hides which copy is used and how copies are maintained. The broad single-logical-database experience may involve all three.ADB35
If users explicitly name a fragment but need not specify its site, fragmentation is visible while location can remain transparent.ADB35
Column subsets define vertical fragmentation, not a type of transparency.ADB35
Choice by choice
A · incorrect — Local transparency does not identify multiple copies.
B · incorrect — Fragmentation concerns subsets rather than duplicates.
C · incorrect — Location hides placement but does not specifically name the copying aspect.
D · correct — Replication transparency is the copy-specific aspect.
Question 112
What is the role of a distributed query processor in a distributed database system?
A. It ensures the consistency of data across all sites.
B. It manages the distribution and retrieval of data across multiple sites.
C. It controls the access permissions of users in the system.
D. It handles the physical storage of data at each site.
Hint / Module Reference (Where to Read)
Compare local processing with the bytes and messages moved between sites.
Correct answer: B — It manages the distribution and retrieval of data across multiple sites.
Distributed query processing compares moving source relations and results among sites, selecting the lower-transfer strategy (pp.120–121). B best captures distributed retrieval, understood as planning transfers for a query rather than deciding the physical database design.
Evidence in the complete study note
Distributed processing decomposes a logical query, localizes referenced relations to fragments/replicas, chooses global placement/order and optimizes local subplans. Include bytes moved, message latency, bandwidth and local work. Apply filters and projections before shipping when equivalent. A semijoin retains only rows of one input that have a match in the other, returning that input's attributes.ADB38
Authentication establishes identity; authorization/access control determines permitted actions.ADB24
A tightly integrated distributed system can present one global schema and shared control.ADB34
Choice by choice
A · incorrect — Global consistency is assigned to concurrency control/recovery rather than query transfer planning.
B · correct — Managing query data transfer and retrieval matches the worked example.
C · incorrect — Access permissions are authorization, not the worked query role.
D · incorrect — Physical local storage management is not choosing distributed query strategies.
Question 113
Which condition specifically makes a distributed database environment homogeneous, rather than merely being useful for distributed operation?
A. Consistency of database schemas across all sites
B. Compatibility of network infrastructures across all sites
C. Standardization of database management systems across all sites
D. Replication of data at each site for fault tolerance
Hint / Module Reference (Where to Read)
Separate coordinated network placement from engine compatibility and semantic integration.
Correct answer: C — Standardization of database management systems across all sites
Homogeneity is defined by using the same or compatible DBMS technology across sites. Schema compatibility, functioning network connections and replication can be useful design concerns, but none alone establishes that common DBMS technology. A homogeneous system may have different local schemas, and replication is not required merely because data is distributed. Narrowing the question to the defining condition preserves C while acknowledging that the other design concerns can matter in practice.
Evidence in the complete study note
A distributed database contains logically related data across networked sites, managed as a coordinated system. A homogeneous system uses compatible DBMS technology across sites; a heterogeneous system integrates differing DBMSs, data models, schemas or interfaces. Different operating systems are possible but do not define the distinction. Same DBMS does not imply identical local schemas, automatic replication or immediate synchronization.ADB33
Full replication places the whole database at every relevant site; partial replication copies selected fragments to selected sites; nonredundant allocation stores each fragment once.ADB37
Choice by choice
A · incorrect — Incorrect: schema compatibility can be useful but does not establish common DBMS technology.
B · incorrect — Incorrect: compatible networking supports communication but does not establish DBMS homogeneity.
C · correct — Correct: DBMS standardization supplies the defining common technology across sites.
D · incorrect — Incorrect: replication can improve availability but is optional and does not define homogeneity.
Question 114
A heterogeneous distributed database retains different local DBMSs and schemas and must integrate them without requiring full replication. Which integration requirements apply? Select all that apply. D means coherent schema semantics through mappings, not identical schemas.
A. Replication of data at each site for fault tolerance
B. Standardization of database management systems across all sites
C. Compatibility of network infrastructures across all sites
D. Consistency of database schemas across all sites
Hint / Module Reference (Where to Read)
Separate coordinated network placement from engine compatibility and semantic integration.
Correct answers (select all): C, D — C. Compatibility of network infrastructures across all sites; D. Consistency of database schemas across all sites
Integration requires interoperable communication so requests and results can move between sites. It also requires coherent mappings for names, types, identifiers and other schema meanings. These support C and D. Replacing every DBMS with one product would remove the retained heterogeneity, and full replication is an optional placement decision rather than a requirement for integrating different local systems.
Evidence in the complete study note
A distributed database contains logically related data across networked sites, managed as a coordinated system. A homogeneous system uses compatible DBMS technology across sites; a heterogeneous system integrates differing DBMSs, data models, schemas or interfaces. Different operating systems are possible but do not define the distinction. Same DBMS does not imply identical local schemas, automatic replication or immediate synchronization.ADB33
Heterogeneous design must reconcile naming, types, units, identifiers, query capabilities and transaction semantics.ADB33
Full replication does not inherently mean every copy is updated simultaneously; its synchronization rule must be stated.ADB37
Choice by choice
A · incorrect — Copying all data everywhere is not required for the stated integration.
B · incorrect — The scenario retains differing DBMSs rather than replacing all with one standard.
C · correct — Interoperable communication is needed to exchange requests and results.
D · correct — Mappings must reconcile schema meanings without requiring identical local schemas.
Question 115
Which approach is commonly used for query processing in a distributed database system?
A. Centralized query processing
B. Distributed query processing
C. Parallel query processing
D. Sequential query processing
Hint / Module Reference (Where to Read)
Compare local processing with the bytes and messages moved between sites.
The module’s distributed-query section compares execution and data-transfer locations among multiple sites (pp.120–121). B names the overall approach. A chosen plan may execute a particular join at one site; that does not make the whole system centralized.
Evidence in the complete study note
Distributed processing decomposes a logical query, localizes referenced relations to fragments/replicas, chooses global placement/order and optimizes local subplans. Include bytes moved, message latency, bandwidth and local work. Apply filters and projections before shipping when equivalent. A semijoin retains only rows of one input that have a match in the other, returning that input's attributes.ADB38
If nearly all S matches, key shipping may not help.ADB38
Plan caching reuses an execution strategy, not the rows returned.ADB22
Choice by choice
A · incorrect — A local execution site can be part of a distributed plan, but centralized processing does not name the chapter’s overall approach.
B · correct — Distributed query processing matches the section and cross-site strategies.
C · incorrect — Parallelism may be used, but simultaneous execution is not required by these examples.
D · incorrect — A plan may contain sequential operations; this does not identify cross-site processing.
Question 116
What is the role of a distributed transaction coordinator in a distributed database system?
A. It ensures the consistency of data across all sites.
B. It manages the distribution and retrieval of data across multiple sites.
C. It controls the access permissions of users in the system.
D. It handles the physical storage of data at each site.
Hint / Module Reference (Where to Read)
Distinguish coordinated transaction outcome from concurrent access and local storage.
Correct answer: A — It ensures the consistency of data across all sites.
Global consistency is required of concurrency/recovery (p.122). Under the primary-site technique a coordinator manages transaction concurrency and commit (p.123). A is supported in that transaction-coordination sense, rather than as an unconditional guarantee against every error.
Evidence in the complete study note
Two-phase commit (2PC) first asks participants to prepare and durably promise they can commit. If all vote yes, the coordinator records commit durably and tells participants; otherwise it decides abort. A prepared participant must retain enough state to obey the final decision. Coordinator/communication failure can leave it in doubt and block progress until the decision is learned. Timeout after preparation is not permission to invent a commit or abort.ADB39
The transaction coordinator coordinates decisions rather than physically storing every site's rows.ADB39
Distributed processing decomposes a logical query, localizes referenced relations to fragments/replicas, chooses global placement/order and optimizes local subplans.ADB38
Choice by choice
A · correct — Coordination of concurrency and commit maintains transaction consistency across sites.
B · incorrect — Query transfer/retrieval is a separate distributed-query task.
C · incorrect — User permissions are authorization, not commit coordination.
D · incorrect — Local physical storage is not the primary coordinator’s stated function.
Question 117
Which listed approaches are replication policies or transfer methods in distributed databases, allowing dimensions to coexist? Select all that apply.
A. Eager replication
B. Lazy replication
C. Snapshot replication
D. Incremental replication
Hint / Module Reference (Where to Read)
Separate copy placement, propagation timing and update-maintenance cost.
Correct answers (select all): A, B, C, D — A. Eager replication; B. Lazy replication; C. Snapshot replication; D. Incremental replication
Eager and lazy replication differ in when propagation occurs relative to the acknowledgment or commit point. Snapshot and incremental replication differ in whether a copy or changes are transferred. These are legitimate replication dimensions and can coexist, so all four qualify. The question asks for recognized approaches rather than claiming that one policy is most prevalent or universally preferable.
Evidence in the complete study note
Full replication places the whole database at every relevant site; partial replication copies selected fragments to selected sites; nonredundant allocation stores each fragment once. Copies improve read locality and can improve availability but require storage and update propagation. They can also spread mistakes, so replication does not replace independent backups.ADB37
Eager/synchronous replication coordinates propagation before the specified commit/acknowledgment point, increasing update latency.ADB37
Snapshot replication transfers a copy at intervals; incremental replication transfers changes.ADB37
Choice by choice
A · correct — Eager replication coordinates propagation before the specified acknowledgment or commit point.
B · correct — Lazy replication propagates changes later and can expose stale reads or require conflict handling.
C · correct — Snapshot replication distributes a copy at intervals rather than necessarily sending each individual update.
D · correct — Incremental replication transfers changes and can coexist with a propagation-timing policy.
Question 118
When comparing distributed query strategies, which factors can affect estimated execution cost or replica placement decisions? Select all that apply.
A. Cost of query execution
B. Size of the database
C. Network bandwidth
D. Data replication
Hint / Module Reference (Where to Read)
Identify the resource objective, counts and unit costs before comparing execution strategies.
Correct answers (select all): A, B, C, D — A. Cost of query execution; B. Size of the database; C. Network bandwidth; D. Data replication
Execution cost summarizes the resources consumed by a candidate plan. Relation sizes affect scans and intermediate work, network bandwidth affects data-transfer time, and replica placement changes which copies are available near the computation. All these inputs can alter a distributed strategy's cost. They are complementary considerations rather than competing names for one exclusively important factor.
Evidence in the complete study note
A cost model estimates resources for a candidate plan: page I/O, CPU, memory, temporary storage and network traffic. Catalog statistics include row/page counts, distinct values and distributions. Selection with fraction s predicts s×N rows. For non-NULL equijoin keys, a common estimate is |R|×|S|/max(V(R,k),V(S,k)), assuming uniform values and containment of the smaller distinct-value domain within the larger. Adjust counts for NULLs; if either non-NULL input is empty, the result is zero. Skew, correlations or incomplete domain overlap can invalidate this estimate; known disjoint domains give zero matches.ADB19
Distributed processing decomposes a logical query, localizes referenced relations to fragments/replicas, chooses global placement/order and optimizes local subplans.ADB38
Include bytes moved, message latency, bandwidth and local work.ADB38
Choice by choice
A · correct — Estimated execution cost is the explicit basis for comparing candidate strategies.
B · correct — Database and relation sizes affect scan work, intermediate sizes and algorithm feasibility.
C · correct — Bandwidth affects transfer time when data or results move between sites.
D · correct — Replica locations affect which copy can serve a query and its communication cost.
Question 119
What is the purpose of query optimization in a database system?
A. To ensure data consistency
B. To minimize query execution time
C. To maximize data replication
D. To manage database security
Hint / Module Reference (Where to Read)
Separate query representation, strategy selection and execution.
Correct answer: B — To minimize query execution time
Query optimization chooses an efficient execution strategy and minimizes resource usage including response time (p.27). Page 30 explicitly seeks faster queries and the most efficient sequence of steps.
Evidence in the complete study note
Parsing checks SQL grammar; semantic analysis resolves names, types and access permissions. Translation constructs a logical representation, typically relational algebra or a logical query tree. Optimization chooses equivalent operations, join order and physical access methods. Evaluation executes that plan and returns results. Not every implementation exposes exactly the same phase boundaries.ADB17
Query processing is the whole activity; selecting a suitable efficient strategy is optimization.ADB17
A transaction is a logical unit of database work, including read-only work.ADB06
Choice by choice
A · incorrect — Consistency is a transaction correctness concern, not the stated optimization objective.
B · correct — Minimizing execution/response time captures the efficiency objective.
C · incorrect — Maximizing replication is not the execution-plan goal.
D · incorrect — Authorization/security is not query optimization’s stated purpose.
Question 120
What is a query plan in a database system?
A. A set of database schemas
B. A representation of the query result
C. A sequence of operations to execute a query
D. A collection of database indices
Hint / Module Reference (Where to Read)
Separate query representation, strategy selection and execution.
Correct answer: C — A sequence of operations to execute a query
An execution plan includes a relational-algebra query tree, access methods and operator implementations (p.44). Its operations run from leaves toward the root (p.29), so C captures the execution procedure, which can have a tree structure rather than a single linear list.
Evidence in the complete study note
Parsing checks SQL grammar; semantic analysis resolves names, types and access permissions. Translation constructs a logical representation, typically relational algebra or a logical query tree. Optimization chooses equivalent operations, join order and physical access methods. Evaluation executes that plan and returns results. Not every implementation exposes exactly the same phase boundaries.ADB17
DDL is not a mandatory stage before each SELECT, so authentication→DDL→DML permutations do not describe the processing sequence.ADB17
A query tree corresponds to a relational-algebra expression: base relations appear at leaves and operators at internal nodes.ADB-S01
Choice by choice
A · incorrect — Schemas describe data structure rather than the executable operations.
B · incorrect — The result is the output, whereas the plan specifies how to obtain it.
C · correct — An operation sequence/tree with access methods is the stated plan concept.
D · incorrect — Indices may be access methods within a plan but do not alone constitute it.
Question 121
What is a cost model in query optimization?
A. A statistical model of query execution times
B. A model for estimating the cost of query plans
C. A model for analyzing database schemas
D. A statistical model for data distribution
Hint / Module Reference (Where to Read)
Identify the resource objective, counts and unit costs before comparing execution strategies.
Correct answer: B — A model for estimating the cost of query plans
The cost function combines I/O, CPU, communication and storage (p.41); optimization estimates and compares alternative strategy costs (p.42). A cost model therefore estimates costs of query plans.
Evidence in the complete study note
A cost model estimates resources for a candidate plan: page I/O, CPU, memory, temporary storage and network traffic. Catalog statistics include row/page counts, distinct values and distributions. Selection with fraction s predicts s×N rows. For non-NULL equijoin keys, a common estimate is |R|×|S|/max(V(R,k),V(S,k)), assuming uniform values and containment of the smaller distinct-value domain within the larger. Adjust counts for NULLs; if either non-NULL input is empty, the result is zero. Skew, correlations or incomplete domain overlap can invalidate this estimate; known disjoint domains give zero matches.ADB19
State the assumed cost objective before ranking strategies.ADB19
Disk I/O often dominates textbook local-disk examples; CPU can dominate in-memory work and network can dominate distributed plans.ADB19
Choice by choice
A · incorrect — Execution time can contribute, but this is narrower than the listed resource-cost model.
B · correct — Estimating plan costs is the defining function.
C · incorrect — Schema analysis is not evaluation of competing execution strategies.
D · incorrect — Data distribution statistics are possible inputs, not the whole cost model.
Question 122
What is a join operation in query processing?
A. Combining tuples from multiple tables based on a common attribute
B. Sorting the query result in ascending order
C. Grouping the query result based on a specific attribute
D. Filtering the query result based on a condition
Hint / Module Reference (Where to Read)
Separate the query's representation from the operations used to calculate its result.
Correct answer: A — Combining tuples from multiple tables based on a common attribute
The query joins Staff and Branch by matching branchNo (p.27). The heuristic combines a Cartesian product and selection on the join condition into a join (p.41); the distributed example similarly matches Employee.Dno and Department.Dnumber (p.120). A expresses this equijoin case; joins in general need not always use equality or identical attribute names.
Evidence in the complete study note
A relation is represented by a table of tuples. Selection filters its rows by a predicate; projection retains chosen columns. A Cartesian product constructs every pair of input tuples. A matching join combines tuples satisfying its predicate, and can be represented as a selection over a product. Sorting orders results, while grouping collects rows sharing grouping values; these are different operations.ADB-S01
Heuristics reduce intermediate work: push selective row filters toward base tables; replace product followed by a join predicate with a join; project unneeded columns away while retaining join/filter columns; reorder inner joins when equivalence permits; reuse common expressions when worthwhile.ADB18
Cost-based optimization estimates candidate plans instead of relying on rules alone.ADB18
Choice by choice
A · correct — Combining tuples that satisfy the matching attribute condition describes these joins.
B · incorrect — Sorting changes order rather than combining source tuples.
C · incorrect — Grouping builds groups/aggregates, not cross-relation matching.
D · incorrect — Filtering alone selects rows rather than combining related tuples from tables.
Question 123
Which listed index or clustered-storage approaches can speed a suitable query workload, without a claim that any always wins? Select all that apply.
A. B-tree index
B. Hash index
C. Bitmap index
D. Clustered index
Hint / Module Reference (Where to Read)
Match the predicate and workload to the access structure or reasons to choose a plan again.
Correct answers (select all): A, B, C, D — A. B-tree index; B. Hash index; C. Bitmap index; D. Clustered index
B-tree indexes support ordered and range access, hash indexes target equality, and bitmap indexes can support analytic selections. Clustered storage places related rows close together and can reduce access work for suitable queries. Each approach can therefore help an appropriate workload. Space, maintenance and selectivity still matter, so accepting all four does not claim that every query benefits or that one design is universally best.
Evidence in the complete study note
B/B+ tree indexes support ordered and range predicates; hash indexes target equality; bitmap indexes can help some analytic selections. Clustered storage keeps related rows close. An index may reduce reads but adds space and update work; scanning can be faster when most rows qualify. Commonly useful is not a unique property of one index type.ADB23
Avoid universal rules that joins always beat subqueries, functions can never use indexes or more indexes always improve performance.ADB23
Updating statistics does not itself execute the query.ADB23
Choice by choice
A · correct — B-tree indexes support selective ordered and range access for suitable queries.
B · correct — Hash indexes can accelerate equality lookups on the indexed key.
C · correct — Bitmap indexes can accelerate suitable analytic selection workloads.
D · correct — Clustered storage keeps related rows close and can reduce reads for appropriate access patterns.
Question 124
What is query re-optimization in a database system?
A. Re-executing the query with different parameters
B. Re-evaluating the query cost based on updated statistics
C. Re-writing the query in a different query language
D. Re-organizing the database schema to improve performance
Hint / Module Reference (Where to Read)
Match the predicate and workload to the access structure or reasons to choose a plan again.
Correct answer: B — Reconsider the plan using updated cost information or statistics.
ADB23 defines re-optimization as choosing a plan again when statistics, parameters or observed cardinalities suggest that the existing plan is unsuitable. B is the listed description of this cost reconsideration. Merely executing again with other values does not necessarily re-optimize, and changing the query language or redesigning the schema is not the definition.
Evidence in the complete study note
B/B+ tree indexes support ordered and range predicates; hash indexes target equality; bitmap indexes can help some analytic selections. Clustered storage keeps related rows close. An index may reduce reads but adds space and update work; scanning can be faster when most rows qualify. Commonly useful is not a unique property of one index type.ADB23
Re-optimization chooses a plan again when statistics, parameters or observed cardinalities suggest the old plan is unsuitable.ADB23
Updating statistics does not itself execute the query.ADB23
Choice by choice
A · incorrect — Re-execution with new parameters can reuse the same plan and is not inherently re-optimization.
B · correct — Updated statistics can change cost estimates and lead to choosing a new execution plan.
C · incorrect — Changing query language is not required to reconsider an execution strategy.
D · incorrect — Schema reorganization is a design change rather than the definition of query re-optimization.
Question 125
What is query pipelining in query processing?
A. Dividing a query into subqueries and executing them in parallel
B. Dividing a query into stages and executing them sequentially
C. Dividing a query into multiple queries and executing them in parallel
D. Dividing a query into multiple stages and executing them concurrently
Hint / Module Reference (Where to Read)
Distinguish overlapping dependent operators, independent workers and reused cached objects.
Correct answer: D — Dividing a query into multiple stages and executing them concurrently
Pipelined evaluation evaluates several operations simultaneously and passes results directly from one to the next (p.44). D means overlapping producer/consumer stages, not necessarily executing independent whole queries.
Evidence in the complete study note
Materialization stores an intermediate result before the next operator consumes it. Pipelining passes tuples as they become available, overlapping producer and consumer work and reducing temporary I/O. A sort is normally blocking; a hash join must build its hash table before probing can output, though later probe/output can stream. Pipelining does not require separate CPUs.ADB22
Parallel execution uses multiple workers, for example partitioned scans/joins, independent subplans or operator pipelines.ADB22
Specify the kind of query caching being discussed.ADB22
Choice by choice
A · incorrect — Parallel independent subqueries are not the defining inter-operator tuple stream.
B · incorrect — Pure sequential completion of each whole stage loses the stated simultaneous overlap.
C · incorrect — Multiple independent queries are not the defining pipeline within an expression tree.
D · correct — Concurrent overlapping stages passing tuples match the explicit definition.
Question 126
What is query result caching, specifically caching returned answers rather than execution plans or intermediate rows?
A. Storing query results for future reuse
B. Storing intermediate results during query execution
C. Storing database metadata for query optimization
D. Storing query plans for efficient execution
Hint / Module Reference (Where to Read)
Distinguish overlapping dependent operators, independent workers and reused cached objects.
Correct answer: A — A. Storing query results for future reuse
Result caching stores a query's returned rows or answer for future reuse. It needs suitable invalidation when underlying data changes. Plan caching instead stores the execution strategy without its returned rows. Intermediate rows retained during one execution are materialization, while database metadata supports optimization. Naming the cached object makes result caching distinct from these other useful storage mechanisms.
Evidence in the complete study note
Parallel execution uses multiple workers, for example partitioned scans/joins, independent subplans or operator pipelines. Interleaving on one CPU is concurrency; simultaneous cores provide parallelism. Result caching reuses answers and needs invalidation when underlying data changes. Plan caching reuses an execution strategy, not the rows returned. Buffer caching keeps data pages; materialization retains intermediate rows. Specify the kind of query caching being discussed.ADB22
Materialization stores an intermediate result before the next operator consumes it.ADB22
Catalog statistics include row/page counts, distinct values and distributions.ADB19
Choice by choice
A · correct — This stores final query answers for future reuse.
B · incorrect — Intermediate-row storage is materialization rather than final-answer caching.
C · incorrect — Metadata supports optimization but is not the query's returned answer.
D · incorrect — Plan caching stores strategy rather than returned rows.
Question 127
What is query parallelization in query processing?
A. Dividing a query into subqueries and executing them in parallel
B. Dividing a query into stages and executing them sequentially
C. Dividing a query into multiple queries and executing them in parallel
D. Dividing a query into multiple stages and executing them concurrently
Hint / Module Reference (Where to Read)
Distinguish overlapping dependent operators, independent workers and reused cached objects.
Correct answer: A — Dividing a query into subqueries and executing them in parallel
The fragmentation discussion says a transaction can be divided into several subqueries operating on fragments, increasing concurrency (p.117). A matches this parallel decomposition; it is distinct from pipelining a dependent stream of stages.
Evidence in the complete study note
Materialization stores an intermediate result before the next operator consumes it. Pipelining passes tuples as they become available, overlapping producer and consumer work and reducing temporary I/O. A sort is normally blocking; a hash join must build its hash table before probing can output, though later probe/output can stream. Pipelining does not require separate CPUs.ADB22
Query processing is the whole activity; selecting a suitable efficient strategy is optimization.ADB17
Parallel execution uses multiple workers, for example partitioned scans/joins, independent subplans or operator pipelines.ADB22
Choice by choice
A · correct — Independent fragment subqueries can run in parallel as described.
B · incorrect — Sequential stages do not supply parallelism.
C · incorrect — Replacing one query with multiple separate whole queries is not the stated subquery decomposition.
D · incorrect — Concurrent stages describe pipelining when results flow between dependent operators, not the specific fragment-subquery approach.
Question 128
If power went off while processing a transaction, what should the DBMS do to reverse any
changes made to the database?
A. Read_transaction
B. Fail transaction
C. Commit transaction
D. Rollback
Hint / Module Reference (Where to Read)
Track durable-write ordering and distinguish reversing incomplete work from installing logged effects.
To reverse changes of an incomplete failed transaction, recovery undoes its effects (p.103) by restoring before images (p.107). This is rollback. A committed transaction instead requires durability, so the answer assumes the interrupted transaction had not committed, as implied by reverse any changes.
Evidence in the complete study note
Write-ahead logging requires the update log needed for recovery to reach stable storage before the corresponding changed data page reaches disk. To acknowledge a durable commit, force the transaction's required log records, including its commit record, to stable storage. A buffer can be changed before that log flush; WAL orders durable writes, not every CPU assignment.ADB27
Undo restores before-images of incomplete effects, usually traversing that transaction's updates backward.ADB27
Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure.ADB06
Choice by choice
A · incorrect — Reading a transaction is not an undo operation.
B · incorrect — Marking failure does not itself restore old values.
C · incorrect — Committing would preserve rather than reverse the changes.
D · correct — Rollback/undo is the direct reversal mechanism.
Question 129
What is the main difference between homogeneous and heterogeneous distributed databases?
Hint / Module Reference (Where to Read)
Separate coordinated network placement from engine compatibility and semantic integration.
Answer: Homogeneous systems use compatible DBMS technology; heterogeneous systems integrate differing DBMSs, models, schemas or interfaces.
ADB33 defines the distinction through compatibility of database technology and representation. Operating-system differences can occur but do not define heterogeneity. Homogeneous sites need not have identical schemas or automatic replication. Heterogeneous integration requires explicit mappings for names, types and semantics, rather than assuming that common connectivity alone reconciles their meanings.
Evidence in the complete study note
A distributed database contains logically related data across networked sites, managed as a coordinated system. A homogeneous system uses compatible DBMS technology across sites; a heterogeneous system integrates differing DBMSs, data models, schemas or interfaces. Different operating systems are possible but do not define the distinction. Same DBMS does not imply identical local schemas, automatic replication or immediate synchronization.ADB33
ODBC and JDBC provide connectivity interfaces; they do not automatically solve semantic differences or create one distributed transaction.ADB33
Heterogeneous design must reconcile naming, types, units, identifiers, query capabilities and transaction semantics.ADB33
Question 130
Why is data fragmentation useful in distributed databases?
Hint / Module Reference (Where to Read)
Identify whether rows or columns are split and reverse the split to reconstruct the original relation.
Answer: Fragmentation improves locality, manageable placement and parallelism by splitting data across useful site boundaries.
ADB36 splits rows with predicates and columns with projections while preserving reconstruction through unions or key joins. This can place frequently used data near its consumers and allow independent work on pieces. Correct fragments must preserve completeness and required disjointness. Fragmentation alone creates no spare copy, so losing the only site containing a fragment still makes it unavailable.
Evidence in the complete study note
Horizontal fragmentation selects row subsets by predicates; their union reconstructs the relation. Check completeness so every original row is represented and disjointness if no overlap is intended. Define an explicit fragment for NULL/other values when needed. Vertical fragmentation projects column subsets and repeats a key or row identifier so joining on it reconstructs the original rows without spurious matches.ADB36
Partitioning improves locality and parallelism but alone creates no spare copy.ADB36
Losing the only site containing a fragment makes that fragment unavailable, even while other sites continue.ADB36
Question 131
What is the purpose of two-phase locking (2PL) in concurrency control?
Hint / Module Reference (Where to Read)
Track the first lock release and the transaction-completion boundary.
Answer: 2PL guarantees conflict serializability by separating lock acquisition from release.
During the growing phase a transaction acquires or upgrades locks; after it releases any lock, the shrinking phase forbids acquiring new ones. For well-formed transactions this guarantees conflict serializability. Basic 2PL can still deadlock and permit cascading aborts, so strict or conservative variants add different protections. It is distinct from distributed two-phase commit.
Evidence in the complete study note
Basic 2PL has a growing phase that acquires/upgrades locks and a shrinking phase that releases/downgrades them. Once any lock is released, no new lock may be acquired. The lock point is the instant the final lock is obtained. 2PL guarantees conflict serializability for well-formed transactions, but can deadlock and basic 2PL alone need not prevent cascading aborts.ADB12
Two-phase locking orders access to data; two-phase commit coordinates a distributed commit decision.ADB12
Strict 2PL retains exclusive locks until commit/abort.ADB12
Question 132
Why is write-ahead logging important for database recovery?
Hint / Module Reference (Where to Read)
Track durable-write ordering and distinguish reversing incomplete work from installing logged effects.
Answer: WAL makes recovery information durable before its changed data pages, enabling safe undo/redo after crashes.
ADB27 requires needed update logs to reach stable storage before corresponding changed pages. A durable commit also forces required log records, including commit. Recovery can then use before-images for undo and after-images for redo if buffered data was lost. WAL orders durable writes; modifying an in-memory buffer before the log flush does not itself violate it.
Evidence in the complete study note
Write-ahead logging requires the update log needed for recovery to reach stable storage before the corresponding changed data page reaches disk. To acknowledge a durable commit, force the transaction's required log records, including its commit record, to stable storage. A buffer can be changed before that log flush; WAL orders durable writes, not every CPU assignment.ADB27
Redo installs logged after-images when necessary, usually forward.ADB27
Undo restores before-images of incomplete effects, usually traversing that transaction's updates backward.ADB27
Question 133
How does a data warehouse differ from an operational database?
Hint / Module Reference (Where to Read)
Distinguish operational transactions, analytical workloads and physical storage layout.
Answer: A warehouse integrates historical data for analysis; an operational database serves frequent business transactions.
OLTP handles account updates and other frequent operational transactions, while OLAP scans and summarizes large datasets. A warehouse commonly integrates sources through extraction, transformation and loading for historical analysis. These are workload tendencies rather than absolute restrictions: operational databases may retain history, and warehouses may receive updates. Schema and storage choices should follow the actual workload.
Evidence in the complete study note
OLTP handles frequent operational transactions, such as account updates; OLAP scans and summarizes large datasets for analysis. A warehouse integrates data for historical analysis, often through extraction, transformation and loading. These are workload tendencies: OLTP can retain history and warehouses can be updated.ADB41
Question 134
What does ACID stand for in the context of database transactions?
A. Atomicity, Consistency, Isolation, Durability
B. Accuracy, Consistency, Integrity, Durability
C. Atomicity, Concurrency, Isolation, Data
D. Availability, Consistency, Isolation, Durability
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
Correct answer: A — Atomicity, Consistency, Isolation, Durability.
ADB06 names all four ACID properties and assigns distinct roles: all-or-none effects, preserved invariants, controlled interference and durable commits. Only A lists those names in the correct order. Accuracy, integrity, concurrency, data and availability are related database concepts but do not replace the letters of this established transaction acronym.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
Choice by choice
A · correct — It lists the four ACID properties in the proper acronym order.
B · incorrect — Accuracy and Integrity replace Atomicity and Isolation, changing the defined acronym.
C · incorrect — Concurrency and Data incorrectly replace Consistency and Durability.
D · incorrect — Availability is not the A in the ACID transaction acronym.
Question 135
Which ACID property ensures that a transaction is treated as an indivisible unit — either all steps succeed or none are applied?
A. Consistency
B. Isolation
C. Atomicity
D. Durability
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
The question specifies indivisibility of the transaction effects: partial success must not remain if the transaction fails. ADB06 assigns that guarantee to atomicity. Consistency concerns valid invariants, isolation controls interference, and durability protects already committed work. Those properties support correctness too, but none directly means all-or-none effects.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
Choice by choice
A · incorrect — Consistency concerns preserving integrity rules rather than specifically making effects indivisible.
B · incorrect — Isolation controls interactions between concurrent transactions rather than partial transaction application.
C · correct — Atomicity applies every transaction effect together or discards the incomplete work.
D · incorrect — Durability protects committed effects after failure rather than defining all-or-none completion.
Question 136
Account A = 5,000 Birr, Account B = 8,000 Birr. After transferring 750 Birr from A to B, which values maintain CONSISTENCY?
A. A = 4,000, B = 9,000
B. A = 4,250, B = 8,750
C. A = 5,750, B = 7,250
D. A = 4,250, B = 8,000
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
Correct answer: B — A = 4,250 Birr and B = 8,750 Birr.
The requested transfer subtracts 750 from 5000 and adds 750 to 8000, giving 4250 and 8750. The invariant total remains 13000. A and C also sum to 13000, but implement a different amount or the reverse direction. Preserving the total alone does not implement the specified successful transfer; D loses the credit.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
Unchanged or reverse-transfer balances can also preserve that sum; only the requested debit/credit implements the specified successful transfer.ADB06
A=5,000 and B=8,000 become 4,250 and 8,750 after transferring 750 from A to B.ADB06
Choice by choice
A · incorrect — The sum remains 13000, but the changes transfer 1000 rather than the requested 750.
B · correct — It debits 750 from A, credits 750 to B and preserves the 13000 total.
C · incorrect — It preserves the total but transfers 750 in the reverse direction.
D · incorrect — It debits A without crediting B, reducing the total to 12250.
Question 137
Which ACID property guarantees that committed transaction changes persist even after a system crash?
A. Atomicity
B. Consistency
C. Isolation
D. Durability
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
ADB06 defines durability as preserving committed effects despite failure. A system may leave data pages buffered at commit provided durable recovery information can restore the changes after a crash. Atomicity controls partial outcomes, consistency protects declared invariants and isolation controls concurrent interference; they do not name this persistence guarantee.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
Choice by choice
A · incorrect — Atomicity means all-or-none effects, not specifically persistence after a successful commit.
B · incorrect — Consistency concerns valid integrity rules before and after correct work.
C · incorrect — Isolation controls concurrent interference rather than persistence across a crash.
D · correct — Durability ensures committed changes survive failures through the supported recovery mechanisms.
Question 138
In the Read_item(X) operation, what is the THIRD and final step?
A. Store the updated block back to disk
B. Find the disk block address containing X
C. Copy the disk block into main memory buffer
D. Copy item X from the buffer to the program variable named x
Hint / Module Reference (Where to Read)
Trace movement between program variables, buffers and disk, or the meaning of a recorded transaction event.
Correct answer: D — Copy X from the buffer into the program variable x.
ADB07 first locates the page containing X, brings it into a buffer if absent and then copies X into a program variable. Thus D is the final conceptual read step. Locating and loading precede it, and storing an updated block to disk belongs to writing/flushing rather than completing read_item. A buffer hit can avoid physical I/O.
Evidence in the complete study note
read_item(X) locates X's page, brings it into a buffer if absent, and copies X into a program variable. write_item(X) copies the new program value into the buffered page, marking it dirty. Writing that page to disk can occur later according to the buffer policy; write_item is not inherently an immediate disk flush. Insert and delete also modify data; sorting is a query-processing operation rather than a primitive item update.ADB07
Choice by choice
A · incorrect — A read does not update and write the block back to disk.
B · incorrect — Locating the containing page is the first conceptual step, not the final one.
C · incorrect — Loading the page into a buffer precedes copying the item into the program variable.
D · correct — The final read step copies the requested item from its buffer into x.
Question 139
A transaction that has started and is currently executing its operations is in which state?
A. Committed
B. Partially Committed
C. Active
D. Failed
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
ADB06 defines Active as the state while transaction operations are executing. Partially committed follows completion of the last operation but precedes assured durable commitment. Committed means commitment succeeded, while Failed means execution encountered a failure. Merely starting and continuing its operations therefore places this transaction in Active.
Evidence in the complete study note
Active means operations are executing. Partially committed means the last operation finished but durable commitment is not yet assured. Committed means commitment succeeded. A failure leads to Failed, then Aborted after rollback; a transaction may terminate or be restarted as a new attempt. Concurrency control primarily enforces isolation; recovery supports atomicity and durability. Transaction management is the broader subsystem, so both it and recovery management can be legitimate answers to broad responsibility questions.ADB06
Choice by choice
A · incorrect — Committed requires successful commitment, which has not occurred while operations are still executing.
B · incorrect — Partially committed follows the last operation rather than ordinary ongoing execution.
C · correct — Active is the state during execution of the transaction operations.
D · incorrect — Failed requires an execution failure, which the stem does not report.
Question 140
Which transaction state means all operations executed successfully but the commit has NOT been finalized yet?
A. Active
B. Partially Committed
C. Committed
D. Aborted
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
Finishing all operations does not yet establish durable commitment. ADB06 names this intermediate state Partially committed. The transaction becomes Committed only after commitment succeeds, or can fail before that boundary. Active describes operations still running, while Aborted means rollback completed, so neither matches successful operations awaiting finalized commit.
Evidence in the complete study note
Active means operations are executing. Partially committed means the last operation finished but durable commitment is not yet assured. Committed means commitment succeeded. A failure leads to Failed, then Aborted after rollback; a transaction may terminate or be restarted as a new attempt. Concurrency control primarily enforces isolation; recovery supports atomicity and durability. Transaction management is the broader subsystem, so both it and recovery management can be legitimate answers to broad responsibility questions.ADB06
Choice by choice
A · incorrect — Active means operations are still executing, whereas the last operation has finished here.
B · correct — Partially committed means execution finished but durable commitment is not yet assured.
C · incorrect — Committed requires the commit itself to have succeeded, which the stem excludes.
D · incorrect — Aborted follows rollback rather than successful execution awaiting a commit decision.
Question 141
What does the log record [abort, T] indicate?
A. Transaction T has just started execution
B. Transaction T committed successfully
C. Transaction T was aborted and ALL its changes must be discarded
D. Transaction T read a data item
Hint / Module Reference (Where to Read)
Trace movement between program variables, buffers and disk, or the meaning of a recorded transaction event.
Correct answer: C — T is aborting/aborted and its transaction effects must be discarded.
ADB07 includes ABORT among conceptual log records and warns that it can mark the start of rollback rather than prove every undo step is already complete. C is correct because it says the changes must be discarded, not that all undo has finished. START, COMMIT and ordinary read activity have different meanings.
Evidence in the complete study note
A conceptual log can contain START, UPDATE(T,X,old,new), COMMIT, ABORT and END records. Before-images support undo; after-images support redo. Real systems may use physical or logical records, and need not log every read. COMMIT records successful completion; an ABORT record can mark the start of rollback rather than prove every undo step is complete.ADB07
Choice by choice
A · incorrect — A START record, rather than ABORT, marks beginning transaction execution.
B · incorrect — COMMIT records successful completion; ABORT means the opposite outcome.
C · correct — Abort requires discarding transaction effects, though this record need not prove all undo is already complete.
D · incorrect — The record denotes abort handling rather than a read_item operation.
Question 142
Which of the following is NOT one of the four basic transaction operations on a database?
A. Read
B. Write
C. Sort
D. Delete
Hint / Module Reference (Where to Read)
Trace movement between program variables, buffers and disk, or the meaning of a recorded transaction event.
ADB07 distinguishes read_item and write_item and notes that inserts and deletes also modify data. Sorting is instead an operation used in query processing, not a primitive item update in this classification. The exact count four is a teaching convention, but the note directly supplies the relevant distinction that singles out Sort.
Evidence in the complete study note
read_item(X) locates X's page, brings it into a buffer if absent, and copies X into a program variable. write_item(X) copies the new program value into the buffered page, marking it dirty. Writing that page to disk can occur later according to the buffer policy; write_item is not inherently an immediate disk flush. Insert and delete also modify data; sorting is a query-processing operation rather than a primitive item update.ADB07
Choice by choice
A · incorrect — Reading is an explicitly defined primitive database item operation.
B · incorrect — Writing is an explicitly defined primitive database item modification operation.
C · correct — Sorting is a query-processing operation rather than the primitive item access/update category.
D · incorrect — Delete modifies stored data and belongs with the listed primitive data operations.
Question 143
The Isolation property of ACID ensures that concurrent transactions:
A. Always read the same data at the same time
B. Do not interfere with each other; each executes independently
C. Commit in the same order they started
D. Always complete successfully without any errors
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
Correct answer: B — Concurrent work is isolated from improper interference.
ADB06 defines isolation as controlling interference between concurrent transactions. B captures logical independence under the specified isolation guarantee; it does not mean transactions literally use separate hardware or never wait. Isolation does not require identical reads, start-order commits or universal successful completion. Strong serializable isolation makes behavior equivalent to some serial order.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
A serializable schedule is equivalent to some serial order; it may interleave transactions.ADB09
A serial schedule executes each transaction's operations consecutively.ADB09
Choice by choice
A · incorrect — Isolation does not require all transactions to read identical values at the same time.
B · correct — Isolation controls interference so concurrent transactions retain the promised logical execution semantics.
C · incorrect — Isolation does not require commit order to match transaction start order.
D · incorrect — Conflicts and failures can still cause aborts; isolation is not a success guarantee.
Question 144
In a simplified teaching sequence, write_item(X) updates a buffered page and then the buffer manager immediately flushes that page, after required WAL records are durable. What is the final physical step of this specified sequence?
A. Copy item X to program variable
B. Find the disk block address
C. Store the updated buffer block back to disk
D. Read the old value of X first
Hint / Module Reference (Where to Read)
Trace movement between program variables, buffers and disk, or the meaning of a recorded transaction event.
Correct answer: C — C. Store the updated buffer block back to disk
After X's program value has been copied into the buffered page, the specified sequence completes by writing the changed page back to disk. That is C. In ordinary buffering this flush may be deferred, but the question explicitly includes it. Locating a page or reading its old value occurs before or separately from the final physical output, and copying into a program variable describes reading.
Evidence in the complete study note
read_item(X) locates X's page, brings it into a buffer if absent, and copies X into a program variable. write_item(X) copies the new program value into the buffered page, marking it dirty. Writing that page to disk can occur later according to the buffer policy; write_item is not inherently an immediate disk flush. Insert and delete also modify data; sorting is a query-processing operation rather than a primitive item update.ADB07
A buffer can be changed before that log flush; WAL orders durable writes, not every CPU assignment.ADB27
To acknowledge a durable commit, force the transaction's required log records, including its commit record, to stable storage.ADB27
Choice by choice
A · incorrect — Copying into a program variable is a read operation.
B · incorrect — The page address is located before the final output.
C · correct — The explicitly included flush stores the updated buffered page on disk.
D · incorrect — Reading the old value is not the final physical output step.
Question 145
A transaction that encountered an error during execution and could not complete is in which state?
A. Aborted
B. Failed
C. Terminated
D. Partially Committed
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
ADB06 places a transaction in Failed when an error prevents successful execution. After rollback removes its effects it reaches Aborted. The stem reports the execution error but does not say rollback has completed, so Failed is the precise state. Partially committed follows successful final operation execution, not an error that prevents completion.
Evidence in the complete study note
Active means operations are executing. Partially committed means the last operation finished but durable commitment is not yet assured. Committed means commitment succeeded. A failure leads to Failed, then Aborted after rollback; a transaction may terminate or be restarted as a new attempt. Concurrency control primarily enforces isolation; recovery supports atomicity and durability. Transaction management is the broader subsystem, so both it and recovery management can be legitimate answers to broad responsibility questions.ADB06
Choice by choice
A · incorrect — Aborted requires completed rollback, which is not stated after the execution error.
B · correct — Failed is the immediate state when an error prevents completing the transaction.
C · incorrect — Termination is later lifecycle handling rather than the immediate error state.
D · incorrect — Partially committed follows completion of the last operation rather than failed execution.
Question 146
Transaction log records serve primarily for which purpose?
A. Speeding up query execution
B. Encrypting sensitive data
C. Enabling database recovery after failures by recording all changes
D. Enforcing access control permissions
Hint / Module Reference (Where to Read)
Trace movement between program variables, buffers and disk, or the meaning of a recorded transaction event.
Correct answer: C — Enable recovery by recording the information needed to undo or redo changes.
ADB07 records starts, updates, commits and aborts, with before-images for undo and after-images for redo. That makes recovery the primary purpose among the choices. Actual log formats may be physical or logical and need not record every read, so all changes should be understood as the change information required by the recovery design, not every operation indiscriminately.
Evidence in the complete study note
A conceptual log can contain START, UPDATE(T,X,old,new), COMMIT, ABORT and END records. Before-images support undo; after-images support redo. Real systems may use physical or logical records, and need not log every read. COMMIT records successful completion; an ABORT record can mark the start of rollback rather than prove every undo step is complete.ADB07
A user who can view but cannot change a record has restricted access privileges.ADB24
Authentication establishes identity; authorization/access control determines permitted actions.ADB24
Choice by choice
A · incorrect — The primary log purpose here is recovery rather than speeding query execution.
B · incorrect — Transaction logging does not inherently encrypt the stored sensitive data.
C · correct — Logged update and transaction-state information supports recovery of valid committed state after failures.
D · incorrect — Access privileges control authorization; transaction log records are not themselves that enforcement mechanism.
Question 147
Which processing type uses multiple CPUs to execute different transactions simultaneously, achieving higher throughput?
A. Sequential processing
B. Interleaved processing
C. Parallel processing
D. Batch processing
Hint / Module Reference (Where to Read)
Distinguish overlapping dependent operators, independent workers and reused cached objects.
ADB22 distinguishes concurrency by interleaving on one CPU from actual simultaneous work on multiple cores. The stem explicitly uses multiple CPUs to execute different transactions at the same time, making Parallel the correct classification. Batch describes grouping work, and sequential processing performs work in order; neither guarantees the stated simultaneous execution.
Evidence in the complete study note
Materialization stores an intermediate result before the next operator consumes it. Pipelining passes tuples as they become available, overlapping producer and consumer work and reducing temporary I/O. A sort is normally blocking; a hash join must build its hash table before probing can output, though later probe/output can stream. Pipelining does not require separate CPUs.ADB22
Interleaving on one CPU is concurrency; simultaneous cores provide parallelism.ADB22
Parallel execution uses multiple workers, for example partitioned scans/joins, independent subplans or operator pipelines.ADB22
Choice by choice
A · incorrect — Sequential processing does not execute different transactions simultaneously on multiple CPUs.
B · incorrect — Interleaving alternates execution and need not provide simultaneous CPU work.
C · correct — Multiple CPUs working simultaneously provide parallel execution.
D · incorrect — Batch describes grouped submission or handling, not necessarily simultaneous multi-CPU execution.
Question 148
Why are transactions important in database systems?
A. They make queries run faster
B. They ensure the database remains consistent and reliable through ACID properties
C. They allow the database to store more data
D. They simplify the SQL syntax for users
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
Correct answer: B — Transactions support consistent, reliable work through ACID guarantees.
ADB06 groups operations into a logical unit with all-or-none effects, invariant preservation, isolation and durable commits. These guarantees support consistent and reliable database behavior when application logic is correct. They do not inherently speed queries, expand storage capacity or simplify syntax. In particular a DBMS cannot make an incorrect business operation consistent merely by wrapping it in a transaction.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
Choice by choice
A · incorrect — Transactions can add coordination costs and do not inherently make queries faster.
B · correct — ACID guarantees support valid and recoverable logical work, provided business logic is correct.
C · incorrect — Transaction semantics do not increase the database's physical storage capacity.
D · incorrect — Grouping work atomically does not inherently simplify the SQL language for users.
Question 149
T1 reads balance=$500 and adds $100, planning to write $600. T2 reads the same $500, adds $200, and writes $700. T1 then writes $600. What problem occurred?
A. Dirty Read
B. Lost Update (Write-Write Problem)
C. Incorrect Summary Problem
D. Deadlock
Hint / Module Reference (Where to Read)
Trace which transaction wrote each value and whether an overwrite or mixed-version read occurs.
Correct answer: B — Lost update: T1 overwrites T2’s increment with its stale calculation.
Both transactions read 500. T2 writes 700 after adding 200, but T1 then writes its previously computed 600, erasing T2’s increment. A serial execution would produce 800. This matches the lost-update trace in ADB08; no waiting cycle or aggregate calculation is described, and reading an uncommitted value is not the stated cause.
Evidence in the complete study note
Lost update: T1 and T2 both read 500; T2 adds 200 and writes 700; T1 adds 100 to its stale value and writes 600. Correct serial execution yields 800. Dirty read: T2 reads a value written by uncommitted T1; if T1 aborts, dependent work must be reconsidered. Reading one's own changes is not a dirty read.ADB08
Deadlock requires a waiting cycle: T1 holds X and wants Y; T2 holds Y and wants X.ADB13
Incorrect summary mixes mutually inconsistent versions: a transfer moves 10 from A=100 to B=100; a sum reading old A=100 and new B=110 gets 210 instead of the invariant 200.ADB08
Choice by choice
A · incorrect — Neither transaction is described as reading the other's uncommitted write.
B · correct — T1's stale write of 600 overwrites T2's added 200; the correct combined result is 800.
C · incorrect — No aggregate over mutually inconsistent versions is being calculated.
D · incorrect — No circular waiting or blocked resource requests are present.
Question 150
A dirty read occurs when transaction T1 reads data that:
A. T1 itself wrote earlier in the same transaction
B. T2 modified but has NOT yet committed, and T2 may later abort
C. Was permanently deleted from the database
D. Was last read more than one hour ago
Hint / Module Reference (Where to Read)
Trace which transaction wrote each value and whether an overwrite or mixed-version read occurs.
Correct answer: B — Read another transaction’s uncommitted modification.
ADB08 defines dirty read as consuming a value written by an uncommitted other transaction. If that writer aborts, the dependent read used a value that should not survive. Reading one’s own changes is allowed and is not this anomaly; the age of a value or a completed permanent deletion does not define dirty reading.
Evidence in the complete study note
Lost update: T1 and T2 both read 500; T2 adds 200 and writes 700; T1 adds 100 to its stale value and writes 600. Correct serial execution yields 800. Dirty read: T2 reads a value written by uncommitted T1; if T1 aborts, dependent work must be reconsidered. Reading one's own changes is not a dirty read.ADB08
A phantom means repeating a predicate query finds a changed qualifying set after another transaction inserts, deletes or changes matching rows.ADB08
A single committed update from B=20 to B=50 does not alone prove an invalid summary of 90.ADB08
Choice by choice
A · incorrect — Reading changes made earlier by the same transaction is not a dirty read.
B · correct — The read depends on T2's uncommitted update, which may later need undo.
C · incorrect — Permanent deletion is not the defining uncommitted-value dependency.
D · incorrect — Elapsed time since a read does not determine whether the writer committed.
Question 151
Which lock type allows MULTIPLE transactions to simultaneously READ a data item?
A. Exclusive Lock
B. Write Lock
C. Shared Lock
D. Binary Lock
Hint / Module Reference (Where to Read)
Compare access permissions and same-item compatibility before granting a lock request.
ADB11 states that a shared lock permits reading and multiple shared holders can coexist on the same item. Exclusive/write locks conflict with another transaction’s shared or exclusive lock on that item. Thus shared locking provides the requested concurrent-reader behavior. A generic binary locked/unlocked label does not establish shared-reader compatibility in the supplied model.
Evidence in the complete study note
A shared (S) lock permits reading; multiple S locks on the same item can coexist. An exclusive (X) lock permits its holder to read and write and conflicts with another S or X on that item. Locks on different items do not conflict merely because one is exclusive. These are the basic lock-based rules; multiversion systems can permit additional snapshot-reader behavior.ADB11
Shared/exclusive locking adds modes so multiple shared readers can coexist while incompatible exclusive access is excluded.ADB-S04
One transaction may request a lock on an already locked item: it is granted if compatible, otherwise waits or aborts under the policy.ADB11
Choice by choice
A · incorrect — An exclusive lock conflicts with other transactions' read locks on the same item.
B · incorrect — A write/exclusive lock does not grant multiple independent readers the requested shared ownership.
C · correct — Shared locks are compatible with other shared locks, permitting multiple readers.
D · incorrect — The binary label does not provide the explicit S/S compatibility defined in the note.
Question 152
In Two-Phase Locking (2PL), a transaction can acquire new locks ONLY during which phase?
A. Shrinking Phase
B. Termination Phase
C. Growing Phase
D. Validation Phase
Hint / Module Reference (Where to Read)
Track the first lock release and the transaction-completion boundary.
Basic 2PL separates acquiring/upgrading locks from releasing/downgrading them. ADB12 permits acquisition only in the growing phase; after any release, no new lock may be acquired. Termination is a transaction boundary and validation belongs to a different control approach. The growing/shrinking rule is what distinguishes 2PL from unrestricted use of locks.
Evidence in the complete study note
Basic 2PL has a growing phase that acquires/upgrades locks and a shrinking phase that releases/downgrades them. Once any lock is released, no new lock may be acquired. The lock point is the instant the final lock is obtained. 2PL guarantees conflict serializability for well-formed transactions, but can deadlock and basic 2PL alone need not prevent cascading aborts.ADB12
Two-phase locking orders access to data; two-phase commit coordinates a distributed commit decision.ADB12
Optimistic/validation control reads and computes privately, validates its read/write sets against overlapping transactions, then publishes if validation succeeds; otherwise it retries.ADB16
Choice by choice
A · incorrect — The shrinking phase releases or downgrades locks and forbids new acquisitions.
B · incorrect — Termination is not the 2PL phase in which new locks are acquired.
C · correct — Growing permits acquiring and upgrading locks before any release begins.
D · incorrect — Validation is an optimistic-control stage, not the lock-acquisition phase of 2PL.
Question 153
What does Two-Phase Locking (2PL) guarantee that locking alone does NOT guarantee?
A. That no deadlocks will occur
B. Serializability — results equivalent to some serial execution
C. That all transactions complete without aborting
D. That every transaction reads the most current data
Hint / Module Reference (Where to Read)
Track the first lock release and the transaction-completion boundary.
Correct answer: B — Conflict serializability for well-formed transactions.
ADB12 explicitly guarantees conflict serializability for well-formed 2PL transactions. Locking without its phase discipline need not provide that ordering guarantee. Basic 2PL can still deadlock and may permit cascading aborts, so it does not guarantee successful completion. Nor does serializability require every read to observe the latest wall-clock update.
Evidence in the complete study note
Basic 2PL has a growing phase that acquires/upgrades locks and a shrinking phase that releases/downgrades them. Once any lock is released, no new lock may be acquired. The lock point is the instant the final lock is obtained. 2PL guarantees conflict serializability for well-formed transactions, but can deadlock and basic 2PL alone need not prevent cascading aborts.ADB12
Two-phase locking orders access to data; two-phase commit coordinates a distributed commit decision.ADB12
Strict 2PL retains exclusive locks until commit/abort.ADB12
Choice by choice
A · incorrect — Basic 2PL can deadlock, so deadlock freedom is not its general guarantee.
B · correct — The phase discipline guarantees conflict-serializable schedules for well-formed transactions.
C · incorrect — Deadlock resolution or failures can still abort transactions.
D · incorrect — Serializability does not require every read to see the newest wall-clock version.
Question 154
What is a deadlock in database systems?
A. A transaction that executes too slowly
B. A circular wait where each transaction holds a resource another needs, and none can proceed
C. A type of hardware failure causing data loss
D. When a transaction violates the ACID consistency property
Hint / Module Reference (Where to Read)
Distinguish a waiting cycle, a long wait and indefinite unfair postponement.
Correct answer: B — A circular wait among transactions holding resources needed by others.
ADB13 defines deadlock through a waiting cycle: each participant holds a resource another needs, so none can advance without an intervention. Slow work alone can still make progress and is not deadlock. Hardware loss and consistency violations are distinct problems that do not require a resource-wait cycle.
Evidence in the complete study note
Deadlock requires a waiting cycle: T1 holds X and wants Y; T2 holds Y and wants X. In a wait-for graph, an edge Ti→Tj means Ti waits for a lock held by Tj. A cycle indicates deadlock under the ordinary lock-wait model. Abort a suitable victim, undo its effects and release its locks; repeat if other cycles remain. Aborting every deadlocked transaction also breaks cycles but wastes more work.ADB13
A timeout aborts long waits but may mistake slow progress for deadlock.ADB13
Transaction failures include invalid operations, constraint violations and concurrency-control aborts.ADB26
Choice by choice
A · incorrect — A slow transaction may still make progress and need not participate in a wait cycle.
B · correct — Circular hold-and-wait prevents the involved transactions from progressing without intervention.
C · incorrect — Hardware failure is a different failure category and does not define a lock wait cycle.
D · incorrect — A consistency violation concerns invalid data states rather than necessarily circular waiting.
Question 155
In the Wait-Die deadlock prevention strategy, when a YOUNGER transaction needs a resource held by an OLDER transaction, the younger transaction:
A. Wounds (aborts) the older transaction
B. Waits for the older transaction to release the resource
C. Is immediately aborted and restarted
D. Receives the resource immediately due to higher priority
Hint / Module Reference (Where to Read)
Identify requester and holder ages before applying the protocol's action.
Correct answer: C — Abort the younger requester; retry with its original timestamp.
The requester is younger and the resource holder older. ADB14 says wait–die permits an older requester to wait but aborts a younger requester needing an older holder. C therefore gives the correct action. Restart should retain the original timestamp to improve relative priority; immediately aborted does not require an immediate successful retry or resource grant.
Evidence in the complete study note
A smaller timestamp means an older transaction. In wait–die, an older requester may wait for a younger holder; a younger requester needing an older holder aborts itself. In wound–wait, an older requester aborts the younger holder; a younger requester waits for the older holder. Always identify requester versus holder before applying the names.ADB14
Choice by choice
A · incorrect — Wait–die does not let the younger requester abort the older holder.
B · incorrect — In wait–die, the younger requester dies rather than waiting for the older holder.
C · correct — The younger requester is aborted and may restart using its original timestamp.
D · incorrect — The older holder retains priority; the younger requester does not receive the resource immediately.
Question 156
In the Wound-Wait strategy, when an OLDER transaction needs a resource held by a YOUNGER transaction:
A. The older transaction waits for the younger
B. The older transaction wounds (aborts) the younger transaction
C. Both transactions are simultaneously aborted
D. A deadlock detection algorithm runs
Hint / Module Reference (Where to Read)
Identify requester and holder ages before applying the protocol's action.
Correct answer: B — The older requester aborts the younger holder.
ADB14 distinguishes wound–wait from wait–die: an older requester wounds the younger holder, while a younger requester waits for an older holder. Here the requester is explicitly older, so B applies. The rule prevents waiting cycles through age-based action; it does not require detecting an existing deadlock first or aborting both participants.
Evidence in the complete study note
A smaller timestamp means an older transaction. In wait–die, an older requester may wait for a younger holder; a younger requester needing an older holder aborts itself. In wound–wait, an older requester aborts the younger holder; a younger requester waits for the older holder. Always identify requester versus holder before applying the names.ADB14
A cycle indicates deadlock under the ordinary lock-wait model.ADB13
A no-wait policy aborts immediately on an incompatible lock request and needs no age timestamp.ADB13
Choice by choice
A · incorrect — An older requester wounds the younger holder rather than waiting under wound–wait.
B · correct — The older requester causes the younger resource holder to abort and release its lock.
C · incorrect — The protocol aborts the younger holder, not both transactions.
D · incorrect — This is a prevention action based on ages, not a requirement to run cycle detection first.
Question 157
Optimistic concurrency control is BEST suited for environments where:
A. Transactions frequently write to the same data items
B. Write operations always conflict with read operations
C. Conflicts between transactions are rare (read-heavy workloads)
D. All transactions must complete in strict timestamp order
Hint / Module Reference (Where to Read)
Consider conflicts, private work and validation costs, or lock-management overhead.
Correct answer: C — Conflicts between transactions are rare (read-heavy workloads)
Optimistic concurrency control lets transactions read and compute privately, then validates overlapping read/write activity before publishing changes. When conflicts are rare, most work can commit without being discarded. Frequent competing writes or persistent read/write conflicts instead lead to costly retries. Strict timestamp completion describes a separate ordering constraint; it does not supply the low-conflict condition that makes optimistic execution suitable. Read-heavy activity is useful here only insofar as actual conflicts remain uncommon.
Evidence in the complete study note
Optimistic/validation control reads and computes privately, validates its read/write sets against overlapping transactions, then publishes if validation succeeds; otherwise it retries. It works well when conflicts are uncommon, but high contention wastes work on aborts.ADB16
Choice by choice
A · incorrect — Incorrect: frequent competing writes cause validation failures and discarded work.
B · incorrect — Incorrect: persistent read/write conflicts increase retry costs rather than favoring optimistic execution.
C · correct — Correct: rare conflicts allow most private work to validate and commit.
D · incorrect — Incorrect: a required completion order does not imply low contention; optimistic suitability depends on conflicts and retry cost.
Question 158
Lock upgrade refers to changing a lock from:
A. Exclusive → Shared
B. Shared → Exclusive
C. No lock → Shared
D. Exclusive → No lock
Hint / Module Reference (Where to Read)
Compare access permissions and same-item compatibility before granting a lock request.
ADB11 explicitly names S→X an upgrade and X→S a downgrade. The stronger exclusive lock permits writing and can be granted only after conflicting other holders leave. Acquiring a new shared lock is initial acquisition rather than conversion, while releasing an exclusive lock is unlocking. Thus B alone names a lock upgrade.
Evidence in the complete study note
A transaction must own the appropriate lock before access and can release only its own locks. S→X is an upgrade, possible only after conflicting other holders leave. X→S is a downgrade. The lock manager decides requests; the lock table stores granted locks and waiting requests. One transaction may request a lock on an already locked item: it is granted if compatible, otherwise waits or aborts under the policy. No double-locking is not a prohibition on shared readers.ADB11
Choice by choice
A · incorrect — Exclusive-to-shared weakens permissions and is a downgrade.
B · correct — Shared-to-exclusive strengthens the held lock to permit writing, subject to compatibility.
C · incorrect — No-lock-to-shared is initial acquisition, not conversion of an already held lock.
D · incorrect — Exclusive-to-no-lock is release rather than upgrade.
Question 159
Which trace demonstrates an incorrect-summary anomaly? The intended anomaly is a mixed-version aggregate, distinct from a nonrepeatable read.
A. Two transactions both delete the same record simultaneously
B. Initially A=B=100. During T2's transfer of 10 from A to B, T1 sums old A=100 and new B=110, returning 210 although the total-balance invariant is 200.
C. A transaction reads uncommitted data from another transaction
D. A transaction fails to acquire all necessary locks
Hint / Module Reference (Where to Read)
Trace which transaction wrote each value and whether an overwrite or mixed-version read occurs.
Correct answer: B — B. Initially A=B=100. During T2's transfer of 10 from A to B, T1 sums old A=100 and new B=110, returning 210 although the total-balance invariant is 200.
The transfer preserves the total balance at 200, but T1 combines the pre-transfer debit balance with the post-transfer credit balance. Its computed 100+110=210 is not a valid summary of a consistent state. This is an incorrect-summary anomaly. Dirty reading is defined by an uncommitted dependency, and lock failure or simultaneous deletion alone does not establish this mixed aggregate.
Evidence in the complete study note
Lost update: T1 and T2 both read 500; T2 adds 200 and writes 700; T1 adds 100 to its stale value and writes 600. Correct serial execution yields 800. Dirty read: T2 reads a value written by uncommitted T1; if T1 aborts, dependent work must be reconsidered. Reading one's own changes is not a dirty read.ADB08
Incorrect summary mixes mutually inconsistent versions: a transfer moves 10 from A=100 to B=100; a sum reading old A=100 and new B=110 gets 210 instead of the invariant 200.ADB08
A single committed update from B=20 to B=50 does not alone prove an invalid summary of 90.ADB08
Choice by choice
A · incorrect — Concurrent deletion alone does not specify an inconsistent aggregate.
B · correct — The mixed old/new balances yield 210 instead of the invariant 200.
C · incorrect — Reading another transaction's uncommitted value defines a dirty read.
D · incorrect — Failure to acquire locks concerns scheduling rather than this aggregate result.
Question 160
Starvation in a database system occurs when:
A. A transaction consumes excessive memory resources
B. A transaction is repeatedly bypassed by higher-priority transactions and never executes
C. A deadlock persists longer than the timeout period
D. The transaction log becomes completely full
Hint / Module Reference (Where to Read)
Distinguish a waiting cycle, a long wait and indefinite unfair postponement.
Correct answer: B — Indefinite postponement while other transactions keep progressing.
ADB13 defines starvation as indefinite postponement while other work proceeds, without requiring any waiting cycle. Repeatedly favoring higher-priority work can prevent a transaction from executing, matching B. Memory consumption, a full log or a long deadlock are different resource/failure conditions; none alone defines the fairness failure of starvation.
Evidence in the complete study note
Deadlock requires a waiting cycle: T1 holds X and wants Y; T2 holds Y and wants X. In a wait-for graph, an edge Ti→Tj means Ti waits for a lock held by Tj. A cycle indicates deadlock under the ordinary lock-wait model. Abort a suitable victim, undo its effects and release its locks; repeat if other cycles remain. Aborting every deadlocked transaction also breaks cycles but wastes more work.ADB13
Starvation is indefinite postponement while other work proceeds, without a required cycle.ADB13
Fair queues, sensible victim selection and retaining original transaction timestamps on retry reduce starvation; deadlock freedom alone does not prove fairness.ADB13
Choice by choice
A · incorrect — Excessive memory use is a resource problem rather than the definition of indefinite postponement.
B · correct — Repeated bypass by other transactions can indefinitely postpone this transaction while work continues.
C · incorrect — Deadlock requires a waiting cycle; starvation can occur without one.
D · incorrect — A full log can block work but does not itself define unfair indefinite postponement.
Question 161
Which concurrency control technique assigns a unique start-time value to each transaction and uses this to order execution and resolve conflicts?
A. Two-Phase Locking
B. Shadow Paging
C. Timestamp-Based Concurrency Control
D. Optimistic Concurrency Control
Hint / Module Reference (Where to Read)
Compare the transaction timestamp with the item's recorded read and write timestamps.
Correct answer: C — Timestamp-based concurrency control.
ADB15 assigns TS(T) to each transaction and compares it with item read_TS and write_TS values. Those tests reject operations that would violate timestamp order, so C matches the start-time ordering technique. 2PL orders locking, optimistic control validates private work, and shadow paging is a recovery/storage technique rather than this conflict-ordering protocol.
Evidence in the complete study note
Assign each transaction TS(T). For item X track read_TS(X), the largest timestamp of a successful reader, and write_TS(X), that of its latest successful writer. A read by T aborts if TS(T)<write_TS(X); otherwise it succeeds and updates read_TS. A write aborts if TS(T)<read_TS(X) or TS(T)<write_TS(X); otherwise it succeeds and sets write_TS. These tests preserve timestamp order.ADB15
Multiversion control keeps versions so a reader can use an appropriate snapshot.ADB16
Redo-only and NO-UNDO/REDO mean the same thing, so those cannot be separate unique answers.ADB29
Choice by choice
A · incorrect — 2PL orders lock acquisition and release rather than defining the timestamp validity tests.
B · incorrect — Shadow paging preserves page versions for recovery rather than ordering concurrent transaction operations by timestamp.
C · correct — Timestamp ordering compares transaction timestamps with item read/write timestamps to resolve conflicts.
D · incorrect — Optimistic control validates private read/write work rather than being defined by these timestamp-order tests.
Question 162
A well-formed transaction must follow which rule regarding locking?
A. Lock all items at the end of the transaction
B. Lock a data item BEFORE reading or writing it
C. Lock items only when modifying them, not when reading
D. Acquire all locks in alphabetical order by item name
Hint / Module Reference (Where to Read)
Compare access permissions and same-item compatibility before granting a lock request.
Correct answer: B — Acquire the appropriate lock before accessing the item.
ADB11 requires ownership of an appropriate lock before access and permits releasing only one’s own locks. Reads therefore need shared or suitable stronger locks in this basic model; writes need exclusive ownership. Alphabetical acquisition may be a separate prevention convention, but it is not the general well-formedness rule and locks cannot be postponed until after access.
Evidence in the complete study note
A shared (S) lock permits reading; multiple S locks on the same item can coexist. An exclusive (X) lock permits its holder to read and write and conflicts with another S or X on that item. Locks on different items do not conflict merely because one is exclusive. These are the basic lock-based rules; multiversion systems can permit additional snapshot-reader behavior.ADB11
A transaction must own the appropriate lock before access and can release only its own locks.ADB11
Aborting every deadlocked transaction also breaks cycles but wastes more work.ADB13
Choice by choice
A · incorrect — Locking only at the end leaves the earlier accesses unprotected.
B · correct — A well-formed transaction holds an appropriate lock before reading or writing an item.
C · incorrect — Basic lock-based access also requires an appropriate read lock, not only locks for writes.
D · incorrect — A fixed resource order can help prevention, but alphabetical order is not the general well-formedness definition.
Question 163
Lock timeout as a deadlock solution works by:
A. Detecting cycles in the wait-for graph
B. Building a priority queue of all waiting transactions
C. Automatically aborting a transaction that has waited too long for a lock
D. Locking all required data items before execution begins
Hint / Module Reference (Where to Read)
Distinguish a waiting cycle, a long wait and indefinite unfair postponement.
Correct answer: C — Abort a transaction after its lock wait exceeds the timeout.
ADB13 describes timeout as aborting long waits, while warning it may mistake slow progress for deadlock. C directly states that mechanism. Detecting cycles is a different method; acquiring all needed locks first is conservative locking; a priority queue alone neither defines a timeout nor guarantees elimination of waiting cycles.
Evidence in the complete study note
Deadlock requires a waiting cycle: T1 holds X and wants Y; T2 holds Y and wants X. In a wait-for graph, an edge Ti→Tj means Ti waits for a lock held by Tj. A cycle indicates deadlock under the ordinary lock-wait model. Abort a suitable victim, undo its effects and release its locks; repeat if other cycles remain. Aborting every deadlocked transaction also breaks cycles but wastes more work.ADB13
A timeout aborts long waits but may mistake slow progress for deadlock.ADB13
Conservative/static 2PL obtains all needed locks before execution and does not begin while only a subset is available, preventing hold-and-wait deadlock.ADB12
Choice by choice
A · incorrect — Cycle detection is graph-based deadlock detection rather than a wait-duration timeout.
B · incorrect — A priority queue alone does not enforce the stated wait-duration abort policy.
C · correct — The policy aborts work whose lock wait exceeds the chosen threshold.
D · incorrect — Obtaining all locks before execution is conservative locking, not timeout handling.
Question 164
What are the three phases of query processing in the correct order?
A. Optimization, Parsing, Evaluation
B. Parsing & Translation, Optimization, Evaluation
C. Evaluation, Translation, Optimization
D. Parsing, Evaluation, Optimization
Hint / Module Reference (Where to Read)
Separate query representation, strategy selection and execution.
Correct answer: B — Parsing/translation → optimization → evaluation.
ADB17 checks grammar and semantics, constructs a logical representation, chooses an equivalent efficient execution plan and evaluates it. B groups parsing and translation into the first phase and preserves that dependency order. Plans cannot sensibly be evaluated before construction and selection, and optimization does not precede understanding the query’s syntax and meaning.
Evidence in the complete study note
Parsing checks SQL grammar; semantic analysis resolves names, types and access permissions. Translation constructs a logical representation, typically relational algebra or a logical query tree. Optimization chooses equivalent operations, join order and physical access methods. Evaluation executes that plan and returns results. Not every implementation exposes exactly the same phase boundaries.ADB17
Query processing is the whole activity; selecting a suitable efficient strategy is optimization.ADB17
DDL is not a mandatory stage before each SELECT, so authentication→DDL→DML permutations do not describe the processing sequence.ADB17
Choice by choice
A · incorrect — Optimization cannot precede parsing and understanding the query in the stated pipeline.
B · correct — It preserves analysis/translation, plan optimization and then execution/evaluation.
C · incorrect — Evaluation cannot be the first phase before the query is represented and planned.
D · incorrect — It executes before selecting the optimized plan, reversing optimization and evaluation.
Question 165
During query translation, SQL is typically converted into which internal representation for processing?
A. Machine code instructions
B. Binary decision trees
C. Relational algebra expressions
D. Compressed byte code
Hint / Module Reference (Where to Read)
Separate query representation, strategy selection and execution.
Correct answer: C — Relational algebra expressions or a logical query tree.
ADB17 states that translation constructs a logical representation, typically relational algebra or a logical query tree. This allows equivalent rewrites and plan choice before physical execution. Machine code or bytecode may appear in particular execution implementations, but they are not the general logical translation target asked for here; a binary decision tree is also not the defined representation.
Evidence in the complete study note
Parsing checks SQL grammar; semantic analysis resolves names, types and access permissions. Translation constructs a logical representation, typically relational algebra or a logical query tree. Optimization chooses equivalent operations, join order and physical access methods. Evaluation executes that plan and returns results. Not every implementation exposes exactly the same phase boundaries.ADB17
A query tree has base relations at leaves and operators at internal nodes; a query graph emphasizes relations and predicates.ADB17
A physical execution plan adds scan/index choices and algorithms.ADB17
Choice by choice
A · incorrect — Machine code is not the logical relational representation described for query translation.
B · incorrect — A binary decision tree is not the note's general query-algebra representation.
C · correct — Relational algebra expresses the logical operators that can be rewritten and optimized.
D · incorrect — Compressed bytecode is not the stated general logical query representation.
Question 166
The query optimizer's primary goal is to:
A. Ensure queries return results in sorted order
B. Find an execution plan that minimizes the overall cost of retrieving data
C. Maximize the number of indexes used in the query
D. Ensure all tables are accessed in the order written in SQL
Hint / Module Reference (Where to Read)
Identify the resource objective, counts and unit costs before comparing execution strategies.
Correct answer: B — Choose a low-cost equivalent execution plan.
ADB19 estimates resource costs such as I/O, CPU, memory, temporary storage and communication for candidate plans. Optimization compares equivalent ways to return the same result and chooses a suitable low-cost strategy. It does not seek index count, force textual table order or impose sorting that the query never requested. The estimate need not prove a global real-world minimum.
Evidence in the complete study note
A cost model estimates resources for a candidate plan: page I/O, CPU, memory, temporary storage and network traffic. Catalog statistics include row/page counts, distinct values and distributions. Selection with fraction s predicts s×N rows. For non-NULL equijoin keys, a common estimate is |R|×|S|/max(V(R,k),V(S,k)), assuming uniform values and containment of the smaller distinct-value domain within the larger. Adjust counts for NULLs; if either non-NULL input is empty, the result is zero. Skew, correlations or incomplete domain overlap can invalidate this estimate; known disjoint domains give zero matches.ADB19
Avoid universal rules that joins always beat subqueries, functions can never use indexes or more indexes always improve performance.ADB23
Cost-based optimization estimates candidate plans instead of relying on rules alone.ADB18
Choice by choice
A · incorrect — Sorting is required only when query semantics need it, not as the universal optimizer goal.
B · correct — The optimizer seeks an efficient equivalent plan under the selected resource-cost model.
C · incorrect — More indexes used does not necessarily mean lower cost or better execution.
D · incorrect — Equivalent join reordering can reduce cost; SQL textual order need not dictate access order.
Question 167
In the textbook local rotating-disk cost model, data is not memory-resident and query execution is disk-bound; client/network and CPU costs are negligible by comparison. Which component dominates the estimated query cost?
A. CPU processing time
B. Disk I/O time (seek time + transfer time)
C. Memory allocation overhead
D. Network latency to the client
Hint / Module Reference (Where to Read)
Identify the resource objective, counts and unit costs before comparing execution strategies.
Correct answer: B — B. Disk I/O time (seek time + transfer time)
The specified disk-bound model estimates the cost of seeks and transferred blocks. Because CPU, memory-management and network costs are negligible by assumption, disk I/O dominates this query's estimated cost. This is a contextual model rather than a universal fact about all databases: memory-resident or distributed workloads can have different dominant resources. The answer follows the scenario's stated objective.
Evidence in the complete study note
For a simple rotating-disk model, cost = seeks×seek_time + transferred_blocks×block_transfer_time. Five seeks at 5 ms plus ten blocks at 1 ms cost 35 ms. Disk I/O often dominates textbook local-disk examples; CPU can dominate in-memory work and network can dominate distributed plans. State the assumed cost objective before ranking strategies.ADB19
Choice by choice
A · incorrect — CPU cost is negligible in the specified model.
B · correct — Seek and transfer time dominate the stated disk-bound workload.
C · incorrect — Memory-management overhead is negligible here.
D · incorrect — Client/network cost is negligible in this local scenario.
Question 168
In the disk I/O cost formula Cost = (S x tS) + (B x tT), what does 'B' represent?
A. Block size in bytes
B. Number of disk blocks transferred
C. Buffer pool size
D. Binary search operations
Hint / Module Reference (Where to Read)
Identify the resource objective, counts and unit costs before comparing execution strategies.
Correct answer: B — The number of transferred disk blocks.
ADB19 computes disk cost as seeks times seek duration plus transferred blocks times per-block transfer time. Therefore B in the formula is a block count, making B×tT the total transfer time. Block size, buffer capacity and search-operation count are different quantities and would not have the units required by this expression.
Evidence in the complete study note
For a simple rotating-disk model, cost = seeks×seek_time + transferred_blocks×block_transfer_time. Five seeks at 5 ms plus ten blocks at 1 ms cost 35 ms. Disk I/O often dominates textbook local-disk examples; CPU can dominate in-memory work and network can dominate distributed plans. State the assumed cost objective before ranking strategies.ADB19
Choice by choice
A · incorrect — Block size is bytes per block rather than the number multiplied by per-block transfer time.
B · correct — B counts transferred blocks, so B×tT gives their total transfer time.
C · incorrect — Buffer-pool capacity is not the transferred-block count in this formula.
D · incorrect — Binary-search operation count is not the disk-block transfer term.
Question 169
The UNDO operation in database recovery is used to:
A. Re-apply changes of committed transactions not yet written to disk
B. Create new checkpoint entries in the log
C. Reverse changes made by incomplete or aborted transactions
D. Transfer data from cache to permanent disk storage
Hint / Module Reference (Where to Read)
Track durable-write ordering and distinguish reversing incomplete work from installing logged effects.
Correct answer: C — Reverse incomplete or aborted transaction effects.
ADB27 uses undo to restore before-images of incomplete effects, usually traversing a transaction’s updates backward. This removes changes that must not survive. Reapplying committed changes is redo, checkpoint records bound recovery searches, and transferring dirty buffers to storage is page flushing. Only C describes undo rather than another recovery or storage operation.
Evidence in the complete study note
Undo restores before-images of incomplete effects, usually traversing that transaction's updates backward. Redo installs logged after-images when necessary, usually forward. Setting X to its logged after-value is idempotent; blindly replaying X=X+10 twice is not. In a simple undo/redo teaching model, undo losers and redo winners. ARIES has a different redo phase that repeats history before undoing losers.ADB27
A checkpoint records enough durable recovery state to avoid scanning all old history.ADB30
In a simple quiescent/checkpoint exercise, specified flushing may make earlier committed work unnecessary to redo.ADB30
Choice by choice
A · incorrect — Reapplying missing committed updates is redo rather than undo.
B · incorrect — Checkpoint creation records recovery state and is not reversal of incomplete effects.
C · correct — Undo restores old values to remove incomplete or aborted transaction changes.
D · incorrect — Writing a buffer to disk is flushing rather than restoring before-images.
Question 170
The REDO operation in database recovery is used to:
A. Abort all active transactions found in the log
B. Reverse changes made by failed transactions
C. Re-apply changes of committed transactions not yet written to disk
D. Create shadow copies of database pages
Hint / Module Reference (Where to Read)
Track durable-write ordering and distinguish reversing incomplete work from installing logged effects.
Correct answer: C — Reapply required logged after-images, including committed changes missing from pages.
ADB27 describes redo as installing logged after-images when necessary. In the simple teaching model it preserves committed work not yet on disk, matching C. ARIES may repeat history more broadly before loser undo, so C describes the ordinary purpose rather than every algorithm’s full phase. Aborting transactions and reversing failed changes are different actions.
Evidence in the complete study note
Undo restores before-images of incomplete effects, usually traversing that transaction's updates backward. Redo installs logged after-images when necessary, usually forward. Setting X to its logged after-value is idempotent; blindly replaying X=X+10 twice is not. In a simple undo/redo teaching model, undo losers and redo winners. ARIES has a different redo phase that repeats history before undoing losers.ADB27
Shadow/copy-on-write update writes a new location while preserving the committed version.ADB29
Shadow paging keeps an unchanged committed page map and a modified map; commit durably installs a new root/map pointer atomically after required pages are durable.ADB29
Choice by choice
A · incorrect — Aborting active transactions is not the operation of applying logged after-images.
B · incorrect — Reversing incomplete effects is undo rather than redo.
C · correct — Redo can restore committed changes whose data pages were not durable at the crash.
D · incorrect — Shadow-page creation is a separate copy-on-write technique rather than logged redo.
Question 171
What is the fundamental durable-write ordering rule of write-ahead logging? In B, 'actual data changes' means writing the corresponding changed data page to persistent storage, not modifying a volatile buffer.
A. Write data to disk before creating the log record
B. Log records must be written to stable storage BEFORE making the actual data changes
C. All transactions must commit before any log entries are written
D. Log records are written only after a system crash occurs
Hint / Module Reference (Where to Read)
Track durable-write ordering and distinguish reversing incomplete work from installing logged effects.
Correct answer: B — B. Log records must be written to stable storage BEFORE making the actual data changes
WAL requires the recovery log for an update to reach stable storage before its corresponding changed page is written to disk. A volatile buffer may already contain the change, so this rule does not order every CPU assignment after a log flush. A reverses the required durable order; C and D postpone logs until too late to support recovery of earlier persistent changes.
Evidence in the complete study note
Write-ahead logging requires the update log needed for recovery to reach stable storage before the corresponding changed data page reaches disk. To acknowledge a durable commit, force the transaction's required log records, including its commit record, to stable storage. A buffer can be changed before that log flush; WAL orders durable writes, not every CPU assignment.ADB27
Setting X to its logged after-value is idempotent; blindly replaying X=X+10 twice is not.ADB27
Redo installs logged after-images when necessary, usually forward.ADB27
Choice by choice
A · incorrect — Data-before-log reverses WAL's required durable ordering.
B · correct — With the defined durable-write scope, the log reaches stable storage first.
C · incorrect — Update logs may need to be durable before commit and before data flushing.
D · incorrect — Recovery requires logs that were durable before the crash.
Question 172
In a simple quiescent checkpoint exercise, all transactions have completed and the required log/data pages are flushed at the checkpoint. How does that checkpoint reduce later recovery search work?
A. Preventing all types of hardware failures
B. Encrypting log records for faster access
C. Providing a known consistent state so recovery can start from there instead of the log beginning
D. Automatically committing all active transactions periodically
Hint / Module Reference (Where to Read)
Identify which history a recovery boundary can safely exclude and whether active work spans it.
Correct answer: C — C. Providing a known consistent state so recovery can start from there instead of the log beginning
The quiescent checkpoint supplies a durable boundary for the simplified exercise. With no active transaction spanning it and the required pages flushed, recovery need not scan all earlier history to redo those completed changes. C captures that bounded search. Checkpointing does not prevent hardware failure, gain its benefit from encryption or automatically commit active transactions. Fuzzy checkpoints require different starting-point qualifications.
Evidence in the complete study note
A checkpoint records enough durable recovery state to avoid scanning all old history. In a simple quiescent/checkpoint exercise, specified flushing may make earlier committed work unnecessary to redo. Active transactions can span the checkpoint, so their relevant earlier updates can still need undo. Do not assume every transaction before a checkpoint needs no processing.ADB30
Recovery may start analysis at the checkpoint but redo from an earlier required log position.ADB30
A checkpoint is neither a full backup nor an automatic commit of active transactions.ADB30
Choice by choice
A · incorrect — Recording recovery state does not prevent hardware failures.
B · incorrect — Encryption is not the mechanism reducing log-search work.
C · correct — The specified durable checkpoint bounds the history needed in this exercise.
D · incorrect — Transactions have completed here; a checkpoint does not automatically commit active work.
Question 173
In deferred update (No-Undo) recovery, actual database modifications are written to disk:
A. Immediately as each SQL statement executes
B. Only AFTER the transaction successfully commits
C. When a checkpoint operation is triggered
D. At regular 5-minute intervals regardless of transaction state
Hint / Module Reference (Where to Read)
Separate update timing from update location and follow the commit boundary.
Correct answer: B — After successful commit, in the deferred-update model.
ADB29 keeps uncommitted changes outside the persistent database. Only committed effects may therefore be installed in its actual database pages, which is the property B expresses. This does not require every committed page to be flushed immediately. Checkpoints and periodic timers cannot independently authorize writing uncommitted changes while retaining the claimed No-Undo model.
Evidence in the complete study note
Deferred update keeps uncommitted changes outside the persistent database; in the usual no-force setting it is NO-UNDO/REDO. Redo-only and NO-UNDO/REDO mean the same thing, so those cannot be separate unique answers. Immediate update allows persistent changes before commit and therefore may require undo; it need not flush every write_item immediately.ADB29
Shadow/copy-on-write update writes a new location while preserving the committed version.ADB29
Shadow paging keeps an unchanged committed page map and a modified map; commit durably installs a new root/map pointer atomically after required pages are durable.ADB29
Choice by choice
A · incorrect — Immediate per-statement persistent writes can expose uncommitted effects and do not define deferred update.
B · correct — The model does not install uncommitted effects in the persistent database; committed changes can be written later.
C · incorrect — A checkpoint alone does not make an uncommitted transaction eligible for persistent installation.
D · incorrect — A fixed timer cannot ignore transaction commit status while preserving deferred-update semantics.
Question 174
Shadow paging recovery technique preserves the original database pages by:
A. Creating a backup on a separate server
B. Writing all modifications to NEW shadow pages, keeping originals intact until commit
C. Encrypting pages before any modification
D. Storing pages in sorted order for faster access
Hint / Module Reference (Where to Read)
Separate update timing from update location and follow the commit boundary.
Correct answer: B — Write changed data to new pages while preserving the committed page map.
ADB29 preserves the committed version and writes changed data at new locations. Shadow paging keeps the old page map plus a modified map; commit atomically installs the new durable root after needed pages are durable. B captures the copy-on-write preservation, although the unchanged old map is the shadow rather than the name of each newly modified page.
Evidence in the complete study note
In-place update overwrites the original location. Shadow/copy-on-write update writes a new location while preserving the committed version. Shadow paging keeps an unchanged committed page map and a modified map; commit durably installs a new root/map pointer atomically after required pages are durable. Before commit, a crash uses the old map. It is an alternative to conventional log-based recovery, with allocation, fragmentation and concurrency costs; it is not primarily a special action done only during recovery.ADB29
A full backup copies the database to separate storage; scheduled backups plus archived logs support restoration to a chosen recoverable point.ADB26
Rollback handles failed/uncommitted work; redo preserves committed work missing from pages; checkpoints reduce log-search work; backups provide another stored copy.ADB26
Choice by choice
A · incorrect — A separate-server backup is not the page-map copy-on-write mechanism of shadow paging.
B · correct — Changes use new pages, preserving the originals until the new committed map is safely installed.
C · incorrect — Encryption does not preserve the old page mapping or provide the stated rollback mechanism.
D · incorrect — Sorting pages is an access-layout choice rather than preserving committed copies.
Question 175
Which type of database failure is caused by accidental deletion of important data by a user?
A. Hardware failure
B. Natural disaster
C. Software error
D. Human error
Hint / Module Reference (Where to Read)
Identify what failed, what durable resources survive and which restoration action is required.
The described cause is an accidental user action, not faulty hardware, a natural event or a software defect. ADB26 treats human mistakes separately and notes they may need point-in-time restoration before a bad committed operation. Ordinary crash redo would otherwise preserve a committed deletion rather than recognize the user’s intention.
Evidence in the complete study note
Transaction failures include invalid operations, constraint violations and concurrency-control aborts. Enforcing concurrency can deliberately abort work; it does not prevent every transaction failure. A system crash loses volatile memory while disks may survive. Media failure damages persistent storage and normally requires restoring a backup, then replaying available archived logs. Human mistakes may require point-in-time restoration before the bad committed operation.ADB26
An accidental user deletion is human error even if the software executes the requested command correctly.ADB-S05
A software failure originates in erroneous program behavior, such as a logical programming defect, invalid operation or operating-system fault.ADB-S05
Choice by choice
A · incorrect — No hardware malfunction is stated; the cause is the user's mistaken deletion.
B · incorrect — A natural disaster is not the cause described in the prompt.
C · incorrect — The software may have executed the requested deletion correctly; the stated mistake belongs to the user.
D · correct — Accidental deletion by a user is a human error.
Question 176
The transaction log records 'before and after values' for data modifications primarily to support:
A. Query optimization
B. User access control
C. UNDO (restore old value) and REDO (apply new value) operations during recovery
D. Database compression and storage efficiency
Hint / Module Reference (Where to Read)
Track durable-write ordering and distinguish reversing incomplete work from installing logged effects.
Correct answer: C — Undo with old values and redo with logged new values.
ADB27 restores before-images when undoing incomplete work and installs after-images when redoing logged effects. Recording both values therefore supports reversal and reapplication after failure. Query costing uses catalog statistics, access control uses privileges, and compression is a different storage concern; none explains the specific recovery value pair as directly.
Evidence in the complete study note
Undo restores before-images of incomplete effects, usually traversing that transaction's updates backward. Redo installs logged after-images when necessary, usually forward. Setting X to its logged after-value is idempotent; blindly replaying X=X+10 twice is not. In a simple undo/redo teaching model, undo losers and redo winners. ARIES has a different redo phase that repeats history before undoing losers.ADB27
A cost model estimates resources for a candidate plan: page I/O, CPU, memory, temporary storage and network traffic.ADB19
Catalog statistics include row/page counts, distinct values and distributions.ADB19
Choice by choice
A · incorrect — Query optimization estimates plan cost rather than restoring old/new transaction values.
B · incorrect — Access control checks privileges rather than using before/after images to authorize users.
C · correct — Old values support reversal and new values support necessary reapplication during recovery.
D · incorrect — Recording two values can add storage overhead and is not primarily a compression mechanism.
Question 177
Which type of distributed database uses the SAME DBMS software at every site?
A. Heterogeneous distributed database
B. Homogeneous distributed database
C. Middleware-based database
D. Partially replicated database
Hint / Module Reference (Where to Read)
Separate coordinated network placement from engine compatibility and semantic integration.
Correct answer: B — Homogeneous distributed database.
ADB33 identifies homogeneous systems by compatible DBMS technology across sites. Using the same DBMS software fits that classification. Heterogeneous systems integrate differing engines, models or interfaces. Middleware describes integration architecture and partial replication describes placement of copies, so neither substitutes for the homogeneity classification asked for here.
Evidence in the complete study note
A distributed database contains logically related data across networked sites, managed as a coordinated system. A homogeneous system uses compatible DBMS technology across sites; a heterogeneous system integrates differing DBMSs, data models, schemas or interfaces. Different operating systems are possible but do not define the distinction. Same DBMS does not imply identical local schemas, automatic replication or immediate synchronization.ADB33
Full replication places the whole database at every relevant site; partial replication copies selected fragments to selected sites; nonredundant allocation stores each fragment once.ADB37
These describe communication responsibilities, not fragmentation types.ADB34
Choice by choice
A · incorrect — Heterogeneous systems integrate differing database technologies rather than the same DBMS throughout.
B · correct — Using the same DBMS at each site fits homogeneous distributed database technology.
C · incorrect — Middleware describes communication/integration responsibilities, not whether DBMS technology is the same.
D · incorrect — Partial replication describes where selected copies reside rather than which DBMS software sites use.
Question 178
Horizontal fragmentation divides a table based on:
A. Splitting columns into separate subsets
B. Splitting rows into subsets based on a condition
C. Combining two tables into one larger table
D. Replicating the table to multiple sites
Hint / Module Reference (Where to Read)
Identify whether rows or columns are split and reverse the split to reconstruct the original relation.
ADB36 defines horizontal fragmentation as selecting row subsets using predicates and reconstructing the relation by union. Column subsets belong to vertical fragmentation. Combining tables is not fragmentation, and copying a whole table is replication. Thus B identifies both the item being split and the rule used to decide fragment membership.
Evidence in the complete study note
Horizontal fragmentation selects row subsets by predicates; their union reconstructs the relation. Check completeness so every original row is represented and disjointness if no overlap is intended. Define an explicit fragment for NULL/other values when needed. Vertical fragmentation projects column subsets and repeats a key or row identifier so joining on it reconstructs the original rows without spurious matches.ADB36
Full replication places the whole database at every relevant site; partial replication copies selected fragments to selected sites; nonredundant allocation stores each fragment once.ADB37
Full replication does not inherently mean every copy is updated simultaneously; its synchronization rule must be stated.ADB37
Choice by choice
A · incorrect — Column splitting is vertical fragmentation rather than horizontal fragmentation.
B · correct — Horizontal fragments contain rows selected by stated predicates.
C · incorrect — Combining tables increases the combined relation rather than splitting one table into row subsets.
D · incorrect — Replicating copies preserves the whole table at more sites rather than partitioning its rows.
Question 179
In vertical fragmentation, why must the PRIMARY KEY be included in every fragment?
A. To speed up INSERT operations
B. To prevent duplicate rows
C. To enable reconstruction of the original table by joining fragments on the primary key
D. To ensure proper replication across sites
Hint / Module Reference (Where to Read)
Identify whether rows or columns are split and reverse the split to reconstruct the original relation.
Correct answer: C — A repeated key or row identifier lets vertical pieces join back losslessly.
ADB36 repeats a key or row identifier across vertical fragments so attributes from the same original row can be matched during reconstruction. C states that purpose. The general requirement is a suitable identifier, not necessarily the particular declared primary key if another row identifier is used. It is unrelated to mandatory replication or inherently faster inserts.
Evidence in the complete study note
Horizontal fragmentation selects row subsets by predicates; their union reconstructs the relation. Check completeness so every original row is represented and disjointness if no overlap is intended. Define an explicit fragment for NULL/other values when needed. Vertical fragmentation projects column subsets and repeats a key or row identifier so joining on it reconstructs the original rows without spurious matches.ADB36
Hybrid fragmentation combines row and column splits in either suitable order.ADB36
Reconstruct by reversing the operations: key joins for corresponding vertical pieces and unions for horizontal pieces.ADB36
Choice by choice
A · incorrect — Faster insertion is not why vertical fragments share a row identifier.
B · incorrect — Avoiding duplicate values is not sufficient to align column pieces from the same original row.
C · correct — The shared key identifies matching original rows when the column fragments are joined.
D · incorrect — Replication copies data and is distinct from reconstructing vertical column fragments.
Question 180
Which data replication scheme stores each data fragment at ONLY ONE site with NO copies?
A. Full replication
B. Partial replication
C. No replication (non-redundant allocation)
D. Hybrid replication
Hint / Module Reference (Where to Read)
Separate copy placement, propagation timing and update-maintenance cost.
Correct answer: C — No replication, or nonredundant allocation.
ADB37 explicitly calls storing each fragment once nonredundant allocation. Full replication places the whole database at every relevant site, while partial replication copies selected fragments to selected sites. A hybrid can mix strategies, but the stem specifies exactly one location and no copies for every fragment, so C is the precise classification.
Evidence in the complete study note
Full replication places the whole database at every relevant site; partial replication copies selected fragments to selected sites; nonredundant allocation stores each fragment once. Copies improve read locality and can improve availability but require storage and update propagation. They can also spread mistakes, so replication does not replace independent backups.ADB37
Full replication does not inherently mean every copy is updated simultaneously; its synchronization rule must be stated.ADB37
Snapshot replication transfers a copy at intervals; incremental replication transfers changes.ADB37
Choice by choice
A · incorrect — Full replication stores copies throughout the relevant sites rather than one copy per fragment.
B · incorrect — Partial replication still copies selected fragments and therefore does not mean no copies.
C · correct — Nonredundant allocation stores each fragment at one site without replicas.
D · incorrect — A hybrid can combine policies, but the stated universal no-copy arrangement is specifically nonreplicated.
Question 181
Transparency in distributed databases means that:
A. All data is encrypted and transparent to hackers
B. The distribution complexity is hidden from users who interact as if using a single database
C. Users can see all technical details of how data is distributed
D. All sites must use the same operating system
Hint / Module Reference (Where to Read)
Identify exactly which detail is hidden from the user.
Correct answer: B — Hide distribution details behind a single logical database experience.
ADB35 defines transparency through which distribution details are hidden: sites, fragment reconstruction or replica selection/maintenance. Together these can let clients work with a single logical database abstraction. B captures that broad meaning. Transparency is not encryption, disclosure of every technical detail or a requirement that operating systems be identical.
Evidence in the complete study note
Location transparency hides which site holds a named object. Fragmentation transparency hides the existence and reconstruction of fragments: users query the whole logical relation. Replication transparency hides which copy is used and how copies are maintained. The broad single-logical-database experience may involve all three.ADB35
Different operating systems are possible but do not define the distinction.ADB33
Encryption protects stored or transmitted content; integrity constraints restrict valid data; auditing records actions.ADB24
Choice by choice
A · incorrect — Encryption protects content; transparency here concerns hiding distribution complexity from legitimate users.
B · correct — Distribution details can be hidden so users work with one logical database abstraction.
C · incorrect — Transparency hides selected implementation details rather than exposing all of them.
D · incorrect — Operating-system uniformity does not define distribution transparency.
Question 182
Hybrid fragmentation combines which two fragmentation types?
A. Replication and Partitioning
B. Horizontal and Vertical fragmentation
C. Client-Server and Middleware architectures
D. Homogeneous and Heterogeneous distribution
Hint / Module Reference (Where to Read)
Identify whether rows or columns are split and reverse the split to reconstruct the original relation.
Correct answer: B — Horizontal and vertical fragmentation.
ADB36 defines hybrid fragmentation as combining row and column splits in a suitable order. Reconstructing reverses those operations using unions for horizontal pieces and key joins for corresponding vertical pieces. Replication, deployment architectures and DBMS homogeneity are different design dimensions rather than the two fragmentation types combined by hybrid fragmentation.
Evidence in the complete study note
Horizontal fragmentation selects row subsets by predicates; their union reconstructs the relation. Check completeness so every original row is represented and disjointness if no overlap is intended. Define an explicit fragment for NULL/other values when needed. Vertical fragmentation projects column subsets and repeats a key or row identifier so joining on it reconstructs the original rows without spurious matches.ADB36
Hybrid fragmentation combines row and column splits in either suitable order.ADB36
Reconstruct by reversing the operations: key joins for corresponding vertical pieces and unions for horizontal pieces.ADB36
Choice by choice
A · incorrect — Replication copies data and is not the second fragmentation dimension in the hybrid definition.
B · correct — Hybrid fragmentation combines horizontal row splits with vertical column splits.
C · incorrect — Client-server and middleware classify architectures rather than fragmentation operations.
D · incorrect — Homogeneous/heterogeneous classifies system technology compatibility rather than row/column splitting.
Question 183
With full replication and synchronous propagation before acknowledgment, which listed disadvantage specifically concerns maintaining copies during an update-heavy workload? Assume usable replicas can serve reads.
A. Data becomes unavailable when any site fails
B. Updates incur propagation and coordination overhead because changes must reach the required copies before acknowledgment.
C. It requires all sites to use the same DBMS software
D. Queries can only be answered from the primary site
Hint / Module Reference (Where to Read)
Separate copy placement, propagation timing and update-maintenance cost.
Correct answer: B — B. Updates incur propagation and coordination overhead because changes must reach the required copies before acknowledgment.
Full replication stores the entire database at each relevant site, so updates create propagation and coordination work. The stated synchronous policy places that work before acknowledgment, adding update latency in the update-heavy scenario. This is the disadvantage described by B. Usable copies can serve reads and improve availability; full replication neither implies identical DBMS software nor restricts queries to one primary site.
Evidence in the complete study note
Full replication places the whole database at every relevant site; partial replication copies selected fragments to selected sites; nonredundant allocation stores each fragment once. Copies improve read locality and can improve availability but require storage and update propagation. They can also spread mistakes, so replication does not replace independent backups.ADB37
Eager/synchronous replication coordinates propagation before the specified commit/acknowledgment point, increasing update latency.ADB37
Full replication does not inherently mean every copy is updated simultaneously; its synchronization rule must be stated.ADB37
Choice by choice
A · incorrect — Usable replicas can keep data available after one site fails.
B · correct — The stated synchronous policy adds propagation and coordination work before acknowledgment.
C · incorrect — Full replication is a copy-placement policy, not a requirement to standardize every DBMS.
D · incorrect — Usable replicas can serve reads in the stated scenario.
Question 184
What does ACID stand for in database transactions?
A. Atomicity, Concurrency, Isolation, Durability
B. Atomicity, Consistency, Isolation, Durability
C. Availability, Consistency, Integrity, Durability
D. Atomicity, Correctness, Isolation, Distribution
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
Correct answer: B — Atomicity, Consistency, Isolation, Durability.
ADB06 gives all-or-none effects, preservation of declared rules, controlled interference and persistence after failure as the four ACID properties. B names them correctly. The other options substitute concurrency, availability, integrity, correctness or distribution for the defined terms, changing the acronym rather than describing an equivalent expansion.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
Choice by choice
A · incorrect — Concurrency incorrectly replaces Consistency in the acronym.
B · correct — The expansion exactly matches the four ACID property names.
C · incorrect — Availability and Integrity replace Atomicity and Isolation, which are the defined terms.
D · incorrect — Correctness and Distribution do not replace Consistency and Durability in ACID.
Question 185
Which ACID property ensures that either ALL operations of a transaction complete or NONE do?
A. Consistency
B. Isolation
C. Atomicity
D. Durability
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
The requested property concerns whether partial transaction effects can survive. ADB06 assigns all-or-none application to atomicity. A correct transaction also preserves consistency, concurrency needs isolation and completed commits need durability, but those are different guarantees. C is therefore the property matching the explicit ALL or NONE condition.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
Choice by choice
A · incorrect — Consistency concerns preservation of integrity rules rather than the indivisible effect boundary itself.
B · incorrect — Isolation governs concurrent interference rather than all-or-none transaction effects.
C · correct — Atomicity makes the transaction effects succeed together or be removed together.
D · incorrect — Durability protects committed effects rather than defining partial-success handling.
Question 186
If A=5000 and B=8000, and 750 is transferred from A to B, what values satisfy the Consistency property?
A. A=4250, B=8750, sum=13000
B. A=4250, B=8000, sum=12250
C. A=5000, B=8750, sum=13750
D. A=5000, B=8000, sum=13000
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
Subtracting 750 from A gives 4250 and adding it to B gives 8750; their total remains 13000. A implements both the specified operation and its total-balance invariant. B and C apply only one side. D preserves the initial total but does not perform the requested transfer, so invariant preservation alone does not make it the successful-transfer result.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
Unchanged or reverse-transfer balances can also preserve that sum; only the requested debit/credit implements the specified successful transfer.ADB06
Both balances total 13,000.ADB06
Choice by choice
A · correct — It performs the 750 debit and credit and preserves the total of 13000.
B · incorrect — The debit occurs without its credit, reducing the total to 12250.
C · incorrect — The credit occurs without its debit, increasing the total to 13750.
D · incorrect — It preserves the starting total but leaves both accounts unchanged rather than performing the transfer.
Question 187
Which ACID property ensures concurrent transactions do not interfere with each other?
A. Atomicity
B. Durability
C. Consistency
D. Isolation
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
ADB06 assigns concurrent-interference control to isolation. That is the distinguishing phrase in the stem. Atomicity handles partial effects, durability handles committed persistence, and consistency means correct work preserves declared rules. Different isolation levels provide different detailed guarantees, so this classification does not imply that all implementations offer serializability by default.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
Choice by choice
A · incorrect — Atomicity governs partial effects within a transaction rather than concurrent interference.
B · incorrect — Durability concerns persistence of committed changes after failures.
C · incorrect — Consistency concerns declared integrity rules under correct business logic.
D · correct — Isolation controls interference among concurrently executing transactions.
Question 188
Which ACID property guarantees committed changes survive system crashes?
A. Atomicity
B. Consistency
C. Isolation
D. Durability
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
ADB06 defines durability by persistence of committed effects despite failure. Recovery information can provide this even when changed data pages were buffered at the instant of commit. All-or-none application, invariant preservation and concurrent isolation serve different transaction guarantees, so only durability matches survival of successful commits after a crash.
Evidence in the complete study note
A transaction is a logical unit of database work, including read-only work. Atomicity means all effects or none; consistency means correct work preserves integrity rules; isolation controls concurrent interference; durability preserves committed effects despite failure. A DBMS cannot automatically make incorrect business logic consistent.ADB06
Choice by choice
A · incorrect — Atomicity concerns all-or-none application rather than the persistence of completed commits.
B · incorrect — Consistency concerns validity of declared invariants rather than crash survival of commits.
C · incorrect — Isolation controls concurrent interactions rather than persistence after a crash.
D · correct — Durability preserves committed effects across system failures.
Question 189
A transaction that has executed all operations but NOT YET committed is in which state?
A. Active
B. Partially Committed
C. Committed
D. Failed
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
ADB06 places a transaction in Partially committed after its last operation finishes but before durable commitment is assured. The stem states exactly that boundary. Active means operations are still executing; Committed needs successful commit; Failed needs an error. Finishing the operation list alone does not establish the committed state.
Evidence in the complete study note
Active means operations are executing. Partially committed means the last operation finished but durable commitment is not yet assured. Committed means commitment succeeded. A failure leads to Failed, then Aborted after rollback; a transaction may terminate or be restarted as a new attempt. Concurrency control primarily enforces isolation; recovery supports atomicity and durability. Transaction management is the broader subsystem, so both it and recovery management can be legitimate answers to broad responsibility questions.ADB06
Choice by choice
A · incorrect — Active describes ongoing operation execution, not the interval after the last operation finishes.
B · correct — The last operation finished, but commitment is not yet assured.
C · incorrect — The stem explicitly says the transaction has not yet committed.
D · incorrect — No execution or commit failure is reported.
Question 190
In the Aborted state, what happens to all changes made by the transaction?
A. Changes are permanently saved
B. All changes are rolled back restoring the database to its pre-transaction state
C. The transaction waits for resources
D. The transaction restarts automatically
Hint / Module Reference (Where to Read)
Track the transaction's guarantee or lifecycle boundary described by the scenario.
Correct answer: B — Its effects have been rolled back; other committed transactions remain.
ADB06 reaches Aborted after rollback. Thus the aborted transaction’s own changes are removed rather than permanently saved. Restoring pre-transaction state must be understood relative to its effects, not erasing unrelated concurrent commits. A restart can be a new attempt, but is not automatic merely because the aborted state was reached.
Evidence in the complete study note
Active means operations are executing. Partially committed means the last operation finished but durable commitment is not yet assured. Committed means commitment succeeded. A failure leads to Failed, then Aborted after rollback; a transaction may terminate or be restarted as a new attempt. Concurrency control primarily enforces isolation; recovery supports atomicity and durability. Transaction management is the broader subsystem, so both it and recovery management can be legitimate answers to broad responsibility questions.ADB06
Undo restores before-images of incomplete effects, usually traversing that transaction's updates backward.ADB27
Write-ahead logging requires the update log needed for recovery to reach stable storage before the corresponding changed data page reaches disk.ADB27
Choice by choice
A · incorrect — Permanent saving is a committed outcome, not an aborted outcome.
B · correct — Rollback removes this transaction's effects while retaining other transactions' legitimate committed work.
C · incorrect — Waiting for a lock describes a blocked executing transaction rather than completed rollback.
D · incorrect — An aborted transaction may be retried, but automatic restart is not guaranteed.
Question 191
Which log record signals successful completion of transaction T?
A. [start_transaction, T]
B. [abort, T]
C. [write_item, T, X, old, new]
D. [commit, T]
Hint / Module Reference (Where to Read)
Trace movement between program variables, buffers and disk, or the meaning of a recorded transaction event.
ADB07 identifies COMMIT as the record of successful transaction completion. START begins work, UPDATE records a data change and ABORT marks failed termination/rollback handling. A data write by itself does not prove the enclosing transaction committed, and an abort record can begin rollback rather than confirm it has completed.
Evidence in the complete study note
A conceptual log can contain START, UPDATE(T,X,old,new), COMMIT, ABORT and END records. Before-images support undo; after-images support redo. Real systems may use physical or logical records, and need not log every read. COMMIT records successful completion; an ABORT record can mark the start of rollback rather than prove every undo step is complete.ADB07
Choice by choice
A · incorrect — The start record indicates beginning work rather than successful completion.
B · incorrect — Abort records failure/rollback handling instead of successful commitment.
C · incorrect — An update record describes one changed item, not successful transaction completion.
D · correct — The commit record signals successful completion of transaction T.
Question 192
The Read_item(X) command transfers data in which order?
A. Disk -> Program variable -> Buffer
B. Buffer -> Disk -> Program variable
C. Disk -> Buffer -> Program variable
D. Program variable -> Buffer -> Disk
Hint / Module Reference (Where to Read)
Trace movement between program variables, buffers and disk, or the meaning of a recorded transaction event.
Correct answer: C — Disk page → buffer → program variable.
ADB07 locates X’s page, loads it into a buffer if absent and copies the item from that buffer into the program variable. C gives that conceptual data movement order. A cached page may avoid the physical disk read, but the program still reads through the buffer. The reverse direction describes a write path rather than read_item.
Evidence in the complete study note
read_item(X) locates X's page, brings it into a buffer if absent, and copies X into a program variable. write_item(X) copies the new program value into the buffered page, marking it dirty. Writing that page to disk can occur later according to the buffer policy; write_item is not inherently an immediate disk flush. Insert and delete also modify data; sorting is a query-processing operation rather than a primitive item update.ADB07
Real systems may use physical or logical records, and need not log every read.ADB07
Before-images support undo; after-images support redo.ADB07
Choice by choice
A · incorrect — The page is buffered before the item is copied to the program variable.
B · incorrect — A read does not first send the buffer to disk before obtaining the variable value.
C · correct — The page enters the buffer if needed, then its item is copied into the program variable.
D · incorrect — Program-variable-to-buffer-to-disk describes writing, not reading into a variable.
Question 193
In a multi-user system, which mechanisms does the DBMS use to manage concurrent access?
A. Only backup systems
B. Locks, timestamps, and scheduling algorithms
C. Physical user separation
D. Only file system permissions
Hint / Module Reference (Where to Read)
Compare access permissions and same-item compatibility before granting a lock request.
Correct answer: B — Locks, timestamp rules and transaction scheduling.
ADB11 defines compatible shared/exclusive locks and ADB15 orders conflicting operations with timestamp tests. These are actual concurrency-control mechanisms that constrain transaction schedules. Backups provide copies for recovery, and file permissions determine access rather than safe interleavings. Physical separation of people also does not coordinate their concurrent database operations.
Evidence in the complete study note
A shared (S) lock permits reading; multiple S locks on the same item can coexist. An exclusive (X) lock permits its holder to read and write and conflicts with another S or X on that item. Locks on different items do not conflict merely because one is exclusive. These are the basic lock-based rules; multiversion systems can permit additional snapshot-reader behavior.ADB11
Transaction failures include invalid operations, constraint violations and concurrency-control aborts.ADB26
Two operations conflict when they belong to different transactions, access the same item and at least one writes.ADB09
Choice by choice
A · incorrect — Backups support restoration rather than scheduling safe concurrent reads and writes.
B · correct — Locks and timestamp rules control conflicting accesses and permissible schedules.
C · incorrect — Separating users physically does not coordinate their concurrent operations on shared database items.
D · incorrect — File permissions alone do not enforce isolation among authorized concurrent transactions.
Question 194
The Lost Update problem is also called:
A. Read-Read Problem
B. Write-Write Problem
C. Read-Write Problem
D. Phantom Read Problem
Hint / Module Reference (Where to Read)
Trace which transaction wrote each value and whether an overwrite or mixed-version read occurs.
Correct answer: B — Write-write problem, as shorthand for a lost-update overwrite.
ADB08 illustrates both transactions reading 500, one writing 700 and the other later overwriting it with stale 600. The conflict is between their writes, which explains the offered write-write shorthand. This is not a claim that every pair of writes loses an update: the stale calculation and overwrite are essential. Read/read and phantom behavior describe different situations.
Evidence in the complete study note
Lost update: T1 and T2 both read 500; T2 adds 200 and writes 700; T1 adds 100 to its stale value and writes 600. Correct serial execution yields 800. Dirty read: T2 reads a value written by uncommitted T1; if T1 aborts, dependent work must be reconsidered. Reading one's own changes is not a dirty read.ADB08
Choice by choice
A · incorrect — Two reads alone do not overwrite and lose an update.
B · correct — The stale write overwrites another write, motivating this write-write shorthand.
C · incorrect — The defining loss is the later stale overwrite, not merely an arbitrary read/write pair.
D · incorrect — A phantom changes a predicate query's qualifying row set rather than overwriting one balance.
Question 195
A Dirty Read occurs when:
A. A transaction reads only committed data
B. A transaction reads data modified by another uncommitted transaction
C. Two transactions read the same record simultaneously
D. A transaction writes invalid format data
Hint / Module Reference (Where to Read)
Trace which transaction wrote each value and whether an overwrite or mixed-version read occurs.
Correct answer: B — Reading another transaction’s uncommitted data.
ADB08 defines dirty read through another transaction’s uncommitted write. The value can disappear if its writer aborts, leaving dependent work based on invalid data. Reading committed data or two readers accessing one item is not inherently dirty. Invalid-format writes concern data validity rather than whether the read-from transaction has committed.
Evidence in the complete study note
Lost update: T1 and T2 both read 500; T2 adds 200 and writes 700; T1 adds 100 to its stale value and writes 600. Correct serial execution yields 800. Dirty read: T2 reads a value written by uncommitted T1; if T1 aborts, dependent work must be reconsidered. Reading one's own changes is not a dirty read.ADB08
Choice by choice
A · incorrect — Reading only committed values avoids the defining uncommitted-writer dependency.
B · correct — The read consumes another transaction's modification before that writer commits.
C · incorrect — Multiple readers alone do not create an uncommitted data dependency.
D · incorrect — Invalid format concerns validation or integrity rather than the definition of a dirty read.
Question 196
Which trace demonstrates an incorrect-summary anomaly, meaning an aggregate that mixes mutually inconsistent versions? Treat nonrepeatable read as a separate anomaly.
A. A transaction reads data after another commits
B. Initially A=B=100. T2 transfers 10 from A to B while T1 reads old A=100 and new B=110, reporting 210 rather than the invariant total 200.
C. Two transactions delete the same record
D. A transaction reads its own uncommitted data
Hint / Module Reference (Where to Read)
Trace which transaction wrote each value and whether an overwrite or mixed-version read occurs.
Correct answer: B — B. Initially A=B=100. T2 transfers 10 from A to B while T1 reads old A=100 and new B=110, reporting 210 rather than the invariant total 200.
The transfer subtracts and adds the same amount, preserving a total of 200. T1 instead adds an old A balance to a new B balance and obtains 210, combining versions that do not form a consistent summary. That is the incorrect-summary anomaly. Reading committed data, deleting a record or reading one's own uncommitted changes alone does not establish this aggregate defect.
Evidence in the complete study note
Lost update: T1 and T2 both read 500; T2 adds 200 and writes 700; T1 adds 100 to its stale value and writes 600. Correct serial execution yields 800. Dirty read: T2 reads a value written by uncommitted T1; if T1 aborts, dependent work must be reconsidered. Reading one's own changes is not a dirty read.ADB08
Incorrect summary mixes mutually inconsistent versions: a transfer moves 10 from A=100 to B=100; a sum reading old A=100 and new B=110 gets 210 instead of the invariant 200.ADB08
A single committed update from B=20 to B=50 does not alone prove an invalid summary of 90.ADB08
Choice by choice
A · incorrect — A read after another commit is not by itself an inconsistent aggregate.
B · correct — 100+110=210 mixes versions and violates the preserved total 200.
C · incorrect — Concurrent deletions alone do not show this summary anomaly.
D · incorrect — Reading one's own changes is not the stated mixed-version aggregate.
Question 197
A Shared Lock allows:
A. Only one transaction to read an item
B. Multiple transactions to read simultaneously but not write
C. One transaction to read and write exclusively
D. Multiple transactions to write simultaneously
Hint / Module Reference (Where to Read)
Compare access permissions and same-item compatibility before granting a lock request.
Correct answer: B — Compatible readers, with no writing under a shared lock.
ADB11 permits multiple shared locks on one item, each for reading. Writing requires a suitable exclusive lock, which conflicts with other shared holders. Thus B describes shared-lock behavior in the basic lock-based model. Exclusive read/write ownership and multiple simultaneous writers are not shared locking; multiversion snapshot rules are a separate model.
Evidence in the complete study note
A shared (S) lock permits reading; multiple S locks on the same item can coexist. An exclusive (X) lock permits its holder to read and write and conflicts with another S or X on that item. Locks on different items do not conflict merely because one is exclusive. These are the basic lock-based rules; multiversion systems can permit additional snapshot-reader behavior.ADB11
One transaction may request a lock on an already locked item: it is granted if compatible, otherwise waits or aborts under the policy.ADB11
No double-locking is not a prohibition on shared readers.ADB11
Choice by choice
A · incorrect — More than one transaction may hold compatible shared read locks on the same item.
B · correct — Shared locks permit multiple readers but do not authorize writes under that lock mode.
C · incorrect — Sole read/write ownership describes an exclusive lock.
D · incorrect — Shared locks do not allow multiple transactions to write simultaneously.
Question 198
An Exclusive Lock allows:
A. Multiple transactions to read concurrently
B. Multiple transactions to write simultaneously
C. Only one transaction to both read and write, blocking all other access
D. Multiple readers and one writer simultaneously
Hint / Module Reference (Where to Read)
Compare access permissions and same-item compatibility before granting a lock request.
Correct answer: C — The exclusive holder may read and write; conflicting access to that item is blocked.
ADB11 permits an exclusive lock holder to read and write and makes that item’s X lock conflict with another S or X. C is correct in this basic model. Blocking all other access means conflicting access to the same item, not the entire database; locks on other items remain independent and multiversion systems can have additional snapshot-reader behavior.
Evidence in the complete study note
A shared (S) lock permits reading; multiple S locks on the same item can coexist. An exclusive (X) lock permits its holder to read and write and conflicts with another S or X on that item. Locks on different items do not conflict merely because one is exclusive. These are the basic lock-based rules; multiversion systems can permit additional snapshot-reader behavior.ADB11
One transaction may request a lock on an already locked item: it is granted if compatible, otherwise waits or aborts under the policy.ADB11
S→X is an upgrade, possible only after conflicting other holders leave.ADB11
Choice by choice
A · incorrect — Other transactions' shared reads conflict with an exclusive lock on the same item in this model.
B · incorrect — Two exclusive writers cannot hold compatible locks on the same item.
C · correct — One holder can read/write the locked item while incompatible same-item accesses wait or abort.
D · incorrect — Concurrent same-item shared readers conflict with this exclusive writer under the basic rules.
Question 199
In Two-Phase Locking (2PL), during the Growing Phase a transaction:
A. Releases locks only
B. Acquires locks without releasing any
C. Acquires and releases locks freely
D. Makes no lock requests
Hint / Module Reference (Where to Read)
Track the first lock release and the transaction-completion boundary.
Correct answer: B — Acquire or upgrade locks without releasing them.
ADB12 defines growing as the acquisition/upgrade phase and shrinking as the release/downgrade phase. Once any lock is released, no further acquisition is allowed. B therefore matches growing. Freely mixing acquisition and release would abandon the two-phase discipline that guarantees conflict serializability for well-formed transactions.
Evidence in the complete study note
Basic 2PL has a growing phase that acquires/upgrades locks and a shrinking phase that releases/downgrades them. Once any lock is released, no new lock may be acquired. The lock point is the instant the final lock is obtained. 2PL guarantees conflict serializability for well-formed transactions, but can deadlock and basic 2PL alone need not prevent cascading aborts.ADB12
Two-phase locking orders access to data; two-phase commit coordinates a distributed commit decision.ADB12
Conservative/static 2PL obtains all needed locks before execution and does not begin while only a subset is available, preventing hold-and-wait deadlock.ADB12
Choice by choice
A · incorrect — Releasing locks belongs to shrinking rather than growing.
B · correct — Growing acquires or upgrades locks before any release occurs.
C · incorrect — Freely acquiring after release violates the defining 2PL phase restriction.
D · incorrect — Growing is precisely the phase in which lock requests may be made.
Question 200
Which concurrency control technique uses start-times to order transaction execution?
A. Locking
B. Optimistic Concurrency Control
C. Timestamp-based Concurrency Control
D. Shadow Paging
Hint / Module Reference (Where to Read)
Compare the transaction timestamp with the item's recorded read and write timestamps.
Correct answer: C — Timestamp-based concurrency control.
ADB15 assigns each transaction a timestamp and checks reads and writes against item read/write timestamps. These checks preserve timestamp order and can abort stale operations. Locking instead uses ownership compatibility, optimistic control validates private work and shadow paging concerns recovery page maps. Only C names the start-time ordering technique described.
Evidence in the complete study note
Assign each transaction TS(T). For item X track read_TS(X), the largest timestamp of a successful reader, and write_TS(X), that of its latest successful writer. A read by T aborts if TS(T)<write_TS(X); otherwise it succeeds and updates read_TS. A write aborts if TS(T)<read_TS(X) or TS(T)<write_TS(X); otherwise it succeeds and sets write_TS. These tests preserve timestamp order.ADB15
Optimistic/validation control reads and computes privately, validates its read/write sets against overlapping transactions, then publishes if validation succeeds; otherwise it retries.ADB16
Shadow/copy-on-write update writes a new location while preserving the committed version.ADB29
Choice by choice
A · incorrect — Locking uses lock compatibility and protocols rather than being defined by transaction start-time ordering.
B · incorrect — Optimistic control validates private read/write sets rather than applying these timestamp-order tests.
C · correct — Timestamp-based control orders valid conflicting operations using transaction timestamps.
D · incorrect — Shadow paging preserves committed page maps for recovery rather than ordering concurrent operations.
Question 201
Optimistic Concurrency Control (OCC) is best suited for:
A. High-conflict write-heavy workloads
B. Read-heavy workloads where conflicts are rare
C. Single-user database systems
D. Distributed transactions only
Hint / Module Reference (Where to Read)
Consider conflicts, private work and validation costs, or lock-management overhead.
Correct answer: B — Read-heavy, low-conflict workloads.
ADB16 computes privately, validates and publishes, retrying failed validation. Low-conflict workloads let most work succeed; high write contention wastes computation on aborts. B states that criterion explicitly. Single-user systems do not need intertransaction conflict control, and OCC is not restricted to distributed transactions. Actual conflict frequency matters more than the read-heavy label alone.
Evidence in the complete study note
Optimistic/validation control reads and computes privately, validates its read/write sets against overlapping transactions, then publishes if validation succeeds; otherwise it retries. It works well when conflicts are uncommon, but high contention wastes work on aborts. Multiversion control keeps versions so a reader can use an appropriate snapshot. Snapshot isolation is not automatically serializable: concurrent writes to different rows can violate a shared invariant.ADB16
Choice by choice
A · incorrect — High conflicts cause validation failures and repeated wasted work.
B · correct — Rare conflicts allow optimistic private work to validate successfully with few retries.
C · incorrect — A single-user setting lacks the concurrent conflict problem motivating this choice.
D · incorrect — Optimistic control is not limited to distributed transaction environments.
Question 202
Deadlock occurs when:
A. One transaction waits for a resource held by no one
B. Two or more transactions each wait for resources held by the other in a circular chain
C. A transaction commits too slowly
D. Multiple transactions read identical data
Hint / Module Reference (Where to Read)
Distinguish a waiting cycle, a long wait and indefinite unfair postponement.
Correct answer: B — A circular resource-wait chain.
ADB13 requires a waiting cycle: participants hold resources needed by others and cannot make progress without a victim release or another intervention. Waiting for an unheld resource does not state such a cycle. Slow commit and simultaneous reads can occur without deadlock; compatible shared readers in particular need not block one another.
Evidence in the complete study note
Deadlock requires a waiting cycle: T1 holds X and wants Y; T2 holds Y and wants X. In a wait-for graph, an edge Ti→Tj means Ti waits for a lock held by Tj. A cycle indicates deadlock under the ordinary lock-wait model. Abort a suitable victim, undo its effects and release its locks; repeat if other cycles remain. Aborting every deadlocked transaction also breaks cycles but wastes more work.ADB13
A timeout aborts long waits but may mistake slow progress for deadlock.ADB13
No double-locking is not a prohibition on shared readers.ADB11
Choice by choice
A · incorrect — A resource held by no one does not create the stated circular hold-and-wait dependency.
B · correct — Each participant waits for another holder in a cycle, preventing independent progress.
C · incorrect — A slow commit may still progress and need not form a waiting cycle.
D · incorrect — Compatible shared reads of the same data do not inherently deadlock.
Question 203
In the Wait-Die protocol, when a YOUNGER transaction requests a resource held by an OLDER transaction:
A. The younger transaction dies (aborts)
B. The older transaction dies
C. The younger transaction waits
D. Both transactions abort
Hint / Module Reference (Where to Read)
Identify requester and holder ages before applying the protocol's action.
The requester is younger and the holder older. ADB14 makes that requester die in wait–die; only an older requester may wait for a younger holder. On retry the aborted transaction retains its original timestamp. Wounding a holder belongs to a different protocol, and both participants need not be aborted.
Evidence in the complete study note
A smaller timestamp means an older transaction. In wait–die, an older requester may wait for a younger holder; a younger requester needing an older holder aborts itself. In wound–wait, an older requester aborts the younger holder; a younger requester waits for the older holder. Always identify requester versus holder before applying the names.ADB14
Retain the original timestamp when restarting an aborted transaction so it gains relative priority as new transactions arrive.ADB14
In wait–die, an older transaction may wait for a younger one; a younger transaction requesting a resource held by an older one aborts.ADB14
Choice by choice
A · correct — Wait–die aborts the younger requester when the needed resource is held by an older transaction.
B · incorrect — The older holder keeps its resource; this protocol does not kill it for a younger requester.
C · incorrect — Waiting is allowed for an older requester facing a younger holder, not this case.
D · incorrect — The policy aborts the younger requester, not both participants.
Question 204
In the Wound-Wait protocol, when an OLDER transaction requests a resource held by a YOUNGER transaction:
A. The older transaction waits
B. The younger is wounded (aborted), allowing the older to proceed
C. Both transactions are aborted
D. A new transaction takes over
Hint / Module Reference (Where to Read)
Identify requester and holder ages before applying the protocol's action.
Correct answer: B — Abort the younger holder so the older requester can proceed.
ADB14 says an older requester wounds a younger holder, while a younger requester waits. B is therefore the rule for the given ages. The protocol uses priority to prevent cycles rather than arbitrarily aborting both or handing the work to a new transaction. Any retry should preserve the original timestamp to reduce starvation.
Evidence in the complete study note
A smaller timestamp means an older transaction. In wait–die, an older requester may wait for a younger holder; a younger requester needing an older holder aborts itself. In wound–wait, an older requester aborts the younger holder; a younger requester waits for the older holder. Always identify requester versus holder before applying the names.ADB14
Retain the original timestamp when restarting an aborted transaction so it gains relative priority as new transactions arrive.ADB14
In wait–die, an older transaction may wait for a younger one; a younger transaction requesting a resource held by an older one aborts.ADB14
Choice by choice
A · incorrect — An older requester wounds the younger holder rather than waiting under this policy.
B · correct — The younger holder is aborted, releasing the resource for the older requester.
C · incorrect — Aborting both is unnecessary and is not the wound–wait rule.
D · incorrect — No replacement transaction takes over; the older requester continues after the conflicting holder releases.
Question 205
Lock Timeout as a deadlock solution works by:
A. Building and analyzing wait-for graphs
B. Aborting any transaction that waits longer than a specified time limit
C. Locking all items before a transaction starts
D. Assigning age-based priorities
Hint / Module Reference (Where to Read)
Distinguish a waiting cycle, a long wait and indefinite unfair postponement.
Correct answer: B — Abort a transaction whose wait exceeds the configured limit.
ADB13 defines timeout by excessive wait duration and warns it can mistake slow progress for deadlock. B matches that mechanism. Wait-for graphs detect cycles, conservative locking obtains all locks before execution and age priorities define wait–die/wound–wait decisions. Those are distinct policies rather than implementations of a time limit.
Evidence in the complete study note
Deadlock requires a waiting cycle: T1 holds X and wants Y; T2 holds Y and wants X. In a wait-for graph, an edge Ti→Tj means Ti waits for a lock held by Tj. A cycle indicates deadlock under the ordinary lock-wait model. Abort a suitable victim, undo its effects and release its locks; repeat if other cycles remain. Aborting every deadlocked transaction also breaks cycles but wastes more work.ADB13
A timeout aborts long waits but may mistake slow progress for deadlock.ADB13
Conservative/static 2PL obtains all needed locks before execution and does not begin while only a subset is available, preventing hold-and-wait deadlock.ADB12
Choice by choice
A · incorrect — Graph analysis detects cycles rather than deciding from elapsed lock-wait time.
B · correct — Timeout aborts a waiting transaction after its configured wait limit is exceeded.
C · incorrect — Preacquiring all locks is conservative locking rather than timeout handling.
D · incorrect — Age-based priorities define other prevention policies, not an elapsed-wait threshold.
Question 206
A schedule is serializable if:
A. All transactions execute one after another with no overlap
B. Its concurrent execution produces the same result as some serial execution of those transactions
C. All transactions commit at the same time
D. No two transactions access the same table
Hint / Module Reference (Where to Read)
Distinguish transaction ordering, conflict conditions and full execution equivalence.
Correct answer: B — Equivalent behavior to some serial execution, even if interleaved.
ADB09 defines a serializable schedule as equivalent to a serial order while permitting interleaving. B matches when same result means the required full transaction equivalence, not an accidental equality of one final value. A describes a serial schedule, which is a special case but not the general definition. Commit simultaneity and disjoint tables are not required.
Evidence in the complete study note
A serial schedule executes each transaction's operations consecutively. A serializable schedule is equivalent to some serial order; it may interleave transactions. Equality of one final value is insufficient evidence because different executions can accidentally coincide. Two operations conflict when they belong to different transactions, access the same item and at least one writes. Read/read pairs do not conflict.ADB09
Choice by choice
A · incorrect — This defines serial execution, a stricter special case that omits permitted serializable interleavings.
B · correct — Serializable behavior is equivalent to some serial ordering; coincidental equality of one value alone is insufficient.
C · incorrect — Simultaneous commits do not establish the required transaction-execution equivalence.
D · incorrect — Serializable transactions may access the same tables if their operations are correctly ordered.
Question 207
Lock Upgrade refers to:
A. Releasing an exclusive lock to a shared lock
B. Converting a shared (read) lock to an exclusive (write) lock
C. Removing all locks at commit
D. Granting a lock to multiple transactions
Hint / Module Reference (Where to Read)
Compare access permissions and same-item compatibility before granting a lock request.
ADB11 identifies S→X as an upgrade, possible only after incompatible other holders leave. It strengthens an existing read lock to permit writing. X→S is a downgrade, releasing all locks is transaction cleanup and granting compatible readers is shared acquisition. Only B names the requested conversion of a held lock.
Evidence in the complete study note
A transaction must own the appropriate lock before access and can release only its own locks. S→X is an upgrade, possible only after conflicting other holders leave. X→S is a downgrade. The lock manager decides requests; the lock table stores granted locks and waiting requests. One transaction may request a lock on an already locked item: it is granted if compatible, otherwise waits or aborts under the policy. No double-locking is not a prohibition on shared readers.ADB11
Choice by choice
A · incorrect — Exclusive-to-shared is a downgrade rather than an upgrade.
B · correct — Shared-to-exclusive strengthens the lock to permit writing when compatibility allows.
C · incorrect — Removing locks is release rather than conversion to a stronger mode.
D · incorrect — Granting compatible shared readers is not the upgrade of one existing lock.
Question 208
Starvation in concurrency control means:
A. A transaction holding too many locks
B. A transaction is perpetually delayed and never executes due to other transactions always taking priority
C. The database runs out of storage space
D. A transaction commits before completing all operations
Hint / Module Reference (Where to Read)
Distinguish a waiting cycle, a long wait and indefinite unfair postponement.
Correct answer: B — Indefinite postponement caused by repeated priority to other work.
ADB13 defines starvation as indefinite postponement while other transactions progress, without requiring a deadlock cycle. B states that repeated bypass precisely. Holding many locks can create contention but does not itself prove starvation. Storage exhaustion and premature commit are different failures, so neither supplies the scheduling-fairness definition.
Evidence in the complete study note
Fixed resource ordering and conservative 2PL prevent cycles. A no-wait policy aborts immediately on an incompatible lock request and needs no age timestamp. A timeout aborts long waits but may mistake slow progress for deadlock. Starvation is indefinite postponement while other work proceeds, without a required cycle. Fair queues, sensible victim selection and retaining original transaction timestamps on retry reduce starvation; deadlock freedom alone does not prove fairness.ADB13
Choice by choice
A · incorrect — Holding many locks describes resource use rather than being perpetually denied execution.
B · correct — Repeated priority to others can indefinitely postpone the transaction while other work continues.
C · incorrect — Running out of storage is a capacity failure rather than the defined scheduling starvation.
D · incorrect — Premature commit concerns transaction correctness rather than indefinite postponement.
Question 209
The first step in SQL query processing is:
A. Optimization
B. Evaluation
C. Parsing and Translation
D. Result Delivery
Hint / Module Reference (Where to Read)
Separate query representation, strategy selection and execution.
ADB17 first checks syntax and semantics and constructs a logical query representation, before optimization and evaluation. C groups those initial activities. Result delivery follows execution, while optimization needs an understood query to compare valid plans. The phase grouping is conventional, but the ordering dependency is explicit in the complete note.
Evidence in the complete study note
Parsing checks SQL grammar; semantic analysis resolves names, types and access permissions. Translation constructs a logical representation, typically relational algebra or a logical query tree. Optimization chooses equivalent operations, join order and physical access methods. Evaluation executes that plan and returns results. Not every implementation exposes exactly the same phase boundaries.ADB17
Query processing is the whole activity; selecting a suitable efficient strategy is optimization.ADB17
DDL is not a mandatory stage before each SELECT, so authentication→DDL→DML permutations do not describe the processing sequence.ADB17
Choice by choice
A · incorrect — Optimization follows understanding and representing the query rather than preceding it.
B · incorrect — Evaluation executes a selected plan and is not the first processing activity.
C · correct — Parsing/translation establishes query meaning and logical representation before optimization.
D · incorrect — Results can be delivered only after the required evaluation produces them.
Question 210
SQL queries are internally translated into:
A. Machine code
B. Relational algebra
C. Python scripts
D. XML documents
Hint / Module Reference (Where to Read)
Separate query representation, strategy selection and execution.
ADB17 translates a query into relational algebra or a logical query tree for equivalent rewrites and plan selection. B names that logical representation. A particular engine may later compile execution code, but machine code, Python scripts and XML documents are not the general logical translation target taught here. Logical meaning precedes implementation-specific execution choices.
Evidence in the complete study note
Parsing checks SQL grammar; semantic analysis resolves names, types and access permissions. Translation constructs a logical representation, typically relational algebra or a logical query tree. Optimization chooses equivalent operations, join order and physical access methods. Evaluation executes that plan and returns results. Not every implementation exposes exactly the same phase boundaries.ADB17
A physical execution plan adds scan/index choices and algorithms.ADB17
A query tree has base relations at leaves and operators at internal nodes; a query graph emphasizes relations and predicates.ADB17
Choice by choice
A · incorrect — Machine code may be an implementation detail but is not the stated logical translation target.
B · correct — Relational algebra represents the logical operations used for rewriting and planning.
C · incorrect — Python scripts are not the general internal representation specified here.
D · incorrect — XML documents are not the defined general relational query representation.
Question 211
What does query optimization aim to minimize?
A. The number of tables referenced
B. The overall cost (time and resources) of executing the query
C. The number of join operations
D. The character length of the SQL query
Hint / Module Reference (Where to Read)
Identify the resource objective, counts and unit costs before comparing execution strategies.
Correct answer: B — Overall execution cost under the chosen resource model.
ADB19 measures candidate plans using I/O, CPU, memory, temporary storage and network traffic. The aim is a suitable low-cost equivalent plan, not syntactic brevity or indiscriminate removal of joins and tables. Required tables and semantics must be preserved; even fewer operations do not necessarily imply lower runtime or resource cost.
Evidence in the complete study note
A cost model estimates resources for a candidate plan: page I/O, CPU, memory, temporary storage and network traffic. Catalog statistics include row/page counts, distinct values and distributions. Selection with fraction s predicts s×N rows. For non-NULL equijoin keys, a common estimate is |R|×|S|/max(V(R,k),V(S,k)), assuming uniform values and containment of the smaller distinct-value domain within the larger. Adjust counts for NULLs; if either non-NULL input is empty, the result is zero. Skew, correlations or incomplete domain overlap can invalidate this estimate; known disjoint domains give zero matches.ADB19
Disk I/O often dominates textbook local-disk examples; CPU can dominate in-memory work and network can dominate distributed plans.ADB19
For a simple rotating-disk model, cost = seeks×seek_time + transferred_blocks×block_transfer_time.ADB19
Choice by choice
A · incorrect — Required tables cannot be discarded merely to reduce the table count.
B · correct — Optimization compares equivalent plans using estimated time and resource costs.
C · incorrect — Join count alone ignores access paths, cardinalities and the cost of each join.
D · incorrect — Short SQL text does not necessarily produce a cheaper execution plan.
Question 212
The dominant cost factor in query execution is typically:
A. CPU processing time
B. Network transmission time
C. Disk I/O time (seek + transfer)
D. Memory allocation time
Hint / Module Reference (Where to Read)
Identify the resource objective, counts and unit costs before comparing execution strategies.
Correct answer: C — Disk I/O in the conventional local-disk query-cost model.
ADB19 says disk I/O often dominates textbook local-disk examples, matching the question’s typical teaching model. C includes both seek and transfer work. This is a contextual tendency, not a universal claim: CPU can dominate in-memory work and communication can dominate distributed plans. The answer should retain that local-disk qualification rather than extend it to every system.
Evidence in the complete study note
For a simple rotating-disk model, cost = seeks×seek_time + transferred_blocks×block_transfer_time. Five seeks at 5 ms plus ten blocks at 1 ms cost 35 ms. Disk I/O often dominates textbook local-disk examples; CPU can dominate in-memory work and network can dominate distributed plans. State the assumed cost objective before ranking strategies.ADB19
Choice by choice
A · incorrect — CPU can dominate in-memory work but is not the typical local-disk-model answer.
B · incorrect — Network cost can dominate distributed plans rather than the conventional local-disk model.
C · correct — Seek and transfer I/O often dominate the conventional disk-based cost model.
D · incorrect — Memory allocation is not the dominant term specified for the textbook local-disk model.
Question 213
If a query requires 5 seeks (tS=5ms each) and transfers 10 blocks (tT=1ms each), what is the disk I/O cost?
A. 50ms
B. 35ms
C. 15ms
D. 25ms
Hint / Module Reference (Where to Read)
Identify the resource objective, counts and unit costs before comparing execution strategies.
ADB19 adds seeks times seek duration to blocks times per-block transfer duration. Five seeks cost 5×5=25 ms and ten block transfers cost 10×1=10 ms. Their sum is 35 ms. The terms must be added, not multiplied together or considered separately; this is the supplied simple disk model rather than a measured universal query runtime.
Evidence in the complete study note
For a simple rotating-disk model, cost = seeks×seek_time + transferred_blocks×block_transfer_time. Five seeks at 5 ms plus ten blocks at 1 ms cost 35 ms. Disk I/O often dominates textbook local-disk examples; CPU can dominate in-memory work and network can dominate distributed plans. State the assumed cost objective before ranking strategies.ADB19
Choice by choice
A · incorrect — Fifty does not equal the sum of 25 ms seeking and 10 ms transfer.
B · correct — Five times five plus ten times one gives 35 ms.
C · incorrect — Fifteen omits the correct five-seek multiplication.
D · incorrect — Twenty-five counts only seek time and omits ten milliseconds of transfer.
Question 214
Database recovery restores the database after failure to:
A. Its state at database creation
B. A consistent and correct state, undoing incomplete transactions and reapplying committed ones
C. The state at the last user login
D. An empty state for fresh start
Hint / Module Reference (Where to Read)
Identify what failed, what durable resources survive and which restoration action is required.
Correct answer: B — A valid recovered state preserving durable commits and removing incomplete effects.
ADB26 defines restart recovery by preserving durable commits and removing incomplete effects after failure. B matches the simple undo/redo teaching model. The detailed work depends on buffer policy and algorithm; not every committed update needs physical redo if already installed. Recovery does not reset the database to creation, last login or an empty replacement state.
Evidence in the complete study note
Transaction failures include invalid operations, constraint violations and concurrency-control aborts. Enforcing concurrency can deliberately abort work; it does not prevent every transaction failure. A system crash loses volatile memory while disks may survive. Media failure damages persistent storage and normally requires restoring a backup, then replaying available archived logs. Human mistakes may require point-in-time restoration before the bad committed operation.ADB26
Restart recovery restores a valid database after a crash while preserving durable commits and removing incomplete effects.ADB26
In a simple undo/redo teaching model, undo losers and redo winners.ADB27
Choice by choice
A · incorrect — Returning to creation would discard legitimate later durable commits.
B · correct — Recovery preserves committed work and removes incomplete effects to restore valid state.
C · incorrect — User login time is not the transaction recovery boundary.
D · incorrect — Emptying the database would destroy committed data rather than recover it.
Question 215
Which type of failure occurs when DBMS software bugs cause unexpected behavior?
A. Hardware Failure
B. Natural Disaster
C. Software Error
D. Network Failure
Hint / Module Reference (Where to Read)
Classify the stated cause rather than inferring it from the observed symptom.
The stem directly identifies DBMS software bugs as the cause. That is a software error rather than failed hardware, a natural event or broken communication. ADB26 separates transaction/system/media failure concerns so the recovery resource can be chosen appropriately; an observed crash alone would not identify its cause, but this question explicitly supplies it.
Evidence in the complete study note
A software failure originates in erroneous program behavior, such as a logical programming defect, invalid operation or operating-system fault. A hardware failure originates in malfunctioning physical equipment; loss of disk contents is media failure. An accidental user deletion is human error even if the software executes the requested command correctly. Physical catastrophes such as fire can also cause failure. The observed symptom of a crash does not alone identify which cause occurred.ADB-S05
Generic recovery-manager terminology should not be mistaken for an exact MySQL component specification.ADB26
Enforcing concurrency can deliberately abort work; it does not prevent every transaction failure.ADB26
Choice by choice
A · incorrect — The stated cause is faulty software behavior rather than a malfunctioning hardware component.
B · incorrect — No natural event is described; the prompt names a software bug.
C · correct — Unexpected behavior caused by DBMS bugs is a software error.
D · incorrect — No failed network connection is given as the cause of the behavior.
Question 216
What does WAL require? In B, 'corresponding data changes' specifically means durable output of the changed data page; volatile buffer changes may occur earlier.
A. Data written to disk before the log record
B. Log records written to stable storage BEFORE corresponding data changes
C. Logs written only after transaction commits
D. Checkpoints before every write operation
Hint / Module Reference (Where to Read)
Track durable-write ordering and distinguish reversing incomplete work from installing logged effects.
Correct answer: B — B. Log records written to stable storage BEFORE corresponding data changes
The update log needed for recovery must be stable before the changed data page becomes persistent. B states that order under the question's explicit durable-write meaning. The buffer can be modified earlier. Data-before-log reverses the rule, waiting until after commit is not the required logging policy and checkpointing before every write is not a WAL requirement.
Evidence in the complete study note
Write-ahead logging requires the update log needed for recovery to reach stable storage before the corresponding changed data page reaches disk. To acknowledge a durable commit, force the transaction's required log records, including its commit record, to stable storage. A buffer can be changed before that log flush; WAL orders durable writes, not every CPU assignment.ADB27
Setting X to its logged after-value is idempotent; blindly replaying X=X+10 twice is not.ADB27
Redo installs logged after-images when necessary, usually forward.ADB27
Choice by choice
A · incorrect — The durable log must precede the changed page, not follow it.
B · correct — With the stated scope, stable logs precede corresponding durable page output.
C · incorrect — Update logging is not deferred until after commit.
D · incorrect — WAL does not require a checkpoint for every write.
Question 217
The Redo operation during recovery:
A. Reverses incomplete transactions
B. Re-applies committed transactions whose changes were not yet persisted to disk
C. Creates new checkpoints
D. Locks data items during the recovery process
Hint / Module Reference (Where to Read)
Track durable-write ordering and distinguish reversing incomplete work from installing logged effects.
Correct answer: B — Reapply missing logged committed effects in the simple undo/redo model.
ADB27 uses redo to install logged after-images when necessary. B describes preserving committed work missing from disk pages in the simple teaching model. It does not mean re-running application side effects or blindly adding an amount twice. ARIES has a broader repeat-history phase before undo, but that does not make reversal, checkpoint creation or lock acquisition the definition of redo.
Evidence in the complete study note
Undo restores before-images of incomplete effects, usually traversing that transaction's updates backward. Redo installs logged after-images when necessary, usually forward. Setting X to its logged after-value is idempotent; blindly replaying X=X+10 twice is not. In a simple undo/redo teaching model, undo losers and redo winners. ARIES has a different redo phase that repeats history before undoing losers.ADB27
In a simple quiescent/checkpoint exercise, specified flushing may make earlier committed work unnecessary to redo.ADB30
Recovery may start analysis at the checkpoint but redo from an earlier required log position.ADB30
Choice by choice
A · incorrect — Reversing incomplete changes is undo rather than redo.
B · correct — Redo installs required logged effects missing from data pages, preserving committed work in the simple model.
C · incorrect — Checkpoint creation records recovery state rather than reapplying update after-images.
D · incorrect — Locking may coordinate access but is not the operation of logged redo.
Question 218
The Undo operation during recovery:
A. Re-applies committed transactions
B. Reverses changes of incomplete/aborted transactions using before-images from the log
C. Writes the WAL to stable storage
D. Creates shadow page copies
Hint / Module Reference (Where to Read)
Track durable-write ordering and distinguish reversing incomplete work from installing logged effects.
Correct answer: B — Restore before-images of incomplete or aborted work.
ADB27 restores before-images when undoing incomplete effects, normally following that transaction’s updates backward. B states both the affected work and the supporting log information. Applying committed after-images is redo, forcing logs is a WAL durability action, and copying pages is part of a different update/recovery approach. None is the reversal operation itself.
Evidence in the complete study note
Undo restores before-images of incomplete effects, usually traversing that transaction's updates backward. Redo installs logged after-images when necessary, usually forward. Setting X to its logged after-value is idempotent; blindly replaying X=X+10 twice is not. In a simple undo/redo teaching model, undo losers and redo winners. ARIES has a different redo phase that repeats history before undoing losers.ADB27
It is an alternative to conventional log-based recovery, with allocation, fragmentation and concurrency costs; it is not primarily a special action done only during recovery.ADB29
Shadow/copy-on-write update writes a new location while preserving the committed version.ADB29
Choice by choice
A · incorrect — Reapplying committed effects is the redo purpose in the simple model.
B · correct — Undo restores logged old values to remove incomplete or aborted transaction effects.
C · incorrect — Forcing the log supports durable ordering but does not itself reverse data changes.
D · incorrect — Shadow-page copies preserve versions instead of defining logged before-image undo.
Question 219
Shadow Paging recovery technique works by:
A. Using timestamps to sequence recovery
B. Writing changes to shadow (copy) pages, keeping originals intact, then swapping on commit
C. Using transaction logs for all rollbacks
D. Taking checkpoints every transaction
Hint / Module Reference (Where to Read)
Separate update timing from update location and follow the commit boundary.
Correct answer: B — Copy changed pages and atomically install the new durable map on commit.
ADB29 keeps the committed page map unchanged while modifications use new locations and a modified map. Commit durably installs the new root/map pointer after required pages are durable. B captures that preservation-and-switch mechanism. Timestamp ordering, mandatory per-transaction checkpoints and universal log-based rollback are not the defining shadow-paging steps.
Evidence in the complete study note
In-place update overwrites the original location. Shadow/copy-on-write update writes a new location while preserving the committed version. Shadow paging keeps an unchanged committed page map and a modified map; commit durably installs a new root/map pointer atomically after required pages are durable. Before commit, a crash uses the old map. It is an alternative to conventional log-based recovery, with allocation, fragmentation and concurrency costs; it is not primarily a special action done only during recovery.ADB29
Do not assume every transaction before a checkpoint needs no processing.ADB30
A checkpoint records enough durable recovery state to avoid scanning all old history.ADB30
Choice by choice
A · incorrect — Timestamp ordering controls concurrent operations rather than defining shadow page-map replacement.
B · correct — Copy-on-write preserves originals and commit safely installs the new page map.
C · incorrect — Shadow paging is an alternative to conventional log-based recovery, not mandatory logged rollback for all changes.
D · incorrect — A checkpoint every transaction is not the mechanism that preserves the old page map.
Question 220
A checkpoint in database recovery helps because:
A. It stops all transactions
B. Recovery can start from the checkpoint state rather than processing the entire log from the beginning
C. It creates a full database backup copy
D. It grants exclusive locks to all active transactions
Hint / Module Reference (Where to Read)
Identify which history a recovery boundary can safely exclude and whether active work spans it.
Correct answer: B — A checkpoint supplies recovery state that avoids scanning all historical logs from the beginning.
ADB30 uses durable checkpoint state to avoid processing all old history. B describes that benefit if start means entering recovery analysis, not assuming every earlier update is irrelevant. Active transactions can span a checkpoint and fuzzy-checkpoint redo may require earlier log positions. A checkpoint is not a full backup, universal stop or lock grant.
Evidence in the complete study note
A checkpoint records enough durable recovery state to avoid scanning all old history. In a simple quiescent/checkpoint exercise, specified flushing may make earlier committed work unnecessary to redo. Active transactions can span the checkpoint, so their relevant earlier updates can still need undo. Do not assume every transaction before a checkpoint needs no processing.ADB30
Recovery may start analysis at the checkpoint but redo from an earlier required log position.ADB30
A checkpoint is neither a full backup nor an automatic commit of active transactions.ADB30
Choice by choice
A · incorrect — Fuzzy checkpoints permit ongoing transactions, so stopping all work is not required.
B · correct — Recorded recovery state provides a bounded starting point, while earlier necessary undo/redo must still be considered.
C · incorrect — A checkpoint stores recovery metadata and is not a full separate database backup.
D · incorrect — Checkpointing does not grant every active transaction exclusive locks.
Question 221
Under conventional deferred update with no-force, which recovery classifications describe the policy? Select all that apply. Interpret NO UNDO / REDO as 'no undo, redo required'.
A. UNDO/REDO
B. UNDO only
C. REDO only
D. NO UNDO / REDO
Hint / Module Reference (Where to Read)
Separate update timing from update location and follow the commit boundary.
Correct answers (select all): C, D — C. REDO only; D. NO UNDO / REDO
Because uncommitted changes have not reached the persistent database, crash recovery does not need to undo them there. No-force permits committed changes to remain uninstalled, so redo may be required. REDO only and NO UNDO / REDO express the same classification under the stated notation. Undo-only and undo/redo both assert an undo requirement that this deferred model avoids.
Evidence in the complete study note
Deferred update keeps uncommitted changes outside the persistent database; in the usual no-force setting it is NO-UNDO/REDO. Redo-only and NO-UNDO/REDO mean the same thing, so those cannot be separate unique answers. Immediate update allows persistent changes before commit and therefore may require undo; it need not flush every write_item immediately.ADB29
Force writes a transaction's updated data pages at commit; no-force permits committed dirty pages to remain buffered, hence recovery may need redo.ADB28
Steal permits a page with uncommitted changes to be written to disk; hence recovery may need undo.ADB28
Choice by choice
A · incorrect — Usual deferred update does not require undo of uncommitted persistent database changes.
B · incorrect — Undo only gives the opposite recovery requirement from the usual deferred no-force model.
C · correct — REDO only is the usual deferred-update recovery classification.
D · correct — NO UNDO/REDO is another spelling of the same redo-only requirement.
Question 222
Which data update strategy writes changes to a SEPARATE disk location preserving the original?
A. Immediate Update
B. Deferred Update
C. Shadow Update
D. In-Place Update
Hint / Module Reference (Where to Read)
Separate update timing from update location and follow the commit boundary.
Correct answer: C — Shadow update, or copy-on-write.
ADB29 distinguishes where an update is written from when it may become persistent. Shadow/copy-on-write uses a new location while retaining the committed version, matching C. Immediate and deferred classify commit timing, not necessarily a separate location. In-place update overwrites the original location, which directly contradicts the preservation condition in the stem.
Evidence in the complete study note
In-place update overwrites the original location. Shadow/copy-on-write update writes a new location while preserving the committed version. Shadow paging keeps an unchanged committed page map and a modified map; commit durably installs a new root/map pointer atomically after required pages are durable. Before commit, a crash uses the old map. It is an alternative to conventional log-based recovery, with allocation, fragmentation and concurrency costs; it is not primarily a special action done only during recovery.ADB29
Immediate update allows persistent changes before commit and therefore may require undo; it need not flush every write_item immediately.ADB29
Deferred update keeps uncommitted changes outside the persistent database; in the usual no-force setting it is NO-UNDO/REDO.ADB29
Choice by choice
A · incorrect — Immediate update concerns allowing persistent changes before commit rather than requiring new locations.
B · incorrect — Deferred update concerns withholding uncommitted persistent effects rather than necessarily copying pages.
C · correct — Shadow/copy-on-write preserves the original and writes the changed value at a separate location.
D · incorrect — In-place update overwrites the original location rather than preserving it intact.
Question 223
In the course's conceptual transaction-log model supporting undo of incomplete changes and redo of committed changes, what value information does a write-operation record store?
A. Only new (after-image) values
B. Only old (before-image) values
C. Both old (before-image) AND new (after-image) values
D. Only transaction IDs and timestamps
Hint / Module Reference (Where to Read)
Trace movement between program variables, buffers and disk, or the meaning of a recorded transaction event.
Correct answer: C — C. Both old (before-image) AND new (after-image) values
The given UPDATE record contains both the old and new values. The old value supplies a before-image for undo, while the new value supplies an after-image for redo. Therefore the record stores both kinds of image. Real systems can use other physical or logical formats, but that variation does not alter the explicit conceptual record being examined here.
Evidence in the complete study note
A conceptual log can contain START, UPDATE(T,X,old,new), COMMIT, ABORT and END records. Before-images support undo; after-images support redo. Real systems may use physical or logical records, and need not log every read. COMMIT records successful completion; an ABORT record can mark the start of rollback rather than prove every undo step is complete.ADB07
Undo restores before-images of incomplete effects, usually traversing that transaction's updates backward.ADB27
In a simple undo/redo teaching model, undo losers and redo winners.ADB27
Choice by choice
A · incorrect — The given format includes old as well as new, so after-images alone are incomplete.
B · incorrect — The format includes new as well as old, so before-images alone are incomplete.
C · correct — Both before-image and after-image fields are present.
D · incorrect — Transaction identifiers alone omit the explicitly supplied value fields.
Question 224
A distributed database is best described as:
A. A single database on one powerful server
B. Multiple logically related databases across networked computers, managed transparently
C. A fully replicated database at every network location
D. A web-based file storage system
Hint / Module Reference (Where to Read)
Separate coordinated network placement from engine compatibility and semantic integration.
Correct answer: B — Logically related data across networked sites under coordinated database management.
ADB33 defines logically related data across networked sites managed as a coordinated system. B matches that architecture and its logical abstraction. Full replication is only one placement choice, not a defining requirement. A single powerful server is centralized, and generic web file storage does not establish coordinated database semantics across related distributed data.
Evidence in the complete study note
A distributed database contains logically related data across networked sites, managed as a coordinated system. A homogeneous system uses compatible DBMS technology across sites; a heterogeneous system integrates differing DBMSs, data models, schemas or interfaces. Different operating systems are possible but do not define the distinction. Same DBMS does not imply identical local schemas, automatic replication or immediate synchronization.ADB33
Full replication does not inherently mean every copy is updated simultaneously; its synchronization rule must be stated.ADB37
Full replication places the whole database at every relevant site; partial replication copies selected fragments to selected sites; nonredundant allocation stores each fragment once.ADB37
Choice by choice
A · incorrect — A database confined to one server is centralized rather than distributed across networked sites.
B · correct — Networked, logically related data with coordinated management fits the distributed database definition.
C · incorrect — A distributed database need not replicate every row at every network location.
D · incorrect — Generic web file storage does not by itself establish coordinated distributed database management.
Question 225
A homogeneous distributed database system requires:
A. Different DBMS at each site
B. Same DBMS software at all sites
C. No network between sites
D. Full independence of each site
Hint / Module Reference (Where to Read)
Separate coordinated network placement from engine compatibility and semantic integration.
Correct answer: B — The same compatible DBMS technology at the sites.
ADB33 defines homogeneity through compatible DBMS technology across sites. B is the listed form of that condition. It does not imply identical local schemas, replication or immediate synchronization. Different DBMSs describe heterogeneity, while removing the network or demanding full independence contradicts coordinated distributed management rather than defining homogeneity.
Evidence in the complete study note
A distributed database contains logically related data across networked sites, managed as a coordinated system. A homogeneous system uses compatible DBMS technology across sites; a heterogeneous system integrates differing DBMSs, data models, schemas or interfaces. Different operating systems are possible but do not define the distinction. Same DBMS does not imply identical local schemas, automatic replication or immediate synchronization.ADB33
These describe communication responsibilities, not fragmentation types.ADB34
State how much schema, transaction and administrative autonomy local sites retain.ADB34
Choice by choice
A · incorrect — Different DBMS technologies characterize heterogeneous integration rather than homogeneity.
B · correct — The same DBMS software supplies the compatible database technology described by homogeneous systems.
C · incorrect — Distributed sites need network communication for coordinated management.
D · incorrect — Full local independence is an autonomy choice rather than a homogeneity requirement.
Question 226
Heterogeneous distributed databases use which standards for inter-database communication?
A. Same DBMS software only
B. Direct SQL between databases
C. ODBC and JDBC generic connectivity
D. LAN protocols exclusively
Hint / Module Reference (Where to Read)
Separate coordinated network placement from engine compatibility and semantic integration.
Correct answer: C — ODBC and JDBC connectivity interfaces.
ADB33 explicitly names ODBC and JDBC as connectivity interfaces for differing systems. C therefore identifies the offered communication interfaces. They do not automatically reconcile schemas, units or transaction semantics and do not create a distributed transaction by themselves. Identical DBMS software is not heterogeneity, and generic LAN protocols do not replace database-level connectivity interfaces.
Evidence in the complete study note
Heterogeneous design must reconcile naming, types, units, identifiers, query capabilities and transaction semantics. ODBC and JDBC provide connectivity interfaces; they do not automatically solve semantic differences or create one distributed transaction. Locality, expansion and replicated availability are potential benefits. Distribution adds communication, coordination and security costs; a site outage can still make needed data unavailable if no usable replica exists.ADB33
Choice by choice
A · incorrect — Requiring the same DBMS removes the differing-engine situation rather than identifying its connectivity interfaces.
B · incorrect — Direct SQL alone does not identify the generic cross-system connectivity standards named in the note.
C · correct — ODBC and JDBC provide generic database connectivity while semantic mappings remain necessary.
D · incorrect — LAN protocols alone do not supply the database API/interface compatibility described.
Question 227
Horizontal fragmentation divides a database table by:
A. Columns (attributes)
B. Rows based on a condition or criterion
C. Both rows and columns simultaneously
D. Database schemas
Hint / Module Reference (Where to Read)
Identify whether rows or columns are split and reverse the split to reconstruct the original relation.
ADB36 defines horizontal fragmentation by selecting row subsets with predicates. B directly states that operation. Column division is vertical fragmentation, and combining both dimensions is hybrid fragmentation. Schema names and geographic placement can influence a design, but are not the definition of what is split by horizontal fragmentation.
Evidence in the complete study note
Horizontal fragmentation selects row subsets by predicates; their union reconstructs the relation. Check completeness so every original row is represented and disjointness if no overlap is intended. Define an explicit fragment for NULL/other values when needed. Vertical fragmentation projects column subsets and repeats a key or row identifier so joining on it reconstructs the original rows without spurious matches.ADB36
Hybrid fragmentation combines row and column splits in either suitable order.ADB36
Reconstruct by reversing the operations: key joins for corresponding vertical pieces and unions for horizontal pieces.ADB36
Choice by choice
A · incorrect — Splitting attributes is vertical rather than horizontal fragmentation.
B · correct — Horizontal fragmentation partitions rows according to stated predicates or criteria.
C · incorrect — Splitting both dimensions combines horizontal and vertical operations in hybrid fragmentation.
D · incorrect — Schemas are not the row units divided by the defined horizontal operation.
Question 228
To reconstruct a horizontally fragmented table, which operation is used?
A. JOIN
B. UNION
C. INTERSECTION
D. DIFFERENCE
Hint / Module Reference (Where to Read)
Identify whether rows or columns are split and reverse the split to reconstruct the original relation.
Horizontal fragments contain selected subsets of the original relation’s rows. ADB36 reconstructs the relation by their union, assuming completeness and the intended overlap rules. Joining is used to align vertical column pieces by a key; intersection would keep only shared rows and difference would remove rows, so neither reconstructs all horizontal fragments.
Evidence in the complete study note
Horizontal fragmentation selects row subsets by predicates; their union reconstructs the relation. Check completeness so every original row is represented and disjointness if no overlap is intended. Define an explicit fragment for NULL/other values when needed. Vertical fragmentation projects column subsets and repeats a key or row identifier so joining on it reconstructs the original rows without spurious matches.ADB36
Reconstruct by reversing the operations: key joins for corresponding vertical pieces and unions for horizontal pieces.ADB36
A relation is represented by a table of tuples.ADB-S01
Choice by choice
A · incorrect — Key joins reconstruct matching vertical column pieces, not ordinary horizontal row partitions.
B · correct — Union collects the rows from every complete horizontal fragment.
C · incorrect — Intersection keeps only common rows and would discard rows unique to individual fragments.
D · incorrect — Difference removes rows instead of recombining all fragment members.
Question 229
Vertical fragmentation divides a table by:
A. Rows
B. Columns (attributes), with primary key repeated in each fragment
C. Number of concurrent transactions
D. Geographic location of data
Hint / Module Reference (Where to Read)
Identify whether rows or columns are split and reverse the split to reconstruct the original relation.
Correct answer: B — Column subsets with a repeated key or row identifier.
ADB36 splits a relation by columns and repeats a key or row identifier so the pieces can be joined without spurious matches. B describes that usual primary-key form. Rows are split horizontally; transaction counts and geography concern workload or placement rather than the attribute-level split defining vertical fragmentation. The shared identifier can be an appropriate equivalent key.
Evidence in the complete study note
Horizontal fragmentation selects row subsets by predicates; their union reconstructs the relation. Check completeness so every original row is represented and disjointness if no overlap is intended. Define an explicit fragment for NULL/other values when needed. Vertical fragmentation projects column subsets and repeats a key or row identifier so joining on it reconstructs the original rows without spurious matches.ADB36
Hybrid fragmentation combines row and column splits in either suitable order.ADB36
Reconstruct by reversing the operations: key joins for corresponding vertical pieces and unions for horizontal pieces.ADB36
Choice by choice
A · incorrect — Row subsets define horizontal rather than vertical fragmentation.
B · correct — Vertical fragments contain column subsets plus a repeated identifier for reconstruction.
C · incorrect — Concurrent transaction count is a workload property rather than the dimension being split.
D · incorrect — Geographic placement does not define the column projection used for vertical fragments.
Question 230
Full replication in distributed databases has which disadvantage?
A. Low data availability
B. Every update must be applied to all copies, creating high overhead
C. Fast read performance
D. Simple concurrency control
Hint / Module Reference (Where to Read)
Separate copy placement, propagation timing and update-maintenance cost.
Correct answer: B — Propagating each update across all copies adds maintenance overhead.
ADB37 says copies improve locality and can improve availability but require storage and update propagation. B names that maintenance burden for full replication. Propagation timing may be synchronous or asynchronous, so the answer does not imply every copy is updated simultaneously. Fast reads are a possible benefit, and concurrency control becomes more involved rather than inherently simple.
Evidence in the complete study note
Full replication places the whole database at every relevant site; partial replication copies selected fragments to selected sites; nonredundant allocation stores each fragment once. Copies improve read locality and can improve availability but require storage and update propagation. They can also spread mistakes, so replication does not replace independent backups.ADB37
Full replication does not inherently mean every copy is updated simultaneously; its synchronization rule must be stated.ADB37
Lazy/asynchronous replication propagates later and may expose stale reads or need conflict resolution.ADB37
Choice by choice
A · incorrect — Usable replicas can improve availability rather than inherently making it low.
B · correct — All copies must eventually reflect relevant updates, creating propagation and coordination overhead.
C · incorrect — Fast reads are a potential benefit rather than the listed disadvantage.
D · incorrect — Maintaining consistent copies does not inherently simplify concurrency control.
Question 231
Fragmentation transparency in distributed databases means:
A. Users can see all the fragmentation details
B. Users interact with the database as a single entity, unaware of fragmentation existence
C. Only DBAs know about fragments
D. Fragments are listed as separate tables in the schema
Hint / Module Reference (Where to Read)
Identify exactly which detail is hidden from the user.
Correct answer: B — Users query the logical relation without knowing its fragments.
ADB35 defines fragmentation transparency as hiding the existence and reconstruction of fragments. B states that user-facing abstraction directly. It does not require literally only DBAs to know the implementation; developers and administrators may also need that knowledge. Exposing fragment tables or all fragmentation details to users would make fragmentation visible instead of transparent.
Evidence in the complete study note
Location transparency hides which site holds a named object. Fragmentation transparency hides the existence and reconstruction of fragments: users query the whole logical relation. Replication transparency hides which copy is used and how copies are maintained. The broad single-logical-database experience may involve all three.ADB35
If users explicitly name a fragment but need not specify its site, fragmentation is visible while location can remain transparent.ADB35
Hiding this column split from queries is fragmentation transparency; the split itself is a data-design operation.ADB35
Choice by choice
A · incorrect — Showing all fragmentation details is the opposite of hiding them from ordinary queries.
B · correct — Users operate on the whole logical relation without managing its fragment reconstruction.
C · incorrect — Transparency describes the user abstraction, not exclusive knowledge restricted to DBAs alone.
D · incorrect — Naming fragments separately exposes fragmentation rather than concealing it from the querying user.
Question 232
The Middleware architecture in distributed DBMS uses:
A. Direct client-to-server connections
B. All servers collaborating on every query
C. A dedicated middleware server that mediates between clients and multiple independent database servers
D. A single centralized database server
Hint / Module Reference (Where to Read)
Identify which tier submits, mediates or executes a request.
Correct answer: C — Middleware mediates between clients and independent database servers.
ADB34 lets middleware translate requests, locate data, coordinate operations and combine results across independent servers. C describes that mediating tier. The logical responsibility does not require exactly one physical machine in every implementation. Direct connections omit this mediator, universal server collaboration is a different arrangement, and one centralized server is not the stated distributed middleware design.
Evidence in the complete study note
In client/server architecture, clients submit requests while servers manage shared data, execute operations and enforce supported integrity/transaction rules. In collaborating-server designs, servers cooperate on subqueries and transactions. Middleware can translate requests, locate data, coordinate operations and combine results across independent servers. These describe communication responsibilities, not fragmentation types.ADB34
Choice by choice
A · incorrect — Direct client-server links alone omit the middleware mediation that defines the requested architecture.
B · incorrect — Every server need not participate in every query; relevant sources depend on the request.
C · correct — A middleware tier mediates client requests across multiple independent database servers.
D · incorrect — One centralized database server does not describe mediation across independent distributed servers.
Question 233
Hybrid fragmentation combines which two techniques?
A. Full replication and no replication
B. Horizontal fragmentation and vertical fragmentation
C. Locking and timestamp-based concurrency control
D. Client-server and middleware architectures
Hint / Module Reference (Where to Read)
Identify whether rows or columns are split and reverse the split to reconstruct the original relation.
Correct answer: B — Horizontal and vertical fragmentation.
ADB36 explicitly defines hybrid fragmentation as combining row and column splits in an appropriate order. B names those two operations. Replication changes copy placement, locking and timestamps control concurrent access, and client-server/middleware describe architecture responsibilities. They can coexist with hybrid fragmentation but are not its two component fragmentation techniques.
Evidence in the complete study note
Horizontal fragmentation selects row subsets by predicates; their union reconstructs the relation. Check completeness so every original row is represented and disjointness if no overlap is intended. Define an explicit fragment for NULL/other values when needed. Vertical fragmentation projects column subsets and repeats a key or row identifier so joining on it reconstructs the original rows without spurious matches.ADB36
Hybrid fragmentation combines row and column splits in either suitable order.ADB36
These describe communication responsibilities, not fragmentation types.ADB34
Choice by choice
A · incorrect — Full/no replication concerns copies rather than the row and column splits of hybrid fragmentation.
B · correct — Hybrid fragmentation combines horizontal row subsets with vertical column subsets.
C · incorrect — Locking and timestamps are concurrency-control mechanisms, not fragmentation types.
D · incorrect — These are architecture forms rather than the two data-fragmentation operations.
Question 234
In client-server database architecture, what is the primary role of the server?
A. Provide the GUI for end users
B. Initiate all network connections and poll clients for updates
C. Store and manage shared data, enforce ACID properties, and process client requests
D. Cache all user sessions locally for offline access
Hint / Module Reference (Where to Read)
Identify which tier submits, mediates or executes a request.
Correct answer: C — Manage shared data, process requests and enforce supported transaction/integrity guarantees.
ADB34 assigns clients request submission and servers shared-data management, operation execution and supported integrity/transaction enforcement. C matches those responsibilities. User interfaces usually belong to clients, while initiating all connections, polling every client or caching all sessions for offline use are not the server role defined by this architecture. Guarantees still depend on supported mechanisms and correct application use.
Evidence in the complete study note
In client/server architecture, clients submit requests while servers manage shared data, execute operations and enforce supported integrity/transaction rules. In collaborating-server designs, servers cooperate on subqueries and transactions. Middleware can translate requests, locate data, coordinate operations and combine results across independent servers. These describe communication responsibilities, not fragmentation types.ADB34
Calling a system heterogeneous does not require all sites to surrender autonomy or to copy every row everywhere.ADB34
A tightly integrated distributed system can present one global schema and shared control.ADB34
Choice by choice
A · incorrect — The user-facing GUI is normally a client responsibility rather than the database server's primary role.
B · incorrect — The server need not initiate every connection or poll clients for every update.
C · correct — Shared-data management, query execution and supported integrity/transaction enforcement are server duties.
D · incorrect — Caching every session for offline access is not the defined database-server responsibility.
Question 235
In two-phase locking (2PL), during which phase may a transaction acquire locks but not
release any?
A. Growing phase
B. Commit phase
C. Validation phase
D. Shrinking phase
Hint / Module Reference (Where to Read)
Track the first lock release and the transaction-completion boundary.
ADB12 allows acquiring or upgrading locks during growing and releasing or downgrading them during shrinking. Once any lock is released, a transaction may not acquire another. A is therefore the phase described. Commit is a transaction event, validation belongs to optimistic control and shrinking has the opposite lock-release role.
Evidence in the complete study note
Basic 2PL has a growing phase that acquires/upgrades locks and a shrinking phase that releases/downgrades them. Once any lock is released, no new lock may be acquired. The lock point is the instant the final lock is obtained. 2PL guarantees conflict serializability for well-formed transactions, but can deadlock and basic 2PL alone need not prevent cascading aborts.ADB12
Two-phase locking orders access to data; two-phase commit coordinates a distributed commit decision.ADB12
Optimistic/validation control reads and computes privately, validates its read/write sets against overlapping transactions, then publishes if validation succeeds; otherwise it retries.ADB16
Choice by choice
A · correct — Growing permits acquisitions and upgrades before any release starts.
B · incorrect — Commit is not the general 2PL phase for new lock acquisition.
C · incorrect — Validation is an optimistic-control stage rather than the 2PL growing phase.
D · incorrect — Shrinking releases or downgrades locks and forbids acquiring new ones.
Question 236
Assume that we are able to specify that Mr. Getahun can view the database record, but
cannot change the value of the record. Which security management technique was used in
this scenario?
A. Access Control
B. Availability
C. Non-repudiation
D. Integrity
Hint / Module Reference (Where to Read)
Identify whether the scenario concerns identity, permission, disclosure or valid values.
ADB24 explicitly describes a user allowed to view but not change a record as having restricted access privileges. That is authorization/access control. Integrity concerns valid values, availability concerns readiness for use and non-repudiation concerns attributable actions rather than whether a particular read or write is permitted. A matches the actual permission distinction.
Evidence in the complete study note
Authentication establishes identity; authorization/access control determines permitted actions. Encryption protects stored or transmitted content; integrity constraints restrict valid data; auditing records actions. A user who can view but cannot change a record has restricted access privileges. A view can restrict visible rows/columns, but its privilege and execution rules must support that restriction.ADB24
Neither property specifies which particular user has permission to change a database row; that is the separate authorization decision.ADB-S03
Availability concerns keeping services and resources accessible to their authorized users.ADB-S03
Choice by choice
A · correct — Access control grants reading while denying modification for the specified user.
B · incorrect — Availability concerns service readiness rather than the read-versus-write privilege distinction.
C · incorrect — Non-repudiation does not define whether this user is authorized to modify a record.
D · incorrect — Integrity restricts valid data states rather than assigning user-specific read/update permissions.
Question 237
Suppose, a developer proposes using deferred update with no checkpoints in a high-
transaction OLTP system. What is the most serious drawback?
A. Log scans become prohibitively expensive after long uptime
B. Increased disk I/O due to frequent page copying
C. Shared locks cannot be used
D. View serializability cannot be ensured
Hint / Module Reference (Where to Read)
Identify which history a recovery boundary can safely exclude and whether active work spans it.
Correct answer: A — Long-running log scans can make recovery expensive.
ADB30 warns that without checkpoints or another valid truncation boundary, a long-running system may scan expensive amounts of log history. Deferred update removes uncommitted persistent effects but does not remove that search problem. Page copying belongs to shadow approaches, while shared locks and serializability are independent concurrency concerns rather than consequences of omitting checkpoints.
Evidence in the complete study note
A checkpoint records enough durable recovery state to avoid scanning all old history. In a simple quiescent/checkpoint exercise, specified flushing may make earlier committed work unnecessary to redo. Active transactions can span the checkpoint, so their relevant earlier updates can still need undo. Do not assume every transaction before a checkpoint needs no processing.ADB30
Without checkpoints or another valid truncation boundary, long-running systems may need expensive log scans; deferred update does not avoid that problem.ADB30
Shadow/copy-on-write update writes a new location while preserving the committed version.ADB29
Choice by choice
A · correct — High transaction volume and long uptime can produce costly recovery log scans without a valid boundary.
B · incorrect — Frequent copy-on-write pages characterize shadow updating rather than the absence of checkpoints in deferred update.
C · incorrect — Omitting checkpoints does not inherently prohibit shared locks.
D · incorrect — View serializability is a concurrency-order property, not inherently lost by this checkpoint omission.
Question 238
Which listed techniques use transaction logs for recovery under the course model? Interpret Deferred Database Modification as conventional logged deferred update with no-force. Select all that apply.
A. Recovery in Multi-database Systems
B. ARIES Recovery Algorithm
C. Shadow Paging Technique
D. Deferred Database Modification
Hint / Module Reference (Where to Read)
Separate update timing from update location and follow the commit boundary.
Correct answers (select all): B, D — B. ARIES Recovery Algorithm; D. Deferred Database Modification
ARIES relies on WAL and log-driven analysis, redo and undo. Conventional logged deferred update also uses recorded updates for redo of committed work, with no undo of uncommitted persistent changes. Both therefore belong to the requested category. Shadow paging preserves old page mappings instead, while recovery in multidatabase systems names an environment rather than a particular log-based technique.
Evidence in the complete study note
Deferred update keeps uncommitted changes outside the persistent database; in the usual no-force setting it is NO-UNDO/REDO. Redo-only and NO-UNDO/REDO mean the same thing, so those cannot be separate unique answers. Immediate update allows persistent changes before commit and therefore may require undo; it need not flush every write_item immediately.ADB29
ARIES uses WAL, page log sequence numbers (pageLSN), a transaction table and a dirty-page table.ADB31
It is an alternative to conventional log-based recovery, with allocation, fragmentation and concurrency costs; it is not primarily a special action done only during recovery.ADB29
Choice by choice
A · incorrect — Multidatabase recovery describes an environment rather than one specific recovery technique.
B · correct — ARIES explicitly uses WAL and log-driven analysis, redo and undo.
C · incorrect — Shadow paging preserves committed page maps rather than being the conventional log-based technique.
D · correct — Deferred modification can use logged changes for redo after commitment.
Question 239
Which type of fragmentation divides a relation into subrelations containing subsets of its columns, repeating a key or row identifier so the original rows can be reconstructed?
A. Replication
B. Location
C. Horizontal
D. Vertical
Hint / Module Reference (Where to Read)
Identify whether rows or columns are split and reverse the split to reconstruct the original relation.
Vertical fragmentation divides a relation by columns. Repeating a key or row identifier allows corresponding pieces to be joined back into the original rows without spurious matches. Horizontal fragmentation divides rows instead. Replication creates copies, while location transparency hides where data resides. Hiding a column split would be fragmentation transparency, but the question asks for the split operation itself.
Evidence in the complete study note
Horizontal fragmentation selects row subsets by predicates; their union reconstructs the relation. Check completeness so every original row is represented and disjointness if no overlap is intended. Define an explicit fragment for NULL/other values when needed. Vertical fragmentation projects column subsets and repeats a key or row identifier so joining on it reconstructs the original rows without spurious matches.ADB36
Hiding this column split from queries is fragmentation transparency; the split itself is a data-design operation.ADB35
Replication transparency hides which copy is used and how copies are maintained.ADB35
Choice by choice
A · incorrect — Replication copies data rather than dividing its columns.
B · incorrect — Location transparency hides sites rather than splitting a relation.
C · incorrect — Horizontal fragmentation selects row subsets.
D · correct — Vertical fragmentation projects column subsets with a reconstruction identifier.