 |
|
Загальна операція з’єднання
Визначення. З’єднанням відношень А та В за умовою с називається відношення (A ТІМЕS B) WHERE c. Умова с являє собою логічний вираз, у який можуть входити атрибути відношень А та В і (чи) скалярні вирази.
Таким чином, операція з’єднання є результатом послідовного застосування операцій декартового добутку і вибірки. Якщо у відношеннях А та В є атрибути з однаковими найменуваннями, то перед виконанням з’єднання такі атрибути необхідно перейменувати.
Тета-з’єднання
Визначення. Нехай відношення А містить атрибут Х, відношення В містить атрибут Y, а Q - один з операторів порівняння (<, >, = тощо). Тоді Q-з’єднанням відношення А заатрибутом Х з відношенням В за атрибутом Y називають відношення (A ТІМЕS B) WHERE ХQY.
Це окремий випадок операції загального з’єднання.
Іноді, для операції Q-з’єднання застосовують наступний, більш короткий синтаксис: A [ХQY]B .
Екві-з’єднання
Найбільш важливим частковим випадком тета-з’єднання є випадок, коли тета - проста рівність.
Синтаксис екві-з’єднання: A [Х=Y]B .
Недоліком екві-з’єднання є те, що якщо з’єднання відбувається по атрибутах з однаковими найменуваннями (а так найчастіше і відбувається!), те в результуючому відношенні з’являється два атрибути з однаковими значеннями. Позбутися від цього недоліку можна, узявши проекцію по всіх атрибутах, крім одного з дублюючих. Саме так діє природне з’єднання.
Приклад. Нехай є відношення P, D i PD, що зберігають інформацію про постачальників, деталі і поставки відповідно (для зручності введемо короткі найменування атрибутів):
| Номер постачальника PNUM | Найменування постачальника PNAME |
| 1 | Іванов |
| 2 | Петров |
| 3 | Сидорук |
Таблиця 13 Відношення P (Постачальники)
| Номер деталі DNUM | Найменування деталі DNAME |
| 1 | Болт |
| 2 | Гайка |
| 3 | Гвинт |
Таблиця 14 Відношення D (Деталі)
| Номер постачальника PNUM | Номер деталі DNUM | Кількість, що поставляється VOLUME |
| 1 | 1 | |
| 1 | 2 | |
| 1 | 3 | |
| 2 | 1 | |
| 2 | 2 | |
| 3 | 1 |
Таблиця 15 Відношення PD (Постачання)
Відповідь на питання, які деталі поставляються цими постачальниками, дає екві-з’єднання P[PNUM=PNUM]PD. Насправді, внаслідок того, що у відношеннях є однакові атрибути, потрібно спочатку перейменувати атрибути, а потім виконати екві-з’єднання . Запис стає більш громіздким:
Звичайно, такою складною формою запису не користаються. Але як би то не було, у результаті маємо відношення:
| Номер постачальника PNUM1 | Найменування постачальника PNAME | Номер постачальника PNUM2 | Номер деталі DNUM | Кількість, що поставляється VOLUME |
| Іванов | ||||
| Іванов | ||||
| Іванов | ||||
| Петров | ||||
| Петров | ||||
| Сидорук |
Таблиця 16 Відношення «Які деталі поставляються якими постачальниками»
Недоліком екві-з’єднання є те, що якщо з’єднання відбувається за атрибутами з однаковими іменами, те в результуючому відношенні з’являється два атрибути з однаковими значеннями. У нашому прикладі атрибути PNUM1 і PNUM2 містять дублюючі дані. Позбутися від цього недоліку можна, узявши проекцію по всіх атрибутах, крім одного з дублюючих. Саме так діє природне з’єднання.
Природне з’єднання
Визначення. Нехай дані відношення A(A1, A2,…,An,Х1, Х2,…,Хр ) та B(Х1, Х2,…,Хр, B1, B2,…,Bm), що маютьоднакові атрибути, Х1, Х2…,Хр (тобто атрибути з однаковими іменами і визначені на однакових доменах).
Тоді природним з’єднанням відношень А та В називається відношення з заголовком (A1, A2,…,An,Х1, Х2,…,Хр , B1, B2,…,Bm)і тілом, що містить множину кортежів (a1, a2,…,an,х1,х2,…,хр, b1,b2,…,bm) таких, що (a1, a2,…,an, х1, х2,…,хр)ÎА, (х1, х2,…,хр, b1, b2,…,bm)ÎВ.
Природне з’єднання настільки важливе, що для нього використовують спеціальний синтаксис:
A JOIN B.
У синтаксисі природного з’єднання не вказуються, по яких атрибутах провадиться з’єднання. Природне з’єднання провадиться по всіх однакових атрибутах.
Природне з’єднання еквівалентне наступної послідовності реляційних операцій:
1. Перейменувати однакові атрибути у відносинах
2. Виконати декартів добуток відносин
3. Виконати вибірку за співпадаючим значенням атрибутів, що мали однакові імена
4. Виконати проекцію, видаливши повторювані атрибути
5. Перейменувати атрибути, повернувши їм первісні імена
Можна виконувати послідовне природне з’єднання декількох відношень. Неважко перевірити, що природне з’єднання (як, утім, і з’єднання загального вигляду) має властивістьасоціативності, тобто
(A JOIN B) JOIN З = A JOIN (B JOIN З), тому такі з’єднання можна записувати, опускаючи дужки
= A JOIN B JOIN С.
Таким чином, застосування оператору JOIN дає змогу компонувати інформацію з двох чи більше таблиць. Оператор JOIN посідає великі можливості у середовищі реляційних БД, дозволяючи використовувати незалежні таблиці, пов’язані спільними атрибутами. Для дослідження ефекту застосування цього оператору скористаємося таблицями ПОКУПЕЦЬ та ПРОДАВЕЦЬ.
| Пок_Код | Пок_Прізвище | Пок_КодМіста | Прод_Код |
| Вовк | |||
| Адамчик | |||
| Раковський | |||
| Робак | |||
| Сищишин | |||
| Яремчук |
Таблиця 17. Відношення ПОКУПЕЦЬ
| Прод_Код | Прод_Телефон |
Таблиця 18. Відношення ПРОДАВЕЦЬ
Природне з’єднання (natural Join) зв’язує таблиці, вибираючи лише рядки зі спільними значеннями їх спільних атрибутів. Природне з’єднання – це результат тристадійної процедури:
1) спочатку застосовуємо оператор PRODUCT, при цьому отримуємо результат, представлений у таблиці 19;
| Пок_Код | Пок_Прізвище | Пок_КодМіста | Прод_Код | Прод_Код | Прод_Телефон |
| Вовк | |||||
| Вовк | |||||
| Вовк | |||||
| Вовк | |||||
| Адамчик | |||||
| Адамчик | |||||
| Адамчик | |||||
| Адамчик | |||||
| Раковський | |||||
| Раковський | |||||
| Раковський | |||||
| Раковський | |||||
| Робак | |||||
| Робак | |||||
| Робак | |||||
| Робак | |||||
| Сищишин | |||||
| Сищишин | |||||
| Сищишин | |||||
| Сищишин | |||||
| Яремчук | |||||
| Яремчук | |||||
| Яремчук | |||||
| Яремчук |
Таблиця 19. Природне з'єднання, крок 1: PRODUCT
2) до отриманого результату застосовується оператор SELECT, який виводить лише рядки, у яких збігаються значення Прод_Код. Спільні стовпці називаються стовпцями з’єднання (join column). Результат цього кроку наведено у таблиці 20.
| Пок_Код | Пок_Прізвище | Пок_КодМіста | Прод_Код | Прод_Код | Прод_Телефон |
| Вовк | |||||
| Адамчик | |||||
| Раковський | |||||
| Робак | |||||
| Яремчук |
Таблиця 20. Природне з'єднання, крок 2: SELECT
3) до результату другого кроку застосовується оператор PROJECT для виведення однієї-єдиної копії кожного атрибуту. Таким чином, стовпці, які дублюються, усуваються з таблиці. Результат цього кроку наведено у таблиці 21.
| Пок_Код | Пок_Прізвище | Пок_КодМіста | Прод_Код | Прод_Телефон |
| Вовк | ||||
| Адамчик | ||||
| Раковський | ||||
| Робак | ||||
| Яремчук |
Таблиця 21. Природне з’єднання, крок 3: PROJECT
Кінцевим результатом дії оператору природного з’єднання є таблиця, у яку не включені пари, що не збігаються за значеннями спільних атрибутів, і в якій представлені лише екземпляри, що збігаються.
При роботі з оператором природного з’єднання слід звернути увагу на наступні його важливі властивості:
1) якщо є рядки однієї таблиці, значення спільного атрибуту у яких не збігаються з жодним значенням цього атрибуту у другій таблиці, то такі рядки у результуючу таблицю не включаються взагалі (покупець Сищишин, продавець 333);
2) стовпець, за яким проводилося об’єднання (Прод_Код), у новій таблиці присутній один раз;
3) якщо одне й те саме значення спільного атрибуту зустрічається у одній з таблиць (чи обидвох) кілька разів, то і результат збіжності буде введено кілька разів. Наприклад, якщо було б два рядки з Прод_Код 167, то двічі б появився у результуючій таблиці і покупець Раковський. Очевидно, що це було б результатом помилкової організації таблиці ПРОДАВЕЦЬ, бо значення Прод_Код мають бути нікальними.
Інша форма оператору JOIN, яку називають еквіз’єднанням (equiJOIN) або з’єднанням за еквівалентністю, зв’язує таблиці за умовою рівності, за якою порівнюються вказані стовпці кожної таблиці. У результуючій таблиці стовпці, що дублюються, не усуваються, а умова (критерій) з’єднання таблиць має бути чітко визначена.
Назва оператору equiJOIN походить від операції еквівалентного з’єднання (==), яка використовується в умовних операторах. Якщо використовуються інші операції порівняння, з’єднання називають тета-з’єднанням (tetaJOIN).
Окремим випадком з’єднання є так зване зовнішнє з’єднання (outer JOIN), яке являє собою розширення природного з’єднання. У зовнішньому з’єднанні залишаються пари, що збігаються, а всі значення з іншої таблиці, що не збігаються з жодним значенням першої таблиці, у результуючій таблиці приймають порожні значення (null). Наприклад, якщо ми виконуємо операцію зовнішнього з’єднання для таблиць ПОКУПЕЦЬ та ПРОДАВЕЦЬ, то можливі два сценарії:
3)лівобічне зовнішнє з’єднання (left outer JOIN) виводить всі рядки першої таблиці ПОКУПЕЦЬ, з тими рядками, що не мають спільних значень атрибутом Прод_Код у таблиці ПРОДАВЕЦЬ, включно. Результат застосування такого оператору з’єднання наведений у таблиці 22.
| Пок_Код | Пок_Прізвище | Пок_КодМіста | Прод_Код | Прод_Телефон |
| Вовк | ||||
| Адамчик | ||||
| Раковський | ||||
| Робак | ||||
| Яремчук | ||||
| Сищишин |
Таблиця 22. Лівобічне зовнішнє з’єднання
2) правобічне зовнішнє з’єднання (right outer JOIN) виводить всі рядки другої таблиці ПРОДАВЕЦЬ, з тими рядками, що не мають спільних значень атрибутом Прод_Код у таблиці ПОКУПЕЦЬ, включно. Результат застосування такого оператору з’єднання наведений у таблиці 23.
| Пок_Код | Пок_Прізвище | Пок_КодМіста | Прод_Код | Прод_Телефон |
| Вовк | ||||
| Адамчик | ||||
| Раковський | ||||
| Робак | ||||
| Яремчук | ||||
Таблиця 23. Правобічне зовнішнє з’єднання
Назви (лівобічне та правобічне) пов’язані з тим, у якому порядку таблиці перераховуються у SQL-командах.
Зовнішні з’єднання особливо корисні при визначенні того, які зі значень у зв’язаних таблицях викликають проблеми цілісності на рівні посилань – які з них були створені, коли значення зовнішнього ключа не відповідали значенням первинного ключа у зовнішніх таблицях.
Ділення (DIVIDE)
Визначення. Нехай дані відношення A(Х1, Х2,…,Хn, Y1, Y2,…,Ym) та B(Y1, Y2,…,Ym),причому атрибути (Y1, Y2,…,Ym) –спільні для двох відношень. Діленням відношення А на Вназивається відношення з заголовком (Х1, Х2,…,Хn) і тілом, що містить множину кортежів (х1, х2,…,хп) таких, що для всіх кортежів (y1, y2,…,ym)ÎВ у відношенні А знайдеться кортеж (х1, х2,…,хп, y1, y2,…,ym)…
Відношення А виступає в ролі діленого, відношення В виступає у ролі дільника. Ділення відносин аналогічно діленню чисел із залишком.
Синтаксис операції ділення:
A DIVIDE BYB.
Оператор ділення вимагає застосування двох таблиць – одної з двох стовпців, другої – з одного.
Наприклад:
| Код | Діяльність | DIVIDE BY | Код | дорівнює | Діяльність |
| А | А | ||||
| А | В | ||||
| А | |||||
| В | |||||
| В | |||||
| С | |||||
| Е |
Як видно, обидві початкові таблиці містять стовпець Код, але не використовують сумісно атрибут Діяльність. При включенні у результуючу таблицю значення у цьому, не використовуваному сумісне, стовпці Діяльність має бути асоційовано в таблиці 2 (дільник) з кожним значенням у таблиці 1. Єдине значення, асоційоване як з А, так і з В – це 5.
Типові запити, які реалізують за допомогою операції ділення, звичайно у своєму формулюванні мають слово «усі» – «які постачальники поставляють усі деталі?».
У прикладі з постачальниками, деталями і поставками відповімо на запитання, «які постачальники поставляють усі деталі?».
Як ділене візьмемо проекцію X=PD[PNUM,DNUM], що містить номера постачальників і номера деталей, що поставляються ними,:
| Номер постачальника PNUM | Номер деталі DNUM |
Таблиця 24 Проекція X=PD[PNUM,DNUM]
Як дільник візьмемо проекцію Y =D[DNUM], що містить список номерів усіх деталей (не обов’язково тих, що поставляються хоч ким-небудь):
| Номер деталі DNUM |
Таблиця 25 Проекція Y=D[DNUM]
Ділення X DEVIDE BY Y дає список номерів постачальників, що поставляють усі деталі:
| Номер постачальника PNUM |
Таблиця 26 Відношення X DEVIDEBY Y
Виявилося, що тільки постачальник з номером 1 поставляє всі деталі.
Залежні реляційні оператори
Не всі оператори реляційної алгебри є незалежними – деякі з них виражаються через інші реляційні оператори.
Оператор з’єднання. Оператор з’єднання визначається через оператори декартового добутку і вибірки. Для оператора природного з’єднання додається оператор проекції.
Оператор перетину. Оператор перетину виражається через вирахування в такий спосіб:
Оператор ділення. Оператор ділення виражається через оператори різниці, декартового добутку і проекції в такий спосіб:
Решта реляційних операторів (об’єднання, різниця, декартів добуток, вибірка, проекція) є примітивними операторами – їх не можна виразити один через одного.