In this chapter
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.
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).
| Model | SQL | Files |
|---|---|---|
| Relation | Table | File |
| Attribute | Column | Field |
| Tuple | Row | Record |
From a user’s point of view, a database is a collection of one or more tables (and nothing but tables). A table:
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
Columns in a given table have these characteristics:
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
Rows in a given table have these characteristics:
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.
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.
| DBMS | Address |
|---|---|
| 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 |
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
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.
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.
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
A relationship is an association established between common columns in two tables. A relationship can be:
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.
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.
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.
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.
A table in first normal form:
Has columns that contain only atomic values
and
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:
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.
A table in second normal form:
Is in first normal form
and
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:
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:
Its primary key is a single column (that is, the key isn’t composite)
or
A table in third normal form:
Is in second normal form
and
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:
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?”
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:
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_id–skill_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_id–proj_id pair always depends on only emp_id. To transform a table with MVDs to 4NF, move each MVD pair to a new table.
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.
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. (Click to enlarge.)
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.
| 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 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.
| 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 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.
| 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
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.
| 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 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.
| 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
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:
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);