Материал: 6БД

6. Лекція

ТЕМА: З'єднання і теоретико-множинні операції над відношеннями:

МЕТА: Розглянути спеціальні операції над відношеннями: вибірка, проекція, декартовий добуток, з'єднання. Навести різні типи з'єднань: з'єднання по еквівалентності, природне, зовнішнє з'єднання, напівз'єднання. Операції над відношеннями проілюструвати на прикладах оператора SELECT. Розглянути приклади вибору даних з декількох таблиць. Привести об'єднання результатів декількох запитів.

ПЛАН

1 Операція вибірки

2 Операція проекції

3 Декартовий добуток

4 Операція з'єднання по двох відношеннях (таблицям)

4.1 Операція тета-з'єднання

4.2 Природне з'єднання

4.3 Ліве і праве зовнішнє з'єднання

4.4 Напівз'єднання

5 Операція об'єднання

6 Операція перетину

7 Операція різниці

8 Операція ділення відношень

Розглянемо основні операції над відношеннями, які можуть представляти інтерес з погляду вибірки даних з реляційних таблиць. Це об'єднання, перетин, різниця, розширений декартовий добуток відношень, а також спеціальні операції над відношеннями: вибірка, проекція і з'єднання.

Для ілюстрації теоретико-множинних операцій над відношеннями введемо абстрактні відношення (таблиці) з деякими атрибутами (полями).

Відношення R

R.a1 R.a2

А 1

А 2

B 1

B 3

B 4

CREATE TABLE R

(a1 CHAR(1), a2 INT, PRIMARY KEY(a1,a2))

Відношення S

S.b1 S.b2

1 h

2 g

3 h

CREATE TABLE S

(b1 INT PRIMARY KEY, b2 CHAR(1))

Операції вибірки і проекції є унарними, оскільки вони працюють з одним відношенням.

1 Операція вибірки

Операція вибірки - побудова горизонтальної підмножини, тобто підмножини кортежів, що володіють заданими властивостями.

Операція вибірки працює з одним відношенням R і визначає результуюче відношення, яке містить тільки ті кортежі (рядки) відношення R, які задовольняють заданій умові F (предикату).

або

Приклад 6.1. Операція вибірки в SQL.

Вибірка записується таким чином:

SELECT a1, a2

FROM R

WHERE a2=1

Приклад 6.1. Операція вибірки в SQL.

2 Операція проекції

Операція проекції - побудова вертикальної підмножини відношення, тобто підмножини кортежів, одержуваної вибором одних і виключенням інших атрибутів.

Операція проекції працює з одним відношенням R і визначає нове відношення, яке містить вертикальну підмножину відношення R, створювану за допомогою витягання значень вказаних атрибутів і виключення з результату рядків-дублікатів.

Приклад 6.2. Операція проекції в SQL.

Проекціяя записується таким чином:

SELECT DISTINCT b2

FROM S

Приклад 6.2. Операція проекції в SQL.

До основних операцій над відношеннями відноситься декартовий добуток.

3 Декартовий добуток

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

RxS={(а, 1, 1, h), (а, 2, 1, h) (b, 1, 1, h) ... }

SELECT R.a1, R.a2, S.b1, S.b2

FROM R, S

Приклад 6.3. Декартовий добуток відношень в SQL.

Результат декартова добутка двох відношень показаний в таблиці.

Таблиця 6.1. R x S

R.a1 R.a2 S.b1 S.b2

а 1 1 h

а 1 2 g

а 1 3 h

а 2 1 h

а 2 2 g

а 2 3 h

b 1 1 h

b 1 2 g

b 1 3 h

b 3 1 h

b 3 2 g

b 3 3 h

b 4 1 h

b 4 2 g

b 4 3 h

Якщо одне відношення має N записів і К полів, а інше M записів і L полів, то відношення з їх декартовим добутком міститиме NxM записів і K+L полів. Початкові відношення можуть містити поля з однаковими іменами, тоді імена полів міститимуть назви таблиць у вигляді префіксів для забезпечення унікальності імен полів у відношенні, одержаному як результат виконання декартова добутка.

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

4 Операція з'єднання по двох відношеннях (таблицям)

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

В мові SQL для завдання типу з'єднання таблиць в логічний набір записів, з якого вибиратиметься необхідна інформація, використовується операція JOIN в пропозиції FROM.

Формат операції:

FROM ім’я_таблиці_1 {INNER | LEFT | RIGHT} JOIN ім’я_таблиці_2

ON умова_з’єднання

Існують різні типи операцій з'єднання:

тета-з'єднання ;

з'єднання по еквівалентності ;

природне з'єднання ;

зовнішнє з'єднання , ;

напівз'єднання .

4.1 Операція тета-з'єднання

Операція тета-з'єднання визначає відношення, яке містить кортежі з декартова добутку відношень R і S, що задовольняють предикату F. Предикат F має вигляд, де може бути вказаний один з операторів порівняння ( >, >= <, <= =, < > ).

Якщо предикат F містить тільки оператор рівності ( = ), то з'єднання називається з'єднанням по еквівалентності.

Таблиця 6.2.

R.a1 R.a2 S.b1 S.b2

а 1 1 h

а 2 2 g

b 3 3 h

b 1 1 h

Операція тета-з'єднання в мові SQL називається INNER JOIN (внутрішнє з'єднання ) і використовується, коли потрібно включити всі рядки з обох таблиць, що задовольняють умові об'єднання. Внутрішнє з'єднання має місце і тоді, коли в пропозиції WHERE порівнюються значення полів з різних таблиць. В цьому випадку будується декартовий добуток рядків першої і другої таблиць, а з одержаного набору даних відбираються записи, що задовольняють умовам об'єднання.

В умовах об'єднання можуть брати участь поля, що відносяться до одного і того ж типу даних і що містять один і той же вид даних, але вони не обов'язково повинні мати однакові імена.

Блоки даних з двох таблиць об'єднуються, як тільки у вказаних полях будуть знайдені співпадаючі значення.

Якщо в пропозиції FROM перераховано декілька таблиць і при цьому не вживається специфікація JOIN, а для вказівки відповідності полів з таблиць використовується умова в пропозиції WHERE, то деякі реляційні СУБД (наприклад, Access) оптимізують виконання запиту, інтерпретуючи його як з'єднання.

Якщо перераховувати ряд таблиць або запитів і не указувати умови об'єднання, як початкова таблиця буде вибрано декартовий (прямий) добуток всіх таблиць.

SELECT R.a1, R.a2, S.b1, S.b2

FROM R, S

WHERE R.a2=S.b1

або

SELECT R.a1, R.a2, S.b1, S.b2

FROM R INNER JOIN S ON умова

Приклад 6.4. Тета-з'єднання відношень в SQL.

4.2 Природне з'єднання

Природним з'єднанням називається з'єднання по еквівалентності двох відношень R і S, виконане по всіх загальних атрибутах, з результатів якого виключається по одному екземпляру кожного загального атрибута.

Таблиця 6.3.

R.a1 R.a2 або S.b1 S.b2

а 1 h

а 2 g

b 3 h

b 1 h

SELECT R.a1, R.a2, S.b2

FROM R, S

WHERE R.a2=S.b1

або

SELECT R.a1, S.b1, S.b2

FROM R INNER JOIN S ON умова

Приклад 6.5. Природне з'єднання відношень в SQL.

Приклад 6.6. Вибрати інформацію про продані товари.

SELECT *

FROM Операція, Товар

WHERE Сделка.КодТовара=Товар.КодТовара

Або (що еквівалентне)

SELECT *

FROM Товар INNER JOIN Операція

ON Товар.КодТовара=Сделка.КодТовара

Приклад 6.6. Вибірка інформації про продані товари.

Можна створити вкладені об'єднання, додавши третю таблицю до результату об'єднання двох інших таблиць.

Приклад 6.7. Одержати відомості про товари, дату операцій, кількість проданого товару і покупців.

SELECT Товар.Назва, Операція.Кількість, Операція. Дата, Клієнт.Фірма

FROM Клієнт INNER JOIN (Товар INNER JOIN Операція ON Товар.КодТовара = Сделка.КодТовара) ON Клиент.КодКлиента=Сделка.КодКлиента

Приклад 6.7. Вибірка відомостей про товари, дату операцій, кількість проданого товару і покупців.

Використовування загальних імен таблиць для ідентифікації стовпців незручно через їх громіздкість. Кожній таблиці можна привласнити яке-небудь коротке позначення, псевдонім.

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

Яка з таблиць буде ведучою, визначає вид з'єднання. LEFT - ліве зовнішнє з'єднання, ведучою є таблиця, розташована зліва від виду з'єднання; RIGHT - праве зовнішнє з'єднання, головна таблиця розташована праворуч від виду з'єднання.