Summarizing the bookbiz Database

Let's take a closer look at the nine tables in the bookbiz database to better familiarize you with the material on which the examples in the rest of The Practical SQL Handbook are based. (There are more bookbiz details in Appendix D.)

The bookbiz database keeps track of the activities of three subsidiary publishing companies. Since the fiscal arrangements of the subsidiaries are not independent, the parent publisher has chosen to maintain a single database.

The publishers table contains information about the three publishing lines: their identification numbers, names, and addresses.

For each author under contract with any of the publishers, the authors table contains an identification number (Social Security number), first and last name, and address information. The editors table contains similar information about each editor, with the addition of a position column that describes the type of work the editor does (acquisitions or project management).

For each book that has been or is about to be published, the titles table contains an identification number, name, type, publisher identification number, price, advance, year-to-date sales, contract status, comments, and publication date. The numbers in the ytd_sales column, which will change as more books are sold, might be kept current in one of several ways:

• By making periodic entries using the data modification commands

• By coding logic into an application program that automatically updates ytd_sales whenever a sale is entered into the salesdetails table

• By using SQL to define a trigger that accomplishes the same automatic updating. (Triggers, not covered in the 1992 ISO-ANSI SQL standard, are provided as extensions by a number of relational database management systems. See Chapter 10.)

The titles and authors are represented in separate tables that can be linked with a third table, the titleauthors table. For each book, titleauthors contains a row for every author involved, with information on the title ID, the author ID, the author cover credit order (which name comes first), and the royalty split among the authors of a book. The titleditors table similarly links the titles and their editors. Instead of cover credit order, it lists editing order so that it's possible to find who was the first or last editor.

The roysched table lists the unit sales ranges and the royalty connected with each range. The royalty is some percentage of the net receipts from sales. The percentage is used to calculate the amount due each author based on sales of his or her book.

The sales table has top-level information about each purchase order received from bookstores: sales order number (assigned by the publisher), store identification, purchase order number (assigned by the store), and date. The salesdetails table contains information about each line in the purchase order (assuming any purchase order may involve more than one book): title, quantity ordered, quantity shipped, and date shipped.

Of course, a real publisher's database would be much more complex, with tables for stores, employees, distributors, production costs, vendors, subcontractors, and the like. (As an exercise, you might try sketching some of these tables and deciding how they would relate to the tables already in bookbiz.) However, these nine tables do present enough material to work with for learning SQL, and they are used throughout this book.