Databases
Chapter: 4.4 Databases Level: 1 — Recognition (MCQ, Matching, True/False with justification) Time Limit: 20 minutes Total Marks: 30
Section A — Multiple Choice (1 mark each, 10 marks)
Choose the single best answer.
Q1. In the relational model, what does a NULL value represent?
- A) The number zero
- B) An empty string
- C) An unknown or missing value
- D) A boolean false
Q2. Which key is a candidate key that has been chosen to uniquely identify rows in a table?
- A) Foreign key
- B) Primary key
- C) Super key
- D) Natural key
Q3. Which SQL statement removes all rows from a table but keeps its structure and cannot normally be rolled back per row?
- A)
DELETE - B)
DROP - C)
TRUNCATE - D)
ALTER
Q4. Which clause filters rows after aggregation (i.e., filters groups)?
- A)
WHERE - B)
HAVING - C)
ORDER BY - D)
LIMIT
Q5. A LEFT JOIN returns:
- A) Only matching rows from both tables
- B) All rows from the right table plus matches from the left
- C) All rows from the left table plus matched rows from the right
- D) The Cartesian product of both tables
Q6. Which window function assigns the same rank to ties and leaves no gaps in the ranking sequence?
- A)
ROW_NUMBER - B)
RANK - C)
DENSE_RANK - D)
LAG
Q7. In the CAP theorem, the "P" stands for:
- A) Persistence
- B) Partition tolerance
- C) Performance
- D) Primary key
Q8. Which normal form eliminates partial dependencies of non-key attributes on part of a composite primary key?
- A) 1NF
- B) 2NF
- C) 3NF
- D) BCNF
Q9. The "D" in ACID stands for:
- A) Distribution
- B) Determinism
- C) Durability
- D) Denormalization
Q10. Which NoSQL type is best suited for storing and querying highly connected data such as social networks?
- A) Key-value (Redis)
- B) Document (MongoDB)
- C) Column (Cassandra)
- D) Graph (Neo4j)
Section B — Matching (1 mark each, 8 marks)
Q11. Match each isolation level / anomaly / concept in Column X to its description in Column Y. Write pairs (e.g., a–3).
| Column X | Column Y | |
|---|---|---|
| a) Dirty read | 1) Highest isolation level; prevents all anomalies | |
| b) READ UNCOMMITTED | 2) Reading data written by an uncommitted transaction | |
| c) SERIALIZABLE | 3) New rows appear on re-query within a transaction | |
| d) Phantom read | 4) Lowest isolation; allows dirty reads |
Q12. Match each SQL/DB feature to its category. Write pairs (e.g., a–3).
| Column X | Column Y | |
|---|---|---|
a) COUNT, SUM, AVG |
1) Multi-version concurrency control | |
| b) B-tree | 2) Aggregate functions | |
| c) MVCC | 3) Named temporary result set in WITH |
|
| d) CTE | 4) Common index structure for range queries |
Section C — True/False with Justification (2 marks each, 12 marks)
State True or False (1 mark) and give a one-line justification (1 mark).
Q13. A foreign key can contain a NULL value.
Q14. GROUP BY executes before the WHERE clause in logical query processing order.
Q15. A covering index is one that contains all the columns a query needs, so the table itself need not be read.
Q16. In a correlated subquery, the inner query is evaluated only once for the entire outer query.
Q17. Pessimistic concurrency control assumes conflicts are rare and checks only at commit time.
Q18. BASE systems favour availability and eventual consistency over the strict consistency of ACID.
Answer keyMark scheme & solutions
Section A (1 mark each)
Q1 — C. NULL denotes unknown/missing data; it is distinct from 0 or ''. (1)
Q2 — B. A primary key is the chosen candidate key; super/candidate keys are broader sets, foreign key references another table. (1)
Q3 — C. TRUNCATE is DDL: removes all rows, keeps structure, minimal logging, resets identity. DELETE is per-row DML. (1)
Q4 — B. HAVING filters aggregated groups; WHERE filters rows before grouping. (1)
Q5 — C. LEFT JOIN keeps every left-table row; unmatched right columns become NULL. (1)
Q6 — C. DENSE_RANK gives ties equal rank with no gaps; RANK leaves gaps. (1)
Q7 — B. CAP = Consistency, Availability, Partition tolerance. (1)
Q8 — B. 2NF removes partial dependencies on part of a composite key. (1)
Q9 — C. ACID = Atomicity, Consistency, Isolation, Durability. (1)
Q10 — D. Graph databases (Neo4j) model nodes/edges — ideal for connected data. (1)
Section B (1 mark per correct pair)
Q11: a–2, b–4, c–1, d–3 (4 marks, 1 each)
- a) Dirty read = reading uncommitted data.
- b) READ UNCOMMITTED = lowest level, allows dirty reads.
- c) SERIALIZABLE = highest, prevents all anomalies.
- d) Phantom read = new rows appear on re-query.
Q12: a–2, b–4, c–1, d–3 (4 marks, 1 each)
- a) Aggregate functions.
- b) B-tree = index for range queries.
- c) MVCC = multi-version concurrency control.
- d) CTE = named temp result in
WITH.
Section C (1 mark T/F + 1 mark justification)
Q13 — True. A foreign key may be NULL (representing "no relationship yet"); the referential constraint is only enforced for non-null values. (2)
Q14 — False. WHERE is applied before GROUP BY in logical order (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY). (2)
Q15 — True. A covering index includes all columns referenced by the query, so it can be answered from the index alone (index-only scan). (2)
Q16 — False. A correlated subquery references the outer row and is (logically) re-evaluated for each outer row; only an uncorrelated subquery runs once. (2)
Q17 — False. That describes optimistic concurrency. Pessimistic control assumes conflicts are likely and locks resources up front. (2)
Q18 — True. BASE = Basically Available, Soft state, Eventual consistency — trades strong consistency for availability. (2)
[
{"claim":"Section A total marks equals 10 (10 questions x 1)","code":"result = (10*1 == 10)"},
{"claim":"Section B total marks equals 8 (8 pairs x 1)","code":"result = (8*1 == 8)"},
{"claim":"Section C total marks equals 12 (6 questions x 2)","code":"result = (6*2 == 12)"},
{"claim":"Overall paper total is 30 marks","code":"result = (10 + 8 + 6*2 == 30)"}
]