Designing a Simple Library Database with Foreign Keys

This challenge focuses on designing a relational database schema for a small library using SQL. You'll need to define tables for books, authors, and borrowers, and crucially, establish relationships between them using FOREIGN KEY constraints to ensure data integrity and consistency. Understanding and implementing foreign keys is vital for building robust and reliable database systems.

Problem Description

You are tasked with designing a database schema for a library. The library tracks books, their authors, and the borrowers who check them out. The schema should include three tables: Authors, Books, and Borrowers.

• Authors: Stores information about the authors of the books.
• Books: Stores information about the books in the library, including who wrote them.
• Borrowers: Stores information about the people who borrow books.

The relationships are as follows:

• Each book is written by one author (one-to-many relationship between Authors and Books).
• A borrower can borrow multiple books (many-to-many relationship between Borrowers and Books, requiring a linking table - Loans).

You need to define the SQL CREATE TABLE statements for these tables, including appropriate data types for each column and, most importantly, defining the FOREIGN KEY constraints to enforce referential integrity. The Loans table will also need to be created.

Key Requirements:

• Define the tables Authors, Books, Borrowers, and Loans.
• Use appropriate data types for each column (e.g., INT, VARCHAR, DATE).
• Implement FOREIGN KEY constraints to link the tables correctly.
• Ensure that you can't add a book without a valid author.
• Ensure that you can't add a loan for a borrower or book that doesn't exist.
• The Loans table should link Borrowers and Books and include a loan_date.

Expected Behavior:

The database should enforce the following rules:

• Deleting an author should not delete their books (cascade delete is not required, simply prevent the deletion if books exist).
• Deleting a book should not delete the borrower information (cascade delete is not required, simply prevent the deletion if loans exist).
• Attempting to insert a book with an author ID that doesn't exist in the Authors table should fail.
• Attempting to insert a loan with a borrower ID or book ID that doesn't exist should fail.

Examples

Example 1:

Input:  (Assume Authors, Books, Borrowers, and Loans tables are empty)

CREATE TABLE Authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(255)
);

CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    title VARCHAR(255),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

CREATE TABLE Borrowers (
    borrower_id INT PRIMARY KEY,
    borrower_name VARCHAR(255)
);

CREATE TABLE Loans (
    loan_id INT PRIMARY KEY,
    book_id INT,
    borrower_id INT,
    loan_date DATE,
    FOREIGN KEY (book_id) REFERENCES Books(book_id),
    FOREIGN KEY (borrower_id) REFERENCES Borrowers(borrower_id)
);

Output: (No output - successful table creation)
Explanation: The tables are created with the specified columns and foreign key constraints.

Example 2:

Input: Attempt to insert a book with a non-existent author_id:

INSERT INTO Books (book_id, title, author_id) VALUES (1, 'The Hitchhiker''s Guide to the Galaxy', 999);

Output: (Error message indicating a foreign key constraint violation)
Explanation: The `author_id` 999 does not exist in the `Authors` table, so the insertion fails due to the foreign key constraint.

Example 3: (Edge Case - Deleting an Author)

Input:  Author with ID 1 exists, and a book with author_id 1 exists. Attempt to delete the author.

DELETE FROM Authors WHERE author_id = 1;

Output: (Error message indicating a foreign key constraint violation)
Explanation:  Deleting the author would violate the foreign key constraint on the Books table, as there's a book referencing that author. The deletion is prevented.

Constraints

• All id columns (author_id, book_id, borrower_id, loan_id) must be of type INT.
• All name columns (author_name, borrower_name, title) must be of type VARCHAR(255).
• loan_date must be of type DATE.
• Primary keys must be defined for each table.
• FOREIGN KEY constraints must be correctly defined to enforce referential integrity.
• The database schema must be valid SQL and executable in a standard SQL environment (e.g., MySQL, PostgreSQL, SQLite).

Notes

• Focus on the correct definition of the FOREIGN KEY constraints. This is the core of the challenge.
• Consider the implications of different FOREIGN KEY actions (e.g., CASCADE, SET NULL, RESTRICT). For this challenge, RESTRICT is the desired behavior (preventing deletions that would violate constraints).
• You don't need to populate the tables with data; only the schema definition is required.
• Think about the relationships between the tables and how the foreign keys enforce those relationships.
• The Loans table is crucial for representing the many-to-many relationship between borrowers and books.