تصميم قواعد البيانات فهم عملية التوحيد Normalization المستخدمة عند تصميم قاعدة البيانات

يجب أن يكون التوحيد Normalization جزءًا من عملية تصميم قاعدة البيانات، ولكن من الصعب فصل عملية التوحيد عن عملية نمذجة الكيان العلائقي ER modelling، لذلك يجب استخدام الطريقتين بصورةٍ متزامنة.

يُستخدَم مخطط الكيان والعلاقة entity relation diagram -أو ERD اختصارًا- لتوفير الرؤية الكبيرة أو الشاملة لمتطلبات بيانات المؤسسة وعملياتها، إذ يَنشأ ذلك من خلال عملية تكرارية تتضمن تحديد الكيانات المرتبطة، وسماتها، وعلاقاتها؛ بينما يركِّز إجراء التوحيد على خصائص كيانات محددَّة، كما يمثِّل رؤيةً مُصغَّرةً للكيانات داخل مخطط ERD.

ما هو التوحيد Normalization؟

التوحيد هو فرع من فروع النظرية العلائقية الذي يوفر رؤى التصميم، وهو عملية تحديد مقدار التكرار redundancy الموجود في الجدول.

أهداف التوحيد هي:

• القدرة على وصف مستوى التكرار في مخطط علائقي.
• توفير آليات لتغيير المخططات بهدف إزالة التكرار.

تعتمد نظرية التوحيد على نظرية الاعتماديات الوظيفية اعتمادًا كبيرًا، وتحدِّد هذه النظرية ستة نماذج موحَّدة normal forms -أو NF اختصارًا-، حيث يتضمن كل نموذج موحَّد مجموعةً من خصائص الاعتمادية التي يجب أن يفي المخطط بها، كما يعطي كل نموذج موحَّد ضمانات حول وجود / أو عدم وجود حالات تحديث شاذة update anomalies، وهذا يعني احتواء النماذج الموحدة الأعلى على عدد أقل من التكرار، وبالتالي، مشاكل تحديث أقل.

النماذج الموحدة Normal Forms

يمكن أن تكون جميع الجداول الموجودة في قواعد البيانات ضمن أحد النماذج الموحَّدة التي سنناقشها الآن. نريد الحد الأدنى من التكرار بين المفتاح الرئيسي PK، والمفتاح الخارجي FK من الناحية المثالية، كما يجب اشتقاق كل شيء آخر من جداول أخرى.

هناك ستة نماذج موحَّدة، ولكننا سنلقي نظرة على النماذج الأربعة الأولى فقط، وهي:

• النموذج الموحَّد الأول First normal form، أو 1NF اختصارًا.
• النموذج الموحَّد الثاني Second normal form، أو 2NF اختصارًا.
• النموذج الموحَّد الثالث Third normal form، أو 3NF اختصارًا.
• نموذج بويس-كود الموحَّد Boyce-Codd normal form، أو BCNF اختصارًا، وهو نادر الاستخدام.

النموذج الموحد الأول 1NF

يُسمَح فقط بقيم غير مكرَّرة في النموذج الموحد الأول عند تقاطع كل صف وعمود، وهذا يؤدي إلى عدم وجود مجموعات مكرَّرة repeating group، حيث يجب إزالة المجموعة المكرَّرة، وتشكيل علاقتين جديدتين لتوحيد علاقة تحتوي على مجموعة مكرَّرة، ويكون المفتاح الرئيسي PK للعلاقة الجديدة هو تركيب من المفتاح الرئيسي PK للعلاقة الأصلية بالإضافة إلى سمة من العلاقة المنشَأة حديثًا للحصول على تعريف فريد.

عملية نموذج 1NF

سنستخدم جدول تقرير درجات الطالب Student_Grade_Report أدناه، من قاعدة بيانات المدرسة School على أساس مثال لشرح عملية نموذج 1NF.

Student_Grade_Report (StudentNo, StudentName, Major, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)

تكون المجموعة المكرَّرة في جدول تقرير درجات الطالب Student Grade Report هي معلومات المقرر الدراسي course، إذ يمكن للطالب أخذ مقررات متعددة.

• أزِل المجموعة المكررَّة، حيث تكون المجموعة المكرَّرة في هذه الحالة هي معلومات المقرر لكل طالب.
• حدِّد المفتاح الرئيسي PK لجدولك الجديد.
• يجب أن يحدِّد المفتاح الرئيسي PK قيمةَ السمة StudentNo وCourseNo تحديدًا فريدًا.
• يتبقى لك جدول مقررات الطلاب StudentCourse بعد إزالة جميع السمات المتعلِّقة بالمقرر والطالب.
• أصبح جدول الطالب Student الآن بصيغة النموذج الموحَّد الأول مع إزالة المجموعة المكرَّرة.
• الجدولان الجديدان موضَّحان كما يلي:

Student (StudentNo, StudentName, Major)

StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)

تحديث حالات نموذج 1NF الشاذة

بالنظر إلى الجدول السابق والذي يسبقه:

• نحتاج طالبًا لإضافة مقرَّر جديد.
• قد يكون لدينا تناقضات عندما تحتاج معلومات المقرَّر إلى تحديث.
• قد نحذف أيضًا معلومات هامة حول مقرر عند حذف طالب.

النموذج الموحد الثاني 2NF

يجب أن تكون العلاقة أولًا بصيغة نموذج 1NF للانتقال إلى النموذج الموحَّد الثاني 2NF، حيث تكون العلاقة تلقائيًا بصيغة نموذج 2NF إذا وفقط إذا اشتمل المفتاح الرئيسي PK على سمة واحدة.

إذا احتوت العلاقة على مفتاح رئيسي مركّب composite PK، فيجب أن تعتمد كل سمةٍ ليست مفتاحًا على المفتاح الرئيسي PK بأكمله اعتمادًا كاملًا، ولا تعتمد على مجموعة فرعية من المفتاح الرئيسي PK، أي لا يجب وجود اعتمادية جزئية partial dependency، أو ما يُسمَّى بالزيادة augmentation.

عملية نموذج 2NF

يجب أولًا أن يكون الجدول بصيغة نموذج 1NF للانتقال إلى النموذج 2NF.

• جدول الطالب Student موجود بالفعل بصيغة نموذج 2NF لأنه يحتوي على عمود مفتاح رئيسي واحد.
• ليست كل السمات -وتحديدًا جميع معلومات المقرر course information- معتمدةً بالكامل على المفتاح الرئيسي عند فحص جدول مقررات الطلاب Student Course، إذ تكون السمة الوحيدة المعتمدة بالكامل على المفتاح الرئيسي هي الدرجة grade.
• عرِّف الجدول الجديد الذي يحتوي على معلومات المقرَّر.
• عرِّف المفتاح الرئيسي PK للجدول الجديد.
• الجداول الثلاثة الجديدة موضَّحة أدناه.

Student (StudentNo, StudentName, Major)

CourseGrade (StudentNo, CourseNo, Grade)

CourseInstructor (CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation)

تحديث حالات نموذج 2NF الشاذة

بالنظر إلى الجداول السابقة:

• نحتاج إلى مقرَّر course عند إضافة مدرِّس جديد.
• قد يؤدي تحديث معلومات المقرر إلى وجود تناقضات في معلومات المدرِّس.
• قد يؤدي حذف المقرر أيضًا إلى حذف معلومات المدرِّس.

النموذج الموحد الثالث 3NF

يجب أن تكون العلاقة بصيغة النموذج الموحَّد الثاني 2NF للانتقال إلى النموذج الموحَّد الثالث 3NF، كما يجب إزالة جميع الاعتماديات المتعدية transitive dependencies أيضًا، فقد لا تعتمد السمة التي ليست مفتاحًا اعتمادًا وظيفيًا على سمة أخرى ليست مفتاحًا.

عملية نموذج 3NF

• أزِل كافة السمات الاعتمادية dependent attributes في العلاقة، أو في العلاقات المتعدية من كل جدول من الجداول التي لها علاقة متعدية.
• أنشِئ جدولًا، أو جداولًا جديدةً مع إزالة الاعتمادية.
• تحقق من الجدول، أو الجداول الجديدة، كما تحقق من الجدول، أو الجداول المعدَّلة أيضًا، وذلك للتأكد من احتواء كل جدول على محدِّد determinant، ومن عدم وجود جدول يحتوي على اعتماديات غير مناسبة.
• الجداول الأربعة الجديدة موضَّحة أدناه.

Student (StudentNo, StudentName, Major)

CourseGrade (StudentNo, CourseNo, Grade)

Course (CourseNo, CourseName, InstructorNo)

Instructor (InstructorNo, InstructorName, InstructorLocation)

يجب ألا تكون هناك حالات شاذة في النموذج الموحَّد الثالث في هذه المرحلة.

لنلقِ نظرةً على مخطط الاعتمادية الموضَّح في الشكل الآتي لهذا المثال، حيث تتمثل الخطوة الأولى في إزالة المجموعات المكرَّرة.

Student (StudentNo, StudentName, Major)
StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)

تلخِّص الاعتماديات الموضَّحة في الشكل التالي عملية توحيد normalization قاعدة بيانات المدرسة School database:

الاختصارات المستخدمة في الشكل السابق هي كما يلي:

• PD: الاعتمادية الجزئية partial dependency.
• TD: الاعتمادية المتعدية transitive dependency.
• FD: الاعتمادية الكاملة full dependency، إذ يشير الاختصار FD إلى الاعتمادية الوظيفية functional dependency عادةً، ولكن استخدمنا الاختصار FD على أساس اختصار للاعتمادية الكاملة full dependency في الشكل السابق فقط.

نموذج بويس-كود الموحد BCNF 

قد تنتج حالات شاذة عندما يحتوي الجدول على أكثر من مفتاح مرشَّح candidate key على الرغم من أن العلاقة بصيغة نموذج 3NF، حيث يُعَدّ نموذج بويس-كود الموحَّد حالةً خاصةً من النموذج 3NF، وتكون العلاقة ضمن نموذج BCNF إذا وفقط إذا كان كل محدّد determinant مفتاحًا مرشَحًا candidate key.

المثال الأول عن نموذج BCNF

ضع في بالك الجدول التالي St_Maj_Adv:

تكون القواعد الدلالية semantic rules -أي قواعد العمل المطبَّقة على قاعدة البيانات- لهذا الجدول هي:

1. يجوز لكل طالب Student التخصص في عدة مواد.
2. يكون لكل طالب معين مدرِّسًا واحدًا فقط لكل تخصص Major.
3. لكل تخصص عدة مدرِّسين.
4. يدرِّس كل مدرِّس تخصصًا واحدًا فقط.
5. يدرِّس كل مدرِّس عدة طلاب في تخصص واحد.

الاعتماديات الوظيفية لهذا الجدول مذكورة أدناه، فالأولى هي مفتاح مرشَّح candidate key، والثانية ليست كذلك.

Student_id, Major ——>  Advisor
Advisor  ——>  Major

تشمل الحالات الشاذة لهذا الجدول ما يلي:

1. الحذف Delete: مثل حالة حذف الطالب معلومات المدرِّس.
2. الإدخال Insert: مثل حالة احتياج المدرِّس الجديد إلى وجود طالب.
3. التحديث Update: مثل الحالات المتناقضة.

ملاحظة: ليست السمة المُفرَدة single attribute مفتاحًا مرشَحًا.

يمكن أن يكون المفتاح الرئيسي PK هو Student_id, Major، أو Student_id, Advisor.

يمكنك إنشاء جدولين جديدين، لتقليل العلاقة St_Maj_Adv إلى النموذج BCNF كما يلي:

St_Adv (Student_id, Advisor)
Adv_Maj (Advisor, Major)

جدول St_Adv:

جدول Adv_Maj:

المثال الثاني عن نموذج BCNF

انظر الجدول التالي Client_Interview:

FD1 – ClientNo, InterviewDate –> InterviewTime, StaffNo, RoomNo  (PK)

FD2 – staffNo, interviewDate, interviewTime –> clientNO      (candidate key: CK)

FD3 – roomNo, interviewDate, interviewTime –> staffNo, clientNo    (CK)

FD4 – staffNo, interviewDate –> roomNo

تكون العلاقة بصيغة نموذج BCNF إذا وفقط إذا كان كل محدّد determinant مفتاحًا مرشَّحًا.

نحن بحاجة إلى إنشاء جدول يتضمن أول ثلاثة اعتماديات كاملة FD -أي جدول Client_Interview2-، وإنشاء جدول آخر -أي جدول StaffRoom- للاعتمادية الكاملة FD الرابعة.

جدول Client_Interview2:

جدول StaffRoom:

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

تأكَّد أثناء عملية توحيد تصميم قاعدة البيانات من توافق الكيانات المقترحة للنموذج الموحَّد المطلوب قبل إنشاء بُنى الجدول.

صُمِّمت العديد من قواعد البيانات واقعيًا بصورة غير سليمة، أو أُثقِل كاهلها بحالات شاذة عند تعديلها بصورة غير سليمة خلال فترة زمنية، وقد يُطلب منك إعادة تصميم قواعد البيانات الحالية، وتعديلها، كما يمكن أن يكون ذلك مهمةً كبيرةً إذا أجريت عملية التوحيد على الجداول بصورةٍ غير صحيحة.

تمارين

اقتباس

ملاحظة: أكمل هذا المقال والمقال السابق قبل حل هذه التمارين.

1. ما هو التوحيد normalization؟
2. متى يكون جدول ما في نموذج 1NF؟ 3.متى يكون جدول ما في نموذج 2NF؟
3. متى يكون جدول ما في نموذج 3NF؟
4. عرِّف وناقش كل من الاعتماديات المشار إليها في مخطط الاعتمادية الموضَّح في الشكل التالي:

6. تستخدم كليّة جامعية college جديدة بنية الجدول الموضَّحة في الشكل التالي، وذلك لتتبع الطلاب والمقرَّرات، وبالتالي، ارسم مخطط الاعتمادية لهذا الجدول.

7. اعرض الجداول بصيغة النموذج الموحَّد الثالث التي ستنشئها لإصلاح المشكلات التي واجهتها باستخدام مخطط الاعتمادية الذي رسمته للتو، ثم ارسم مخطط الاعتمادية للجدول الثابت.
8. توفر الوكالة التي تسمى Instant Cover موظفِين بدوام جزئي / مؤقت للفنادق في اسكتلندا، إذ يوضِّح الشكل الآتي الوقت الذي يقضيه موظفو الوكالة في العمل في فنادق مختلفة، حيث يكون رقم التأمين الوطني NIN -أي national insurance number- فريدًا لكل موظف.

استخدم الشكل التالي للإجابة على السؤالين الآتيين:

• هذا الجدول عرضة لحالات تحديث شاذة، لذلك قدّم أمثلةً على حالات شاذة للإدخال، والحذف، والتحديث.
• طَبّق عملية التوحيد على هذا الجدول ليصبح له صيغة النموذج الموحّد الثالث، مع ذكر أي افتراضات.

9. إملأ الفراغات:
   • ينتج عن __ نموذجًا موحّدًا أقل.
   • تسمى السمة التي تحدِّد قيمتها قيمًا أخرى داخل صف __.
   • السمة التي لا يمكن تقسيمها توصف بأنها ___.
   • يشير __ إلى مستوى التفاصيل الذي تمثِّله القيم المخزَّنة في صف الجدول.
   • يجب ألا يحتوي الجدول العلائقي على مجموعات ___.

ترجمة -وبتصرف- للمقال Normalization لصاحبته Adrienne Watt.

اقرأ أيضًا

• المقال التالي: عملية تطوير قواعد البيانات Database Development
• المقال السابق: الاعتماديات الوظيفية المستخدمة في تصميم قواعد البيانات
• نموذج الكيان والعلاقة ER لتمثيل البيانات وتخزينها في قاعدة البيانات
• المفاهيم الأساسية في قواعد البيانات وتصميمها
• نموذج الكيان والعلاقة ER لتمثيل البيانات وتخزينها في قاعدة البيانات
• النسخة العربية الكاملة لكتاب تصميم قواعد البيانات