Top 25 - Part 1

1. What is a Database Management System (DBMS) and how does it differ from a Relational Database Management System (RDBMS)?

A Database Management System (DBMS) is software that handles the storage, retrieval, organization, and management of data.

An RDBMS is a particular type of DBMS based on the relational model, where data is organized into tables and relationships are represented using keys.

Feature	DBMS	RDBMS
Data organization	Depends on the database model	Tables with rows and columns
Relationships	May not support relational relationships	Relationships represented using keys
Integrity constraints	Depends on the DBMS	Supports constraints such as primary/foreign keys
SQL support	Depends on the DBMS	Commonly uses SQL

             DBMS
              │
       ┌──────┴──────┐
       │             │
   RDBMS          Other DBMS
       │
   ┌───┴────┐
   │ Tables │
   │  Keys  │
   │Relations│
   └─────────┘

Remember: An RDBMS is a type of DBMS that uses the relational model — tables, keys, relationships, and integrity constraints.

---

2. What are the ACID properties in a transaction?

ACID describes four important properties that help database transactions remain reliable.

• A — Atomicity: the transaction is all or nothing. If it fails, its changes are rolled back.
• C — Consistency: the transaction takes the database from one valid state to another, respecting defined constraints and rules.
• I — Isolation: concurrent transactions are controlled so that their intermediate changes do not improperly interfere with one another.
• D — Durability: once a transaction is committed, its changes survive failures such as a crash or power loss, subject to the DBMS’s durability guarantees.

Transaction
    │
    ├── Atomicity   → All or nothing
    ├── Consistency → Valid → Valid
    ├── Isolation   → Transactions don't improperly interfere
    └── Durability  → Committed data survives failure

A common interview trap is confusing Isolation with Integrity. They are different concepts.

Remember: ACID = Atomicity + Consistency + Isolation + Durability

---

3. What is Normalization and its primary purpose?

Normalization is the practice of organizing database tables to reduce unnecessary data redundancy and prevent data anomalies.

Suppose an address is stored repeatedly:

Orders
────────────────────
Order 1 → Ali → Kochi
Order 2 → Ali → Kochi
Order 3 → Ali → Kochi

If Ali’s address changes, every copy must be updated.

Instead, normalization separates the information:

Customers                    Orders
┌────┬─────┬────────┐       ┌──────┬─────────────┐
│ ID │Name │Address │       │Order │ Customer_ID │
├────┼─────┼────────┤       ├──────┼─────────────┤
│ 1  │ Ali │ Kochi  │◄──────│ 101  │      1      │
└────┴─────┴────────┘       └──────┴─────────────┘

Now the address is stored in one place.

Normalization helps prevent:

• Insert anomaly — difficulty inserting data because unrelated data is missing.
• Update anomaly — the same information must be changed in multiple places.
• Delete anomaly — deleting one record accidentally removes other useful information.

Common normal forms:

• 1NF — atomic values and no repeating groups.
• 2NF — removes partial dependencies.
• 3NF — removes transitive dependencies.

Remember: Normalization mainly reduces redundancy and prevents data anomalies.

---

4. What is the difference between DELETE, TRUNCATE, and DROP?

All three can remove data, but they operate at different levels.

Command	Typical classification	Purpose
DELETE	DML	Removes selected rows
TRUNCATE	Usually DDL	Removes all rows from a table
DROP	DDL	Removes the table itself

DELETE

DELETE FROM employees
WHERE department = 'HR';

Table
 ├── Row 1  ✓
 ├── Row 2  ✗ DELETE
 ├── Row 3  ✓
 └── Row 4  ✗ DELETE

• Can use WHERE.
• Removes rows individually/logically.
• Can generally be rolled back when used within a transaction, depending on the DBMS and transaction context.
• DELETE triggers may fire, depending on the DBMS.

TRUNCATE

TRUNCATE TABLE employees;

Table
 ├── Row 1 ─┐
 ├── Row 2  ├──> ALL ROWS REMOVED
 ├── Row 3  │
 └── Row 4 ─┘

• Removes all rows.
• Cannot use WHERE.
• Often faster than deleting rows individually.
• Rollback behavior is DBMS-specific.
• Trigger behavior is DBMS-specific, so don’t make a universal “triggers never fire” claim.

DROP

DROP TABLE employees;

┌─────────────────┐
│   employees     │
│ data + structure│
└────────┬────────┘
         ↓
      REMOVED

It removes the table itself, including its definition and data.

Easy memory trick: DELETE → remove rows TRUNCATE → empty the table DROP → remove the table

Also, avoid memorizing a universal speed ranking such as DROP > TRUNCATE > DELETE. Actual performance depends on the DBMS, table size, indexes, logging, constraints, and transaction context.

---

5. Explain the difference between Primary Key, Foreign Key, and Candidate Key.

• Candidate Key: a minimal set of columns that can uniquely identify a row. A table can have multiple candidate keys.
• Primary Key: the candidate key chosen as the main identifier for rows. A table has one primary-key constraint.
• Foreign Key: a column or set of columns that references a candidate/primary key in another table, creating a relationship and helping enforce referential integrity.

Example:

Students
┌────────────┬──────────────┐
│ student_id │ email        │
├────────────┼──────────────┤
│ 101        │ a@mail.com   │
│ 102        │ b@mail.com   │
└────────────┴──────────────┘

student_id → Candidate Key
email       → Candidate Key

Choose student_id
       ↓
Primary Key

Another table can reference it:

Enrollments
┌────────────┬─────────────┐
│ enroll_id  │ student_id  │
├────────────┼─────────────┤
│ 5001       │ 101         │
└────────────┴─────────────┘
                   │
                   └── Foreign Key

Remember: Candidate Key → possible unique identifier Primary Key → chosen identifier Foreign Key → reference to a key in another table

---

6. How do WHERE and HAVING clauses differ?

The simplest distinction is:

WHERE filters rows; HAVING filters groups.

Conceptually:

FROM
 ↓
WHERE        ← filter individual rows
 ↓
GROUP BY
 ↓
HAVING       ← filter groups
 ↓
SELECT

Example:

SELECT department, AVG(salary)
FROM employees
WHERE salary > 1000
GROUP BY department
HAVING AVG(salary) > 5000;

The process is conceptually:

All employees
      ↓
WHERE salary > 1000
      ↓
Filtered rows
      ↓
GROUP BY department
      ↓
Department groups
      ↓
HAVING AVG(salary) > 5000
      ↓
Final result

HAVING is especially useful when filtering based on aggregate results such as COUNT(), SUM(), or AVG().

Remember: WHERE → rows, HAVING → groups.

---

7. What is Indexing and why use it?

An index is a data structure that helps the database find rows more efficiently.

Think of it like the index of a book:

Without index:

Search "Database"
      ↓
Page 1
Page 2
Page 3
Page 4
...
      ↓
Found


With index:

Index
  ↓
"Database" → Page 250
  ↓
Go directly there

A common index structure is a B-tree/B+ tree, although databases can support other index types too.

Advantage

Indexes can make suitable read operations much faster:

Without index:
SELECT → scan many/all rows → find match

With index:
SELECT → index lookup → matching row(s)

Disadvantage

Indexes require additional storage and must be maintained when data changes.

INSERT
  │
  ├──> Update table
  ├──> Update index 1
  ├──> Update index 2
  └──> Update index 3

Therefore:

Indexes can improve read performance but add storage and write/maintenance overhead.

Index columns based on actual query patterns, such as columns frequently used in WHERE, JOIN, and ORDER BY operations.

---

8. What is the difference between INNER JOIN and LEFT JOIN?

A JOIN combines rows from two tables based on a related condition.

Consider:

Students

name	class_id
Aya	1
Ben	2
Cal	NULL

Classes

id	class_name
1	Math
2	Science
3	Art

INNER JOIN

Returns only rows where a match exists on both sides.

SELECT s.name, c.class_name
FROM students s
INNER JOIN classes c
    ON s.class_id = c.id;

Students                 Classes

Aya ── class 1 ────────> Math
Ben ── class 2 ────────> Science
Cal ── NULL

          ↓ INNER JOIN

Aya → Math
Ben → Science

Cal disappears because there is no matching class.

LEFT JOIN

Keeps all rows from the left table, whether or not a match exists.

SELECT s.name, c.class_name
FROM students s
LEFT JOIN classes c
    ON s.class_id = c.id;

Aya → Math
Ben → Science
Cal → NULL

Remember: INNER JOIN → only matching rows LEFT JOIN → everything from the left + matching data from the right

---

9. What is a View and a Materialized View?

A view is a named query that can be queried like a table.

Regular View

A regular view generally does not store a separate physical copy of the query result.

Application
     ↓
   View
     ↓
Underlying Tables
     ↓
Current data

When queried, the DBMS uses the view definition to produce the result.

Materialized View

A materialized view stores the query result physically.

Underlying Tables
       ↓
   Complex Query
       ↓
Materialized View
       ↓
    Fast Reads

Because the result is stored, it must be refreshed to reflect changes in the underlying data.

	Regular View	Materialized View
Stores result	No separate copy	Yes
Extra storage	Usually no	Yes
Freshness	Reflects underlying data when queried	Depends on refresh
Read performance	Depends on underlying query	Often faster

Materialized views are especially useful for expensive reports and aggregations where slightly stale data is acceptable.

Remember: View → virtual result; Materialized View → stored result.

---

10. What is a Deadlock?

A deadlock occurs when two or more transactions are waiting for resources locked by one another, creating a circular wait.

Example:

T1                         T2
│                          │
├── locks Row 1            ├── locks Row 2
│                          │
├── wants Row 2 ──────────>│
│       waits              │
│                          ├── wants Row 1
│                          │       waits
└──────────────┬───────────┘
               ↓
           DEADLOCK

Sequence:

1. T1 locks Row 1.
2. T2 locks Row 2.
3. T1 requests Row 2 → waits.
4. T2 requests Row 1 → waits.
5. Both are now waiting for each other.

Many database systems detect deadlocks automatically:

Deadlock detected
       ↓
Choose a victim
       ↓
Rollback victim
       ↓
Other transaction continues

How to reduce deadlocks

• Acquire locks in a consistent order.
• Keep transactions short.
• Avoid unnecessary work while holding locks.
• Use appropriate indexes to reduce unnecessary locking.
• Avoid waiting for user input while holding locks.
• Retry transactions that are aborted because of deadlocks.

Remember: Deadlock = circular waiting between transactions.