Вернутися назад Друкувати

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

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

Визначення

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


Представлена схема наочно відображає суть даного відношення.
Зв'язок багато-до-багатьом: приклад Access, SQL. Як зробити зв'язок багато-до-багатьом?

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

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

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

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


Microsoft Access

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

Є дві таблиці

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

Схема даних

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

SQL

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

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

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

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

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

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

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

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

Ця навчальна база даних являє собою відомості про корпорації Кінга. Серед таблиць:
  • співробітники фірми - містить в собі код співробітника, його прізвище, ім'я та по-батькові (орієнтованість на закордонні імена), код начальника і займаної співробітником посади, дату його надходження в фірму, одержуваний їм оклад і передбачені комісійні, код відділу;
  • відділи корпорації - серед полів таблиці є код та назва відділу, а також код його розміщення;
  • місця розміщення відділів, яка передбачає внесення інформації за кодом місця розміщення та назви міста;
  • посади у фірмі - невелика таблиця з двома полями коду посади та її офіційної назви;
  • фірми-покупці - поля: код і назва покупця, адреса, місто і штат, поштовий код і код регіону, телефон, код обслуговуючого покупця менеджера, кредит для покупця і коментарі (примітки нотатки);
  • договори про продаж, містить в собі код і дату договору, код покупця, дату поставки та загальну суму договору;
  • акти продажу - код акта та код договору, в який входить акт, код продукту, його ціна, кількість придбаної та загальна вартість покупки;
  • товари - код і назва продукту;
  • ціни - код продукту, оголошена на нього ціна, мінімально можлива ціна,дата встановлення і дата скасування ціни.
  • Невеликі таблиці, в яких не більше ніж два-три поля, пов'язані максимум з однією таблицею відношенням один-до-одного або один-до-багатьох.
    Зв'язок багато-до-багатьом: приклад Access, SQL. Як зробити зв'язок багато-до-багатьом?
    Масштабні ж таблиці, такі як "працівники фірми", "фірми-покупці", "договори про продаж" і "акти продажу" пов'язані відразу з декількома сутностями, причому з деякими - за допомогою "посередників" ставленням багато-до-багатьох. Таблиця "фірми-покупці" сама є посередницькою, як така, адже в ній є багато поля, запозичені з інших таблиць і є зовнішніми ключами. Крім того, масштабність і взаємозв'язок бази даних "Корпорації Кінга" така, що всі відносини нерозривно корелюють між собою та впливають одне на інше. Руйнування хоч би одного з них спричинить за собою деструкцію цілісності всієї БД.

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

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