MySQL وتصميم قواعد البيانات

التطبيع - 3NF و BCNF

13 دقيقة الدرس 4 من 40

التطبيع - 3NF و BCNF

في هذا الدرس، سنكمل رحلتنا عبر التطبيع من خلال استكشاف الشكل الطبيعي الثالث (3NF) وشكل Boyce-Codd الطبيعي (BCNF). سنناقش أيضاً متى قد يكون إلغاء التطبيع ضرورياً للأداء.

الشكل الطبيعي الثالث (3NF)

الجدول في الشكل الطبيعي الثالث إذا:

قواعد 3NF:

1. يجب أن يكون في 2NF
   - جميع قواعد 2NF محققة
   - لا توابع جزئية

2. لا توابع متعدية
   - الأعمدة غير المفتاحية لا يمكن أن تعتمد على أعمدة غير مفتاحية أخرى
   - كل عمود غير مفتاحي يجب أن يعتمد مباشرة على المفتاح الأساسي
   - إلغاء توابع "A → B → C"

مفهوم رئيسي: في 3NF، كل قطعة معلومات تُخزَّن في مكان واحد فقط، وكل عمود غير مفتاحي يقدم حقيقة عن المفتاح، والمفتاح بالكامل، ولا شيء غير المفتاح.

فهم التوابع المتعدية

التابع المتعدي يحدث عندما يعتمد عمود غير مفتاحي على عمود غير مفتاحي آخر، والذي بدوره يعتمد على المفتاح الأساسي.

مثال على التابع المتعدي:

employee_id → department_id → department_name

- department_name يعتمد على department_id
- department_id يعتمد على employee_id
- لذلك: department_name يعتمد متعدياً على employee_id

المشكلة:
إذا قمت بتغيير اسم قسم، يجب تحديثه في كل سجل موظف،
مما يؤدي إلى شذوذات تحديث وعدم اتساق البيانات.

مثال انتهاك 3NF

❌ ليس في 3NF (لديه تابع متعدي):

CREATE TABLE employees_bad (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    department_name VARCHAR(100),    -- ❌ يعتمد على department_id
    department_location VARCHAR(100) -- ❌ يعتمد على department_id
);

INSERT INTO employees_bad VALUES
(1, 'John Doe', 10, 'Engineering', 'Building A'),
(2, 'Jane Smith', 10, 'Engineering', 'Building A'),
(3, 'Bob Johnson', 20, 'Marketing', 'Building B');

المشاكل:
1. department_name مخزن بشكل متكرر لكل موظف
2. إذا تغير اسم القسم، يجب تحديث صفوف متعددة
3. لا يمكن تخزين معلومات القسم بدون موظف
4. حذف آخر موظف في القسم يفقد بيانات القسم

التحويل إلى 3NF

للتحويل إلى 3NF، قم بإزالة التوابع المتعدية بإنشاء جداول منفصلة:

✓ في 3NF (لا توابع متعدية):

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    hire_date DATE,
    salary DECIMAL(10,2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100),
    department_location VARCHAR(100),
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'John Doe', 10, '2023-01-15', 75000.00),
(2, 'Jane Smith', 10, '2023-02-20', 82000.00),
(3, 'Bob Johnson', 20, '2023-03-10', 68000.00);

INSERT INTO departments VALUES
(10, 'Engineering', 'Building A', 1),
(20, 'Marketing', 'Building B', 3);

الفوائد:
- لا تكرار: معلومات القسم مخزنة مرة واحدة
- التحديث في مكان واحد: تغيير اسم القسم مرة واحدة
- يمكن إضافة أقسام بدون موظفين
- لا شذوذات حذف

مساعد للذاكرة: "المفتاح، والمفتاح بالكامل، ولا شيء غير المفتاح، فليساعدني Codd" (نسبة إلى Edgar F. Codd، منشئ النموذج العلائقي).

مثال 3NF من العالم الحقيقي: كتالوج المنتجات

❌ ليس في 3NF:

CREATE TABLE products_bad (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category_id INT,
    category_name VARCHAR(100),      -- ❌ تابع متعدي
    supplier_id INT,
    supplier_name VARCHAR(100),      -- ❌ تابع متعدي
    supplier_country VARCHAR(50)     -- ❌ تابع متعدي
);

✓ في 3NF:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category_id INT,
    supplier_id INT,
    price DECIMAL(10,2),
    stock_quantity INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
);

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(100),
    description TEXT
);

CREATE TABLE suppliers (
    supplier_id INT PRIMARY KEY,
    supplier_name VARCHAR(100),
    supplier_country VARCHAR(50),
    contact_email VARCHAR(100),
    phone VARCHAR(20)
);

شكل Boyce-Codd الطبيعي (BCNF)

BCNF هو إصدار أكثر صرامة من 3NF. الجدول في BCNF إذا:

قواعد BCNF:

1. يجب أن يكون في 3NF
   - جميع قواعد 3NF محققة

2. لكل تابع دالي (X → Y)، يجب أن يكون X مفتاحاً فائقاً
   - الجانب الأيسر من كل تابع يجب أن يكون مفتاح مرشح
   - لا عمود أو مجموعة أعمدة تحدد آخر ما لم يكن مفتاحاً

ملاحظة: معظم الجداول في 3NF هي أيضاً في BCNF. انتهاكات BCNF نادرة ولكن مهم التعرف عليها.

مثال انتهاك BCNF

❌ في 3NF ولكن ليس في BCNF:

CREATE TABLE course_instructors (
    student_id INT,
    course VARCHAR(100),
    instructor VARCHAR(100),
    PRIMARY KEY (student_id, course)
);

السيناريو:
- كل طالب يمكنه أخذ دورة مرة واحدة فقط
- كل دورة تُدرّس بواسطة مدرس واحد فقط
- طلاب مختلفون في نفس الدورة لديهم نفس المدرس

التوابع الدالية:
- (student_id, course) → instructor  ✓ (المفتاح الأساسي يحدد المدرس)
- course → instructor  ❌ (الدورة تحدد المدرس، لكن الدورة ليست مفتاحاً)

المشكلة:
التابع الثاني ينتهك BCNF لأن "course" ليس مفتاحاً فائقاً،
ومع ذلك يحدد "instructor".

التحويل إلى BCNF

✓ في BCNF:

CREATE TABLE enrollments (
    student_id INT,
    course VARCHAR(100),
    enrollment_date DATE,
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course),
    FOREIGN KEY (course) REFERENCES course_instructors(course)
);

CREATE TABLE course_instructors (
    course VARCHAR(100) PRIMARY KEY,
    instructor VARCHAR(100),
    semester VARCHAR(20)
);

الآن جميع التوابع صالحة:
- course → instructor (course مفتاح ✓)
- (student_id, course) → جميع الأعمدة (مفتاح مركب ✓)

مقارنة 3NF مقابل BCNF

3NF:
- لا توابع متعدية
- كل سمة غير مفتاحية تعتمد على المفتاح
- أسهل في التحقيق
- الأكثر شيوعاً في الممارسة

BCNF:
- أكثر صرامة من 3NF
- كل محدد يجب أن يكون مفتاح مرشح
- يمنع كل التكرار (نظرياً)
- أصعب في التحقيق
- أحياناً يتطلب المزيد من الوصلات

متى تستخدم أيهما:
- استخدم 3NF لمعظم التطبيقات العملية
- استخدم BCNF عندما تكون سلامة البيانات حرجة
- انظر في إلغاء التطبيع للأداء

متى تلغي التطبيع

التطبيع مثالي لسلامة البيانات، ولكن أحياناً إلغاء التطبيع ضروري للأداء:

أسباب صالحة لإلغاء التطبيع:

✓ متطلبات الأداء
  - الاستعلامات تصبح بطيئة جداً مع وصلات كثيرة
  - تطبيقات كثيفة القراءة (تقارير، لوحات معلومات)
  - تخزين مؤقت للبيانات المستخدمة بشكل متكرر

✓ تبسيط الاستعلامات الشائعة
  - تقليل تعقيد الاستعلامات المنفذة بشكل متكرر
  - تجنب وصلات 5+ جداول
  - تحسين وقت الاستجابة للميزات الحرجة

✓ مستودع البيانات
  - قواعد بيانات التحليلات والتقارير
  - تصاميم مخطط النجمة وندفة الثلج
  - تخزين البيانات التاريخية

✓ القيم المحسوبة
  - تخزين عدادات إجمالية (total_orders، total_spent)
  - تخزين مؤقت للحسابات المكلفة
  - التحديث عبر المحفزات أو وظائف دفعية

تحذير: إلغاء التطبيع يقدم تكرار البيانات. تأكد دائماً من وجود آليات (محفزات، منطق التطبيق، أو وظائف دفعية) للحفاظ على البيانات الملغاة التطبيع متزامنة.

مثال إلغاء التطبيع

محسّن (3NF):

-- جدول الطلبات
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

-- جدول عناصر الطلب
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2)
);

-- استعلام للحصول على إجمالي الطلب (يتطلب JOIN و SUM)
SELECT o.order_id, SUM(oi.quantity * oi.price) as total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id;

ملغى التطبيع (للأداء):

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2)  -- ✓ ملغى التطبيع: يخزن قيمة محسوبة
);

-- تحديث total_amount عند تغيير العناصر (باستخدام محفز)
CREATE TRIGGER update_order_total
AFTER INSERT ON order_items
FOR EACH ROW
UPDATE orders
SET total_amount = (
    SELECT SUM(quantity * price)
    FROM order_items
    WHERE order_id = NEW.order_id
)
WHERE order_id = NEW.order_id;

المقايضة:
- قراءات أسرع (لا حاجة لـ JOIN أو SUM)
- كتابات أبطأ (عبء المحفز)
- تكرار البيانات (الإجمالي مخزن مرتين)
- يجب الحفاظ على الاتساق

مقايضات التطبيع

إيجابيات التطبيع الكامل (3NF/BCNF):
✓ لا تكرار للبيانات
✓ أسهل للحفاظ على سلامة البيانات
✓ حجم قاعدة بيانات أصغر
✓ كتابات أسرع (أماكن أقل للتحديث)
✓ يمنع الشذوذات

سلبيات التطبيع الكامل:
❌ استعلامات أكثر تعقيداً (JOINs كثيرة)
❌ أداء قراءة أبطأ
❌ أصعب للفهم لغير التقنيين
❌ مزيد من المفاتيح الخارجية للإدارة

أفضل ممارسة:
- ابدأ بـ 3NF
- ألغِ التطبيع بشكل استراتيجي حيث ثبتت الضرورة
- وثّق قرارات إلغاء التطبيع
- نفّذ ضمانات للحفاظ على الاتساق

أنماط إلغاء التطبيع العملية

1. ذاكرات التخزين المؤقت للعدادات
جدول users: أضف عمود "posts_count"
UPDATE users SET posts_count = posts_count + 1 WHERE id = ?;

2. بيانات اللقطة
تخزين سعر المنتج في وقت الطلب (قد يتغير السعر لاحقاً)
order_items: product_id، price_at_purchase

3. العروض المادية
احسب مسبقاً التجميعات المعقدة
CREATE VIEW monthly_sales AS SELECT...

4. الطوابع الزمنية المتكررة
تخزين كل من created_at و updated_at للتصفية السريعة
INDEX على updated_at لاستعلامات التغييرات الأخيرة

تمرين تطبيقي:

حدد الشكل الطبيعي وحوّل إلى 3NF:

CREATE TABLE book_sales (
    sale_id INT PRIMARY KEY,
    book_isbn VARCHAR(13),
    book_title VARCHAR(200),
    author_name VARCHAR(100),
    author_country VARCHAR(50),
    publisher_name VARCHAR(100),
    publisher_city VARCHAR(50),
    quantity INT,
    sale_date DATE
);

التحليل:

الحالة الحالية:
- في 1NF: ✓ جميع القيم ذرية
- في 2NF: ✓ مفتاح أساسي عمود واحد (لا توابع جزئية)
- في 3NF: ❌ توابع متعدية متعددة

التوابع المتعدية:
- book_isbn → book_title (معلومات الكتاب تعتمد على ISBN)
- book_isbn → author_name (المؤلف يعتمد على الكتاب)
- author_name → author_country (البلد يعتمد على المؤلف)
- book_isbn → publisher_name (الناشر يعتمد على الكتاب)
- publisher_name → publisher_city (المدينة تعتمد على الناشر)

حل 3NF:

CREATE TABLE books (
    book_isbn VARCHAR(13) PRIMARY KEY,
    book_title VARCHAR(200),
    author_id INT,
    publisher_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id),
    FOREIGN KEY (publisher_id) REFERENCES publishers(publisher_id)
);

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(100),
    author_country VARCHAR(50)
);

CREATE TABLE publishers (
    publisher_id INT PRIMARY KEY,
    publisher_name VARCHAR(100),
    publisher_city VARCHAR(50)
);

CREATE TABLE book_sales (
    sale_id INT PRIMARY KEY,
    book_isbn VARCHAR(13),
    quantity INT,
    sale_date DATE,
    FOREIGN KEY (book_isbn) REFERENCES books(book_isbn)
);

الملخص

في هذا الدرس، تعلمت:

3NF يلغي التوابع المتعدية بين الأعمدة غير المفتاحية
في 3NF، الأعمدة غير المفتاحية تعتمد فقط على المفتاح الأساسي
BCNF أكثر صرامة: كل محدد يجب أن يكون مفتاح مرشح
معظم الجداول في 3NF هي أيضاً في BCNF
إلغاء التطبيع يقايض سلامة البيانات بأداء الاستعلام
ألغِ التطبيع بشكل استراتيجي عند الضرورة المثبتة
استخدم المحفزات أو منطق التطبيق للحفاظ على البيانات الملغاة التطبيع

التالي: في الدرس التالي، سنتعلم عن اختيار أنواع البيانات الصحيحة وأفضل ممارسات تصميم الأعمدة!