How to practise for a Database Systems viva
A viva in Database Systems can ask you to explain a concept, reason from a schema or schedule, and defend a design choice. The exact format depends on your course and institution: check your course handbook or official department guidance for the topics, assessment format and expectations that apply to you. There is no single international Database Systems viva format.
For practice, make each answer do four things: state your assumptions, give the relevant rule or mechanism, apply it to the example, and name a limitation or trade-off. This avoids two common problems: reciting definitions without using them, and jumping to a recommendation before establishing what the question assumes.
The practice examiner used here focuses on technical correctness, explicit reasoning and trade-offs. Those are the platform’s practice emphases, not a claim about how any university marks a viva. A useful starting strip looks like this:
Build practice stations from the material you have actually been taught. The four below cover relational design, transaction concurrency, indexes and query plans, and crash recovery. Treat their opening questions as prompts for a spoken answer, not as a script to memorise. The attempt counts and scores in the practice screens are examples of study progress, not exam results.
The station list for this set looks like this:
Station 1: Normalisation and functional dependencies
This station tests whether you can move from a wide table to dependencies and a defensible decomposition. State the assumptions first: a schema alone does not tell you which values are unique or which dependencies hold. In this example, assume each student has one student ID, each course has one course ID, and each instructor ID identifies one instructor and office.
A clear answer distinguishes a key from a functional dependency. If a student can take several courses, neither StudentID nor CourseID alone identifies an enrolment; the pair can identify it. Then use the stated dependencies to explain why storing student, course and instructor details on every enrolment row creates repeated facts and update anomalies.
The first oral station, with practice feedback against the platform’s rubric, looks like this:
Examiner
Assume StudentID determines StudentName; CourseID determines CourseName and InstructorID; InstructorID determines InstructorOffice; and (StudentID, CourseID) determines Grade. Identify a candidate key for the original relation, explain two anomalies, and propose a decomposition that avoids them.
You correctly identified the composite key and repeated course facts.
ImproveName the functional dependencies explicitly before using them to justify a decomposition.
You used the assumption that a student may enrol in multiple courses.
ImproveExplain why neither StudentID nor CourseID alone identifies one enrolment.
You proposed separating student and course details.
ImproveAccount for the separate InstructorID-to-InstructorOffice dependency as well.
Your answer was understandable and linked repetition to update problems.
ImproveFinish by naming the attributes in each resulting relation.
A strong answerThe candidate key for the original relation is (StudentID, CourseID), because a student can take several courses and a course has several students. StudentID determines StudentName, while CourseID determines CourseName and InstructorID, and InstructorID determines InstructorOffice; the key pair determines Grade. Repeating these facts across enrolment rows can cause update anomalies, such as changing an instructor’s office in some rows but not others. I would separate Student(StudentID, StudentName), Course(CourseID, CourseName, InstructorID), Instructor(InstructorID, InstructorOffice) and Enrolment(StudentID, CourseID, Grade). Under the stated dependencies, these relations separate the independently determined facts while retaining the enrolment grade.
That is a MySummaries station, filled with database systems material. Yours is written from your own notes. Start free
Notice the assumptions in the model answer. If the examiner changes a rule—for example, if instructors can teach the same course in different terms—ask whether the schema needs a term or section attribute before claiming a key. Normalisation is not a pattern-matching exercise: the right decomposition depends on the functional dependencies and candidate keys you are given.
One sentence that loses precision is easy to spot when you review a recording. The transcript below marks only the phrase that needs more detail; the rest is the candidate’s answer, not an error:
The candidate key is the student and course together, because a student can take more than one course. Course details repeat in the table, so I would split it up. I would also put the instructor office with the instructor ID, since that determines it. Then the enrolment relation keeps the grade for that student on that course.
Course details repeat in the table, so I would split it up.
The answer identifies repetition but does not name the relations or show which attributes go in each one.
Say: Say: ‘I would store CourseID, CourseName and InstructorID in Course; InstructorID and InstructorOffice in Instructor; and StudentID, CourseID and Grade in Enrolment.’A stronger follow-up is to mention that a decomposition should be assessed for properties such as lossless joins and dependency preservation. Do not assert these properties just because you have divided a table: explain how the dependencies support your claim, or say what further information you would need.
Station 2: Lost updates and isolation
This station asks you to reason through an interleaving rather than recite isolation-level names. Suppose an account starts at 100. T1 reads 100 and calculates 90 after a debit of 10. T2 also reads 100 and calculates 120 after a credit of 20. If T1 writes 90 and T2 then writes 120, the debit is lost; the result is not the 110 that both changes would produce together.
The key viva move is to identify what each transaction read and wrote, then explain why the interleaving is unsafe. A conflict-serialisability explanation can add precision: T1’s read before T2’s write gives an edge from T1 to T2, while T2’s read before T1’s write gives an edge from T2 to T1. The cycle means this schedule is not conflict-serialisable.
The transaction station and a complete practice answer look like this:
Examiner
An account balance begins at 100. T1 reads it and plans to subtract 10. T2 reads it and plans to add 20. T1 writes 90, then T2 writes 120, and both commit. Name the anomaly, explain the problem with this schedule, and give two ways to prevent it.
You identified a lost update and calculated that applying both changes gives 110.
ImproveDistinguish the lost update from a dirty read: both transactions here commit.
You traced both reads from the original value of 100.
ImproveShow the two opposing precedence edges if you use conflict serialisability.
You suggested locking and an atomic update.
ImproveNote that isolation behaviour and locking details depend on the database system and statement used.
You gave two practical prevention approaches.
ImproveExplain that a serialisable transaction may be aborted and require a retry.
A strong answerThis is a lost update: each transaction bases its write on 100, so the final value of 120 overwrites the debit and does not include both changes. The schedule has conflicting operations in both directions, producing a cycle in its precedence graph, so it is not conflict-serialisable. One approach is to run the read-modify-write operation under suitable locking or serialisable isolation, handling any transaction abort and retry. Another is to express the change as an atomic database update, such as adding or subtracting from the stored balance, rather than calculating a replacement value from an earlier application read. I would check the database system’s documented isolation and update behaviour rather than assume every product handles a particular isolation level identically.
When asked about isolation levels, define the anomaly or guarantee the question cares about before naming a level. Implementations and the behaviour of particular statements can differ between database systems. A safe answer says what guarantee you need, then says you would confirm how the chosen system provides it. If you propose locks, mention their costs: contention can reduce concurrency, and deadlocks may require aborting and retrying a transaction.
Station 3: Index choice and query plans
Here the examiner wants a reasoned index choice and a way to check whether it helps. Consider a query that filters on customer_id, applies a lower bound to created_at, orders newest first and returns 20 rows. A B-tree index beginning with customer_id and then created_at is a reasonable candidate: the equality condition can use the leading column, and the date column can support the range condition and potentially the ordering.
Do not promise that the index will be used or will make the query faster. The optimiser may choose another plan based on table size, statistics, data distribution, available indexes and the cost of fetching table rows. A covering index may reduce extra reads if it contains the requested columns, but it costs storage and makes writes more expensive. Check an execution plan using the syntax and tools for the database system in question; where available, compare actual execution information as well as the estimated plan.
A full practice response to the index question looks like this:
Examiner
For SELECT customer_id, created_at FROM orders WHERE customer_id = ? AND created_at >= ? ORDER BY created_at DESC LIMIT 20, what index would you test? Explain what you would inspect in the query plan and one cost of adding the index.
You proposed a composite B-tree index with customer_id before created_at.
ImproveExplain the equality-prefix and date-range roles of the two columns.
You connected the index to the filter and ordering in the query.
ImproveKeep the recommendation conditional on the plan, selectivity and data distribution.
You mentioned extra storage and slower writes.
ImproveSay that an optimiser can still prefer a scan or a different plan.
You named a plan inspection as the validation step.
ImproveState what evidence you would compare, such as rows estimated versus observed and sort or table-access steps.
A strong answerI would test a B-tree index on (customer_id, created_at), with descending date order if the database system supports that index ordering and it suits the workload. The equality filter can use the leading customer_id column, followed by the created_at range; the index may also help return rows in the requested order. I would inspect the system’s query plan for the chosen access path, estimated and observed row counts where available, and any separate sort or table lookups. I would compare performance on representative data rather than assume the index must be used. The index consumes storage and adds work to inserts, deletes and updates, so I would keep it only if the workload justifies those costs.
A useful spoken follow-up is: “I would test that index against representative data and inspect the chosen plan, because the optimiser’s decision depends on the workload and statistics.” That sentence makes a conditional recommendation rather than treating an index as a guaranteed fix.
Station 4: Write-ahead logging and recovery
For recovery questions, separate the order-of-write rule from what recovery does after a crash. In a write-ahead logging approach, the relevant log record must be made durable before the corresponding changed data page is written to durable storage. This gives the recovery process information it can use to restore database consistency after a failure. Exact recovery protocols, checkpoints and logging details depend on the database system, so avoid claiming that all systems perform identical steps.
The fourth station is included so you can practise a topic that is easy to describe too vaguely. Give the core rule first, then explain what the log is for and identify where implementation details may vary.
A sample examiner sentence can be reviewed in audio debrief form:
You gave the write-ahead rule clearly; now connect it to how recovery uses the log after a crash.
A repeatable method for your own viva topics
Use a five-step cycle for each topic from your syllabus:
- Turn a heading into an opening question. “Explain normalisation” is broad. “Given these dependencies, what is the candidate key, and what decomposition would you choose?” gives you a problem to reason through.
- Answer aloud in a fixed order. State assumptions, name the relevant concept, apply it to the example, and finish with a limitation or alternative. Aim for a concise first response, then invite follow-up questions rather than adding every fact you know.
- Record one attempt. Listen for unsupported claims, missing definitions, and points where you name a solution without explaining why it fits. A technically correct answer can still be incomplete if it skips the reasoning.
- Repair the precise gap. If you cannot explain why a schedule has a cycle, revise precedence graphs—not all of transaction processing. If you recommend an index without considering write cost, add that trade-off to your next attempt.
- Repeat after a delay. Re-answer the same question later without reading the model. Then change one assumption and see whether your answer changes appropriately.
Keep a personal question bank organised by the topics on your course. For each entry, store the prompt, the assumptions, a short model structure, and the one point you missed last time. Ask a study partner to challenge one assumption or request a comparison—for example, “Why not a different isolation guarantee?” or “What changes if the date predicate is not selective?” Those follow-ups reveal whether you understand the mechanism or have memorised one response.
Do not treat the sample scores as a target pass mark. They are feedback from a practice rubric, not university assessment criteria. Use them to choose what to practise next: a weak score in reasoning calls for worked schedules or schemas; a weak score in trade-offs calls for comparing alternatives; a weak score in clarity calls for shorter, more explicit answers. Check your course materials for the terminology and scope your department expects.
How MySummaries helps
MySummaries can turn your own Database Systems notes into a revision board, then use that board to create oral stations and examiner-style practice with recorded answers and feedback. Add your course slides, PDFs or photographed notes, check the generated questions against your syllabus, and practise the topics you need to explain aloud. Open MySummaries.