علاقات واحد إلى متعدد

لماذا ترك مدير قاعدة البيانات زوجته؟ لأنها كانت لديها علاقات واحد إلى متعدد. —مؤلف مجهول

في الفصل التالي، نقدّم استعلامات SQL التي تمتد عبر عدة جداول باستخدام JOINS. وللتدريب، ستحتاج إلى مخطط يتكوّن من عدة جداول. ويقدّم لك هذا الفصل مخططًا صغيرًا فيه علاقة واحد إلى متعدد (one-to-many) بين جدولين. وفي أحد دروس النمذجة التالية تُفصَّل النظرية الكامنة وراء هذا الفصل العملي تفصيلًا كاملًا. وهنا لن تجد سوى مقدمة موجزة.

جدولان

في الفصول السابقة، جرى تطوير مكوّنات جملة SELECT المختلفة. واستخدمنا أساسًا جدولًا كان ترجمة لهذا النموذج المفاهيمي (conceptual model):

جدولان منفصلان

يحتوي هذا المخطط على نوع كيان واحد له عدد من الخواص. وإحدى تلك الخواص رمز المحاضر لمنسّق المقرر. فلكل مقرر منسّق فريد في النهاية. ومن المنطقي إضافة نوع كيان ثانٍ "Lecturer" إلى المخطط. فلدينا في النهاية كثير من المعلومات عن المحاضرين. ويحتوي نوع الكيان هذا معلومات عن جميع المحاضرين، لا عن منسّقي المقررات فقط. والمحاولة الساذجة هي الشكل التالي:

وإذا ترجمت هذا إلى PostgreSQL فتحصل على جدولين منفصلين. غير أن هذه البنية مصدر مشكلات. ونذكر خطأين محتملين:

• يضيف شخص صفًا (= مقررًا جديدًا) إلى جدول "Course" بمنسّق غير موجود في جدول "Lecturer".
• يحذف شخص صفًا في جدول "Lecturer". غير أن المحاضر في هذا الصف منسّق لمقرر أيضًا.

وسيؤدي كلا العمليتين إلى أن تحتوي قاعدة البيانات أخطاء. فلن تستطيع بعد ذلك البحث عن معلومات بعض المقررات بشأن منسّق المقرر. وهذا ليس حلًا جيدًا. أنت تريد أن يحميك نظام إدارة قواعد البيانات العلائقية (RDBMS) من العمليات التي تُدخل أخطاء إلى المخطط.

جداول مترابطة

المشكلة، بالطبع، أن الجدولين لا يمكن أن يوجدا منفصلين. فهناك علاقة بينهما. ويجعل النموذج المفاهيمي في الشكل أدناه هذه العلاقة ظاهرة:

من اليمين إلى اليسار تقرأ العلاقة "هو منسّق لـ"، ومن اليسار إلى اليمين يمكن أن تصبح شيئًا على غرار "له منسّق". ولكل مقرر منسّق واحد على الأقل وواحد على الأكثر (أي واحد فقط). وليس كل محاضر منسّقًا لمقرر، لكن قد يكون المحاضر منسّقًا لعدة مقررات. والتعددية على جهة المحاضر هي إذن (0, N). وهذا مثال على علاقة واحد إلى متعدد.

ولاحظ أن الخاصية "Coordinator" من شكل سابق أُسقطت. فقد استُبدلت فعلًا بعلاقة مع نوع كيان جديد "Lecturer" لأننا نشعر بالحاجة إلى أكثر من مجرد تتبّع رقم المحاضر.

الترجمة إلى النموذج العلائقي

فكيف تترجم الآن علاقة واحد إلى متعدد هذه إلى نموذج علائقي؟ ففي النهاية، PostgreSQL مثال على قاعدة بيانات علائقية. وإذا ترجمنا النموذج المفاهيمي من القسم السابق إلى نموذج منطقي، فسيلزم أن يكون ذلك النموذج المنطقي نموذجًا علائقيًا.

ويمكنك دراسة كل شيء عن النماذج المنطقية بالتفصيل في فصل "نموذج البيانات المنطقي". والغرض الوحيد من هذا القسم هو تقديمك بإيجاز إلى هذا الموضوع حتى نمضي إلى الجزء التالي في استكشافنا للغة SQL (JOIN). ويبدو النموذج المنطقي هكذا:

وما يكفي الآن أن تستطيع قراءة هذا المخطط، وهو ليس بالصعب:

• يصبح كل نوع كيان جدولًا، يمثله مستطيل بعنوان: "Course" و"Lecturer".
• وتصبح كل خاصية لنوع كيان عمودًا في الجدول.
• ولكل جدول مفتاح أساسي (PK)، ممثّل هنا بالأصفر. ولذلك المفتاح الأساسي قيمة فريدة لكل صف في الجدول.
• ويجب أن يكون لكل وحدة مقرر منسّق واحد. لذا توجد علاقة بين الجدولين. وفي جدول "Course" أُضيف عمود "Coordinator". وهذا العمود ما يسمى "مفتاحًا أجنبيًا" (FK، ويُشار إليه بالأخضر) لأن كل قيمة من قيم هذا العمود يجب أن تظهر مفتاحًا أساسيًا لجدول "Lecturer".
• ويوضح الرابط بين الجدولين أن محتوى العمود يوافق قيمة العمود "Lecturer_id". ويرمز الرمز (شرطتان) في الدائرة الزرقاء الصغيرة إلى أنه يجب أن يكون لكل وحدة مقرر محاضر واحد منسّقًا.
• وبالعكس، يوضح الرمز في الدائرة البرتقالية الأكبر أن كل محاضر يمكن أن يكون منسّقًا لصفر (نقطة) أو واحد أو أكثر من المقررات (قدم الغراب).

ويُحوَّل هذا النموذج المنطقي أخيرًا إلى نموذج فيزيائي، حيث نحدد معلومات إضافية تعتمد على الخصائص المحددة لنظام إدارة قواعد البيانات العلائقية المختار (في حالتنا PostgreSQL). ومن الأمثلة على ذلك أنواع بيانات الأعمدة.

ورُسم النموذج الفيزيائي بـ DBSchema. وسنغطّي ذلك بالتفصيل لاحقًا. ويقابل هذا النموذج الفيزيائي النموذجَ المنطقي بتدوين مختلف قليلًا (لاحظ السهم الأزرق المنطلق الذي يشير إلى أن هذا الحقل مفتاح أجنبي يشير إلى السهم الأصفر-البنّي الواصل عند lecturer_id). كما أُعطي كل حقل نوع بيانات (char وdate...):

وتتم إضافة علاقة واحد إلى متعدد هذه في قاعدة البيانات كما يلي:

1. أنشئ أولًا جدول "Lecturer".
2. ابحث عن المفتاح الأساسي لجدول "Lecturer". وهذا المفتاح الأساسي حقل "lecturer_id". ونوع بيانات هذا المفتاح الأساسي char(8)، لأن رمز المحاضر يبدأ دائمًا بـ "u" متبوعًا بسبعة أرقام. ولاحظ أننا نستخدم في الواقع التعيينين "حقل" و"عمود" بالتبادل.
3. وفي جدول "Course" ينبغي الآن إنشاء عمود بنوع البيانات نفسه بالضبط الذي للمفتاح الأساسي في جدول المحاضر، أي char(8) أيضًا. ونسمي ذلك "نقل المفتاح الأساسي" ونشير إليه في جدول المقررات بأنه "مفتاح أجنبي".
4. اربط الآن هذين العمودين بقيد (constraint).
5. ثم أدخل البيانات أولًا في جدول "Lecturer".
6. وبعد ذلك يمكنك ملء جدول "Course" بالصفوف.

التنفيذ في مخططك الخاص

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

وإذا اتبعت خارطة الطريق أدناه (المبنية على الخطوات في القسم السابق) فستحصل على جدولين مترابطين مملوءين بالبيانات. وسنضع بعض التمارين الصغيرة على هذا المخطط. وفي الفصل التالي عن JOIN سنستخدم هذا المخطط أساسًا.

وتفترض الخطوات التالية أن لكل شخص مخططه الخاص بالاسم "rxxxxxxx"، مع استخدام رقم r الخاص به بالطبع. وأسهل طريقة هي أن تستخدم اتصال التجميع على المنفذ 62526. وتحقق من مخططك مما إذا كان جدول "Course" أو "Lecturer" موجودًا فيه بالفعل. فإن كان كذلك، أزل هذا الجدول بـ DROP TABLE ... في أداة استعلامات. وتذكّر: إما أن تستخدم لكل اسم جدول أولًا اسم المخطط متبوعًا بنقطة ثم اسم الجدول، وإما أن تحدد search_path بشكل صحيح. ونزّل الملف "lecturer-course-SQL.txt". وتستخدم الخطوات التالية أجزاءً من شيفرة SQL هذه لبناء كل شيء وملئه بالبيانات. ويمكنك في كل مرة نسخ جزء من هذا المستند النصي ولصقه في أداة الاستعلامات وتنفيذه. أنشئ جدول "Lecturer" بتنفيذ شيفرة CREATE الخاصة بهذا الجدول من الملف الذي نزّلته للتو. لا جديد هنا: حقلان غير مطلوبين، وبعض أنواع بيانات مختلفة، ومفتاح أساسي... ثم أنشئ جدول "Course" بالنسخ واللصق من ملف txt. وتحتاج هذه الشيفرة إلى قليل من الشرح:

CREATE TABLE rxxxxxxx.course (
  code char(6) NOT NULL ,

  ...

  coordinator char(8) NOT NULL ,
  CONSTRAINT pk_course_code PRIMARY KEY ( code ),
  CONSTRAINT fk_course_lecturer FOREIGN KEY ( coordinator )
    REFERENCES rxxxxxxx.lecturer( lecturer_id )
);

وهذا القيد CONSTRAINT الأخير جديد. وبترجمة فضفاضة يقول هذا القيد ما يلي: "يشير العمود "coordinator" في هذا الجدول إلى المفتاح الأساسي "lecturer_id" في جدول "Lecturer" وهو بالتالي مفتاح أجنبي". ومن الجيد أن تستخدم لكل CONSTRAINT اسمًا معقولًا. وفي هذه الحالة: "fk" (من "foreign key") متبوعًا باسمي الجدولين المعنيين. وتستخدم أداة DBSchema التي سنستخدمها لاحقًا لذلك نظام التسمية هذا تلقائيًا.

وفكّر منطقيًا في الترتيب الصحيح لملء البيانات في الجدولين. فجدول "Course" يعتمد على جدول "Lecturer". ولا يمكنك إضافة منسّق لمقرر إلا إذا كان هذا الشخص موجودًا بالفعل في جدول "Lecturer". لذا املأ أولًا جدول المحاضرين بعملية نسخ/لصق لجمل INSERT من ملف txt.

INSERT INTO rxxxxxxx.lecturer( lecturer_id, last_name, first_name, email, street, house_number,
  municipality, birthdate, start_date, end_date, sex ) VALUES ( 'u0042352', 'Adriaens',
  'Gerben', 'gerben.adriaens@ucll.be', 'Ankerstraat', '12', '1330 Rixensart',
  '1984-02-16', '2017-09-01', null, 'M');
INSERT INTO rxxxxxxx.lecturer( lecturer_id, last_name, first_name, email, street, house_number,
  municipality, birthdate, start_date, end_date, sex ) VALUES ( 'u0057764', 'Bogers',
  'Goedele', 'goedele.bogers@ucll.be', 'Elzasgang', null, '2830 Willebroek',
  '1979-09-09', '2006-11-20', null, 'F');
INSERT INTO ...

وأخيرًا، بعد أن امتلأ جدول "Lecturer"، يمكنك ملء جدول "Course" بالبيانات أيضًا. وسنفعل ذلك هذه المرة بقائمة CSV. انسخ هذه الأسطر من القائمة وضعها في ملف .csv جديد. واستورد ملف .csv هذا كما في (انظر قسم "ملف CSV"):

code,credits,name,start_date,end_date,language,semester,coordinator
MBI71A,3,Probleemoplossend denken,2016-09-15,2022-09-14,nl,1,u0012047
MBI65X,4,Webontwikkeling 1,2017-09-15,2022-09-14,nl,1,u0012047
MBI66X,4,Webontwikkeling 2,2017-09-15,2022-09-14,nl,2,u0015529
MBI20X,3,MobieleToepassingen,2018-09-15,2023-09-14,nl,5,u0057764
MBI26A,6,Computersystemen,2013-09-15,2022-09-14,nl,6,u0041234
...

تم! نفّذ SELECT * FROM ... على الجدولين لعرض البيانات.

استجابة نظام إدارة قواعد البيانات العلائقية للأخطاء

نفّذ الآن التمارين الصغيرة التالية. وأحيانًا يكون الغرض من تمرين أن نجعلك ترتكب خطأ حتى تتعلم تفسير رسائل الخطأ من نظام إدارة قواعد البيانات العلائقية تفسيرًا سليمًا.

أضف محاضرًا جديدًا: "Anouk De Ridder" برقم u "u0012047". وهي تسكن في "Kerkstraat 23, 3053 Haasrode"، ووُلدت في 5 مايو 1994 وتنضم إلى UCLL اليوم. وعناوين البريد في UCLL بسيطة: firstname.lastname@ucll.be. واشرح ما يحدث عند تنفيذ INSERT بالبيانات أعلاه.

الحل

تحصل على رسالة خطأ تفيد بوجود محاضر برمز المحاضر هذا بالفعل. وبما أن رمز المحاضر هو المفتاح الأساسي (وبالتالي فريد)، يعيد نظام إدارة قواعد البيانات رسالة خطأ ولا يُنفَّذ INSERT. ومرة أخرى ستلاحظ أن من المفيد استخدام اسم معبّر لقيدك CONSTRAINT: فهناك خطأ ما في المفتاح الأساسي "lecturer_id" في جدول "Lecturer":

ERROR: duplicate key value violates unique constraint "pk_lecturer_lecturer_id"
DETAIL: Key (lecturer_id)="(u0012047)" already exists.
SQL state: 23505

خطأ مطبعي سخيف بالطبع أعلاه. فرقم u الخاص بـ Anouk ليس "u0012047" بل "u0099999". أضف الآن هذا المحاضر إلى جدول "Lecturer".

الحل

لا مشكلة هذه المرة إذا كان استعلامك على غرار هذا:

INSERT INTO rxxxxxxx.lecturer( lecturer_id, last_name, first_name, email, street, house_number,
  municipality, birthdate, start_date, end_date, sex ) VALUES ( 'u0099999', 'De Ridder',
  'Anouk', 'anouk.deridder@ucll.be', 'Kerkstraat', '23', '3053 Haasrode',
  '1994-07-05', '2022-10-15', null, 'F');

ويردّ الخادم بأنه أُضيف صف واحد بنجاح:

INSERT 0 1

Query returned successfully in 85 msec.

وفي اليوم نفسه الذي أُضيفت فيه إلى قاعدة البيانات، تقرر Anouk De Ridder أنها لا تريد أن تصبح محاضرة بعد كل شيء. أزلها من جدول "Lecturer".

الحل

DELETE
FROM rxxxxxxx.lecturer
WHERE lecturer_id = 'u0099999';

ويمكنك استخدام الشيفرة التالية أيضًا، لكنك حينئذ تحذف كل من يحمل هذا الاسم. ومن يدري، ربما يوجد أكثر من شخص باسم "Anouk De Ridder" وحينئذ سيُزال هؤلاء الأشخاص أيضًا من الجدول:

DELETE
FROM rxxxxxxx.lecturer
WHERE last_name = 'De Ridder' AND first_name = 'Anouk'; -- dangerous!

ويردّ الخادم بأنه حُذف صف واحد بنجاح:

DELETE 1

Query returned successfully in 53 msec

أزل "Bram De Smet" من Leuven من جدول "Lecturer". واشرح ما يحدث.

الحل

بهذه الشيفرة تحذف الصف:

DELETE
FROM rxxxxxxx.lecturer
WHERE first_name = 'Bram' AND last_name = 'De Smet' AND municipality LIKE '%Leuven';

غير أن الخادم يعطي رسالة الخطأ هذه:

ERROR: update or delete on table "lecturer" violates foreign key constraint
  "fk_course_lecturer" on table "course"
DETAIL: Key (lecturer_id)=(u0012047) is still referenced from table "course".
SQL state: 23503

وتقرأ رسالة الخطأ هذه كما يلي: "يحتوي جدول "Course" على صف واحد على الأقل يشير إلى هذا المحاضر. فهذا المحاضر منسّق لمقرر. ولذلك لا ينبغي إزالته.". وفي حالة Anouk De Ridder لم تكن هناك مشكلة لأن رقم u الخاص بها لا يظهر في أي مكان في جدول المقررات، لذا كان يمكن إزالتها بأمان من ذلك الجدول.

وأخيرًا، أضف مقررًا جديدًا: "Trees and graphs" برمز "MBI39A". وقد دُرِّس هذا المقرر في الفصل الثاني، وتوقف هذا العام، واستمر من 15 سبتمبر 2017 إلى 14 سبتمبر 2022. ودُرِّس المقرر بالهولندية وكان له 3 نقاط. وكان منسّق المقرر المحاضر ذا الرقم u "u0099998". اكتب INSERT ونفّذه وفسّر النتيجة.

الحل

بهذه الشيفرة تنفّذ INSERT:

INSERT INTO rxxxxxxx.course( code, credits, name, start_date,
  end_date, language, semester, coordinator ) VALUES ( 'MBI39A', 3, 'Trees and graphs',
  '2017-09-15', '2022-09-14', 'nl', 2, 'u0099998');

ويرفض خادم قاعدة البيانات تنفيذ هذا INSERT ويعيد رسالة الخطأ التالية:

ERROR: insert or update on table "course" violates
  foreign key constraint "fk_course_lecturer"
DETAIL: Key (coordinator)=(u0099998) is not present in table "lecturer".
SQL state: 23503

وبما أنه لا يوجد محاضر برمز المحاضر هذا بالفعل في جدول "Lecturer"، لا يمكنك إضافة مقرر جديد بهذا المحاضر منسّقًا. ولا يمكنك إضافة المقرر إلا بعد إضافة هذا المحاضر في جدول "Lecturer".