DBMS Important Questions with Solutions: Complete Exam Prep

Posted on Oct 14, 2026 in Management Assistance

🔥 DBMS Most Important Questions with Solutions

1. Differentiate File Processing System and DBMS

Answer

Why DBMS was developed?

Main problems of file systems:

1. Data redundancy

2. Data inconsistency

3. Difficult data access

4. Data isolation

5. Integrity problems

6. Security problems

7. Concurrent-access problems

8. Recovery problems

⭐ Exam Line

DBMS overcomes the limitations of traditional file-processing systems by providing data independence, security, integrity, concurrency control, transaction management and recovery.

2. Explain Three-Schema Architecture

Answer

Three-schema architecture provides three levels of abstraction:

        USERS
          ↓
   External Level
          ↓
   Conceptual Level
          ↓
    Internal Level
          ↓
   Physical Storage

1. External Level

User’s individual view of database.

Example:

Student → Student details
Teacher → Marks/details
Accounts → Fee details

2. Conceptual Level

Complete logical structure of database.

Contains:

• Entities

• Attributes

• Relationships

• Constraints

• Tables

3. Internal Level

Describes physical storage.

Includes:

• Files

• Indexes

• Storage blocks

• Record organization

3. Explain Data Independence

Data independence means changing one level of database architecture without affecting higher levels.

Physical Data Independence

Change internal storage without changing conceptual schema.

Example:

Change file organization
        ↓
No change to tables

Logical Data Independence

Change conceptual schema without changing external views.

Example:

Add new attribute
        ↓
Existing user views continue working

⭐ Remember

Physical = Storage change
Logical = Structure change

Your PDF specifically covers physical and logical data independence.

4. Differentiate E-R Model and Relational Model

Example

ER:

STUDENT ---- ENROLLS ---- COURSE

Relational:

STUDENT(Student_ID, Name)

COURSE(Course_ID, Course_Name)

ENROLL(Student_ID, Course_ID)

5. Explain Cardinality / Mapping Cardinality

Mapping cardinality specifies how many entities of one set can be associated with entities of another set.

1:1

PERSON -------- PASSPORT
   1                1

One person has one passport.

1:N

DEPARTMENT -------- EMPLOYEE
     1                 N

One department has many employees.

M:N

STUDENT -------- COURSE
   M               N

A student can take many courses and a course can have many students.

The ER notes explicitly cover mapping cardinality and participation constraints.

6. Explain Participation Constraints

Participation tells whether participation in a relationship is mandatory or optional.

Total Participation

Every entity must participate.

Example:

Every employee must belong to a department.

Minimum cardinality: 1..1

Represented using a double line.

Partial Participation

Participation is optional.

Example:

A department may have no employees.

Minimum cardinality: 0..0

Represented using a single line.

7. Explain Weak Entity and Identifying Relationship

A weak entity does not have a complete key of its own.

It depends on an owner/strong entity.

Example:

EMPLOYEE -------- DEPENDENT

Dependent is weak.

Suppose:

Employee(E_No, E_Name)

Dependent(DName, Age)

Then:

Dependent(E_No, DName, Age)

Primary key: (E_No, DName)

Foreign key: E_No

The PDF specifically states that the owner’s primary key becomes a foreign key in the weak-entity relation, combined with the partial key.

UNIT 2 — RELATIONAL MODEL

8. Explain Relational Model Terminology

Relation

A table consisting of rows and columns.

Tuple

A row in a relation.

Attribute

A column.

Domain

Set of valid values of an attribute.

Degree

Number of attributes/columns.

Cardinality

Number of tuples/rows.

Intension

Structure/schema of relation.

Example:

STUDENT(RollNo, Name, Branch)

Degree: 3

If table has 50 rows: Cardinality = 50

9. Explain Types of Keys

Super Key

Any set of attributes that uniquely identifies a tuple.

Candidate Key

Minimal super key.

Primary Key

Candidate key selected to uniquely identify tuples.

Alternate Key

Candidate keys not selected as primary key.

Foreign Key

Attribute referencing the primary key of another relation.

10. Explain Integrity Constraints

The PDFs cover key, entity-integrity and referential-integrity constraints.

1. Domain Constraint

Values must belong to valid domain.

Example: Age > 0

2. Key Constraint

Key values must be unique.

3. Entity Integrity

Primary key cannot be NULL.

PK ≠ NULL

4. Referential Integrity

Foreign key must reference an existing primary-key value or be NULL where permitted.

Example:

Employee.DeptID
       ↓
Department.DeptID

11. ER Diagram → Relational Model

This is VERY IMPORTANT.

Strong Entity

For every strong entity, create one table.

Example:

STUDENT(RollNo, Name, Marks)

Primary key: RollNo

Weak Entity

Owner key + partial key.

EMPLOYEE(E_No, EName)

DEPENDENT(E_No, DName, Age)

PK: (E_No, DName)

FK: E_No

1:1 Relationship

Put foreign key in one of the participating relations, generally on the appropriate/total-participation side.

1:N Relationship

Put the primary key of the 1-side as foreign key in the N-side.

Example:

DEPARTMENT(DeptID, DeptName)

EMPLOYEE(EmpID, Name, DeptID)

M:N Relationship

Create a separate relation.

Example:

STUDENT(StudentID, ...)

COURSE(CourseID, ...)

ENROLL(StudentID, CourseID)

PK: (StudentID, CourseID)

The PDF specifically describes creating a new table for M:N relationships with the participating primary keys as foreign keys and their combination as the primary key.

🔥 12. Paper Question: Find Minimum Number of Tables from ER Diagram

For the diagram in your first paper:

Entities:

M
P
N

where N is a weak entity.

A suitable relational representation is:

P(P1, P2)

M(M1, M2, M3, P1)

N(P1, N1, N2)

Assuming N1 is the partial key:

Primary keys

P → P1

M → M1

N → (P1, N1)

Foreign keys

M.P1 → P.P1

N.P1 → P.P1

⭐ Exam Method

Always identify:

1. Strong entities

2. Weak entities

3. Relationship cardinality

4. Partial key

5. Foreign keys

6. Minimum number of tables

13. Candidate Keys + Number of Superkeys

Given: R(A, B, C, D, E)

Candidate keys: ABD, BCE

For candidate key ABD:

Remaining attributes: C, E

Number of supersets: 2² = 4

They are:

ABD
ABCD
ABDE
ABCDE

For BCE:

Remaining: A, D

Again: 2² = 4

But ABCDE is counted twice.

Therefore: 4 + 4 − 1 = 7

Answer

7 superkeys

UNIT 3 — SQL

Your SQL PDF explicitly covers DDL, DML, queries, set operations, NULL, aggregate functions, subqueries and database modification.

14. Explain CREATE, INSERT, DELETE, TRUNCATE, COMMIT and ROLLBACK

CREATE

CREATE TABLE Student
(
    RollNo INT PRIMARY KEY,
    Name VARCHAR(30),
    Marks INT
);

INSERT

INSERT INTO Student
VALUES (101, 'Rahul', 85);

DELETE

DELETE FROM Student
WHERE RollNo = 101;

TRUNCATE

TRUNCATE TABLE Student;

Deletes all rows while retaining table structure.

COMMIT

COMMIT;

Permanently saves transaction.

ROLLBACK

ROLLBACK;

Undoes uncommitted changes.

15. SQL Integrity Constraints

CREATE TABLE Student
(
    RollNo INT PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Email VARCHAR(50) UNIQUE,
    Age INT CHECK (Age >= 17),
    DeptID INT
);

Important:

PRIMARY KEY → unique + NOT NULL
UNIQUE      → duplicate values not allowed
NOT NULL    → NULL not allowed
CHECK       → condition must be satisfied
FOREIGN KEY → references another table

16. SELECT + WHERE

SELECT *
FROM Employee
WHERE Salary > 50000;

Only employees with salary greater than 50,000 are selected.

17. DISTINCT

SELECT DISTINCT DeptID
FROM Employee;

Removes duplicate department IDs.

18. ORDER BY

Ascending:

SELECT *
FROM Employee
ORDER BY Salary ASC;

Descending:

SELECT *
FROM Employee
ORDER BY Salary DESC;

19. GROUP BY + Aggregate

Find average salary of each department:

SELECT DeptID, AVG(Salary)
FROM Employee
GROUP BY DeptID;

Other aggregate functions:

COUNT()
SUM()
AVG()
MAX()
MIN()

20. HAVING

Find departments whose average salary is greater than 50,000:

SELECT DeptID, AVG(Salary)
FROM Employee
GROUP BY DeptID
HAVING AVG(Salary) > 50000;

VERY IMPORTANT

WHERE  → rows
HAVING → groups

21. SQL Queries from Your Paper

Given:

Emp(Eid, Ename, Salary, Address, Dept_id, Age)

Dept(Dept_id, Dname, Address)

a) Employees from BHOPAL

SELECT Ename
FROM Emp
WHERE Address = 'BHOPAL';

b) Employees working in CSE

SELECT E.Ename
FROM Emp E
JOIN Dept D
ON E.Dept_id = D.Dept_id
WHERE D.Dname = 'CSE';

c) Employee IDs where age > 30

SELECT Eid
FROM Emp
WHERE Age > 30;

d) Employee address same as department address

SELECT E.Ename
FROM Emp E
JOIN Dept D
ON E.Dept_id = D.Dept_id
WHERE E.Address = D.Address;

e) Average salary of each department

SELECT Dept_id, AVG(Salary)
FROM Emp
GROUP BY Dept_id;

f) Employees with salary > 50,000

SELECT E.Ename, D.Dname
FROM Emp E
JOIN Dept D
ON E.Dept_id = D.Dept_id
WHERE E.Salary > 50000;

22. Employee–Company SQL Questions

Given:

Employee(employee_name, street, city)

Works(employee_name, company_name, salary)

Company(company_name, city)

Manages(employee_name, manager_name)

a) Employees working for State Bank

SELECT employee_name
FROM Works
WHERE company_name = 'State Bank';

b) Names and cities of employees working for HDFC Bank

SELECT E.employee_name, E.city
FROM Employee E
JOIN Works W
ON E.employee_name = W.employee_name
WHERE W.company_name = 'HDFC Bank';

c) Employees not working for State Bank or ICICI Bank

SELECT employee_name
FROM Works
WHERE company_name NOT IN ('State Bank', 'ICICI Bank');

d) Employees living in same city where their company is located

SELECT E.employee_name
FROM Employee E
JOIN Works W
ON E.employee_name = W.employee_name
JOIN Company C
ON W.company_name = C.company_name
WHERE E.city = C.city;

e) Employee name and salary whose manager is Ravi

SELECT W.employee_name, W.salary
FROM Works W
JOIN Manages M
ON W.employee_name = M.employee_name
WHERE M.manager_name = 'Ravi';

f) Highest salary in each company

SELECT company_name, MAX(salary)
FROM Works
GROUP BY company_name;

UNIT 4 — RELATIONAL ALGEBRA

Your Unit-II Part-4 PDF contains relational algebra operations and examples including intersection, natural join, division and banking queries.

23. Six Basic Relational Algebra Operators

Additional operations:

Intersection
Join
Division
Assignment

24. Selection

Find employees with salary > 70,000:

σ_Salary>70000(Employee)

25. Projection

Find employee names:

π_EmpName(Employee)

26. Join

If:

Employee(EmpID, EmpName, DeptID)

Department(DeptID, DeptName)

then:

Employee ⋈ Department

or:

Employee ⋈_{Employee.DeptID = Department.DeptID} Department

27. Cartesian Product

Employee × Department

If Employee has 4 tuples and Department has 4 tuples:

4 × 4 = 16 tuples.

28. Relational Algebra Paper Question

Given Employee:

Department:

a) Employees with salary > 70,000

π_{EmpID, EmpName}( σ_Salary>70000(Employee) )

Result:

103 Charlie
104 Diana

b) Employees working in departments located in New York

π_EmpName( σ_{Location = ‘New York’} (Employee ⋈ Department) )

Result:

Alice

c) Employees working in IT but not HR

σ_{Department=’IT’}(Employee) − σ_{Department=’HR’}(Employee)

Result:

Bob
Charlie

d) Employee–department matching combinations

Employee ⋈_{Employee.Department = Department.DeptName} Department

Result:

Alice    HR
Bob      IT
Charlie  IT
Diana    Marketing

UNIT 5 — FUNCTIONAL DEPENDENCY

29. What is Functional Dependency?

Functional dependency:

X → Y

means X determines Y.

Example:

Student(RollNo, Name, Branch)

RollNo → Name

because one RollNo identifies one student.

Applications

1. Finding candidate keys

2. Normalization

3. Reducing redundancy

4. Preventing anomalies

5. Database design

30. Attribute Closure

Given:

A → B
BC → E
ED → A

Find: A⁺

Start: A⁺ = {A}

Using A → B: {A, B}

BC → E cannot be used because C is missing.

Therefore: A⁺ = {A, B}

31. Candidate Keys — Important Paper Question

Given: R(A, B, C, D, E)

FDs:

A → B
BC → E
ED → A

Attributes C and D do not appear on RHS.

Therefore every key must contain: CD

Try:

ACD

ACD

A → B: ABCD

BC → E: ABCDE

So: ACD

BCD

BC → E: BCDE

ED → A: ABCDE

So: BCD

CDE

ED → A: ACDE

A → B: ABCDE

So: CDE

Final:

ACD, BCD, CDE

32. BCNF Question

BCNF condition:

For every non-trivial FD X → Y, X must be a superkey.

Given: A → B

Calculate: A⁺ = {A, B}

Not all attributes.

Therefore A is not a superkey.

Hence: R is not in BCNF

Decompose using A → B into:

R₁(A, B) and R₂(A, C, D, E)

Then check remaining dependencies and decompose further if necessary.

UNIT 6 — TRANSACTION / SERIALIZABILITY

33. What are Conflicting Operations?

Two operations conflict when:

1. They belong to different transactions.

2. They access the same data item.

3. At least one operation is WRITE.

Conflicts

RW, WR, WW

No conflict

RR

34. Precedence Graph

Steps:

Step 1

Create one node for every transaction.

Step 2

Find conflicting operations.

Step 3

If Tᵢ’s conflicting operation occurs before Tⱼ: Tᵢ → Tⱼ

Step 4

Check cycle.

No cycle → Conflict Serializable
Cycle → Not Conflict Serializable

35. Paper Question: Determine Conflict Serializability

Schedule:

r₁(X); r₃(Y); r₃(X); r₂(Y); r₂(Z); w₃(Y); w₂(Z); r₁(Z); w₁(X); w₁(Z)

On X

r₃(X) → w₁(X)

Therefore: T₃ → T₁

On Y

r₂(Y) → w₃(Y)

Therefore: T₂ → T₃

On Z

w₂(Z) → r₁(Z)

Therefore: T₂ → T₁

Graph:

T2 ─────→ T3
│          │
│          ↓
└───────→ T1

No cycle.

Therefore: Schedule is conflict serializable

Equivalent serial order: T₂ → T₃ → T₁

36. Second Paper: Candidate Key Question

Given: R(A, B, C, D, E, H)

FDs:

A → B
BC → D
E → C
D → A

Because E and H never appear on RHS, every candidate key must contain: EH

Try:

AEH

AEH

A → B: ABEH

E → C: ABCEH

BC → D: ABCDEH

Therefore: AEH

BEH

E → C: BCEH

BC → D: BCDEH

D → A: ABCDEH

Therefore: BEH

DEH

D → A: ADEH

A → B: ABDEH

E → C: ABCDEH

Therefore: DEH

Final:

AEH, BEH, DEH

37. Second Paper: Relational Algebra Output

Given:

Employee

Salary

Employee ⋈ Salary

Employee × Salary

4 × 4:

16 rows

Projection

π_{EMP_NAME, SALARY}(Employee ⋈ Salary)

Result:

LEFT OUTER JOIN

Since every employee has a salary: