📌 What is a Database?

👉 A database is an organized collection of data that is stored electronically so it can be easily

accessed, managed, and updated.

💡 Example: A Student Database can store students’ IDs, names, emails, and grades in a
structured way.

Interview Answer (Simple):

“A database is an organized collection of data that can be stored, retrieved, and managed ef ciently.
It helps in storing data in a structured format like tables, making it easy to handle large amounts of
information.”

📌 What is DBMS (Database Management System)?

👉 DBMS is a software that allows users to create, store, manage, and retrieve data from a
database.
It acts as a bridge between the user and the database.

💡 Examples of DBMS: MySQL, Oracle, PostgreSQL, SQL Server, MongoDB.

Key Features of DBMS (for interview):

1. Data Storage & Retrieval → Helps store and fetch data ef ciently.

2. Data Security → Provides user authentication & access control.

3. Data Integrity → Ensures accuracy and consistency.

4. Concurrency Control → Allows multiple users to use data at the same time.

5. Backup & Recovery → Protects data from failures.

📌 What is an ER Diagram?

👉 ER Diagram (Entity-Relationship Diagram) is a visual representation of data that shows:

• Entities → Real-world objects (e.g., Student, Course)

• Attributes → Properties of entities (e.g., Name, Age, Email)

• Relationships → How entities are connected (e.g., Student enrolls in Course)

It is used in database design to clearly understand the structure before creating tables.
fi
fi
📌 Basic Components of ER Diagram
1. Entity → Represented by a rectangle
◦ Example: STUDENT, COURSE

2. Attribute → Represented by an oval

◦ Example: Name, Email, Age

3. Primary Key → Underlined attribute

◦ Example: StudentID

4. Relationship → Represented by a diamond

◦ Example: ENROLLS (between STUDENT and COURSE)

5. Cardinality → Shows how many instances participate

◦ One-to-One (1:1)

◦ One-to-Many (1:N)

◦ Many-to-Many (M:N)

📌 What is a Transaction?

👉 A Transaction in DBMS is a single logical unit of work that performs one or more database
operations (like INSERT, UPDATE, DELETE, SELECT).

• It must be executed completely or not at all.

• Example: Money transfer from one account to another →

1. Deduct money from Account A

2. Add money to Account B

→ Both steps together make one transaction.

📌 Properties of Transaction – ACID

To make sure transactions are reliable, DBMS follows ACID properties:

1. Atomicity → All or nothing

◦ If one part of the transaction fails, the whole transaction fails.
 ◦ Example: If money is deducted from A but not added to B → rollback.

2. Consistency → Database must remain correct before and after the transaction.
◦ Example: Total money in the system should remain the same.

3. Isolation → Transactions should not interfere with each other.

◦ Example: If two people transfer money at the same time, results should not clash.

4. Durability → Once a transaction is successful, it stays saved permanently (even after

system crash).

📌 States of a Transaction
A transaction goes through different states:

1. Active → Transaction is running.

2. Partially Committed → All operations are done but not yet saved.

3. Committed → Changes are permanently saved.

4. Failed → Some error occurred.

5. Aborted → Changes are rolled back (undo).

📌 What is an Index?

👉 An Index in DBMS is like an index in a book.

• It helps the database nd data faster without scanning the entire table.

• It is created on columns of a table.

💡 Example:
If you often search students by StudentID, creating an index on StudentID will make
queries faster.

-- Create index on StudentID

CREATE INDEX idx_student_id

ON Students(StudentID);

-- Drop index

DROP INDEX idx_student_id;

fi
📌 Types of Indexes
1. Primary Index
◦ Automatically created on the Primary Key.

◦ Unique and does not allow NULL.

2. Unique Index
◦ Ensures that the values in the column are unique.

◦ Example: Email column.

3. Clustered Index
◦ The physical order of rows in the table is the same as the index.

◦ Only one clustered index per table.

4. Non-Clustered Index
◦ Creates a separate structure to hold the index.

◦ Does not change the physical order of data.

◦ A table can have many non-clustered indexes.

1. Stored Procedures
👉 A saved block of SQL code that can be executed whenever needed.

• Improves reusability and performance.

💡 Example:

CREATE PROCEDURE GetHighSalaryEmployees()

AS
BEGIN
SELECT Name, Salary FROM Employees WHERE Salary > 50000;
END;

2. Functions
👉 Similar to procedures but must return a value.

• Scalar functions → return single value

 • Table functions → return a table

💡 Example:

CREATE FUNCTION GetTotalSalary()

RETURNS INT
AS
BEGIN
RETURN (SELECT SUM(Salary) FROM Employees);
END;

3. Triggers
👉 A trigger automatically runs when an INSERT, UPDATE, or DELETE happens.

💡 Example:

CREATE TRIGGER after_insert

ON Employees
AFTER INSERT
AS
BEGIN
PRINT 'New Employee Added!';
END;

🗂 DBMS (Database 🧮 RDBMS (Relational Database

🔸 Feature
Management System) Management System)
Data Storage File-based or hierarchical Table-based (rows and columns)
Data
Not supported Supported using foreign keys
Relationships
Enforced using constraints (Primary, Foreign
Data Integrity Not strictly enforced
keys)
Normalization Not enforced Enforced to reduce redundancy
Multi-user
Generally supports single-user Supports multi-user access
Access
Data
High Low (due to normalization)
Redundancy
ACID
May not fully support Fully supports
Properties
Examples MS Access, File system-based DBs MySQL, PostgreSQL, Oracle, SQL Server
✅ 1. Atomicity → “All or Nothing”

• A transaction should be fully done or not done at all.

• If something fails in the middle, everything is rolled back.

👉 Example: In money transfer, if ₹1000 is deducted from Account A but not added to
Account B, the whole transaction is canceled.

✅ 2. Consistency → “Valid State”

• After a transaction, the database must remain in a valid state.

• Data must follow rules, constraints, and integrity.

👉 Example: Balance should never go negative if the bank rule says so.

✅ 3. Isolation → “Transactions don’t disturb each other”

• Multiple transactions running at the same time should not affect each other’s result.
👉 Example: If two people book the last movie ticket at the same time, only one should
succeed.

✅ 4. Durability → “Once saved, always saved”

• Once a transaction is committed, data will be permanently stored, even if the system
crashes.
👉 Example: If money is transferred successfully, even a power cut won’t cancel it.

Normalization is the process of organizing data in a database to:

• Remove duplicate data (redundancy)

• Ensure data is stored logically and ef ciently

• Make the database easier to maintain

✅ 1. First Normal Form (1NF)

📌 Rule:

• Each column must contain atomic (indivisible) values

fi
 • No repeating groups or arrays in a row

🧾 Example (Before 1NF):

Studen
Courses
t
Math,
Ravi
Science

✅ After 1NF:

Student Course
Ravi Math
Ravi Science

✅ 2. Second Normal Form (2NF)

📌 Rule:

• Must be in 1NF

• No partial dependency (i.e., non-key column depends on part of the composite primary
key)

🧾 Example:
If a table has a composite key (StudentID, CourseID), then all other columns must depend on both
keys — not just one.

✅ Fix: Move partially dependent data to a new table.

✅ 3. Third Normal Form (3NF)

📌 Rule:

• Must be in 2NF

• No transitive dependency (i.e., non-key column depends on another non-key column)

🧾 Example (Bad 3NF):

StudentI Nam Departmen

HOD
D e t
Dr.
101 Ravi CS
Sharma
Here, HOD depends on Department, not on StudentID.

✅ Fix:

• Split into two tables:

◦ One for Student details

◦ One for Department-HOD info

An index in a database is like a table of contents in a book.

It helps the database nd data faster without scanning the entire table.

Constraint Description Example

1. NOT Column must have a value, can’t be left
NULL blank
name VARCHAR(50) NOT NULL
Value must be different from all other
2. UNIQUE email VARCHAR(100) UNIQUE
values
3. PRIMARY Uniquely identi es a row, cannot be
KEY NULL or duplicate
id INT PRIMARY KEY
4. FOREIGN student_id REFERENCES
Links to another table’s primary key
KEY students(id)
Restricts data to meet a speci c
5. CHECK age INT CHECK (age >= 18)
condition
status VARCHAR(10)
6. DEFAULT Sets a default value if none is provided
DEFAULT 'active'

📌 CAP Theorem (Very Important for Interviews)

👉 CAP theorem is about distributed databases.

It says:
A distributed system cannot guarantee all three — Consistency, Availability, and Partition
tolerance — at the same time. It can only provide two of them.

🔑 1. Consistency (C)

• Every read receives the latest written data (or an error).

• Same data across all nodes.

• Example: Banking system must always show the correct balance.

fi
fi
fi
🔑 2. Availability (A)

• Every request gets a response (even if it may not be the latest data).

• System is always up and responsive.

• Example: Social media apps → you always see something, even if slightly outdated.

🔑 3. Partition Tolerance (P)

• System keeps working even if there are network failures (some nodes cannot
communicate).

• Example: If a server in another region fails, the system still works.

📌 Trade-off

👉 In real-world distributed systems, Partition Tolerance (P) is mandatory (since networks can
fail).
So, systems must choose between Consistency (C) and Availability (A):

• CP System → Consistency + Partition tolerance (e.g., HBase, MongoDB in strong

consistency mode).

• AP System → Availability + Partition tolerance (e.g., Cassandra, DynamoDB).

• CA System → Only possible in single-node databases, not real distributed ones.