Yashvardhan Jani |
Meetrajsinh Jadeja |
-
Instructors (Professors):
- Dr. Kalgi Gandhi - Lab Instructor, PDEU
- Dr. Debabrata Swain - Theory Lecture Instructor, PDEU
-
College: Pandit Deendayal Energy University, Gandhinagar, Gujarat, India
University Management System is a comprehensive Database Management System (DBMS) course project developed at Pandit Deendayal Energy University (PDEU), Gandhinagar, Gujarat, India. This project demonstrates real-world database design, implementation, and management principles through building a complete university operations platform.
The system efficiently manages:
- Student Information - Academic records, enrollment, and profiles
- Faculty Management - Instructor details, course assignments, and schedules
- Course Administration - Course catalogs, scheduling, and prerequisites
- Enrollment & Registration - Course registration, waitlists, and academic standing
- Department Operations - Departmental structure, resources, and administration
- Academic Planning - Grade management, transcripts, and performance tracking
This project bridges the gap between theoretical DBMS concepts and practical implementation, providing hands-on experience with real-world database challenges.
- Comprehensive entity-relationship modeling with normalized schema (3NF/BCNF)
- Efficient data storage with optimized indexing strategies
- Data integrity through constraints, keys, and triggers
- Support for complex queries and multi-table joins
- Role-based access control (RBAC) with DCL commands
- Transaction management with ACID properties
- Constraint enforcement (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL)
- Automated data validation through stored procedures and triggers
- Aggregate functions for reporting and analytics
- Subqueries and nested queries for complex data retrieval
- Views for simplified data access and security
- Performance monitoring and query optimization
- Stored procedures for business logic automation
- Triggers for automatic data validation and maintenance
- Views for secure and simplified data access
- Window functions for advanced analytics (optional)
- Optimized query execution with proper indexing
- Partitioning strategies for large datasets
- Connection pooling for efficient resource management
- Query optimization and performance tuning
| Category | Technology | Version |
|---|---|---|
| Database Engine | MySQL | 8.0+ |
| Query Language | SQL | SQL:2016 Standard |
| Procedural Language | PL/SQL | MySQL Compatible |
| IDE / Workbench | MySQL Workbench | Latest |
Before you begin, ensure you have the following installed:
- MySQL Server 8.0 or higher
- MySQL Workbench or any MySQL client
- Git for version control
- Basic SQL Knowledge (DDL, DML, DQL commands)
git clone https://github.com/YashvardhanJani/DBMS-Lab-Project.git
cd DBMS-Lab-Project- PDEU DBMS Lab Work - Complete SQL lab exercises and experiments covering all DBMS concepts
- ER Diagram - Complete Entity-Relationship Diagram in Canva
- Review the ER Diagram to understand system architecture
- Explore PDEU-DBMS-LabWork for foundational concepts
- Study the Database Schema section above
- Execute the SQL Scripts in sequence
- Practice Usage Examples
- Modify and extend the system for your use cases
-- Register a new student
INSERT INTO students (first_name, last_name, email, phone_number, date_of_birth,
gender, enrollment_date, department_id, status)
VALUES ('John', 'Doe', '[email protected]', '9876543210', '2003-05-15',
'M', CURDATE(), 1, 'Active');-- Enroll student in a course
INSERT INTO enrollment (student_id, course_id, semester, enrollment_date, status)
VALUES (1, 5, 'Fall 2024', CURDATE(), 'Enrolled');-- View student with their GPA and enrolled courses
SELECT
s.student_id,
CONCAT(s.first_name, ' ', s.last_name) AS student_name,
s.gpa,
COUNT(e.enrollment_id) AS courses_enrolled,
AVG(CASE WHEN e.grade = 'A' THEN 4.0
WHEN e.grade = 'B' THEN 3.0
WHEN e.grade = 'C' THEN 2.0
WHEN e.grade = 'D' THEN 1.0
ELSE 0.0 END) AS calculated_gpa
FROM students s
LEFT JOIN enrollment e ON s.student_id = e.student_id
GROUP BY s.student_id, s.first_name, s.last_name, s.gpa;-- Find faculty workload by semester
SELECT
f.faculty_id,
CONCAT(f.first_name, ' ', f.last_name) AS faculty_name,
c.semester,
COUNT(c.course_id) AS courses_teaching,
COUNT(DISTINCT e.student_id) AS total_students
FROM faculty f
JOIN courses c ON f.faculty_id = c.instructor_id
LEFT JOIN enrollment e ON c.course_id = e.course_id
GROUP BY f.faculty_id, f.first_name, f.last_name, c.semester
ORDER BY c.semester, courses_teaching DESC;After completing this project, you will have mastered:
-
Database Design & Modeling
- ER diagram creation and interpretation
- Schema design with normalization (1NF, 2NF, 3NF, BCNF)
- Relationship mapping and cardinality
- Integrity constraints implementation
-
SQL Mastery
- Data Definition Language (DDL) - CREATE, ALTER, DROP, TRUNCATE
- Data Manipulation Language (DML) - INSERT, UPDATE, DELETE
- Data Query Language (DQL) - Complex SELECT statements
- Data Control Language (DCL) - GRANT, REVOKE
- Transaction Control Language (TCL) - COMMIT, ROLLBACK, SAVEPOINT
-
Advanced Database Concepts
- Joins (INNER, LEFT, RIGHT, FULL, CROSS)
- Subqueries (Correlated and Non-correlated)
- Aggregate functions (SUM, AVG, COUNT, MIN, MAX)
- Window functions and analytical queries
- Views for data abstraction
- Stored procedures for business logic
- Triggers for data automation
- Indexes for performance optimization
-
Database Administration
- User management and access control
- Backup and recovery strategies
- Query optimization techniques
- Performance monitoring and tuning
- Data integrity and constraint management
-
Real-World Application Development
- Building scalable database systems
- Handling concurrent access
- Implementing business logic
- Security best practices
- Documentation and maintenance
Contributions are welcome! If you have improvements, bug fixes or additional features:
-
Fork the repository
git clone https://github.com/YashvardhanJani/DBMS-Lab-Project.git
-
Create your feature branch
git checkout -b feature/YourFeatureName
-
Commit your changes
git commit -m 'Add YourFeatureName with description' -
Push to the branch
git push origin feature/YourFeatureName
-
Open a Pull Request
- Describe your changes
- Link any related issues
- Request review from project maintainers
- Follow SQL naming conventions
- Add comments to complex queries
- Update documentation for new features
- Test all changes thoroughly
- Maintain code quality and readability
⭐ Star this repository if you found it helpful! ⭐
💡 "Data is the new oil and databases are the refineries that transform raw information into valuable insights."
This project is licensed under the MIT License - see the LICENSE file for details.