📘 1.

Basic Concepts
🔹 DBMS Architecture
●​ Single-Tier Architecture: All tasks on one machine.​

●​ Two-Tier Architecture: Client connects directly to the DB server.​

●​ Three-Tier Architecture:​

○​ Presentation Tier: User interface (e.g., browser).​

○​ Application Tier: Business logic (e.g., API).​

○​ Database Tier: Database server.​

🔹 ER Model (Entity-Relationship)
●​ Entity: Real-world object (e.g., Student, Book).​

●​ Attribute: Property of an entity (e.g., Name, Roll No.).​

●​ Relationship: Association among entities (e.g., Borrows).​

●​ Types of Attributes:​

○​ Simple, Composite, Derived, Multivalued​

●​ Types of Relationships:​

○​ One-to-One, One-to-Many, Many-to-Many​

●​ Keys:​

○​ Primary Key: Unique identifier​

○​ Foreign Key: Refers to primary key in another table​

📘 2. Relational Models
🔹 Relational Model
●​ Data is stored in tables (relations) consisting of rows (tuples) and columns
(attributes).​

●​ Each table has a unique name.​

🔹 Relational Algebra
●​ Procedural query language to retrieve data.​

●​ Operators:​

○​ Select (σ): Filter rows​

○​ Project (π): Select columns​

○​ Union (∪), Set Difference (−), Intersection (∩)​

○​ Cartesian Product (×)​

○​ Rename (ρ)​

○​ Join (⨝)​

🔹 SQL (Structured Query Language)

●​ DDL (Data Definition Language): CREATE, ALTER, DROP​

●​ DML (Data Manipulation Language): INSERT, UPDATE, DELETE, SELECT​

●​ DCL (Data Control Language): GRANT, REVOKE​

●​ TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT​

🔹 Constraints in RDBMS
 ●​ Primary Key: Uniquely identifies each tuple.​

●​ Foreign Key: Maintains referential integrity.​

●​ Not Null, Unique, Check, Default​

🔹 Views
●​ Virtual table derived from base tables.​

Created using:​
​
sql​
Copy code​
CREATE VIEW view_name AS
SELECT column1, column2 FROM table WHERE condition;

●​

🔹 Updates in RDBMS
●​ UPDATE table SET column=value WHERE condition;​

●​ INSERT INTO table VALUES (...);​

●​ DELETE FROM table WHERE condition;​

🔹 Integrity and Security

●​ Integrity:​

○​ Entity integrity: No primary key can be NULL.​

○​ Referential integrity: Foreign keys must match primary keys.​

●​ Security:​
 ○​ Access controls using GRANT and REVOKE.​

📘 3. Functional Dependencies and Normalization

🔹 Functional Dependency (FD)
●​ A → B means if A is known, B is determined.​

●​ Trivial FD: B ⊆ A​

●​ Non-Trivial FD: B ⊄ A​

🔹 Normalization
Process of removing redundancy and anomalies.

Normal Form Condition

1NF No multivalued attributes

2NF 1NF + No partial dependency

3NF 2NF + No transitive dependency

BCNF Every determinant is a candidate

key

🔹 Lossless Join Decomposition

●​ Decomposition where no information is lost.​

●​ Ensures:​

○​ R = R1 ⨝ R2​

🔹 Dependency Preserving Decomposition

●​ After decomposition, all FDs should still be enforceable without join.
📘 1. Data Storage and Query Processing
🔹 Types of Indexes
Indexes improve data retrieval speed without scanning the whole table.

🔸 Primary Index:
●​ Based on the primary key of the table.​

●​ Dense Index: Every search key value appears in the index.​

●​ Sparse Index: Index contains entries only for some records.​

🔸 Secondary Index:
●​ Index on non-primary key attributes.​

🔸 Clustered Index:
●​ Data is physically sorted according to the indexed column.​

🔸 Non-clustered Index:
●​ Index structure is separate from data storage.​

🔹 B-Tree
●​ Balanced multi-level index tree​

●​ Each node can have multiple children (m-way tree).​

●​ Maintains sorted data and allows search, sequential access, insertions, and deletions
in O(log n) time.​

●​ Keys in nodes guide the search path.​

Properties:

●​ Height-balanced​
 ●​ Internal nodes store keys and child pointers.​

●​ Leaves contain keys or pointers to records.​

🔹 B+ Tree
●​ An extension of B-tree used in DBMS for efficient range queries.​

●​ All records are stored at the leaf level.​

●​ Internal nodes contain only keys and pointers.​

Advantages:

●​ Faster access in range-based queries​

●​ Leaf nodes are linked → fast sequential access​

●​ Ideal for database indexing​

📘 2. Transaction Management
🔹 Transaction
●​ A transaction is a sequence of operations treated as a single logical unit of work.​

●​ ACID Properties:​

○​ Atomicity: All or nothing​

○​ Consistency: Maintain integrity​

○​ Isolation: Concurrent transactions don't interfere​

○​ Durability: Once committed, changes are permanent​

🔹 Schedule and Recoverability

Schedule:

●​ A sequence of operations from multiple transactions.​

Recoverable Schedule:

●​ A schedule where a transaction commits only after all transactions from which it has
read data have committed.​

Cascadeless Schedule:

●​ A recoverable schedule with no cascading rollbacks (a transaction does not read

uncommitted data).​

🔹 Serializability and Schedules

●​ Serial Schedule: Executes one transaction at a time.​

●​ Serializable Schedule: Concurrent execution but equivalent to some serial

execution.​

○​ Conflict Serializability: Based on conflicting operations (read-write,

write-read, write-write).​

○​ View Serializability: Based on view equivalence of read/write sets.​

📘 3. Concurrency Control
Concurrency control manages simultaneous execution of transactions to avoid:

●​ Lost updates​

●​ Temporary inconsistency​

●​ Uncommitted data read (dirty read)​

🔹 Types of Locks
 ●​ Shared Lock (S): For reading data.​

●​ Exclusive Lock (X): For writing/updating data.​

🔹 Two-Phase Locking (2PL)

●​ Phase 1: Growing Phase – acquire all the locks.​

●​ Phase 2: Shrinking Phase – release locks; no new lock can be acquired.​

➡️ Guarantees conflict serializability.

🔹 Deadlock
Occurs when two or more transactions wait indefinitely for each other’s resources.

Deadlock Prevention:

●​ Wait-Die​

●​ Wound-Wait​

Deadlock Detection:

●​ Using Wait-for Graph (WFG)​

🔹 Timestamp-Based Concurrency Control

●​ Each transaction gets a unique timestamp.​

●​ Older transactions get priority.​

●​ Ensures serializability based on timestamp order.​

📘 4. Recovery Techniques
Used to bring the database to a consistent state after failures.

🔹 Immediate Update vs. Deferred Update

Technique Description

Immediate Changes are applied to DB immediately; logs are written first

Update (Write-ahead logging).

Deferred Updates are delayed until the transaction reaches commit. No

Update changes made before commit.

🔹 Shadow Paging
●​ Maintains two page tables: current and shadow.​

●​ Changes are made on a new page table.​

●​ Old data is preserved until commit.​

Pros:

●​ No need for log records​

Cons:​

●​ Space overhead​

Multiple Choice Questions: Database Management Systems (DBMS)

Part 1: Basic Concepts & ER Model

1.​ DBMS Architecture Which layer of the DBMS architecture is responsible for
handling user requests and translating them into operations on the physical
database? a) External Schema b) Conceptual Schema c) Internal Schema d) Data
Definition Language (DDL)​
​
Answer: c) Internal Schema​
2.​ The three-schema architecture provides data independence. Which type of data
independence allows changes in the conceptual schema without affecting external
schemas? a) Physical Data Independence b) Logical Data Independence c) View
Data Independence d) Instance Data Independence​
​
Answer: b) Logical Data Independence​

3.​ Which component of a DBMS is responsible for creating and maintaining the data
dictionary? a) Query Processor b) Storage Manager c) DDL Interpreter d)
Transaction Manager​
​
Answer: c) DDL Interpreter​

4.​ A database administrator (DBA) is primarily responsible for: a) Developing

application programs. b) Designing the database schema. c) Managing software and
hardware. d) All of the above.​
​
Answer: d) All of the above.​

5.​ The overall design of the database is called the: a) Schema b) Instance c) Tuple d)
Attribute​
​
Answer: a) Schema​

6.​ ER Model In an ER model, a "student" can be an entity, and "roll number" can be its:
a) Relationship b) Attribute c) Weak Entity d) Primary Key (more specific, but attribute
is generally correct for ER)​
​
Answer: b) Attribute​

7.​ A relationship type in an ER model represents: a) An association among entity types.

b) A collection of entities. c) A property of an entity. d) A table in a relational
database.​
​
Answer: a) An association among entity types.​

8.​ In an ER diagram, a double rectangle represents a: a) Strong Entity b) Weak Entity c)

Relationship d) Attribute​
​
Answer: b) Weak Entity​

9.​ Which of the following describes a many-to-many relationship? a) One department

has many employees. b) One student enrolls in many courses, and one course has
many students. c) One person owns one car. d) One book has many chapters, and
each chapter belongs to one book.​
​
Answer: b) One student enrolls in many courses, and one course has many
 students.​

10.​A composite attribute in an ER model is an attribute that: a) Cannot be divided into

smaller sub-parts. b) Can be divided into smaller sub-parts, each with its own
meaning. c) Is unique for each entity. d) Represents a relationship.​
​
Answer: b) Can be divided into smaller sub-parts, each with its own meaning.​

11.​An attribute that can have multiple values for a single entity instance is called a: a)
Simple attribute b) Composite attribute c) Multivalued attribute d) Derived attribute​
​
Answer: c) Multivalued attribute​

12.​A derived attribute is an attribute whose value: a) Is stored directly in the database.
b) Is computed from other attributes. c) Has multiple values. d) Is part of a composite
attribute.​
​
Answer: b) Is computed from other attributes.​

13.​What symbol is used to represent an entity set in an ER diagram? a) Diamond b)

Rectangle c) Oval d) Double Oval​
​
Answer: b) Rectangle​

14.​The term "cardinality" in an ER model refers to: a) The number of attributes in an

entity. b) The number of entities in a relationship. c) The number of tuples in a
relation. d) The maximum number of relationship instances that an entity can
participate in.​
​
Answer: d) The maximum number of relationship instances that an entity can
participate in.​

15.​Total participation in a relationship is represented by: a) A single line b) A double line

c) A dashed line d) An arrow​
​
Answer: b) A double line​

Part 2: Relational Models & Normalization

16.​Relational Model Constraints Which constraint ensures that no primary key value
can be NULL? a) Referential Integrity b) Entity Integrity c) Domain Constraint d) Key
Constraint​
​
Answer: b) Entity Integrity​
17.​A foreign key is used to enforce which type of integrity constraint? a) Entity Integrity
b) Domain Constraint c) Referential Integrity d) Not Null Constraint​
​
Answer: c) Referential Integrity​

18.​A superkey is a set of attributes that: a) Uniquely identifies a tuple in a relation. b)

Uniquely identifies a tuple and is minimal. c) Can be NULL. d) Refers to a primary
key in another relation.​
​
Answer: a) Uniquely identifies a tuple in a relation.​

19.​A candidate key is a superkey for which: a) All attributes are NULL. b) All attributes
are foreign keys. c) No proper subset of its attributes is also a superkey. d) It refers to
another table.​
​
Answer: c) No proper subset of its attributes is also a superkey.​

20.​Relational Algebra Which relational algebra operation combines rows from two
relations? a) SELECT b) PROJECT c) UNION d) JOIN​
​
Answer: c) UNION​

21.​The relational algebra operation that removes duplicate tuples and selects specific
columns is: a) SELECT b) PROJECT c) RENAME d) CARTESIAN PRODUCT​
​
Answer: b) PROJECT​

22.​Which relational algebra operation is used to select tuples that satisfy a given
predicate? a) σ (Select) b) π (Project) c) ∪ (Union) d) × (Cartesian Product)​
​
Answer: a) σ (Select)​

23.​The result of a JOIN operation between two relations R1​and R2​contains: a) Only
common attributes. b) All attributes from R1​and R2​. c) Attributes from R1​and R2​
based on a join condition. d) Only unique attributes.​
​
Answer: c) Attributes from R1​and R2​based on a join condition.​

24.​Which of the following relational algebra operations is equivalent to a division

operation for finding suppliers who supply all parts? a) Product followed by select b)
Project followed by join c) Minus followed by project, then another minus d) Union
followed by intersection​
​
Answer: c) Minus followed by project, then another minus (conceptual
implementation of division)​

25.​SQL: Queries, Updates, Views, Integrity, Security Which SQL command is used to
retrieve data from a database? a) INSERT b) UPDATE c) DELETE d) SELECT​
 ​
Answer: d) SELECT​

26.​Which clause is used with the SELECT statement to filter records based on a
specified condition? a) GROUP BY b) ORDER BY c) WHERE d) HAVING​
​
Answer: c) WHERE​

27.​Which SQL command is used to add new rows to a table? a) ALTER TABLE b)
UPDATE c) INSERT INTO d) ADD ROW​
​
Answer: c) INSERT INTO​

28.​To change existing data in a table, you use the SQL command: a) CREATE TABLE b)
UPDATE c) MODIFY d) ALTER​
​
Answer: b) UPDATE​

29.​A virtual table based on the result-set of an SQL statement is called a: a) Index b)
View c) Trigger d) Stored Procedure​
​
Answer: b) View​

30.​Which SQL statement is used to remove a view from the database? a) DELETE
VIEW b) REMOVE VIEW c) DROP VIEW d) ALTER VIEW​
​
Answer: c) DROP VIEW​

31.​To grant specific privileges to a user, which SQL command is used? a) GRANT b)
ADD USER c) SET PERMISSION d) ALLOW​
​
Answer: a) GRANT​

32.​What is the purpose of the REVOKE command in SQL? a) To create a new user
account. b) To remove a user from the database. c) To remove privileges from a user.
d) To refresh the database schema.​
​
Answer: c) To remove privileges from a user.​

33.​Which of the following ensures that data remains consistent and correct in a
database? a) Query Optimization b) Data Redundancy c) Data Integrity d) Data
Definition​
​
Answer: c) Data Integrity​

34.​A CHECK constraint in SQL is used to: a) Ensure uniqueness of values. b) Enforce
referential integrity. c) Restrict the values that can be placed in a column. d)
 Automatically generate unique identifiers.​
​
Answer: c) Restrict the values that can be placed in a column.​

35.​The ON DELETE CASCADE option in a foreign key constraint ensures that: a) If a row
in the parent table is deleted, corresponding rows in the child table are also deleted.
b) If a row in the parent table is deleted, corresponding rows in the child table are set
to NULL. c) Deletion in the parent table is prevented if there are matching rows in the
child table. d) A log of deleted rows is maintained.​
​
Answer: a) If a row in the parent table is deleted, corresponding rows in the
child table are also deleted.​

36.​Functional Dependencies and Normalization A functional dependency A→B

means: a) B determines A. b) A determines B. c) A and B are independent. d) A is a
subset of B.​
​
Answer: b) A determines B.​

37.​A relation is in 1NF if: a) It has no partial dependencies. b) It has no transitive

dependencies. c) All attributes contain atomic values. d) It has a single primary key.​
​
Answer: c) All attributes contain atomic values.​

38.​A relation is in 2NF if it is in 1NF and: a) Every non-key attribute is fully functionally
dependent on the primary key. b) It has no transitive dependencies. c) It has no
multivalued dependencies. d) It has only one candidate key.​
​
Answer: a) Every non-key attribute is fully functionally dependent on the
primary key.​

39.​A relation is in 3NF if it is in 2NF and: a) It has no partial dependencies. b) It has no

transitive dependencies. c) All attributes are atomic. d) It has only one primary key.​
​
Answer: b) It has no transitive dependencies.​

40.​BCNF (Boyce-Codd Normal Form) is a stronger form of: a) 1NF b) 2NF c) 3NF d)
4NF​
​
Answer: c) 3NF​

41.​A relation is in BCNF if for every non-trivial functional dependency X→Y, X is a: a)

Candidate key. b) Primary key. c) Foreign key. d) Non-key attribute.​
​
Answer: a) Candidate key.​
 42.​What is the main objective of normalization in a relational database? a) To increase
data redundancy. b) To improve query performance. c) To reduce data redundancy
and improve data integrity. d) To make the database schema more complex.​
​
Answer: c) To reduce data redundancy and improve data integrity.​

43.​A decomposition is considered lossless-join if: a) The natural join of the

decomposed relations is equal to the original relation. b) Information is lost when
joining the decomposed relations. c) It preserves all functional dependencies. d) It
results in more relations.​
​
Answer: a) The natural join of the decomposed relations is equal to the
original relation.​

44.​A decomposition is dependency-preserving if: a) All original functional

dependencies can be inferred from the functional dependencies in the decomposed
relations. b) No functional dependencies are preserved. c) It always results in a
lossless join. d) It reduces the number of functional dependencies.​
​
Answer: a) All original functional dependencies can be inferred from the
functional dependencies in the decomposed relations.​

45.​Which normal form deals with multivalued dependencies? a) 1NF b) 2NF c) 3NF d)
4NF​
​
Answer: d) 4NF​

Part 3: Data Storage and Query Processing

46.​Different Types of Indexes Which type of index is created automatically when a

primary key is defined on a table? a) Non-clustered index b) Clustered index c) Hash
index d) Bitmap index​
​
Answer: b) Clustered index​

47.​In a clustered index, the physical order of the data rows on disk: a) Is random. b) Is
determined by the order of the index key. c) Is independent of the index. d) Is only
applicable to views.​
​
Answer: b) Is determined by the order of the index key.​

48.​A non-clustered index: a) Stores the actual data rows at the leaf level. b) Stores
pointers to the actual data rows at the leaf level. c) Can only be created on a primary
key. d) Allows only one per table.​
​
 Answer: b) Stores pointers to the actual data rows at the leaf level.​

49.​B-Tree - B+ Tree In a B-tree, data pointers can exist at: a) Only leaf nodes. b) Only
internal nodes. c) Both leaf and internal nodes. d) Only the root node.​
​
Answer: c) Both leaf and internal nodes.​

50.​The primary advantage of a B+ Tree over a B-Tree for database indexing is: a)
Faster insertion and deletion. b) Better sequential access performance. c) Reduced
memory usage. d) Support for non-key searches.​
​
Answer: b) Better sequential access performance.​

51.​In a B+ Tree, all data pointers are stored at the: a) Root node. b) Internal nodes. c)
Leaf nodes. d) Any node randomly.​
​
Answer: c) Leaf nodes.​

52.​The leaf nodes of a B+ Tree are typically linked together: a) To form a hash table. b)
To facilitate efficient range queries. c) To create a circular list. d) To support random
access only.​
​
Answer: b) To facilitate efficient range queries.​

53.​What is the fanout (order) of a B-tree or B+ tree? a) The number of data records. b)
The maximum number of children a node can have. c) The height of the tree. d) The
number of unique keys.​
​
Answer: b) The maximum number of children a node can have.​

54.​If a B+ tree has a height of h and a fanout of m, the maximum number of disk I/Os to
retrieve a record is approximately: a) m b) h c) m×h d) logm​N (where N is number of
records)​
​
Answer: b) h​

55.​Which type of index is best suited for columns that have a small number of distinct
values (e.g., gender, true/false)? a) Clustered Index b) Unique Index c) Hash Index d)
Bitmap Index​
​
Answer: d) Bitmap Index​

Part 4: Transaction Management

56.​Schedule and Recoverability, Serializability and Schedules A transaction in a
DBMS is an atomic unit of work that either completes entirely or has no effect at all.
This property is known as: a) Consistency b) Isolation c) Atomicity d) Durability​
​
Answer: c) Atomicity​

57.​The ACID properties of transactions stand for: a) Atomicity, Consistency, Isolation,

Durability b) Accuracy, Concurrency, Integrity, Durability c) Atomic, Concurrent,
Isolated, Dependent d) Accessible, Coherent, Independent, Distributed​
​
Answer: a) Atomicity, Consistency, Isolation, Durability​

58.​A schedule is considered serializable if: a) Its result is equivalent to the result of
some serial schedule. b) All transactions commit. c) No deadlocks occur. d) All
transactions are executed concurrently.​
​
Answer: a) Its result is equivalent to the result of some serial schedule.​

59.​A schedule where transactions are executed one after another without any
interleaving is called a: a) Concurrent schedule b) Serializable schedule c) Serial
schedule d) Recoverable schedule​
​
Answer: c) Serial schedule​

60.​Which type of schedule ensures that if a transaction Tj​reads a data item written by
Ti​, then Ti​must commit before Tj​commits? a) Serial schedule b) Conflict serializable
schedule c) Recoverable schedule d) Strict schedule​
​
Answer: c) Recoverable schedule​

61.​A schedule is conflict serializable if: a) It can be transformed into a serial schedule
by swapping non-conflicting operations. b) It can be transformed into a serial
schedule by swapping any operations. c) It involves only read operations. d) It has no
conflicting operations.​
​
Answer: a) It can be transformed into a serial schedule by swapping
non-conflicting operations.​

62.​Two operations conflict if they: a) Belong to the same transaction. b) Access the
same data item, and at least one of them is a write operation. c) Are both read
operations on the same data item. d) Are executed by different users.​
​
Answer: b) Access the same data item, and at least one of them is a write
operation.​

63.​Which type of schedule avoids cascading rollbacks? a) Recoverable schedule b)

Conflict serializable schedule c) Strict schedule d) Non-recoverable schedule​
 ​
Answer: c) Strict schedule​

64.​Concurrency Control The main goal of concurrency control mechanisms is to: a)

Maximize disk I/O. b) Ensure atomicity and durability. c) Allow multiple transactions to
execute concurrently while maintaining database consistency and isolation. d)
Reduce the number of transactions.​
​
Answer: c) Allow multiple transactions to execute concurrently while
maintaining database consistency and isolation.​

65.​Types of Locks A transaction holds a ________ lock on a data item X if it can read
X but no other transaction can write X. a) Exclusive (X) b) Shared (S) c) Intent (I) d)
Update (U)​
​
Answer: b) Shared (S)​

66.​If a transaction holds an exclusive lock on data item X, what can other transactions
do with X? a) Read X. b) Write X. c) Both read and write X. d) Neither read nor write
X.​
​
Answer: d) Neither read nor write X.​

67.​Two-Phase Locking (2PL) The Two-Phase Locking (2PL) protocol ensures: a)

Recoverability. b) Deadlock freedom. c) Conflict serializability. d) Both recoverability
and deadlock freedom.​
​
Answer: c) Conflict serializability.​

68.​The two phases of 2PL are: a) Read phase and Write phase. b) Growing phase and
Shrinking phase. c) Acquire phase and Release phase. d) Commit phase and Abort
phase.​
​
Answer: b) Growing phase and Shrinking phase.​

69.​In the shrinking phase of 2PL, a transaction: a) Can acquire new locks. b) Can
release existing locks. c) Can acquire new locks and release existing locks. d)
Cannot perform any operations.​
​
Answer: b) Can release existing locks.​

70.​Strict Two-Phase Locking (Strict 2PL) ensures: a) Recoverability and deadlock

freedom. b) Conflict serializability and strict recoverability. c) Lower concurrency than
basic 2PL. d) Non-cascading rollbacks.​
​
Answer: b) Conflict serializability and strict recoverability.​
71.​Deadlock A deadlock occurs when: a) A transaction attempts to acquire a lock it
already holds. b) Two or more transactions are each waiting for a lock held by
another transaction in the cycle. c) A transaction is aborted. d) There is insufficient
disk space.​
​
Answer: b) Two or more transactions are each waiting for a lock held by
another transaction in the cycle.​

72.​Which of the following is a method for handling deadlocks? a) Deadlock prevention b)

Deadlock detection and recovery c) Deadlock avoidance d) All of the above​
​
Answer: d) All of the above​

73.​The "wait-die" and "wound-wait" schemes are used for: a) Deadlock prevention. b)
Deadlock detection. c) Deadlock recovery. d) Ensuring serializability.​
​
Answer: a) Deadlock prevention.​

74.​If a system uses deadlock detection, what typically happens when a deadlock is
detected? a) The system reboots. b) One or more transactions are aborted
(victimized) to break the cycle. c) All transactions are committed. d) Locks are
automatically released.​
​
Answer: b) One or more transactions are aborted (victimized) to break the
cycle.​

75.​Timestamp-Based Concurrency Control In timestamp-based concurrency control,

each transaction is assigned a unique timestamp. This timestamp is used to: a)
Order the execution of operations. b) Identify the transaction's priority. c) Determine
the transaction's commit time. d) Generate unique primary keys.​
​
Answer: a) Order the execution of operations.​

76.​If TS(Ti) < TS(Tj) (timestamp of Ti​is less than timestamp of Tj​), what typically
happens if Tj​tries to read a data item written by Ti​before Ti​writes it? a) Tj​proceeds.
b) Tj​waits. c) Tj​is aborted. d) Ti​is aborted.​
​
Answer: b) Tj​waits (assuming a basic timestamp ordering rule for read-write
conflicts)​

77.​A timestamp ordering protocol ensures: a) Deadlock freedom. b) Conflict

serializability. c) Recoverability. d) Strict recoverability.​
​
Answer: b) Conflict serializability.​

78.​The primary drawback of basic timestamp ordering is: a) It can lead to cascading
rollbacks. b) It can lead to deadlocks. c) It is difficult to implement. d) It generates
 long schedules.​
​
Answer: a) It can lead to cascading rollbacks.​

79.​Recovery Techniques The purpose of the recovery manager in a DBMS is to: a)

Optimize queries. b) Ensure that the database remains consistent despite failures. c)
Manage user permissions. d) Control concurrent access.​
​
Answer: b) Ensure that the database remains consistent despite failures.​

80.​A WAL (Write-Ahead Logging) protocol dictates that: a) All writes must be in memory
before they are logged. b) All log records for a change must be written to stable
storage before the corresponding data changes are written to disk. c) Writes should
be deferred until commit. d) Reads are always processed first.​
​
Answer: b) All log records for a change must be written to stable storage
before the corresponding data changes are written to disk.​

81.​In the Immediate Update recovery technique: a) All updates are written to disk only
after the transaction commits. b) Updates are written to the database disk as they
occur, before commit. c) Updates are stored in a shadow copy of the database. d) No
logging is required.​
​
Answer: b) Updates are written to the database disk as they occur, before
commit.​

82.​To undo an operation in an Immediate Update system, the recovery manager uses:
a) The system's backup. b) UNDO log records. c) REDO log records. d) Checkpoints.​
​
Answer: b) UNDO log records.​

83.​In the Deferred Update recovery technique: a) All updates are written to disk as they
occur. b) Updates are temporarily stored in a local buffer and written to the database
disk only when the transaction commits. c) Shadow paging is the primary
mechanism. d) No logging is performed.​
​
Answer: b) Updates are temporarily stored in a local buffer and written to the
database disk only when the transaction commits.​

84.​In a Deferred Update system, what is generally NOT required for recovery after a
crash? a) REDO log records b) UNDO log records c) Transaction log d) Checkpoints​
​
Answer: b) UNDO log records (since updates are only written on commit, an
abort just means not writing them)​

85.​Shadow Paging Shadow Paging is a recovery technique that avoids: a) The need
for logging. b) The need for undo operations. c) The need for redo operations. d)
 Both undo and redo operations.​
​
Answer: d) Both undo and redo operations.​

86.​In Shadow Paging, a "shadow directory" points to: a) The most recent version of
database pages. b) The previous (consistent) version of database pages. c) A copy
of the log file. d) The backup of the database.​
​
Answer: b) The previous (consistent) version of database pages.​

87.​When a transaction commits in Shadow Paging, the current directory becomes the:
a) Backup directory. b) New shadow directory. c) Previous shadow directory. d)
Stable directory.​
​
Answer: d) Stable directory (or the new 'shadow' is promoted to current, and
the old 'current' is discarded or becomes the shadow for the next transaction).​

88.​The main disadvantage of Shadow Paging is: a) It requires extensive logging. b) It

can be expensive to manage disk space, leading to fragmentation. c) It does not
guarantee atomicity. d) It cannot handle concurrent transactions.​
​
Answer: b) It can be expensive to manage disk space, leading to
fragmentation.​

89.​A checkpoint in database recovery is used to: a) Rollback all active transactions. b)
Reduce the amount of log that needs to be scanned during recovery. c) Initiate a new
transaction. d) Create a full backup of the database.​
​
Answer: b) Reduce the amount of log that needs to be scanned during
recovery.​

90.​During the UNDO phase of recovery after a system crash, the recovery manager
processes log records in: a) Forward chronological order. b) Reverse chronological
order. c) Random order. d) Alphabetical order.​
​
Answer: b) Reverse chronological order.​

91.​During the REDO phase of recovery after a system crash, the recovery manager
processes log records in: a) Forward chronological order. b) Reverse chronological
order. c) Random order. d) Alphabetical order.​
​
Answer: a) Forward chronological order.​

92.​The BEGIN TRANSACTION, COMMIT, and ROLLBACK commands are part of: a) DDL
(Data Definition Language) b) DML (Data Manipulation Language) c) TCL
(Transaction Control Language) d) DCL (Data Control Language)​
​
 Answer: c) TCL (Transaction Control Language)​

93.​A transaction is in the partially committed state when: a) It has successfully

executed all its operations. b) Its changes have been written to disk. c) It has started
executing. d) It has been aborted.​
​
Answer: a) It has successfully executed all its operations.​

94.​If a transaction fails before its COMMIT point, it enters the: a) Committed state b)
Aborted state c) Active state d) Partially committed state​
​
Answer: b) Aborted state​

95.​The concept of Durability in ACID properties means that: a) Changes made by a

committed transaction are permanent and survive system failures. b) Transactions
execute in isolation. c) All operations in a transaction are completed or none are. d)
The database remains in a consistent state.​
​
Answer: a) Changes made by a committed transaction are permanent and
survive system failures.​

96.​Which problem is NOT directly addressed by concurrency control mechanisms? a)

Lost Update Problem b) Dirty Read Problem c) Phantom Read Problem d) Data
Definition Language (DDL) errors​
​
Answer: d) Data Definition Language (DDL) errors​

97.​A deadlock graph (wait-for graph) is used in: a) Deadlock prevention. b) Deadlock
detection. c) Deadlock avoidance. d) Concurrency serializability checking.​
​
Answer: b) Deadlock detection.​

98.​In timestamp ordering, the read_TS(X) for a data item X indicates: a) The largest
timestamp of any transaction that has successfully written X. b) The largest
timestamp of any transaction that has successfully read X. c) The timestamp of the
transaction currently holding a lock on X. d) The time when X was last accessed.​
​
Answer: b) The largest timestamp of any transaction that has successfully
read X.​

99.​In timestamp ordering, the write_TS(X) for a data item X indicates: a) The largest
timestamp of any transaction that has successfully written X. b) The largest
timestamp of any transaction that has successfully read X. c) The timestamp of the
transaction that last accessed X. d) The current time.​
​
Answer: a) The largest timestamp of any transaction that has successfully
 written X.​

100.​ A system crash resulting in loss of volatile storage but preserving non-volatile
storage is known as a: a) Disk failure b) Media failure c) System failure d) Power
failure​

**Answer: c) System failure**