Science Fiction Wall of Fame (Shame?)

Question

I have a few of hobbies, one is reading science fiction and fantasy and another is coding solutions. I thought I might have a chance to combine both hobbies on this project.

I have well over 1500 Science Fiction and Fantasy books that I have accumulated over the years. Some of them are signed by the authors. Most of them are Mass Market Paperbacks. Quite a few of them are in a series by the author.

Problem Statement
Over the years I have occasionally purchased the same book twice because I didn’t realize I already had it. I don’t actually know how many books I own. At some point I may want to sell some or all of the books. I don’t have a clue about the value of the library. It became obvious that a catalog or inventory of the all SF and F books I owned was necessary.

Six months ago a Microsoft excel spread sheet of books was started. I found I kept adding columns to the spreadsheet, and the spreadsheet seemed like it was insufficient to do the job. Some of the issues included multiple data entries for the same book in different lists for wish lists or for selling or buying books. The user has the ability to add formats and categories. Formats and categories can’t be deleted once they are in use. The user can add and delete authors, books and author series. A book can be bought or sold. A book can be added to the wish list. A book on the wish list is updated when it is bought. A book may be borrowed from the library and read. Since these books started being purchased in 1968 the purchase information may not be available for all books. Deleting an author deletes all the authors’ series and books.

This database is the first part of the project, I couldn’t really create a friendly user interface until the database is working. Due to feature creep this database can now handle other kinds of books besides science fiction and fantasy. A future version of this database will have an additional table for the status of the book (new or user) and the condition of the book (Excellent, Good, Fair, Poor).

Solution
I was unable to provide the entire solution due to size constraints on the question. The code is available here. The unit tests run by default. To prevent the unit tests from running comment out the call to booklibinventory.zzzRunAllUnitTests(); at the end of the SQL script.

For books there is no single identifying item, or rather the ISBN is the single identifying item based on author, title, format and edition, but some books printed in the 1960’s and 1970’s don’t have an ISBN on the book itself. This database uses the author title and format together as the identity of the book.

There are about 2790 lines of code and comments in this database. The first 240 lines are the data definitions of the tables. There are 1849 lines of stored procedures implementing the insert, update, delete and retrieval stored procedures. The last 701 line of code are unit tests in an attempt to make sure the stored procedures and functions work when I start developing the user interface.

This is the first relational schema I have designed from scratch. A year ago I wouldn’t have used any stored procedures because I didn’t know about the benefits of using stored procedures.

When I started this project I didn’t know about database partitioning, I learned about table normalization in the university and that is what I tried to do. The current design allows for addition of fields at a later time without modifying any existing tables. This solution requires additional joins when generating reports, but existing data won’t be invalidated.

Questions:
Is the normalization well designed, or would it be better off using partitioning? Is my use of indexes good, am I using too many or too few? Are there any obvious bottle necks that would affect the performance of inserts, update or queries? Is there anything in the code that really shouldn’t be done, any bad practices? Is there anything that should be added to the database prevent or help deter SQL injection? What should be implemented that hasn’t been implemented (what features would you want)?

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema booklibinventory
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `booklibinventory` DEFAULT CHARACTER SET utf8 ;
USE `booklibinventory` ;

-- -----------------------------------------------------
-- Table `booklibinventory`.`authorstab`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`authorstab` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`authorstab` (
  `idAuthors` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `LastName` VARCHAR(20) NOT NULL,
  `FirstName` VARCHAR(20) NOT NULL,
  `MiddleName` VARCHAR(20) NULL DEFAULT NULL,
  `YearOfBirth` VARCHAR(4) NULL DEFAULT NULL,
  `YearOfDeath` VARCHAR(4) NULL DEFAULT NULL,
  PRIMARY KEY (`idAuthors`, `LastName`, `FirstName`),
  UNIQUE INDEX `idAuthors_UNIQUE` (`idAuthors` ASC),
  INDEX `LastName` (`LastName` ASC),
  INDEX `LastCMFirst` (`LastName` ASC, `FirstName` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`bookcategories`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`bookcategories` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`bookcategories` (
  `idBookCategories` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `CategoryName` VARCHAR(45) NOT NULL COMMENT 'This will be strings like Non-Fiction, Mystery, Science-Fiction, Fantasy, Poetry, Art etc.',
  PRIMARY KEY (`idBookCategories`, `CategoryName`),
  UNIQUE INDEX `idBookCategories_UNIQUE` (`idBookCategories` ASC),
  INDEX `CategoryNames` (`CategoryName` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`bksynopsis`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`bksynopsis` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`bksynopsis` (
  `BookFKbd` INT(10) UNSIGNED NOT NULL,
  `StoryLine` VARCHAR(1024) NULL DEFAULT NULL,
  PRIMARY KEY (`BookFKbd`),
  INDEX `BookFKbD` (`BookFKbd` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`bookformat`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`bookformat` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`bookformat` (
  `idFormat` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `FormatName` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idFormat`, `FormatName`),
  UNIQUE INDEX `idFormat_UNIQUE` (`idFormat` ASC),
  UNIQUE INDEX `FormatName_UNIQUE` (`FormatName` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`bookinfo`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`bookinfo` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`bookinfo` (
  `idBookInfo` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `TitleFKbi` INT(10) UNSIGNED NOT NULL,
  `AuthorFKbi` INT(10) UNSIGNED NOT NULL COMMENT 'Foreign Key Into Author Table',
  `CategoryFKbi` INT(10) UNSIGNED NOT NULL,
  `BookFormatFKbi` INT(10) UNSIGNED NOT NULL COMMENT 'Foreign Key Into Format Table',
  `SeriesFKBi` INT(10) UNSIGNED NOT NULL COMMENT 'Foreign Key into Series Table',
  PRIMARY KEY (`idBookInfo`, `TitleFKbi`, `AuthorFKbi`),
  UNIQUE INDEX `idBookInfo_UNIQUE` (`idBookInfo` ASC),
  INDEX `CategoryFKbI` (`CategoryFKbi` ASC),
  INDEX `AuthorFKbi` (`AuthorFKbi` ASC),
  INDEX `BookFormatFKBi` (`BookFormatFKbi` ASC),
  INDEX `SeriesFKBi` (`SeriesFKBi` ASC),
  INDEX `TitleFKbi` (`TitleFKbi` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`forsale`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`forsale` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`forsale` (
  `BookFKfs` INT(10) UNSIGNED NOT NULL,
  `IsForSale` TINYINT(4) NOT NULL DEFAULT '0',
  `AskingPrice` DOUBLE NOT NULL DEFAULT '0',
  `EstimatedValue` DOUBLE NOT NULL DEFAULT '0',
  PRIMARY KEY (`BookFKfs`),
  INDEX `BookFKfs` (`BookFKfs` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`haveread`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`haveread` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`haveread` (
  `BookFKhr` INT(10) UNSIGNED NOT NULL,
  `HaveReadBook` TINYINT(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`BookFKhr`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`isbn`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`isbn` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`isbn` (
  `BookFKiSBN` INT(10) UNSIGNED NOT NULL,
  `ISBNumber` VARCHAR(32) NULL DEFAULT NULL,
  PRIMARY KEY (`BookFKiSBN`),
  INDEX `ISBNumber` (`ISBNumber` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`owned`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`owned` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`owned` (
  `BookFKo` INT(10) UNSIGNED NOT NULL,
  `IsOwned` TINYINT(4) NOT NULL,
  `IsWishListed` TINYINT NOT NULL,
  PRIMARY KEY (`BookFKo`),
  INDEX `BookFKo` (`BookFKo` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`publishinginfo`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`publishinginfo` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`publishinginfo` (
  `BookFKPubI` INT(10) UNSIGNED NOT NULL COMMENT 'Foreign Key into the Book Info Table.',
  `Copyright` VARCHAR(4) NOT NULL,
  `Edition` INT(10) UNSIGNED NULL DEFAULT NULL,
  `Publisher` VARCHAR(45) NULL DEFAULT NULL,
  `OutOfPrint` TINYINT(4) NULL DEFAULT NULL COMMENT 'Is the book still being printed or has it lapsed.',
  `Printing` INT(10) UNSIGNED NULL DEFAULT NULL COMMENT 'A book may be printed may times. This will indicate which printing it is. Check the back of the title page.',
  PRIMARY KEY (`BookFKPubI`),
  INDEX `BookFKPubI` (`BookFKPubI` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`purchaseinfo`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`purchaseinfo` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`purchaseinfo` (
  `BookFKPurI` INT(10) UNSIGNED NOT NULL,
  `PurchaseDate` DATE NULL DEFAULT NULL,
  `ListPrice` DOUBLE NULL DEFAULT NULL,
  `PaidPrice` DOUBLE NULL DEFAULT NULL,
  `Vendor` VARCHAR(64) NULL DEFAULT NULL,
  PRIMARY KEY (`BookFKPurI`),
  INDEX `BookFKPurI` (`BookFKPurI` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`series`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`series` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`series` (
  `idSeries` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `AuthorFK` INT(10) UNSIGNED NOT NULL COMMENT 'Foriegn Key into Author Table',
  `SeriesName` VARCHAR(128) NOT NULL,
  PRIMARY KEY (`idSeries`, `AuthorFK`, `SeriesName`),
  UNIQUE INDEX `idSeries_UNIQUE` (`idSeries` ASC),
  INDEX `AuthorFKs` (`AuthorFK` ASC),
  INDEX `SeriesTitle` (`SeriesName` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`signedbyauthor`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`signedbyauthor` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`signedbyauthor` (
  `BookFKsba` INT(10) UNSIGNED NOT NULL,
  `IsSignedByAuthor` TINYINT(4) NULL DEFAULT NULL,
  PRIMARY KEY (`BookFKsba`),
  INDEX `BookFKsba` (`BookFKsba` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`title`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`title` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`title` (
  `idTitle` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `TitleStr` VARCHAR(128) NOT NULL,
  PRIMARY KEY (`idTitle`, `TitleStr`),
  UNIQUE INDEX `idTitle_UNIQUE` (`idTitle` ASC),
  INDEX `TitleStr` (`TitleStr` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`volumeinseries`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`volumeinseries` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`volumeinseries` (
  `BookFKvs` INT(10) UNSIGNED NOT NULL,
  `SeriesFK` INT(10) UNSIGNED NOT NULL,
  `VolumeNumber` INT(10) UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY (`BookFKvs`),
  INDEX `BookFKvs` (`BookFKvs` ASC),
  INDEX `SeriesFKvs` (`SeriesFK` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

USE `booklibinventory` ;

-- -----------------------------------------------------
-- function findAuthorKey
-- -----------------------------------------------------

USE `booklibinventory`;
DROP function IF EXISTS `booklibinventory`.`findAuthorKey`;

DELIMITER $$
USE `booklibinventory`$$
CREATE FUNCTION `findAuthorKey`(
    firstName VARCHAR(20),
    lastName VARCHAR(20)
) RETURNS INT
BEGIN

    SET @authorKey = 0;

    SELECT COUNT(*) INTO @authorCount FROM authorstab;
    IF @authorCount > 0 THEN
        SELECT authorstab.idAuthors INTO @authorKey
            FROM authorstab
            WHERE authorsTab.LastName = lastName AND authorsTab.FirstName = firstName;
        IF @authorKey IS NULL THEN
            SET @authorKey = 0;
        END IF;
    END IF;

    RETURN @authorKey;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- function findBookKey
-- -----------------------------------------------------

USE `booklibinventory`;
DROP function IF EXISTS `booklibinventory`.`findBookKey`;

DELIMITER $$
USE `booklibinventory`$$
CREATE FUNCTION `findBookKey`(
    authorLast VARCHAR(20),
    authorFirst VARCHAR(20),
    titleStr VARCHAR(128),
    formatStr VARCHAR(45)
) RETURNS INT
BEGIN

    SET @bookKey = 0;

    SET @authorKey = findauthorKey(authorFirst, authorLast);

    SET @titleKey = findTitleKey(titleStr);

    SET @formatKey = findFormatKeyFromStr(formatStr);

    IF @authorKey > 0 AND @titleKey > 0 THEN
        SET @bookKey = findBookKeyFromKeys(@authorKey, @titleKey, @formatKey);
    END IF;

    RETURN @bookKey;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- function findBookKeyFast
-- -----------------------------------------------------

USE `booklibinventory`;
DROP function IF EXISTS `booklibinventory`.`findBookKeyFast`;

DELIMITER $$
USE `booklibinventory`$$
CREATE FUNCTION `findBookKeyFast`(
    authorLast VARCHAR(20),
    authorFirst VARCHAR(20),
    titleStr VARCHAR(128),
    formatStr VARCHAR(45)
) RETURNS INT
BEGIN

    /*
     * There may be multiple copies of a book in the library, one of each format.
     * Specifying the format makes it distinct.
     */

    SELECT BKI.idBookInfo INTO @bookKey FROM bookinfo as BKI
        INNER JOIN authorsTab AS a ON a.idAuthors = BKI.AuthorFKbi
        INNER JOIN title AS t ON t.idTitle = BKI.TitleFKbi
        INNER JOIN bookformat AS bf ON bf.idFormat = BKI.BookFormatFKBi
        WHERE a.LastName = authorLast AND a.FirstName = authorFirst AND t.TitleStr = titleStr and bf.FormatName = formatStr;

    IF @bookKey IS NULL THEN
        SET @bookKey = 0;
    END IF;


    RETURN @bookKey;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- function findBookKeyFromKeys
-- -----------------------------------------------------

USE `booklibinventory`;
DROP function IF EXISTS `booklibinventory`.`findBookKeyFromKeys`;

DELIMITER $$
USE `booklibinventory`$$
CREATE FUNCTION `findBookKeyFromKeys`(
    authorKey INT,
    titleKey INT,
    formatKey INT
) RETURNS INT
BEGIN

    SET @bookKey = 0;

    IF authorKey > 0 AND titleKey > 0 then
        SELECT bookinfo.idBookInfo INTO @bookKey 
            FROM BookInfo 
            WHERE bookinfo.AuthorFKbi = authorKey AND bookinfo.TitleFKbi = titleKey AND bookinfo.BookFormatFKbi = formatKey;
        IF @bookKey IS NULL THEN
            SET @bookKey = 0;
        END IF;
    END IF;

    RETURN @bookKey;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- function findTitleKey
-- -----------------------------------------------------

USE `booklibinventory`;
DROP function IF EXISTS `booklibinventory`.`findTitleKey`;

DELIMITER $$
USE `booklibinventory`$$
CREATE FUNCTION `findTitleKey`(
    TitleStr VARCHAR(128)
) RETURNS INT
BEGIN

    SELECT title.idTitle INTO @titleKey FROM title WHERE title.TitleStr = TitleStr;
    IF @titleKey IS NULL THEN
        SET @titleKey = 0;
    END IF;

    RETURN @titleKey;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- function insertTitleIfNotExist
-- -----------------------------------------------------

USE `booklibinventory`;
DROP function IF EXISTS `booklibinventory`.`insertTitleIfNotExist`;

DELIMITER $$
USE `booklibinventory`$$
CREATE FUNCTION `insertTitleIfNotExist`(
    titleStr VARCHAR(128)
) RETURNS INT
BEGIN

    SET @titleKey = findTitleKey(titleStr);

    if @titleKey < 1 THEN
        INSERT INTO title (title.TitleStr) VALUES(titleStr);
        SET @titleKey := LAST_INSERT_ID();
    END IF;

    RETURN @titleKey;
END$$

DELIMITER ;


/*
 * Data inserts, deletions and updates.
 */

-- -----------------------------------------------------
-- procedure UpdateAuthor
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`UpdateAuthor`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `UpdateAuthor`(
    IN LastName VARCHAR(20),
    IN FirstName VARCHAR(20),
    IN MiddleName VARCHAR(20),
    IN DOB VARCHAR(4),
    IN DOD VARCHAR(4)
)
BEGIN

    UPDATE authorstab 
        SET 
            authorstab.MiddleName = MiddleName,
            authorstab.YearOfBirth = DOB,
            authorstab.YearOfDeath = DOD
        WHERE authorstab.LastName = LastName AND authorstab.FirstName = FirstName;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure addAuthor
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`addAuthor`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `addAuthor`(
    IN authorLastName VARCHAR(20),
    IN authorFirstName VARCHAR(20),
    IN authorMiddleName VARCHAR(20),
    IN dob VARCHAR(4),
    IN dod VARCHAR(4)
)
BEGIN

    INSERT INTO authorstab (authorstab.LastName, authorstab.FirstName, authorstab.MiddleName, authorstab.YearOfBirth, authorstab.YearOfDeath)
        VALUES(authorLastName, authorFirstName, authorMiddleName, dob, dod);
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure addBookToLibrary
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`addBookToLibrary`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `addBookToLibrary`
(
    IN categoryName VARCHAR(45),
    IN authorLastName VARCHAR(20),
    IN authorFirstName VARCHAR(20),
    IN titleStr VARCHAR(128), 
    IN bookFormatStr VARCHAR(45),
    IN copyright VARCHAR(4),
    IN edition INT,
    IN printing INT,
    IN publisher VARCHAR(45),
    IN outOfPrint TINYINT,
    IN seriesName VARCHAR(128),
    IN volumeNumber INT,
    IN iSBNumber VARCHAR(32),
    IN iSignedByAuthor TINYINT,
    IN isOwned TINYINT,
    IN isWishListed TINYINT,
    IN isForSale TINYINT,
    IN askingPrice DOUBLE,
    IN estimatedValue DOUBLE,
    IN haveRead TINYINT,
    IN bookDescription VARCHAR(1024),
    OUT bookKey INT
)
BEGIN

    -- All book data except for purchasing data will be added directly or indirectly from this procedure.
    -- Purchasing data will be handled outside of this procedure because the book may be added to a wishlist
    -- instead of added to the library.
    -- Each independent portion of the data will have it's own add procedure that will be called here.

    SET @titleKey = 0, @formatKey = 0, @authorKey = 0, @seriesKey = 0;

    SET @authorKey = findAuthorKey(authorFirstName, authorLastName);

    -- If the author isn't found then the user has to add the author before they add any books or
    -- Series by the author.
    if @authorKey > 0 then
        SET @formatKey = findFormatKeyFromStr(BookFormatStr);
        IF @formatKey > 0 THEN
            SET @seriesKey = findSeriesKeyByAuthKeyTitle(@authorKey, SeriesName);
            SET @titleKey = insertTitleIfNotExist(titleStr);
            SET @categoryKey = findCategoryKeyFromStr(categoryName);

            SET bookKey = findBookKeyFromKeys(@authorKey, @titleKey, @formatKey);
            IF bookKey < 1 THEN
                -- Don't add a book if it is already in the library. There will be special cases such as when a book has been signed by the author
                -- but these will be added later.
                INSERT INTO bookinfo (bookinfo.AuthorFKbi, bookinfo.TitleFKbi, bookinfo.CategoryFKbi, bookinfo.BookFormatFKbi, bookinfo.SeriesFKbi)
                    VALUES (@authorKey, @titleKey, @categoryKey, @formatKey, @seriesKey);
                SET bookKey := LAST_INSERT_ID();

                CALL insertOrUpdatePublishing(bookKey, copyright, edition, printing, publisher, outOfPrint);
                IF iSBNumber IS NOT NULL OR LENGTH(iSBNumber) > 1 THEN
                    -- Mass Market Paperback Books older than 1985 may not have an isbn printed on them any where.
                    CALL insertOrUpdateISBN(bookKey, iSBNumber);
                END IF;
                CALL insertOrUpdateOwned(bookKey, isOwned, isWishListed);
                CALL insertOrUpdateHaveRead(bookKey, haveRead);
                CALL insertOrUpdateVolumeInSeries(bookKey, volumeNumber, @seriesKey);
                IF isOwned > 0 THEN
                    CALL insertOrUpdateForSale(bookKey, isForSale, askingPrice, estimatedValue);
                END IF;
                CALL insertOrUpdateIsSignedByAuthor(bookKey, iSignedByAuthor);
                IF bookDescription IS NOT NULL OR LENGTH(bookDescription) > 0 THEN
                    -- Try to save space if there is no description.
                    CALL insertOrUpdateSynopsis(bookKey, bookDescription);
                END IF;
            END IF;

        END IF;
    END IF;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure buyBook
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`buyBook`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `buyBook`
(
    IN categoryName VARCHAR(45),
    IN authorLastName VARCHAR(20),
    IN authorFirstName VARCHAR(20),
    IN titleStr VARCHAR(128), 
    IN bookFormatStr VARCHAR(45),
    IN copyright VARCHAR(4),
    IN edition INT,
    IN printing INT,
    IN publisher VARCHAR(45),
    IN outOfPrint TINYINT,
    IN seriesName VARCHAR(128),
    IN volumeNumber INT,
    IN iSBNumber VARCHAR(32),
    IN iSignedByAuthor TINYINT,
    IN bookDescription VARCHAR(1024),
    IN purchaseDate DATE,
    IN listPrice DOUBLE,
    IN pricePaid DOUBLE,
    IN vendor VARCHAR(64),
    OUT bookKey INT    -- allows the calling program or procedure to test for failure.
)
BEGIN

    SET @estimatedValue = listPrice - 1.00;

    SET @IsBookAlreadyInDB = findBookKeyFast(authorLastName, authorFirstName, TitleStr, bookFormatStr);
    IF @IsBookAlreadyInDB < 1 THEN
    -- The book was not already read or wishlisted.
    -- Some fields such as IsOwned are added by default because the book was purchased.
        CALL addBookToLibrary(
            categoryName,
            authorLastName,
            authorFirstName,
            titleStr, 
            bookFormatStr,
            copyright,
            edition,
            printing,
            publisher,
            outOfPrint,
            seriesName,
            volumeNumber,
            iSBNumber,
            iSignedByAuthor,
            1,  -- IsOwned
            0,  -- IsWishlisted
            0,  -- IsForsale
            @estimatedValue,  -- Asking Price
            @estimatedValue,  -- Estimated Value
            0,  -- HaveReadBook This is assumed to be false, this might be a bug.
            bookDescription,
            bookKey
        );
        IF bookKey IS NOT NULL AND bookKey > 0 THEN
            CALL insertOrUpdatePurchaseInfo(bookKey, purchaseDate, listPrice, pricePaid, vendor);
        END IF;
    ELSE
        SET bookKey = @IsBookAlreadyInDB;
        -- The book was wishlisted or already read, update any changes.
        CALL insertOrUpdatePurchaseInfo(bookKey, purchaseDate, listPrice, pricePaid, vendor);
        CALL insertOrUpdatePublishing(bookKey, copyright, edition, printing, publisher, outOfPrint);
        CALL insertOrUpdateOwned(bookKey, 1, 0);
        CALL insertOrUpdateForSale(bookKey, 0, @estimatedValue, @estimatedValue);
        CALL insertOrUpdateIsSignedByAuthor(bookKey, iSignedByAuthor);
        IF iSBNumber IS NOT NULL OR LENGTH(iSBNumber) > 1 THEN
            -- Mass Market Paperback Books older than 1985 may not have an isbn printed on them any where.
            CALL insertOrUpdateISBN(bookKey, iSBNumber);
        END IF;
        IF bookDescription IS NOT NULL OR LENGTH(bookDescription) > 0 THEN
            -- Try to save space if there is no description.
            CALL insertOrUpdateSynopsis(bookKey, bookDescription);
        END IF;
    END IF;


END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure deleteAuthor
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`deleteAuthor`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `deleteAuthor`
(
    IN authorLast VARCHAR(20),
    IN authorFirst VARCHAR(20),
    IN authorMiddle VARCHAR(20)
)
BEGIN
    -- This procedure deletes everything associated with the specified author
    -- including books, series and volumes in series. It affects almost every table
    -- in this database.
    -- Do not delete formats and categories.

    DELETE a, BKI, s, v, i, sba, pub, pur, o, fs, hr, BDesk
        FROM authorstab AS a 
        LEFT JOIN series AS s ON s.AuthorFK = a.idAuthors
        LEFT JOIN volumeinseries AS v ON v.SeriesFK = s.idSeries
        INNER JOIN bookinfo AS BKI ON BKI.AuthorFKbi = a.idAuthors
        LEFT JOIN isbn AS i ON i.BookFKiSBN = BKI.idBookInfo
        LEFT JOIN signedbyauthor AS sba ON sba.BookFKsba = BKI.idBookInfo
        LEFT JOIN publishinginfo AS pub ON pub.BookFKPubI = BKI.idBookInfo
        LEFT JOIN purchaseinfo AS pur ON pur.BookFKPurI = BKI.idBookInfo
        LEFT JOIN owned AS o ON o.BookFKo = BKI.idBookInfo
        LEFT JOIN forsale AS fs ON fs.BookFKfs = BKI.idBookInfo
        LEFT JOIN haveread AS hr ON hr.BookFKhr = BKI.idBookInfo
        LEFT JOIN bksynopsis AS BDesk ON BDesk.BookFKbd = BKI.idBookInfo 
        WHERE a.LastName = authorLast AND a.FirstName = authorFirst AND a.MiddleName = authorMiddle;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure deleteBook
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`deleteBook`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `deleteBook`
(
    IN authorLast VARCHAR(20),
    IN authorFirst VARCHAR(20),
    IN titleStr VARCHAR(128),
    IN formatStr VARCHAR(45)
)
BEGIN

    -- Do not delete authors, titles, series, formats or categories. These may be shared with other books.

    DELETE BKI, i, sba, pub, pur, v, o, fs, hr, BDesk
        FROM bookinfo AS BKI 
        INNER JOIN authorsTab AS a ON a.idAuthors = BKI.AuthorFKbi
        INNER JOIN title AS t ON t.idTitle = BKI.TitleFKbi
        INNER JOIN bookformat AS bf ON bf.idFormat = BKI.BookFormatFKBi
        LEFT JOIN isbn AS i ON i.BookFKiSBN = BKI.idBookInfo
        LEFT JOIN signedbyauthor AS sba ON sba.BookFKsba = BKI.idBookInfo
        LEFT JOIN publishinginfo AS pub ON pub.BookFKPubI = BKI.idBookInfo
        LEFT JOIN purchaseinfo AS pur ON pur.BookFKPurI = BKI.idBookInfo
        LEFT JOIN volumeinseries AS v ON v.BookFKvs = BKI.idBookInfo
        LEFT JOIN owned AS o ON o.BookFKo = BKI.idBookInfo
        LEFT JOIN forsale AS fs ON fs.BookFKfs = BKI.idBookInfo
        LEFT JOIN haveread AS hr ON hr.BookFKhr = BKI.idBookInfo
        LEFT JOIN bksynopsis AS BDesk ON BDesk.BookFKbd = BKI.idBookInfo 
        WHERE a.LastName = authorLast AND a.FirstName = authorFirst AND t.TitleStr = titleStr and bf.FormatName = formatStr;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure insertOrUpdatePublishing
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`insertOrUpdatePublishing`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `insertOrUpdatePublishing` 
(
    IN bookKey INT,
    IN copyright VARCHAR(4),
    IN edition INT,
    IN printing INT,
    IN publisher VARCHAR(45),
    IN outOfPrint TINYINT
)
BEGIN

   --  DECLARE testCopyright VARCHAR(4);

    SET @testKey = NULL;
    SELECT publishinginfo.Copyright INTO @testCopyright FROM publishinginfo WHERE publishinginfo.BookFKPubI = bookKey;

    IF @testCopyright IS NULL THEN
        INSERT INTO publishinginfo (
                publishinginfo.BookFKPubI,
                publishinginfo.Copyright,
                publishinginfo.Edition,
                publishinginfo.Printing,
                publishinginfo.Publisher,
                publishinginfo.OutOfPrint
            )
            VALUES(
                bookKey,
                copyright,
                edition,
                printing,
                publisher,
                outOfPrint
            )
        ;
    ELSE
        UPDATE publishinginfo
            SET
                publishinginfo.Copyright = copyright,
                publishinginfo.Edition = edition,
                publishinginfo.Printing = printing,
                publishinginfo.Publisher = publisher,
                publishinginfo.OutOfPrint = outOfPrint
            WHERE publishinginfo.BookFKPubI = bookKey;
    END IF;
END$$

DELIMITER ;
-- -----------------------------------------------------
-- procedure addCategory
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`addCategory`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `addCategory`
(
    categoryName VARCHAR(45)
)
BEGIN

    SET @categoryKey = NULL;

    SELECT bookcategories.idBookCategories INTO @categoryKey
        FROM bookcategories
        WHERE bookcategories.CategoryName = categoryName;

    -- Prevent adding the same category again to avoid breaking the unique key structure.

    IF @categoryKey IS NULL THEN
        INSERT INTO bookcategories (bookcategories.CategoryName) VALUES(categoryName);
    END IF;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure addFormat
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`addFormat`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `addFormat` (IN bookFormatStr VARCHAR(45))
BEGIN

    SET @formatKey = findFormatKeyFromStr(bookFormatStr);

    -- Prevent adding the same format again to avoid breaking the unique key structure.
    IF @formatKey < 1 THEN
        INSERT INTO bookformat (bookformat.FormatName) VALUES(bookFormatStr);
    END IF;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure getAllBooks
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`getAllBooks`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `getAllBooks`()
BEGIN

    SELECT
            a.LastName,
            a.FirstName,
            t.TitleStr,
            bf.FormatName,
            BCat.CategoryName,
            i.ISBNumber,
            pub.Copyright,
            pub.Edition,
            pub.Publisher,
            pub.OutOfPrint,
            pub.Printing,
            s.SeriesName,
            v.VolumeNumber,
            pur.PurchaseDate,
            pur.ListPrice,
            pur.PaidPrice,
            pur.Vendor,
            sba.IsSignedByAuthor,
            o.IsOwned,
            o.IsWishListed,
            hr.HaveReadBook,
            fs.IsForSale,
            fs.AskingPrice,
            fs.EstimatedValue,
            BDesk.StoryLine
        FROM bookinfo AS BKI 
        INNER JOIN authorsTab AS a ON a.idAuthors = BKI.AuthorFKbi
        INNER JOIN title AS t ON t.idTitle = BKI.TitleFKbi
        INNER JOIN bookformat AS bf ON bf.idFormat = BKI.BookFormatFKBi
        INNER JOIN bookcategories AS BCat ON BCat.idBookCategories = BKI.CategoryFKbI
        LEFT JOIN isbn AS i ON i.BookFKiSBN = BKI.idBookInfo
        LEFT JOIN signedbyauthor AS sba ON sba.BookFKsba = BKI.idBookInfo
        LEFT JOIN publishinginfo AS pub ON pub.BookFKPubI = BKI.idBookInfo
        LEFT JOIN purchaseinfo AS pur ON pur.BookFKPurI = BKI.idBookInfo
        LEFT JOIN series AS s ON s.idSeries = BKI.SeriesFKbi
        LEFT JOIN volumeinseries AS v ON v.BookFKvs = BKI.idBookInfo
        LEFT JOIN owned AS o ON o.BookFKo = BKI.idBookInfo
        LEFT JOIN forsale AS fs ON fs.BookFKfs = BKI.idBookInfo
        LEFT JOIN haveread AS hr ON hr.BookFKhr = BKI.idBookInfo
        LEFT JOIN bksynopsis AS BDesk ON BDesk.BookFKbd = BKI.idBookInfo 
        ORDER BY BCat.CategoryName, a.LastName, a.FirstName, s.SeriesName, v.VolumeNumber, t.TitleStr;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure getAllBookCategoriesWithKeys
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`getAllBookCategoriesWithKeys`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `getAllBookCategoriesWithKeys` ()
BEGIN

/*
 * Example usage would be to get all the categories to CREATE a control that embeds the primary key rather than the text.
 */

    SELECT bookcategories.CategoryName, bookcategories.idBookCategories FROM bookcategories;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure getAllBookFormatsWithKeys
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`getAllBookFormatsWithKeys`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `getAllBookFormatsWithKeys`()
BEGIN

/*
 * Example usage would be to get all the formats to CREATE a control embeds the primary key rather than the text.
 */

    SELECT bookformat.FormatName, bookformat.idFormat FROM bookformat;

END$$

DELIMITER ;

/*
 * Start of functions that allow the user to update books in a limited manner.
 */

-- -----------------------------------------------------
-- procedure putBookUpForSale
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`putBookUpForSale`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `putBookUpForSale`
(
    IN authorFirstName VARCHAR(20),
    IN authorLastName VARCHAR(20),
    IN bookTitle VARCHAR(128),
    IN bookFormat VARCHAR(45),
    IN askingPrice DOUBLE,
    IN estimatedValue DOUBLE
)
BEGIN

    SET @isForSale = 1;

    SET @bookKey = findBookKeyFast(authorLastName, authorFirstName, bookTitle, bookFormat);

    CALL insertOrUpdateForSale(@bookKey, @isForSale, askingPrice, estimatedValue);

END$$

DELIMITER ;

/*
 * Once only code called during installation or testing.
 */

-- -----------------------------------------------------
-- procedure initBookInventoryTool
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`initBookInventoryTool`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `initBookInventoryTool` ()
BEGIN

-- Initialize some basic formats, user can add more later.
    CALL addFormat('Hardcover');
    CALL addFormat('Trade Paperback');
    CALL addFormat('Mass Market Paperback');
    CALL addFormat('eBook PDF');
    CALL addFormat('eBook Kindle');
    CALL addFormat('eBook iBooks');
    CALL addFormat('eBook EPUB');
    CALL addFormat('eBook HTML');

-- Initialize some basic categories, user can add more later.
    CALL addCategory('Non-Fiction');
    CALL addCategory('Non-Fiction: Biography');
    CALL addCategory('Non-Fiction: Biology');
    CALL addCategory('Non-Fiction: Computer');
    CALL addCategory('Non-Fiction: Electrical Engineering');
    CALL addCategory('Non-Fiction: History');
    CALL addCategory('Textbook');
    CALL addCategory('Poetry');
    CALL addCategory('Art');
    CALL addCategory('Dictionary');
    CALL addCategory('Encyclopedia');
    CALL addCategory('Fiction');
    CALL addCategory('Fiction: Anime');
    CALL addCategory('Fiction: Fantasy');
    CALL addCategory('Fiction: Horror');
    CALL addCategory('Fiction: Romance');
    CALL addCategory('Fiction: Science Fiction');
    CALL addCategory('Fiction: Western');

END$$

DELIMITER ;

/*
 * Unit testing procedures.
 */

-- -----------------------------------------------------
-- procedure zzzUnitTestAddAuthors
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`zzzUnitTestAddAuthors`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `zzzUnitTestAddAuthors` ()
BEGIN
SET @procName = 'zzzUnitTestAddAuthors';

    CALL addAuthor('Heinlein', 'Robert', 'Anson', '1907', '1988');
    CALL addAuthor('Asimov', 'Isaac', NULL, '1920', '1992');
    CALL addAuthor('Clarke', 'Arthur', 'Charles', '1917', '2008');
    CALL addAuthor('Le Guin', 'Ursula', 'Kroeber', '1929', '2018');
    CALL addAuthor('Bradbury', 'Ray', 'Douglas ', '1920', '2012');
    CALL addAuthor('Dick', 'Philip', 'Kindred', '1928', '1982');
    CALL addAuthor('Wells', 'Herbert', 'George', '1866', '1946');
    CALL addAuthor('Silverberg', 'Robert', NULL, '1935', NULL);
    CALL addAuthor('Zimmer Bradley', 'Marion', 'Eleanor', '1930', '1999');
    CALL addAuthor('Norton', 'Andre', 'Alice', '1912', '2005');
    CALL addAuthor('Drake', 'David', NULL, '1945', NULL);
    CALL addAuthor('Weber', 'David', 'Mark', '1952', NULL);
    CALL addAuthor('Baxter', 'Stephen', NULL, '1957', NULL);
    CALL addAuthor('Knuth', 'Donald', 'Ervin', '1938', NULL);

    IF (SELECT COUNT(*) FROM authorstab) != 14 THEN
        SELECT @procName, COUNT(*) FROM series;
        SELECT * FROM series;
    END IF;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure zzzUnitTestAddAuthorSeries
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`zzzUnitTestAddAuthorSeries`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `zzzUnitTestAddAuthorSeries` ()
BEGIN
SET @procName = 'zzzUnitTestAddAuthorSeries';

    CALL addAuthorSeries('David', 'Weber', 'Safehold');
    CALL addAuthorSeries('David', 'Weber', 'Honor Harrington');
    CALL addAuthorSeries('David', 'Weber', 'Honorverse');
    CALL addAuthorSeries('Marion', 'Zimmer Bradley', 'Darkover');
    CALL addAuthorSeries('Isaac', 'Asimov', 'Foundation');
    CALL addAuthorSeries('Stephen', 'Baxter', 'Northland');
    CALL addAuthorSeries('Donald', 'Knuth', 'The Art of Computer Programming');
-- The follow statement should fail to insert the series since John Ringo has not been added to authorstab.
    CALL addAuthorSeries('John', 'Ringo', 'Kildar');

    IF (SELECT COUNT(*) FROM series) != 7 THEN
        SELECT @procName, COUNT(*) FROM series;
        SELECT * FROM series;
    END IF;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure zzzUnitTestAddBookToLibrary
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`zzzUnitTestAddBookToLibrary`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `zzzUnitTestAddBookToLibrary` ()
BEGIN
/*
 * The following procedures are tested by this procedure.
 *      addBookToLibrary
 *      insertOrUpdatePublishing
 *      insertOrUpdateOwned
 *      insertOrUpdateHaveRead
 *      insertOrUpdateVolumeInSeries
 *      insertOrUpdateForSale()
 *      insertOrUpdateIsSignedByAuthor
 *      insertOrUpdateSynopsis
 *      insertOrUpdateISBN
 *      insertOrUpdatePurchaseInfo
 *
 * The following functions are tested by this procedure:
 *      findAuthorKey
 *      findFormatKeyFromStr
 *      findSeriesKeyByAuthKeyTitle
 *      insertTitleIfNotExist
 *      findCategoryKeyFromStr
 *      findBookKeyFromKeys
 *
 */

    DECLARE bookKey INT;

SET @procName = 'zzzUnitTestAddBookToLibrary';

    CALL addBookToLibrary('Fiction: Science Fiction', 'Weber', 'David', 'On Basilisk Station',  'Mass Market Paperback', '1993', 1, 9, 'Baen Books', 0, 'Honor Harrington', 1,
        '0-7434-3571-0', 0, 1, 0, 0, 8.99, 8.99, 1, 'bookDescription', bookKey);
    IF (bookKey != 1) THEN
        SELECT @procName, bookKey;
        SELECT COUNT(*) FROM bookinfo;
    END IF;

    CALL addBookToLibrary('Fiction: Science Fiction', 'Weber', 'David', 'Honor of the Queen',  'Mass Market Paperback', '1993', 1, 10, 'Baen Books', 0, 'Honor Harrington', 2,
        '978-0-7434-3572-7', 0, 1, 0, 0, 6.99, 6.99, 1, NULL, bookKey);
    IF (bookKey != 2) THEN
        SELECT @procName, bookKey;
        SELECT COUNT(*) FROM bookinfo;
    END IF;

    CALL addBookToLibrary('Fiction: Science Fiction', 'Weber', 'David', 'Short Victorious War',  'Mass Market Paperback', '1994', 1, 8, 'Baen Books', 0, 'Honor Harrington', 3,
        '0-7434-3573-7', 0, 1, 0, 0, 6.99, 6.99, 1, NULL, bookKey);
    IF (bookKey != 3) THEN
        SELECT @procName, bookKey;
        SELECT COUNT(*) FROM bookinfo;
    END IF;

    CALL addBookToLibrary('Fiction: Science Fiction', 'Weber', 'David', 'Field of Dishonor',  'Mass Market Paperback', '1994', 1, 6, 'Baen Books', 0, 'Honor Harrington', 4,
        '0-7434-3574-5', 0, 1, 0, 0, 7.99, 7.99, 1, NULL, bookKey);
    IF (bookKey != 4) THEN
        SELECT @procName, bookKey;
        SELECT COUNT(*) FROM bookinfo;
    END IF;

    CALL addBookToLibrary('Fiction: Science Fiction', 'Norton', 'Andre', 'Star Guard',  'Mass Market Paperback', '1955', 1, NULL, 'Harcourt', 0, NULL, NULL,
        NULL, 0, 0, 1, NULL, NULL, NULL, 1, NULL, bookKey);
    IF (bookKey != 5) THEN
        SELECT @procName, bookKey;
        SELECT COUNT(*) FROM bookinfo;
    END IF;

    -- The following statement should fail to add a book since David Brin is not in authorstab.
    -- The failure is indicated by bookKey being zero.
    CALL addBookToLibrary('Fiction: Science Fiction', 'Brin', 'David', 'Uplift War',  'Hard Cover', '1987', 1, 1, 'Phantasia Press', 0, NULL, NULL,
        0-932096-44-1, 1, 1, 0, 0, 100.00, 100.00, 1, NULL, bookKey);
    IF (bookKey != 0) THEN
        SELECT @procName, bookKey;
        SELECT COUNT(*) FROM bookinfo;
    END IF;
    IF (SELECT COUNT(*) FROM bookinfo) != 5 THEN
        SELECT @procName, COUNT(*) FROM bookInfo;
        SELECT * FROM bookInfo;
    END IF;

    IF (SELECT COUNT(*) FROM publishinginfo) != 5 THEN
        SELECT @procName, COUNT(*) FROM publishinginfo;
        SELECT * FROM publishinginfo;
    END IF;

    IF (SELECT COUNT(*) FROM bksynopsis) != 1 THEN
        SELECT @procName, COUNT(*) FROM bksynopsis;
        SELECT * FROM bksynopsis;
    END IF;

    IF (SELECT COUNT(*) FROM forsale) != 4 THEN
        SELECT @procName, COUNT(*) FROM forsale;
        SELECT * FROM forsale;
    END IF;

    IF (SELECT COUNT(*) FROM haveread) != 5 THEN
        SELECT @procName, COUNT(*) FROM haveread;
        SELECT * FROM haveread;
    END IF;

    IF (SELECT COUNT(*) FROM owned) != 5 THEN
        SELECT @procName, COUNT(*) FROM owned;
        SELECT * FROM owned;
    END IF;

    IF (SELECT COUNT(*) FROM signedbyauthor) != 5 THEN
        SELECT @procName, COUNT(*) FROM signedbyauthor;
        SELECT * FROM signedbyauthor;
    END IF;

    IF (SELECT COUNT(*) FROM isbn) != 4 THEN
        SELECT @procName, COUNT(*) FROM isbn;
        SELECT * FROM isbn;
    END IF;

    IF (SELECT COUNT(*) FROM purchaseinfo) != 0 THEN
        SELECT @procName, COUNT(*) FROM purchaseinfo;
        SELECT * FROM purchaseinfo;
    END IF;

    IF (SELECT COUNT(*) FROM title) != 5 THEN
        SELECT @procName, COUNT(*) FROM title;
        SELECT * FROM title;
    END IF;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure zzzUnitTestUserUpdates
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`zzzUnitTestUserUpdates`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `zzzUnitTestUserUpdates` ()
BEGIN

/*
 * This procedure tests the buyBook procedure. Since the buyBook procedure call addBookToLibrary, everything tested
 * by zzzUnitTestAddBookToLibrary is also tested by this procedure.
 *
 */

    DECLARE bookKey INT;
    SET @procName = 'zzzUnitTestUserUpdates';

    SELECT COUNT(*) INTO @forSaleCount FROM forsale WHERE forsale.IsForSale = 1;
    CALL putBookUpForSale('David', 'Weber', 'Honor of the Queen', 'Mass Market Paperback', 10.99, 7.99);
    IF (SELECT COUNT(*) FROM forsale WHERE forsale.IsForSale = 1) != (@forSaleCount + 1) THEN
        SELECT @procName, COUNT(*) FROM forsale;
        SELECT * FROM forsale;
    END IF;
    SELECT COUNT(*) INTO @forSaleCount FROM forsale;
    -- CALL getAllBooksForSale();

    SELECT COUNT(*) INTO @haveReadCount FROM haveread WHERE haveread.HaveReadBook = 1;
    CALL finishedReadingBook('Stephen', 'Baxter', 'Stone Spring', 'Mass Market Paperback');
    CALL finishedReadingBook('Stephen', 'Baxter', 'Bronze Summer', 'Mass Market Paperback');
    IF (SELECT COUNT(*) FROM haveread WHERE haveread.HaveReadBook = 1) != (@haveReadCount + 2) THEN
        SELECT @procName, COUNT(*) FROM haveread;
        SELECT * FROM haveread;
    END IF;
    -- CALL getAllBooksThatWereRead();

    CALL bookSold('David', 'Weber', 'Honor of the Queen', 'Mass Market Paperback');
    IF (SELECT COUNT(*) FROM forsale) != (@forSaleCount - 1) THEN
        SELECT @procName, COUNT(*) FROM forsale;
        SELECT * FROM forsale;
    END IF;

    -- Test update buy buying wish listed book.
    Set @buyDate = CURDATE();
    CALL buyBook('Fiction: Science Fiction', 'Norton', 'Andre', 'Star Guard',  'Mass Market Paperback', '1955', 3, 4, 'Harcourt', 0, NULL, NULL,
        '978-0-345-35036-7', 0,  'Testing 1 2 3', @buyDate, 7.99, 7.99, 'Amazon', bookKey);

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure zzzUnitTestFunctions
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`zzzUnitTestFunctions`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `zzzUnitTestFunctions` ()
BEGIN

SET @procName = 'zzzUnitTestFunctions';

    /*
     * The functions not explicitly tested here are tested indirectly 
     * through the function calls here with the exception of insertTitleIfNotExist
     */

    SET @authorKey = findAuthorKey('Arthur','Clarke');
    IF @authorKey != 3 THEN
        SELECT @procName, @authorKey;
        SELECT authorstab.FirstName, authorstab.LastName FROM authorstab WHERE idAuthors = @authorKey;
    END IF;

    SET @bookKey = findBookKeyFast('Baxter', 'Stephen', 'Stone Spring', 'Mass Market Paperback');
    IF (@bookKey != 6) THEN
        SELECT @procName, @bookKey;
        SELECT * FROM bookinfo WHERE bookinfo.idBookInfo = @bookKey;
    END IF;

    SET @titleKey = findTitleKey('Star Guard');
    IF (@titleKey != 5) THEN
        SELECT @procName, @titleKey;
        SELECT * FROM title WHERE title.idTitle = @titleKey;
    END IF;

    SET @categoryKey = findCategoryKeyFromStr('Non-Fiction: Electrical Engineering');
    IF (@categoryKey != 5) THEN
        SELECT @procName, @categoryKey;
        SELECT * FROM bookcategories; -- WHERE bookcategories.idBookCategories = @categoryKey;
    END IF;

    SET @formatKey = findFormatKeyFromStr('Mass Market Paperback');
    IF (@formatKey != 3) THEN
        SELECT @procName, @formatKey;
        SELECT * FROM bookformat WHERE bookformat.idFormat = @formatKey;
    END IF;

    SET @seriesKey = findSeriesKey('David', 'Weber', 'Honorverse');
    IF (@seriesKey != 3) THEN
        SELECT @procName, @seriesKey;
        SELECT * FROM series WHERE series.idSeries = @seriesKey;
    END IF;

END$$

DELIMITER ;    
-- -----------------------------------------------------
-- procedure zzzUnitTestDelete
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`zzzUnitTestDelete`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `zzzUnitTestDelete` ()
BEGIN

    SELECT COUNT(*) INTO @bookCount FROM bookinfo;

    CALL deleteBook('Weber', 'David', 'Honor of the Queen', 'Mass Market Paperback');

    IF (SELECT COUNT(*) FROM bookinfo) != (@bookCount - 1) THEN
        SELECT * FROM bookinfo;
    END IF;
    SET @bookCount = @bookCount - 1;
    IF (SELECT COUNT(*) FROM isbn) > @bookCount THEN
        SELECT * FROM isbn;
    END IF;
    IF (SELECT COUNT(*) FROM signedbyauthor) > @bookCount THEN
        SELECT * FROM signedbyauthor;
    END IF;
    IF (SELECT COUNT(*) FROM haveread) > @bookCount THEN
        SELECT * FROM haveread;
    END IF;
    IF (SELECT COUNT(*) FROM forsale) > @bookCount THEN
        SELECT * FROM forsale;
    END IF;

    SELECT COUNT(*) INTO @bookCount FROM bookinfo;
    SELECT COUNT(*) INTO @seriesCount FROM series;
    SELECT COUNT(*) INTO @authorCount FROM authorstab;

    CALL deleteAuthor('Knuth', 'Donald', 'Ervin');

    IF (SELECT COUNT(*) FROM bookinfo) != (@bookCount - 3) THEN
        SELECT * FROM bookinfo;
    END IF;
    IF (SELECT COUNT(*) FROM series) != (@seriesCount - 1) THEN
        SELECT * FROM series;
    END IF;
    IF (SELECT COUNT(*) FROM authorstab) != (@authorsCount - 1) THEN
        SELECT * FROM authors;
    END IF;
    SET @bookCount = @bookCount - 3;
    IF (SELECT COUNT(*) FROM haveread) > @bookCount THEN
        SELECT * FROM haveread;
    END IF;
    IF (SELECT COUNT(*) FROM forsale) > @bookCount THEN
        SELECT * FROM forsale;
    END IF;
    IF (SELECT COUNT(*) FROM owned) > @bookCount THEN
        SELECT * FROM owned;
    END IF;
    IF (SELECT COUNT(*) FROM purchaseinfo) > @bookCount THEN
        SELECT * FROM purchaseinfo;
    END IF;
    IF (SELECT COUNT(*) FROM publishinginfo) > @bookCount THEN
        SELECT * FROM publishinginfo;
    END IF;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure zzzRunAllUnitTests
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`zzzRunAllUnitTests`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `zzzRunAllUnitTests` ()
BEGIN
    /*
     * The unit tests are in a specific order. Data from the early test procedures
     * is required by the later test procedures.
     *
     * The general functionality of the unit tests is to run the procedures or functions
     * and then test values that would be affected by the routine. If the test failed
     * then a select is run to show the error. No output means no errors.
     */

    SET @ShowAllResults = 1;

    CALL zzzUnitTestInitProcedure();
    CALL zzzUnitTestAddAuthors();
    CALL zzzUnitTestAddAuthorSeries();
    CALL zzzUnitTestAddBookToLibrary();
    CALL zzzUnitTestBuyBook();
    CALL zzzUnitTestFunctions();

    CALL addMoreBooksForInterst();

    -- Test all the data retrieval procedures to see that they return data rows.
    -- These tests by default will provide output.
    IF @showAllResults > 0 THEN
        CALL getAllBookFormatsWithKeys();
        CALL getAllBookCategoriesWithKeys();
        CALL getAllBooksInLib(); -- Test selecting all fields
        CALL getAllBooksByThisAuthor('Baxter', 'Stephen');
        CALL getAllWishListBooks();
        CALL getAllBooksThatWereRead();
        CALL getThisAuthorsData('Norton','Andre');
        CALL getAllSeriesByThisAuthor('Weber', 'David');
        CALL getAllSeriesData();
        CALL getAllAuthorsData();
        CALL getBookData('Weber', 'David', 'Honor of the Queen', 'Mass Market Paperback');
        CALL getAuthorDataByLastName('Asimov'); -- This could be changed if more authors are added, such as all the Greens.
        CALL getAllBooksSignedByAuthor();
    END IF;

    CALL zzzUnitTestUserUpdates();
    CALL getAllBooks(); -- Test selecting all fields all books
    CALL zzzUnitTestDelete ();
    CALL getAllBooks(); -- Test selecting all fields all books

END$$

DELIMITER ;

CALL booklibinventory.zzzRunAllUnitTests();

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

Answer 1

Problem Statement

Tiered Architecture

This database is the first part of the project, I couldn’t really create a friendly user interface until the database is working. Due to feature creep this database can now handle other kinds of books besides science fiction and fantasy.

The most important step when taking on a project as you have, is deciding the tiered architecture and which tier is responsible for what. You have decided to make a WPF application without using an ORM (out of scope for this question), and let the database store most of the business logic. And as you put it, feature creep is an issue you are likely to encounter when maintaining and extending this application.

Database-driven design

There are about 2790 lines of code and comments in this database. The first 240 lines are the data definitions of the tables. There are 1849 lines of stored procedures implementing the insert, update, delete and retrieval stored procedures. The last 701 line of code are unit tests in an attempt to make sure the stored procedures and functions work when I start developing the user interface.

The above statements show how hard it becomes maintaining business logic in the database. You should realise the implications of putting the business logic inside the database.

• your application depends on the database (what if you want to use a different db provider?)
• feature flags, configuration and business parameters need all be addressed in your stored procedures (this is much easier handled in code)
• code is much better suited to handle configurable validation rules, data loads depending on external context, state machines, design patterns, managing complex flow, and so on.

Author's intent

This is the first relational schema I have designed from scratch. A year ago I wouldn’t have used any stored procedures because I didn’t know about the benefits of using stored procedures.

I wonder what made you decide to use stored procedures. You could update your question with additional information regarding this choice, it won't invalidate my answer. And don't get me wrong, having some stored procedures is fine. I only have questions with implementing all application business logic in stored procedures. Through personal experience, I notice a change from database-driven to application-driven design in the companies I have worked or still do.

My proposed change in architecture

I would suggest making the software (a server component) your engine for defining business logic. At the back-end, use an ORM (EF, NHibernate, ..) to communicate to a database. The database should only provide stored procedures for exceptional scenario's where you are unable to map your code using ORM or where you are just not able to meet critical performance, memory, security requirements.

This does not mean your database should be an open, denormalized simple storage facility. You should still consider:

• creating reader / writer / admin user accounts for the software to work with
• proper normalisation of tables; denormalise only where benefits are clear (like reporting tables, views)
• smart use of indexes

If you can't change your design, or just feel good with the current architecture, that's fine. This does not influence the remainder of this review :)

I also wouldn't worry about data integrity when removing stored procedures. It is up to the business flow in the code to use Transactions to enforce data integrity.

---

Review

The first thing I notice in your script is commented credentials for database users. I am not sure whether this is dummy data, but you should never include sensitive user data in a script.

General Guidelines

• Don't restrict data types too much: LastName VARCHAR(20) NOT NULL this could get tricky if carry both your name as your spouse's name. What's wrong with VARCHAR(128)?
• Don't create combined primary keys when only one field is the primary key and the others are a (combined) surrogate unique key: PRIMARY KEY (idBookCategories, CategoryName) -> the PK is idBookCategories and CategoryName is a unique key. All your tables abuse the PK like this.
• Don't use attributes that appear unique to be part of a primary key: PRIMARY KEY (idAuthors, LastName, FirstName) -> LastName,FirstName should not be primary key, nor should it be unique.
• Don't use the type of constraint in the name of a field; instead favor the same name as the referenced field: CategoryFKbi -> idBookCategories not because of the camel casing, but because of using the same name as the referenced field; and also, FK should not be part of a column name.

Index Guide

• You have created indexes eagerly, but realize that each index has a performance impact on insert, update and delete statements. Try to avoid having too much indexes on large tables that get lots of crud operations.
• There is also a memory impact for each index. Since your database won't grow too much, this should not become a problem. Unless you hyper-optimize your database with indexes all over the place :)
• I would suggest the opposite and only make the least amount of indexes.
  • Primary and foreign keys
  • Fields that are part of a join in your top 5 queries that require high performance
• Be agile, you can always add indexes later on, once your database grows and you get some analytics information about crud operations.

Normalisation

• Your tables seem reasonably normalised. No point in partitioning some of the tables to meet NF5 or something like that. The important part is that each of the attributes in your tables depend on the primary key (well, what should be the PK after you refactor them to take out surrogate keys)
• Once you will implement reporting features, you will probably want to create some views, or maybe store certain data in de-normalised tables. But this is not yet in scope.

The problem with the incorrectly combined primary keys is as follows: PK (id, last name, first name): 2 authors with the same first and last name are not allowed by the system.

• John Smith [id= 1]
• John Smith [id= 794]

There is no reason not to allow duplicate names. Entities, such as author, are identified by their id, not their name combo. In the application you could always show John Smith vs John Smith (2).

This database uses the author title and format together as the identity of the book.

Would book revision n° be an interesting additional field (and part of PK) for defining a unique book?