The School Database Project is designed to manage and organize essential school data, including information about students, teachers, departments, grades, and subjects. The database schema ensures data integrity, consistency, and efficient retrieval, making it easier to handle various school operations.
The database consists of the following tables:
Stores department details. Primary Key: dept_id.
Stores student information, including their department, name, phone number, date of birth, and email. Primary Key: student_id. Foreign Key: dep_id references dept_id in departments. Unique Constraint: email.
Stores grade categories. Primary Key: grade_id.
Links students to their grades, with a grade value for each subject. Composite Primary Key: student_id, grade_id. Foreign Keys: student_id references student_id in students, grade_id references grade_id in grades. Check Constraint: Ensures grade_value is between 50 and 100.
Stores teacher details, including their department, name, email, and phone number. Primary Key: teacher_id. Foreign Key: dept_id references dept_id in departments. Unique Constraints: email, phone_number.
Stores subject information and links each subject to a teacher and department. Primary Key: sub_id. Foreign Keys: teacher_id references teacher_id in teachers, dep_id references dept_id in departments. Unique Constraint: sub_name.
Maps subjects to departments, facilitating a many-to-many relationship. Composite Primary Key: dept_id, sub_id. Foreign Keys: dept_id references dept_id in departments, sub_id references sub_id in subjects.
Add, update, or remove departments.
Manage student records, including enrollment and grade tracking.
Maintain teacher information and assign subjects.
Assign subjects to departments and teachers.
Record and monitor student grades with defined constraints.
