Module 2 – Data Model: SQL Schema (Postgres)

Goal: create normalized schema for courses, semesters, subjects, staff, students, timetable, attendance.

Overview

We design a relational schema to reflect the MCP entities. This schema supports:

• 2 courses (8-sem & 4-sem)
• Semesters with odd/even type
• Subjects (6 per semester)
• Staff, student enrollments, timetable, attendance logs

SQL Schema (mcp_schema.sql)

-- MCP Attendance SQL Schema

BEGIN;

CREATE TABLE courses (
  id SERIAL PRIMARY KEY,
  code TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  total_semesters INT NOT NULL
);

CREATE TABLE semesters (
  id SERIAL PRIMARY KEY,
  course_id INT NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  number INT NOT NULL,
  type TEXT NOT NULL CHECK (type IN ('odd','even'))
);

CREATE TABLE staff (
  id SERIAL PRIMARY KEY,
  staff_code TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL
);

CREATE TABLE subjects (
  id SERIAL PRIMARY KEY,
  semester_id INT NOT NULL REFERENCES semesters(id) ON DELETE CASCADE,
  code TEXT NOT NULL,
  name TEXT NOT NULL,
  UNIQUE(semester_id, code)
);

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  student_code TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  course_id INT NOT NULL REFERENCES courses(id),
  semester INT NOT NULL
);

CREATE TABLE staff_subjects (
  staff_id INT NOT NULL REFERENCES staff(id) ON DELETE CASCADE,
  subject_id INT NOT NULL REFERENCES subjects(id) ON DELETE CASCADE,
  PRIMARY KEY(staff_id, subject_id)
);

CREATE TABLE timetable_slots (
  id SERIAL PRIMARY KEY,
  semester_id INT NOT NULL REFERENCES semesters(id) ON DELETE CASCADE,
  day_of_week INT NOT NULL CHECK(day_of_week BETWEEN 1 AND 7),
  slot_order INT NOT NULL,
  subject_id INT NOT NULL REFERENCES subjects(id)
);

CREATE TABLE attendance (
  id SERIAL PRIMARY KEY,
  subject_id INT NOT NULL REFERENCES subjects(id),
  staff_id INT NOT NULL REFERENCES staff(id),
  date DATE NOT NULL,
  hour INT NOT NULL,
  student_id INT NOT NULL REFERENCES students(id),
  status TEXT NOT NULL CHECK (status IN ('present','absent','late')) DEFAULT 'present'
);

CREATE INDEX idx_attendance_date ON attendance(date);

COMMIT;

Notes & Constraints

• students.semester stores the semester number (1..8 or 1..4 depending on course).
• Use staff_subjects to authorize staff to mark attendance for specific subjects.
• timetable_slots maps a semester's weekly slots (40 slots = 5 days × 8 slots).
• Attendance is stored per student per subject per hour to allow hourly reporting.

How to run

Create DB & run the SQL file using psql:

createdb mcp_db
psql -U postgres -d mcp_db -f mcp_schema.sql

Sample queries

-- Count students per semester
SELECT course_id, semester, COUNT(*) AS students_count
FROM students
GROUP BY course_id, semester
ORDER BY course_id, semester;

-- Attendance percentage for a student in a date range
SELECT s.student_code, s.name,
  (COUNT(*) FILTER (WHERE a.status='present')::float / COUNT(*) * 100) AS pct
FROM attendance a
JOIN students s ON s.id = a.student_id
WHERE s.student_code = 'S101' AND a.date BETWEEN '2025-08-01' AND '2025-08-31'
GROUP BY s.student_code, s.name;

Next: Module 3 — MCP Schemas (JSON Schema) & Validation.