ISRO CS DBMS questions move from an attribute-closure calculation to SQL output, index I/O or a precedence graph. Remembering isolated definitions does not survive that mix. Four ISRO Computer Science previous-year questions from 2015 to 2018 are worked out below, each with a link to its own practice page, and a fifth problem carries one relation from its candidate key to a BCNF decomposition. Our ISRO DBMS previous-year set runs past 70 questions, so the skill that pays is recognising the reasoning family fast.
ISRO CS DBMS PYQ patterns: identify the reasoning family first
Match each reasoning family to its scratch work:
For functional dependencies and keys, make a closure table.
For normal forms and decomposition, mark the key and test every dependency.
For SQL logical processing, write the filtered rows before grouping.
For file organisation and B+ tree cost, list page-count assumptions.
For transactions, locking and serialisability, draw the precedence graph.
Arrows between attributes suggest closure or normalisation. A query printed beside relation rows demands logical-order tracing. Block size and fan-out signal I/O arithmetic. Interleaved reads and writes require conflict edges. The wider subject-by-subject route for this exam sits on the ISRO Scientist/Engineer CS Preparation page.
Candidate keys and functional dependencies: calculate every closure
ISRO CS 2015 set this one, and it appears in UGC NET June 2014 as well. Let R = {A, B, C, D, E, F} be a relation schema with F = {C -> F, E -> A, EC -> D, A -> B}. Which of the following is a key for R?
(A)
CD(B)
EC(C)
AE(D)
AC
Start with EC. E -> A adds A, C -> F adds F, A -> B adds B, and EC -> D adds D. So (EC)+ = {A, B, C, D, E, F}, which is every attribute of R.
Now test the alternatives instead of stopping at the first success. E+ = {E, A, B} and C+ = {C, F}, so neither half of EC determines everything on its own, which makes EC minimal and a candidate key. (CD)+ = {C, D, F}, (AE)+ = {A, E, B} and (AC)+ = {A, B, C, F}, none of which reach all six attributes. Option B is the answer. Attempt it cold on the ISRO keys and integrity constraints practice set before reading the working.
Normalisation in DBMS: carry one relation from 2NF to a BCNF split
Now take a second relation, this one R(A, B, C, D, E) with F = {A -> BC, C -> D, D -> E} and atomic attributes throughout. A+ starts at {A}, then A -> BC gives {A, B, C}, C -> D adds D and D -> E adds E, so A+ = {A, B, C, D, E} and A is the only candidate key. What is the highest normal form the relation satisfies?
(A) 1NF
(B) 2NF
(C) 3NF
(D) BCNF
A has no proper subset other than the empty set, so no non-prime attribute can depend on part of the key. That rules out partial dependency, and the relation is in 2NF.
It stops there. In C -> D, determinant C is not a superkey and D is non-prime. In D -> E, determinant D is not a superkey and E is non-prime. Both dependencies violate 3NF and BCNF, so option B is correct. A single-attribute key prevents partial dependency, but it does not automatically give 3NF.
For a lossless, dependency-preserving BCNF decomposition, first use D -> E. Split off R3(D, E), leaving R'(A, B, C, D). The intersection D determines R3, so the split is lossless. Next use C -> D to obtain R2(C, D) and R1(A, B, C). Intersection C determines R2, making this split lossless. The dependencies remain enforceable as A -> BC in R1, C -> D in R2, and D -> E in R3.
SQL query output: apply WHERE, GROUP BY and HAVING in order
ISRO CS 2015 gave the schema Emp(Empcode, Name, Sex, Salary, Deptt) and this query:
SELECT Deptt
FROM Emp
WHERE Sex = 'M'
GROUP BY Deptt
HAVING AVG(Salary) > (SELECT AVG(Salary) FROM Emp);The query returns the departments in which the average salary of male employees:
(A) is the average salary of the organisation
(B) is less than the average salary of the organisation
(C) is equal to the average salary of the organisation
(D) is more than the average salary of the organisation
Settle it on rows rather than on intuition. Take this six-row instance of Emp:
Empcode | Name | Sex | Salary | Deptt |
|---|---|---|---|---|
1 | Asha | F | 40000 | Computer |
2 | Ravi | M | 90000 | Computer |
3 | Iqbal | M | 50000 | Computer |
4 | Nita | F | 60000 | Mechanical |
5 | Gopal | M | 46000 | Mechanical |
6 | Deepa | F | 38000 | Civil |
The subquery has its own scope and runs over the whole table, both sexes: (40000 + 90000 + 50000 + 60000 + 46000 + 38000) / 6 = 54,000. WHERE then keeps only rows 2, 3 and 5. GROUP BY Deptt builds Computer with (90000, 50000) averaging 70,000 and Mechanical with (46000) averaging 46,000. Civil forms no group at all, because its only row is female.
HAVING compares each group average against 54,000, so Computer survives at 70,000 and Mechanical is dropped at 46,000. The output is Computer, and option D states the rule: the query returns departments whose male average beats the organisation average, not the reverse and not an equality. The same question is on the ISRO SQL GROUP BY practice set, and GROUP BY and HAVING in SQL: Solved Query Examples drills the same processing order.
B+ tree indexing: turn storage values into page-I/O cost
ISRO CS 2018 asked: a file contains 1 million records and the order of the tree is 100. What is the maximum number of nodes to be accessed if a B+ tree index is used?
(A) 5
(B) 4
(C) 3
(D) 10
Order 100 means a node holds up to 100 entries, so one leaf indexes up to 100 records and one internal node separates up to 100 children. A million records therefore need at least 1,000,000 / 100 = 10,000 leaf nodes. Those 10,000 leaves need 100 parents, and those 100 parents need a single root, so the index stands three levels deep: root, one internal level, leaf.
The root-to-leaf path touches 3 index nodes, and the record itself sits in a data block, which is one more access. The maximum is 3 + 1 = 4, so option B. Stop at the height and you answer 3, which is option C and the commonest slip on this question.
Add two storage facts the question leaves out and the worth of the index becomes visible. At 100 bytes per record in 4,096-byte unspanned pages, the blocking factor is floor(4096 / 100) = 40 records per page, so the data file spans ceil(1,000,000 / 40) = 25,000 pages. A binary search over that sorted file costs ceil(log2 25,000) = 15 reads against the index's 4. A cached root or a clustered leaf layout moves the count again, which is why every storage assumption belongs in the working. The original question is on the ISRO B+ tree practice set, and B+ Trees and Database Indexing: Worked Insert Example builds the structure the count depends on.
Transactions and serialisability: precedence graph and timestamp order
ISRO CS 2018 set the schedule S = r1(A); r2(B); w2(A); w1(B), with transaction T1 arriving before T2, and asked which statement is true:
(A) allowed under the basic timestamp protocol
(B) not allowed under basic timestamp protocols, because T1 is rolled back
(C) not allowed under basic timestamp protocols, because T2 is rolled back
(D) none of these
Timestamps follow arrival order, so TS(T1) < TS(T2). The read r2(B) raises the read timestamp of B to TS(T2). When T1 later attempts w1(B), basic timestamp ordering refuses a write from a transaction older than the last reader of that item, so T1 is aborted and rolled back. Option B. The write w2(A) is never in trouble, because T2 is the younger transaction, which is what option C gets backwards.
The same two conflicts settle serialisability as well. On A, r1(A) precedes w2(A), giving the edge T1 -> T2; on B, r2(B) precedes w1(B), giving T2 -> T1. Put values on that pattern with the two writes in the other order, writing A as X and B as Y: S = r1(X=100), r2(Y=200), w1(Y=150), w2(X=80). Each conflicting pair is unchanged, so the graph is the same two-node cycle T1 -> T2 -> T1.
A cyclic precedence graph has no topological order, so neither version has a conflict-equivalent serial schedule. Neither proves recoverability or cascadelessness either, because neither supplies a commit or an abort. The original question is on the ISRO timestamp-ordering practice set, and DBMS Transaction MCQs: 12 Solved (ACID, Locking) separates serialisability from locking and recovery.
ISRO CS DBMS PYQ traps and where to check the official pattern
Tempting shortcut | Why it fails | Correct check |
|---|---|---|
Treat every superkey as minimal | A superkey may carry extraneous attributes | Drop one attribute and recompute the closure |
Assume a single-attribute key implies 3NF | It rules out only partial dependency | Test every non-trivial FD against 3NF |
Assume the HAVING subquery is filtered too | The subquery has its own scope and sees every row | Evaluate the subquery on the full table first |
Apply HAVING before grouping | HAVING filters completed groups | Run WHERE, then GROUP BY, then HAVING |
Report B+ tree height alone | The record still costs one more access | Add the data-block read to the path length |
Draw the edge from the later transaction to the earlier one | Direction follows schedule order | Point the earlier operation's transaction to the later one |
Every trap above is a method error rather than a memory error, which is why the same ones keep working paper after paper. Marks split, question count, duration and negative marking do change between advertisements, so read those off the notification you are applying under, at ISRO Current Opportunities.
ISRO CS DBMS PYQs: the short revision loop and next step
Use this five-line scratch-work loop:
Compute closures completely.
Test every non-trivial FD against 3NF and BCNF.
Materialise SQL rows in logical order.
Write every storage assumption before counting I/O.
Draw one conflict edge at a time, then test the graph for a cycle.
Redo the four ISRO questions and the BCNF decomposition without looking at the solutions if DBMS is your immediate target. When you want the whole exam route and previous-year practice organised in one place, continue with the ISRO Scientist/Engineer SC (CS) Course.
