DEPARTMENT OF

COMPUTER SCIENCE & ENGINEERING

Experiment 1
Student Name:Akash UID: 23bcs14008
Branch: BE CSE Section/Group: 604-B
Semester: 5 Date of Performance: 28/7/25
Subject Name: ADBMS Subject Code: 23CSP-333

Easy
1. Aim: Author-Book Relationship Using Joins and Basic SQL Operations
I. Design two tables — one for storing author details and the other for book
details.
II. Ensure a foreign key relationship from the book to its respective author. III.
Insert at least three records in each table.
IV. Perform an INNER JOIN to link each book with its author using the common author ID. V.
Select the book title, author name, and author’s country.
2. Objective:
• To create relational database tables for authors and their books.
• To establish a foreign key relationship between books and authors.
• To insert sample data into both the Authors and Books tables.
• To retrieve combined information from both tables using INNER JOIN.
• To display book titles along with corresponding author names and their countries.

3. Code and output:

I. CREATE TABLE Authors (

author_id INT PRIMARY

KEY, name

VARCHAR(100), country

VARCHAR(100)

);

II. CREATE TABLE Books (

book_id INT PRIMARY

KEY, title VARCHAR(100),

author_id INT,
 DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

III. INSERT INTO Authors

(author_id, name,
country) VALUES

(1, 'J.K. Rowling', 'United Kingdom'), (2,

'George R.R. Martin', 'United States'), (3,
'Haruki Murakami', 'Japan');

INSERT INTO Books (book_id, title, author_id) VALUES

(101, 'Harry Potter and the Sorcerer''s Stone', 1),
(102, 'A Game of Thrones', 2),
(103, 'Kafka on the Shore', 3);

IV and V.
SELECT
 DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
[Link] AS Book_Title,
[Link] AS Author_Name,
[Link] AS Author_Country
FROM
Books
INNER JOIN
Authors
ON
Books.author_id = Authors.author_id;

4. Learning Outcomes
• Understand how to establish relationships between multiple tables using foreign keys.
• Learn to apply the INNER JOIN clause to retrieve combined data from related tables.
• Gain practical experience in writing SELECT queries involving multiple columns across tables.
• Interpret and manipulate real-world data structures like authors and books using SQL.
• Understand how to use aliases to simplify query readability and improve clarity in complex queries.
 DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Medium
1. Aim: Department-Course Subquery and Access Control.
• Design normalized tables for departments and the courses they offer, maintaining
a foreign key relationship.
• Insert five departments and at least ten courses across those departments.
• Use a subquery to count the number of courses under each department.
• Filter and retrieve only those departments that offer more than two courses.
Grant SELECT-only access on the courses table to a specific user.

2. Objective:
• Design normalized relational tables for departments and their offered courses with a foreign key
relationship.
• Populate sample data by inserting five departments and at least ten courses across them.
• Use subqueries to count courses per department and filter departments offering more than two
courses.
• Implement access control by granting SELECT-only privileges on the courses table to a specific
user.

3. Code and output:

a) CREATE TABLE Department ( dept_id INT PRIMARY KEY, dept_name VARCHAR(100) NOT NULL

);

b) CREATE TABLE Course ( course_id INT PRIMARY KEY, course_name VARCHAR(100) NOT NULL, dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Department(dept_id) );

c) INSERT INTO Department (dept_id, dept_name) VALUES

(1, 'Computer Science & Engineering'),
(2, 'Mechanical Engineering'),

(3, 'Electrical Engineering'),

(4, 'Civil Engineering'),
(5, 'Information Technology');
 DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

d) SELECT dept_name, course_count

FROM (
SELECT d.dept_name, COUNT(c.course_id) AS course_count
FROM Department d
LEFT JOIN Course c ON d.dept_id = c.dept_id
GROUP BY d.dept_name )
AS dept_course_count
WHERE course_count > 2;

e) GRANT SELECT ON Course TO 'student_user'@'localhost';

 DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

4. Learning Outcomes
• Understand how to design normalized tables with primary–foreign key relationships.
• Gain skills in inserting structured data with realistic, domain-specific values.
• Apply subqueries and filtering to aggregate and analyze relational data.
• Learn to implement database access control using SQL GRANT privileges.
 DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Hard
1. Aim: Transaction Management and Savepoint Simulation in Student
Enrollments.

2. Objective:
• Design normalized tables for students, courses, and enrollments with proper relationships.
• Simulate a transaction using a savepoint and partial rollback.
• Handle errors gracefully by rolling back only faulty operations.
• Display joined results showing student names, course titles, and grades.

3. Code and output:

a) CREATE TABLE Students (

student_id INTEGER PRIMARY KEY AUTOINCREMENT, student_name
TEXT NOT NULL
);

b) CREATE TABLE Courses ( course_id INTEGER PRIMARY KEY AUTOINCREMENT,

course_title TEXT NOT NULL
);

c) CREATE TABLE Enrollments (

enrollment_id INTEGER PRIMARY KEY AUTOINCREMENT,
student_id INTEGER, course_id INTEGER, grade TEXT,
FOREIGN KEY(student_id) REFERENCES Students(student_id),
FOREIGN KEY(course_id) REFERENCES Courses(course_id)
);

d) INSERT INTO Students (student_name) VALUES

('Aarav Sharma'),
('Priya Patel'),
('Vikram Reddy');

INSERT INTO Courses (course_title) VALUES

('Mathematics'),
('Computer Science'),
('Physics');

DEPARTMENT OF
 COMPUTER SCIENCE & ENGINEERING

e) BEGIN TRANSACTION;

f) INSERT INTO Enrollments (student_id, course_id, grade) VALUES (1, 1, 'A');

g) SAVEPOINT sp1;

h) INSERT INTO Enrollments (student_id, course_id, grade) VALUES (2, 999, 'B');

i) ROLLBACK TO sp1;
j) COMMIT;

k) SELECT s.student_name, c.course_title, [Link]

FROM Enrollments e
JOIN Students s ON e.student_id = s.student_id
JOIN Courses c ON e.course_id = c.course_id;

4. Learning Outcomes
• Learn to design and link normalized tables using primary and foreign keys. Understand
transaction control with BEGIN, COMMIT, and ROLLBACK.
• Apply SAVEPOINT to handle partial rollbacks in multi-step operations.
• Execute multi-table joins to retrieve combined and meaningful results.
 UNIVERSITY INSTITUTE OF ENGINEERING
Department of Computer Science & Engineering
(BE-CSE/IT-5th Sem)

Subject Name: ADBMS

Subject Code: 23CSP-333

Submitted to: Submitted by:

Faculty name: [Link] KAur Name: Akash

UID: 23bcs14008

Section: 604

Group: ‘B’
 INDEX

Name: Akash Kaushal UID: 23bcs14008

Ex. Name of Experiments Date Conduct Viva Worksheet Total Remarks Signature
No (MM: 12) (MM: (Record) (MM: 30) (with date)
10) (MM: 8)

10
Experiment 3
Student Name: Akash UID: 23bcs14008
Branch: CSE Section/Group: 604/B
Semester: 05TH Date of Performance: 28/07/25
Subject Name: ADBMS Subject Code: 23CSP-333

1. Aim: To analyze, implement, and compare SQL query performance using execution plans, B-Tree
indexing, hash-based indexing (via computed hash columns), and other performance tuning strategies
in SQL Server for optimizing query efficiency and resource utilization.

Easy-Level Problem: -
Problem Title: Author-Book Relationship Using Joins and Basic SQL Operations

2. Objectives: -

• Create a table to store book details including title, author, genre, and published year.
• Insert a diverse set of records to simulate a real-world library.
• Run a query to search for books by title and analyze the execution plan.
• Create a B-Tree index on the title column.
• Re-run the query and compare the execution plan to observe improvements.

3. Implementation/Code: -

DROP TABLE IF EXISTS Books;

CREATE TABLE Books (

BookID INT AUTO_INCREMENT PRIMARY KEY,
Title VARCHAR(200),
Author VARCHAR(100),
Genre VARCHAR(50),
PublishedYear INT
);

INSERT INTO Books (Title, Author, Genre, PublishedYear) VALUES

('To Kill a Mockingbird', 'Harper Lee', 'Fiction', 1960),
('1984', 'George Orwell', 'Dystopian', 1949),
('The Great Gatsby', 'F. Scott Fitzgerald', 'Classic', 1925),
('A Brief History of Time', 'Stephen Hawking', 'Science', 1988),
('The Catcher in the Rye', 'J.D. Salinger', 'Fiction', 1951),
('The Hobbit', 'J.R.R. Tolkien', 'Fantasy', 1937),
('Sapiens', 'Yuval Noah Harari', 'Non-fiction', 2011),
('Pride and Prejudice', 'Jane Austen', 'Romance', 1813),
('The Alchemist', 'Paulo Coelho', 'Philosophy', 1988),
 ('The Da Vinci Code', 'Dan Brown', 'Thriller', 2003);

EXPLAIN SELECT * FROM Books WHERE Title = 'The Hobbit';

CREATE INDEX idx_title ON Books(Title);

EXPLAIN SELECT * FROM Books WHERE Title = 'The Hobbit';

SHOW INDEXES FROM Books;

4. Output:

5. Learning Outcomes: -
• Understand how to create and define a table with appropriate columns and constraints
in SQL.

• Learn to insert multiple records into a table using the INSERT INTO statement.

• Gain experience with the EXPLAIN command to analyze query execution plans.

• Understand the role of indexes in optimizing query performance and how to create
them.

• Learn to use SHOW INDEXES to view existing indexes and assess their impact on
query efficiency.

Medium-Level Problem: -
1. Problem Title: Course Registration Query with Multi-Column Index
2. Objectives: -
• Create a table for course registrations with fields such as semester and registration
date.
• Populate it with a large dataset covering multiple terms.
• Run a query that filters data by semester and a date range.
• Create a composite B-Tree index on semester and registration date.
• Use execution plans to analyze and compare performance before and after indexing.
3. Implementation/Code: -
DROP TABLE IF EXISTS
CourseRegistrations;

CourseRegistrations table
CREATE TABLE
CourseRegistrations
( RegistrationID INT
AUTO_INCREMENT
PRIMARY KEY,
StudentID INT,
CourseID INT,
Semester VARCHAR(20),
RegistrationDate DATE
);

INSERT INTO
CourseRegistrations (StudentID,
CourseID, Semester,
RegistrationDate) VALUES
(1001, 501, 'Fall 2023', '2023-08-10'),
(1002, 502, 'Fall 2023',
'2023-08-12'),
(1003, 503, 'Spring 2024', '2024-01-15'),
(1004, 504, 'Spring 2024',
'2024-01-18'),
(1005, 505, 'Fall 2024', '2024-08-09'),
(1006, 506, 'Fall 2024',
'2024-08-11'),
(1007, 507, 'Spring 2025',
'2025-01-10'),
(1008, 508, 'Spring 2025',
'2025-01-12'),
(1009, 509, 'Fall 2025', '2025-08-14'),
(1010, 510, 'Fall 2025',
'2025-08-15');

EXPLAIN
SELECT *
FROM CourseRegistrations
WHERE Semester = 'Fall 2025'
AND RegistrationDate
BETWEEN '2025-08-01' AND
'2025-08-31';
 CREATE INDEX
idx_semester_date ON
CourseRegistrations(Semester,
RegistrationDate);

EXPLAIN
SELECT *
FROM CourseRegistrations
WHERE Semester = 'Fall 2025'
AND RegistrationDate
BETWEEN '2025-08-01' AND
'2025-08-31';

SHOW INDEXES FROM

CourseRegistrations;

4. Output: -

5. Learning Outcomes:
● Learn how to create a table with multiple columns, including auto-incrementing
primary keys, in SQL
● Understand how to insert multiple rows of data into a table using the INSERT INTO
statement.
● Gain experience with the EXPLAIN command to analyze and optimize SQL queries.

Hard-Level Problem: -
Problem Title: Sales Query Optimization with Hash Index
1. Objectives: -
• Create a high-volume sales table with product code, sale date, quantity, and price.
• Insert thousands of records to mimic large-scale sales data.
• Query the table for all sales related to a specific product code.
• Apply or simulate hash-based indexing depending on system support.
• Optimize the query by selecting only necessary columns and using filters.
• Compare execution plans to assess the impact of indexing and tuning.

2. Implementation/Code: -
DROP TABLE IF EXISTS SalesInnoDB;
DROP TABLE IF EXISTS SalesMemory;
CREATE TABLE SalesInnoDB (
SaleID INT AUTO_INCREMENT PRIMARY KEY,
ProductCode VARCHAR(20),
SaleDate DATE,
Quantity INT,
Price DECIMAL(10,2)
) ENGINE=InnoDB;

CREATE TABLE SalesMemory (

SaleID INT AUTO_INCREMENT PRIMARY KEY,
ProductCode VARCHAR(20),
SaleDate DATE,
Quantity INT,
Price DECIMAL(10,2)
) ENGINE=MEMORY;

INSERT INTO SalesInnoDB (ProductCode, SaleDate, Quantity, Price) VALUES

('P1001', '2025-01-01', 2, 19.99),
('P1002', '2025-01-02', 1, 29.99),
('P1001', '2025-01-03', 3, 19.99),
('P1003', '2025-01-04', 1, 49.99),
('P1002', '2025-01-05', 2, 29.99),
('P1001', '2025-01-06', 5, 19.99),
('P1004', '2025-01-07', 2, 59.99),
('P1003', '2025-01-08', 1, 49.99),
('P1001', '2025-01-09', 4, 19.99),
('P1002', '2025-01-10', 3, 29.99),
('P1001', '2025-01-11', 6, 19.99),
('P1004', '2025-01-12', 1, 59.99),
('P1003', '2025-01-13', 2, 49.99),
('P1001', '2025-01-14', 2, 19.99),
('P1002', '2025-01-15', 1, 29.99),
('P1001', '2025-01-16', 3, 19.99),
('P1004', '2025-01-17', 2, 59.99),
('P1003', '2025-01-18', 1, 49.99),
('P1001', '2025-01-19', 5, 19.99),
('P1002', '2025-01-20', 2, 29.99);

INSERT INTO SalesMemory (ProductCode, SaleDate, Quantity, Price)

SELECT ProductCode, SaleDate, Quantity, Price FROM SalesInnoDB;

EXPLAIN SELECT * FROM SalesInnoDB WHERE ProductCode = 'P1001';

EXPLAIN SELECT SaleDate, Quantity, Price FROM SalesInnoDB

WHERE ProductCode = 'P1001';
 CREATE INDEX idx_productcode ON SalesInnoDB(ProductCode);

EXPLAIN SELECT SaleDate, Quantity, Price FROM SalesInnoDB

WHERE ProductCode = 'P1001';

EXPLAIN SELECT SaleDate, Quantity, Price FROM SalesMemory

WHERE ProductCode = 'P1001';

SHOW INDEXES FROM SalesInnoDB;

SHOW INDEXES FROM SalesMemory;

6. Output: -

7. Learning Outcomes: -
1. Understand the difference between InnoDB and MEMORY storage engines in terms of
data persistence and indexing capabilities
2. Learn how to create indexes to improve query performance and analyze their impact
using the EXPLAIN command.
3. Gain experience comparing query execution plans and index usage across different
storage engines.
 DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
Experiment 2
Student Name: Akash UID: 23bcs14008
Branch: BE CSE Section/Group: 604-B
Semester: 5 Date of Performance: 4/8/25
Subject Name: ADBMS Subject Code: 23CSP-333
Easy
1. Aim: Filtered Student View with Aggregated Data

2. Objective:
• Create tables for students and their subject-wise marks.
• Insert at least 5 students and 10 marks entries.
• Create a view that filters students who scored more than 80 marks.
• Query the view to get performance grouped by department.

3. Code/output:
a) CREATE TABLE Students
( student_id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50)
);

CREATE TABLE Marks (

mark_id INT PRIMARY KEY,
student_id INT, subject
VARCHAR(50), marks INT,
FOREIGN KEY (student_id) REFERENCES Students(student_id)
);

b) INSERT INTO Students VALUES

(1, 'Aarav Sharma', 'Computer Science'),
(2, 'Isha Verma', 'Electronics'),
(3, 'Rajesh Kumar', 'Mechanical'),
(4, 'Priya Singh', 'Computer Science'),
(5, 'Ananya Patel', 'Civil');

INSERT INTO Marks VALUES

(1, 1, 'Mathematics', 85),
(2, 1, 'Physics', 78),
(3, 2, 'Electronics', 90),
(4, 2, 'Mathematics', 88),
 DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
(5, 3, 'Mechanics', 75),
(6, 3, 'Thermodynamics', 82),
(7, 4, 'Programming', 95),
(8, 4, 'Data Structures', 89),
(9, 5, 'Surveying', 92),
(10, 5, 'Mathematics', 65);

c) CREATE VIEW HighScorers AS

SELECT [Link], [Link], [Link], [Link]
FROM Students s
JOIN Marks m ON s.student_id = m.student_id
WHERE [Link] > 80;

d) SELECT department, COUNT(*) AS high_scorer_count

FROM HighScorers
GROUP BY department;

4. Learning Outcomes
• Ability to design tables with meaningful relationships.
• Understanding how to filter data using views.
• Skills to group and summarize filtered records.
 DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
• Practice in retrieving structured information efficiently.
Medium
1. Aim: Logging Grade Updates with Triggers & Functions

2. Objective:
• Create tables for grades and update logs.
• Implement a function to validate allowed grades.
• Create a trigger to log all grade changes.
• Test logging by updating a grade.

3. Code/output:
a) CREATE TABLE Grades
( grade_id INT PRIMARY
KEY, student_id INT, course
VARCHAR(50), grade
CHAR(1)
);

CREATE TABLE GradeLogs ( log_id

INT PRIMARY KEY AUTO,
grade_id INT, old_grade CHAR(1),
new_grade CHAR(1), change_time TIMESTAMP DEFAULT
CURRENT_TIMESTAMP
);

b) INSERT INTO Grades VALUES

(1, 1, 'Mathematics', 'A'), (2,
2, 'Electronics', 'B'),
(3, 3, 'Mechanics', 'C');

c) DELIMITER $$
 DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
CREATE FUNCTION IsValidGrade(g CHAR(1))
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
RETURN g IN ('A', 'B', 'C', 'D', 'F');
END$$ DELI
MITER ;

d) DELIMITER $$
CREATE TRIGGER LogGradeUpdate
BEFORE UPDATE ON Grades
FOR EACH ROW
BEGIN
IF NOT IsValidGrade([Link]) THEN SIGNAL
SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid Grade!';
END IF;

INSERT INTO GradeLogs (grade_id, old_grade, new_grade)

VALUES (OLD.grade_id, [Link], [Link]);
END$$ DELI
MITER ;

e) UPDATE Grades SET grade = 'A' WHERE grade_id = 2;

f) SELECT * FROM GradeLogs;

4. Learning Outcomes
• Understanding of data validation through functions.
• Knowledge of creating triggers for data change tracking.
• Ability to record old and new values for auditing.
• Practical skill in maintaining change history.
Hard
1. Aim: GPA Summary Using Procedures and Cursors

2. Objective:
• Design student, course, and enrollment tables.
• Write a procedure to calculate GPA per department
• Use cursors to iterate through students.
• Display student name and GPA for a given department.
 DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

3. Code/output:
a) CREATE TABLE Students
( student_id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50)
);

CREATE TABLE Courses

( course_id INT PRIMARY
KEY, course_name
VARCHAR(50)
);

CREATE TABLE Enrollments ( enrollment_id

INT PRIMARY KEY,
student_id INT,
course_id INT,
grade
CHAR(1),
FOREIGN KEY (student_id) REFERENCES Students(student_id),
FOREIGN KEY (course_id) REFERENCES Courses(course_id) );

b) INSERT INTO Students VALUES

(1, 'Aarav Sharma', 'Computer Science'),
(2, 'Isha Verma', 'Computer Science'), (3,
'Rajesh Kumar', 'Mechanical');

INSERT INTO Courses VALUES

(1, 'Mathematics'),
(2, 'Programming'),
(3, 'Mechanics');

INSERT INTO Enrollments VALUES

(1, 1, 1, 'A'),
(2, 1, 2, 'B'),
(3, 2, 1, 'A'),
(4, 2, 2, 'A'),
(5, 3, 3, 'B');
 DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

c) DELIMITER $$
CREATE PROCEDURE GPA_By_Department(IN dept_name VARCHAR(50))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE sid INT;
DECLARE sname VARCHAR(50);
DECLARE total_points INT;
DECLARE total_courses INT;
DECLARE cur CURSOR FOR
SELECT student_id, name FROM Students WHERE department = dept_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP
FETCH cur INTO sid, sname;
IF done THEN
LEAVE read_loop; END
IF;

SELECT SUM(
CASE grade
WHEN 'A' THEN 10
WHEN 'B' THEN 8 WHEN
'C' THEN 6
WHEN 'D' THEN 4
WHEN 'F' THEN 0 END
 DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
), COUNT(*) INTO total_points, total_courses FROM
Enrollments WHERE student_id = sid;

SELECT sname AS Student_Name, (total_points / total_courses) AS GPA;

END LOOP;

CLOSE cur;
END$$ DELIMITER ;

d) CALL GPA_By_Department('Computer Science');

4. Learning Outcomes
• Hands-on with stored procedures in MySQL.
• Use of cursors for row-by-row processing.
• GPA calculation from grades using mappings.
• Dynamic filtering using procedure parameters.
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Experiment 4
Student Name: UID: 23bcs14008
Akash Branch: CSE Section/Group: 604/B
Semester: 05TH Date of Performance: / /25
Subject Name: ADBMS Subject Code: 23CSP-333

1. Aim: To understand and implement NoSQL database systems using Redis (key-value store) and
MongoDB (document store) by performing data modeling, CRUD operations, and evaluating
schema flexibility and performance for modern applications.
2. Objectives: -

 To explore non-relational data modeling approaches.

 To perform data storage and retrieval in Redis using key-value and hash operations.

 To perform document-oriented modeling and CRUD operations in MongoDB.

 To compare schema-less vs schema-based data storage.

 To demonstrate real-time data processing and flexibility in schema design.

Easy-Level Problem: -

Problem Title: Student Session Management using Redis

1. Objectives: -
 Use Redis to store session data where the student ID is the key and session token or timestamp is
the value.
 Set an expiration time for each key to simulate session timeout or auto-logout.
 Retrieve the session for an active student.
 Update the session data based on student activity (e.g., refresh login time).
 Delete session data upon logout or expiration.

Implementation/Code: -

CREATE TABLE IF NOT EXISTS student_sessions

( session_id INT AUTO_INCREMENT PRIMARY KEY,
student_id VARCHAR(20) NOT NULL,
session_token VARCHAR(50) NOT NULL,
login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_activity_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
expiry_time TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
);

DELIMITER //
CREATE PROCEDURE create_session(IN sid VARCHAR(20))
BEGIN
DECLARE token VARCHAR(50);
SET token = CONCAT('TOKEN_', FLOOR(RAND() * 1000000));

INSERT INTO student_sessions (student_id, session_token, expiry_time)

VALUES (sid, token, DATE_ADD(NOW(), INTERVAL 10 MINUTE));

SELECT CONCAT('Session created for student ', sid, ' with token: ', token) AS message;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE get_active_session(IN sid VARCHAR(20))
BEGIN
SELECT student_id, session_token, expiry_time
FROM student_sessions
WHERE student_id = sid
AND is_active = TRUE
AND expiry_time > NOW();
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE refresh_session(IN sid VARCHAR(20))
BEGIN
UPDATE student_sessions
SET expiry_time = DATE_ADD(NOW(), INTERVAL 10 MINUTE),
last_activity_time = NOW()
WHERE student_id = sid
AND is_active = TRUE
AND expiry_time > NOW();

SELECT CONCAT('Session for student ', sid, ' refreshed for 10 more minutes.') AS message;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE logout_session(IN sid VARCHAR(20))
BEGIN
DELETE FROM student_sessions
WHERE student_id = sid;

SELECT CONCAT('Session for student ', sid, ' deleted successfully.') AS message;
END //
DELIMITER ;
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

DELIMITER //
CREATE PROCEDURE cleanup_expired_sessions()
BEGIN
UPDATE student_sessions
SET is_active = FALSE
WHERE expiry_time <= NOW() AND is_active = TRUE;

SELECT CONCAT(ROW_COUNT(), ' expired sessions cleaned up.') AS result;

END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE show_active_sessions()
BEGIN
SELECT student_id, session_token, expiry_time
FROM student_sessions
WHERE is_active = TRUE AND expiry_time > NOW();
END //
DELIMITER ;

CALL create_session('S101');

CALL get_active_session('S101');

CALL refresh_session('S101');

CALL logout_session('S101');

CALL cleanup_expired_sessions();

CALL show_active_sessions();
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Output: -

Learning Outcomes: -

 Database Schema Design for Session Management.

 Implementing Stored Procedures for CRUD OperationsCreate and query SQL views for reusable
logic.

 Mastering the logic for managing a session's complete lifecycle.

 Token Generation and Security Fundamentals.

DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Medium-Level Problem: -

Problem Title: Student Profile Management using MongoDB.

2. Objectives: -

 Create a MongoDB collection named students and insert documents with nested fields
(personal info, academics, contact).

 Query for students belonging to a specific department with GPA greater than 8.

 Update only the required nested fields like email or address, not the entire document.

 Delete a student profile using their student ID.

Implementation/Code: -

CREATE TABLE IF NOT EXISTS personal_info

( student_id VARCHAR(20) PRIMARY KEY,
name VARCHAR(50),
age INT,
gender VARCHAR(10)
);
CREATE TABLE IF NOT EXISTS academics (
student_id VARCHAR(20),
department VARCHAR(50),
gpa DECIMAL(3,1),
FOREIGN KEY (student_id) REFERENCES personal_info(student_id)
ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS contact_info

( student_id VARCHAR(20),
email VARCHAR(100),
phone VARCHAR(15),
address VARCHAR(100),
FOREIGN KEY (student_id) REFERENCES personal_info(student_id)
ON DELETE CASCADE
);

INSERT INTO personal_info VALUES

('S101', 'Aarav Sharma', 20, 'Male'),
('S102', 'Priya Singh', 21, 'Female'),
('S103', 'Karan Patel', 22, 'Male');

INSERT INTO academics VALUES

DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
('S101', 'CSE', 9.1),
('S102', 'ECE', 7.8),
('S103', 'CSE', 8.5);

INSERT INTO contact_info VALUES

('S101', 'aarav@[Link]', '9876543210', 'Delhi, India'),
('S102', 'priya@[Link]', '9998887776', 'Mumbai, India'),
('S103', 'karan@[Link]', '9812345678', 'Ahmedabad, India');

DELIMITER //
CREATE PROCEDURE get_top_students(IN dept_name VARCHAR(50))
BEGIN
SELECT p.student_id, [Link], [Link], [Link], [Link], [Link]
FROM personal_info p
JOIN academics a ON p.student_id = a.student_id
JOIN contact_info c ON p.student_id = c.student_id
WHERE [Link] = dept_name AND [Link] > 8;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE
update_contact_info( IN sid
VARCHAR(20),
IN new_email VARCHAR(100),
IN new_address VARCHAR(100)
)
BEGIN
IF new_email IS NOT NULL THEN
UPDATE contact_info
SET email = new_email
WHERE student_id = sid;
END IF;

IF new_address IS NOT NULL THEN

UPDATE contact_info
SET address = new_address
WHERE student_id = sid;
END IF;

SELECT 'Contact information updated successfully.' AS message;

END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE delete_student(IN sid VARCHAR(20))
BEGIN
DELETE FROM personal_info WHERE student_id = sid;
SELECT CONCAT('Student with ID ', sid, ' deleted successfully.') AS message;
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
END //
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
DELIMITER ;

DELIMITER //
CREATE PROCEDURE show_all_students()
BEGIN
SELECT p.student_id, [Link], [Link], [Link],
[Link], [Link],
[Link], [Link], [Link]
FROM personal_info p
JOIN academics a ON p.student_id = a.student_id
JOIN contact_info c ON p.student_id = c.student_id;
END //
DELIMITER ;

CALL show_all_students();

CALL get_top_students('CSE');

CALL update_contact_info('S103', 'karan_new@[Link]', NULL);

CALL delete_student('S102');

CALL show_all_students();
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Output: -

Learning Outcomes: -

 Relational Database Design and Normalization.

 Implementing Referential Integrity with Foreign Keys.

 Advanced Data Retrieval with JOINs and Filtering.

 Conditional Logic and Data Modification.

DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Hard-Level Problem: -

Problem Title: Real-Time Scoreboard with Redis and MongoDB Integration

Objectives: -

 Use Redis sorted sets to store live quiz scores with student names as members and scores as
values.
 Update scores in real-time as students answer questions.
 Periodically sync the top scores and student data to MongoDB for long-term recordkeeping.
 Fetch performance history from MongoDB by filtering quiz data by date or topic.

Implementation/Code: -

CREATE TABLE IF NOT EXISTS personal_info

( student_id VARCHAR(20) PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT,
gender VARCHAR(10)
);

CREATE TABLE IF NOT EXISTS academics (

student_id VARCHAR(20) PRIMARY KEY,
department VARCHAR(50) NOT NULL,
gpa DECIMAL(3,1),
FOREIGN KEY (student_id) REFERENCES personal_info(student_id)
ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS contact_info

( student_id VARCHAR(20) PRIMARY KEY,
email VARCHAR(100)UNIQUE NOT NULL,
phone VARCHAR(15),
address VARCHAR(100),
FOREIGN KEY (student_id) REFERENCES personal_info(student_id)
ON DELETE CASCADE
);

INSERT INTO personal_info VALUES

('S101', 'Aarav Sharma', 20, 'Male'),
('S102', 'Priya Singh', 21, 'Female'),
('S103', 'Karan Patel', 22, 'Male'),
('S104', 'Deepa Varma', 19, 'Female');

INSERT INTO academics VALUES

DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
('S101', 'CSE', 9.1),
('S102', 'ECE', 7.8),
('S103', 'CSE', 8.5),
('S104', 'IT', 9.5);

INSERT INTO contact_info VALUES

('S101', 'aarav@[Link]', '9876543210', 'Delhi, India'),
('S102', 'priya@[Link]', '9998887776', 'Mumbai, India'),
('S103', 'karan@[Link]', '9812345678', 'Ahmedabad, India'),
('S104', 'deepa@[Link]', '8887776665', 'Kolkata, India');

DELIMITER //
CREATE PROCEDURE
register_student( IN sid VARCHAR(20),
IN sname VARCHAR(50),
IN sage INT,
IN sgender VARCHAR(10),
IN sdept VARCHAR(50),
IN sgpa DECIMAL(3,1),
IN semail VARCHAR(100),
IN sphone VARCHAR(15),
IN saddress VARCHAR(100)
)
BEGIN
START TRANSACTION;

INSERT INTO personal_info (student_id, name, age, gender)

VALUES (sid, sname, sage, sgender);

INSERT INTO academics (student_id, department, gpa)

VALUES (sid, sdept, sgpa);

INSERT INTO contact_info (student_id, email, phone, address)

VALUES (sid, semail, sphone, saddress);

COMMIT;
SELECT CONCAT('Student ', sid, ' registered successfully.') AS message;
END //

CREATE PROCEDURE get_top_students(IN dept_name VARCHAR(50), IN min_gpa DECIMAL(3,1))

BEGIN
SELECT p.student_id, [Link], [Link], [Link]
FROM personal_info p
JOIN academics a ON p.student_id = a.student_id
WHERE [Link] = dept_name
AND [Link] >= min_gpa
ORDER BY [Link] DESC;
END //
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
CREATE PROCEDURE
update_contact_info( IN sid VARCHAR(20),
IN new_email VARCHAR(100),
IN new_address VARCHAR(100)
)
BEGIN
UPDATE contact_info
SET email = COALESCE(new_email, email),
address = COALESCE(new_address, address)
WHERE student_id = sid;

SELECT CONCAT('Contact information for student ', sid, ' updated.') AS message;
END //

CREATE PROCEDURE delete_student(IN sid VARCHAR(20))

BEGIN
DELETE FROM personal_info WHERE student_id = sid;

SELECT CONCAT('Student with ID ', sid, ' and all related records deleted successfully.') AS message;
END //

CREATE PROCEDURE show_all_students()

BEGIN
SELECT p.student_id, [Link], [Link], [Link],
[Link], [Link],
[Link], [Link], [Link]
FROM personal_info p
JOIN academics a ON p.student_id = a.student_id
JOIN contact_info c ON p.student_id = c.student_id
ORDER BY p.student_id;
END //

DELIMITER ;
SELECT '--- 1. Initial State: Show all students ---' AS 'DEMO';
CALL show_all_students();

SELECT '--- 2. Register a New Student (S105) ---' AS 'DEMO';

CALL register_student(
'S105',
'Rahul Yadav',
21,
'Male',
'ECE',
8.9,
'rahul@[Link]',
'9000011111',
'Pune, India'
);
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
SELECT '--- 3. Find Top Students in CSE with GPA >= 8.5 ---' AS 'DEMO';
CALL get_top_students('CSE', 8.5);

SELECT '--- 4. Update S103''s contact info (only email changes) ---' AS 'DEMO';
CALL update_contact_info('S103', '[Link]@[Link]', NULL);

SELECT '--- 5. Verify the update for S103 ---' AS 'DEMO';

SELECT * FROM contact_info WHERE student_id = 'S103';

SELECT '--- 6. Delete student S102 (Triggers ON DELETE CASCADE) ---' AS 'DEMO';
CALL delete_student('S102');

SELECT '--- 7. Final State: Show all students (S102 should be gone) ---' AS 'DEMO';
CALL show_all_students();

Output: -
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Learning Outcomes: -

 Relational Database Design and Normalization.

 Implementing Referential Integrity with Foreign Keys.

 Advanced Data Retrieval with JOINs and Filtering.

 Conditional Logic and Data Modification.

DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Experiment 5
Student Name:Akash UID: 23bcs14008
Branch: CSE Section/Group: 604/B
Semester: 05TH Date of Performance: / /25
Subject Name: ADBMS Subject Code: 23CSP-333

1. Aim: To implement non-relational database techniques using Apache Cassandra for column-
family storage and Neo4j for graph-based modeling. The experiment demonstrates denormalized
data handling, optimized querying, and complex relationship modeling for scalable and efficient
analytics.

2. Objectives: -
 To understand and apply data modeling in column-family databases like Cassandra.
 To perform insert, update, and query operations in Cassandra.
 To construct and query graph databases using Neo4j and Cypher.
 To model and retrieve connected data using nodes and relationships.
 To evaluate denormalization and relationship representation in NoSQL systems.

Easy-Level Problem: -

Problem Title: Course Catalog Modeling in Cassandra

Objectives: -
 Design a column-family table named courses using department as the partition key, and course
ID and semester as clustering columns.
 Insert data for at least three departments and six different courses.
 Query all courses for a specific department and ensure results are sorted by semester.
 Explain how storing all related data together (denormalization) reduces the need for joins and
improves read performance.

Implementation/Code: -

CREATE TABLE courses

( department VARCHAR(50),
course_id VARCHAR(10),
semester VARCHAR(10),
course_name VARCHAR(100),
credits INT,
PRIMARY KEY (department, course_id, semester)
);

INSERT INTO courses VALUES ('CSE', 'CSE101', 'Sem1', 'Intro to Programming', 4);
INSERT INTO courses VALUES ('CSE', 'CSE102', 'Sem1', 'Data Structures', 4);
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
INSERT INTO courses VALUES ('CSE', 'CSE201', 'Sem3', 'DBMS', 3);
INSERT INTO courses VALUES ('ECE', 'ECE101', 'Sem1', 'Circuit Theory', 4);
INSERT INTO courses VALUES ('ECE', 'ECE102', 'Sem2', 'Signals & Systems', 4);
INSERT INTO courses VALUES ('MECH', 'ME101', 'Sem1', 'Engineering Mechanics', 4);

SELECT course_id, course_name, semester, credits

FROM courses
WHERE department = 'CSE'
ORDER BY semester;

Output: -

Learning Outcomes: -

 In the Course Catalog modeling, I understood how to design schemas using partition keys and
clustering columns to organize data efficiently.

 Implementing Stored Procedures for CRUD OperationsCreate and query SQL views for reusable
logic.

 Database Constraints and Performance.

 Token Generation and Security Fundamentals.

DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Medium-Level Problem: -

Problem Title: Student Friendship Network using Neo4j

1. Objectives: -
 Create student nodes with properties such as name, roll number, and batch.
 Establish FRIENDS_WITH relationships between students to form a social graph.
 Write a Cypher query to find all direct friends of a student.
 Use another Cypher query to find indirect friends (friends of friends) who are not directly
connected.

Implementation/Code: -
CREATE TABLE students (
roll VARCHAR(10) PRIMARY KEY,
name VARCHAR(100) NOT NULL,
batch INT
);

CREATE TABLE friendships (

student_roll VARCHAR(10) NOT NULL,
friend_roll VARCHAR(10) NOT NULL,
PRIMARY KEY (student_roll, friend_roll),
FOREIGN KEY (student_roll) REFERENCES students(roll),
FOREIGN KEY (friend_roll) REFERENCES students(roll)
);

INSERT INTO students (roll, name, batch) VALUES

('S001', 'Alice', 2022),
('S002', 'Bob', 2023),
('S003', 'Carol', 2022),
('S004', 'Dave', 2023),
('S005', 'Eve', 2022),
('S006', 'Frank', 2022);

INSERT INTO friendships (student_roll, friend_roll) VALUES

('S001', 'S002'), ('S002', 'S001'), -- Alice <-> Bob
('S001', 'S003'), ('S003', 'S001'), -- Alice <-> Carol
('S002', 'S004'), ('S004', 'S002'), -- Bob <-> Dave
('S002', 'S006'), ('S006', 'S002'), -- Bob <-> Frank
('S003', 'S005'), ('S005', 'S003'), -- Carol <-> Eve
('S004', 'S006'), ('S006', 'S004'); -- Dave <-> Frank

SELECT [Link], [Link], [Link]

FROM students s
JOIN friendships f ON f.friend_roll = [Link]
WHERE f.student_roll = 'S001'
ORDER BY [Link];

SELECT DISTINCT [Link], [Link], [Link]

DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
FROM friendships f1
JOIN friendships f2 ON f1.friend_roll = f2.student_roll
JOIN students fof ON f2.friend_roll = [Link]
WHERE f1.student_roll = 'S001'
AND [Link] <> 'S001'
AND [Link] NOT IN (
SELECT friend_roll FROM friendships WHERE student_roll = 'S001'
);
ORDER BY [Link];

Output: -

Learning Outcomes: -

 Relational Database Design and Normalization.

 Neo4j represent data as nodes and relationships, making it easier to query direct and indirect
connections.

 Database Constraints and Performance.

 Conditional Logic and Data Modification.

DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Hard-Level Problem: -

Problem Title: Academic Resource Mapping with Cassandra and Neo4j

Objectives: -

 In Cassandra, design a table resources_by_course with partition key as course ID and

clustering on semester and topic.
 Insert resource data for two different courses across two semesters.
 Query all topics and links for a specific course and semester combination.
 In Neo4j, create nodes for students, courses, and resources.
 Establish relationships: ACCESSED (student → resource), ENROLLED_IN (student →
course), BELONGS_TO (resource → course).

Implementation/Code: -

CREATE TABLE courses (

course_id VARCHAR(20) PRIMARY KEY,
course_name VARCHAR(200) NOT NULL
);

CREATE TABLE students (

student_id VARCHAR(20) PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE resources (
resource_id VARCHAR(20) PRIMARY KEY,
course_id VARCHAR(20) NOT NULL,
semester INT NOT NULL,
topic VARCHAR(200),
link VARCHAR(500),
FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);
CREATE TABLE enrolled_in (
student_id VARCHAR(20) NOT NULL,
course_id VARCHAR(20) NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);

CREATE TABLE accessed (

student_id VARCHAR(20) NOT NULL,
resource_id VARCHAR(20) NOT NULL,
accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
PRIMARY KEY (student_id, resource_id),
FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
FOREIGN KEY (resource_id) REFERENCES resources(resource_id) ON DELETE CASCADE
);

INSERT INTO courses (course_id, course_name) VALUES

('CSE101', 'Data Structures'),
('ECE201', 'Signals and Systems');

INSERT INTO students (student_id, name) VALUES

('S01', 'Arjun'),
('S02', 'Meera'),
('S03', 'Dev');

INSERT INTO resources (resource_id, course_id, semester, topic, link) VALUES

('R01', 'CSE101', 1, 'Introduction to Programming', '[Link]
('R02', 'CSE101', 2, 'Data Structures', '[Link]
('R03', 'ECE201', 1, 'Circuit Theory', '[Link]
('R04', 'ECE201', 2, 'Signals and Systems', '[Link]

INSERT INTO enrolled_in (student_id, course_id) VALUES

('S01', 'CSE101'),
('S02', 'ECE201'),
('S03', 'CSE101');

INSERT INTO accessed (student_id, resource_id) VALUES

('S01', 'R02'),
('S02', 'R04'),
('S03', 'R01');

SELECT topic, link

FROM resources
WHERE course_id = 'CSE101' AND semester = 2
ORDER BY topic;

SELECT s.student_id, [Link], r.resource_id, [Link], [Link], a.accessed_at

FROM accessed a
JOIN students s ON a.student_id = s.student_id
JOIN resources r ON a.resource_id = r.resource_id
WHERE r.course_id = 'CSE101'
ORDER BY [Link], a.accessed_at;

SELECT s.student_id, [Link], r.resource_id, [Link], [Link], [Link]

FROM students s
JOIN enrolled_in e ON s.student_id = e.student_id
JOIN resources r ON e.course_id = r.course_id
WHERE s.student_id = 'S01'
ORDER BY [Link], [Link];
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Output: -

Learning Outcomes: -

 Relational Database Design and Normalization.

 SQL Query Capabilities.

 Database Constraints and Performance.

 Neo4j represent data as nodes and relationships, making it easier to query direct and
indirect connections.
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Experiment 6
Student Name:Akash UID: 23bcs14008
Branch: CSE Section/Group: 604/B
Semester: 05TH Date of Performance: / /25
Subject Name: ADBMS Subject Code: 23CSP-333

1. Aim: To understand and implement foundational concepts of distributed databases such as replication,
horizontal partitioning, sharding, and eventual consistency through practical modeling of real-world
scenarios in multi-node environments.

2. Objectives: -
 To understand the architecture and working of distributed databases.
 To implement replication to ensure data availability and fault tolerance.
 To apply horizontal partitioning and sharding for scalable data distribution.
 To simulate scenarios exhibiting eventual consistency in a multi-node setup.

Easy-Level Problem: -

Problem Title: Bookstore Replication Simulation.

Objectives: -
 Design a Books table with fields like book ID, title, and stock quantity.
 Assume the table is replicated across two nodes: Node A and Node B.
 Simulate reading data (e.g., book stock) from Node A.
 Simulate writing or updating the stock from Node B.
 Explain how replication ensures both nodes are synchronized after changes.

Implementation/Code: -

CREATE TABLE Books (

book_id INT PRIMARY KEY,
title VARCHAR(100),
stock_quantity INT
);

INSERT INTO Books VALUES

(1, 'Database Systems', 10),
(2, 'Operating Systems', 5);

SELECT * FROM Books;

UPDATE Books
SET stock_quantity = 8
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
WHERE book_id = 1;

SELECT * FROM Books;

Output: -

Medium-Level Problem: -

Problem Title: Student Record Partitioning by Semester

1. Objectives: -
 Define a Students table with student details and current semester.
 Horizontally partition the table: semesters 1 & 2 on Node 1, 3 & 4 on Node 2.
 Insert student records into their respective nodes.
 Query across both nodes to retrieve students from a specific department.
 Explain how horizontal partitioning distributes the load and improves scalability.

Implementation/Code: -

CREATE TABLE Students_Node1

( roll_no INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
semester INT
);

CREATE TABLE Students_Node2

( roll_no INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
semester INT
);
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

INSERT INTO Students_Node1 VALUES

(101, 'Alice', 'CSE', 1),
(102, 'Bob', 'ECE', 2),
(103, 'Bhaskar', 'CSE', 2);

INSERT INTO Students_Node2 VALUES

(201, 'Carol', 'CSE', 3),
(202, 'Dave', 'MECH', 4),
(203, 'Balaji', 'CSE', 3);

SELECT * FROM Students_Node1 WHERE department = 'CSE';

Output: -

Hard-Level Problem: -

Problem Title: Order Sharding and Eventual Consistency Simulation

Objectives: -

 Create an Orders table with order ID, customer ID, total amount, and status.
 Shard the data based on customer region: Region A → Node 1, Region B → Node 2.
 Insert a new order into Node 1 (Region A).
 Simulate a delay before the data is reflected in Node 2.
 Explain how eventual consistency ensures both nodes will eventually show the same data,
even if temporarily out of sync.
 Discuss the suitability of this model for read-heavy operations like tracking and status checks.

Implementation/Code: -

CREATE TABLE Orders_Node1

( order_id INT PRIMARY KEY,
customer_id INT,
region VARCHAR(10),
total_amount DECIMAL(10,2),
status VARCHAR(20)
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
);

CREATE TABLE Orders_Node2

( order_id INT PRIMARY KEY,
customer_id INT,
region VARCHAR(10),
total_amount DECIMAL(10,2),
status VARCHAR(20)
);

INSERT INTO Orders_Node1 VALUES

(1001, 501, 'A', 2500.00, 'Pending'),
(1002, 502, 'A', 4800.50, 'Delivered');

INSERT INTO Orders_Node2 VALUES

(2001, 601, 'B', 3000.75, 'Pending'),
(2002, 602, 'B', 1200.00, 'Shipped');

SELECT * FROM Orders_Node1 WHERE region = 'A';

-- Simulate delay in replication (Node2 yet to receive Node1's update)

INSERT INTO Orders_Node1 VALUES (1003, 503, 'A', 5600.00, 'Processing');

-- Later reflected in Node2 (eventual consistency)

INSERT INTO Orders_Node2 VALUES (1003, 503, 'A', 5600.00, 'Processing');

SELECT * FROM Orders_Node2;

Output: -
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Learning Outcomes: -

 Distributed database design and data consistency models using a simulated e-commerce order
system.

 SQL Query Capabilities.

 Database Constraints and Performance.

 Suitability for Operations.

DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Experiment 7
StudentName: Akash UID: 23bcs14008
Branch: CSE Section/Group:604/B
Semester: 05TH Date of Performance: / /25
Subject Name: ADBMS Subject Code: 23CSP-333

1. Aim: To differentiate OLTP and OLAP systems, simulate ETL workflows, and design a basic data lake
setup that reflects real-world data flow, storage, and analysis processes in enterprise-scale environments.

2. Objectives: -
 To understand the architecture and operational differences between OLTP and OLAP
systems.
 To simulate an ETL (Extract, Transform, Load) pipeline for loading data into a data
warehouse or data lake.
 To model and implement a simplified data lake setup.
 To analyze the role of data lakes in enterprise analytics and decision-making.

Easy-Level Problem: -

Problem Title: OLTP vs. OLAP in Retail Store

Objectives: -
 Define two conceptual schemas: one for OLTP (real-time transactions) and another for OLAP
(aggregated analytics).
 OLTP schema handles frequent inserts like customer sales.
 OLAP schema stores summarized data such as daily or monthly sales metrics.
 Simulate an OLTP operation by inserting a sale record.
 Simulate an OLAP query that aggregates total sales for a month.
 Highlight three key differences: purpose (transaction vs. analysis), data volume, and response
time.

Implementation/Code: -

CREATE TABLE Sales_OLTP (

sale_id INT PRIMARY KEY,
customer_name VARCHAR(50),
product_name VARCHAR(50),
quantity INT,
sale_date DATE
);

CREATE TABLE Sales_OLAP (

month_year VARCHAR(7),
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
total_sales INT
);

INSERT INTO Sales_OLTP VALUES

(1, 'Alice', 'Laptop', 1, '2025-09-21');

INSERT INTO Sales_OLAP (month_year, total_sales)

SELECT DATE_FORMAT(sale_date, '%Y-%m') AS month_year, SUM(quantity) AS total_sales
FROM Sales_OLTP
GROUP BY month_year;

SELECT * FROM Sales_OLAP;

Output: -

Medium-Level Problem: -

Problem Title: ETL Workflow Simulation for Student Enrollment

1. Objectives: -
 Assume student data is stored in a raw CSV file with inconsistent formatting and null values.
 Extract: Load raw records from the file.
 Transform: Clean the data — fix dates, map department codes, and remove invalid entries.
 Load: Insert the cleaned data into a normalized SQL table with defined schema.
 Present a snapshot showing raw vs. cleaned data before and after each step.

Implementation/Code: -

CREATE TABLE Students_Raw

( roll_no VARCHAR(10),
name VARCHAR(100),
department_code VARCHAR(10),
dob VARCHAR(20),
batch INT
);

INSERT INTO Students_Raw VALUES

('S001', 'Alice', 'CSE', '21-09-2003', 2022),
('S002', 'Bob', 'ECE', '2003/08/15', 2022),
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
('S003', 'Bhaskar', 'CSE', '15-07-2002', 2022),
('S004', 'Balaji', 'ME', '10-01-2003', 2023),
('S005', 'Atul', 'ECE', '03-05-2003', 2022);

CREATE TABLE Students_Cleaned

( roll_no VARCHAR(10) PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
dob DATE,
batch INT
);

INSERT INTO Students_Cleaned (roll_no, name, department, dob, batch)

SELECT
roll_no,
name,
CASE department_code
WHEN 'CSE' THEN 'Computer Science'
WHEN 'ECE' THEN 'Electronics'
WHEN 'ME' THEN 'Mechanical'
ELSE NULL
END AS department,
CASE
WHEN dob LIKE '%-%' THEN STR_TO_DATE(dob, '%d-%m-%Y')
WHEN dob LIKE '%/%' THEN STR_TO_DATE(dob, '%Y/%m/%d')
ELSE NULL
END AS dob,
batch
FROM Students_Raw
WHERE roll_no IS NOT NULL
AND name IS NOT NULL
AND department_code IS NOT NULL
AND dob IS NOT NULL;

SELECT * FROM Students_Cleaned;

Output: -
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Hard-Level Problem: -

Problem Title: Mini Data Lake Design for Feedback Analytics

Objectives: -

 Assume incoming feedback data in multiple formats: CSV (survey), JSON (mobile app), and
logs (timestamped comments).
 Design a folder structure for storing raw files in a cloud-like hierarchy:
/feedback/{department}/{year}/.
 Organize the data without forcing a predefined schema.
 Describe how tools like Apache Spark or Hive can later read and analyze this raw data.
 Compare flexibility of a data lake (schema-on-read) vs. a data warehouse (schema-on-write).

Implementation/Code: -

CREATE TABLE Feedback_CSV

( feedback_id INT PRIMARY KEY,
student_name VARCHAR(100),
rating INT,
comments VARCHAR(255)
);

CREATE TABLE Feedback_JSON

( feedback_id INT PRIMARY KEY,
student_name VARCHAR(100),
app_feedback JSON
);

CREATE TABLE Feedback_Logs

( log_id INT PRIMARY KEY,
timestamp DATETIME,
message VARCHAR(255)
);

INSERT INTO Feedback_CSV VALUES

(1, 'Alice', 5, 'Great course'),
(2, 'Bob', 4, 'Good, but needs improvement');

INSERT INTO Feedback_JSON VALUES

(3, 'Carol', '{"experience": "smooth", "rating": 5}'),
(4, 'Dave', '{"experience": "laggy", "rating": 3}');

INSERT INTO Feedback_Logs VALUES

(101, '2025-11-01 10:00:00', 'Student 301: Excellent support'),
(102, '2025-11-01 10:05:00', 'Student 302: Add more quizzes');

SELECT * FROM Feedback_CSV;

DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
SELECT app_feedback FROM Feedback_JSON;
SELECT * FROM Feedback_Logs;

Output: -

Learning Outcomes: -

 Learned to create separate tables for different data sources or purposes (Feedback_CSV,
Feedback_JSON, Feedback_Logs) to keep the data organized and efficient.

 Learned that modern databases can store different types of data (simple structured data, complex
JSON data, and unstructured log messages) all in one system.

 Database Constraints and Performance.

 Easily read data from all three different table structures using standard SELECT
statements.
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Experiment 8
Student Name:Akash UID: 23bcs14008
Branch: CSE Section/Group: 604/B
Semester: 05TH Date of Performance: / /25
Subject Name: ADBMS Subject Code: 23CSP-333

1. Aim: To design and interact with cloud-based data storage systems using AWS RDS, Google Firebase,
and Azure Cosmos DB, focusing on relational modeling, real-time synchronization, and distributed data
access.

2. Objectives: -
 To understand the architecture and use cases of major cloud-based databases.
 To design and deploy a relational schema using AWS RDS.
 To configure real-time data operations using Firebase Realtime Database or Firestore.
 To explore distributed, globally available NoSQL models using Azure Cosmos DB.
 To compare data access patterns and consistency models across cloud platforms.

Easy-Level Problem: -

Problem Title: Student Data Management with AWS RDS

Objectives: -
 Use Firebase Realtime Database to create a path like /attendance/{date}/{student_id}.
 Add 3 attendance records with fields like student name and attendance status.
 Retrieve all records for a particular date.
 Demonstrate how updating attendance reflects instantly without page reload.
 Mention a key difference: Firebase uses JSON tree storage and real-time sync, unlike traditional
SQL databases.

Implementation/Code: -

CREATE TABLE Students

( student_id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50)
);

INSERT INTO Students VALUES

(101, 'Lucifer', 'CSE');

SELECT name
FROM Students
WHERE department = 'CSE';
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Output: -

Medium-Level Problem: -

Problem Title: Real-Time Attendance Tracking with Firebase

1. Objectives: -
 Use Firebase Realtime Database to create a path like /attendance/{date}/{student_id}.
 Add 3 attendance records with fields like student name and attendance status.
 Retrieve all records for a particular date.
 Demonstrate how updating attendance reflects instantly without page reload.
 Mention a key difference: Firebase uses JSON tree storage and real-time sync, unlike
traditional SQL databases.

Implementation/Code: -

CREATE TABLE Attendance

( attendance_date DATE,
student_id VARCHAR(10),
student_name VARCHAR(50),
status VARCHAR(10),
PRIMARY KEY(attendance_date, student_id)
);

INSERT INTO Attendance VALUES

('2025-09-21', 'S001', 'Om', 'Present'),
('2025-09-21', 'S002', 'Ajay', 'Absent'),
('2025-09-21', 'S003', 'Punit', 'Present');

SELECT * FROM Attendance

WHERE attendance_date = '2025-09-21';

UPDATE Attendance
SET status = 'Present'
WHERE attendance_date = '2025-09-21' AND student_id = 'S002';

SELECT * FROM Attendance

WHERE attendance_date = '2025-09-21';
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
Output: -

Hard-Level Problem: -

Problem Title: Multi-Region Record Access with Azure Cosmos DB

Objectives: -

 Create a StudentRecords container using Cosmos DB (SQL API).

 Insert three JSON documents, each with fields like ID, name, region, and program.
 Query the database to return all student records from a specific region.
 Explain how Cosmos DB maintains data consistency and availability across geographically
distributed regions.
 Describe a use case like a university with campuses in multiple countries requiring fast local
access to shared data.

Implementation/Code: -

CREATE TABLE StudentRecords_RegionA

( id INT PRIMARY KEY,
name VARCHAR(100),
region VARCHAR(50),
program VARCHAR(50)
);

CREATE TABLE StudentRecords_RegionB

( id INT PRIMARY KEY,
name VARCHAR(100),
region VARCHAR(50),
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
program VARCHAR(50)
);

INSERT INTO StudentRecords_RegionA VALUES

(1, 'Alice', 'Asia', 'Computer Science'),
(2, 'Bob', 'Asia', 'Electronics'),
(3, 'Charlie', 'Asia', 'Mechanical');

INSERT INTO StudentRecords_RegionB VALUES

(4, 'David', 'Europe', 'Computer Science'),
(5, 'Eve', 'Europe', 'Civil'),
(6, 'Frank', 'Europe', 'AI');

SELECT * FROM StudentRecords_RegionA WHERE region = 'Asia';

Output: -

Learning Outcomes: -

 Learned how to design and query relational databases for student records, courses, and
attendance.

 Worked with cloud databases like AWS RDS and NoSQL systems like Firebase for real-time
and scalable storage.

 Database Constraints and Performance.

 Learned how each type of database system is useful for different real-world
applications.
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

Experiment 9
Student Name: UID: 23bcs14008
Akash Branch: CSE Section/Group: 604/B
Semester: 05TH Date of Performance: / /25
Subject Name: ADBMS Subject Code: 23CSP-333

1. Aim: To implement secure database practices including role-based access control, SQL injection
prevention, and encryption to protect sensitive data in real-world applications.

2. Objectives: -
 To understand and apply Role-Based Access Control (RBAC) in database environments.
 To identify and prevent SQL injection vulnerabilities using best practices.
 To encrypt sensitive data during storage and transmission.
 To evaluate and compare database security mechanisms.
 To simulate real-world security threats and analyze mitigation techniques.

Easy-Level Problem: -

Problem Title: Role-Based Access in Library Database

Objectives: -
 Create two tables: Users(user_id, name, role) and Books(book_id, title, author).
 Insert users with roles such as 'admin', 'librarian', and 'student'.
 Insert a few sample records into the Books table.
 Use SQL GRANT statements to provide:
 SELECT access to students
 ALL PRIVILEGES to librarians and admins
 Use REVOKE if needed to test restrictions.
 Verify access behavior by simulating operations from users of different roles.
 Discuss how role-based access control (RBAC) ensures that users only perform allowed actions.

Implementation/Code: -

CREATE TABLE Users (

user_id INT PRIMARY KEY,
name VARCHAR(100),
role VARCHAR(50)
);

CREATE TABLE Books (

book_id INT PRIMARY KEY,
title VARCHAR(100),
author VARCHAR(100)
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
);

INSERT INTO Users VALUES

(1, 'Alice', 'admin'),
(2, 'Bob', 'librarian'),
(3, 'Charlie', 'student');

INSERT INTO Books VALUES

(101, 'Database Systems', 'Elmasri'),
(102, 'Operating Systems', 'Galvin'),
(103, 'Computer Networks', 'Tanenbaum');

SELECT * FROM Books WHERE 'student' IN (SELECT role FROM Users WHERE name='Charlie');

Output: -

Medium-Level Problem: -

Problem Title: SQL Injection Prevention in Login System

1. Objectives: -
 Create a Users(user_id, username, password) table and insert three accounts.
 Write a basic login verification query using input values (simulate insecure dynamic query).
 Demonstrate an SQL injection attempt using input like ' OR '1'='1.
 Explain how such an input bypasses authentication logic.
 Rewrite the login logic using parameterized queries or input validation.
 Show how the updated version blocks injection attacks.

Implementation/Code: -

CREATE TABLE Users (

user_id INT PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(50)
);
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
INSERT INTO Users VALUES
(1, 'Alice', 'alice123'),
(2, 'Bob', 'bob123'),
(3, 'Charlie', 'charlie123');

SELECT * FROM Users WHERE username = '' OR '1'='1' AND password = '';

PREPARE secure_stmt FROM

'SELECT * FROM Users WHERE username = ? AND password = ?';
SET @user = 'Bob';
SET @pass = 'bob123';
EXECUTE secure_stmt USING @user, @pass;
DEALLOCATE PREPARE secure_stmt;

Output: -

Hard-Level Problem: -

Problem Title: Encrypting Sensitive Data in Patient Records

Objectives: -

 Create a Patients(patient_id, name, diagnosis, email) table.

 Use a basic encryption method (e.g., AES, SHA, or custom) to encrypt the diagnosis value
before insertion.
 Insert encrypted patient records into the table.
 Query the raw data to show that diagnosis field is unreadable without decryption.
 Implement a decryption function or logic to retrieve the diagnosis only for authorized users.
 Discuss the importance of encryption in domains like healthcare, where privacy is critical.

Implementation/Code: -

CREATE TABLE Patients

( patient_id INT PRIMARYKEY,
name VARCHAR(100),
diagnosis VARBINARY(255),
email VARCHAR(100)
);
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING

SET @key = 'secretkey';

INSERT INTO Patients VALUES

(1, 'Alice', AES_ENCRYPT('Flu', @key), 'alice@[Link]'),
(2, 'Bob', AES_ENCRYPT('Diabetes', @key), 'bob@[Link]'),
(3, 'Charlie', AES_ENCRYPT('Asthma', @key), 'charlie@[Link]');

SELECT * FROM Patients;

SELECT patient_id, name, AES_DECRYPT(diagnosis, @key) AS diagnosis, email FROM Patients;

Output: -

Learning Outcomes: -

 Understand how to protect private data (like diagnosis) using encryption in MySQL

 Understand what SQL Injection is and how it compromises database security.

 Understand the concept of Role-Based Access Control (RBAC) in databases.

 Learned how each type of database system is useful for different real-world
applications.

 Develop awareness of security and authorization in multi-user environments.