آوند — داده: SQL، مدل‌سازی و تحلیل برای تصمیم

فصل ۴ از ۹

پیشرفت ترم
۰٪

در این فصل چه یاد می‌گیری#

فایلِ صادراتیِ ما یک ستون دارد که همه‌چیزِ این فصل را در خودش خلاصه کرده: tags، که چند برچسب را با ویرگول در یک خانه نگه می‌دارد.

پرسشِ «چند کتابِ برچسبِ شعر داریم؟» روی این ستون ۴۱۸ جواب می‌دهد. عددِ درست ۲۱۳ است. و این بدترین موردش نیست: برای برچسبِ «کودک» همان پرسش ۴۴۸ می‌گوید در حالی که جوابِ درست ۲۲۷ است — تقریباً دو برابر.

و همان جدول سه ناهنجاریِ دیگر هم دارد که با عدد نشانشان می‌دهیم: شعبه‌ای که نمی‌توانی ثبتش کنی چون هنوز کتاب ندارد، شعبه‌ای که با حذفِ آخرین کتابش کاملاً ناپدید می‌شود، و شعبه‌ای که بعد از یک تغییرِ نصفه‌کاره دو شهرِ متفاوت پیدا می‌کند.

یک بایگانی که یک برگهٔ واحد در ده کشوی مختلف کپی شده و یکی از کپی‌ها با بقیه فرق دارد

آخر این فصل می‌توانی:

  • سه ناهنجاریِ درج و حذف و به‌روزرسانی را در یک جدولِ واقعی نشان بدهی
  • وابستگیِ تابعی را بنویسی و از رویش تصمیم بگیری کدام ستون کجا برود
  • ستونی را که چند مقدار در یک خانه دارد به یک جدولِ پیوندی تبدیل کنی
  • ثابت کنی که در این تبدیل هیچ داده‌ای گم یا تکثیر نشده

قبل از شروع#

از فصلِ ۲ و ۳: کلیدِ اصلی، کلیدِ جانشین، و FOREIGN KEY با پرچمِ روشن. از ترمِ ۳ فصلِ ۱: جدولِ پهنی که برای یک شعبه دو عددِ متفاوتِ صندلی می‌داد. آن فصل نشانه را نشان داد؛ این فصل نام و درمانش را می‌دهد.

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

📓 نوت‌بوک: نوت‌بوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

جدول تعدادِ سطر زمانِ ساخت
catalog_raw ۱٬۲۰۰ کمتر از یک ثانیه
members_raw ۳۰۰ آنی
loan_events ۲۶٬۱۱۹ حدودِ یک ثانیه

۱. سه ناهنجاری، روی یک نسخهٔ کاری#

فایلِ صادراتی نامِ شعبه را دارد ولی مشخصاتش را نه. بیایید نسخهٔ کاری‌ای بسازیم که شهرِ شعبه را هم کنارِ هر کتاب دارد — دقیقاً همان شکلی که صادراتیِ واقعی معمولاً دارد، و همان شکلی که هر کسی برای «راحت‌تر شدنِ گزارش» می‌سازد.

CITY_OF = {"مرکزی": "تهران", "نسیم": "مشهد", "دانش": "شیراز",
           "بهار": "تبریز", "کوشا": "اصفهان", "پویا": "کرمانشاه"}

con.execute("DROP TABLE IF EXISTS catalog_wide")
con.execute("""
    CREATE TABLE catalog_wide (
        id          INTEGER PRIMARY KEY,
        title       TEXT NOT NULL,
        author      TEXT NOT NULL,
        branch      TEXT NOT NULL,
        branch_city TEXT NOT NULL,
        added_at    TEXT NOT NULL,
        tags        TEXT NOT NULL
    )
""")
con.executemany("""
    INSERT INTO catalog_wide (id, title, author, branch, branch_city, added_at, tags)
    VALUES (?, ?, ?, ?, ?, ?, ?)
""", [(r.id, r.title, r.author, r.branch, CITY_OF[r.branch], r.added_at, r.tags)
      for r in q("SELECT id, title, author, branch, added_at, tags FROM catalog_raw")
             .itertuples(index=False)])
con.commit()

print(q("SELECT id, title, branch, branch_city, tags FROM catalog_wide ORDER BY id LIMIT 3"))
print()
print(q("SELECT branch, branch_city, COUNT(*) AS rows FROM catalog_wide GROUP BY branch, branch_city ORDER BY rows"))
   id        title branch branch_city                         tags
0   1    روزهای پل   نسیم        مشهد                   ترجمه، شعر
1   2  مه در باران   دانش       شیراز                        تاریخ
2   3   روزهای خاک   کوشا      اصفهان  ادبیات کودک، شعر، شعر معاصر

  branch branch_city  rows
0  مرکزی       تهران   180
1   دانش       شیراز   187
2   پویا    کرمانشاه   195
3   کوشا      اصفهان   200
4   بهار       تبریز   214
5   نسیم        مشهد   224

شش شعبه، و نامِ هر شهر بینِ ۱۸۰ تا ۲۲۴ بار نوشته شده. جمعِ ستونِ آخر ۱٬۲۰۰ است، یعنی هیچ سطری جا نمانده.

حالا سه اتفاقِ کاملاً معمولی. سه دستور در این سلول تازه‌اند و فعلاً همین‌قدر بدان: DELETE FROM سطرهایی را که شرط را برآورده می‌کنند برمی‌دارد، UPDATE … SET مقدارِ یک ستون را در آن سطرها عوض می‌کند، و con.rollback() هر چیزی را که از آخرین commit تا حالا نوشته‌ای پس می‌گیرد — پس این آزمایش‌ها داده را واقعاً خراب نمی‌کنند. ترمِ ۵ فصلِ ۵ هر سه را کامل باز می‌کند، با تراکنش و اتمی بودن. و SELECT changes() می‌گوید آخرین دستور روی چند سطر اثر گذاشت — همان عددی است که این بخش رویش حرف دارد.

before = int(q("SELECT COUNT(DISTINCT branch) AS n FROM catalog_wide").iloc[0, 0])

# ۱. ناهنجاری حذف — آخرین کتاب‌های یک شعبه را حذف کن
con.execute("DELETE FROM catalog_wide WHERE branch = ?", ("مرکزی",))
after_delete = int(q("SELECT COUNT(DISTINCT branch) AS n FROM catalog_wide").iloc[0, 0])
con.rollback()

# ۲. ناهنجاری به‌روزرسانی — شهر یک شعبه عوض شد، ولی فقط سطرهای تازه به‌روز شدند
con.execute("UPDATE catalog_wide SET branch_city = ? WHERE branch = ? AND added_at >= ?",
            ("اصفهان (شعبهٔ نو)", "کوشا", "2023-01-01"))
touched = con.execute("SELECT changes()").fetchone()[0]
pairs = int(q("SELECT COUNT(*) AS n FROM (SELECT DISTINCT branch, branch_city FROM catalog_wide)").iloc[0, 0])
con.rollback()

print("شعبه‌های متمایز، قبل از هر کاری :", before)
print("بعد از حذف کتاب‌های یک شعبه     :", after_delete)
print()
print("سطرهایی که به‌روز شدند           :", touched)
print("جفت‌های متمایز «شعبه و شهر»      :", pairs, "برای", before, "شعبه")
شعبه‌های متمایز، قبل از هر کاری : 6
بعد از حذف کتاب‌های یک شعبه     : 5

سطرهایی که به‌روز شدند           : 91
جفت‌های متمایز «شعبه و شهر»      : 7 برای 6 شعبه

دو ناهنجاری، هر دو با عدد:

  • ناهنجاریِ حذف: با پاک کردنِ کتاب‌های یک شعبه، خودِ شعبه هم از پایگاه داده ناپدید شد — از شش به پنج. واقعیتِ «شعبه‌ای به نامِ مرکزی در تهران وجود دارد» هیچ خانهٔ مستقلی نداشت که در آن بماند.
  • ناهنجاریِ به‌روزرسانی: یک UPDATE که فقط بخشی از سطرها را گرفت، و حالا یک شعبه دو شهر دارد — هفت جفت برای شش شعبه. هیچ خطایی، هیچ هشداری. این دقیقاً همان چیزی است که در ترمِ ۳ فصلِ ۱ دیدی که یک شعبه دو عددِ صندلی داشت.

و ناهنجاریِ سوم را نمی‌شود با کد نشان داد، چون کاری است که اصلاً نمی‌شود کرد: شعبهٔ تازه‌ای که هنوز کتابی ندارد، در این جدول جایی برای نشستن ندارد. تنها راهش ساختنِ یک سطرِ کتابِ ساختگی است — که یعنی وارد کردنِ یک دروغ در جدول تا بتوانی یک راست را ثبت کنی.

۲. وابستگیِ تابعی: ابزارِ تشخیص#

سه ناهنجاریِ بالا یک ریشهٔ مشترک دارند و یک آزمونِ ساده مچشان را می‌گیرد.

بپرس: آیا از روی مقدارِ ستونِ الف، مقدارِ ستونِ ب یکتا معلوم می‌شود؟ اگر بله، می‌گوییم ب به الف وابستگیِ تابعی دارد و می‌نویسیم الف → ب.

در جدولِ ما:

  • id → title — از روی شناسهٔ کتاب، عنوانش معلوم است. درست، و بی‌خطر، چون id کلیدِ همین جدول است.
  • branch → branch_city — از روی نامِ شعبه، شهرش معلوم است. درست، و خطرناک، چون branch کلیدِ این جدول نیست.

قاعده در یک جمله: هر وابستگی‌ای که سمتِ چپش کلیدِ جدول نیست، یک جدولِ جدا می‌خواهد. همین. تمامِ صورت‌های نرمال، شکل‌های دقیق‌ترِ همین یک جمله‌اند.

آزمونش هم در SQL یک خط است:

print(q("""
    SELECT branch, COUNT(DISTINCT branch_city) AS cities
    FROM catalog_wide
    GROUP BY branch
    ORDER BY branch
"""))
print()
print("هر شعبه دقیقاً یک شهر دارد؟",
      int(q("""
          SELECT MAX(cities) AS worst FROM (
              SELECT COUNT(DISTINCT branch_city) AS cities
              FROM catalog_wide GROUP BY branch
          )
      """).iloc[0, 0]) == 1)
  branch  cities
0   بهار       1
1   دانش       1
2  مرکزی       1
3   نسیم       1
4   پویا       1
5   کوشا       1

هر شعبه دقیقاً یک شهر دارد؟ True

همهٔ شعبه‌ها دقیقاً یک شهر دارند — یعنی وابستگی برقرار است و جدول هنوز سالم است.

این آزمون را روی هر جفت ستونی که مشکوکی می‌توانی بزنی، و دو کاربردِ کاملاً متفاوت دارد: قبل از طراحی به تو می‌گوید کدام ستون‌ها با هم به جدولِ جدا می‌روند، و بعد از طراحی به تو می‌گوید آیا کسی جایی وابستگی را شکسته یا نه. در بخشِ ۱ همین عدد بعد از آن UPDATE نصفه‌کاره برابرِ ۲ می‌شد.

۳. صورتِ نرمالِ اول: یک خانه، یک مقدار#

حالا بدترین ستونِ این فایل.

print(q("SELECT id, tags FROM catalog_raw ORDER BY id LIMIT 5"))
print()
for tag in ("شعر", "کودک", "تاریخ"):
    n = int(q("SELECT COUNT(*) AS n FROM catalog_raw WHERE tags LIKE ?",
              ("%" + tag + "%",)).iloc[0, 0])
    print(f"  LIKE '%{tag}%' → {n} کتاب")
   id                         tags
0   1                   ترجمه، شعر
1   2                        تاریخ
2   3  ادبیات کودک، شعر، شعر معاصر
3   4                       پرفروش
4   5                   جایزه‌برده

  LIKE '%شعر%' → 418 کتاب
  LIKE '%کودک%' → 448 کتاب
  LIKE '%تاریخ%' → 408 کتاب

سه عدد که هر سه غلط‌اند، و دلیلش از سطرِ سومِ جدولِ بالا معلوم است.

برچسبِ «شعر معاصر» رشتهٔ «شعر» را در خودش دارد، پس هر کتابی که «شعر معاصر» خورده در جست‌وجوی «شعر» هم پیدا می‌شود. همین برای «کودک» و «ادبیات کودک»، و برای «تاریخ» و «تاریخ علم».

و زرنگی فقط تا جایی نجاتت می‌دهد. ممکن است وسوسه شوی الگو را دقیق‌تر کنی، و راهش هم هست: دو سرِ ستون را با همان جداکننده ببند و دنبالِ برچسبِ کامل بگرد. ولی این راه به قراردادی بند است که هیچ‌چیز اجرایش نمی‌کند: یک فاصلهٔ اضافه، یک ویرگولِ لاتین، یا برچسبی که خودش ویرگول دارد، بی‌صدا خرابش می‌کند. مشکل در الگو نیست؛ در این است که یک خانه چند مقدار دارد.

نامِ این قاعده صورتِ نرمالِ اول است: هر خانهٔ جدول دقیقاً یک مقدار دارد. و نشانهٔ نقضش را با یک سؤال می‌گیری: آیا برای خواندنِ این ستون باید متنش را بشکنی؟

یک شاهدِ جانبی که خیلی گویاست: SQLite اصلاً تابعِ تقسیمِ رشته ندارد. برای شکستنِ همین ستون یا باید داده را به پایتون بیاوری یا یک CTE بازگشتی بنویسی — و هر دو یعنی داری کاری می‌کنی که پایگاه داده برایش ساخته نشده.

۴. جدولِ پیوندی: رابطهٔ چندبه‌چند#

یک کتاب چند برچسب دارد و یک برچسب روی چند کتاب است. این رابطهٔ چندبه‌چند است، و راهِ درستِ نگه داشتنش یک جدولِ سوم است که هر سطرش یک جفت را ثبت می‌کند.

con.executescript("""
    DROP TABLE IF EXISTS book_tags;
    DROP TABLE IF EXISTS tags;
    CREATE TABLE tags (
        id   INTEGER PRIMARY KEY,
        name TEXT NOT NULL UNIQUE
    );
    CREATE TABLE book_tags (
        catalog_id INTEGER NOT NULL REFERENCES catalog_raw(id),
        tag_id     INTEGER NOT NULL REFERENCES tags(id),
        PRIMARY KEY (catalog_id, tag_id)
    );
""")

pairs = []
names = {}
for row in q("SELECT id, tags FROM catalog_raw").itertuples(index=False):
    for name in row.tags.split("، "):
        if name not in names:
            names[name] = len(names) + 1
        pairs.append((row.id, names[name]))

con.executemany("INSERT INTO tags VALUES (?, ?)",
                [(number, name) for name, number in names.items()])
con.executemany("INSERT INTO book_tags VALUES (?, ?)", pairs)
con.commit()

print("برچسب‌های متمایز :", int(q("SELECT COUNT(*) AS n FROM tags").iloc[0, 0]))
print("سطرهای جدول پیوندی:", int(q("SELECT COUNT(*) AS n FROM book_tags").iloc[0, 0]))
print("کتاب‌هایی که در جدول پیوندی دیده می‌شوند:",
      int(q("SELECT COUNT(DISTINCT catalog_id) AS n FROM book_tags").iloc[0, 0]))
برچسب‌های متمایز : 10
سطرهای جدول پیوندی: 2189
کتاب‌هایی که در جدول پیوندی دیده می‌شوند: 1200

ده برچسب، ۲٬۱۸۹ جفت، و هر ۱٬۲۰۰ کتاب حاضرند.

PRIMARY KEY (catalog_id, tag_id) همان کلیدِ مرکبِ فصلِ ۲ است و اینجا دقیقاً کارِ درست را می‌کند: یک کتاب نمی‌تواند یک برچسب را دو بار بگیرد. و دو REFERENCES تضمین می‌کنند که هیچ جفتی به کتاب یا برچسبی که وجود ندارد اشاره نکند.

حالا همان سه پرسش، این بار روی مدلِ درست:

print(q("""
    SELECT t.name, COUNT(*) AS books
    FROM book_tags AS bt
    JOIN tags AS t ON t.id = bt.tag_id
    GROUP BY t.id, t.name
    ORDER BY books DESC, t.name
"""))
print()
print("جمع ستون books:",
      int(q("SELECT COUNT(*) AS n FROM book_tags").iloc[0, 0]),
      "| سطرهای جدول پیوندی: 2189")
          name  books
0  ادبیات کودک    250
1        تاریخ    233
2    چاپ نایاب    228
3         کودک    227
4    شعر معاصر    218
5          شعر    213
6        ترجمه    209
7       پرفروش    209
8    تاریخ علم    202
9   جایزه‌برده    200

جمع ستون books: 2189 | سطرهای جدول پیوندی: 2189

«شعر» ۲۱۳ کتاب دارد، نه ۴۱۸. و «کودک» ۲۲۷، نه ۴۴۸.

و شمارشِ دوم همان‌جاست: جمعِ ستونِ books روی ده برچسب باید دقیقاً برابرِ تعدادِ سطرهای جدولِ پیوندی باشد — چون هر سطرِ آن جدول دقیقاً یک بار در یکی از این ده گروه شمرده می‌شود. اگر این دو نخوانند، یا JOIN سطر تکثیر کرده یا برچسبی بدونِ جفت مانده.

✅ چک کن: ۲٬۱۸۹ باید هم جمعِ ستونِ books باشد و هم تعدادِ سطرهای book_tags. و COUNT(DISTINCT catalog_id) باید ۱٬۲۰۰ باشد. برچسب‌های متمایز هم باید ۱۰ باشند: اگر ۱۱ شد، کتابی برچسبِ خالی داشته، چون split روی رشتهٔ خالی فهرستی با یک رشتهٔ خالی می‌دهد و حلقهٔ بالا همان را یک برچسب می‌گیرد.

۵. صورتِ دوم و سوم، به زبانِ ساده#

سه قاعده‌ای که به آن‌ها صورت‌های نرمال می‌گویند، سه شکلِ همان جملهٔ بخشِ ۲ هستند:

  1. صورتِ نرمالِ اول — هر خانه یک مقدار. ستونِ tags این را می‌شکست.
  2. صورتِ نرمالِ دوم — وقتی کلید مرکب است، هیچ ستونی نباید فقط به بخشی از کلید وابسته باشد. اگر در book_tags ستونِ tag_name را هم نگه می‌داشتیم، آن ستون فقط به tag_id وابسته بود نه به کلِ کلید — پس جایش جدولِ tags است.
  3. صورتِ نرمالِ سوم — هیچ ستونی نباید از راهِ یک ستونِ غیرِکلید به کلید وصل شود. در catalog_wide مسیر این بود: id → branch → branch_city. شهر به کتاب وابسته است، ولی از راهِ شعبه — پس جایش جدولِ شعبه‌هاست.

هر سه یک هدف دارند: هیچ واقعیتی دو بار نوشته نشود. اگر همین یک جمله را رعایت کنی، به هر سه رسیده‌ای بدونِ اینکه نامشان را بلد باشی.

و یک هشدارِ لازم: نرمال‌سازی افزونگی را حذف می‌کند، ولی خطای ورودی را درست نمی‌کند. ستونِ نویسنده را که جدا کنیم، همان ۱۲۳ املا برای ۱۲۰ نفر سرِ جایشان می‌مانند. چیزی که عوض می‌شود هزینهٔ درست کردنشان است:

con.executescript("""
    DROP TABLE IF EXISTS authors;
    CREATE TABLE authors (
        id   INTEGER PRIMARY KEY,
        name TEXT NOT NULL UNIQUE
    );
    INSERT INTO authors (name) SELECT DISTINCT author FROM catalog_raw ORDER BY author;
""")
con.commit()

wide_rows = int(q("""
    SELECT COUNT(*) AS n FROM catalog_raw
    WHERE author LIKE ? OR author LIKE ?
""", ("%ي%", "%ك%")).iloc[0, 0])
author_rows = int(q("""
    SELECT COUNT(*) AS n FROM authors
    WHERE name LIKE ? OR name LIKE ?
""", ("%ي%", "%ك%")).iloc[0, 0])

print("سطرهای نویسنده‌ها      :", int(q("SELECT COUNT(*) AS n FROM authors").iloc[0, 0]))
print("سطرهایی که باید در جدولِ پهن اصلاح شوند:", wide_rows)
print("سطرهایی که باید در جدولِ نویسنده‌ها اصلاح شوند:", author_rows)
سطرهای نویسنده‌ها      : 123
سطرهایی که باید در جدولِ پهن اصلاح شوند: 19
سطرهایی که باید در جدولِ نویسنده‌ها اصلاح شوند: 3

نوزده در برابرِ سه.

همان اشتباه، همان داده — ولی در مدلِ نرمال‌شده غلط‌ها سه جای مشخص دارند و در جدولِ پهن نوزده جا. و مهم‌تر از عدد: اصلاحِ یک املا این‌جا نصفه‌کاره نمی‌ماند. یک سطر از authors را که عوض کنی، همهٔ کتاب‌هایش با هم عوض می‌شوند؛ در جدولِ پهن هر سطری که یادت برود، یک تناقضِ تازه است. فقط یک استثنا هست و این دادهٔ ما دقیقاً همان است: اگر املای درست هم از قبل سطرِ خودش را داشته باشد، UNIQUE جلوی UPDATE را می‌گیرد و باید دو سطر را ادغام کنی.

۶. اسکیمای نهایی، و شمارشِ کنترلی#

con.executescript("""
    DROP TABLE IF EXISTS books;
    DROP TABLE IF EXISTS branches;
    CREATE TABLE branches (
        id   INTEGER PRIMARY KEY,
        name TEXT NOT NULL UNIQUE,
        city TEXT NOT NULL
    );
    CREATE TABLE books (
        id        INTEGER PRIMARY KEY,
        title     TEXT    NOT NULL,
        author_id INTEGER NOT NULL REFERENCES authors(id),
        branch_id INTEGER NOT NULL REFERENCES branches(id),
        added_at  TEXT    NOT NULL
    );
""")
con.executemany("INSERT INTO branches (name, city) VALUES (?, ?)",
                sorted(CITY_OF.items()))
con.execute("""
    INSERT INTO books (id, title, author_id, branch_id, added_at)
    SELECT c.id, c.title, a.id, br.id, c.added_at
    FROM catalog_raw AS c
    JOIN authors  AS a  ON a.name  = c.author
    JOIN branches AS br ON br.name = c.branch
""")
con.commit()

for table in ("branches", "authors", "books", "tags", "book_tags"):
    n = int(q(f"SELECT COUNT(*) AS n FROM {table}").iloc[0, 0])
    print(f"  {table:<12} {n:>6} سطر")
print()
print("کتاب‌هایی که در تبدیل گم شدند:",
      1200 - int(q("SELECT COUNT(*) AS n FROM books").iloc[0, 0]))
moved = {"id", "title", "author", "branch", "added_at", "tags"}
columns = set(q("SELECT * FROM catalog_raw LIMIT 0").columns)
print("ستون‌های فایل اصلی           :", len(columns))
print("ستون‌هایی که هنوز منتقل نشده‌اند:", sorted(columns - moved))
  branches          6 سطر
  authors         123 سطر
  books          1200 سطر
  tags             10 سطر
  book_tags      2189 سطر

کتاب‌هایی که در تبدیل گم شدند: 0
ستون‌های فایل اصلی           : 14
ستون‌هایی که هنوز منتقل نشده‌اند: ['audio_minutes', 'borrowed', 'category', 'edition', 'pages', 'price_text', 'rating', 'year']

پنج جدول، و صفرِ خطِ «کتاب‌هایی که در تبدیل گم شدند» همان اثباتی است که لازم داشتیم: هیچ کتابی در تبدیل گم نشد.

و دو خطِ آخر عمدی‌اند و صادقانه: از چهارده ستونِ فایلِ اصلی، هشت‌تا اصلاً منتقل نشدند، چون این فصل دربارهٔ ساختار بود نه دربارهٔ کاملِ کار. اگر این را ننویسی، هر کسی که اسکیمای تازه را ببیند فکر می‌کند مهاجرت تمام شده. فصلِ ۹ همین اسکیما را کامل می‌کند: شش‌تا از این هشت ستون با نوع و قیدِ درست سرِ جایشان می‌روند، و دو ستونِ audio_minutes و borrowed عمداً نمی‌روند. دلیلش را همان‌جا می‌نویسد.

🔧 اگر کار نکرد: اگر بخواهی یک جفتِ برچسب را دوباره وارد کنی، کلیدِ مرکبِ جدولِ پیوندی صریح اعتراض می‌کند:

first = q("SELECT catalog_id, tag_id FROM book_tags ORDER BY catalog_id, tag_id LIMIT 1")
try:
    con.execute("INSERT INTO book_tags VALUES (?, ?)",
                (int(first.iloc[0, 0]), int(first.iloc[0, 1])))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
print()
try:
    con.execute("INSERT INTO book_tags VALUES (?, ?)", (99999, 1))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
sqlite3.IntegrityError
UNIQUE constraint failed: book_tags.catalog_id, book_tags.tag_id

sqlite3.IntegrityError
FOREIGN KEY constraint failed

دو قید، دو پیامِ متفاوت، و هر دو در اسکیما نوشته شده‌اند نه در کد.

ولی خطای واقعیِ این فصل همان ۴۱۸ بود که هیچ پیامی نداشت.

📏 اندازه بگیر: یک سطر یعنی چه؟ در book_tags یک سطر یعنی «یک کتاب، یک برچسب» — نه یک کتاب و نه یک برچسب. به همین دلیل ۲٬۱۸۹ را نمی‌شود با ۱٬۲۰۰ مقایسه کرد. با کدام شمارشِ دوم سنجیدی؟ با سه‌تا: جمعِ ستونِ books روی ده برچسب که برابرِ ۲٬۱۸۹ شد، COUNT(DISTINCT catalog_id) که ۱٬۲۰۰ درآمد یعنی هیچ کتابی بی‌برچسب نماند، و تعدادِ سطرهای books بعد از دو JOIN که باز ۱٬۲۰۰ ماند. چه کسی از قلم افتاد؟ در جدولِ برچسب‌ها هیچ‌کس. ولی در اسکیمای بخشِ ۶ هشت ستون از چهارده ستونِ فایلِ اصلی اصلاً منتقل نشدند، و این را باید همان‌جا نوشت وگرنه کسی فکر می‌کند مهاجرت کامل بوده.

🤖 از دستیارت بپرس: «صورتِ نرمالِ اول یعنی چه و چرا ذخیرهٔ چند مقدار در یک ستون آن را نقض می‌کند؟» بعد این را بپرس: «چرا نمی‌شود با LIKE هوشمندانه‌تر، شمارشِ درست را از یک ستونِ کاماجدا گرفت؟» — جوابِ درست به زیررشته بودنِ یک برچسب در برچسبِ دیگر اشاره می‌کند و می‌گوید بستنِ دو سرِ ستون با جداکننده کار می‌کند، ولی به قراردادی بند است که پایگاه داده اجرایش نمی‌کند. اگر جواب یک الگوی تازه پیشنهاد داد، اول با «شعر» و «شعر معاصر» امتحانش کن، بعد بپرس اگر کسی در یک خانه فاصلهٔ اضافه بگذارد چه می‌شود.

واژه‌های تازهٔ این فصل#

کلمه تلفظ به حروف فارسی یعنی چه
normalization نرمالایزیشن نرمال‌سازی؛ شکستنِ جدول تا هر واقعیت یک جا بماند
functional dependency فانکشنال دیپندنسی وابستگیِ تابعی؛ از روی مقدارِ الف، مقدارِ ب یکتا معلوم است
insert anomaly اینسرت آنومالی ناهنجاریِ درج؛ واقعیتی که جایی برای ثبت شدن ندارد
delete anomaly دیلیت آنومالی ناهنجاریِ حذف؛ حذفِ یک سطر واقعیتِ دیگری را هم می‌برد
junction table جانکشن تیبل جدولِ پیوندی؛ جدولِ سومی که رابطهٔ چندبه‌چند را نگه می‌دارد
first normal form فرست نرمال فرم صورتِ نرمالِ اول؛ هر خانه دقیقاً یک مقدار

تمرین‌ها

اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.

در فصل بعد#

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

پرسشِ «هر کتاب چند بار امانت رفته؟» روی مدلِ نرمال‌شده باید ۲۶ هزار سطرِ رویداد را بگردد. اگر همان عدد را در یک ستونِ کنارِ خودِ کتاب نگه داریم، خواندنش تقریباً رایگان می‌شود. فصلِ بعد هر دو زمان را اندازه می‌گیرد، و بعد هزینهٔ همین کار را هم اندازه می‌گیرد: پس از ۵۰۰ امانتِ تازه که فقط در یک جا ثبت شوند، آن ستون دقیقاً ۵۰۰ واحد دروغ می‌گوید.

به آخر این فصل رسیدی!

اگر ساختی و جواب داد، این دکمه مال توست.