DBMS Scenario Questions: Schema Design, Indexing and Concurrency Trade-offs

A candidate may define 3NF, a B+ tree and two-phase locking, then freeze when an interviewer gives one system and asks, "What would you design, what would you index, and what can break under two requests?" The course-enrolment case links each design choice to a workload, an invariant, actual values and a cost. Interviewers may vary the system or numbers, but the reasoning remains requirement, invariant, mechanism and cost.

Related reading: transaction management and database normalization.

Answer DBMS scenario questions as requirement, choice, mechanism, cost

A strong answer follows four moves: state the requirement and invariant, choose a mechanism, trace it with given values, then name its read, write, storage, contention or complexity cost. "Add an index" is incomplete without the supported query order. "Use a transaction" is incomplete without the protected race.

Our portal has 5,000,000 enrolment rows. Section SEC-DB-03 belongs to C-DB, is taught by I08, has capacity 60, and has one seat left before two requests arrive. It must reject duplicate (student_id, section_id) pairs and never confirm enrolments beyond capacity.

Definition-level revision belongs in DBMS Interview Questions for Freshers: Top 50 Answers; this enrolment case tests how those definitions constrain design choices. The Interview & Resume Preparation Course is an optional route for practising how to structure and deliver such technical answers.

Schema-design scenario: remove update anomalies without deleting history

Start with EnrollmentFlat(student_id, student_name, student_city, section_id, course_id, course_title, instructor_id, instructor_name, enrolled_on, status), keyed by (student_id, section_id).

Changing Meera's city requires two updates. Deleting the last SEC-DB-03 enrolment also erases its only course and instructor facts.

The dependencies are:

• student_id -> student_name, student_city

• course_id -> course_title

• instructor_id -> instructor_name

• section_id -> course_id, instructor_id

• (student_id, section_id) -> enrolled_on, status

Decompose into Student(student_id PK, student_name, student_city), Course(course_id PK, course_title), Instructor(instructor_id PK, instructor_name), Section(section_id PK, course_id FK, instructor_id FK, capacity) and Enrollment(student_id FK, section_id FK, enrolled_on, status, PK(student_id, section_id)).

Each fact now has one owner, and the composite key rejects another S17, SEC-DB-03 row. Keep historical enrolments as rows whose status changes to cancelled or completed instead of deleting them. Reads cost more joins. A dashboard refreshed every 5 minutes across all rows may use a named materialised summary, with refresh lag displayed, while these tables remain authoritative. Do not copy student_city or instructor_name into Enrollment merely to avoid a join.

Indexing scenario: derive one composite index from one exact query

Consider this listing:

SELECT student_id, enrolled_on
FROM Enrollment
WHERE section_id = 'SEC-DB-03'
  AND status = 'active'
  AND enrolled_on >= DATE '2026-07-01'
ORDER BY enrolled_on DESC
LIMIT 50;

Of 5,000,000 rows, 40,000 belong to this section, 30,000 are active there, and 6,000 are active and recent. Choose (section_id, status, enrolled_on DESC, student_id). The equality columns set the range, enrolled_on DESC serves the date and order, and student_id covers the projection in this simplified model.

Assume 100 table rows per page, 200 index entries per leaf page and 3 internal pages per seek.

• Full scan: 5,000,000 / 100 = 50,000 table pages.

• Ordered listing: 3 internal pages plus 1 leaf page for the newest 50 matches, about 4 index pages.

• Export of all recent matches: 6,000 / 200 = 30 leaf pages, then 30 + 3 = 33 index pages.

Page-touch counts do not predict latency. Cache, visibility, included columns, descending keys, partial indexes and index-only scans are engine-specific. Name the database and check its documentation and execution plan.

Index trade-offs: read gain, write cost and leading-column choice

Only section_id leaves up to 40,000 rows to filter and order. (section_id, status, enrolled_on DESC) finds the ordered range but may fetch base rows for student_id. Adding it can cover this projection while widening every entry. Three separate indexes are not automatically equivalent.

Workload A has 800 listing reads per minute, 8,000 inserts and 2,000 status changes per day, making the wider index plausible. Workload B imports 2,000 rows per second but lists only 10 times per hour, so maintenance and page splits matter more. At an assumed 48 bytes per entry, 5,000,000 x 48 = 240,000,000 bytes, about 240 MB decimal before page overhead. This is an estimate.

Lead with section_id because the query fixes it, while 3,800,000 of 5,000,000 rows are active, so status alone is weak. Some optimisers handle equality-column order similarly here, but the prefix affects reuse. Keep the index only after EXPLAIN on representative data.

Concurrency scenario: trace oversubscription before naming a fix

Start with Section(SEC-DB-03, capacity=60, seats_remaining=1, version=12) and no enrolment for S21 or S22. At 09:00:00.000, R1 and R2 both read one. They insert E9001 and E9002, each computes 1 - 1 = 0, and both report success. Two enrolments consumed one seat, while the stored zero hides the violation.

This read-check-write did not serialise the decision. A transaction boundary alone proves nothing. Name the conditional write, row lock, version check, serializable transaction or equivalent enforcement in the chosen engine.

If R1 commits but its response times out, UNIQUE(student_id, section_id) blocks a duplicate S21 row, while unique request_id='REQ-771' lets a retry retrieve and return the earlier committed outcome. Duplicate and capacity checks protect different invariants.

Choose conditional update, row lock or optimistic version by contention

The compact atomic option is:

UPDATE Section
SET seats_remaining = seats_remaining - 1
WHERE section_id = 'SEC-DB-03'
  AND seats_remaining > 0;

One request gets affected_rows=1, inserts and commits. The other gets zero and reports full. Roll back the decrement if insertion fails. Retain both unique keys.

With a row lock, R1 locks at 09:00:00.000, changes 1 -> 0, inserts E9001, and commits at 09:00:00.040. R2 waits 40 ms, sees zero and refuses E9002. Heavy contention makes this simple but increases waiting and deadlock exposure. Keep transactions short and use one lock order.

Optimistically, both read version 12. R1 updates through WHERE version=12 AND seats_remaining>0, producing zero seats and version 13. R2 affects zero rows, retries and stops. This avoids rare-collision waiting, but 200 attempts for the last seat can cause many failures. Transactions & Concurrency Control in DBMS covers wider choices. Engine behaviour differs.

Interview traps: absolute rules fail when the workload changes

Three follow-ups test transfer. All active enrolments across sections cannot directly use the leading section_id prefix. Multiple instructors require SectionInstructor(section_id, instructor_id, PK(section_id, instructor_id)). A cancellation that restores a seat must combine its state change and counter increment in one idempotent transaction.

Page-count arithmetic is not a benchmark. An execution plan is required before claiming index use, and a study page cannot establish an employer's interview format.

DBMS scenario questions: a 25-minute drill and the next step

• Minutes 0-6: derive the five tables and explain why student_city has one owner.

• Minutes 6-12: derive the composite index and reproduce 50,000 versus 4 page touches.

• Minutes 12-18: trace the two-request oversubscription.

• Minutes 18-22: compare conditional, pessimistic and optimistic control.

• Minutes 22-25: answer one changed requirement without dropping the original invariant.

KnowledgeGate offers more than 2,200 DBMS practice questions across the subject.

The short version: model each fact once, derive the index from its access path, and make the shared-state invariant atomic. Computer Science Fundamentals for Placements by Sanchit Sir supports broader revision. The Resume & Interview Preparation category is another optional route.