Building a School Database Using PostgreSQL

Introduction

Every application we use today relies on a database. Whether it's a banking system, an online shopping platform, or a school management system, databases provide a reliable way to store, organize, and retrieve information efficiently.

I designed and implemented a School Database using PostgreSQL. The project shows how a school can manage information about students, subjects, and examination results in a structured relational database.

I hosted the PostgreSQL database on Aiven, a cloud database hosting platform, and connected to it using DBeaver, an open-source database management tool. This allowed me to write and execute SQL queries against a cloud-hosted database.

Throughout this article, I'll walk through the process of setting up the database environment, designing the database structure using Data Definition Language (DDL), manipulating data with Data Manipulation Language (DML), and querying the database to extract meaningful information.

Project Overview

The objective of this project was to design and implement a relational database for managing information in a school environment. Instead of working with an existing database, I built the entire database from scratch using PostgreSQL.

The database was designed around three main entities:

• Students – stores information such as the student's name, gender, date of birth, class, and city.
• Subjects – stores details about the subjects offered, including the department, teacher, and credit hours.
• Exam Results – records each student's performance in different subjects, including marks, grades, and examination dates.

Technologies Used

To build this project, I used a combination of cloud services, database management tools, and version control technologies. Each tool played a specific role in the development process.

PostgreSQL

The database was built using PostgreSQL, one of the world's most powerful open-source relational database management systems (RDBMS). PostgreSQL provides excellent support for relational database design, SQL standards, data integrity, and scalability, making it an excellent choice for both learning and real-world applications.

Aiven

Instead of hosting the database locally, I used Aiven to provide a cloud-hosted PostgreSQL instance. Aiven provides managed database services, allowing developers to deploy PostgreSQL servers without worrying about server configuration or maintenance.

DBeaver

To interact with the database, I used DBeaver, an open-source database management tool that supports multiple database systems.

DBeaver provided an intuitive graphical interface for:

• Connecting to the PostgreSQL database hosted on Aiven.
• Writing and executing SQL queries.
• Creating database schemas and tables.
• Viewing stored data.
• Managing database objects such as tables, columns, and relationships.

Using DBeaver made it much easier to visualize the database structure while developing the project.

Git and GitHub

To manage the project files and track changes, I used Git for version control and GitHub to host the project repository. This made it easy to organize the SQL scripts, documentation, and project files while providing a public portfolio of the completed work.

Building the Database with Data Definition Language (DDL)

With the database environment ready, the next step was to define the database structure. This was done using Data Definition Language (DDL), a category of SQL commands used to create and modify database objects such as schemas, tables, and columns.

Since the project models a school management system, I first created a dedicated schema called greenwood_academy. Using a schema helps organize related database objects and keeps the database structured, especially as projects grow larger.

CREATE SCHEMA greenwood_academy;

After creating the schema, I created three tables:

• students
• subjects
• exam_results

create table greenwood_academy.students (
student_id int primary key,
first_name varchar(50) not null,
last_name varchar(50) not null,
gender varchar(1),
date_of_birth date,
class varchar(10),
city varchar(50)
);

create table greenwood_academy.subjects (
subject_id int primary key,
subject_name varchar(100) not null unique,
department varchar(50),
teacher_name varchar(100),
credits int
);

create table greenwood_academy.exam_results (
 result_id int primary key,
 student_id int not null,
 subject_id int not null,
 marks int not null,
 exam_date date,
grade varchar(2)
);

Each table was designed to store a specific type of information while maintaining relationships through primary and foreign keys.

Populating the Database with DML

After defining the database structure, I used Data Manipulation Language (DML) to populate the tables with sample data.

This involved inserting student records, subjects, and examination results using the INSERT statement.

INSERT INTO greenwood_academy.students (student_id, first_name, last_name, gender, date_of_birth, class, city)
VALUES (1, 'Amina', 'Wanjiku', 'F', '2008-03-12', 'Form 3', 'Nairobi');

INSERT INTO greenwood_academy.subjects (subject_id, subject_name, department, teacher_name, credit_hours)
VALUES (1, 'Mathematics', 'Sciences', 'Mr. Njoroge', 4);

INSERT INTO greenwood_academy.exam_results (result_id, student_id, subject_id,  marks,  exam_date, grade)
VALUES (1, 1, 1, 78, '2024-03-15', 'B');

Querying the Database

Once the database was populated with data, I began querying it to retrieve meaningful information. This is where SQL becomes powerful, allowing you to filter, summarize, and analyze data efficiently.

For example, I retrieved students from a specific class using the WHERE clause:

SELECT *
FROM greenwood_academy.students
WHERE class = 'Form 4';

Here is the output of this code showing students currently in Form 4:

Filtering records like this makes it easy to answer specific questions without scanning the entire table.

I also used operators such as BETWEEN, IN, and LIKE to perform more flexible searches.

SELECT *
FROM greenwood_academy.exam_results
WHERE marks BETWEEN 50 AND 80;

Output showing marks between 50 and 80:

SELECT *
FROM greenwood_academy.students
WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');

Output showing students from Nairobi, Mombasa and Kisumu:

SELECT *
FROM greenwood_academy.subjects
WHERE subject_name LIKE '%Studies%';

Output showing subject name with the word studies:

I used aggregate functions such as COUNT().

SELECT COUNT(*) AS total_students
FROM greenwood_academy.students
WHERE class = 'Form 3';

Output showing the total number of students in Form 3:

Finally, I used CASE WHEN to categorize examination performance based on students' marks.

SELECT result_id,
       marks,
       CASE
           WHEN marks >= 80 THEN 'Distinction'
           WHEN marks >= 60 THEN 'Merit'
           WHEN marks >= 40 THEN 'Pass'
           ELSE 'Fail'
       END AS performance
FROM greenwood_academy.exam_results;

Output showing examination performance based on students' marks:

These queries demonstrated how SQL can be used not only to retrieve data but also to transform it into meaningful information that supports decision-making.

Project Repository

You can find the complete source code and SQL scripts for this project on GitHub:

GitHub: https://github.com/MugeKaibati/sql-week2-assignment-mugendi

Conclusion

Building this school database was an excellent hands-on exercise that strengthened my understanding of PostgreSQL and relational database concepts. Throughout the project, I gained practical experience creating database structures, manipulating data, and writing queries to retrieve meaningful information.