The School Management Database System is designed to efficiently store and manage school-related data, including students, teachers, courses, and grades. It provides a structured database with stored procedures, triggers, And Queries to automate operations such as enrollment, grade assignment,student ranking, Acquiring Useful insights.
- Functionalities
- Technologies Used
- ERD Diagram
- Installation Instructions
- Stored Procedures, Triggers, and Queries
- How To Test Stored Procedures and Triggers
- Student Management: Registering students, tracking attendance, and managing grades.
- Teacher Management: Assigning courses, updating records, and storing evaluations.
- Course & Enrollment Management: Handling course schedules, enrollments, and prerequisites.
- Automated Triggers & Procedures: Enforcing business rules, validating data integrity, and automating updates.
You can choose one of the following database implementations:
- Microsoft SQL Server (MSSQL) with T-SQL – MSSQL as database choice, and T-SQL for writing queries, procedures, and triggers.
- Oracle Database with PL/SQL – Oracle as database choice, you will use PL/SQL for writing queries, procedures, and triggers.
- Vertabelo – Used for designing the ERD diagram.
git clone https://github.com/shehabkh03/SchoolManagementDatabaseSystem.git
cd SchoolManagementDatabaseSystemThis project supports Microsoft SQL Server (MSSQL) or Oracle Database, but you only need to set up one of them. Follow the appropriate setup instructions based on your chosen database system.
- Open your database management tool of choice (e.g., SQL Server Management Studio, Azure Data Studio, or DataGrip) and connect to your MSSQL Server using your database credentials.
- Run the following scripts within the existing database in the correct order:
SQL_Scripts/T-SQL/ddl.sql
SQL_Scripts/T-SQL/dml.sql
SQL_Scripts/T-SQL/Procedures_TriggersWithtestcases.sql- Open your database management tool of choice (e.g., SQL Server Management Studio, Azure Data Studio, or DataGrip) and connect to your MSSQL Server using your database credentials.
- Run the following scripts within the existing database in the correct order:
SQL_Scripts/PL-SQL/ddl.sql
SQL_Scripts/PL-SQL/dml.sql
SQL_Scripts/PL-SQL/Procedures_TriggersWithtestcases.sqlAfter running the scripts, your database should be properly configured. You can now test the stored procedures and triggers as described in the next section.
Stored procedures are used for handling automated operations within the database.
- CalculateAvgGradeAndRank: Computes a student’s average grade and determines their rank.
- Enrollment Procedures: Automates student enrollment and ensures prerequisites are met.
- Grade Management: Updates student grades and maintains historical records.
Triggers enforce data integrity and automate changes when specific events occur.
- Validate Enrollment: Prevents students from enrolling in conflicting courses.
- Auto-Recalculate Grades: Updates average grades when new scores are added.
- Prevent Invalid Entries: Ensures only valid data can be inserted into critical tables.
SQL queries that include SELECT statements with WHERE conditions, queries that join multiple tables, queries that use GROUP BY with aggregate functions (such as COUNT, SUM, AVG), and queries that utilize subqueries to retrieve data based on nested conditions.
Test cases for all stored procedures and triggers are already included each procedure/trigger in the following file:
📄 Procedures_TriggersWithtestcases.sql
To test them, simply select the the query you want to test and run it.
SQL_Scripts/T-SQL/Procedures_TriggersWithtestcases.sql -- For MSSQL
SQL_Scripts/PL-SQL/Procedures_TriggersWithtestcases.sql -- For Oracle