College Attendance System Schema

Problem Statement

Design a database schema for a college attendance system. Requirements: - Students belong to a batch (e.g. 'CSE 2024') - Multiple subjects per batch - Attendance marked per student per subject per date - Attendance status: present or absent Write CREATE TABLE statements, then write these queries: 1. Find attendance percentage for each student in a given subject 2. Find all students below 75% attendance in any subject

Sample Data

Entities to model:
- batches: id, name, year
- students: id, name, email, batch_id
- subjects: id, name, batch_id, teacher_name
- attendance: id, student_id, subject_id, date, status ('present'/'absent')

Expected Output

4 CREATE TABLE statements
Query 1: student, subject, (present_count/total_count)*100 as percentage
Query 2: students where percentage < 75 in any subject