question bank :

UNIT - 1
1. Define Data and Information. Explain the need for
database systems.
Data
Data refers to raw facts and figures that do not have meaning by themselves.
Examples:
Numbers, names, marks, dates, etc.
Example:
85, Rahul, 20 → These are raw values without context.

Information
Information is processed or organized data that has meaning and is useful for
decision making.
Example:
“Rahul scored 85 marks in DBMS” → meaningful information.

Need for Database Systems

Traditional file systems created many problems. Database systems were
introduced to overcome them.
Main needs:
1. Data redundancy control – Avoid storing the same data multiple times.
2. Data consistency – Ensures same data everywhere.
3. Efficient data access – Fast retrieval using queries.

question bank : 1
 4. Data security – Authorized access only.
5. Data sharing – Multiple users can access data simultaneously.
6. Backup and recovery – Protection against data loss.
7. Integrity maintenance – Ensures correct and valid data.

2. Explain the limitations of the traditional file system

approach.
The traditional file system stores data in separate files managed by application
programs.

Limitations
1. Data redundancy and inconsistency
Same data stored in multiple files.
Updating one file may not update others.
2. Difficulty in accessing data
Requires writing new programs for every query.
3. Data isolation
Data scattered in different formats and files.
4. Integrity problems
Hard to enforce constraints.
5. Security problems
Limited access control.
6. Concurrency issues
Multiple users updating data causes conflicts.
7. Lack of backup and recovery
Data loss risk is high.

question bank : 2
 3. Compare File System and DBMS with suitable
examples.
Feature File System DBMS
Data storage Separate files Centralized database
Redundancy High Controlled
Data consistency Difficult Maintained
Security Low High
Data sharing Difficult Easy
Query processing Program dependent SQL based
Backup & recovery Limited Automatic support

Example
File System: Student details stored in separate files for library, exam, and
hostel.
DBMS: All data stored in a single database and shared by all departments.

4. Explain the major characteristics of a DBMS.

1. Self-describing system
Database contains metadata (data about data).
2. Program-data independence
Data structure changes do not affect programs.
3. Data abstraction
Hides internal details from users.
4. Data sharing
Multiple users can access data simultaneously.
5. Multiple views
Different users see different views.

question bank : 3
 6. Concurrency control
Manages simultaneous operations.
7. Security and authorization
Access control mechanisms.
8. Backup and recovery
Protects against failures.

5. Describe the advantages and disadvantages of

DBMS.
Advantages
1. Reduced data redundancy
2. Improved data consistency
3. Better data security
4. Data sharing and integration
5. Backup and recovery
6. Data integrity enforcement
7. Faster data access

Disadvantages
1. High cost of software and hardware
2. Complex system
3. Requires trained personnel
4. Performance overhead for small applications
5. Database failure affects entire system

question bank : 4
 6. Explain the three-tier DBMS architecture with a neat
diagram.
The three-tier architecture separates users from physical database storage.

Levels
1. External Level (View Level)
User view of data.
Different views for different users.
2. Conceptual Level (Logical Level)
Describes overall database structure.
Defines entities, relationships, constraints.
3. Internal Level (Physical Level)
Describes how data is stored physically.

Diagram (Exam Representation)

External Level
(User Views / Applications)
↓
Conceptual Level
(Logical Database Design)
↓
Internal Level
(Physical Storage)

7. What is data abstraction? Explain its levels.

Data Abstraction
Data abstraction is the process of hiding complex database details from users and
showing only required information.

question bank : 5
 Levels of Data Abstraction
1. Physical Level
Lowest level.
Describes how data is stored in memory.
2. Logical Level
Describes what data is stored and relationships.
3. View Level
Highest level.
Shows only required data to users.

8. Define data independence. Explain logical and

physical data independence.
Data Independence
The ability to change database schema without affecting application programs.

Types
1. Physical Data Independence
Ability to change physical storage without changing logical schema.
Example:
Changing indexing or storage method without affecting programs.

2. Logical Data Independence

Ability to change logical schema without affecting user views.
Example:
Adding a new attribute without changing existing applications.

question bank : 6
 9. Explain different types of data models used in
DBMS.
A data model defines how data is structured and organized.

Types of Data Models

1. Hierarchical Model
Tree structure.
One-to-many relationship.
2. Network Model
Graph structure.
Many-to-many relationships.
3. Relational Model
Data stored in tables (relations).
Most widely used.
4. Entity-Relationship (ER) Model
Uses entities and relationships.
Used for database design.
5. Object-Oriented Model
Stores data as objects.

10. Describe the hierarchical and network data models.

Hierarchical Data Model
Data organized in tree form.
Parent-child relationship.
Each child has only one parent.
Example:

question bank : 7
 Company → Department → Employee
Advantages
Simple structure
Fast data access
Disadvantages
Rigid structure
Difficult many-to-many relationships

Network Data Model

Data organized as a graph.
A child can have multiple parents.
Example:
Student can enroll in multiple courses.
Advantages
Supports many-to-many relationships
More flexible than hierarchical model
Disadvantages
Complex structure
Difficult to maintain

11. Explain the relational data model in detail.

The Relational Data Model was proposed by E.F. Codd in 1970. It represents data
in the form of tables (relations) consisting of rows and columns.

Basic Concepts
1. Relation (Table)
A table that stores data in rows and columns.

question bank : 8
 Example: STUDENT table.
2. Tuple (Row)
A single record in a table.
Example: One student’s details.
3. Attribute (Column)
A property of an entity.
Example: StudentID, Name, Age.
4. Domain
Set of allowed values for an attribute.
5. Degree
Number of attributes in a relation.
6. Cardinality
Number of tuples in a relation.

Properties of Relational Model

Data stored in tables.
Each cell contains atomic values.
No duplicate tuples.
Order of rows and columns is not important.
Each table has a primary key.

Advantages
Simple structure.
Easy data manipulation using SQL.
Reduced redundancy.
Data independence.

question bank : 9
 12. Define ER model. Explain entities and attributes
with examples.
ER Model
The Entity–Relationship (ER) Model is a conceptual data model used to design
databases by representing entities and relationships between them.

Entity
An entity is a real-world object that can be uniquely identified.
Examples: Student, Employee, Book.
Types:
Strong entity
Weak entity

Attribute
An attribute describes properties of an entity.
Example:
Student → StudentID, Name, Age, Address.

Example
Entity: STUDENT
Attributes: RollNo, Name, Department

13. Explain different types of attributes in ER modeling.

1. Simple Attribute
Cannot be divided further.
Example: Age.
2. Composite Attribute

question bank : 10
 Can be divided into sub-parts.
Example: Address → Street, City, PIN.
3. Single-valued Attribute
Has only one value.
Example: Date of Birth.
4. Multi-valued Attribute
Can have multiple values.
Example: Phone numbers.
5. Derived Attribute
Derived from other attributes.
Example: Age derived from DOB.
6. Key Attribute
Uniquely identifies an entity.
Example: StudentID.

14. Describe relationship types and their constraints in

ER model.
Relationship
A relationship represents association between entities.
Example: Student enrolls in Course.

Types of Relationships
1. One-to-One (1:1)
One entity related to one entity.
Example: Person – Passport.
2. One-to-Many (1:N)

question bank : 11
 One entity related to many entities.
Example: Department – Employees.
3. Many-to-Many (M:N)
Many entities related to many entities.
Example: Student – Course.

Relationship Constraints
Cardinality constraint
Participation constraint

15. Explain cardinality and participation constraints

with examples.
Cardinality Constraint
Specifies number of entities that can participate in a relationship.
Types:
1:1
1:N
M:N
Example: One teacher teaches many students.

Participation Constraint
Specifies whether participation is mandatory or optional.
1. Total Participation
Every entity must participate.
Example: Every employee works in a department.
2. Partial Participation

question bank : 12
 Participation is optional.
Example: Not every employee manages a project.

16. What is a weak entity? Explain with an example.

A weak entity is an entity that cannot be uniquely identified by its own attributes
and depends on another entity.

Characteristics
Does not have a primary key.
Depends on strong entity.
Has partial key.

Example
Employee (strong entity)
Dependent (weak entity)
Dependent cannot exist without employee.

17. Explain specialization and generalization in EER

model.
The Enhanced ER (EER) Model extends ER model with advanced concepts.

Specialization
Process of dividing a higher-level entity into lower-level entities based on
characteristics.
Example:
Employee → Manager, Engineer.

Generalization
Process of combining lower-level entities into a higher-level entity.

question bank : 13
 Example:
Car and Bike → Vehicle.

Advantages
Better representation of real-world data.
Reduces redundancy.

18. Differentiate between ER and EER models.

ER Model EER Model
Basic data model Extended ER model
Uses entities and relationships Includes specialization & generalization
Simple structure More complex
Suitable for simple databases Suitable for complex databases
No inheritance Supports inheritance

19. Draw and explain an ER diagram for a Library

Management System.
Entities
Book
Member
Librarian
Issue

Attributes
Book(BookID, Title, Author, Publisher)
Member(MemberID, Name, Address)

Relationships

question bank : 14
 Member borrows Book
Librarian issues Book

Diagram (Exam Representation)

MEMBER ---- borrows ---- BOOK
| |
MemberID BookID
Name Title

LIBRARIAN ---- issues ---- BOOK

Explanation
One member can borrow many books.
One book can be issued to one member at a time.

20. Draw and explain an ER diagram for a Banking

System.
Entities
Customer
Account
Loan
Branch

Attributes
Customer(CustomerID, Name, Address)
Account(AccountNo, Type, Balance)

Relationships
Customer owns Account

question bank : 15
 Branch maintains Account
Customer takes Loan

Diagram (Exam Representation)

CUSTOMER ---- owns ---- ACCOUNT ---- maintained by ---- BRANC
H
|
| takes
|
LOAN

Explanation
A customer can have multiple accounts.
A branch maintains many accounts.
Customers can take loans from banks.

UNIT - 2
1. Explain the basic concepts of the relational model.
The relational model represents data in the form of tables called relations. Each
relation consists of rows and columns.

Basic Concepts
1. Relation
A table containing data.
Example: STUDENT table.
2. Tuple
A row in a relation representing a single record.

question bank : 16
 3. Attribute
A column in a relation describing a property.
4. Domain
Set of allowed values for an attribute.
5. Degree
Number of attributes in a relation.
6. Cardinality
Number of tuples in a relation.
7. Relational Schema
Structure of a relation.
Example:

STUDENT(StudentID, Name, Department)

2. Define relation, tuple, attribute, domain, and degree.

1. Relation
A table used to store data.
2. Tuple
A single row in a relation.
3. Attribute
A column representing a property.
4. Domain
Set of valid values for an attribute.
5. Degree
Total number of attributes in a relation.
Example:

question bank : 17
 STUDENT(StudentID, Name, Age)

Degree = 3

3. Explain different types of keys in relational

databases.
Keys are used to uniquely identify records.

Types of Keys
1. Super Key
Set of attributes that uniquely identify a tuple.
2. Candidate Key
Minimal super key.
3. Primary Key
Selected candidate key used for identification.
4. Alternate Key
Candidate keys not chosen as primary key.
5. Foreign Key
Attribute that refers to primary key of another table.
6. Composite Key
Key made of more than one attribute.
Example:
STUDENT(StudentID, Email)

4. Describe integrity constraints in relational

databases.
Integrity constraints ensure accuracy and consistency of data.

question bank : 18
 Types
1. Domain Constraint
Attribute values must belong to a valid domain.
2. Entity Integrity Constraint
Primary key cannot be NULL.
3. Referential Integrity Constraint
Foreign key must match primary key value.
4. Key Constraint
No duplicate primary key values.

5. Explain entity integrity and referential integrity

constraints.
Entity Integrity
Ensures every table has a primary key.
Primary key values cannot be NULL.
Example:
StudentID must always have a value.

Referential Integrity
Ensures relationship consistency between tables.
Foreign key must refer to existing primary key.
Example:
DeptID in EMPLOYEE must exist in DEPARTMENT table.

6. What is a relational schema? Explain schema and

instance.

question bank : 19
 Relational Schema
The logical design or structure of a database.
Example:

STUDENT(StudentID, Name, Department)

Schema
Overall structure of database.
Does not change frequently.

Instance
Actual data stored in database at a particular time.
Changes frequently.

7. Explain relational algebra and its significance.

Relational algebra is a procedural query language used to retrieve data from
relational databases.
It specifies how to perform operations.

Basic Operations
Selection (σ)
Projection (π)
Union (∪)
Set Difference (−)
Cartesian Product (×)
Join (⨝)

Significance
Forms theoretical foundation of SQL.

question bank : 20
 Used in query optimization.
Helps in database design.

8. Explain selection and projection operations with

examples.
Selection (σ)
Selects rows that satisfy a condition.
Syntax:

σ condition (Relation)

Example:
Select students from CSE department:

σ Dept = 'CSE' (STUDENT)

Projection (π)
Selects specific columns.
Syntax:

π attribute (Relation)

Example:

π Name, Dept (STUDENT)

9. Explain union, set difference, and intersection

operations.
These are set operations in relational algebra.

question bank : 21
 Union (∪)
Combines tuples from two relations.
Condition:
Same number of attributes.
Same domains.
Example:

R ∪ S

Set Difference (−)

Returns tuples present in one relation but not in another.
Example:

R − S

Intersection (∩)
Returns common tuples in both relations.
Example:

R ∩ S

10. Explain Cartesian product and join operations.

Cartesian Product (×)
Combines every tuple of one relation with every tuple of another.
Example:
If R has 3 tuples and S has 4 tuples,
Result = 12 tuples.

question bank : 22
 R × S

Join Operation (⨝)

Combines related tuples based on a condition.
Types:
1. Theta Join
2. Equi Join
3. Natural Join
Example:

STUDENT ⨝ [Link] = [Link] DEPT

11. Explain different types of joins in relational algebra.

A join operation combines tuples from two relations based on a related attribute.

Types of Joins
1. Theta Join (θ-Join)
Join condition uses comparison operators (=, <, >, ≤, ≥, ≠).
Example:

R ⨝ R.A > S.B S

1.
2. Equi Join
Join condition uses equality (=) only.
Duplicate columns are retained.
3. Natural Join

question bank : 23
 Automatically joins relations based on common attributes.
Duplicate columns are removed.
4. Outer Join
Includes unmatched tuples.
Types:
Left Outer Join
Right Outer Join
Full Outer Join

12. Write relational algebra expressions for given

queries.
Relational algebra expressions are used to retrieve data using operators.

Examples
1. Find names of students in CSE department

π Name (σ Dept = 'CSE' (STUDENT))

1.
2. Find employee names with salary > 50000

π EmpName (σ Salary > 50000 (EMPLOYEE))

1.
2. Find students and their department names

STUDENT ⨝ [Link] = [Link] DEPARTMENT

13. Define tuple relational calculus with examples.

question bank : 24
 Tuple Relational Calculus (TRC) is a non-procedural query language where
queries specify what to retrieve, not how.
It uses tuple variables.

General Form
{ t | P(t) }

where
t = tuple variable
P(t) = condition

Example
Find names of students in CSE:

{ [Link] | STUDENT(t) AND [Link] = 'CSE' }

14. Explain domain relational calculus with examples.

Domain Relational Calculus (DRC) is a non-procedural query language that uses
domain variables (attribute values).

General Form
{ <x1, x2, … xn> | P(x1, x2, … xn) }

Example
Find names of students in CSE:

{ <Name> | <ID, Name, Dept> ∈ STUDENT AND Dept = 'CSE' }

15. Compare relational algebra and relational calculus.

question bank : 25
 Relational Algebra Relational Calculus
Procedural language Non-procedural language
Specifies how to retrieve data Specifies what to retrieve
Uses operations Uses logical expressions
Based on algebra Based on predicate logic
Foundation of query execution Foundation of query specification

16. Differentiate between TRC and DRC.

TRC DRC
Uses tuple variables Uses domain variables
Entire tuple considered Individual attributes considered
Easier to understand More mathematical
Example: [Link] Example:

17. Explain expressiveness of relational algebra.

Expressiveness refers to the ability of relational algebra to express different types
of queries.

Features
Can perform selection, projection, and joins.
Supports set operations.
Can retrieve complex query results.
Equivalent in expressive power to relational calculus.

Importance
Forms theoretical basis of SQL.
Used in query optimization.

question bank : 26
 18. Convert SQL queries into relational algebra
expressions.
Examples
1. SQL:

SELECT Name FROM STUDENT WHERE Dept='CSE';

Relational Algebra:

π Name (σ Dept='CSE' (STUDENT))

1. SQL:

SELECT * FROM EMPLOYEE WHERE Salary > 30000;

Relational Algebra:

σ Salary > 30000 (EMPLOYEE)

1. SQL:

SELECT [Link], [Link]

FROM STUDENT S, DEPARTMENT D
WHERE [Link] = [Link];

Relational Algebra:

π Name, DeptName (STUDENT ⨝ DEPARTMENT)

19. Write queries using relational calculus.

Example 1 (TRC)

question bank : 27
 Find names of students older than 20:

{ [Link] | STUDENT(t) AND [Link] > 20 }

Example 2 (DRC)
Find employee names with salary greater than 40000:

{ <Name> | <ID, Name, Salary> ∈ EMPLOYEE AND Salary > 40000 }

20. Explain the importance of query languages in

DBMS.
Query languages are used to interact with databases.

Importance
1. Easy data retrieval.
2. Data manipulation (insert, update, delete).
3. Reduces programming effort.
4. Ensures data independence.
5. Supports complex queries.
6. Helps in decision making.
7. Provides user-friendly interaction.

UNIT - 3
1. Explain the features and advantages of SQL.
SQL (Structured Query Language)
SQL is a standard language used to create, manage, and manipulate relational
databases.

question bank : 28
 Features of SQL
1. Simple and easy to learn.
2. Standardized language.
3. Supports data definition and manipulation.
4. Supports integrity constraints.
5. Provides security and authorization.
6. Supports complex queries.
7. Portable across database systems.

Advantages of SQL
Fast data retrieval.
Reduced programming effort.
Supports multiple users.
Ensures data consistency.
Easy database management.

2. Describe different SQL data types with examples.

SQL data types define the type of data stored in a column.

1. Numeric Data Types

INT
FLOAT
DECIMAL
Example:

Salary INT

2. Character/String Data Types

question bank : 29
 CHAR(n)
VARCHAR(n)
Example:

Name VARCHAR(50)

3. Date and Time Data Types

DATE
TIME
DATETIME
Example:

DOB DATE

4. Boolean Data Type

TRUE or FALSE values.

3. Explain DDL commands with syntax and examples.

DDL (Data Definition Language) commands are used to define database structure.

DDL Commands
1. CREATE
Creates database or table.

CREATE TABLE STUDENT(

StudentID INT,
Name VARCHAR(30)
);

1.

question bank : 30
 2. ALTER
Modifies table structure.

ALTER TABLE STUDENT ADD Age INT;

1.
2. DROP
Deletes table.

DROP TABLE STUDENT;

1.
2. TRUNCATE
Removes all records from table.

TRUNCATE TABLE STUDENT;

4. Explain CREATE TABLE command with constraints.

The CREATE TABLE command creates a new table along with constraints.

Syntax
CREATE TABLE TableName(
ColumnName DataType Constraint
);

Example
CREATE TABLE STUDENT(
StudentID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,

question bank : 31
 Age INT,
Dept VARCHAR(20)
);

Common Constraints
PRIMARY KEY
FOREIGN KEY
NOT NULL
UNIQUE
CHECK
DEFAULT

5. Explain PRIMARY KEY and FOREIGN KEY

constraints.
Primary Key
Uniquely identifies each record.
Cannot be NULL or duplicate.
Example:

StudentID INT PRIMARY KEY

Foreign Key
Refers to primary key of another table.
Maintains relationship between tables.
Example:

DeptID INT,
FOREIGN KEY (DeptID)

question bank : 32
 REFERENCES DEPARTMENT(DeptID)

6. Explain DML commands with suitable examples.

DML (Data Manipulation Language) commands are used to manipulate data.

Commands
1. INSERT

INSERT INTO STUDENT VALUES(1,'Rahul','CSE');

1. UPDATE

UPDATE STUDENT
SET Dept='IT'
WHERE StudentID=1;

1. DELETE

DELETE FROM STUDENT

WHERE StudentID=1;

7. Explain INSERT, UPDATE, and DELETE statements.

INSERT
Adds new records.

INSERT INTO STUDENT(Name, Dept)

VALUES('Amit','CSE');

UPDATE
Modifies existing records.

question bank : 33
 UPDATE STUDENT
SET Dept='ECE'
WHERE StudentID=2;

DELETE
Removes records.

DELETE FROM STUDENT

WHERE StudentID=2;

8. Explain SELECT statement with all clauses.

The SELECT statement retrieves data from tables.

Syntax
SELECT column_list
FROM table_name
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column;

Clauses
SELECT → columns to retrieve
FROM → table name
WHERE → condition
GROUP BY → grouping rows
HAVING → condition on groups
ORDER BY → sorting result

question bank : 34
 9. Explain WHERE, ORDER BY, and DISTINCT clauses.
WHERE Clause
Filters rows based on condition.

SELECT * FROM STUDENT

WHERE Dept='CSE';

ORDER BY Clause
Sorts result in ascending or descending order.

SELECT * FROM STUDENT

ORDER BY Name ASC;

DISTINCT Clause
Removes duplicate values.

SELECT DISTINCT Dept FROM STUDENT;

10. Explain aggregate functions with examples.

Aggregate functions perform calculations on a group of values.

Common Aggregate Functions

1. COUNT()

SELECT COUNT(*) FROM STUDENT;

1. SUM()

SELECT SUM(Salary) FROM EMPLOYEE;

question bank : 35
 1. AVG()

SELECT AVG(Marks) FROM STUDENT;

1. MAX()

SELECT MAX(Salary) FROM EMPLOYEE;

1. MIN()

SELECT MIN(Marks) FROM STUDENT;

11. Explain GROUP BY and HAVING clauses.

GROUP BY Clause
The GROUP BY clause is used to group rows having the same values in specified
columns and apply aggregate functions on each group.
Syntax

SELECT column_name, aggregate_function(column)

FROM table_name
GROUP BY column_name;

Example

SELECT Dept, COUNT(*)

FROM STUDENT
GROUP BY Dept;

HAVING Clause
The HAVING clause is used to apply conditions on groups created by GROUP BY.
Example

question bank : 36
 SELECT Dept, COUNT(*)
FROM STUDENT
GROUP BY Dept
HAVING COUNT(*) > 5;

12. Explain different types of joins in SQL.

Joins combine rows from two or more tables based on a related column.

Types of Joins
1. INNER JOIN
Returns matching rows from both tables.

SELECT * FROM STUDENT

INNER JOIN DEPARTMENT
ON [Link] = [Link];

1.
2. LEFT OUTER JOIN
Returns all rows from left table and matching rows from right table.
3. RIGHT OUTER JOIN
Returns all rows from right table and matching rows from left table.
4. FULL OUTER JOIN
Returns all records when there is a match in either table.

13. Write SQL queries using INNER and OUTER joins.

INNER JOIN Example
SELECT [Link], [Link]
FROM STUDENT S

question bank : 37
 INNER JOIN DEPARTMENT D
ON [Link] = [Link];

LEFT OUTER JOIN Example

SELECT [Link], [Link]
FROM STUDENT S
LEFT JOIN DEPARTMENT D
ON [Link] = [Link];

14. Explain nested queries with examples.

A nested query (subquery) is a query inside another SQL query.

Example
SELECT Name
FROM EMPLOYEE
WHERE Salary >
(SELECT AVG(Salary) FROM EMPLOYEE);

The inner query calculates average salary, and the outer query selects employees
earning more than average.

15. Explain subqueries using IN, ANY, and ALL.

IN Operator
Checks if a value matches any value in a subquery.

SELECT Name
FROM STUDENT
WHERE DeptID IN
(SELECT DeptID FROM DEPARTMENT);

question bank : 38
 ANY Operator
Condition is true if it satisfies any value returned by subquery.

SELECT Name
FROM EMPLOYEE
WHERE Salary > ANY
(SELECT Salary FROM EMPLOYEE WHERE Dept='HR');

ALL Operator
Condition must satisfy all values returned by subquery.

SELECT Name
FROM EMPLOYEE
WHERE Salary > ALL
(SELECT Salary FROM EMPLOYEE WHERE Dept='HR');

16. Explain set operations in SQL.

Set operations combine results of two queries.

Types
1. UNION
Combines results and removes duplicates.

SELECT Name FROM STUDENT

UNION
SELECT Name FROM EMPLOYEE;

1.
2. UNION ALL
Includes duplicates.

question bank : 39
 3. INTERSECT
Returns common rows.
4. EXCEPT (MINUS)
Returns rows present in first query but not in second.

17. What are views? Explain advantages and

limitations.
View
A view is a virtual table created from one or more tables.
Syntax

CREATE VIEW CSE_STUDENTS AS

SELECT Name FROM STUDENT WHERE Dept='CSE';

Advantages
Improves security.
Simplifies complex queries.
Provides data abstraction.
Restricts data access.

Limitations
Performance may decrease.
Limited update capability.
Depends on base tables.

18. Explain creation and updating of views.

Creating a View

question bank : 40
 CREATE VIEW EMP_VIEW AS
SELECT EmpName, Salary
FROM EMPLOYEE;

Updating a View
UPDATE EMP_VIEW
SET Salary = 50000
WHERE EmpName='Rahul';

Changes reflect in base table.

19. Explain stored procedures and their benefits.

Stored Procedure
A stored procedure is a precompiled collection of SQL statements stored in the
database.
Example

CREATE PROCEDURE GetEmployees

AS
SELECT * FROM EMPLOYEE;

Benefits
Improves performance.
Reduces network traffic.
Reusable code.
Better security.
Easier maintenance.

question bank : 41
 20. Explain embedded SQL and its applications.
Embedded SQL
SQL statements written inside a host programming language such as C, Java, or
Python.

Applications
Application development.
Banking systems.
Online reservation systems.
Enterprise applications.

21. Create the following tables with appropriate data

types and constraints.
STUDENT Table
CREATE TABLE STUDENT(
StudentId INT PRIMARY KEY,
Fname VARCHAR(30) NOT NULL,
Lname VARCHAR(30),
Department VARCHAR(20),
DOB DATE,
Gender CHAR(1),
Address VARCHAR(100)
);

STUDENT_MARKS Table
CREATE TABLE STUDENT_MARKS(
StudentId INT,
Subject VARCHAR(30),

question bank : 42
 Marks INT CHECK (Marks >= 0),
Semester INT,
FOREIGN KEY (StudentId)
REFERENCES STUDENT(StudentId)
);

22. Create tables and write SQL queries.

Create Tables
CREATE TABLE DEPARTMENT(
DeptId INT PRIMARY KEY,
DeptName VARCHAR(30),
Location VARCHAR(30)
);

CREATE TABLE EMPLOYEE(

EmpId INT PRIMARY KEY,
EmpName VARCHAR(30),
DeptId INT,
Salary INT,
DOJ DATE,
FOREIGN KEY (DeptId)
REFERENCES DEPARTMENT(DeptId)
);

Queries
a) Count employees grouped by department whose salary is between 25000
and 60000

SELECT DeptId, COUNT(*)

FROM EMPLOYEE

question bank : 43
 WHERE Salary BETWEEN 25000 AND 60000
GROUP BY DeptId;

b) Retrieve employee names starting with ‘S’ and exactly six characters

SELECT EmpName
FROM EMPLOYEE
WHERE EmpName LIKE 'S_____';

c) Display employees working as Team Lead

SELECT *
FROM EMPLOYEE
WHERE Designation = 'Team Lead';

d) List employees working in the same project

SELECT *
FROM EMPLOYEE
WHERE ProjectId IN
(
SELECT ProjectId
FROM EMPLOYEE
GROUP BY ProjectId
HAVING COUNT(*) > 1
);

UNIT - 4
1. Define functional dependency with suitable
examples.

question bank : 44
 A Functional Dependency (FD) describes the relationship between attributes in a
relation.
If attribute A determines attribute B, then B is functionally dependent on A.

Notation
A → B

(Read as A determines B)

Example
STUDENT(StudentID, Name, Dept)

StudentID → Name, Dept

Because StudentID uniquely identifies a student.

2. Explain types of functional dependencies.

1. Trivial Functional Dependency
If Y is a subset of X.

{A, B} → A

2. Non-Trivial Functional Dependency

If Y is not a subset of X.

StudentID → Name

3. Fully Functional Dependency

When an attribute depends on the whole composite key.
Example:

question bank : 45
 (StudentID, Subject) → Marks

4. Partial Dependency
When an attribute depends only on part of a composite key.
Example:

(StudentID, Subject) → StudentName

5. Transitive Dependency
If A → B and B → C, then A → C.
Example:

StudentID → DeptID
DeptID → DeptName

3. Explain closure of attribute sets.

The closure of an attribute set is the set of all attributes that can be functionally
determined from it using functional dependencies.

Notation
X⁺

Purpose
To find candidate keys.
To check functional dependencies.

Example
R(A, B, C)

question bank : 46
 FDs: A → B, B → C
Closure of A:

A⁺ = {A, B, C}

4. What is decomposition? Explain its types.

Decomposition is the process of dividing a relation into smaller relations to
remove redundancy and anomalies.

Types
1. Lossless Join Decomposition
Original relation can be reconstructed without loss of information.
2. Lossy Decomposition
Some information is lost after decomposition.

5. Explain lossless join decomposition.

A decomposition is lossless if the original relation can be obtained by joining
decomposed relations without generating spurious tuples.

Condition
Common attribute must be a key in at least one relation.

Example
R(A, B, C)
Decompose into:
R1(A, B) and R2(B, C)
If B is key, decomposition is lossless.

6. Explain dependency preservation.

question bank : 47
 Dependency preservation means all functional dependencies can be enforced
without joining decomposed relations.

Importance
Improves efficiency.
Avoids expensive joins.
Maintains data consistency.

Example
If FD A → B exists, it should remain enforceable after decomposition.

7. Explain First Normal Form (1NF) with example.

A relation is in First Normal Form (1NF) if:
All attributes contain atomic values.
No repeating groups or multivalued attributes.

Example (Not in 1NF)

StudentID Phone
1 9876, 8765

After 1NF
StudentID Phone
1 9876
1 8765

8. Explain Second Normal Form (2NF) with example.

A relation is in Second Normal Form (2NF) if:
It is in 1NF.
No partial dependency exists.

question bank : 48
 Example
ENROLL(StudentID, Subject, StudentName, Marks)
FD:

(StudentID, Subject) → Marks

StudentID → StudentName

StudentName depends only on StudentID → Partial dependency.

After 2NF
Split into:
STUDENT(StudentID, StudentName)
ENROLL(StudentID, Subject, Marks)

9. Explain Third Normal Form (3NF) with example.

A relation is in Third Normal Form (3NF) if:
It is in 2NF.
No transitive dependency exists.

Example
STUDENT(StudentID, DeptID, DeptName)
FD:

StudentID → DeptID
DeptID → DeptName

Transitive dependency exists.

After 3NF
STUDENT(StudentID, DeptID)
DEPARTMENT(DeptID, DeptName)

question bank : 49
 10. Explain Boyce-Codd Normal Form (BCNF).
A relation is in BCNF if:
For every functional dependency,

X → Y

X must be a super key.

Difference from 3NF

BCNF is stricter than 3NF.

Example
If non-key attribute determines another attribute, it violates BCNF.

11. Compare 3NF and BCNF.

3NF BCNF
Removes transitive dependency Removes all anomalies
Less strict More strict
Some redundancy allowed No redundancy allowed
Easier to achieve Harder to achieve

12. Explain multivalued dependency with example.

A Multivalued Dependency (MVD) occurs when one attribute determines multiple
independent values of another attribute.

Notation
A →→ B

Example

question bank : 50
 STUDENT(StudentID, Phone, Skill)
If phone numbers and skills are independent:

StudentID →→ Phone
StudentID →→ Skill

This causes redundancy.

Solution
Decompose into:
STUDENT_PHONE(StudentID, Phone)
STUDENT_SKILL(StudentID, Skill)

13. Explain Fourth Normal Form (4NF).

A relation is in Fourth Normal Form (4NF) if:
It is in BCNF.
It has no non-trivial multivalued dependencies.

Example
STUDENT(StudentID, Phone, Skill)
Since:

StudentID →→ Phone
StudentID →→ Skill

The relation violates 4NF.

After 4NF
STUDENT_PHONE(StudentID, Phone)
STUDENT_SKILL(StudentID, Skill)

question bank : 51
 14. Explain Join Dependency and Fifth Normal Form
(5NF).
Join Dependency
A join dependency exists when a relation can be reconstructed by joining multiple
smaller relations.

Fifth Normal Form (5NF)

A relation is in 5NF if:
It is in 4NF.
It cannot be decomposed further without loss of information.

Example
SUPPLIER(Product, Project, Supplier)
If relationships are independent, relation can be decomposed into smaller tables.

15. Explain storage system architecture in DBMS.

Storage system architecture defines how data is stored and accessed.

Components
1. Primary Storage
Main memory (RAM).
Fast but temporary.
2. Secondary Storage
Hard disk or SSD.
Permanent storage.
3. Buffer Manager
Transfers data between disk and memory.
4. File Manager

question bank : 52
 Manages disk space allocation.

16. Explain primary and secondary storage.

Primary Storage
Main memory (RAM).
Fast access.
Temporary storage.
Used during execution.

Secondary Storage
Hard disk, SSD.
Permanent storage.
Large capacity.
Slower than primary memory.

17. Explain different file organization methods.

File organization determines how records are stored.

Types
1. Heap File Organization
Records stored randomly.
Fast insertion.
2. Sequential File Organization
Records stored in sorted order.
Efficient for sequential access.
3. Indexed File Organization
Uses index for fast retrieval.

question bank : 53
 4. Hashed File Organization
Uses hash function for storage location.

18. Explain hashing file organization.

Hashing uses a hash function to compute storage location.

Process
Address = Hash(Key)

Advantages
Fast data retrieval.
Direct access.

Disadvantages
Collision problem.
Difficult range queries.

19. Explain indexing techniques in DBMS.

Indexing improves speed of data retrieval.

Types
1. Primary Index
Based on primary key.
2. Secondary Index
Based on non-key attribute.
3. Clustering Index
Data stored physically in order.

question bank : 54
 Advantages
Faster searching.
Reduced disk access.

20. Explain B-tree and B+ tree with operations.

B-Tree
A balanced tree used for indexing.

Properties
All leaves at same level.
Nodes contain keys and pointers.
Supports search, insertion, deletion.

B+ Tree
An extension of B-tree.

Properties
Data stored only in leaf nodes.
Leaf nodes are linked.
Faster range queries.

21. Explain B-tree and B+ tree. Compare them with

suitable diagrams.
B-Tree
Data stored in internal and leaf nodes.
Searching may stop at internal nodes.

B+ Tree

question bank : 55
 Data stored only in leaf nodes.
Internal nodes store only keys.
Leaf nodes linked sequentially.

Comparison
B-Tree B+ Tree
Data in all nodes Data only in leaf nodes
Slower range queries Faster range queries
Less efficient indexing More efficient indexing
Leaves not linked Leaves linked

22. Construct a B-tree of order 3 for the following

keys:
15, 5, 1, 20, 25, 30, 10, 8, 12

Steps (Summary)
1. Insert 15 → root.
2. Insert 5, 1 → node fills.
3. Split when overflow occurs.
4. Continue inserting and splitting.

Final Structure (Exam Representation)

15
/ \
5,10 25
/ | \ / \
1 8 12 20 30

23. Compute closure of relational schema.

question bank : 56
 Given:

R = {P, Q, R, S, T}
FDs = { P → QR, RQ → S, Q → T, T → P }

Step 1: Find Closure

Start with P⁺:
P → QR
Q→T
T→P
RQ → S
So,

P⁺ = {P, Q, R, S, T}

Hence P is a candidate key.

Candidate Keys
P
Q
T
(All determine entire relation)

Prime Attributes
Attributes that are part of candidate key:

P, Q, T

24. Consider relation

question bank : 57
 R = {A, B, C, D, E, F}
FDs = { A → BC, B → D, CD → E, E → F }

(a) Attribute Closure of A

Start with A⁺:
A → BC
B→D
CD → E
E→F
So,

A⁺ = {A, B, C, D, E, F}

(b) Candidate Keys

Since A⁺ contains all attributes,

A is the candidate key.

UNIT - 5 :
1. Explain query processing steps in DBMS.
Query processing refers to the steps followed by DBMS to execute a user query
efficiently.

Steps in Query Processing

1. Parsing and Translation
SQL query is checked for syntax and converted into internal form.
2. Query Optimization

question bank : 58
 DBMS chooses the most efficient execution plan.
3. Query Evaluation
Execution of optimized query to produce result.

Flow
SQL Query → Parser → Optimizer → Execution Engine → Result

2. Explain cost-based query optimization.

Cost-based optimization selects the query execution plan with the minimum
estimated cost.

Cost Factors
Disk I/O operations
CPU time
Memory usage
Number of records processed

Working
Multiple execution plans are generated.
Cost of each plan is estimated.
Lowest cost plan is selected.

Advantages
Improves performance.
Reduces execution time.

3. Explain different query evaluation strategies.

Query evaluation strategy determines how query operations are executed.

question bank : 59
 Types
1. Materialization
Intermediate results stored temporarily.
2. Pipelining
Output of one operation directly used by next operation without storing.

Comparison
Materialization uses more memory.
Pipelining is faster and efficient.

4. Explain selection and join operation cost estimation.

Selection Operation Cost
Depends on:
Number of records
Type of search (linear or indexed)
Selectivity of condition
Example:
Linear search → scans entire table.
Indexed search → faster.

Join Operation Cost

Depends on:
Size of relations
Join algorithm used
Memory availability
Common join methods:

question bank : 60
 Nested loop join
Sort-merge join
Hash join

5. Explain transaction concept in DBMS.

A transaction is a sequence of database operations performed as a single logical
unit of work.

Example
Bank transfer:
Debit from account A
Credit to account B
Both operations must succeed together.

Properties
Executed completely or not at all.

6. Explain ACID properties of transactions.

ACID properties ensure reliable transaction processing.
1. Atomicity
Transaction occurs completely or not at all.
2. Consistency
Database remains in valid state.
3. Isolation
Transactions do not interfere with each other.
4. Durability
Changes remain permanent after commit.

question bank : 61
 7. Explain transaction states with diagram.
A transaction passes through different states during execution.

Transaction States
1. Active – Transaction is executing.
2. Partially Committed – Final statement executed.
3. Committed – Changes permanently saved.
4. Failed – Execution unsuccessful.
5. Aborted – Transaction rolled back.
6. Terminated – Transaction finished.

Diagram (Exam Representation)

Active → Partially Committed → Committed → Terminated
↓
Failed → Aborted → Terminated

8. Explain concurrency control problems.

Concurrency problems occur when multiple transactions execute simultaneously.

Problems
1. Lost Update
One update overwrites another.
2. Dirty Read
Reading uncommitted data.
3. Non-repeatable Read
Same data gives different results.
4. Phantom Read

question bank : 62
 New records appear during execution.

9. Explain lock-based concurrency control.

Lock-based concurrency control uses locks to prevent conflicts.

Types of Locks
1. Shared Lock (Read Lock)
Multiple transactions can read.
2. Exclusive Lock (Write Lock)
Only one transaction can write.

Advantages
Maintains consistency.
Prevents simultaneous conflicting updates.

10. Explain two-phase locking protocol.

Two-Phase Locking (2PL) ensures serializability of transactions.

Phases
1. Growing Phase
Transaction acquires locks.
No locks released.
2. Shrinking Phase
Locks released.
No new locks acquired.

Advantages
Prevents conflicts.
Ensures consistency.

question bank : 63
 Disadvantage
May lead to deadlock.

11. Explain deadlock and deadlock handling

techniques.
Deadlock
A deadlock occurs when two or more transactions wait indefinitely for each other
to release locks.

Example
Transaction T1 holds resource A and waits for B.
Transaction T2 holds resource B and waits for A.
Both wait forever → Deadlock.

Deadlock Handling Techniques

1. Deadlock Prevention
Prevent one of the deadlock conditions.
Example: Request all resources at once.
2. Deadlock Avoidance
Allocate resources carefully to avoid unsafe states.
Example: Banker’s Algorithm.
3. Deadlock Detection and Recovery
System detects deadlock and aborts one transaction.

12. Explain timestamp-based concurrency control.

Timestamp ordering assigns a unique timestamp to each transaction.

Working

question bank : 64
 Older transaction gets priority.
Operations executed according to timestamp order.

Rules
Read and write operations allowed only if timestamp order is maintained.
Violating transaction is rolled back.

Advantages
Deadlock-free.
Simple implementation.

13. Explain recovery management in DBMS.

Recovery management ensures database consistency after failures.

Types of Failures
Transaction failure
System crash
Disk failure

Functions
Restore database to consistent state.
Undo incomplete transactions.
Redo committed transactions.

14. Explain log-based recovery techniques.

Log-based recovery maintains a log file containing transaction activities.

Log Entries
Transaction start

question bank : 65
 Data modification
Commit or rollback

Types
1. Deferred Update
Changes recorded after commit.
2. Immediate Update
Changes recorded immediately.

Advantages
Reliable recovery.
Maintains data consistency.

15. Explain shadow paging method.

Shadow paging is a recovery technique where changes are made on a copy of
database pages.

Working
Original pages remain unchanged.
Updates made on shadow pages.
On commit, shadow pages replace original pages.

Advantages
No need for log file.
Fast recovery.

Disadvantages
High memory usage.
Fragmentation problem.

question bank : 66
 16. Explain checkpoint mechanism.
Checkpoint is a mechanism used to reduce recovery time.

Working
DBMS periodically saves database state.
All committed transactions written to disk.
Recovery starts from last checkpoint.

Advantages
Faster recovery.
Reduces log scanning time.

17. Explain buffer management in DBMS.

Buffer manager manages data transfer between disk and main memory.

Functions
Loads required pages into memory.
Replaces pages when memory is full.
Reduces disk I/O operations.

Benefits
Improves performance.
Efficient memory utilization.

18. Explain crash recovery techniques.

Crash recovery restores database after system failure.

Techniques
1. Log-based Recovery

question bank : 67
 Uses log file to redo or undo transactions.
2. Checkpointing
Recovery starts from last checkpoint.
3. Shadow Paging
Uses shadow copies of data pages.

19. Explain database backup techniques.

Database backup is the process of copying database data for recovery.

Types
1. Full Backup
Entire database copied.
2. Incremental Backup
Only changed data backed up.
3. Differential Backup
Changes since last full backup.

Importance
Protects against data loss.
Supports disaster recovery.

20. Explain importance of transaction management in

DBMS.
Transaction management ensures reliable database operations.

Importance
1. Maintains data consistency.
2. Ensures ACID properties.

question bank : 68
 3. Handles concurrent transactions safely.
4. Provides recovery from failures.
5. Prevents data corruption.
6. Ensures reliable multi-user access.

question bank : 69