What is the difference between a DBMS and a traditional file

1.
system?
FILE SYSTEM VS DBMS

Basis File system DBMS

• File system stores raw files; DBMS
manages structured data with metadata. Access Manual programs Query language

Integrity App-level Constraints

• DBMS provides querying, security,
concurrency control and recovery. Concurrency Limited Built-in control

• Avoids uncontrolled redundancy and Recovery Manual backups Logs + recovery

inconsistent copies of the same data.

• Best interview angle: DBMS = data + APP A APP B

files files
rules + access control + reliability.

DBMS
shared data + rules

Gate Smashers DBMS Interview Preparation

2. What is data independence, and why is it important in database systems?

External / View level

• Data independence means changing one Different user views
level without breaking other levels.

• Physical independence: storage/index Logical level

Tables, relationships, constraints
changes do not affect logical schema.

• Logical independence: logical schema

Physical level
changes minimally affect user views. Files, pages, indexes

• It reduces application rewrites and

makes DB systems easier to evolve.

Gate Smashers DBMS Interview Preparation

3. How is a database schema different from a database instance?

SCHEMA
• Schema is the database Student(id, name, email)
design/structure: tables, columns and Course(id, title)
constraints.

• Instance is the actual data stored at a INSTANCE

particular moment. (1, Asha, a@[Link])
(2, Ravi, r@[Link])

• Schema changes rarely; instance

changes whenever rows are DIFFERENCE
inserted/updated.
Point Schema Instance
Meaning Structure Current data
• Analogy: schema is blueprint, instance
Changes Rarely Frequently
is the current building state.
Example Columns Rows

Gate Smashers DBMS Interview Preparation

4. What is the difference between a primary key and a candidate key?

STUDENT TABLE
roll_no email phone
• Candidate keys are all minimal attributes
101 a@[Link] 9991
that can uniquely identify a row.
102 b@[Link] 9992

• Primary key is the candidate key chosen 103 c@[Link] 9993

as the main row identifier.
Candidate keys
• A table can have many candidate keys {roll_no}, {email}, {phone}
but only one primary key.

• Primary key should be stable, unique, Chosen primary key

non-null and preferably small. roll_no

ALL PKs ARE CANDIDATE KEYS

Gate Smashers DBMS Interview Preparation

5. What is referential integrity, and how does a DBMS enforce it?

• Referential integrity keeps child-table STUDENTS ENROLLMENTS

foreign keys pointing to valid parent rows. id PK student_id FK
name course_id FK
• A child row cannot reference a parent
record that does not exist. PARENT ACTION

Action Meaning
• DBMS enforces it through foreign key
constraints. RESTRICT Block delete

CASCADE Delete child rows

• On parent delete/update, actions may be
RESTRICT, CASCADE, SET NULL or SET SET NULL Keep child, clear FK
DEFAULT.
Without valid parent
foreign key insert fails

Gate Smashers DBMS Interview Preparation

 You are designing a college database. How would you model students, courses, and
6. enrollments?

ENROLLMENT
STUDENT
student_id FK
student_id PK
course_id FK
• Identify entities first: Student and Course name
semester
email
are independent entities. grade

• Enrollment represents the relationship COURSE

between a student and a course. course_id PK
title
credits
• Use Enrollment(student_id, course_id) as
a bridge table with foreign keys.
M:N becomes two 1:N relationships

• Keep relationship attributes like grade, BRIDGE KEEPS RELATIONSHIP DATA

semester and enrollment_date there. Attribute Placed in

grade Enrollment

semester Enrollment

credits Course

Gate Smashers DBMS Interview Preparation

 Why is a many-to-many relationship usually resolved using an associative
7.
entity?

BEFORE: DIRECT M:N

• Relational tables cannot directly store
a clean M:N relationship without STUDENT COURSE

repetition.
AFTER: ASSOCIATIVE ENTITY
• An associative entity converts M:N into
two manageable 1:N relationships. STUDENT ENROLLMENT COURSE

• It prevents repeated groups and WHY IT HELPS

supports extra relationship attributes.
Benefit Impact

• It also makes constraints, indexing and No repetition Cleaner rows

querying cleaner. FKs enforced Better integrity

Attrs stored grade, semester

Gate Smashers DBMS Interview Preparation

8. What are insertion, update, and deletion anomalies?

BAD TABLE: STUDENTCOURSE

Student Course Teacher

• Anomalies happen when a poor table
Asha DBMS Rao
design mixes multiple facts in one table.
Ravi DBMS Rao

• Insertion anomaly: cannot add one fact Neha CN Mehta

without unrelated data.
Update anomaly
• Update anomaly: same fact appears in Teacher name repeated
many rows and must be changed
everywhere.
Deletion anomaly
Remove last CN student → lose CN teacher
• Deletion anomaly: deleting one row
accidentally removes another important
fact. Insertion anomaly
Cannot add new course without student

Gate Smashers DBMS Interview Preparation

 A table stores customer details and repeated order information. How would you decide
9. whether normalization is required?

BAD TABLE
customer_name, phone, order_id, item, qty
• Look for repeated groups, duplicate
customer data and mixed facts in one row.

• Check update/delete/insert anomalies and CUSTOMER ORDER ORDER ITEM

functional dependencies. customer_id PK order_id PK order_id FK
name, phone customer_id FK item_id, qty

• Split stable entities: Customer, Order and

OrderItem. DECISION CHECKS

Check Meaning
• Normalize when correctness and
maintainability matter more than one-table Repeating values Normalize
convenience.
Many updates Normalize

Pure reporting Maybe denormalize

Gate Smashers DBMS Interview Preparation

10. What is the difference between Third Normal Form and BCNF?

3NF VS BCNF
• 3NF removes transitive dependency but
allows a special case when RHS is prime. Basis 3NF BCNF

Rule X super key OR Y prime X must be super key

• BCNF is stricter: for every functional
dependency X → Y, X must be a super key. Strength Less strict More strict

Always? BCNF ⇒ 3NF 3NF ⇏ BCNF

• Every BCNF table is in 3NF, but every
3NF table may not be in BCNF. Trade-off Dependency friendly Less redundancy

• BCNF reduces redundancy further but Interview line

can sacrifice dependency preservation. BCNF = stronger version of 3NF where every determinant
is a candidate/super key.

Gate Smashers DBMS Interview Preparation

11. When can denormalization be a better design choice than normalization?

NORMALIZED DENORMALIZED
Less redundancy Faster reads
• Denormalization adds controlled More joins Repeated data
redundancy to improve read performance. Easy updates Consistency risk

• Useful for dashboards, reports,

materialized summaries and read-heavy USE IT WHEN
systems.
Situation Reason

• It reduces joins but increases storage and Dashboard Precomputed summary

consistency-management cost.
Heavy reads Avoid repeated joins

• Never denormalize blindly; do it for Low writes Less update pain

measured access patterns.

Trade correctness cost for read speed carefully

Gate Smashers DBMS Interview Preparation

12. What do the ACID properties guarantee in a database transaction?

Atomicity Consistency
Atomicity: all operations complete or All or nothing Valid rules
none do.

Consistency: transaction moves

database from one valid state to another. Isolation Durability
Safe concurrency Survives crash
Isolation: concurrent transactions
should not corrupt each other.

Durability: committed changes survive

crashes. Bank transfer
Debit A + Credit B
commit together

Gate Smashers DBMS Interview Preparation

 Two users try to update the same account balance at the same time. What
13. concurrency problem can occur?

LOST UPDATE SCHEDULE

Step T1 T2
• This can cause the lost update
1 Read balance = 100
problem.
2 Read balance = 100
• Both transactions read the old 3 Write 90
balance, then overwrite each other’s 4 Write 80
result.
Final Expected 70 Actual 80

• Final balance becomes incorrect

because one update is effectively lost. Problem
T2 overwrites T1 update
• DBMS prevents it using isolation,
locks, MVCC checks or atomic update
logic. Concurrency control is needed

Gate Smashers DBMS Interview Preparation

14. What is the difference between a serial schedule and a serializable schedule?

• Serial schedule executes transactions one SERIAL SERIALIZABLE

after another with no interleaving. T1 completes T1 and T2 interleave
then T2 starts but result is safe
• Serializable schedule may interleave
operations but final result equals some
serial order. EXECUTION IDEA

• Serializable schedules improve Type Interleaving Correctness

concurrency while preserving correctness.
Serial No Safe
• Interview key: not every concurrent
schedule is serializable. Serializable Allowed Same as serial

Non-serializable Allowed May be wrong

Gate Smashers DBMS Interview Preparation

15. What is a dirty read, and which isolation level allows it?

DIRTY READ TIMELINE

Step T1 T2
• Dirty read means reading data written by
another transaction before it commits. 1 Update balance = 500

2 Read balance = 500

• If the writer later rolls back, the reader
has used a value that never existed
3 ROLLBACK
permanently.
Issue T2 read uncommitted data
• READ UNCOMMITTED can allow dirty
reads.
Allowed by
• READ COMMITTED prevents dirty reads READ UNCOMMITTED
in normal SQL isolation behavior.
Prevented by
READ COMMITTED or higher

Gate Smashers DBMS Interview Preparation

 A transaction reads the same row twice but gets different values. Which anomaly
16.
has occurred?

NON-REPEATABLE READ

• This is a non-repeatable read. Step T1 T2

1 Read salary = 50k
• T1 reads a row, T2 updates and commits it,
then T1 reads again and gets a new value. 2 Update salary = 60k
3 COMMIT
• It is different from phantom read, where 4 Read salary = 60k
new matching rows appear/disappear.

• Repeatable Read or stronger isolation is DO NOT CONFUSE WITH

generally used to prevent it.
Anomaly What changes
Dirty read Uncommitted value
Non-repeatable Same row value
Phantom Number of rows

Gate Smashers DBMS Interview Preparation

17. What is a deadlock in DBMS, and how can the database detect it?

• Deadlock happens when transactions wait

T1 T2
for each other in a cycle. holds A holds B
waits B waits A
• Example: T1 holds A and waits for B; T2
holds B and waits for A.
WAIT-FOR GRAPH CYCLE
• DBMS detects it using a wait-for graph; a
cycle means deadlock.
Detect cycle
• Recovery usually aborts one victim → choose victim
transaction and releases its locks. → rollback victim
→ release locks

Gate Smashers DBMS Interview Preparation

18. How does Two-Phase Locking help maintain serializability?

TWO PHASES

• 2PL controls when a transaction can

acquire and release locks. GROWING PHASE SHRINKING PHASE
Acquire locks Release locks
No releases No new locks
• Growing phase: transaction can acquire
locks but cannot release any.
VARIANTS
• Shrinking phase: transaction can release
locks but cannot acquire new ones. Variant Idea

Basic 2PL Two phases only

• This discipline guarantees conflict
serializability, though it may still deadlock. Strict 2PL Hold write locks till commit

Conservative Get locks before start

Gate Smashers DBMS Interview Preparation

 How does MVCC improve concurrency compared with strict lock-based
19.
execution?
LOCKING VS MVCC

Point Strict locks MVCC

• MVCC keeps multiple versions of rows Read/write Often blocks Snapshot read
instead of forcing every read to wait.
Writers Lock rows Create versions
• Readers can read a consistent snapshot
Best for Simple control High concurrency
while writers create new versions.
Cost Waiting Version cleanup
• This reduces read-write blocking and
improves throughput for read-heavy
systems. Row version chain
V1 → V2 → V3
• Old versions are eventually cleaned after
no transaction needs them. READER SEES SNAPSHOT, WRITER CONTINUES

Gate Smashers DBMS Interview Preparation

 A table has millions of records and searches are becoming slow. What DBMS
20.
feature would you consider first?

Full table scan Index lookup

• First consider an index on columns checks millions of rows jumps to matching rows
used in WHERE, JOIN, ORDER BY or
GROUP BY.
INDEX DECISION
• The index lets DBMS avoid scanning
every row for selective searches. Column use Index value

WHERE filter High

• Choose index based on query pattern,
selectivity and sort requirements. JOIN key High

ORDER BY Useful
• Still verify using the execution plan; not
every slow query is fixed by an index. Low selectivity Maybe low

Always confirm with EXPLAIN

Gate Smashers DBMS Interview Preparation

21. Why are B+ trees commonly used for database indexes?

Root
keys
• B+ trees are balanced, so lookup time stays
predictable as data grows.

• Internal nodes guide search; actual

Internal Internal
records/row pointers stay at leaf level. 10 | 20 30 | 40

• Leaf nodes are linked, making range queries

and ordered scans efficient.

• They work well for equality search, range

1-9 10-19 20-29 30-40
search, sorting and disk-page access.

Linked leaves support range scans

Gate Smashers DBMS Interview Preparation

22. What is the difference between clustered and non-clustered indexing?

CLUSTERED VS NON-CLUSTERED

Basis Clustered Non-clustered

• Clustered index defines the physical/logical
order of table data. Data order Rows ordered by index Separate index

Count Usually one Can be many

• A table usually has only one clustered
organization because rows have one order. Leaf level Actual data Row pointer/key

• Non-clustered index is a separate structure Best for Range scans Lookups

that points to data rows.

• Clustered helps range reads; non-clustered Clustered: index order = data order
supports multiple alternate lookups.
Non-clustered: index points to row

Gate Smashers DBMS Interview Preparation

23. Why can adding too many indexes reduce database performance?

READS WRITES
faster filtering more index updates
• Every INSERT, UPDATE and DELETE may
less scanning slower commits
need to update multiple index structures.
INDEX COST
• Indexes consume extra disk and memory.

• Too many indexes increase maintenance cost Cost Why it matters

and can slow write-heavy workloads. Storage Extra pages

• Good answer: add indexes for proven access Insert Add index entry
patterns, not for every column.
Update Possibly move entries

Delete Remove entries

Index only what queries actually need

Gate Smashers DBMS Interview Preparation

 A server crashes while a transaction is partially completed. How does log-
24.
based recovery restore consistency?
CRASH RECOVERY DECISION

Txn state Recovery action

• DBMS uses transaction logs to know Committed REDO if needed
what happened before the crash.
Uncommitted UNDO
• Committed changes are redone if they
Checkpointed Less log scan
had not reached the data page.
Partial write Use log record
• Uncommitted changes are undone so
partial work does not remain.
Log records
• Recovery ensures atomicity and Before image + After image
durability after failure.

After crash
REDO committed
UNDO uncommitted

Gate Smashers DBMS Interview Preparation

 What is Write-Ahead Logging, and why must the log be written before the data
25. page?

WAL ORDER

1. Update happens 2. Log forced

• Write-Ahead Logging means log records are in memory to disk
safely written before data pages are flushed.

• If the system crashes, the log becomes the 3. Data page

source for redo and undo recovery. flushed later

• Without WAL, a data page may contain

changes that cannot be explained or WHY LOG FIRST?
reversed.
Guarantee Meaning

• Rule: log first, data later; commit only after Atomicity Undo partial work
required log records are durable. Durability Redo committed work

Consistency Recover known state

Gate Smashers DBMS Interview Preparation