Database: Bookbiz Tables

The relationship diagram and SQL describe tables in the Bookbiz database that comes with Practical SQL Handbook. The tables presented here are the same as those in the text example with minor changes (see the files on the book's CD for the original).

Relationship Diagram

SQL for CREATE TABLEs

  1 
  2 
  3 
  4 
  5 
  6 
  7 
  8 
  9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
 39 
 40 
 41 
 42 
 43 
 44 
 45 
 46 
 47 
 48 
 49 
 50 
 51 
 52 
 53 
 54 
 55 
 56 
 57 
 58 
 59 
 60 
 61 
 62 
 63 
 64 
 65 
 66 
 67 
 68 
 69 
 70 
 71 
 72 
 73 
 74 
 75 

CREATE TABLE authors (
    au_id    CHAR(11)     PRIMARY KEY,
    au_lname VARCHAR(40)  NOT NULL,
    au_fname VARCHAR(20)  NOT NULL,
    phone    CHAR(12),
    address  VARCHAR(40),
    city     VARCHAR(20),
    state    CHAR(2),
    zip      CHAR(5) 
);

CREATE TABLE publishers (
    pub_id   CHAR(4)      PRIMARY KEY,
    pub_name VARCHAR(40),
    address  VARCHAR(40),
    city     VARCHAR(20),
    state    CHAR(2) 
);

CREATE TABLE titleauthors (
    au_id        CHAR(11)      REFERENCES authors(au_id),
    title_id     CHAR(6)       REFERENCES titles(title_id),
    au_ord       TINYINT,
    royaltyshare DECIMAL(5,2),
    PRIMARY KEY (au_id, title_id)
);

CREATE TABLE titles (
    title_id  CHAR(6)        PRIMARY KEY,
    title     VARCHAR(80)    NOT NULL,
    type      CHAR(12),
    pub_id    CHAR(4)        REFERENCES publishers(pub_id),
    price     DECIMAL(8,2),
    advance   DECIMAL(10,2),
    ytd_sales BIT,
    contract  TINYINT        NOT NULL,
    notes     VARCHAR(200),
    pubdate   DATE 
);

CREATE TABLE editors (
    ed_id    CHAR(11)     PRIMARY KEY,
    ed_lname VARCHAR(40)  NOT NULL,
    ed_fname VARCHAR(20)  NOT NULL,
    ed_pos   VARCHAR(12),
    phone    CHAR(12),
    address  VARCHAR(40),
    city     VARCHAR(20),
    state    CHAR(2),
    zip      CHAR(5),
    ed_boss  CHAR(11)     REFERENCES editors(ed_id)
);

CREATE TABLE titleditors (
    ed_id    CHAR(11)  REFERENCES editors(ed_id),
    title_id CHAR(6)   REFERENCES titles(title_id),
    ed_ord   TINYINT,
    PRIMARY KEY (ed_id, title_id)
);

CREATE TABLE sales (
    sonum   INT          PRIMARY KEY,
    stor_id CHAR(4)      NOT NULL,
    ponum   VARCHAR(20)  NOT NULL,
    sdate   DATE 
);

CREATE TABLE salesdetails (
    sonum        INT       REFERENCES sales(sonum),
    title_id     CHAR(6)   REFERENCES titles(title_id),
    qty_ordered  SMALLINT  NOT NULL,
    qty_shipped  SMALLINT,
    date_shipped DATE,
    PRIMARY KEY (sonum, title_id)
);