title: "DBMS Practical Labs" description: "Practice Database Management System concepts through hands-on exercises covering database installation, SQL commands, joins, subqueries, views, indexes, transactions, normalization, and MongoDB."
DBMS Practical Labs
1. Introduction
DBMS practical labs provide hands-on experience with database creation, data manipulation, querying, database design, and administration.
The practical exercises in this section help learners understand how database concepts are implemented using real database management systems.
You will work with relational databases such as MySQL and PostgreSQL and NoSQL databases such as MongoDB.
These practicals are designed to develop database programming, problem-solving, and database management skills.
2. What You Will Learn
After completing these practical exercises, you will be able to:
- Install and configure database management systems.
- Create and manage databases and tables.
- Use SQL commands to manipulate data.
- Apply primary key, foreign key, and other constraints.
- Retrieve records using SQL queries.
- Perform different types of joins.
- Write nested queries and subqueries.
- Create and manage views and indexes.
- Understand stored procedures and triggers.
- Perform transaction operations.
- Apply database normalization.
- Work with MongoDB collections and documents.
- Perform CRUD operations in NoSQL databases.
3. Practical Lab Requirements
Software Requirements
| Software | Purpose |
|---|---|
| MySQL Community Server | Relational database |
| MySQL Workbench | Graphical database management |
| PostgreSQL | Relational database |
| pgAdmin | PostgreSQL management |
| MongoDB Community Server | NoSQL database |
| MongoDB Compass | MongoDB graphical interface |
| Redis | Key-value database practice |
| Visual Studio Code | Code editor |
| Command Line / Terminal | Execute database commands |
You do not need to install every database system at once. Start with MySQL and MongoDB, then explore other systems as required.
Basic Knowledge Required
Before starting these labs, you should understand:
- Database and DBMS fundamentals.
- Tables, rows, and columns.
- Primary and foreign keys.
- SQL syntax.
- Basic relational algebra.
- Database relationships.
- Basic programming concepts.
4. DBMS Practical Lab List
Complete the following practical exercises in sequence.
Practical 1: DBMS Installation and Configuration
File: dbms-installation.md
Learn how to install and configure database software.
Topics include:
- Installing MySQL Server.
- Installing MySQL Workbench.
- Configuring a database connection.
- Installing PostgreSQL and pgAdmin.
- Installing MongoDB Community Server.
- Installing MongoDB Compass.
- Connecting to databases.
- Executing your first database commands.
Practical 2: DDL and DML Practicals
File: ddl-and-dml-practicals.md
Practice database creation and data manipulation.
Topics include:
- CREATE DATABASE.
- CREATE TABLE.
- ALTER TABLE.
- DROP TABLE.
- TRUNCATE TABLE.
- INSERT INTO.
- UPDATE.
- DELETE.
- Primary key and foreign key constraints.
- NOT NULL, UNIQUE, CHECK, and DEFAULT constraints.
Practical 3: SQL Joins and Subqueries
File: sql-joins-and-subqueries.md
Learn how to retrieve related information from multiple tables.
Topics include:
- INNER JOIN.
- LEFT JOIN.
- RIGHT JOIN.
- FULL OUTER JOIN.
- CROSS JOIN.
- SELF JOIN.
- Equi Join.
- Natural Join.
- Nested queries.
- Single-row and multiple-row subqueries.
- Correlated subqueries.
- EXISTS and NOT EXISTS.
Practical 4: Views, Indexes, Procedures and Triggers
File: views-indexes-procedures-and-triggers.md
Learn advanced SQL database objects.
Topics include:
- Creating and managing views.
- Creating and dropping indexes.
- Unique and composite indexes.
- Stored procedures.
- Procedure parameters.
- Conditional statements.
- Loops.
- BEFORE and AFTER triggers.
- OLD and NEW values.
- Practical audit logging.
Practical 5: Normalization and Transaction Practicals
File: normalization-and-transaction-practicals.md
Practice database normalization and transaction management.
Topics include:
- Identifying database anomalies.
- Functional dependencies.
- First Normal Form (1NF).
- Second Normal Form (2NF).
- Third Normal Form (3NF).
- Boyce-Codd Normal Form (BCNF).
- Lossless decomposition.
- Transaction creation.
- COMMIT and ROLLBACK.
- SAVEPOINT.
- ACID properties.
- Concurrent transaction concepts.
Practical 6: MongoDB Practicals
File: mongodb-practicals.md
Learn practical operations using MongoDB.
Topics include:
- Installing MongoDB.
- Creating databases and collections.
- Inserting documents.
- Retrieving documents.
- Filtering and sorting.
- Updating documents.
- Deleting documents.
- Embedded documents and references.
- Aggregation pipeline.
- Index creation.
- Basic MongoDB database design.
5. Suggested Practical Learning Sequence
Follow this sequence to develop your database skills progressively.
DBMS Installation | v Database and Table Creation | v DDL and DML Commands | v SELECT and Filtering | v SQL Joins and Subqueries | v Views and Indexes | v Procedures and Triggers | v Normalization and Transactions | v MongoDB Practicals | v Database Projects
6. Sample Database for Practical Exercises
Throughout the SQL practicals, you can use a common college database.
Database Name
CREATE DATABASE college;
For MySQL, select the database:
USE college;
Suggested Tables
| Table | Purpose |
|---|---|
| students | Stores student information |
| departments | Stores department information |
| courses | Stores course information |
| teachers | Stores teacher information |
| enrollments | Stores student course enrollments |
| marks | Stores student examination marks |
Sample Relationship Structure
DEPARTMENTS | | STUDENTS | | ENROLLMENTS | | COURSES | | TEACHERS
This sample database can be expanded as you progress through the practical exercises.
7. Practical Record Format
For every practical exercise, maintain a proper practical record.
Use the following format:
1. Practical Number
Mention the practical exercise number.
2. Aim
Write the objective of the practical.
3. Software Requirements
List the database software and tools used.
4. Theory
Explain the database concepts used in the exercise.
5. Procedure
Write the steps followed to complete the practical.
6. SQL Commands / Program
Include the complete SQL statements or database commands.
7. Output
Show the expected output or actual execution result.
8. Result
Write a short conclusion explaining what was achieved.
9. Viva Questions
Prepare important questions related to the practical.
8. Learning Outcomes
After completing the DBMS practical labs, you should be able to:
- Install and configure database systems.
- Create and manage relational databases.
- Write SQL queries for different operations.
- Apply database constraints and relationships.
- Retrieve information using joins and subqueries.
- Create views, indexes, stored procedures, and triggers.
- Apply normalization techniques.
- Perform transaction operations.
- Create and query MongoDB collections.
- Design databases for real-world applications.
9. Recommended Practice Projects
After completing the practical exercises, try building these database projects:
- Student Management System.
- Library Management System.
- Banking Management System.
- Hospital Management System.
- Course Registration System.
- Inventory Management System.
- Online Shopping Database.
These projects are available in the Database Projects section.
10. Conclusion
DBMS practical labs connect theoretical database concepts with real-world implementation.
Start with database installation and basic SQL commands, then gradually move toward joins, subqueries, advanced database objects, normalization, transactions, and NoSQL operations.
Regular practice will help you develop the skills needed to design, implement, and manage database systems.