Sign Up Sign Up


Have an account? Sign In Now

Sign In Sign In


Forgot Password?

Don't have account, Sign Up Here

Forgot Password Forgot Password

Lost your password? Please enter your email address. You will receive a link and will create a new password via email.


Have an account? Sign In Now

You must login to ask a question.


Forgot Password?

Need An Account, Sign Up Here

You must login to add post.


Forgot Password?

Need An Account, Sign Up Here

Please briefly explain why you feel this question should be reported.

Please briefly explain why you feel this answer should be reported.

Please briefly explain why you feel this user should be reported.

RTSALL Logo RTSALL Logo
Sign InSign Up

RTSALL

RTSALL Navigation

  • Home
  • Tutorials
    • All Subjects & Branches (90+)
    • C Programming MCQs
    • Python Programming MCQs
    • Data Structures MCQs
    • DBMS & SQL MCQs
    • Computer Networks MCQs
    • Operating Systems MCQs
    • Quantitative Aptitude MCQs
  • Interview
    • Interview Preparation Portal
    • Web API & REST API
    • SQL Server
    • System Design
    • C# (.NET 8/9)
    • ASP.NET MVC & Core
    • DSA (Data Structures)
    • Cyber Security
    • Burp Suite (AppSec)
  • Tools
    • Run Code
    • JSON Beautifier
    • Regex Tester
    • Diff Checker
    • JWT Decoder
    • UUID Generator
    • .htaccess Generator
    • YAML/JSON Converter
    • SQL Formatter
    • Cron Generator
    • JSON to CSV/Excel
    • System Design Estimator
    • Chmod Calculator
    • Duplicate Line Remover
  • DSA
    • All DSA Problems
    • Online C++ Runner
    • Arrays, Strings & Cache
    • Two Pointers & Sliding Window
    • Linked Lists & Custom Allocators
    • Stacks, Queues & Ring Buffers
    • Trees, BSTs & Indexes
    • Tries & Prefix Search
    • Heaps & Priority Schedulers
    • Hashing & Collision Resolution
    • Graphs & Network Topologies
    • Dynamic Programming
    • Advanced Bitmask & Tree DP
    • Greedy & Resource Allocation
    • Binary Search & State Spaces
    • Bit Manipulation & Low-Level
    • System-Scale & Probabilistic
  • AI Utilities
    • Token Counter
    • JSON Schema Compiler
    • Fine-Tuning JSONL Converter
    • Vector RAG Playground
    • Prompt Optimizer & Architect
    • LLM GPU VRAM Calculator
    • .NET AI Roadmap
  • Finance Tools
    • Compound Interest Calculator
    • SIP Calculator
    • Simple Interest Calculator
    • EMI Calculator
    • Present Value (PV) Calculator
    • Compound Interest Calculator
    • Future Value (FV) Calculator
    • NPV Calculator
    • IRR Calculator
    • CAGR Calculator
    • Dividend Income Calculator
    • Yield on Cost Calculator
    • Dividend Payout Ratio
    • WACC Calculator
    • CAPM & Cost of Equity
    • Cost of Debt Calculator
    • DCF Valuation Calculator
    • Enterprise Value Calculator
  • About Us
  • Blog
  • Contact Us
Search
Ask A Question

Mobile menu

Close
Ask a Question
  • Home
  • Tutorials
    • All Subjects & Branches (90+)
    • C Programming MCQs
    • Python Programming MCQs
    • Data Structures MCQs
    • DBMS & SQL MCQs
    • Computer Networks MCQs
    • Operating Systems MCQs
    • Quantitative Aptitude MCQs
  • Interview
    • Interview Preparation Portal
    • Web API & REST API
    • SQL Server
    • System Design
    • C# (.NET 8/9)
    • ASP.NET MVC & Core
    • DSA (Data Structures)
    • Cyber Security
    • Burp Suite (AppSec)
  • Tools
    • Run Code
    • JSON Beautifier
    • Regex Tester
    • Diff Checker
    • JWT Decoder
    • UUID Generator
    • .htaccess Generator
    • YAML/JSON Converter
    • SQL Formatter
    • Cron Generator
    • JSON to CSV/Excel
    • System Design Estimator
    • Chmod Calculator
    • Duplicate Line Remover
  • DSA
    • All DSA Problems
    • Online C++ Runner
    • Arrays, Strings & Cache
    • Two Pointers & Sliding Window
    • Linked Lists & Custom Allocators
    • Stacks, Queues & Ring Buffers
    • Trees, BSTs & Indexes
    • Tries & Prefix Search
    • Heaps & Priority Schedulers
    • Hashing & Collision Resolution
    • Graphs & Network Topologies
    • Dynamic Programming
    • Advanced Bitmask & Tree DP
    • Greedy & Resource Allocation
    • Binary Search & State Spaces
    • Bit Manipulation & Low-Level
    • System-Scale & Probabilistic
  • AI Utilities
    • Token Counter
    • JSON Schema Compiler
    • Fine-Tuning JSONL Converter
    • Vector RAG Playground
    • Prompt Optimizer & Architect
    • LLM GPU VRAM Calculator
    • .NET AI Roadmap
  • Finance Tools
    • Compound Interest Calculator
    • SIP Calculator
    • Simple Interest Calculator
    • EMI Calculator
    • Present Value (PV) Calculator
    • Compound Interest Calculator
    • Future Value (FV) Calculator
    • NPV Calculator
    • IRR Calculator
    • CAGR Calculator
    • Dividend Income Calculator
    • Yield on Cost Calculator
    • Dividend Payout Ratio
    • WACC Calculator
    • CAPM & Cost of Equity
    • Cost of Debt Calculator
    • DCF Valuation Calculator
    • Enterprise Value Calculator
  • About Us
  • Blog
  • Contact Us
Home/RTSALL Educational Academy: Subject MCQs, Practice Tests & Detailed Explanations/DBMS & SQL: 5-Chapter Comprehensive Practice & 75 Verified MCQs

DBMS & SQL: 5-Chapter Comprehensive Practice & 75 Verified MCQs

Home / Learning Platform / Computer Science & IT / Database Management Systems (DBMS) & SQL MCQs
Computer Science & IT 🟢 75 Verified MCQs • 5 Complete Chapters Active ⭐ Interview & Exam Grade

DBMS & SQL: 5-Chapter Comprehensive Practice & 75 Verified MCQs

Master Database Management Systems (DBMS) & SQL with our complete 5-chapter question bank. Each verified MCQ provides four distinct options, verified solution keys, step-by-step technical rationale, and high-yield conceptual rules.

Curriculum Roadmap & Chapters Covered (All Live & Unlocked):
✓ Chapter 1: Relational Schema, Primary Keys & Integrity Constraints (Q1 – Q15)
✓ Chapter 2: Normalization (1NF, 2NF, 3NF, BCNF & Multivalued Dependencies) (Q16 – Q30)
✓ Chapter 3: Advanced SQL, Window Functions, CTEs & Aggregations (Q31 – Q45)
✓ Chapter 4: Indexing (B+ Trees, Hash Indexes, Clustered vs Non-Clustered) (Q46 – Q60)
✓ Chapter 5: Transactions, ACID Properties, Concurrency Control & WAL Recovery (Q61 – Q75)
📝 Total Questions: 75 Verified MCQs
📚 Modules: 5 Complete Chapters
⏱️ Est. Practice Time: ~90 minutes
🎯 Target Benchmark: 80% (60/75 Correct)
Select Chapter to Practice:
Interactive Practice Dashboard
Answered: 0 / 75 Correct: 0 Accuracy: 0% Streak: 🔥 0
Currently Displaying: All 75 Questions across Chapters 1 to 5. Click any option for instant evaluation and in-depth explanations.
Curriculum Track • Module 1 of 5
Chapter 1: Relational Schema, Primary Keys & Integrity Constraints (Q1 – Q15)
Question 1 of 75 Chapter 1
Which normal form requires a table to be in 1NF and additionally ensures that no non-prime attribute is partially dependent on any candidate key?
A. First Normal Form (1NF)
B. Second Normal Form (2NF)
C. Third Normal Form (3NF)
D. Boyce-Codd Normal Form (BCNF)
✔ Correct Answer: Option B
Detailed Explanation: Second Normal Form (2NF) mandates that the relation is in 1NF and contains zero partial functional dependencies. A partial dependency occurs when a non-prime attribute depends on only a subset of a composite candidate key.
🎯 Key Learning Point: If all candidate keys in a table consist of a single column (no composite keys), the table is automatically in 2NF once it satisfies 1NF.
Question 2 of 75 Chapter 1
What is the defining condition for a relation R to satisfy Third Normal Form (3NF) for every non-trivial functional dependency X -> Y?
A. X must be a primary key and Y must be a foreign key.
B. Either X is a superkey of R, OR Y is a prime attribute (part of a candidate key).
C. X and Y must have identical column data types.
D. Y must be multivalued and dependent on X.
✔ Correct Answer: Option B
Detailed Explanation: 3NF eliminates transitive dependencies. For every non-trivial FD X -> Y, either X is a superkey (meaning X uniquely identifies rows), or every attribute in Y is prime (belongs to some candidate key).
🎯 Key Learning Point: 3NF always guarantees lossless join decomposition and dependency preservation, whereas BCNF may not always preserve all functional dependencies.
Question 3 of 75 Chapter 1
Which ACID property guarantees that all database updates within a transaction are either completely committed to disk or entirely rolled back if an error occurs?
A. Atomicity
B. Consistency
C. Isolation
D. Durability
✔ Correct Answer: Option A
Detailed Explanation: Atomicity enforces the ‘all-or-nothing’ principle: either every statement within the transaction executes successfully, or the entire transaction is aborted and the database reverts to its prior state via undo logs.
🎯 Key Learning Point: Atomicity is managed by the DBMS recovery subsystem using Write-Ahead Logging (WAL) and rollback segments.
Question 4 of 75 Chapter 1
Which transaction isolation level allows a transaction to read uncommitted changes made by concurrent transactions, exposing it to 'Dirty Reads'?
A. Serializable
B. Repeatable Read
C. Read Committed
D. Read Uncommitted
✔ Correct Answer: Option D
Detailed Explanation: ‘Read Uncommitted’ is the lowest ANSI SQL isolation level. It permits dirty reads because transactions read data directly from the buffer pool without checking whether the modifying transaction has committed or will eventually roll back.
🎯 Key Learning Point: ANSI SQL Isolation hierarchy: Read Uncommitted (allows dirty reads) < Read Committed < Repeatable Read (allows phantoms) < Serializable (strict serial order).
Question 5 of 75 Chapter 1
In Boyce-Codd Normal Form (BCNF), what strict rule must hold for every non-trivial functional dependency X -> Y?
A. Y must be a prime attribute.
B. X must be a superkey of the relation.
C. X and Y must be derived from the same domain.
D. The relation must contain at least two foreign keys.
✔ Correct Answer: Option B
Detailed Explanation: BCNF is a stricter variant of 3NF that drops the alternative clause allowing Y to be a prime attribute. In BCNF, for every functional dependency X -> Y, the determinant X must strictly be a superkey of the relation.
🎯 Key Learning Point: Every relation in BCNF is guaranteed to be in 3NF, but a relation in 3NF is not necessarily in BCNF.
Question 6 of 75 Chapter 1
What is the primary role of Write-Ahead Logging (WAL) in database crash recovery mechanisms?
A. It writes query execution plans to cache memory for faster lookups.
B. It guarantees that log records describing data changes are flushed to stable non-volatile storage before corresponding dirty database pages are written to disk.
C. It automatically normalizes table schemas at runtime.
D. It encrypts user passwords before hashing.
✔ Correct Answer: Option B
Detailed Explanation: The Write-Ahead Logging protocol dictates that log records recording any change must reach durable disk storage before the corresponding modified table data pages are written. This ensures the database can perform REDO or UNDO operations following an unexpected server crash.
🎯 Key Learning Point: WAL underpins database Durability (the ‘D’ in ACID) and the ARIES crash recovery algorithm.
Question 7 of 75 Chapter 1
In relational algebra, what operation is represented by the Greek letter Sigma (σ)?
A. Projection (choosing specific columns)
B. Selection (filtering rows satisfying a predicate condition)
C. Cartesian Product
D. Natural Join
✔ Correct Answer: Option B
Detailed Explanation: Sigma (σ) denotes the Selection operation in relational algebra, which filters tuples (rows) from a relation based on a specified boolean predicate condition (equivalent to SQL’s WHERE clause). Projection is denoted by Pi (π).
🎯 Key Learning Point: σ_condition(R) filters rows; π_columns(R) projects attributes; ⨝ denotes joins.
Question 8 of 75 Chapter 1
Under the Two-Phase Locking (2PL) protocol, what characterizes the 'shrinking phase' of a transaction?
A. The transaction may acquire new locks but cannot release any.
B. The transaction may only release existing locks and cannot acquire any new locks.
C. The transaction compresses table data to save storage.
D. The transaction executes rollback operations.
✔ Correct Answer: Option B
Detailed Explanation: Two-Phase Locking divides concurrency into two distinct phases: 1) Growing phase (locks are acquired, none released), and 2) Shrinking phase (locks are released, no new locks can be obtained). Once a transaction releases a single lock, it enters the shrinking phase and can never acquire another lock.
🎯 Key Learning Point: Basic 2PL guarantees serializability of concurrent schedules but does not prevent deadlocks or cascading aborts. Strict 2PL holds exclusive locks until commit to prevent cascading aborts.
Question 9 of 75 Chapter 1
What is a 'Phantom Read' anomaly in database transactions?
A. Reading data from a row that was never written.
B. A transaction re-executes a range query with identical search criteria and discovers new rows inserted and committed by another concurrent transaction.
C. Reading uncommitted data that is subsequently rolled back.
D. A query that returns corrupted binary data.
✔ Correct Answer: Option B
Detailed Explanation: A Phantom Read occurs when transaction T1 executes a range query (e.g., ‘SELECT WHERE age > 30’), and a concurrent transaction T2 inserts new rows satisfying that condition and commits. When T1 re-runs the range query, new ‘phantom’ rows appear.
🎯 Key Learning Point: Phantom reads are prevented under the ‘Serializable’ isolation level using range locks, predicate locking, or index-range gap locks.
Question 10 of 75 Chapter 1
How does a Clustered Index differ physically from a Non-Clustered (Secondary) Index in relational databases like SQL Server and MySQL (InnoDB)?
A. A clustered index stores only pointers to rows; non-clustered indexes store the actual table data.
B. A clustered index dictates the physical sorting and storage order of actual data rows in leaf pages; non-clustered indexes maintain a separate B+Tree containing index keys pointing back to data rows.
C. A table can have up to 16 clustered indexes but only 1 non-clustered index.
D. Clustered indexes do not support B+Tree structures.
✔ Correct Answer: Option B
Detailed Explanation: In a clustered index, the leaf nodes of the B+Tree ARE the actual data rows; hence a table can have only ONE clustered index because rows can be physically ordered in only one sequence. Non-clustered indexes are secondary trees whose leaves contain the indexed column plus a row locator (RID or clustered key).
🎯 Key Learning Point: Because clustered index leaves hold the data, range queries on the clustered index key avoid costly secondary bookmark/key lookups.
Question 11 of 75 Chapter 1
What type of dependency is eliminated when moving from Second Normal Form (2NF) to Third Normal Form (3NF)?
A. Partial dependency
B. Transitive dependency
C. Multivalued dependency
D. Join dependency
✔ Correct Answer: Option B
Detailed Explanation: 3NF specifically resolves transitive dependencies: situations where a non-prime attribute depends on another non-prime attribute (X -> Z where X -> Y and Y -> Z, with X being candidate key and Y non-prime).
🎯 Key Learning Point: 1NF removes repeating groups; 2NF removes partial key dependencies; 3NF removes transitive dependencies; 4NF removes multivalued dependencies.
Question 12 of 75 Chapter 1
In SQL, what is the consequence of executing 'DELETE FROM table_name' compared to 'TRUNCATE TABLE table_name'?
A. DELETE is a DDL operation; TRUNCATE is a DML operation.
B. DELETE removes rows row-by-row logging each deletion in the transaction log; TRUNCATE deallocates entire data pages with minimal logging and resets identity seeds.
C. DELETE cannot be rolled back under any circumstances.
D. TRUNCATE activates all configured DELETE triggers on the table.
✔ Correct Answer: Option B
Detailed Explanation: DELETE is a DML command that evaluates where-clauses, fires delete triggers, and logs individual row deletions, making it slower for massive datasets. TRUNCATE is a DDL operation that deallocates entire data storage pages, logs page deallocations minimally, resets identity seeds, and bypasses row-level triggers.
🎯 Key Learning Point: Both DELETE and TRUNCATE can be rolled back if executed inside an active explicit transaction, provided the transaction log is maintained.
Question 13 of 75 Chapter 1
What is the primary characteristic of an outer join (e.g., LEFT OUTER JOIN) compared to an INNER JOIN in SQL?
A. It returns only rows that have matching values in both tables.
B. It preserves all rows from the specified table (e.g. left), filling columns from the non-matching side with NULL values when join criteria fail.
C. It filters out duplicate rows automatically like a UNION.
D. It generates a full Cartesian product without evaluating predicates.
✔ Correct Answer: Option B
Detailed Explanation: A LEFT OUTER JOIN returns every record from the left table. If a matching record exists in the right table, right columns are populated; if no match exists, all columns from the right table are returned as NULL.
🎯 Key Learning Point: Use ‘LEFT JOIN … WHERE right_table.id IS NULL’ to find orphaned parent rows with zero associated child records.
Question 14 of 75 Chapter 1
In database normalization, when is a decomposition of a relation schema R into R1 and R2 guaranteed to be a 'Lossless-Join' decomposition?
A. When the intersection (R1 ∩ R2) is empty.
B. When the intersection of attributes (R1 ∩ R2) forms a superkey of at least one of the decomposed relations (R1 or R2).
C. When R1 and R2 contain an identical number of rows.
D. Only if no candidate keys exist.
✔ Correct Answer: Option B
Detailed Explanation: By definition, a decomposition of R into (R1, R2) is lossless-join with respect to functional dependencies F if and only if the common attributes (R1 ∩ R2) functionally determine all attributes in R1 ((R1 ∩ R2) -> R1) OR all attributes in R2 ((R1 ∩ R2) -> R2).
🎯 Key Learning Point: Lossy decompositions introduce spurious tuples when rejoined, destroying data integrity.
Question 15 of 75 Chapter 1
What is the purpose of the SQL 'HAVING' clause, and how does it fundamentally differ from the 'WHERE' clause?
A. HAVING filters individual rows before grouping; WHERE filters aggregated groups.
B. WHERE filters individual rows prior to GROUP BY aggregation; HAVING filters aggregated groups based on aggregate function results (e.g., COUNT(), AVG()).
C. HAVING can only be used on string data types.
D. WHERE requires subqueries, whereas HAVING cannot contain subqueries.
✔ Correct Answer: Option B
Detailed Explanation: In SQL logical query processing, WHERE filters raw rows before aggregation occurs. The GROUP BY clause then groups surviving rows into aggregated buckets. Finally, the HAVING clause filters those aggregated buckets based on aggregate conditions (e.g., ‘HAVING COUNT(*) > 5’).
🎯 Key Learning Point: Logical order: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT.
Curriculum Track • Module 2 of 5
Chapter 2: Normalization (1NF, 2NF, 3NF, BCNF & Multivalued Dependencies) (Q16 – Q30)
Question 16 of 75 Chapter 2
What is the primary objective of normalizing a relational database schema?
A. To maximize redundant data storage for faster read throughput
B. To eliminate data anomalies (insertion, deletion, update) and minimize unnecessary data redundancy
C. To automatically convert tables into non-relational document collections
D. To enforce software licensing keys at the column level
✔ Correct Answer: Option B
Detailed Explanation: Normalization is the systematic process of decomposing relations to minimize redundancy and eradicate update, insertion, and deletion anomalies without losing informational dependency.
🎯 Key Learning Point: Higher normal forms reduce anomalies at the trade-off of requiring more table joins during queries.
Question 17 of 75 Chapter 2
A relational table is in First Normal Form (1NF) if and only if:
A. It contains no foreign key references
B. Every attribute domain contains only atomic (indivisible) values and there are no repeating groups
C. All non-key attributes are fully dependent on the primary key
D. It has at least two candidate keys
✔ Correct Answer: Option B
Detailed Explanation: 1NF requires that each column contain only single scalar, atomic values (e.g., no comma-separated arrays or nested composite structures) and each record is unique.
🎯 Key Learning Point: 1NF disallows multi-valued attributes and composite attributes within individual table cells.
Question 18 of 75 Chapter 2
Under what condition is a relation in 1NF guaranteed to already be in Second Normal Form (2NF)?
A. When all columns are foreign keys
B. When the primary key consists of a single (simple) attribute rather than a composite key
C. When there are no NULL values allowed anywhere in the table
D. When table has fewer than 1,000 records
✔ Correct Answer: Option B
Detailed Explanation: 2NF requires that no non-prime attribute be partially dependent on any candidate key. If the primary key is a single attribute, partial dependency is mathematically impossible, so it is automatically in 2NF.
🎯 Key Learning Point: Partial dependency only occurs when a non-key attribute depends on a proper subset of a composite candidate key.
Question 19 of 75 Chapter 2
What defines Third Normal Form (3NF) in relational schema theory?
A. The relation is in 2NF and has no transitive dependencies for non-prime attributes
B. The relation has 3 separate foreign keys
C. The table size does not exceed 3 Gigabytes
D. Every attribute is part of a compound index
✔ Correct Answer: Option A
Detailed Explanation: A relation is in 3NF if it is in 2NF and for every functional dependency X -> A, either X is a superkey or A is a prime attribute (member of a candidate key), preventing X -> Y -> Z transitive chains among non-prime attributes.
🎯 Key Learning Point: 3NF eliminates transitive functional dependencies on candidate keys.
Question 20 of 75 Chapter 2
How does Boyce-Codd Normal Form (BCNF) differ strictly from Third Normal Form (3NF)?
A. BCNF allows transitive dependencies whereas 3NF strictly forbids them
B. In BCNF, for every non-trivial functional dependency X -> A, X must strictly be a Superkey (removing 3NF's exception where A can be a prime attribute)
C. BCNF applies exclusively to NoSQL key-value stores
D. BCNF requires denormalized reporting views
✔ Correct Answer: Option B
Detailed Explanation: 3NF allows X -> A if A is a prime attribute even when X is not a superkey. BCNF eliminates this loophole: for every functional dependency X -> A, X MUST be a superkey without exception.
🎯 Key Learning Point: Every relation in BCNF is in 3NF, but not every 3NF relation is in BCNF.
Question 21 of 75 Chapter 2
Which normal form specifically addresses and eliminates Multivalued Dependencies (MVDs)?
A. Second Normal Form (2NF)
B. Third Normal Form (3NF)
C. Fourth Normal Form (4NF)
D. Fifth Normal Form (5NF)
✔ Correct Answer: Option C
Detailed Explanation: Fourth Normal Form (4NF) handles multivalued dependencies (X ->-> Y). A relation is in 4NF if it is in BCNF and for every non-trivial multivalued dependency X ->-> Y, X is a superkey.
🎯 Key Learning Point: Fagin defined 4NF to eliminate anomalies caused by independent multi-valued facts about an entity.
Question 22 of 75 Chapter 2
Fifth Normal Form (5NF), also known as Project-Join Normal Form (PJNF), eliminates anomalies caused by:
A. Join Dependencies that cannot be inferred from candidate keys
B. Floating point precision rounding errors
C. Multi-threaded transaction conflicts
D. Unindexed foreign keys
✔ Correct Answer: Option A
Detailed Explanation: 5NF deals with Join Dependencies. A relation is in 5NF if every non-trivial join dependency in it is implied by the candidate keys, guaranteeing lossless join reconstruction when decomposed.
🎯 Key Learning Point: A relation decomposed into 3 or more tables without loss of information satisfies join dependency.
Question 23 of 75 Chapter 2
What is the concept of 'Lossless-Join Decomposition' in relational database design?
A. Joins execute in constant O(1) time
B. Decomposing relation R into R1 and R2 guarantees that natural joining R1 and R2 reconstructs the exact original relation R without generating spurious tuples
C. Foreign keys can never be dropped or renamed
D. Network bandwidth between database replicas is zero
✔ Correct Answer: Option B
Detailed Explanation: Lossless join ensures that R1 ⨝ R2 = R. According to Heath’s theorem, decomposition into R1 and R2 is lossless if and only if (R1 ∩ R2) -> R1 or (R1 ∩ R2) -> R2.
🎯 Key Learning Point: Spurious (hallucinated) rows are created if a decomposition violates the lossless-join property.
Question 24 of 75 Chapter 2
When is a decomposition of a relation considered 'Dependency Preserving'?
A. When all original foreign keys remain on a single machine
B. When every functional dependency in the original relation can be checked and verified by examining individual decomposed relations without computing multi-table joins
C. When data values are cryptographically hashed
D. When no cascading deletes occur
✔ Correct Answer: Option B
Detailed Explanation: Dependency preservation guarantees that the union of dependencies of all decomposed sub-relations logically entails the complete set of original dependencies F (i.e. (F1 ∪ F2)+ = F+).
🎯 Key Learning Point: 3NF decomposition always achieves dependency preservation and lossless join; BCNF guarantees lossless join but may NOT preserve dependencies.
Question 25 of 75 Chapter 2
Given Relation R(A, B, C, D) with FDs: {A -> B, B -> C, C -> D}. What is the Candidate Key of R?
A. {A}
B. {B}
C. {C, D}
D. {B, C}
✔ Correct Answer: Option A
Detailed Explanation: Computing attribute closure: A+ = {A, B, C, D}. Because A derives all attributes and no proper subset of A can do so, {A} is the minimal candidate key.
🎯 Key Learning Point: Attribute closure determines whether a set of attributes functionally determines all relation columns.
Question 26 of 75 Chapter 2
In relation R(A, B, C) where A is Primary Key and FD is {B -> C}. In which normal form is R?
A. 1NF and 2NF, but NOT 3NF (transitive dependency exists)
B. 3NF and BCNF
C. Unnormalized
D. 4NF
✔ Correct Answer: Option A
Detailed Explanation: The primary key is single-column A, so 2NF holds (no partial dependency). However, B is a non-prime attribute determining another non-prime attribute C (transitive dependency A -> B -> C), violating 3NF.
🎯 Key Learning Point: To reach 3NF, decompose R into R1(A, B) and R2(B, C).
Question 27 of 75 Chapter 2
What is Armstrong's Axiom of 'Augmentation' for functional dependencies?
A. If X -> Y, then XZ -> YZ for any attribute set Z
B. If X -> Y and Y -> Z, then X -> Z
C. If Y ⊆ X, then X -> Y
D. If X -> Y and X -> Z, then X -> YZ
✔ Correct Answer: Option A
Detailed Explanation: Augmentation states that adding common attributes to both sides preserves validity: If X -> Y holds, then XZ -> YZ holds for any attribute set Z.
🎯 Key Learning Point: Armstrong’s primary axioms are: Reflexivity, Augmentation, and Transitivity.
Question 28 of 75 Chapter 2
What is a 'Canonical Cover' (Minimal Cover) of a set of functional dependencies?
A. A zip archive of SQL schema files
B. A minimal, simplified equivalent set of functional dependencies having no redundant dependencies or extraneous attributes
C. The physical storage layout of primary keys on SSD
D. A table with zero foreign keys
✔ Correct Answer: Option B
Detailed Explanation: A canonical cover Fc for F has the same closure as F (Fc+ = F+), contains only singleton right-hand sides, has no extraneous attributes on left or right, and contains no redundant FDs.
🎯 Key Learning Point: Canonical covers are critical inputs to standard 3NF synthesis algorithms.
Question 29 of 75 Chapter 2
What is the primary operational trade-off of applying intentional 'Denormalization' in production OLAP data warehouses?
A. Eliminates all primary keys
B. Introduces controlled data redundancy to avoid expensive multi-table JOINs, accelerating analytical read query throughput
C. Reduces disk space consumption
D. Prevents user concurrency
✔ Correct Answer: Option B
Detailed Explanation: Denormalization deliberately combines related tables (e.g., Star Schema / Snowflake Schema) to eliminate JOIN overhead during complex analytical queries, trading off write performance and disk storage for read speed.
🎯 Key Learning Point: OLTP systems favor normalized 3NF/BCNF schemas; OLAP systems favor denormalized dimensional models.
Question 30 of 75 Chapter 2
If relation R(A, B, C) has Candidate Keys {A, B} and {B, C}, and FD {C -> A}, why is R in 3NF but NOT in BCNF?
A. It violates 1NF atomicity
B. In C -> A, C is not a superkey, but A is a prime attribute (part of candidate key {A, B}), satisfying 3NF but violating BCNF
C. It contains a cyclical loop
D. It has no primary key
✔ Correct Answer: Option B
Detailed Explanation: In 3NF, C -> A is permitted because attribute A is prime. However, BCNF strictly demands that the left-hand determinant C must be a superkey. Because C alone cannot determine all attributes, it fails BCNF.
🎯 Key Learning Point: This is the classic textbook illustration distinguishing 3NF and BCNF.
Curriculum Track • Module 3 of 5
Chapter 3: Advanced SQL, Window Functions, CTEs & Aggregations (Q31 – Q45)
Question 31 of 75 Chapter 3
What is the difference between RANK() and DENSE_RANK() window functions in SQL?
A. RANK() ignores NULLs; DENSE_RANK() includes NULLs
B. RANK() leaves gaps in sequence ranks after tied values; DENSE_RANK() assigns consecutive rank numbers without skipping gaps
C. DENSE_RANK() runs in O(N^2) time
D. RANK() works only on integers
✔ Correct Answer: Option B
Detailed Explanation: If two rows tie for 1st place, RANK() gives both 1 and assigns 3 to the next row (gap). DENSE_RANK() gives both 1 and assigns 2 to the next row (no gaps).
🎯 Key Learning Point: ROW_NUMBER() never ties; RANK() skips rank numbers after ties; DENSE_RANK() produces continuous sequence.
Question 32 of 75 Chapter 3
What is the result of applying a LEFT JOIN between Table A (10 rows) and Table B (5 rows) if none of the join conditions match?
A. 0 rows returned
B. 10 rows returned, with NULL values populated for all Table B columns
C. 5 rows returned
D. SQL Error: Unmatched Left Join Exception
✔ Correct Answer: Option B
Detailed Explanation: A LEFT JOIN preserves every row from the left table (Table A). When no matching record is found in Table B, NULL is substituted for all columns belonging to Table B.
🎯 Key Learning Point: LEFT JOIN guarantees cardinality >= row count of the left table.
Question 33 of 75 Chapter 3
How does the 'HAVING' clause differ from the 'WHERE' clause in SQL execution order?
A. WHERE filters individual rows before grouping; HAVING filters aggregated groups after GROUP BY evaluation
B. HAVING filters rows before WHERE
C. HAVING is used exclusively for string pattern matching
D. There is no operational difference
✔ Correct Answer: Option A
Detailed Explanation: WHERE filters base records prior to GROUP BY aggregation. Aggregate functions like SUM() or COUNT() cannot appear in WHERE; HAVING filters the groups generated by GROUP BY.
🎯 Key Learning Point: SQL logical execution order: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT.
Question 34 of 75 Chapter 3
What is a Common Table Expression (CTE) defined with the 'WITH' keyword in SQL?
A. A temporary named result set whose scope is confined to the execution of a single primary query statement
B. A permanent table stored on physical disk
C. A database trigger invoked on table insert
D. A stored procedure compiled in C++
✔ Correct Answer: Option A
Detailed Explanation: A CTE provides a readable, modular temporary result set defined using ‘WITH cte_name AS (…)’. Recursive CTEs can also traverse hierarchical tree and graph structures.
🎯 Key Learning Point: CTEs significantly improve readability over deeply nested subqueries and enable recursion.
Question 35 of 75 Chapter 3
What does the SQL window function LAG(column_name, 1) OVER (ORDER BY date) return?
A. The highest value in the column
B. The value of column_name from the row preceding the current row in the partitioned sort order
C. The value of column_name from the row immediately following the current row
D. Average of all previous rows
✔ Correct Answer: Option B
Detailed Explanation: LAG() accesses data from a previous row at a specified physical offset without requiring a self-join. LEAD() similarly accesses data from subsequent rows.
🎯 Key Learning Point: LAG and LEAD are essential for calculating day-over-day growth and time-series deltas.
Question 36 of 75 Chapter 3
What is the difference between UNION and UNION ALL operators in SQL?
A. UNION combines results and performs a deduplication sort pass to remove duplicate rows; UNION ALL concatenates results directly without eliminating duplicates
B. UNION ALL removes duplicates; UNION retains them
C. UNION ALL works only on numeric columns
D. UNION creates a new database table
✔ Correct Answer: Option A
Detailed Explanation: UNION executes an implicit DISTINCT sort/hash step to remove duplicates, incurring CPU overhead. UNION ALL simply appends results together, running substantially faster.
🎯 Key Learning Point: Always prefer UNION ALL over UNION when duplicates are impossible or acceptable.
Question 37 of 75 Chapter 3
In SQL, what is a Correlated Subquery?
A. A subquery that references columns from the outer enclosing query and is evaluated once for each row processed by the outer query
B. A subquery that executes once independently before the outer query runs
C. A subquery containing two JOIN operations
D. A query running across two distributed database servers
✔ Correct Answer: Option A
Detailed Explanation: A correlated subquery cannot be executed independently because it depends on values passed in from the outer query row-by-row, which can result in O(N*M) performance if unoptimized.
🎯 Key Learning Point: Modern cost-based query optimizers attempt to ‘decorrelate’ subqueries into equivalent JOINs.
Question 38 of 75 Chapter 3
What is the output of the expression 'SELECT COUNT(*), COUNT(col) FROM tbl' if 'col' contains 5 non-null numbers and 3 NULL values?
A. 8, 8
B. 8, 5
C. 5, 5
D. 8, 3
✔ Correct Answer: Option B
Detailed Explanation: COUNT(*) counts all rows regardless of NULL content (8 total rows). COUNT(column_name) counts only rows where the specified column is NOT NULL (5 rows).
🎯 Key Learning Point: Aggregate functions (except COUNT(*)) automatically ignore NULL values during computation.
Question 39 of 75 Chapter 3
What does a CROSS JOIN between Table X (m rows) and Table Y (n rows) produce?
A. A Cartesian product containing m * n total rows
B. A table containing m + n rows
C. An intersection containing min(m, n) rows
D. An error unless an ON condition is supplied
✔ Correct Answer: Option A
Detailed Explanation: CROSS JOIN produces the Cartesian product, pairing every row of Table X with every row of Table Y, generating exactly m * n rows.
🎯 Key Learning Point: Omitting an ON clause from an INNER JOIN in ANSI SQL produces a CROSS JOIN.
Question 40 of 75 Chapter 3
How does NULL behave in boolean ternary logic comparison (e.g. 'WHERE salary = NULL')?
A. Evaluates to TRUE
B. Evaluates to FALSE
C. Evaluates to UNKNOWN, which causes the row to be excluded by the WHERE filter
D. Throws a NullPointerException
✔ Correct Answer: Option C
Detailed Explanation: In SQL three-valued logic (3VL), comparing anything with NULL using equality (= or !=) yields UNKNOWN. Only ‘IS NULL’ or ‘IS NOT NULL’ evaluates to TRUE/FALSE.
🎯 Key Learning Point: Never test for nullity with ‘= NULL’; always use ‘IS NULL’.
Question 41 of 75 Chapter 3
What does the SQL COALESCE(val1, val2, val3, …) function do?
A. Concatenates all strings together
B. Returns the first non-NULL expression from its argument list, or NULL if all arguments are NULL
C. Calculates standard deviation
D. Returns the largest numeric value
✔ Correct Answer: Option B
Detailed Explanation: COALESCE evaluates arguments from left to right and returns the first non-null value found, functioning as an ANSI-standard null fallback mechanism.
🎯 Key Learning Point: COALESCE is the multi-argument standard equivalent of IFNULL() or NVL().
Question 42 of 75 Chapter 3
What is the purpose of the 'PARTITION BY' clause inside a SQL OVER() window definition?
A. It physically partitions the table into separate files on disk
B. It divides query result rows into separate subsets/partitions across which the window function is independently calculated
C. It restricts database permissions to specific users
D. It replaces the WHERE filter
✔ Correct Answer: Option B
Detailed Explanation: PARTITION BY divides rows into groups (e.g. PARTITION BY department_id) so the window function calculates ranks or aggregates separately for each department without collapsing rows.
🎯 Key Learning Point: Unlike GROUP BY, PARTITION BY does not collapse rows into single group summary records.
Question 43 of 75 Chapter 3
What happens when an INNER JOIN condition matches multiple rows in both tables (a 1-to-many or many-to-many match)?
A. The join fails with a duplicate key exception
B. Output rows are multiplied, producing all combination pairs matching the join predicate
C. Only the first matching row is returned
D. Duplicate values are converted to NULL
✔ Correct Answer: Option B
Detailed Explanation: INNER JOIN outputs every pair of rows that satisfies the join predicate. If row A matches 3 rows in B, 3 result rows are emitted.
🎯 Key Learning Point: Unintended join row multiplication is a primary cause of inflated SUM/COUNT figures in reporting queries.
Question 44 of 75 Chapter 3
What does the 'EXISTS' operator evaluate to in an SQL query filter?
A. TRUE if the subquery returns at least one row; short-circuits as soon as a single match is found
B. TRUE only if all subquery rows match
C. An integer representing matching row count
D. TRUE only when subquery returns NULL
✔ Correct Answer: Option A
Detailed Explanation: EXISTS tests for the existence of rows in a subquery. The database engine short-circuits evaluation as soon as the first matching row is discovered, making it highly efficient.
🎯 Key Learning Point: EXISTS generally outperforms IN when checking presence against large subquery sets containing NULLs.
Question 45 of 75 Chapter 3
What is an SQL View, and does a standard (non-materialized) View store data on disk?
A. A view is a virtual table defined by a stored SELECT query; standard views do NOT store data on disk, re-executing the underlying query upon access
B. Yes, standard views copy all data to separate SSD blocks
C. A view is an encrypted backup copy of a table
D. A view can only be created on foreign key columns
✔ Correct Answer: Option A
Detailed Explanation: A standard View is metadata storing a query definition. Data is dynamically read from base tables at runtime. In contrast, Materialized Views persist query output to disk.
🎯 Key Learning Point: Materialized views require periodic REFRESH to synchronize with base table updates.
Curriculum Track • Module 4 of 5
Chapter 4: Indexing (B+ Trees, Hash Indexes, Clustered vs Non-Clustered) (Q46 – Q60)
Question 46 of 75 Chapter 4
Why do relational database storage engines (InnoDB, PostgreSQL) predominantly utilize B+ Trees over standard Binary Search Trees for disk-based indexing?
A. Binary Search Trees require too much CPU cache
B. B+ Trees have very high fan-out (hundreds of keys per node), keeping tree height low (3-4 levels) to minimize expensive disk I/O operations
C. B+ Trees do not support range queries
D. B+ Trees cannot store string data
✔ Correct Answer: Option B
Detailed Explanation: Disk seeks are thousands of times slower than RAM. High fan-out B+ trees store millions of keys with height 3-4, meaning any lookup requires at most 3-4 disk block reads. BSTs would require log2(N) levels (~20 disk reads).
🎯 Key Learning Point: All leaf nodes in a B+ tree are linked in a sequential doubly linked list, enabling fast O(range) scans.
Question 47 of 75 Chapter 4
What is the primary architectural difference between a Clustered Index and a Non-Clustered (Secondary) Index?
A. Clustered index stores keys in RAM; secondary index stores keys in flash memory
B. The leaf pages of a Clustered Index contain the actual physical table data rows; secondary indexes store index keys and pointer references to the clustered index key
C. A table can possess multiple clustered indexes but only one secondary index
D. Clustered indexes do not support B-Tree layout
✔ Correct Answer: Option B
Detailed Explanation: A Clustered Index dictates the physical ordering of data rows on disk (leaf level = actual row data). Therefore, a table can have exactly ONE clustered index (usually the Primary Key).
🎯 Key Learning Point: Secondary index lookups perform a secondary traversal to the clustered index leaf to retrieve non-indexed row columns.
Question 48 of 75 Chapter 4
What is a 'Covering Index' in relational database performance tuning?
A. An index that covers all tables in the entire database
B. An index that contains all columns requested by a SELECT query, allowing the engine to satisfy the query entirely from the index without reading the base table pages
C. An index that encrypts sensitive table columns
D. An index that automatically deletes duplicate records
✔ Correct Answer: Option B
Detailed Explanation: When an index contains every column referenced in SELECT, WHERE, and ORDER BY clauses, the database performs an ‘Index Only Scan’, completely avoiding random I/O row lookups.
🎯 Key Learning Point: Covering indexes yield massive speedups by avoiding base table page fetches (eliminating ‘bookmark lookups’).
Question 49 of 75 Chapter 4
How does the 'Leftmost Prefix Rule' apply to a composite index created on (col_a, col_b, col_c)?
A. The index can only be used if col_a is included in the query search condition
B. Queries filtering only on col_c can use the index at full efficiency
C. The index works from right to left
D. The leftmost column must always be a primary key integer
✔ Correct Answer: Option A
Detailed Explanation: A composite B-Tree index sorts data first by col_a, then by col_b, then by col_c. If a query does not specify col_a, the index cannot be traversed hierarchically, resulting in a full table scan.
🎯 Key Learning Point: Composite index (A, B, C) can satisfy queries filtering on (A), (A, B), or (A, B, C), but NOT (B, C) alone.
Question 50 of 75 Chapter 4
What is the primary drawback of adding excessive indexes to high-write (OLTP) tables?
A. Read queries become slower
B. Every INSERT, UPDATE, and DELETE operation must update all corresponding index B+ Trees, substantially degrading write throughput and causing page splits
C. Database connections are restricted
D. Transactions lose ACID compliance
✔ Correct Answer: Option B
Detailed Explanation: Indexes accelerate reads but penalize writes. Every write operation must maintain B+ Tree invariants, balancing nodes and performing costly disk page splits across every index on the table.
🎯 Key Learning Point: Index intentionally: analyze query execution plans before adding redundant secondary indexes.
Question 51 of 75 Chapter 4
Why are standard Hash Indexes unsuitable for SQL queries containing 'WHERE age BETWEEN 20 AND 30'?
A. Hash functions consume too much memory
B. Hash indexes distribute keys uniformly across buckets using a hash function, destroying ordering and preventing range scans
C. Hash tables only store string characters
D. Hash collision handling is impossible on disk
✔ Correct Answer: Option B
Detailed Explanation: Hash indexes offer O(1) point lookups for exact equality (=, IN) but cannot satisfy range operators (<, >, BETWEEN) because hashed keys do not preserve lexical or numeric order.
🎯 Key Learning Point: B+ Trees are the default indexing structure because they efficiently support both equality and range queries.
Question 52 of 75 Chapter 4
What does a 'Full Table Scan' (Seq Scan in PostgreSQL) signify in a database EXPLAIN query plan?
A. The query used the fastest index available
B. The storage engine read every page of the table sequentially from disk/cache to evaluate query predicates because no suitable index was matched or cost was deemed lower
C. The database is corrupted
D. Table is locked exclusively
✔ Correct Answer: Option B
Detailed Explanation: A full table scan inspects every row in the table. For small tables, sequential scans are often faster than random index lookups; for large tables, missing indexes cause severe latency spikes.
🎯 Key Learning Point: Use EXPLAIN ANALYZE to identify sequential scans on tables with millions of rows.
Question 53 of 75 Chapter 4
What causes an 'Index Page Split' in a B+ Tree data structure?
A. Running a SELECT query with multiple JOINs
B. Inserting a new key into an index leaf page that is already 100% full, forcing the engine to allocate a new page and redistribute half the keys
C. Dropping an unused index
D. Executing an index rebuild command
✔ Correct Answer: Option B
Detailed Explanation: When an inserted key lands on a leaf page with no remaining free space, the engine splits the page into two 50% full pages, updating parent pointers. Random UUID keys cause severe page split thrashing.
🎯 Key Learning Point: Sequential auto-incrementing integer keys minimize page splits because inserts always append to the rightmost leaf.
Question 54 of 75 Chapter 4
How does an LSM Tree (Log-Structured Merge-tree, used in RocksDB/Cassandra) differ fundamentally from a B+ Tree?
A. LSM Trees write sequentially to an append-only in-memory MemTable and commit log, flushing sorted SSTables to disk to maximize write throughput
B. LSM Trees cannot store sorted data
C. LSM Trees do not support disk persistence
D. LSM Trees are strictly single-threaded
✔ Correct Answer: Option A
Detailed Explanation: B+ Trees perform random in-place page writes. LSM Trees convert all mutations into sequential append writes in RAM and log, flushing immutable SSTables and merging them via background compaction.
🎯 Key Learning Point: LSM Trees optimize for write-heavy workloads; B+ Trees optimize for read-heavy workloads.
Question 55 of 75 Chapter 4
What is Index Cardinality in database statistics?
A. The total number of disk pages occupied by the index
B. The uniqueness of values in a column; high cardinality means high percentage of unique values (e.g. email, user_id)
C. The number of CPUs allocated to index queries
D. The depth of the B+ tree root node
✔ Correct Answer: Option B
Detailed Explanation: High cardinality columns (e.g. SSN, email) make excellent index candidates because an index can quickly filter down to a tiny fraction of records. Low cardinality columns (e.g. gender: M/F) are rarely indexed alone.
🎯 Key Learning Point: The cost-based optimizer uses cardinality statistics to decide whether an index lookup is cheaper than a full scan.
Question 56 of 75 Chapter 4
What happens when an expression or function wraps an indexed column in the WHERE clause (e.g. 'WHERE UPPER(last_name) = "SMITH"')?
A. The standard index on last_name is used at full speed
B. The standard index is rendered unusable, triggering a full table scan unless a Function-Based Index is defined
C. Query optimizer throws a syntax error
D. Database automatically creates a temporary index in RAM
✔ Correct Answer: Option B
Detailed Explanation: The B-Tree index stores raw last_name values, not UPPER(last_name). Applying functions prevents binary search traversal unless a functional/expression index on UPPER(last_name) exists.
🎯 Key Learning Point: Avoid wrapping indexed columns in functions in WHERE clauses; compare against literal transformed values instead.
Question 57 of 75 Chapter 4
What is the purpose of the Database Buffer Pool (Buffer Cache)?
A. To store incoming client network connections
B. To cache recently accessed data and index pages in main RAM, avoiding disk I/O for frequently read tables
C. To hold temporary SQL query syntax strings
D. To store user passwords
✔ Correct Answer: Option B
Detailed Explanation: The buffer pool caches database disk pages in RAM using LRU eviction policies. Queries finding target pages in the buffer pool achieve sub-millisecond memory-speed execution (cache hits).
🎯 Key Learning Point: Buffer pool hit ratio (>99%) is a primary operational health metric for production database instances.
Question 58 of 75 Chapter 4
What is a 'Bitmap Index' and in which environment is it most effectively utilized?
A. An index storing image bitmaps for computer vision
B. An index that uses bit arrays (0s and 1s) for low-cardinality columns, highly effective in OLAP data warehouses for fast bitwise AND/OR query filtering
C. An index restricted to primary keys
D. An index that locks entire tables on write
✔ Correct Answer: Option B
Detailed Explanation: Bitmap indexes represent column values as strings of bits. Bitwise operations (AND, OR, NOT) evaluate complex multi-attribute queries in microseconds. However, row locking makes them unviable for OLTP writes.
🎯 Key Learning Point: Bitmap indexes excel in read-heavy analytics with low distinct value counts.
Question 59 of 75 Chapter 4
What is the difference between Clustered Index implementation in MySQL InnoDB vs PostgreSQL?
A. InnoDB always organizes table storage as a clustered index B+ Tree (IOT); PostgreSQL uses Heap Tables where rows are unordered and all indexes are secondary pointer structures
B. PostgreSQL only uses clustered indexes; MySQL does not support them
C. Both engines store data as flat text files
D. There is no difference
✔ Correct Answer: Option A
Detailed Explanation: InnoDB tables ARE clustered indexes (Index-Organized Tables). In PostgreSQL, all tables are unordered Heap structures, and the CLUSTER command is a one-time sort that does not maintain continuous clustering.
🎯 Key Learning Point: In PostgreSQL, index entries store (BlockNumber, Offset) ctid tuple pointers to heap pages.
Question 60 of 75 Chapter 4
What is the role of the Cost-Based Optimizer (CBO) in a relational database engine?
A. Calculates the financial cost of cloud hosting bills
B. Evaluates multiple candidate execution plans (JOIN algorithms, index paths) and selects the plan with the lowest estimated disk I/O and CPU cost
C. Compresses database backup archives
D. Authenticates database client connections
✔ Correct Answer: Option B
Detailed Explanation: The CBO uses table statistics, histogram distributions, and hardware parameters to estimate costs for candidate query execution plans, selecting the mathematically cheapest execution strategy.
🎯 Key Learning Point: Outdated table statistics (ANALYZE / VACUUM) cause the optimizer to choose poor execution plans.
Curriculum Track • Module 5 of 5
Chapter 5: Transactions, ACID Properties, Concurrency Control & WAL Recovery (Q61 – Q75)
Question 61 of 75 Chapter 5
What does the 'A' (Atomicity) in ACID transaction properties guarantee?
A. Transactions execute at atomic clock speeds
B. All operations within a transaction succeed completely, or the entire transaction is rolled back leaving the database state unmodified ('All or Nothing')
C. Individual column values cannot be split into sub-strings
D. Transactions never deadlock
✔ Correct Answer: Option B
Detailed Explanation: Atomicity ensures that a transaction containing multiple SQL statements is treated as an indivisible atomic unit. If a crash or error occurs mid-way, all changes are completely aborted and undone.
🎯 Key Learning Point: Undo logs are utilized by database storage engines to guarantee atomicity during aborts.
Question 62 of 75 Chapter 5
What phenomenon is referred to as a 'Dirty Read' in database concurrency?
A. A query reading corrupted disk sectors
B. Transaction T1 reads data modified by uncommitted concurrent transaction T2, which subsequently rolls back
C. A transaction reading the same row twice and seeing different values due to committed updates
D. Reading unindexed table rows
✔ Correct Answer: Option B
Detailed Explanation: A Dirty Read occurs when a transaction views uncommitted changes of another transaction. If the modifying transaction aborts, the first transaction has acted upon phantom, non-existent data.
🎯 Key Learning Point: Read Committed isolation level strictly prevents Dirty Reads.
Question 63 of 75 Chapter 5
In ANSI SQL isolation levels, what concurrency anomaly is permitted under 'Read Committed' but prevented under 'Repeatable Read'?
A. Dirty Read
B. Non-Repeatable Read (Fuzzy Read)
C. Write Skew
D. Deadlock
✔ Correct Answer: Option B
Detailed Explanation: Non-Repeatable Read occurs when T1 reads a row, T2 updates/deletes that row and commits, and T1 re-reads the row obtaining different values. Repeatable Read ensures T1 re-reads identical row values.
🎯 Key Learning Point: Repeatable Read isolation prevents both Dirty Reads and Non-Repeatable Reads.
Question 64 of 75 Chapter 5
What is a 'Phantom Read' anomaly?
A. A query reading rows from deleted tables
B. Transaction T1 queries a range of rows; concurrent transaction T2 inserts new matching rows and commits; T1 re-runs the range query and discovers newly appeared 'phantom' records
C. A database connection disconnecting unexpectedly
D. A query timing out due to lock contention
✔ Correct Answer: Option B
Detailed Explanation: Phantom Reads involve set-based range queries where concurrent committed INSERTs cause new rows to appear between consecutive range scans within the same transaction.
🎯 Key Learning Point: Serializable isolation (or Next-Key Locking in InnoDB) prevents Phantom Reads.
Question 65 of 75 Chapter 5
How does Multi-Version Concurrency Control (MVCC) eliminate read-write contention?
A. By locking the entire database table on read
B. By maintaining multiple timestamped versions of each record, allowing readers to read historical snapshots without acquiring shared locks while writers modify rows concurrently
C. By forcing all queries to run sequentially in a single thread
D. By moving write operations to background threads
✔ Correct Answer: Option B
Detailed Explanation: MVCC provides ‘readers do not block writers, and writers do not block readers’. When a transaction reads, it sees a consistent snapshot of data as of transaction start time without locking records.
🎯 Key Learning Point: PostgreSQL, MySQL InnoDB, and Oracle rely on MVCC for scalable concurrent query processing.
Question 66 of 75 Chapter 5
What is the Strict Two-Phase Locking (Strict 2PL) protocol?
A. Locks can be acquired and released in any arbitrary order across 2 seconds
B. A concurrency protocol where all exclusive (write) locks acquired during the Growing Phase are held until the transaction fully commits or aborts
C. A protocol requiring two distinct database passwords
D. A protocol that eliminates all deadlocks
✔ Correct Answer: Option B
Detailed Explanation: Strict 2PL prevents cascading rollbacks: all exclusive (X) locks acquired during execution are retained until the transaction terminates (COMMIT or ROLLBACK), ensuring strict recoverability.
🎯 Key Learning Point: Standard 2PL guarantees conflict serializability; Strict 2PL also guarantees freedom from cascading aborts.
Question 67 of 75 Chapter 5
How does a database engine detect deadlocks among concurrent transactions?
A. By pinging the network card
B. By constructing a Wait-For Graph (WFG) where directed edges represent transactions waiting for locks held by others, and periodically running cycle detection algorithms
C. By monitoring CPU heat temperatures
D. By checking table row counts
✔ Correct Answer: Option B
Detailed Explanation: In a Wait-For Graph, nodes are transactions and directed edges T1 -> T2 indicate T1 is waiting for a resource held by T2. A directed cycle in the graph indicates a deadlock condition.
🎯 Key Learning Point: Upon deadlock detection, the engine selects a ‘victim’ transaction and aborts/rolls it back to break the cycle.
Question 68 of 75 Chapter 5
What is Write-Ahead Logging (WAL) and why is it essential for database Durability ('D' in ACID)?
A. Logging all SELECT queries for billing audits
B. Ensuring that log records describing data mutations are flushed to non-volatile disk before the corresponding dirty data pages are written to the database file
C. Writing database code in Python before compiling
D. Replicating databases to external tape drives
✔ Correct Answer: Option B
Detailed Explanation: WAL guarantees durability and crash recovery. Sequential WAL flushes are fast. If a server abruptly loses power, unwritten dirty buffer pool changes can be reliably reconstructed via WAL REDO logs.
🎯 Key Learning Point: WAL follows the rule: Log must reach disk before dirty database data pages overwrite disk blocks.
Question 69 of 75 Chapter 5
What does the ARIES recovery algorithm perform during the 'Analysis Pass' following an unexpected database crash?
A. Deletes all user accounts
B. Scans the log forward from the most recent checkpoint to identify all dirty pages in the buffer pool and determine the set of active (loser) transactions at crash time
C. Recompiles stored procedures
D. Drops all foreign key constraints
✔ Correct Answer: Option B
Detailed Explanation: ARIES recovery executes 3 passes: 1) Analysis (determines state of buffer pool and active transactions at crash), 2) REDO (repeats history to restore exact state), 3) UNDO (rolls back active uncommitted transactions).
🎯 Key Learning Point: ARIES is the foundational crash recovery algorithm implemented across modern relational engines.
Question 70 of 75 Chapter 5
What is the difference between a Shared Lock (S-Lock) and an Exclusive Lock (X-Lock)?
A. Shared locks are used for reads and allow concurrent shared locks; Exclusive locks are required for writes and block all other shared and exclusive locks
B. Exclusive locks permit concurrent reads
C. Shared locks are permanent on disk
D. Exclusive locks can only be held by system administrators
✔ Correct Answer: Option A
Detailed Explanation: Multiple transactions can concurrently acquire Shared (S) locks on the same row. An Exclusive (X) lock guarantees sole ownership: no other transaction can read or write the locked resource.
🎯 Key Learning Point: Lock compatibility matrix: S and S are compatible; S and X conflict; X and X conflict.
Question 71 of 75 Chapter 5
What is 'Write Skew' anomaly under Snapshot Isolation?
A. Writing invalid data types to numeric columns
B. Two concurrent transactions read overlapping data sets satisfying a constraint, make disjoint writes that together violate the global integrity constraint, and both commit successfully
C. Hard drive head misalignment during writes
D. A query writing to two tables simultaneously
✔ Correct Answer: Option B
Detailed Explanation: Example: On-call doctors requirement (at least 1 doctor must be on duty). Dr. A and Dr. B both query (count = 2). Dr. A goes off duty; Dr. B goes off duty. Under Snapshot Isolation, both disjoint writes commit, leaving 0 on duty.
🎯 Key Learning Point: Snapshot Isolation prevents dirty, non-repeatable, and phantom reads, but DOES NOT prevent Write Skew. True Serializable isolation is required.
Question 72 of 75 Chapter 5
What is a 'Cascading Abort' (Cascading Rollback) in transaction processing?
A. The entire operating system rebooting
B. The failure of a single transaction forcing a series of other dependent transactions to roll back because they read uncommitted data written by the failing transaction
C. Multiple tables dropping sequentially
D. Network switch packet loss
✔ Correct Answer: Option B
Detailed Explanation: If T1 updates a row without committing, T2 reads it, and T3 reads T2’s update, an abort of T1 forces T2 and T3 to abort as well, wasting substantial system resources.
🎯 Key Learning Point: Cascading rollbacks are prevented by ensuring transactions only read COMMITTED data (Cascardless Schedules).
Question 73 of 75 Chapter 5
What is the role of 'Checkpoints' in database log management?
A. To authenticate user logins every 5 minutes
B. Periodically flushing dirty pages to disk and writing a checkpoint record to the log, bounding how far back the recovery engine must scan during crash restart
C. Encrypting database tablespace
D. Halting all database queries permanently
✔ Correct Answer: Option B
Detailed Explanation: Without checkpoints, crash recovery would require processing the log from the very beginning of the database lifetime. Checkpoints guarantee all dirty pages before the checkpoint are safely on disk, shortening recovery time.
🎯 Key Learning Point: Fuzzy checkpointing allows normal query processing to continue while dirty pages are flushed in background.
Question 74 of 75 Chapter 5
In distributed database transactions, what is the role of the Coordinator in the Two-Phase Commit (2PC) protocol?
A. To encrypt network communications
B. Phase 1 (Prepare): Ask all cohort participants if they can commit; Phase 2 (Commit/Abort): If all reply YES, send COMMIT broadcast; if any replies NO, send ABORT broadcast
C. To randomly select an active server for backup
D. To assign DNS IP addresses
✔ Correct Answer: Option B
Detailed Explanation: 2PC coordinates atomic commit across distributed nodes. The coordinator polls all nodes (‘Prepare’). Only if 100% of participants vote YES does the coordinator log a global commit and signal cohorts to commit.
🎯 Key Learning Point: 2PC is a blocking protocol: if the coordinator crashes during phase 2, participants may be left in an uncertain blocked state.
Question 75 of 75 Chapter 5
What does the CAP theorem state regarding distributed data stores under network partition?
A. A distributed system can achieve Consistency, Availability, and Partition Tolerance simultaneously at all times
B. When a network partition (P) inevitably occurs, a distributed system must trade off strictly between Consistency (C) and Availability (A)
C. Consistency is impossible in distributed computing
D. Partition tolerance can be eliminated by using fiber optic cables
✔ Correct Answer: Option B
Detailed Explanation: Formulated by Eric Brewer, CAP states that during a network partition, a system must choose either Consistency (refuse requests to avoid serving stale data, sacrificing Availability) or Availability (accept writes/reads on isolated partitions, sacrificing Consistency).
🎯 Key Learning Point: Relational cluster systems typically choose CP (Consistency + Partition tolerance); distributed NoSQL systems often choose AP (Availability + Eventual Consistency).

Explore Other Comprehensive 75-Question Subject Tracks

Continue your preparation across our active curriculum tracks. Every track provides 75 verified questions across 5 complete chapters.

🟢 100% Live Track
C Programming
75 Qs • 5 Chapters • Practice Now →
🟢 100% Live Track
Python Programming
75 Qs • 5 Chapters • Practice Now →
🟢 100% Live Track
Data Structures
75 Qs • 5 Chapters • Practice Now →
Active Track
📍 DBMS & SQL
75 Qs • 5 Chapters (You are here)
🟢 100% Live Track
Computer Networks
75 Qs • 5 Chapters • Practice Now →
🟢 100% Live Track
Operating Systems
75 Qs • 5 Chapters • Practice Now →
🟢 100% Live Track
Quantitative Aptitude
75 Qs • 5 Chapters • Practice Now →
📚 View All 92+ Subjects in Master Directory
Share
  • Facebook
Queryiest

Queryiest

Enlightened

Queryiest – Technology Writer | Software Developer | Digital Learning Enthusiast

Queryiest is a technology writer, software developer, and knowledge-sharing enthusiast passionate about simplifying complex technical concepts for students, professionals, and lifelong learners. With expertise in software development, programming, cybersecurity, artificial intelligence, digital tools, and emerging technologies, Queryiest creates practical, research-driven content that helps readers solve real-world problems. As a regular contributor to RTSALL, Queryiest publishes easy-to-understand guides, coding resources, technology news, career advice, and educational tutorials designed for beginners and professionals alike. Every article focuses on accuracy, clarity, and actionable insights to help readers stay informed in the rapidly evolving digital world. Whether it's programming, software engineering, AI, cybersecurity, online platforms, or digital productivity, Queryiest believes that quality knowledge should be accessible to everyone. The goal is to build a trusted learning resource where readers can discover reliable answers, improve their technical skills, and make informed decisions. Areas of Expertise: Software Development, Programming, Cybersecurity, Artificial Intelligence, Technology News, Coding Interview Preparation, Digital Learning, Productivity Tools, and Online Knowledge Sharing.

    Sidebar

    Ask A Question
    • Popular
    • Answers
    • Queryiest

      What is a database?

      • 3 Answers
    • Anonymous

      How to rotate an array in-place with O(1) space and ...

      • 3 Answers
    • hannah

      What steps can businesses take to identify the most valuable ...

      • 2 Answers
    • Vikram
      aarav0 added an answer Direct Technical Solution: Unlike IVFFlat (which partitions vector spaces with… September 11, 2026 at 9:57 pm
    • Abhishek
      Abhishek added an answer Direct Technical Solution: In C++20, range view adaptors (like std::views::filter,… September 11, 2026 at 9:57 pm
    • Sneha Patel
      Anonymous added an answer Direct Technical Solution: torch.cuda.empty_cache() releases only cached (unallocated) blocks back… September 11, 2026 at 9:57 pm

    Top Members

    Queryiest

    Queryiest

    • 201 Questions
    • 295 Points
    Enlightened
    Anonymous

    Anonymous

    • 11 Questions
    • 42 Points
    Begginer
    paperubofficial

    paperubofficial

    • 0 Questions
    • 22 Points
    Begginer

    Trending Tags

    ai asp.net aws basics aws certification aws console aws free tier aws login aws scenario-based questions c++ career cyber security cyber security interview git java javascript jobs jquery net core net core interview questions sql

    Explore

    • Home
    • Add group
    • Groups page
    • Communities
    • Questions
      • DSA Problems
    • Polls
    • Tags
    • Badges
    • Users
    • Help
    • New Questions
    • Trending Questions
    • Must read Questions
    • Hot Questions

    Footer

    About Us

    • Meet The Team
    • Blog
    • About Us
    • Contact Us

    Legal Stuff

    • Privacy Policy
    • Disclaimer
    • Terms & Conditions

    Help

    • Knowledge Base
    • Support

    Follow

    © 2023-25 RTSALL. All Rights Reserved