Collected data still has to be kept somewhere, prepared, and shown to someone, and those three jobs organize this section. It starts with storage in a relational database, where the design of the tables determines what can be asked later. It then turns to the work done on data, from simple operations like sorting to cleaning and transforming it, and then to charts, which communicate results and can also distort them. Spreadsheets and databases can both do much of this work, so the section closes by asking when each one fits.

Storing data: tables, keys, and relationships#

A relational database stores data in tables. Each row is a record, representing one entity, such as one form submission or one copy of a book, and each column is a field, holding one attribute of every record, such as the email column of a sign-up table.¹ Two copies of the same novel are two records, even when most of their values match. Deciding what counts as one entity is the heart of table design, and repeating columns are the usual sign of a poor decision. If a Reservations table has guest1, guest2, and guest3, every additional booking calls for a new column, which signals that the guests should be separate records in a related table. A gradebook moved into a database works the same way: with one record per student-assignment grade, a new assignment adds records and leaves the table's structure alone.

Once data is spread across tables, the tables need a way to refer to one another, and keys provide it. A primary key uniquely identifies each record in its table, so every record needs one and no two records can share one. Because its values must be unique, last names make poor keys: two students who share a name couldn't both be stored. A foreign key is a field that refers to a primary key in another table, creating a relationship between them. The same field can play both roles in different places, as student_id is the primary key of Students and a foreign key in Absences, where one student can appear many times. Referential integrity means that foreign keys match existing records, so a book_id in a Loans table that matches nothing in Books violates it.

Where the foreign key goes depends on how the two kinds of entity are related, and there are three cases. In a one-to-one relationship, such as a person and a passport, each record on one side matches at most one on the other. In a one-to-many relationship, such as an author and books or a customer and orders, the foreign key goes on the "many" side: each book names its one author, because an author can't hold a list of book IDs in a single field. In a many-to-many relationship, such as students and classes, neither side can hold the key, since each record would need many values. The usual design adds a third junction table holding pairs of foreign keys, one row per pairing. An Appointments table holding patient_id and doctor_id links patients and doctors this way. Storing several values in one text field, such as "Math, Art, Music", hides the relationship and makes it hard to reliably find all students in a given class.

With the structure in place, Structured Query Language (SQL) is how you retrieve and change the data. SQL is declarative, meaning that you state what you want and the database works out how to find it, an idea 0002.g develops. A simple query chooses a column, names a table, and filters rows:

SELECT title FROM Books WHERE price < 10

This returns the title of each book whose price is below 10. The clause order is fixed: SELECT, FROM, WHERE, and then ORDER BY with DESC for highest first or ASC for lowest first, as in SELECT city FROM Trips WHERE miles > 100 ORDER BY miles DESC. WHERE matters just as much when changing data. UPDATE Scores SET points = 0 changes every row in the table, because without a WHERE clause nothing limits it.

Working with data: finding, cleaning, and transforming#

The same basic operations appear in a query and in a spreadsheet menu, and three of them are easy to confuse. Search finds particular values, such as whether one ISBN appears in a catalog. Sort reorders all rows and hides none. It must move whole rows together, since each row is a record: sort only a column of names, and the scores beside them no longer match the right people. Filter displays only the rows meeting a condition without removing the others. That makes it a safe way to view just the grade 10 students, and it also explains a common surprise, because some formulas still include the hidden rows, so an average of "just the Math rows" may be an average of everything.

These operations assume that the data is in good condition, and real data seldom is, as the quality problems in 0002.b suggest. Cleaning means standardizing formats, handling missing values, removing duplicates, and correcting errors, and it comes before analysis because errors affect all later results.² Each task involves a judgment. "Yes," "yes," and "Y" need to become one value, and someone must decide what blanks mean. Dates written as "03/04/2023" and "2023-04-03" need one format, or purchases may land in the wrong months. Codes such as -99 for "absent" distort averages, in that case pulling them too low, because the code is treated as a real score. Duplicates need a definition, ideally through a unique identifier, since repeat purchases by the same customer look like duplicates and aren't errors.

Clean data may still not be in a form that answers your question, so the next step is transforming it, which means changing its form to answer a question.³ You might calculate a new column, such as price * quantity for each row. You might aggregate by group, turning a list of sales into total sales per store or daily attendance into monthly rates. Or you might join two datasets on a shared key, so that a table of scores and a table of grade levels, both containing student_id, can produce average scores by grade level when you join and then aggregate. Joins depend on matching values, so clean identifiers first. If one table says "NYC DOE" and the other says "New York City DOE," the rows won't match, and some can disappear from the result.

Showing data: choosing and reading charts#

After the analysis comes communication, and a chart's form makes a claim about the data, so it should match what you are trying to show. Line charts show change over time. Bar charts compare categories. Scatter plots show relationships between two numeric variables, such as hours of study and test scores.⁴ Histograms show distributions, grouping a numeric variable's values into bins and showing how many fall in each. Pie charts show parts of a whole. Because a line implies an order, connecting average scores for Math, Science, Art, and History suggests a trend among categories that have no sequence, and a bar chart would be the honest choice.

A chart can also mislead even when every data point is accurate, through three main devices. A truncated axis starts above zero and exaggerates differences: sales of 980 and 1,000 on an axis starting at 970 make one bar look three times as tall as the other. For this reason, bar charts should start at zero. Inconsistent scales make slopes impossible to compare, as when the years 2000, 2005, 2010, 2011, 2012, and 2013 are evenly spaced along an axis, so that growth seems to speed up at the end. Selective data shows only part of the record, such as a company's three best years out of twenty. The fix is to change the design and the data shown, since changes such as brighter colors don't address the distortion, and a three-dimensional effect adds more of it.

Choosing a tool#

A spreadsheet can do much of what this section has described, using formulas, sorting, filtering, charts, and pivot tables, which summarize data by group. Its formulas also adjust as you copy them: copying =A2*B2 down one row gives =A3*B3, because references are relative. All this suits small datasets, such as a few hundred expenses that one person updates at a time. Databases better handle large, shared, related data, such as millions of licensing records that many staff members update at once, and they reduce duplication. When club rosters are kept in separate sheets that each repeat a student's address, a correction made on one sheet leaves the others wrong. With the address in one table that the others reference, the change needs to be made only once.

That last example shows a general principle: keep something in one place, and refer to it from everywhere it's needed. The same principle applies to the code that processes data, which is where the next section goes.

• relational database: a system that stores data in related tables
• record: a row, representing one entity
• field: a column, holding one attribute
• primary key: a field that uniquely identifies each record
• foreign key: a field that refers to a primary key in another table
• referential integrity: the requirement that foreign keys match existing records
• one-to-one, one-to-many, many-to-many: the three kinds of relationship between tables
• junction table: a third table of paired foreign keys that represents a many-to-many relationship
• SQL: a declarative language for retrieving and changing data in a database
• search, sort, filter: finding particular values, reordering all rows, and displaying only the rows that meet a condition
• cleaning: standardizing formats, handling missing values, removing duplicates, and correcting errors
• transforming: changing data's form to answer a question, by calculating, aggregating, or joining
• join: combining two datasets on a shared key
• truncated axis: an axis that starts above zero and exaggerates differences
• pivot table: a spreadsheet tool that summarizes data by group

Practice: practice questions for 0002.c

1. MWC2, Riddle Server: views, models, fields, subclasses, and an API specification. ↩

2. MWC2, Classification: Features: train/test splits, evaluation, feature engineering, and data cleaning. ↩

3. MWC1, Pipes: programs as transformations of data, and combining commands. ↩

4. MWC1, Scatter: top-down and bottom-up problem solving, conditionals, return values, minimums and maximums, and plots. ↩