# فصل ۰۴ — هر واقعیت فقط یک جا

<div dir="rtl">

# فصل ۰۴ — هر واقعیت فقط یک جا

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

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

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

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

![یک بایگانی که یک برگهٔ واحد در ده کشوی مختلف کپی شده و یکی از کپی‌ها با بقیه فرق دارد](/lesson-assets/t4-s04-normalization/prompt/01-image-one-fact-many-drawers.png)

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

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

## قبل از شروع

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

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

> 📓 **نوت‌بوک:** [نوت‌بوک این فصل را در Colab باز کن](/notebooks/avand/term-4-modelling/session-04-normalization.ipynb) — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

</div>

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

<div dir="rtl">

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

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

</div>

```python
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"))
```

```text
   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
```

<div dir="rtl">

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

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

</div>

```python
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, "شعبه")
```

```text
شعبه‌های متمایز، قبل از هر کاری : 6
بعد از حذف کتاب‌های یک شعبه     : 5

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

<div dir="rtl">

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

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

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

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

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

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

در جدولِ ما:

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

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

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

</div>

```python
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)
```

```text
  branch  cities
0   بهار       1
1   دانش       1
2  مرکزی       1
3   نسیم       1
4   پویا       1
5   کوشا       1

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

<div dir="rtl">

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

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

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

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

</div>

```python
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} کتاب")
```

```text
   id                         tags
0   1                   ترجمه، شعر
1   2                        تاریخ
2   3  ادبیات کودک، شعر، شعر معاصر
3   4                       پرفروش
4   5                   جایزه‌برده

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

<div dir="rtl">

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

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

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

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

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

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

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

</div>

```python
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]))
```

```text
برچسب‌های متمایز : 10
سطرهای جدول پیوندی: 2189
کتاب‌هایی که در جدول پیوندی دیده می‌شوند: 1200
```

<div dir="rtl">

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

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

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

</div>

```python
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")
```

```text
          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
```

<div dir="rtl">

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

و شمارشِ دوم همان‌جاست: **جمعِ ستونِ `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`. شهر به کتاب وابسته است، ولی **از راهِ شعبه** — پس جایش جدولِ شعبه‌هاست.

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

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

</div>

```python
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)
```

```text
سطرهای نویسنده‌ها      : 123
سطرهایی که باید در جدولِ پهن اصلاح شوند: 19
سطرهایی که باید در جدولِ نویسنده‌ها اصلاح شوند: 3
```

<div dir="rtl">

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

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

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

</div>

```python
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))
```

```text
  branches          6 سطر
  authors         123 سطر
  books          1200 سطر
  tags             10 سطر
  book_tags      2189 سطر

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

<div dir="rtl">

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

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

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

</div>

```python
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)
```

```text
sqlite3.IntegrityError
UNIQUE constraint failed: book_tags.catalog_id, book_tags.tag_id

sqlite3.IntegrityError
FOREIGN KEY constraint failed
```

<div dir="rtl">

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

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

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

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

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

</div>

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

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: چرا `LIKE '%کودک%'` عددِ ۴۴۸ داد در حالی که جوابِ درست ۲۲۷ است؟

**تمرینِ اجباری:** آزمونِ وابستگیِ تابعیِ بخشِ ۲ را روی `members_raw` بزن. برای هر جفتِ `city → ?` و `phone → ?` بپرس آیا از روی مقدارِ سمتِ چپ، سمتِ راست یکتا معلوم می‌شود. **دستِ‌کم سه جفت را امتحان کن و برای هرکدام `MAX(COUNT(DISTINCT ...))` را چاپ کن.** بعد جواب بده: **کدام جفت وابستگی دارد و آیا آن وابستگی به یک جدولِ جدا نیاز دارد؟** جوابت باید بگوید سمتِ چپ کلید هست یا نه.

**تمرینِ ستاره‌دار ⭐:** جدولِ `book_tags` را طوری بشکن که یک ناهنجاری بسازد: ستونِ `tag_name` را هم به آن اضافه کن، مقدارها را پر کن، و بعد نامِ یک برچسب را **فقط در نصفِ سطرها** عوض کن. **بشمار چند نامِ متمایز برای همان `tag_id` پیدا می‌شود.** بعد سؤالِ سخت را جواب بده: **این کدام صورتِ نرمال را می‌شکند و چرا همان قید `UNIQUE` جدولِ `tags` نتوانست جلویش را بگیرد؟** جوابت باید بگوید قید روی کدام جدول بود و داده در کدام جدول خراب شد.

## در فصل بعد

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

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

</div>

---
Source: https://yaadestan.com/courses/avand/lessons/t4/s04-normalization (یادستان, Yaadestan)
