Designing a Library Database

Problem

Design a database to store information about a library's patrons, books, and checkout transactions.
The database should be able to track the following information:
-Patron's name, ID, address, phone number, date of birth
-Book title, author, ISBN
-Checkout date, return date, overdue status
Example input:
Patron: John Smith
ID: 12345
Address: 123 Main St
Phone: 555-1234
DOB: 01/01/1980
Book: The Cat in the Hat
Author: Dr. Seuss
ISBN: 0-345-39489-2
Checkout date: 01/01/2018
Return date: 01/15/2018
Example output:
Patron: John Smith
ID: 12345
Address: 123 Main St
Phone: 555-1234
DOB: 01/01/1980
Book: The Cat in the Hat
Author: Dr. Seuss
ISBN: 0-345-39489-2
Checkout date: 01/01/2018
Return date: 01/15/2018
Overdue: False

Solution

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

sarastevens

/*

Solution:

CREATE TABLE Patron (
  id INTEGER PRIMARY KEY,
  name VARCHAR(255),
  address VARCHAR(255),
  phone VARCHAR(255),
  dob DATE
);

CREATE TABLE Book (
  id INTEGER PRIMARY KEY,
  title VARCHAR(255),
  author VARCHAR(255),
  isbn VARCHAR(255)
);

CREATE TABLE Checkout (
  id INTEGER PRIMARY KEY,
  patron_id INTEGER,
  book_id INTEGER,
  checkout_date DATE,
  return_date DATE,
  overdue BOOLEAN,
  FOREIGN KEY (patron_id) REFERENCES Patron(id),
  FOREIGN KEY (book_id) REFERENCES Book(id)
);

*/

/*

Explanation:

The solution above is optimal because it is normalized.

The tables are in 3NF because there are no transitive dependencies.

The tables are in BCNF because there are no non-trivial functional dependencies.

The tables are in 4NF because there are no multi-valued dependencies.

*/