Операції з’єднання відношень

12. Операції з’єднання відношень

 

Практична робота з БД показала, що для уникнення надлишковості інформації, що зберігається, і водночас для забезпечення її цілісності, цю інформацію доцільно розподілити між двома або більше таблицями. Коли ж потрібно переглянути загальні дані для деякої предметної області проводиться з’єднання таблиць, в результаті якого інформація однієї таблиці доповнюється даними з інших, зв’язаних з нею таблиць. Такі таблиці повинні мати спільні стовпці (найчастіше це поля первинного та зовнішнього ключів), що вказуються у директиві WHERE. З операцією з’єднання ми зустрічалися при розгляді операцій реляційної алгебри і познайомилися з операціями природного з’єднання, з’єднання за умовою та еквіз’єднанням. Саме про останній варіант піде мова сьогодні, а саме про його різновиди. Розрізняють внутрішнє (INNER JOIN) та зовнішнє з’єднання (OUTER JOIN). Останнє ділиться в свою чергу на ліве LEFT та праве RIGHT зовнішні з’єднання (OUTER JOIN).

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

1)         SELECT A.A#, Aname, B#, Bname

FROM A, B

WHERE A.A#=B.A#;

Зовнішнє з’єднання використовується для того, щоб вибирати з таблиці рядки, що містять як відповідні так і не відповідні значення для двох таблиць. Невідповідними є поля тих рядків, які не знаходять у пов’язаній таблиці

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

Записується оператор лівого зовнішнього з’єднання наступним чином:

2)         SELECT A.A#, A.name, B#, Bname

FROM A, B

WHERE A.A# *= B.A#;

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

13. Створення базових відношень за допомогою оператора CREATE TABLE

 

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

Реляційні системи надають засоби для створення, в першу чергу базових змінних-відношень. На мові SQL, наприклад, ця функція забезпечується оператором CREATE TABLE EMP…;

Якщо за допомогою засобів мови SQL визначають базу даних, то для кожної базової таблиці визначення містить один оператор CREATE TABLE ім¢я базової таблиці (список елементів таблиці);. Під елементом таблиці розуміють найчастіше визначення стовпчика, яке має наступний вигляд:

<ім¢я стовпчика>< тип/ім¢я домена>[<значення за замовчуванням>]

Оператор CREATE TABLE дозволяє не лише створити первинні і зовнішні ключі, визначити унікальні стовпці і рядки, але й вказати обмеження, що використовуються при перевірці допустимих значень даних, а також стандартні значення для стовпців.

CREATE TABLE<table name>

(<column name><data type><column constraint>,

<column name><data type><column constraint>,);

CREATE TABLE S// suppliers

(S#CHAR(5),

SNAMECHAR(20),

STATUSNUMERIC(5),

CITYCHAR(15),

PRIMARY KEY (S#));

 

14. Обмеження відношень

 

Крім зазаначення безпосередньо типів та розміру полів таблиці можна задавати обмеження з метою забезпечення цілісності таблиці. При створенні таблиці (або, при її зміні), можна накладати обмеження на значення які можуть вводитися в поля. Якщо це зробити, SQL буде відхиляти будь-які значення які порушують критерії які визначені для того чи іншого поля.

Обмеження PRIMARY KEY. При розробці таблиці визначається набір потенціальних ключів, тобто тих полів, значення яких однозначно ідентифікують кожен рядок таблиці. Причому з них, як наведено у попередньому прикладі, обирається в якості первинного ключа, для якого неможна використовувати атрибут NULL. Не може бути однакових значень цього поля для різних записів. Первинний ключ забезпечує цілісність даних у стовпцях та є першим кроком до цілісності у всій БД. СКБД створює унікальний індекс для стовпця первинного ключа. Якщо Ви не хочете, щоб створений індекс був кластеризованим, можна використовувати слово NONCLUSTERED при створенні обмеження.

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

Обмеження цілісності за посиланням, або зовнішній ключ. Після створення в одній таблиці поля первинного ключа, значення цього поля можуть використовуватися в іншій таблиці (таблицях) БД для створення істинних тверджень при характеристиці різних сутностей. В інших таблицях це поле вже не є унікальним, має назву поля зовнішнього ключа. FOREIGN KEY пов’язує один або декілька стовпців таблиці з ідентичним номером стовпців іншої таблиці. Його значення не можуть вийти за межі діапазону значень, який у першопочатковій таблиці задається значеннями цього ж поля але вже первинного ключа. Таким чином обмеження FOREIGN KEY використовується лише після створення у відповідній таблиці обмеження PRIMARY KEY для цього є поля. Причому, ці поля не обов’язково повинні мати однакову назв (хоча це бажано), але повинні мати один тип і однакову довжину. Коли в таблиці, де визначено обмеження PRIMARY KEY обновлюються значення стовпців, автоматично обновлюються значення в таблиці, для якої визначено обмеження FOREIGN KEY.

Обмеження СНЕСК (перевірка). Можна запобігти небажаним або помилковим введенням значень. Для цього використовується обмеження СНЕСК. Воно накладає умову на значення, що вводиться, які можна ввести в один або декілька стовпців таблиці. Це область з якої стовпець може одержувати свої значення. СНЕСК виражається як нулевий вираз, який приймає значення true або false.

Обмеження DEFAULT (значення за замовчуванням). Коли ви вставляєте новий рядок у таблицю без вказання одного чи декількох значень чи при введені інформації залишаєте незаповненими поля, SQL повинен мати значення за замовчуванням для заповнення цих пропусків. Найбільш загальним значенням за замовчуванням є — NULL. Це значення за замовчуванням для будь-якого стовпця, якому не надано обмеження NOT NULL. Значення DEFAULT вказується в команді CREATE TABLE у той самий спосіб що і обмеження стовпця, хоча, з технічної точки зору, воно не має обмежуючих властивостей — воно не обмежує значення, які можна вводити, а просто визначає, що може статися, якщо не ввести будь-яке з них. В якості обмеження обирають значення, що найчастіше зустрічається у стопці.

15. Групування даних таблиці за допомогою директив ORDER BY і GROUP BY

 

Таблиці — це невпорядковані набори рядків і дані, які входять до них, не обов’язково з’являються в деякій визначеній послідовності. SQL використовує команду ORDER BY щоб дозволити користувачеві впорядкувати ваше виведення. Ця команда впорядковує виведення запиту згідно зі значеннями в тій чи іншій кількості обраних стовпців. Для кожного стовпця можна встановити як зростання (ASC) так і спадання (DESC). За замовчуванням встановлюється зростання. Для впорядкування даних таблиці за спаданням номеру груп запишемо таку команду:

SELECT * FROM Students ORDER BY sgrup DESC;

Якщо в директиві ORDER BY після імені першого стовпчика вказати другий, тоді після впорядкування за першим стовпчику будуть впорядковані за другим.

Якщо стовпчики перераховані в директиві SELECT, в директиві ORDER BY їх можна визначити, вказавши порядкові номери їх слідування після слова SELECT, тобто замість назви стовпчика ставляться порядкові номери:

SELECT sname, sgrup

FROM Students

ORDER BY 1,2

Директива GROUP BY

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

SELECT snum, sname, sgrup

FROM Students

GROUP BY snum;

Директива GROUP BY дозволяє сортувати значення стовпців у порядку зростання (це стандартний спосіб сортування), а потім перерахувати лише унікальні значення стовпців. На відміну від ORDER BY, директива GROUP BY видає як результат список унікальних значень стовпців. Проте спільні їх властивості в тому, що обидві директиви за замовчуванням сортують значення стовпців в порядку зростання.

В наступному прикладі виконується сортування рядків за стовпчиком sgrup. Спочатку значення з цього стовпчика сортуються в порядку зростання, а потім підраховується кількість представників кожної окремої групи і в якості другого результату виводяться у запиті:

SELECT sgrup, “HeadCount”=count (*)

FROM Students

GROUP BY sgrup

Крім того можна вибрати певні групи за допомогою директиви HAVING, яка дозволяє порівнювати деяку властивість групи з константою. Якщо група задовольняє умові (яка є логічним виразом) з директиви HAVING, тоді вона включається до результату запиту.

SELECT sgrup, “HeadCount”=count (*)

FROM Students

GROUP BY sgrup

HAVING count(*)=5;

Одержаний результат буде впорядкований відносно номеру групи і стосуватиметься лише тих груп, кількість представників яких у загальному списку рівна 5-ти.