Glossary

Key Points

Introduction	Organizing your data tables according to tidy data principles will make them easier for you and others to use for analysis.
Formatting Spreadsheets as Data Tables	A relational database lets you compute on multiple tables at once. Never modify your raw data. Always make a copy before making any changes. Keep track of all of the steps you take to clean your data. Organize your data according to tidy data principles. Record metadata in a separate plain text file.
Formatting Problems	Avoid using multiple tables within one spreadsheet. Avoid spreading data across multiple tabs (but do use a new tab to record data cleaning or manipulations). Record zeros as zeros. Use an appropriate null value to record missing data. Don’t use formatting to convey information or to make your spreadsheet look pretty. Place comments in a separate column. Record units in column headers. Include only one piece of information in a cell. Avoid spaces, numbers and special characters in column headers. Avoid special characters in your data.
Dates as Data	Use extreme caution when working with date data. Splitting dates into their component values can make them easier to handle. Don’t use Excel.
Using DB Browser for SQLite	The DB Browser for SQLite application allows you to connect to an existing database or create a new database When connected to a database you can create new tables When connected to a database you can write and run SQL queries and view the results You can save the results of a query to a file
Selecting Data	A relational database stores information in tables, each of which has a fixed set of columns and a variable number of records. A database manager is a program that manipulates information stored in a database. We write queries in a specialized language called SQL to extract information from databases. Use SELECT… FROM… to get values from a database table. SQL is case-insensitive (but data is case-sensitive).
Sorting and Removing Duplicates	The records in a database table are not intrinsically ordered: if we want to display them in some order, we must specify that explicitly with ORDER BY. The values in a database are not guaranteed to be unique: if we want to eliminate duplicates, we must specify that explicitly as well using DISTINCT.
Filtering	Use WHERE to specify conditions that records must meet in order to be included in a query’s results. Use AND, OR, and NOT to combine tests. Filtering is done on whole records, so conditions can use fields that are not actually displayed. Write queries incrementally.
Combining Data	Use JOIN to combine data from two tables. Use table.field notation to refer to fields when doing joins. Every fact should be represented in a database exactly once. A join produces all combinations of records from one table with records from another. A primary key is a field (or set of fields) whose values uniquely identify the records in a table. A foreign key is a field (or set of fields) in one table whose values are a primary key in another table. We can eliminate meaningless combinations of records by matching primary keys and foreign keys between tables. The most common join condition is matching keys.
Calculating New Values	Queries can do the usual arithmetic operations on values. Use UNION to combine the results of two or more queries.
Missing Data	Databases use a special value called NULL to represent missing information. Almost all operations on NULL produce NULL. Queries can test for NULLs using IS NULL and IS NOT NULL.
Aggregation	Use aggregation functions to combine multiple values. Aggregation functions ignore `null` values. Aggregation happens after filtering. Use GROUP BY to combine subsets separately. If no aggregation function is specified for a field, the query may return an arbitrary value for that field.
Programming with Databases - Python	General-purpose languages have libraries for accessing databases. To connect to a database, a program must use a library specific to that database manager. These libraries use a connection-and-cursor model. Programs can read query results in batches or all at once. Queries should be written using parameter substitution, not string formatting.
Creating and Modifying Data	Use CREATE and DROP to create and delete tables. Use INSERT to add data. Use UPDATE to modify existing data. Use DELETE to remove data. It is simpler and safer to modify data when every record has a unique primary key. Do not create dangling references by deleting records that other records refer to.

aggregation function: A function that combines multiple values to produce a single new value (e.g. sum, mean, median).
atomic: Describes a value not divisible into parts that one might want to work with separately. For example, if one wanted to work with first and last names separately, the values “Ada” and “Lovelace” would be atomic, but the value “Ada Lovelace” would not.
cascading delete: An SQL constraint requiring that if a given record is deleted, all records referencing it (via foreign key) in other tables must also be deleted.
case insensitive: Treating text as if upper and lower case characters were the same. See also: case sensitive.
case sensitive: Treating upper and lower case characters as different. See also: case insensitive.
comma-separated values (CSV): A common textual representation for tables in which the values in each row are separated by commas.
cross product: A pairing of all elements of one set with all elements of another.
cursor: A pointer into a database that keeps track of outstanding operations.
database manager: A program that manages a database, such as SQLite.
fields: A set of data values of a particular type, one for each record in a table.
filter: To select only the records that meet certain conditions.
foreign key: One or more values in a database table that identify records in another table.
prepared statement: A template for an SQL query in which some values can be filled in.
primary key: One or more fields in a database table whose values are guaranteed to be unique for each record, i.e., whose values uniquely identify the entry.
query: A textual description of a database operation. Queries are expressed in a special-purpose language called SQL, and despite the name “query”, they may modify or delete data as well as interrogate it.
record: A set of related values making up a single entry in a database table, typically shown as a row. See also: fields.
referential integrity: The internal consistency of values in a database. If an entry in one table contains a foreign key, but the corresponding records don’t exist, referential integrity has been violated.
relational database: A collection of data organized into tables.
sentinel value: A value in a collection that has a special meaning, such as 999 to mean “age unknown”.
SQL: A special-purpose language for describing operations on relational databases.
SQL injection attack: An attack on a program in which the user’s input contains malicious SQL statements. If this text is copied directly into an SQL statement, it will be executed in the database.
table: A set of data in a relational database organized into a set of records, each having the same named fields.
wildcard: A character used in pattern matching. In SQL’s like operator, the wildcard “%” matches zero or more characters, so that %able% matches “fixable” and “tablets”.