wiseup_system_db_ver2

Tables (19)

Attendance

  • attendance_id — BIGINT
  • student_id — BIGINT — NOT NULL
  • class_id — BIGINT — NOT NULL
  • attendance_date — DATE — NOT NULL
  • status — ENUM('present', 'absent', 'late', 'excused') — NOT NULL
  • reason — TEXT
  • checkin_time — TIME
  • checkout_time — TIME
  • created_at — TIMESTAMP

Class

  • class_id — BIGINT
  • class_name — VARCHAR(100) — NOT NULL
  • grade_level — INT
  • max_students — INT
  • academic_year — YEAR
  • created_at — TIMESTAMP

Exam

  • exam_id — BIGINT
  • subject_id — BIGINT — NOT NULL
  • class_id — BIGINT — NOT NULL
  • exam_type_id — BIGINT — NOT NULL
  • exam_name — VARCHAR(100) — NOT NULL
  • exam_date — DATE
  • max_score — INT
  • created_at — TIMESTAMP

Exam_Scores

  • score_id — BIGINT
  • exam_id — BIGINT — NOT NULL
  • student_id — BIGINT — NOT NULL
  • score — DECIMAL(5,2)
  • grade — VARCHAR(2)
  • created_at — TIMESTAMP

Exam_type

  • exam_type_id — BIGINT
  • exam_type_name — VARCHAR(50) — NOT NULL
  • description — TEXT

Notification

  • notification_id — BIGINT
  • sender_id — BIGINT — NOT NULL
  • target_user_id — BIGINT — NOT NULL
  • title — VARCHAR(200)
  • message — TEXT — NOT NULL
  • notification_type — ENUM('general', 'urgent', 'exam', 'attendance')
  • status — ENUM('sent', 'read', 'deleted')
  • sent_at — TIMESTAMP
  • read_at — TIMESTAMP

Parent_Student

  • parent_id — BIGINT
  • student_id — BIGINT

Parents

  • parent_id — BIGINT
  • user_id — BIGINT — NOT NULL
  • parent_name — VARCHAR(100) — NOT NULL
  • relationship — ENUM('father', 'mother', 'guardian') — NOT NULL

Roles

  • role_id — VARCHAR(20)
  • role_name — VARCHAR(50) — NOT NULL

Schedules

  • schedule_id — BIGINT
  • class_id — BIGINT — NOT NULL
  • subject_id — BIGINT — NOT NULL
  • teacher_id — BIGINT — NOT NULL
  • day_of_week — ENUM('monday', 'tuesday', 'wednesday', 'thursday', 'friday') — NOT NULL
  • period — INT — NOT NULL
  • start_time — TIME
  • end_time — TIME
  • classroom — VARCHAR(50)
  • academic_year — YEAR
  • semester — ENUM('1', '2')
  • created_at — TIMESTAMP

Seat_Assignments

  • assignment_id — BIGINT
  • seat_id — BIGINT — NOT NULL
  • student_id — BIGINT — NOT NULL
  • class_id — BIGINT — NOT NULL
  • assigned_date — DATE — NOT NULL
  • start_time — TIME
  • end_time — TIME
  • is_active — BOOLEAN

Seats

  • seat_id — BIGINT
  • classroom — VARCHAR(50) — NOT NULL
  • row_number — INT — NOT NULL
  • seat_number — INT — NOT NULL
  • is_active — BOOLEAN

Student

  • student_id — BIGINT
  • user_id — BIGINT — NOT NULL
  • student_number — VARCHAR(20)
  • class_id — BIGINT
  • student_name — VARCHAR(100) — NOT NULL
  • student_age — INT
  • student_photo — VARCHAR(255)
  • student_schoolname — VARCHAR(100)
  • student_target_univ — VARCHAR(100)
  • enrollment_date — DATE

Subjects

  • subject_id — BIGINT
  • subject_name — VARCHAR(100) — NOT NULL
  • subject_code — VARCHAR(20)
  • description — TEXT
  • credits — INT

Teacher

  • teacher_id — BIGINT
  • user_id — BIGINT — NOT NULL
  • teacher_number — VARCHAR(20)
  • teacher_name — VARCHAR(100) — NOT NULL
  • hire_date — DATE
  • specialization — VARCHAR(100)

Teacher_Classes

  • teacher_id — BIGINT
  • class_id — BIGINT
  • is_homeroom — BOOLEAN
  • academic_year — YEAR

Teacher_Subjects

  • teacher_id — BIGINT
  • subject_id — BIGINT
  • is_primary — BOOLEAN

User

  • user_id — BIGINT
  • user_name — VARCHAR(100) — NOT NULL
  • user_phone — VARCHAR(20)
  • user_email — VARCHAR(100)
  • user_password_hash — VARCHAR(255) — NOT NULL
  • user_status — ENUM('active', 'inactive', 'suspended')
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

User_roles

  • user_id — BIGINT
  • role_id — VARCHAR(20)