CSA Practical File

Database Management System (DBMS) Practicals

Practical 1: MySQL Installation and Configuration

Objective: Install MySQL server and verify installation.

Procedure:

1. Download MySQL Community Server from official website

2. Run installer and choose "Developer Default" setup type

3. Complete configuration wizard with root password

4. Enable MySQL service to start automatically

Verification Command:

mysql --version

Expected Output:

mysql Ver 8.0.XX for Win64 on x86_64 (MySQL Community Server)

Practical 2: Database Creation and Basic Commands

Objective: Create database and demonstrate basic MySQL commands.

Procedure:

# Connect to MySQL server

mysql -u root -p

# Create database

CREATE DATABASE school_db;

# Show databases

SHOW DATABASES;

# Select database

USE school_db;

# Show tables (empty now)

Expected Output:

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| school_db |

| sys |

+--------------------+

Practical 3: Table Creation with Constraints

Objective: Create tables with primary key, foreign key and other
constraints.

Procedure:

CREATE TABLE students (

roll_no INT PRIMARY KEY,

name VARCHAR(100) NOT NULL,

gender CHAR(1) CHECK (gender IN ('M','F')),

dob DATE,

class VARCHAR(10)

);

CREATE TABLE subjects (

sub_code VARCHAR(10) PRIMARY KEY,

sub_name VARCHAR(50) UNIQUE,

credits INT DEFAULT 4

CREATE TABLE marks (

exam_id INT AUTO_INCREMENT PRIMARY KEY,

roll_no INT,

sub_code VARCHAR(10),

marks INT CHECK (marks BETWEEN 0 AND 100),

FOREIGN KEY (roll_no) REFERENCES students(roll_no),

FOREIGN KEY (sub_code) REFERENCES subjects(sub_code)

);

Verification:

DESCRIBE students;

DESCRIBE subjects;

DESCRIBE marks;

Practical 4: Data Manipulation Operations

Objective: Perform INSERT, UPDATE, DELETE operations.

Procedure:

-- Insert records

INSERT INTO students VALUES

(101, 'Amit Kumar', 'M', '2005-03-15', 'XII-A'),

(102, 'Priya Sharma', 'F', '2005-07-22', 'XII-B');

-- Update record

UPDATE students SET class = 'XII-A' WHERE roll_no = 102;

-- Delete record

DELETE FROM students WHERE roll_no = 101;

SELECT * FROM students;

Expected Output:

+---------+-------------+--------+------------+--------+

| roll_no | name | gender | dob | class |

+---------+-------------+--------+------------+--------+

| 102 | Priya Sharma | F | 2005-07-22 | XII-A |

+---------+-------------+--------+------------+--------+

Practical 5: Basic SELECT Queries

Objective: Demonstrate SELECT with WHERE, ORDER BY, and LIMIT.

Sample Data (Insert first):

INSERT INTO students VALUES

(101, 'Amit Kumar', 'M', '2005-03-15', 'XII-A'),

(102, 'Priya Sharma', 'F', '2005-07-22', 'XII-B'),

(103, 'Rahul Verma', 'M', '2005-05-10', 'XII-A'),

(104, 'Neha Gupta', 'F', '2005-01-30', 'XII-B'),

(105, 'Vikas Singh', 'M', '2005-11-05', 'XII-C');

Queries:

-- Basic SELECT

SELECT * FROM students;

-- Filter with WHERE

SELECT name, class FROM students WHERE gender = 'F';

-- Sorting with ORDER BY

SELECT name, dob FROM students ORDER BY dob DESC;

SELECT * FROM students LIMIT 3;

Practical 6: Aggregate Functions

Objective: Use COUNT, SUM, AVG, MAX, MIN functions.

Procedure:

-- Prepare sample marks data

INSERT INTO subjects VALUES ('MATH', 'Mathematics', 5);

INSERT INTO subjects VALUES ('PHY', 'Physics', 4);

INSERT INTO marks (roll_no, sub_code, marks) VALUES

(101, 'MATH', 85), (101, 'PHY', 78),

(102, 'MATH', 92), (102, 'PHY', 88),

(103, 'MATH', 76), (103, 'PHY', 82),

(104, 'MATH', 90), (104, 'PHY', 85);

-- Aggregate functions

SELECT COUNT(*) AS total_students FROM students;

SELECT AVG(marks) AS avg_marks FROM marks;

SELECT MAX(marks) AS highest, MIN(marks) AS lowest FROM marks;

SELECT sub_code, SUM(marks) AS total FROM marks GROUP BY sub_code;

Practical 7: Joins (INNER, LEFT, RIGHT)

Objective: Demonstrate different types of joins.

Procedure:

-- INNER JOIN

SELECT s.name, m.sub_code, m.marks

FROM students s INNER JOIN marks m ON s.roll_no = m.roll_no;

SELECT s.roll_no, s.name, m.sub_code, m.marks

FROM students s LEFT JOIN marks m ON s.roll_no = m.roll_no;

-- RIGHT JOIN (all subjects even without marks)

SELECT m.roll_no, m.sub_code, sb.sub_name, m.marks

FROM marks m RIGHT JOIN subjects sb ON m.sub_code = sb.sub_code;

Practical 8: Subqueries

Objective: Demonstrate subqueries with WHERE and FROM.

Procedure:

-- Students with marks > average

SELECT name FROM students

WHERE roll_no IN (

SELECT roll_no FROM marks

WHERE marks > (SELECT AVG(marks) FROM marks)

);

-- Marks info with calculated columns

SELECT sub_code, AVG(marks) as avg_marks,

(SELECT MAX(marks) FROM marks m2 WHERE m1.sub_code =

FROM marks m1

GROUP BY sub_code;

Practical 9: Views

Objective: Create and use views.

Procedure:

-- Create view
CREATE VIEW student_marks_view AS

SELECT s.roll_no, s.name, sb.sub_name, m.marks

FROM students s

JOIN marks m ON s.roll_no = m.roll_no

JOIN subjects sb ON m.sub_code = sb.sub_code;

-- Query the view

SELECT * FROM student_marks_view;

-- Update base table through view (if possible)

UPDATE student_marks_view SET marks = 90

WHERE roll_no = 101 AND sub_name = 'Mathematics';

Practical 10: Stored Procedures

Objective: Create and execute stored procedures.

Procedure:

DELIMITER //

CREATE PROCEDURE GetStudentMarks(IN student_id INT)

BEGIN

SELECT s.name, sb.sub_name, m.marks

FROM students s

JOIN marks m ON s.roll_no = m.roll_no

JOIN subjects sb ON m.sub_code = sb.sub_code

WHERE s.roll_no = student_id;

END //

DELIMITER ;

-- Execute procedure

CALL GetStudentMarks(102);
Practical 11: Triggers

Objective: Create triggers for data integrity.

Procedure:

-- Audit log table

CREATE TABLE student_audit (

action_id INT AUTO_INCREMENT PRIMARY KEY,

roll_no INT,

action_type VARCHAR(20),

action_date DATETIME,

changes TEXT

);

-- Create trigger for student updates

DELIMITER //

CREATE TRIGGER student_update_audit

AFTER UPDATE ON students

FOR EACH ROW

BEGIN

INSERT INTO student_audit (roll_no, action_type, action_date, changes)

VALUES (NEW.roll_no, 'UPDATE', NOW(),

CONCAT('Name: ', OLD.name, ' -> ', NEW.name, ' | Class: ',

OLD.class, ' -> ', NEW.class));

END //

DELIMITER ;

-- Test the trigger

UPDATE students SET name = 'Priya Verma' WHERE roll_no = 102;

SELECT * FROM student_audit;

Objective: Demonstrate index creation and effect on performance.

Procedure:

-- Create index

CREATE INDEX idx_student_class ON students(class);

-- Show indexes

SHOW INDEXES FROM students;

-- Test query with EXPLAIN

EXPLAIN SELECT * FROM students WHERE class = 'XII-A';

Practical 13: Transactions

Objective: Demonstrate ACID properties with transactions.

Procedure:

-- Start transaction

START TRANSACTION;

-- Try to transfer marks between subjects

UPDATE marks SET marks = marks - 5

WHERE roll_no = 101 AND sub_code = 'MATH';

UPDATE marks SET marks = marks + 5

WHERE roll_no = 101 AND sub_code = 'PHY';

-- Verify changes

SELECT * FROM marks WHERE roll_no = 101;

ROLLBACK; -- OR COMMIT;

Practical 14: User Management

Objective: Create users and manage permissions.

Procedure:

-- Create user

CREATE USER 'school_admin'@'localhost' IDENTIFIED BY 'admin123';

-- Grant privileges

GRANT SELECT, INSERT, UPDATE ON school_db.* TO

-- Show privileges

SHOW GRANTS FOR 'school_admin'@'localhost';

-- Revoke privileges

REVOKE INSERT ON school_db.* FROM 'school_admin'@'localhost';

Advanced Practicals

Practical 15: Backup and Restore

# Backup database (command line)

mysqldump -u root -p school_db > school_backup.sql

# Restore database

mysql -u root -p school_db < school_backup.sql

Practical 16: JSON Data Handling

-- Create table with JSON column

id INT PRIMARY KEY AUTO_INCREMENT,

roll_no INT UNIQUE,

profile_data JSON

);

-- Insert JSON data

INSERT INTO student_profiles (roll_no, profile_data) VALUES

(101, '{"hobbies": ["chess", "reading"], "address": {"city": "Delhi"}}'),

(102, '{"hobbies": ["dancing", "music"], "address": {"city": "Mumbai"}}');

-- Query JSON data

SELECT roll_no,

profile_data->>"$.address.city" AS city,

profile_data->>"$.hobbies[0]" AS primary_hobby

FROM student_profiles;

Practical 17: Full-Text Search

-- Create table with FULLTEXT index

CREATE TABLE articles (

id INT PRIMARY KEY AUTO_INCREMENT,

title VARCHAR(200),

content TEXT,

FULLTEXT(title, content)

);

-- Insert sample data

INSERT INTO articles (title, content) VALUES

('Database Systems', 'This article covers relational database systems'),

-- Search using MATCH AGAINST

SELECT * FROM articles

WHERE MATCH(title, content) AGAINST('database' IN NATURAL LANGUAGE

Practical 18: Common Table Expressions (CTE)

-- Recursive CTE example

WITH RECURSIVE number_sequence AS (

SELECT 1 AS n

UNION ALL

SELECT n + 1 FROM number_sequence WHERE n < 10

SELECT * FROM number_sequence;

Practical 19: Window Functions

-- Calculate running total and rank

SELECT

roll_no,

sub_code,

marks,

SUM(marks) OVER (PARTITION BY roll_no) AS total_marks,

RANK() OVER (ORDER BY marks DESC) AS rank

FROM marks;

Practical 20: Connecting MySQL with Programming Language

Python Example:

import mysql.connector
# Connect to database

connection = mysql.connector.connect(

host="localhost",

user="root",

password="yourpassword",

database="school_db"

# Execute query

cursor = connection.cursor()

cursor.execute("SELECT * FROM students")

result = cursor.fetchall()

for row in result:

print(row)

# Close connection

cursor.close()

connection.close()