Зв'язок багато-до-багатьом: приклад Access, SQL. Як зробити зв'язок багато-до-багатьом?

У всіх СУБД (системи управління базами даних) є кілька типів відносин між таблицями. Серед них зв'язок один-до-одного, один-до-багатьох, багато-до-одного (деякі схильні ототожнювати ці два типи в один) та зв'язок багато-до-багатьох. Приклад останньої, її пояснення і застосування в різних СУБД, таких як Access або SQL, буде розглянуто в цій статті.

Визначення

Зв'язок багато-до-багатьом визначається як відповідність кожному з примірників однієї з сутностей всіх примірників іншого. Іншими словами, кожне поле з першої (другої) таблиці пов'язано з усіма полями з другої (першої).


Представлена схема наочно відображає суть даного відношення.

Коли може бути використана зв'язок багато-до-багатьом?

Приклад зв'язку багато-до-багатьох - це студентська група і група викладачів. Кожен з студентів навчається відразу у кількох професорів, які, в свою чергу, читають лекції кільком учням. На малюнку показана різниця між зв'язок один-до-багатьох і багато-до-багатьох. Зв'язок багато-до-багатьом нерідко необхідна при складанні масштабних баз даних, дрібні показові приклади зазвичай використовуються тільки в навчальних цілях, у реальній же практиці виходить так, що, чим більше сутностей в БД і більше взаємозв'язків між ними - тим більше ймовірність частого звернення до відношення багато-до-багатьох.

Як зробити зв'язок багато-до-багатьом?

Приклади розглянутого відносини ще будуть додаватися по ходу статті, однак важливо не тільки зрозуміти, що воно собою являє, але і те, яким чином можна його реалізувати. Деталі даного процесу прямо залежать від обраної для роботи СУБД, в той час як принцип залишається одним для всіх.

Microsoft Access

Офісний програмний продукт від "Майкрософт" відомий на ринку вже досить тривалий час. Він поставляється разом з текстовим редактором Worfd, табличним процесором Excel і іншими, що входять в лінійку "офісу". Можна Access (читається як "аксес", дослівний переклад - "доступ") придбати і окремо від його "колег". Рекомендується купити, зрозуміло, ліцензійний софт, але ні для кого не секрет, скільки піратських репаков можна знайти на просторах Мережі, у вигляді звичайних файлів або роздачі торрентів. "Майкрософт Аксес" доступний навіть в портативної збірці. Вона, не вимагає інсталяції і особливих навичок роботи з ПК, краще всього підходить для вибору, якщо не буде використовуватися тривало і часто. З контексту ясно, що "Майкрософт Аксес" - це система управління базами даних. Причому одна з найпопулярніших. Вона є реляційною, що означає, що вона заснована на логічній моделі даних, яка в ході своєї роботи звертається до теорії множин і логіки першого порядку. Зв'язок багато-до-багатьом в Access (приклади будуть дані в ході пояснення) реалізується дуже і дуже просто. Розглянемо її.

Є дві таблиці

Щоб не вигадувати нічого нового, візьмемо вже вказаний для того, щоб роз'яснити зв'язок багато-до-багатьох, приклад про студентство. Необхідно створити таблиці "Студенти" і таблицю "Викладачі". Як у першій, так і в другій з них є первинні ключі. Для об'єднання примірників цих двох сутностей потрібно також ще одна таблиця, поля якої - ключі першої та другої таблиць.
Якщо розглянути інший приклад: припустимо, футболісти команди ( з урахуванням того, що хоча б один з футболістів грав за різні збірні, і кожна збірна має у своєму складі одинадцять гравців), суть побудови зв'язку не зміниться. Також будуть потрібні три таблиці. З них "Футболісти" і "Команди" в якості основних, і одна проміжна.

Схема даних

Зв'язки між таблицями в СУБД "Microsoft Аксес" реалізуються за допомогою вкладки "Схема даних". На що з'явилася панель додаються всі необхідні сутності (в нашому випадку всі три таблиці). Створення зв'язку багато-до-багатьом буде відбуватися за допомогою двох відносин один-до-багатьох між головними ("Студенти" і "Викладачі") і проміжної таблиці. Для цього необхідно з'єднати між собою відповідні первинні ключі. На малюнку вище показано, як виглядає вкладка "Схема даних" (Relathionships). Кількість доданих на панель таблиць необмежено. Розташування повністю регулюється користувачем.

SQL

Проектування баз даних на SQL - завдання складніше, ніж на "Аксес". Якщо майкрософтовський продукт повністю адаптований під офісне середовище, має величезний і, з кожним випуском і оновленням все розширюваний, функціонал, але в той же час і зручний для простого користувача інтерфейс, то SQL - це окремий непроцедурный мова програмування, за допомогою якого на різних платформах можна працювати з базами даних. Відоме для даної задачі: Oracle, MySQL та DB2 (популярне, але не єдине у своєму роді). Незважаючи на те, що у кожного з них є свої тонкощі і нюанси, мова SQL їх "об'єднує". Навчившись працювати хоча б з одним з них, розібратися з іншим буде набагато простіше. Створення, заповнення та безпосередньо дії над вже наявної БД в SQL потрібно через спеціальні коди або скрипти. Однак ті, хто вже добрався до розділу "Зв'язок багато-до-багатьом", приклад якої на даній мові програмування наданий нижче, повинні знати хоча б основні команди і принципи використання мови SQL.

Принцип створення зв'язку багато-до-багатьом

Тривале вступ могло дещо збентежити і "напустити туману", але насправді принцип реалізації зв'язку залишається колишнім. Для того щоб тип зв'язку багато-до-багатьом був втілений на практиці, не тільки в "Аксес", але і SQL, потрібно спочатку створити дві базові таблиці і одну - проміжну. Аналогічно справа йде і з ключами: основні сутності мають головні поля, кожен з яких записується в перехідну таблицю. Що означає, що SQL-зв'язок багато-до-багатьох принципово не відрізняється від "Аксеса".

Реалізація зв'язку

Для реалізації зв'язку багато-до-багатьом в скриптах SQL використовуються зовнішні ключі (FOREIGN KEY) аналогічні вихідним ключам в основних таблицях. Вони записуються разом з усіма полями при їх створенні та/або редагування.

Роль зв'язку багато-до-багатьом

Взагалі відносини між сутностями в базах даних використовуються для цілісності інформації, що в них зберігається. Тільки добре спроектована БД з усіма необхідними зв'язками гарантує безпеку зберігання, зручність роботи і являє собою структуру, стійку до зовнішніх впливів і змін. Зазвичай, якщо база містить дані про цілої організації, компанії або фірмі, у ній міститься безліч сутностей з різними екземплярами. А це означає, що при складанні схеми даних ("Аксесе") або написанні скриптів ("Оракл" або "ДиБиТу") буде присутній як мінімум одна зв'язок багато-до-багатьох. Приклад SQl, що часто використовується при навчанні курсу "Організація баз даних" - БД Кінга.

База даних Кінга

Ця навчальна база даних являє собою відомості про корпорації Кінга. Серед таблиць:

співробітники фірми - містить в собі код співробітника, його прізвище, ім'я та по-батькові (орієнтованість на закордонні імена), код начальника і займаної співробітником посади, дату його надходження в фірму, одержуваний їм оклад і передбачені комісійні, код відділу;

відділи корпорації - серед полів таблиці є код та назва відділу, а також код його розміщення;

місця розміщення відділів, яка передбачає внесення інформації за кодом місця розміщення та назви міста;

посади у фірмі - невелика таблиця з двома полями коду посади та її офіційної назви;

фірми-покупці - поля: код і назва покупця, адреса, місто і штат, поштовий код і код регіону, телефон, код обслуговуючого покупця менеджера, кредит для покупця і коментарі (примітки нотатки);

договори про продаж, містить в собі код і дату договору, код покупця, дату поставки та загальну суму договору;

акти продажу - код акта та код договору, в який входить акт, код продукту, його ціна, кількість придбаної та загальна вартість покупки;

товари - код і назва продукту;

ціни - код продукту, оголошена на нього ціна, мінімально можлива ціна,дата встановлення і дата скасування ціни.

Невеликі таблиці, в яких не більше ніж два-три поля, пов'язані максимум з однією таблицею відношенням один-до-одного або один-до-багатьох. Масштабні ж таблиці, такі як "працівники фірми", "фірми-покупці", "договори про продаж" і "акти продажу" пов'язані відразу з декількома сутностями, причому з деякими - за допомогою "посередників" ставленням багато-до-багатьох. Таблиця "фірми-покупці" сама є посередницькою, як така, адже в ній є багато поля, запозичені з інших таблиць і є зовнішніми ключами. Крім того, масштабність і взаємозв'язок бази даних "Корпорації Кінга" така, що всі відносини нерозривно корелюють між собою та впливають одне на інше. Руйнування хоч би одного з них спричинить за собою деструкцію цілісності всієї БД.

Важливі нюанси

При реалізації зв'язку багато-до-багатьох, незалежно від того, яка використовується СУБД, дуже важливо вірно визначити ключі, за допомогою яких буде складатися ставлення. Неправильно реалізована зв'язок не виконає свого основного призначення, а саме - забезпечення цілісності таблиці, і в результаті, замість очікуваного комфорту, користувач отримає, навпаки, незручності і додаткові проблеми, які особливо проявляються при заповненні таблиць і редактури у них даних.