Previous Year Question Breakdown

Every question from 4 comprehensive exams, tagged by topic, type, and difficulty.

F Factual C Conceptual S Solving

2023-24 Comprehensive (40 Marks, 60 min, Closed Book)

Part I — MCQ (1 × 20 = 20 marks, 50% negative)

#	Topic	Type	Question Summary
1	Indexing	C	Which index is NEVER needed as clustering index?
2	Indexing	C	Which index suits low-cardinality attributes? (Bitmap)
3	Normalization	F	Highest NF based on MVD? (4NF)
4	Crash Recovery	F	Operation to undo committed transaction effects? (Compensatory)
5	Transactions	F	Transaction terminates when? (Committed or Failed)
6	Transactions	F	Successfully completed transaction state? (Committed)
7	FDs & Closure	S	Closure of {A} given F = {A→BC, B→D, AB→D}
8	SQL	F	SQL features — which function does SQL support?
9	Concurrency	C	Problem from introducing locks? (Performance degradation)
10	Normalization	C	Relation in 3NF — what can be inferred?
11	B+ Tree	C	Which property means POOR B+ tree performance? (Long branches)
12	Indexing	C	True statements about indexes?
13	Crash Recovery	C	Best algorithm for long-lived transactions with few rollbacks?
14	Concurrency	S	Identify consistency problem from schedule (Lost Update)
15	Relational Model	F	Referential integrity definition
16	ER Mapping	C	1:1 optional mapping — where to put FK?
17	Relational Model	C	What does referential integrity enforcement ensure?
18	Normalization	S	Identify normal form of R(p,q,r,s,t) with given FDs
19	DBMS General	F	Data dictionary benefit that is NOT valid? (Performance measurement)
20	ER Mapping	C	1:1 optional at both ends — mapping strategy?

Part II — Short Answers (2 × 10 = 20 marks)

#	Topic	Type	Question Summary
1	Relational Model	C	Which operations on A/B violate referential integrity constraint from A to B?
2	MVD / 4NF	C	Fill-in-the-blank: MVD A→→BC semantics
3	Crash Recovery	C	Why is shadow paging unsuitable for DBMS? Where is it suitable?
4	Crash Recovery	F	Purpose and conditions of checkpoint operation
5	Relational Model	S	Max simultaneous candidate keys for R(A,B,C)? Extend to 4 attributes
6	MVD / 4NF	S	Provide tuple data where FDs hold but MVD does not
7	Decomposition	S	Example of dependency-preserving but not lossless decomposition
8	Relational Algebra	S	Express division operator using basic RA operators
9	Transactions	F	Define commit dependency and its significance
10	File Organization	F	Two ways to handle variable-length records, show structure

Type Distribution: Factual: 9, Conceptual: 11, Solving: 10

2022-23 Comprehensive (105 Marks)

Part A — Closed Book (20 marks, 30 min)

#	Topic	Type	Marks	Question Summary
1	Concurrency	C	6	Compare timestamp vs 2PL across 5 properties (fill table)
2a	FDs & Closure	S	2	Compute closure of each attribute (EID, PID, Role, Skill)
2b	Normalization	S	2	Find all candidate keys
2c	4NF	S	4	Decompose into 4NF with PKs and FKs
3	Relational Algebra	S	2	Convert SQL JOIN query to RA
4	DBMS General	C	4	Why do modern apps prefer NoSQL? (Select all true)

Part B — Open Book (85 marks)

#	Topic	Type	Marks	Question Summary
1a	Serializability	S	5	Precedence graph, check conflict serializability of 4-transaction schedule
1b	Serializability	S	5	Check view serializability of 3-transaction schedule
2a	Recoverability	S	5	Determine if schedule is recoverable
2b	Recoverability	S	5	Determine recoverability AND cascadelessness
2c	Recoverability	S	5	Check two schedules for recoverability + cascadelessness
3a	2PL	S	5	Apply simple 2PL, show lock/unlock, identify deadlocks
3b	2PL	S	5	Apply strict and conservative 2PL
4	Timestamps	S	5	Apply basic timestamp protocol with R-TS, W-TS
5a	Crash Recovery	S	5	Log-based recovery with checkpoint — undo/redo
5b	Crash Recovery	S	5	WAL recovery — recover from log
6a	B+ Tree + Storage	S	5	Calculate blocks, binary search accesses, B+ tree height
6b	B+ Tree	S	5	Construct B+ tree with sequential inserts, show all states
7a	FDs & Closure	S	5	Compute closure of each attribute
7b	Normalization	S	5	Find all candidate keys
7c	Normalization	C	5	Identify highest normal form with justification
8a	4NF	S	5	Decompose into 4NF
8b	4NF	S	5	Identify candidate keys and foreign keys for decomposed tables

Type Distribution: Factual: 0, Conceptual: 3, Solving: 19

2021-22 Comprehensive (120 Marks)

Part A — Closed Book MCQ (15 marks, 30 min)

#	Topic	Type	Question Summary
1	B+ Tree	S	Calculate optimal B+ tree degree (key=8B, block=512B, ptr=4B)
2	Normalization	C	How to distinguish BCNF vs 3NF decomposition?
3	Decomposition	S	A→B, C→D: is R1(AB), R2(CD) lossless/dep-preserving?
4	B+ Tree	F	Which B+ tree statement is NOT correct? (Non-leaf pointers to data)
5	Normalization	S	BCNF decomposition — check both sub-schemas, lossless + dep-preserving
6	File Organization	C	Best file org for range search? (Sorted)
7	File Organization	C	Best for inserts + unordered scans? (Heap)
8	File Organization	C	Best for exact match search? (Hash)
9	2PL	F	Which 2PL statement is FALSE?
10	Crash Recovery	C	Advantage of immediate update?

Part B — Open Book (105 marks)

#	Topic	Type	Marks	Question Summary
1a	Serializability	S	12	3 schedules — conflict + view serializability, serial order
1b	Recoverability	S	3	Check recoverable + cascadeless for 3 schedules
2a	2PL	S	5	Apply rigorous 2PL to schedule
2b	2PL	C	5	Does rigorous 2PL ensure conflict/view serial, deadlock freedom, etc.?
3	Crash Recovery	S	10	Step-by-step recovery with immediate update + checkpoint
4	B+ Tree	S	10	Construct B+ tree for A-T (20 letters), n=4, show best full tree
5a	Timestamps	S	5	Implement timestamp ordering on a schedule
5b	Timestamps	C	5	What does timestamp protocol ensure?
6a	B+ Tree	C	5	Problems of building dense clustered B+ tree by sequential inserts
6b	B+ Tree	C	5	Modification to insertion routine to fix the problem
7	Hashing	S	10	Extendible + linear hashing with 17 records
8	Storage + Indexing	S	20	Full chain: blocking factor → blocks → primary index → multi-level
9a	Query Optimization	C	5	5 ways to improve SQL query performance
9b	FDs	S	5	Find canonical/minimal cover

Type Distribution: Factual: 2, Conceptual: 10, Solving: 14

2017-18 Comprehensive (80 Marks)

Part A — Closed Book Short Answers (20 marks, 45 min)

#	Topic	Type	Marks	Question Summary
1	Hashing	S	4	Partitioned hash: bucket number for record, avg buckets searched
2	Decomposition	F	4	State sufficient and necessary conditions for dependency preservation
3	Query Optimization	S	4	Heuristic for 6-relation join, RA expression tree, number of join orders
4	Materialized Views	S	4	Incremental maintenance of full outer join view on inserts/deletes
5	Query Processing	S	4	Graphically depict hash join (R=10 blocks, S=5 blocks, mod 5)

Part B — Open Book (30 marks)

#	Topic	Type	Marks	Question Summary
1	Relational Algebra	S	10	RA expression for finding repeating students, what DB object to use
2	Transactions	S	10	Trace interleaved schedule, populate value table, check correctness
3	Hashing	S	10	Extendible hashing: max/min buckets after 8 inserts into full 4-bucket structure
4	MD Indexing	S	10	Grid file NN query — determine additional buckets to search

Part C — SQL (20 marks)

#	Topic	Type	Marks	Question Summary
1	SQL	S	5	Create view for guests arriving in 3 days
2	SQL	S	5	View with pattern matching + aggregate query on view
3	SQL	S	5	Write trigger for fully booked hotel + verification query
4	SQL	S	5	T-SQL query with specific conditions

Type Distribution: Factual: 1, Conceptual: 0, Solving: 12

Aggregate Analysis Across All Papers

Questions by Topic (all 4 papers combined)

Topic	Total Questions	Factual	Conceptual	Solving
Normalization & FDs	18	2	5	11
Concurrency (2PL, TS)	14	2	4	8
Serializability & Recoverability	12	0	1	11
B+ Tree & Indexing	12	2	5	5
Crash Recovery	8	2	3	3
File Org & Storage	6	1	3	2
SQL & RA	10	1	1	8
Hashing	4	0	0	4
ER & Relational Model	8	3	4	1
Query Processing	4	0	1	3
MD Indexing & Others	3	0	0	3

Key Takeaway

~45% of all marks come from solving-type questions on just 4 topics: normalization, serializability/recoverability, concurrency control protocols, and B+ tree operations. Master these with pen-and-paper practice.