Program: MTech/ MSc Artificial Intelligence & Cybersecurity
Year: 2025-26
Module: DBMS Lab Manual
Bridge Course/ Induction Credits: NA
Batch 3
REVA Academy for Corporate Excellence (RACE)
Faculty – Prof. Kiran Kumar KV
DBMS and SQL Laboratory Lesson Plan
Objective
The primary objective of this lab manual is to help students gain practical knowledge of database
concepts, SQL queries, and DBMS tools. By following this manual, students will design, implement,
and query relational databases using industry-standard tools and methodologies.
Contents
1. Lab Guidelines
2. Prerequisites
3. Week-Wise Lessons
4. Hands-On Exercises
5. Evaluation Criteria
Lab Guidelines
Attend each session punctually and regularly.
Ensure all software (e.g., MySQL Workbench, PostgreSQL) is installed on your system before the
session.
Submit deliverables by the specified deadlines.
Maintain a personal log of all queries, errors, and solutions.
Collaborate with peers but ensure all submissions are your own work.
For more information, visit the program LMS https://reva.linkstreet.in/
©Copyright 2023. REVA Academy for Corporate Excellence, REVA University. All Rights Reserved.
Program: MTech/ MSc Artificial Intelligence & Cybersecurity
Year: 2025-26
Module: DBMS Lab Manual
Bridge Course/ Induction Credits: NA
Batch 3
REVA Academy for Corporate Excellence (RACE)
Prerequisites
Basic understanding of database concepts (e.g., tables, primary keys, foreign keys).
Familiarity with relational algebra and normalization.
Install required tools:
o MySQL Workbench (or other DBMS software like PostgreSQL).
o ER Diagram Tools (e.g., Lucidchart, Draw.io, or inbuilt tools in DBMS software).
Week-wise Lab Lessons
Week 1: Introduction to SQL and Relational Databases
1. Activities:
o Introduction to DBMS:
Overview of relational database concepts.
Key terminologies: schemas, tables, attributes, primary and foreign keys.
o Pre-requisite Coursework:
Complete the "SQL and Relational Database 101" course by IBM.
IBM SQL and Relational Database 101
Topics include:
Database fundamentals.
Introduction to SQL commands (SELECT, INSERT, UPDATE,
DELETE).
Relationships and normal forms.
o Hands-On Practice:
MySQL installation and setup.
Execute basic SQL commands in MySQL Workbench.
2. Refer DBMS Installation File in LMS
3. Deliverables:
o Proof of IBM course completion. “SQL and Relational Databases 101”
o A basic MySQL setup document and execution log for commands like
CREATE TABLE, SELECT, etc.
For more information, visit the program LMS https://reva.linkstreet.in/
©Copyright 2023. REVA Academy for Corporate Excellence, REVA University. All Rights Reserved.
Program: MTech/ MSc Artificial Intelligence & Cybersecurity
Year: 2025-26
Module: DBMS Lab Manual
Bridge Course/ Induction Credits: NA
Batch 3
REVA Academy for Corporate Excellence (RACE)
Week 2: Entity-Relationship (ER) Modeling
1. Activities:
o Theory Discussion:
Importance of ER models in database design.
Explanation of entities, attributes, relationships, cardinality, and keys.
o Practical Task:
Design an ER model for the Online Baseball League Store
or library/bookstore system
Entities: Customer, Player, Team, Product, Order, Inventory, Order_Item.
Relationships:
Customers place orders.
Players belong to teams.
Teams use products.
Products are managed in the inventory.
Orders consist of multiple order items.
o Tool: Use MySQL Workbench to create and visualize the ER diagram.
2. Datasets:
Online Baseball League Store
library/bookstore system
3. Deliverables:
o MySQL Workbench ER Diagram file (.mwb).
o A report explaining design decisions (e.g., attribute selection, cardinality).
Week 3: SQL Query Practice and Data Population
1. Activities:
o SQL Data Population:
Populate the Online Baseball League Store database using INSERT statements.
o SQL Query Practice:
Solve problems from the SQLZoo Tutorial:
SQLZoo MySQL Tutorial
Focus Areas:
Basic SELECT statements.
WHERE clauses and filtering data.
JOIN operations.
Aggregations (GROUP BY, HAVING).
o Custom Query Tasks:
Write SQL queries for:
Retrieving team details for a specific player.
For more information, visit the program LMS https://reva.linkstreet.in/
©Copyright 2023. REVA Academy for Corporate Excellence, REVA University. All Rights Reserved.
Program: MTech/ MSc Artificial Intelligence & Cybersecurity
Year: 2025-26
Module: DBMS Lab Manual
Bridge Course/ Induction Credits: NA
Batch 3
REVA Academy for Corporate Excellence (RACE)
Listing products used by a specific team.
Retrieving all orders placed by a customer within a given date range.
Identifying products with low inventory levels.
2. Datasets:
IMDB or a sports league Dataset.
3. Deliverables:
o A SQL script file containing data population and query solutions.
o Screenshots of query outputs.
Week 4: Advanced SQL Queries and Problem-Solving
1. Activities:
o Advanced SQL Features:
Subqueries: Writing nested SELECT statements.
Joins: INNER, LEFT, RIGHT, FULL OUTER.
Case Expressions: Creating conditional query logic.
Triggers and Views: Using them for automation and abstraction.
o Practical Problem Solving:
Use the populated database to solve complex scenarios:
Retrieve the total revenue generated from all orders.
Identify the most popular products (based on order quantity).
Generate a report of inventory restock recommendations.
o Hands-On Projects:
Create a View for customer order summaries.
Write a Trigger to automatically update inventory when a new order is placed.
2. Datasets:
Employee Dataset
3. Deliverables:
o SQL scripts for advanced queries, views, and triggers.
o Output logs and a report explaining the solutions.
Week 5: Comprehensive DBMS large-scale Problem
1. Activities:
o Project: Implement the full database for the Online Baseball League Store.
Start from schema design (ER model).
Populate the database with realistic data.
For more information, visit the program LMS https://reva.linkstreet.in/
©Copyright 2023. REVA Academy for Corporate Excellence, REVA University. All Rights Reserved.
Program: MTech/ MSc Artificial Intelligence & Cybersecurity
Year: 2025-26
Module: DBMS Lab Manual
Bridge Course/ Induction Credits: NA
Batch 3
REVA Academy for Corporate Excellence (RACE)
Create complex queries to retrieve and analyze data.
Additional Features:
o
Apply constraints (e.g., UNIQUE, CHECK).
Optimize database performance using indexes.
Normalize tables to 3NF.
2. Datasets:
Real-world datasets from Kaggle or SQL public datasets
3. Deliverables:
o Final SQL script file (schema, data population, and queries).
o Project report summarizing:
ER diagram.
SQL implementation.
Query results and analysis.
Hands-on Exercises
Exercise 1: Basic SQL Commands
Create a table Students with attributes:
o StudentID (Primary Key)
o Name
o Age
o Department
o Email
Insert at least five records into the table.
Write queries to:
o Retrieve all student names and departments.
o Update the age of a specific student.
o Delete a student by ID.
Exercise 2: Designing an ER Diagram
Create an ER diagram for a hospital management system with the following entities:
o Doctors
o Patients
o Appointments
o MedicalRecords
Define relationships and cardinalities.
Exercise 3: Query Optimization
For more information, visit the program LMS https://reva.linkstreet.in/
©Copyright 2023. REVA Academy for Corporate Excellence, REVA University. All Rights Reserved.
Program: MTech/ MSc Artificial Intelligence & Cybersecurity
Year: 2025-26
Module: DBMS Lab Manual
Bridge Course/ Induction Credits: NA
Batch 3
REVA Academy for Corporate Excellence (RACE)
Analyze the execution time of a query using indexes.
Create an index on the Email column in the Students table and compare query performance.
Exercise 4: Data Filtering and Aggregation
Create a table Sales with attributes SaleID, ProductID, Quantity, Price, SaleDate.
Insert data into the table.
Write queries to:
o Calculate total sales revenue.
o Find the highest-selling product.
o Retrieve sales data for a specific month.
Exercise 5: Joins and Relationships
Create two tables: Customers and Orders.
Populate them with sample data.
Write queries to:
o List all orders placed by a specific customer.
o Find customers who haven’t placed any orders.
Exercise 6: Subqueries
Use the Sales table to write:
o A query to find products sold above the average price.
o A query to retrieve the second-highest revenue day.
Exercise 7: Views
Create a view CustomerOrders that combines Customers and Orders.
Use the view to retrieve:
o Total orders per customer.
o Details of customers who placed more than five orders.
Exercise 8: Triggers
Define a trigger on the Orders table to:
o Automatically update the inventory when a new order is placed.
Test the trigger functionality.
Exercise 9: Normalization
Design a database for a university system.
Normalize it to 3NF.
For more information, visit the program LMS https://reva.linkstreet.in/
©Copyright 2023. REVA Academy for Corporate Excellence, REVA University. All Rights Reserved.
Program: MTech/ MSc Artificial Intelligence & Cybersecurity
Year: 2025-26
Module: DBMS Lab Manual
Bridge Course/ Induction Credits: NA
Batch 3
REVA Academy for Corporate Excellence (RACE)
Provide reasons for each normalization step.
Exercise 10: Comprehensive Problem-Solving
Create a database for a movie rental system with the following requirements:
o Entities: Movies, Customers, Rentals.
o Relationships: Customers rent movies, rentals track due dates and late fees.
Write queries to:
o Retrieve overdue rentals.
o Find the most frequently rented movie.
o Calculate total late fees per customer.
Evaluation Metrics
Category Excellent (90- Good (75- Average (50-74%) Below Average
100%) 89%) (<50%)
Completeness Fully implemented Mostly complete, Incomplete Major portions
and accurate minor errors missing
Implementation Flawless setup and Few execution Moderate errors or Unable to execute
execution issues incomplete tasks tasks
Problem-Solving Advanced and Adequate solutions Basic solutions, Unable to solve
efficient solutions with scope for lacks complexity problems
improvement
Documentation Clear, professional, Adequate but lacks Minimal, lacks Unclear,
and well-structured depth or clarity detail unstructured
Creativity Highly innovative Some creative Minimal creativity No extra effort
and insightful elements shown
References
1. "SQL and Relational Database 101" by IBM.
2. Official MySQL Documentation: https://dev.mysql.com/doc/
3. SQLZoo Tutorials: https://sqlzoo.net/
For more information, visit the program LMS https://reva.linkstreet.in/
©Copyright 2023. REVA Academy for Corporate Excellence, REVA University. All Rights Reserved.
