Apply your knowledge to build something amazing!
What you'll do: Open the project and familiarize yourself with the interface
DO NOT DELETE the existing files in the template:
ONLY EDIT the necessary files.
A full-stack library management system that handles book borrowing, returns, and overdue tracking with real-time availability updates. You'll evolve your database design from a simple denormalized structure to a properly normalized, performant system that can handle hundreds of users and thousands of books.
Before starting, ensure you can:
Don't worry if you're not 100% confident - this project will strengthen these skills!
The Book Lending System requires these relationships:
Users (1) ←→ (Many) Loans (Many) ←→ (1) Books
-- Creating related tables
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE books (
book_id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author TEXT,
isbn TEXT UNIQUE,
available_copies INTEGER DEFAULT 1,
total_copies INTEGER DEFAULT 1
);
CREATE TABLE loans (
loan_id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES users(user_id),
book_id INTEGER REFERENCES books(book_id),
loan_date DATE DEFAULT CURRENT_DATE,
due_date DATE,
return_date DATE,
UNIQUE(user_id, book_id, return_date) -- Prevent duplicate active loans
);
-- Common queries you'll write
-- Find overdue books
SELECT u.name, b.title, l.due_date,
julianday('now') - julianday(l.due_date) as days_overdue
FROM loans l
JOIN users u ON l.user_id = u.user_id
JOIN books b ON l.book_id = b.book_id
WHERE l.return_date IS NULL
AND l.due_date < date('now');
-- Check book availability
SELECT book_id, title,
(total_copies - COUNT(l.loan_id)) as available_copies
FROM books b
LEFT JOIN loans l ON b.book_id = l.book_id
AND l.return_date IS NULL
GROUP BY b.book_id
HAVING available_copies > 0;
Draw an ER diagram for a library system that includes:
Given this denormalized table, identify problems and normalize to 3NF:
CREATE TABLE library_chaos (
loan_id INT,
user_name TEXT,
user_email TEXT,
user_phone TEXT,
user_address TEXT,
book_title TEXT,
book_author TEXT,
book_isbn TEXT,
book_category TEXT,
publisher_name TEXT,
publisher_address TEXT,
loan_date DATE,
due_date DATE,
return_date DATE,
librarian_name TEXT,
librarian_employee_id TEXT,
late_fee_amount DECIMAL
);
Identify: What problems do you see? How many tables should this become?
Write SQL queries to:
⭐⭐⭐⭐☆ (4/5 stars) - Advanced beginner to intermediate
User Management
Book Catalog
Lending System
Admin Features
Your system must:
Start with a deliberately problematic design to understand why normalization matters:
-- Version 1: The "Everything in One Table" Disaster
-- TODO: Implement this first to experience the pain points
CREATE TABLE library_everything (
id INTEGER PRIMARY KEY,
user_name TEXT,
user_email TEXT,
book_title TEXT,
book_author TEXT,
book_isbn TEXT,
loan_date TEXT,
due_date TEXT,
return_date TEXT
-- What problems will you encounter with this design?
-- Try inserting 10 loans and observe the redundancy
);
Discovery Challenges:
Your First Features (keep it simple):
// Basic CRUD operations
app.post('/api/loans', (req, res) => {
// TODO: Insert a new loan
// Challenge: How do you check if the book is available?
// Research: What's a race condition? How might two users borrow the same last copy?
});
app.get('/api/available-books', (req, res) => {
// TODO: List available books
// Problem: With everything in one table, how do you find books nobody is borrowing?
// Hint: You might need a creative self-join or subquery
});
After experiencing the pain, evolve to a normalized design:
-- Version 2: Discovering Normalization
-- TODO: Extract entities into separate tables
-- Research Task: What is 1NF? How does this achieve it?
CREATE TABLE users (
user_id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT UNIQUE NOT NULL,
name TEXT NOT NULL,
-- What other user attributes belong here?
);
-- Challenge: Design the books table
-- Requirements:
-- • Each book can have multiple copies
-- • Books have ISBN for unique identification
-- • Some books might have multiple authors
-- Research: Should author be in this table? Why or why not?
-- TODO: Design the loans table
-- Critical thinking questions:
-- • How do you ensure a user can't borrow the same book twice?
-- • How do you track both active and historical loans?
-- • What constraints prevent data inconsistencies?
Normalization Exercises:
Add sophistication to your system:
-- Version 3: Production-Ready Features
-- TODO: Implement reservation system
CREATE TABLE reservations (
-- Design challenge: How do you queue reservations?
-- How do you automatically convert a reservation to a loan?
-- What happens when a reserved book is returned?
);
-- TODO: Add audit logging
CREATE TABLE audit_log (
-- What events should you track?
-- How do you capture who made changes?
-- Research: What's the difference between application-level and database-level auditing?
);
-- TODO: Implement late fees
-- Design decisions:
-- • Fixed fee vs. per-day calculation?
-- • Grace periods?
-- • How do you handle partial payments?
Performance Optimization Tasks:
-- TODO: Add indexes for common queries
-- Research: What's the difference between clustered and non-clustered indexes?
-- Experiment: Use EXPLAIN QUERY PLAN to see the impact
-- Which columns need indexes?
CREATE INDEX idx_loans_user_id ON loans(user_id);
-- What other indexes would help?
-- TODO: Create a view for available books
CREATE VIEW available_books AS
-- How can you optimize this view?
-- Consider: Materialized views vs regular views
Production Checklist:
Once your core system works, consider these enhancements:
Before submitting, verify:
GitHub Repository with:
Live Demo including:
Documentation containing:
| Criteria | Weight | Focus |
|---|---|---|
| Functionality | 40% | All features work as specified |
| Database Design | 30% | Proper normalization, constraints |
| Code Quality | 15% | Clean, documented, maintainable |
| Performance | 10% | Optimized queries, fast loading |
| Documentation | 5% | Clear setup and usage instructions |
Ready to build? Start with Phase 1 and experience the evolution from chaos to elegance. Remember: it's not about building a perfect system immediately - it's about understanding why good database design matters through hands-on discovery!
🚀 Begin by creating your project folder and implementing the "everything in one table" disaster. The pain you feel is the learning happening!