Skip to content

For this project you will be creating a new database that will manage a Library System. This project should be a culmination of all the other projects.

  • Start by building a diagram in Lucid Chart.
  • Then create the database using SQLite. Make sure you have the relationships constrained in SQLite.
  • Add the data below AND add 1 extra book, 2 extra members, and 4 extra transactions.

Objective: To create a database that stores information about books and their availability in a library. The system should be able to manage the borrowing and returning of books by members, and provide relevant reports and statistics to the librarian.

Tables

  1. Books: This table will store information about books including the book ID, title, author, publisher, publication date, ISBN, total copies, and available copies.
  2. Members: This table will store information about library members including the member ID, name, address, phone number, email, and date of registration.
  3. Transactions: This table will store information about book transactions including the transaction ID, book ID, member ID, date borrowed, due date, and date returned (if applicable).

Books

Here are some books to add:

  • Book ID: 111; Title: To Kill a Mockingbird; Author: Harper Lee; Publisher: J.B. Lippincott & Co.; Publication Date: July 11, 1960; ISBN: 978-0-446-31078-1; Total Copies: 4; Available Copies: 2
  • Book ID: 1212; Title: The Great Gatsby; Author: F. Scott Fitzgerald; Publisher: Scribner’s; Publication Date: April 10, 1925; ISBN: 978-0-684-80146-2; Total Copies: 6; Available Copies: 4
  • Book ID: 1313; Title: Pride and Prejudice; Author: Jane Austen; Publisher: T. Egerton, Whitehall; Publication Date: January 28, 1813; ISBN: 978-1-973-20043-2; Total Copies: 2; Available Copies: 2
  • Book ID: 789; Title: The Catcher in the Rye; Author: J.D. Salinger; Publisher: Little, Brown and Company; Publication Date: July 16, 1951; ISBN: 978-0-316-76948-8; Total Copies: 5; Available Copies: 3
  • Book ID: 1010; Title: The Hitchhiker’s Guide to the Galaxy; Author: Douglas Adams; Publisher: Pan Books; Publication Date: October 12, 1979; ISBN: 0-330-25864-8; Total Copies: 3; Available Copies: 1

Members

Here are some members to add:

  • Member ID: 1; Name: John Smith; Address: 123 Main St, Anytown USA; Phone Number: 555-1234; Email: john.smith@email.com; Date of Registration: 2022-01-15
  • Member ID: 2; Name: Jane Doe; Address: 456 Oak St, Anycity USA; Phone Number: 555-5678; Email: jane.doe@email.com; Date of Registration: 2022-02-01
  • Member ID: 3; Name: Tom Johnson; Address: 789 Elm St, Anystate USA; Phone Number: 555-9012; Email: tom.johnson@email.com; Date of Registration: 2022-02-15
  • Member ID: 4; Name: Sarah Williams; Address: 1010 Maple St, Anytown USA; Phone Number: 555-3456; Email: sarah.williams@email.com; Date of Registration: 2022-03-01
  • Member ID: 5; Name: Michael Lee; Address: 111 Pine St, Anycity USA; Phone Number: 555-7890; Email: michael.lee@email.com; Date of Registration: 2022-03-15

Transactions

Here is some transaction data:

  • Member ID: 1; Book ID: 111; Transaction Type: Borrow; Transaction Date: 2022-01-20; Due Date: 2022-02-10; Return Date: -; Fine Amount: –
  • Member ID: 2; Book ID: 1212; Transaction Type: Borrow; Transaction Date: 2022-02-03; Due Date: 2022-02-24; Return Date: 2022-03-02; Fine Amount: 0.50 USD
  • Member ID: 1; Book ID: 1313; Transaction Type: Return; Transaction Date: 2022-02-12; Due Date: -; Return Date: 2022-02-12; Fine Amount: –
  • Member ID: 3; Book ID: 789; Transaction Type: Borrow; Transaction Date: 2022-02-18; Due Date: 2022-03-11; Return Date: 2022-03-08; Fine Amount: –
  • Member ID: 4; Book ID: 1010; Transaction Type: Reserve; Transaction Date: 2022-03-02; Due Date: -; Return Date: -; Fine Amount: –
  • Member ID: 5; Book ID: 111; Transaction Type: Borrow; Transaction Date: 2022-03-17; Due Date: 2022-04-07; Return Date: -; Fine Amount: –
  • Member ID: 4; Book ID: 1212; Transaction Type: Reserve; Transaction Date: 2022-03-20; Due Date: -; Return Date: -; Fine Amount: –
  • Member ID: 3; Book ID: 1313; Transaction Type: Borrow; Transaction Date: 2022-03-23; Due Date: 2022-04-13; Return Date: -; Fine Amount: –
  • Member ID: 2; Book ID: 1010; Transaction Type: Return; Transaction Date: 2022-03-28; Due Date: -; Return Date: 2022-03-28; Fine Amount: –
  • Member ID: 5; Book ID: 789; Transaction Type: Reserve; Transaction Date: 2022-04-01; Due Date: -; Return Date: -; Fine Amount: –

Queries

Write the queries that will answer the following questions.

  1. Which books are currently available for borrowing?
  2. Who has borrowed a particular book and when is it due for return?
  3. Which books are currently overdue and who has borrowed them?
  4. Which books are the most popular among the members?
  5. How many books has a particular member borrowed and when were they borrowed and returned?
  6. How many members have registered with the library?
  7. What is the total overdue fine collected by the library?
  8. Which books have been borrowed the most and which have been borrowed the least?
  9. What is the average borrowing period for the books in the library?
  10. What is the total number of books in the library and how many of them are reference books?
  11. How many copies of a particular book are available in the library?
  12. What is the borrowing history of a particular member, including the number of books borrowed and returned, and the total amount of overdue fines paid?
  13. What is the average overdue fine per book in the library?
  14. Which books are currently on hold by members and how long have they been on hold?
  15. What is the total revenue generated by the library through membership fees and overdue fines?

Turn In

To turn in, add the lucid chart, the .db file and the .sql file to the GeorgiaView assignment.