خانه/ پروژه‌های واقعی/ کتابخانه

پروژه ۳: کتابخانه — امانت، دیرکرد و موجودی

پروژه عملی ۲۲ دقیقه

این پروژه ساده‌تر از دو پروژه‌ی قبل به‌نظر می‌رسد (فقط ۳ جدول)، اما یک نکته‌ی ظریف دارد که خیلی از طراحی‌های تازه‌کار را گیج می‌کند: وضعیت امانت نیاز به جدول یا ستون جداگانه ندارد — همه‌چیز فقط با NULL یا نبودن return_date مشخص می‌شود.

نقشه‌ی جدول‌ها

books
  • idINTEGER PK
  • titleTEXT
  • authorTEXT
  • categoryTEXT
  • total_copiesINTEGER
members
  • idINTEGER PK
  • full_nameTEXT
  • join_dateTEXT
loans
  • idINTEGER PK
  • book_idFK→ books
  • member_idFK→ members
  • loan_dateTEXT
  • due_dateTEXT
  • return_dateTEXT NULL
return_date عمداً می‌تواند NULL باشد. اگر NULL است، یعنی کتاب هنوز برنگشته — همان مفهوم NULL از فصل ۲ («مقدار نامشخص/وجودنداشته») این‌جا نقش کلیدی بازی می‌کند، نه فقط یک جای خالی بی‌اهمیت.

داده‌ی کامل: ۸ کتاب، ۷ عضو، ۱۳ امانت. برای همه‌ی چالش‌های زیر، تاریخ «امروز» را ثابت ۲۰۲۵-۰۱-۲۵ در نظر می‌گیریم تا نتیجه‌ها همیشه یکسان و قابل‌تکرار بمانند.

sql
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  category TEXT NOT NULL,
  total_copies INTEGER NOT NULL
);

CREATE TABLE members (
  id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  join_date TEXT NOT NULL
);

CREATE TABLE loans (
  id INTEGER PRIMARY KEY,
  book_id INTEGER NOT NULL REFERENCES books(id),
  member_id INTEGER NOT NULL REFERENCES members(id),
  loan_date TEXT NOT NULL,
  due_date TEXT NOT NULL,
  return_date TEXT
);

-- به‌همراه ۸ کتاب، ۷ عضو و ۱۳ امانت
-- (متن کامل INSERTها داخل فایل قابل‌دانلود پایین همین صفحه است)

این پایگاه‌داده را دانلود کن و روی سیستم خودت کار کن

هر دو فایل، همین ۳ جدول و همین داده را دارند — کافی‌ست یکی را انتخاب کنی:

فایل .db را می‌توانی مستقیم با نرم‌افزار رایگان DB Browser for SQLite باز کنی. فایل .sql برای اجرا در SQL Server Management Studio، DBeaver، یا هر کلاینت دیگری مناسب است.

چالش‌ها: خودت را امتحان کن

اول خودت سعی کن، بعد پاسخ پیشنهادی را باز کن. همه‌ی جعبه‌ها از همین پایگاه‌داده به‌طور کامل و مستقل استفاده می‌کنند.

۱ کتاب‌هایی که الان امانت‌اند

کدام کتاب‌ها هنوز برنگشته‌اند؟

sql
SELECT b.title, m.full_name, l.due_date
FROM loans l
JOIN books b ON b.id = l.book_id
JOIN members m ON m.id = l.member_id
WHERE l.return_date IS NULL;

۵ کتاب هنوز برنگشته‌اند — از جمله «دن کیشوت» که همین چند روز پیش (۲۰۲۵-۰۱-۲۰) امانت گرفته شده و سررسیدش هنوز نرسیده.

۲ دیرکردهای فعلی

کدام امانت‌ها الان دیرکرد دارند؟ (هنوز برنگشته و سررسیدش هم گذشته)

sql
SELECT b.title, m.full_name, l.due_date
FROM loans l
JOIN books b ON b.id = l.book_id
JOIN members m ON m.id = l.member_id
WHERE l.return_date IS NULL
  AND l.due_date < '2025-01-25';

۴ ردیف — یکی کمتر از چالش قبل. «دن کیشوت» این‌جا حذف شد چون سررسیدش (۲۰۲۵-۰۲-۰۳) هنوز نگذشته: در امانت است، اما دیرکرد ندارد. این دقیقاً تفاوت بین «هنوز برنگشته» و «دیرکرد دارد» است.

۳ بازگشت‌های دیرهنگام تاریخی

کدام امانت‌ها (که قبلاً برگشته‌اند) دیرتر از سررسید برگردانده شده‌اند؟

sql
SELECT b.title, m.full_name
FROM loans l
JOIN books b ON b.id = l.book_id
JOIN members m ON m.id = l.member_id
WHERE l.return_date IS NOT NULL
  AND l.return_date > l.due_date;

۳ نمونه: علی محمدی (بوف کور)، زهرا احمدی (کلیدر) و فاطمه کریمی (کلیدر) — این‌ها برای گزارش «اعضای پرریسک برای دیرکرد» مفیدند.

۴ محبوب‌ترین کتاب‌ها

کتاب‌ها را بر اساس تعداد دفعاتی که امانت گرفته شده‌اند رتبه‌بندی کن.

sql
SELECT b.title, COUNT(*) AS loan_count
FROM loans l
JOIN books b ON b.id = l.book_id
GROUP BY b.id
ORDER BY loan_count DESC;

«ملت عشق» و «کیمیاگر» هر دو با ۳ امانت مشترکاً محبوب‌ترین‌اند. دقت کن «سووشون» اصلاً در نتیجه دیده نمی‌شود — چون یک INNER JOIN است و کتابی که هیچ‌وقت امانت نرفته، اصلاً ردیفی در loans ندارد تا بشمریمش.

۵ اعضای بی‌فعالیت

کدام عضو، از وقتی ثبت‌نام کرده، هنوز حتی یک کتاب هم امانت نگرفته؟

sql
SELECT m.full_name FROM members m
WHERE NOT EXISTS (SELECT 1 FROM loans l WHERE l.member_id = m.id);

جواب: سارا نوری — تازه‌عضوشده و هنوز هیچ کتابی امانت نگرفته.

۶ میانگین مدت امانت

به‌طور میانگین، هر امانت (که برگردانده شده) چند روز طول کشیده؟

sql
SELECT ROUND(AVG(julianday(return_date) - julianday(loan_date)), 1)
FROM loans
WHERE return_date IS NOT NULL;

julianday() تابع ویژه‌ی SQLite است که یک تاریخ را به عدد اعشاری (تعداد روز از یک مبدأ ثابت) تبدیل می‌کند — تفریق دو julianday، دقیقاً تعداد روزهای بین‌شان را می‌دهد. معادلش در SQL Server: DATEDIFF(day, loan_date, return_date). نتیجه: میانگین ۱۶.۳ روز.

۷ موجودی فعلی هر کتاب

با توجه به تعداد کل نسخه‌ها و تعداد نسخه‌هایی که الان در امانت‌اند، چند نسخه از هر کتاب همین الان روی قفسه موجود است؟

sql
SELECT b.title, b.total_copies,
  b.total_copies - (
    SELECT COUNT(*) FROM loans l
    WHERE l.book_id = b.id AND l.return_date IS NULL
  ) AS available_now
FROM books b ORDER BY b.id;

دو کتاب موجودی صفر دارند: «تاریخ بیهقی» (تنها ۱ نسخه، همان یکی هم در امانت است) و «دن کیشوت» (همین‌طور). این زیرپرس‌وجوی همبسته (Correlated Subquery)، دقیقاً برای هر ردیف books، تعداد امانت‌های بازنگشته‌اش را جداگانه می‌شمرد.

جمع‌بندی این پروژه

  • گاهی بهترین طراحی، اضافه‌کردن یک جدول یا ستون جدید نیست — بلکه استفاده‌ی هوشمندانه از NULL روی یک ستون موجود (return_date) است.
  • توابع تاریخ SQLite (julianday) امکان محاسبات دقیق روی بازه‌های زمانی را می‌دهند.
  • زیرپرس‌وجوی همبسته، ابزار استاندارد برای محاسبه‌ی «برای هر ردیف، یک عدد جداگانه» است.

در آخرین پروژه‌ی این دوره، سراغ یک سیستم مدیریت کارکنان می‌رویم — با زنجیره‌ی مدیریتی، CTE بازگشتی و مقایسه‌ی حقوق داخل هر بخش.

درس قبلی
فروشگاه اینترنتی
© ۱۴۰۴ SQLFarsi — سوالات متداول · ساخته شده توسط مصطفی موسوی