How to create a MySQL Database for Book Inventory Management

Ryan Cheevers-Brown: Photo of the ISTS Cybersecurity Competition on (March 4-6, 2022) used on post including a sample mysql book database.

What is MySQL?

MySQL is a free and open-source relational database management system. This tutorial shows how to configure and modify a MySQL book database. If you are only interested in the sample MySQL book database, here is a link the downloadable file on my GitHub.

Sample MySQL Book Database

This database is based on createBOOK.sql by RIT Professor James Habermas. The original database, not included in this article, can be used with his permission. The modified database, included in this article, can be used with Gallucci’s and Professor Habermas’ permission.

Link to downloadable file: https://github.com/oliviagallucci/CreateBookMySQL 

Rough diagram

Red lines mean I am unsure of what syntax to use. That is what makes this diagram “rough.”

TableDescription
Book.. (1) uniquely identified by an InventoryID, and informally identified by an ISBN, (2) written by one primary
BookAuthor and zero or more secondary BookAuthors,
(3) categorized by subject matter Category, (4) published
by Publisher, and (5) reviewed in a given BookReview
BookAuthor.. individuals that are either the primary or secondary author(s) of a specific Book occurrence
Author.. individuals that are primary or secondary authors
BookReview.. the rating assigned to a specific Book occurrence by agiven Reviewer
Reviewer.. individuals that review Books and assign a rating to it
Users.. individuals that interact with the website and purchase books. Each user has a unique UserID and a set of Permissions identified by a RoleNumber.
Condition.. the set of Condition “rankings” (1..5, corresponding to“bad”, “poor”, “average”, “good”, “excellent,” and “new”) that can be assigned to an InventoryID
Category… the set of BOOK subject matter classifications
Publisher.. publishes BOOKs in a given U.S. State
State.. state codes and full state names
Inventory.. lists the books the store owns or owned at one point, assigning each book an InventoryID
PurchaseLog.. lists the userIDs that purchased an inventoryID
Permissions.. lists the possible Permissions that can be assigned to a user (1..4, corresponding to “Guest,” “RegisteredUser,” StoreOwner,” and “Admin”)

Key

Key source: Professor Jim Habermas

How to use MySQL?

What is verbose?

MySQL Shell logs information and can display it on the console. MySQL stores logs within an application log file. However, many people like to use it for debugging. As a result, MySQL developed a logging feature known as Verbose, where logs are displayed on the console and stored in the application file. Verbose can be activated using a command line option. 

Starting MySQL

MacOS

With verbose:

/usr/local/mysql/bin/mysql -u root -p -v

Without verbose:

/usr/local/mysql/bin/mysql -u root -p

Create alias: 

cd ~
open .zshrc

Add the following to ~/.zshrc:

# MySQL shortcuts
alias mysqlv="/usr/bin/mysql -u root -p -v"
alias mysqlw="/usr/bin/mysql -u root -p"

Close ~/.zshrc. Then, update your ~/.zshrc configuration source:

source .zshrc

Now, the mysqlv will run mysql with verbose, while mysqlw will run it without verbose. 

Windows

Windows is sensitive to capitalization! Thus, select may need to be SELECT in order to work properly.

Windows Menu → MySQL → MySQL Command Line Client

If prompted for a password, use the same password that was used to set up the MySQL server. 

Linux

Creating an alias:

cd ~
nano .bashrc

Add the following to ~/.bashrc:

# MySQL shortcuts
alias mysqlv='/usr/bin/mysql -u root -p -v'
alias mysqlw='/usr/bin/mysql -u root -p'

Close ~/.bashrc. Then, update your ~/.bashrc configuration source:

source ~/.bashrc

Now, the mysqlv will run mysql with verbose, while mysqlw will run it without verbose. 

Exiting MySQL

Linux and MacOS:

mysql > exit 

Windows, click the “x” in the top, right-hand corner. 

Database statements

Source

SOURCE [full_path_to_createBOOK.sql]; 

Show

SHOW databases;

Use

Source what you want to work with:

USE [DATABASE_NAME];

For this example, we want to type:

USE book; 

Tee the file

This step is unnecessary but can be helpful for debugging.

Create the Tee file (note that there is *no* ending semicolon):

TEE D:\full_path_fo_file\tee_file_name.txt

If the .txt file specified does not exist, the TEE command will create it for you. 

Stop the tee file:

notee

Comments

This step is unnecessary but can be helpful for debugging.

-- bleh blah blah 

Table statements

Show

Show all tables:

SHOW tables;

Describe

Shows the format of the table. 

DESC [table_name];

Select

All columns

* selects all fields

Examples
SELECT * FROM [table_name];
SELECT * FROM [table_name]\G

Some columns

SELECT [field] FROM [table_name];
SELECT ISBN FROM book;

Sorting

SELECT [field]
FROM [table_name]
ORDER BY {Column, Numerical Position, Alias Columns}[ASC | DESC]
SELECT ISBN, Category FROM book ORDER BY Category ASC;

Select books that have not been purchased: 

SELECT * FROM inventory WHERE Purchased=0;

Joins

Learn more about join here: https://www.w3schools.com/mysql/mysql_join.asp

Examples

See price and title of inventory item:

SELECT title, price FROM book JOIN inventory USING(ISBN) JOIN bookcondition USING(Ranks);

See book ranking:

SELECT name AS reviewerName, title, rating FROM book 
JOIN bookreview USING(ISBN) 
JOIN reviewer USING(reviewerID);

Insert

Insert allows you to add new information to a table. In order to add information properly, you need to follow the table structure or specify the values you want to add. 

Example

CREATE TABLE team5 (
    lastName VARCHAR(25) NOT NULL PRIMARY KEY, 
    gpa DECIMAL(3, 2) NULL, 
    age int NULL, 
    uid int NOT NULL AUTO_INCREMENT=1000000
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Important notes: Do not specify values for your auto increment fields.

Add the values:

 - - full table structure 
mysql > INSERT INTO team5 VALUES("smith", 4.00, 19); 
 - - specify values 
mysql > INSERT INTO team5 (lastName, age) VALUES("johnson", 22);
 - - full table structure 
mysql > INSERT INTO team5 VALUES("doe", 3.87, 20);

Resulting table is:

lastNamegpaageuid
smith4.00191000000
johnsonNULL221000001
doe3.87201000002

When should I use insert? 

When the store gets new inventory or when a user returns a book. Add a new user. When a user purchases a book.

Add inventory: 

INSERT INTO inventory ('ISBN', 'Ranks') VALUES ('THE_ISBN', 'THE_RANK'); 

Here is how you can add a user:

INSERT INTO user (LastName, FirstName, PhoneNumber, Username, Password, Permissions) 
VALUES ('LAST_NAME', 'FIRST_NAME', 'PHONE_NUMBER', 'USERNAME', 'PASSWORD', 'PERMISSIONS'); 

Add to purchase log when a user purchases a book:

INSERT INTO purchaselog VALUES(UserID, InventoryID); 
INSERT INTO purchaselog VALUES(1000008, 1000002);

Update

Update allows you to change information in a table. Remember that if you do not use “WHERE,” every record will be updated. 

When should I use update?

You should use update when the store changes the price of a book rank or when an error was made to an entry (i.e., misspelled an author’s name). When a user purchases a book.

General format: 

UPDATE [table_name] SET [field]=[value] WHERE [field]=[value];

When user purchases a book:

UPDATE inventory SET Purchased=1 WHERE InventoryID=1000000;

When user returns a book:

UPDATE inventory SET Purchased=0 WHERE InventoryID=1000000;

Delete

Delete allows you to remove information in a table. Remove a user. 

When should I use delete? 

When a user purchases a book. Remove a user.

Delete book:

DELETE FROM inventory WHERE InventoryID="1000009";

Delete user:

DELETE FROM user WHERE UserID=00000000;

Full Sequence Examples

New user purchases book. The user’s UserID=1000008. The book the user is purchasing has InventoryID=1000002; 

-- user creates an account. Remember that the UserID is auto-increment. 
INSERT INTO user (LastName, FirstName, PhoneNumber, Username, Password) 
VALUES ('Gallucci', 'Olivia', '(123) 456-7890', 'oliviagallucci', 'password');
-- change the inventory from available to purchased 
UPDATE inventory SET Purchased=1 WHERE InventoryID=1000002;
-- insert a row in the purchase log
INSERT INTO purchaselog VALUES(1000008, 1000002);
-- remove the inventory 
DELETE FROM inventory WHERE InventoryID=1000002;

Further reading

If you enjoyed this post about creating a sample MySQL book database, consider reading my posts about Connecting Angular Apps to MySQL and Learning How to Build a Database Server on a Raspberry Pi 4.

This blog post was created as part of the Introduction to Software Development (SWEN 261; Spring 2022) class at the Rochester Institute of Technology.

Join 1,216 other subscribers
Portrait of Olivia Gallucci in garden, used in LNP article.

Written by Olivia Gallucci

Olivia is an honors student at the Rochester Institute of Technology. She writes about security, open source software, and professional development.