DBMS QUESTION BANK -

COMPLETE SOLUTION
GUIDE
For Dr. D. Y. Patil Institute of Technology, Pune Department of Artificial Intelligence and Data Science
Engineering

Q1: Define DBMS and Draw architecture

and explain Storage manager and Query
Processor
What is DBMS?
DBMS (Database Management System) is a software that allows you to store, retrieve, and manage data efficiently.
Think of it like a digital filing cabinet that organizes millions of records and lets you find what you need instantly.

Key Functions of DBMS

1. Data Storage: Safely stores data on disk
2. Data Retrieval: Finds data quickly
3. Data Update: Modifies existing data
4. Data Security: Protects data from unauthorized access
5. Backup & Recovery: Prevents data loss

DBMS Architecture (3-Layer Model)

┌─────────────────────────────────────────┐
│ EXTERNAL/VIEW LAYER (User Interface) │
│ (Different views for different users) │
├─────────────────────────────────────────┤
│ CONCEPTUAL/LOGICAL LAYER (Data Model) │
│ (Overall database structure) │
├─────────────────────────────────────────┤
│ INTERNAL/PHYSICAL LAYER (Storage) │
│ (How data is physically stored on disk) │
└─────────────────────────────────────────┘

Layer 1 - External/View Layer: What users see (different views for different users) Layer 2 - Conceptual/Logical
Layer: Overall database structure (tables, relationships) Layer 3 - Internal/Physical Layer: How data is physically
stored on disk

Storage Manager
Manages how data is stored on the hard disk. It handles:

File organization: How records are arranged

Buffer management: Keeps frequently used data in RAM
Space allocation: Decides where to store new data
Index management: Creates indexes for faster searching

Query Processor
Interprets and executes SQL queries. Process:

1. Parser: Checks if query syntax is correct

2. Optimizer: Finds the best way to execute query (fastest route)
3. Compiler: Converts query to executable form
4. Execution Engine: Actually runs the query and returns results

Q2: Hospital ER Diagram - Identify

relationships, cardinalities, keys
Entities Identified
1. Doctor: Doctor_ID (unique), name, specialization
2. Patient: Patient_ID (unique), name, address, contact
3. Room: Room_No (unique), type (general/ICU), capacity
 4. Lab: Lab_ID (unique), tests available
5. Department: Dept_ID (unique), department name, location

Relationships
1. Doctor WORKS_IN Department (Many-to-One)

One doctor works in one department

Many doctors in same department

2. Patient ADMITTED_TO Room (Many-to-One)

Many patients can use a room (at different times)

3. Patient TREATED_BY Doctor (Many-to-Many)

One patient treated by multiple doctors

One doctor treats multiple patients

4. Patient PERFORMS Lab_Test (Many-to-Many)

Patients can have multiple tests

Tests are done on multiple patients

Cardinalities
Doctor-Department: Many-to-One (M:1)
Patient-Doctor: Many-to-Many (M:M)
Patient-Room: Many-to-One (M:1)
Patient-Lab: Many-to-Many (M:M)

Keys to Define
Primary Keys: Doctor_ID, Patient_ID, Room_No, Lab_ID, Dept_ID
Foreign Keys: Doctor.Dept_ID (references Department)
Composite Keys: For relationships like Treatment(Doctor_ID, Patient_ID, Date)

Q3: Levels of Abstraction in DBMS

Why Do We Need Abstraction?
Abstraction hides complexity. Just like you don't need to know how a car engine works to drive it, database users don't
need to know where data is physically stored on disk.

Level 1 - Physical Layer (Internal)

 Deals with actual storage on disk (hard drive, SSD)
How data is arranged in files and blocks
How indexes are stored
Storage structures like B+ trees
Users: Database administrators (very few people)

Level 2 - Logical Layer (Conceptual)

Describes overall structure - what tables exist, what columns they have
Shows relationships between tables
Defines constraints like 'email must be unique'
Example: Student table has RollNo, Name, Email columns
Users: Database designers, administrators

Level 3 - View Layer (External)

What end-users see - customized views of data
Different users see different data
Example: HR manager sees salary, but students don't
A teacher might see only student grades, not home address
Can be based on one or multiple tables

Why This Matters

If physical storage changes (use new disk technology), the logical and view layers don't change - users aren't affected.
Database can handle huge changes internally without disrupting users.

Q4: Convert ER Diagram to Relational

Schema with Primary and Foreign Keys
Basic Conversion Rules
1. Each Entity → One Table
2. Primary Key of entity → Primary Key of table
3. Attributes of entity → Columns in table
4. Relationships → Foreign Keys (depends on cardinality)

For One-to-Many (1:M) Relationships

Add Foreign Key on the 'Many' side

Example: Department (1) to Doctor (M)

Doctor table gets Department_ID as Foreign Key
Doctor (Doctor_ID PK, Name, Department_ID FK)

For Many-to-Many (M:M) Relationships

Create a NEW table (junction table)

Example: Doctor-Patient relationship

Create: Treats(Doctor_ID FK, Patient_ID FK, Date, Time)

This new table's PK is combination of both FKs

For One-to-One (1:1) Relationships

Can add FK on either side (convention: add to dependent entity)

Example: Person(PK:PersonID) to Passport(PK:PassportID)

Passport gets PersonID as FK to ensure one person per passport

Example Conversion

ER: Doctor-Treats-Patient
Tables:
✓ Doctor (DoctorID PK, Name, Spec)
✓ Patient (PatientID PK, Name, Address)
✓ Treats (DoctorID FK, PatientID FK, Date, Notes)

Q5: Convert ER Diagram to Relational

Schema with Keys
Given ER Diagram Analysis
From the diagram we see:

Match entity attributes: date, matchId, stadium, opponent, own_score, opp_score

Player entity attributes: name, age, score, season_score
Relationship: Match 'played' by Player (Many-to-Many)
 Relational Schema Creation

Match (MatchID PK, MatchDate, Stadium, Opponent, OwnScore, OppScore)

Player (PlayerID PK, Name, Age)
Played (MatchID FK, PlayerID FK, PlayerScore)

Key Definitions
Primary Keys: MatchID uniquely identifies each match, PlayerID uniquely identifies each player
Foreign Keys: In Played table, both MatchID and PlayerID are FKs pointing to their respective tables
Composite Key: (MatchID, PlayerID) in Played table ensures one player per match

Constraints
NOT NULL: MatchDate, Stadium, Opponent (can't have match without these)
UNIQUE: MatchID, PlayerID
FOREIGN KEY: Ensures referential integrity - can't add match/player that doesn't exist

Q6: Define Data Models - Different Types

Used in DBMS
What is a Data Model?
A data model is a blueprint showing how data is organized and how it relates to other data. Think of it like architectural
plans for a building - it defines the structure before building.

1. Hierarchical Data Model

Data organized in tree structure with parent-child relationships
One parent can have many children, but each child has only one parent
Example: Organization structure - CEO at top, managers below, employees below them
Used in: Old mainframe systems, file systems
Disadvantage: Can't represent complex relationships

2. Network Data Model

Extension of hierarchical model
Allows many-to-many relationships (unlike hierarchical)
Data represented as graph with nodes and connections
Example: Student can take multiple courses, course can have multiple students
 Used in: Some specialized databases
Disadvantage: More complex to navigate

3. Relational Data Model (Most Popular)

Data organized in tables (relations) with rows (records) and columns (attributes)
Based on mathematical set theory
Simple and easy to understand
Example: Student table with RollNo, Name, Branch columns
Uses SQL for querying
Current standard in databases

4. Entity-Relationship (ER) Model

Shows entities (things we store), attributes (properties), and relationships
Used during database design phase
Visual representation with boxes (entities), ovals (attributes), diamonds (relationships)
Bridge between conceptual design and relational model
Easy to communicate with non-technical people

5. Object-Oriented Data Model

Combines OOP with databases
Data stored as objects with properties and methods
Supports inheritance and polymorphism
Used in: Object databases like GemStone
Good for complex applications

6. NoSQL/Document Model
Flexible schema - different records can have different fields
Data stored as JSON documents or key-value pairs
Example: MongoDB stores documents like {name: 'Ram', age: 25, grade: 'A'}
Scalable for big data
Used in: Web applications, real-time data

Q7: Banking System ER Diagram - Identify

Relationships and Create Tables
Requirements Analysis
1. Bank organized into branches in different cities
 2. Each branch has multiple employees
3. Accounts can be: Saving or Current type
4. One account can be owned by multiple customers
5. One customer can have multiple accounts
6. Loans are held at a specific branch
7. One loan can be taken by multiple customers

Entities Identified
Branch: BranchID, Location, ManagerName, Assets
Customer: CustomerID, Name, Address, PhoneNo
Account: AccountNo, Type (Saving/Current), Balance, DateOpened
Loan: LoanID, Amount, Type, Rate, Status
Employee: EmployeeID, Name, Salary, Position

Relationships with Cardinality

1. Branch (1) --- MANAGES --- (M) Loan: One branch manages many loans
2. Branch (1) --- EMPLOYS --- (M) Employee: One branch employs many employees
3. Customer (M) --- OPENS --- (M) Account: M-M relationship
4. Customer (M) --- BORROWS --- (M) Loan: M-M relationship

Relational Schema

Branch (BranchID PK, Location, ManagerName, Assets)

Customer (CustomerID PK, Name, Address, PhoneNo)
Account (AccountNo PK, Type, Balance, DateOpened)
Loan (LoanID PK, Amount, Type, Rate, BranchID FK)
Opens (CustomerID FK, AccountNo FK) - Junction table
Borrows (CustomerID FK, LoanID FK, DateApplied) - Junction table
Employee (EmployeeID PK, Name, Salary, Position, BranchID FK)

Q8: What is a VIEW? How to Create It?

Can You Update Views?
What is a VIEW?
A VIEW is a virtual table created from a query. It's like a saved search result that appears as a table. It doesn't store data
- it shows data from actual tables dynamically.

Think: A view is a window showing specific data you're interested in.

 How to Create a VIEW
Syntax:

CREATE VIEW viewname AS SELECT ... FROM ... WHERE ...;

Example:

CREATE VIEW HighScorers AS

SELECT StudentID, Name, Marks
FROM Student
WHERE Marks > 75;

Now 'HighScorers' acts like a table with only students who scored above 75.

Types of VIEWs
1. Simple Views: Based on one table
2. Complex Views: Based on multiple tables (using JOINs)

Can You UPDATE Views?

YES, but with conditions!

✓ Simple Views (single table): Can INSERT, UPDATE, DELETE

✗ Complex Views (multiple tables/aggregates): Usually CANNOT update

Example that CAN be updated:

UPDATE HighScorers SET Marks = 80 WHERE StudentID = 101;

Example that CANNOT be updated:

CREATE VIEW AvgMarks AS

SELECT Department, AVG(Marks) as Average FROM Student GROUP BY Department;
-- Can't update aggregates

Advantages of VIEWs
Security: Hide sensitive columns
Simplicity: Complex queries saved as simple view
Consistency: Multiple users see same data format
 Q9: What is an INDEX? Advantages and
Disadvantages
What is an INDEX?
An INDEX is a database structure that speeds up data retrieval, like a book's index.

Without index: Finding 'Ram' in student table = check every student (slow) With index on Name: Directly jump to 'Ram'
(fast)

Database maintains: Name → Position in table mapping

Types of Indexes
1. Primary Index: Automatically created on primary key
2. Secondary Index: User-created on other columns
3. Unique Index: Ensures all values are unique
4. Composite Index: Index on multiple columns together
5. Full-text Index: For searching text like Google does

How INDEXes Speed Up Searches

Without Index:

SELECT * FROM Student WHERE Name='Ram';

Scans entire table → O(n) complexity

With Index on Name:

Uses index to find position → O(log n) complexity

Then retrieves from that position → Much faster

ADVANTAGES
✓ Much faster data retrieval (10-100x faster) ✓ Makes WHERE clauses faster ✓ Makes JOIN operations faster ✓
Reduces disk I/O operations ✓ Improves ORDER BY and GROUP BY performance

DISADVANTAGES
✗ Uses extra disk space (index table takes space) ✗ Makes INSERT/UPDATE/DELETE slower (must update index too)
✗ Index maintenance overhead ✗ If you index on column you never search, wastes space ✗ Too many indexes can
confuse optimizer

When to Use Indexes

Use on: Primary key, Foreign key, columns in WHERE clause, columns in ORDER BY Avoid on: Columns with few
distinct values (M/F), columns rarely used in queries, small tables

Q10: SQL Queries for Employee Schema

Schema Given

Emp(Emp_no, Emp_name, Dept_no)

Dept(Dept_no, Dept_name)
Address(Dept_name, Dept_location)

Query 1: Display location of department where employee

'Ram' works

SELECT Dept_location
FROM Address a, Dept d, Emp e
WHERE e.Emp_name = 'Ram'
AND e.Dept_no = d.Dept_no
AND d.Dept_name = a.Dept_name;

Explanation: Join all three tables and filter for Ram

Query 2: Create view to store total no of employees working

in each department in ascending order

CREATE VIEW DeptEmpCount AS

SELECT d.Dept_name, COUNT(e.Emp_no) as EmployeeCount
FROM Dept d LEFT JOIN Emp e ON d.Dept_no = e.Dept_no
GROUP BY d.Dept_no, d.Dept_name
ORDER BY EmployeeCount DESC;

-- Now use: SELECT * FROM DeptEmpCount;

 Query 3: Find the name of the department in which no
employee is working

SELECT d.Dept_name
FROM Dept d
WHERE d.Dept_no NOT IN
(SELECT DISTINCT Dept_no FROM Emp WHERE Dept_no IS NOT NULL);

OR using LEFT JOIN:

SELECT d.Dept_name
FROM Dept d LEFT JOIN Emp e ON d.Dept_no = e.Dept_no
WHERE e.Emp_no IS NULL;

Q11: PL/SQL Block for Student

Attendance Check
Requirement
Accept RollNo from user and check attendance in student_attendance table. Display the attendance percentage.

PL/SQL Code
DECLARE
v_rollno NUMBER;
v_attendance NUMBER;
v_total_days NUMBER;
v_percentage NUMBER;
BEGIN
v_rollno := &Enter_RollNo; -- Accept input from user

-- Count attended days

SELECT COUNT(*)
INTO v_attendance
FROM student_attendance
WHERE RollNo = v_rollno AND Attendance = 'Present';

-- Count total days

SELECT COUNT(*)
INTO v_total_days
FROM student_attendance
WHERE RollNo = v_rollno;

-- Calculate percentage
IF v_total_days = 0 THEN
DBMS_OUTPUT.PUT_LINE('No attendance records found');
ELSE
v_percentage := (v_attendance / v_total_days) * 100;
DBMS_OUTPUT.PUT_LINE('RollNo: ' || v_rollno);
DBMS_OUTPUT.PUT_LINE('Attendance Days: ' || v_attendance);
DBMS_OUTPUT.PUT_LINE('Total Days: ' || v_total_days);
DBMS_OUTPUT.PUT_LINE('Attendance %: ' || v_percentage);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Student not found');
END;
/

Explanation
DECLARE: Define variables
&Enter_RollNo: Takes user input
INTO: Stores result in variable
IF-ELSE: Handles no records case
DBMS_OUTPUT: Displays results
 EXCEPTION: Handles errors gracefully

Q12: What is JOIN? Main Types of JOINs

with Examples
What is JOIN?
JOIN combines rows from two or more tables based on a condition. It's like combining information from different sources.

Without JOIN: Must query each table separately With JOIN: Get related data in one query

1. INNER JOIN (Most Common)

Returns rows where condition matches in BOTH tables.

SELECT [Link], [Link]

FROM Employee e INNER JOIN Department d
ON [Link] = [Link];

Result: Only employees with existing departments

2. LEFT JOIN (LEFT OUTER JOIN)

Returns ALL rows from LEFT table + matching rows from RIGHT table.

SELECT [Link], COUNT([Link])

FROM Department d LEFT JOIN Employee e
ON [Link] = [Link]
GROUP BY [Link];

Result: All departments shown, even those with no employees (count=0)

3. RIGHT JOIN (RIGHT OUTER JOIN)

Returns ALL rows from RIGHT table + matching rows from LEFT table.

SELECT [Link], [Link]

FROM Department d RIGHT JOIN Employee e
ON [Link] = [Link];
 Result: All employees shown, even those without department

4. FULL OUTER JOIN

Returns ALL rows from both tables.

SELECT COALESCE([Link], 'No Employee') as Name,

COALESCE([Link], 'No Dept') as Dept
FROM Employee e FULL OUTER JOIN Department d
ON [Link] = [Link];

Result: All employees and all departments, with NULLs where no match

5. CROSS JOIN
Returns Cartesian product (all combinations).

SELECT [Link], [Link]

FROM Employee e CROSS JOIN Project p;

Result: If 5 employees and 3 projects → 15 rows (each employee with each project)

Q13: Referential Integrity and Entity

Integrity Constraints
Entity Integrity Constraint
Rule: Primary key cannot be NULL and must be UNIQUE.

Why? Each row must be uniquely identifiable.

Student (StudentID PK NOT NULL UNIQUE, Name, Email)

✓ Allowed: StudentID = 101, Name = 'Ram'

✗ Not allowed: StudentID = NULL (violates entity integrity)

Referential Integrity Constraint

Rule: Foreign key must either match a value in referenced table's primary key, or be NULL.

Why? Prevents orphaned records (child without parent).

 Employee (EmpID PK, Name, DeptID FK)
Department (DeptID PK, DeptName)

[Link] must exist in [Link]

✓ Allowed: DeptID = 10 (if dept 10 exists)

✓ Allowed: DeptID = NULL (employee not assigned to dept)
✗ Not allowed: DeptID = 99 (if dept 99 doesn't exist)

Enforcement Through Cascade

DELETE CASCADE: If parent deleted, children deleted too UPDATE CASCADE: If parent key updated, children updated
too

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,
Name VARCHAR(50),
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
ON DELETE CASCADE
);

If Department 10 is deleted, all employees in dept 10 are deleted too

Why These Matter

Entity Integrity: Prevents duplicate/undefined records
Referential Integrity: Prevents invalid relationships
Together: Ensure database consistency and reliability

Q14: JOIN Commands and Types with

Examples
Different JOIN Syntax Styles
1. Comma-separated tables (Old style):

SELECT * FROM Table1, Table2 WHERE condition;

2. ON Clause (Recommended):
SELECT * FROM Table1 JOIN Table2 ON condition;

3. USING Clause (When column names same):

SELECT * FROM Table1 JOIN Table2 USING (CommonColumn);

INNER JOIN Example

SELECT [Link], [Link], [Link]

FROM Employee e
INNER JOIN Department d ON [Link] = [Link];

Result: Only employees with departments (excludes employees with no dept)

LEFT JOIN Example

SELECT [Link], [Link], [Link]

FROM Employee e
LEFT JOIN Department d ON [Link] = [Link];

Result: All employees, with NULL for dept if employee not assigned

MULTI-TABLE JOIN

SELECT [Link], [Link], [Link]

FROM Employee e
INNER JOIN Department d ON [Link] = [Link]
INNER JOIN Manager m ON [Link] = [Link];

Combines 3 tables: Employee → Department → Manager

Q15: What is TRIGGER? Types and How

to Create
What is a TRIGGER?
A TRIGGER is a special program that automatically executes (fires) in response to specific events on a table in
database.
 Think: A trigger is like an alarm - when something happens, it automatically responds.

When Triggers Fire

1. BEFORE INSERT: Before new row is added
2. AFTER INSERT: After new row is added
3. BEFORE UPDATE: Before row is modified
4. AFTER UPDATE: After row is modified
5. BEFORE DELETE: Before row is deleted
6. AFTER DELETE: After row is deleted

Why Use Triggers?

Maintain audit logs (track who changed what when)
Enforce complex business rules
Update derived columns automatically
Prevent invalid transactions
Maintain referential integrity
Log historical changes

CREATE TRIGGER Example

CREATE TRIGGER CheckSalary

BEFORE INSERT ON Employee
FOR EACH ROW
BEGIN
IF [Link] < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary cannot be negative';
END IF;
END;

This trigger prevents negative salaries from being entered.

Another Example - Audit Log

CREATE TRIGGER LogEmployeeDeletion
AFTER DELETE ON Employee
FOR EACH ROW
BEGIN
INSERT INTO EmployeeAudit
VALUES ([Link], [Link], 'DELETED', NOW());
END;

This automatically records whenever employee is deleted.

Types of Triggers
1. Row-level: Fires once per affected row (FOR EACH ROW)
2. Statement-level: Fires once per statement (FOR EACH STATEMENT)
3. INSTEAD OF: Replaces original action (INSTEAD OF INSERT)
4. Cascading: Triggers that fire other triggers

Q16: PL/SQL Procedure for Hotel Room

Listing
Requirement
Write procedure to list price and type of all rooms at hotel 'TAJ'

PL/SQL Procedure Code

CREATE PROCEDURE ListRoomsByHotel(p_hotel_name VARCHAR2)
IS
CURSOR room_cursor IS
SELECT r.Room_no, [Link], [Link]
FROM Rooms r
INNER JOIN Hotels h ON r.hotel_no = h.hotel_no
WHERE h.hotel_name = p_hotel_name;
BEGIN
DBMS_OUTPUT.PUT_LINE('=== ROOMS AT ' || p_hotel_name || ' ===');
DBMS_OUTPUT.PUT_LINE('RoomNo | Type | Price');
DBMS_OUTPUT.PUT_LINE('--------+----------+-------');

FOR room IN room_cursor LOOP

DBMS_OUTPUT.PUT_LINE(
room.Room_no || ' | ' || [Link] || ' | Rs.' || [Link]
);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Hotel not found');
END ListRoomsByHotel;
/

-- Call procedure:
EXEC ListRoomsByHotel('TAJ');

Explanation
CREATE PROCEDURE: Defines reusable code block
p_hotel_name: Input parameter
CURSOR: Loop through query results
FOR LOOP: Process each room
DBMS_OUTPUT: Display results
EXCEPTION: Handle errors

Q17: Impact of Normalization: 2NF, 3NF,

BCNF
What is Normalization?
Normalization is the process of organizing data to reduce redundancy and improve data integrity. It's like organizing a
messy closet into organized drawers.

Benefit: Eliminates data duplication, saves space, prevents update anomalies Trade-off: More tables = more joins
needed = slightly slower queries

Why Normalize?
Without Normalization Problem (Anomalies):

1. Insert Anomaly: Can't insert data without other info

2. Update Anomaly: Changing one piece of data requires changing multiple places
3. Delete Anomaly: Deleting one record removes unrelated information

2NF (Second Normal Form)

Rules:

1. Must satisfy 1NF (atomic values, no repeating groups)

2. Every non-key attribute must depend on ENTIRE primary key, not just part of it

Problem Fixed:

Before: StudentCourse(StudentID, CourseID, StudentName, CourseName)

Issue: StudentName depends only on StudentID, not on CourseID

After 2NF:
Student(StudentID, StudentName)
Course(CourseID, CourseName)
Enrollment(StudentID, CourseID)

Benefit: Eliminates partial dependencies

3NF (Third Normal Form)

Rules:

1. Must satisfy 2NF

2. Non-key attributes must depend ONLY on primary key, not on other non-key attributes (no transitive dependency)

Problem Fixed:
Before: Student(StudentID, StudentName, DeptID, DeptName, DeptLocation)
Issue: DeptName depends on DeptID (non-key), not directly on StudentID

After 3NF:
Student(StudentID, StudentName, DeptID)
Department(DeptID, DeptName, DeptLocation)

Benefit: Eliminates transitive dependencies, prevents update anomalies

BCNF (Boyce-Codd Normal Form)

Stricter than 3NF Rule: Every determinant must be a candidate key

Example where 3NF fails but BCNF needed:

TeacherCourse(TeacherID, CourseID, Time)

Where:
- TeacherID, CourseID → Time (primary key)
- Time → TeacherID (same teacher teaches at same time)

BCNF Solution:
TeacherSchedule(TeacherID, Time)
CourseSchedule(CourseID, Time)

Impact Summary
✓ Reduces redundancy (saves space)
✓ Improves data consistency
✓ Prevents anomalies
✓ Easier to maintain
✗ More tables (more joins)
✗ Slightly complex queries
✗ Marginally slower performance

Q18: Define 3NF - How to Bring Relation

into 3NF
3NF Definition
A relation is in 3NF if:
 1. It's in 2NF
2. No transitive dependencies (non-key attributes don't depend on other non-key attributes)

What is Transitive Dependency?

When X → Y → Z (X determines Y, Y determines Z, so X transitively determines Z)

Example:

StudentEnrollment(StudentID, StudentName, DeptID, DeptName)

• StudentID → StudentName (OK - both depend on primary key)
• StudentID → DeptID (OK)
• DeptID → DeptName (PROBLEM - non-key attribute depends on non-key attribute)
This violates 3NF!

Bringing into 3NF - Example

BEFORE 3NF:

Employee(EmpID, EmpName, DeptID, DeptName, DeptLocation)

Dependencies:

EmpID → EmpName ✓
EmpID → DeptID ✓
DeptID → DeptName ✗ (DeptName depends on DeptID, not EmpID)
DeptID → DeptLocation ✗ (transitive dependency)

AFTER Decomposing to 3NF:

Employee(EmpID, EmpName, DeptID)

Department(DeptID, DeptName, DeptLocation)

Now all non-key attributes depend ONLY on primary key ✓

Step-by-Step Process
1. Identify all functional dependencies
2. Find transitive dependencies (A→B→C)
3. Remove transitive dependencies by splitting table
4. Create new table for transitively dependent attributes
5. Link tables using foreign keys
 Q19: Functional Dependency and
Checking 3NF
Functional Dependency (FD) Definition
X → Y means: If you know value of X, you can uniquely determine value of Y.

Example: StudentID → StudentName If you tell me StudentID=101, I can find unique StudentName='Ram'

Not FD: StudentID → Height (same StudentID could have different heights at different times)

Identifying FDs
1. Primary Key → All attributes (always true)
2. Unique columns → Dependent attributes
3. Logical relationships create FDs

Example schema:

Student(StudentID, StudentName, Email, DeptID, DeptName, DeptLocation)

FDs:

StudentID → StudentName, Email, DeptID ✓

StudentID → DeptName ✓ (through DeptID)
DeptID → DeptName ✓ (TRANSITIVE - creates problem!)
Email → StudentID ✓ (if email unique)

Checking if Relation is 3NF

Question: Is Student(StudentID, StudentName, Email, DeptID, DeptName, DeptLocation) in 3NF?

Step 1: Check 2NF ✓ All non-key attributes depend on ENTIRE primary key (StudentID is single)

Step 2: Check for Transitive Dependencies

✗ DeptID → DeptName (non-key → non-key)

✗ DeptID → DeptLocation

CONCLUSION: NOT in 3NF

Converting to 3NF
Decompose:
 Student(StudentID, StudentName, Email, DeptID)
Department(DeptID, DeptName, DeptLocation)

Now checking:

✓ All non-key attributes depend only on primary key

✓ No transitive dependencies
✓ IN 3NF!

Why This Matters

Update Anomaly Example:

Before: Change DeptName in one Student record requires changing in all records of that dept
After: Change DeptName once in Department table, all students automatically see updated info

Q20-21: Codd's Rules - Explanation

Who is Codd?
Dr. Edgar F. Codd invented the Relational Database Model and defined 13 rules that a system must satisfy to be called
Truly Relational.

Rule 0: Foundation Rule

System must be relational to be considered relational database. All data must be represented in tables (relations).

Rule 1: Information Rule

All information in database must be explicitly represented as values in tables. No metadata hidden in code or comments.

✓ Create Status column with values: 'Active', 'Inactive' ✗ Don't use positive IDs for active, negative for inactive

Rule 2: Guaranteed Access Rule

Every atomic (individual) value must be retrievable using:

Table name + Column name + Primary key value

SELECT Email FROM Student WHERE StudentID = 101;

Rule 3: Systematic NULL Handling

 NULL must be used to represent missing/unknown information.

✓ Use NULL for unknown age (not 0 or -1) ✗ Don't use magic numbers like 999 to represent missing data

Rule 4: Dynamic Online Catalog

Database structure info (metadata) should be stored in tables like regular data. Users should query database structure
same way as regular data.

SELECT * FROM INFORMATION_SCHEMA.TABLES (SQL Server)

-- Shows all tables in database

Rule 5: Comprehensive DML Language

System must support comprehensive data manipulation language (like SQL). Must support: SELECT, INSERT, UPDATE,
DELETE at minimum.

Rules 6-9
Rule 6: View Updatability Views should be updatable (at least theoretically) whenever update makes sense.

Rule 7: High-Level Insert, Update, Delete Data manipulation possible not just by individual rows but also sets of rows.

Rule 8: Physical Data Independence Changing physical storage (how stored on disk) shouldn't affect
applications/queries. Applications continue working even if database switches from HDD to SSD.

Rule 9: Logical Data Independence Changing logical structure (adding columns/tables) shouldn't break existing
queries.

OLD query: SELECT EmpID, Name FROM Employee

AFTER adding column: Same query still works

Rules 10-13
Rule 10: Integrity Independence Integrity constraints should be definable and stored in catalog, not in application code.

✓ Define NOT NULL in table definition ✗ Don't check NOT NULL in application

Rule 11: Distributed Independence Database distribution (across multiple servers) shouldn't affect queries. Query
works whether data is local or remote.

Rule 12: Nonsubversion Rule If relational interface exists, no low-level language can bypass integrity rules. Can't use
backdoor methods to violate constraints.
Rule 13: Rule Independence If any rule is relaxed, it must be stated in documentation. Can't claim to be relational while
breaking these rules secretly.

Why These Rules Matter?

These rules define what makes a database truly 'relational'. Most modern RDBMS (Oracle, MySQL, PostgreSQL) follow
most of these rules. Understanding these helps in designing good databases.

Q22: INSERT, UPDATE, DELETE

Anomalies
INSERT Anomaly
Problem: Can't insert a record without having certain other data.

Example (Denormalized):

StudentCourse(StudentID, StudentName, CourseID, CourseName, Credits)

Can't insert a new course without assigning a student!

Impact: Can't register new courses until someone enrolls.

Solution - Normalize:

Course(CourseID, CourseName, Credits) - can insert independently

StudentCourse(StudentID, CourseID) - links students to courses

UPDATE Anomaly
Problem: Changing one piece of data requires changing it in multiple places.

Example (Denormalized):

Employee(EmpID, EmpName, DeptID, DeptName, Location)

If Department location changes, must update ALL employees in that department!

Impact:

Expensive operation (update many rows)

Risk of inconsistency (miss some updates)
Data integrity violations
Example: Dept 10 'Sales' was in 'Mumbai', now in 'Delhi' Miss updating John's record → John still shows 'Mumbai'
Database now has inconsistent data!

Solution - Normalize:

Department(DeptID, DeptName, Location)

Employee(EmpID, EmpName, DeptID)
Update location once in Department table → All employees see update

DELETE Anomaly
Problem: Deleting a record removes unrelated information.

Example (Denormalized):

Course(CourseID, CourseName, Credits, InstructorID, InstructorName, Dept)

Want to delete a course → Lost instructor info!

Want to remove instructor → Must delete course!

Impact:

Unintended data loss

Can't delete without losing important info

Solution - Normalize:

Course(CourseID, CourseName, Credits, InstructorID)

Instructor(InstructorID, InstructorName, Dept)
Delete Course table row → Instructor still exists
Delete Instructor → Course can still exist (with NULL InstructorID)

How Normalization Prevents These

✓ 1NF: Eliminates repeating groups
✓ 2NF: Eliminates partial dependencies (INSERT anomaly)
✓ 3NF: Eliminates transitive dependencies (UPDATE & DELETE anomalies)
✓ BCNF: Stricter form ensures all anomalies prevented

Q23: Conflict Serializability

 What is Serializability?
A schedule is serializable if its result is same as some serial execution.

Serial: T1 completes fully, then T2 starts (no interleaving)

Interleaved: T1 and T2 execute together (instructions mixed)

Goal: Interleaved schedules that give same result as serial = SERIALIZABLE

What is Conflict?
Two operations conflict if:

1. They belong to different transactions

2. They access same data item
3. At least one is WRITE operation

Conflicting pairs:

Read-Write: T1 reads X, T2 writes X (or vice versa)

Write-Write: T1 writes X, T2 writes X

NON-conflicting:

Read-Read: Both just read (no conflict)

Different data items (no conflict)

Conflict Serializable Definition

A schedule is conflict serializable if we can reorder non-conflicting operations to get a serial schedule.

If conflicts prevent reordering → NOT serializable

Example - Check Serializability

Transaction T1: Read(X), Write(X), Read(Y)

Transaction T2: Read(Y), Write(Y), Read(X)

Schedule: R1(X), R2(Y), W1(X), W2(Y), R1(Y), R2(X)

Step 1: Identify conflicts:

W1(X) conflicts with R2(X) → T1 before T2
R2(Y) conflicts with W1(Y) → T2 before T1

T1→T2 but T2→T1 (cycle!) → NOT SERIALIZABLE

 Testing Using Precedence Graph
1. For each conflict: Draw edge from earlier transaction to later
2. If cycle exists → NOT serializable
3. If no cycle → IS serializable (topological order exists)

Q24: ACID Properties and Transaction

States
What is a Transaction?
A transaction is a sequence of database operations that must all succeed together or all fail together.

Example: Money transfer

Begin
Withdraw 1000 from Account A
Deposit 1000 to Account B
End

Either both succeed or both fail. Can't withdraw without deposit!

ACID Properties
A = Atomicity: All or Nothing
C = Consistency: Valid to Valid
I = Isolation: No Interference
D = Durability: Permanent Once Committed

A - Atomicity
Either ALL operations execute or NONE execute.

Example:

✓ Case 1: Both withdraw and deposit succeed

✓ Case 2: System fails, both don't execute (rollback)
✗ Case 3: Only withdraw happens, deposit fails - NOT ALLOWED

Technical Implementation: Write-Ahead Log (WAL)

C - Consistency
 Database moves from valid state to valid state. All integrity constraints maintained before and after transaction.

Example: Bank account total money must equal sum of all accounts

Before: Total = 50000

After transfer: Total still = 50000 (consistency maintained)

I - Isolation
Each transaction executes independently, not seeing intermediate states of other transactions.

Example:

T1: Transfer 1000

T2: Check balance

T2 should see either:

✓ Balance BEFORE transfer, OR
✓ Balance AFTER transfer
✗ NOT intermediate balance (during transfer)

Implemented using: Locking, MVCC (Multi-Version Concurrency Control)

D - Durability
Once transaction is committed, data is permanently saved. Survives system failures, power outages, crashes.

Example:

Wrote to database at 5PM

System crashes at 5:01PM
Data from transaction is still there when system restarts

Implemented using: Persistent storage, transaction logs, regular backups

Transaction States Diagram

BEGIN → RUNNING → COMMITTED (success) or ROLLED BACK (failure)

Active: Transaction executing

Partially Committed: All operations done but not yet saved
Committed: Saved permanently
Failed: Error occurred
Aborted: Rolled back to before transaction

Can't go backward from Committed (permanent!)

Q25: Log-Based Recovery and Shadow

Paging
Need for Recovery
Systems crash (power failure, hardware error, software bug) Must recover without losing committed data or leaving
database corrupted.

Two main recovery methods:

1. Log-Based Recovery
2. Shadow Paging

Log-Based Recovery (Write-Ahead Logging)

Concept: Write INTENTIONS before executing

Process:

1. Before executing: Write log entry describing what will be done

2. Execute the operation in database
3. Commit: Mark log as committed

Log contains: Transaction ID, Operation (insert/update/delete), Old Value, New Value, Timestamp

Example log:

[T1 START]
[T1 UPDATE Student SET age=20 WHERE id=101, Old=19]
[T1 COMMIT]

Advantage: ✓ Can redo committed transactions if lost ✓ Can undo uncommitted transactions if failed
 Log-Based Recovery Process (After Crash)
1. Redo Phase: Replay all committed transactions from log → Recovers data that was in memory but not saved to
disk

2. Undo Phase: Rollback all uncommitted transactions → Removes partial changes from failed transactions

Example:

Before crash: T1 committed, T2 uncommitted, T3 running

After restart:
✓ Redo T1 (committed)
✓ Redo T2? (Check if committed in log - NO) → Skip
✓ Undo T3 (running, incomplete)

Shadow Paging
Concept: Make complete copy, work on copy, switch when ready

Process:

1. Current Page Table: Points to current database version

2. Shadow Page Table: Copy of page table
3. Write to shadow pages (original untouched)
4. If successful: Swap current ← shadow
5. If failed: Discard shadow pages

Advantage: ✓ No need for undo (original unchanged) ✓ Atomic switch (either all or nothing)

Disadvantage: ✗ Uses lots of disk space (copying entire pages) ✗ Garbage collection needed for old pages

Comparison
Log-Based: Small log overhead, fast writing, redo/undo needed
Shadow Paging: Large space overhead, slower (copy everything), simpler recovery

Q26: Deadlock Problem and Two-Phase

Lock Protocol
What is Deadlock?
Deadlock occurs when transactions wait for each other in circular manner, neither can proceed.
 Road analogy: Two cars facing each other, both waiting for other to move

Example:

T1: Locks A, wants B

T2: Locks B, wants A

T1 waits for T2 to release B

T2 waits for T1 to release A
Neither proceeds → DEADLOCK

Deadlock Example

Initial: A=100, B=100

T1: Read(A), Compute (5% interest), Write(A)
T2: Read(B), Compute (5% interest), Write(B)

Sequence (Deadlock):
T1: Lock A (success)
T2: Lock B (success)
T1: Lock B (waits for T2)
T2: Lock A (waits for T1)
→ DEADLOCK!

Circular dependency: T1→T2→T1

Two-Phase Lock Protocol (2PL)

Solution to prevent deadlock:

Rule: Transaction must follow two phases:

1. Growing Phase: Can only ACQUIRE locks (no releases)

2. Shrinking Phase: Can only RELEASE locks (no new locks)

Example with 2PL:

T1:
Lock A (growing)
Lock B (growing)
Read A, B
Perform calculation
Write A, B
Release A (shrinking)
Release B

T2:
Waits for T1 to release B
Once T1 releases, can proceed
NO DEADLOCK!

Types of 2PL
1. Strict 2PL: Release all locks only at END of transaction

✓ Safest, prevents cascading rollbacks

✗ Longest lock duration

2. Rigorous 2PL: Release write locks at end, read locks anytime

✓ Balance between safety and performance

3. Conservative 2PL: Acquire ALL locks before starting transaction

✓ No deadlock possible
✗ Need to know what locks needed in advance

Why 2PL Works

By enforcing strict lock order and preventing interleaving, creates serializable schedules and prevents deadlocks.

Q27: Recovery Methods - Log-Based

Recovery
Two Main Recovery Methods
1. Deferred Database Modification
2. Immediate Database Modification
 1. Deferred Database Modification
Concept: Don't modify database until transaction commits

Process:

1. Transaction executes and updates only in memory/temporary storage

2. Write updates to log (with , markers)
3. Only after commit: Write to actual database (redo phase)

Log format:

<Tx Start>
<Tx, Data_Item, Old_Value, New_Value> ← Recorded but NOT applied
<Tx Commit>

Recovery after crash:

✓ Redo Phase: Apply all logged updates from committed transactions

✗ Undo Phase: Not needed (nothing changed in database yet)

Advantage: ✓ No undo phase needed ✓ Simple recovery process

Disadvantage: ✗ Must buffer all updates in memory (what if big transaction?) ✗ Uses system memory

2. Immediate Database Modification

Concept: Modify database immediately, but keep log for recovery

Process:

1. Before writing: Log <Tx, Item, Old_Value, New_Value>

2. Write to database immediately
3. After transaction: Log

Recovery after crash:

✓ Redo Phase: Redo committed transactions (already in DB but maybe lost from cache)
✓ Undo Phase: Undo uncommitted transactions (remove partial changes)

Example:
T1: <T1 Start>
<T1, A, 100, 110> → Write A=110
<T1, B, 50, 55> → Write B=55
<T1 Commit>

If crash BEFORE <T1 Commit>:

Undo Phase: Reverse to A=100, B=50

If crash AFTER <T1 Commit>:

Redo Phase: Ensure A=110, B=55

Advantage: ✓ No memory buffering ✓ Works for large transactions

Disadvantage: ✗ More complex (need both redo and undo)

Checkpoint Optimization
Problem: After crash, must scan entire log (slow)

Solution: Checkpoint

Process:

1. Periodically pause transactions

2. Flush all dirty pages to disk
3. Log
4. Resume transactions

After crash, only process log AFTER last checkpoint (faster!)

Q28: Time-Stamp Based Protocol for

Concurrency Control
What is Time-Stamp?
A unique identifier assigned to each transaction based on when it started.

T1 starts at 10:00 → TS(T1) = 10:00

T2 starts at 10:05 → TS(T2) = 10:05
TS(T1) < TS(T2) → T1 is older

Time-Stamp Ordering Rule (TSO)

 Rule: If TS(Ti) < TS(Tj), then operations of Ti must appear before Tj

Example:

T1 (TS=100): Read(X)
T2 (TS=200): Write(X)
T3 (TS=300): Read(X)

Must execute in order: T1 → T2 → T3 (respecting timestamps)

How Protocol Works

Each data item has:

R-TS(X) = Read time-stamp (latest transaction to read X)

W-TS(X) = Write time-stamp (latest transaction to write X)

When transaction Ti tries operation on X:

If Ti wants READ(X):

IF TS(Ti) < W-TS(X)

→ X was written by newer transaction (violates order)
→ Reject and rollback Ti
ELSE
→ Allow, Update R-TS(X) = max(R-TS(X), TS(Ti))

If Ti wants WRITE(X):

IF TS(Ti) < R-TS(X) OR TS(Ti) < W-TS(X)

→ X was read/written by newer transaction (violates order)
→ Reject and rollback Ti
ELSE
→ Allow, Update W-TS(X) = TS(Ti)

Example Execution

T1 (TS=100): Read(X) → R-TS(X)=100 ✓

T2 (TS=200): Write(X) → W-TS(X)=200 ✓
T1 (TS=100): Write(Y) → W-TS(Y)=100 ✓
T3 (TS=300): Read(X) → TS(T3)=300 > W-TS(X)=200 ✓
T2 (TS=200): Read(Y) → TS(T2)=200 > W-TS(Y)=100 ✓ (gets updated value)
 Advantage & Disadvantage
Advantage: ✓ No deadlock (no waiting/locking) ✓ Strict ordering maintained

Disadvantage: ✗ May rollback many transactions ✗ Cascading aborts (one rollback causes others) ✗ Phantom problem
(newly inserted records)

Q29: Deadlock Scenario and Resolution

Given Deadlock Scenario

T31:
read(A);
read(B);
if A = 0 then B:=B+1;
Write(B)

T32:
read(B);
read(A);
if B= 0 then A: = A+1;
write(A).

How Deadlock Occurs

Without 2PL (no locks):

T31: Read(A) → a=5 ✓

T32: Read(B) → b=10 ✓
T31: Read(B) → b=10 ✓
T32: Read(A) → a=5 ✓
T31: A≠0, so Write(B) → b=10 ✓
T32: B≠0, so Write(A) → a=5 ✓
No problem here!

With locks (causing deadlock):

T31: Lock(A) ✓
T32: Lock(B) ✓
T31: Read(A), now wants Lock(B) → waits for T32
T32: Read(B), now wants Lock(A) → waits for T31
CIRCULAR DEPENDENCY → DEADLOCK!

Solution with 2-Phase Locking

Add locks and unlocks to enforce 2PL:

T31:
Lock(A);
Lock(B);
read(A);
read(B);
if A = 0 then B:=B+1;
Write(B);
Unlock(B);
Unlock(A);

T32:
Lock(B); ← T31 has A,B locked
wait... ← T32 waits
Lock(A);
read(B);
read(A);
if B= 0 then A: = A+1;
write(A);
Unlock(A);
Unlock(B);

Execution with 2PL

T31: Lock(A) ✓
T31: Lock(B) ✓
T32: Lock(B) → waits (T31 has it)
T31: reads and writes
T31: Unlock(B)
T31: Unlock(A)
T32: Lock(B) ✓ (now available)
T32: Lock(A) ✓
T32: reads and writes
T32: Unlock(A)
T32: Unlock(B)

Sequential execution → NO DEADLOCK ✓

Q30: Two-Phase Lock Protocol - Strict and

Rigorous Versions
Two-Phase Lock Protocol Overview
All locks acquired before any release (Growing → Shrinking) Prevents deadlock through ordered lock acquisition

Strict 2PL (Strict Two-Phase Locking)

Rule: ALL locks held until transaction commits

Process:

1. Growing Phase: Acquire all needed locks

2. Active Phase: Execute operations (hold all locks)
3. Shrinking Phase: Release ALL locks at once (at commit)

Transaction T1:

Lock A
Lock B
Lock C
read/write operations using A, B, C
Commit ← Release A, B, C here (all at once)
End

Advantage: ✓ No cascading rollbacks (dirty reads prevented) ✓ Strict order maintained ✓ Very safe
 Disadvantage: ✗ Longest lock duration (locks held until end) ✗ Lower concurrency (other transactions wait longer)

Rigorous 2PL (Rigorous Two-Phase Locking)

Rule: Write locks held until commit; Read locks can be released anytime

Process:

1. Growing Phase: Acquire all locks

2. Active Phase: Execute operations
3. Shrinking Phase:
Can release read locks any time
Hold write locks until commit

Transaction T1:

Lock A (write)
Lock B (read)
read/write operations
Unlock B (can release now)
... more operations (keeping A locked)
Commit ← Release A here
End

Advantage: ✓ Better concurrency (read locks released early) ✓ Safer than basic 2PL ✓ No cascading rollbacks

Disadvantage: ✗ Complex to implement (different handling for read/write) ✗ Still prevents deadlock but less than strict

Comparison Summary
Basic 2PL: Release after growing phase (high concurrency, deadlock possible)
Strict 2PL: Release all at commit (safe, low concurrency)
Rigorous 2PL: Write-lock at end, read early (balanced approach)

Q31: R-Timestamp and W-Timestamp in

Time-Stamp Protocol
R-Timestamp (Read Timestamp)
R-TS(X) = The time-stamp of the LATEST transaction that successfully READ data item X.

Meaning: The most recent transaction to read this data.

 Example:

T100: Read(X) → R-TS(X) = 100

T200: Read(X) → R-TS(X) = 200 (updated)
T150: Read(X) → R-TS(X) = 200 (unchanged, T150 is older than T200)

W-Timestamp (Write Timestamp)

W-TS(X) = The time-stamp of the LATEST transaction that successfully WROTE to data item X.

Meaning: The most recent transaction to modify this data.

Example:

T100: Write(X) → W-TS(X) = 100

T200: Write(X) → W-TS(X) = 200 (updated)
T150: Write(X) → But 150 < 200? Would be rejected!

Condition for READ Operation

Transaction Ti wants to READ(X) where TS(Ti) is its timestamp:

Condition: TS(Ti) ≥ W-TS(X)

Logic: Can read only if Ti is NEWER than or equal to the writer If Ti is OLDER than writer → Writer wrote newer data →
Can't read

Example:

W-TS(X) = 100 (last writer was T100)

T50 wants Read(X) → 50 < 100 → REJECT (T50 is too old)
T150 wants Read(X) → 150 > 100 → ACCEPT (T150 is newer)

If accepted:
R-TS(X) = max(R-TS(X), TS(Ti))

Condition for WRITE Operation

Transaction Ti wants to WRITE(X) where TS(Ti) is its timestamp:

Condition: TS(Ti) ≥ R-TS(X) AND TS(Ti) ≥ W-TS(X)

Logic: Can write only if Ti is NEWER than all readers and writers
 Example:

R-TS(X) = 150 (last reader was T150)

W-TS(X) = 100 (last writer was T100)
T140 wants Write(X) → 140 < 150 → REJECT (T150 already read, can't change)
T160 wants Write(X) → 160 > 150 AND 160 > 100 → ACCEPT

If accepted:
W-TS(X) = TS(Ti)

Thomas Write Rule (Optimization)

Modification to handle writes:

If TS(Ti) < W-TS(X) and TS(Ti) ≥ R-TS(X):

→ Ignore the write (it would be overwritten anyway)
→ Don't abort

Example:

W-TS(X) = 200
R-TS(X) = 150
T160 wants to Write(X) → 160 < 200
→ T160's write is already stale (T200 will write after)
→ Safely ignore, don't abort T160

Benefit: Reduces cascading aborts

Q32: Types of Data - Structured, Semi-

Structured, Unstructured
1. Structured Data
Data organized in fixed format with clear structure.

Characteristics: ✓ Organized in tables/rows/columns ✓ Fixed schema (predefined structure) ✓ Easy to search and
analyze ✓ Stored in RDBMS

Examples:
 Employee database: EmpID, Name, Salary, Department
Bank accounts: AccountNo, Balance, Type, CustomerID
Student records: RollNo, Name, CGPA, Branch

Advantages: ✓ Efficient storage ✓ Easy to query with SQL ✓ Data integrity enforced ✓ Fast access

Disadvantages: ✗ Rigid (hard to change structure) ✗ May not fit complex data

2. Semi-Structured Data
Data that has some structure but not rigid.

Characteristics: ✓ No fixed schema ✓ Self-describing ✓ Tags/keys explain the data ✓ Flexible structure

Examples:

XML: <Student><RollNo>101</RollNo><Name>Ram</Name></Student>
JSON: {"rollno":101, "name":"Ram", "marks":[80,85,90]}
HTML: <p>Content here</p>

Advantages: ✓ Flexible ✓ Can store different types of data ✓ Human-readable

Disadvantages: ✗ More storage space (includes tags) ✗ Slower queries than structured ✗ Less standardized

3. Unstructured Data
Data with no predefined structure.

Characteristics: ✓ No organization ✓ No predefined format ✓ Hard to parse ✓ Often stored as files

Examples:

Text documents: Word files, PDFs, emails

Images: Photos, screenshots
Videos: Movie files, streaming
Audio: Songs, voice recordings
Social media: Twitter posts, Facebook comments

Advantages: ✓ Can store any type of data ✓ Natural representation

Disadvantages: ✗ Hard to search ✗ Difficult to analyze ✗ Requires specialized tools (NLP, ML) ✗ Storage intensive

Comparison Table

STRUCTURED: Fixed schema, SQL queries, RDBMS, fast, organized

SEMI-STRUCTURED: Flexible schema, XML/JSON, flexible, moderate speed
UNSTRUCTURED: No schema, full-text search, slow, hard to analyze
 Q33: BASE Transactions and Soft State
with Eventual Consistency
What is BASE?
BASE is alternative to ACID for distributed/NoSQL databases

B = Basically Available
A = Soft state
E = Eventual Consistency

Used when: CAP theorem says you can't have all three (Consistency, Availability, Partition-tolerance) Prioritizes:
Availability & Partition-tolerance over Consistency

B - Basically Available
System guarantees availability even during failures.

Meaning: Even if some parts fail, system continues to function. May not be 100% consistent, but continues serving
requests.

Example: Facebook: Some servers down, but you can still use Facebook Database might not have latest data from
failed server, but system works

Contrast with ACID:

ACID: Sacrifice availability for consistency (strict)

BASE: Sacrifice consistency for availability (relaxed)

A - Soft State
Data state can change even without new requests (due to internal updates).

Meaning: Data is not guaranteed to be in consistent state at all times. Internal processes may be updating data in
background.

Example:

Google Search: Your search result rankings change daily (background updates)
Your email: Spam detection updates happen in background
You didn't request this, but state changed internally

Contrast with Consistency:

 ACID Consistency: State only changes with explicit operations
BASE Soft State: State may change from internal processes

E - Eventual Consistency
System will become consistent EVENTUALLY, not immediately.

Meaning: After write operation, you may not immediately read latest value. But after some time (seconds/minutes),
everyone sees same value.

Example:

You post on Instagram → Your follower doesn't see immediately (30 sec delay)
But after some time → Everyone sees your post

Timeline:

You: Write post at T0

Friend1: Reads at T1 (may not see → stale read)
Friend2: Reads at T2 (definitely sees → eventually consistent)

Contrast with ACID:

ACID: Immediate consistency (everyone sees immediately)

BASE: Delayed consistency (becomes consistent over time)

How BASE Works in Practice

Scenario: Bank transfers in distributed system

ACID (Traditional):

You: Withdraw from Account A → Locked

Friend: Can't access Account A until transfer complete → Blocks

BASE (NoSQL):

You: Withdraw from A (local update)

Friend: Can immediately access A (sees stale data initially)
System: Synchronizes in background
Eventually: Everyone consistent

Benefit: ✓ Much faster (no waiting) ✓ More scalable (no global locks) ✗ Temporary inconsistency
 Q34: Difference Between RDBMS and
NoSQL
RDBMS (Relational Database Management System)
Traditional databases like Oracle, MySQL, PostgreSQL, SQL Server

Characteristics:

Tables with rows and columns

Fixed schema (must define before storing)
Relationships between tables (Foreign Keys)
SQL for querying
ACID transactions
Vertical scaling (bigger server)

Example:

Student(StudentID, Name, Email, Department)

Department(DeptID, DeptName, Location)

NoSQL (Not Only SQL) Databases

New generation databases like MongoDB, Redis, Cassandra, DynamoDB

Characteristics:

No fixed schema (flexible)

Document/Key-Value/Graph storage
Limited joins
No SQL (use own query language)
Eventual consistency (not ACID)
Horizontal scaling (more servers)

Example:

{StudentID: 101, Name: "Ram", Email: "ram@[Link]", Department: "CS"}

Key Differences Table

 RDBMS:
✓ Schema: Fixed
✓ Scalability: Vertical (harder)
✓ Consistency: ACID (immediate)
✓ Transactions: Full ACID support
✓ Complex Queries: Easy (joins)
✓ Storage: Row-based
✓ Speed: Good for complex queries

NoSQL:
✓ Schema: Flexible
✓ Scalability: Horizontal (easy)
✓ Consistency: BASE (eventual)
✓ Transactions: Limited
✓ Complex Queries: Hard (no joins)
✓ Storage: Document/Key-value
✓ Speed: Very fast simple queries

When to Use RDBMS

✓ Structured data ✓ Complex relationships ✓ Require ACID transactions ✓ Fixed schema ✓ Complex queries Example:
Banking, Airlines, HR systems

When to Use NoSQL

✓ Unstructured/semi-structured data ✓ Rapid development (no schema definition) ✓ Massive scalability needed ✓
Simple queries ✓ High availability more important than consistency Example: Social media, Big Data, Real-time
analytics, Web applications

Q35: Document-Based and Key-Value

Data Models in NoSQL
1. Document-Based Data Model
Data stored as documents (usually JSON/BSON format)

Example Database: MongoDB, CouchDB, Firebase

Structure: Each record is a document with flexible fields

Example:
Database: Student
Document 1:
{
"_id": ObjectId("507f1f77bcf86cd799439011"),
"name": "Ram",
"email": "ram@[Link]",
"age": 20,
"courses": ["Math", "Physics", "Chemistry"],
"address": {
"city": "Mumbai",
"zip": "400001"
}
}

Document 2:
{
"_id": ObjectId("507f1f77bcf86cd799439012"),
"name": "Shyam",
"email": "shyam@[Link]",
"age": 21,
"gpa": 3.8
// no "courses" field - flexible!
}

Advantages: ✓ Flexible schema (different docs different fields) ✓ Nested structure (arrays, objects inside) ✓ Easy to
represent complex data ✓ Works well for semi-structured data

Queries:

[Link]({name: "Ram"})
[Link]({"[Link]": "Mumbai"})

2. Key-Value Data Model

Data stored as key-value pairs

Example Database: Redis, Memcached, DynamoDB

Structure: Every piece of data has a KEY and VALUE

Example:
 KEY → VALUE
"student_101" → {"name": "Ram", "email": "ram@[Link]"}
"student_102" → {"name": "Shyam", "email": "shyam@[Link]"}
"course_1" → "Mathematics"
"course_2" → "Physics"

Or using Redis:
SET student:101:name "Ram"
SET student:101:email "ram@[Link]"
GET student:101:name → Returns "Ram"

Advantages: ✓ Very fast (direct access via key) ✓ Simple structure (easy to understand) ✓ Great for caching ✓ Highly
scalable

Disadvantages: ✗ No query language (only key-based access) ✗ Hard to find by value (must know key) ✗ Limited
relationships

Comparison

DOCUMENT:
✓ Better for complex/nested data
✓ Can query by any field
✓ Flexible structure
✗ Slightly slower than key-value

KEY-VALUE:
✓ Lightning fast
✓ Perfect for caching
✓ Simple
✗ Limited query capability
✗ Must know key to retrieve

Q36: Significance of XML Databases and

When to Use
What is XML Database?
A database specifically designed to store and query XML documents.

Examples: eXist-db, MarkLogic, BaseX

 Instead of relational tables, stores XML documents

Example XML Data

<?xml version="1.0"?>
<library>
<book>
<title>Database Design</title>
<author>Navathe</author>
<isbn>978-0-13-459402-5</isbn>
<price>2500</price>
</book>
<book>
<title>SQL Guide</title>
<author>Kulkarni</author>
<isbn>978-81-7525-868-7</isbn>
<price>1800</price>
</book>
</library>

Advantages of XML Databases

✓ Natural representation of hierarchical data ✓ Flexible structure (different elements in different records) ✓ Supports
namespaces (organize related elements) ✓ Can store meta-data ✓ Good for document-oriented apps ✓ XPath and
XQuery for querying ✓ Self-documenting (tags explain data)

Disadvantages
✗ Verbose (tags add size) ✗ Not ideal for highly structured tabular data ✗ Less mature than RDBMS ✗ Slower than
relational for simple queries ✗ Learning curve for XQuery

When to Use XML Database

✓ Document management systems (PDFs, contracts) ✓ Content management systems (news, blogs) ✓ Configuration
files and settings ✓ Semi-structured data (different documents different structure) ✓ Scientific data with complex
hierarchies ✓ Medical records (variable fields per patient) ✓ Integration hub (combining data from multiple sources) ✓
When data is naturally hierarchical

DO NOT USE: ✗ Simple relational data (use RDBMS) ✗ Massive structured data (use NoSQL) ✗ When performance is
critical for simple queries
 Q37: Replication and Sharding in
MongoDB
What is Replication?
Creating copies of data across multiple servers for reliability and availability.

Goal: ✓ High availability (if one server down, others serve) ✓ Disaster recovery (backup if data lost) ✓ Read scaling
(distribute read load)

Example:

Original Server: Contains all student data

Replica 1: Copy of all data
Replica 2: Copy of all data

If Original fails → Replica automatically takes over

How MongoDB Replication Works

MongoDB uses Replica Sets:

1 PRIMARY: Master server (handles all writes)

3 SECONDARY: Copies of data (read-only)

Process:

1. Write to PRIMARY
2. PRIMARY logs change to oplog
3. SECONDARY servers read oplog
4. SECONDARY apply changes to their copies

Example:

PRIMARY: INSERT student {id: 101, name: "Ram"}

→ Logged in oplog
SECONDARY-1: Reads oplog, inserts in its copy
SECONDARY-2: Reads oplog, inserts in its copy

Now all 3 servers have identical data

Advantages of Replication
✓ High availability (automatic failover) ✓ Read scaling (read from secondaries) ✓ Disaster recovery (restore from replica)
✓ Maintenance (upgrade one replica at a time)

What is Sharding?
Distributing data across multiple servers by KEY RANGES.

Goal: ✓ Horizontal scaling (add more servers → more capacity) ✓ Handle large data (no single server can store all) ✓
Write scaling (writes distributed across shards)

Example:

Student table: 1 million records

Without Sharding: One server has ALL 1 million records

With Sharding:
Shard-1: Students with RollNo 1-250k (250k records)
Shard-2: Students with RollNo 250k-500k (250k records)
Shard-3: Students with RollNo 500k-750k (250k records)
Shard-4: Students with RollNo 750k-1M (250k records)

How MongoDB Sharding Works

1. Choose Shard Key: Field to distribute data (e.g., StudentID)
2. MongoDB divides data into chunks based on key ranges
3. Config Server: Maintains mapping of key ranges to shards
4. Mongos Router: Routes queries to correct shard

Example Query:

Find student with RollNo=600k

→ Mongos checks config server
→ 600k falls in Shard-3 range
→ Mongos routes to Shard-3
→ Get data from Shard-3

Insert new student:

→ Mongos calculates shard based on RollNo
→ Inserts in correct shard

Replication vs Sharding
REPLICATION:
✓ Copies of same data
✓ Vertical scaling (reliability)
✓ All servers have full data
✓ Automatic failover

SHARDING:
✓ Different data on each server
✓ Horizontal scaling (capacity)
✓ Each server has portion
✓ Handle very large datasets

Q38: CRUD Operations in MongoDB

CRUD Definition
C = Create (INSERT)
R = Read (FIND)
U = Update (UPDATE)
D = Delete (DELETE)

Basic operations to manage data in MongoDB

CREATE (Insert Documents)

Insert single document:

[Link]({
_id: 101,
name: "Ram",
email: "ram@[Link]",
age: 20,
courses: ["Math", "Physics"]
});

Insert multiple documents:

[Link]([
{_id: 102, name: "Shyam", age: 21},
{_id: 103, name: "Hari", age: 20}
]);

If _id not provided, MongoDB generates unique ObjectId

READ (Query Documents)

Find all documents:

[Link]();

Find specific document:

[Link]({name: "Ram"});

Find with conditions:

[Link]({age: {$gt: 20}}); // age greater than 20

Find specific fields:

[Link]({}, {name: 1, email: 1});

// Returns only name and email

Find with AND:

[Link]({name: "Ram", age: 20});

Find with OR:

[Link]({$or: [{name: "Ram"}, {name: "Shyam"}]});

UPDATE (Modify Documents)

Update single document:
[Link](
{name: "Ram"}, // filter
{$set: {age: 21, email: "[Link]@[Link]"}}
);

Update multiple documents:

[Link](
{age: 20},
{$set: {status: "Senior"}}
);

Increase field value:

[Link](
{_id: 101},
{$inc: {age: 1}} // increase age by 1
);

Push to array:

[Link](
{_id: 101},
{$push: {courses: "Chemistry"}}
);

DELETE (Remove Documents)

Delete single document:

[Link]({name: "Ram"});

Delete multiple documents:

[Link]({age: {$lt: 18}});

Delete all documents:

[Link]({});
 Delete collection:

[Link]();

Q39: Emerging Database Technologies

1. Cloud Databases
Databases hosted on cloud infrastructure

Examples: AWS RDS, Google Cloud SQL, Azure Database, Firebase

Characteristics: ✓ Scalable (grow/shrink as needed) ✓ Managed (vendor handles updates/backups) ✓ Pay-as-you-go

(pay for what you use) ✓ High availability (distributed) ✓ Automatic backups

Advantages: ✓ No hardware maintenance ✓ Auto-scaling ✓ Global distribution ✗ Network latency ✗ Vendor lock-in

2. Mobile Databases
Lightweight databases for mobile apps

Examples: SQLite, Realm, Room (Android)

Characteristics: ✓ Lightweight (small footprint) ✓ Fast (local device) ✓ Offline first (works without internet) ✓ Syncs
when online

Use Case: Mobile apps need offline functionality

Example:

WhatsApp: Uses Realm

Facebook: Uses SQLite
Works offline, syncs to server when reconnected

3. Graph Databases
Databases optimized for relationships

Examples: Neo4j, ArangoDB

Structure:

Nodes: Entities (Person, Movie, Product)

Edges: Relationships (knows, acted_in, bought)
Properties: Attributes on nodes/edges
 Example:

Node: Person(name: "Tom Hanks")

Edge: ACTED_IN
Node: Movie(name: "Forrest Gump")

Query: Find all movies Tom Hanks acted in

Advantages: ✓ Fast relationship queries ✓ Intuitive for connected data ✓ Great for social networks, recommendations

Use Cases:

Social networks (friend connections)

Recommendation engines (product suggestions)
Knowledge graphs (Wikipedia connections)

4. Time-Series Databases
Optimized for time-stamped data

Examples: InfluxDB, Prometheus, TimescaleDB

Characteristics: ✓ Stores data points with timestamps ✓ Optimized for quick inserts ✓ Aggregation queries fast ✓
Automatic data compression

Use Cases:

Stock prices (every second)

Sensor data (IoT devices)
Server metrics (CPU, memory)
Weather data (hourly readings)

5. Search Databases
Optimized for full-text search

Examples: Elasticsearch, Solr

Characteristics: ✓ Inverted indexes (fast text search) ✓ Advanced search (fuzzy, wildcards) ✓ Analytics on text ✓ Real-
time indexing

Use Cases:

Google-like search
Log analysis
E-commerce product search
Q40: NoSQL Data Models - Document
Store Example
Different NoSQL Data Models
1. Document Model (JSON/BSON)
2. Key-Value Model
3. Column-Family Model
4. Graph Model
5. Search Model

1. Document Model (Document Store)

Most popular, used by MongoDB, CouchDB, Firebase

Data Structure: JSON documents

Example: Student Management System

MongoDB Database: University
Collection: Students

[
{
"_id": ObjectId("507f1f77bcf86cd799439011"),
"studentId": 101,
"name": "Ram Kumar",
"email": "ram@[Link]",
"age": 20,
"department": "Computer Science",
"courses": [
{
"courseId": "CS101",
"courseName": "Data Structures",
"marks": 85,
"semester": 3
},
{
"courseId": "CS102",
"courseName": "Algorithms",
"marks": 78,
"semester": 3
}
],
"address": {
"street": "123 Main St",
"city": "Pune",
"zip": "411001",
"country": "India"
},
"enrollmentDate": ISODate("2022-08-15"),
"isActive": true,
"gpa": 3.65
},
{
"_id": ObjectId("507f1f77bcf86cd799439012"),
"studentId": 102,
"name": "Shyam Sharma",
"email": "shyam@[Link]",
"age": 21,
"department": "Electronics",
// Different structure - no courses/gpa yet
 // This flexibility is key to document stores!
}
]

Advantages of Document Model

✓ Flexible Schema: Different documents can have different fields ✓ Nested Data: Can store complex hierarchical data
naturally ✓ No Joins Needed: Related data stored together ✓ Developer Friendly: Matches object-oriented programming
✓ Easy Scaling: Sharding by document key simple ✓ Semi-Structured: Handles varying data naturally

Key Features Shown in Example

1. Nested Objects: address field is nested object
2. Arrays: courses is array of course objects
3. Mixed Types: numbers, strings, booleans, dates, objects
4. Flexible Fields: First student has gpa, second doesn't
5. Timestamps: enrollmentDate as ISO date
6. Unique ID: _id field unique per document

Queries on This Data

Find student by name:

[Link]({name: "Ram Kumar"});

Find students with GPA > 3.5:

[Link]({gpa: {$gt: 3.5}});

Find students in specific course:

[Link]({"[Link]": "CS101"});

Find active students in Computer Science:

[Link]({
isActive: true,
department: "Computer Science"
});

Update marks in a course:

 [Link](
{studentId: 101, "[Link]": "CS101"},
{$set: {"courses.$.marks": 90}}
);

STUDY TIPS FOR EXAM

1. Practice SQL queries - They often come in exam
2. Understand concepts deeply - Don't just memorize
3. Draw diagrams - ER diagrams, schema diagrams help
4. Revise normalization rules - Very important topic
5. Understand transaction concepts - ACID, recovery important
6. Study concurrency control - Locks, timestamps, conflicts
7. Do previous years papers - Get idea of question pattern
8. Make quick revision notes - One page for each major topic
9. Time management - Allocate time based on marks
10. Don't skip any question - Attempt all questions even if unsure

All the Best for Your Exam!

This comprehensive guide covers all 40 questions from your DBMS question bank with detailed explanations suitable for
15-mark answers.

Remember: Understanding concepts is more important than memorizing. Read through each answer carefully and try to
understand the "why" behind each concept.