Question

The following questions will all use the schema for a library database as follows.

Books (isbn, title, author, genre, publisher)
Members (id, name) Lending (isbn, id, checkout, returned) // checkout and returned are both DATETIME
A tuple in Books represents a single book in the library system (We're assuming the library doesn't have multiple copies of a book, and each book has a single author and publisher). There may be multiple books with the same title, and different isbn.
The Members table holds identifying information for people checking out books from the library. There may be multiple people with the same name, and different member ids.
Lastly the Lending table shows a history of books loaned to various members. The checkout and returned attributes are the time stamps the book was checked out to a user and subsequently returned by that user. Any Lending tuple with a NULL "returned" attribute means that book is still currently checked out. People may return a book and then check out the same book out again, each unique checkout becomes a new tuple with the new checkout time.
Books.isbn is the primary key for Books. Members.id is the primary key for Members.
Lending.isbn is a foreign key to Books.isbn
Lending.id is a foreign key to Members.id.
Lending.isbn, Lending.id, and Lending.checkout together constitute the primary key for Lending.
isbn and id are INT type. checkout and returned are DATETIME type. All other attributes are VARCHAR(1000)
Q1. Write the CREATE TABLE(...) statement for the Lending table in the schema described above. Use DATETIME for the type of the checkout and returned attributes, and INT for isbn and id. Remember to specify the relevant primary key and foreign key constraints. (You don't have to write .import or 'PRAGMA FOREIGN KEYS')
Q2. Write a query to find the books currently checked out and the name of the person checking out each one. Return the member name and book title, both sorted alphabetically. (Hint: the 'IS NULL' boolean test is useful here)
Q3. Write a query to find the number of book checkouts that have occurred in each genre of book in the library. (Multiple checkouts of the same book are still multiple checkouts in that genre.) If there is a genre that has some books in the library but none have ever been checked out, that genre's count should be 0 in the output. Return the genre and number of checkouts, sorted by number of checkouts in decreasing order.
Q4. Find the names and ids of all people who checked out the book titled 'Leaves of Grass' and also checked out the book titled 'Harmonium' at some point (it is not required that the person had both books at once.) Return the id and name for each person who has checked out both books. Do not return duplicate tuples.

Answer 1

Foreign key constraints:

isbn references the ISBN in the Books table

id references the member ID in the Members table

Here is the CREATE TABLE statement for the Lending table:

SQL

CREATE TABLE Lending (

isbn INT,

id INT,

checkout DATETIME,

returned DATETIME,

PRIMARY KEY (isbn, id, checkout),

FOREIGN KEY (isbn) REFERENCES Books(isbn),

FOREIGN KEY (id) REFERENCES Members(id)

);

This statement defines the following:

Table name: Lending

Columns:

isbn (INT): references the book being loaned (foreign key)

id (INT): references the member borrowing the book (foreign key)

checkout (DATETIME): timestamp when the book was checked out

returned (DATETIME): timestamp when the book was returned (nullable)

Primary key: (isbn, id, checkout) - ensures unique combinations of book, member, and checkout time

Foreign key constraints:

isbn references the ISBN in the Books table

id references the member ID in the Members table

This structure allows us to track borrowings, identify overdue books, and calculate statistics on member borrowing patterns.