202 lines
7.2 KiB
SQL
202 lines
7.2 KiB
SQL
-- Create Database
|
|
CREATE DATABASE StudentSchedulerDB;
|
|
USE StudentSchedulerDB;
|
|
|
|
-- User/Account Management
|
|
CREATE TABLE Users (
|
|
user_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
username VARCHAR(50) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
email VARCHAR(100) UNIQUE NOT NULL,
|
|
full_name VARCHAR(100) NOT NULL,
|
|
role ENUM('Teacher', 'Admin', 'View-Only') NOT NULL,
|
|
is_2fa_enabled BOOLEAN DEFAULT FALSE,
|
|
last_login DATETIME,
|
|
account_created DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
account_active BOOLEAN DEFAULT TRUE
|
|
);
|
|
|
|
-- Student Management
|
|
CREATE TABLE Students (
|
|
student_id VARCHAR(20) PRIMARY KEY,
|
|
user_id INT UNIQUE,
|
|
full_name VARCHAR(100) NOT NULL,
|
|
date_of_birth DATE NOT NULL,
|
|
grade_level VARCHAR(10) NOT NULL,
|
|
emergency_contact_name VARCHAR(100) NOT NULL,
|
|
emergency_contact_phone VARCHAR(20) NOT NULL,
|
|
photo_path VARCHAR(255),
|
|
medical_notes TEXT,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
last_updated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE SET NULL
|
|
);
|
|
|
|
-- Medical Information
|
|
CREATE TABLE MedicalInformation (
|
|
medical_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
student_id VARCHAR(20) NOT NULL,
|
|
allergy_type VARCHAR(100),
|
|
allergy_details TEXT,
|
|
medication_name VARCHAR(100),
|
|
medication_details TEXT,
|
|
additional_notes TEXT,
|
|
created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (student_id) REFERENCES Students(student_id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Room/Classroom Management
|
|
CREATE TABLE Rooms (
|
|
room_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
room_number VARCHAR(20) UNIQUE NOT NULL,
|
|
building_name VARCHAR(50),
|
|
capacity INT NOT NULL,
|
|
has_computers BOOLEAN DEFAULT FALSE,
|
|
has_lab_equipment BOOLEAN DEFAULT FALSE,
|
|
special_equipment_notes TEXT,
|
|
is_active BOOLEAN DEFAULT TRUE
|
|
);
|
|
|
|
-- Subject/Course Management
|
|
CREATE TABLE Subjects (
|
|
subject_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
subject_code VARCHAR(20) UNIQUE NOT NULL,
|
|
subject_name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
credit_hours INT,
|
|
is_active BOOLEAN DEFAULT TRUE
|
|
);
|
|
|
|
-- Class Schedule Management
|
|
CREATE TABLE ClassSchedules (
|
|
class_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
subject_id INT NOT NULL,
|
|
teacher_id INT NOT NULL,
|
|
room_id INT NOT NULL,
|
|
schedule_pattern ENUM('Weekly', 'Bi-Weekly', 'One-Time') DEFAULT 'Weekly',
|
|
max_students INT DEFAULT 30,
|
|
start_date DATE NOT NULL,
|
|
end_date DATE,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (subject_id) REFERENCES Subjects(subject_id),
|
|
FOREIGN KEY (teacher_id) REFERENCES Users(user_id),
|
|
FOREIGN KEY (room_id) REFERENCES Rooms(room_id)
|
|
);
|
|
|
|
-- Schedule Time Slots
|
|
CREATE TABLE ScheduleTimeSlots (
|
|
time_slot_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
class_id INT NOT NULL,
|
|
day_of_week ENUM('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'),
|
|
start_time TIME NOT NULL,
|
|
end_time TIME NOT NULL,
|
|
is_recurring BOOLEAN DEFAULT TRUE,
|
|
specific_date DATE,
|
|
FOREIGN KEY (class_id) REFERENCES ClassSchedules(class_id) ON DELETE CASCADE,
|
|
UNIQUE KEY unique_schedule (room_id, day_of_week, start_time, specific_date),
|
|
UNIQUE KEY unique_teacher_schedule (teacher_id, day_of_week, start_time, specific_date)
|
|
);
|
|
|
|
-- Student Enrollment
|
|
CREATE TABLE StudentEnrollments (
|
|
enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
student_id VARCHAR(20) NOT NULL,
|
|
class_id INT NOT NULL,
|
|
enrollment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
enrollment_status ENUM('Active', 'Dropped', 'Completed') DEFAULT 'Active',
|
|
drop_date DATE,
|
|
FOREIGN KEY (student_id) REFERENCES Students(student_id),
|
|
FOREIGN KEY (class_id) REFERENCES ClassSchedules(class_id),
|
|
UNIQUE KEY unique_enrollment (student_id, class_id)
|
|
);
|
|
|
|
-- Attendance Tracking
|
|
CREATE TABLE Attendance (
|
|
attendance_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
enrollment_id INT NOT NULL,
|
|
class_date DATE NOT NULL,
|
|
time_slot_id INT NOT NULL,
|
|
status ENUM('Present', 'Absent', 'Late', 'Excused') NOT NULL,
|
|
notes TEXT,
|
|
recorded_by INT NOT NULL,
|
|
recorded_time DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (enrollment_id) REFERENCES StudentEnrollments(enrollment_id),
|
|
FOREIGN KEY (time_slot_id) REFERENCES ScheduleTimeSlots(time_slot_id),
|
|
FOREIGN KEY (recorded_by) REFERENCES Users(user_id),
|
|
UNIQUE KEY unique_attendance (enrollment_id, class_date, time_slot_id)
|
|
);
|
|
|
|
-- Performance/Grades Tracking
|
|
CREATE TABLE StudentPerformance (
|
|
performance_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
enrollment_id INT NOT NULL,
|
|
assessment_date DATE NOT NULL,
|
|
assessment_type VARCHAR(50),
|
|
score DECIMAL(5,2),
|
|
max_score DECIMAL(5,2) DEFAULT 100,
|
|
grade_letter CHAR(2),
|
|
teacher_notes TEXT,
|
|
recorded_by INT NOT NULL,
|
|
recorded_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (enrollment_id) REFERENCES StudentEnrollments(enrollment_id),
|
|
FOREIGN KEY (recorded_by) REFERENCES Users(user_id)
|
|
);
|
|
|
|
-- Audit Trail Log
|
|
CREATE TABLE AuditTrail (
|
|
audit_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
user_id INT,
|
|
action_type ENUM('CREATE', 'READ', 'UPDATE', 'DELETE', 'LOGIN', 'LOGOUT', 'EXPORT') NOT NULL,
|
|
table_name VARCHAR(50) NOT NULL,
|
|
record_id VARCHAR(100),
|
|
old_values JSON,
|
|
new_values JSON,
|
|
ip_address VARCHAR(45),
|
|
user_agent TEXT,
|
|
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE SET NULL
|
|
);
|
|
|
|
-- Bulk Import Log
|
|
CREATE TABLE BulkImportLog (
|
|
import_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
user_id INT NOT NULL,
|
|
filename VARCHAR(255) NOT NULL,
|
|
import_type ENUM('Students', 'Schedules', 'Enrollments') NOT NULL,
|
|
total_records INT,
|
|
successful_records INT,
|
|
failed_records INT,
|
|
error_log TEXT,
|
|
import_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES Users(user_id)
|
|
);
|
|
|
|
-- Indexes for Performance
|
|
CREATE INDEX idx_students_grade ON Students(grade_level);
|
|
CREATE INDEX idx_students_active ON Students(is_active);
|
|
CREATE INDEX idx_schedules_active ON ClassSchedules(is_active);
|
|
CREATE INDEX idx_schedules_dates ON ClassSchedules(start_date, end_date);
|
|
CREATE INDEX idx_enrollments_status ON StudentEnrollments(enrollment_status);
|
|
CREATE INDEX idx_attendance_date ON Attendance(class_date);
|
|
CREATE INDEX idx_audit_user_time ON AuditTrail(user_id, timestamp);
|
|
CREATE INDEX idx_audit_action ON AuditTrail(action_type, table_name);
|
|
|
|
-- Multi-language Support Table
|
|
CREATE TABLE LanguageTranslations (
|
|
translation_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
language_code CHAR(2) NOT NULL,
|
|
page_section VARCHAR(50) NOT NULL,
|
|
element_key VARCHAR(100) NOT NULL,
|
|
translation_text TEXT NOT NULL,
|
|
last_updated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY unique_translation (language_code, page_section, element_key)
|
|
);
|
|
|
|
-- Insert default languages
|
|
INSERT INTO LanguageTranslations (language_code, page_section, element_key, translation_text) VALUES
|
|
('en', 'general', 'welcome', 'Welcome'),
|
|
('ru', 'general', 'welcome', 'Добро пожаловать'),
|
|
('en', 'general', 'logout', 'Logout'),
|
|
('ru', 'general', 'logout', 'Выход'); |