# 2. The Relational Model

To program competently in SQL, you must be familiar with the standard data model for organizing and managing data, called the relational model (Figure 2.1).

Figure 2.1Relational databases are based on the data model that Edgar F. Codd defined in A Relational Model of Data for Large Shared Data Banks (Communications of the ACM, Vol. 13, No. 6, June 1970, pp. 377–387).

The foundation of the relational model, set theory, makes you think in terms of sets of data rather than individual items or rows of data. The model describes how to perform common algebraic operations (such as unions and intersections) on database tables in much the same way that they’re performed on mathematical sets.

The Venn diagram in Figure 2.2 expresses the results of operations on sets. The rectangle (U) represents the universe, and the circles (A and B) inside represent sets of objects. The relative position and overlap of the circles indicate relationships between sets. In the relational model, the circles are tables, and the rectangle is all the information in a database.

Tables are analogues of sets: they’re collections of distinct elements having common properties. A mathematical set would contain positive integers, for example, whereas a database table would contain information about, say, students.

Figure 2.2A Venn diagram expresses the results of operations on sets.

## Tables, Columns, and Rows

If you’re familiar with databases already, then you’ve heard alternative terms for similar concepts. Table 2.1 shows how these terms are related. E. F. Codd’s relational-model terms are in the first column; SQL-standard and DBMS-documentation terms are in the second column; and the third-column terms are holdovers from traditional (nonrelational) file processing. SQL terms are used in this book (although in formal texts the SQL and Model terms never are used interchangeably).

Table 2.1Similar Concepts
Model SQL Files
Relation Table File
Attribute Column Field
Tuple Row Record

### Tables

From a user’s point of view, a database is a collection of one or more tables (and nothing but tables). A table:

• Is the database structure that holds data.
• Contains data about a specific entity type. An entity type is a class of distinguishable real-world objects, events, or concepts with common properties—patients, movies, genes, weather conditions, invoices, projects, or appointments, for example. (Patients and appointments are different entities, so you’d store information about them in different tables.)
• Is a two-dimensional grid characterized by rows and columns (Figure 2.3 and Figure 2.4).

Figure 2.3This grid is an abstract representation of a table—the fundamental storage unit in a database.

Figure 2.4This grid represents an actual (not abstract) table, shown as it usually appears in database software and books. This table has 3 columns, 4 rows, and 3 × 4 = 12 values. The top “row” is not a row but a header that displays column names.

``` au_id au_fname      au_lname ----- ------------- ------------- A01   Sarah         Buchman A02   Wendy         Heydemark A03   Hallie        Hull A04   Klee          Hull ```

• Holds a data item called a value at each row–column intersection (refer to Figure 2.3 and Figure 2.4).
• Has at least one column and zero or more rows. A table with no rows is an empty table.
• Has a unique name within a database (or, strictly speaking, within a schema).

### Columns

Columns in a given table have these characteristics:

• Each column represents a specific attribute (or property) of the table’s entity type. In a table employees, a column named hire_date might show when an employee was hired, for example.
• Each column has a domain that restricts the set of values allowed in that column. A domain is a set of constraints that includes restrictions on a value’s data type, length, format, range, uniqueness, specific values, and nullability (whether the value can be null or not). You can’t insert the string value 'john' into the column hire_date, for example, if hire_date requires a valid date value. Furthermore, you can’t insert just any date if hire_date’s range is further constrained to fall between the date that the company started and today’s date. You can define a domain by using data types (Chapter 3) and constraints (Chapter 11).
• Entries in columns are single-valued (atomic); see “Normalization” later in this chapter.
• The order of columns (left to right) is unimportant (Figure 2.5).

Figure 2.5Rows and columns are said to be unordered, meaning that their order in a table is irrelevant for informational purposes. Interchanging columns or rows does not change the meaning of the table; this table conveys the same information as the table in Figure 2.4.

``` au_lname      au_id au_fname ------------- ----- ------------- Hull          A04   Klee Buchman       A01   Sarah Hull          A03   Hallie Heydemark     A02   Wendy ```

• Each column has a name that identifies it uniquely within a table. (You can reuse the same column name in other tables.)

### Rows

Rows in a given table have these characteristics:

• Each row describes a fact about an entity, which is a unique instance of an entity type—a particular student or appointment, for example.
• Each row contains a value or null for each of the table’s columns.
• The order of rows (top to bottom) is unimportant (refer to Figure 2.5).
• No two rows in a table can be identical.
• Each row in a table is identified uniquely by its primary key; see “Primary Keys” later in this chapter.

### Tips for Tables, Columns, and Rows

• Use the SELECT statement to retrieve columns and rows; see Chapters 4 through 9. Use INSERT, UPDATE, and DELETE to add, edit, and delete rows; see Chapter 10. Use CREATE TABLE, ALTER TABLE, and DROP TABLE to add, edit, and delete tables and columns; see Chapter 11.
• Tables have the attractive property of closure, which ensures that any operation performed on a table yields another table (Figure 2.6).

Figure 2.6Closure guarantees that you’ll get another table as a result no matter how you split or merge tables. This property lets you chain any number of table operations or nest them to any depth. Unary (or monadic) table operations operate on one table to produce a result table. Binary (or dyadic) table operations operate on two tables to produce a result table.

• A DBMS uses two types of tables: user tables and system tables. User tables store user-defined data. System tables contain metadata—data about the database—such as structural information, physical details, performance statistics, and security settings. System tables collectively are called the system catalog; the DBMS creates and manages these tables silently and continually. This scheme conforms with the relational model’s rule that all data be stored in tables (Figure 2.7).

Figure 2.7DBMSs store system information in special tables called system tables. Here are some of the system tables that Microsoft SQL Server creates and maintains. You access system tables in the same way that you access user-defined tables, but don’t alter them unless you know what you’re doing.

• In practice, the number of rows in a table changes frequently, but the number of columns changes rarely. Database complexity makes adding or dropping columns difficult; column changes can affect keys, referential integrity, privileges, and so on. Inserting or deleting rows doesn’t affect these things.
• Database designers divide values into columns based on the users’ needs. Telephone numbers, for example, might reside in the single column tel_no or be split into the columns country_code, area_code, prefix, and line_number, depending on what users want to query, analyze, and report.
• The resemblance of spreadsheets to tables is superficial. Unlike a spreadsheet, a table doesn’t depend on row and column order, doesn’t perform calculations, doesn’t allow free-form data entry, strictly checks each value’s validity, and is related easily to other tables.
• The SQL standard defines a hierarchy of relational-database structures. A catalog contains one or more schemas (sets of objects and data owned by a given user). A schema contains one or more objects, such as base tables, views, functions, and procedures.
• DBMSs sometimes use other terms for the same concepts. An instance (analogous to a catalog) contains one or more databases. A database contains one or more schemas. A schema contains tables, views, privileges, stored procedures, and so on. To refer to an object unambiguously, each item at each level in the hierarchy needs a unique name (identifier). Table 2.2 shows how to address objects. See also “Identifiers” in Chapter 3.

Table 2.2Object References
Standard SQL catalog.schema.object
Access database.object
SQL Server server.database.owner.object
Oracle schema.object
Db2 schema.object
MySQL database.object
PostgreSQL database.schema.object

## Primary Keys

Every value in a database must be accessible. Values are stored at row–column intersections in tables, so a value’s location must refer to a specific table, column, and row. You can identify a table or column by its unique name. Rows are unnamed, however, and need a different identification mechanism called a primary key. A primary key is:

• Required.Every table has exactly one primary key. Remember that the relational model sees a table as an unordered set of rows. Because there’s no concept of a “next” or “previous” row, you can’t identify rows by position; without a primary key, some data would be inaccessible.
• Unique.Because a primary key identifies a single row in a table, no two rows in a table can have the same primary-key value.
• Simple or composite.A primary key has one or more columns in a table; a one-column key is called a simple key, and a multiple-column key is called a composite key.
• Not null.A primary-key value can’t be empty. For composite keys, no column’s value can be empty; see “Nulls” in Chapter 3.
• Stable.Once created, a primary-key value seldom if ever changes. If an entity is deleted, then its primary-key value isn’t reused for a new entity.
• Minimal.A primary key includes only the column(s) necessary for uniqueness.

A database designer designates each table’s primary key. This process is crucial because the consequence of a poor key choice is the inability to add data (rows) to a table. I’ll review the essentials here, but read a database-design book if you want to learn more about this topic.

Suppose that you need to choose a primary key for the table in Figure 2.8. The columns au_fname and au_lname separately won’t work because each one violates the uniqueness requirement. Combining au_fname and au_lname into a composite key won’t work because two authors might share a name. Names generally make poor keys because they’re unstable (people divorce, companies merge, spellings change). The correct choice is au_id, which I created to identify authors uniquely. Database designers create unique identifiers when natural or obvious ones (such as names) won’t work.

Figure 2.8The column au_id is the primary key in this table.

``` au_id au_fname      au_lname ----- ------------- ------------- A01   Sarah         Buchman A02   Wendy         Heydemark A03   Hallie        Hull A04   Klee          Hull ```

After a primary key is defined, your DBMS will enforce the integrity of table data. You can’t insert the following row because the au_id value A02 already exists in the table:

`A02   Christian    Kells`

Nor can you insert this row because au_id can’t be null:

`NULL  Christian    Kells`

This row is legal:

`A05   Christian    Kells`

### Tips for Primary Keys

• See also “Specifying a Primary Key with PRIMARY KEY” in Chapter 11.
• In practice, the primary key often is placed in a table’s initial (leftmost) column(s). When a column name contains id, key, code, or num, it’s a clue that the column might be a primary key or part of one (or a foreign key, described in the next section).
• Database designers often forgo common unique identifiers such as Social Security numbers for U.S. citizens. Instead, they use artificial keys that encode internal information that is meaningful inside the database users’ organization. An employee ID, for example, might embed the year that the person was hired. Other reasons, such as privacy and security concerns, also spur the use of artificial keys.
• Database designers might have a choice of several unique candidate keys in a table, one of which is designated the primary key. After designation, the remaining candidate keys become alternate keys. Candidate keys often have non-nullable, unique constraints; see “Forcing Unique Values with UNIQUE” in Chapter 11.
• You could use au_id and, say, au_lname as a composite key, but that combination violates the minimality criterion. For an example of a composite primary key, see the table title_authors in “The Sample Database” later in this chapter.
• DBMSs provide data types and attributes that provide unique identification values automatically for each row (such as an integer that autoincrements when a new row is inserted). See “Unique Identifiers” in Chapter 3.

Learning Database Design

To learn how to design production databases, read an academic text for a grounding in relational algebra, entity–relationship (E–R) modeling, Codd’s relational model, system architecture, nulls, integrity, and other crucial concepts. I like Chris Date’s An Introduction to Database Systems, but alternatives abound—a cheaper (and chattier) option is Date’s Database in Depth. A modern introduction to set theory and logic is Applied Mathematics for Database Professionals by Lex de Haan and Toon Koppelaars. Classical introductions include Robert Stoll’s Set Theory and Logic and the gentler Logic by Wilfrid Hodges. You also can search the web for articles by E. F. Codd, Chris Date, Fabian Pascal, and Hugh Darwen. All this material might seem like overkill, but you’ll be surprised at how complex a database becomes after adding a few tables, constraints, triggers, and stored procedures. As in all fields, a practical grasp of theory lets you predict results and avoid trial-and-error fixes when things go wrong.

Avoid mass-market junk like Database Design for Dummies/Mere Mortals. If you rely on their guidance, then you will create databases where you get answers that you know are wrong, can’t retrieve the information that you want, enter the same data over and over, or type in values only to have them go “missing.” Such books gloss over (or omit) first principles in favor of administrivia like choosing identifier names and interviewing subject-matter experts.

## Foreign Keys

Information about different entities is stored in different tables, so you need a way to navigate between tables. The relational model provides a mechanism called a foreign key to associate tables. A foreign key has these characteristics:

• It’s a column (or group of columns) in a table whose values relate to, or reference, values in some other table.
• It ensures that rows in one table have corresponding rows in another table.
• The table that contains the foreign key is the referencing or child table. The other table is the referenced or parent table.
• A foreign key establishes a direct relationship to the parent table’s primary key (or any candidate key), so foreign-key values are restricted to existing parent-key values. This constraint is called referential integrity. A particular row in a table appointments must have an associated row in a table patients, for example, or there would be appointments for patients who don’t exist or can’t be identified. An orphan row is a row in a child table for which no associated parent-table row exists. In a properly designed database, you can’t insert new orphan rows or make orphans out of existing child-table rows by deleting associated rows in the parent table.
• The values in the foreign key have the same domain as the parent key. Recall from “Tables, Columns, and Rows” earlier in this chapter that a domain defines the set of valid values for a column.
• Unlike primary-key values, foreign-key values can be null (empty); see “Tips for Foreign Keys”.
• A foreign key can have a different column name than its parent key.
• Foreign-key values generally aren’t unique in their own table.
• The first point above is simplified: a foreign key can actually reference the primary key of its own table (rather than only some other table). A table employees with the primary key emp_id can have a foreign key boss_id, for example, that references the column emp_id. This type of table is called self-referencing.

Figure 2.9 shows a primary- and foreign-key relationship between two tables.

Figure 2.9The column pub_id is a foreign key of the table titles that references the column pub_id of publishers.

After a foreign key is defined, your DBMS will enforce referential integrity. You can’t insert the following row into the child table titles because the pub_id value P05 doesn’t exist in the parent table publishers:

`T07    I Blame My Mother         P05`

You can insert this row only if the foreign key accepts nulls:

`T07    I Blame My Mother         NULL`

This row is legal:

`T07    I Blame My Mother         P03`

### Tips for Foreign Keys

• See also “Specifying a Foreign Key with FOREIGN KEY” in Chapter 11.
• SQL lets you specify the referential-integrity action that the DBMS takes when you attempt to update or delete a parent-table key value to which foreign-key values point; see “Tips for FOREIGN KEY” in Chapter 11.
• Allowing nulls in a foreign-key column complicates enforcement of referential integrity. In practice, nulls in a foreign key often remain null temporarily, pending a real-life decision or discovery; see “Nulls” in Chapter 3.

## Relationships

A relationship is an association established between common columns in two tables. A relationship can be:

• One-to-one
• One-to-many
• Many-to-many

### One-to-One

In a one-to-one relationship, each row in table A can have at most one matching row in the table B, and each row in table B can have at most one matching row in table A. Even though it’s practicable to store all the information from both tables in only one table, one-to-one relationships often are used to segregate confidential information for privacy and security reasons, to speed queries by splitting single monolithic tables, and to avoid inserting nulls into tables (see “Nulls” in Chapter 3).

A one-to-one relationship is established when the primary key of one table also is a foreign key referencing the primary key of another table (Figure 2.10 and Figure 2.11).

Figure 2.10A one-to-one relationship. Each row in titles can have at most one matching row in royalties, and each row in royalties can have at most one matching row in titles. Here, the primary key of royalties also is a foreign key referencing the primary key of titles.

Figure 2.11This diagram shows an alternative way to depict the one-to-one relationship in Figure 2.10. The connecting line indicates associated columns. The PK symbol indicates a primary key.

### One-to-Many

In a one-to-many relationship, each row in table A can have many (zero or more) matching rows in table B, but each row in table B has only one matching row in table A. A publisher can publish many books, but each book is published by only one publisher, for example.

One-to-many relationships are established when the primary key of the “one” table appears as a foreign key in the “many” table (Figure 2.12 and Figure 2.13).

Figure 2.12A one-to-many relationship. Each row in publishers can have many matching rows in titles, and each row in titles has only one matching row in publishers. Here, the primary key of publishers (the “one” table) appears as a foreign key in titles (the “many” table).

Figure 2.13This diagram shows an alternative way to depict the one-to-many relationship in Figure 2.12. The connecting line’s unadorned end indicates the “one” table, and the arrow indicates the “many” table. The PK symbol indicates a primary key.

### Many-to-Many

In a many-to-many relationship, each row in table A can have many (zero or more) matching rows in table B, and each row in table B can have many matching rows in table A. Each author can write many books, and each book can have many authors, for example.

A many-to-many relationships is established only by creating a third table called a junction table, whose composite primary key is a combination of both tables’ primary keys; each column in the composite key separately is a foreign key. This technique always produces a unique value for each row in the junction table and splits the many-to-many relationship into two separate one-to-many relationships (Figure 2.14 and Figure 2.15).

Figure 2.14A many-to-many relationship. The junction table title_authors splits the many-to-many relationship between titles and authors into two one-to-many relationships. Each row in titles can have many matching rows in title_authors, as can each row in authors. Here, title_id in title_authors is a foreign key that references the primary key of titles, and au_id in title_authors is a foreign key that references the primary key of authors.

Figure 2.15This diagram shows an alternative way to depict the many-to-many relationship in Figure 2.14. The PK symbol indicates a primary key.

### Tips for Relationships

• Joins (for performing operations on multiple tables) are covered in Chapter 7.
• You can establish a many-to-many relationship without creating a third table if you add repeating groups to the tables, but that method violates first normal form; see “Normalization” later in this chapter.
• A one-to-many relationship also is called a parent–child or master–detail relationship.
• A junction table also is called an associating, linking, pivot, connection, or intersection table.

## Normalization

It’s possible to consolidate all information about books (or any entity type) into a single monolithic table, but that table would be loaded with duplicate data; each title (row) would contain redundant author, publisher, and royalty details. Redundancy is the enemy of database users and administrators: it causes databases to grow wildly large, it slows queries, and it’s a maintenance and reporting nightmare. (When someone moves, you want to change his address in one place, not thousands of places.)

Redundancies lead to a variety of update anomalies—that is, difficulties with operations that insert, update, and delete rows. Normalization is the process—a series of steps—of modifying tables to reduce redundancy and inconsistency. After each step, the database is in a particular normal form. The relational model defines three normal forms, named after famous ordinal numbers:

• First normal form (1NF)
• Second normal form (2NF)
• Third normal form (3NF)

Each normal form is stronger than its predecessors; a database in 3NF also is in 2NF and 1NF. Higher normalization levels tend to increase the number of tables relative to lower levels. Lossless decomposition ensures that table splitting doesn’t cause information loss, and dependency-preserving decomposition ensures that relationships aren’t lost. The matching primary- and foreign-key columns that appear when tables are split are not considered to be redundant data.

Normalization is not systematic; it’s an iterative process that involves repeated table splitting and rejoining and refining until the database designer is (temporarily) happy with the result.

### First Normal Form

A table in first normal form:

• Has columns that contain only atomic values

and

• Has no repeating groups

An atomic value, also called a scalar value, is a single value that can’t be subdivided (Figure 2.16). A repeating group is a set of two or more logically related columns (Figure 2.17). To fix these problems, store the data in two related tables (Figure 2.18).

Figure 2.16In first normal form, each table’s row–column intersection must contain a single value that can’t be subdivided meaningfully. The column authors in this table lists multiple authors and so violates 1NF.

``` title_id title_name                       authors -------- -------------------------------- ------------- T01      1977!                            A01 T04      But I Did It Unconsciously       A03, A04 T11      Perhaps It's a Glandular Problem A03, A04, A06 ```

Figure 2.17Redistributing the column authors into a repeating group also violates 1NF. Don’t represent multiple instances of an entity as multiple columns.

``` title_id title_name                       author1 author2 author3 -------- -------------------------------- ------- ------- ------- T01      1977!                            A01 T04      But I Did It Unconsciously       A03     A04 T11      Perhaps It's a Glandular Problem A03     A04     A06 ```

Figure 2.18The correct design solution is to move the author information to a new child table that contains one row for each author of a title. The primary key in the parent table is title_id, and the composite key in the child table is title_id and au_id.

A database that violates 1NF causes problems:

• Multiple values in a row–column intersection mean that the combination of table name, column name, and key value is insufficient to address every value in the database.
• It’s difficult to retrieve, insert, update, or delete a single value (among many) because you must rely on the order of the values.
• Queries are complex and slow.
• The problems that further normalization solves become unsolvable.

Atomicity

Atomic values are perceived to be indivisible from the point of view of database users. A date, a telephone number, and a character string, for example, aren’t actually intrinsically indivisible because you can decompose the date into a year, month, and day; the telephone number into a country code, area code, prefix, and line number; and the string into its individual characters. What’s important as far as you’re concerned is that the DBMS provide operators and functions that let you extract and manipulate the components of “atomic” values if necessary, such as a substring() function to extract a telephone number’s area code or a year() function to extract a date’s year.

### Second Normal Form

A table in second normal form:

A table contains a partial functional dependency if some (but not all) of a composite key’s values determine a nonkey column’s value. A 2NF table is fully functionally dependent, meaning that a nonkey column’s value might need to be updated if any column values in the composite key change.

The composite key in the table in Figure 2.19 is title_id and au_id. The nonkey columns are au_order (the order in which authors are listed on the cover of a book with multiple authors) and au_phone (the author’s telephone number).

Figure 2.19au_phone depends on au_id but not title_id, so this table contains a partial functional dependency and isn’t in 2NF. The PK symbol indicates a primary key.

For each nonkey column, ask, “Can I determine a nonkey column value if I know only part of the primary-key value?” A no answer means the nonkey column is fully functionally dependent (good); a yes answer means that it’s partially functionally dependent (bad).

For the column au_order, the questions are:

• Can I determine au_order if I know only title_id? No, because there might be more than one author for the same title.
• Can I determine au_order if I know only au_id? No, because I need to know the particular title too.

Good—au_order is fully functionally dependent and can remain in the table. This dependency is denoted by

{title_id, au_id} → {au_order}

and is read “title_id and au_id determine au_order” or “au_order depends on title_id and au_id.” The determinant is the expression to the left of the arrow.

For the column au_phone, the questions are:

• Can I determine au_phone if I know only title_id? No, because there might be more than one author for the same title.
• Can I determine au_phone if I know only au_id? Yes! The author’s telephone number doesn’t depend on the title.

Bad—au_phone is partially functionally dependent and must be moved elsewhere (probably to an authors or telephone_numbers table) to satisfy 2NF rules.

Instant 2NF

A 1NF table is in second normal form automatically if:

• Its primary key is a single column (that is, the key isn’t composite)

or

• All the columns in the table are part of the primary key (simple or composite)

### Third Normal Form

A table in third normal form:

A table contains a transitive dependency if a nonkey column’s value determines another nonkey column’s value. In 3NF tables, nonkey columns are mutually independent and dependent on only primary-key column(s). 3NF is the next logical step after 2NF.

The primary key in the table in Figure 2.20 is title_id. The nonkey columns are price (the book’s price), pub_city (the city where the book is published), and pub_id (the book’s publisher).

Figure 2.20pub_city depends on pub_id, so this table contains a transitive dependency and isn’t in 3NF. The PK symbol indicates a primary key.

For each nonkey column, ask, “Can I determine a nonkey column value if I know any other nonkey column value?” A no answer means that the column is not transitively dependent (good); a yes answer means that the column whose value you can determine is transitively dependent on the other column (bad).

For the column price, the questions are:

• Can I determine pub_id if I know price? No.
• Can I determine pub_city if I know price? No.

For the column pub_city, the questions are:

• Can I determine price if I know pub_city? No.
• Can I determine pub_id if I know pub_city? No, because a city might have many publishers.

For the column pub_id, the questions are:

• Can I determine price if I know pub_id? No.
• Can I determine pub_city if I know pub_id? Yes! The city where the book is published depends on the publisher.

Bad—pub_city is transitively dependent on pub_id and must be moved elsewhere (probably to a publishers table) to satisfy 3NF rules.

As you can see, it’s not enough to ask, “Can I determine A if I know B?” to discover a transitive dependency; you also must ask, “Can I determine B if I know A?”

### Other Normal Forms

Higher levels of normalization exist, but the relational model doesn’t require (or even mention) them. They’re useful in some cases to avoid redundancy. Briefly, they are:

• Boyce-Codd normal form is a more rigorous version of 3NF. BCNF deals with tables that have multiple candidate keys, composite candidate keys, or candidate keys that overlap. A table is in BCNF if every determinant is a candidate key. (A determinant column is one on which some of the columns are fully functionally dependent.)
• A table in fourth normal form is in BCNF and has no multivalued dependencies (MVDs). An MVD occurs when in a table containing at least three columns, one column has multiple rows whose values match a value of a single row of one of the other columns.

Suppose that employees can be assigned to multiple projects and each employee can have multiple skills. If you stuff all this information into a single table, then you must use all three attributes as the key because nothing less can identify a row uniquely. The relationship between emp_id and proj_id is an MVD because for each pair of emp_idskill_id values in the table, only emp_id (independent of skill_id) determines the associated set of proj_id values. The relationship between emp_id and skill_id also is an MVD because the set of skill values for an emp_idproj_id pair always depends on only emp_id. To transform a table with MVDs to 4NF, move each MVD pair to a new table.

• A table in fifth normal form is in 4NF and has no join dependencies, which are generalizations of MVDs. The aim of 5NF is to have tables that can’t be decomposed further into any number of smaller tables. The redundancies and anomalies that 5NF cures are rare and unintuitive. In real databases, you’ll see 1NF, 2NF, 3NF, and occasionally 4NF. 4NF and even 3NF tables almost always are 5NF too.

Denormalization

The increased number of tables that normalization generates might sway you to denormalize your database to speed queries (because having fewer tables reduces computationally expensive joins and storage throughput). This common technique trades off data integrity for performance and presents a few other problems. A denormalized database:

• Usually is harder to understand than a normalized one
• Usually makes retrievals faster but updates slower
• Increases the risk of inserting inconsistent data
• Might improve the performance of some database applications but hurt that of others (because users’ table-access patterns change over time)

The need for denormalization isn’t a weakness in the relational model but reveals a flawed implementation of the model in DBMSs. A common use for denormalized tables is as permanent logs of data copied from other tables. The logged rows are redundant, but because they’re only INSERTed (never UPDATEd), they serve as an audit trail immune to future changes in the source tables.

## The Sample Database

Pick up an SQL or database-design book, and probably you’ll find a students/courses/teachers, customers/orders/products, or authors/books/publishers database. In a bow to convention, most of the SQL examples in this book use an authors/books/publishers sample database named books. Here are some things that you should know about books:

• Recall from “Tables, Columns, and Rows” earlier in this chapter that a database appears to the user as a collection of tables (and nothing but tables). books contains five main tables that contain information about authors, titles they’ve published, their publishers, and their royalties. Figure 2.21 depicts the tables and relationships in books by using the graphical conventions introduced earlier in this chapter.
• books also contains a few additional tables that are used in specific examples. These tables are described later in the text when they’re relevant.
• The SQL statements in Chapters 10 and later modify data in books (rather than just retrieve data). Unless otherwise noted, each new section in a chapter starts with a pristine copy of books. In other words, assume that database changes made in one section don’t carry over to the next section.
• Some of the concepts mentioned in this section, such as data types and nulls, are covered in the next chapter.
• books is a teaching tool; its structure and size don’t approach the complexity of real production databases.
• To create the sample database on your own DBMS, see “Creating the Sample Database” later in this chapter.

Figure 2.21The sample database books. PK denotes a primary key. (Click to enlarge.)

### The Table authors

The table authors describes the books’ authors. Each author has a unique identifier (au_id) that’s the primary key. Table 2.3 shows the structure of the table authors, and Figure 2.22 shows its contents.

Table 2.3Table Structure of authors
Column Name Description Data Type Nulls? Keys
au_id Unique author identifier CHAR(3) PK
au_fname Author first name VARCHAR(15)
au_lname Author last name VARCHAR(15)
phone Author telephone number VARCHAR(12) Yes
city Author city VARCHAR(15) Yes
state Author state CHAR(2) Yes
zip Author zip (postal) code CHAR(5) Yes

Figure 2.22The contents of the table authors.

```au_id au_fname  au_lname    phone        address              city           state zip
----- --------- ----------- ------------ -------------------- -------------- ----- -----
A01   Sarah     Buchman     718-496-7223 75 West 205 St       Bronx          NY    10468
A02   Wendy     Heydemark   303-986-7020 2922 Baseline Rd     Boulder        CO    80303
A03   Hallie    Hull        415-549-4278 3800 Waldo Ave, #14F San Francisco  CA    94123
A04   Klee      Hull        415-549-4278 3800 Waldo Ave, #14F San Francisco  CA    94123
A05   Christian Kells       212-771-4680 114 Horatio St       New York       NY    10014
A06             Kellsey     650-836-7128 390 Serra Mall       Palo Alto      CA    94305
A07   Paddy     O'Furniture 941-925-0752 1442 Main St         Sarasota       FL    34236
```

### The Table publishers

The table publishers describes the books’ publishers. Each publisher has a unique identifier (pub_id) that’s the primary key. Table 2.4 shows the structure of the table publishers, and Figure 2.23 shows its contents.

Table 2.4Table Structure of publishers
Column Name Description Data Type Nulls? Keys
pub_id Unique publisher identifier CHAR(3) PK
pub_name Publisher name VARCHAR(20)
city Publisher city VARCHAR(15)
state Publisher state/province/region CHAR(2) Yes
country Publisher country VARCHAR(15)

Figure 2.23The contents of the table publishers.

```pub_id pub_name            city          state country
------ ------------------- ------------- ----- -------
P01    Abatis Publishers   New York      NY    USA
P02    Core Dump Books     San Francisco CA    USA
P03    Schadenfreude Press Hamburg       NULL  Germany
P04    Tenterhooks Press   Berkeley      CA    USA
```

### The Table titles

The table titles describes the books. Each book has a unique identifier (title_id) that’s the primary key. titles contains a foreign key, pub_id, that references the table publishers to indicate a book’s publisher. Table 2.5 shows the structure of the table titles, and Figure 2.24 shows its contents.

Table 2.5Table Structure of titles
Column Name Description Data Type Nulls? Keys
title_id Unique title identifier CHAR(3) PK
title_name Book title VARCHAR(40)
type Subject of the book VARCHAR(10) Yes
pub_id Publisher identifier CHAR(3) FK publishers(pub_id)
pages Page count INTEGER Yes
price Cover price DECIMAL(5,2) Yes
sales Lifetime number of copies sold INTEGER Yes
pubdate Date of publication DATE Yes
contract Nonzero if author(s) signed contract SMALLINT

Figure 2.24The contents of the table titles.

```title_id title_name                           type       pub_id pages price sales   pubdate    contract
-------- ------------------------------------ ---------- ------ ----- ----- ------- ---------- --------
T01      1977!                                history    P01      107 21.99     566 2000-08-01        1
T02      200 Years of German Humor            history    P03       14 19.95    9566 1998-04-01        1
T04      But I Did It Unconsciously           psychology P04      510 12.99   13001 1999-05-31        1
T05      Exchange of Platitudes               psychology P04      201  6.95  201440 2001-01-01        1
T06      How About Never?                     biography  P01      473 19.95   11320 2000-07-31        1
T07      I Blame My Mother                    biography  P03      333 23.95 1500200 1999-10-01        1
T08      Just Wait Until After School         children   P04       86 10.00    4095 2001-06-01        1
T09      Kiss My Boo-Boo                      children   P04       22 13.95    5000 2002-05-31        1
T10      Not Without My Faberge Egg           biography  P01     NULL  NULL    NULL       NULL        0
T11      Perhaps It's a Glandular Problem     psychology P04      826  7.99   94123 2000-11-30        1
T12      Spontaneous, Not Annoying            biography  P01      507 12.99  100001 2000-08-31        1
T13      What Are The Civilian Applications?  history    P03      802 29.99   10467 1999-05-31        1```

### The Table title_authors

Authors and books have a many-to-many relationship because an author can write multiple books and a book can have multiple authors. title_authors is the junction table that associates the tables authors and titles; see “Relationships” earlier in this chapter. title_id and au_id together form a composite primary key, and each column separately is a foreign key that references titles and authors, respectively. The nonkey columns indicate the order of the author’s name on the book’s cover (always 1 for a book with a sole author) and the fraction of total royalties that each author receives (always 1.0 for a book with a sole author). Table 2.6 shows the structure of the table title_authors, and Figure 2.25 shows its contents.

Table 2.6Table Structure of title_authors
Column Name Description Data Type Nulls? Keys
title_id Title identifier CHAR(3) PK, FK titles(title_id)
au_id Author identifier CHAR(3) PK, FK authors(au_id)
au_order Author name order on book cover SMALLINT
royalty_share Author fractional royalty share DECIMAL(5,2)

Figure 2.25The contents of the table title_authors.

```title_id au_id au_order royalty_share
-------- ----- -------- -------------
T01      A01          1          1.00
T02      A01          1          1.00
T03      A05          1          1.00
T04      A03          1          0.60
T04      A04          2          0.40
T05      A04          1          1.00
T06      A02          1          1.00
T07      A02          1          0.50
T07      A04          2          0.50
T08      A06          1          1.00
T09      A06          1          1.00
T10      A02          1          1.00
T11      A03          2          0.30
T11      A04          3          0.30
T11      A06          1          0.40
T12      A02          1          1.00
T13      A01          1          1.00
```

### The Table royalties

The table royalties specifies the royalty rate paid to all the authors (not each author) of each book, including the total up-front advance against royalties paid to all authors (again, not each author) of a book. The royalties primary key is title_id. The table royalties has a one-to-one relationship with titles, so the royalties primary key also is a foreign key that references the titles primary key. Table 2.7 shows the structure of the table royalties, and Figure 2.26 shows its contents.

Table 2.7Table Structure of royalties
Column Name Description Data Type Nulls? Keys
title_id Unique title identifier CHAR(3) PK, FK titles(title_id)
advance Up-front payment to author(s) DECIMAL(9,2) Yes
royalty_rate Fraction of revenue paid author(s) DECIMAL(5,2) Yes

Figure 2.26The contents of the table royalties.

```title_id advance     royalty_rate
-------- ----------- ------------
T01         10000.00         0.05
T02          1000.00         0.06
T03         15000.00         0.07
T04         20000.00         0.08
T05        100000.00         0.09
T06         20000.00         0.08
T07       1000000.00         0.11
T08             0.00         0.04
T09             0.00         0.05
T10             NULL         NULL
T11        100000.00         0.07
T12         50000.00         0.09
T13         20000.00         0.06
```

## Creating the Sample Database

To create (or re-create) the database books on your own DBMS, visit questingvolepress.com, click the Download link for this book, and then follow the onscreen instructions. Creating books is a two-step process:

1. Use your DBMS’s built-in tools to create a new, blank database named books.
2. Run an SQL script that creates tables within books and populates them with data.

Listing 2.1 shows a standard (ISO/ANSI) SQL script that creates the sample-database tables and inserts rows into them.

If you’re using Microsoft Access, then you don’t run a script—you simply open an Access database file.

Listing 2.1This standard SQL script, books_standard.sql, creates the tables in the sample database books and populates them with data. The file that you download at the companion website includes versions of this script changed to run on specific DBMSs.

```DROP TABLE authors;
CREATE TABLE authors
(
au_id    CHAR(3)     NOT NULL,
au_fname VARCHAR(15) NOT NULL,
au_lname VARCHAR(15) NOT NULL,
phone    VARCHAR(12)         ,
city     VARCHAR(15)         ,
state    CHAR(2)             ,
zip      CHAR(5)             ,
CONSTRAINT pk_authors
PRIMARY KEY (au_id)
);
INSERT INTO authors VALUES
('A01','Sarah','Buchman','718-496-7223',
'75 West 205 St','Bronx','NY','10468');
INSERT INTO authors VALUES
('A02','Wendy','Heydemark','303-986-7020',
'2922 Baseline Rd','Boulder','CO','80303');
INSERT INTO authors VALUES
('A03','Hallie','Hull','415-549-4278',
'3800 Waldo Ave, #14F','San Francisco','CA','94123');
INSERT INTO authors VALUES
('A04','Klee','Hull','415-549-4278',
'3800 Waldo Ave, #14F','San Francisco','CA','94123');
INSERT INTO authors VALUES
('A05','Christian','Kells','212-771-4680',
'114 Horatio St','New York','NY','10014');
INSERT INTO authors VALUES
('A06','','Kellsey','650-836-7128',
'390 Serra Mall','Palo Alto','CA','94305');
INSERT INTO authors VALUES
'1442 Main St','Sarasota','FL','34236');

DROP TABLE publishers;
CREATE TABLE publishers
(
pub_id   CHAR(3)     NOT NULL,
pub_name VARCHAR(20) NOT NULL,
city     VARCHAR(15) NOT NULL,
state    CHAR(2)             ,
country  VARCHAR(15) NOT NULL,
CONSTRAINT pk_publishers
PRIMARY KEY (pub_id)
);
INSERT INTO publishers VALUES
('P01','Abatis Publishers','New York','NY','USA');
INSERT INTO publishers VALUES
('P02','Core Dump Books','San Francisco','CA','USA');
INSERT INTO publishers VALUES
INSERT INTO publishers VALUES
('P04','Tenterhooks Press','Berkeley','CA','USA');

DROP TABLE titles;
CREATE TABLE titles
(
title_id   CHAR(3)      NOT NULL,
title_name VARCHAR(40)  NOT NULL,
type       VARCHAR(10)          ,
pub_id     CHAR(3)      NOT NULL,
pages      INTEGER              ,
price      DECIMAL(5,2)         ,
sales      INTEGER              ,
pubdate    DATE                 ,
contract   SMALLINT     NOT NULL,
CONSTRAINT pk_titles
PRIMARY KEY (title_id)
);
INSERT INTO titles VALUES
('T01','1977!','history',
'P01',107,21.99,566,DATE '2000-08-01',1);
INSERT INTO titles VALUES
('T02','200 Years of German Humor','history',
'P03',14,19.95,9566,DATE '1998-04-01',1);
INSERT INTO titles VALUES
'P02',1226,39.95,25667,DATE '2000-09-01',1);
INSERT INTO titles VALUES
('T04','But I Did It Unconsciously','psychology',
'P04',510,12.99,13001,DATE '1999-05-31',1);
INSERT INTO titles VALUES
('T05','Exchange of Platitudes','psychology',
'P04',201,6.95,201440,DATE '2001-01-01',1);
INSERT INTO titles VALUES
'P01',473,19.95,11320,DATE '2000-07-31',1);
INSERT INTO titles VALUES
('T07','I Blame My Mother','biography',
'P03',333,23.95,1500200,DATE '1999-10-01',1);
INSERT INTO titles VALUES
('T08','Just Wait Until After School','children',
'P04',86,10.00,4095,DATE '2001-06-01',1);
INSERT INTO titles VALUES
('T09','Kiss My Boo-Boo','children',
'P04',22,13.95,5000,DATE '2002-05-31',1);
INSERT INTO titles VALUES
('T10','Not Without My Faberge Egg','biography',
'P01',NULL,NULL,NULL,NULL,0);
INSERT INTO titles VALUES
('T11','Perhaps It''s a Glandular Problem','psychology',
'P04',826,7.99,94123,DATE '2000-11-30',1);
INSERT INTO titles VALUES
('T12','Spontaneous, Not Annoying','biography',
'P01',507,12.99,100001,DATE '2000-08-31',1);
INSERT INTO titles VALUES
('T13','What Are The Civilian Applications?','history',
'P03',802,29.99,10467,DATE '1999-05-31',1);

DROP TABLE title_authors;
CREATE TABLE title_authors
(
title_id      CHAR(3)      NOT NULL,
au_id         CHAR(3)      NOT NULL,
au_order      SMALLINT     NOT NULL,
royalty_share DECIMAL(5,2) NOT NULL,
CONSTRAINT pk_title_authors
PRIMARY KEY (title_id, au_id)
);
INSERT INTO title_authors VALUES('T01','A01',1,1.0);
INSERT INTO title_authors VALUES('T02','A01',1,1.0);
INSERT INTO title_authors VALUES('T03','A05',1,1.0);
INSERT INTO title_authors VALUES('T04','A03',1,0.6);
INSERT INTO title_authors VALUES('T04','A04',2,0.4);
INSERT INTO title_authors VALUES('T05','A04',1,1.0);
INSERT INTO title_authors VALUES('T06','A02',1,1.0);
INSERT INTO title_authors VALUES('T07','A02',1,0.5);
INSERT INTO title_authors VALUES('T07','A04',2,0.5);
INSERT INTO title_authors VALUES('T08','A06',1,1.0);
INSERT INTO title_authors VALUES('T09','A06',1,1.0);
INSERT INTO title_authors VALUES('T10','A02',1,1.0);
INSERT INTO title_authors VALUES('T11','A03',2,0.3);
INSERT INTO title_authors VALUES('T11','A04',3,0.3);
INSERT INTO title_authors VALUES('T11','A06',1,0.4);
INSERT INTO title_authors VALUES('T12','A02',1,1.0);
INSERT INTO title_authors VALUES('T13','A01',1,1.0);

DROP TABLE royalties;
CREATE TABLE royalties
(
title_id     CHAR(3)      NOT NULL,
royalty_rate DECIMAL(5,2)         ,
CONSTRAINT pk_royalties
PRIMARY KEY (title_id)
);
INSERT INTO royalties VALUES('T01',10000,0.05);
INSERT INTO royalties VALUES('T02',1000,0.06);
INSERT INTO royalties VALUES('T03',15000,0.07);
INSERT INTO royalties VALUES('T04',20000,0.08);
INSERT INTO royalties VALUES('T05',100000,0.09);
INSERT INTO royalties VALUES('T06',20000,0.08);
INSERT INTO royalties VALUES('T07',1000000,0.11);
INSERT INTO royalties VALUES('T08',0,0.04);
INSERT INTO royalties VALUES('T09',0,0.05);
INSERT INTO royalties VALUES('T10',NULL,NULL);
INSERT INTO royalties VALUES('T11',100000,0.07);
INSERT INTO royalties VALUES('T12',50000,0.09);
INSERT INTO royalties VALUES('T13',20000,0.06);
```