Database Design Project: Secondary School Database with MySQL

Image from Toptal.com

The idea for this project came when I saw a friend; a UI/UX designer who designed a school manager app. While I scrolled through wireframes and clicked through prototypes, the thought of building something similar with SQL slowly grew in my head.

A school manager app is a great idea, no doubt. However, for such a thing to work, a database is required. Several kinds of databases exist, and resources abound on the internet to understand what a database is. If you are unfamiliar with the concept and wish to take a quick overview, you can read this article on freecodecamp. Otherwise, we can go straight to business.

Project Overview

The aim of this project is to create a scalable database for a newly established secondary school in Nigeria; Stardust High School. It is to allow the following;

• Creation of student and staff ids and emails
• Students to view their semester courses or results
• Parents to view their children’s results, subjects, and subject teachers
• Form teachers to view their students’ performances
• Auto-update of students’ class every new session.

Skills required to perform these tasks include; database planning and ERD design, database creation, views, triggers, stored procedures, and user management.

1: Database Planning

Just as the first stage in constructing a building is drawing up the plan, the first stage in creating the Stardust High School database is to create the database plan. This plan is an Entity Relationship Diagram (ERD); a visual representation of the tables, columns, and other objects to be added to the database.

Entity Relationship Diagram for Stardust High School

This ERD was created on MySQL Workbench. It contains 13 tables;

• staff positions — lookup table; contains all the staff along with their current positions and the number of staff in each category.
• marital status — lookup table; contains possible marital status for staff and guardians
• guardian type — lookup table; shows the different types of guardians a student might have
• grades — the different grades possible; lettered A to F
• subjects — a list of all subjects offered in the school; subjects taken by both junior and senior students are indicated as such.
• academic year — lookup table for every academic year since Stardust High School’s founding.
• genders — lookup table for staff and students' tables
• term — lookup table for every term (every academic year) since Stardust’s founding.
• staff — table containing all the staff; academic and non-academic.
• guardians — table containing list of all guardians for Stardust students.
• results — every result in every subject for every student in Stardust.
• students — table containing every Stardust student (past and present).
• classes — lookup table for all 6 academic years (JSS1 — SS3)

2: Database Creation

This step involves creating Stardust High’s database, along with all the tables involved. The first step here is relatively simple:

-- Drop any existing schemas of the same name
DROP SCHEMA IF EXISTS stardust;

-- Create a new Stardust schema
CREATE SCHEMA stardust;

-- Set stardust as the active schema for subsequent queries
USE stardust;

Next comes the table creation. All 13 tables will be created, along with their constraints: primary key, foreign key, nulls, defaults, etc. Here’s a code snippet from the staff table creation.

CREATE TABLE staff (
 staff_id INTEGER PRIMARY KEY, -- Sets Staff Id as primary key
    first_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),
    surname VARCHAR(50) NOT NULL,
    email VARCHAR(150),
    gender_id VARCHAR(50) REFERENCES genders (gender_id),
    phone VARCHAR(20),
    position_id INTEGER NOT NULL,
    marital_id INTEGER,
    FOREIGN KEY (position_id) REFERENCES staff_positions (position_id),
    FOREIGN KEY (marital_id) REFERENCES marital_status (status_id)
);

See SQL codes for all 13 tables here.

3: Triggers

Next comes the triggers. In simple terms, triggers are actions to be carried out automatically, tied to other actions by SQL code. In layman’s terms; one thing happens on the database (an update, insert, delete, etc.), and it causes something else to happen in the database. Read more about Triggers on freecodecamp.

The following triggers are set up in the database:

1. Staff Table Triggers: There were three triggers in the staff table; one to update the number of staff in each position, one to create an employee email address, and another to create an employee id.
2. Student Table Triggers: Similar to the staff table, the triggers here were for student email, student id, and a third to update the number of students in each class.
3. Result Trigger: A single trigger to register the date wherein a result entry was made
4. Term and Year Triggers: Creates ids for new academic sessions, then updates students’ classes and the number of students in each class.

-- Result Trigger
CREATE TRIGGER result_date
BEFORE INSERT ON results
FOR EACH ROW 
 SET new.subject_date = CAST(NOW() AS DATE);

-- Term Triggers
CREATE TRIGGER term_id
BEFORE INSERT ON term
FOR EACH ROW
 SET new.term_id = CONCAT(
                      REPLACE(
                       REPLACE(
                        '2022/2023', '20', '')
                       , '/', '')
                      , CASE WHEN term_name = 'First' THEN '01'
                        WHEN term_name = 'Second' 
                        THEN '02'
                        WHEN term_name = 'Third' 
                        THEN '03'
                       ELSE NULL
                        END
                    );
-- The rest of the triggers can be found on GitHub in the same document 
-- as the database creation queries.

4: Test Data Loading

In order to ensure that the database objects created thus far work properly, the next step would be to create dummy data that fit the inputs this database would expect once it goes live. Some of the data was created manually (especially the lookup tables). The others, however, were created using Cobbl; a dummy data generator. Here’s a peek of the staff table using data generated from Cobbl:

Staff Table, using data generated from Cobbl

Test data for all tables can be found here.

5: Stored Procedures

A stored procedure is a series of SQL statements put together and saved under a name such that, when the named procedure is called, those statements are executed by SQL. It is often said that stored procedures are useful when one intends to reuse the same queries often or to grant access to organization report creators who require certain data but don’t write SQL themselves.

The following stored procedures were created in the Stardust High database:

1. form_teacher_students: to show each form teacher a list of students in their class. This procedure takes in a single argument; the teacher’s staff id.
2. guardians_wards: to pull up a list of students in the school (past and present) registered by a parent/guardian. This takes the guardian id as its sole argument.
3. num_students_class: shows a list of students currently in a given class along with the number of students in said class. This takes two arguments; one input and one output.
4. student_details: takes in an argument (student id) and returns their details from their full name to their age, and their current form teacher.
5. student_subjects: takes in two arguments — students id and department (Science/Arts/Commercial) where necessary — and returns a list of subjects such a student can take in the current term.
6. student_results: takes three arguments — student id, academic year, and term — and returns the uploaded results for the student that term along with the term average.

See the stored Procedure for Student Results:

DELIMITER //                 -- Change the delimiter
CREATE PROCEDURE student_results 
(
  IN stud_id INT,            -- Student ID
  IN acad_year VARCHAR(20),  -- Academic Year (for result)
  IN term_i VARCHAR(20)      -- term
  )
BEGIN
 WITH res1 AS (              -- A CTE to retrieve the original result
   SELECT
      s.subject_name,
      test1,
      test2,
      exam,
      (test1+test2+exam) AS total
   FROM results r
     LEFT JOIN subjects s ON r.subject_id = s.subject_id
   WHERE student_id = stud_id 
      AND term_id = (         -- Subquery to retrieve term id
              SELECT term_id FROM term 
              WHERE academic_year = acad_year AND term_name = term_i
              )

   UNION                      -- Union, append the result average
   
   SELECT 
       'Overall Avg:',
        null, null, null,
        ROUND(AVG(test1+test2+exam), 2)
   FROM results
   WHERE student_id = stud_id 
    AND term_id = (
             SELECT term_id FROM term 
             WHERE academic_year = acad_year AND term_name = term_i
              )
),
grad AS (                     -- A second CTE to add the grades
 SELECT 
      *,
      COALESCE(LAG(min_score, 1) OVER(), 100) AS next_score
 FROM grades
)

-- Main query to match the grades to the results
SELECT 
 subject_name,
    test1, test2, exam,
    total,
    grade_id AS grade,
    honour
FROM res1 r
LEFT JOIN grad g
ON r.total > g.min_score
 AND r.total BETWEEN g.min_score AND next_score;
END //
DELIMITER ;          -- Change the delimiter back to the semicolon


-- Next, test the procedure with one of the students
CALL student_results(2020101, '2022/2023', 'First');

Result for student with id 2020101 in 1st term, 2022/2023 academic year

See the rest of the stored procedures here.

6: User Management

The next task here is creating users to access database objects. Three different permission levels are in order:

1. Database Administrator: All privileges granted.
2. Database Manager: Other ICT staff get privileges such as CREATE, CREATE VIEW, ALTER, INSERT, DROP, DELETE, etc.
3. Form Teachers & ICT Interns: Form teachers need to access student results and see certain student details. Hence, they are granted SELECT and EXECUTE permissions.

First, these users are created, using their organization email address. The organization email is used because this project assumes the database will be hosted on the school’s private server.

-- This block creates all the necessary database accounts: 
-- that is, ICT staff and the class teachers. 
-- The last 3 users are ICT staff; administrators

CREATE USER 
 'nathan.l.bailey'@'staff.stardust.com' IDENTIFIED BY 'nathan123l',
 'brianna.e.allen'@'staff.stardust.com' IDENTIFIED BY 'brianna123e',
 'nicholas.w.roberts'@'staff.stardust.com' IDENTIFIED BY 'nicholas123w',
 'audrey.l.gonzales'@'staff.stardust.com' IDENTIFIED BY 'audrey123l',
 'alyssa.n.simmons'@'staff.stardust.com' IDENTIFIED BY 'alyssa123n',
 'alexandra.l.hernandez'@'staff.stardust.com' IDENTIFIED BY 'alexandra123l',
 'alexandra.m.wilson'@'staff.stardust.com' IDENTIFIED BY 'alexandra123m',
 'lillian.m.turner'@'staff.stardust.com' IDENTIFIED BY 'lillian123m',
 'robert.clark'@'staff.stardust.com' IDENTIFIED BY 'robert123c',
 'stephen.w.lopez'@'staff.stardust.com' IDENTIFIED BY 'stephen123w'
PASSWORD EXPIRE;

-- The 'Password Expire' statement ensures the original password expires 
-- upon first login and they create a new password for better security

Next, we create the permissions.

-- Lilian Turner is the sole DB Admin, so their permission is 
-- specifically created
GRANT ALL PRIVILEGES ON stardust.*
TO 'lillian.m.turner'@'staff.stardust.com'; 

-- For DB Managers, more ICT staff could be hired along the way; and since
-- there are too many privileges, a role is created instead.

CREATE ROLE DBManager; 

GRANT     -- Grants privileges to the new role
 CREATE,
 ALTER,
    CREATE TEMPORARY TABLES,
    CREATE VIEW,
    EVENT,
    EXECUTE,
    INDEX,
    INSERT, 
    SELECT, 
    SHOW VIEW, 
    TRIGGER, 
    UPDATE 
ON stardust.* TO DBManager;


-- This block adds the ICT DB Managers to the role
GRANT 'DBManager'
TO 
'robert.clark'@'staff.stardust.com', 'stephen.w.lopez'@'staff.stardust.com';


-- Select and Execute privilege for the form teachers and future interns
-- to enable them so they can use stored procedures
GRANT SELECT, EXECUTE ON stardust.*
TO
 'nathan.l.bailey'@'staff.stardust.com',
 'brianna.e.allen'@'staff.stardust.com',
 'nicholas.w.roberts'@'staff.stardust.com',
 'audrey.l.gonzales'@'staff.stardust.com',
 'alyssa.n.simmons'@'staff.stardust.com',
 'alexandra.l.hernandez'@'staff.stardust.com',
 'alexandra.m.wilson'@'staff.stardust.com';

7. Backup and Recovery

Like all forms of digital information, data stored within a database can be lost due to several reasons; from user erros to hardware malfunction. Therefore, it is important to occasionally save a copy of your data for easy retrieval in the case of a mishap. For stardust college, I would recommend the following:

• A cloud storage; a different service provider (needless to say, off-premises)
• A backup hard-drive/optical storage
• A backup on the on-premises server itself, to be synced periodically.

Conclusion

Having a database like this one only serves to create a platform for schools to better manage their student data and understand metrics for both the business and educational aspects of the institution. Room for growth appears in the following departments;

• Financial data, to help both school management and guardians track payments for students.
• A school manager app (such as a Power BI service app) to help parents, staff, students, and other stakeholders access data visualization to uncover trends as concerns them — business or academic.
• Adding data about students in extracurricular activities; prefects, representatives in competitions, medals won, etc.
• A pipeline from this project to an input source where the student and guardian data can be added by admin, staff data by HR, and results, by their various teachers.

Thank you for reading up to this point. See the complete project repository here. Your comments, suggestions and other corrections are welcome.