الدرس 13 · الوحدة 6: تحليل وتحسين نماذج البيانات

التطبيع: الصيغ 1NF و 2NF و 3NF

ثلاث خطوات تجعل كل حقيقة مخزنة مرة واحدة فقط. ⏱ نحو 35 دقيقة.

بعد هذا الدرس ستستطيع أن:

تعرّف التطبيع والصيغ الطبيعية الثلاث.
تحوّل جدولًا إلى 1NF بفك القيم المتعددة.
تكتشف الاعتماد الجزئي وتعالجه للوصول إلى 2NF.
تكتشف الاعتماد المتعدي وتعالجه للوصول إلى 3NF.

١ما التطبيع؟

التطبيع Normalization: خطوات منظمة نقسّم بها الجدول حتى تُخزَّن كل حقيقة مرة واحدة فقط، فتختفي شذوذات الدرس السابق. وكل خطوة تسمى صيغة طبيعية Normal Form، وكل صيغة تشترط تحقق التي قبلها:

الصيغة	الشرط باختصار	تذكير سهل
1NF الأولى	كل خلية فيها قيمة واحدة، ولا مجموعات مكررة، وللجدول مفتاح.	لا قوائم داخل الخلية.
2NF الثانية	1NF + كل عمود يعتمد على المفتاح كله، لا على جزء منه.	مهم حين يكون المفتاح مركّبًا.
3NF الثالثة	2NF + لا عمود يعتمد على عمود آخر ليس مفتاحًا.	لا اعتماد متعدٍّ (عبر وسيط).

جملة يحفظها المصممون: كل عمود يعتمد على المفتاح (1NF)، والمفتاح كله (2NF)، ولا شيء غير المفتاح (3NF).

٢البداية: جدول غير مطبَّع

جدول تسجيل في الكلية. لاحظ عمود المقررات: قائمة داخل خلية واحدة:

student_id	student_name	dept	building	courses
1001	أحمد السلمي	البرمجيات	مبنى أ	111, 121, 131
1003	عمر الحربي	الشبكات	مبنى ب	211, 111

كيف تبحث عن «كل طلاب المقرر 111»؟ أو تسجّل درجة كل مقرر؟ لا يمكن بسهولة.

٣الصيغة الأولى 1NF: قيمة واحدة في كل خلية

نفكّ القائمة: صف لكل طالب في كل مقرر. والآن يتكون المفتاح من عمودين: (student_id, course)، ونضيف الدرجة وعنوان المقرر:

student_id	course	student_name	dept	building	course_title	grade
1001	111	أحمد السلمي	البرمجيات	مبنى أ	أساسيات البرمجة	95
1001	121	أحمد السلمي	البرمجيات	مبنى أ	قواعد البيانات	92
1003	111	عمر الحربي	الشبكات	مبنى ب	أساسيات البرمجة	55

صار الجدول في 1NF. لكن الخلايا الملوّنة تكررت: اسم أحمد وقسمه ومبناه يتكررون مع كل مقرر، وعنوان المقرر 111 يتكرر مع كل طالب.

٤الصيغة الثانية 2NF: الاعتماد على المفتاح كله

المفتاح (student_id, course). لنسأل عن كل عمود: على ماذا يعتمد؟

العمود	يعتمد على	الحكم
`grade`	الطالب و المقرر معًا	✓ على المفتاح كله
`student_name`، `dept`، `building`	`student_id` وحده	✗ اعتماد جزئي
`course_title`	`course` وحده	✗ اعتماد جزئي

العلاج: كل اعتماد جزئي ينتقل إلى جدول مفتاحه ذلك الجزء:

students: (student_id، student_name، dept، building)
courses: (course، course_title)
enrollments: (student_id، course، grade)

٥الصيغة الثالثة 3NF: لا اعتماد متعدٍّ

في جدول students بقيت مشكلة: المبنى لا يعتمد على الطالب مباشرة، بل على قسمه:

student_id → dept → building

هذا اعتماد متعدٍّ Transitive Dependency. والنتيجة: لو انتقل قسم البرمجيات إلى مبنى جديد، نعدّل صفوف كل طلابه (شذوذ تحديث). العلاج: جدول للأقسام.

والتصميم النهائي في 3NF، أربعة جداول. شغّله، ثم جرّب نقل القسم إلى مبنى آخر بتعديل صف واحد:

query.sql

CREATE TABLE departments (  name     TEXT PRIMARY KEY,  building TEXT);CREATE TABLE students (  id   INTEGER PRIMARY KEY,  name TEXT NOT NULL,  dept TEXT REFERENCES departments(name));CREATE TABLE courses (  code  TEXT PRIMARY KEY,  title TEXT NOT NULL);CREATE TABLE enrollments (  student_id INTEGER REFERENCES students(id),  course     TEXT REFERENCES courses(code),  grade      INTEGER,  PRIMARY KEY (student_id, course));INSERT INTO departments VALUES ('البرمجيات', 'مبنى أ'), ('الشبكات', 'مبنى ب');INSERT INTO students VALUES (1001, 'أحمد السلمي', 'البرمجيات'), (1003, 'عمر الحربي', 'الشبكات');INSERT INTO courses VALUES ('111', 'أساسيات البرمجة'), ('121', 'قواعد البيانات'), ('131', 'الواجهات الأمامية'), ('211', 'الشبكات');INSERT INTO enrollments VALUES (1001, '111', 95), (1001, '121', 92), (1001, '131', 88), (1003, '211', 70), (1003, '111', 55); SELECT * FROM departments;

النتيجة · صفّان

name	building
البرمجيات	مبنى أ
الشبكات	مبنى ب

والمعلومات كلها ما زالت موجودة، نجمعها حين نحتاجها بالاستعلام. هذا مثال يسبق درس الربط JOIN:

query.sql

CREATE TABLE departments (  name     TEXT PRIMARY KEY,  building TEXT);CREATE TABLE students (  id   INTEGER PRIMARY KEY,  name TEXT NOT NULL,  dept TEXT REFERENCES departments(name));CREATE TABLE courses (  code  TEXT PRIMARY KEY,  title TEXT NOT NULL);CREATE TABLE enrollments (  student_id INTEGER REFERENCES students(id),  course     TEXT REFERENCES courses(code),  grade      INTEGER,  PRIMARY KEY (student_id, course));INSERT INTO departments VALUES ('البرمجيات', 'مبنى أ'), ('الشبكات', 'مبنى ب');INSERT INTO students VALUES (1001, 'أحمد السلمي', 'البرمجيات'), (1003, 'عمر الحربي', 'الشبكات');INSERT INTO courses VALUES ('111', 'أساسيات البرمجة'), ('121', 'قواعد البيانات'), ('131', 'الواجهات الأمامية'), ('211', 'الشبكات');INSERT INTO enrollments VALUES (1001, '111', 95), (1001, '121', 92), (1001, '131', 88), (1003, '211', 70), (1003, '111', 55); SELECT students.name, courses.title, enrollments.grade, departments.buildingFROM enrollmentsJOIN students    ON students.id = enrollments.student_idJOIN courses     ON courses.code = enrollments.courseJOIN departments ON departments.name = students.dept;

النتيجة · 5 صفوف

name	title	grade	building
أحمد السلمي	أساسيات البرمجة	95	مبنى أ
أحمد السلمي	قواعد البيانات	92	مبنى أ
أحمد السلمي	الواجهات الأمامية	88	مبنى أ
عمر الحربي	الشبكات	70	مبنى ب
عمر الحربي	أساسيات البرمجة	55	مبنى ب

٦هل نطبّع دائمًا إلى أقصى حد؟

3NF هدف كل تصميم في الأنظمة اليومية (التسجيل، والمبيعات، والمخازن). وتوجد صيغ أعلى (BCNF و 4NF و 5NF) لحالات نادرة. وأحيانًا يتعمد المصمم عكس التطبيع Denormalization، فيكرر معلومة لتسريع التقارير الضخمة، وهو قرار أداء يُتخذ بوعي، كما سترى في الدرس القادم.

توقّع وتحقّق

خلية فيها «111, 121, 131» تخالف:

قيمة واحدة في كل خلية.

عمود يعتمد على جزء من المفتاح المركّب يخالف:

اعتماد جزئي.

student_id → dept → building اعتماد:

Transitive.

الهدف المعتاد للأنظمة اليومية:

توازن جيد.

الخلاصة

1NF: قيمة واحدة في كل خلية، وللجدول مفتاح.
2NF: لا اعتماد على جزء من المفتاح المركّب.
3NF: لا اعتماد على عمود غير مفتاح (لا اعتماد متعدٍّ).
كل عمود يعتمد على المفتاح، والمفتاح كله، ولا شيء غير المفتاح.
بعد التقسيم نربط الجداول بالمفاتيح الأجنبية، ونجمعها بالاستعلام.

تحدٍّ برمجي: طبّع جدول الفواتير

صعب

لديك جدول فواتير مسطح. أنشئ بدلًا منه تصميمًا في 3NF بأربعة جداول، بأسماء الأعمدة المحددة بالضبط (لا تحتاج نقل البيانات).

المطلوب:

customers: phone (مفتاح نصي)، name.
items: code (مفتاح نصي)، name، unit_price.
invoices: id (مفتاح)، invoice_date، customer_phone (مفتاح أجنبي).
invoice_lines: invoice_id و item_code (مفتاح مركّب، وكل منهما مفتاح أجنبي)، qty.

👀 اعرض النتيجة المطلوبة

بعد التنفيذ: SELECT name FROM sqlite_master WHERE type = 'table' AND name <> 'invoices_flat' ORDER BY name

name
customers
invoice_lines
invoices
items

بعد التنفيذ: SELECT m.name, p.name FROM sqlite_master m, pragma_table_info(m.name) p WHERE m.type = 'table' AND m.name <> 'invoices_flat' ORDER BY m.name, p.cid

name	name
customers	phone
customers	name
invoice_lines	invoice_id
invoice_lines	item_code
invoice_lines	qty
invoices	id
invoices	invoice_date
invoices	customer_phone
items	code
items	name
items	unit_price

بعد التنفيذ: SELECT m.name, f."from", f."table" FROM sqlite_master m, pragma_foreign_key_list(m.name) f WHERE m.type = 'table' ORDER BY m.name, f."from"

name	from	table
invoice_lines	invoice_id	invoices
invoice_lines	item_code	items
invoices	customer_phone	customers

✓ التحقق الآلي من حلّك:

الكود يعمل بلا أخطاء
الجداول المطلوبة موجودة
أعمدة الجداول الأربعة صحيحة
المفاتيح الأجنبية الثلاثة في مكانها
لا يتكرر الصنف نفسه في الفاتورة نفسها

💡 تلميح 1

اسأل عن كل عمود في الجدول المسطح: على ماذا يعتمد؟ الاسم يعتمد على الجوال، والسعر على رمز الصنف، والتاريخ على رقم الفاتورة، والكمية على الفاتورة والصنف معًا.

💡 تلميح 2

invoice_lines يشبه enrollments في القسم 5.

حاول بنفسك أولًا؛ للمسألة أكثر من حلّ صحيح.

solution.sql

-- الجدول المسطح (لا تعدّله)CREATE TABLE invoices_flat (  invoice_id INTEGER, invoice_date TEXT,  customer_phone TEXT, customer_name TEXT,  item_code TEXT, item_name TEXT, unit_price REAL, qty INTEGER); CREATE TABLE customers (  phone TEXT PRIMARY KEY,  name  TEXT);CREATE TABLE items (  code       TEXT PRIMARY KEY,  name       TEXT,  unit_price REAL);CREATE TABLE invoices (  id             INTEGER PRIMARY KEY,  invoice_date   TEXT,  customer_phone TEXT REFERENCES customers(phone));CREATE TABLE invoice_lines (  invoice_id INTEGER REFERENCES invoices(id),  item_code  TEXT REFERENCES items(code),  qty        INTEGER,  PRIMARY KEY (invoice_id, item_code));

انتقل إلى تقييم الدرساختيار من متعدد، وصح وخطأ، وأكمل الفراغ، وتتبّع الناتج.ابدأ التقييم ←

📝 معلم؟ أرسل ملاحظة للمؤلف عن هذا الدرس