Our fictional bookshop is located in Hertford, England (a very real and charming town north of London). It is located in a building constructed around 1735 and has two rather smallish rooms on two floors with floor-to-ceiling bookshelves throughout. Upon entering, one is immediately transported to a much earlier era being quite overwhelmed by the wonderful sight and odor of the ancient mahogany wood in which the entire interior is lined along with the rare and ancient books that reside on them. There is a little bell that announces one's entrance into the shop but it is not really needed, as the delightfully squeaky floor boards quite clearly makes your presence known.
In spite of the ancient setting and very old and rare books, this bookshop has a very modern Internet storefront through which it sells and auctions off its expensive inventory. A computer system contains a database describing the inventory and manages the sales and auction processes. The database schema for our bookshop is given below.
CREATE TABLE acctmgr( mgrid CHAR(7) PRIMARY KEY, name CHAR(24), hire_date DATE, commission DECIMAL(4,3) ); CREATE TABLE patron( patid CHAR(3) PRIMARY KEY, name CHAR(30), street CHAR(35), city CHAR(17), state CHAR(2), country CHAR(2), pc CHAR(10), email CHAR(63), phone CHAR(15), mgrid CHAR(7) REFERENCES acctmgr ON UPDATE CASCADE, notes LONG VARCHAR ); CREATE TABLE author( last_name CHAR(13) PRIMARY KEY, full_name CHAR(35), gender CHAR(1), yr_born SMALLINT, yr_died SMALLINT, short_bio VARCHAR(216), CONSTRAINT yob_gender_key KEY (yr_born, gender) ); CREATE TABLE genres( text CHAR(31) PRIMARY KEY ); CREATE TABLE subjects( text CHAR(51) PRIMARY KEY ); CREATE TABLE book( bookid CHAR(14) PRIMARY KEY, last_name CHAR(13) REFERENCES author ON DELETE CASCADE ON UPDATE CASCADE, title VARCHAR(105) KEY, descr CHAR(61), publisher VARCHAR(136), publ_year SMALLINT KEY, lc_class CHAR(33), date_acqd DATE, date_sold DATE, price DECIMAL(10,2), cost DECIMAL(10,2), notes LONG VARCHAR ); CREATE TABLE related_name( bookid CHAR(14) REFERENCES book ON DELETE CASCADE ON UPDATE CASCADE, name CHAR(61) ); CREATE TABLE genres_books( bookid CHAR(14) REFERENCES book ON DELETE CASCADE ON UPDATE CASCADE, genre CHAR(31) REFERENCES genres ); CREATE TABLE subjects_books( bookid CHAR(14) REFERENCES book ON DELETE CASCADE ON UPDATE CASCADE, subject CHAR(51) REFERENCES subjects ); CREATE TABLE sale( bookid CHAR(14) REFERENCES book ON DELETE CASCADE ON UPDATE CASCADE, patid CHAR(3) REFERENCES patron ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE auction( aucid INTEGER PRIMARY KEY, bookid CHAR(14) REFERENCES book ON DELETE CASCADE ON UPDATE CASCADE, mgrid CHAR(7) REFERENCES acctmgr ON UPDATE CASCADE, start_date DATE, end_date DATE, reserve DECIMAL(10,2), curr_bid DECIMAL(10,2), bid_time TIME ); CREATE TABLE bid( aucid INTEGER REFERENCES auction ON DELETE CASCADE ON UPDATE CASCADE, patid CHAR(3) REFERENCES patron ON DELETE CASCADE ON UPDATE CASCADE, offer DECIMAL(10,2), bid_ts TIMESTAMP );
Descriptions for each of the above tables are given below.
|author||Each row contains biographical information about a renowned author.|
|book||Contains information about each book in the bookshop inventory. The last_name column associates the book with its author. Books with a non null date_sold are no longer available.|
|genres||Table of genre names (e.g., "Historical fiction") with which particular books are associated via the genres_books table.|
|subjects||Table of subject names (e.g., "Cape Cod") with which particular books are associated via the subjects_books table.|
|related_name||Related names are names of individuals associated with a particular book. The names are usually hand-written in the book's front matter or on separate pages that were included with the book (e.g., letters) and identify the book's provenance (owners). Only a few books have related names. However, their presence can significantly increase the value of the book.|
|genres_books||Used to create a many-to-many relationship between genres and books.|
|subjects_books||Used to create a many-to-many relationship between subjects and books.|
|note||Connects each note_line to its associated book. Notes include edition info and other comments (often coded) relating to its condition.|
|note_line||One row for each line of text in a particular note.|
|acctmgr||Account manager are the bookshop employees responsible for servicing the patrons and managing auctions.|
|patron||Bookshop customers and their contact info. Connected to their purchases/bids through their relationship with the sale and auction tables.|
|sale||Contains one row for each book that has been sold. Connects the book with the patron who acquired through the bookid and patid columns.|
|auction||Some books are auctioned. Those that have been (or currently being) auctioned have a row in this table that identifies the account manager who oversees the auction. The reserve column specifies the minimum acceptable bid, curr_bid contains the current amount bid.|
|bid||Each row provides the bid history for a particular auction.|
Foreign keys are declared using the REFERENCES clause. Many are specified with the ON DELETE/UPDATE CASCADE option indicating that deletions or updates to the referenced rows will cause the referencing row to automatically be deleted or updated as well.
A schema diagram depicting the inter-table relationships is shown below. As was mentioned above for the NSF awards database, the arrows represent a one-to-many relationship between the source and target tables and labels on the arrows identify the foreign key in the target table on which the relationship is formed.
Figure 6 - Bookshop Database Schema Diagram
The sample data that is included with this example contains book descriptions that were obtained from the United States Library of Congress online card catalog: http://catalog.loc.gov. The short biographical sketches included with each author entry are condensed descriptions from information about each author contained on Wikipedia: http://www.wikipedia.org. The use of the Wikipedia information is governed by the Creative Commons Attribution-ShareAlike license: http://creativecommons.org/licenses/by-sa/3.0/. Pricing information and the JPEG files of photographs of some of the books in the database were obtained from the website for Peter Harrington Antiquarian Bookseller in Chelsea London, http://www.peterharrington.co.uk, which is a perfect real-world example of the kind of bookshop depicted in this example.