5. العلاقات بين الجداول

5.1. المفاتيح الخارجية

قاعدة البيانات العلائقية هي مجموعة من الجداول المرتبطة ببعضها البعض من خلال العلاقات. لنأخذ مثالاً مستوحى من الجدول [BIBLIO] السابق، الذي كان له الهيكل التالي:

وكان أحد أمثلة المحتوى كما يلي:

قد ترغب في الحصول على معلومات حول المؤلفين المختلفين لهذه الأعمال، مثل أسمائهم الأولى والأخيرة وتاريخ ميلادهم وجنسياتهم. لنقم بإنشاء مثل هذا الجدول. انقر بزر الماوس الأيمن على [DBBIBLIO / Tables] واختر خيار [New Table]:

الآن دعونا ننشئ الجدول [AUTHORS] التالي:

id	المفتاح الأساسي للجدول — يُستخدم لتعريف الصف بشكل فريد
اللقب	الاسم الأخير للمؤلف
الاسم الأول	الاسم الأول للمؤلف، إن أمكن
تاريخ_الميلاد	تاريخ الميلاد
الجنسية	بلد المنشأ

يمكن أن تكون محتويات جدول [AUTHORS] كما يلي:

لنعد إلى جدول [BIBLIO] ومحتوياته:

في عمود [AUTHOR] بالجدول، لم يعد من الضروري إدخال اسم المؤلف. بدلاً من ذلك، يُفضل إدخال رقم التعريف المخصص له في جدول [AUTHORS]. لنقم بإنشاء جدول جديد باسم [BOOKS]. لإنشائه، سنستخدم البرنامج النصي [biblio.sql] الذي تم إنشاؤه في القسم 3.14. نقوم بتشغيل هذا البرنامج النصي باستخدام أداة [Script Executive, Ctrl-F12]:

نقوم بتعديل البرنامج النصي لإنشاء جدول BIBLIO لتكييفه مع جدول BOOKS:

CREATE TABLE LIVRES (
ID          INTEGER NOT NULL,
TITRE       VARCHAR(30) NOT NULL,
AUTEUR      INTEGER,
GENRE       VARCHAR(30) NOT NULL,
ACHAT       DATE NOT NULL,
PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 8, 'POèME', '1978-01-01', 120, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (3, 'Tintin au Tibet', 6, 'BD', '1990-11-10', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (4, 'Du côté de chez Swann', 3, 'ROMAN', '1978-12-08', 220.5, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (5, 'La terre', 4, 'ROMAN', '1990-06-12', 55.13, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (6, 'Madame Bovary', 1, 'ROMAN', '1988-03-12', 143.33, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (7, 'Manhattan transfer', 7, 'ROMAN', '1987-08-30', 352.8, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (8, 'Tintin en Amérique', 6, 'BD', '1991-05-15', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (10, 'Le père Goriot', 5, 'Roman', '1991-09-01', 210, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (11, 'Poèmes saturniens', 2, 'Poème', '1992-09-02', 200, 'o');

COMMIT WORK;



/******************************************************************************/
/****                          Unique Constraints                          ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT UNQ1_LIVRES UNIQUE (TITRE);


/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT PK_LIVRES PRIMARY KEY (ID);

سنعلق فقط على التغييرات:

• السطر 4: يصبح العمود [AUTHOR] في الجدول عددًا صحيحًا. يشير هذا الرقم إلى أحد المؤلفين في الجدول [AUTHORS] الذي تم إنشاؤه مسبقًا.
• الأسطر 11–19: تم استبدال أسماء المؤلفين بمعرفات المؤلفين الخاصة بهم.
• السطر 29: تم تغيير اسم القيد. كان يُسمى سابقًا [UNQ1_BIBLIO]. وهو يُسمى الآن [UNQ1_LIVRES]. يمكن أن يكون هذا الاسم أي شيء. ومع ذلك، يفضل أن يكون له معنى. هنا، لم يتم بذل هذا الجهد. يجب تمييز القيود المفروضة على الحقول المختلفة والجداول المختلفة داخل قاعدة البيانات بأسماء مختلفة. تذكر أن القيد في السطر 29 يتطلب أن يكون العنوان فريدًا داخل الجدول.
• السطر 36: قم بتغيير اسم القيد على المفتاح الأساسي ID.

دعونا نقوم بتشغيل هذا البرنامج النصي. إذا نجح، فسنحصل على جدول [BOOKS] الجديد التالي:

قد يتساءل المرء عما إذا كنا قد حققنا أي مكسب في النهاية. في الواقع، يسرد جدول [BOOKS] أرقام المؤلفين بدلاً من أسمائهم. ونظرًا لوجود آلاف المؤلفين، يبدو ربط كتاب بمؤلفه أمرًا صعبًا. لحسن الحظ، فإن لغة SQL موجودة لمساعدتنا. فهي تتيح لنا الاستعلام عن جداول متعددة في نفس الوقت. في هذا المثال، نقدم استعلام SQL الذي يتيح لنا استرداد عناوين الكتب الموجودة في المكتبة، إلى جانب معلومات مؤلفيها. دعونا نستخدم محرر SQL (F12) لتنفيذ عبارة SQL التالية:

SQL> select LIVRES.titre, AUTEURS.nom, AUTEURS.prenom,AUTEURS.date_naissance
FROM LIVRES inner join AUTEURS on LIVRES.AUTEUR=AUTEURS.ID
ORDER BY AUTEURS.nom asc

من السابق لأوانه شرح استعلام SQL هذا. سنعود إليه بعد قليل. نتيجة هذا الاستعلام هي كما يلي:

تمت مطابقة كل كتاب بشكل صحيح مع مؤلفه والمعلومات المرتبطة به.

دعونا نلخص ما قمنا به للتو:

• لدينا جدولان يحتويان على أنواع مختلفة من المعلومات:
  • يحتوي جدول AUTHORS على معلومات عن المؤلفين
  • يحتوي الجدول BOOKS على معلومات حول الكتب التي اشترتها المكتبة
• هذه الجداول مرتبطة ببعضها البعض. يجب أن يكون لكل كتاب مؤلف. وقد يكون له عدة مؤلفين. لم يتم تناول هذا السيناريو هنا. يشير الحقل [AUTHOR] في جدول [BOOKS] إلى صف في جدول [AUTHORS]. وهذا ما يُسمى بالعلاقة.

العلاقة بين جدول [BOOKS] وجدول [AUTHORS] هي في الواقع نوع من القيود: يجب أن يحتوي كل صف في جدول [BOOKS] دائمًا على معرف مؤلف موجود في جدول [AUTHORS]. إذا كان أحد الصفوف في [BOOKS] يحتوي على معرف مؤلف غير موجود في جدول [AUTHORS]، فسنكون في حالة غير طبيعية حيث لن نتمكن من العثور على مؤلف الكتاب.

نظام إدارة قواعد البيانات (DBMS) قادر على ضمان استيفاء هذا القيد دائمًا. للقيام بذلك، سنضيف قيدًا إلى جدول [BOOKS]:

يُطلق على الرابط الذي يربط العمود [AUTHOR] في الجدول [BOOKS] بحقل [ID] في الجدول [AUTHORS] اسم علاقة المفتاح الخارجي. يُطلق على حقل [AUTHOR] في الجدول [BOOKS] اسم "مفتاح خارجي" في المعالج أعلاه. يعني تعريف مفتاح خارجي أن قيمة العمود [c1] في الجدول [T1] يجب أن توجد في العمود [c2] من الجدول [T2]. يُشار إلى العمود [c1] باسم "المفتاح الخارجي" للجدول T1 في العمود [c2] من الجدول [T2]. غالبًا ما يكون العمود [c2] هو المفتاح الأساسي للجدول [T2]، ولكن هذا ليس إلزاميًا.

نحدد المفتاح الخارجي [AUTHOR] للجدول [BOOKS] في الحقل [ID] للجدول [AUTHORS] على النحو التالي:

1. اسم القيد: free
2. عمود "المفتاح الأجنبي"، وهو هنا عمود [AUTHOR] في الجدول [BOOKS]
3. الجدول المشار إليه بواسطة المفتاح الخارجي. هنا، يجب أن يكون لعمود [AUTHOR] في جدول [BOOKS] قيمة في عمود [ID] في جدول [AUTHORS]. لذلك، فإن جدول [AUTHORS] هو الجدول المشار إليه.
4. العمود المشار إليه بواسطة المفتاح الخارجي. هنا، العمود [ID] في جدول [AUTHORS].

نقوم بالتحقق من صحة هذا القيد:

إذا سارت الأمور على ما يرام، فسيتم قبولها:

ما هي نتيجة هذا القيد الجديد للمفتاح الخارجي؟ باستخدام محرر SQL (F12)، دعونا نحاول إدراج صف في جدول BOOKS بمعرف مؤلف غير موجود:

حاولت عملية [INSERT] أعلاه إدراج كتاب بمعرف مؤلف غير موجود (100). فشل تنفيذ الاستعلام. تشير رسالة الخطأ المرتبطة إلى وجود انتهاك لقيود المفتاح الخارجي "FK_BOOKS_AUTHORS". هذا هو ما حددناه للتو.

5.2. عمليات الانضمام بين جدولين

لا نزال في قاعدة البيانات [DBBIBLIO] (أو أي قاعدة بيانات أخرى)، فلنقم بإنشاء جدولين اختباريين باسم TA و TB ونحددهما على النحو التالي:

الجدول TA

- ID: المفتاح الأساسي للجدول TA - DATA: بيانات عشوائية

الجدول TB

- ID: المفتاح الأساسي لجدول TB - IDTA: المفتاح الخارجي لجدول TB الذي يشير إلى عمود ID في جدول TA. وبالتالي، يجب أن توجد قيمة من عمود IDTA في جدول TA في عمود ID في جدول TA - VALUE: أي بيانات

في محرر SQL (F12)، سنقوم بتنفيذ عبارات SQL التي تستخدم في آن واحد كل من الجدولين TA و TB.

SQL>select * from TA,  TB

يستخدم جملة SQL الكلمة الرئيسية FROM للإشارة إلى كل من الجدولين TA و TB. ستؤدي عملية FROM TA, TB إلى إنشاء جدول مؤقت جديد، حيث سيتم ربط كل صف من جدول TA بكل صف من جدول TB. وبالتالي، إذا كان جدول TA يحتوي على NA صفوفًا وجدول TB يحتوي على NB صفوفًا، فسيحتوي الجدول الناتج على NA × NB صفوفًا. يظهر هذا في لقطة الشاشة أعلاه. علاوة على ذلك، يحتوي كل صف على الأعمدة من كلا الجدولين. تشير الأعمدة المحددة بالترتيب [SELECT col1, col2, ... FROM ...] إلى الأعمدة المطلوب تضمينها. هنا، تشير الكلمة الرئيسية * إلى طلب جميع أعمدة الجدول الناتج. يُشار أحيانًا إلى الجدول الناتج عن عبارة SQL السابقة باسم المنتج الديكارتي للجدولين TA و TB.

أعلاه، تم ربط كل صف من الجدول TA بكل صف من الجدول TB. بشكل عام، نريد ربط صف من TB بصف من TA له علاقة به. غالبًا ما تأخذ هذه العلاقة شكل قيد مفتاح خارجي. وهذا هو الحال هنا. بالنسبة لصف في الجدول TA، يمكننا ربط الصفوف في الجدول TB التي تستوفي العلاقة TB.IDTA=TA.ID. هناك عدة طرق للاستعلام عن هذا:

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA, TB where TA.ID=TB.IDTA

تشبه عبارة SQL السابقة العبارة السابقة، مع اختلافين:

• يتم تصفية صفوف نتائج المنتج الديكارتي TA x TB بواسطة جملة WHERE التي تربط بصف في الجدول TA فقط تلك الصفوف في الجدول TB التي تستوفي العلاقة TB.IDTA=TA.ID
• يتم طلب أعمدة معينة فقط باستخدام صيغة [T.col]، حيث T هو اسم الجدول و col هو اسم العمود في ذلك الجدول. تعمل هذه الصيغة على حل أي غموض قد ينشأ في حالة وجود أعمدة تحمل نفس الاسم في جدولين. وعندما لا يوجد هذا الغموض، يمكن استخدام صيغة [col] دون تحديد الجدول الخاص بهذا العمود.

والنتيجة هي كما يلي:

يمكن الحصول على نفس النتيجة باستخدام عبارة SQL التالية:

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA inner join TB on TA.ID=TB.idta

يُشير مصطلح [inner join] إلى اسم "inner join" الذي يُطلق على هذا النوع من العمليات بين جدولين. وسنرى أن هناك أيضًا ما يُسمى "outer join". في عملية inner join، لا يؤثر ترتيب الجداول في الاستعلام على النتيجة: فـ FROM TA inner join TB تعادل FROM TB inner join TA.

يتضمن ترتيب SQL السابق في مجموعة النتائج فقط تلك الصفوف من الجدول TA التي تمت الإشارة إليها بواسطة صف واحد على الأقل من الجدول TB. وبالتالي، لا يظهر الصف [3, data3] في TA في النتيجة لأنه لم تتم الإشارة إليه بواسطة صف في TB. قد ترغب في الحصول على جميع الصفوف من TA، سواء تمت الإشارة إليها بواسطة صف في TB أم لا. في هذه الحالة، تستخدم انضمام خارجي أيسر بين الجدولين:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA left outer join TB on TA.ID= TB.IDTA 

لدينا هنا ربط خارجي أيسر. لفهم مصطلح "FROM TA left outer join TB"، تخيل ربطًا مع الجدول TA على اليسار والجدول TB على اليمين. تظهر جميع الصفوف من الجدول الأيسر في نتيجة الربط الخارجي الأيسر، حتى تلك التي لا تستوفي شرط الربط. لا يمثل شرط الربط هذا بالضرورة قيدًا للمفتاح الخارجي، على الرغم من أن هذه هي الحالة الأكثر شيوعًا.

بالترتيب التالي:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TB left outer join TA on TA.ID= TB.IDTA

يوجد جدول TB على الجانب "الأيسر" من الارتباط الخارجي. لذلك، ستظهر جميع الصفوف من TB في النتيجة:

على عكس عملية «الربط الداخلي»، فإن ترتيب الجداول مهم هنا. وهناك أيضًا عمليات «الربط الخارجي الأيمن»:

• FROM TA LEFT OUTER JOIN TB يعادل FROM TB RIGHT OUTER JOIN TA: جدول TA موجود على اليسار
• FROM TB LEFT OUTER JOIN TA يعادل FROM TA RIGHT OUTER JOIN TB: الجدول TB موجود على اليسار

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