Databases and SQL — Data Technician Bootcamp (Week 3)

Relational database design and hands-on SQL, completed during Week 3 of a Data Technician bootcamp. The week covered the theory of how relational data is structured, a full schema design for a retail scenario, and a practical PostgreSQL query set run against a live cloud database.

---

Overview

Topics	Relational database design, entity relationships, SQL querying and debugging
Tools	PostgreSQL, Supabase, ERD modelling
Dataset	world_combined_30 — countries, cities, languages, official-language flags, population
Deliverables	ERD, retail schema design, ~20 PostgreSQL queries with fault diagnosis

---

Skills demonstrated

Database design and modelling

• Producing an entity relationship diagram (ERD) in draw.io to model tables, fields and the links between them. The worked example models a student results schema and resolves a many-to-many relationship explicitly:

Table	Fields
Student	id (PK), name
Class	class_id (PK), class_name
Results	result_id (PK), student_id (FK), class_id (FK), mark

A student can take many classes and a class holds many students — so Results sits between them as the linking table, carrying both foreign keys plus the attribute that only exists at the intersection (mark). That last point is the reason a linking table is not merely plumbing: a mark belongs to the pairing of a student and a class, not to either one alone.

• Distinguishing primary keys (unique, non-null, one per record) from secondary keys (used for searching, sorting and grouping; not required to be unique)
• Using foreign keys to express relationships — a primary key value stored in a second table to link the two
• Identifying and modelling all three cardinalities:

Relationship	Example	Implementation
One-to-one	A person and their passport	Shared unique identifier
One-to-many	A customer and their orders	customer_id as a foreign key in Orders
Many-to-many	Students and classes	A linking table (Enrolments) holding both foreign keys

• Comparing relational and non-relational models, and identifying where a flexible schema is the better fit — semi-structured or unstructured data such as social media posts, images, activity logs and product catalogues, where fields differ between records or change frequently

SQL joins

All six join types researched, written and explained with worked examples:

-- INNER JOIN — only records matching in both tables (customers who have ordered)
SELECT Customers.name, Orders.order_id
FROM Customers
INNER JOIN Orders ON Customers.customer_id = Orders.customer_id;

-- LEFT JOIN — every customer, including those with no orders
SELECT Customers.name, Orders.order_id
FROM Customers
LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id;

-- RIGHT JOIN — every customer retained from the right-hand table
SELECT Customers.name, Orders.order_id
FROM Orders
RIGHT JOIN Customers ON Orders.customer_id = Customers.customer_id;

-- FULL JOIN — all matching and non-matching records from both sides
SELECT *
FROM Customers
FULL JOIN Orders ON Customers.customer_id = Orders.customer_id;

-- SELF JOIN — a table joined to itself, linking employees to their managers
SELECT e.name AS Employee, m.name AS Manager
FROM Employees e
JOIN Employees m ON e.manager_id = m.employee_id;

-- CROSS JOIN — every possible combination (each product against each colour)
SELECT Products.product_name, Colours.colour
FROM Products
CROSS JOIN Colours;

Querying in PostgreSQL

Queries written and executed against a live Supabase-hosted PostgreSQL database:

Technique	Applied to
SELECT DISTINCT	Deduplicating the country list from a multi-row-per-country table
WHERE with AND / comparison / boolean	Filtering by country, by official-language flag (TRUE/FALSE), by threshold
BETWEEN	Population ranges (100,000 to 3,000,000)
ORDER BY … DESC	Ranking languages by share, countries by count
GROUP BY with COUNT, MAX, SUM, AVG	Languages per country, highest language share, total official-language share
HAVING	Restricting groups — countries with more than one official language
LIKE '%la%'	Pattern matching on city names
Correlated subquery	Finding countries whose most-spoken language is not official
Scalar subquery	Countries above the overall average population
IN (SELECT …)	Rows belonging to countries with more than five languages

-- Correlated subquery: countries whose most-spoken language is not official
SELECT country_name
FROM world_combined_30
WHERE language_percentage = (
        SELECT MAX(w2.language_percentage)
        FROM world_combined_30 AS w2
        WHERE w2.country_name = world_combined_30.country_name
      )
  AND is_official = FALSE;

-- Data quality check: countries whose language shares do not total 100%
SELECT country_name, SUM(language_percentage) AS total_language_share
FROM world_combined_30
GROUP BY country_name
HAVING SUM(language_percentage) < 100
ORDER BY total_language_share;

Debugging SQL

A significant part of the practical was fault-finding rather than writing from scratch — each query was supplied with a deliberate defect to locate, fix and explain in one line. Faults included:

• Missing statement terminator (;)
• Missing table name after FROM
• Missing boolean literal after is_official =
• Missing column reference in a WHERE, ORDER BY, HAVING or AVG() clause
• Missing sort direction (DESC) on ORDER BY
• Missing comparison value in a HAVING or WHERE predicate
• Empty pattern in a LIKE wildcard

This is closer to real database work than writing greenfield queries — most SQL time is spent reading someone else's query and working out why the result looks wrong.

---

Retail database design

A design exercise for a small convenience store moving to a database system, covering inventory, sales, customers and a loyalty scheme.

Requirements

Table	Fields
Inventory	SKU, product name, category, price, stock count, supplier ID
Sales	Transaction ID, transaction date, customer ID, SKU, quantity
Suppliers	Supplier ID, supplier name, contact details
Customers	Customer ID, name, address, telephone, email, membership status, points total
Loyalty	Membership number

Relationships

• Customers → Sales — one-to-many; one customer makes many purchases, customer_id as foreign key
• Suppliers → Inventory — one-to-many; one supplier provides many products
• Sales ↔ Inventory — many-to-many, resolved with a sale_items linking table on transaction_id and SKU
• Customers → Loyalty — one-to-one; each membership belongs to one customer

Users and their needs

User	Requirement
Cashier	Record transactions, identify loyalty customers
Manager	Run sales reports, monitor stock levels
Stock staff	Update on delivery, flag low stock
Database administrator	Accounts, permissions, security, backups

Implementation

-- CREATE TABLE defines a new table and its column data types
CREATE TABLE Products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100),
    price        DECIMAL(10,2),
    stock_count  INT
);

-- INSERT INTO adds new records to an existing table
INSERT INTO Products (product_id, product_name, price, stock_count)
VALUES (1, 'Milk', 1.50, 40);

-- UPDATE changes existing data matching a condition
UPDATE Products
SET stock_count = 35
WHERE product_id = 1;

-- SELECT retrieves stored information
SELECT * FROM Products;

Maintenance and security

• Constraints as the first line of defence — PRIMARY KEY and UNIQUE to prevent duplicate records, NOT NULL to enforce required fields
• Always pairing UPDATE with a WHERE condition, so a single statement cannot silently overwrite every row
• Least-privilege permissions — cashiers can add sales, only managers can change prices
• Backups taken regularly, encrypted, and held in both secure cloud storage and a separate physical location — with restores actually tested, not assumed
• Retention discipline: deciding up front what needs recording, who may access it, and for how long

---

Repository contents

.
├── README.md
├── world_combined_30_rows.sql   # exported table definition and data
├── world_combined_30_rows.csv   # same dataset in CSV
└── queries/                     # completed PostgreSQL queries with fix notes

---

Key takeaways

• Design the relationships before writing a single CREATE TABLE — a many-to-many missed at design time becomes a linking table retrofitted under pressure later
• A HAVING clause filters groups, a WHERE clause filters rows; confusing the two produces a query that runs and returns the wrong answer
• Boolean columns fail quietly — is_official = with nothing after it is a syntax error, but filtering on the wrong boolean returns a clean, plausible, incorrect result
• Sum the parts and check they reconcile; the language-share query that totals under 100% is a data quality test, not a bug
• UPDATE without WHERE is the single most expensive typo in SQL