DEV Community: Joy Mbugua

Building My First Real Database: What a Weekend SQL Assignment Taught Me About Postgres (and Git)

Joy Mbugua — Sat, 18 Jul 2026 10:21:58 +0000

This weekend I got an assignment that sounded simple on paper: build a small school database for a fictional secondary school called Greenwood Academy, fill it with data, and then write a bunch of queries against it. Three tables. Thirty questions. Easy, right?

It was not "easy," but it was one of those exercises where you can actually feel yourself understanding SQL instead of just copying syntax from a tutorial. Here's what the process looked like, and a few things that tripped me up along the way.

Setting up the database

I'm using PostgreSQL hosted on Aiven, with DBeaver as my SQL client. The first step was creating a schema basically a named container to keep all of Greenwood Academy's tables separate from anything else in the database:

CREATE SCHEMA greenwood_academy;
SET search_path TO greenwood_academy;

That second line tripped me up at first. I kept looking for a USE statement like you'd see in MySQL tutorials, and PostgreSQL just doesn't have one. SET search_path is the equivalent-it tells PostgreSQL, "when I say students, I mean greenwood_academy.students" for the rest of the session.

From there I created three tables:students, subjects, and exam_results, and then immediately had to alter them: adding a phone_number column to students, renaming credits to credit_hours in subjects, and then dropping that same phone_number column a few questions later because the "school" changed its mind. It's a small thing, but it made ALTER TABLE feel less like a random command and more like something you reach for constantly in real database work, schemas are never really "done."

Filling it up and immediately breaking it

Once the tables existed, I inserted 10 students, 10 subjects, and 10 exam results.Then the assignment had me simulate the kind of small corrections that happen in any real system: a student moves cities, a mark gets entered wrong and needs fixing, an exam result gets cancelled and needs deleting.

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

UPDATE exam_results
SET marks = 59
WHERE result_id = 5;

DELETE FROM exam_results
WHERE result_id = 9;

Nothing complicated, but it's a good reminder of why WHERE clauses deserve respect.Forget one on an UPDATE or DELETE and you're not fixing one row, you're rewriting the whole table.

The query section is where it clicked

Sections C through F were the actual querying practice: filtering with WHERE, combining conditions with AND/OR, using BETWEEN, IN, NOT IN, and LIKE, running COUNT(), and finally writing CASE WHEN statements to turn raw numbers into labels.

The CASE WHEN questions were my favorite part of the whole assignment.

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;

...felt like the first time in this assignment where SQL stopped being about storing data and started being about presenting it.

The part nobody warns you about: Git

Writing the SQL was honestly the easy part.

Pushing it to GitHub is where I actually got stuck.

I created the repo on GitHub first, then initialized git locally, committed my files, and tried to push — only to get hit with:

! [rejected] main -> main (fetch first)

Turns out my GitHub repo wasn't actually empty — it already had its own README sitting there, and my local repo had a different one for the same file. Classic case of two histories that don't know about each other. The fix was:

git pull origin main --allow-unrelated-histories --no-rebase

which merged the two histories, dumped me into a merge conflict in README.md, and forced me to actually open the file and decide what content to keep between the <<<<<<< and >>>>>>> markers.Once I resolved it, added it, and committed the merge, the push finally went through clean.

It wasn't a big deal in hindsight, but in the moment it was the most stressful five minutes of the whole assignment — way more than any SQL query. If you're newer to git, that's a totally normal thing to run into the first time you create a repo with a README both on GitHub and locally. Now I know: pick one place to create the README, not both.

What I'd tell someone starting this assignment

Don't skip SET search_path.
Run your INSERT statements and immediately check them with SELECT COUNT(*).
Read every WHERE clause twice before running an UPDATE or DELETE.
If your GitHub repo and your local folder both end up with a README.md, expect a merge conflict.

All the SQL files are up in my repo here:

sql-week2-assignment-Joy-Mbugua

Small assignment, but a genuinely useful one, the kind where the mistakes taught me more than the parts that went smoothly.

My Experience in Connecting Power BI to Local SQL and Aiven PostgreSQL(Including an SSL Certificate Fix)

Joy Mbugua — Mon, 06 Jul 2026 19:36:02 +0000

Introduction

When I first started using Power BI, almost all of the datasets I worked with came from Excel files. As I continued learning more about business intelligence, I realised that most real-world applications store their data in databases instead.

To understand how Power BI works with databases, I decided to try two different scenarios. First, I connected it to a database running locally on my machine. I then connected it to a cloud-hosted PostgreSQL database on Aiven to see how the process differed. While the local connection worked almost immediately, the cloud connection introduced SSL certificates, something I had never configured before.

During the process I encountered a certificate trust error that initially prevented Power BI from connecting. After troubleshooting the issue and finding the solution, I thought it would be useful to document the entire process so that anyone facing the same problem can resolve it much faster.

Connecting Power BI to a Local SQL Database

If your database is running locally,for example SQL Server Express or PostgreSQL installed on your computer,the connection process is relatively straightforward. Since the database is on the same machine, there is no SSL certificate configuration required. In my case, Power BI connected successfully as soon as I entered the correct server details and authentication credentials.

Steps

Open Power BI Desktop.
Select Home - Get Data - More...
Search for SQL Server or PostgreSQL Database.
Click Connect.
Enter the server details.

Server: localhost
Database: your database name

Choose your authentication method.
Click Connect and either select Load or Transform Data.

Because the database was running locally, there were no certificate or encryption issues to configure. The connection worked immediately, making this the easiest part of the process.

Connecting Power BI to Aiven PostgreSQL

Unlike the local database, connecting to Aiven required a few additional steps. Since the database is hosted in the cloud, the connection uses a public hostname and requires SSL encryption to keep the communication secure.

Getting the Connection Details

The first thing I did was open my PostgreSQL service on Aiven and copy the connection details. These included the hostname, port number, database name, username and password. I also downloaded the CA certificate because it would later be needed for SSL.

Testing the Connection in DBeaver

Before opening Power BI, I decided to test the connection in DBeaver first. I prefer using DBeaver for troubleshooting because it gives much clearer error messages whenever something isn't configured correctly.

Inside the SSL settings, I imported the CA certificate downloaded from Aiven and changed the SSL mode to Require.

After saving the settings, I tested the connection again and it connected successfully. This confirmed that the database itself was working correctly before moving to Power BI.

Importing Data into PostgreSQL

Since my data was originally stored in a flat file, I used DBeaver's Import Data wizard to create a table and populate it. The wizard automatically mapped most of the columns, making the import process straightforward.

After the import completed, I checked the row count to make sure every record had been transferred successfully.

Connect Power BI to the Aiven database

In Power BI Desktop, go to Get Data - PostgreSQL database
In the Server field, enter your host and port together, separated by a colon: your-host.aivencloud.com:16392
In Database, enter your database name (e.g. defaultdb)
Click OK, then choose Database authentication and enter your username and password

The SSL Certificate Error

This was the only part of the exercise that actually slowed me down. After entering the connection details in Power BI, I expected the connection to work because DBeaver had already connected successfully.
Instead Power BI displayed an error to my surprise.
At first, I assumed I had entered the wrong password or hostname. After checking everything several times, I realised those details were correct because the exact same connection was already working in DBeaver.

After doing some research, I discovered that Windows simply didn't trust Aiven's certificate authority. The issue wasn't with PostgreSQL or my credentials, it was with Windows certificate validation.

After importing the certificate into Windows' Trusted Root Certification Authorities store and restarting Power BI, I retried the connection. This time it connected immediately and displayed all the available tables.

Creating Basic Measures

Once the tables had been loaded into Power BI, I created a few simple DAX measures to confirm that everything was working correctly.

Total Revenue =
SUM(Jcars[revenue])

Total Units Sold =
SUM(Jcars[units_sold])

Gross Profit =
[Total Revenue] -
SUMX(
    Jcars,
    Jcars[unit_cost] * Jcars[units_sold]
)

The measures calculated successfully, confirming that Power BI was reading the PostgreSQL data correctly. From this point onwards, I could build visuals exactly as I would when working with any other data source.

Conclusion

Before working on this exercise, I expected connecting Power BI to a cloud database to be much more complicated than connecting to a local database. After completing both, I realised that the overall process is almost identical. The biggest difference is that cloud databases require SSL because the connection is made over the internet.

The only challenge I encountered was the certificate trust error. Once I understood that Windows simply didn't trust Aiven's certificate authority and imported the CA certificate into the Trusted Root store, the connection worked without any further issues.

Besides learning how to connect Power BI to different database environments, this exercise also gave me a much better understanding of why SSL certificates are important and how cloud-hosted databases secure their connections.

Connecting the Dots: Understanding Database Relationships and SQL Joins

Joy Mbugua — Sun, 28 Jun 2026 21:16:50 +0000

Have you ever wondered how apps like university portals know which courses a student is enrolled in, or how they pull up an instructor's full schedule in seconds?

The answer lies in database relationships - one of the most important concepts in backend development.

In this article, we'll explore:

What database relationships are and why they matter
The three types of relationships: One-to-One, One-to-Many, and Many-to-Many
How relationship schemas work (primary keys, foreign keys)
How SQL Joins let you pull connected data from multiple tables

To keep things grounded, we'll use one running example throughout: a University Management System.

By the end, you won't just understand the theory, you'll see exactly how these concepts connect in a real-world scenario.

What Are Database Relationships?

A database relationship defines how data in one table connects to data in another.

Instead of storing the same information repeatedly, relational databases organize data into separate tables and link them using keys.

Think about our university system. We have a table for students and another for courses.

A student can enroll in multiple courses, and each course can have many students. Rather than storing a student's full details on every course record, we store the student's info once and create a relationship between the two tables.

This keeps data clean, reduces duplication, and makes updates easy. If a student's email changes? Update it in one place - done.

Here's a simple visual of what that looks like:

+------------------+          +------------------+
| Students         |          | Courses          |
+------------------+          +------------------+
| student_id (PK)  |          | course_id (PK)   |
| name             |          | title            |
| email            |          | credits          |
+------------------+          +------------------+
          \                        /
           \                      /
            \                    /
         Enrollments (links students ↔ courses)

Now let's look at the three types of relationships you'll encounter.

Types of Database Relationships

1. One-to-One (1:1)

Each record in Table A matches exactly one record in Table B and vice versa.

University example: Every student has one student profile (bio, photo, address).

-- Students table
CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

-- Profiles table
CREATE TABLE profiles (
  profile_id INT PRIMARY KEY,
  student_id INT UNIQUE,
  bio TEXT,
  photo_url VARCHAR(255),
  FOREIGN KEY (student_id) REFERENCES students(student_id)
);

The UNIQUE constraint on student_id in the profiles table is what enforces the one-to-one rule; no two profiles can belong to the same student.

2. One-to-Many (1:N)

One record in Table A can relate to many records in Table B.

University example: One department has many students. One instructor teaches many courses.

-- Departments table
CREATE TABLE departments (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(100)
);

-- Students table (updated)
CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Many students share the same dept_id. That's your one-to-many relationship.

3. Many-to-Many (M:N)

Records in Table A can relate to many records in Table B, and records in Table B can relate to many in Table A.

University example: Students enroll in many courses. Each course has many students enrolled.

You can't link these directly but instead you need a junction table (also called a bridge or pivot table).

-- Enrollments (junction table)
CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  course_id INT,
  enrolled_date DATE,
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Understanding Relationship Schemas

Primary Keys

A Primary Key (PK) uniquely identifies every row in a table. No two rows can share the same value.

In our students table, student_id is the primary key therefore every student gets a unique ID.

Foreign Keys

A Foreign Key (FK) is a column in one table that points to the primary key of another table.

In enrollments, both student_id and course_id are foreign keys. They reference the students and courses tables respectively.

Why This Matters

Keys are what make relationships real in a database. Without them, your tables are just isolated spreadsheets. With them, your database understands how data connects, and your queries can take advantage of that.

SQL Joins

Now that our tables are related, Joins are how we query across them.

Let's use these sample tables:

students               enrollments            courses
-----------            -----------            -----------
1 | Alice              1 | 1 | 101            101 | Math
2 | Bob                2 | 1 | 102            102 | Physics
3 | Carol              3 | 2 | 101            103 | History

INNER JOIN

Returns only rows where there's a match in both tables.

-- Which courses is Alice enrolled in?
SELECT students.name, courses.title
FROM students
INNER JOIN enrollments ON students.student_id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.course_id
WHERE students.name = 'Alice';

Result: Carol appears with NULL for course title — she exists but isn't enrolled yet.

RIGHT JOIN

The opposite of LEFT JOIN, it returns all rows from the right table, plus matches from the left.

-- Show all courses, even ones with no students enrolled
SELECT students.name, courses.title
FROM students
RIGHT JOIN enrollments ON students.student_id = enrollments.student_id
RIGHT JOIN courses ON enrollments.course_id = courses.course_id;

Result: History (103) shows up with NULL for student name where the course exists but nobody enrolled.

FULL OUTER JOIN

Returns everything from both tables. NULLs fill in wherever there's no match.

SELECT students.name, courses.title
FROM students
FULL OUTER JOIN enrollments ON students.student_id = enrollments.student_id
FULL OUTER JOIN courses ON enrollments.course_id = courses.course_id;

Result: Every student and every course appears whether matched or not.

Conclusion

Let's recap what we covered:

Database relationships prevent data duplication by linking tables instead of repeating data
One-to-One: one student, one profile
One-to-Many: one department, many students
Many-to-Many: students ↔ courses through an enrollments table
Primary Keys uniquely identify rows; Foreign Keys point to another table's PK
SQL Joins let you query across related tables ,INNER, LEFT, RIGHT, and FULL OUTER

Our university management system went from isolated tables to a fully connected, queryable database.

That's the power of relational databases.

If this helped, drop a comment or reaction below and let me know which SQL join trips you up the most!

How Excel Is Used in Real-world Data Analysis

Joy Mbugua — Sun, 07 Jun 2026 05:15:10 +0000

When I started my Data Science and Analytics course a week ago, I expected to spend most of my time learning programming languages and building machine learning models.Instead one of the first tools we were introduced to was Microsoft Excel. At first, I wondered why a spreadsheet application was so important in a Data Science course, especially because I initially thought it was just something that accountants used. After just one week of learning it, I am starting to understand why.

So, what even is Excel? At its core, it is a spreadsheet tool where you work with rows, columns, and cells to store and organize data. That is the simple version. But after one week, I am starting to see that it is also a place where raw, messy data can actually start to make sense, and also how that data is eventually used in decision-making

Real-World Uses I Did Not Know About

1.Cleaning up messy data

One of the first things that surprised me was how much of data analysis is simply just fixing bad data. In real jobs, data comes in all shapes. Names typed in random cases, extra spaces everywhere, inconsistent formats. This week I learned PROPER(), UPPER(), and LOWER(), and I immediately thought: this is literally what analysts do before anything else. They clean. Removing duplicates is another one that sounds boring but is actually critical because one duplicate record in a sales dataset can throw off an entire report.

2.Tracking and summarizing numbers

Think about a small business owner who wants to know their average monthly sales, or a manager counting how many employees completed a training. Functions like SUM(), AVERAGE(), COUNT(), MAX(), and MIN() handle all of that. These functions seem simple at first glance but are actually very powerful when it comes to summarizing information very quickly to be used for creating reports and identifying trends. I practiced these on a sample dataset this week, that is the HR_dirty_data.xlsx dataset and it helped me understand the concepts even faster.

3. Filtering and validating data before analysis

Sorting and filtering are things I had used before without thinking much about them. But now I understand why they matter. Before you analyze anything, you need to see the right slice of your data. Filtering lets you do that without deleting anything. I also learned about data validation this week, which is used to control what gets entered into a cell in the first place. That is something I had never thought about before, but it makes a lot of sense for keeping datasets consistent.

Features That Stood Out to Me

Conditional formatting was probably my favorite discovery this week. You highlight cells based on their values, and suddenly a column of numbers becomes something you can read at a glance. I used it to flag values above a certain threshold and it felt genuinely useful, not just decorative.

Another concept that changed the way I think about spreadsheets was cell references. Rather than typing values directly into formulas, Excel allows you to reference cells so calculations update automatically whenever the underlying data changes.Here is an example i practiced:

=AVERAGE(B2:B10)

This formula calculates the average of the values between cells B2 and B10. What impressed me most was that if any of those values change, Excel automatically updates the result. That made me appreciate why analysts rely on cell references instead of manually recalculating everything.

Removing duplicates was another one I underestimated. In the exercise we did, there were repeated customer entries in a list. Finding and removing them manually would have taken forever, but with Excel's Remove Duplicates feature, the repeated records were removed in seconds, leaving a cleaner and more reliable dataset for analysis.

How This Week Changed the Way I See Data

Before this week, I thought data analysis was mainly about calculations and complex software. Now I realize that a large part of the process involves organizing, validating, and cleaning data before any analysis can happen.

Learning Excel has shown me that sorting, filtering, formatting, and cleaning are not just preparation steps. They are essential parts of working with data effectively. Even after only one week, I can already see why Excel remains one of the most widely used tools in data analysis.

I still have a lot to learn, but I am excited to continue building my skills and discovering how these foundations connect to more advanced areas of data science.