J-1

GUJARAT TECHNOLOGICAL UNIVERSITY

GRADUATE SCHOOL OF ENGINEERING AND TECHNOLOGY

PRACTICAL – 1

Course Code & Name Database Management System (BE03000091)

Academic Term: 2025-26
Student Enrollment No: Batch:
Student Name:

AIM/Objective:

Perform fundamental operations such as SELECT, INSERT, UPDATE, and DELETE. Utilize a schema with tables
1
like Sales and Products to demonstrate how to retrieve, modify, and manage data.

Expected Outcome: CO/PO/PSO

Students will learn to create tables, insert, update, select, and delete records in a
1 MySQL database. They will be able to manage and manipulate data using
fundamental SQL commands.

Student Report with Experiment Results and Analysis

Code:

CREATE TABLE Products (

product_id INT PRIMARY KEY,
product_name VARCHAR(50),
price INT
);

CREATE TABLE Sales (

sale_id INT PRIMARY KEY,
product_id INT,
quantity INT,
sale_date DATE,
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

INSERT INTO Products VALUES

 (1, 'Laptop', 55000),
(2, 'Mouse', 500),

(3, 'Keyboard', 1200);

INSERT INTO Sales VALUES

(101, 1, 2, '2024-01-10'),
(102, 3, 1, '2024-01-12'),
(103, 2, 5, '2024-01-15');

SELECT * FROM Products;

SELECT s.sale_id, p.product_name, [Link], [Link]

FROM Sales s
JOIN Products p ON s.product_id = p.product_id;

SELECT product_name, price

FROM Products
WHERE price > 1000;

UPDATE Products SET price = 60000 WHERE product_id = 1;

UPDATE Sales SET quantity = 4 WHERE sale_id = 102;

DELETE FROM Sales WHERE sale_id = 103;

DELETE FROM Products WHERE product_id = 2;

Output:
 Conclusion:

This practical demonstrated how to create relational tables and perform fundamental
data operations such as SELECT, INSERT, UPDATE, and DELETE in MySQL using the
Products and Sales schema. It effectively showcased how to manage, query, and
maintain data integrity in a sales database through practical SQL commands.

Inadequate Good Excellent

Evaluation Rubrics Marks
0% 50% 100%
The understanding of the student regarding the objective of
1 2
the given practical
2 Implementation of the Practical 2

3 Quality of the Result Analysis done 2

Quality of the report including concluding remarks and
4 2
Findings
5 Timely submission practical & self-learning activities. 2
10
Total Marks Obtained Out of 10

Date of Completion:___________________ Course Coordinator: Prof. G. D. Makwana

 J-1
GUJARAT TECHNOLOGICAL UNIVERSITY
GRADUATE SCHOOL OF ENGINEERING AND TECHNOLOGY

PRACTICAL – 2

Course Code & Name Database Management System (BE03000091)

Academic Term: 2025-26
Student Enrollment No: Batch:
Student Name:

AIM/Objective:

Implement Data Constraints: Add primary key, foreign key, unique key, and check constraints, define and
1
remove integrity constraints using the ALTER TABLE command.

Expected Outcome: CO/PO/PSO

Students will understand how to enforce, modify, and drop various integrity
1 constraints in a database, ensuring data validity and consistency during data
management operations.

Student Report with Experiment Results and Analysis

Code:
CREATE TABLE Products (

product_id INT,

product_name VARCHAR(50),

price INT

);

CREATE TABLE Sales (

sale_id INT,

product_id INT,
 quantity INT,

sale_date DATE

);

ALTER TABLE Products ADD CONSTRAINT pk_product PRIMARY KEY (product_id);

ALTER TABLE Sales ADD CONSTRAINT pk_sales PRIMARY KEY (sale_id);

ALTER TABLE Sales ADD CONSTRAINT fk_sales_product FOREIGN KEY (product_id)

REFERENCES Products(product_id);

ALTER TABLE Products ADD CONSTRAINT unique_product_name UNIQUE (product_name);

ALTER TABLE Products ADD CONSTRAINT check_price CHECK (price > 0);

ALTER TABLE Sales ADD CONSTRAINT check_quantity CHECK (quantity > 0);

INSERT INTO Products VALUES

(1, 'Laptop', 55000),

(2, 'Mouse', 500),

(3, 'Keyboard', 1200);

INSERT INTO Sales VALUES

(101, 1, 2, '2024-01-10'),

(102, 3, 1, '2024-01-12');

SELECT * FROM Products;

SELECT * FROM Sales;

ALTER TABLE Products DROP INDEX unique_product_name;

ALTER TABLE Products DROP CHECK check_price;

ALTER TABLE Sales DROP CHECK check_quantity;

ALTER TABLE Sales DROP FOREIGN KEY fk_sales_product;

ALTER TABLE Sales DROP PRIMARY KEY;

SELECT * FROM Products;

SELECT * FROM Sales;

Output:
 Conclusion:
This practical demonstrated the implementation of data integrity constraints such as
primary key, foreign key, unique, and check constraints in relational tables. It also showed
how to define and remove these constraints dynamically using the ALTER TABLE command.

Inadequate Good Excellent

Evaluation Rubrics Marks
0% 50% 100%
The understanding of the student regarding the objective of
1 2
the given practical
2 Implementation of the Practical 2

3 Quality of the Result Analysis done 2

Quality of the report including concluding remarks and
4 2
Findings
5 Timely submission practical & self-learning activities. 2
10
Total Marks Obtained Out of 10

Date of Completion:___________________ Course Coordinator: Prof. G. D. Makwana

 J-1
GUJARAT TECHNOLOGICAL UNIVERSITY
GRADUATE SCHOOL OF ENGINEERING AND TECHNOLOGY

PRACTICAL – 3

Course Code & Name Database Management System (BE03000091)

Academic Term: 2025-26
Student Enrollment No: Batch:
Student Name:

AIM/Objective:

1 Perform joins, subqueries, aggregate functions, and window functions

Expected Outcome: CO/PO/PSO

Students will be able to perform multi-table queries, use subqueries for advanced
1 filtering, calculate summary statistics with aggregate functions, and apply window
functions for complex data analysis tasks.

Student Report with Experiment Results and Analysis

Code:
CREATE TABLE Customers (

customer_id INT PRIMARY KEY,

customer_name VARCHAR(50),

city VARCHAR(50)

);

CREATE TABLE Orders (

order_id INT PRIMARY KEY,

customer_id INT,

amount INT,
 order_date DATE,

FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)

);

INSERT INTO Customers VALUES

(1, 'Amit', 'Delhi'),

(2, 'Priya', 'Mumbai'),

(3, 'Karan', 'Delhi'),

(4, 'Sneha', 'Pune');

INSERT INTO Orders VALUES

(101, 1, 5000, '2024-01-05'),

(102, 2, 12000, '2024-01-10'),

(103, 1, 8000, '2024-01-12'),

(104, 3, 15000, '2024-02-01'),

(105, 3, 6000, '2024-02-10');

SELECT c.customer_name, [Link], o.order_id, [Link]

FROM Customers c

JOIN Orders o ON c.customer_id = o.customer_id;

SELECT customer_name

FROM Customers
WHERE customer_id IN (

SELECT customer_id

FROM Orders

GROUP BY customer_id

HAVING SUM(amount) > 10000

);

SELECT COUNT(*) AS total_orders,

SUM(amount) AS total_sales,

AVG(amount) AS avg_order_value,

MAX(amount) AS highest_order,

MIN(amount) AS lowest_order

FROM Orders;

SELECT customer_id, amount, order_date,

SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS

running_total,

RANK() OVER (ORDER BY amount DESC) AS order_rank

FROM Orders;
Output:

Conclusion:

This practical demonstrated how to combine data using joins, extract specific results
through subqueries, and analyze data using aggregate and window functions in SQL.
These techniques enable efficient data retrieval and meaningful insights from
relational databases.
 Inadequate Good Excellent
Evaluation Rubrics Marks
0% 50% 100%
The understanding of the student regarding the objective of
1 2
the given practical
2 Implementation of the Practical 2

3 Quality of the Result Analysis done 2

Quality of the report including concluding remarks and
4 2
Findings
5 Timely submission practical & self-learning activities. 2
10
Total Marks Obtained Out of 10

Date of Completion:___________________ Course Coordinator: Prof. G. D. Makwana

 J-1
GUJARAT TECHNOLOGICAL UNIVERSITY
GRADUATE SCHOOL OF ENGINEERING AND TECHNOLOGY

PRACTICAL – 4

Course Code & Name Database Management System (BE03000091)

Academic Term: 2025-26
Student Enrollment No: Batch:
Student Name:

AIM/Objective:

1 Implement self-joins, and intricate data manipulation techniques

Expected Outcome: CO/PO/PSO

Students will be able to implement self-joins to analyze hierarchical relationships
1 and perform advanced data modifications, equipping them with techniques for
solving complex data management problems in relational databases.

Student Report with Experiment Results and Analysis

Code:

DROP TABLE IF EXISTS Employees;

CREATE TABLE Employees (

emp_id INT PRIMARY KEY,

emp_name VARCHAR(50),

manager_id INT,

salary INT,

department VARCHAR(50)

);
INSERT INTO Employees VALUES

(1, 'Amit', NULL, 60000, 'HR'),

(2, 'Priya', 1, 50000, 'HR'),

(3, 'Karan', 1, 45000, 'Finance'),

(4, 'Sneha', 2, 40000, 'Finance'),

(5, 'Rahul', 2, 35000, 'IT'),

(6, 'Isha', 3, 30000, 'IT');

SELECT e.emp_name AS Employee, m.emp_name AS Manager

FROM Employees e

LEFT JOIN Employees m ON e.manager_id = m.emp_id;

UPDATE Employees SET salary = salary + 5000 WHERE department = 'Finance';

DELETE FROM Employees WHERE salary < 35000;

INSERT INTO Employees (emp_id, emp_name, manager_id, salary, department)

SELECT 7, 'Mehul', 1, AVG(salary), 'HR'

FROM Employees;

UPDATE Employees e

JOIN (

SELECT department, AVG(salary) AS avg_sal

 FROM Employees

GROUP BY department

) d ON [Link] = [Link]

SET [Link] = [Link] + 2000

WHERE [Link] < d.avg_sal;

SELECT * FROM Employees;

Output:
 Conclusion:

This practical explored the use of self-joins to relate data within the same table and
demonstrated advanced data manipulation techniques such as conditional updates,
deletions, and inserting aggregate results. These operations enable the effective
management of hierarchical or interrelated data and refine complex business logic
within a single dataset.

Inadequate Good Excellent

Evaluation Rubrics Marks
0% 50% 100%
The understanding of the student regarding the objective of
1 2
the given practical
2 Implementation of the Practical 2

3 Quality of the Result Analysis done 2

Quality of the report including concluding remarks and
4 2
Findings
5 Timely submission practical & self-learning activities. 2
10
Total Marks Obtained Out of 10

Date of Completion:___________________ Course Coordinator: Prof. G. D. Makwana

 J-1
GUJARAT TECHNOLOGICAL UNIVERSITY
GRADUATE SCHOOL OF ENGINEERING AND TECHNOLOGY

PRACTICAL – 5

Course Code & Name Database Management System (BE03000091)

Academic Term: 2025-26
Student Enrollment No: Batch:
Student Name:

AIM/Objective:

1 Perform SET operations, Implement In-built functions.

Expected Outcome: CO/PO/PSO

Students will be able to merge results from multiple queries using SET operations
1 and utilize various SQL built-in functions, enhancing their skills in data processing,
formatting, and computation within a database.

Student Report with Experiment Results and Analysis

Code:
CREATE TABLE DeptA (

emp_id INT,

emp_name VARCHAR(50),

city VARCHAR(50)

);

CREATE TABLE DeptB (

emp_id INT,

emp_name VARCHAR(50),
 city VARCHAR(50)

);

INSERT INTO DeptA VALUES

(1, 'Amit', 'Delhi'),

(2, 'Priya', 'Mumbai'),

(3, 'Karan', 'Pune');

INSERT INTO DeptB VALUES

(2, 'Priya', 'Mumbai'),

(3, 'Karan', 'Pune'),

(4, 'Sneha', 'Delhi');

-- SET OPERATIONS

SELECT emp_id, emp_name, city FROM DeptA

UNION

SELECT emp_id, emp_name, city FROM DeptB;

SELECT emp_id, emp_name, city FROM DeptA

UNION ALL

SELECT emp_id, emp_name, city FROM DeptB;

SELECT A.emp_id, A.emp_name, [Link]

FROM DeptA A

JOIN DeptB B USING(emp_id, emp_name, city);

SELECT A.emp_id, A.emp_name, [Link]

FROM DeptA A

LEFT JOIN DeptB B USING(emp_id, emp_name, city)

WHERE B.emp_id IS NULL;

-- IN-BUILT FUNCTIONS

SELECT emp_name,

UPPER(emp_name) AS upper_name,

LOWER(emp_name) AS lower_name,

LENGTH(emp_name) AS name_length

FROM DeptA;

SELECT ABS(-50) AS absolute_value,

CEIL(10.3) AS ceiling_value,

FLOOR(10.9) AS floor_value,

POWER(3,2) AS power_value;

SELECT CURDATE() AS today_date,

NOW() AS current_datetime,

DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS next_week,

 YEAR(CURDATE()) AS year_value;

SELECT COUNT(*) AS total_records,

AVG(emp_id) AS avg_id,

MAX(emp_id) AS max_id,

MIN(emp_id) AS min_id

FROM DeptA;

Output:
 Conclusion:

This practical demonstrated how to use SET operations such as UNION and
INTERSECT to combine query results, and applied in-built SQL functions for
formatting, calculation, and string manipulation. These tools enable efficient and
powerful data analysis across different tables and result sets.

Inadequate Good Excellent

Evaluation Rubrics Marks
0% 50% 100%
The understanding of the student regarding the objective of
1 2
the given practical
2 Implementation of the Practical 2

3 Quality of the Result Analysis done 2

Quality of the report including concluding remarks and
4 2
Findings
5 Timely submission practical & self-learning activities. 2
10
Total Marks Obtained Out of 10

Date of Completion:___________________ Course Coordinator: Prof. G. D. Makwana

 J-1
GUJARAT TECHNOLOGICAL UNIVERSITY
GRADUATE SCHOOL OF ENGINEERING AND TECHNOLOGY

PRACTICAL – 6

Course Code & Name Database Management System (BE03000091)

Academic Term: 2025-26
Student Enrollment No: Batch:
Student Name:

AIM/Objective:

1 Implement nested subqueries

Expected Outcome: CO/PO/PSO

Students will be able to compose and utilize multiple levels of subqueries to

1 efficiently filter, compute, and retrieve precise information from a database for
advanced analytical needs.

Student Report with Experiment Results and Analysis

Code:
CREATE TABLE Departments (

dept_id INT PRIMARY KEY,

dept_name VARCHAR(50)

);

CREATE TABLE Employees (

emp_id INT PRIMARY KEY,

emp_name VARCHAR(50),

salary INT,
 dept_id INT,

FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)

);

INSERT INTO Departments VALUES

(1, 'HR'),

(2, 'IT'),

(3, 'Finance');

INSERT INTO Employees VALUES

(101, 'Amit', 50000, 1),

(102, 'Priya', 65000, 2),

(103, 'Karan', 45000, 3),

(104, 'Sneha', 60000, 2),

(105, 'Rohan', 30000, 1);

-- Nested subqueries examples

SELECT emp_name, salary

FROM Employees

WHERE salary > (

SELECT AVG(salary) FROM Employees

);
SELECT emp_name

FROM Employees

WHERE dept_id IN (

SELECT dept_id FROM Departments WHERE dept_name IN ('IT', 'Finance')

);

SELECT emp_name, salary

FROM Employees

WHERE salary > (

SELECT AVG(salary)

FROM Employees

WHERE dept_id = (

SELECT dept_id

FROM Departments

WHERE dept_name = 'IT'

);

SELECT emp_name, salary

FROM Employees e

WHERE salary > (

SELECT AVG(salary)
 FROM Employees

WHERE dept_id = e.dept_id

);

Output:
 Conclusion:
This practical illustrated the use of nested subqueries to solve complex data retrieval
problems, allowing queries to be embedded within other queries for layered filtering and
data extraction. Nested subqueries significantly increase the flexibility and analytical power
of SQL statements.

Inadequate Good Excellent

Evaluation Rubrics Marks
0% 50% 100%
The understanding of the student regarding the objective of
1 2
the given practical
2 Implementation of the Practical 2

3 Quality of the Result Analysis done 2

Quality of the report including concluding remarks and
4 2
Findings
5 Timely submission practical & self-learning activities. 2
10
Total Marks Obtained Out of 10

Date of Completion:___________________ Course Coordinator: Prof. G. D. Makwana

 J-1
GUJARAT TECHNOLOGICAL UNIVERSITY
GRADUATE SCHOOL OF ENGINEERING AND TECHNOLOGY

PRACTICAL – 7

Course Code & Name Database Management System (BE03000091)

Academic Term: 2025-26
Student Enrollment No: Batch:
Student Name:

AIM/Objective:

1 Study & Implementation of Rollback, Commit, Savepoint

Expected Outcome: CO/PO/PSO

Students will be able to execute and manage transactions, set savepoints, and use
1 commit/rollback features to maintain data integrity and handle errors efficiently
in SQL databases.

Student Report with Experiment Results and Analysis

Code:
CREATE TABLE Accounts (

acc_id INT PRIMARY KEY,

acc_name VARCHAR(50),

balance INT

);

INSERT INTO Accounts VALUES

(1, 'Amit', 20000),

 (2, 'Priya', 15000),

(3, 'Karan', 18000);

SET autocommit = 0;

UPDATE Accounts SET balance = balance - 5000 WHERE acc_id = 1;

SAVEPOINT s1;

UPDATE Accounts SET balance = balance + 3000 WHERE acc_id = 2;

SAVEPOINT s2;

UPDATE Accounts SET balance = balance + 7000 WHERE acc_id = 3;

ROLLBACK TO s2;

COMMIT;

SELECT * FROM Accounts;

Output:
 Conclusion:

This practical illustrated how to manage database transactions using the COMMIT,
ROLLBACK, and SAVEPOINT commands, ensuring data consistency and control over
changes made during a session. These operations enable users to safely experiment with
data and confidently reverse or finalize changes as needed.

Inadequate Good Excellent

Evaluation Rubrics Marks
0% 50% 100%
The understanding of the student regarding the objective of
1 2
the given practical
2 Implementation of the Practical 2

3 Quality of the Result Analysis done 2

Quality of the report including concluding remarks and
4 2
Findings
5 Timely submission practical & self-learning activities. 2
10
Total Marks Obtained Out of 10

Date of Completion:___________________ Course Coordinator: Prof. G. D. Makwana

 J-1
GUJARAT TECHNOLOGICAL UNIVERSITY
GRADUATE SCHOOL OF ENGINEERING AND TECHNOLOGY

PRACTICAL – 8

Course Code & Name Database Management System (BE03000091)

Academic Term: 2025-26
Student Enrollment No: Batch:
Student Name:

AIM/Objective:

Execute value-matching and pattern-matching conditions on any sample schema to retrieve specific data based
1
on given requirements.

Expected Outcome: CO/PO/PSO

Students will be able to apply value-based and pattern-based filters to query

1 databases, enabling precise and versatile data retrieval to meet diverse analytical
requirements.

Student Report with Experiment Results and Analysis

Code:
CREATE TABLE Students (

stud_id INT PRIMARY KEY,

stud_name VARCHAR(50),

city VARCHAR(50),

marks INT

);
INSERT INTO Students VALUES

(1, 'Amit', 'Delhi', 85),

(2, 'Priya', 'Mumbai', 92),

(3, 'Karan', 'Pune', 76),

(4, 'Sneha', 'Delhi', 88),

(5, 'Rohan', 'Surat', 67),

(6, 'Isha', 'Indore', 91),

(7, 'Aman', 'Mumbai', 45);

-- Value-matching conditions

SELECT * FROM Students WHERE city = 'Mumbai';

SELECT * FROM Students WHERE city IN ('Delhi', 'Pune');

SELECT * FROM Students WHERE marks BETWEEN 70 AND 90;

-- Pattern-matching conditions

SELECT * FROM Students WHERE stud_name LIKE 'A%';

SELECT * FROM Students WHERE stud_name LIKE '_r%';

SELECT * FROM Students WHERE city LIKE '%i%';

SELECT * FROM Students WHERE stud_name RLIKE '^[A-Z].+n$';

Output:

Conclusion:
This practical demonstrated value-matching using the WHERE clause and pattern-matching
using the LIKE operator to filter and retrieve specific records from a database. These
 techniques allow users to extract targeted data efficiently based on exact values or flexible
text patterns.

Inadequate Good Excellent

Evaluation Rubrics Marks
0% 50% 100%
The understanding of the student regarding the objective of
1 2
the given practical
2 Implementation of the Practical 2

3 Quality of the Result Analysis done 2

Quality of the report including concluding remarks and
4 2
Findings
5 Timely submission practical & self-learning activities. 2
10
Total Marks Obtained Out of 10

Date of Completion:___________________ Course Coordinator: Prof. G. D. Makwana

 J-1
GUJARAT TECHNOLOGICAL UNIVERSITY
GRADUATE SCHOOL OF ENGINEERING AND TECHNOLOGY

PRACTICAL – 9

Course Code & Name Database Management System (BE03000091)

Academic Term: 2025-26
Student Enrollment No: Batch:
Student Name:

AIM/Objective:

1 Exploring Functional Dependencies and Normalization in Relational Database Design

Expected Outcome: CO/PO/PSO

Students will be able to recognize functional dependencies, apply normalization

1 rules (up to 2NF/3NF), and redesign tables for improved consistency and storage
efficiency in relational databases.

Student Report with Experiment Results and Analysis

Functional dependencies describe the relationship between attributes in a table and are
the foundation for normalization. Normalization is the process of organizing data to
minimize redundancy and ensure data integrity through a series of normal forms (1NF,
2NF, 3NF, etc.).

Sample Unnormalized Table

Consider the following table structure and data:

Student_Result(student_id, student_name, course, instructor, instructor_phone, marks)

 student_id student_name course instructor instructor_phone marks

1 Amit DBMS Sharma 9999988888 87

2 Priya OS Verma 8888877777 90

3 Karan DBMS Sharma 9999988888 78

1 Amit OS Verma 8888877777 80

Step 1: Identify Functional Dependencies

 student_id → student_name

 course → instructor, instructor_phone

 (student_id, course) → marks

Step 2: Convert to 1NF

All attributes must be atomic. The table is already in 1NF because all values are atomic.

Code:

CREATE TABLE Student_Result (

student_id INT,

student_name VARCHAR(50),

course VARCHAR(50),

instructor VARCHAR(50),

instructor_phone VARCHAR(15),

marks INT
);

Convert to 2NF

New Tables:

Students

CREATE TABLE Students (

student_id INT PRIMARY KEY,

student_name VARCHAR(50)

);

-- Sample Data

INSERT INTO Students VALUES (1, 'Amit'), (2, 'Priya'), (3, 'Karan');

Courses
CREATE TABLE Courses (

course VARCHAR(50) PRIMARY KEY,

instructor VARCHAR(50),

instructor_phone VARCHAR(15)

);

-- Sample Data

INSERT INTO Courses VALUES

('DBMS', 'Sharma', '9999988888'),

('OS', 'Verma', '8888877777');

Marks
CREATE TABLE Marks (

student_id INT,

course VARCHAR(50),

marks INT,

PRIMARY KEY (student_id, course)

);

-- Sample Data

INSERT INTO Marks VALUES

(1, 'DBMS', 87), (2, 'OS', 90), (3, 'DBMS', 78), (1, 'OS', 80);

Convert to 3NF
Remove transitive dependencies (non-prime attribute depending on another non-prime
attribute).
In this example, all tables after 2NF are also in 3NF.

Sample SELECT Query

-- Show student marks with instructor for each course

SELECT s.student_name, [Link], [Link], [Link]

FROM Marks m

JOIN Students s ON m.student_id = s.student_id

JOIN Courses c ON [Link] = [Link];

After Normalization: Tables in 2NF/3NF

Students Table

student_id student_name

1 Amit

2 Priya

3 Karan

Courses table

course instructor instructor_phone

DBMS Sharma 9999988888

OS Verma 8888877777

Marks Table

student_id course marks

1 DBMS 87

2 OS 90

3 DBMS 78

1 OS 80

Conclusion
 This practical demonstrated how to identify functional dependencies and systematically
normalize an unnormalized table up to 3NF. The process helps optimize data storage,
prevent redundancy, and maintain relational integrity in a database.

Inadequate Good Excellent

Evaluation Rubrics Marks
0% 50% 100%
The understanding of the student regarding the objective of
1 2
the given practical
2 Implementation of the Practical 2

3 Quality of the Result Analysis done 2

Quality of the report including concluding remarks and
4 2
Findings
5 Timely submission practical & self-learning activities. 2
10
Total Marks Obtained Out of 10

Date of Completion:___________________ Course Coordinator: Prof. G. D. Makwana

 J-1
GUJARAT TECHNOLOGICAL UNIVERSITY
GRADUATE SCHOOL OF ENGINEERING AND TECHNOLOGY

PRACTICAL – 10

Course Code & Name Database Management System (BE03000091)

Academic Term: 2025-26
Student Enrollment No: Batch:
Student Name:

AIM/Objective:

1 Implementation and Analysis of Indexing Techniques in Databases

Expected Outcome: CO/PO/PSO

Students will be able to create, use, and analyze different types of indexes,
1 understand their effect on query speed, and make informed decisions about index
selection when designing efficient databases.

Student Report with Experiment Results and Analysis

An index is a database object created on a table’s column to speed up data retrieval.

It works like a book index — instead of searching every page, you jump directly to the
needed page.
Indexes internally use B-Tree or Hash structures to quickly locate data.
Types of Indexes

1. Primary Index:

 Automatically created when a primary key is defined.

 Ensures uniqueness of the key column.

2. Unique Index:

 Prevents duplicate values in a column.

 Useful for columns like email, phone, username, etc.

 3. Simple Index:

 Normal index created on a single column to improve search speed.

4. Composite Index:

 Index created on multiple columns.

 Used when the WHERE clause contains more than one column.

 Example: Index on (city, salary) helps queries like: WHERE city='Delhi' AND
salary>50000

5. Full-text Index:

 Used for fast searching in text fields (articles, descriptions, documents).

Why Indexing is Needed?

 Without indexing, the database performs FULL TABLE SCAN, which is slow.

 With indexing, the database directly jumps to the required data block.

Advantages of Indexing

 Faster SELECT queries.

 Less searching time.

 Improves performance on large datasets.

 Helps in sorting and grouping operations.

Disadvantages of Indexing

 Extra storage space required.

 Slows down INSERT, UPDATE, DELETE because the index must be updated.
  Too many indexes reduce performance.

Analysis

Indexes improve read performance but add overhead to write operations.

Choosing the right columns to index is important for optimal database speed.

Code:

-- Create a table

CREATE TABLE Employees (

emp_id INT PRIMARY KEY,

emp_name VARCHAR(50),

department VARCHAR(50),

salary INT

);

-- Insert sample data

INSERT INTO Employees VALUES

(1, 'Amit', 'HR', 60000),

(2, 'Priya', 'IT', 65000),

(3, 'Karan', 'Finance', 72000),

(4, 'Sneha', 'IT', 68000),

(5, 'Isha', 'HR', 59000);

-- Create a unique index on emp_name

CREATE UNIQUE INDEX idx_emp_name ON Employees(emp_name);

-- Create a non-clustered index on department

CREATE INDEX idx_department ON Employees(department);

-- Create a composite index on department and salary

CREATE INDEX idx_dept_salary ON Employees(department, salary);

-- Example SELECT query to utilize indexes

SELECT * FROM Employees WHERE department = 'IT';

SELECT * FROM Employees WHERE emp_name = 'Amit';

-- Drop an index

DROP INDEX idx_dept_salary ON Employees;

Output:
 Conclusion:

This practical demonstrated how to implement indexing techniques such as primary,

unique, and non-clustered indexes in relational databases to improve query
performance. It also analyzed the impact of indexes on data retrieval efficiency and
database design.

Inadequate Good Excellent

Evaluation Rubrics Marks
0% 50% 100%
The understanding of the student regarding the objective of
1 2
the given practical
2 Implementation of the Practical 2

3 Quality of the Result Analysis done 2

Quality of the report including concluding remarks and
4 2
Findings
5 Timely submission practical & self-learning activities. 2
10
Total Marks Obtained Out of 10

Date of Completion:___________________ Course Coordinator: Prof. G. D. Makwana