The Role of a Database Administrator: Building a Database from Scratch in PostgreSQL

A database administrator (DBA) is the custodian of an organisation's data: the person who designs the structures that hold it, enforces the rules that keep it accurate and consistent, loads and maintains the records as the real world changes, protects it from accidental damage, and turns it into answers when the organisation asks questions. It's a role that blends architecture, discipline, and service. You build the system, you guard its integrity, and you serve everyone who depends on it. In this guide, I step into that role for Greenwood Academy, a secondary school in Nairobi with no database at all, and walk through the full process a DBA follows to build one from scratch in PostgreSQL: create the schema, design the tables, adapt the structure when requirements change, load and correct the data, and finally query it. The complete scripts available on GitHub: sql-week2-assignment-brian.

Step 1 - Create the schema: give the database a home

Before a single table can exist, it needs somewhere to live. In PostgreSQL that's a schema; a namespace that keeps Greenwood's tables from colliding with anything else on the server:

CREATE SCHEMA greenwood_academy;
SET search_path TO greenwood_academy;

The second line is the one beginners skip and then suffer for. search_path tells PostgreSQL which schema to use by default without it, every table reference needs the full greenwood_academy.students prefix for the rest of the project.

Step 2 - Design and create the tables: write the contracts

A DBA doesn't start typing CREATE TABLE immediately. First, you ask what the organisation is, in data terms. A school reduces to three entities:

Table	Represents
students	Who is enrolled
subjects	What is taught
exam_results	How each student performed in each subject

Each CREATE TABLE is a contract: every column gets a name, a data type, and rules the data must obey forever.

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

Reading the contract: every student must have a unique ID and a full name; everything else the database will accept in silence, even if missing. One rule worth memorizing early- PRIMARY KEY already implies NOT NULL and UNIQUE, so you never write those alongside it.

The subjects table adds one new promise that no two subjects may share a name:

CREATE TABLE subjects (
    subject_id   INT PRIMARY KEY,
    subject_name VARCHAR(100) NOT NULL UNIQUE,
    department   VARCHAR(50),
    teacher_name VARCHAR(100),
    credits      INT
);

Then the table that ties the school together:

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

exam_results doesn't describe a person or a subject, it describes a relationship between them: this student sat this subject and scored these marks. Those student_id and subject_id columns are logically foreign keys, already pointing at the other two tables and waiting for the day we learn joins.

⚠️ Keep an eye on that grade column. It stores a value that's derived from marks. In Step 4, that design choice comes back to bite, exactly the way it bites in real databases.

Step 3 - Adapt the schema: requirements change (ALTER TABLE)

No schema survives contact with stakeholders. Barely a day into the job, the requests start arriving, and each one maps to one of the three ALTER TABLE moves a DBA uses constantly.

"We forgot phone numbers. Add a column."

ALTER TABLE students ADD COLUMN phone_number VARCHAR(20);

"'Credits' is confusing. Call it 'credit_hours'."

ALTER TABLE subjects RENAME COLUMN credits TO credit_hours;

"Actually, we've decided we don't need phone numbers. Remove it."

ALTER TABLE students DROP COLUMN phone_number;

Add, rename, drop one afternoon, all three. The lesson isn't the syntax; it's that schemas are living things. Nobody designs a database correctly on the first try, and the DBA who evolves structure calmly is the one the school keeps. One caution: DROP COLUMN destroys the column and all its data immediately, with no undo in production, that statement gets reviewed twice.

Step 4, Load and maintain the data: the registrar arrives (DML)

Structure without data is just an opinion. The registrar delivers the records: 10 students, 10 subjects, 10 exam results. Rather than 30 separate statements, a multi-row INSERT loads each table in one go:

INSERT INTO students (student_id, first_name, last_name, gender, date_of_birth, class, city) 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'),
(4,  'David',   'Kamau',   'M', '2007-02-18', 'Form 4', 'Nairobi'),
(5,  'Esther',  'Akinyi',  'F', '2009-06-30', 'Form 2', 'Nakuru'),
(6,  'Felix',   'Otieno',  'M', '2009-09-14', 'Form 2', 'Eldoret'),
(7,  'Grace',   'Mwangi',  'F', '2008-01-22', 'Form 3', 'Nairobi'),
(8,  'Hassan',  'Abdi',    'M', '2007-04-09', 'Form 4', 'Mombasa'),
(9,  'Ivy',     'Chebet',  'F', '2009-12-01', 'Form 2', 'Nakuru'),
(10, 'James',   'Kariuki', 'M', '2008-08-17', 'Form 3', 'Nairobi');

Subjects and exam results load the same way (full statements in the repo ; note the subjects insert uses credit_hours, the column renamed in Step 3). A roll call confirms everything arrived:

SELECT * FROM students;
SELECT * FROM subjects;
SELECT * FROM exam_results;

A habit worth forcing on yourself: always list column names explicitly in an INSERT. The shortcut INSERT INTO students VALUES (...) silently depends on column order, one schema change later, cities end up in the class column without so much as an error.

Corrections arrive; the safe way to UPDATE and DELETE

The database hums along for about an hour. Then real life intervenes with three pieces of news.

Esther Akinyi has moved from Nakuru to Nairobi. Before touching anything, preview exactly which rows the WHERE clause will hit:

SELECT * FROM students WHERE student_id = 5;   -- preview first
UPDATE students SET city = 'Nairobi' WHERE student_id = 5;

Why the ritual? An UPDATE without a WHERE clause rewrites every row in the table — no confirmation dialog, no undo. The preview costs five seconds and removes all guesswork.

An exam has been cancelled. Same discipline, then delete:

DELETE FROM exam_results WHERE result_id = 9;

And a marks entry was wrong — result 5 was recorded as 49 when the student actually scored 59:

UPDATE exam_results SET marks = 59 WHERE result_id = 5;

Fixed? Look at the row again:

 result_id | marks | grade
-----------+-------+-------
         5 |    59 | D

The marks are corrected, but grade that column from Step 2- still says 'D', the grade that matched the wrong marks. Under the scale used elsewhere in the data, 59 is a 'C'. One fact, stored twice, and only one copy got updated.

This is the derived-value trap in miniature: grade is computable from marks, so storing both creates two versions of the truth and copies drift. In a 10-row table a DBA spots it by eye. In a 10-million-row production table, it's a silent data quality bug that surfaces months later in a report nobody trusts. Step 6 delivers the fix.

Step 5 - Query the data: the staffroom discovers the database

With the data settled, the questions start coming, and this is what the whole process was building toward. Each request from the staff maps to a filtering pattern.

"Who's in Form 4?" the exam office, planning registrations. An exact match:

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

⚠️ PostgreSQL string comparisons are case-sensitive: 'form 4' returns zero rows and zero errors, a classic ten-minute debugging session.

"Which subjects are in Sciences? And show me all the girls' records for the mentorship program":

SELECT * FROM subjects WHERE department = 'Sciences';
SELECT * FROM students WHERE gender = 'F';

"Form 3 students from Nairobi specifically" AND requires both conditions; OR requires either:

SELECT * FROM students WHERE class = 'Form 3' AND city = 'Nairobi';
SELECT * FROM students WHERE class = 'Form 2' OR class = 'Form 4';

"Every result between 50 and 80 marks, and the exams from mid-March" BETWEEN is inclusive on both ends, and works on dates exactly like numbers:

SELECT * FROM exam_results WHERE marks BETWEEN 50 AND 80;
SELECT * FROM exam_results WHERE exam_date BETWEEN '2024-03-15' AND '2024-03-18';

"Students from our three biggest cities" -IN replaces a clumsy chain of ORs, and NOT IN inverts it:

SELECT * FROM students WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');
SELECT * FROM students WHERE class NOT IN ('Form 2', 'Form 3');

"Names starting with A or E... and which subjects have 'Studies' in the name?" pattern matching with LIKE, where % matches any sequence of characters:

SELECT * FROM students WHERE first_name LIKE 'A%' OR first_name LIKE 'E%';
SELECT * FROM subjects WHERE subject_name LIKE '%Studies%';

💡 PostgreSQL also offers ILIKEcase-insensitive LIKE. A Postgres extension rather than standard SQL, and invaluable with messy real-world text.

"Don't show me rows. Just tell me HOW MANY." the deputy principal wants numbers, which is COUNT's job:

SELECT COUNT(*) AS form3_students FROM students WHERE class = 'Form 3';
SELECT COUNT(*) AS high_scores   FROM exam_results WHERE marks >= 70;

Note the order of operations: WHERE filters rows first, then COUNT aggregates what survived. Filter, then aggregate — that sequencing becomes the backbone of every complex analytical query you'll ever write.

Step 6 - Classify at query time: CASE WHEN redeems the grade column

The final request comes from the academic office: label every result; Distinction, Merit, Pass, or Fail.

A week-one DBA might add another column and store the labels. But Step 4 already showed where that road leads: a stored grade drifting away from its own marks. The professional move is the opposite, don't store the label at all; compute it the moment someone asks:

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 exam_results;

The mental model that makes CASE click: it evaluates top-down and stops at the first true condition. That's why WHEN marks >= 60 needs no upper bound, anything 80 or above was already claimed by the branch above it. It also means condition order is your logic: swap the first two branches and every Distinction in the school silently becomes a Merit.

The same pattern classifies students, with IN keeping the conditions tidy:

SELECT
    first_name,
    last_name,
    class,
    CASE
        WHEN class IN ('Form 3', 'Form 4') THEN 'Senior'
        WHEN class IN ('Form 1', 'Form 2') THEN 'Junior'
    END AS student_level
FROM students;

A label computed at read time can never disagree with its source, because it doesn't exist until the moment you ask for it. That's the resolution to Step 4's bug and a design principle you'll reuse for the rest of your career.

The process, in summary

Greenwood Academy now has a single source of truth, and the path there is the same process behind every database ever built:

1. Create the schema , give the database a namespace to live in (CREATE SCHEMA + search_path).
2. Design and create the tables , write contracts with constraints; remember PRIMARY KEY implies NOT NULL and UNIQUE.
3. Adapt the structure ,schemas are living things; add, rename, and drop columns with ALTER TABLE, treating DROP COLUMN with respect.
4. Load and maintain the data , insert with explicit column lists; preview before every UPDATE or DELETE; beware stored derived values, because two copies of one fact will drift.
5. Query it ,WHERE, AND/OR, BETWEEN, IN, LIKE, and COUNT: filter, then aggregate.
6. Classify at query time ,CASE WHEN reads top-down, first match wins, and computed labels can't lie.

The story isn't finished: those student_id and subject_id columns in exam_results are still pointing at the other tables, waiting for JOINs — when the principal finally gets to ask the question he's really after: "Which students are passing which subjects?" That's the next article.

The complete scripts, organised section by section, are on GitHub: sql-week2-assignment-brian. If you're building your first database and anything here doesn't click, drop a comment.

---