Database design for a library

Problem

Design a database for a library. The database should track books, patrons, and checkouts.
Example input:
Books:
Title: The Catcher in the Rye
Author: J.D. Salinger
ISBN: 0316769177
Patrons:
Name: John Smith
Address: 123 Main Street
Phone: 555-1234
Checkouts:
Patron ID: 1
Book ID: 1
Checkout date: 1/1/2000
Due date: 1/15/2000
Example output:
Books:
The Catcher in the Rye by J.D. Salinger (ISBN 0316769177)
Patrons:
John Smith (123 Main Street, 555-1234)
Checkouts:
John Smith has checked out The Catcher in the Rye and is due to return it on 1/15/2000.

Solution

This solution is in SQL. Our A.I. can create solutions in multiple languages.

sarastevens

/*

Solution:

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  isbn TEXT
);

CREATE TABLE patrons (
  id INTEGER PRIMARY KEY,
  name TEXT,
  address TEXT,
  phone TEXT
);

CREATE TABLE checkouts (
  id INTEGER PRIMARY KEY,
  patron_id INTEGER,
  book_id INTEGER,
  checkout_date DATE,
  due_date DATE,
  FOREIGN KEY (patron_id) REFERENCES patrons(id),
  FOREIGN KEY (book_id) REFERENCES books(id)
);

*/

/*

Explanation:

The solution above is optimal because it uses the minimum number of tables to store the data.

*/