Scribd
Upload
13 views

Attendance

Database queries

Uploaded by

Melvin Carpio
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
13 views

Attendance

Database queries

Uploaded by

Melvin Carpio
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 6

Melvin M.

Carpio

MIT

Title: Students Attendance Monitoring System

Objectives

For precise and efficient management and tracking of student attendance, an efficient student
attendance

system is essential. Because it offers information about student engagement, academic performance,
and

general well-being, attendance monitoring is essential to education. Conventional attendance


techniques,

which frequently depend on manual recordkeeping, can be laborious and prone to mistakes. These

difficulties underscore the need for a simplified, automated approach that lowers administrative

workloads, enhances data precision, and facilitates prompt intervention for vulnerable kids.

Key Features

Generate Attendance Reports: Provide detailed attendance summaries and reports, such as daily,

weekly, or monthly records, showing total absences, late arrivals, and excused absences for each
student.

Monitor Student Attendance Patterns: Identify attendance trends to monitor student participation,

flagging students with frequent absences or late arrivals for early intervention.

Reduce Paper Usage: Decrease reliance on physical attendance sheets, supporting a more

environmentally friendly and sustainable school operation.

Provide Easy Data Access: Enable teachers and school administrators to quickly retrieve attendance data

for any student, class, or time period through a user-friendly interface.

Automate Attendance Tracking: Streamline the process of recording student attendance by automating

daily check-ins and generating accurate, time-stamped attendance records.


First Normal Form (1NF)
AttendanceID StudentID StudentName CourseID CourseName AttendanceDate Status
1 101 Alice Johnson C01 Math 2023-12-01 Present
2 101 Alice Johnson C01 Math 2023-12-02 Absent
3 102 Bob Smith C02 Science 2023-12-01 Present

Second Normal Form (2NF)

Students table

StudentID StudentName
102 Bob Smith
101 Alice Johnson

Course Table

CourseID CourseName
C01 Math
C02 Science

Attendance Table

AttendanceID StudentID CourseID AttendanceDate Status


1 101 C01 2023-12-01 Present
2 101 C01 2023-12-02 Absent
StudentID StudentName
102 Bob Smith
101 Alice Johnson

Third Normal Form (3NF)

Students Table

CREATE TABLE students (

student_id INT AUTO_INCREMENT PRIMARY KEY,

student_Firstname VARCHAR(100) NOT NULL,

student_Lastname VARCHAR(100) NOT NULL,

email VARCHAR(100),

phone VARCHAR(15),

enrollment_number VARCHAR(20) UNIQUE

);

StudentID StudentName LastName email phone Enrollment_no


101 Alice Johnson [email protected] 09445315456 452

Course Table

CREATE TABLE courses (

course_id INT AUTO_INCREMENT PRIMARY KEY,

course_name VARCHAR(100) NOT NULL,

teacher_id INT NOT NULL,

FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id) ON DELETE CASCADE

);

CourseID CourseName TeacherID


C01 Math 1
C02 Science 2

Attendance Table
CREATE TABLE attendance (

attendance_id INT AUTO_INCREMENT PRIMARY KEY,

student_id INT NOT NULL,

course_id INT NOT NULL,

attendance_date DATE NOT NULL,

status ENUM('Present', 'Absent', 'Excused') DEFAULT 'Absent',

FOREIGN KEY (student_id) REFERENCES students(student_id),

FOREIGN KEY (course_id) REFERENCES courses(course_id)

);

AttendanceID StudentID CourseID AttendanceDate Status


1 101 C01 2023-12-01 Present
2 101 C01 2023-12-02 Absent
3 102 C02 2023-12-01 Present

Teachers Table

CREATE TABLE teachers (

teacher_id INT AUTO_INCREMENT PRIMARY KEY,

teacher_name VARCHAR(100) NOT NULL,

email VARCHAR(100),

phone VARCHAR(15)

);

TeacherID TeacherName Email Phone


1 John Doe [email protected] 1234567890
2 Jane Smith [email protected] 0987654321

Students Report Table

CREATE TABLE student_reports (

report_id INT AUTO_INCREMENT PRIMARY KEY,

student_id INT NOT NULL,

course_id INT NOT NULL,

report_date DATE NOT NULL,


grade VARCHAR(5), -- e.g., A, B, C

remarks TEXT,

FOREIGN KEY (student_id) REFERENCES students(student_id),

FOREIGN KEY (course_id) REFERENCES courses(course_id)

);

ReportID StudentID CourseID ReportDate Grade Remarks


1 101 1 2023-12-15 A Execellent
2 102 2 2023-12-15 B Good

Queries

1. Fetch attendance for a course along with teacher information

SELECT

s.student_name,

c.course_name,

t.teacher_name,

a.attendance_date,

a.status

FROM attendance a

JOIN students s ON a.student_id = s.student_id

JOIN courses c ON a.course_id = c.course_id

JOIN teachers t ON c.teacher_id = t.teacher_id

WHERE c.course_id = 1;
2. Generate report for a student

SELECT

s.student_name,

c.course_name,

sr.grade,

sr.remarks

FROM student_reports sr

JOIN students s ON sr.student_id = s.student_id

JOIN courses c ON sr.course_id = c.course_id

WHERE s.student_id = 101; -- Replace with desired student ID

You might also like