Name : Muhammad Younas and Abbas
Roll no : 34
Project : Student Attendance Management System
Subject : DataBase Management System
Section : A
1. Create Database
CREATE DATABASE StudentAttendanceDB;
USE StudentAttendanceDB;
2. Create Tables
Students Table
CREATE TABLE Students (
student_id INT PRIMARY KEY AUTO_INCREMENT,
student_name VARCHAR(100) NOT NULL,
gender VARCHAR(10),
class_id INT,
phone VARCHAR(15)
);
Classes Table
CREATE TABLE Classes (
class_id INT PRIMARY KEY AUTO_INCREMENT,
class_name VARCHAR(50) NOT NULL
);
Subjects Table
CREATE TABLE Subjects (
subject_id INT PRIMARY KEY AUTO_INCREMENT,
subject_name VARCHAR(100) NOT NULL
);
Attendance Table
CREATE TABLE Attendance (
attendance_id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT,
subject_id INT,
attendance_date DATE,
status VARCHAR(10),
FOREIGN KEY (student_id) REFERENCES Students(student_id),
FOREIGN KEY (subject_id) REFERENCES Subjects(subject_id)
);
3. Insert Sample Data
Insert Classes
INSERT INTO Classes (class_name)
VALUES
('BSCS-1'),
('BSCS-2');
Insert Students
INSERT INTO Students (student_name, gender, class_id, phone)
VALUES
('Ali Khan', 'Male', 1, '03001234567'),
('Sara Ahmed', 'Female', 1, '03111234567'),
('Usman Tariq', 'Male', 2, '03221234567');
Insert Subjects
INSERT INTO Subjects (subject_name)
VALUES
('Database Systems'),
('Programming Fundamentals');
Insert Attendance
INSERT INTO Attendance (student_id, subject_id, attendance_date, status)
VALUES
(1, 1, '2026-05-01', 'Present'),
(2, 1, '2026-05-01', 'Absent'),
(3, 2, '2026-05-01', 'Present'),
(1, 2, '2026-05-02', 'Present'),
(2, 2, '2026-05-02', 'Absent');
4. Feature: Mark Attendance
INSERT INTO Attendance (student_id, subject_id, attendance_date, status)
VALUES
(1, 1, CURDATE(), 'Present');
5. Feature: Attendance Report
Total Present Days of Each Student
SELECT
s.student_name,
COUNT([Link]) AS total_present
FROM Students s
JOIN Attendance a
ON s.student_id = a.student_id
WHERE [Link] = 'Present'
GROUP BY s.student_name;
Total Absences of Each Student
SELECT
s.student_name,
COUNT([Link]) AS total_absent
FROM Students s
JOIN Attendance a
ON s.student_id = a.student_id
WHERE [Link] = 'Absent'
GROUP BY s.student_name;
6. Feature: Absentee Alert
Students Absent More Than 1 Time
SELECT
s.student_name,
COUNT([Link]) AS absences
FROM Students s
JOIN Attendance a
ON s.student_id = a.student_id
WHERE [Link] = 'Absent'
GROUP BY s.student_name
HAVING COUNT([Link]) > 1;
7. SQL View
Create Attendance Report View
CREATE VIEW Attendance_Report AS
SELECT
s.student_name,
c.class_name,
sub.subject_name,
a.attendance_date,
[Link]
FROM Attendance a
JOIN Students s
ON a.student_id = s.student_id
JOIN Classes c
ON s.class_id = c.class_id
JOIN Subjects sub
ON a.subject_id = sub.subject_id;
Use View
SELECT * FROM Attendance_Report;
8. Indexing
Create Index for Faster Search
CREATE INDEX idx_student
ON Attendance(student_id);
CREATE INDEX idx_date
ON Attendance(attendance_date);
9. Output Example
Student Name Present Days
Ali Khan 2
Usman Tariq 1