DEV Community: Brian kiplangat

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

Brian kiplangat — Mon, 20 Jul 2026 17:39:34 +0000

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:

Create the schema , give the database a namespace to live in (CREATE SCHEMA + search_path).
Design and create the tables , write contracts with constraints; remember PRIMARY KEY implies NOT NULL and UNIQUE.
Adapt the structure ,schemas are living things; add, rename, and drop columns with ALTER TABLE, treating DROP COLUMN with respect.
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.
Query it ,WHERE, AND/OR, BETWEEN, IN, LIKE, and COUNT: filter, then aggregate.
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.

Linux Fundamentals for Data Engineering

Brian kiplangat — Fri, 12 Jun 2026 01:15:31 +0000

Introduction

As a data engineer, most of your work will happen
on Linux servers. Whether you are managing databases,
running data pipelines, or processing large datasets,
Linux is the operating system that powers the majority
of servers worldwide. Understanding Linux fundamentals
is therefore not optional for data engineers it is
a core skill that separates beginners from
professionals.

In this article, I will walk you through the
essential Linux skills every data engineer needs,
based on my hands-on experience setting up a remote
Ubuntu server, configuring PostgreSQL, and performing

file transfers using SCP.

1. Setting Up Linux on Windows with WSL

Most data engineers start their journey on Windows.
The good news is that you do not need to install a
separate Linux machine Windows Subsystem for Linux
(WSL) allows you to run a full Linux environment
directly inside Windows.

To install WSL, open Windows CMD as Administrator
and run:

wsl --install -d Ubuntu-22.04

After installation, restart your PC. You can now
launch Ubuntu directly from your Start menu or by
typing wsl in CMD.

One important lesson I learned during setup: WSL
comes in different flavors. If your prompt shows
-sh instead of bash, you are running a minimal
shell, not full Ubuntu. In that case, install Ubuntu
specifically using the command above.

2. Connecting to a Remote Server with SSH

SSH (Secure Shell) is the standard way to connect
to remote Linux servers. As a data engineer you will
use SSH daily to access cloud servers, manage
databases, and run data pipelines remotely.

The basic SSH command syntax is:

ssh username@server_ip -p port_number

For example, to connect to our assignment server:

ssh root@159.65.222.96 -p 22

Port 22 is the default SSH port. When connecting
for the first time, you will see:

Are you sure you want to continue connecting?
(yes/no)

Always type yes and press Enter.

One important thing to understand about your
terminal prompt:

# at the end means you are root (full admin)
$ at the end means you are a normal user

Always run whoami to confirm which user you are
operating as this saved me from many permission
errors during this assignment.

3. Linux User Management

On a shared server, every person should have their
own user account. This is important for security,
accountability, and proper file permissions.

To create a new user:

adduser briank

An important lesson I learned: Linux usernames must
be lowercase. When I tried to create a user called
BrianK, I got this error:

Please enter a username matching the regular
expression configured via the NAME_REGEX
configuration variable.

The fix was simple use lowercase:

adduser briank

To give the user sudo (admin) privileges:

usermod -aG sudo briank

To verify the user was created successfully:

id briank

Output:

uid=1088(briank) gid=1088(briank)
groups=1088(briank),27(sudo),100(users)

4. Essential Linux Commands for Data Engineers

Here are the most important Linux commands every
data engineer should know, organized by category:

Navigation Commands

pwd          # print current directory
ls           # list files
ls -la       # detailed list including hidden files
cd Documents # go into a folder
cd ..        # go up one level
cd ~         # go to home directory

File Operations

touch notes.txt           # create empty file
mkdir linux_assignment    # create folder
cp notes.txt backup.txt   # copy file
mv backup.txt old.txt     # rename/move file
rm old.txt                # delete file
cat notes.txt             # view file contents
head -10 notes.txt        # view first 10 lines
tail -10 notes.txt        # view last 10 lines
grep "error" log.txt      # search inside file

System Information

whoami       # current username
uname -a     # system and kernel info
hostname     # server name
uptime       # how long server has been running
df -h        # disk space usage
free -h      # memory usage
top          # running processes (q to quit)
ps aux       # list all processes

File Permissions

Linux file permissions control who can read,
write, and execute files. They are shown as:

-rwxr-xr-x

Breaking this down:

r = read (4)
w = write (2)
x = execute (1)

Three groups: owner, group, others.

To change permissions:

chmod 755 script.sh   # owner: rwx, others: r-x
chmod 644 notes.txt   # owner: rw-, others: r--

To change file ownership:

chown briank notes.txt

Networking Commands

ip a                  # show network interfaces
ping google.com -c 4  # test connectivity
netstat -tulnp        # show open ports
ss -tlnp              # modern version of netstat
curl ifconfig.me      # show your public IP

5. PostgreSQL Setup on Linux

PostgreSQL is the most popular open source database
for data engineering. Here is how to set it up on
Ubuntu:

Installation

apt update
apt install postgresql postgresql-contrib -y

Start and Enable the Service

systemctl start postgresql
systemctl enable postgresql
systemctl status postgresql

Log Into PostgreSQL

su -s /bin/bash postgres
psql

Create a Database and Schema

CREATE DATABASE briank;
\c briank
CREATE SCHEMA staging;

Create a Table and Insert Data

CREATE TABLE staging.farmers (
    id SERIAL PRIMARY KEY,
    farmer_name VARCHAR(100),
    county VARCHAR(50),
    subcounty VARCHAR(50),
    acreage DECIMAL(5,2),
    crop VARCHAR(50),
    loan_amount DECIMAL(10,2),
    loan_status VARCHAR(20),
    season VARCHAR(20)
);

INSERT INTO staging.farmers 
(farmer_name, county, subcounty, 
acreage, crop, loan_amount, loan_status, season)
VALUES
('John Kipchumba', 'Uasin Gishu', 
 'Turbo', 2.5, 'Maize', 15000.00, 'Paid', '2023A'),
('Mary Jelimo', 'Uasin Gishu', 
 'Soy', 1.8, 'Maize', 12000.00, 'Defaulted', '2023A'),
('Peter Rotich', 'Uasin Gishu', 
 'Eldoret East', 3.2, 'Maize', 20000.00, 'Paid', '2023B');

Useful psql Commands

\l          -- list all databases
\c dbname   -- connect to database
\dt         -- list all tables
\du         -- list all users
\q          -- quit psql

Allow External Connections

To allow tools like DBeaver or pgAdmin to connect
remotely, two configuration files need editing:

postgresql.conf change listen_addresses:
listen_addresses = ''
**pg_hba.conf* — add this line at the bottom:
host all all 0.0.0.0/0 md5
Then restart PostgreSQL:

systemctl restart postgresql

6. File Transfers with SCP

SCP (Secure Copy Protocol) uses SSH to transfer
files between your local machine and a remote server.
This is essential for data engineers who need to
move datasets, scripts, and configuration files.

Upload from local PC to server

scp C:\Users\Brian\notes.txt root@159.65.222.96:/root/

Download from server to local PC

scp root@159.65.222.96:/root/notes.txt C:\Users\Brian\Downloads\

Copy an entire folder

scp -r myfolder/ root@159.65.222.96:/root/

Use SSH key instead of password

scp -i ~/.ssh/mykey.pem file.txt root@server:/path/

7. Key Lessons Learned

During this assignment I encountered several
real-world challenges that taught me valuable lessons:

Lesson 1 Always check who you are
Running whoami before every session saved me
from making changes as the wrong user. I accidentally
switched to another student's account and spent time
wondering why permissions were denied.

Lesson 2 Usernames must be lowercase
Linux enforces strict naming rules. BrianK failed
but briank worked perfectly.

Lesson 3 The prompt tells you everything
# means root, $ means normal user.
=# in psql means ready, (# means incomplete
command press Ctrl+C to cancel.

Lesson 4 WSL is not always Ubuntu
Not all WSL installations are equal. A minimal
shell missing apt, sudo, and ssh taught me
to always verify my environment with
cat /etc/os-release.

Lesson 5 Shared servers have history
On a shared assignment server, previous students
had already made some configurations. Always check
before editing - use grep and tail to verify
what already exists.

8. Conclusion

Linux is the backbone of modern data engineering.
From managing remote servers with SSH, to setting
up PostgreSQL databases, to transferring files with
SCP every skill covered in this article is used
daily by professional data engineers.

The best way to learn Linux is by doing. Set up WSL
on your Windows machine, spin up a cloud server,
and practice these commands every day. The more you
use them the more natural they become.

As I continue my journey in data engineering at
LuxDevHQ Cohort 8, Linux will remain a foundation
skill that everything else builds on from Python
data pipelines, to cloud infrastructure, to
geospatial data processing with PostGIS.

Brian Kiplangat - LuxDevHQ Data Engineering
Cohort 8 | Nairobi, Kenya

GitHub: https://github.com/kiplangatbrian85/
linux-data-engineering