DBMS Interview Questions: Keys, Normalisation and Transactions Through One Worked Schema

Definitions feel easy until an interviewer changes the schema or adds a concurrency follow-up. Keys, dependencies, normal forms, ACID and isolation interact in a university-enrolment system. Use it with wider CS Fundamentals for Placements revision to prove answers from data.

1. DBMS keys: identify every key in one enrolment relation

Start with ENROLMENT_RAW(StudentID, StudentEmail, StudentName, DeptID, DeptName, CourseID, CourseTitle, InstructorID, InstructorName, Grade):

The composite candidate keys are (StudentID, CourseID) and (StudentEmail, CourseID). Choose the first as primary, leaving the second alternate. {StudentID, CourseID, Grade} is a non-minimal superkey because Grade is unnecessary. StudentID, StudentEmail and CourseID are prime; the rest are non-prime.

A candidate key is a minimal superkey; primary and alternate keys are the selected and unselected candidates. After decomposition, a foreign key references another relation and may repeat. Thus a primary key is not the only candidate key. The earlier Super Key, Candidate Key and Primary Key worked example owns exhaustive superkey enumeration and SQL constraint selection; this interview schema instead carries two derived keys through decomposition, ACID and isolation.

2. Functional dependencies: prove the candidate keys with closures

Use these business rules, not patterns guessed from three rows:

StudentID -> StudentEmail, StudentName, DeptID

StudentEmail -> StudentID

DeptID -> DeptName

CourseID -> CourseTitle, InstructorID

InstructorID -> InstructorName

(StudentID, CourseID) -> Grade

For {StudentID, CourseID}+, start with the pair. StudentID adds {StudentEmail, StudentName, DeptID}; DeptID adds DeptName; CourseID adds {CourseTitle, InstructorID}; InstructorID adds InstructorName; the pair adds Grade. That is all ten attributes.

It is minimal: StudentID+ misses course data and Grade; CourseID+ misses student data and Grade. For {StudentEmail, CourseID}+, email gives StudentID, then the same chain reaches everything. Every key needs the underived CourseID plus StudentID or StudentEmail, ruling out other minimal candidates.

3. DBMS normalisation: take the same relation from 1NF to BCNF

The raw relation is in 1NF because its values are atomic, yet it is redundant. Renaming D10 from CSE to Computer Science needs several updates. Deleting the last C205 enrolment erases that I9 teaches Operating Systems. A new course cannot be stored before anyone enrols.

For 2NF, remove partial dependencies:

• STUDENT_2NF(StudentID, StudentEmail, StudentName, DeptID, DeptName)

• COURSE_2NF(CourseID, CourseTitle, InstructorID, InstructorName)

• ENROLMENT(StudentID, CourseID, Grade)

For 3NF, remove transitive dependencies:

• STUDENT(StudentID, StudentEmail, StudentName, DeptID)

• DEPARTMENT(DeptID, DeptName)

• COURSE(CourseID, CourseTitle, InstructorID)

• INSTRUCTOR(InstructorID, InstructorName)

• ENROLMENT(StudentID, CourseID, Grade)

StudentID keys STUDENT, where StudentEmail is unique. DeptID, CourseID and InstructorID key their tables; (StudentID, CourseID) keys ENROLMENT; corresponding IDs are foreign keys. Every non-trivial determinant is now a key, so the result is BCNF. The join is lossless and the listed FDs remain enforceable without joins. DBMS Normalization from 1NF to BCNF owns the full normal-form sequence; this section extends the same registration setting with email, course and instructor facts before carrying it into transactions and isolation.

4. 3NF versus BCNF: answer the follow-up most candidates blur

Take TEACHING(StudentID, CourseID, InstructorID) with (S17, C101, I7), (S22, C101, I7) and (S17, C205, I9). Given (StudentID, CourseID) -> InstructorID and InstructorID -> CourseID because each instructor teaches one course, its candidate keys are (StudentID, CourseID) and (StudentID, InstructorID).

InstructorID -> CourseID satisfies 3NF because CourseID is prime, but violates BCNF because InstructorID is not a superkey. Decompose into INSTRUCTOR_COURSE(InstructorID, CourseID) and STUDENT_INSTRUCTOR(StudentID, InstructorID).

The join is lossless because common attribute InstructorID determines INSTRUCTOR_COURSE. However, (StudentID, CourseID) -> InstructorID is not directly preserved. BCNF need not preserve every dependency.

5. DBMS transactions and ACID: make one enrolment all-or-nothing

Add COURSE_CAPACITY(CourseID, Capacity, SeatsTaken) with (C101, 30, 29). T1 for S31 begins, conditionally changes 29 to 30 when SeatsTaken < Capacity, inserts ENROLMENT(S31, C101, NA), then commits. Insert only if one row changed. On a duplicate key or an interruption before commit, rollback restores 29.

Atomicity joins the update and insert. Consistency preserves 0 <= SeatsTaken <= Capacity and unique (StudentID, CourseID). Isolation hides partial state. Durability keeps committed 30 and (S31, C101, NA) after recovery. COMMIT alone proves little: wrong checks can make an atomic transaction violate an invariant. The transactions and concurrency control guide develops schedules and serializability.

6. Isolation levels: trace the race before naming the level

Initially, C101 has capacity 30, counter 29, and 29 rows. T1/S31 and T2/S32 both read 29. Each writes 30, inserts its enrolment and commits. Now 29 + 2 = 31 rows exist while the counter is 30: capacity is exceeded and an increment is lost.

With a row lock or atomic conditional update, T1 locks C101, changes 29 to 30, inserts S31 and commits. T2 then sees 30 and stops. Counter and row count finish at 30; insertion requires one affected update row.

The interview-safe ladder is:

• Read Uncommitted permits dirty reads.

• Read Committed blocks dirty reads, but does not by itself make a naive read-modify-write safe.

• Repeatable Read protects repeated row reads; phantom behaviour varies by DBMS implementation.

• Serializable requires an outcome equivalent to some serial order.

Snapshot isolation is not automatically serializable and may permit write skew.

7. DBMS interview follow-ups: rehearse the proof, not a script

Practise this schema-based chain:

• Candidate keys? (StudentID, CourseID) and (StudentEmail, CourseID); both closures reach all ten attributes, but no component alone does.

• Which dependency violates 2NF? StudentID -> StudentName depends on only part of the chosen composite key.

• Why lossless? Each split joins through a determinant that keys one output relation.

• 3NF but not BCNF? InstructorID -> CourseID has a prime right side, but a non-superkey determinant.

• Failed insert property? Atomicity rolls back the insert and counter.

• 29-seat anomaly? A lost update leaves 31 rows, counter 30, capacity 30.

Remember: a primary key is not the only candidate; foreign keys may repeat; 1NF may be redundant; 3NF differs from BCNF; lossless join differs from dependency preservation; ACID is not an isolation ladder; Read Committed does not prevent every lost update.

Define the term, apply it, then add one constraint or counterexample. The earlier Top 50 DBMS interview questions for freshers owns broad definition-and-follow-up rehearsal across DBMS; this worked schema owns the narrower proof chain from closures and decomposition to one seat-allocation race.

8. DBMS interview questions: the short version and next step

Keys come from minimal closures. Normalisation puts each fact under its determinant. Transactions preserve invariants as one unit. Isolation controls concurrent observation and interleaving.

In 60 seconds, explain why (StudentID, CourseID) is a key, why InstructorID -> CourseID separates 3NF from BCNF, and why C101: 29 of 30 seats needs safe concurrency. If stuck, redo the closure or schedule.

For structured revision across DBMS and the other core subjects, continue with Computer Science Fundamentals for Placements by Sanchit sir.