High School Admin App

Featuring database creation, stored procedures, Flask etc.

Table of Contents

• Requirement gathering
• Database Creation
• Application
• Screenshots
• Contact

About The Project 🍪

Requirement Gathering

School needs to move away from storing data on Excel files and adopt an automated way of managing personnel data

Creating the database 📂

I designed a 25-table normalized database holding data ranging from examinations data, staff salary, student class history to student, teacher and staff data.

Deciding how to store personnel data was quite the challenge. I wanted a way to organize the student, guardian, teacher and staff in an hierarchy under a person table with the added complexity of the teacher table being a child table to the staff table.

I was ultimately able to solve this problem with stored procedures which enabled me to write complicated logic in SQL that perform operations like check for existence of records before insertion etc. For example, this stored procedure served to promote specific students at the end of the school year:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `promote_students`()
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE current_grade INT;
DECLARE student_id INT;
DECLARE session_id INT;
DECLARE error_message VARCHAR(255);
DECLARE student_cursor CURSOR FOR
SELECT DISTINCT student_id FROM studentclasshistory WHERE graduated <> 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
error_message = MESSAGE_TEXT;
ROLLBACK;
-- SELECT CONCAT('Error: ', error_message) AS 'Error Message';
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = error_message;
END;
START TRANSACTION;



-- Initialize a cursor that loops through every student in the table
OPEN student_cursor;
student_loop: LOOP
FETCH student_cursor INTO student_id;
IF done THEN
LEAVE student_loop;
END IF;

-- These statements run for each iteration in the loop i.e for each student
-- Save the current (ensured by the order by clause acting on last_update) grade/class of the student into a variable
SET current_grade = (SELECT class_id FROM studentclasshistory WHERE student_id
= student_id ORDER BY last_update DESC LIMIT 1);

SET session_id = (SELECT id FROM school_session ORDER BY id DESC LIMIT 1);

-- For all non-terminal class students, insert a new record into the table
IF current_grade IS NULL THEN
INSERT INTO studentclasshistory (
student_id, class_id, graduated, teacher_id, classroom_id, session_id, start_date, end_date, last_update
)
VALUES (student_id, current_grade, NULL, NULL, NULL, session_id, NOW(), NULL, NOW());
END IF;

IF current_grade < 12 THEN
INSERT INTO studentclasshistory (
student_id, class_id, graduated, teacher_id, classroom_id, session_id, start_date, end_date, last_update
)
VALUES (student_id, current_grade + 1, NULL, NULL, NULL, session_id + 1, NOW(), NULL, NOW());

-- set their previous record's end_date to now
UPDATE studentclasshistory 
SET 
    end_date = NOW()
WHERE
    student_id = student_id
        AND class_id = current_grade;
ELSE
-- For terminal class students, set their graduated column to TRUE
UPDATE studentclasshistory 
SET graduated = 1
 WHERE student_id = student_id
AND class_id = 12;
END IF;
END LOOP;

 CLOSE student_cursor;
 
 -- We would still have to later set the teacher_id and classroom_id of the newly promoted classes
COMMIT;
END$$
DELIMITER ;

All stored procedures used for the application can be studied to view my solution.

Flask App 📱

Using the Blueprint feature of Flask, I organized my app into 4 main modules: auth, forms, models and views. View the code here

Screenshots 📸

Contact

Edun Joshua Olubori - connect on linkedin - joshuaolubori@gmail.com

(back to top)