Database from Scratch Using PostgreSQL

Building a School Database from Scratch Using PostgreSQL

Introduction

Databases play an important role in storing, organizing, and managing information efficiently. Educational institutions such as schools handle large amounts of data, including student details, subjects, and academic performance records. A properly designed database helps ensure that this information can be stored securely and retrieved easily.

This article demonstrates the process of designing and managing a relational database for Greenwood Academy, a secondary school in Nairobi, using PostgreSQL. as an EXAMPLE

The project covers the complete database workflow, including:

• Creating a database schema
• Designing relational tables
• Defining constraints and relationships
• Inserting and modifying records
• Querying data using SQL
• Applying filtering, aggregation, and conditional logic

The key SQL concepts covered include:

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• SQL filtering techniques
• SQL operators
• Aggregate functions
• CASE WHEN statements

Part1: Creating schemas and tables (DDL)

A database schema provides a logical structure for organizing database objects such as tables, views, and functions.

For the Greenwood Academy database, a dedicated schema is created to separate school-related data from other database objects.

CREATE SCHEMA greenwood_academy;

SET search_path TO greenwood_academy;

The SET search_path command ensures that all database objects created afterward are stored within the greenwood_academy schema.

Designing Database Tables

A relational database stores information in structured tables where data is organized into rows and columns.

The Greenwood Academy database consists of three main tables:

• students
• subjects
• exam_results

Creating the Students Table

The students table stores basic information about learners, including names, gender, class, and location.

CREATE TABLE 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)
);

Part2: Modifying table structures using ALTER TABLE

Database structures may change as new requirements arise. PostgreSQL provides the ALTER TABLE command to modify existing tables without recreating them.

Adding a Column

A phone number column can be added to the students table using:

ALTER TABLE students
ADD COLUMN phone_number VARCHAR(20);

Removing a Column

Unnecessary columns can be permanently removed.

ALTER TABLE students
DROP COLUMN phone_number;

Part3: Inserting, updating and deleting records (DML)

After creating the database structure, the next step is populating the tables with actual records.

After creating the database structure, records can be added using Data Manipulation Language (DML).

The main DML commands include:

INSERT: Adds new records
UPDATE: Modifies existing records
DELETE: Removes records

Example:
The INSERT statement is used to add new records into database tables.

Adding Student Records

The students table stores learner information such as names, gender, date of birth, class, and city.

INSERT INTO students VALUES
(1,'Amina','Wanjiku','F','2008-03-12','Form 3','Nairobi'),
(2,'Brian','Ochieng','M','2007-07-25','Form 4','Mombasa'),
(3,'Cynthia','Mutua','F','2008-11-05','Form 3','Kisumu'
); 

Results

Updating existing information:

UPDATE students
SET city='Nairobi'
WHERE student_id=5;

Part4: Querying data using WHERE conditions

The SELECT statement is used to retrieve information stored in database tables.

Example: Finding all Form 4 students:

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

The WHERE clause filters records based on specific conditions.
Results

Part5: Using BETWEEN, IN, NOT IN and LIKE operators

SQL provides several operators for searching and filtering information.

BETWEEN Operator

The BETWEEN operator is used when searching for values within a specific range.

SELECT *
FROM exam_results
WHERE marks BETWEEN 50 AND 80;

Results

LIKE Operator

The LIKE operator is used for pattern-based searches.

SELECT *
FROM students
WHERE first_name LIKE 'A%';

The % wildcard represents any number of characters.
Results

Part6: Aggregating data using COUNT

Aggregate functions allow calculations to be performed on groups of records.

The COUNT() function determines the number of records matching a condition.

Example:

SELECT COUNT(*) AS form3_students
FROM students
WHERE class='Form 3';

Results

Part7: Classifying data using CASE WHEN

The CASE WHEN statement applies conditional logic within SQL queries.

It is useful when creating categories from existing data.

Example:

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

This query creates a new performance category based on examination marks.
Results

Conclusion

These foundational SQL skills are essential for database administration, backend development, and data analytics. Mastering concepts such as DDL, DML, filtering, aggregation, and CASE WHEN provides a strong foundation for working with larger and more complex database systems in real-world applications.