How to use these questions

These eight single-best-answer questions cover common database systems topics: functional dependencies, normalisation, SQL semantics, serialisability, indexes, and transaction properties. They are practice questions, not a claim about any particular course or examination format. Match the topics and terminology to your course specification and lecture notes.

Choose an option for each question, then read the explanation straight after it. The questions are designed to test a specific decision, not to replace working through the underlying concepts. A question from a Database Systems board in MySummaries looks like this:

Question 11 mark

For relation R(A, B, C, D), the functional dependencies are A → BC, C → D, and D → A. Which set contains all candidate keys?

A determines B and C, then C determines D, so A is a key. C determines D, D determines A, and A determines B and C, so C is a key. D determines A and then B and C, so D is a key. Each is minimal. Option 1 misses the keys C and D; adding attributes to a key, as in option 2, does not make a candidate key.

The explanation uses attribute closure: follow the dependencies from a proposed key and check whether they produce every attribute. Then check minimality—no proper subset of the proposed key should also determine the full relation. This two-step test is more dependable than guessing from how many attributes appear on the left-hand sides.

Questions

1. Candidate keys and functional dependencies

This tests whether you can calculate candidate keys and distinguish a minimal key from a superkey.

Question 11 mark

For relation R(A, B, C, D), the functional dependencies are A → BC, C → D, and D → A. Which set contains all candidate keys?

A determines B and C, then C determines D, so A is a key. C determines D, D determines A, and A determines B and C, so C is a key. D determines A and then B and C, so D is a key. Each is minimal. Option 1 misses the keys C and D; adding attributes to a key, as in option 2, does not make a candidate key.

Attribute closure is the useful method here: follow the dependencies until no new attributes can be added, then test each proposed key for minimality. In an assessment answer, show enough of that reasoning to make clear why the keys are complete, rather than only listing one key.

2. Third normal form and BCNF

This tests the difference between the 3NF exception for a prime attribute and BCNF's stricter determinant rule.

Question 21 mark

A relation R(A, B, C) has functional dependencies AB → C and C → B. Candidate keys are AB and AC. Which is the strongest normal-form statement that is true?

C → B violates BCNF because C is not a superkey. It does satisfy 3NF: C is not a superkey, but B is a prime attribute because it belongs to candidate key AB. Option 1 overlooks the BCNF violation. The stated dependencies do not establish a 2NF violation, and no fact suggests non-atomic values.

The key check is not simply whether a dependency has a non-key determinant. For 3NF, a dependency X → A is allowed when X is a superkey or A is prime. BCNF permits only the superkey case. Be ready to state which dependency causes the violation and why.

3. SQL comparisons with NULL

This tests SQL's three-valued logic: a comparison involving NULL can evaluate to UNKNOWN rather than TRUE or FALSE.

Question 31 mark

An Employee table has rows with salary values 40000, 50000, and NULL. What does SELECT COUNT(*) FROM Employee WHERE salary <> 50000 return?

Only the row with salary 40000 satisfies the WHERE condition. The comparison NULL <> 50000 is UNKNOWN, so that row is not selected; the 50000 row is FALSE. Option 1 is correct. Treating NULL as zero would incorrectly include it. Use IS NULL to test for a missing value.

A WHERE clause keeps rows only when its condition is TRUE. UNKNOWN does not pass the filter. This matters beyond inequality: ordinary comparisons such as = NULL do not test for missing values; use IS NULL or IS NOT NULL.

4. Conflict serialisability

This tests whether you can construct a precedence graph from conflicting operations and use a cycle to decide serialisability.

Question 41 mark

Consider the schedule r1(X), w1(X), r2(X), w2(X), r2(Y), w2(Y), r1(Y), w1(Y). Is it conflict-serialisable?

On X, T1's write precedes T2's read and write, giving T1 → T2. On Y, T2's write precedes T1's read and write, giving T2 → T1. These edges form a cycle, so the schedule is not conflict-serialisable. Options 1 and 2 each satisfy one edge but contradict the other.

A repeatable procedure is to compare operations from different transactions on the same item. Read–read pairs do not conflict; a pair conflicts if at least one operation is a write. Add the directed edge in schedule order. A cycle means no topological ordering exists, so the schedule is not conflict-serialisable.

5. Choosing an index for a range query

This tests how index structure affects a query that needs ordered range retrieval.

Question 51 mark

A query frequently retrieves records with created_at between two timestamps. Which index structure is generally suited to supporting this range search?

A B+ tree keeps keys in sorted order and can locate the start of the range, then scan forward through matching keys. A hash index is useful for equality lookup but does not preserve key order for range scans. Option 2 does not provide an ordered index on the timestamp.

“Generally suited” matters: the actual benefit depends on the workload, selectivity, table size, and database engine. An index also has costs, including storage and extra work when indexed values are inserted or updated. A sound answer names the access pattern the structure supports, rather than claiming an index always makes a query faster.

6. Partial dependencies and 2NF

This tests whether a non-key attribute depends on only part of a composite candidate key.

Question 61 mark

In Enrollment(student_id, course_id, student_name, course_title, grade), the candidate key is (student_id, course_id). The dependencies include student_id → student_name and course_id → course_title. Which issue is present?

student_name depends on only student_id, and course_title depends on only course_id. Each is a proper subset of the composite key, so these are partial dependencies of non-prime attributes and violate 2NF. Option 0 mislabels the dependencies: they are not shown as transitive dependencies through another non-key attribute.

A typical decomposition separates student details, course details, and the student-course relationship. Keep the key and attribute dependencies explicit when checking the result: a decomposition should reduce the partial dependencies without losing the facts represented by the original relation.

7. Atomicity

This tests whether you can identify the ACID property that governs an all-or-nothing transaction.

Question 71 mark

A bank transfer debits one account and credits another. If the transaction fails after the debit but before the credit, which ACID property requires the transaction's effects to be rolled back as a unit?

Atomicity requires the transaction to take effect completely or not at all, so a failure must not leave only the debit committed. Consistency concerns preserving database constraints; isolation concerns concurrent transactions; durability concerns committed changes surviving failures.

A common error is to treat the ACID terms as interchangeable. For this scenario, focus on the incomplete transaction. If the question instead asked whether a committed credit survives a subsequent crash, durability would be the relevant property.

8. Filtering grouped results

This tests the difference between filtering individual rows before grouping and filtering groups after aggregation.

Question 81 mark

You need each department whose employee count is at least 3. Which clause applies the condition to the grouped COUNT(*) result?

HAVING filters groups after GROUP BY has formed them and aggregate values have been calculated. WHERE filters input rows before grouping, so an aggregate such as COUNT(*) cannot be used there in the ordinary query pattern. ORDER BY sorts results; it does not filter them.

The usual structure is SELECT department_id, COUNT(*) FROM Employee GROUP BY department_id HAVING COUNT(*) >= 3. If the condition concerns individual employees, filter those rows with WHERE before grouping. If it concerns the count for each department, use HAVING.

Use the results to choose what to review

Do more than count correct answers. For each missed question, identify the exact rule that failed: closure and minimality, the 3NF prime-attribute exception, UNKNOWN in SQL, a cycle in a precedence graph, ordered versus equality lookup, partial dependency, an ACID definition, or aggregate filtering. Then test the rule with a new example from your course material. A correct guess is not evidence that you can explain or apply the concept.

A results summary for this set might show weaker areas like this:

Where marks go missing
50%Conflict serialisability4×
63%Normalisation5×
88%SQL semantics8×

The percentages and attempt counts help separate a recurring gap from a single mistake. If serialisability remains weak, practise drawing precedence graphs and explaining each edge before moving on to more complex schedules. For normalisation, work from candidate keys and dependencies rather than memorising labels alone.

A missed concept can be turned into a short prompt for a later review:

Remediation tray

You lost this mark twice: which edge does a conflicting operation pair add to the precedence graph, and what does a cycle prove?

Add cardDismiss

Answer the prompt from memory, then check your reasoning against your notes. If you cannot justify the rule with a fresh example, return to the relevant lecture or textbook section and try again.

How MySummaries helps

Build a Database Systems revision board from your own notes, PDFs, slides, or photographed material, then use it to generate practice questions and track which topics need review. Open MySummaries.