Реферат
Організація баз даних
1. Файлові системи. Особливості та недоліки використання традиційних файлових систем
Файлові системи – це набір програм, які виконують для користувачів деякі операції, наприклад створення звітів. Кожна програма визначає свої власні дані і керує ними.
Файлові системи були першою спробою комп¢ютиризувати відомі всім ручні картки. Подібна картка (або підшивка документів) в деякій організації могла містити всю зовнішню і внутрішню документацію, пов¢язану з деяким продуктом, завданням, клієнтом або співробітником. Зазвичай таких папок дуже багато, їх нумерують і зберігають у шафах. З метою безпеки шафи можуть навіть закривати на ключ або зберігати у приміщеннях, що знаходяться під охороною. У більшості людей вдома є власні карототеки, що містять підшивки документів, такі як рахунки, гарантійні талони, чеки про сплату за комунальні послуги, страхові та банківські папери, звіти з лабораторних робіт, конспекти лекцій тощо. Якщо нам потрібна будь-яка інформація, потрібно вручну переглянути картотеку від початку до кінця, щоб знайти те, що нас цікавить. Цей процес можна вдосконалити, ввівши у такій системі процес індексування, який дозволить прискорити процес пошуку потрібних відомостей. Наприклад, можна використати спеціальні розділювачі або окремі папки для різноманітних логічно пов¢язаних типів об¢єктів.
Ручні картки дозволяють успішно справитися з поставленими задачами, якщо кількість об¢єктів, що зберігаються, невелике. Також вони цілковито підходять для роботи з великою кількістю об¢єктів, які потрібно лише зберігати, або вилучати. Проте вони виявляються цілковито не придатними для випадків, коли потрібно встановити перехресні зв’язки чи виконати обробку відомостей.
В наш час клієнтам, менеджерам та іншим співробітникам з кожним днем потрібно все більше і більше інформації. Зрозуміло, що картотека зовсім не підходить для виконання роботи подібного типу. Файлові системи були розроблені для забезпечення більш ефективних способів доступу до даних. Проте, замість організації централізованого сховища всіх даних підприємства, використовувався децентралізований підхід, за яким співробітники кожного відділу за допомогою спеціалістів по обробці даних (програмістів) працюють зі своїми власними даними і зберігають їх у своєму відділі у окремих файлах. Файл являє собою простий набір записів , які містять логічно зв’язані дані кожен запис містить логічно зв’язаний набір з одного чи декількох полів, кожне з яких представляє деяку характеристику об’єкту, що моделюється. Кожен співробітник звертається до даних за допомогою спеціалізованої прикладної програми. Набір прикладних програм кожного відділу дозволяє вводити дані, працювати з файлами і генерувати деякий фіксований набір спеціалізованих звітів. Найважливішим є те, що фізична структура і методи зберігання записів файлів з даними жорстко визначаються в коді прикладної програми.
Обмеження, притаманні файловим системам
- Відокремлення та ізоляція даних.
- Дублювання даних.
- Залежність від даних.
- Несумісність файлів.
- Фіксовані запити/ швидке збільшення кількості додатків.
2. Системи керування баз даних. Основні компоненти і функції сучасних СКДБ
Традиційних можливостей файлових систем виявляється недостатньо для побудови навіть простих інформаційних систем. Серед потреб, які не покривалися можливостями систем управління файлами, можна назвати такі, як: підтримка логічно узгодженого набору файлів; забезпечення мови маніпулювання даними; відновлення інформації після різного роду збоїв; реально паралельна робота декількох користувачів. Можна сказати, що якщо прикладна інформаційна система спирається на деяку систему керування даними, яка володіє цими властивостями, тоді ця система є системою керування базами даних (СКБД).
Спрощена схема системи баз даних складається з таких компонентів: дані, апаратне забезпечення, програмне забезпечення, користувачі.
До числа функцій СКБД відносять наступні:
1. Безпосереднє керування даними у зовнішній пам’яті. Ця функція включає забезпечення необхідних структур зовнішньої пам’яті як для зберігання безпосередньо даних, що належать до БД, так і для службових цілей, наприклад, для прискорення доступу до даних, журналів транзакцій тощо..СКБД підтримує власну систему іменування об’єктів БД (це дуже важливо, оскільки імена об’єктів бази даних відповідають іменам об’єктів предметної області).
2. Управління буферами оперативної пам’яті. СКБД звичайно працюють з БД значного розміру; у будь-якому випадку цей розмір значно перевищує доступний об’єм оперативної пам’яті. При зверненні до деякого елементу даних буде виконуватися обмін зі зовнішньою пам’яттю, щоб реально підвищити цю швидкість використовують буферизацію даних в оперативній пам’яті.
3. Керування транзакціями. Транзакція - це послідовність операцій над БД, які розглядаються СКБД як єдине ціле. Будь-яку транзакцію, що успішно виконується, СКБД фіксує зміни у БД, у зовнішній пам’яті, або жодна з цих змін ніяк не відбивається на стані БД.
4. Журналізація. Однією з основних вимог до СКБД є надійне зберігання даних у зовнішній пам’яті. Під надійністю зберігання розуміють те, що СКБД повинна бути в змозі відновити останній узгоджений стан БД після будь-якої апаратної або програмної відмови. Забезпечення надійного збереження даних у БД потребує надлишкового збереження даних, причому та їх частина, яка використовується для відновлення, повинна зберігатися особливо ретельно. Найбільш поширеним методом збереження такої надлишкової інформації –є ведення журналу змін БД.
3. Концептуальна модель даних. Сутність, атрибут, в’язок. Діаграма «сутність-зв’язок»
Концептуальна модель – це представлення всієї інформації БД в дещо абстрактній формі (як і у випадку зовнішнього представлення) в порівнянні з фізичним способом зберігання інформації.
З позиції моделювання джерело інформації називають предметною областю (ПрО)
, а знакову систему, що використовується (спосіб абстрагування предметної області) - моделлю даних (МД)
. Опис предметної області у термінах обраної моделі даних називають концептуальною схемою ПрО
. Для опису ПрО використовують такі основні конструктивні елементи – сутність
, атрибут
та зв’язок
.
Реальний світ складається з об’єктів, що взаємодіють. Об’єкти часто представляються у вигляді іменників, а відношення – у вигляді дієслів. Об’єкти – це речі, які користувачі вважають потрібними в моделюванні частин реальності. Об’єкти можуть бути конкретними (людина, автомобіль, дерево, книги тощо) та концептуальними (організації, проекти, штатний розклад тощо). Об’єктна множина – це позначення множини речей одного типу. Об’єкт-елемент –позначення одного елементу об’єктної множини.
Речі в природі взаємодіють, це саме стосується сутностей. Зв’язок або відношення ‑ це узагальнене поняття, передбачене для позначення відокремленого в ПрО відношення між двома або більше сутностями. Як і сутності, кожна категорія користувачів відокремлює зв’язки у відповідності зі своєю концепцією ПрО. проектування БД починається з попередньої структуризації предметної області: фіксації об’єктів (сутностей), властивостей цих об’єктів та види відношень між об’єктами.
Інформацію про проект підсумовують з використанням графічних
діаграм
. Для них використовують наступні позначення:
1. Сутність зображають прямокутниками.
2. Атрибути позначають овалами.
3. Зв’язки зображають ромбами.
Відношення саме по собі є об’єктною множиною, що складається з елементів двох об’єктних множин.
Концептуальне представлення – це представлення всього вмісту бази даних, а концептуальна схема – це визначення такого представлення. Вважається, що в якості кінцевої мети створення концептуальної схеми опису всього підприємства є не лише опис самих лише даних, але й того, як ці дані використовуються, як вони переміщуються всередині підприємства, для чого використовуються у кожному окремому випадку.
4. Реляційна модель даних та її аспекти. Поняття відношення
Реляційна система базується на формальних основах, або теорії, яка називається реляційною моделлю даних. Для такої системи виконуються як мінімум три умови:
Структурний аспект.Дані в базі сприймаються користувачами у вигляді таблиць (і лише таблиць);
Аспект цілісності. Ці таблиці задовольняють певним умовам цілісності (це ми розглянемо на цій лекції трохи згодом);
Аспект обробки. У розпорядженні користувача є оператори маніпулювання даними (наприклад, вибірки інформації), які генерують нові таблиці на основі вже наявних і серед цих операторів є принаймні оператори вибірки (select), проекції (project) і об¢єднання (join).
На рис. Наведено простий приклад реляційної бази даних відділів (таблиця DEPT) і службовців (таблиця EMP).
DEPT
| DEPT# | DNAME | BUDGET |
| D1 | Marketing | 10M |
| D2 | Development | 12M |
| D3 | Research | 5M |
EMP
| EMP# | ENAME | DEPT# | SALARY |
| E1 | Lopez | D1 | 40K |
| E2 | Cheng | D2 | 42K |
| E3 | Finizi | D2 | 30K |
| E4 | Satio | D2 | 35K |
Операція вибірки SELECT передбачена для вибірки певних рядків та стовпців
SELECT DEPT#, DNAME, BUDGET
FROM DEPT
WHERE BUDGET>8M
Очевидно, результат кожної з 3-х представлених операцій – це ще одна таблиця (іншими словами, ці оператори – такі що породжують таблиці). Це є реляційною властивістю
замкненості. Вона має велике значення і, головним чином, через те, що результатом виконання операції є об¢єкт того ж роду, що ї об¢єкт, над яким виконується операція, а саме – таблиця. Це, крім того, означає, що над результатом операції можна виконувати знову деякі операції (вибрати стовпці). Весь інформаційний вміст бази представляється виключно одним єдиним способом, а саме – явним завданням значень, розміщених в позиціях стовпців у рядках таблиці. Цей метод представляється єдино можливим для реляційних баз даних (на логічному рівні). Зокрема, немає ніяких вказівників, які пов¢язують одну таблицю з іншою.
Якщо припустити, що реляційна база даних – це просто база даних, в якій дані зберігаються у вигляді таблиць, тоді виникає питання, чому ми називаємо таку базу реляційною
, а не табличною
? Відповідь проста (вона вже колись звучала) – relation
(відношення) це математична назва таблиці. Наприклад, можна сказати, що база даних робітників і відділів містить два відношення
.
У даний час у неформальному контексті терміни відношення
та таблиця
вважаються синонімами. На практиці термін таблиця
використовується частіше, ніж термін відношення
.
Типи – це об¢єкти (множини об¢єктів), які можна обговорювати. Відношення – це факти (множини фактів), відносно об¢єктів, які можна обговорювати.
З концептуальної точки зору операції з представленнями фактично реалізуються через заміну посилання на ім¢я представлення, виразом,який визначає представлення (тобто виразом, збереженим у каталозі). Тому можна стверджувати, що базові змінні-відношення існують незалежно, а представлення – ні, оскільки залежать від базових змінних-відношень.
5. Основні поняття реляційної моделі даних. Тип даних, домен, кортеж, атрибут, кардинальність, степінь відношення. Схема відношення та бази даних
Найбільш поширена трактовка реляційної моделі даних належить Дейту. Згідно ньому реляційна модель характеризується трьома частинами, що описують різні аспекти реляційного підходу: Єдиною структурою даних, що використовується в реляційних БД; механізмами маніпулювання даними та цілісністю сутностей та посилань.
Реліційна модель складається з п¢яти компонентів:
1. Необмежений набір скалярних типів (включаючи, зокрема, логічний тип або значення істини);
2. Генератор типів відношень і відповідна інтерпретація для таких згенерованих типів відношень.
3. Можливість визначення змінних відношень для таких згенерованих типів відношень.
4. Операція реляційного присвоєння для присвоєння реляційних значень таким змінним відношенням;
5. Необмежений набір реляційних операторів для одержання значень відношень з інших значень відношень.
Основними термінами тут є: відношення, домен, атрибут, кортеж, первинний ключ, кардинальність, ступінь.
| Формальний реляційний термін | Неформальний еквівалент |
| Відношення | Таблиця |
| Кортеж | Рядок або запис |
| Кардинальність | Кількість рядків |
| Атрибут | Стовпчик або поле |
| Степень | К-ть стовпчиків |
| Первинний ключ | Унікальний ідентифікатор |
| Домен | Сукупність допустимих значень |
Поняття типу даних в реляційній моделі даних повністю адекватно поняттю типа даних у мовах програмування. Кожне значення даних обов¢язково повинно мати свій тип.
Поняття домена більш специфічне для баз даних, хоча і має деякі аналогії з підтипами в деяких мовах програмування. Це дещо відмінне від типу даних, що визначаються системою, подібно наведеним. Основне призначення доменів у мові SQL – дозволити надавати (присвоїти) вбудованим типам скорочених імен, які можна було б використовувати для спрощеного запису визначення деяких стовпців у базових таблицях, наприклад таким чином:
CREATE DOMAIN S# CHAR(5);
CREATE DOMAIN P# CHAR(6):
Кортеж - це множина пар {ім’я атрибуту, значення}, яка містить одне входження кожного імені атрибуту, що належить схемі відношень. "Значення" є допустимим значенням домена даного атрибута (або типу даних, якщо поняття домена не підтримується). Тому, тут t – множина компонентів виду Аі
:nі
, в яких nі
– значення типу Ті
, тобто значення атрибуту для атрибуту Аі
в кортежі t (i=1, 2,…n).
Значення m та n називають відповідно кардинальністю та ступінню (арністю). У подальшому відношення степені 1 називатимемо унарним, 2 – бінарним, 3 – тернарним, n- n-арним. Тим самим, ступінь або "арність" кортежа, тобто число елементів у ньому, співпадає з "арністю" відповідної схеми відношень. Простіше кажучи, кортеж - це набір іменованих значень одного типу (рядок у відношенні). Набір кортежі складає тіло відношення.
Оскільки відношення є множинами кортежів, в них не повинні зустрічатися однакові кортежі і порядок кортежів у відношенні є несуттєвим.
Схема відношення - це іменована множина пар ім’я атрибута, ім’я домена (або типу, якщо поняття домена не підтримується). Ступінь, або "арність" схеми відношення - потужність цієї множини. Якщо всі атрибути одного відношення визначені на різних доменах, доцільно використовувати для іменування атрибутів імена відповідних доменів (пам’ятаючи при цьому, що це є лише зручним засобом іменування і не усуває різниці між поняттями домена і атрибута).
Схема БД (в структурному розумінні) - це набір іменованих схем відношень. Сукупність схем відношень називається схемою (реляционною) БД, а поточні значення відповідних відношень - (реляційною) БД.
6. Властивості відношень
Відношення мають певні властивості, причому всі вони дуже важливі і випливають з визначення відношення. Спочатку перерахуємо ці властивості:
· у відношеннях немає однакових кортежів;
· кортежі відношення не мають впорядкованості у напрямку знизу вверх;
· атрибути в кортежах не впорядковані зліва направо;
· кожен кортеж містить одне значення для кожного атрибуту.
Властивість 1. Відсутність однакових кортежів.
Дана властивість випливає з того факту, що тіло відношення – це математична МНОЖИНА (кортежів), а в математиці множини за визначенням не містять однакових елементів. Дійсно, поняття „однакові кортежі” немає змісту. Припустимо, що в деякому відношенні є кортеж, який відображає той істинний факт, що постачальник з номером С1 знаходиться в місті Лондон. Далі, якщо у відношенні є другий такий кортеж, він також відображатиме цей самий істинний факт. Проте, якщо істинний факт повторюється двічі, більш істинним він від цього не стане.
Властивість 2. Відсутність впорядкованих кортежів (зверху вниз).
Дана властивість так само випливає з того, що тіло відношення - це математична множина, а прості математичні множини у математиці не впорядковані. Наприклад, кортежі могли б розташовуватися у протилежному порядку, проте відношення залишилося тим самим. Тому у відношенні немає 5-, 97-, або 1-го кортежу, тобто немає поняття позиціонованої адресації.
Властивість 3. Відсутність впорядкування атрибутів (зліва направо).
Дана властивість випливає з того факту, що заголовок відношення також визначено як множина (атрибутів). Наприклад, атрибути відношення С могли би бути представлені, в іншому порядку. Це було б таке саме відношення, принаймні з точки зору реляційної моделі. Тому не існує першого або останнього атрибуту, не існує останнього атрибуту. Іншими словами, атрибут завжди визначається за іменем, а не за розташуванням.
Властивість 4 Кожен кортеж містить рівно одне значення для кожного атрибуту.
Остання властивість випливає безпосередньо з визначення кортежу: кортеж є множиною n впорядкованих пар виду Ai
:nI
(i=1,2,…n). Відношення, що задовольняє цій властивості називається нормалізованим або представленим у першій нормальній формі (1НФ).
Модель даних описує деякий набір родових понять і ознак, якими повинні володіти всі конкретні СКБД і БД, що ними керуються якщо вони базуються на цій моделі. Наявність моделі даних дозволяє порівнювати конкретні реалізації, використовуючи одну спільну мову.
Згідно з найбільш поширеною трактовкою, реляційна модель даних складається з трьох частин, які описують різні аспекти реляційного підходу: структурної частин, маніпуляційної частини і цілісної частини.
7. Цілісність бази даних. Первинний та зовнішній ключ
Значення кортежу t на атрибуті А називають t (А) або, іншими словами, А - значення кортежу t . Серед атрибутів схеми відношення можна вибрати таку підмножину атрибутів К Ì R, що для будь-якого ti
(K) буде виконуватися
ti
(K) ¹tj
(K),
при i¹j. Якщо К - мінімальна підмножина атрибутів з R , то К - ключ відношення. Відношення може мати не єдиний ключ. Ці ключі називають можливими ключами. Множина ключів, обраних з усіх можливих ключів і певним визначеним способом перерахованих, називають виділеними ключами. Один з виділених ключів відношення обирають в якості первинного.
Кінцеві відносини можуть мати різні ключі залежно від значень і кількості кортежів у відношенні, але при цьому схема відносини не міняється. Поняття ключа задається для схеми відносини, але це необхідно робити з обліком всіх можливих станів відносин з даною схемою. Маніпулювання зв'язками й сутностями може бути реалізоване за допомогою реляційної алгебри, що задає операції над відносинами.
У цілісній частині реляційної моделі даних фіксуються дві базових вимоги цілісності, які повинні підтримуватися в будь-який реляційній СУБД. Перша вимога називається вимогою цілісності сутностей. Об'єкту або сутності реального миру в реляційних БД відповідають кортежі відносин. Конкретна вимога полягає в тому, що будь-який кортеж будь-якого відношення повинен бути відрізнимо від будь-якого іншого кортежу цього відношення, тобто інакше кажучи, будь-яке відношення повинне мати первинний ключ. Друга вимога називається вимогою цілісності по посиланнях і є трохи більше складним. Очевидно, що при дотриманні нормалізованості відносин складні сутності реального миру представляються в реляційної БД у вигляді декількох кортежів декількох відносин. Обмеження цілісності сутності й по посиланнях повинні підтримуватися СУБД. Для дотримання цілісності сутності досить гарантувати відсутність у будь-якім відношенні кортежів з тим самим значенням первинного ключа. Із цілісністю по посиланнях справи йдуть трохи більш складно. Зрозуміло, що при відновленні відношення, що посилається (вставці нових кортежів або модифікації значення зовнішнього ключа в існуючих кортежах) досить стежити за тим, щоб не з'являлися некоректні значення зовнішнього ключа.
8. SQL – мова структурованих запитів сучасних СКБД
У сучасних СУБД звичайно підтримується єдина інтегрована мова, що містить всі необхідні засоби для роботи із БД, починаючи від її створення й користувальницький інтерфейс, що забезпечує базовий, з базами даних. Стандартною мовою найпоширеніших у цей час реляційних СУБД є мова SQL (Structured Query Language).
SQL є стандартною мовою для роботи з реляційними БД і в даний час підтримується всіма
Створення будь-якої таблиці передбачає визначення типу даних. Тип
даних
стовпчика визначає тип інформації, яка в ньому зберігається. Для того щоб визначити тип стовпчика необхідно після імені стовпчика вказати одне з ключових слів, яким позначається тип. Назви типів є регістрово незалежними.
Після визначення тип стовпчика буде зберігатися як його характеристика, яку змінити неможна.
Насамперед мова SQL сполучить засобу SDL і DML, тобто дозволяє визначати схему реляційної БД і маніпулювати даними. При цьому іменування об'єктів БД (для реляційної БД - іменування таблиць і їхніх стовпців) підтримується на язиковому рівні в тому розумінні, що компілятор мови SQL робить перетворення імен об'єктів у їхні внутрішні ідентифікатори на підставі спеціально підтримуваних службових таблиць-каталогів. Внутрішня частина СУБД (ядро) взагалі не працює з іменами таблиць і їхніх стовпців.
Мова SQL містить спеціальні засоби визначення обмежень цілісності БД. Знову ж обмеження цілісності зберігаються в спеціальних таблицях-каталогах, і забезпечення контролю цілісності БД виробляється на язиковому рівні, тобто при компіляції операторів модифікації БД компілятор SQL на підставі наявних у БД обмежень цілісності генерує відповідний програмний код.
Спеціальні оператори мови SQL дозволяють визначати так звані подання БД, що фактично є збереженими в БД запитами (результатом будь-якого запиту до реляційної БД є таблиця) з іменованими стовпцями. Для користувача подання є такою ж таблицею, як будь-яка базова таблиця, збережена в БД, але за допомогою подань можна обмежити або навпаки розширити видимість БД для конкретного користувача. Підтримка подань виробляється також на язиковому рівні.
Нарешті, авторизація доступу до об'єктів БД виробляється на основі спеціального набору операторів SQL. Ідея полягає в тому, що для виконання операторів SQL різного виду користувач повинен мати різні повноваження. Користувач, що створив таблицю БД, має повний набір повноважень для роботи із цією таблицею. У їхнє число входить повноваження на передачу всіх або частини повноважень іншим користувачам, включаючи повноваження на передачу повноважень. Повноваження користувачів описуються в спеціальних таблицях-каталогах, контроль повноважень підтримується на мовному рівні.
9. Створення запиту. Поняття вибірки. Оператор SELECT та приклади його використання
Назва мови запитів SQL є англійською абревіатурою слів, що перекладаються як Структурована Мова Запитів. Ця назву вона одержала не дарма, адже Запити — ймовірно найбільш часто використовуваний аспект SQL. Найбільша частина користувачів SQL найчастіше використовують цю мову саме для створення запитів. Тому сьогодні розглянемо SQL з точки зору запиту і того як він виконується в цій мові.
Запит — команда яку користувач віддає своїй СКБД, і яка повідомляє їй, яку інформацію потрібно вивести з таблиць у пам’ять. Ця інформація звичайно надсилається безпосередньо на екран комп’ютера, хоча в більшості випадків, її можна також передати принтеру, зберегти у файлі (як об’єкт у пам’яті комп’ютера), або надати як вихідну інформацію для іншої команди або процесу.
Всі запити в SQL складаються з одиночної команди, структура якої оманливо проста, тому що її потрібно розширяти таким чином щоб виконати складні оцінки і обробки даних. Ця команда називається — SELECT (ВИБІР).
У найпростішій формі, команда SELECT просто дає інструкції базі даних щоб вибрати інформацію з таблиці. Наприклад, можна було б вивести повний вміст таблиці STUDENTS надрукувавши наступне:
SELECTsnum, sname, syear, sgrup
FROMStudents;
Якщо ВИ хочете бачити кожен стовпець таблиці, існує необов’язкове скорочення. Зірочка (*), яке може використовуватися для виведення повного списку стовпців наступним чином:
SELECT * FROM Students;
В загальному випадку, команда SELECT починається з ключового слова SELECT, за яким стоїть пропуск. Після цього повинен стояти список імен стовпців, які потрібно вибрати, відокремлених комами. Ключове слово FROM, що йде далі, супроводжується пропуском і іменем таблиці, до якої робиться запит. В кінці ставиться крапка з комою (;).
Таблиці мають тенденцію ставати дуже великими, оскільки з часом до них додається все більше і більше рядків, з яких користувача цікавлять лише деякі. SQL дає можливість встановлювати критерії для визначення рядків, що будуть обрані для виведення.
WHERE — пропозиція команди SELECT, яка дозволяє установлювати логічні функції (предикати), умова яких може бути істинним або хибним для будь-якої таблиці. Команда вибирає лише ті рядки, для яких таке твердження вірне.
SELECT *
FROM Students
WHERE syear=1984;
Синтаксис директиви WHERЕ дозволяє вказувати після імені стовпчика оператор порівняння, а потім вказувати значення. У наведеному вище прикладі використовується лише рівність. Але можна застосовувати також інші оператори порівняння після WHERE:
!= ,<> не рівно
<, >,<=, >= менше, більше, менше рівне, більше рівне
LIKE-рівне фрагментові тексту.
10. Реляційна алгебра і реляційне числення. Основні операції реляційної алгебри
В маніпуляційній складовій реляційної моделі даних визначаються два базові механізми маніпулювання реляційними даними: основана на теорії множин реляційна алгебра і реляційне числення, що базується на математичній логіці (вірніше, на численні предикатів першого порядку). Всі ці механізми володіють однією важливою властивістю: вони замкнені відносно поняття відношення. Це означає, що вирази реляційної алгебри і формули реляційного числення визначаються над відношеннями реляційні БД і результатами обчислень також є відношення. Як наслідок, будь-який вираз або формула можуть інтерпретуватися як відношення, що дозволяє використовувати їх в інших виразах або формулах. Основна ідея реляційної алгебри полягає в тому, що оскільки відношення є множинами, засоби маніпулювання відношеннями можуть базуватися на традиційних теоретико-множинних операціях, додатково до деяких спеціальних операцій, специфічних для баз даних. Набір основних алгебраїчних операцій, запропонований Коддом, складається з восьми операцій, які діляться на два класи, - теоретико-множинні операції і спеціальні реляційні операції. До складу теоретико-множинних операцій входять операції:
· об¢єднання відношень;
· перетин відношень;
· взяття різниці відношень;
· Декартовий добуток відношень.
Спеціальні реляційні операції включають:
· обмеження відношення;
· проекція відношення;
· з¢єднання відношень;
· ділення відношень.
Крім того, до складу алгебри входить операція присвоювання, що дозволяє зберігати в базі даних результати обрахунку алгебраїчних виразів, і операція перейменування атрибутів, яка дає можливість коректно сформувати заголовок (схему) результуючого відношення. При виконанні операції об¢єднання двох відношень одержується відношення, щ містять всі кортежі, що входять хоча б в одне з відношень-операндів. Операція перетину двох відношень дозволяє одержати відношення, що включає всі кортежі, які входять до обох відношень-операндів. Відношення, що є різницею двох відношень, включає всі кортежі, що входять у відношення - перший операнд; такі, що жоден з них не входить у відношення, яке є другим операндом. Результат операцій: відношення. При виконанні цієї операції необхідно пам¢ятати про сумісність відношень по об’єднанню: два відношення сумісні по об’єднанню в тому і лише в тому випадку, коли володіють однаковими заголовками. Вибірка (обмеження). Результатом обмеження відношення за деякою умовою є відношення, що містить кортежі відношення-операнда, яке задовольняє цій умові.
При виконанні проекції відношення на заданий набір його атрибутів одержується відношення, кортежі якого одержуються шляхом взяття відповідних значень з заданих стовпців кортежів відношення-операнда.
При з¢єднанні двох відношень за деякою умовою утворюється результуюче відношення, кортежі якого є конкатенацією кортежів першого і другого відношень і задовольняють цій умові. У операції реляційного ділення два операнди - бінарне і унарне відношення. Результуюче відношення складається з одноатрибутних кортежів, що містять значення першого атрибута кортежів першого операнда таких, що множина значень другого атрибута (при фіксованому значенні першого атрибута) співпадає з множиною значень другого операнда.
11. Агрегатні функції
За допомогою запиту можна одержувати узагальнене групове значення полів так само як і значення одного поля. Це робиться за допомогою агрегатних функцій. Агрегатні функції повертають одне значення для всієї групи таблиці. Всього таких функцій, що підтримуються будь-яким діалектом мови SQL, є 5:
*COUNT (лічильник) виробляє кількість рядків або не-NULL значення полів, які вибрав запит.
*SUM (Сума) знаходить арифметичну суму всіх вибраних значень даного поля.
*AVG (Середнє) виконує усереднення всіх вибраних значень даного поля.
*MAX знаходить найбільше з усіх вибраних значень даного поля.
*MIN знаходить найменше з усіх вибраних значень даного поля.
Агрегатні функції використовуються подібно іменам полів у команді SELECT запиту. Їх аргументами є імена стовпців. З SUM і AVG можуть використовуватись лише числові поля. Функції COUNT, MAX, и MIN, допускають використання і числових і символьних полів. Коли вони використовуються з символьними полями, MAX і MIN будуть транслювати їх у еквівалент ASCII, за яким MIN буде означати перше, а MAX – останнє значення за алфавітом.
AVG() – повертає середнє арифметичне значень для заданого стовпця. Результатом буде таблиця з додатковим стовпцем, що міститиме середнє значення, причому йому можна давати окрему назву:
SELECT AVG(amt) as avamt
From Orders;
Функція COUNT дещо відрізняється від усіх. Вона повертає кількість значень у даному столпці, або кількість рядків у таблиці.
Select count(snum) from orders;
Коли вона проводить підрахунок наявних значень у тому чи іншому стовпчику, перед іменем аргументу можна використовувати необов¢язкове ключове слово DISTINCT, яке вказує, що перед застосуванням цієї функції дублюючі рядки потрібно ігнорувати при підрахунку. Тобто виконується підрахунок лише унікальних значень у деякому полі. Можна використовувати її, наприклад, для підрахунку числа продавців які включені до таблиці.
МАХ(), MIN() повертають, відповідно найбільше і найменше значення стовпчика. Причому пошук можна здійснювати не серед всіх значень стовпчика, а лише у тих рядках, які задовольняють критерію, вказаному в директиві WHERE:
SELECT MAX (amt) as maxamt, MIN(amt) as minamt
FROM Orders
Пропозиція GROUP BY дозволяє визначити підмножену значень у певному полі в термінах іншого поля, і застосувати функцію агрегату до підмножини. Це дає можливість об¢єднувати поля і агрегатні функції у єдиній команді SELECT.
Згідно строгій інтерпретації ANSI SQL, не можна використовувати агрегат агрегату.
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-ти.