18.5. Studio: A Library

Word has gotten out about your amazing SQL skills! The head librarian at the local library asked if you could help with their database.

18.5.1. Database Setup

Let’s start by creating a new schema in MySQL Workbench called library. Once you have accepted all defaults and hit Apply, you need to create tables and import data from CSVs or add data via SQL query. By the end of your setup, you should have 6 tables:

  1. book
  2. author
  3. patron
  4. reference_books
  5. genre
  6. loan

Note

For the starter data, you need to use the same Github repo as the exercises. The files you need are in lesson-15-studio.

18.5.1.1. The book table

To create the book table, you can use the following SQL query:

1
2
3
4
5
6
7
8
CREATE TABLE book (
   book_id INT AUTO_INCREMENT PRIMARY KEY,
   author_id INT,
   title VARCHAR(255),
   isbn INT,
   available BOOL,
   genre_id INT
);

For the starter data, use the books.csv file to the fill the table.

18.5.1.2. The author table

Use the following query to create the author table.

1
2
3
4
5
6
7
CREATE TABLE author (
   author_id INT AUTO_INCREMENT PRIMARY KEY,
   first_name VARCHAR(255),
   last_name VARCHAR(255),
   birthday DATE,
   deathday DATE
);

For the starter data, use the authors.csv file to fill the author table.

18.5.1.3. The patron table

Use the following query to create the patron table.

1
2
3
4
5
6
CREATE TABLE patron (
   patron_id INT AUTO_INCREMENT PRIMARY KEY,
   first_name VARCHAR(255),
   last_name VARCHAR(255),
   loan_id INT
);

To fill the table, use the patrons.csv file.

18.5.1.4. The reference_books table

Use the following query to create the reference_books table.

1
2
3
4
5
6
7
8
9
CREATE TABLE reference_books (
   reference_id INT AUTO_INCREMENT PRIMARY KEY,
   edition INT,
   book_id INT,
   FOREIGN KEY (book_id)
      REFERENCES book(book_id)
      ON UPDATE SET NULL
      ON DELETE SET NULL
);

To fill the table, use the following query.

1
2
INSERT INTO reference_books(edition, book_id)
VALUE (5,32);

18.5.1.5. The genre table

Use the following query to create the genre table.

1
2
3
4
CREATE TABLE genre (
   genre_id INT PRIMARY KEY,
   genres VARCHAR(100)
);

Use the genres.csv file to fill the genre table.

18.5.1.6. The loan table

Use the following query to create the loan table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE TABLE loan (
   loan_id INT AUTO_INCREMENT PRIMARY KEY,
   patron_id INT,
   date_out DATE,
   date_in DATE,
   book_id INT,
   FOREIGN KEY (book_id)
      REFERENCES book(book_id)
      ON UPDATE SET NULL
      ON DELETE SET NULL
);

For now, the loan table can stay empty! We will add information to it in a bit!

18.5.2. Warm-up Queries

Write the following queries to get warmed up.

  1. Return the mystery book titles and their ISBNs.
  2. Return all of the titles and author’s first and last names for books written by authors who are currently living.

18.5.3. Loan Out a Book

A big function that you need to implement for the library is a script that updates the database when a book is loaned out.

The script needs to perform the following functions:

  1. Change available to FALSE for the appropriate book.
  2. Add a new row to the loan table with today’s date as the date_out and the ids in the row matching the appropriate patron_id and book_id.
  3. Update the appropriate patron with the loan_id for the new row created in the loan table.

You can use any patron and book that strikes your fancy to create this script!

18.5.4. Check a Book Back In

Working with the same patron and book, create the new script!

The other key function that we need to implement is checking a book back in. To do so, the script needs to:

  1. Change available to TRUE for the appropriate book.
  2. Update the appropriate row in the loan table with today’s date as the date_in.
  3. Update the appropriate patron changing loan_id back to null.

Once you have created these scripts, loan out 5 new books to 5 different patrons.

18.5.5. Wrap-up Query

Write a query to wrap up the studio. This query should return the names of the patrons with the genre of every book they currently have checked out.

18.5.6. Bonus Mission

  1. Return the counts of the books of each genre. Check out the documentation to see how this could be done!
  2. A reference book cannot leave the library. How would you modify either the reference_book table or the book table to make sure that doesn’t happen? Try to apply your modifications.