DBMS(Technical Based Learning)

1. Keys
🔹 Primary Key
Definition: Attribute (or set of attributes) that uniquely identifies each record in a table.
Cannot have NULL values, only one primary key per table.
Example:

CREATE TABLE Student (

RollNo INT PRIMARY KEY,

Name VARCHAR(50),

Age INT

);

👉 Here, RollNo is the primary key, ensures no two students have the same RollNo.
Interview Check: Can a primary key be NULL? (👉 No).

🔹 Candidate Key
Definition: All possible attributes that can uniquely identify a record. Out of these, one is
chosen as primary key.
Example:

CREATE TABLE Student (

RollNo INT,

Email VARCHAR(50),

Name VARCHAR(50),

PRIMARY KEY (RollNo)

);

👉 Both RollNo and Email can uniquely identify students → so they are candidate keys.
🔹 Alternate Key
Definition: Candidate keys that are not chosen as the primary key.
 Example:
In the above table, RollNo is primary key, so Email becomes an alternate key.

🔹 Foreign Key
Definition: Attribute in one table that refers to the primary key of another table.
Maintains referential integrity.
Example:

CREATE TABLE Department (

DeptID INT PRIMARY KEY,

DeptName VARCHAR(50)

);

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,

EmpName VARCHAR(50),

DeptID INT,

FOREIGN KEY (DeptID) REFERENCES Department(DeptID)

);

👉 DeptID in Employee is a foreign key, referring to DeptID in Department.

Interview Check: What happens if we delete a row from Department?
Answer: Depends on constraints (ON DELETE CASCADE deletes related employees,
ON DELETE SET NULL sets DeptID null).

🔹 Composite Key
Definition: Key formed by two or more attributes to uniquely identify a record.
Example:

CREATE TABLE Enrollment (

StudentID INT,

CourseID INT,

PRIMARY KEY (StudentID, CourseID)

);
👉 Neither StudentID nor CourseID alone is unique, but together they form a composite key.
🔹 Super Key
Definition: Any set of attributes that can uniquely identify a record. Primary key is a
minimal super key.
Example:
If {RollNo} is primary key, then {RollNo, Name} is also a super key (though not minimal).

⚡ Interview Expectations
Most Asked: Difference between Primary Key vs Unique Key.
Primary Key: One per table, cannot be NULL.
Unique Key: Multiple per table, can have one NULL.
Difference between Primary Key vs Candidate Key vs Alternate Key.
Example of Composite Key.
Role of Foreign Key in maintaining referential integrity.

2. Normalization
🔹 Why Normalization is Needed?
To remove redundancy (repeated data).
To avoid anomalies (insertion, update, deletion problems).
To keep data consistent.

👉 Example of redundancy:
If we store Employee and Department info in one table, every employee of the same
department will repeat the department name multiple times.

🔹 Types of Anomalies
Insertion anomaly → Can’t insert data without unnecessary details.
Example: Can’t insert a new Department unless at least one Employee exists.
Update anomaly → Updating data in one place but forgetting in others.
Example: Department name changed, but old name still exists in some rows.
Deletion anomaly → Deleting a record removes useful data.
Example: Deleting the last Employee of a Department deletes the Department info too.

🔹 Normal Forms
1. First Normal Form (1NF)
Rule: No repeating groups, no multi-valued attributes.
Example : ❌
Student(RollNo, Name, Subjects)
Here Subjects = {Math, Physics} (multi-valued).
✅
Fixed :
 Student(RollNo, Name, Subject)
Each subject in a new row.
2. Second Normal Form (2NF)
Rule: Must be in 1NF + no partial dependency (no non-key attribute depends only on
part of a composite key).
Example : ❌
Enrollment(StudentID, CourseID, StudentName)
Composite key = (StudentID, CourseID).
StudentName depends only on StudentID → partial dependency.
✅
Fixed :
Student(StudentID, StudentName)
Enrollment(StudentID, CourseID)
3. Third Normal Form (3NF)
Rule: Must be in 2NF + no transitive dependency (non-key attribute depends on
another non-key attribute).
Example : ❌
Employee(EmpID, EmpName, DeptID, DeptName)
DeptName depends on DeptID (non-key) → transitive dependency.
✅
Fixed :
Department(DeptID, DeptName)
Employee(EmpID, EmpName, DeptID)
4. Boyce-Codd Normal Form (BCNF)
Stricter than 3NF.
Rule: For every functional dependency (X → Y), X should be a super key.
Example : ❌
Teacher(TeacherID, Subject, Dept)
If one subject is taught by only one teacher, Subject → TeacherID creates violation.
✅
Fixed : Split into two tables.

⚡ Interview Expectations
👉
“What is normalization? Why do we need it?” ( redundancy, anomalies).
“Explain anomalies with examples.”
“Difference between 1NF, 2NF, 3NF.”
Sometimes: “What’s the difference between 3NF and BCNF?”

3. SQL Queries
🔹 Find 2nd Highest Salary
👉 Most asked!
Method 1 (Subquery with MAX):

SELECT MAX(salary)

FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

Method 2 (Using LIMIT / OFFSET – MySQL style):

SELECT DISTINCT salary

FROM Employee

ORDER BY salary DESC

LIMIT 1 OFFSET 1;

⚡ Interview Tip: Always mention both methods (subquery + limit).

🔹 Select Duplicate Records
SELECT Name, COUNT(*)

FROM Employee

GROUP BY Name

HAVING COUNT(*) > 1;

👉 Groups by Name → shows only duplicates.

🔹 Select Employees with Same Department
SELECT DeptID, EmpName

FROM Employee e1

WHERE EXISTS (

SELECT 1

FROM Employee e2

WHERE [Link] = [Link]

AND [Link] <> [Link]

);

👉 Or simpler way (if they just want dept grouping):

SELECT DeptID, GROUP_CONCAT(EmpName)

FROM Employee

GROUP BY DeptID
HAVING COUNT(*) > 1;

🔹 Joins
INNER JOIN → Returns only matching rows.

SELECT [Link], [Link]

FROM Employee e

INNER JOIN Department d ON [Link] = [Link];

LEFT JOIN → All rows from left table + matching from right.

SELECT [Link], [Link]

FROM Employee e

LEFT JOIN Department d ON [Link] = [Link];

RIGHT JOIN → All rows from right table + matching from left.

SELECT [Link], [Link]

FROM Employee e

RIGHT JOIN Department d ON [Link] = [Link];

FULL OUTER JOIN → All rows when there is a match in either table (not supported in MySQL,
but exists in Oracle/SQL Server).

SELECT [Link], [Link]

FROM Employee e

FULL OUTER JOIN Department d ON [Link] = [Link];

⚡ Interview Expectations
“Write query to find 2nd/3rd highest salary.”
“How do you find duplicates?”
“What’s the difference between INNER JOIN and OUTER JOIN?”
Sometimes: “Can you get 2nd highest salary without using MAX?” (👉 Use LIMIT/OFFSET).
4. Transactions & ACID Properties
🔹 What is a Transaction?
A transaction is a single logical unit of work in a database.
 Example: Transferring money from Account A → B:
Debit from A.
Credit to B.
Both must succeed, or both must fail.

🔹 ACID Properties
1. Atomicity – All or Nothing
A transaction is either fully completed or fully rolled back.
Example: In money transfer, if debit succeeds but credit fails → rollback debit too.
2. Consistency – Valid State
Database moves from one consistent state to another.
Example: Total money before and after transfer must remain the same.
3. Isolation – Transactions Don’t Interfere
Multiple transactions happening together should not affect each other’s result.
Example: Two people booking the last movie ticket at the same time → isolation
ensures only one succeeds.
4. Durability – Permanent Results
Once a transaction is committed, the changes are permanent, even if the system
crashes.
Example: Once money transfer is done, the record remains safe even after power loss.

🔹 Why ACID is Important?

Ensures data integrity.
Prevents corruption, anomalies, and conflicts in multi-user environments.
Makes DBMS reliable.

⚡ Interview Expectations
“Explain ACID with real-life example.”
“Why is isolation important in DBMS?”
“What happens if atomicity is not maintained?”

5. Indexing
🔹 What is an Index?
An index is like a book’s index – it helps find data faster without scanning the whole table.
Internally, indexes are usually implemented using B-Trees or Hash tables.

👉 Without index → DBMS scans every row (Full Table Scan).

👉 With index → DBMS jumps directly to the required rows (Faster Lookup).
🔹 Clustered vs Non-Clustered Index
1. Clustered Index
 The actual table data is stored in sorted order of the index.
Only one clustered index per table (since data can only be sorted one way).
Example: Primary Key by default creates a clustered index.
2. CREATE CLUSTERED INDEX idx_empid
3. ON Employee(EmpID);
4. Non-Clustered Index
Stores a separate structure with pointers to actual rows.
A table can have multiple non-clustered indexes.
Example:
5. CREATE NONCLUSTERED INDEX idx_name
6. ON Employee(Name);

👉 Analogy:
Clustered = Dictionary arranged alphabetically (data itself is sorted).
Non-Clustered = Separate bookmark list pointing to pages.

🔹 Why Indexing Improves Performance?

Makes search, WHERE, JOIN, ORDER BY queries faster.
Reduces I/O by avoiding full table scans.
BUT: Indexing slows down INSERT/UPDATE/DELETE (because index also needs updating).

⚡ Interview Expectations
“What is an index in DBMS? Give an example.”
“Difference between clustered and non-clustered index.”
“Why can we have only one clustered index but multiple non-clustered indexes?”
👉
Sometimes: “Does indexing always improve performance?” ( No, inserts/updates get
slower).

6. ER Model Basics
🔹 Entity
Definition: A real-world object or concept that has a unique existence in the database.
Example: Student, Course, Employee, Department.
In ER diagram → shown as a rectangle.

🔹 Attributes
Definition: Properties/characteristics of an entity.
Types:
Simple: Cannot be divided further (e.g., Age).
Composite: Can be divided (e.g., FullName → FirstName + LastName).
Derived: Can be calculated from others (e.g., Age from DOB).
Multi-valued: More than one value possible (e.g., Phone Numbers).
 In ER diagram → shown as ovals.

🔹 Relationships
Definition: Association between entities.
Types:
1:1 (One-to-One) → Each student has one ID card.
1:N (One-to-Many) → One department has many employees.
M:N (Many-to-Many) → Students and Courses (many students enroll in many courses).
In ER diagram → shown as a diamond.

🔹 Strong vs Weak Entity

1. Strong Entity
Has its own primary key.
Does not depend on other entities.
Example: Student(StudentID, Name) → StudentID is primary key.
2. Weak Entity
Cannot be uniquely identified without another entity (depends on strong entity).
Has a partial key + uses foreign key from strong entity.
Example: Dependent(DependentName, StudentID) → needs StudentID from Student
to be uniquely identified.
In ER diagram → double rectangle for weak entity, double diamond for identifying
relationship.

⚡ Interview Expectations
“What is the difference between strong and weak entity? Give examples.”
“What are different types of attributes in ER model?”
“Explain cardinality (1:1, 1:N, M:N) with examples.”

DBMS – Remaining Important Interview Topics

🔹 7. Joins vs Subqueries vs Views
Joins: Combine rows from multiple tables based on related columns.
Subquery: Query inside another query.
View: Virtual table created from a query.
CREATE VIEW high_salary AS
SELECT EmpName, Salary FROM Employee WHERE Salary > 50000;

👉 Interview Q: “Difference between Join and Subquery?” (👉 Joins combine, Subqueries are
nested).

🔹 8. Stored Procedures vs Triggers

Stored Procedure: Precompiled set of SQL statements, executed when called.
 CREATE PROCEDURE getEmployees()
BEGIN
SELECT * FROM Employee;
END;
Trigger: Automatic execution of SQL when an event (INSERT/UPDATE/DELETE) occurs.
CREATE TRIGGER before_insert_employee
BEFORE INSERT ON Employee
FOR EACH ROW
SET [Link] = UPPER([Link]);

👉 Interview Q: “Difference between Stored Procedure and Trigger?”

🔹 9. Concurrency Control & Deadlocks
Concurrency Control: Ensures correct results when multiple transactions run at the same
time.
Deadlock: Two transactions waiting for each other’s resources → stuck forever.
👉 Interview Q: “What is deadlock? How can DBMS prevent it?”

🔹 10. Difference Between DBMS & RDBMS

DBMS: Stores data as files (no relations). Example: XML DB, file systems.
RDBMS: Stores data in tables with relations (foreign keys, joins). Example: MySQL, Oracle.
👉Very common conceptual check.

🔹 11. Difference Between DELETE, TRUNCATE, DROP

DELETE: Removes specific rows, can rollback.
TRUNCATE: Removes all rows, can’t rollback.
DROP: Deletes entire table structure.
👉Classic short question.

🔹 12. Difference Between WHERE and HAVING

WHERE: Used before grouping (row-level filtering).
HAVING: Used after grouping (aggregate filtering).
👉They love to throw this as a trick.