Antiquarian Bookshop Database

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.

Table 3. Bookshop Database Table Descriptions

Table Name	Description
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.