2. The Relational Model

Sections
Tables, Columns, and Rows
Primary Keys
Foreign Keys
Relationships
Normalization
The Sample Database
Creating the Sample Database

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).

Document: Abstract of A Relational Model of Data for Large Shared Data Banks by Edgar F. Codd

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.

Diagram: A Venn diagram showing overlapping circles A and B within the universe U

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:

Columns

Columns in a given table have these characteristics:

Rows

Rows in a given table have these characteristics:

Tips for Tables, Columns, and Rows

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:

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

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:

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.

Diagram: The primary-key and foreign-key relationship between the table publishers and the table titles

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

Relationships

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

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.

Diagram: The one-to-one relationship between the table titles and the table royalties

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.

Diagram: An alternative way to show the preceding one-to-one relationship

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).

Diagram: The one-to-many relationship between the table publishers (one) and the table titles (many)

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.

Diagram: An alternative way to show the preceding one-to-many relationship

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.

Diagram: The many-to-many relationship between the table titles and the table authors, with junction table title_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.

Diagram: An alternative way to show the preceding many-to-many relationship

Tips for Relationships

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:

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:

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.

Diagram: Tables in first normal form

A database that violates 1NF causes problems:

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.

Diagram: A table not in second normal form

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:

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:

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:

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.

Diagram: A table not in third normal form

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:

For the column pub_city, the questions are:

For the column pub_id, the questions are:

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:

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:

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:

Figure 2.21The sample database books. PK denotes a primary key.

Diagram: The sample database, named books, with tables authors, publishers, titles, title_authors, and royalties

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
address Author address VARCHAR(20) 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
T03      Ask Your System Administrator        computer   P02     1226 39.95   25667 2000-09-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)         ,
  address  VARCHAR(20)         ,
  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
  ('A07','Paddy','O''Furniture','941-925-0752',
  '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
  ('P03','Schadenfreude Press','Hamburg',NULL,'Germany');
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
  ('T03','Ask Your System Administrator','computer',
  '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
  ('T06','How About Never?','biography',
  '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,
  advance      DECIMAL(9,2)         ,
  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);