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:

ExaminerDatabase Systems viva examiner
Technical correctness and terminologyReasoning from stated assumptionsTrade-offs and limitations

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:

StationAttemptsBestAvg
Normalisation and functional dependenciesGiven a student-course relation with student, course, instructor and grade attributes, identify a suitable key and explain how you would normalise it.27468
Lost updates and isolationTwo transactions read the same account balance and then write different updates. Explain the anomaly and how you would prevent it.37871
Index choice and query plansA query filters orders by customer and date, sorts by date and returns a small number of rows. What index would you consider, and how would you test it?17070
Write-ahead logging and recoveryAfter a crash, what role does a write-ahead log play in restoring the database to a consistent state?0——

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:

Oral — Normalisation and functional dependenciesMarked

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.

2:323:00Mark answer
75%Normalisation and functional dependencies — marked75/100 · Developing · 2:32 spoken of 3:00
Technical correctness and terminology16/20

You correctly identified the composite key and repeated course facts.

ImproveName the functional dependencies explicitly before using them to justify a decomposition.

Reasoning from stated assumptions15/20

You used the assumption that a student may enrol in multiple courses.

ImproveExplain why neither StudentID nor CourseID alone identifies one enrolment.

Trade-offs and limitations14/20

You proposed separating student and course details.

ImproveAccount for the separate InstructorID-to-InstructorOffice dependency as well.

Clear, complete explanation15/20

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:

Transcript

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.

not enough detail
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:

Oral — Lost updates and isolationMarked

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.

2:483:00Mark answer
76%Lost updates and isolation — marked76/100 · Developing · 2:48 spoken of 3:00
Technical correctness and terminology16/20

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.

Reasoning from stated assumptions15/20

You traced both reads from the original value of 100.

ImproveShow the two opposing precedence edges if you use conflict serialisability.

Trade-offs and limitations15/20

You suggested locking and an atomic update.

ImproveNote that isolation behaviour and locking details depend on the database system and statement used.

Clear, complete explanation15/20

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:

Oral — Index choice and query plansMarked

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.

2:393:00Mark answer
78%Index choice and query plans — marked78/100 · Developing · 2:39 spoken of 3:00
Technical correctness and terminology16/20

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.

Reasoning from stated assumptions16/20

You connected the index to the filter and ordering in the query.

ImproveKeep the recommendation conditional on the plan, selectivity and data distribution.

Trade-offs and limitations15/20

You mentioned extra storage and slower writes.

ImproveSay that an optimiser can still prefer a scan or a different plan.

Clear, complete explanation15/20

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:

Spoken feedback

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.