DATABASE MANAGEMENT SYSTEMS
Name: _____________________________________________________ Roll No: ______
Sr. No Topic Date Sign
1. Creating an ER Diagram and Converting it to Relational
Schema
a. Bank Management System
b. College Admission System
c. Online Shopping System
2. Creating Tables and Defining Relationships
a. Create Students, Courses and Departments tables
b. Define Primary and Foreign Key relationships
c. Insert initial data into tables
3. Applying Data Types and Constraints
a. Define constraints on Student and Professor tables
b. Modify Courses table to add a DEFAULT constraint on credits
c. Implement a CHECK constraint on Age (must be >18)
4. SQL Data Definition Language (DDL) Commands
a. Alter the Students table to add ‘DateOfBirth’ column
b. Rename the Courses table to ‘Course Catalog’
c. Drop an unused table from the database
5. Performing Insert, Update, and Delete Operations
a. Insert records into Students and Professors tables
b. Update course credits for all courses in a specific department
c. Delete students who have not enrolled in any course
6. Implementing Integrity Constraints
a. Enforce FOREIGN KEY constraint between Students and
Courses
b. Implement NOT NULL constraint on Student Names
c. Add a UNIQUE constraint on Email column in Professors table
7. Retrieving and Sorting Data Efficiently
a. Retrieve distinct courses offered in the university
DATABASE MANAGEMENT SYSTEMS
b. Group students by department and count them
c. Order professors by salary in descending order
8. Performing Joins for Data Retrieval
a. INNER JOIN Students and Enrollments to display grades
b. LEFT JOIN Professors and Departments to show department
names
c. RIGHT JOIN Courses and Enrollments to list registered
students
9. Using Subqueries for Data Extraction
a. Find students who are enrolled in more than 3 courses
b. Retrieve courses with more than 50 enrollments
c. Display students who scored higher than the course average
10. Using Aggregate Functions for Data Analysis
a. Find the average grade of students in each course
b. Determine the highest and lowest professor salaries
c. Count the number of students in each department
MODULE 2
1. Managing Views for Better Query Performance
a.Create a view of all students enrolled in Computer Science
b.Drop an existing view of Enrolled Students
c.Retrieve data from a View displaying top-performing students
2. Implementing SQL Triggers for Automation
a. Prevent negative salary updates for professors
b. Automatically update student GPA after a grade is entered
c. Log deleted student records in an audit table
3. Handling Transactions in SQL
a.Implement a transaction for course enrollments
b.Use SAVEPOINT to partially rollback an enrollment
DATABASE MANAGEMENT SYSTEMS
c.Test ACID properties with fee payment
4. Implementing User Management and Security
a.Create a user and assign privileges
b. Grant read-only access to an external user
c. Revoke access from a specific user
5. Working with Date and Time in SQL
a. Retrieve the current system date
b. Calculate the duration between two dates
c. List students who registered within the last 30 days
6. Applying String Functions
a.Convert student names to uppercase
b. Extract domain from email addresses
c.Find the length of course names
7. Creating and Managing Indexes
a.Create an index on Student Names for faster search
b. Drop an index on Course table
c. Use an index to speed up a query for top-performing students
8. Optimizing Queries for Performance
a. Use EXPLAIN to analyze query execution time
b. Optimize a slow-running query using indexing
c.Use EXISTS instead of IN for better performance
9. Implementing Concurrency Control
a. Implement locking in a multi-user database
b. Demonstrate Deadlock detection and resolution
c. Use Isolation Levels for concurrent transactions
10. Implementing Database Backup and Recovery Methods
a. Take a full database backup and restore it
DATABASE MANAGEMENT SYSTEMS
b. Perform an incremental backup and apply recovery
c. Simulate a failure and recover using transaction logs
