Top 50 - Part 1

1. What is a Database Schema?

A schema is the logical blueprint or structure of a database. It defines the tables, columns, data types, constraints, and relationships between tables.

DATABASE
   │
   ├── Schema
   │    ├── Tables
   │    ├── Columns
   │    ├── Constraints
   │    └── Relationships
   │
   └── Data (actual rows)

A simple analogy:

Schema  = Building blueprint
Data    = Furniture inside the building

For example:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE defines the schema. When you run INSERT, you add the actual data.

Key point: Schema = structure; Instance = data at a particular moment.

---

2. What is Data Independence in a DBMS?

Data independence means you can change one level of the database without unnecessarily affecting the levels above it.

There are two types:

Physical Data Independence

You can change how data is physically stored without changing the logical schema or application.

Application
     │
Logical Schema
     │
     X   ← physical storage can change
     │
Disk / Files / Indexes

For example, adding an index or changing the file organization should not require application changes.

Logical Data Independence

You can change the logical schema without affecting external views or applications, as far as the system can preserve those interfaces.

Applications / Views
        │
        X   ← logical schema can change
        │
   Database Schema

Easy way to remember:

Physical independence → change storage
Logical independence  → change structure

The goal is insulation: changes at a lower level should have minimal impact on higher levels.

---

3. What does the ‘Atomicity’ property in ACID ensure?

Atomicity means a transaction is all-or-nothing.

Either every operation in the transaction succeeds, or the entire transaction is rolled back.

Transfer ₹500
     │
     ├── Debit ₹500 from A
     │
     └── Credit ₹500 to B
              │
          If this fails
              ↓
          ROLLBACK
              ↓
       A gets ₹500 back

For example, if ₹500 is transferred from Account A to Account B:

1. ₹500 is removed from A.
2. ₹500 is added to B.
3. If step 2 fails, step 1 is undone.

So you never end up with:

A: -₹500
B: +₹0

Key point: Atomicity = all operations happen, or none happen.

---

4. What is a Surrogate Key?

A surrogate key is an artificial/system-generated identifier used to uniquely identify a row.

It has no business meaning.

Example:

customers
+----+--------+------------------+
| ID | Name   | Email            |
+----+--------+------------------+
| 1  | Ali    | ali@gmail.com    |
| 2  | Sara   | sara@gmail.com   |
| 3  | John   | john@gmail.com   |
+----+--------+------------------+

Here, ID is a surrogate key.

It could be generated using:

customer_id INT IDENTITY(1,1)

or a sequence/UUID.

Compare:

Surrogate Key                  Natural Key
     │                              │
customer_id = 101              email = ali@gmail.com
     │                              │
No business meaning            Real-world meaning
Stable                          May change

If Ali changes his email, customer_id = 101 remains unchanged.

Key point: Surrogate key = artificial identifier with no business meaning.

---

5. What are OLTP and OLAP systems?

OLTP (Online Transaction Processing) handles many small, fast day-to-day transactions.

Examples:

• Placing an order
• Booking a ticket
• Updating a bank balance
• Processing a payment

OLAP (Online Analytical Processing) handles large, complex queries used for analysis and reporting.

Examples:

• Monthly sales analysis
• Yearly revenue trends
• Customer behavior analysis
• Business dashboards

OLTP
Users
  │
  ├── INSERT
  ├── UPDATE
  ├── DELETE
  └── Small SELECTs
        │
        ↓
   Operational DB


OLAP
Data Warehouse
      │
      ├── SUM
      ├── AVG
      ├── GROUP BY
      └── Large JOINs
            │
            ↓
       Reports / Analysis

	OLTP	OLAP
Purpose	Daily transactions	Analysis
Workload	Many small operations	Fewer, large queries
Operations	INSERT/UPDATE/DELETE	Aggregations/SELECT
Data	Usually current	Often historical
Example	Banking system	Sales dashboard

Easy memory trick:

OLTP → Run the business
OLAP → Analyze the business

---

6. What is the primary goal of Database Normalization?

Normalization organizes data into well-structured tables to reduce data redundancy and prevent data anomalies.

Without normalization:

Orders
+---------+----------+-------------+
| OrderID | Customer | Address     |
+---------+----------+-------------+
| 101     | Ali      | Kochi       |
| 102     | Ali      | Kochi       |
| 103     | Ali      | Kochi       |
+---------+----------+-------------+

Ali’s address is repeated multiple times.

After normalization:

Customers
+------------+------+---------+
| CustomerID | Name | Address |
+------------+------+---------+
| 1          | Ali  | Kochi   |
+------------+------+---------+

Orders
+---------+------------+
| OrderID | CustomerID |
+---------+------------+
| 101     | 1          |
| 102     | 1          |
| 103     | 1          |
+---------+------------+

Now the address is stored once.

Normalization helps prevent:

• Update anomaly — same data must be updated in many places.
• Insert anomaly — can’t insert data without unrelated data.
• Delete anomaly — deleting one record accidentally removes useful information.

Key point: Normalization = reduce redundancy + improve consistency.

---

7. What distinguishes 3NF (Third Normal Form) from 2NF (Second Normal Form)?

They remove different types of dependencies.

2NF → Removes Partial Dependency

A non-key attribute must depend on the entire composite key, not just part of it.

Example:

OrderDetails
+---------+-----------+-------------+
| OrderID | ProductID | ProductName |
+---------+-----------+-------------+

Suppose:

Primary Key = (OrderID, ProductID)

But:

ProductID → ProductName

ProductName depends only on ProductID, not the entire key.

That’s a partial dependency.

---

3NF → Removes Transitive Dependency

A non-key attribute should not depend on another non-key attribute.

EmpID → DeptID → DeptHead

Here:

EmpID → DeptID
DeptID → DeptHead

So DeptHead indirectly depends on EmpID.

Fix it by separating departments:

Employees
EmpID → DeptID

Departments
DeptID → DeptHead

Easy memory trick:

2NF → No partial dependency
      "Depends on the whole key"

3NF → No transitive dependency
      "Non-key shouldn't depend on non-key"

---

8. What is a ‘Dirty Read’ in transaction management?

A dirty read occurs when one transaction reads data that another transaction has changed but has not committed yet.

Example:

T1                         T2
│                          │
│ UPDATE balance = 500     │
│ (not committed)          │
│                          │
│                     READ balance
│                          │
│                     sees 500
│                          │
│ ROLLBACK                 │
│                          │
balance = 100              │

T2 read 500, but that value was never actually committed.

That’s a dirty read.

Uncommitted data
      ↓
   T2 reads it
      ↓
  T1 ROLLBACK
      ↓
T2 read invalid data

Isolation levels such as READ COMMITTED prevent dirty reads by ensuring transactions don’t read another transaction’s uncommitted changes.

Key point: Dirty read = reading uncommitted data.

---

9. What is the main difference between a Clustered and a Non-Clustered Index?

The main difference is where the actual table data is stored relative to the index.

Clustered Index

The table’s rows are stored in the order defined by the clustered index key.

Clustered Index
      │
      ↓
[10 | Ali]
[20 | Bob]
[30 | Cam]
[40 | Dan]

There can generally be only one clustered index per table, because the table’s rows can have only one physical/logical ordering.

Non-Clustered Index

The index is a separate structure containing keys and row locators pointing to the actual data.

Non-Clustered Index

[Ali] ───────→ Row 1
[Bob] ───────→ Row 2
[Cam] ───────→ Row 3

A table can have multiple non-clustered indexes.

	Clustered	Non-Clustered
Data rows	Organized with index key	Separate from index
Number per table	Usually 1	Multiple
Lookup	Can be very efficient	May require extra lookup

Important: The exact physical-storage behavior is database-engine dependent; “clustered” should not be interpreted as simply “the table is permanently sorted on disk.”

Easy memory trick:

Clustered     → data is organized around the index
Non-clustered → index points to the data

---

10. What is the purpose of a Two-Phase Locking (2PL) protocol?

Two-Phase Locking (2PL) is a concurrency-control technique used to ensure conflict serializability.

It has two phases:

Transaction
    │
    ↓
GROWING PHASE
Acquire locks
    │
    │
    ↓
SHRINKING PHASE
Release locks

Growing Phase

The transaction can:

Acquire locks ✓
Release locks ✗

Shrinking Phase

The transaction can:

Acquire locks ✗
Release locks ✓

Example:

T1
│
├── Lock A
├── Lock B
├── Read/Write
├── Unlock A
└── Unlock B

Once T1 starts releasing locks, it cannot acquire new ones under basic 2PL.

Important: Basic 2PL guarantees serializability but does not eliminate deadlocks.

For example:

T1 holds A → waits for B
T2 holds B → waits for A

A deadlock can still occur.

Strict 2PL, commonly used in database systems, keeps write locks until commit/rollback and provides stronger recovery properties.

Key point: 2PL = lock acquisition first, lock release later → serializable execution.

---

11. What does Referential Integrity enforce in a relational model?

Referential integrity ensures that a foreign key refers to a valid row in the referenced table, unless NULL is permitted.

Example:

Customers
+----+------+
| ID | Name |
+----+------+
| 1  | Ali  |
| 2  | Sara |
+----+------+
       ↑
       │
       │ Foreign Key
       │
Orders
+---------+------------+
| OrderID | CustomerID |
+---------+------------+
| 101     | 1          |
| 102     | 2          |
+---------+------------+

This would be invalid:

OrderID = 103
CustomerID = 999

because customer 999 doesn’t exist.

The database can enforce this using:

FOREIGN KEY (customer_id)
REFERENCES customers(id)

Depending on the foreign-key action, deleting or updating the parent row may be restricted, cascaded, or handled in another defined way.

Key point: Referential integrity prevents invalid references/orphan relationships.

---

12. What is the difference between Synchronous and Asynchronous Database Replication?

The key difference is when the primary considers a transaction committed relative to replica acknowledgment.

Synchronous Replication

The primary waits for the required replica acknowledgment before completing the commit.

Client
  │
  ↓
Primary
  │
  ├────────→ Replica
  │             │
  │         Write data
  │             │
  │←────── Acknowledge
  │
  ↓
Commit confirmed

This provides stronger durability/consistency guarantees, but adds network latency.

Asynchronous Replication

The primary doesn’t wait for the replica before acknowledging the commit.

Client
  │
  ↓
Primary ─────→ Client: COMMIT ✓
  │
  └──────────→ Replica
                │
            Replication later

It’s usually faster, but the replica may temporarily lag.

If the primary fails before replication completes, the latest acknowledged data may not yet exist on the replica.

	Synchronous	Asynchronous
Primary waits	Yes	No
Replica lag	Minimal/controlled	Possible
Write latency	Higher	Lower
Failure risk	Lower data-loss window	Possible recent-write loss

Key point: Synchronous = wait for replica; Asynchronous = don’t wait.

---

13. What is the difference between a Functional Dependency and a Trivial Functional Dependency?

A functional dependency X → Y means that knowing X uniquely determines Y.

Example:

EmpID → EmployeeName

If EmpID is 101, there can be only one corresponding employee name.

A dependency is trivial when every attribute on the right side is already part of the left side.

Example:

{EmpID, Name} → Name

Name is already contained in {EmpID, Name}, so this dependency is trivial.

Non-trivial:
EmpID → Name
       │
       └── Name isn't part of EmpID

Trivial:
{EmpID, Name} → Name
                │
                └── already included on left

Key point:

Trivial FD     → RHS ⊆ LHS
Non-trivial FD → RHS is not completely contained in LHS

---

14. What is a Trigger?

A trigger is database code that executes automatically when a specified database event occurs.

Common events include:

INSERT
UPDATE
DELETE

Example:

UPDATE employees
       │
       ↓
   TRIGGER fires
       │
       ↓
Write audit record

For example:

CREATE TRIGGER log_order_deletes
AFTER DELETE ON orders
FOR EACH ROW
INSERT INTO audit_log (order_id, deleted_at)
VALUES (OLD.id, NOW());

Whenever an order is deleted, the trigger automatically records the deletion.

Triggers are useful for:

• Auditing
• Maintaining derived information
• Enforcing certain database rules

But too much trigger logic can make application behavior difficult to understand and debug.

Key point: Trigger = automatically executed database code caused by an event.

---

15. What is a Synonym in a Database?

A synonym is an alternate name (alias) for a database object.

For example:

hr.employees
     ↑
     │
  SYNONYM
     │
     ↓
    emp

Then you can write:

CREATE SYNONYM emp FOR hr.employees;

SELECT * FROM emp;

Instead of:

SELECT * FROM hr.employees;

Synonyms can simplify object references and hide the underlying schema/owner name from users or applications.

Important: A synonym is an alias, not a copy of the table.

emp ───────────→ hr.employees
                   │
                   └── Actual object

If the underlying object is removed or renamed in a way that breaks the synonym, the synonym does not magically preserve the data.

Key point: Synonym = alternate name for an existing database object.