Understanding databases

01The problem

Kolanut's orders.csv says customer 12 bought something. It doesn't say who customer 12 is, where they are, or which sales rep looks after them. That information lives in a different file, customers.csv. Why split it up? And how do you put it back together?

02The concept

A database is an organised store of data that many people and programs can use at the same time, safely. Most company data you'll analyse lives in one: the order system, the accounting system and the HR system each sit on top of a database.

Spreadsheet or database?

Analysts use both: they pull data out of the database with SQL, then often finish the analysis in a spreadsheet or a BI tool.

A relational database (the most common kind) stores data in several tables, each about one kind of thing, and links them through keys. Common ones are PostgreSQL, MySQL, Microsoft SQL Server, Oracle and SQLite.

Tables, rows and columns

Keys and relationships

• A primary key (PK) uniquely identifies each row in its table. customer_id is the primary key of customers: no two customers share one, and none is empty.
• A foreign key (FK) is a column that points to a primary key in another table. customer_id in orders is a foreign key: it says which customer bought that line.

A drawing like this is called an entity-relationship diagram (ERD). It's the map of a database; when you join a new company, ask for it first.

Kinds of relationship

One-to-many is by far the most common. A many-to-many relationship is stored through a third table in the middle, sometimes called a junction or bridge table. Kolanut's orders is exactly that between customers and products: a customer buys many products, a product is bought by many customers, and each order line records one customer-product pair.

Why not keep everything in one big sheet?

If every order line repeated the customer's name, region, city and rep, then:

• the same facts would be typed thousands of times, so mistakes creep in: "Alhaji Musa Wholesale" on 130 rows and "Alhaji Musa Wholesal" on four;
• when a customer moves city or changes rep, you'd have to change hundreds of rows, and miss some;
• the file grows much larger than it needs to be.

Storing each fact once, and linking with keys, avoids all three. This design is called normalisation.

The opposite is also useful. For analysis, you often want one wide table with everything side by side: each order line with its customer's region and product's category. Building that is called denormalising, and it's what a join does.

SQL: asking a database questions

SQL (Structured Query Language, said "S-Q-L" or "sequel") is the language used to ask relational databases questions. Analysts use it every day. A query has a small set of parts, always in this order:

You'll learn each one properly in the SQL course. Here, it's enough to read a query and see what it's asking.

03Example

These queries run in your browser against a small freight company's database (Harbourline, used in the SQL course). It has customers, routes, shipments and payments. Press Run on each.

One table, some rows. The customers based in Kano:

SELECT company_name, city, industry
FROM customers
WHERE city = 'Kano';

Read it almost like English: select these columns from the customers table where the city is Kano.

Following a relationship. Each shipment has a customer_id. A join looks it up in customers, so names appear next to shipments:

SELECT s.shipment_id, c.company_name, s.containers
FROM shipments AS s
JOIN customers AS c ON c.customer_id = s.customer_id
LIMIT 10;

s and c are short nicknames (aliases) for the two tables. ON c.customer_id = s.customer_id is the key that links them.

A summary. How many customers are in each city, biggest first:

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
ORDER BY customers DESC;

Lagos comes first with 46 of Harbourline's 120 customers. That's the same question you'd answer with a pivot table, asked of a database.

04Walkthrough

You can follow a relationship by hand, too. Suppose you want to know where the customer on Kolanut order 10050 is based.

1. In orders.csv, find order_id 10050. Its customer_id is 13.
2. In customers.csv, find customer_id 13: Peace Provisions, a kiosk in Kano, region North West, looked after by Sani Garba.
3. While you're in orders.csv, note its product_id: 5. In products.csv, product 5 is Plantain chips 150g (20), a snack.

So order line 10050 was five packs of plantain chips for a kiosk in Kano. Three tables, two keys, one complete picture. That two-step lookup is exactly what a database join does, for every row at once. In spreadsheets you'll do the same with a lookup formula (XLOOKUP, in the Excel course).

Check the keys. A primary key must be unique. In a spreadsheet, compare the number of rows with the number of distinct IDs: customers.csv has 90 rows and 90 different customer_id values, so it's a valid key. If a column has fewer distinct values than rows, it can't be the primary key.

Summary

05Practice

06More practice

Optional drills. They don't count towards the certificate, but they're the fastest way to make this lesson stick. Several use a different dataset from the lesson on purpose: if you can do the same thing on unfamiliar data, you've really learned it.

07Check your understanding