Database Design | SQL Programming | Normalization | Transactions | Indexing | Query Optimization
Developed by Antrang Srivastava
B.Tech — Computer Science & Engineering (AI & ML)
Chandigarh University
Welcome to my Database Management Systems (DBMS) Laboratory Repository!
This repository contains 10 structured DBMS practical experiments covering fundamental and advanced concepts of relational database management systems.
The experiments demonstrate database modeling, relational schema design, SQL querying, normalization, database programming, transaction management, indexing, and query optimization.
Each experiment is organized into its own folder with SQL scripts (where applicable) and documentation.
- ✅ 10 DBMS laboratory experiments
- ✅ Structured SQL implementations
- ✅ Experiment-wise documentation
- ✅ Database design and integrity constraints
- ✅ Joins, subqueries, views, and recursive CTEs
- ✅ Stored procedures, triggers, and auditing
- ✅ Database normalization up to BCNF
- ✅ Transaction management and concurrency control
- ✅ Indexing and query performance analysis
- ✅ SQL query optimization techniques
| Field | Details |
|---|---|
| Student Name | Antrang Srivastava |
| UID | 25LBCS3232 |
| Course | B.Tech — CSE (AI & ML) |
| Section | AI/ML-2A |
| Semester | 3rd Semester |
| Subject | Database Management Systems |
| Subject Code | 25CSH-210 |
| University | Chandigarh University |
The main objectives of these laboratory experiments are:
- Understand fundamental concepts of relational database management systems.
- Design Entity-Relationship diagrams and convert them into relational schemas.
- Implement database integrity constraints using SQL.
- Perform data retrieval and manipulation using SQL queries.
- Understand joins, nested queries, aggregation, and query execution plans.
- Create database views and recursive Common Table Expressions.
- Implement stored procedures, triggers, and audit logging.
- Apply database normalization techniques from 1NF to BCNF.
- Understand transactions, ACID properties, and concurrency control.
- Evaluate indexing strategies and optimize SQL query performance.
| Exp. No. | Experiment Title | Key Concepts | Access |
|---|---|---|---|
| 01 | E-Commerce ER Diagram | Entities, Attributes, Relationships, Keys | View |
| 02 | Relational Schema & Integrity Constraints | CREATE TABLE, PK, FK, UNIQUE, CASCADE | View |
| 03 | Employee–Department–Project Database | SELECT, GROUP BY, HAVING, Aggregates | View |
| 04 | SQL Joins, Subqueries & EXPLAIN | INNER JOIN, LEFT JOIN, SELF JOIN, EXISTS | View |
| 05 | SQL Views & Recursive CTE | Views, Hierarchies, Recursive Queries | View |
| 06 | Stored Procedures, Triggers & Audit Logging | Procedures, Triggers, Transactions | View |
| 07 | Database Normalization | 1NF, 2NF, 3NF, BCNF, Lossless Join | View |
| 08 | Banking Transactions & Concurrency Control | COMMIT, ROLLBACK, SAVEPOINT, ACID | View |
| 09 | Railway Booking Indexing & Performance | Single Index, Composite Index, EXPLAIN ANALYZE | View |
| 10 | SQL Query Optimization & Performance Analysis | Indexing, Query Rewriting, EXPLAIN | View |
Aim: Design an Entity-Relationship model for an E-Commerce database system.
Topics Covered:
- Entity and attribute identification
- Primary and foreign keys
- Relationships and cardinality
- ER diagram representation
- Database modeling concepts
Aim: Convert an ER model into a relational database schema and implement integrity constraints.
Topics Covered:
- CREATE TABLE statements
- Primary Key and Foreign Key
- UNIQUE and NOT NULL constraints
- Referential integrity
- CASCADE and SET NULL operations
Aim: Create a relational database and implement different SQL queries for retrieving and analyzing employee data.
Database: CompanyDB
Topics Covered:
- Database and table creation
- INSERT and SELECT operations
- WHERE and ORDER BY
- Aggregate functions
- GROUP BY and HAVING
- CASE expressions
- Relational queries
Dataset:
- 30 Employees
- 5 Departments
- 8 Projects
Aim: Implement different SQL joins and subqueries and analyze query execution plans.
Topics Covered:
- INNER JOIN
- LEFT JOIN
- SELF JOIN
- Multi-table joins
- Correlated subqueries
- EXISTS
- Set-operation alternatives
- EXPLAIN statement
Database: CompanyDB
Aim: Create SQL views and implement recursive queries to analyze employee reporting relationships.
Topics Covered:
- CREATE VIEW
- Department salary summary
- Employee-manager hierarchy
- View updatability
- Recursive Common Table Expressions
- Reporting chains
- Hierarchical data retrieval
Database: CompanyDB
Aim: Implement database automation and validation using stored procedures and triggers.
Topics Covered:
- Stored procedures
- BEFORE INSERT triggers
- BEFORE UPDATE triggers
- AFTER UPDATE triggers
- Salary validation
- Employee transfer procedures
- Transaction handling
- Error handling and audit logging
Database: CompanyDB
Aim: Identify functional dependencies in a denormalized Sales table and normalize it step-by-step.
Topics Covered:
- Functional dependencies
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
- Boyce-Codd Normal Form (BCNF)
- Lossless join verification
- Dependency preservation checks
- Data redundancy elimination
Database: SalesDB
Aim: Implement banking transactions using SQL transaction management commands and demonstrate concurrency behavior.
Topics Covered:
- BEGIN and COMMIT
- ROLLBACK
- SAVEPOINT
- ACID properties
- Transaction isolation levels
- READ COMMITTED
- SERIALIZABLE
- Dirty reads
- Non-repeatable reads
- Two-session concurrency demonstrations
DBMS: PostgreSQL
Aim: Create a Railway Booking database containing at least 5,000 records and compare query performance before and after indexing.
Topics Covered:
- Generation of 6,000 sample records
- Single-column indexing
- Composite indexing
- EXPLAIN
- EXPLAIN ANALYZE
- Query execution plans
- Full table scans
- Index lookups
- Leftmost-prefix principle
- Query performance comparison
Database: RailwayDB
Aim: Generate a dataset of at least 10,000 rows, identify five inefficient SQL queries, analyze execution plans, and implement optimization techniques.
Topics Covered:
- Dataset generation using recursive CTEs
- Analysis of five SQL query cases
- Single-column and composite indexes
- Date-range query optimization
- LIKE pattern optimization
- Indexing WHERE conditions
- Correlated subquery rewriting
- JOIN-based optimization
- OR versus UNION
- EXPLAIN and EXPLAIN ANALYZE
- Before-and-after performance comparison
Database: QueryLabDB
Dataset: 10,000 employee records
| Technology | Purpose |
|---|---|
| MySQL 8.0+ | Relational database management |
| PostgreSQL | Transactions and concurrency demonstrations |
| SQL | Database definition, manipulation, and querying |
| MySQL Workbench | SQL development and query execution |
| pgAdmin 4 | PostgreSQL database administration |
| Visual Studio Code | Code editing and organization |
| Git | Version control |
| GitHub | Repository hosting and documentation |
| Markdown | Experiment documentation |
DBMS-Experiments/
│
├── Experiment-01/
│ └── E-Commerce ER Diagram & Documentation
│
├── Experiment-02/
│ └── Relational Schema & Constraints
│
├── Experiment-03/
│ └── Employee-Department-Project Database
│
├── Experiment-04/
│ └── SQL Joins, Subqueries & EXPLAIN
│
├── Experiment-05/
│ └── SQL Views & Recursive CTE
│
├── Experiment-06/
│ └── Stored Procedures, Triggers & Audit Logs
│
├── Experiment-07/
│ └── Database Normalization (1NF to BCNF)
│
├── Experiment-08/
│ └── Banking Transactions & Concurrency
│
├── Experiment-09/
│ └── Railway Booking Indexing & Performance
│
├── Experiment-10/
│ └── SQL Query Optimization
│
└── README.md
Each experiment folder contains its relevant SQL scripts and documentation. Experiment 01 focuses on an ER diagram.
Open a terminal and run:
git clone https://github.com/antrang0912-dotcom/DBMS-Experiments.gitThen navigate into the project directory:
cd DBMS-ExperimentsAlternatively, download the repository using Code → Download ZIP on GitHub.
For MySQL-based experiments:
- Install MySQL Server 8.0 or later.
- Open MySQL Workbench or another compatible SQL client.
- Connect to your local MySQL server.
For Experiment 08:
- Install PostgreSQL.
- Open pgAdmin 4 or the PostgreSQL command-line interface.
- Use two database sessions for concurrency demonstrations.
- Open the required experiment folder.
- Read its
README.mdfor prerequisites. - Open the numbered SQL scripts in your database client.
- Execute the scripts in the documented order.
- Observe the results and compare them with the expected behavior.
| Experiment | Database / Prerequisite |
|---|---|
| 01 | ER diagram — No database execution required |
| 02 | EcommerceDB |
| 03 | CompanyDB |
| 04 | Requires CompanyDB from Experiment 03 |
| 05 | Requires CompanyDB from Experiment 03 |
| 06 | Requires CompanyDB from Experiment 03 |
| 07 | SalesDB |
| 08 | PostgreSQL banking accounts table |
| 09 | RailwayDB |
| 10 | QueryLabDB |
Note: Run experiments on practice databases only. Some scripts modify data, create or remove database objects, and demonstrate transactions.
Actual execution plans and query timings can vary according to the database version, available indexes, dataset, and system configuration.
- ER Modeling
- Relational Schemas
- Entity Relationships
- Primary and Foreign Keys
- Database Constraints
- DDL and DML
- SELECT Statements
- Filtering and Sorting
- Aggregate Functions
- GROUP BY and HAVING
- Joins
- Nested and Correlated Subqueries
- Database Views
- Recursive CTEs
- Stored Procedures
- SQL Triggers
- Functional Dependencies
- Normalization
- Referential Integrity
- Data Consistency
- Audit Logging
- ACID Properties
- COMMIT
- ROLLBACK
- SAVEPOINT
- Isolation Levels
- Concurrency Control
- Database Indexing
- Single-Column Indexes
- Composite Indexes
- Query Execution Plans
- EXPLAIN
- EXPLAIN ANALYZE
- SQL Query Rewriting
Through these experiments, the following learning outcomes are addressed:
- Understanding database architecture and relational database concepts.
- Designing and implementing database schemas.
- Applying integrity constraints to maintain consistency.
- Writing efficient SQL queries for data retrieval.
- Understanding joins, views, and recursive queries.
- Implementing database automation through stored procedures and triggers.
- Applying normalization to reduce redundancy.
- Managing database transactions safely.
- Understanding concurrency and transaction isolation.
- Analyzing indexing strategies and SQL performance.
| Category | Details |
|---|---|
| Total Experiments | 10 |
| Primary Language | SQL |
| Database Systems | MySQL, PostgreSQL |
| Coverage | Beginner to Advanced DBMS |
| Documentation | Experiment-wise README files |
| Application Areas | E-Commerce, Company Management, Sales, Banking, Railway Booking, Employee Analytics |
This repository provides a structured collection of DBMS laboratory experiments demonstrating practical applications of relational database management systems.
The experiments progress from fundamental database design and SQL operations to advanced concepts such as normalization, stored procedures, transaction management, indexing, and query optimization.
The overall objective is to strengthen practical SQL knowledge, database design skills, data integrity understanding, and database performance analysis.
