MAINHACK

Viewing / Editing File: 20250807134525_purple_bar.sql

-- Database schema for Student Attendance Management System
-- Created for PHP-based attendance system

CREATE DATABASE IF NOT EXISTS student_attendance CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE student_attendance;

-- Admin table
CREATE TABLE admins (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Educational stages table (ابتدائي، إعدادي، ثانوي)
CREATE TABLE stages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Grades/Classes table (الصف الأول، الصف الثاني...)
CREATE TABLE grades (
    id INT PRIMARY KEY AUTO_INCREMENT,
    stage_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (stage_id) REFERENCES stages(id) ON DELETE CASCADE
);

-- Subjects table (المواد الدراسية)
CREATE TABLE subjects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Grade-Subject relationship table
CREATE TABLE grade_subjects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    grade_id INT NOT NULL,
    subject_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (grade_id) REFERENCES grades(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
    UNIQUE KEY unique_grade_subject (grade_id, subject_id)
);

-- Teachers table
CREATE TABLE teachers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Teacher-Subject-Grade relationship table
CREATE TABLE teacher_assignments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    teacher_id INT NOT NULL,
    grade_id INT NOT NULL,
    subject_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
    FOREIGN KEY (grade_id) REFERENCES grades(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
);

-- Weekly groups table
CREATE TABLE groups (
    id INT PRIMARY KEY AUTO_INCREMENT,
    teacher_id INT NOT NULL,
    grade_id INT NOT NULL,
    subject_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    sessions_per_week INT NOT NULL CHECK (sessions_per_week IN (1, 2)),
    day1 VARCHAR(20) NOT NULL,
    time1 TIME NOT NULL,
    day2 VARCHAR(20) NULL,
    time2 TIME NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
    FOREIGN KEY (grade_id) REFERENCES grades(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
);

-- Students table
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    group_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    student_code VARCHAR(10) UNIQUE NOT NULL,
    parent_phone VARCHAR(20) NOT NULL,
    student_phone VARCHAR(20) NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE CASCADE
);

-- Attendance table
CREATE TABLE attendance (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    group_id INT NOT NULL,
    session_date DATE NOT NULL,
    session_time TIME NOT NULL,
    is_present BOOLEAN NOT NULL,
    homework_level ENUM('ممتاز', 'جيد جداً', 'جيد', 'مقبول', 'ضعيف') NULL,
    has_test BOOLEAN DEFAULT FALSE,
    test_total_score DECIMAL(5,2) NULL,
    student_test_score DECIMAL(5,2) NULL,
    teacher_notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE CASCADE,
    UNIQUE KEY unique_attendance (student_id, session_date, session_time)
);

-- Makeup sessions table
CREATE TABLE makeup_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    original_group_id INT NOT NULL,
    makeup_group_id INT NOT NULL,
    session_date DATE NOT NULL,
    session_time TIME NOT NULL,
    homework_level ENUM('ممتاز', 'جيد جداً', 'جيد', 'مقبول', 'ضعيف') NULL,
    has_test BOOLEAN DEFAULT FALSE,
    test_total_score DECIMAL(5,2) NULL,
    student_test_score DECIMAL(5,2) NULL,
    teacher_notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (original_group_id) REFERENCES groups(id) ON DELETE CASCADE,
    FOREIGN KEY (makeup_group_id) REFERENCES groups(id) ON DELETE CASCADE
);

-- Insert default admin (password: password)
INSERT INTO admins (username, password) VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

-- Insert sample educational stages
INSERT INTO stages (name) VALUES ('المرحلة الابتدائية'), ('المرحلة الإعدادية'), ('المرحلة الثانوية');

-- Insert sample grades for each stage
INSERT INTO grades (stage_id, name) VALUES 
(1, 'الصف الأول الابتدائي'), (1, 'الصف الثاني الابتدائي'), (1, 'الصف الثالث الابتدائي'),
(1, 'الصف الرابع الابتدائي'), (1, 'الصف الخامس الابتدائي'), (1, 'الصف السادس الابتدائي'),
(2, 'الصف الأول الإعدادي'), (2, 'الصف الثاني الإعدادي'), (2, 'الصف الثالث الإعدادي'),
(3, 'الصف الأول الثانوي'), (3, 'الصف الثاني الثانوي'), (3, 'الصف الثالث الثانوي');

-- Insert sample subjects
INSERT INTO subjects (name) VALUES 
('الرياضيات'), ('العلوم'), ('اللغة العربية'), ('اللغة الإنجليزية'), 
('التاريخ'), ('الجغرافيا'), ('الفيزياء'), ('الكيمياء'), ('الأحياء');

-- Insert sample grade-subject relationships
INSERT INTO grade_subjects (grade_id, subject_id) VALUES
-- Primary grades (1-6) with basic subjects
(1, 1), (1, 2), (1, 3), (1, 4),
(2, 1), (2, 2), (2, 3), (2, 4),
(3, 1), (3, 2), (3, 3), (3, 4),
(4, 1), (4, 2), (4, 3), (4, 4), (5, 5), (4, 6),
(5, 1), (5, 2), (5, 3), (5, 4), (5, 5), (5, 6),
(6, 1), (6, 2), (6, 3), (6, 4), (6, 5), (6, 6),
-- Middle school grades (7-9) with more subjects
(7, 1), (7, 2), (7, 3), (7, 4), (7, 5), (7, 6),
(8, 1), (8, 2), (8, 3), (8, 4), (8, 5), (8, 6),
(9, 1), (9, 2), (9, 3), (9, 4), (9, 5), (9, 6),
-- High school grades (10-12) with specialized subjects
(10, 1), (10, 3), (10, 4), (10, 5), (10, 6), (10, 7), (10, 8), (10, 9),
(11, 1), (11, 3), (11, 4), (11, 5), (11, 6), (11, 7), (11, 8), (11, 9),
(12, 1), (12, 3), (12, 4), (12, 5), (12, 6), (12, 7), (12, 8), (12, 9);

Cancel / Back

[ MAINHACK ]

Viewing / Editing File: 20250807134525_purple_bar.sql

Server Info

System Features

User Info