Enforcing Referential Integrity: The Bookstore Database Challenge

You are tasked with designing a relational database for a small independent bookstore. To ensure data accuracy and consistency, it's crucial to enforce relationships between different pieces of information. This challenge focuses on using FOREIGN KEY constraints to maintain referential integrity between tables representing authors, books, and their publishers.

Problem Description

You need to create a database schema with three tables: Publishers, Authors, and Books. The goal is to establish relationships between these tables using FOREIGN KEY constraints to ensure that a book is always associated with a valid publisher, and an author is always associated with a valid book (and vice-versa in a more complex scenario, but we'll focus on publisher-book for this specific challenge).

Key Requirements:

1. Publishers Table: This table will store information about the book publishers.
   • It must have a primary key column to uniquely identify each publisher.
   • It must store the publisher's name.
2. Authors Table: This table will store information about the authors.
   • It must have a primary key column to uniquely identify each author.
   • It must store the author's name.
3. Books Table: This table will store information about the books.
   • It must have a primary key column to uniquely identify each book.
   • It must store the book's title.
   • It must include a column to reference the publisher of the book. This column will be a FOREIGN KEY referencing the Publishers table.
   • It must include a column to reference the author of the book. This column will be a FOREIGN KEY referencing the Authors table.

Expected Behavior:

• When inserting a new book, the publisher_id must correspond to an existing id in the Publishers table.
• When inserting a new book, the author_id must correspond to an existing id in the Authors table.
• Attempting to insert a book with a publisher_id or author_id that does not exist in the respective parent tables should result in an error.
• Attempting to delete a publisher or author that is currently referenced by one or more books should also result in an error (or a predefined cascading action, though for this challenge, we will enforce strict referential integrity).

Edge Cases to Consider:

• What happens if you try to delete a publisher or author that is referenced by a book? The FOREIGN KEY constraint should prevent this.

Examples

Example 1: Creating Tables and Establishing Relationships

Input:

• Create Publishers table with id (INT, PRIMARY KEY) and name (VARCHAR).
• Create Authors table with id (INT, PRIMARY KEY) and name (VARCHAR).
• Create Books table with id (INT, PRIMARY KEY), title (VARCHAR), publisher_id (INT), and author_id (INT).
• Add a FOREIGN KEY constraint to Books.publisher_id referencing Publishers.id.
• Add a FOREIGN KEY constraint to Books.author_id referencing Authors.id.

Output:

A database schema with three tables (Publishers, Authors, Books) where the Books table has correctly defined FOREIGN KEY relationships with Publishers and Authors.

Explanation:

The SQL statements for creating these tables and their constraints will define the structural integrity. The FOREIGN KEY clauses explicitly link the columns, enforcing the rules described.

Example 2: Attempting to Insert Invalid Data

Input:

Assume the following data exists:

Publishers table:

Authors table:

Now, attempt to insert the following book:

INSERT INTO Books (id, title, publisher_id, author_id)
VALUES (301, 'Pride and Prejudice', 999, 201); -- Invalid publisher_id

Output:

An error message indicating that the publisher_id (999) does not exist in the Publishers table.

Explanation:

The FOREIGN KEY constraint on Books.publisher_id prevents the insertion because there is no publisher with id = 999.

Example 3: Attempting to Delete Referenced Data

Input:

Assume the following data exists:

Publishers table:

Authors table:

Books table:

Now, attempt to delete the publisher with id = 101:

DELETE FROM Publishers
WHERE id = 101;

Output:

An error message indicating that the publisher_id (101) is being referenced by a foreign key constraint in the Books table.

Explanation:

The FOREIGN KEY constraint on Books.publisher_id prevents the deletion of Publishers record with id = 101 because it is currently associated with the book 'Pride and Prejudice'.

Constraints

• The id columns in Publishers, Authors, and Books tables must be unique integers.
• The name columns in Publishers and Authors tables, and the title column in Books table, must be non-empty strings.
• The publisher_id and author_id columns in the Books table must be integers.
• Your solution should involve defining the tables and then adding the FOREIGN KEY constraints. The specific SQL dialect might vary, but the core concepts of primary and foreign keys should be applicable.

Notes

• This challenge is language-agnostic, focusing on the SQL concepts. You will be expected to provide the SQL statements to create the tables and enforce the constraints.
• Pay close attention to the syntax for defining primary and foreign keys in your chosen SQL dialect (e.g., MySQL, PostgreSQL, SQLite, SQL Server).
• The error messages in the examples are illustrative; the actual error messages may vary depending on the database system. The key is that an error occurs and the invalid operation is prevented.
• Consider the ON DELETE and ON UPDATE clauses for foreign keys. For this challenge, the default behavior (which typically results in an error for ON DELETE or ON UPDATE if there are referencing rows) is sufficient, but understanding these options is valuable for more complex scenarios.