Book Database Revisited¶

Problem definition¶

Imagine you’re designing a database for a small library. The library has several books, each of which has a unique ISBN (International Standard Book Number) number, a title, a list of authors, a publication date, a publisher, and a number of copies available. The library also has several members, each of whom has a unique ID number, a name, an address, a phone number, and a list of books they have borrowed.

Design a database to represent this information. Your database should include tables for books, authors, publishers, members, and borrowing history. In the borrowing history table, you should keep track of the date the book was borrowed, the due date, and the date the book was returned. Make sure to include appropriate constraints and indices on your tables.

Feel free to add any additional fields or entities that you think would be useful for the library.

Solution¶

Here’s a possible solution to the problem:

Books revisted

Here is the SQL code to create the database and tables:

CREATE DATABASE IF NOT EXISTS books_revisited;

USE books_revisited;

/* Create the Authors table */
CREATE TABLE Authors (
    author_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100)
);

/* Create the Publishers table */
CREATE TABLE Publishers (
    publisher_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(200) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100)
);

/* Create the Books table */
CREATE TABLE Books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    isbn VARCHAR(20) UNIQUE NOT NULL,
    publication_date DATE,
    publisher_id INT NOT NULL,
    copies_available INT NOT NULL,
    FOREIGN KEY (publisher_id) REFERENCES Publishers(publisher_id)
);

/* Books Authors table */
CREATE TABLE BooksAuthors (
    book_id INT NOT NULL,
    author_id INT NOT NULL,
    PRIMARY KEY (book_id, author_id),
    FOREIGN KEY (book_id) REFERENCES Books(book_id),
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

/* Create the Members table */
CREATE TABLE Members (
    member_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(200) NOT NULL,
    phone VARCHAR(20) NOT NULL
);

/* Create the Borrowings table */
CREATE TABLE Borrowings (
    borrowing_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    member_id INT NOT NULL,
    borrowed_date DATE NOT NULL,
    due_date DATE NOT NULL,
    returned_date DATE,
    FOREIGN KEY (book_id) REFERENCES Books(book_id),
    FOREIGN KEY (member_id) REFERENCES Members(member_id)
);

Here’s a brief description of each entity:

Authors: Stores information about an author, such as their name and email address.
Publishers: Stores information about a book publisher, such as their name, address, phone number, and email address.
Books: Stores information about a book, such as its title, ISBN number, publication date, publisher, and number of copies available.
BooksAuthors: Stores information about the authors of a book. This is a many-to-many relationship (junction table), since a book can have multiple authors, and an author can write multiple books.
Members: Stores information about a library member, such as their name, address, and phone number.
Borrowings: Stores information about a book borrowing, such as the book ID, member ID, borrowing date, due date, and return date.
Each entity has its own table in the database, and the tables are related to each other through foreign key constraints. For example, the Books table has a foreign key that references the Publishers table, which allows us to associate books with their publishers. Similarly, the Borrowings table has foreign keys that reference the Books and Members tables, which allows us to associate borrowings with the books and members involved.