Top 10 High-Frequency Questions

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 lets you store, manage, retrieve, and manipulate data in an organized way.

An RDBMS is a specific type of DBMS that organizes data using the relational model.

DBMS
 │
 ├── Relational DBMS (RDBMS)
 │      ├── Tables
 │      ├── Rows & columns
 │      ├── Primary/foreign keys
 │      └── Relationships
 │
 └── Other database models
        ├── Hierarchical
        ├── Network
        └── Object-oriented

In an RDBMS, data is organized into tables and relationships between tables can be represented using keys.

Example:

Customers                    Orders
┌────┬────────┐              ┌────┬─────────────┐
│ ID │ Name   │              │ ID │ Customer_ID │
├────┼────────┤              ├────┼─────────────┤
│ 1  │ Ali    │◄─────────────│ 10 │     1       │
│ 2  │ Bob    │              │ 11 │     1       │
└────┴────────┘              └────┴─────────────┘
             Foreign Key → Customer ID

A foreign key can enforce referential integrity, preventing invalid references according to the database’s constraints.

Remember: Every RDBMS is a DBMS, but not every DBMS is an RDBMS.

---

2. What are the ACID properties in a transaction, and why are they critical for database integrity?

ACID describes four important properties of reliable database transactions.

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

Example: bank transfer

Account A                    Account B
   │                            │
   │ -₹100                      │
   └────────── Transaction ─────┘
                              +₹100

        Both succeed
             OR
        Both are rolled back

Without atomicity:

A: -₹100
B: +₹0
   ↓
Money appears to disappear

ACID properties help ensure that transactions remain reliable even when multiple users operate concurrently or failures occur.

---

3. What is Normalization, and what is the primary goal of applying it to a database design?

Normalization is the process of organizing data into related tables to reduce unnecessary data redundancy and prevent data anomalies.

Suppose the same customer address is repeated in many orders:

Orders

Order 1 → Ali → Kochi
Order 2 → Ali → Kochi
Order 3 → Ali → Kochi
Order 4 → Ali → Kochi

If Ali moves and only some rows are updated:

Order 1 → Ali → Kochi
Order 2 → Ali → Ernakulam
Order 3 → Ali → Kochi

The database now contains inconsistent information.

Normalization separates the data:

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

Common normal forms:

• 1NF — eliminates repeating groups and requires atomic values.
• 2NF — removes partial dependencies from relations where they can occur.
• 3NF — removes transitive dependencies.
• BCNF, 4NF, 5NF — handle more advanced dependency and decomposition problems.

For many practical OLTP systems, 3NF is a common design target, although the appropriate level depends on the application.

Trade-off: more normalization can mean more tables and JOINs. Sometimes controlled denormalization is used for performance.

---

4. What is the functional difference between the DELETE, TRUNCATE, and DROP commands in SQL?

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

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

DELETE
Table
 ├── Row 1  ← keep
 ├── Row 2  ← DELETE
 └── Row 3  ← keep


TRUNCATE
Table
 ├── Row 1 ─┐
 ├── Row 2 ─┼──> ALL ROWS REMOVED
 └── Row 3 ─┘


DROP
┌──────────────┐
│ Entire Table │ ───> removed
└──────────────┘

DELETE

DELETE FROM employees
WHERE department = 'HR';

Removes matching rows and can normally be rolled back when executed inside a transaction, depending on the DBMS and transaction context.

TRUNCATE

TRUNCATE TABLE employees;

Removes all rows and generally performs the operation more efficiently than deleting rows individually. It cannot use a WHERE clause.

DROP

DROP TABLE employees;

Removes the table definition along with its data and associated table objects, subject to the DBMS’s rules.

Important: rollback behavior for TRUNCATE is DBMS-specific. It is not universally correct to say “TRUNCATE cannot be rolled back.” Check the particular database system.

Easy memory trick:

DELETE → remove rows TRUNCATE → empty the table DROP → remove the table

---

5. What is the difference between a Primary Key, a Foreign Key, and a Candidate Key?

These keys serve different purposes.

• Primary Key — the chosen candidate key used to uniquely identify rows in a table.
• Candidate Key — a minimal set of attributes that can uniquely identify a row.
• Foreign Key — an attribute or set of attributes that references a key in another table (commonly a primary key), enforcing referential integrity.

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

employee_id → Candidate Key
email       → Candidate Key

Choose employee_id
       ↓
Primary Key

Another table can reference it:

Orders
┌─────────┬─────────────┐
│ order_id│ employee_id │
├─────────┼─────────────┤
│ 5001    │ 101         │
└─────────┴─────────────┘
                │
                └── Foreign Key

Key	Uniquely identifies row?	NULL?	Main purpose
Primary Key	Yes	No	Identifies rows
Candidate Key	Yes	No	Possible primary key
Foreign Key	Not necessarily	Often yes*	Links tables

• Whether a foreign key may be NULL depends on the constraint definition.

Important: A table can have multiple candidate keys but only one primary-key constraint.

---

6. How do the WHERE and HAVING clauses differ in their application during a query?

The basic difference is:

WHERE filters rows; HAVING filters groups.

Conceptually:

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

Example:

SELECT department, COUNT(*)
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING COUNT(*) > 2;

The database conceptually does:

All employees
     ↓
WHERE status = 'active'
     ↓
Active employees
     ↓
GROUP BY department
     ↓
Department groups
     ↓
HAVING COUNT(*) > 2
     ↓
Final result

WHERE normally cannot directly use aggregate results such as COUNT(*) because aggregation has not yet occurred.

HAVING is designed to filter groups based on aggregate results.

---

7. What is the role of Indexing in a database, and how does it affect read and write performance?

An index is a data structure that helps the database locate rows more efficiently without scanning the entire table.

A useful analogy is a book’s index:

Without index:

Search "Database"
      ↓
Read page 1
Read page 2
Read page 3
...
Read until found


With index:

Index
  ↓
"Database" → Page 250
  ↓
Go directly to page 250

A common index structure is a B-tree/B+ tree, although databases also support other index types such as hash indexes.

Read performance

Indexes can speed up operations involving:

• WHERE
• JOIN
• ORDER BY
• Certain GROUP BY operations

Write performance

Indexes have a cost.

INSERT row
   │
   ├──> Update table
   │
   ├──> Update Index 1
   ├──> Update Index 2
   └──> Update Index 3

Therefore:

More indexes can improve reads but increase storage usage and write/maintenance overhead.

Don’t blindly index every column. Index design should depend on the workload, query patterns, cardinality, and the database optimizer.

---

8. What is a ‘JOIN’ in SQL, and what is the difference between an INNER JOIN and a LEFT JOIN?

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

Consider:

Employees

name	department_id
Ali	1
Bob	2
Cam	NULL

Departments

id	dept_name
1	Sales
2	IT
3	HR

INNER JOIN

Returns only rows where the JOIN condition matches.

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.id;

Employees             Departments

Ali ───── department 1 ─────> Sales
Bob ───── department 2 ─────> IT
Cam ───── NULL

INNER JOIN result:
Ali → Sales
Bob → IT

Cam is excluded because there is no matching department.

LEFT JOIN

Keeps every row from the left table, even when there is no match.

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.id;

Result:

name	dept_name
Ali	Sales
Bob	IT
Cam	NULL

LEFT JOIN

Left table
    │
    ├── matching rows → data from both tables
    │
    └── no match → left row kept + NULLs from right

Remember: INNER JOIN → only matches LEFT JOIN → everything from the left + matches from the right

---

9. What is a Database View, and what is the primary difference between a regular 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 the query result as its own physical copy of the data.

Application
     ↓
   View
     ↓
Underlying tables
     ↓
Current data

When the view is queried, the DBMS uses its underlying query to produce the result.

Materialized View

A materialized view stores the query result physically.

Underlying tables
       ↓
    Complex query
       ↓
Materialized View
       ↓
     Fast read

The stored result must be refreshed to reflect changes in the underlying data.

	Regular View	Materialized View
Stores result	No	Yes
Extra storage	Usually no	Yes
Data freshness	Based on current underlying data	Depends on refresh
Query performance	Query is executed/optimized when used	Often faster for expensive queries

Materialized views are useful for expensive aggregations and reporting workloads.

---

10. What is a Deadlock in the context of database transactions, and how can it be avoided?

A deadlock occurs when transactions are waiting for locks/resources held by one another, creating a circular wait.

Example:

Transaction A                  Transaction B

Locks Row 1                    Locks Row 2
     │                              │
     │ wants Row 2                  │ wants Row 1
     └──────────────┐  ┌────────────┘
                    ▼  ▼
                 DEADLOCK

More explicitly:

A holds Row 1
A waits for Row 2
        ↑
        │
B holds Row 2
B waits for Row 1

Neither transaction can continue.

How databases handle it

Many DBMSs detect deadlocks automatically.

Deadlock detected
       ↓
Choose a victim
       ↓
Rollback victim transaction
       ↓
Other transaction continues
       ↓
Victim may be retried

How to reduce deadlocks

• Acquire locks in a consistent order.
• Keep transactions short.
• Avoid unnecessary work while holding locks.
• Use appropriate indexes so queries don’t lock more rows than necessary.
• Avoid waiting for user input while a transaction is holding locks.
• Implement retry logic for transactions that are aborted because of deadlocks.

Remember: A deadlock is a circular waiting problem, not simply a slow query.