Library Data Analysis Project 🚀📚

Introduction

Dive into Library Data Analysis! 📊
This project focuses on analyzing a library database using SQL, exploring various questions from basic to advanced levels. By examining the dataset, we can gain insights to help the library understand its operations, improve book management, and make data-driven decisions. 🌐

Background

The questions I aimed to answer through SQL queries include:

• 💼📋 Who are the top employees in terms of book issues?
• 📅📖 Which books are returned after more than 30 days?
• 📈📚 How many books are issued and returned, and what is the generated revenue from fines?
• 📊👥 Which members have issued books more than twice?
• 🛠️🔍 How many damaged books are issued by members?

Tools I Used

For my analysis of the library database, I used the following key tools:

• SQL: The backbone of my analysis, allowing me to query the database and uncover meaningful insights.
• PostgreSQL: The chosen database management system for handling the dataset efficiently.
• Visual Studio Code: My preferred environment for writing and executing SQL queries.
• Git & GitHub: Essential for version control, sharing SQL scripts, and collaborating on project updates.

The Analysis

Each query in this project was crafted to investigate specific aspects of the library database, as outlined below:

1. Top Employees by Book Issues

To identify the top 3 employees who issued the most books:

SELECT 
    employee_id, 
    COUNT(issue_id) AS total_issues
FROM 
    issued_status
GROUP BY 
    employee_id
ORDER BY 
    total_issues DESC
LIMIT 3;

2. Books Returned After 30 Days

This query identifies books returned after a delay of over 30 days:

SELECT 
    book_id, 
    return_date - issue_date AS days_taken
FROM 
    return_status
WHERE 
    return_date - issue_date > 30;

3. Books Issued, Returned, and Revenue from Fines

Collecting data on the total number of books issued and returned, along with the revenue from fines:

SELECT 
    COUNT(issue_id) AS books_issued,
    COUNT(return_id) AS books_returned,
    SUM(fine_amount) AS total_revenue
FROM 
    issued_status
JOIN 
    return_status ON issued_status.issue_id = return_status.issue_id;

4. Frequent Book Issuers

Identifying members who have issued books more than twice:

SELECT 
    member_id, 
    COUNT(issue_id) AS issue_count
FROM 
    issued_status
GROUP BY 
    member_id
HAVING 
    COUNT(issue_id) > 2;

5. Issued Damaged Books

Finding members who have issued damaged books:

SELECT 
    member_id, 
    COUNT(book_id) AS damaged_books
FROM 
    issued_status
WHERE 
    book_condition = 'Damaged'
GROUP BY 
    member_id;

What I Learned

This project not only strengthened my SQL skills but also enriched my understanding of data analysis within the library management context. Key takeaways include:

• 🎓 Mastering Complex SQL Queries: Gained proficiency in handling complex SQL queries, data aggregation, and multi-table joins to retrieve relevant information.
• 📊 Uncovering Meaningful Insights: Learned how to analyze book issuance patterns, track overdue returns, and calculate revenue from fines, providing actionable insights.
• 💡 Data-Driven Decision-Making: Recognized the significance of data-driven strategies to improve library operations and enhance user experiences.

Conclusions

• 🧑‍💼 Insights on Employee and Member Behavior: The analysis offers a comprehensive view of employee performance in book issuance and member habits, equipping the library with valuable insights to target areas for improvement and optimize service.
• 🎯 Data-Driven Decisions: The findings guide strategies for better managing book returns, improving member engagement, and refining fine collection policies, supporting a data-focused approach to library management.

Closing Thoughts

✨ This SQL project has been a rewarding experience, shedding light on key aspects of library operations and reinforcing my data analysis skills. I'm excited to continue applying these insights and skills in future projects! 🌟