🗄️ DBMS Revision — MathWorks EDG Interview Prep

⏱ Time-boxed for spoken answers under 30 seconds each 🔴 = HIGHEST PRIORITY (answer these first if time is short)

🔴 1. Relational (SQL) vs Non-Relational (NoSQL)

One-line definition:

SQL databases store data in structured tables with fixed schemas; NoSQL databases store data in flexible formats (documents, key-value, graphs, columns) without a rigid schema.

Key Point / Mechanism:

• SQL: ACID-compliant, uses joins across normalized tables, best when data relationships are complex and consistency is critical (e.g., banking).
• NoSQL: built for scale, speed, and schema flexibility — trades strong consistency for high availability and horizontal scaling.
• The CAP theorem explains why: you can only fully guarantee 2 of Consistency, Availability, Partition Tolerance.

Types of NoSQL:

When to use which:

Common Follow-up:

"Can NoSQL databases be consistent?" Yes — many offer tunable consistency (e.g., Cassandra lets you choose consistency level per query), but they default to eventual consistency for performance.

🔴 2. ACID Properties

One-line definition:

ACID is a set of 4 properties that guarantee database transactions are processed reliably even in case of failures.

Each letter explained:

Common Follow-up:

"What is the opposite of ACID?" BASE — used in NoSQL: Basically Available, Soft state, Eventually consistent.

3. Normalization — 1NF / 2NF / 3NF

One-line definition:

Normalization is the process of organizing a database to reduce redundancy and improve data integrity by splitting data into related tables.

Each form in one sentence:

Common Follow-up:

"Why not always normalize fully?" Over-normalization leads to too many joins, hurting read performance. Sometimes denormalization is intentional for speed (e.g., data warehouses).

4. Indexing

One-line definition:

An index is a data structure (typically a B-Tree) that the database maintains separately to allow fast lookup of rows without scanning the entire table.

Key Point / Mechanism:

• Without an index: the DB does a full table scan — O(n) for every query.
• With an index: lookup becomes O(log n) — like finding a word in a book using the index at the back instead of reading every page.
• Most common type: B-Tree index (balanced tree, great for range queries). Also: Hash index (exact match only).

Tradeoff:

Indexes speed up reads but slow down writes (INSERT/UPDATE/DELETE must also update the index). Also consume extra disk space.

Common Follow-up:

"When would you NOT add an index?" On small tables (full scan is fine), on columns with low cardinality (e.g., a boolean column — index adds no value), or on write-heavy tables where index maintenance overhead is too costly.

🔴 5. Primary Key vs Foreign Key vs Unique Key

One-line definitions:

• Primary Key: Uniquely identifies each row in a table; cannot be NULL; only one per table.
• Foreign Key: A column that references the Primary Key of another table — enforces referential integrity between tables.
• Unique Key: Ensures all values in a column are distinct; can contain one NULL; a table can have multiple unique keys.

Key Point / Mechanism:

• Primary key = identity of a record. Foreign key = link between two tables (relationship enforcer).
• Foreign key constraint prevents orphan records: you cannot insert a row referencing a non-existent parent.
• Analogy: Employee table PK = EmployeeID. Department table PK = DeptID. Employee.DeptID is a FK — ensures every employee belongs to a real department.

Common Follow-up:

"What happens if you delete a parent row that a foreign key references?" Depends on the constraint: CASCADE deletes child rows too; RESTRICT blocks the delete; SET NULL sets the FK to null.

DBMS Rapid-Fire Self-Test

(Try to answer each in one breath)

1. What does ACID stand for? Explain Atomicity in one sentence.
2. When would you choose NoSQL over SQL?
3. What are the 4 types of NoSQL databases? Give one example each.
4. What is a primary key vs a unique key — one key difference?
5. What does a foreign key enforce?
6. What is 2NF and what dependency does it eliminate?
7. What is an index and what data structure does it typically use?
8. What is the tradeoff of adding too many indexes?
9. What does Durability in ACID mean?
10. What is eventual consistency (used in NoSQL)?

Answers

1. Atomicity, Consistency, Isolation, Durability. Atomicity means a transaction is all-or-nothing — if any step fails, the whole thing rolls back.
2. When you need massive scale, flexible schema, high write throughput, or unstructured data (e.g., social feeds, IoT).
3. Document (MongoDB), Key-Value (Redis), Column-family (Cassandra), Graph (Neo4j).
4. Primary key cannot be NULL; unique key allows one NULL. Also, only one PK per table but multiple unique keys.
5. Referential integrity — ensures the referenced row in the parent table actually exists.
6. 2NF eliminates partial dependency — every non-key column must depend on the WHOLE composite primary key.
7. A separate data structure (usually B-Tree) that enables fast row lookup without a full table scan.
8. Indexes slow down writes (INSERT/UPDATE/DELETE must update every index) and consume extra storage.
9. Once a transaction is committed, the data survives even a system crash immediately afterward.
10. The database will become consistent eventually — at the moment of a read, you might get slightly stale data, but all nodes will sync eventually.