0% found this document useful (0 votes)

29 views13 pages

MySQL DBMS Practical Guide

Q: Examine how the practical exercises demonstrate the use of aggregate functions in SQL. Why are these functions important for data analysis?

The exercises demonstrate the use of aggregate functions with examples such as COUNT, which counts the number of rows, AVG for calculating the average value of a column, and SUM for adding up all values in a column grouped by a certain criteria. MAX and MIN functions are also used to find the highest and lowest values respectively. These functions are crucial for data analysis as they allow users to quickly summarize and derive insights from large sets of data .

Q: Why are stored procedures important in database management, and how is this concept practically applied according to the exercises?

Stored procedures are vital as they allow the encapsulation of complex logic within the database, leading to enhanced performance through reduced client-server communication and improved security by providing controlled access to data. The exercises show creating a stored procedure 'GetStudentMarks' to retrieve a student's marks, demonstrating procedural logic execution within the database, making common tasks easily repeatable and more efficient .

Q: What are the steps involved in the installation and verification of MySQL as described in the practical exercises, and why is the verification step important?

The installation of MySQL involves downloading the MySQL Community Server from the official website, running the installer, and selecting the "Developer Default" setup type. Completing the configuration wizard includes setting up a root password and enabling MySQL service to start automatically. Verification is done using the command 'mysql --version', which should output the version number, ensuring that the installation is successful and the server is running properly .

Q: Analyze the role of triggers in maintaining database integrity and auditing changes, as demonstrated in the exercises.

Triggers are used to automatically perform a specified action when a certain event occurs in the database, helping maintain database integrity and provide a means to audit changes. In the exercises, a trigger was created to insert audit log entries into a 'student_audit' table every time a record in the 'students' table was updated. This helps track changes made to the data, ensuring transparency and aiding in problem diagnosis .

Q: How do common table expressions (CTEs) simplify complex queries in SQL, and what specific example was used in the exercises to demonstrate this?

CTEs simplify complex queries by allowing temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. They enhance the readability and manageability of SQL code. An example from the exercises demonstrates a recursive CTE to generate a sequence of numbers from 1 to 10, making it easier to work with lists of values or perform iterative operations without cumbersome code .

Q: How do primary keys and foreign keys contribute to database integrity when designing a table, and what other constraints were utilized in creating the tables?

Primary keys ensure uniqueness and allow each record in a table to be uniquely identified, while foreign keys enforce referential integrity by linking tables together and ensuring that relationships between tables remain consistent. In addition to primary and foreign keys, other constraints used include NOT NULL, which ensures that a column cannot have a NULL value, CHECK constraints for validating values in a column, and UNIQUE constraints which enforce the uniqueness of values within a column .

Q: What are the potential advantages and drawbacks of using JSON data handling in SQL databases?

JSON data handling allows the storage of semi-structured data alongside structured data, providing flexibility and enabling easy integration with applications that use JSON formats. However, using JSON can lead to complexity in data querying and updates since SQL requires more effort to fetch or manipulate deeply nested JSON objects. It may also affect performance compared to traditional structured data in relational models .

Q: Discuss the importance of transaction management in databases, with reference to the practical exercises on demonstrating ACID properties.

Transaction management is crucial for ensuring the ACID properties—Atomicity, Consistency, Isolation, and Durability—fundamental for reliable transactions in databases. The exercises include transactions to transfer marks between subjects, demonstrating rollback and commit operations to maintain data integrity in case of errors. This helps in preserving the database state accurately in the event of failures or concurrent transactions .

Q: How does index creation impact performance in SQL databases, and what was the effect demonstrated in the exercises?

Indexes significantly enhance query performance by allowing the database management system to locate and access data faster, reducing the need for full table scans. However, they can increase storage requirements and potentially slow down write operations due to the overhead of maintaining index data. In the exercises, an index was created on the students' class column and tested using the EXPLAIN statement to show improved query execution plans, illustrating its effectiveness .

Q: In what scenarios would using a view in SQL be beneficial, and how was a view utilized in the practical exercises?

Using views can be beneficial when you need to simplify complex queries, encapsulate complex logic, provide an additional layer of security by exposing only certain columns or rows, and to present aggregated data. In the practical exercises, a view 'student_marks_view' was created to join information from students, marks, and subjects tables, allowing for a simplified and consistent way to query data without repeatedly writing complex joins .

The document outlines a series of practical exercises for learning Database Management System (DBMS) concepts using MySQL. It includes installation, database creation, data manipulation, and advanced topics like triggers, stored procedures, and JSON data handling. Each practical is structured with objectives, procedures, and expected outputs to facilitate hands-on learning.

Uploaded by

yashyadav78621

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as DOCX, PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

29 views13 pages

MySQL DBMS Practical Guide

Uploaded by

yashyadav78621

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as DOCX, PDF, TXT or read online on Scribd

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 [Link] 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)

SHOW TABLES;

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;

-- Verify operations

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;

-- Limited rows with LIMIT

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 [Link], m.sub_code, [Link]

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

-- LEFT JOIN (all students even without marks)

SELECT s.roll_no, [Link], m.sub_code, [Link]

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, [Link]

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 =

m2.sub_code) as max_marks

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, [Link], sb.sub_name, [Link]

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 [Link], sb.sub_name, [Link]

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: ', [Link], ' -> ', [Link], ' | Class: ',

[Link], ' -> ', [Link]));

END //

DELIMITER ;

-- Test the trigger

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

SELECT * FROM student_audit;

Practical 12: Indexes

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

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

'school_admin'@'localhost';

-- 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

CREATE TABLE student_profiles (

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->>"$.[Link]" 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'),

('NoSQL Databases', 'About modern non-relational databases');

-- Search using MATCH AGAINST

SELECT * FROM articles

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

MODE);

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 [Link]
# Connect to database

connection = [Link](

host="localhost",

user="root",

password="yourpassword",

database="school_db"

# Execute query

cursor = [Link]()

[Link]("SELECT * FROM students")

result = [Link]()

for row in result:

print(row)

# Close connection

[Link]()

Common questions