The prescription medicines file of the National Health and Nutrition Examination Survey (NHANES) for 2017 to 2018 has 19,643 rows and 13 columns. Each row is either one medicine reported by one person or, for the 5,209 people who reported none or did not answer, a single row saying so. A person who takes 22 medicines has 22 rows, and each of them repeats that person's answer to "have you taken a prescription medicine in the past 30 days" and the count of medicines, 22. The reasons for use are coded in three columns, of which the second is filled in 1,070 rows and the third in 187. The phrase "Essential (primary) hypertension" is stored 2,483 times.
None of this is wrong. The file is a convenient way to publish the data in one piece. But it is an awkward way to keep data that will be updated, checked and queried by many people, and the reasons are the subject of the relational model of data, proposed by Edgar F. Codd in 1970 and still the basis of almost every database that holds structured records. This article covers that model: what a relation is, how keys tie tables together, which rules a database can enforce, and how a table like the prescription file is split into tables that each store a fact once. The SQL articles that follow query the result, a database of eleven NHANES tables in SQLite, the database engine built into Python.
Why a database
Files are enough for one analyst working on a fixed dataset; Introduction to Data Wrangling works that way. A database adds what files lack when data are shared and changing: many users reading and writing at once without corrupting each other's work, rules that reject data violating the declared structure, transactions that apply a group of changes completely or not at all, recovery after a crash, and control over who may read or change what. Data Engineering places databases among the other systems that store data, such as warehouses and data lakes. This article is about the model of data that most databases share.
Codd's relational model
The problem: data independence
Codd's paper opens with a requirement: "Future users of large data banks must be protected from having to know how the data is organized in the machine (the internal representation)." In the systems of the 1960s, programs depended on the representation in three ways he named: ordering dependence (programs assumed records came back in the order they were stored), indexing dependence (programs referred to indexes by name and failed when an index was removed) and access path dependence. The last was the most serious. When data were stored as trees or networks of records, a program had to follow the paths of the structure, from a part to its projects, say, and a program written for one arrangement failed on another. Codd's example is a data bank of parts, projects and the quantity of each part committed to each project, which can be arranged as five different tree structures; a program that prints the parts used by one project, written for any one of them, "will fail on at least three of the remaining structures" (Codd, 1970).
His answer was to give users a model of the data that has no paths at all: a collection of tables, which he called relations, whose only structure is their values. How the tables are stored, sorted and indexed is the system's business, and it can change without changing what any query means. This separation is called data independence.
Relations, tuples and domains
Codd used "relation" in its mathematical sense. Given sets $S_1, S_2, \ldots, S_n$, a relation $R$ on them is a set of $n$-tuples whose first element comes from $S_1$, second from $S_2$, and so on; that is,
$$R \subseteq S_1 \times S_2 \times \cdots \times S_n,$$
a subset of the Cartesian product. The sets $S_j$ are the domains of the relation, the pools of values a column may take (all possible ages, all drug identifiers), and $n$ is its degree. Codd drew a relation as an array with these properties: each row represents one tuple, "the ordering of rows is immaterial", "all rows are distinct", and each column is labeled with the name of its domain. Because users should not have to remember which column comes first, he proposed that they work with columns identified by name rather than position, with a role name added where two columns draw on the same domain.
The everyday vocabulary maps onto Codd's like this:
| Relational model | SQL | Data analysis |
|---|---|---|
| relation | table | dataset |
| tuple | row | observation, record |
| attribute (named domain) | column | variable, feature |
| domain | data type, constraints | set of possible values |
Where SQL tables differ from relations
SQL, the language that grew out of the model, does not follow it exactly, and the differences matter in practice.
Tables may contain duplicate rows. A relation is a set, so its rows are distinct, and Codd's projection operation (keeping some columns) removes the duplicates it creates. An SQL table without a key is a multiset. Copying the sex column of the 9,254 people into a table of its own gives a table of 9,254 rows, 4,697 of them 'female'; the relation it represents has two tuples. SQL removes duplicates only when asked, with SELECT DISTINCT, which the Special Operators and Clauses article covers.
Rows have no order, but columns do. SQL agrees with Codd about rows: the SQLite documentation states that if a query "does not have an ORDER BY clause, the order in which the rows are returned is undefined." The same query can return the same rows in a different order when the storage changes. Asking for the four examined people with a body mass index (BMI) above 70 returns them in the order of their survey numbers, 94117, 97938, 100082 and 102778; after an index on BMI is created, the identical query returns them in the order 100082, 94117, 102778, 97938, sorted by BMI, because SQLite now reads them through the index. The set of rows is the same, which is all the query asked for. This is data independence at work, and it is why a program must never rely on an order it did not request. Columns, on the other hand, have a fixed order in SQL, which SELECT * exposes.
Values can be missing. The 1970 paper has no notion of a missing value. SQL has NULL, discussed below and, with its three-valued logic, in Simple SQL Queries.
Keys
Primary keys
Codd (1970) defined the key that identifies each row: "Normally, one domain (or combination of domains) of a given relation has values which uniquely identify each element (n-tuple) of that relation. Such a domain (or combination) is called a primary key." A key should also be minimal, with no column that could be dropped while keeping it unique; Codd called such a key nonredundant. A table can have several minimal keys, the candidate keys, and one is chosen as the primary key.
In the NHANES database, every person has a respondent sequence number, seqn, which is the primary key of person. The blood pressure table has up to four readings per person, so its key is the pair (seqn, reading), a composite key. The prescription table shows why the choice of key is a question about the data and not a formality. The natural candidate is the pair of person and drug, on the assumption that a person reports a drug once. The data say otherwise: 60 person and drug pairs occur twice, 34 of them for albuterol, the asthma inhaler. The survey documentation explains such repeats: a participant may have reported two brand names with the same generic name, different forms or dosages of one product (its example is albuterol as an oral tablet and as an inhalation solution), or different lengths or reasons of use (National Center for Health Statistics, 2020). Because no combination of the published columns identifies a row, the table is given a surrogate key, rx_id, a number with no meaning beyond identity. Surrogate keys are common in practice for exactly this reason: natural keys that look unique often are not, and they can change.
Foreign keys
A key also lets one table refer to rows of another. Codd's definition: a column or combination of columns of relation $R$ is a foreign key "if it is not the primary key of R but its elements are values of the primary key of some relation S (the possibility that S and R are identical is not excluded)." The last clause allows a table to refer to itself, as an employee table refers to each employee's manager.
The diagram is the database's schema: the tables, their columns and the links between them. Each link has a cardinality. A person has at most one row in exam (one-to-one, or rather one to zero-or-one, since 550 people were interviewed but not examined), but any number of rows in prescription (one-to-many). A prescription can have up to three reasons, and a condition can be the reason for many prescriptions, so rx_reason links prescriptions and conditions many-to-many. Knowing these cardinalities is the condition for writing correct joins, as Join Queries shows with a join that counts some people dozens of times.
Integrity constraints
A database can enforce rules that a file cannot. The common ones:
- Entity integrity: a primary key is unique and never null, so every row can be identified.
- Referential integrity: every value of a foreign key matches an existing primary key, or is null where that is allowed. No prescription may name a person who does not exist.
- Domain constraints: a column's values have the declared type, may be required (
NOT NULL), and may be restricted by aCHECKcondition.
Codd (1970) framed the general idea as consistency: the stored data should satisfy a declared set of constraint statements, which the system can check on each change or periodically. In SQL, constraints are declared with the table. Two tables of the NHANES database:
CREATE TABLE drug (
drug_id TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL
);
CREATE TABLE prescription (
rx_id INTEGER PRIMARY KEY,
seqn INTEGER NOT NULL REFERENCES person (seqn),
drug_id TEXT REFERENCES drug (drug_id),
days INTEGER
);
drug_id in prescription may be null: 134 reported medicines are recorded only as the codes 55555 (unknown), 77777 (refused) or 99999 (don't know), with no drug identifier, and a foreign key that is null refers to nothing and violates nothing.
With the constraints switched on, the database rejects bad data at the moment it arrives. Inserting a second drug with the identifier d00749 fails with UNIQUE constraint failed: drug.drug_id; a drug without a name fails with NOT NULL constraint failed: drug.name; a prescription for person number 1, who does not exist, fails with FOREIGN KEY constraint failed.
The phrase "with the constraints switched on" is where SQLite differs from most databases, and the differences are worth knowing because SQLite, which ships with Python, is often the first database a data scientist meets. Its documentation lists them as quirks (SQLite, n.d.-a):
- Foreign keys are not enforced by default. SQLite parsed foreign key clauses long before it enforced them, and to avoid breaking existing databases, enforcement must be switched on for each connection with
PRAGMA foreign_keys = ON. With it off, the prescription for the nonexistent person 1 is accepted without complaint; the database is now inconsistent, and only an explicitPRAGMA foreign_key_checkreports the orphan row. - A primary key may contain NULLs unless it is an
INTEGER PRIMARY KEYor is declaredNOT NULL, a behavior the documentation calls "a bug" kept for compatibility. A test table with aTEXT PRIMARY KEYaccepted two rows whose key was null. The tables above sayNOT NULLfor this reason. - Types are advisory. SQLite stores what it is given: setting a person's weight, declared
REAL, to the text'not weighed'succeeds, and the value is stored as text. Since version 3.37.0 a table declaredSTRICTrejects it ("cannot store TEXT value in REAL column"). PostgreSQL, SQL Server and Oracle enforce declared types, and enforce declared foreign keys by default.
Normalization
Codd's normal form
Codd (1970) also proposed a "normal form" for relations. Some domains in the data systems of his time were themselves relations: an employee record held a job history, which held a salary history, and a list of children. He called such domains nonsimple, roughly what programmers called repeating groups, and gave a procedure for eliminating them: copy the primary key of the parent into each subordinate relation, make the copied key part of the subordinate's key, and remove the nonsimple domain from the parent. His employee example ends as four flat relations, employee, job history, salary history and children, each keyed by the employee number together with its own identifying columns. A relation whose domains are all simple can be stored as a plain two-dimensional array, and Codd noted that this form also suits the exchange of data between systems, being "devoid of pointers". "Further operations of a normalizing kind are possible", he wrote, but "are not discussed in this paper"; he defined the second and third normal forms in a later paper (Codd, 1972).
Kent (1983) summarized the first three normal forms in a sentence each, and his phrasing is easier to apply than the formal definitions:
- First normal form: "all occurrences of a record type must contain the same number of fields". There are no repeating groups and no variable-length lists inside a row.
- Second normal form "is violated when a non-key field is a fact about a subset of a key." It matters only when the key has more than one column.
- Third normal form "is violated when a non-key field is a fact about another non-key field."
Kent's summary of the three together is that every non-key field must give a fact about the key, the whole key, and nothing but the key. The design goal behind them is that each fact is stored in exactly one place.
Worked example: the prescription file
The published file, RXQ_RX_J, has these columns for each row: the person (SEQN), whether they took any prescription medicine (RXDUSE), the generic drug name and its identifier (RXDDRUG, RXDDRGID), whether the container was seen (RXQSEEN), the number of days taken (RXDDAYS), up to three reason codes from the ICD-10-CM classification of diseases (RXDRSC1 to RXDRSC3) with their descriptions (RXDRSD1 to RXDRSD3), and the number of medicines the person reported (RXDCOUNT).
First normal form. The three reason columns are a repeating group laid out horizontally: a medicine has from zero to three reasons, and the file reserves three slots for every row, mostly empty. A question such as "how many medicines are taken for hypertension" must look in three places. The repair is Codd's procedure: a table rx_reason with one row per reason, keyed by the prescription and the position of the reason (1, 2 or 3), which holds 15,565 rows. Whether a value is atomic depends on the questions to be asked of it. 1,073 rows name combination products, such as ENALAPRIL; HYDROCHLOROTHIAZIDE. The survey treats each as one product with one identifier, and so does this database; an analysis of individual ingredients would need to split the name into a table of ingredients, and until then a search for one ingredient must use pattern matching, as Special Operators and Clauses does.
Second normal form. Once the rows of the file have an identifier, the medicine within the person, any column that describes the person alone is a fact about part of the key. RXDUSE and RXDCOUNT are such columns: one person with 22 medicines repeats the same two values 22 times, and nothing prevents two of the copies from disagreeing. RXDUSE moves to a table with one row per person (here questionnaire). RXDCOUNT can be dropped altogether, because it is derivable: for every one of the 4,045 people who reported medicines, it equals the number of their rows. Storing a value that can be computed from others is what Codd (1970) called redundancy, and each redundant copy is a chance for inconsistency.
Third normal form. The drug name is a fact about the drug identifier, not about the prescription; the description of a reason is a fact about its code. These are dependencies between non-key columns. They can be checked on the data rather than assumed: each of the 666 drug identifiers has exactly one name, and each of the 241 reason codes exactly one description (the special codes 55555, 77777 and 99999 have none). So drug names move to a drug table keyed by identifier, and descriptions to a condition table keyed by code. The text "Essential (primary) hypertension", stored 2,483 times in the file, is now stored once.
The result is the right-hand part of the schema above: prescription (one row per reported medicine), drug, rx_reason and condition. The four tables hold 106,245 values, and the person-level answers moved to questionnaire another 9,254, where the file held 255,359. The saving in space is the least of the gains. The normalized design removes three kinds of anomaly that the single file invites:
- Update anomalies. Correcting the wording of a diagnosis in the file means changing every row that carries it, 2,483 of them for hypertension; miss one and the data contradict themselves. In
conditionit is one row. - Insertion anomalies. The file cannot record a drug identifier and its name unless someone takes the drug. The
drugtable can. - Deletion anomalies. Deleting the only person who takes a rare drug deletes the only record of its name. With a separate table, it does not.
The design has a cost. A question that spans the tables, such as which conditions are most often treated with a given drug, now needs joins, the subject of Join Queries. That is the usual division of labor: data are stored normalized, so that each fact has one home and updates are safe, and are joined into a wide table, one row per person with many columns, when a model or a plot needs them. The table of 5,533 examined adults used throughout Performing Exploratory Data Analysis is such a join of nine NHANES files. The two views are close relatives: Wickham (2014) describes tidy data, in which each variable is a column, each observation a row and each type of observational unit a table, as "Codd's 3rd normal form ... but with the constraints framed in statistical language" (Structuring Data).
Missing values and NULL
SQL marks a missing value with NULL, which is not zero, not an empty string and not equal to anything, including another NULL. One marker has to stand for situations that are different in kind, and the NHANES tables contain several of them:
- Not applicable. The education question was asked only of adults aged 20 and over, so
educationis NULL for all 3,685 people under 20. Nothing is unknown; the question did not apply. - Unknown. The ratio of family income to the poverty line is NULL for 1,231 of the 9,254 people. A value exists in principle; the survey does not have it.
- Refused or not known by the respondent. These are not NULL at all in the published data but codes: 7 for refused and 9 for don't know in
education(13 adults), 77777 and 99999 in the drug names and the days of use. A query that averages a column holding the code 99999 as if it were a number of days gets an absurd answer, which Cleaning Data discusses for the same survey. - Known only to a limit. Age is top-coded at 80, so the 427 people recorded as 80 are 80 or older, and the income ratio is capped at 5 (1,208 people). These values are present but not exact.
The database stores all of these as published, so the SQL articles can show how each is handled. Collapsing them into one NULL loses the distinction between "did not apply", "refused" and "unknown", which can matter: people who refuse a question are rarely a random subset. NULL's behavior in comparisons and logic, the source of most surprises in SQL, is covered in Simple SQL Queries, and its behavior in aggregates in Aggregate Functions. One small example of the second: counting the refusal codes separately for the people under 20 and those 20 and over gives 13 for the older group and NULL, not 0, for the younger, because a sum over values that are all null is null.
SQL
Codd (1970) proposed that users query relations through a "universal data sublanguage based on an applied predicate calculus": a language that describes the set of data wanted rather than the steps for finding it. Several such languages were built at IBM in the 1970s; one of them, SEQUEL, a "structured English query language" (Chamberlin and Boyce, 1974), became SQL. SQL was first standardized in 1986, and the standard is now ISO/IEC 9075, but no product implements the whole standard and every product adds features of its own, so SQL comes in dialects: PostgreSQL, MySQL, SQL Server, Oracle and SQLite differ in details, from the function that computes a logarithm to whether FULL JOIN exists. The articles in this topic use SQLite, because it ships with Python and needs no server, and note the differences from the other main dialects where they matter. As the SQLite documentation puts it, SQLite "is not intended as a replacement or competitor" to client-server databases such as PostgreSQL; it is an embedded engine, a library that reads and writes a single file (SQLite, n.d.-a).
SQL statements fall into a few groups:
- Data definition:
CREATE TABLE,ALTER TABLE,DROP TABLE,CREATE INDEX, as above. - Data manipulation:
INSERT,UPDATE,DELETE. - Queries:
SELECT, which reads data and changes nothing. Most of a data scientist's SQL is queries, and so are the next five articles. - Transaction control:
BEGIN,COMMIT,ROLLBACK, covered with Python in Queries in Python.
A few lexical rules apply in all dialects. Keywords are not case-sensitive (SELECT and select are the same); writing them in capitals is a convention. Line breaks and indentation are ignored. String literals take single quotes, 'female'; in standard SQL double quotes delimit identifiers such as column names, a rule SQLite relaxes with consequences shown in the next article. Two hyphens begin a comment that runs to the end of the line. A semicolon separates statements.
The most important property of SQL is that it is declarative. A query states what result is wanted, and the database decides how to compute it: which table to read first, whether to use an index, how to perform a join. The BMI query above returned the same rows with and without an index because it described a set, not a procedure. This is the payoff of Codd's data independence, and it is also why the SQLite documentation, describing the steps by which a SELECT is evaluated, cautions that the description "is purely illustrative" and that "in practice neither SQLite nor any other SQL engine is required to follow this or any other specific process" (SQLite, n.d.-b). The logical order in which the clauses of a query take effect is set out in Simple SQL Queries and Aggregate Functions.
In Python
The database is built from the public NHANES files by tools/experiments/data-science/sqldata.py in the site's repository. The pattern is the one Queries in Python explains in detail: connect, create the tables, insert the rows with placeholders, commit.
import sqlite3
con = sqlite3.connect("nhanes.db")
con.execute("PRAGMA foreign_keys = ON") # SQLite does not enforce them by default
con.executescript(SCHEMA) # the CREATE TABLE statements
con.executemany("INSERT INTO drug VALUES (?, ?)", drug_rows)
con.commit()
con.execute("PRAGMA foreign_key_check").fetchall() # [] when every reference resolves
The numbers in this article are computed by tools/experiments/data-science/relational-databases.py, which builds the database, checks the dependencies on the raw file and exercises the constraints on a scratch copy.
References
- Chamberlin, D. D., and Boyce, R. F. (1974). SEQUEL: A structured English query language. In Proceedings of the 1974 ACM SIGFIDET Workshop on Data Description, Access and Control (Ann Arbor, May 1974), 249–264. https://doi.org/10.1145/800296.811515 (The DOI's registered metadata dates the proceedings 1976; the workshop and paper are from 1974.) record
- Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377–387. https://doi.org/10.1145/362384.362685 record · 3 pages
- Codd, E. F. (1972). Further normalization of the data base relational model. In R. Rustin (Ed.), Data Base Systems (Courant Computer Science Symposia 6), 33–64. Prentice-Hall. record
- Kent, W. (1983). A simple guide to five normal forms in relational database theory. Communications of the ACM, 26(2), 120–125. https://doi.org/10.1145/358024.358054 record
- National Center for Health Statistics (2020). National Health and Nutrition Examination Survey 2017–2018 Data Documentation, Codebook, and Frequencies: Prescription Medications (RXQ_RX_J). Centers for Disease Control and Prevention. https://wwwn.cdc.gov/Nchs/Data/Nhanes/Public/2017/DataFiles/RXQ_RX_J.htm record · 8 pages
- SQLite (n.d.-a). Quirks, caveats, and gotchas in SQLite. https://sqlite.org/quirks.html record · 3 pages
- SQLite (n.d.-b). The SELECT statement. https://sqlite.org/lang_select.html record · 5 pages
- Wickham, H. (2014). Tidy data. Journal of Statistical Software, 59(10), 1–23. https://doi.org/10.18637/jss.v059.i10 record · 5 pages