Revision approach
- Reason about system behavior
- Object semantics
- Recovery algorithms
- Distributed design
- Explicit query costs
- Worked traces and assumptions
- Incomplete module/bank extensions
👁 Full explanation
Use object semantics, recovery algorithms, distributed design and explicit query costs to reason about system behavior. Worked traces and explicit assumptions support the study of object models, recovery and distributed databases. - Schedule analysis
- Mark conflicting items
- Build precedence graph
- Test commit dependencies separately
👁 Full explanation
For each schedule, mark conflicting items and build the precedence graph; test commit dependencies separately. - Recovery assumptions first
- Durable commit set
- Buffer policy
- Checkpoint/ARIES assumptions
- Then choose undo/redo
👁 Full explanation
For recovery, write the durable commit set, buffer policy and checkpoint/ARIES assumptions before choosing undo or redo. - Equivalent-plan comparison
- Page counts
- Memory limits
- Network bytes
- Show arithmetic
- No universal preferred join
👁 Full explanation
Compare equivalent plans with stated page counts, memory and network bytes; show the arithmetic rather than memorizing a preferred join. - Construct distributed designs
- Homogeneous versus heterogeneous
- Fact ownership
- Reconstruction
- Replication
- Failure behavior
👁 Full explanation
Construct one homogeneous and one heterogeneous distributed design, including ownership, reconstruction, replication and failure behavior. - Contrast through short examples
- OID versus values
- Encapsulation versus abstraction
- 2PL versus 2PC
- Rollback versus redo
👁 Full explanation
Review OID versus values, encapsulation versus abstraction, 2PL versus 2PC and rollback versus redo through short examples.
ADB01
Objects, identity, classes and persistence
[Exam Focus]- Object identity, state, behavior
- Class → attributes/operations
- Object → class instance
- OID independent of attribute values
- Lifetime-stable OID
- Equal values/different identities
- Name change ≠ identity change
👁 Full explanation
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. - Persistence and database approaches
- Object survives creating program
- OODBMS durable storage/transactions
- Querying/controlled sharing
- Objects not primarily rows/tables
- Object-relational → richer relational types
- ORM → objects mapped to tables
- Three distinct approaches
- Complex CAD/multimedia suitability
- M:N alone needs no relational abandonment
👁 Full explanation
Persistence lets an object survive beyond the program execution that created it. An OODBMS adds durable storage, transactions, querying and controlled sharing to object concepts. It does not primarily present objects as rows and tables. An object-relational DBMS extends a relational system with richer types; ORM maps programming objects into relational tables. These three approaches differ. Complex CAD or multimedia structures can suit an object model, but many-to-many relationships alone do not require abandoning relational design.
6 related practice questions
Blueprint: A1 · Note references: ADB-NOTE, ADB-OO
- Object identity, state, behavior
ADB02
Encapsulation, abstraction and object associations
[Exam Focus]- Encapsulation versus abstraction
- Encapsulation → state plus operations
- Controlled implementation access
- Account.withdraw(amount) → balance rule
- Hidden storage details
- Abstraction → relevant operation contract
- What versus implementation how
- Overlapping information hiding
- Combining state/methods → encapsulation
👁 Full explanation
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. - Object associations and collaboration
- References link objects
- Department.employees → Employee objects
- Association ≠ inheritance
- Inheritance → is-a types
- Methods invoke other-object operations
- Class → structure/behavior
- Attribute → state
- Method → operation implementation
- Part names ≠ whole object
👁 Full explanation
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.
4 related practice questions
Blueprint: A1 · Note references: ADB-NOTE, ADB-OO
- Encapsulation versus abstraction
ADB03
Inheritance, polymorphism and safe substitutability
[Exam Focus]- Inheritance versus association
- Subtype from superclass
- Reuse applicable attributes/behavior
- GraduateStudent is-a Student
- Student belongs-to Department
- EER inherited attributes/relationships
- Programming object model adds behavior
👁 Full explanation
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. - Subtype polymorphism
- Superclass/interface reference → compatible subclasses
- Common operation/different implementations
- Shape.area() dispatch
- Actual Circle.area()/Rectangle.area()
- No arbitrary unrelated-class conversion
- No dynamic class mutation
- Inheritance → structural subtype prerequisite
- Polymorphism → runtime common-interface use
- Ambiguous MCQ needs requested distinction
👁 Full explanation
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.
4 related practice questions
Blueprint: A1 · Note references: ADB-NOTE, ADB-POLY
- Inheritance versus association
ADB04
Object type constructors
[Exam Focus]- Object type constructors
- (OID,type constructor,state)
- Atom → basic value
- Integer/real/Boolean/string
- Tuple → named component values
- (Street:'A',City:'B')
- Structured record ≠ record collection
👁 Full explanation
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. - Collection constructors and nesting
- Set → unordered/no duplicates
- Bag → duplicates allowed
- List → ordered
- Array → indexed positions
- Nested constructors → complex objects
- Tuple with phone-number set
- One variable ≠ atomic constructor
- Type constructor → value shape
- Executable constructor → object creation
👁 Full explanation
A set is an unordered collection without duplicates; a bag allows duplicates; a list has order; an array provides indexed positions. Constructors can be nested to represent complex objects, such as a tuple with a set of phone numbers. An array is not an atomic constructor merely because stored as one programming variable. A type constructor defines the shape of a value; it is not the same as an executable constructor operation that creates a new object.
2 related practice questions
Blueprint: A1 · Note references: ADB-CONSTRUCT
- Object type constructors
ADB05
ODL, OQL and inverse relationships
[Exam Focus]- ODMG object languages
- ODL → Object Definition Language
- Object database schema definition
- Interfaces/classes/attributes/operations/relationships
- OQL → Object Query Language
- Host-language bindings → Java/C++
- Definitions/operations connected to host languages
- Logical schema ≠ storage implementation
👁 Full explanation
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. - Inverse relationship declaration
- Department.employees ↔ Employee.department
- Opposite directions/same relationship
- Employee in department D
- Employee appears in D collection
- Relationship consistency
- Not inheritance keyword/scalar attribute
- ODL DDL/logical-schema descriptions overlap
- Exact MCQ wording matters
👁 Full explanation
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.
2 related practice questions
Blueprint: A1 · Note references: ADB-ODL
- ODMG object languages
ADB06
Transactions, ACID and state transitions
- Transactions and ACID
- Logical database-work unit
- Read-only transactions included
- Atomicity → all effects or none
- Consistency → correct integrity-preserving work
- Isolation → concurrent interference control
- Durability → committed effects survive failure
- DBMS cannot repair incorrect business logic
👁 Full explanation
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. - Transaction states and responsibilities
- Active → executing operations
- Partially committed → last operation finished
- Durability not yet assured
- Committed → commitment succeeded
- Failure → Failed
- Rollback → Aborted
- Terminate or restart new attempt
- Concurrency control → isolation
- Recovery → atomicity/durability
- Transaction management broader
- Broad responsibility answers can overlap
👁 Full explanation
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. - Worked example Requested transfer versus invariant
- Initial A=5,000/B=8,000
- Transfer 750 A→B
- Final A=4,250/B=8,750
- Total=13,000
- Unchanged/reverse balances also preserve total
- Correct debit/credit required
👁 Full explanation
Worked exampleA=5,000 and B=8,000 become 4,250 and 8,750 after transferring 750 from A to B. Both balances total 13,000. Unchanged or reverse-transfer balances can also preserve that sum; only the requested debit/credit implements the specified successful transfer.
29 related practice questions
Note references: ADB-MOD
- Transactions and ACID
ADB07
Read/write operations, buffers and log records
- Item operations and buffer policy
- read_item(X) → locate page
- Fetch absent page into buffer
- Copy X into program variable
- write_item(X) → variable into buffered page
- Mark page dirty
- Disk flush may occur later
- write_item ≠ immediate disk flush
- Insert/delete modify data
- Sorting → query processing
- Sorting ≠ primitive item update
👁 Full explanation
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. - Log records and images
- START/UPDATE(T,X,old,new)
- COMMIT/ABORT/END
- Before-image → undo
- After-image → redo
- Physical/logical record alternatives
- Not every read needs logging
- COMMIT → successful completion
- ABORT may only start rollback
- ABORT ≠ completed undo proof
👁 Full explanation
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.
8 related practice questions
Note references: ADB-MOD
- Item operations and buffer policy
ADB08
Diagnose concurrency anomalies from a trace
- Lost update versus dirty read
- Both read 500
- T2 +200 → writes 700
- T1 stale +100 → writes 600
- Correct serial result 800
- Dirty read → other's uncommitted write
- Writer abort → reconsider dependent work
- Own changes ≠ dirty read
👁 Full explanation
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. - Read and summary anomalies
- Nonrepeatable → same-row committed change
- Phantom → changed predicate result set
- Matching insert/delete/update
- Incorrect summary → inconsistent versions
- Transfer 10: A=100/B=100
- Old A=100 plus new B=110
- Observed 210 versus invariant 200
- Single committed B=20→50 insufficient
- Invalid-summary 90 needs more evidence
👁 Full explanation
Nonrepeatable read means a repeated read of one row sees a committed change. A phantom means repeating a predicate query finds a changed qualifying set after another transaction inserts, deletes or changes matching rows. 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. A single committed update from B=20 to B=50 does not alone prove an invalid summary of 90.
12 related practice questions
Note references: ADB-MOD
- Lost update versus dirty read
ADB09
Serial schedules, conflicts, and precedence graphs
- Serial versus serializable
- Serial → consecutive transaction operations
- Serializable → equivalent serial order
- Interleaving permitted
- Same final value insufficient
- Accidental coincidence possible
- Conflict → different transactions
- Same item/at least one write
- Read/read nonconflicting
👁 Full explanation
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. - Precedence graph test
- Vertex per transaction
- Earlier/later conflict → Ti→Tj
- Acyclic → conflict-serializable
- Topological order → valid serial order
- Cycle → not conflict-serializable
- View serializability weaker test
- Read-from relationships/final writers
- Some blind-write schedules permitted
👁 Full explanation
For conflict serializability, make one graph vertex per transaction. For every earlier conflicting operation of Ti and later operation of Tj, add Ti→Tj. Acyclic means conflict-serializable; a topological ordering gives a valid serial order. Cyclic means not conflict-serializable. This test is stronger than view serializability, which also compares read-from relationships and final writers and may permit some schedules with blind writes. - Worked example Worked conflict graphs
- r1(X),w2(X),r2(Y),w1(Y)
- X → T1→T2
- Y → T2→T1
- Cycle → not conflict-serializable
- r1(X),w1(X),r2(X),w2(X)
- Only T1→T2
- Acyclic → conflict-serializable
👁 Full explanation
Worked exampler1(X), w2(X), r2(Y), w1(Y) gives T1→T2 on X and T2→T1 on Y, a cycle. r1(X), w1(X), r2(X), w2(X) gives only T1→T2 and is conflict-serializable.
4 related practice questions
Note references: ADB-MOD
- Serial versus serializable
ADB10
Recoverable, cascadeless and strict schedules
- Commit-dependency schedule hierarchy
- Recoverable → reader commits after writer
- Avoid committed dependency on aborted writer
- Cascadeless → read committed writes only
- No cascading aborts
- Strict → no other's uncommitted-item reads
- No other's uncommitted-item overwrites
- Strict ⇒ cascadeless ⇒ recoverable
👁 Full explanation
Recoverable means if Tj reads Ti's value, Tj cannot commit before Ti commits. Otherwise Ti might abort after Tj already committed dependent work. Cascadeless means a transaction reads only values written by committed transactions, avoiding cascading aborts. Strict means no other transaction reads or overwrites an item written by an uncommitted transaction. Strict ⇒ cascadeless ⇒ recoverable. - Recoverability worked trace
- w1(X),r2(X),c2,a1
- Dirty dependency committed → unrecoverable
- c2 after c1 → recoverable dependency
- Earlier dirty read remains
- Not cascadeless
- Serializability → equivalent order
- Recoverability → safe commit/rollback dependencies
- Separate tests
👁 Full explanation
For w1(X), r2(X), c2, a1, T2 committed a dirty dependency, so the schedule is unrecoverable. Moving c2 after c1 makes that dependency recoverable, but the earlier dirty read still makes it not cascadeless. Serializability and recoverability are separate tests: one concerns equivalent transaction order and the other safe commit/rollback dependencies.
Note references: ADB-MOD
- Commit-dependency schedule hierarchy
ADB11
Locks, compatibility, conversion and managers
- Shared/exclusive lock compatibility
- S → read permission
- Multiple S locks coexist
- X → holder reads/writes
- X conflicts with other's S/X
- Different-item locks need not conflict
- Basic lock-based rules
- Multiversion snapshot readers may differ
👁 Full explanation
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. - Lock ownership, conversion, management
- Appropriate lock before access
- Release only own locks
- S→X upgrade
- Conflicting holders must leave
- X→S downgrade
- Lock manager → request decisions
- Lock table → granted/waiting requests
- Already locked request allowed
- Compatible → granted
- Incompatible → wait/abort by policy
- No-double-locking ≠ shared-reader prohibition
👁 Full explanation
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. - Held S compatibility
- Requested S → compatible
- Requested X → conflict
👁 Full explanation
Held / requested S X S Compatible Conflict - Held X compatibility
- Requested S → conflict
- Requested X → conflict
👁 Full explanation
Held / requested S X X Conflict Conflict
11 related practice questions
Note references: ADB-MOD
- Shared/exclusive lock compatibility
ADB12
Two-phase locking variants
- Basic two-phase locking
- Growing → acquire/upgrade
- Shrinking → release/downgrade
- Any release → no new acquisition
- Lock point → final lock obtained
- Well-formed 2PL → conflict serializability
- Deadlock possible
- Basic 2PL may allow cascading aborts
👁 Full explanation
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. - 2PL variants versus 2PC
- Strict → X until commit/abort
- Rigorous → S/X until commit/abort
- Conservative/static → all locks beforehand
- No execution with partial lock set
- Prevents hold-and-wait deadlock
- Requires known lock set
- May reduce concurrency
- 2PL → data-access ordering
- 2PC → distributed commit decision
👁 Full explanation
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.
9 related practice questions
Note references: ADB-MOD
- Basic two-phase locking
ADB13
Deadlock detection, prevention and starvation
- Deadlock detection and repair
- T1 holds X/wants Y
- T2 holds Y/wants X
- Wait-for Ti→Tj → held-lock wait
- Cycle → ordinary lock-wait deadlock
- Select victim/abort/undo/release locks
- Repeat for remaining cycles
- Aborting all also works
- Aborting all wastes more work
👁 Full explanation
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. - Deadlock prevention versus starvation
- Fixed resource ordering
- Conservative 2PL prevents cycles
- No-wait → abort incompatible request
- No age timestamp needed
- Timeout → abort long waits
- Slow progress mistaken for deadlock
- Starvation → indefinite postponement
- Other work continues/no required cycle
- Fair queues/sensible victim selection
- Retain original retry timestamps
- Deadlock freedom ≠ fairness
👁 Full explanation
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.
11 related practice questions
Note references: ADB-MOD
- Deadlock detection and repair
ADB14
Wait–die and wound–wait requester/holder rules
- Wait–die versus wound–wait
- Smaller timestamp → older
- Identify requester/holder first
- Wait–die older requester → wait
- Wait–die younger requester → self-abort
- Wound–wait older requester → abort holder
- Wound–wait younger requester → wait
👁 Full explanation
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. - Timestamp priority and correction
- Restart with original timestamp
- Relative priority grows with new arrivals
- Starvation avoidance needs finishing holders
- Fair scheduling also required
- Guide reverses wait–die incorrectly
- Younger waits/older dies incorrect
- Prevention priorities ≠ timestamp-ordering validity tests
👁 Full explanation
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. In wait–die, an older transaction may wait for a younger one; a younger transaction requesting a resource held by an older one aborts. Deadlock-prevention priority rules are also different from basic timestamp ordering's read/write validity tests. - Older requester/younger holder
- Wait–die → requester waits
- Wound–wait → abort younger holder
👁 Full explanation
Requester relative to holder Wait–die Wound–wait Older Wait Abort younger holder - Younger requester/older holder
- Wait–die → abort requester
- Wound–wait → requester waits
👁 Full explanation
Requester relative to holder Wait–die Wound–wait Younger Abort requester Wait
5 related practice questions
Note references: ADB-MOD, ADB-DEADLOCK
- Wait–die versus wound–wait
ADB15
Basic timestamp ordering tests
- Basic timestamp-ordering tests
- Assign TS(T)
- read_TS(X) → largest successful reader timestamp
- write_TS(X) → latest successful writer timestamp
- Read abort: TS(T)<write_TS(X)
- Otherwise read/update read_TS
- Write abort: TS(T)<read_TS(X)
- Or TS(T)<write_TS(X)
- Otherwise write/set write_TS
- Preserve timestamp order
👁 Full explanation
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. - Timestamp example and protocol limits
- TS(T)=10/write_TS(X)=20
- Read aborts → logically later write
- Older transaction can abort
- Younger-always-aborts claim false
- Thomas rule → obsolete write ignored
- Different protocol must be stated
- No lock-wait deadlocks
- Repeated restarts possible
👁 Full explanation
For TS(T)=10 and write_TS(X)=20, T cannot read X: it would observe a logically later write. Thus the transaction aborted can be older, contrary to the guides' blanket younger-always-aborts claim. The Thomas write rule can ignore an obsolete write instead of aborting in one case, but that is a different protocol and must be stated. Basic timestamp ordering avoids lock-wait deadlocks but may cause repeated restarts.
2 related practice questions
Note references: ADB-MOD, ADB-TIMESTAMP
- Basic timestamp-ordering tests
ADB16
Optimistic control, versions, and lock granularity
- Optimistic and multiversion control
- Private reads/computation
- Validate read/write sets
- Check overlapping transactions
- Validation success → publish
- Failure → retry
- Uncommon conflicts favorable
- High contention → wasted aborted work
- MVCC → multiple versions
- Reader uses appropriate snapshot
- Snapshot isolation ≠ automatic serializability
- Different-row writes can violate shared invariant
👁 Full explanation
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. - Lock granularity tradeoffs
- Fine row locks → independent operations
- Many-lock tracking overhead
- Coarse table/database → simpler management
- Unrelated rows blocked/lower concurrency
- Intention → planned descendant locks
- Hierarchy
- Escalation → many small to coarse
- Small granularity ≠ more logical conflicts
- Choose contention versus management overhead
👁 Full explanation
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.
3 related practice questions
Note references: ADB-MOD
- Optimistic and multiversion control
ADB17
Query processing before execution
[Exam Focus]- Query-processing phases
- Parsing → SQL grammar
- Semantic analysis → names/types/permissions
- Translation → logical representation
- Relational algebra/logical query tree
- Optimization → equivalent operations
- Join order/physical access methods
- Evaluation → execute plan/return results
- Phase boundaries implementation-dependent
👁 Full explanation
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. - Query representation and scope
- Tree leaves → base relations
- Internal nodes → operators
- Graph → relations/predicates
- Physical plan → scans/indexes/algorithms
- Query processing → whole activity
- Optimization → efficient strategy selection
- DDL unnecessary before each SELECT
- Authentication→DDL→DML permutations incorrect
👁 Full explanation
A query tree has base relations at leaves and operators at internal nodes; a query graph emphasizes relations and predicates. A physical execution plan adds scan/index choices and algorithms. Query processing is the whole activity; selecting a suitable efficient strategy is optimization. DDL is not a mandatory stage before each SELECT, so authentication→DDL→DML permutations do not describe the processing sequence.
11 related practice questions
Blueprint: A4 · Note references: ADB-MOD
- Query-processing phases
ADB18
Heuristic and semantic query rewrites
[Exam Focus]- Heuristic rewrites
- Push selective filters toward bases
- Product plus join predicate → join
- Project unneeded columns away
- Retain join/filter columns
- Reorder equivalent inner joins
- Reuse common expressions when worthwhile
- Split conjunctive selections
- Apply each predicate to own input
👁 Full explanation
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. - Semantic versus cost-based optimization
- Trusted constraints drive semantic rewrites
- Key → join cannot multiply matches
- Cost-based → estimate candidate plans
- Outer joins/NULLs/duplicates/aggregation restrict equivalence
- LEFT JOIN right filter ON→WHERE
- Preserved unmatched rows may disappear
- Fewer intermediates ≠ minimum-runtime proof
👁 Full explanation
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. Outer joins, NULLs, duplicates and aggregation restrict equivalences: moving a right-table filter from ON to WHERE after a left join may delete its preserved unmatched rows. Fewer intermediate rows is a useful heuristic, not proof of minimum runtime.
4 related practice questions
Blueprint: A4 · Note references: ADB-MOD
- Heuristic rewrites
ADB19
Costs, cardinalities, and selectivity
[Exam Focus]- Cost and cardinality estimation
- Page I/O/CPU/memory
- Temporary storage/network traffic
- Catalog row/page counts
- Distinct values/distributions
- Selection rows ≈ s×N
- Equijoin ≈ |R|×|S|/max(V(R,k),V(S,k))
- Non-NULL join-key counts
- Uniform-value assumption
- Smaller distinct domain contained in larger
- Adjust for NULLs
- Empty non-NULL input → zero
- Skew/correlation/incomplete overlap invalidate estimate
- Known disjoint domains → zero
👁 Full explanation
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. - Disk-time model and objective
- Cost = seeks×seek_time + blocks×block_transfer_time
- 5×5ms + 10×1ms = 35ms
- Textbook local disk → I/O often dominates
- In-memory → CPU may dominate
- Distributed → network may dominate
- State objective before ranking plans
👁 Full explanation
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.
8 related practice questions
Blueprint: A4 · Note references: ADB-MOD, PRIMARY-CMU-INCLUSION, PRIMARY-JOB
- Cost and cardinality estimation
ADB20
Join algorithms and access paths
[Exam Focus]- Nested-loop page costs
- Tuple loops → inner scan/outer tuple
- Page loops ≈ B(R)+B(R)×B(S)
- M buffers
- Block loops ≈ B(R)+ceil(B(R)/(M−2))×B(S)
- Reserve inner/output buffers
- Try both outer relations
👁 Full explanation
Tuple nested loops scan the inner relation for each outer tuple. Page nested loops approximately cost B(R)+B(R)×B(S) page reads. With M buffers, block nested loops can cost B(R)+ceil(B(R)/(M−2))×B(S), reserving an inner and output buffer. Try both outer choices. - Indexed, merge, and hash joins
- Indexed loops → probe/qualifying outer tuple
- Small outer/useful inner index
- Sort-merge → sorted join keys
- Include sorting cost
- Hash → partition/build/probe
- Equality suited/ranges generally unsuited
- Build side fits → one-pass hash
- Each input read once
- Include required output/materialization costs
👁 Full explanation
An indexed nested-loop join probes an inner index for each qualifying outer tuple; it suits a small outer result and a useful index. Sort-merge joins sorted join keys and must include any sorting cost. Hash join partitions/builds a hash structure and probes it; equality joins suit it, ranges generally do not. A one-pass in-memory hash join can read each input once when the build side fits. Include output/materialization cost if the exercise requires it.
1 related practice question
Blueprint: A4 · Note references: ADB-MOD
- Nested-loop page costs
ADB21
Compare actual query strategies
[Exam Focus]- Fair strategy comparison
- Same result/same assumptions
- Add scans/probes/sorts
- Intermediate writes/communication
- Consistent cost inclusion
- Record memory limits
- Already-ordered inputs?
- Lowest estimated specified-objective cost
- Compare estimates with actual execution
👁 Full explanation
Compare only plans returning the same result under the same assumptions. Add scans, probes, sorting, intermediate writes and communication as applicable; do not count an omitted cost in only one plan. Record memory limits and whether inputs are already ordered. Choose the lowest estimated cost for the specified objective, then test estimates against actual execution. - Worked join-cost comparison
- B(R)=100/B(S)=1,000/M=12
- M−2=10 block pages
- R outer: 100+ceil(100/10)×1,000=10,100
- S outer: 1,000+ceil(1,000/10)×100=11,000
- Selective R index=3 reads
- 20 qualifying outer tuples
- S probe including matches=4 reads
- Indexed loops: 3+20×4=83
- Indexed wins supplied assumptions
- No universal join ranking
👁 Full explanation
Example: R has 100 pages, S has 1,000 pages and M=12 buffers. Block nested loops with R outer cost 100+ceil(100/10)×1,000=10,100 reads; S outer costs 1,000+ceil(1,000/10)×100=11,000. If a selective R index costs 3 reads and returns 20 outer tuples, with each S index probe costing 4 reads including matches, indexed nested loops cost 3+20×4=83 reads. That wins under these stated assumptions; it is not a universal join ranking.
Blueprint: A4 · Note references: ADB-MOD
- Fair strategy comparison
ADB22
Pipelining, parallelism and caching
[Exam Focus]- Materialization versus pipelining
- Materialize → store intermediate before consumption
- Pipeline → tuples as available
- Producer/consumer overlap
- Reduced temporary I/O
- Sort normally blocking
- Hash build before probe output
- Later probe/output can stream
- Separate CPUs unnecessary
👁 Full explanation
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. - Parallelism and cache distinctions
- Multiple workers → parallel execution
- Partitioned scans/joins
- Independent subplans/operator pipelines
- One-CPU interleaving → concurrency
- Simultaneous cores → parallelism
- Result cache → answers/invalidation
- Plan cache → strategy, not rows
- Buffer cache → data pages
- Materialization → intermediate rows
- Query-caching question needs cache kind
👁 Full explanation
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.
4 related practice questions
Blueprint: A4 · Note references: ADB-MOD
- Materialization versus pipelining
ADB23
Indexes, statistics and re-optimization
[Exam Focus]- Index and storage choices
- B/B+ trees → ordered/range
- Hash → equality
- Bitmap → some analytic selections
- Clustered storage → nearby related rows
- Index read savings
- Space/update overhead
- Most rows qualify → scan may win
- Common usefulness ≠ unique index property
👁 Full explanation
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. - Re-optimization and measurement
- Statistics/parameters/observed cardinalities trigger reconsideration
- Choose suitable plan again
- Statistics update ≠ query execution
- EXPLAIN plus measured execution
- Estimates/spills/scans/join choices
- Joins not universally faster than subqueries
- Functions can use indexes
- More indexes not always better
👁 Full explanation
Re-optimization chooses a plan again when statistics, parameters or observed cardinalities suggest the old plan is unsuitable. Updating statistics does not itself execute the query. Use EXPLAIN and measured execution to compare estimates, spills, scans and join choices. Avoid universal rules that joins always beat subqueries, functions can never use indexes or more indexes always improve performance.
3 related practice questions
Blueprint: A4 · Note references: ADB-MOD
- Index and storage choices
ADB24
Security, integrity and inference control
- Security versus integrity mechanisms
- Authentication → identity
- Authorization/access control → permitted actions
- Encryption → stored/transmitted content
- Integrity constraints → valid data
- Auditing → actions
- View-only/no-change → restricted privileges
- Views → visible row/column restriction
- Privilege/execution rules must support restriction
👁 Full explanation
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. - Inference control
- Protected facts from allowed aggregates
- SUM(group)−SUM(group excluding person)
- Individual value disclosed
- Row suppression alone insufficient
- Query restrictions/minimum aggregate sizes
- Appropriately designed noise
- Aggregates-visible/details-hidden also fits views
- Indirect-disclosure prevention uniquely identifies inference control
👁 Full explanation
Inference control concerns learning protected individual facts indirectly from allowed aggregate answers. Releasing SUM for a group and SUM for the same group excluding one known person may disclose that person's value by subtraction. Suppressing individual rows alone is insufficient; protections may restrict queries, aggregate sizes or add appropriately designed noise. Access-restricted views can expose aggregates while hiding details; inference control additionally concerns preventing indirect disclosure.
2 related practice questions
Note references: ADB-MOD, ADB-INFERENCE
- Security versus integrity mechanisms
ADB25
GRANT, REVOKE, and privilege scope
- GRANT versus REVOKE scope
- GRANT SELECT ON Catalogue TO Mohammed
- Read permission
- REVOKE DELETE, UPDATE (Price,Year)
- Catalogue FROM Mohammed
- Dialect must support form
- DELETE → rows/table, not columns
- UPDATE can be column-scoped
- REVOKE removes/GRANT gives permission
👁 Full explanation
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. - Roles and effective privileges
- Roles → privilege groups
- WITH GRANT OPTION → onward delegation
- Upstream removal → CASCADE/RESTRICT possibilities
- Chosen command/DBMS determines behavior
- Other roles/grant paths may retain access
- Direct-grant removal ≠ no effective access
- View → stored query, not encryption
- Least privilege → only needed access
👁 Full explanation
Roles group privileges. 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. Removing one direct grant may leave effective access through another role or grant path. A view is a stored query definition, not encryption. Least privilege assigns only the access needed for the task.
2 related practice questions
Note references: ADB-MOD
- GRANT versus REVOKE scope
ADB26
Failures and recovery starting resources
[Exam Focus]- Failure classes and starting resources
- Transaction → invalid operations/constraint violations
- Concurrency-control aborts
- Concurrency enforcement cannot prevent every failure
- System crash → volatile memory lost
- Disks may survive
- Media failure → persistent storage damage
- Restore backup/replay available archived logs
- Human error → point-in-time restoration
- Restore before bad committed operation
👁 Full explanation
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. - Cooperating recovery mechanisms
- Restart → valid post-crash database
- Preserve durable commits/remove incomplete effects
- Rollback → failed/uncommitted work
- Redo → missing committed page effects
- Checkpoints → less log searching
- Backups → another stored copy
- Not mutually exclusive complete alternatives
- Buffer management ≠ durable backup
- Generic manager ≠ exact MySQL component
- Full backup → separate-storage database copy
- Scheduled backups plus archived logs
- Chosen recoverable-point restoration
👁 Full explanation
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. These are cooperating mechanisms, not four mutually exclusive complete alternatives. Buffer management is not durable backup. Generic recovery-manager terminology should not be mistaken for an exact MySQL component specification. A full backup copies the database to separate storage; scheduled backups plus archived logs support restoration to a chosen recoverable point.
17 related practice questions
Blueprint: A2 · Note references: ADB-MOD
- Failure classes and starting resources
ADB27
WAL, undo and redo
[Exam Focus]- Write-ahead logging ordering
- Recovery update log durable first
- Then changed data page durable
- Durable commit acknowledgment → required logs forced
- Commit record included
- Buffer change may precede log flush
- WAL orders durable writes
- Not every CPU assignment
👁 Full explanation
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. - Undo versus redo models
- Undo → incomplete-effect before-images
- Usually backward transaction traversal
- Redo → logged after-images as needed
- Usually forward traversal
- Set X to after-value → idempotent
- Repeat X=X+10 twice → non-idempotent
- Simple model → undo losers/redo winners
- ARIES → repeat history then undo losers
👁 Full explanation
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.
20 related practice questions
Blueprint: A2 · Note references: ADB-MOD
- Write-ahead logging ordering
ADB28
Steal/force recovery requirements
[Exam Focus]- Steal and force independence
- Steal → uncommitted page reaches disk
- May need undo
- No-steal → uncommitted effects stay off disk
- Force → updated pages at commit
- No-force → committed dirty pages buffered
- May need redo
👁 Full explanation
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. - Recovery-policy matrix assumptions
- Conventional page/log model
- Steal/no-force → undo+redo
- Steal/force → undo only
- No-steal/no-force → redo only
- No-steal/force → neither data-page operation
- After system crash
- Logging/atomic commit may remain necessary
- Independent of overwrite/separate-location updates
- Immediate update alone insufficient
👁 Full explanation
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. Logging/atomic commit machinery may still be necessary. These policies are independent of whether an update overwrites an old page or uses a separate location. Immediate update alone does not uniquely specify every recovery requirement. - Steal recovery requirements
- Force → UNDO
- No-force → UNDO+REDO
👁 Full explanation
Buffer policy Force No-force Steal UNDO UNDO + REDO - No-steal recovery requirements
- Force → neither data-page UNDO/REDO
- No-force → REDO
👁 Full explanation
Buffer policy Force No-force No-steal Neither data-page UNDO nor REDO REDO
1 related practice question
Blueprint: A2 · Note references: ADB-MOD
- Steal and force independence
ADB29
Deferred, immediate, in-place, and shadow updates
[Exam Focus]- Deferred versus immediate updates
- Deferred → uncommitted outside persistent database
- Usual no-force → NO-UNDO/REDO
- Redo-only = NO-UNDO/REDO
- Synonyms cannot be unique alternatives
- Immediate → persistent changes before commit
- May require undo
- write_item need not flush immediately
👁 Full explanation
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. - In-place versus shadow updates
- In-place → overwrite original location
- Shadow/copy-on-write → new location
- Committed version preserved
- Unchanged committed page map
- Modified page map
- Commit → durable atomic root/map-pointer installation
- Required pages durable first
- Precommit crash → old map
- Alternative to conventional log recovery
- Allocation/fragmentation/concurrency costs
- Not solely recovery-time action
👁 Full explanation
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.
13 related practice questions
Blueprint: A2 · Note references: ADB-MOD
- Deferred versus immediate updates
ADB30
Checkpoints and recovery boundaries
[Exam Focus]- Checkpoint boundaries
- Durable state avoids all-history scan
- Simple quiescent/checkpoint assumptions
- Specified flushing may remove earlier redo
- Active transactions can span checkpoint
- Earlier active updates may need undo
- Before checkpoint ≠ no processing
👁 Full explanation
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. - Fuzzy checkpoints
- Transactions continue
- Active-transaction/dirty-page information recorded
- No simultaneous all-page forcing
- Analysis may start at checkpoint
- Redo may start earlier
- Checkpoint ≠ full backup
- Checkpoint ≠ active-transaction commit
- No checkpoint/truncation → expensive log scans
- Deferred update does not avoid scans
👁 Full explanation
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.
9 related practice questions
Blueprint: A2 · Note references: ADB-MOD
- Checkpoint boundaries
ADB31
ARIES and repeated crashes during recovery
[Exam Focus]- ARIES phases and structures
- WAL/pageLSN
- Transaction table/dirty-page table
- Analysis → active/loser transactions
- Reconstruct dirty-page information
- Redo → repeat history
- Earliest relevant dirty-page update start
- pageLSN skips installed actions
- Undo → reverse losers
👁 Full explanation
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. - CLRs and repeated-crash recovery
- Compensation log records → undo actions
- Continuation information
- CLRs redone, never undone
- Restartable recovery
- Durable undo progress retained
- No-checkpoint alone ≠ identical undo/redo lists
- Specify algorithm/durable logs/page state
- Specify recovery progress
👁 Full explanation
Compensation log records (CLRs) describe undo actions and their continuation information. They can be redone but are not themselves undone. This makes recovery restartable: after a second crash, durable undo progress need not be undone again as if no progress occurred. Absence of checkpoints alone does not imply identical undo/redo lists. State the recovery algorithm, durable logs/page state and progress before predicting repeated-crash behavior.
6 related practice questions
Blueprint: A2 · Note references: ADB-MOD, ADB-RECOVERY
- ARIES phases and structures
ADB32
Execute a small recovery problem
[Exam Focus]- Worked undo/redo recovery
- Steal/no-force/durable WAL
- Simple undo/redo algorithm
- Initially X=100/Y=50
- T1 X:100→120, durable COMMIT
- T2 Y:50→70, no commit
- Crash → T1 winner/T2 loser
- Redo X=120 if needed
- Undo Y=50 if reached disk
- No-steal → Y never persisted
- No Y undo under no-steal
👁 Full explanation
Assume steal/no-force, durable WAL and a simple undo/redo method. Initially X=100,Y=50. The durable log contains START T1; UPDATE T1 X 100→120; COMMIT T1; START T2; UPDATE T2 Y 50→70; crash. T1 is a winner and T2 a loser. Redo X=120 if needed; undo Y=50 if T2's change reached disk. Under no-steal, Y never reached the persistent database, so no undo of it is needed. - Recovery-solving procedure
- List durable transaction commit status
- Identify checkpoint type/buffer policy
- Select recovery algorithm
- Apply required redo/undo order
- Recompute final values
- Active-at-failure ≠ automatically restart/commit
- Business retry → controlled new attempt
- Uncertain commit → duplicate-effects risk
- Idempotency prevents duplicate effects
👁 Full explanation
To solve larger cases, list transactions and durable commit status, identify the checkpoint type and buffer policy, select the algorithm, apply redo/undo in its required order and recompute final values. Transactions active at failure are not automatically restarted and committed. Business-level retries require a new controlled attempt; idempotency prevents duplicate effects when the client is unsure whether a commit succeeded.
2 related practice questions
Blueprint: A2 · Note references: ADB-MOD
- Worked undo/redo recovery
ADB33
Distributed, homogeneous, and heterogeneous systems
[Exam Focus]- Distributed database distinctions
- Logically related data/networked sites
- Coordinated management
- Homogeneous → compatible DBMS technology
- Heterogeneous → differing DBMS/model/schema/interface
- Operating systems do not define distinction
- Same DBMS ≠ identical local schemas
- No automatic replication/immediate synchronization
👁 Full explanation
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. - Heterogeneous reconciliation and distribution costs
- Naming/types/units/identifiers
- Query capabilities/transaction semantics
- ODBC/JDBC → connectivity interfaces
- No automatic semantic reconciliation
- No automatic distributed transaction
- Potential locality/expansion/replicated availability
- Communication/coordination/security costs
- Outage without usable replica → unavailable data
👁 Full explanation
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.
13 related practice questions
Blueprint: A3 · Note references: ADB-MOD
- Distributed database distinctions
ADB34
Architectures and local autonomy
[Exam Focus]- Distributed architecture responsibilities
- Client → requests
- Server → shared-data management/operations
- Supported integrity/transaction enforcement
- Collaborating servers → cooperative subqueries/transactions
- Middleware → request translation/data location
- Operation coordination/result combination
- Independent servers
- Responsibilities ≠ fragmentation types
👁 Full explanation
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. - Integration versus local autonomy
- Tight integration → global schema/shared control
- Federation → autonomous databases/mappings
- Loose multidatabase → more independence
- May lack complete global schema
- Specify schema/transaction/administrative autonomy
- Heterogeneous ≠ surrender all autonomy
- No requirement to copy every row
👁 Full explanation
A tightly integrated distributed system can present one global schema and shared control. A federation integrates autonomous databases through mappings; a loosely coupled multidatabase system may preserve more independence without one complete global schema. State how much schema, transaction and administrative autonomy local sites retain. Calling a system heterogeneous does not require all sites to surrender autonomy or to copy every row everywhere.
3 related practice questions
Blueprint: A3 · Note references: ADB-MOD
- Distributed architecture responsibilities
ADB35
Distribution transparency
[Exam Focus]- Distribution transparency types
- Location → site hidden
- Fragmentation → fragments/reconstruction hidden
- Query whole logical relation
- Replication → chosen copy/maintenance hidden
- Single logical database may combine all
👁 Full explanation
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. - Visible fragments versus hidden locations
- Explicit fragment name → fragmentation visible
- Unnamed site → location possibly transparent
- Specific-fragment stem needs clarification
- Column subsets → vertical fragmentation
- Split itself ≠ transparency type
- Hidden column split → fragmentation transparency
👁 Full explanation
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. Column subsets define vertical fragmentation, not a type of transparency. Hiding this column split from queries is fragmentation transparency; the split itself is a data-design operation.
5 related practice questions
Blueprint: A3 · Note references: ADB-MOD
- Distribution transparency types
ADB36
Fragmentation: correctness and reconstruction
[Exam Focus]- Fragmentation correctness
- Horizontal → predicate row subsets
- Union reconstructs relation
- Completeness → every original row represented
- Disjointness if overlap forbidden
- Explicit NULL/other-value fragment when needed
- Vertical → projected column subsets
- Repeat key/row identifier
- Key join reconstructs original rows
- No spurious matches
👁 Full explanation
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. - Hybrid reconstruction and availability
- Combine row/column splits
- Either suitable split order
- Reconstruct by reversing operations
- Vertical pieces → key joins
- Horizontal pieces → unions
- FULL OUTER JOIN not universally required
- Partitioning → locality/parallelism
- Partitioning alone ≠ spare copy
- Only-fragment-site loss → fragment unavailable
- Other sites may continue
👁 Full explanation
Hybrid fragmentation combines row and column splits in either suitable order. Reconstruct by reversing the operations: key joins for corresponding vertical pieces and unions for horizontal pieces. A full outer join is not universally required. Partitioning improves locality and parallelism but alone creates no spare copy. Losing the only site containing a fragment makes that fragment unavailable, even while other sites continue.
11 related practice questions
Blueprint: A3 · Note references: ADB-MOD
- Fragmentation correctness
ADB37
Replication policies and their costs
[Exam Focus]- Replication extent and backup distinction
- Full → whole database/relevant sites
- Partial → selected fragments/selected sites
- Nonredundant → one copy/fragment
- Read locality/possible availability gains
- Storage/update-propagation costs
- Mistakes can propagate
- Replication ≠ independent backups
👁 Full explanation
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. - Replication timing and transfer policies
- Eager/synchronous → propagation before commit/ack point
- Higher update latency
- Lazy/asynchronous → later propagation
- Stale reads/conflict resolution
- Snapshot → periodic copy
- Incremental → changes only
- Policy dimensions can coexist
- Commonly-used stem not unique
- Full replication ≠ simultaneous updates
- State synchronization rule
👁 Full explanation
Eager/synchronous replication coordinates propagation before the specified commit/acknowledgment point, increasing update latency. Lazy/asynchronous replication propagates later and may expose stale reads or need conflict resolution. Snapshot replication transfers a copy at intervals; incremental replication transfers changes. These dimensions can coexist, so commonly used does not identify one unique policy. Full replication does not inherently mean every copy is updated simultaneously; its synchronization rule must be stated.
5 related practice questions
Blueprint: A3 · Note references: ADB-MOD
- Replication extent and backup distinction
ADB38
Distributed plans and semijoin arithmetic
[Exam Focus]- Distributed query planning
- Logical query decomposition
- Localize relations → fragments/replicas
- Global placement/order
- Optimize local subplans
- Bytes/messages/latency/bandwidth/local work
- Equivalent filters/projections before shipping
- Semijoin → matching rows from one input
- Only that input's attributes returned
👁 Full explanation
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. - Worked semijoin communication
- R at A=100MB
- S at B=20MB
- Result required at A
- Ship all S → 20MB
- Send distinct R keys → 0.2MB
- Return matching S → 1MB
- Semijoin bytes=0.2+1=1.2MB
- Other costs excluded
- Extra messages/high latency may dominate
- Nearly all S matches → little benefit
👁 Full explanation
Suppose R at A occupies 100 MB and S at B 20 MB; compute the result at A. Shipping all S costs 20 MB. A semijoin can send 0.2 MB of distinct R join keys to B, then return 1 MB of matching S rows: 1.2 MB before other costs. It wins on transferred bytes, but extra messages may dominate on high-latency links. If nearly all S matches, key shipping may not help.
3 related practice questions
Blueprint: A3, A4 · Note references: ADB-MOD
- Distributed query planning
ADB39
Distributed atomic commit and Oracle roles
[Exam Focus]- Two-phase commit protocol
- Prepare → durable commit promise
- All yes → coordinator durably records commit
- Then inform participants
- Otherwise decide abort
- Prepared participant retains decision-following state
- Coordinator/communication failure → in-doubt blocking
- Wait until decision learned
- Post-prepare timeout ≠ invented commit/abort
👁 Full explanation
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. - Atomic commit versus isolation
- 2PC → atomic outcome
- Locking/other concurrency protocols → isolation
- Oracle COMMIT_POINT_STRENGTH
- Higher eligible strength → commit point site
- Oracle-specific 2PC role selection
- Not lock phase/checkpoint setting
- Coordinator → decision coordination
- Not every site's physical row storage
👁 Full explanation
2PC coordinates atomic outcome; locking/other concurrency protocols provide isolation. Oracle's COMMIT_POINT_STRENGTH helps choose the commit point site in its distributed transaction mechanism, with higher eligible strength preferred. This is an Oracle-specific 2PC role rule, not a lock phase or checkpoint setting. The transaction coordinator coordinates decisions rather than physically storing every site's rows.
3 related practice questions
Blueprint: A3 · Note references: ADB-MOD, ADB-ORACLE
- Two-phase commit protocol
ADB40
Construct homogeneous and heterogeneous designs
[Exam Focus]- Homogeneous three-branch bank design
- Compatible DBMS
- Horizontal accounts by BranchID
- Home branch owns account
- Replicate selected read-mostly reference data
- Cross-branch → global identifiers
- Coordinated commit/stated isolation
- Replica placement meets specified failure service
- Verify reconstruction/capacity/latency/recovery
👁 Full explanation
For a bank using one compatible DBMS at three branches, horizontally fragment branch accounts by BranchID; keep each account at its home branch and replicate selected read-mostly reference data. For cross-branch transfers, use global identifiers, coordinated commit and a stated isolation policy. Choose replica placement so the required service remains available after the specified failure. Verify reconstruction, capacity, latency and recovery procedures. - Heterogeneous merger design
- Different branch DBMSs retained
- AccountID/currency/time-zone/precision mappings
- Middleware/gateways for supported operations
- All engines support atomic distributed commit?
- Otherwise explicit compensating workflow
- State intermediate-state limits
- Autonomy versus coordination/availability
- Fact ownership/copy placement
- Failed-link permitted behavior
- Conflicting-write resolution
👁 Full explanation
For a merger retaining different branch DBMSs, add mappings for AccountID, currency, time zones and numeric precision, plus middleware/gateways for supported operations. Decide whether all engines support atomic distributed commits; if not, define an explicit compensating workflow and its intermediate-state limits. Compare retaining autonomy against coordination and availability requirements. A good design answers who owns each fact, where copies live, what a failed link permits and how conflicting writes are resolved.
3 related practice questions
Blueprint: A3 · Note references: ADB-MOD
- Homogeneous three-branch bank design
ADB41
Warehouses, OLTP, OLAP, and columnar storage
- OLTP, OLAP, and warehouses
- OLTP → frequent operational transactions
- Account updates
- OLAP → large-data scans/summaries
- Warehouse → integrated historical analysis
- Extraction/transformation/loading
- Workload tendencies, not absolute restrictions
- OLTP may retain history
- Warehouses may be updated
👁 Full explanation
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. - Warehouse schemas and storage layouts
- Star → declared fact grain
- Fact measurements linked to dimensions
- Time/product/store dimensions
- Snowflake → normalized dimensions
- Columnar → values grouped by column
- Fewer unnecessary column reads/compression
- Row-oriented → record fields grouped
- Physical layout ≠ relational-model determination
- Indexes/denormalization depend on workload
👁 Full explanation
A star schema places measurements at a declared grain in a fact table linked to descriptive dimensions, such as time, product and store. A snowflake normalizes dimension structure further. Columnar storage groups values by column, reducing unnecessary column reads and aiding compression in scans; row-oriented storage groups each record's fields. Neither physical layout alone determines whether the logical model is relational. Indexes and denormalization choices depend on workload.
2 related practice questions
Note references: ADB-MOD, ADB-NOTE
- OLTP, OLAP, and warehouses
ADB42
NoSQL families, wide columns and CAP
- NoSQL families
- Key-value → retrieve by key
- Document → structured documents
- Wide-column/column-family → sparse rows
- Related column families
- Graph → nodes/edges/relationship traversal
- Cassandra wide-column example
- Not interchangeable with analytic relational columnar engines
- Flexible schema ≠ absent schema rules
- NoSQL ≠ no transactions
👁 Full explanation
Key-value stores retrieve values by key; document stores hold structured documents; wide-column/column-family systems organize sparse rows and related column families; graph systems model nodes and edges for relationship traversal. Wide-column systems such as Cassandra are not simply interchangeable with analytic columnar relational engines. Flexible schema does not mean no schema rules, and NoSQL does not imply no transactions. - CAP under network partitions
- Cannot guarantee both linearizable consistency/availability
- Every request at nonfailed nodes
- Partition condition essential
- Not casually pick-any-two forever
- Failure behavior choices
- May vary by operation
- CAP consistency → single-copy visibility/order
- ACID consistency → application invariant preservation
👁 Full explanation
CAP states that under a network partition, a distributed service cannot guarantee both linearizable consistency and availability for every request at nonfailed nodes. It is not a universal menu saying casually pick any two forever. Systems choose failure behavior and may vary it by operation. CAP consistency is a single-copy visibility/order property, distinct from ACID consistency's preservation of application invariants.
Note references: ADB-NOTE, ADB-CAP
- NoSQL families
ADB43
Data sources and association rules
- Structured versus unstructured data
- Structured → explicit fields
- Registrar relational tables
- Unstructured → free text/images/audio
- Web/search collections may mix both
- DBMS can store text/BLOBs
- Storage product ≠ all-structured contents
👁 Full explanation
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. - Association rules and mining contrasts
- X⇒Y → co-occurrence
- Support=count(X and Y)/N
- Confidence=count(X and Y)/count(X)=P(Y|X)
- 75/100 shoe purchases include socks
- Confidence(shoes⇒socks)=0.75
- No causation proof
- Support needs total transaction count
- Clustering → similarity/no target labels
- Classification → categories
- Regression → numeric outcomes
- Purchasing co-occurrence → association mining
- Store-layout use does not change task
👁 Full explanation
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.
2 related practice questions
Note references: ADB-MINING
- Structured versus unstructured data
ADB44
Data-mining workflow and evaluation
- CRISP-DM workflow
- Business understanding first
- Decision/success criteria
- Data understanding → preparation
- Modeling → evaluation → deployment
- Iterative process
- Preparation → cleaning/integration/feature selection
- Business problem precedes preparation
👁 Full explanation
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. - Model evaluation and leakage
- Appropriate data split
- Train on training data
- Tune on validation
- Evaluate once on held-out test
- Before deployment
- Testing/evaluation labels can overlap
- Training→testing→evaluation→deployment needs convention
- Model-selection evaluation may precede final testing
- Deployment monitoring
- Return to preparation/modeling when needed
- No future/test outcome leakage
👁 Full explanation
Within modeling, split data appropriately, train on training data, tune using validation and evaluate once on a held-out test set before deployment. Testing and evaluation are sometimes overlapping labels rather than universally distinct sequential phases. If an MCQ asks training→testing→evaluation→deployment, state that convention; another valid workflow can evaluate during model selection before final testing. Deployment includes monitoring and may trigger another preparation/modeling cycle. Do not leak future or test outcomes into training.
2 related practice questions
Note references: ADB-CRISP
- CRISP-DM workflow
ADB45
Repair wording before trusting answer keys
- Overlapping answer mechanisms
- ARIES/deferred update both log-based
- Restart → checkpoints/rollback/redo cooperation
- NO-UNDO/REDO = redo-only
- Best/important/common/faster → criterion/workload
- Literal scope over familiar-term uniqueness
👁 Full explanation
Database mechanisms can overlap: ARIES and deferred update can both be log-based; restart recovery can use checkpoints, rollback and redo; NO-UNDO/REDO equals redo-only. Questions using best, important, common or faster need a workload and comparison criterion. Read the literal scope rather than treating one familiar term as automatically unique. - Minimum missing assumptions
- Vendor behavior → exact DBMS/version
- Constructors → OID/type convention
- Waiting → requester/holder ages
- Recovery → durable log/page state/policy
- Distribution → fragments/network cost
- Multiple true descriptions → genuine ambiguity
- Index → question-specific clarifications/corrections
- No whole-question reproduction needed
👁 Full explanation
State the minimum missing information: exact DBMS/version for vendor behavior; OID/type convention for object constructors; requester/holder ages for waiting; durable log, page state and policy for recovery; fragment definitions and network cost for distribution. Evaluate each description separately when several database mechanisms apply. Use the stated assumptions to explain which database mechanisms apply to each question.
36 related practice questions
Note references: ADB-MOD
- Overlapping answer mechanisms
No matching topics. Clear the search or change the filter.
Blueprint learning outcomes
- A1 · Describe the main concepts of the object oriented model
ADB01, ADB02, ADB03, ADB04, ADB05 - A2 · Use different recovery methods when there is a database failure
ADB26, ADB27, ADB28, ADB29, ADB30, ADB31, ADB32 - A3 · Design a distributed database system in homogenous and heterogeneous environments
ADB33, ADB34, ADB35, ADB36, ADB37, ADB38, ADB39, ADB40 - A4 · Evaluate a set of query processing strategies
ADB17, ADB18, ADB19, ADB20, ADB21, ADB22, ADB23, ADB38