SINDH MADRESSATUL ISLAM
UNIVERSITY (SMIU)
Advanced Database Management
System (ADBMS) - Lab Manual
Name: Muhammad Saad Habib
Student I'd: CSC-23F-203
Instructor: Miss tahira Iftikhar
Objectives:
Students will have the ability to:
• Keep abreast of current developments to continue their own
professional development.
• To engage themselves in lifelong learning of Database management
systems theories and technologies this enables them to purse higher
studies.
• To interact professionally with colleagues or clients located abroad
and the ability to overcome challenges that arises from geographic
distance, cultural differences, and multiple languages in the context
of computing.
• Develop team spirit, effective work habits, and professional attitude
in written and oral forms, towards the development of database
applications
Outcomes:
Students will be able to demonstrate
their skills
In drawing the ER, EER, and UML
Diagrams.
• In analyzing the business requirements and producing a viable model
for the implementation of the database.
• In converting the entity-relationship diagrams into relational tables.
• To develop appropriate Databases to a given problem that integrates
ethical, social, legal, and economic concerns.
Advanced Database Management
System (ADBMS) - Lab Manual
Index
SINDH MADRESSATUL ISLAM UNIVERSITY (SMIU) 1
Lab 1: ER 4
Modeling and Schema Creation 4
Lab 2: SQL Basics 5
Practicing DDL & DML Commands 5
Data Definition Language 5
Lab 3: Stored Procedures 6
Stored Procedure 1: Update Grade 6
Stored Procedure 2: Get Students by Department 6
Stored Procedure 3: Count Enrollments in Course 7
Lab 4: Joins 7
INNER JOIN: 7
LEFT JOIN: 7
RIGHT JOIN: 8
FULL OUTER JOIN (MySQL workaround using UNION): 8
UNION 8
Lab 5: Nested and Correlated Subqueries 8
Nested: 8
Lab 6: Views 9
View 1: Student Performance 9
View 2: Course Enrollment Count 10
Lab 7: Aggregation and Grouping 10
Lab 8: Normalization (To 3NF) 11
Unnormalized Relation: 11
Lab 9: Transactions 12
START TRANSACTION; 12
ROLLBACK; 12
Lab 1: ER
Modeling and Schema Creation
Objective: Design a university database and implement schema.
CREATE TABLE Students (
student_id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50)
);
CREATE TABLE Courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100),
credits INT
);
CREATE TABLE Enrollments (
student_id INT,
course_id INT,
semester VARCHAR(10),
grade CHAR(2),
PRIMARY KEY(student_id, course_id),
FOREIGN KEY(student_id) REFERENCES Students(student_id),
FOREIGN KEY(course_id) REFERENCES Courses(course_id)
);
Lab 2: SQL Basics
Practicing DDL & DML Commands
Data Definition Language
The data definition language is used to create an object, alter the structure of an object and
also drop already created object. The Data Definition Languages used for table definition
can be classified into following:
• Create table command
• Alter table command
• Truncate table command
• Drop table command
INSERT INTO Students VALUES (1, 'Alice', 'CS'), (2, 'Bob', 'IT'), (3,
'Clara', 'CS');
INSERT INTO Courses VALUES (101, 'DBMS', 4), (102, 'OS', 3), (103,
'Networks', 3);
INSERT INTO Enrollments VALUES (1, 101, 'Sem1', 'A'), (2, 101, 'Sem1',
'B'), (3, 102, 'Sem1', 'A');
SELECT name FROM Students WHERE department = 'CS';
Lab 3: Stored Procedures
Stored Procedure 1: Update Grade
DELIMITER //
CREATE PROCEDURE UpdateGrade(
IN sid INT,
IN cid INT,
IN new_grade CHAR(2)
)
BEGIN
UPDATE Enrollments SET grade = new_grade
WHERE student_id = sid AND course_id = cid;
END;
//
DELIMITER ;
CALL UpdateGrade(1, 101, 'A+');
Stored Procedure 2: Get Students by Department
DELIMITER //
CREATE PROCEDURE GetStudentsByDept(IN dept VARCHAR(50))
BEGIN
SELECT * FROM Students WHERE department = dept;
END;
//
DELIMITER ;
CALL GetStudentsByDept('CS');
Stored Procedure 3: Count Enrollments in Course
DELIMITER //
CREATE PROCEDURE CountEnrollments(IN cid INT)
BEGIN
SELECT COUNT(*) AS Total FROM Enrollments WHERE course_id = cid;
END;
//
DELIMITER ;
CALL CountEnrollments(101);
Lab 4: Joins
INNER JOIN:
sql
SELECT s.name, c.course_name, e.grade
FROM Students s
JOIN Enrollments e ON s.student_id = e.student_id
JOIN Courses c ON e.course_id = c.course_id;
LEFT JOIN:
sql
SELECT s.name, e.course_id
FROM Students s
LEFT JOIN Enrollments e ON s.student_id = e.student_id;
RIGHT JOIN:
sql
SELECT e.student_id, c.course_name
FROM Courses c
RIGHT JOIN Enrollments e ON c.course_id = e.course_id;
FULL OUTER JOIN (MySQL workaround using UNION):
sql
SELECT s.student_id, e.course_id
FROM Students s
LEFT JOIN Enrollments e ON s.student_id = e.student_id
UNION
SELECT s.student_id, e.course_id
FROM Students s
RIGHT JOIN Enrollments e ON s.student_id = e.student_id;
Lab 5: Nested and Correlated Subqueries
Nested:
sql
SELECT name FROM Students
WHERE student_id IN (
SELECT student_id FROM Enrollments WHERE grade = 'A'
);
Correlated:
sql
SELECT name FROM Students s
WHERE EXISTS (
SELECT * FROM Enrollments e
WHERE e.student_id = s.student_id AND e.grade = 'A'
);
Lab 6: Views
View 1: Student Performance
sql
CREATE VIEW StudentPerformance AS
SELECT s.name, c.course_name, e.grade
FROM Students s
JOIN Enrollments e ON s.student_id = e.student_id
JOIN Courses c ON c.course_id = e.course_id;
Query:
sql
SELECT * FROM StudentPerformance;
View 2: Course Enrollment Count
sql
CREATE VIEW CourseEnrollCount AS
SELECT c.course_name, COUNT(e.student_id) AS total_enrolled
FROM Courses c
LEFT JOIN Enrollments e ON c.course_id = e.course_id
GROUP BY c.course_name;
Lab 7: Aggregation and Grouping
sql
SELECT course_id, AVG(CASE
WHEN grade = 'A' THEN 4
WHEN grade = 'B' THEN 3
WHEN grade = 'C' THEN 2
ELSE 0
END) AS average_score
FROM Enrollments
GROUP BY course_id;
Lab 8: Normalization (To 3NF)
Unnormalized Relation:
text
Student(student_id, name, {course_id, course_name, grade})
Normalized Tables:
sql
-- Students
CREATE TABLE Students (
student_id INT PRIMARY KEY,
name VARCHAR(100)
);
-- Courses
CREATE TABLE Courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100)
);
-- Enrollments
CREATE TABLE Enrollments (
student_id INT,
course_id INT,
grade CHAR(2),
PRIMARY KEY(student_id, course_id),
FOREIGN KEY(student_id) REFERENCES Students(student_id),
FOREIGN KEY(course_id) REFERENCES Courses(course_id) );
Lab 9: Transactions
sql
START TRANSACTION;
UPDATE Students SET name = 'Test Name' WHERE student_id = 1;
ROLLBACK;
-- Check result
SELECT * FROM Students WHERE student_id = 1;
-- Again
START TRANSACTION;
UPDATE Students SET name = 'Final Name' WHERE student_id = 1;
COMMIT;
