A hammer is good at nails
A hammer makes nails easy and screws hard. Software is the same. Each kind makes some jobs easy and others slow or risky.
A data analyst picks the tool that makes the job easy. First you need to know what each tool is good at.
Key words
| Word | What it means |
|---|---|
| Function | a job the software can do, such as sorting or adding |
| Affordance | what a tool makes easy to do, because of how it is built |
| Database software | software that stores linked tables and answers questions about them |
| Spreadsheet software | software that shows data in cells with formulas that recalculate |
| Query | one question asked of a database, written in SQL |
| SQL | the language used to ask a database for data |
| Database | an organised store of tables that you can ask questions |
| Integrity | data that stays correct and in agreement with itself |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can say what database software and spreadsheet software are good at, and what each makes hard.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
What database software is good at
| Function | What it means for the analyst |
|---|---|
| Linked tables | Each fact is stored once and tables are joined by keys |
| Queries | Ask any question with SQL and get the rows back |
| Rules on fields | Types, required fields and limits refuse bad data |
| Many users at once | Staff can read and write together, with permissions |
| Large amounts of data | Millions of rows stay fast |
| Integrity | A sale cannot point at a product that is not there |
The cells students fill in are left blank in this preview.
A database refuses bad data
CREATE TABLE pupils (
name TEXT NOT NULL,
year INTEGER CHECK (year BETWEEN 7 AND 13)
);
INSERT INTO pupils VALUES ('Bo', 99);
-- Error: CHECK constraint failed: year BETWEEN 7 AND 13
-- The row is not saved. Run in the sqlite3 client; wording can differ.
Try it: ask a database a question
-- Which products sold, and how many times? A join and a group in one query.
SELECT products.name, COUNT(*) AS sales
FROM sales
INNER JOIN products ON sales.product_id = products.product_id
GROUP BY products.name
ORDER BY sales DESC, products.name;
What spreadsheet software is good at
| Function | What it means for the analyst |
|---|---|
| Cells and formulas | Change one number and every answer updates |
| What-if work | Try a different price and see the total change |
| Quick layout | Colour, columns and notes are free to arrange |
| Charts and pivot tables | A picture or a summary in a few clicks |
| Sort and filter | Look at one list in many ways |
| Easy for one person | No set-up: open it and start |
The cells students fill in are left blank in this preview.
No spreadsheet here
Important
This platform has no spreadsheet program, so you cannot try these functions here. On site you use a real one. The database ideas can be run: the block above asks one question of a table.