Chapter 1: Introduction

SQL Explained by an Idiot — data, information, knowledge, wisdom

What is data?

Data is very simple. It is a collection of facts at any given point in time. This means get those math equations out of your head cause trust me this is going to be very helpful later. In the slides, there is a flow diagram that goes like this:

DATA -> INFORMATION -> KNOWLEDGE -> WISDOM

The purpose of this is for you to link back to why data isn't the end of the road. It's the beginning. This is going to be the link in assignments and exams when they ask questions like "What can this tell us about Coffee Co." or "Provide your insights on..." etc.

Data is a single raw fact: ID: ABC123, Name: Eric, DOB: 2000-03-20. On its own it tells us nothing. Information is the data with context attached — for example, what does ID: ABC123, Name: Eric, DOB: 2000-03-20 mean? Staff ID? Student? Prisoner? We know it identifies someone, but what information do we get out of it? Nothing if there is no context.

Let's say we've got some context: staff_id: ABC123, name: Eric, dob: 2000-03-20, branch_id: Wellington, position: Assistant.

Now with context, we can derive information from it, and in this case, the information is that there is an assistant staff member that works in the Wellington branch named Eric that was born on 20th March 2000.

Knowledge is the pattern we can act on. Given the following rows of data:

The pattern here: almost all staff are in Wellington, and the assistants are the youngest by a couple of decades. That's knowledge. Wisdom is the judgement call made off the back of it — e.g. Auckland is one resignation away from having no staff, so hire there first.

Also it might be one of those "I don't know what it is and I'm too afraid to ask" type of questions, but columns are the vertical groups — staff_id is a column, name is a column. Each column runs top-to-bottom, and the columns are arranged left-to-right across the table. A row runs horizontally and is a single record — one staff member per row, in this case.

Types of Data — not to be confused with data types (will cover later)

In the context/sense of statistics, you've probably heard of qualitative/categorical vs quantitative/numerical. Quantitative then further splits into discrete (one can of Coke) and continuous (like 500ml).

Then, there is structured vs unstructured data.

Structured data fits a predefined format and attributes. A birth certificate is different per individual, but you can define what needs to be on it — date of birth, full legal name, location born/hospital, parents etc.

Unstructured data has no shape. Think of a signature, or a voicemail, or a quick note with no structure. The trade-off is that structured data is orderly and fast to draw insights from, but unstructured is messy but richer.

Some data is both — a photo of a birth certificate can be unstructured cause it's a photo, but it might carry structured metadata (timestamp, taken with what camera, how large the file is etc.).

Note: A DBMS is a Database Management System, which is software that enables the "database approach." Think of it like the difference between a paper accounting book and Xero. The DBMS does five jobs: defines the structure of the data, stores it, enforces constraints (refuses nonsense entries), answers queries, and manages the data (security, access, concurrency).

Relational Databases

A relational database stores data as tables, and the formal term for a table is a relation. Here are the rules of the game:

1) All columns must be named 2) Every row must be unique 3) Order of rows and columns doesn't matter.

NoSQL AKA Non-Relational databases

The classic kind stores documents instead of tables. There is no fixed schema/structure, nothing to force every record to be universal or look alike. (Fine print: document databases - think MongoDB - are just one family of NoSQL. Key-value, graph, and wide-column stores exist too, but you can safely ignore them for now.) Related point: Excel is not a relational database. When you send something like file.csv or file.json, even file.xlsx, from a high-level perspective they are documents (in the plain everyday sense: files) not relations. Sure, they might contain tables inside, but the file itself is not a relational database/table.

Serialisation in the slides means the format the data is written to a file in — CSV, JSON, YAML, Parquet, ORC etc. It's how the facts get encoded into bytes, not the file extension. The extension is just the label on the envelope; the serialisation format is the language the letter inside is written in. (Rename a .json file to .txt — the data inside hasn't changed at all.)

Other tech words that sound techy but they're simple.

• Storage is where the bytes (the ones and zeroes) actually physically live — local disk, or cloud stores, data centres.
• Compute is the engine/computer that reads and processes them.

Traditional relational databases bundle both of them together. Modern big-data stacks often separate them. Imagine petabytes of data in some warehouse in the middle of nowhere — the compute power required to go through it without taking years must be really high, and keeping storage and compute separate means you can scale each one up (and pay for each) independently.