кафедра комп'ютерних та інформаційних технологій
курс лекцій
У цьому курсі розглядаються питання організації баз даних і знань. Це важлива тема, без грунтовного знайомства з якою, у наш час, неможливо стати кваліфікованим фахівцем у сфері інформаційних технологій.
Основне призначення даного курсу - систематичне введення в ідеї та методи, використовувані в сучасних системах управління базами даних. У курсі не розглядається яка-небудь одна популярна СУБД; викладається в рівній мірі відноситься до будь-якої сучасної системі. Як показує досвід, без знання основ теорії баз даних важко на серйозному рівні працювати з конкретними системами, як би добре вони не були задокументовані.
курс лекцій
У цьому курсі розглядаються питання організації баз даних і знань. Це важлива тема, без грунтовного знайомства з якою, у наш час, неможливо стати кваліфікованим фахівцем у сфері інформаційних технологій.
Основне призначення даного курсу - систематичне введення в ідеї та методи, використовувані в сучасних системах управління базами даних. У курсі не розглядається яка-небудь одна популярна СУБД; викладається в рівній мірі відноситься до будь-якої сучасної системі. Як показує досвід, без знання основ теорії баз даних важко на серйозному рівні працювати з конкретними системами, як би добре вони не були задокументовані.
Зміст
\ T "Заголовок 1; 1" ЛЕКЦІЯ 1. Поняття СУБД. Функції СУБД ............................................... 7
1.1 Запровадження ................................................ .................................................. ... 7
1.2 Поняття БД і СУБД ............................................. ...................................... 7
1.3 Рівні абстракції в СУБД. Функції абстрактних даних .................. 9
1.4 Уявлення ................................................ .......................................... 10
1.5 Функції СУБД ............................................... .......................................... 11
1.6 Експертні системи і бази знань ............................................ ............ 11
ЛЕКЦІЯ 2. Моделі БД ................................................ .............................. 13
2.1 Огляд ранніх (дореляціонних) СУБД ........................................... ....... 13
2.2 Системи, засновані на інвертованих списках .............................. 13
2.3 Ієрархічна модель ............................................... .............................. 14
2.4 Мережева модель ............................................... .......................................... 16
2.5 Основні переваги і недоліки ранніх СУБД ............................... 17
ЛЕКЦІЯ 3. Реляційна модель та її характеристики. Цілісність в реляційній моделі 18
3.1 Представлення інформації у реляційних БД .................................... 18
3.2 Домени 19
3.3 Відносини. Властивості і види відносин .............................................. . 20
3.4 Цілісність реляційних даних .............................................. ........... 21
3.5 Потенційні і первинні ключі ............................................. .......... 22
3.6 Зовнішні ключі ............................................... ......................................... 22
3.7 Посилальна цілісність ............................................... ............................ 23
3.8 Значення NULL і підтримка посилальної цілісності ........................... 24
ЛЕКЦІЯ 4. Реляційна алгебра ................................................ .............. 25
4.1 Поняття реляційної алгебри .............................................. ................. 25
4.2 Замкнутість в реляційній алгебрі ............................................. .......... 25
4.3 Традиційні операції над множинами ............................................ 25
4.4 Властивості основних операцій реляційної алгебри ............................ 27
4.5 Спеціальні реляційні операції .............................................. ....... 28
ЛЕКЦІЯ 5. Питання проектування БД ............................................... .. 34
5.1 Поняття проектування БД .............................................. ..................... 34
5.2 Функціональні залежності ............................................... .................. 35
5.3 Тривіальні і нетривіальні залежності .......................................... 36
5.4 Замикання безлічі залежностей і правила висновку Армстронга ... 36
5.5 Непріводімие безліч залежностей .............................................. ... 38
5.6 Нормальні форми - основні поняття ............................................ ... 38
5.7 Декомпозиція без втрат і функціональні залежності ................... 39
5.8 Діаграми функціональних залежностей ........................................... 40
ЛЕКЦІЯ 6. Проектування БД. Нормальні форми відносин ......... 42
6.1 Перша нормальна форма. Можливі недоліки відносини в 1НФ 42
6.2 Друга нормальна форма. Можливі недоліки відносини у 2НФ 44
6.3 Третя нормальна форма. Можливі недоліки відносини в 3НФ 45
6.4 Нормальна форма Бойса-Кодда ............................................ ................ 46
ЛЕКЦІЯ 7. Проектування БД. Нормальні форми відносин (продовження) 49
7.1 Багатозначні залежності ............................................... ...................... 49
7.2 Четверта нормальна форма .............................................. .................... 51
7.3 Залежності з'єднання ............................................... ........................... 51
7.4 П'ята нормальна форма .............................................. .......................... 53
7.5 Підсумкова схема процедури нормалізації ............................................ 53
ЛЕКЦІЯ 8. Проектування БД методом сутність-зв'язок. ER-діаграми 55
8.1 Виникнення семантичного моделювання .................................... 55
8.2 Основні поняття методу .............................................. .......................... 55
8.3 Діаграми ER-екземплярів і ER-типу ......................................... ......... 56
8.4 Правила формування відносин .............................................. ......... 59
8.5 Методологія IDEF1 (самостійне вивчення ).................................. 62
ЛЕКЦІЯ 9. Мова SQL ................................................ ................................ 66
9.1 Історія створення та розвитку SQL ............................................ .............. 66
9.2 Основні поняття SQL .............................................. .............................. 66
9.3 Запити на читання даних. Оператор SELECT ..................................... 71
9.4 багатотабличних запити на читання (об'єднання ).............................. 75
ЛЕКЦІЯ 10. Мова SQL (продовження )............................................. .......... 77
10.1 Об'єднання і стандарт SQL2 ............................................. ................. 77
10.2 Підсумкові запити на читання. Агрегатні функції ............................. 80
10.3 Запити з угрупованням (пропозиція GROUP BY ).......................... 80
10.4 Вкладені запити ............................................... ................................ 82
ЛЕКЦІЯ 11. Мова SQL. (Продовження )............................................... ....... 86
11.1 Внесення змін до бази даних ............................................ ........... 86
11.2 Видалення існуючих даних (Оператор DELETE )...................... 87
11.3 Оновлення існуючих даних (Оператор UPDATE )................. 87
11.4 Визначення структури даних в SQL ............................................ .... 88
11.5 Поняття представлення ............................................... ........................... 91
11.6 Подання в SQL .............................................. ............................... 92
11.7 Системний каталог (самостійне вивчення ).................................. 93
ЛЕКЦІЯ 12. Забезпечення безпеки БД ............................................... . 99
12.1 Загальні положення ............................................... ................................... 99
12.2 Методи забезпечення безпеки .............................................. ....... 100
12.3 Виборче управління доступом .............................................. ... 101
12.4 Обов'язкове управління доступом .............................................. ..... 102
12.5 Шифрування даних ............................................... ............................. 102
12.6 Контрольний слід виконуваних операцій ....................................... 102
12.7 Підтримка заходів забезпечення безпеки в мові SQL ................... 103
12.8 Директиви GRANT і REVOKE ............................................. ............. 103
12.9 Подання та безпека .............................................. .............. 105
ЛЕКЦІЯ 13. Фізична організація БД: структури зберігання і методи доступу 106
13.1 Доступ до бази даних ............................................. .............................. 106
13.2 Кластеризація ................................................ ...................................... 108
13.3 Індексування ................................................ ................................... 108
13.4 Структури типу Б-дерева ............................................ ........................ 111
13.5 Хешування ................................................ ........................................ 114
ЛЕКЦІЯ 14. Оптимізація запитів ................................................ ......... 116
14.1 Оптимізація в реляційних СУБД ............................................. ....... 116
14.2 Приклад оптимізації реляційного вираження ............................... 116
14.3 Огляд процесу оптимізації .............................................. ................ 117
14.4 Перетворення виразів ............................................... ................. 119
ЛЕКЦІЯ 15. Відновлення після збоїв ............................................... .. 123
15.1 Поняття відновлення системи .............................................. .......... 123
15.2 Транзакції ................................................ ........................................... 123
15.3 Алгоритм відновлення після збою системи .................................. 125
15.4 Паралелізм. Проблеми паралелізму ............................................. 127
15.5 Поняття блокування ............................................... ............................. 129
15.6 Рішення проблем паралелізму .............................................. ........... 130
15.7 Статті без ситуації ............................................... ............................. 132
15.8 Здатність до впорядкування .............................................. ............... 133
15.9 Рівні ізоляції транзакції .............................................. ................ 134
15.10 Підтримка в мові SQL ............................................. ....................... 135
ЛЕКЦІЯ 16. Технології СУБД ................................................ ................. 136
16.1 Розподілені бази даних .............................................. ............... 136
16.2 Принципи функціонування розподіленої БД ........................... 136
16.3 Системи типу клієнт / сервер ............................................ .................... 139
16.4 Сервери баз даних .............................................. .............................. 139
ЛЕКЦІЯ 17. Сучасні постреляціонние моделі БД ........................ 141
17.1 Системи управління базами даних наступного покоління ........... 141
17.2 Орієнтація на розширену реляційну модель .......................... 141
17.3 Об'єктно-орієнтовані СУБД ............................................. ........ 143
1.2 Поняття БД і СУБД
1.3 Рівні абстракції в СУБД. Функції абстрактних даних
1.4 Уявлення
1.5 Функції СУБД
1.6 Експертні системи і бази знань
Одночасно з розвитком систем баз даних, відбувалося інтенсивне розвиток засобів обчислювальної техніки, що використовується для роботи з великими обсягами інформації. Обчислювальна потужність і, особливо, обсяги запам'ятовувальних пристроїв перших обчислювальних систем були недостатні для зберігання та обробки інформації в обсягах, необхідних на практиці.
У міру розвитку систем баз даних, змінювалися принципи організації даних в них: спочатку дані представлялися на основі ієрархічної, а надалі мережевої моделі. В кінці 1970-х - початку 1980-х років почали з'являтися перші реляційні продукти. В даний час системи баз даних на основі реляційної моделі займають лідируюче положення, незважаючи на заяви багатьох дослідників про швидкий перехід до об'єктно-орієнтованим системам. В даний час об'єктно-орієнтовані системи, тим не менш, розвиваються, хоча темпи їх розвитку і стримуються повільним прийняттям відповідних стандартів. Крім того, багато комерційних реляційні системи набувають об'єктно-орієнтовані риси. На підставі цього, можна припустити, що в майбутньому об'єктно-орієнтовані системи будуть поступово витісняти реляційні.
В даний час ведуться дослідження в наступних напрямках:
1. дедуктивні системи;
2. експертні системи;
3. розгортаються системи;
4. об'єктно-орієнтовані системи.
Система баз даних включає в себе (REF _Ref9908347 \ * MERGEFORMAT рис. 1.1):
1. дані, безпосередньо зберігаються в базі даних;
2. апаратне забезпечення;
3. програмне забезпечення;
4. користувачів:
4.1. прикладні програмісти;
4.2. кінцеві користувачі;
4.3. адміністратори баз даних.
Література: \ T "Заголовок 1; 1" ЛЕКЦІЯ 1. Поняття СУБД. Функції СУБД ............................................... 7
1.1 Запровадження ................................................ .................................................. ... 7
1.2 Поняття БД і СУБД ............................................. ...................................... 7
1.3 Рівні абстракції в СУБД. Функції абстрактних даних .................. 9
1.4 Уявлення ................................................ .......................................... 10
1.5 Функції СУБД ............................................... .......................................... 11
1.6 Експертні системи і бази знань ............................................ ............ 11
ЛЕКЦІЯ 2. Моделі БД ................................................ .............................. 13
2.1 Огляд ранніх (дореляціонних) СУБД ........................................... ....... 13
2.2 Системи, засновані на інвертованих списках .............................. 13
2.3 Ієрархічна модель ............................................... .............................. 14
2.4 Мережева модель ............................................... .......................................... 16
2.5 Основні переваги і недоліки ранніх СУБД ............................... 17
ЛЕКЦІЯ 3. Реляційна модель та її характеристики. Цілісність в реляційній моделі 18
3.1 Представлення інформації у реляційних БД .................................... 18
3.2 Домени 19
3.3 Відносини. Властивості і види відносин .............................................. . 20
3.4 Цілісність реляційних даних .............................................. ........... 21
3.5 Потенційні і первинні ключі ............................................. .......... 22
3.6 Зовнішні ключі ............................................... ......................................... 22
3.7 Посилальна цілісність ............................................... ............................ 23
3.8 Значення NULL і підтримка посилальної цілісності ........................... 24
ЛЕКЦІЯ 4. Реляційна алгебра ................................................ .............. 25
4.1 Поняття реляційної алгебри .............................................. ................. 25
4.2 Замкнутість в реляційній алгебрі ............................................. .......... 25
4.3 Традиційні операції над множинами ............................................ 25
4.4 Властивості основних операцій реляційної алгебри ............................ 27
4.5 Спеціальні реляційні операції .............................................. ....... 28
ЛЕКЦІЯ 5. Питання проектування БД ............................................... .. 34
5.1 Поняття проектування БД .............................................. ..................... 34
5.2 Функціональні залежності ............................................... .................. 35
5.3 Тривіальні і нетривіальні залежності .......................................... 36
5.4 Замикання безлічі залежностей і правила висновку Армстронга ... 36
5.5 Непріводімие безліч залежностей .............................................. ... 38
5.6 Нормальні форми - основні поняття ............................................ ... 38
5.7 Декомпозиція без втрат і функціональні залежності ................... 39
5.8 Діаграми функціональних залежностей ........................................... 40
ЛЕКЦІЯ 6. Проектування БД. Нормальні форми відносин ......... 42
6.1 Перша нормальна форма. Можливі недоліки відносини в 1НФ 42
6.2 Друга нормальна форма. Можливі недоліки відносини у 2НФ 44
6.3 Третя нормальна форма. Можливі недоліки відносини в 3НФ 45
6.4 Нормальна форма Бойса-Кодда ............................................ ................ 46
ЛЕКЦІЯ 7. Проектування БД. Нормальні форми відносин (продовження) 49
7.1 Багатозначні залежності ............................................... ...................... 49
7.2 Четверта нормальна форма .............................................. .................... 51
7.3 Залежності з'єднання ............................................... ........................... 51
7.4 П'ята нормальна форма .............................................. .......................... 53
7.5 Підсумкова схема процедури нормалізації ............................................ 53
ЛЕКЦІЯ 8. Проектування БД методом сутність-зв'язок. ER-діаграми 55
8.1 Виникнення семантичного моделювання .................................... 55
8.2 Основні поняття методу .............................................. .......................... 55
8.3 Діаграми ER-екземплярів і ER-типу ......................................... ......... 56
8.4 Правила формування відносин .............................................. ......... 59
8.5 Методологія IDEF1 (самостійне вивчення ).................................. 62
ЛЕКЦІЯ 9. Мова SQL ................................................ ................................ 66
9.1 Історія створення та розвитку SQL ............................................ .............. 66
9.2 Основні поняття SQL .............................................. .............................. 66
9.3 Запити на читання даних. Оператор SELECT ..................................... 71
9.4 багатотабличних запити на читання (об'єднання ).............................. 75
ЛЕКЦІЯ 10. Мова SQL (продовження )............................................. .......... 77
10.1 Об'єднання і стандарт SQL2 ............................................. ................. 77
10.2 Підсумкові запити на читання. Агрегатні функції ............................. 80
10.3 Запити з угрупованням (пропозиція GROUP BY ).......................... 80
10.4 Вкладені запити ............................................... ................................ 82
ЛЕКЦІЯ 11. Мова SQL. (Продовження )............................................... ....... 86
11.1 Внесення змін до бази даних ............................................ ........... 86
11.2 Видалення існуючих даних (Оператор DELETE )...................... 87
11.3 Оновлення існуючих даних (Оператор UPDATE )................. 87
11.4 Визначення структури даних в SQL ............................................ .... 88
11.5 Поняття представлення ............................................... ........................... 91
11.6 Подання в SQL .............................................. ............................... 92
11.7 Системний каталог (самостійне вивчення ).................................. 93
ЛЕКЦІЯ 12. Забезпечення безпеки БД ............................................... . 99
12.1 Загальні положення ............................................... ................................... 99
12.2 Методи забезпечення безпеки .............................................. ....... 100
12.3 Виборче управління доступом .............................................. ... 101
12.4 Обов'язкове управління доступом .............................................. ..... 102
12.5 Шифрування даних ............................................... ............................. 102
12.6 Контрольний слід виконуваних операцій ....................................... 102
12.7 Підтримка заходів забезпечення безпеки в мові SQL ................... 103
12.8 Директиви GRANT і REVOKE ............................................. ............. 103
12.9 Подання та безпека .............................................. .............. 105
ЛЕКЦІЯ 13. Фізична організація БД: структури зберігання і методи доступу 106
13.1 Доступ до бази даних ............................................. .............................. 106
13.2 Кластеризація ................................................ ...................................... 108
13.3 Індексування ................................................ ................................... 108
13.4 Структури типу Б-дерева ............................................ ........................ 111
13.5 Хешування ................................................ ........................................ 114
ЛЕКЦІЯ 14. Оптимізація запитів ................................................ ......... 116
14.1 Оптимізація в реляційних СУБД ............................................. ....... 116
14.2 Приклад оптимізації реляційного вираження ............................... 116
14.3 Огляд процесу оптимізації .............................................. ................ 117
14.4 Перетворення виразів ............................................... ................. 119
ЛЕКЦІЯ 15. Відновлення після збоїв ............................................... .. 123
15.1 Поняття відновлення системи .............................................. .......... 123
15.2 Транзакції ................................................ ........................................... 123
15.3 Алгоритм відновлення після збою системи .................................. 125
15.4 Паралелізм. Проблеми паралелізму ............................................. 127
15.5 Поняття блокування ............................................... ............................. 129
15.6 Рішення проблем паралелізму .............................................. ........... 130
15.7 Статті без ситуації ............................................... ............................. 132
15.8 Здатність до впорядкування .............................................. ............... 133
15.9 Рівні ізоляції транзакції .............................................. ................ 134
15.10 Підтримка в мові SQL ............................................. ....................... 135
ЛЕКЦІЯ 16. Технології СУБД ................................................ ................. 136
16.1 Розподілені бази даних .............................................. ............... 136
16.2 Принципи функціонування розподіленої БД ........................... 136
16.3 Системи типу клієнт / сервер ............................................ .................... 139
16.4 Сервери баз даних .............................................. .............................. 139
ЛЕКЦІЯ 17. Сучасні постреляціонние моделі БД ........................ 141
17.1 Системи управління базами даних наступного покоління ........... 141
17.2 Орієнтація на розширену реляційну модель .......................... 141
17.3 Об'єктно-орієнтовані СУБД ............................................. ........ 143
ЛЕКЦІЯ 1. Поняття СУБД. Функції СУБД
1.1 Введення1.2 Поняття БД і СУБД
1.3 Рівні абстракції в СУБД. Функції абстрактних даних
1.4 Уявлення
1.5 Функції СУБД
1.6 Експертні системи і бази знань
1.1 Введення
Історично склалося розвиток обчислювальних систем зумовило необхідність зберігання в електронному (машиночитаному) вигляді все більшої кількості інформації. Одночасно з вдосконаленням і подальшим розвитком обчислювальних систем зростали обсяги інформації, що підлягає обробці і зберіганню. Складнощі, що виникли при вирішенні на практиці завдань структурованого зберігання і ефективної обробки зростаючих обсягів інформації, стимулювали дослідження у відповідних областях. Завдання зберігання і обробки даних були формалізовані. Була створена теоретична база для вирішення завдань такого класу, результатом реалізації на практиці якої стали системи, призначені для організації обробки, зберігання і надання доступу до інформації. Пізніше такі системи стали називати системами баз даних.Одночасно з розвитком систем баз даних, відбувалося інтенсивне розвиток засобів обчислювальної техніки, що використовується для роботи з великими обсягами інформації. Обчислювальна потужність і, особливо, обсяги запам'ятовувальних пристроїв перших обчислювальних систем були недостатні для зберігання та обробки інформації в обсягах, необхідних на практиці.
У міру розвитку систем баз даних, змінювалися принципи організації даних в них: спочатку дані представлялися на основі ієрархічної, а надалі мережевої моделі. В кінці 1970-х - початку 1980-х років почали з'являтися перші реляційні продукти. В даний час системи баз даних на основі реляційної моделі займають лідируюче положення, незважаючи на заяви багатьох дослідників про швидкий перехід до об'єктно-орієнтованим системам. В даний час об'єктно-орієнтовані системи, тим не менш, розвиваються, хоча темпи їх розвитку і стримуються повільним прийняттям відповідних стандартів. Крім того, багато комерційних реляційні системи набувають об'єктно-орієнтовані риси. На підставі цього, можна припустити, що в майбутньому об'єктно-орієнтовані системи будуть поступово витісняти реляційні.
В даний час ведуться дослідження в наступних напрямках:
1. дедуктивні системи;
2. експертні системи;
3. розгортаються системи;
4. об'єктно-орієнтовані системи.
1.2 Поняття БД і СУБД
Система баз даних - це комп'ютеризована система основне завдання якої - збереження інформації та надання доступу до неї на вимогу.Система баз даних включає в себе (REF _Ref9908347 \ * MERGEFORMAT рис. 1.1):
1. дані, безпосередньо зберігаються в базі даних;
2. апаратне забезпечення;
3. програмне забезпечення;
4. користувачів:
4.1. прикладні програмісти;
4.2. кінцеві користувачі;
4.3. адміністратори баз даних.
Прикладні програмісти |
Кінцеві користувачі |
1. Дейт К.Дж. Введення в системи баз даних. -Пер. з англ. -6-е вид. -К. Діалектика, 1998. Стор. 279-301.
ЛЕКЦІЯ 7. Проектування БД. Нормальні форми відносин (продовження)
7.1 Багатозначні залежності7.2 Четверта нормальна форма
7.3 Залежності з'єднання
7.4 П'ята нормальна форма
7.5 Підсумкова схема процедури нормалізації
7.1 Багатозначні залежності
Нехай дано ненормалізоване ставлення UCTX (тобто відношення, яка не перебуває у 1НФ), що містить інформацію про курси навчання, викладачів і підручниках. Кожен кортеж такого ставлення складається з назви курсу (Course), a також груп імен викладачів (Teachers) і назв підручників (Texts) - на REF _Ref9932819 \ h \ * MERGEFORMAT рис. 7.1| UCTX | ||
| COURSE | TEACHERS | TEXTS |
| Фізика | проф. Іванов проф. Петров | основи механіки оптика |
| Математика | проф. Іванов | основи механіки дискретна математика тригонометрія |
Перетворимо це відношення в еквівалентну нормалізоване відношення. Слід зауважити, що для розглянутих даних функціональні залежності не задано (за винятком тривіальних залежностей типу Course ® Course). Тому висловлені в попередньому розділі ідеї не дозволяють створити ніякої формальної основи для виконання декомпозиції даного відносини на проекції.
| CTX | ||
| COURSE | TEACHER | TEXT |
| Фізика | проф. Іванов | основи механіки |
| Фізика | проф. Іванов | оптика |
| Фізика | проф. Петров | основи механіки |
| Фізика | проф. Петров | оптика |
| Математика | проф. Іванов | основи механіки |
| Математика | проф. Іванов | дискретна математика |
| Математика | проф. Іванов | тригонометрія |
У простій формулюванні нормалізоване відношення CTX означає, що кортеж {Course: c, Teacher: t, Техт: x} з'являється в даному відношенні тоді і тільки тоді, коли курс c читається викладачем t з використанням підручника x. Тоді, приймаючи до уваги допустимість існування для даного відносини всіх можливих комбінацій викладачів разом з підручниками, можна стверджувати, що для відносини CTX вірно наступне обмеження: якщо присутні обидва кортежу (c, tl, xl) і (c, t2, x2), тоді присутні також обидва кортежу (c, tl, x2) і (c, t2, xl)
Очевидно, що ставлення CTX характеризується значною надмірністю і призводить до виникнення аномалій оновлення. Наприклад, для додавання інформації про те, що курс фізики може читатися новим викладачем, необхідно створити два нових кортежу, по одному для кожного підручника. Тим не менш, ставлення CTX знаходиться в НФБК, оскільки є "повністю ключовим".
Можна помітити, що ситуація може бути виправлена на краще, якщо замінити ставлення СТХ його проекціями {Course, Teacher} і {Course, Text}, показаними на REF _Ref10022856 \ h \ * MERGEFORMAT рис. 7.3
| CT | СХ | |||
| COURSE | TEACHER | COURSE | TEXT | |
| фізика | проф. Іванов | фізика | основи механіки | |
| фізика | проф. Петров | фізика | оптика | |
| математика | проф. Іванов | математика | основи механіки | |
| математика | дискретна математика | |||
| математика | тригонометрія | |||
Повертаючись до розглянутого прикладу з дійсно коректної та бажаною декомпозицією, показаної на REF _Ref10022856 \ h \ * MERGEFORMAT рис. 7.3
Course->> Teacher
Course->> Text
Зверніть увагу на подвійну стрілку, яка в багатозначною залежності A->> B означає, що "B багатозначно залежить від A" або "A багатозначно визначає B".
Нехай A, B і C є довільними підмножинами безлічі атрибутів відносини R. Тоді B багатозначно залежить від A, що символічно виражається записом
А->> В
тоді і тільки тоді, коли безліч значень B, відповідне заданої парі (значення A, значення C) відношення R, залежить тільки від A, але не залежить від C.
Для даного відношення R {A, B, C} багатозначна залежність A->> B виконується тоді і тільки тоді, коли також виконується багатозначна залежність A ->> C. Таким чином, багатозначні залежності завжди утворюють пов'язані пари і тому їх зазвичай представляють разом в символічному вигляді:
А->> В | С.
Для розглянутого прикладу такий запис буде мати наступний вигляд:
Course->> Teacher | Text
Повертаючись до початкової задачі з відношенням СТХ, тепер можна відзначити, що описана раніше проблема з відношенням типу СТХ виникає через те, що воно містить багатозначні залежності, які не є функціональними. (Слід відзначити зовсім неочевидний факт, що саме наявність таких МОЗ вимагає вставляти два кортежу, коли необхідно додати дані ще про одне викладача фізики.) Проекції СТ і СГ не містять багатозначних залежностей, а тому вони дійсно представляють собою деякий удосконалення вихідної структури. Тому було б бажано замінити ставлення СТХ двома цими проекціями. Це можна зробити, виходячи з теореми Фейгіна, що наведена нижче.
Теорема Фейгіна (ця теорема є більш суворою версією теореми Хеза). Нехай А, В і С є множинами атрибутів відношення R {A, В, С}. Відношення R дорівнюватиме з'єднанню його проекцій {А, В} і {А, С} тоді і тільки тоді, коли для відносини R виконується багатозначна залежність А->> В | С.
7.2 Четверта нормальна форма
Відношення R знаходиться в четвертій нормальній формі (4НФ) тоді і тільки тоді, коли існують такі підмножини А і В атрибутів відношення R, що виконується (нетривіальна) багатозначна залежність А ->> В. Тоді всі атрибути відносини R також функціонально залежать від атрибута A .7.3 Залежності з'єднання
До цих пір передбачалося, що єдиною операцією в процесі декомпозиції є заміна даного відносини (при декомпозиції без втрат) двома його проекціями. Це припущення успішно виконувалося аж до визначення 4НФ. Однак існують відносини, для яких не можна виконати декомпозицію без втрат на дві проекції, але які можна піддати декомпозиції без втрат на три або більше проекції.На малюнку представлений приклад конкретного набору даних, що відповідають деякому моменту часу. Однак, якщо дане відношення задовольняє деякому який не залежить від часу обмеження, то 3-декомпозіруемость відносини TSG може бути більш фундаментальним і не залежним від часу властивістю, тобто властивістю, які задовільняються для всіх допустимих значень даного відношення. Для того щоб зрозуміти, яким має бути таке ставлення, перш за все відзначимо, що твердження "ставлення TSG одно з'єднанню трьох проекцій TS, SG і TG" еквівалентно наступного твердження:
Якщо пара (t1, s1) перебуває у відношенні TS і пара (s1, g1) перебуває у відношенні SG і пара (t1, g1) перебуває у відношенні TG то трійка (t1, s1, g1) перебуває у відношенні TSG.
| TSG | ||
| TEACHER | SUBJECT | GROUP |
| Іванов | Математика | А-98-51 |
| Іванов | Фізика | Б-00-51 |
| Петров | Математика | А-99-51 |
| Петров | Фізика | А-98-51 |
| TS | SG | TG | ||||||||||||
| TEACHER | SUBJECT | SUBJECT | GROUP | TEACHER | GROUP | |||||||||
| Іванов | Фізика | Математика | А-99-51 | Іванов | А-98-51 | |||||||||
| Іванов | Математика | Математика | А-98-51 | Іванов | Б-00-51 | |||||||||
| Петров | Фізика | Фізика | А-98-51 | Петров | А-99-51 | |||||||||
| Петров | Математика | Фізика | Б-00-51 | Петров | А-98-51 | |||||||||
| ëСоедіненіе по Subjectû ¯ | ||||||||||||||
| TEACHER | SUBJECT | GROUP | ||||||||||||
| Іванов | Фізика | А-98-51 | ||||||||||||
| Іванов | Фізика | Б-00-51 | ||||||||||||
| Іванов | Математика | А-99-51 | ||||||||||||
| Іванов | Математика | А-98-51 | ||||||||||||
| Петров | Фізика | А-98-51 | ||||||||||||
| Петров | Фізика | Б-00-51 | ||||||||||||
| Петров | Математика | А-99-51 | ||||||||||||
| Петров | Математика | А-98-51 | ||||||||||||
| ëСоедіненіе по комбінації Teacher і Groupû ¯ | ||||||||||||||
| Початкове TSG | ||||||||||||||
Виходячи з цих висновків можна сказати, що пара (t1, s1) присутній у відношенні TS тоді і тільки тоді, коли трійка (t1, s1, g2) присутній у відношенні TSG для деякого значення g2. Тоді наведене вище твердження можна переписати у вигляді обмеження, що накладається на ставлення SPJ:
Якщо (t1, s1, g2), (t2, s1, g1), (t1, s2, g1) знаходяться у відношенні TSG то (t1, s1, g1) також знаходиться у відношенні TSG.
Якщо це твердження виконується завжди, тобто для всіх допустимих значень відносини TSG, то тим самим буде отримано незалежну від часу (хоча й трохи дивне) обмеження для даного відношення. Зверніть увагу на циклічну структуру цього обмеження. Ставлення буде n-декомпозіруемим для n> 2 тоді і тільки тоді, коли воно задовольняє деякому циклічного обмеження.
Циклічне обмеження з практичної точки зору означає, що, наприклад, якщо:
1. Петров викладає математику;
2. математика викладається в А-98-51;
3. Петров викладає в А-98-51
то:
4. Петров викладає математику в А-98-51.
Зверніть увагу, що з узятих разом умов (1), (2) і (3) не слід (4).
Нехай R є відношенням, а А, В ,..., Z-довільними підмножинами безлічі атрибутів відносини R. Відношення R задовольняє залежності з'єднання
* (A, B, ..., Z)
тоді і тільки тоді, коли воно рівносильне з'єднанню своїх проекцій з підмножинами атрибутів А, В, ..., Z.
Звідси ясно, що ставлення TSG із залежністю з'єднання * (TS, SG, TG) може бути 3-декомпозіруемим. Проте чи слід виконувати таку декомпозицію? По всій видимості, так, тому що відношення TSG характеризується численними аномаліями оновлення, які можна усунути за допомогою 3-декомпозиції. Приклад був приведений при визначенні циклічного обмеження, через наявність якого, щодо TSG повинен бути присутнім наступний кортеж (REF _Ref10021479 \ h \ * MERGEFORMAT рис. 7.5
| TEACHER | SUBJECT | GROUP |
| Петров | Математика | А-98-51 |
Також теорема Фейгіна може бути сформульована таким чином: відношення R {A, В, С} задовольняє залежності з'єднання * (АВ, АС) тоді і тільки тоді, коли воно задовольняє багатозначною залежно А ->> В | С.
Цю теорему можна використовувати в якості визначення багатозначною залежності, звідси випливає, що багатозначна залежність є окремим випадком залежності з'єднання. Більш того, з визначення залежності з'єднання слід, що з усіх можливих форм це найбільш загальна форма залежності.
Повертаючись до розглянутого прикладу, можна виявити наступну проблему: ставлення TSG містить залежність з'єднання, яка не є ні багатозначною, ні функціональною залежністю. Можна також зауважити, що рекомендується декомпозіровать таке ставлення на менші компоненти, а саме на проекції, задані залежністю з'єднання. Такий процес декомпозиції може повторюватися до тих пір, поки всі результуючі відносини не будуть знаходитися в п'ятій нормальній формі.
7.4 П'ята нормальна форма
Відношення R знаходиться в п'ятій нормальній формі (5НФ), яка також називається проекційно-сполучної нормальною формою, тоді і тільки тоді, коли кожна залежність з'єднання щодо R мається на увазі потенційними ключами відносини R.Ставлення TSG не знаходиться в 5НФ. Воно задовольняє деякій залежності з'єднання, а саме ЗД-обмеження, яке, звичайно, не мається на увазі його єдиним потенційним ключем. Навпаки, після 3-декомпозиції проекції TS, SG і GT знаходяться в 5НФ, оскільки для них зовсім немає залежностей з'єднання.
7.4.1 Залежності з'єднання, що мається на увазі потенційними ключами
Розглянемо простий приклад, в якому дано ставлення з даними студентів Students з потенційним ключем StNo. Таке ставлення задовольняє декільком залежностям з'єднання, наприклад залежності* ((StNo, GrNo, StName), (StNo, CityNo)).
Це означає, що ставлення Students рівносильно з'єднанню його проекцій з атрибутами {StNo, GrNo, StName} і {StNo, CityNo}, а тому може бути піддано декомпозиції без втрат на зазначені проекції. (Зауважте, що його не слід, а можна піддати декомпозиції.) Існування цієї залежності з'єднання слід (чи мається на увазі) з того, що StNo є потенційним ключем (насправді це випливає з теореми Хеза).
На закінчення зазначимо, що, як випливає з визначення 5НФ, вона є остаточною нормальною формою по відношенню до проекції і з'єднання. Таким чином, гарантується, що ставлення в п'ятій нормальній формі не містить аномалій, які можуть бути виключені розбиттям на проекції.
7.5 Підсумкова схема процедури нормалізації
Нехай дано відношення R, яке знаходиться в 1НФ (або може бути приведене до такої форми після вирівнювання вихідної ненормалізованном структури), разом з деякими обмеженнями (функціональними залежностями, багатозначними залежностями і залежностями з'єднання). Тоді основна ідея цієї технології полягає в систематичному приведення відношення R до набору менших відносин, який у певному заданому сенсі еквівалентний відношенню R, але більш кращий. Кожен етап процесу приведення складається з розбиття на проекції відносин, отриманих на попередньому етапі, таким чином, щоб проекції перебували в нормальній формі більш високого порядку, ніж первісне відношення.З наведених вище правил можна виділити деякі особливості.
1. Перш за все, процес розбиття на проекції на кожному етапі повинен бути виконаний без втрат і з збереженням залежності (там, де це можливо).
2. Необхідно підкреслити той факт, що можуть існувати міркування, за якими нормалізацію не слід виконувати повністю.
П'ята нормальна форма є остаточною в тому сенсі, що подальше усунення аномалій неможливо шляхом розбиття вихідного відносини на проекції. Існують нормальні форми більш високих порядків, однак вони вкрай рідко зустрічаються на практиці і в даному курсі не розглядаються.
Література:
1. Дейт К.Дж. Введення в системи баз даних. -Пер. з англ. -6-е вид. -К. Діалектика, 1998. Стор. 309-328.
ЛЕКЦІЯ 8. Проектування БД методом сутність-зв'язок. ER-діаграми
8.1 Виникнення семантичного моделювання8.2 Основні поняття методу
8.3 Діаграми ER-екземплярів і ER-типу
8.4 Правила формування відносин
8.5 Методологія IDEF1 (самостійне вивчення)
8.1 Виникнення семантичного моделювання
Широке поширення реляційних СУБД та їх використання в найрізноманітніших додатках показує, що реляційна модель даних достатня для моделювання предметних областей. Однак проектування реляційної бази даних в термінах відносин на основі механізму нормалізації часто представляє собою дуже складний і незручний для проектувальника процес. Потреби проектувальників баз даних в більш зручних і потужних засобах моделювання предметної області викликали до життя напрямок семантичних моделей даних. У цій лекції розглядається одна з популярних семантичних моделей даних - модель "сутність-зв'язок".Метод сутність-зв'язок називають також методом "ER-діаграм": по-перше, ER-абревіатура від слів Essence (сутність) і Relation (зв'язок), по-друге, метод заснований на використанні діаграм, званих відповідно діаграмами ER-екземплярів та діаграмами ER-типу.
8.2 Основні поняття методу
Основними поняттями методу сутність-зв'язок є наступні:1. сутність - представляє собою об'єкт, інформація про який зберігається в БД. Примірники сутності відрізняються один від одного і однозначно ідентифікуються. Назвами сутностей є, як правило, іменники, наприклад: ВИКЛАДАЧ, ДИСЦИПЛІНА, ГРУПА.
2. Атрибут сутності - представляє собою властивість сутності. Це поняття аналогічно поняттю атрибуту у відношенні. Так, атрибутами сутності ВИКЛАДАЧ може бути його Прізвище, Посада, Стаж (викладацький) і т. д.
3. Ключ сутності - атрибут або набір атрибутів, що використовується для ідентифікації примірника сутності. Як видно з визначення, поняття ключа суті аналогічно поняттю ключа відносини.;
4. Зв'язок між сутностями. Зв'язок двох або більше сутностей - припускає залежність між атрибутами цих сутностей. Назва зв'язку зазвичай представляється дієсловом. Прикладами зв'язків між сутностями є наступні-ВИКЛАДАЧ просунутий ДИСЦИПЛІНУ (Іванов ВЕДЕ "Організацію БД і знань"), ВИКЛАДАЧ Викладає в ГРУПІ (Іванов ВИКЛАДАЮТЬ У 256 групі);
5. Ступінь зв'язку - є характеристикою зв'язку між сутностями, яка може бути наступних видів: 1:1, 1: М, М: 1, М: М.;
6. Клас приналежності (КП) примірників сутності. КП суті може бути: обов'язковим і необов'язковим. Клас приналежності суті є обов'язковим, якщо всі екземпляри цієї сутності обов'язково беруть участь в даній зв'язку, в іншому разі клас приналежності суті є необов'язковим.
7. Діаграми ER-примірників;
8. Діаграми ER-типу.
Наведені визначення сутності і зв'язку не повністю формалізовані, але прийнятні для практики. Слід мати на увазі, що в результаті проектування можуть бути отримані декілька варіантів однієї і тієї ж БД. Так, два різних проектувальника, розглядаючи одну і ту ж проблему з різних точок зору, можуть отримати різні набори сутностей і зв'язків. При цьому обидва варіанти можуть бути робітниками, а вибір кращого з них буде результатом особистих переваг.
8.3 Діаграми ER-екземплярів і ER-типу
З метою підвищення наочності та зручності проектування для подання сутностей, примірників сутностей і зв'язків між ними використовуються наступні графічні засоби:1. діаграми ER-екзрмпляров,
2. діаграми ER-типу, або ER-діаграми.
На малюнку REF _Ref9933102 \ h \ * MERGEFORMAT рис. 8.1
|
Діаграма ER-екземплярів показує, яку конкретно дисципліну (СУБД, C + + і т.д.) веде кожен з викладачів. На REF _Ref9933147 \ h \ * MERGEFORMAT рис. 8.2
Викладач |
Веде |
На початковому етапі проектування БД виділяються атрибути, складові ключі сутностей.
На основі аналізу діаграм ER-типу формуються відносини проектованої БД. При цьому враховується ступінь зв'язку сутностей і клас їх належності, які, у свою чергу, визначаються на основі аналізу діаграм ER-примірників відповідних сутностей.
Варіюючи класом приналежності сутностей для кожного з названих типів зв'язку, можна отримати кілька варіантів діаграм ER-типу. Розглянемо приклади деяких з них.
8.3.1 Зв'язки типу 1:1 і необов'язковий клас приналежності
У наведеній на REF _Ref9933147 \ h \ * MERGEFORMAT рис. 8.21. кожен викладач веде не більше однієї дисципліни, а кожна дисципліна ведеться не більш ніж одним викладачем (ступінь зв'язку 1:1);
2. деякі викладачі не ведуть жодної дисципліни і є дисципліни, які не веде ні один з викладачів (клас приналежності обох сутностей необов'язковий).
8.3.2 Зв'язки типу 1:1 і обов'язковий клас приналежності
На малюнку наведено діаграми, у яких ступінь зв'язку між сутностями 1:1, а клас приналежності обох сутностей обов'язковий.
|
Викладач |
Дисципліна |
Веде |
1 |
1 |
У цьому випадку кожен викладач веде одну дисципліну і кожна дисципліна ведеться одним викладачем.
Можливі два проміжних варіанти з необов'язковим класом приналежності однієї з сутностей.
Діаграми ER-типу графічно зображуються наступним чином:
1. обов'язкова участь у зв'язку примірників сутності відзначається блоком з крапкою всередині, суміжних з блоком цієї сутності (REF _Ref10021805 \ h \ * MERGEFORMAT рис. 8.4
2. необов'язкове участь примірників сутності в зв'язку - додатковий блок до блоку суті не прилаштовується, а точка розміщується на лінії зв'язку (REF _Ref9933147 \ h \ * MERGEFORMAT рис. 8.2
3. символи на лінії зв'язку вказують на ступінь зв'язку.
4. під кожним блоком, відповідним деякої сутності, вказується її ключ, що виділяється підкресленням. Многоточие за ключовими атрибутами означає, що можливі інші атрибути сутності, але жоден з них не може бути частиною її ключа. Ці атрибути виявляються після формування відносин.
На практиці ступінь зв'язку і клас приналежності сутностей при проектуванні БД визначається специфікою предметної області. Розглянемо приклади варіантів зі ступенем зв'язку 1: М або М: 1.
Зв'язок типу 1: М - кожен викладач може вести кілька дисциплін, але кожна дисципліна ведеться одним викладачем,
Зв'язки типу М: 1 - кожен викладач може вести одну дисципліну, але кожну дисципліну можуть вести кілька викладачів.
Приклади з типом зв'язку 1: М або М: 1 можуть мати ряд варіантів, що відрізняються класом приналежності однієї або обох сутностей. Позначимо обов'язковий клас приналежності символом "О", а необов'язковий - символом "Н", тоді варіанти для зв'язку типу 1: М умовно можна представити як: О-О, О-Н, Н-О, Н-Н. Для зв'язку типу М: 1 також є 4 аналогічних варіанту.
8.3.3 Зв'язки типу 1: М варіант Н-О
Кожен викладач може вести кілька дисциплін АБО жодної, але кожна дисципліна ведеться одним викладачем (REF _Ref10021939 \ h \ * MERGEFORMAT рис. 8.5
|
Викладач |
Дисципліна |
Веде |
1 |
М |
За аналогією легко скласти діаграми і для інших варіантів.
Зв'язки типу М: М - кожен викладач може вести кілька дисциплін, а кожна дисципліна може вестися кількома викладачами. Як і у випадку інших типів зв'язків, для зв'язку типу М: М можливі 4 варіанти, що відрізняються класом приналежності сутностей.
8.3.4 Зв'язки типу М: М і варіант класу приналежності О-Н
Припустимо, що кожен викладач веде не менше однієї дисципліни, а дисципліна може вестися більш ніж одним викладачем, є і такі дисципліни, які ніхто не веде. Відповідні цієї нагоди діаграми приведені на малюнку REF _Ref9933507 \ h \ * MERGEFORMAT рис. 8.7
|
Викладач |
Дисципліна |
Веде |
М |
М |
Виявлення сутностей і зв'язків між ними, а також формування на їх основі діаграм ER-типу виконується на початкових етапах методу сутність-зв'язок. Розглянемо етапи реалізації методу.
Процес проектування бази даних є ітераційним - допускає повернення до попередніх етапів для перегляду раніше прийнятих рішень і включає наступні етапи:
1. виділення сутностей і зв'язків між ними;
2. побудова діаграм er-типу з урахуванням всіх сутностей та їх зв'язків;
3. формування на основі побудованих раніше діаграм er-типу набору попередніх відносин з зазначенням передбачуваного первинного ключа для кожного відносини;
4. додавання не ключових атрибутів у відносини;
5. приведення попередніх відносин до нормальної форми Бойса-Кодда, наприклад, за допомогою методу нормальних форм;
6. перегляд er-діаграм в наступних випадках;
6.1. деякі відношення не приводяться до нормальної формі Бойса-Кодда;
6.2. деяким атрибутам не знаходиться логічно обгрунтованих, місць у попередніх відносинах.
Після перетворення ER-діаграм здійснюється повторне виконання попередніх етапів проектування (повернення до етапу 1).
Одним з вузлових етапів проектування є етап формування відносин. Розглянемо процес формування попередніх відносин, що складають первинний варіант схеми БД.
У розглянутих вище прикладах зв'язок ВЕДЕ завжди з'єднує дві сутності і тому є бінарною. Сформульовані нижче правила формування відносин з діаграм ER-типу поширюються саме на бінарні зв'язку. Тому, коли мова йде про зв'язки, слово "бінарні" далі опускається.
8.4 Правила формування відносин
Правила формування відносин грунтуються на обліку наступного:1. ступеня зв'язку між сутностями (1:1, 1: М, М: 1, М: М);
2. класу приналежності екземплярів сутностей (обов'язковий та необов'язковий).
Розглянемо формулювання шести правил формування відносин на основі діаграм ER-типу.
8.4.1 Ступінь зв'язку 1:1, клас приналежності обох сутностей обов'язковий
Якщо ступінь бінарної зв'язку 1:1 і клас приналежності обох сутностей обов'язковий, то формується одне відношення. Первинним ключем цього відношення може бути ключ будь-який з двох сутностей.На REF _Ref9933850 \ h \ * MERGEFORMAT рис. 8.9
C1 |
C2 |
1 |
1 |
R1 |
K1 або K2, ... |
K1, ... |
K2, ... |
На REF _Ref9933850 \ h \ * MERGEFORMAT рис. 8.9
Cl, C2 - сутності 1 і 2;
Kl, K2 - ключі першої та другої сутності відповідно;
Rl - відношення 1, сформоване на основі першої і другої сутностей;
Kl, K2, ... означає, що ключем сформованого відношення може бути або К1, або К2.
8.4.2 Ступінь зв'язку 1:1, клас приналежності однієї сутності обов'язковий, а другий - необов'язковий
Якщо ступінь зв'язку 1:1 і клас приналежності однієї сутності обов'язковий, а другий - необов'язковий, то під кожну з сутностей формується по відношенню з первинними ключами, які є ключами відповідних сутностей. Далі до відношення, сутність якого має обов'язковий КП, додається як атрибуту ключ сутності з необов'язковим КП.На REF _Ref9933887 \ h \ * MERGEFORMAT рис. 8.10
C1 |
C2 |
1 |
1 |
K1, ... |
K2, ... |
R1 |
K1, K2 |
R2 |
K2, ... |
8.4.3 Ступінь зв'язку 1:1, клас приналежності обох сутностей - необов'язковий
Якщо ступінь зв'язку 1:1 і клас приналежності обох сутностей є необов'язковим, то необхідно використовувати три відносини. Два відносини відповідають пов'язують сутність, ключі яких є первинними в цих відносинах. Третє відношення є зв'язковим між першими двома, тому його ключ об'єднує ключові атрибути пов'язують відносин. C1 |
C2 |
1 |
1 |
K1, ... |
K2, ... |
R1 |
K1, ... |
R2 |
R1_R2 |
K1, K2 |
K2, ... |
На REF _Ref9933945 \ h \ * MERGEFORMAT рис. 8.11
Сформулюємо аналогічні два правила для варіантів, ступінь зв'язку між сутностями яких 1: М. Якщо дві сутності С1 і С2 пов'язані як 1: М, сутність С1 будемо називати однозв''язної (1-зв'язкової), а сутність С2-многосвязной (М-зв'язкової). Визначальним фактором при формуванні відносин, пов'язаних цим видом зв'язку, є клас приналежності М-зв'язковий сутності. Так, якщо клас приналежності М-зв'язковий сутності обов'язковий, то в результаті застосування правила отримаємо два відносини, якщо необов'язковий - три відносини. Клас приналежності однозв''язної суті не впливає на результат.
8.4.4 Ступінь зв'язку між сутностями 1: М (або М: 1), клас приналежності М-зв'язковий сутності обов'язковий
Якщо ступінь зв'язку між сутностями 1: М (або М: 1) і клас приналежності М-зв'язковий сутності обов'язковий, то досить формування двох відносин (по одному на кожну з сутностей). При цьому первинними ключами цих відносин є ключі їхніх сутностей. Крім того, ключ 1-зв'язковий сутності додається як атрибут (зовнішній ключ) у відношення, відповідне М-зв'язковий сутності.На REF _Ref9933977 \ h \ * MERGEFORMAT рис. 8.12
C1 |
C2 |
1 |
М |
K1, ... |
K2, ... |
K1, ... |
R1 |
R2 |
K1, K2, ... |
8.4.5 Ступінь зв'язку 1: М (М: 1) і клас приналежності М-зв'язковий сутності - необов'язковий
Якщо ступінь зв'язку 1: М (М: 1) і клас приналежності М-зв'язковий суті є необов'язковим, то необхідно формування трьох відносин (REF _Ref9934021 \ h \ * MERGEFORMAT рис. 8.13 R1 |
K1, ... |
R2 |
R1_R2 |
K1, K2 |
K2, ... |
C1 |
C2 |
1 |
М |
K1, ... |
K2, ... |
рис. STYLEREF 1 \ s 8. SEQ рис. \ * ARABIC \ s 13 січня Діаграма і відношення для правила REF _Ref45419075 \ r \ h \ * MERGEFORMAT 8.4.5
Два відносини відповідають пов'язують сутність, ключі яких є первинними в цих відносинах. Третє відношення є зв'язковим між першими двома (його ключ об'єднує ключові атрибути пов'язують відносин).
За наявності зв'язку М: М між двома сутностями необхідно три відносини незалежно від класу приналежності будь-який з сутностей. Використання одного або двох відносин у цьому випадку не рятує від порожніх полів або надлишково дубльованих даних.
8.4.6 Ступінь зв'язку М: М, незалежно від класу приналежності сутностей
Якщо ступінь зв'язку М: М, то незалежно від класу приналежності сутностей формуються три відносини Два відносини відповідають пов'язують сутність і їх ключі є первинними ключами цих відносин. Третє відношення є зв'язковим між першими двома, а його ключ об'єднує ключові атрибути пов'язують відносин.На REF _Ref9934078 \ h \ * MERGEFORMAT рис. 8.14
R1 |
K1, ... |
R2 |
R1_R2 |
K1, K2 |
K2, ... |
C1 |
C2 |
М |
М |
K1, ... |
K2, ... |
Аналогічні результати виходять і для трьох інших варіантів, що розрізняються класами приналежності їхніх сутностей.
8.5 Методологія IDEF1 (самостійне вивчення)
Метод IDEF1, розроблений Т. Ремей (T. Ramey), також заснований на підході П. Чена і дозволяє побудувати модель даних, еквівалентну реляційної моделі в третій нормальній формі. В даний час на основі вдосконалення методології IDEF1 створена її нова версія - методологія IDEF1X. IDEF1X розроблена з урахуванням таких вимог, як простота вивчення і можливість автоматизації. IDEF1X-діаграми використовуються поруч поширених CASE-засобів (таких, як, ERwin, Design / IDEF).Сутність в методології IDEF1X є незалежною від ідентифікаторів або просто незалежною, якщо кожен екземпляр сутності може бути однозначно ідентифікований без визначення його відносин з іншими сутностями (REF _Ref45508910 \ h \ * MERGEFORMAT рис. 8.15
Ім'я сутності / Номер сутності |
Студент/44 |
Ім'я сутності / Номер сутності |
Оценка/56 |
Кожній сутності присвоюється унікальне ім'я та номер, розділяються косою рисою "/" і поміщаються над блоком.
Зв'язок може додатково визначатися за допомогою вказівки ступеня або потужності (кількості примірників сутності-нащадка, яке може існувати для кожного екземпляра сутності-предка). У IDEF1X можуть бути виражені такі потужності зв'язків:
5. кожен примірник сутності-предка може мати нуль, один або більше пов'язаних з ним примірників сутності-нащадка;
6. кожен примірник сутності-предка повинен мати не менше одного пов'язаного з нею примірника сутності-нащадка;
7. кожен примірник сутності-предка повинен мати не більше одного пов'язаного з нею примірника сутності-нащадка;
8. кожен примірник сутності-предка пов'язаний з деяким фіксованим числом екземплярів сутності-нащадка.
Якщо примірник сутності-нащадка однозначно визначається своїм зв'язком з сутністю-батьком, то зв'язок називається ідентифікуючої, в іншому випадку - неидентифицирующей.
Зв'язок зображується лінією, проведеної між сутністю-батьком і сутністю-нащадком з точкою на кінці лінії у сутності-нащадка. Потужність зв'язку позначається як показано на REF _Ref45509272 \ h \ * MERGEFORMAT рис. 8.17
Нуль або один |
Z |
Один або більше |
P |
Нуль, один або більше |
N |
рис. STYLEREF 1 \ s 8. SEQ рис. \ * ARABIC \ s 1 17. Потужність зв'язку.
Идентифицирующая зв'язок між сутністю-батьком і сутністю-нащадком зображується суцільною лінією (REF _Ref45509877 \ h \ * MERGEFORMAT рис. 8.18
Ключовий-атрибут-A |
Сутність-A / 1 |
Ключовий-атрибут-A (FK) Ключовий-атрибут-B |
Сутність-B / 2 |
Сутність-батько |
Сутність-нащадок |
Ім'я зв'язку |
Пунктирна лінія зображує неидентифицирующей зв'язок (REF _Ref45509971 \ h \ * MERGEFORMAT рис. 8.19
Ключовий-атрибут-A |
Сутність-A / 1 |
Атрибут-A (FK) |
Ключовий-атрибут-B |
Сутність-B / 2 |
Сутність-батько |
Сутність-нащадок |
Ім'я зв'язку |
Атрибути зображаються у вигляді списку імен усередині блоку сутності. Атрибути, що визначають первинний ключ, розміщуються вгорі списку і відділяються від інших атрибутів горизонтальній рисою (REF _Ref45510609 \ h \ * MERGEFORMAT рис. 8.20
ім'я-атрибуту [Ім'я-атрибуту] ... |
[Ім'я-атрибуту] [Ім'я-атрибуту] [Ім'я-атрибуту] ... |
Атрибути первинного ключа |
Імя_сущності / номер_сущності |
Сутності можуть мати також зовнішні ключі (Foreign Key), які можуть використовуватися в якості частини або цілого первинного ключа або неключових атрибута. Зовнішній ключ зображується за допомогою приміщення всередину блоку сутності імен атрибутів, після яких слідують букви FK в дужках (REF _Ref45510690 \ h \ * MERGEFORMAT рис. 8.21
Код_Студента |
Код_группи (FK) |
Студент/44 |
Код_Студента (FK) Код_дісціпліни (FK) Код_документа |
Оценка/44 |
Література:
1. Бази даних: Підручник для вищих навчальних закладів / За ред. проф. А.Д. Хомоненко. -Спб.: КОРОНА принт, 2000. -416с. Стор. 147-161.
2. Сергій Кузнєцов, "Основи сучасних баз даних". Центр Інформаційних Технологій, http://www.citforum.ru/database/osbd/contents.shtml
ЛЕКЦІЯ 9. Мова SQL
9.1 Історія створення та розвитку SQL9.2 Основні поняття SQL
9.3 Запити на читання даних. Оператор SELECT
9.4 багатотабличних запити на читання (об'єднання).
9.1 Історія створення та розвитку SQL
Мова для взаємодії з БД SQL з'явився в середині 70-х і був розроблений в рамках проекту експериментальної реляційної СУБД System R. Початкове назву мови SEQUEL (Structered English Query Language) тільки частково відображає суть цієї мови. Звичайно, мова був орієнтований головним чином на зручну і зрозумілу користувачам формулювання запитів до реляційної БД, але насправді вже був повним мовою БД, що містить крім операторів формулювання запитів і маніпулювання БД засоби визначення та маніпулювання схемою БД; визначення обмежень цілісності і тригерів; уявлень БД; можливості визначення структур фізичного рівня, що підтримують ефективне виконання запитів; авторизації доступу до відносин і їхніх полів; точок збереження транзакції і відкатів. Таким чином, SQL став досить потужним мовою для взаємодії з СУБД. На сьогоднішній день SQL є єдиним стандартною мовою запитів. Мова SQL володіє наступними перевагами:1. незалежність від конкретних СУБД. Якщо при створенні БД не використовувалися нестандартні можливості мови SQL надаються деякої СУБД, то таку БД можна без змін перенести на СУБД іншого виробника. На жаль більшість БД використовують особливості СУБД, на якій працюють, що ускладнює їх перенесення на іншу СУБД без змін;
2. реляційна основа. Реляційна модель має солідний теоретичний фундамент. Мова SQL заснований на реляційної моделі і є єдиною мовою для реляційних БД;
3. SQL має високорівневої структурою, що нагадує англійську мову.
4. SQL дозволяє створювати різні подання даних для різних користувачів;
5. SQL є повноцінною мовою для роботи з БД;
6. стандарти мови SQL. Офіційний стандарт мови SQL опублікований ANSI та ISO в 1989 році і значно розширений у 1992 році.
9.2 Основні поняття SQL
9.2.1 Оператори
У SQL використовується приблизно тридцять операторів, кожен з яких "просить" СУБД виконати певну дію, наприклад, прочитати дані, створити таблицю або додати в таблицю нові дані. Всі оператори SQL мають однакову структуру, яка показана на REF _Ref9934366 \ h \ * MERGEFORMAT рис. 9.1рис. STYLEREF 1 \ s 9. SEQ рис. \ * ARABIC \ s 1 січня
Дієслово |
Ім'я таблиці |
Ім'я стовпця |
Константа |
Ключові слова |
Пропозиції |
Кожен оператор SQL починається з дієслова, тобто ключового слова, що описує дію, що виконується оператором. Типовими дієсловами є SELECT (вибрати), CREATE (створити), INSERT (додати), DELETE (видалити), COMMIT (завершити). Після дієслова йде одне або декілька пропозицій. Пропозиція описує дані, з якими працює оператор, або містить уточнюючу інформацію про дію, що виконується оператором. Кожна пропозиція також починається з ключового слова, такого як WHERE (де), FROM (звідки), INTO (куди) і HAVING (має). Одні пропозиції в операторі є обов'язковим, а інші - ні. Конкретна структура і вміст пропозиції можуть змінюватися. Багато пропозиції містять імена таблиць або стовпців; деякі з них можуть містити додаткові ключові слова, константи та вирази.
У стандарті ANSI / ISO визначені ключові слова, які застосовуються в якості дієслів і в пропозиціях операторів. У відповідності зі стандартом, ці ключові слова не можна використовувати для іменування об'єктів бази даних, таких як таблиці, стовпці та користувачі
9.2.2 Імена.
У кожного об'єкта в базі даних є унікальне ім'я. Імена використовуються в операторах SQL і вказують, над яким об'єктом бази даних оператор повинен виконати дію. У стандарті ANSI / ISO визначено, що імена є в таблиць, стовпців і користувачів. У багатьох реалізаціях SQL підтримуються також додаткові іменовані об'єкти, такі як збережені процедури, іменовані відносини "первинний ключ - зовнішній ключ" та форми для введення даних.У відповідності зі стандартом ANSI / ISO, в SQL імена повинні містити від 1 до 18 символів, починатися з літери та не містити пробіли або спеціальні символи пунктуації. У стандарті SQL2 максимальне число символів в імені збільшено до 128.
Повне ім'я таблиці складається з імені власника таблиці та власне її імені, розділених крапкою (.). Наприклад, повне ім'я таблиці Students, власником якої є користувач на ім'я Admin, має наступний вигляд:
Admin.Students
Якщо в операторі задається ім'я стовпця, SQL сам визначає, в якій із зазначених у цьому ж операторі таблиць міститься даний стовпець. Однак якщо в оператор потрібно включити два стовпці з різних таблиць, але з однаковими іменами, необхідно вказати повні імена стовпців, які однозначно визначають їх місцезнаходження. Повне ім'я стовпця складається з імені таблиці, яка містить стовпець, й імені стовпця (простого імені), розділених крапкою (.). Наприклад, повне ім'я стовпця StName з таблиці Students має наступний вигляд:
Students.StName
9.2.3 Типи даних в SQL
У стандарті ANSI / ISO визначені типи даних, які можна використовувати для представлення інформації в реляційної базі даних. Типи даних, наявні в стандарті SQL1, становлять лише мінімальний набір і підтримуються у всіх комерційних СУБД. У REF _Ref10087105 \ h \ * MERGEFORMAT табл. 9.1табл. STYLEREF 1 \ s 9. SEQ табл. \ * ARABIC \ s 1 січня Типи даних в SQL.
| Тип даних | Опис |
| CHAR (довжина) | Рядки символів постійної довжини |
| CHARACTER (довжина) | |
| VARCHAR (довжина) | Рядки символів змінної довжини * |
| CHAR VARYING (довжина) | |
| CHARACTER VARYING (довжина) | |
| NСНАР (довжина) | Рядки локалізованих символів постійної довжини * |
| NATIONAL CHAR (довжина) | |
| NATIONAL CHARACTER (довжина) | |
| NCHAR VARYING (довжина) | Рядки локалізованих символів змінної довжини * |
| NATIONAL CHAR VARYING (довжина) | |
| NATIONAL CHARACTER VARYING (довжина) | |
| INTEGER | Цілі числа |
| INT | |
| SMALLINT | Маленькі цілі числа |
| BIT (довжина) | Рядки бітів постійної довжини * |
| BIT VARYNG (довжина) | Рядки бітів змінної довжини * |
| NUMERIC (точність, ступінь) | Масштабовані цілі (десяткові) числа |
| DECIMAL (точність, ступінь) | |
| DEC (точність, ступінь) | |
| FLOAT (точність) | Числа з плаваючою комою |
| REAL | Числа з плаваючою комою низької точності |
| DOUBLE PRECISION | Числа з плаваючою комою високої точності |
| DATE | Календарна дата * |
| TIME (точність) | Час |
| TIME STAMP (точність) | Дата і час * |
| INTERVAL | Часовий інтервал * |
1. Рядки символів постійної довжини. У стовпцях, що мають цей тип даних, зазвичай зберігаються імена людей і компаній, адреси, описи і т д.
2. Цілі числа. У стовпцях, що мають цей тип даних, зазвичай зберігаються дані про рахунки, кількостях, віках і т.д. Цілочисельні стовпці часто використовуються також для зберігання ідентифікаторів, таких як ідентифікатор клієнта, службовця або замовлення.
3. Масштабовані цілі числа. У стовпцях даного типу зберігаються числа,) мають дробову частину, які необхідно обчислювати точно, наприклад курси валют і відсотки. Крім того, в таких стовпцях часто зберігаються) грошові величини.
4. Числа з плаваючою комою. Стовпці цього типу використовуються для зберігання величин, які можна обчислювати приблизно, наприклад ваги і відстані. Числа з плаваючою комою можуть представляти більший діапазон значень, ніж десяткові числа, однак при обчисленнях можуть виникати похибки округлення.
У більшості комерційних СУБД крім типів даних, визначених у стандарті SQL1, є безліч додаткових типів даних, більшість з яких увійшло в стандарт SQL2. Нижче перераховані найбільш важливі з них:
1. Рядки символів змінної довжини. Майже у всіх СУБД підтримується тип даних VARCHAR, що дозволяє зберігати рядка символів, довжина яких змінюється в деякому діапазоні В стандарті SQL1 були визначені рядка постійної довжини, які праворуч доповнюються пробілами.
2. Грошові величини. У багатьох СУБД підтримується тип даних MONEY або CURRENCY, який зазвичай зберігається у вигляді десяткового числа або числа з плаваючою комою. Наявність окремого типу даних для представлення грошових величин дозволяє правильно форматувати їх при виведенні на екран.
3. Дата і час. Підтримка значень дати / часу також широко поширена в різних СУБД, хоча способи її реалізації досить сильно відрізняються один від одного. Як правило, над значеннями цього типу даних можна виконувати різні операції. У стандарт SQL2 входить визначення типів даних DATE, TIME, TIMESTAMP і INTERVAL, включаючи підтримку часових поясів і можливість вказівки точності, представлення часу (наприклад, десяті або соті частки секунди).
4. Булеві дані. Деякі СУБД явним чином підтримують логічні значення (TRUE або FALSE).
9.2.4 Константи
У стандарті ANSI / ISO визначено формат числових і рядкових констант, або літералів, які представляють конкретні значення даних. Цей формат використовується в більшості реалізації SQL.Числові константи. Цілі і десяткові константи (відомі також під назвою точних числових літералів) в операторах SQL представляються у вигляді звичайних. десяткових чисел з необов'язковим знаком плюс (+) або мінус (-) перед ними:
21 -3752000,00 +497500,8778
Константи з плаваючою комою (відомі також під назвою приблизних числових літералів) визначаються за допомогою символу Е і мають однаковий формат, як і в більшості мов програмування. Нижче наведені приклади констант з плаваючою комою:
1.5Е3-3.14159Е1 2.5Е-7 0.783926Е21
Символ Е читається як "помножити на десять в ступені", так що перша константа представляє число "1,5 помножити на десять в ступені 3", або 1500.
Строкові константи. У відповідності зі стандартом ANSI / ISO, рядкові константи в SQL повинні бути укладені в одинарні лапки, як показано в наступних прикладах:
Jones, John J. ' 'New York' 'Western'
Якщо необхідно включити в строкову константу одинарну лапку, замість неї слід написати дві одинарні лапки.
Константи дати і часу. У реляційних СУБД календарні дати, час та інтервали часу представляються у вигляді рядкових констант. Формати цих констант у різних СУБД відрізняються один від одного. Крім того, способи запису часу і дати змінюються в залежності від країни.
Символьні константи. Крім користувальницьких констант, в SQL існують спеціальні символьні константи, які повертають значення, що зберігаються в самій СУБД.
У стандарт SQL2 увійшли найбільш корисні символьні константи з різних реалізації SQL, зокрема константи CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, а також USER, SESSION_USER і SYSTEM_USER (зверніть увагу на знак підкреслення!).
Вирази в SQL використовуються для виконання операцій над значеннями, ліченими з бази даних або використовуваними для пошуку в базі. даних. Наприклад, в наступному запиті обчислюється відсоткове співвідношення обсягу та плану продажу для кожного офісу:
SELECT CITY, TARGET, SALES, (SALES / TARGET) * 100
FROM OFFICES
У відповідності зі стандартом ANSI / ISO, у виразах можна використовувати чотири арифметичні операції: складання (X + Y), віднімання (X - Y), множення (X * Y) і ділення (X / Y). Для формування складних виразів можна використовувати дужки.
Відсутні дані (значення NULL). Оскільки база даних представляє собою модель реального світу, окремі елементи даних в ній неминуче будуть відсутні або підходити не для всіх сутностей. Для вказівки на такі дані іспользуеBӦся спеціальна константа - NULL.
Вбудовані функції
У стандарт SQL2 увійшли найбільш корисні функції з різних реалізації SQL. Ці функції перераховані в таблце.
табл. STYLEREF 1 \ s 9. SEQ табл. \ * ARABIC \ s 1 лютого Вбудовані функції SQL.
| Функція | Повертається |
| ВIT LENGTH (рядок) | кількість бітів у рядку |
| САSТ (значення AS тип даних) | значення, перетворене тип даних (наприклад, дата перетворена в рядок) |
| CHAR_LENGTH (рядок) | довжина рядка символів |
| CONVERT (рядок USING функція) | рядок, перетворена у відповідності з зазначеною функцією |
| CURRENT_DATE | поточна дата |
| CURRENT_TIME (точність) | поточний час з вказаною точністю |
| CURRENT_IMESTAMP (точність) | поточні дата і час з зазначеної точністю |
| EXTRACT (частина FROM значення) | зазначена частина (DAY, HOUR і т.д.) із значення типу DATETIME |
| LOWER (рядок) | рядок, перетворена до нижнього регістру |
| OCTETLENGTH (рядок) | кількість байтів у рядку символів |
| POSITION (перший рядок IN другий рядок) | позиція, з якої починається входження першого рядка у другий рядок |
| SUBSTRING (рядок FROM n FOR довжина) | частина рядка, що починається з n-го символу і має зазначену довжину |
| TRANSLATE (рядок USING функція) | рядок, перетворена з допомогою вказаної функції |
| TRIM (BOTH символ FROM рядок) | рядок, в якої вилучені перші і останні зазначені символи |
| TRIM (LEADING символ FROM рядок) | рядок, в якої вилучені першого зазначені символи |
| TRIM (TRAILING символ FROM рядок) | рядок, в якої вилучені останні зазначені символи |
| UPPER (рядок) | рядок, перетворена до верхнього регістру |
9.3 Запити на читання даних. Оператор SELECT
Оператор SELECT застосовується для побудови вибірок даних і має такий синтаксис:SELECT [ALL | DISTINCT] возвращаемий_столбец, ... | *
FROM спеціфікатор_табліци, ...
WHERE условіе_поіска
GROUP BY ім'я_стовпця, ...
HAVING условіе_поіска
ORDER BY спеціфікатор_сортіровкі, ...
9.3.1 Пропозиція SELECT
У пропозиції SELECT, з якого починаються всі оператори SELECT, необхідно вказати елементи даних, які будуть повернуті в результаті запиту Ці елементи задаються у вигляді списку повертаються стовпців, розділених комами Для кожного елемента з цього списку в таблиці результатів запиту буде створений один стовпець Стовпці в таблиці результатів будуть розташовані в тому ж порядку, що й елементи списку повертаються стовпців повертається стовпець може представляти собою:1. ім'я стовпця, ідентифікує один з стовпців, що містяться в таблицях, які перераховані в реченні FROM Коли в якості повертається стовпця вказується ім'я стовпця таблиці бази даних SQL просто бере значення цього стовпця для кожного з рядків таблицю і поміщає його в відповідний рядок таблиці результатів запиту;
2. константа, що показує, що в кожному рядку результатів запиту має міститися одне і те ж значення,
3. вираз, що показує, що SQL повинен обчислювати значення, що поміщається в результати запиту, за формулою, визначеною у виразі.
9.3.2 Пропозиція FROM
Пропозиція FROM складається з ключового слова FROM, за яким слідує список специфікатором таблиць, розділених комами Кожен специфікатор таблиці ідентифікує таблицю, яка містить дані, які зчитує запит Такі таблиці називаються вихідними таблицями запиту (і оператора SELECT), оскільки всі дані, що містяться в таблиці результатів запиту , беруться з них.Результатом SQL-запиту на читання завжди є таблиця, яка містить дані і нічим не відрізняється від таблиць бази даних
Крім стовпців, значення яких зчитуються безпосередньо з бази даних, SQL-запит на читання може містити обчислювані стовпці, значення яких визначаються на підставі значень, що зберігаються в базі даних. Щоб отримати обчислюваний стовпець, в списку повертаються стовпців необхідно вказати вираз.
Іноді потрібно отримати вміст всіх стовпців таблиці. На практиці така ситуація може виникнути, коли ви вперше стикаєтеся з новою базою даних і необхідно швидко отримати уявлення про її структуру і збережених у ній даних. З урахуванням цього в SQL дозволяється використовувати замість списку. Повертаються стовпців символ зірочки (*), який означає, що потрібно прочитати всі стовпці:
Показати всі дані, що містяться в таблиці Students.
SELECT *
FROM Students
Повторювані рядки з таблиці результатів запиту можна видалити, якщо в операторі SELECT перед списком повертаються стовпців вказати ключове слово DISTINCT.
9.3.3 Відбір рядків (пропозиція WHERE)
SQL-запити, зчитувальні з таблиці всі рядки, корисні при перегляді бази даних і створення звітів, однак рідко застосовуються для чого-небудь ще Звичайно потрібно вибрати з таблиці кілька рядків і включити в результати запиту лише їх Щоб вказати, які рядки потрібно відібрати, слід використати пропозицію WHERE.Пропозиція WHERE складається з ключового слова WHERE, за яким слід умова пошуку, що визначає, які саме рядки потрібно прочитати.
Якщо умова пошуку має значення TRUE, рядок буде включена в результати запиту.
Якщо умова пошуку має значення FALSE або NULL, то рядок виключається з результатів запиту.
9.3.4 Умови пошуку
У SQL використовується безліч умов пошуку, які дозволяють ефективно і природним чином створювати різні типи запитів. Нижче розглядаються п'ять основних умов пошуку (у стандарті ANSI / ISO вони називаються предикатами).Порівняння (=, <>, <, <=,>,> =). Найбільш поширеним умовою пошуку в SQL є порівняння. При порівнянні SQL обчислює і порівнює значення двох виразів для кожного рядка даних. Вирази можуть бути як дуже простими, наприклад містити одне ім'я стовпця або константу, так і більш складними - арифметичними виразами.
Нижче наведено синтаксис оператора порівняння.
Вираз1 = | <> | <| <= |> |> = Вираженіє2
Перевірка на належність діапазону значень (BETWEEN). Іншою формою умови пошуку є перевірка на приналежність діапазону значень (ключове слово BETWEEN). При цьому перевіряється, чи знаходиться значення даних між двома певними значеннями.
Проверяемое_вираженіе [NOT] BETWEEN ніжнее_вираженіе AND верхнее_вираженіе
При перевірці на належність діапазону верхній і нижній межі вважаються частиною діапазону, тому в результати запиту.
Якщо вираз, що визначає нижню межу діапазону, має значення NULL, то перевірка BETWEEN повертає значення FALSE тоді, коли перевіряється значення більше верхньої межі діапазону, і значення NULL в іншому випадку. |
Якщо вираз, що визначає верхню межу діапазону, має значення NULL, то перевірка BETWEEN повертає значення FALSE, коли перевіряється значення менше нижньої межі, і значення NULL іншому випадку.
Повірка на членство в множині (IN). Ще одним поширеним умовою пошуку є перевірка на членство в множині (IN). У цьому випадку перевіряється, чи відповідає значення даних якому-небудь значенням із заданого списку.
проверяемое_вираженіе [NOT] IN (константа, ...)
Наприклад: вивести список прізвищ студентів, які навчаються в групах з кодами 1, 3, 5 і 10.
SELECT StName
FROM Students
WHERE GrNo IN (1, 3, 5, 10)
Перевірка на відповідність шаблону (LIKE). Для читання рядків, в яких вміст деякого текстового стовпця збігається з заданим текстом, можна використовувати просте порівняння, однак іноді необхідно здійснити порівняння на основі фрагмента рядка.
Повірка на відповідність шаблону (ключове слово LIKE), дозволяє визначити, чи відповідає значення даних у стовпці деякого шаблоном. Шаблон являє собою рядок, в яку може входити один або більше символів узагальнення. Ці знаки інтерпретуються особливим чином.
ім'я_стовпця [NOT] LIKE шаблон [ESCAPE сімвол_пропуска]
Символи узагальнення. Підстановки знак% збігається з будь-якою послідовністю з нуля або більше символів. Наприклад, наступний запит виведе інформацію про студентів, чиє прізвище починається з "Іван":
SELECT *
FROM StudentsWHERE StName LIKE 'Іван%'
Підстановки знак "_" (символ підкреслення) збігається з будь-яким окремим символом. Наприклад, якщо ви впевнені, що ім'я студентки-небудь "Наталія", або "Наталія", то можете скористатися наступним запитом:
SELECT *
FROM Students WHERE StName LIKE 'Натал_я'
Символи узагальнення можна поміщати в будь-яке місце рядка шаблону, і в одному рядку може міститися декілька символів узагальнення.
Символи пропуску. При перевірці рядків на відповідність шаблону може виявитися, що символи узагальнення входять до рядка символів в якості літералів. Наприклад, не можна перевірити, чи міститься знак відсотка в рядку, просто включивши їх у шаблон, оскільки SQL буде вважати цей знак підстановки. Як правило, це не викликає серйозних проблем, оскільки символи узагальнення досить рідко зустрічаються в іменах, назвах товарів та інших текстових даних, які зазвичай зберігаються в базі даних.
У стандарті ANSI / ISO визначено спосіб перевірки наявності в рядках літералів, що використовуються як символів узагальнення. Для цьому застосовуються символи пропуску. Коли в шаблоні зустрічається такий символ то символ, наступний безпосередньо за ним, вважається не символів узагальнення, а літералом. (Відбувається пропуск символу.) Безпосередньо за символом пропуску може слідувати або один з двох символів узагальнення, або сам символ пропуску, оскільки він теж набуває в шаблоні особливе значення.
Символ пропуску визначається у вигляді рядка, що складається з одного символу, і пропозиції ESCAPE. Нижче наведено приклад використання знака долара ($) в якості символу пропуску:
SELECT *
FROM DataTable WHERE Text LIKE '% менше 50 $%%' ESCAPE $
Перевірка на рівність значенню NULL (IS NULL). Значення NULL забезпечують можливість застосування тризначної логіки в умовах пошуку. Для будь-якої заданої рядка результат застосування умови пошуку може бути TRUE, FALSE або NULL (у разі, коли в одному з стовпців міститься значення NULL). Іноді буває необхідно явно перевіряти значення стовпців на рівність NULL і безпосередньо обробляти їх. Для цього в SQL є спеціальна перевірка на рівність значенню NULL (IS NULL).
IS [NOT] NULL імя_ стовпця
Складові умови пошуку (AND, OR і NOT). Прості умови пошуку, описані в попередніх параграфах, після застосування до деякої рядку повертають значення TRUE, FALSE або NULL. За допомогою правил логіки ці прості умови можна об'єднувати у більш складні. Умови пошуку, об'єднані за допомогою ключових слів AND, OR і NOT, самі можуть бути складовими.
WHERE [NOT] условіе_поска [AND | OR] [NOT] условіе_поска ...
9.3.5 Сортування результатів запиту (пропозиція ORDER BY).
Рядки результатів запиту, як і рядки таблиці бази даних, не мають певного порядку. Включивши в оператор SELECT пропозицію ORDER BY, можна відсортувати результати запиту. Це пропозиція складається з ключових слів ORDER BY, за якими слідує список імен стовпців, розділених комами.ORDER BY ім'я_стовпця [ASC | DESC], ...
У пропозиції ORDER BY можна вибрати зростаючий або спадаючий порядок сортування. За замовчуванням, дані сортуються в порядку зростання. Щоб сортувати їх за спаданням, слід включити в пропозицію сортування ключове слово DESC.
Наприклад: вивести список прізвищ студентів учнів у групі з кодом 1 у зворотному алфавітному порядку.
SELECT StName
FROM Students ORDER BY DESC StName
9.4 багатотабличних запити на читання (об'єднання).
На практиці багато запити зчитують дані відразу з декількох таблиць бази даних.9.4.1 Запити з використанням відносини предок / нащадок.
Серед багатотабличних запитів найбільш поширені запити до двох таблиць, пов'язаних за допомогою відношення предок / нащадок. Запит про студентів, які навчаються у групі з деяким назвою є прикладом такого запиту. У кожного студента (нащадка) є відповідна йому група (предок), і кожна група (предок) може мати багато студентів (нащадків). Пари рядків, з яких формуються результати запиту, пов'язані ставленням предок / нащадок.Наприклад: вивести список прізвищ студентів з назвою групи, в якій він навчається
SELECT StName, GrName
FROM Students, Groups
WHERE Students.GrNo = Groups.GrNo
9.4.2 Інші об'єднання таблиць з рівності
Величезна безліч багатотабличних запитів засноване на відносинах предок / нащадок, але в SQL не потрібно, щоб пов'язані стовпці представляли собою пару "зовнішній ключ - первинний ключ". Будь-які два стовпці з двох таблиць можуть бути пов'язаними стовпцями, якщо тільки вони мають порівнянні типи даних.Об'єднання таблиць за нерівністю. Термін "об'єднання" застосовується до будь-якого запиту, який об'єднує дані з двох таблиць бази даних шляхом порівняння значень у двох стовпцях цих таблиць. Найпоширенішими є об'єднання, створені на основі рівності пов'язаних стовпців (об'єднання з рівності). Крім того, SQL дозволяє об'єднувати таблиці за допомогою інших операцій порівняння. Наприклад, отримати всі коди дисциплін за яким студентом з кодом 1 була отримана оцінка більша, ніж з дисципліни з кодом 1.
SELECT Marks1.SubjNo
FROM Marks AS Marks1, Marks AS Marks2
WHERE Marks1.Mark> Marks2.Mark
AND Marks2.SubjNo = 1 AND Marks1.StNo = 1
Слід визнати, що даний приклад має дещо штучний характер і є ілюстрацією того, чому так мало поширені об'єднання за нерівністю. Однак вони можуть виявитися корисними в додатках, призначених для підтримки прийняття рішень, і в інших додатках, які досліджують більш складні взаємозв'язки в базі даних.
Повні імена стовпців. У навчальній базі даних є декілька випадків, коли дві таблиці містять стовпці з однаковими іменами. Наприклад, стовпчики з ім'ям GrNo є в таблицях Groups та Students.
Щоб виключити різночитання, за умов згадування стовпців необхідно використовувати їх повні імена. Повне ім'я стовпця містить назву стовпця та ім'я таблиці, в якій він знаходиться. Повні імена двох стовпців GrNo у навчальній базі даних будуть такими:
Groups.GrNo Students.GrNo
У операторі SELECT замість простих імен стовпців завжди можна використовувати повні імена. Таблиця, задана в повному імені стовпця, повинна, звичайно, відповідати одній з таблиць, заданих в пропозиції FROM.
Читання всіх стовпців. Як вже говорилося раніше, оператор SELECT * використовується для читання всіх стовпців таблиці, зазначеної в пропозиції FROM. У багатотабличних запиті зірочка означає вибір всіх стовпців з усіх таблиць, зазначених у пропозиції FROM.
Самооб'єднання. Деякі багатотабличні запити використовують відносини, що існують усередині однієї з таблиць. Припустимо, наприклад, що потрібно вивести список імен всіх викладачів і їх керівників. Кожному викладачеві відповідає один рядок у таблиці Teachers, а стовпець ChiefNo містить ідентифікатор викладача, що є керівником. Стовпцю ChiefNo слід було б бути зовнішнім ключем для таблиці, в якій зберігаються дані про керівників. І він їм, фактично, є - це зовнішній ключ для самої таблиці Teachers.
Для об'єднання таблиці з самою собою у SQL застосовується саме такий підхід: використання "віртуальної копії". Замість того щоб насправді зробити копію таблиці, SQL дозволяє вам послатися на неї, використовуючи інше ім'я, яке називається псевдонімом таблиці.
Наприклад: вивести список усіх викладачів і їх керівників.
SELECT Teachers. TName, Chiefs.TName
FROM Teachers, Teachers Chiefs
WHERE Teachers. ChiefNo = Chiefs.TNo
Псевдоніми таблиць. Як вже було сказано в попередньому параграфі, псевдоніми таблиць необхідні у запитах, що включають самооб'єднання. Однак псевдонім можна використовувати в будь-який запит (наприклад, якщо запит стосується таблиці іншого користувача або якщо ім'я таблиці дуже довге і використовувати його в повних іменах стовпців утомливо).
Зовнішнє об'єднання таблиць. Операція об'єднання в SQL об'єднає інформацію з двох таблиць, формуючи пари пов'язаних рядків з цих двох таблиць. Об'єднану таблицю утворюють пари тих рядків з різних таблиць, у яких в пов'язаних стовпці містять однакові значення. Якщо рядок однієї з таблиць не має пари, то такий вид об'єднання, званий внутрішнім об'єднанням, може призвести до несподіваних результатів (втрати деяких даних у результаті запиту). Для створення об'єднань таблиць, що мають неоднакові значення у стовпцях, на основі яких здійснюється зв'язок, застосовують зовнішнє об'єднання таблиць найбільш повно представлене в стандарті SQL2.
Література:
1. Джеймс Р. Грофф, Пол Н. Вайнберг. SQL: повне керівництво: пер.с англ. -К.: Видавнича група BHV, 2000.-608с. Стор. 31-39,69-166.
ЛЕКЦІЯ 10. Мова SQL (продовження)
10.1 Об'єднання і стандарт SQL210.2 Підсумкові запити на читання. Агрегатні функції
10.3 Запити з угрупованням (пропозиція GROUP BY)
10.4 Вкладені запити
10.1 Об'єднання і стандарт SQL2
У стандарті SQL2 був визначений абсолютно новий метод підтримки зовнішніх об'єднань, який не спирався на жодну популярну СУБД. У специфікації стандарту SQL2 підтримка зовнішніх об'єднань здійснювалася в пропозиції FROM з ретельно розробленим синтаксисом, що дозволяв користувачеві точно визначити, як вихідні таблиці мають бути об'єднані в запиті. Розширене пропозицію FROM підтримує також операцію UNION над таблицями і допускає складні комбінації запитів на об'єднання операторів SELECT та об'єднань таблиць.10.1.1 Внутрішні об'єднання в стандарті SQL2
Дві об'єднуються таблиці з'єднуються явно за допомогою операції JOIN, а умова пошуку, що описує об'єднання, знаходиться тепер у пропозиції ON всередині пропозиції FROM У умови пошуку, наступного за ключовим словом ON, можуть бути задані будь-які критерії порівняння рядків двох об'єднуються таблиць.Наприклад: вивести список прізвищ студентів, і назви груп, до яких вони навчаються.
SELECT StName, GrName
FROM Students INNER JOIN Groups
ON Students.GrNo = Groups.GrNo
(У цих простих двухтаблічних об'єднаннях весь вміст пропозиції WHERE просто перейшло в речення ON, і пропозиція ON не додає нічого нового до мови SQL.
Стандарт SQL2 допускає ще один варіант запиту на просте внутрішнє об'єднання таблиць Students і Groups. Так як пов'язані стовпці цих таблиць мають однакові імена і порівнюються на предмет рівності (що робиться досить часто), то можна використовувати альтернативну форму пропозиції ON, в якій задається список імен пов'язаних стовпців:
SELECT StName, GrName
FROM Students INNER JOIN Groups USING (GrNo)
Нижче наведено синтаксис оператора JOIN:
1. природне з'єднання.
FROM спеціфікація_табліц, ...,
таблица1
NATURAL {INNER | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]} JOIN
таблица2 ...
2. з'єднання з використанням вираження.
FROM спеціфікація_табліци, ...,
таблица1
{INNER | [OUTER] FULL | [OUTER] LEFT | [OUTER] RIGHT} JOIN
таблица2
ON умова | USING (спісок_столбцов), ...
3. об'єднання або декартово твір.
FROM спеціфікація_табліци, ...,
таблица1 {UNION | CROSS JOIN} таблица2, ...
Об'єднання двох таблиць, в якому пов'язані стовпці мають ідентичні імена, називається природним об'єднанням, так як зазвичай це дійсно самий "природний" спосіб об'єднання двох таблиць. Запит на вибірку пар прізвище студента / назва групи, в якій він вчитися, можна виразити як природне об'єднання наступним чином:
SELECT StName, GrName
FROM Students NATURAL INNER JOIN Groups
10.1.2 Зовнішні об'єднання в стандарті SQL2
Стандарт SQL2 забезпечує повну підтримку зовнішніх об'єднань, розширюючи мовні конструкції, які використовуються для внутрішніх об'єднань. Наприклад, для побудови таблиці підпорядкованості викладачів можна застосувати наступний запит:SELECT Chief.TName, SubOrdinate.TName
FROM Teachers AS Chief FULL OUTER JOIN Teachers AS SubOrdinate
ON Chief.TNo = SubOrdinate.TChiefNo
Результат такого запиту (дані з Додатка А) наведено на REF _Ref12179186 \ h \ * MERGEFORMAT рис. 10.1
| Chief.TName | SubOrdinate.TName |
| NULL | Іванов |
| Іванов | Петров |
| Петров | Стрільців |
| Петров | Сидоров |
| Сидоров | NULL |
| Стрільців | NULL |
Таблиця результатів запиту буде містити по одному рядку для кожної пов'язаної пари начальник / підлеглий, а також по одному рядку для кожної незв'язаної запису для начальника чи підлеглого, розширеної значеннями NULL у стовпцях іншої таблиці.
Ключове слово OUTER, так само як і ключове слово INNER, в стандарті SQL2 не є обов'язковим. Тому попередній запит можна, було б переписати таким чином:
SELECT Chief.TName, SubOrdinate.TName
FROM Teachers AS Chief FULL JOIN Teachers AS SubOrdinate
ON Chief.TNo = SubOrdinate.TChiefNo
По слову FULL СУБД сама визначає, що запитується зовнішнє об'єднання.
Цілком природно, що в стандарті SQL2 ліве і праве зовнішні об'єднання позначаються словами LEFT і RIGHT замість слова FULL. Ось варіант того ж запиту, визначає ліве зовнішнє об'єднання:
SELECT Chief.TName, SubOrdinate.TName
FROM Teachers AS Chief LEFT OUTER JOIN Teachers AS SubOrdinate
ON Chief.TNo = SubOrdinate.TChiefNo
У результаті такого запиту (дані з Додатка А.) буде отримано таке ставлення (REF _Ref12179298 \ h \ * MERGEFORMAT рис. 10.2
| Chief.TName | SubOrdinate.TName |
| Іванов | Петров |
| Петров | Стрільців |
| Петров | Сидоров |
| Сидоров | NULL |
| Стрільців | NULL |
10.1.3 Перехресні об'єднання та запити на об'єднання в SQL2
Розширене пропозицію FROM в стандарті SQL2 підтримує також два інших способи з'єднання даних з двох таблиць - декартово твір і запити на об'єднання. Строго кажучи, жоден з них не є операцією "об'єднання", але вони підтримуються в стандарті SQL2 за допомогою тих же самих пропозицій, що і внутрішні і зовнішні об'єднання. Ось запит, створює декартово твір таблиць Students і Groups:SELECT *
FROM Students CROSS JOIN Groups
10.1.4 багатотабличних об'єднання в стандарті SQL2
Одне з великих переваг розширеної пропозиції FROM полягає в тому, що воно дає єдиний стандарт для визначення як внутрішніх і зовнішніх об'єднань, так і творів і запитів на об'єднання. Іншим, навіть ще більш важливою перевагою цієї пропозиції є те, що воно забезпечує дуже ясну і чітку специфікацію об'єднань трьох і чотирьох таблиць, а також творів і запитів на об'єднання. Для побудови цих складних об'єднань будь-які висловлювання описані раніше, можуть бути укладені в круглі дужки. Результуюче вираз, у свою чергу, можна використовувати для створення інших виразів об'єднання, як якщо б воно було простою таблицею. Точно так само, як SQL дозволяє за допомогою круглих дужок комбінувати різні арифметичні операції (+, -, * і /) і будувати складні вирази, стандарт SQL2 дає можливість створювати складні вирази для об'єднань.10.2 Підсумкові запити на читання. Агрегатні функції
Для підбиття підсумків за інформацією, що міститься в базі даних, в SQL передбачені агрегатні (статистичні) функції. Агрегатна функція приймає в якості аргументу будь-якої стовпець даних цілком, а повертає одне значення, яке певним чином підсумовує цей стовпець. Наприклад, агрегатна функція AVG () приймає в якості аргументу стовпець чисел і обчислює їх середнє значення.У SQL є шість агрегатних функцій, які дозволяють отримувати різні види підсумкової інформації. Нижче описаний синтаксис цих функцій:
1. функція SUM () обчислює суму всіх значень, що містяться в стовпці:
SUM (вираз | [DISTINCT] ім'я_стовпця)
2. функція AVG () обчислює середнє всіх значень, що містяться в стовпці:
AVG (вираз | [DISTINCT] ім'я_стовпця)
3. функція MIN () знаходить найменше серед усіх значень, що містяться в стовпці:
MIN (вираз | ім'я_стовпця)
4. функція МАХ () знаходить найбільше серед всіх значень, що містяться в стовпці:
MAX (вираз | ім'я_стовпця)
5. функція COUNT () підраховує кількість значень, що містяться в стовпці:
COUNT ([DISTINCT] ім'я_стовпця)
6. функція COUNT (*) підраховує кількість рядків у таблиці результатів запиту:
COUNT (*)
10.2.1 Агрегатні функції і значення NULL
У стандарті ANSI / ISO також визначені наступні точні правила обробки значень NULL в агрегатних функціях:1. якщо які-небудь із значень, що містяться в стовпці, рівні NULL, при обчисленні результату функції вони виключаються;
2. якщо всі значення в стовпці рівні NULL, то функції SUM (), AVG (), MIN () і MAX () повертають значення NULL; функція COUNT () повертає нуль;
3. якщо в стовпці немає значень (тобто стовпець порожній), то функції SUM (), AVG (), MIN () і МАХ () повертають значення NULL; функція COUNT () повертає нуль;
4. функція COUNT (*) підраховує кількість рядків і не залежить від наявності або відсутності в стовпці значень NULL; якщо рядків в таблиці немає, ця функція повертає нуль.
10.3 Запити з угрупованням (пропозиція GROUP BY)
Підсумкові запити, про яких до цих пір йшлося в цій главі розраховують підсумкові результати на підставі всіх записів в таблиці. Однак необхідність у таких обчисленнях виникає досить рідко, частіше буває необхідно отримувати проміжні підсумки на підставі груп записів у таблиці. Цю можливість надає пропозицію GROUP BY оператора SELECT.Запит, що включає в себе пропозицію GROUP BY, називається запитом з угрупованням, оскільки він об'єднує рядка вихідних таблиць в групи і для кожної групи рядків генерує один рядок таблиці результатів запиту. Стовпці, зазначені в пропозиції GROUP BY, називаються стовпцями угруповання, оскільки саме вони визначають, за якою ознакою рядки діляться на групи. Наприклад, отримати список прізвищ студентів та їх середніх оцінок.
SELECT StName, AVG (Mark)
FROM Marks INNER JOIN Students USING (StNo)
GROUP BY StName
10.3.1 Кілька стовпців угруповання
SQL дозволяє групувати результати запиту на підставі двох або більше стовпців. Наприклад, отримати список прізвищ студентів та їх середніх оцінок за кожний семестр.SELECT StName, Semester, AVG (Mark)
FROM Marks INNER JOIN Students USING (StNo)
GROUP BY StName, Semester
10.3.2 Обмеження на запити з угрупуванням
На запити, в яких використовується угруповання, накладаються додаткові обмеження. Стовпці з угрупованням повинні представляти собою реальні стовпці таблиць, перелічених у реченні FROM. Не можна групувати рядки на підставі значення обчислюваного виразу.Крім того, існують обмеження на елементи списку повертаються стовпців. Всі елементи цього списку повинні мати одне значення для кожної групи рядків. Це означає, що повертається стовпцем може бути:
1. константа;
2. агрегатна функція, що повертає одне значення для всіх рядків, що входять в групу;
3. стовпець угруповання, який, за визначенням, має одне і те ж значення у всіх рядках групи;
4. вираз, що включає в себе перераховані вище елементи.
На практиці в список повертаються стовпців запиту з угрупованням завжди входять стовпець угруповання і агрегатна функція. Якщо остання не зазначена, значить, запит можна більш просто виразити за допомогою ключового слова DISTINCT без використання пропозиції GROUP BY. І навпаки, якщо не включити в результати запиту стовпець угруповання, ви не зможете визначити, до якої групи належить кожен рядок результатів.
10.3.3 Значення NULL в стовпцях угруповання
У стандарті ANSI визначено, що два значення NULL в пропозиції GROUP BY рівні.Рядки, що мають значення NULL в однакових стовпцях угруповання й ідентичні значення у всіх інших стовпцях угруповання, поміщаються в одну групу.
10.3.4 Умови пошуку груп (пропозиція HAVING)
Точно так само, як пропозиція WHERE використовується для відбору окремих рядків, які беруть участь у запиті, пропозиція HAVING можна застосувати для відбору груп рядків. Його формат відповідає формату пропозиції WHERE. Пропозиція HAVING складається з ключового слова HAVING, за яким слід умова пошуку. Таким чином, дана пропозиція визначає умову пошуку для груп.10.3.5 Обмеження на умови пошуку груп
Пропозиція HAVING використовується для того, щоб включати і виключати групи рядків з результатів запиту, тому на паливо в ньому умова пошуку накладені такі ж обмеження, як і на елементи списку повертаються стовпців.10.3.6 Пропозиція HAVING без GROUP BY
Пропозиція HAVING майже завжди використовується в поєднанні з пропозицією GROUP BY, проте синтаксис оператора SELECT не вимагає цього. Якщо пропозиція HAVING використовується без пропозиції GROUP BY, SQL розглядає повні результати запиту як одну групу. Іншими словами, агрегатні функції, що містяться в пропозиції HAVING, застосовуються до однієї й лише одній групі, і ця група складається з усіх рядків. На практиці пропозицію HAVING дуже рідко використовується без відповідної пропозиції GROUP BY.10.4 Вкладені запити
Вкладеним (або підлеглим) запитом називається запит, що міститься у пропозиції WHERE або HAVING іншого оператора SQL. Вкладені запити дозволяють природним чином обробляти запити, виражені через результати інших запитів.Найчастіше вкладені запити вказуються в реченні WHERE оператора SQL. Коли вкладений запит міститься в даній пропозиції, він бере участь у процесі відбору рядків.
Наприклад, вивести список прізвищ студентів, середній бал яких перевищує 4,5:
SELECT StName
FROM Students
WHERE (SELECT AVG (Mark)
FROM Marks
WHERE Marks.StNo = Students.StNo)> 4.5
Вкладений запит завжди полягає в круглі дужки, але як і раніше зберігає знайому структуру оператора SELECT, що містить пропозицію FROM і необов'язкові пропозиції WHERE, GROUP BY і HAVING. Структура цих пропозицій у вкладеному запиті ідентична їх структурі в оператора SELECT; у вкладеному запиті ці речення виконують свої звичайні функції. Однак між вкладеним запитом і оператором SELECT є ряд відмінностей:
1. Таблиця результатів вкладеного запиту завжди складається з одного стовпця. Це означає, що в пропозиції SELECT вкладеного запиту завжди вказується один повертається стовпець.
2. Під вкладений запит не може входити пропозицію ORDER BY. Результати вкладеного запиту використовуються тільки всередині головного запиту і для користувача залишаються невидимими, тому немає сенсу їх сортувати.
3. Вкладений запит не може бути запитом на об'єднання кількох різних операторів SELECT; допускається використання тільки одного оператора SELECT.
4. Імена стовпців у вкладеному запиті можуть бути посиланням на стовпці таблиць головного запиту. Це так звані зовнішні посилання (посилання на Students.StNo у попередньому прикладі). Зовнішні посилання необов'язково повинні бути присутніми у вкладеному запиті.
10.4.1 Умови пошуку у вкладеному запиті
Вкладений запит завжди є частиною умови пошуку в реченні WHERE або HAVING. Раніше було розглянуто прості умови пошуку, які можуть використовуватися в цих пропозиціях. Крім того, в SQL є наступні умови пошуку у вкладеному запиті.Порівняння з результатом вкладеного запиту (=, <>, <, <=,>,> =). Порівнює значення виразу з одним значенням, повернутим вкладеним запитом. Ця перевірка нагадує просте порівняння.
проверяемое_вираженіе | = | <> | <| <= |> |> = | вложенний_запрос
Порівняння з результатом вкладеного запиту є модифікованою формою простого порівняння. Значення виразу порівнюється зі значенням, повернутим вкладеним запитом, і якщо умова порівняння виконується, то перевірка повертає значення TRUE. Ця перевірка використовується для порівняння значення з перевіреній рядки з одним значенням, повернутим вкладеним запитом, як показано першому прикладі.
Перевірка на належність результатами вкладеного запиту (IN). Перевірка на належність результатами вкладеного запиту (ключове слово IN) є видозміненою формою простої перевірки на членство в множині.
проверяемое_вираженіе [NOT] IN вложенний_запрос
Одне значення порівнюється зі стовпцем даних, повернутих вкладеним запитом, і якщо це значення дорівнює одному зі значень в стовпці, перевірка повертає TRUE. Ця перевірка використовується, коли необхідно порівняти значення з перевіреній рядки з безліччю значень, повернутих вкладеним запитом. Наприклад, вивести всю інформацію про студентів, які навчаються у групах сназваніямі, що починаються на букву ² А ²:
SELECT *
FROM Students
WHERE GrNo IN (SELECT GrNo FROM Groups WHERE GrName LIKE 'А%')
Перевірка на існування (EXISTS). У результаті перевірки на існування (ключове слово EXISTS) можна з'ясувати, чи міститься в таблиці результатів вкладеного запиту хоча б один рядок. Аналогічної простої перевірки не існує. Перевірка на існування використовується тільки з вкладеними запитами.
[NOT] EXISTS вложенний_запрос
Наприклад, вивести прізвища студентів, які в 1-му семестрі здали хоча б одну дисципліну:
SELECT StName
FROM Students
WHERE EXISTS (SELECT *
FROM Marks
WHERE Marks.StNo = Students.StNo AND Semester = 1)
Багаторазове порівняння (ANY і ALL). У перевірці IN з'ясовується, не дорівнює чи деяке значення одному зі значень, що містяться в стовпці результатів вкладеного запиту. У SQL є два різновиди багаторазового порівняння - ANY і ALL, розширюють попередню перевірку до рівня інших операторів порівняння. В обох перевірках деяке значення порівнюється зі стовпцем даних, повернутих вкладеним запитом.
проверяемое_вираженіе | = | <> | <| <= |> |> = | ANY | ALL вложенний_запрос
Перевірка ANY. У перевірці ANY використовується один з шести операторів порівняння (=, <>, <, <=,>,> =) для того, щоб порівняти одне перевіряється значення зі стовпцем даних, повернутих вкладеним запитом. Перевіряється значення по черзі порівнюється з кожним значенням, що містяться в стовпці. Якщо будь-яке з цих порівнянь дає результат TRUE, то перевірка ANY повертає значення TRUE.
Наприклад, вивести список прізвищ студентів, які отримали в першому семестрі хоча б одну відмінну оцінку.
SELECT StName
FROM Students
WHERE 5 = ANY (SELECT Mark
FROM Marks
WHERE Marks.StNo = Students.StNo AND Semester = 1)
Якщо вкладений запит у перевірці ANY не створює жодного рядка результату або якщо результати містять значення NULL, то в різних СУБД перевірка ANY може виконуватися по-різному. У стандарті ANSI / ISO для мови SQL містяться докладні правила, що визначають результати перевірки ANY, коли перевіряється значення порівнюється зі стовпцем результатів вкладеного запиту:
1. якщо вкладений запит повертає порожній стовпець результатів, то перевірка ANY має значення FALSE (в результаті виконання вкладеного запиту не отримано жодного значення, для якого виконувалося б умова порівняння).
2. якщо операція порівняння має значення TRUE хоча б для одного значення в стовпці, то перевірка ANY повертає значення TRUE (мається деяке значення, отримане вкладеним запитом, для якого умова порівняння виконується).
3. якщо операція порівняння має значення FALSE для всіх значень у стовпці, то перевірка ANY повертає значення FALSE (можна стверджувати, що ні для одного значення, повернутого вкладеним запитом, умова порівняння не виконується);
4. якщо операція порівняння не має значення TRUE ні для одного значення в стовпці, але в ньому є одне або кілька значень NULL то перевірка ANY повертає результат NULL. (У цій ситуації неможливо але з певністю стверджувати, чи існує отримане вкладеним запитом значення, для якого виконується умова порівняння; може бути, існує, а може й ні - все залежить від "справжніх" значень невідомих даних.)
Перевірка ALL. У перевірці ALL, як і в перевірці ANY, використовується один з шести операторів (=, <>, <, <=,>,> =) для порівняння одного перевіряється значення зі стовпцем даних, повернутих вкладеним запитом. Перевіряється значення по черзі порівнюється з кожним значенням, що містяться в стовпці. Якщо всі порівняння дають результат TRUE, то перевірка ALL повертає значення TRUE.
Наприклад, вивести список прізвищ студентів, які отримали в першому семестрі тільки задовільні оцінки.
SELECT StName
FROM Students
WHERE 3 = ALL (SELECT Mark
FROM Marks
WHERE Marks.StNo = Students.StNo AND Semester = 1)
Якщо вкладений запит у перевірці ALL не повертає ні одного рядка або якщо результати запиту містять значення NULL, то в різних СУБД перевірка ALL може виконуватися по-різному. У стандарті ANSI / ISO для мови SQL містяться докладні правила, що визначають результати перевірки ALL, коли перевіряється значення порівнюється зі стовпцем результатів вкладеного запиту:
1. якщо вкладений запит повертає порожній стовпець результатів, то перевірка ALL має значення TRUE. Вважається, що умова порівняння виконується, навіть якщо результати вкладеного запиту відсутні.
2. якщо операція порівняння дає результат TRUE для кожного значення в стовпці, то перевірка ALL повертає значення TRUE. Умова порівняння виконується для кожного значення, отриманого вкладеним запитом.
3. якщо операція порівняння дає результат FALSE для якого-небудь значення в стовпці, то перевірка ALL повертає значення FALSE. У цьому, випадку можна стверджувати, що умова пошуку працює не для кожного значення, отриманого вкладеним запитом.
4. якщо операція порівняння не дає результат FALSE ні для одного значення в стовпці, але для одного або декількох значень дає результат NULL, то перевірка ALL повертає значення NULL. У цій ситуації не можна з певністю стверджувати, чи для всіх значень, отриманих вкладеним запитом, справедливо умова порівняння; може бути, для всіх, а може й ні - все залежить від "справжніх" значень невідомих даних.
10.4.2 Вкладені запити та об'єднання
При читанні цього розділу ви, можливо, помітили, що багато запити, записані із застосуванням вкладених запитів, можна також записати у вигляді багатотабличних запитів. Таке трапляється досить часто, і SQL дозволяє записати запит будь-яким способом.10.4.3 Рівні вкладеності запитів
Всі розглянуті до цих пір запити були "дворівневими" і складалися з головного і вкладеного запитів. Точно так само, як усередині головної запиту може знаходиться вкладений запит, всередині вкладеного запит може знаходитися ще один вкладений запит.10.4.4 Вкладені запити в пропозиції HAVING
Хоча вкладені запити найчастіше застосовуються в реченні WHERE їх можна використовувати і в пропозиції HAVING головного запиту. Коли вкладений запит міститься в пропозиції HAVING, він бере участь у відбір групи рядків.Література:
1. Джеймс Р. Грофф, Пол Н. Вайнберг. SQL: повне керівництво: пер.с англ. -К.: Видавнича група BHV, 2000.-608с. Стор. 169-217.
ЛЕКЦІЯ 11. Мова SQL. (Продовження)
11.1 Внесення змін до бази даних.11.2 Видалення існуючих даних (Оператор DELETE)
11.3 Оновлення існуючих даних (Оператор UPDATE)
11.4 Визначення структури даних в SQL
11.5 Поняття представлення.
11.6 Подання в SQL.
11.7 Системний каталог (самостійне вивчення)
11.1 Внесення змін до бази даних.
SQL представляє собою повноцінний мова, призначена для роботи з даними і дозволяє не тільки отримувати інформацію з бази даних за допомогою запитів на читання, але і змінювати міститься в ній за допомогою запитів на додавання, видалення і оновлення.У порівнянні з оператором SELECT, за допомогою якого виконуються запити на читання, оператори SQL, що змінюють вміст бази даних, є більш простими. Однак при зміні вмісту бази даних до СУБД пред'являється ряд додаткових вимог. При внесенні змін СУБД повинна зберігати цілісність даних і дозволяти введення в базу даних тільки допустимих значень, а також забезпечувати несуперечність бази даних навіть у разі системної помилки. Крім цього, СУБД повинна забезпечувати можливість одночасного зміни бази даних кількома користувачами таким чином, щоб вони не заважали один одному.
11.1.1 Додавання нових даних (оператор INSERT).
Однорядковий оператор INSERT, синтаксис якого описаний нижче, додає в таблицю новий рядок. У пропозиції INTO вказується таблиця, в яку додається новий рядок (цільова таблиця), а в пропозиції VALUES містяться значення даних для нового рядка. Список стовпців визначає, які значення в який стовпець заносяться.INSERT INTO ім'я_таблиці (ім'я_стовпця, ...) VALUES (константа | NULL, ...)
Нижче наведено приклад оператора INSERT, який додає інформацію про нову групу ² До-99-51 ² в навчальну базу даних:
INSERT INTO Groups (GrNo, EnterYear, GrName)
VALUES (6, 1999, 'К-99-51')
Додавання значень NULL. При додаванні в таблицю нового рядка всіма стовпцями, імена яких відсутні в списку стовпців оператора INSERT, автоматично присвоюється значення NULL.
Додавання всіх стовпців. Для зручності в SQL дозволяється не включати список стовпців в оператор INSERT. Якщо список стовпців опущений, він генерується автоматично і в ньому зліва направо перераховуються всі стовпці таблиці. При виконанні оператора SELECT * генерується такий же список стовпців. Користуючись цією скороченою формою запису, оператор INSERT з попереднього прикладу можна переписати таким чином:
INSERT INTO Groups
VALUES (6, 1999, 'К-99-51')
Якщо список стовпців опущений, то в списку значень необхідно явно вказувати значення NULL. Крім того, послідовність значень даних повинна в точності відповідати порядку стовпців в таблиці.
Багаторядковий оператор INSERT, додає в цільову таблицю кілька рядків. У цьому різновиді оператора INSERT значення даних для нових рядків явно не задаються. Джерелом нових рядків служить запит на читання, що міститься всередині оператора INSERT.
INSERT INTO ім'я_таблиці (ім'я_стовпця, ...) запит
11.2 Видалення існуючих даних (Оператор DELETE)
Найменшою одиницею інформації, яку можна видалити з реляційної бази даних, є один рядок.Оператор DELETE, синтаксис якого зображений нижче, видаляє обрані рядки даних з однієї таблиці.
DELETE FROM ім'я_таблиці [WHERE условіе_поіска]
У пропозиції FROM вказується таблиця, яка містить рядки, які потрібно видалити. У пропозиції WHERE вказуються рядки, які повинні бути видалені.
Наприклад, видалити з навчальної бази даних студента з кодом 3.
DELETE FROM Students
WHERE StNo = 3
Хоча пропозиція WHERE в операторі DELETE є необов'язковим, воно є майже завжди. Якщо ж воно відсутнє, то видаляються всі рядки цільової таблиці.
Іноді відбір рядків необхідно виробляти, спираючись на дані з інших таблиць. Для цього можна використовувати вкладені запити.
11.3 Оновлення існуючих даних (Оператор UPDATE)
Оператор UPDATE, оновлює значення одного або кількох стовпців у вибраних рядках однієї таблиці.UPDATE ім'я_таблиці
SET ім'я_стовпця = вираз, ...
[WHERE условіе_поіска]
У операторі вказується цільова таблиця, яка повинна бути модифікована, при цьому користувач повинен мати дозвіл на оновлення таблиці і кожного конкретного стовпця. Пропозиція WHERE відбирає рядки таблиці, що підлягають оновленню. У пропозиції SET вказується, які стовпці повинні бути оновлені, і для них задаються нові значення.
Наприклад, Змінити прізвище студента ² Петрова ² з кодом 2 на прізвище ² Петренко ²:
UPDATE Students
SET StName = 'Петренко'
WHERE StNo = 2
Умови пошуку, які можуть бути задані в пропозиції WHERE оператора UPDATE, в точності відповідають умовам пошуку, доступним в операторах SELECT і DELETE.
Як і оператор DELETE, оператор UPDATE може одночасно оновити кілька рядків, які відповідають умовам пошуку.
Пропозиція WHERE в операторі UPDATE є необов'язковим. Якщо воно опущено, то оновлюються всі рядки цільової таблиці.
У операторі UPDATE, так само як і в операторі DELETE, вкладені запити можуть відігравати важливу роль, оскільки вони дають можливість відбирати рядка для оновлення, спираючись на інформацію з інших таблиць.
11.4 Визначення структури даних в SQL
У реляційній базі даних найбільш важливим елементом її структури є таблиця. У багатокористувацької базі даних основні таблиці, як правило, створює адміністратор, а користувачі просто звертаються і ним в процесі роботи. При роботі з базою даних часто виявляється зручним створювати власні таблиці і зберігати в них власні дані, а також дані, прочитані з інших таблиць. Такі таблиці можуть бути тимчасовими і існувати тільки протягом одного інтерактивного SQL-сеансу, але можуть зберігатися і протягом декількох тижнів або навіть місяців.11.4.1 Створення таблиці (оператор CREATE TABLE)
Оператор CREATE TABLE, синтаксис якого зображений нижче, визначає нову таблицю. Різні пропозиції оператора задають елементи визначень таблиці. слід також пам'ятати, що не всі параметри стандарту SQL2 присутні у всіх СУБД.Після виконання оператора CREATE TABLE з'являється нова таблиця, якій присвоюється ім'я, вказане в операторі. Ім'я таблиці має бути ідентифікатором, припустимим у SQL, і не повинно конфліктувати з іменами існуючих таблиць.
CREATE TABLE ім'я_таблиці (
визначення стовпців
ім'я_стовпця тип_даних [DEFAULT значення] [NOT NULL], ...
визначення первинного ключа
PRIMARY KEY (ім'я_стовпця, ...),
визначення зовнішніх ключів
FOREIGN KEY імя_ограніченія (ім'я_стовпця, ...)
REFERENCE ім'я_таблиці
[ON DELETE CASCADE | SET NULL | SET DEFAULT | NO ACTION]
[ON UPDATE CASCADE | SET NULL | SET DEFAULT | NO ACTION],
умова унікальності даних
UNIQUE (ім'я_стовпця, ...),
умова перевірки
CHECK (условіе_поіска)
)
Визначення стовпців. Стовпці нової таблиці задаються в операторі CREATE TABLE. Визначення стовпців представляють собою укладений в дужки список, елементи якого відокремлені один від одного комами. Порядок проходження визначень стовпців у списку відповідає розташуванню стовпців в таблиці. Кожне таке визначення містить наступну інформацію:
1. Ім'я стовпця, яке використовується для посилання на стовпець у оператора SQL. Кожен стовпець у таблиці повинен мати унікальне ім'я, але 'різних таблицях імена стовпців можуть збігатися.
2. Тип даних стовпця, що показує, дані якого виду зберігаються у стовпці.
3. Вказівка на те, чи обов'язково стовпець повинен містити дані. Пропозиція NOT NULL запобігає занесення в стовпець значень NULL, в іншому випадку значення NULL допускаються.
4. Значення за замовчуванням для стовпця СУБД, яке заноситься у стовпець у тому випадку, якщо в операторі INSERT для таблиці не визначено значення даного стовпця.
Визначення первинного та зовнішнього ключів. Крім визначень стовпців таблиці, в операторі CREATE TABLE вказується інформація про первинному ключі таблиці та про її зв'язки з іншими таблицями бази даних Ця інформація міститься в пропозиціях PRIMARY KEY і FOREIGN KEY.
У пропозиції PRIMARY KEY задається стовпець або стовпці, які утворюють первинний ключ таблиці. Цей стовпець (або комбінація стовпців) служить в якості унікального ідентифікатора рядків таблиці. СУБД автоматично стежить за тим, щоб первинний ключ кожного рядка таблиці мав унікальне значення. Крім того, у визначеннях стовпців первинного ключа повинно бути вказано, що вони не можуть містити значення NULL.
У пропозиції FOREIGN KEY задається зовнішній ключ таблиці і визначається зв'язок, яку він створює для неї з іншою таблицею (таблицею-предком). У ньому зазначаються:
1. стовпець або стовпці створюваної таблиці, які утворюють зовнішній ключ;
2. таблиця, зв'язок з якою створює зовнішній ключ. Це таблиця-предок;
3. необов'язкове ім'я для цього відношення; воно не використовується в операторах SQL, але може з'являтися в повідомленнях про помилки і потрібно надалі, якщо буде необхідно видалити зовнішній ключ;
4. як СУБД повинна поводитися зі значеннями NULL в одному чи кількох стовпчиках зовнішнього ключа при зв'язуванні його з рядками таблиці-предка;
5. необов'язкове правило видалення для цього відносини (CASCADE, SET NULL, SET DEFAULT або NO ACTION), яке визначає дії, що робляться при видаленні рядка-предка;
6. необов'язкове правило поновлення даного ставлення, яке визначає дії, що робляться при оновленні первинного ключа в рядку-предка;
7. необов'язкова умова перевірки, яке обмежує дані в таб особі так, щоб вони відповідали певній умові пошуку.
Наприклад, створити таблицю Students.
CREATE TABLE Students (
StNo INT NOT NULL,
GrNo INT NOT NULL,
StName CHAR (30) NOT NULL,
CityNo INT,
PRIMARY KEY (StNo),
FOREIGN KEY Students_Groups (GrNo)
REFERENCES Groups,
FOREIGN KEY Students_Cities (CityNo)
REFERENCES Cities,
CHECK (CHAR_LENGTH (StName)> 10)
)
11.4.2 Видалення таблиці (оператор DROP TABLE)
Таблиці можна видалити з бази даних за допомогою оператора DROP TABLE.DROP TABLE ім'я_таблиці CASCADE | RESTRICT
Оператор містить ім'я видаляється таблиці. Зазвичай користувач видаляє одну зі своїх власних таблиць і вказує в операторі неповне. ім'я таблиці. Маючи відповідний дозвіл, можна також видаліть таблицю іншого користувача, але в цьому випадку необхідно вказувати повне ім'я таблиці.
Стандарт SQL2 вимагає, щоб оператор DROP TABLE включав в себе або параметр CASCADE, або RESTRICT, які визначають, як впливає видалення таблиці на інші об'єкти бази даних. Якщо задано параметр RESTRICT та в базі даних є об'єкти, які містять посилання на удаляемую таблицю, то виконання оператора DROP TABLE закінчиться неуспішно. У більшості комерційних СУБД допускається застосування оператора DROP TABLE без будь-яких параметрів.
11.4.3 Зміна визначення таблиці (оператор ALTER TABLE)
У процесі роботи з таблицею іноді виникає необхідність додати до таблиці деяку інформацію. Для зміни структури таблиці використовується оператор ALTER TABLE, за допомогою якого можливо:1. додати в таблицю визначення стовпця;
2. змінити значення за замовчуванням для будь-якого стовпця;
3. додати або видалити первинний ключ таблиці;
4. додати або видалити новий зовнішній ключ таблиці;
5. додати або видалити умова унікальності;
6. додати або видалити умова перевірки.
ALTER TABLE ім'я_таблиці
ADD определеніе_столбца
ALTER ім'я_стовпця SET DEFAULT значення | DROP DEFAULT
DROP ім'я_стовпця CASCADE | RESTRICT
ADD определение_первичного_ключа
ADD определение_внешнегого_ключа
ADD условие_уникальности_данных
ADD условіе_проверкі
DROP CONSTRAINT імя_ограніченія CASCADE | RESTRICT
11.4.4 Визначення доменів
Для вмісту окремого стовпця таблиці в реляційній базі даних існує обмеження: значення стовпця в усіх рядках таблиці повинні бути одного і того ж типу. Наприклад, всі назви міст в стовпці CityName таблиці Sities є символьними рядками змінної довжини.У стандарті SQL2 формально визначення домену реалізовано як частина визначення бази даних. Згідно з цим стандартом, домен є іменованою сукупністю значень даних і широко використовується у визначенні бази даних як додатковий тип даних. Домен створюється за допомогою оператора CREATE DOMAIN на основі базових типів даних. Після створення домену на нього можна посилатися всередині визначення таблиці як на тип даних.
11.4.5 Індекси (оператори CREATE / DROP INDEX)
Одним із структурних елементів фізичної пам'яті, присутніх у більшості реляційних СУБД, є індекс - засіб забезпечує швидкий доступ до рядків таблиці на основі значення одного або декількох стовпців.Для створення і видалення індексів застосовуються оператори CREATE INDEX та DROP INDEX, синтаксис яких описано нижче.
Слід також зауважити, що СУБД автоматично створює індекси для первинних ключів.
CREATE [UNIQUE] INDEX ім'я_індексу ON ім'я_таблиці (ім'я_стовпця [ASC | DESC], ...)
DROP INDEX ім'я_індексу
11.5 Поняття представлення.
Коли СУБД зустрічає в операторі SQL посилання на виставу, вона відшукує його визначення, збережене в базі даних. Потім СУБД перетворює користувальницький запит, що посилається на подання, в еквівалентний запит до вихідних таблиць подання, і виконує цей запит. Таким чином, СУБД створює ілюзію існування подання у вигляді окремої таблиці і в той же час зберігає цілісність вихідних таблиць.11.5.1 Переваги уявлень
Застосування уявлень в базах даних різних типів може виявитися корисним у найрізноманітніших ситуаціях. У базах даних на персональних комп'ютерах подання застосовуються для зручності і дозволяють спрощувати запити до бази даних. У промислових базах даних уявлення грають головну роль у створенні власної структури бази даних для кожного користувача і забезпеченні її безпеки. Основні переваги уявлень перераховані нижче.1. Безпека. Кожному користувачеві можна дозволити доступ до невеликого числа уявлень, які містять лише ту інформацію, яку йому дозволено знати. Таким чином, можна здійснити обмеження доступу користувачів до інформації, що зберігається.
2. Простота запитів. За допомогою подання можна прочитати дані з декількох таблиць і представити їх як одну таблицю, перетворюючи тим самим запит до багатьох таблиць в однотаблічний запит до подання.
3. Простота структури. За допомогою подань для кожного користувача можна створити власну "структуру" бази даних, визначивши її як безліч доступних користувачеві віртуальних таблиць.
4. Захист від змін. Подання може повертати несуперечливий і незмінний образ структури бази даних, навіть якщо вихідні таблиці розділяються, реструктуіруются або перейменовуються.
5. Цілісність даних. Якщо доступ до даних або введення даних здійснюється за допомогою подання, СУБД може автоматично перевіряти, чи виконуються певні умови цілісності.
11.5.2 Недоліки уявлень
Поряд з перерахованими вище перевагами, уявлення мають і двома істотними недоліками;1. Продуктивність. Представлення створює лише видимість існування відповідної таблиці, і СУБД доводиться перетворювати запит до подання у запит до вихідних таблиць. Якщо подання відображає багатотабличних запит, то простий запит до подання стає складним об'єднанням і на його виконання може знадобитися багато часу.
2. Обмеження на оновлення. Коли користувач намагається оновити рядка подання, СУБД повинна встановити їх відповідність рядкам вихідних таблиць, а також оновити останні. Це можливо тільки для простих уявлень; складні уявлення оновлювати не можна, вони доступні лише для читання.
Зазначені недоліки означають, що не варто без розбору застосовувати подання замість вихідних таблиць. У кожному конкретному випадку необхідно враховувати перераховані переваги та недоліки уявлень.
11.6 Подання в SQL.
Оператор CREATE VIEW, синтаксис якого зображений нижче, використовується для створення уявлень. У ньому вказується ім'я подання і запит, що лежить в його основі. Для успішного створення подання необхідно мати дозвіл на доступ до всіх таблиць, що входять до запиту.CREATE VIEW імя_представленія (ім'я_стовпця, ...) AS запит
При необхідності в операторі CREATE VIEW можна задати ім'я для кожного стовпця створюваного подання. Якщо вказується список імен стовпців, то він повинен містити стільки елементів, скільки стовпців міститься в запиті. Зверніть увагу на те, що задаються тільки імена. Наприклад, створити уявлення, що містить прізвища студентів групи A-98-51.
CREATE VIEW GroupA98 (Name) AS
SELECT StName
FROM Students INNER JOIN Groups ON Students.GrNo = Groups.GrNo
WHERE Groups.GrName = 'A-98-51'
11.6.1 Оновлення уявлень і стандарт ANSI / ISO
У стандарті SQL1 точно вказано, для яких уявлень в базі даних повинна існувати можливість оновлення. Відповідно до стандарту, уявлення можна оновлювати в тому випадку, якщо визначає його запит відповідає таким вимогам:1. Повинен бути відсутнім предикат DISTINCT, тобто повторювані рядки не повинні виключатися з таблиці результатів запиту.
2. У пропозиції FROM повинна бути задана тільки одна таблиця, яку можна оновлювати; тобто у подання має бути одна вихідна таблиця, а користувач повинен мати відповідні права доступу до неї. Якщо вихідна таблиця сама є поданням, то воно також має задовольняти цим умовам.
3. Кожне ім'я в списку повертаються стовпців має бути посиланням на просте стовпець; в цьому списку не повинні міститися вираження, обчислювані стовпці або агрегатні функції.
4. Пропозиція WHERE не повинно містити вкладений запит, в ньому можуть бути присутні лише прості умови пошуку.
5. У запиті не повинно міститися пропозиція GROUP BY або HAVING.
Правила, встановлені в стандарті SQL1, є дуже жорсткими. Існує безліч уявлень, які теоретично можна оновлювати, хоча вони не відповідають цим правилам. Крім того, існують уявлення, над якими можна проводити не всі операції оновлення, а також подання, в яких можна оновлювати не всі стовпці. У більшості комерційних СУБД правила оновлення уявлень значно менш суворі, ніж в стандарті SQL1.
11.6.2 Видалення подання (оператор DROP VIEW)
У стандарті SQL2 було формально закріплено використання оператора DROP VIEW для видалення уявлень. У ньому також деталізовані правила видалення уявлень, на основі яких були створені інші уявлення.DROP VIEW імя_представленія CASCADE | RESTRICT
11.7 Системний каталог (самостійне вивчення)
11.7.1 Поняття системний каталог
Системним каталогом називається сукупність спеціальних таблиць бази даних. Їх створює, супроводжує і володіє ними сама СУБД. Ці системні таблиці містять інформацію, яка описує структуру бази даних. Таблиці системного каталогу створюються автоматично при створенні бази даних. Зазвичай вони об'єднуються під спеціальним "системним ідентифікатором користувача" з таким ім'ям, як SYSTEM, SYSTEM, MASTER або DBA.При обробці операторів SQL СУБД постійно звертається до даних системного каталогу. Наприклад, щоб обробити двухтаблічний оператор SELECT, СУБД повинна:
1. перевірити, чи існують дві зазначені таблиці;
2. переконатися, що користувач має дозвіл на доступ до них;
3. перевірити, чи існують стовпчики, на які є посилання в даному запиті;
4. встановити, до яких таблиць відносяться неповні імена стовпців;
5. визначити тип даних кожного стовпця.
Якби системні таблиці служили тільки для задоволення внутрішніх потреб СУБД, то для користувачів бази даних вони не представляли б практично ніякого інтересу. Однак системні таблиці, як правило, доступні також і для користувачів - запити до системних каталогах дозволені майже у всіх СУБД для персональних комп'ютерів і великих ЕОМ. За допомогою запитів до системних каталогах ви можете отримати інформацію про структуру бази даних, навіть якщо ніколи раніше з нею не працювали.
Користувачі можуть тільки зчитувати інформацію з системного каталогу. СУБД забороняє користувачам модифіковані системні таблиці безпосередньо, так як це може порушити цілісність бази даних. СУБД сама вставляє, видаляє і оновлює рядка системних таблиць під час модифікації структури бази даних. Зміни в системних таблицях відбуваються також в якості побічного результату виконання таких операторів DDL, як CREATE, ALTER, DROP, GRANT І REVOKE.
11.7.2 Системний каталог і стандарт ANSI / ISO
У стандарті SQL1 нічого не говориться про структуру та зміст системного каталогу. Стандарт фактично не вимагає навіть наявності самого системного каталогу. Однак у всіх основних реляційних СУБД в тій чи іншій формі він створюється. Структура каталогу і що у ньому таблиці значно відрізняються один від одного в різних СУБД.У зв'язку зі зростаючим значенням інструментальних програм загального призначення, призначених для роботи з базами даних і потребують доступу до системного каталогу, в стандарт SQL2 включена специфікація набору уявлень, що забезпечує стандартизований доступ до інформації, яка зазвичай міститься в системному каталозі. СУБД, що відповідає стандарту SQL2, повинна підтримувати ці подання, (що позначаються всі разом як INFORMATION_SCHEMA (інформаційна схема).
11.7.3 Вміст системного каталогу
Кожна таблиця системного каталогу містить інформацію про окремий структурному елементі бази даних. До складу майже всіх комерційних реляційних СУБД входять, з невеликими відмінностями, системні таблиці, кожна з яких описує один з таких п'яти елементів:1. Таблиці. У каталозі описується кожна таблиця бази даних: зазначається її ім'я, власник, кількість пронумерованих стовпців, їх розмір і т.д.
2. Стовпці. У каталозі описується кожен стовпець бази даних: наводиться ім'я стовпця, ім'я таблиці, якій він належить, тип даних стовпця, його розмір, дозволені значення null і т.д.
3. Користувачі. У каталозі описується кожен зареєстрований користувач бази даних: зазначаються ім'я користувача, його пароль в зашифрованому вигляді і інші дані.
4. Подання. У каталозі описується кожна вистава, що є в базі даних: зазначаються його ім'я, ім'я власника, запит, який є визначенням уявлення, і т.д.
5. Привілеї. У каталозі описується кожен набір привілеїв, наданих в базі даних: наводяться імена тих, хто надав привілеї, і тих, кому вони надані, вказуються самі привілеї, об'єкт, на які вони поширюються, і т.д.
11.7.4 Інформаційна схема в стандарті SQL2
У стандарті SQL2 не визначена форма системного каталогу, яку повинні підтримувати реляційні СУБД. Оскільки в той час, коли приймався стандарт SQL2, вже існував широкий розкид характеристик комерційних СУБД різних типів і величезні відмінності в їх системних каталогах, було неможливо досягти згоди з питання стандартної специфікації системного каталогу. Натомість автори стандарту дали визначення "ідеалізованого" системного каталогу, який постачальники СУБД могли б застосовувати при розробці "з нуля" СУБД, відповідних стандартам SQL2. Таблиці цього ідеалізованого системного каталогу (який у стандарті називається схема визначень) наведені в REF _Ref45511862 \ h \ * MERGEFORMAT табл. 11.1табл. STYLEREF 1 \ s 11. SEQ табл. \ * ARABIC \ s 1 січня Ідеалізований системний каталог, описаний в стандарті SQL2
| Системна таблиця | Вміст |
| USERS | Один рядок для кожного ідентифікатора користувача в каталозі |
| SCHEMATA | Один рядок для кожної інформаційної схеми в каталозі |
| DATA_TYPE_DESCRIPTOR | Один рядок для кожного домену або стовпця, що має якийсь тип даних |
| DOMAINS | Один рядок для кожного домену |
| DOMAIN_CONSTRAINTS | Один рядок для кожного обмежувального умови, накладеного на домен |
| TABLES | Один рядок для кожної таблиці або подання |
| VIEWS | Один рядок для кожного подання |
| COLUMNS | Один рядок для кожного стовпця в кожному визначенні таблиці або подання |
| VIEW_TABLE_USAGE | Один рядок для кожної таблиці, на яку є посилання в будь-якому визначенні подання (якщо визначенням подання є багато табличні запити, кожна таблиця буде представлена окремим рядком) |
| VIEW_COLUMN_USAGE | Один рядок для кожного стовпця, на який є посилання в будь-якому поданні |
| TABLE_CONSTRAINTS | Один рядок для кожного обмежувального умови, заданого в будь-якому визначенні таблиці |
| KEY_COLUMN_USAGE | Один рядок для кожного стовпця, на який накладено умова унікальності і який присутній у визначенні первинного або зовнішнього ключа (якщо у визначенні ключа або умови унікальності зазначено кілька стовпців, то це визначення буде представлено декількома рядками) |
| REFERENTIAL_CONSTRAINTS | Один рядок для кожного визначення зовнішнього ключа, присутнього у визначенні таблиці |
| CHECK_CONSTRAINTS | Один рядок для кожної умови перевірки, заданого у визначенні таблиці |
| CHECK_TABLE_USAGE | Один рядок для кожної таблиці, на яку є посилання в умови перевірки, обмеженому умови для домену або затвердження |
| CHECK_COLUMN_USAGE | Один рядок для кожного стовпця, на який є посилання в умови перевірки, обмеженому умови для домену або затвердження |
| ASSERTIONS | Один рядок для кожного заданого твердження |
| TABLE_PRIVILEGES | Один рядок для кожної привілеї, наданої на будь-яку таблицю |
| COLUMN_PRIVILEGES | Один рядок для кожної привілеї, наданої на який-небудь стовпець |
| USAGE_PRIVILEGES | Один рядок для кожної привілеї, наданої на який-небудь домен, набір символів і т.п. |
| CHARACTER_SETS | Один рядок для кожного заданого набору символів |
| COLLATIONS | Один рядок для кожної заданої послідовності порівняння |
| TRANSLATIONS | Один рядок для кожного заданого перетворення |
| SQL_LANGUAGES | Один рядок для кожної мови (наприклад, COBOL, С і т.д.), підтримуваного СУБД даного типу |
На практиці комерційні реляційні СУБД підтримують стандартні подання каталогу шляхом створення відповідних подань на основі таблиць своїх власних системних каталогів. Інформація в системних каталогах більшості СУБД досить близька до необхідної в стандарті, тому визначення стандартних уявлень каталогу, що створюються в цих СУБД, будуть відносно простими.
Уявлення системного каталогу, необхідні стандартом SQL2, наведені у REF _Ref45512048 \ h \ * MERGEFORMAT табл. 11.2
табл. STYLEREF 1 \ s 11. SEQ табл. \ * ARABIC \ s 1 лютого Уявлення системного каталогу, встановлені стандартом SQL2
| Подання в системному каталозі | Вміст |
| INFORMATION_SСНЕМА_CATALOG_NAME | Один рядок з ім'ям бази даних для кожного користувача ("каталогу" за термінологією стандарту SQL2), описуваного даної інформаційної схемою |
| SCHEMATA | Один рядок для кожної інформаційної схеми в базі даних, що належить поточному користувачеві; містить ім'я схеми, набір символів за замовчуванням і т.д. |
| DOMAINS | Один рядок для кожного домену, доступного активного користувача; містить ім'я домену, базовий тип даних, набір символів, максимальну довжину, ступінь, точність і т.д. |
| DOMAIN_CONSTRAINTS | Один рядок для кожного обмежувального умови домену; містить ім'я умови і його характеристики |
| TABLES | Один рядок для кожної таблиці або подання, доступних користувачеві; містить ім'я й ознака того, чи йде мова про таблиці або поданні |
| VIEWS | Один рядок для кожного подання, доступного користувачу; містить ім'я, інформацію про режим контролю та можливості оновлення. |
| COLUMNS | Один рядок для кожного стовпця, доступного користувачу; містить ім'я стовпця, ім'я таблиці або подання, які містять даний стовпець, тип містяться в ньому, ступінь, точність, набір символів і т.д. |
| TABLE_PRIVILEGES | Один рядок для. Кожної привілеї на таблицю, наданої користувачеві або наданої їм іншому користувачеві; містить ім'я таблиці, тип привілеї, вказівка на те, хто надав привілей, кому вона надана і чи має користувач право надання цього привілею |
| COLUMN_PRIVILEGES | Один рядок для кожної привілеї на стовпець, наданої користувачеві або наданої їм іншому користувачеві; містить ім'я таблиці та стовпця, тип привілеї, вказівка на те, хто надав привілей, кому вона надана і чи має користувач право надання цього привілею |
| USAGE_PRIVILEGES | Один рядок для кожної привілеї, наданої користувачеві або користувачем на будь-якої домен, набір символів і т.п. |
| TABLE_CONSTRAINTS | Один рядок на кожне обмежувальне умова (первинний ключ, зовнішній ключ, умова унікальності чи умова перевірки), заданий для таблиці, якою володіє користувач; містить ім'я умови і таблиці, тип умови і його характеристики |
| REFERENTIAL_CONSTRAINTS | Один рядок для кожного посилального обмеження (визначення зовнішнього ключа) на таблицю, якою володіє користувач; містить ім'я обмеження, ім'я таблиці-нащадка і ім'я таблиці-предка |
| CHECK__CONSTRAINTS | Один рядок на кожне умова перевірки для таблиці, якою володіє користувач |
| KEY_COLUMN_USAGE | Один рядок для кожного стовпця первинного або зовнішнього ключа, на який (стовпець) накладено), умова унікальності і який входить в таблицю, що належить користувачеві; рядок містить ім'я таблиці, ім'я стовпця та позицію стовпця в ключі |
| ASSERTIONS | Один рядок для кожного твердження, яким володіє користувач; містить ім'я утвердження і його характеристики |
| CHARACTER_SETS | Один рядок для кожного визначення набору символів, доступного користувачу |
| COLLATIONS | Один рядок для кожного визначення послідовності порівняння, доступного користувачу |
| TRANSLATIONS | Один рядок для кожного визначення перетворення, доступного користувачу |
| VIEW_TABLE_USAGE | Один рядок для кожної таблиці, на яку є посилання у визначеннях уявлень, що належать користувачу; рядок містить ім'я таблиці |
| VIEW_COLUMN_USAGE | Один рядок для кожного стовпця, на який є посилання в уявленнях, що належать користувачу; рядок містить ім'я стовпця і таблиці, в яку входить стовпець |
| CONSTRAINT_TABLE_ USAGE | Один рядок для кожної таблиці, на яку є посилання в умови перевірки, умови унікальності, затвердження та визначенні зовнішнього ключа, що належать користувачеві |
| CONSTRAINT_COLUMN_ USAGE | Один рядок для кожного стовпця, на який є посилання в умови перевірки, умови унікальності, затвердження та визначенні зовнішнього ключа, що належать користувачеві |
| SQL_LANGUAGES | Один рядок для кожної мови (наприклад, COBOL, С і т.д.), підтримуваного СУБД даного типу; в рядку вказується рівень відповідності мови стандарту SQL2, тип підтримуваного діалекту SQL і т.д. |
| Системний домен | Область значень домену |
| SQL_IDENTIFIER | Домен всіх символьних рядків змінної довжини, які є допустимими ідентифікаторами SQL відповідно до стандарту SQL2. Будь-яке значення, взяте з цього (домену, є допустимим ім'ям таблиці, ім'ям стовпця і т.д. |
| CHARACTER_DATA | Домен всіх символьних рядків змінної довжини, що мають довжину від нуля до максимального значення, підтримуваного даної СУБД. Значення, взяте з цього домену, є допустимими символьним рядком. |
| CARDINAL_NUMBER | Домен всіх невід'ємних чисел від нуля до максимального цілого числа, з яким може працювати дана СУБД. Значення, взяте з цього домену, є нулем або допустимим позитивним числом. |
1. Вивести імена всіх таблиць і уявлень користувача, що працює зараз з базою даних.
SELECT TABLE_NAME
FROM TABLES
2. Вивести ім'я, позицію і тип даних для кожного стовпця у всіх виставах.
SELECT TABLE_NAME, С. COLUMN_NAME, ORDINAL_POSITION, DATAJTYPE
FROM COLUMNS
WHERE (COLUMNS.TABLE_NAME IN (SELECT TABLE_NAME FROM VIEWS))
3. Визначити, скільки стовпців є в таблиці STUDENTS.
SELECT COUNT (*)
FROM COLUMNS
WHERE (TABLE_NAME = 'STUDENTS')
Література:
1. Джеймс Р. Грофф, Пол Н. Вайнберг. SQL: повне керівництво: пер.с англ. -К.: Видавнича група BHV, 2000.-608с. Стор. 295-346.
ЛЕКЦІЯ 12. Забезпечення безпеки БД
12.1 Загальні положення12.2 Методи забезпечення безпеки
12.3 Виборче управління доступом
12.4 Обов'язкове управління доступом
12.5 Шифрування даних
12.6 Контрольний слід виконуваних операцій
12.7 Підтримка заходів забезпечення безпеки в мові SQL
12.8 Директиви GRANT і REVOKE
12.9 Подання та безпека
12.1 Загальні положення
Терміни безпеку і цілісність у контексті обговорення баз даних часто використовується спільно, хоча насправді, це зовсім різні поняття. Термін безпеку відноситься до захисту даних від несанкціонованого доступу, зміни або руйнування даних, а цілісність - до точності або істинності даних. По-іншому їх можна описати таким чином:1. під безпекою мається на увазі, що користувачам дозволяється виконувати деякі дії;
2. під цілісністю мається на увазі, що ці дії виконуються коректно.
Між ними є, звичайно, деяку схожість, оскільки як при забезпеченні безпеки, так і при забезпеченні цілісності система змушена перевірити, чи не порушують виконувані користувачем дії деяких правил. Ці правила повинні бути задані (зазвичай адміністратором бази даних) на деякому зручному для цього мові і збережені в системному каталозі. Причому в обох випадках СУБД повинна якимось чином відстежувати всі дії користувача і перевіряти їх відповідність заданими правилами.
Серед численних аспектів проблеми безпеки необхідно відзначити наступні:
1. Правові, громадські та етичні аспекти (чи має право деякий особа може отримати інформацію, наприклад про оцінки студента).
2. Фізичні умови (наприклад, чи закритий даний комп'ютер або термінальна кімната або захищений яким-небудь іншим чином).
3. Організаційні питання (наприклад, як у рамках підприємства, що володіє якоюсь системою, організований доступ до даних).
4. Питання реалізації управління (наприклад, якщо використовується метод доступу за паролем, то як організовано реалізацію управління і як часто змінюються паролі).
5. Апаратне забезпечення (чи забезпечуються заходи безпеки на апаратному рівні, наприклад, за допомогою захисних ключів або привілейованого режиму управління).
6. Безпека операційної системи (наприклад, затирає чи базова операційна система утримання структури зберігання і файлів з даними при припиненні роботи з ними).
7. І нарешті, деякі питання, що стосуються безпосередньо самої системи управління базами даних (наприклад, чи існує для бази даних деяка концепція надання прав володіння даними).
У цій лекції розглядаються питання, що стосуються останнього пункту цього переліку.
12.2 Методи забезпечення безпеки
У сучасних СУБД підтримується один з двох широко поширених підходів до питання забезпечення безпеки даних, а саме виборчий підхід чи обов'язкова підхід. В обох підходах одиницею даних або "об'єктом даних", для яких повинна бути створена система безпеки, може бути як вся база даних повністю або який-небудь набір відносин, так і деяке значення даних для заданого атрибуту усередині деякого кортежу в певному відношенні. Ці підходи відрізняються наступними властивостями:1. У разі виборчого управління якийсь користувач володіє різними правами (привілеями чи повноваженнями) при роботі з різними об'єктами. Більше того, різні користувачі зазвичай володіють і різними правами доступу до одного і того ж об'єкту. Тому виборчі схеми характеризуються значною гнучкістю.
2. У випадку обов'язкового управління, навпаки, кожному об'єкту даних присвоюється певний класифікаційний рівень, а кожен користувач має деяким рівнем допуску. Отже, при такому підході доступом до певного об'єкту даних мають тільки користувачі з відповідним рівнем допуску. Тому обов'язкові схеми досить жорсткі і статичні.
Незалежно від того, які схеми використовуються - виборчі або обов'язкові, всі рішення щодо допуску користувачів до виконання тих чи інших операцій приймаються на стратегічному, а не технічному рівні. Тому вони знаходяться за межами досяжності самої СУБД, і все, що може в такій ситуації зробити СУБД, - це тільки привести в дію вже прийняті раніше рішення. Виходячи з цього, можна відзначити наступне:
По-перше. Результати стратегічних рішень повинні бути відомі системі (тобто виконані на основі тверджень, заданих за допомогою деякого відповідного мови) і зберігатися в ній (шляхом збереження їх у каталозі у вигляді правил безпеки, які також називаються повноваженнями).
По-друге. Очевидно, повинні бути деякі засоби регулювання запитів доступу по відношенню до відповідних правил безпеки. (Тут під "запитом, доступу" мається на увазі комбінація запитуваної операції, запитуваної, об'єкта і запитуючої користувача.) Така перевірка виконується підсистемою безпеки СУБД, яка також називається підсистемою повноважень.
По-третє. Для того щоб розібратися, які правила безпеки до яких запитах доступу застосовуються, в системі повинні бути передбачені способи пізнання джерела цього запиту, тобто впізнання запитуючої користувача. Тому в момент входу в систему від користувача звичайно потрібно ввести не тільки його ідентифікатор (наприклад, ім'я або посаду), але також і пароль (щоб підтвердити свої права на заявлені раніше ідентифікаційні дані). Звичайно передбачається, що пароль відомий лише системі і деяким особам з особливими правами.
Щодо останнього пункту варто помітити, що різні користувачі можуть володіти одним і тим же ідентифікатором деякої групи. Таким чином, в системі можуть підтримуватися групи користувачів і забезпечуватися однакові права доступу для користувачів однієї групи, наприклад для всіх осіб з розрахункового відділу. Крім того, операції додавання окремих користувачів в групу або їх видалення з неї можуть виконуватися незалежно від операції завдання привілеїв для цієї групи. Зверніть увагу, однак, що місцем зберігання інформації про належність до групи також є системний каталог (або, можливо, база даних).
Перераховані вище методи управління доступом насправді є частиною більш загальної класифікації рівнів безпеки. Насамперед у цих документах визначається чотири класи безпеки (security classes) - D, С, В і А. Серед них клас D найменш безпечний, клас С - більш безпечний, ніж клас D, і т.д. Клас D забезпечує мінімальний захист, клас С - виборчу, клас В - обов'язкову, а клас А - перевірену захист.
База даних |
1.2.1 Дані.
Дані в базі даних є інтегрованими і, як правило, загальними. Поняття інтегрованих даних передбачає можливість подання бази даних як об'єднання декількох окремих файлів даних, повністю або частково не перекриваються. Поняття загальні увазі можливість використання окремих областей даних, у базі даних кількома різними користувачами.1.2.2 Апаратне забезпечення.
До апаратного забезпечення системи відносяться накопичувачі для зберігання інформації, разом з пристроями введення-виведення, контролерами пристроїв тощо; обчислювальна техніка, яка використовується для підтримки роботи ПЗ системи.1.2.3 Програмне забезпечення.
Програмне забезпечення є проміжним шаром між власне фізичної базою даних та користувачами системи і називається диспетчером бази даних або системою управління базами даних, СУБД (DBMS). Всі запити користувачів обробляються СУБД.Таким чином, СУБД - це спеціалізоване програмне забезпечення, що надає користувачеві бази даних можливість працювати з нею, не вникаючи в деталі зберігання інформації на рівні програмного забезпечення.
1.2.4 Користувачі.
Прикладні програмісти - відповідають за написання прикладних програм, що використовують базу даних.Кінцеві користувачі - працюють з базою даних безпосередньо, через робочу станцію або термінал. Кінцевий користувач може отримати доступ до бази даних використовуючи відповідне прикладне ПЗ.
Адміністратори бази даних - технічні фахівці, що здійснюють створення БД, технічний контроль роботи СУБД і ін операції. Адміністратори бази даних відповідають за реалізацію рішень адміністратора даних. Адміністратор даних вирішує, які дані необхідно зберігати в БД, забезпечує підтримання порядку при обслуговуванні та використанні збережених у БД даних.
Функції адміністратора бази даних:
1. визначення концептуальної схеми. Адміністратор БД визначає які саме дані необхідно зберігати в БД. Цей процес зазвичай називають логічним (або концептуальним) проектуванням БД. Після визначення вмісту БД на абстрактному рівні, адміністратор БД створює відповідну концептуальну схему, за допомогою концептуального ЯОД.
2. Визначення внутрішньої схеми. Адміністратор БД вирішує, як дані повинні бути представлені у збереженій БД. Цей процес називають фізичним проектуванням. Після завершення фізичного проектування адміністратор баз даних за допомогою внутрішнього ЯОД повинен створити відповідну структуру зберігання, а також визначити відображення між внутрішньою та концептуальною схемою.
3. Взаємодія з користувачами. Адміністратор БД забезпечує користувачів необхідними їм даними. Для цього адміністратор БД повинен написати (або надати користувачам допомогу в написанні) необхідних зовнішніх схем. Крім цього необхідно визначити відображення між зовнішньою та концептуальної схемами.
4. Визначення правил безпеки і цілісності.
5. Визначення процедур резервного копіювання і відновлення.
6. Управління продуктивністю та реагування на вимоги.
База даних складається з певного набору постійних даних, які використовуються прикладними системами будь-якого підприємства. Під словом "постійні" маються на увазі дані, які відрізняються від інших, більш мінливих даних, таких, як проміжні дані і взагалі всі транзитні дані. "Постійні" дані насправді можуть недовго залишатися такими, оскільки дані в БД повинні відображати про мінливих об'єктах реального світу і стосунки між ними.
Використання баз даних для зберігання інформації дозволяє організувати централізоване керування даними, що забезпечує наступні переваги:
1. можливість скорочення надмірності;
2. можливість усунення (до деякої міри) суперечливості;
3. можливість загального доступу до даних;
4. можливість дотримання стандартів;
5. можливість введення обмежень для забезпечення безпеки
6. можливість забезпечення цілісності даних;
7. можливість збалансувати суперечливі вимоги;
8. можливість забезпечення незалежності даних. Оскільки програмне забезпечення відокремлюється від даних, що зберігаються БД, зміни, що вносяться до структури БД, в більшості випадків не призводять до необхідності внесення радикальних змін в програмне забезпечення.
1.3 Рівні абстракції в СУБД. Функції абстрактних даних
Існує 3 рівні архітектури СУБД (REF _Ref9908383 \ * MERGEFORMAT рис. 1.2):1. Внутрішній рівень - найбільш близький до фізичного зберігання. Він пов'язаний зі способами зберігання інформації на фізичних пристроях зберігання;
2. Зовнішній рівень - найбільш близький до користувачів. Він пов'язаний із способами подання даних для окремих користувачів;
3. Концептуальний рівень - є проміжним між двома першими. Цей рівень пов'язаний з узагальненими уявленнями користувачів, на відміну від зовнішнього рівня, пов'язаного з індивідуальними уявленнями користувачів.
1.4 Уявлення
Відповідно трьома рівнями архітектури виділяють три рівні абстракції даних в СУБД.1.4.1 Зовнішній рівень - зовнішнє уявлення
Зовнішній рівень - індивідуальний рівень користувача. Користувач може бути як прикладним програмістом, так і кінцевим користувачем з будь-яким рівнем професійної підготовки. Кожен користувач має свою мову спілкування з СУБД. Для програміста - це будь-якої мова програмування, для користувача - мова запитів або мова, заснований на формах і меню. Будь-який з цих мов включає под'язик даних, тобто безліч операторів всієї мови, пов'язане тільки з об'єктами і операціями баз даних. Т.ч. под'язик даних вбудований в базовий мову користувача, який також забезпечує на пов'язані з БД можливості.Подання окремого користувача про БД на зовнішньому рівні архітектури називають зовнішнім поданням. Т.ч. зовнішнє подання - це вміст БД, яким його бачить окремий користувач. Наприклад, співробітник відділу кадрів бачить БД як набір записів про співробітників плюс набір записів про підрозділи. У загальному випадку зовнішнє подання складається з безлічі екземплярів кожного типу зовнішньої записи, які не обов'язково збігаються з збереженими записами. Под'язик даних користувача визначений у термінах зовнішніх записів. Кожне зовнішнє подання визначається засобами зовнішньої схеми, яка, в основному, складається з визначень кожного типу записів у зовнішньому поданні.
1.4.2 Концептуальний рівень - концептуальне уявлення
Концептуальне подання - це подання всієї інформації БД в дещо більш абстрактній формі в порівнянні з фізичним способом зберігання даних. Концептуальне уявлення представляє дані такими, якими вони є насправді, а не такими, якими їх змушений бачити користувач у рамках певної мови. Концептуальне уявлення складається з безлічі екземплярів кожного типу концептуальної запису, хоча в деяких системах у способи концептуального представлення даних можуть бути іншими - наприклад, у вигляді об'єктів і зв'язків між ними. Концептуальне уявлення визначається засобами концептуальної схеми, яка складається з визначень кожного типу концептуальних записів. При визначенні концептуальної схеми використовується концептуальний мова визначення даних, визначення якого відносяться тільки до змісту інформації. Концептуальне уявлення, таким чином забезпечує незалежність даних від способу їх зберігання.1.4.3 Внутрішній рівень - внутрішнє представлення
Внутрішнє подання - це подання нижнього рівня всієї БД. Воно складається з безлічі екземплярів кожного типу внутрішньої запису. Внутрішня запис відповідає збереженої запису. Внутрішнє подання не пов'язане з фізичним рівнем і в ньому не розглядаються фізичні запису. Внутрішнє подання передбачає існування нескінченного лінійного адресного простору. Подробиці відображення цього простору на фізичні пристрої зберігання не включені в загальну архітектуру через сильній залежності від системи.Внутрішнє представлення описується за допомогою внутрішньої схеми, яка описується за допомогою внутрішнього мови визначення даних.
Між трьома рівнями уявлень є два рівні відображень. Відображення концептуального рівня на внутрішній і зовнішнього рівня на концептуальний. Відображення зберігають незалежність даних разі внесення до структури БД змін.
1.5 Функції СУБД
1. Визначення даних. СУБД повинна допускати визначення даних (зовнішні схеми, концептуальну та внутрішню схеми, відповідні відображення). Для цього СУБД включає в себе мовний процесор для різних мов визначень даних.2. Обробка даних. СУБД повинна обробляти запити користувача на вибірку, а також модифікацію даних. Для цього СУБД включає в себе компоненти процесора мови обробки даних.
3. Безпека та цілісність даних. СУБД повинна контролювати запити і припиняти спроби порушення правил безпеки і цілісності.
4. Відновлення даних і дублювання. СУБД повинна забезпечити відновлення даних після збоїв.
5. Словник даних. СУБД повинна забезпечити функцію словника даних. Сам словник можна вважати системної базою даних, яка містить дані про дані користувача БД, тобто містить визначення інших об'єктів системи. Словник інтегрований в визначається ним БД і, тому, містить опис самого себе.
6. Продуктивність. СУБД повинна виконувати свої функції з максимальною продуктивністю.
1.6 Експертні системи і бази знань
Останнім часом з'явилася необхідність зберігання та використання слабоструктурованих даних, якими є, наприклад, людські знання в експертних системах.Експертна система - система штучного інтелекту, що включає знання про певну слабо структурованої і важко формалізується вузькій предметній області і здатна пропонувати і пояснювати користувачеві розумні рішення. Експертна система складається з бази знань, механізму логічного висновку і підсистеми пояснень.
База знань - семантична модель, що описує предметну область і дозволяє відповідати на такі питання з цієї предметної області, відповіді на які в явному вигляді не присутні в базі. База знань є основним компонентом інтелектуальних та експертних систем.
Для зберігання баз знань в сучасних експертних системах використовуються або промислові СУБД і спеціалізоване проміжне ПЗ, або спеціалізоване ПЗ.
У цьому курсі основна увага приділяється проектуванню БД і організації зберігання в них структурованої інформації. Проектування та створення баз знань буде детально розглядатися в інших курсах, пов'язаних з вивченням інтелектуальних систем.
Література:
1. Дейт К.Дж. Введення в системи баз даних. -Пер. з англ. -6-е вид. -К. Діалектика, 1998. Стор. 36-75.
ЛЕКЦІЯ 2. Моделі БД
2.1 Огляд ранніх (дореляціонних) СУБД2.2 Системи, засновані на інвертованих списках
2.3 Ієрархічна модель
2.4 Мережева модель
2.5 Основні переваги і недоліки ранніх СУБД
2.1 Огляд ранніх (дореляціонних) СУБД
Розглянемо системи, історично передували реляційних, що необхідно для правильного розуміння причин повсюдного переходу до реляційних систем. Крім того, внутрішня організація реляційних систем багато в чому заснована на використанні методів ранніх систем. І, нарешті, деяке знання в області ранніх систем буде корисно для розуміння шляхів розвитку постреляціонних СУБД.Обмежимося розглядом тільки загальних підходів до організації трьох типів ранніх систем, а саме, систем, заснованих на інвертованих списках, ієрархічних і мережевих систем управління базами даних. Не будемо торкатися особливостей будь-яких конкретних систем, оскільки це призвело б до викладу багатьох технічних деталей. Деталі можна знайти у відповідній літературі.
Розглянемо деякі найбільш загальні характеристики ранніх систем:
1. Ці системи активно використовувалися протягом багатьох років, довше, ніж використовується багато з реляційних СУБД. Насправді деякі з ранніх систем використовуються навіть у наш час, накопичені величезні бази даних, і однією з актуальних проблем інформаційних систем є використання цих систем спільно з сучасними системами.
2. Усі ранні системи не грунтувалися на будь-яких абстрактних моделях. Поняття моделі даних фактично увійшло в побут фахівців в області БД тільки разом з реляційних підходом. Абстрактні уявлення ранніх систем з'явилися пізніше на основі аналізу та виявлення загальних ознак у різних конкретних систем.
3. У ранніх системах доступ до БД проводився на рівні записів. Користувачі цих систем здійснювали явну навігацію в БД, використовуючи мови програмування, розширені функціями СУБД. Інтерактивний доступ до БД підтримувався тільки шляхом створення відповідних прикладних програм з власним інтерфейсом.
Виборча захист. Клас С ділиться на два підкласи - С1 і С2 (де підклас С1 менш безпечний, ніж підклас С2), які підтримують виборче керування доступом в тому сенсі, що управління доступом здійснюється на розсуд власника даних.
Згідно з вимогами класу С1 необхідно поділ даних і користувача, тобто поряд з підтримкою концепції взаємного доступу до даних тут можливо також організувати спільне використання даних користувачами.
Згідно з вимогами класу С2 необхідно додатково організувати облік на основі процедур входу в систему, аудиту та ізоляції ресурсів.
Обов'язковий захист. Клас В містить вимоги до методів обов'язкового управління доступом і ділиться на три підкласи - В1, В2 і В3 (де В1 є найменш, а В3 - найбільш безпечним підкласом).
Згідно з вимогами класу В1 необхідно забезпечити "зазначену захист" (це означає, що кожен об'єкт даних повинен містити позначку про його рівень класифікації, наприклад: секретно, для службового користування і т.д.), а також неформальне повідомлення про діючої стратегії безпеки.
Згідно з вимогами класу В2 необхідно додатково забезпечити формальне твердження про діючої стратегії безпеки, а також виявити і виключити погано захищені канали передачі інформації.
Згідно з вимогами класу В3 необхідно додатково забезпечити підтримку аудиту та відновлення даних, а також призначення адміністратора режиму безпеки.
Перевірена захист. Клас А є найбільш безпечним і згідно з його вимогами необхідно математичне доказ того, що даний метод забезпечення безпеки сумісний і адекватний заданої стратегії безпеки.
Хоча деякі комерційні СУБД забезпечують обов'язковий захист на рівні класу В1, зазвичай вони забезпечують виборче керування на рівні класу С2.
12.3 Виборче управління доступом
Виборче управління доступом підтримується багатьма СУБД. Виборче управління доступом підтримується в мові SQL.У загальному випадку система безпеки таких СУБД базується на трьох компонентах:
1. Користувачі. СУБД виконує будь-яке дії з БД від імені якогось користувача. Кожному користувачеві присвоюється ідентифікатор - коротке ім'я, що однозначно визначає користувача в СУБД. Для підтвердження того, що користувач може працювати з введеним ідентифікатором використовується пароль. Таким чином, за допомогою ідентифікатора і пароля проводиться ідентифікація та аутентифікація користувача. Більшість комерційних СУБД дозволяє об'єднувати користувачів з однаковими привілеями у групи - це дозволяє спростити процес адміністрування.
2. Об'єкти БД. За стандартом SQL2 захищеними об'єктами в БД є таблиці, подання, домени і певні користувачем набори символів. Більшість комерційних СУБД розширює список об'єктів, додаючи в нього збережені процедури та інші об'єкти.
3. Привілеї. Привілеї показують набір дій, які можливо проводити над тим чи іншим об'єктом. Наприклад користувач має привілей для перегляду таблиці.
12.4 Обов'язкове управління доступом
Методи обов'язкового управління доступом застосовуються до баз даних, в яких дані мають досить статичну або жорстку структуру, властиву, наприклад, урядовим чи військовим організаціям. Як вже зазначалося, основна ідея полягає в тому, що кожен об'єкт даних має деякий рівень класифікації, наприклад: секретно, цілком таємно, для службового користування і т.д., а кожен користувач має рівень допуску з такими ж градаціями, що і в рівні класифікації. Передбачається, що ці рівні утворюють строгий ієрархічний порядок, наприклад: цілком таємно ® таємно ® для службового користування і т.д. Тоді на основі цих даних можна сформулювати два дуже простих правил безпеки:1. користувач має доступ до об'єкта, тільки якщо його рівень допуску більше або дорівнює рівню класифікації об'єкта.
2. користувач може модифікувати об'єкту, тільки якщо його рівень допуску дорівнює рівню класифікації об'єкта.
Правило 1 досить очевидно, а правило 2 вимагає додаткових роз'яснень. Перш за все слід зазначити, що по-іншому друге правило можна сформулювати так: будь-яка інформація, записана деяким користувачем, автоматично набуває рівень, рівний рівню класифікації цього користувача. Таке правило необхідно, наприклад, для того, щоб запобігти запис секретних даних, виконувану користувачем з рівнем допуску "таємно", в файл з меншим рівнем класифікації, що порушує всю систему секретності.
Останнім часом методи обов'язкового управління доступом отримали широке поширення. Вимоги до такого управління доступом викладені в двох документах, які неформально називаються "помаранчевої" книгою (Orange Book) і "рожевої" книгою (Lavender Book). У "помаранчевої" книзі перерахований набір вимог до безпеки для якоїсь "надiйної обчислювальної бази" (Trusted Computing Base), а в "рожевої" книзі дається інтерпретація цих вимог для систем управління базами даних.
12.5 Шифрування даних
До цих пір в цій главі малося на увазі, що передбачуваний нелегальний користувач намагається незаконно проникнути в базу даних за допомогою звичайних засобів доступу, що є в системі. Тепер слід розглянути випадок, коли такий користувач намагається проникнути в базу даних, минаючи систему, тобто фізично переміщаючи частину бази даних або підключаючись до комунікаційного каналу. Найбільш ефективним методом боротьби з такими загрозами є шифрування даних, тобто зберігання та передавання особливо важливих даних у зашифрованому вигляді.Для обговорення основних концепцій кодування даних слід ввести деякі нові поняття. Вихідні (незакодірованний) дані називаються відкритим текстом. Відкритий текст шифрується за допомогою спеціального алгоритму шифрування. В якості вхідних даних для такого алгоритму виступають відкритий текст і ключ шифрування, а в якості вихідних - зашифрована форма відкритого тексту, яка називається зашифрованим текстом. Якщо деталі алгоритму шифрування можуть бути опубліковані або, принаймні, можуть не приховуватись, то ключ шифрування обов'язково зберігається в секреті. Саме зашифрований текст, який незрозумілий тим, хто не володіє ключем шифрування, зберігається в базі даних і передається з комунікаційного каналу.
12.6 Контрольний слід виконуваних операцій
Важливо розуміти, що не буває невразливих систем безпеки, оскільки наполегливий потенційний порушник завжди зможе знайти спосіб подолання всіх систем контролю, особливо якщо за це буде запропоновано досить високу винагороду. Тому при роботі з дуже важливими даними або при виконанні критичних операцій виникає необхідність реєстрації контрольного сліду виконуваних операцій. Якщо, наприклад, суперечливість даних призводить до підозрою, що скоєно несанкціоноване втручання в базу даних, то контрольний слід повинен бути використаний для прояснення ситуації і підтвердження того, що всі процеси перебувають під контролем. Якщо це не так, то контрольний слід допоможе, принаймні, виявити порушника.Для збереження контрольного сліду зазвичай використовується особливий файл, в якому система автоматично записує всі виконані користувачами операції при роботі зі звичайною базою даних. Типова запис у файлі контрольного сліду може містити таку інформацію:
1. запит (вихідний текст запиту);
2. термінал, з якого була викликана операція;
3. користувач, який загадав операцію;
4. дата і час запуску операції;
5. залучені до процесу виконання операції базові відносини, кортежі та атрибути;
6. старі значення;
7. нові значення.
Як вже згадувалося раніше, навіть констатація факту, що в даній системі підтримується контрольне спостереження, в деяких випадках вельми істотна для запобігання несанкціонованого проникнення в систему.
12.7 Підтримка заходів забезпечення безпеки в мові SQL
У чинному стандарті мови SQL передбачається підтримка тільки виборчого управління доступом. Вона заснована на двох більш-менш незалежних частинах SQL. Одна з них називається механізмом уявлень, який (як говорилося вище) може бути використаний для приховування дуже важливих даних від несанкціонованих користувачів. Інша називається підсистемою повноважень і наділяє одних користувачів правом вибірково і динамічно задавати різні повноваження іншим користувачам, а також відбирати такі повноваження у разі необхідності.12.8 Директиви GRANT і REVOKE
Механізм уявлень мови SQL дозволяє різними способами розділити базу даних на частини таким чином, щоб деяка інформація була прихована від користувачів, які не мають прав для доступу до неї. Однак цей режим задається не за допомогою параметрів операцій, на основі яких санкціоновані користувачі виконують ті чи інші дії із заданою частиною даних. Ця функція (як було показано вище) виконується за допомогою директиви GRANT.Зверніть увагу, що творцеві будь-якого об'єкту автоматично надаються всі привілеї стосовно цього об'єкта.
Стандарт SQL1 визначає наступні привілеї для таблиць:
1. SELECT - дозволяє зчитувати дані з таблиці або подання;
2. INSERT - дозволяє вставляти нові записи в таблицю або подання;
3. UPDATE - дозволяє модифікувати записи з таблиці або подання;
4. DELETE - дозволяє видаляти записи з таблиці або подання.
Стандарт SQL2 розширив список привілеїв для таблиць і уявлень:
1. INSERT для окремих стовпців, подібно привілеї UPDATE;
2. REFERENCES - для підтримки зовнішнього ключа.
Крім перерахованих вище добавлена привілей USAGE - для інших об'єктів бази даних.
Крім того, більшість комерційних СУБД підтримує додаткові привілеї, наприклад:
1. ALTER - дозволяє модифікувати структуру таблиць (DB2, Oracle);
2. EXECUTE - дозволяє виконувати збережені процедури.
Творець об'єкта також отримує право надати привілеї доступу якомусь іншому користувачеві за допомогою оператора GRANT. Нижче наводиться синтаксис затвердження GRANT:
GRANT {SELECT | INSERT | DELETE | (UPDATE стовпець, ...)}, ...
ON таблиця ТО {користувач | PUBLIC} [WITH GRANT OPTION]
Привілеї вставки (INSERT) та оновлення (UPDATE) (але не привілеї вибору SELECT, що досить дивно) можуть задаватися для спеціально заданих стовпчиків.
Якщо задана директива WITH GRANT OPTION, це означає, що зазначені користувачі наділені особливими повноваженнями для заданого об'єкта - правом надання повноважень. Це, у свою чергу, означає, що для роботи з даним об'єктом вони можуть наділяти повноваженнями інших користувачів
Наприклад: надати користувачеві Ivanov повноваження для здійснення вибірки і модифікації прізвищ у таблиці Students з правом надання повноважень.
GRANT SELECT, UPDATE StName
ON Students ТО Ivanov WITH GRANT OPTION
Якщо користувач А наділяє деякими повноваженнями іншого користувача В, то згодом він може скасувати ці повноваження для користувача В. Скасування повноважень виконується за допомогою директиви REVOKE з наведеним нижче синтаксисом.
REVOKE {{SELECT | INSERT | DELETE | UPDATE}, ... | ALL PRIVILEGES}
ON таблиця, ... FROM {користувач | PUBLIC}, ... {CASCADE | RESTRICT}
Оскільки користувач, з якого знімається привілей, міг надати її іншому користувачеві (якщо володів правом надання повноважень), можливе виникнення ситуації покинутих привілеїв. Основне призначення параметрів RESTRICT і CASCADE полягає в запобіганні ситуацій з виникненням покинутих привілеїв. Завдяки завданням параметра RESTRICT не дозволяється виконувати операцію скасування привілеї, якщо вона призводить до появи покинутої привілеї. Параметр CASCADE вказує на послідовну скасування всіх привілеїв, похідних від запропонованої.
Наприклад: зняти з користувача Ivanov повноваження для здійснення модифікації прізвищ у таблиці Students. Також зняти цей привілей з усіх користувачів, яким вона була надана Івановим.
REVOKE UPDATE
ON Students FROM Ivanov CASCADE
В імені домену, таблиці, стовпця або подання автоматично будуть видалені також і всі привілеї стосовно цих об'єктів з боку всіх користувачів.
12.9 Подання та безпека
Створюючи подання, і даючи користувачам дозвіл на доступ до нього, а не до початкової таблиці, можна тим самим обмежити доступ користувача, дозволивши його лише до заданих стовпцях або записів. Таким чином, уявлення дозволяють здійснити повний контроль над тим, які дані доступні тому або іншому користувачеві.Продемонструємо використання подань для забезпечення безпеки за допомогою описаних мовою SQL прикладів.
1. Створення подання для доступу до даних групи А-98-51.
CREATE VIEW GroupA98 AS
SELECT *
FROM Students INNER JOIN Groups ON Students.GrNo = Groups.GrNo
WHERE Groups.GrName = 'A-98-51'
2. Надання повноважень користувачу Ivanov.
GRANT SELECT, INSERT, DELETE, UPDATE
ON GroupA98 ТО Ivanov
Тепер користувач Ivanov може переглядати і модифікувати тільки дані групи А-98-51.
Література:
1. Дейт К.Дж. Введення в системи баз даних. -Пер. з англ. -6-е вид. -К. Діалектика, 1998. Стор. 394 - 410.
2. Джеймс Р. Грофф, Пол Н. Вайнберг. SQL: повне керівництво: пер.с англ. -К.: Видавнича група BHV, 2000.-608с. Стор. 329-368.
ЛЕКЦІЯ 13. Фізична організація БД: структури зберігання і методи доступу
13.1 Доступ до бази даних13.2 Кластеризація
13.3 Індексування
13.4 Структури типу Б-дерева
13.5 Хешування
13.1 Доступ до бази даних
У цій лекції розглядаються технології фізичного зберігання даних зберігаються на диску і здійснення доступу до них. (Далі під терміном диск будуть матися на увазі всі пристрої зберігання даних прямого доступу, тобто перш за все традиційні жорсткі диски з рухомими магнітними головками, зовнішні запам'ятовувальні пристрої великої ємності, оптичні диски і ін)Увага фахівців до структур зберігання та методів доступу викликано дуже повільної зовнішньою пам'яттю. Основним способом підвищення продуктивності є мінімізація числа дискових операцій введення-виведення даних.
Як згадувалося раніше, будь-яке упорядкування (розташування) даних на диску називається структурою зберігання. Можна організувати найрізноманітніші структури зберігання, володіють різною продуктивністю і оптимальні для різних способів використання. Однак не існує ідеальної структури зберігання, яка була б оптимальною для будь-яких завдань. Виходячи з цього, можна зробити висновок, що досконала СУБД повинна містити кілька різних структур зберігання для різних частин системи. Крім того, слід також передбачити можливість зміни структури зберігання в міру зміни вимог до продуктивності системи.
Робота СУБД побудована таким чином і включає три основних етапи (REF _Ref11658268 \ h \ * MERGEFORMAT рис. 13.1
СУБД |
Диспетчер файлів |
Диспетчер дисків |
Збережена БД |
Повернення збереженої запису |
Запит зберігається запису |
Запит збереженої сторінки |
Дискова операція вводу / виводу |
Повернення збереженої сторінки |
Читання даних з диска |
1. Спочатку в СУБД визначається шукана запис, а потім для її витягання запитується диспетчер файлів.
2. Диспетчер файлів визначає сторінку, на якій знаходиться шукана запис, а потім для вилучення цієї сторінки запитує диспетчер дисків.
3. 3. Нарешті, диспетчер дисків визначає фізичне положення шуканої сторінки на диску і посилає відповідний запит на введення-виведення даних.
Диспетчер дисків. Диспетчер дисків є компонентом операційної системи, за допомогою якого виконуються всі дискові операції вводу-виводу (в деяких операційних системах він називається компонентом базової системи введення-виведення).
Для виконання цих операцій необхідно знати значення фізичних адрес на диску. Наприклад, якщо диспетчер файлів запитує деяку сторінку диспетчеру дисків необхідно знати, де конкретно знаходиться сторінка на фізичному диску. Проте "користувачеві" диспетчера дисків, тобто диспетчеру файлів, не обов'язково знати фізичні адреси. Замість цього диспетчеру файлів досить розглядати диск як набір сторінок фіксованого розміру (тобто набір "осередків" пам'яті однакового розміру) з унікальним ідентифікаційним номером набору сторінок. Кожна сторінка, у свою чергу, володіє унікальним всередині даного набору ідентифікаційним номером сторінки, причому набори не мають спільних сторінок. Відповідність фізичних адрес на диску і номерів сторінок досягається за допомогою диспетчера дисків. Головним (і не єдиним) перевагою такої організації є ізоляція програмного коду, що залежить від конкретного пристрою для диску, усередині одного з компонентів системи, а саме всередині диспетчера дисків. У такому випадку всі компоненти високого рівня, зокрема диспетчера файлів, можуть бути апаратно незалежними.
Диспетчер файлів. При роботі з диском як набором файлів, що зберігаються, диспетчер файлів використовує всі наявні засоби диспетчера дисків згідно з визначеним в СУБД способу. При цьому кожен набір сторінок може містити один або кілька файлів, що зберігаються.
Кожен зберігається файл має ім'я (file name) або ідентифікаційний номер (file ID), унікальні в цьому наборі сторінок. А кожна збережена запис, у свою чергу, володіє ідентифікаційним номером запису (record number або record ID), унікальним, принаймні, в межах даного зберігається файлу.
13.2 Кластеризація
Не можна завершити цей короткий огляд без згадки технології кластеризації. В її основі лежить принцип якомога більш близького фізичного розміщення на диску логічно пов'язаних між собою і часто використовуваних даних. Фізична кластеризація даних - надзвичайно важлива умова високої продуктивності, що можна продемонструвати таким прикладом. Припустимо, що найбільш часто використовується зберігається запис r1 сторінки p1, для роботи з якою також потрібно викликати збережену запис r2 сторінки p2. Тоді можливе виникнення наступних ситуацій:1. Якщо сторінки р1 і р2 збігаються, то для доступу до запису r2 не буде потрібно виконувати ще одну фізичну операцію введення-виведення, оскільки потрібна сторінка вже буде знаходитися в оперативній пам'яті.
2. Якщо сторінки р1 і р2 не збігаються, але фізично розміщуються досить близько, наприклад суміжні сторінки, то для доступу до запису r2 буде потрібно виконати ще одну фізичну операцію введення-виведення (якщо, звичайно, сторінка p2 ще не знаходиться в оперативній пам'яті). Однак, оскільки головка читання / запису вже буде знаходитися в безпосередній близькості від потрібного положення, час пошуку буде дуже малим. А якщо сторінки р1 і р2 знаходяться на одному циліндрі, час пошуку взагалі дорівнюватиме нулю.
Внутріфайловую і межфайловую кластеризацію СУБД може здійснювати, розміщуючи логічно пов'язані запису на одній сторінці (якщо це можливо) або на суміжних сторінках (у протилежному випадку).
Кластеризація всередині СУБД можлива тільки в тому випадку, якщо адміністратор бази даних організує її. У скоєних СУБД часто передбачено завдання декількох різних типів кластеризації даних з різних файлів.
13.3 Індексування
Розглянемо як приклад таблицю з даними про студентів, а також часто використовується і тому дуже важливий запит типу "Знайти усіх студентів учнів у групі X", де X - якийсь параметр. За таких умов адміністратор бази даних може вибрати спосіб збереження даних, схематично показаний на REF _Ref11659310 \ h \ * MERGEFORMAT рис. 13.2Для пошуку всіх студентів з групи Б-99-51 можна застосувати таку стратегію: знайти у файлі груп групу Б-99-51, а потім згідно з вказівниками витягти всі відповідні записи з файлу студентів.
Така стратегія буде більш ефективною в порівнянні з пошуком у файлі з даними студентів, оскільки, СУБД відома фізична послідовність записів у файлі груп (пошук буде припинений після вилучення наступного за Б-98-51 назви групи в алфавітному порядку). Крім того, навіть якщо доведеться переглянути файл груп повністю, для такого пошуку потрібно значно менше операцій введення-виведення, оскільки фізичний розмір файлу груп менше, ніж розмір файлу з даними студентів з-за меншого розміру записів.
У розглянутому прикладі файл груп називається індексним файлом або індексом по відношенню до файлу студентів, і навпаки, файл студентів індексований (називається індексованих файлом) по відношенню до файлу груп.
Індексний файл - це файл зберігається особливого типу, в якому кожен запис складається з двох значень, а саме даних і покажчика. Дані відповідають деякому полю (індексному полю) з індексованого файлу, а покажчик служить для зв'язування з відповідним записом індексованого файлу. Індексне полі також називається індексним ключем (index key).
Індекс можна порівняти з предметним покажчиком звичайної книги, який складається зі списку слів з "покажчиками" (номерами сторінок) для спрощення пошуку пов'язаної з цими словами інформації з "індексованого файлу" (тобто з вмісту книги).
Основною перевагою використання індексів є значне прискорення процесу вибірки або вилучення даних, а основним недоліком - уповільнення процесу оновлення даних, оскільки при кожному додаванні нового запису в індексований файл потрібно також додати новий індекс у індексний файл.
Зберігається файл може мати кілька індексів, які можуть як роздільно, так і спільно використовуватися для більш ефективного доступу до записів про постачальників.
Індекси часто називають інвертованими списками. Справа в тому, що якщо файл студентів (див. REF _Ref11659310 \ h \ * MERGEFORMAT рис. 13.2
Індекс можна також створити на основі комбінації двох або більше полів. Наприклад, на REF _Ref11659383 \ h \ * MERGEFORMAT рис. 13.3
| |||||||||||||||||||||||||||||||||||||||||||||||||
Зверніть увагу, що комбінований індекс GrName / City може також служити індексом по одному полю GrName, оскільки всі записи в комбінованому індексі розташовані послідовно.
13.3.1 Щільне і нещільне індексування
Основною метою використання індексу є прискорення процесу вилучення даних, точніше, зменшення числа дискових операцій введення-виведення, необхідних для отримання необхідної запису. В основному це досягається завдяки використанню покажчиків. Хоча до цих пір передбачалося, що в цій якості використовуються покажчики записів, насправді для цього достатньо було б покажчиків сторінок (тобто номерів сторінок). Звичайно, для подальшого пошуку запису всередині даної сторінки доведеться здійснити ще одну операцію вилучення запису, проте тепер вона буде виконуватися в оперативній пам'яті і для цього не доведеться збільшувати число дискових операцій введення-виведення.Цю ідею можна розвинути далі, якщо згадати, що дані в кожному доглянутому файл знаходяться в єдиній "фізичної" послідовності на основі комбінації послідовності збережених записів всередині кожної сторінки і послідовності сторінок всередині кожного набору сторінок. Припустимо, що фізична послідовність файлу студентів відповідає логічній послідовності, заданої на основі деякого поля, наприклад номери студента. Інакше кажучи, в цьому файлі виконана кластеризація по даному полю. Припустимо, що з цього ж поля здійснюється індексування; тоді немає необхідності в даному індексі зберігати покажчики для кожного запису індексуємого файлу (у даному випадку для файлу студентів). Все, що потрібно, - це дороговказ для кожної сторінки, що складається з максимального номери студента для даної сторінки і відповідного номера сторінки. Схематично така структура показана на REF _Ref11659408 \ h \ * MERGEFORMAT
рис. 13.4
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
В якості прикладу розглянемо процес вилучення запису з номером 3 за допомогою такого індексу. Спочатку в СУБД проводиться пошук індексу для запису з номером, більшим чи рівним 3. При цьому буде знайдено поле з номером 4, яке містить покажчик на сторінку p. Сторінка p витягується, поміщається в оперативну пам'ять і проглядається для пошуку заданої збереженої запису (яка в даному прикладі буде знайдена дуже швидко).
Індекс з описаною структурою називається нещільним (або розрядженим), оскільки в ньому не містяться покажчики на записи індексованого файлу. Схематично приклад такого індексу показаний на REF _Ref11659408 \ h \ * MERGEFORMAT
рис. 13.4
Слід зазначити, що в даному доглянутому файлі може бути принаймні один нещільний індекс, який організовується на основі (унікальної) фізичної послідовності, заданої у файлі. А всі інші індекси обов'язково повинні бути щільними.
13.4 Структури типу Б-дерева
Одним з найбільш важливих і поширених індексів є структура типу Б-дерева (B-tree).Причина необхідності створення структури типу Б-дерева полягає в бажанні уникнути обов'язкового перегляду всього вмісту індексованого файлу згідно з його фізичної послідовності. Справа в тому, що якщо індексований файл має великий розмір, то і його індекс також дуже великий. Тому послідовний перегляд навіть одного тільки індексу вимагає великих витрат часу. Вирішити цю проблему можна тим же способом, що й раніше: розглянути індексний файл як звичайний зберігається файл і створити для нього ще один індекс. Цю операцію можна здійснювати повторно потрібну кількість разів (зазвичай вона застосовується тричі, оскільки створення великої кількості ієрархічних рівнів індексування потрібно для дуже великих файлів). При цьому індекс на кожному з рівнів буде нещільним по відношенню до нижнього індексуємого рівнем (він обов'язково повинен бути нещільним, інакше така структура безглузда, так як рівень n містив би таку ж кількість записів, що і рівень n +1, а для перегляду треба було б таке ж тривалий час).
Структура типу Б-дерева є окремим випадком індексу деревовидного типу і вперше описана в статті Баєра (Вауег) і Мак-Крайт (McCreight) у 1972 році. З тих пір Байєром та іншими дослідниками було запропоновано безліч варіантів реалізації цієї ідеї. У результаті бінарні індекси різних типів стали широко використовуватися у всіх сучасних СУБД.
У варіанті Кнута індекс складається з двох частин:
1. Набір послідовностей включає однорівневий індекс для реальних даних, що звичайно є щільним, але може бути і нещільним, якщо в індексувати файли проведена кластеризація на основі індексу
2. Набір індексів, у свою чергу, забезпечує швидкий безпосередній доступ до набору послідовностей (а значить, і до даних). По суті, набір індексів є деревовидним індексним файлом для набору послідовностей або, суворо кажучи, індексом зі структурою Б-дерева. Комбінація набору індексів і набору послідовностей називається структурою типу Б-плюс-дерева (B-plus tree або B-tree). На REF _Ref11637502 \ h \ * MERGEFORMAT рис. 13.5
Числа 6, 8, 12, ... 97, 99 є значеннями індексованого поля F. Кореневий елемент містить два значення поля F (50 і 82) та три покажчики (номери сторінок). Дані зі значенням поля F, що дорівнює або менше 50, можуть бути знайдені за допомогою лівого покажчика; дані зі значенням поля F, великим 50 і рівним або меншим 82, - за допомогою середнього покажчика; нарешті, дані зі значенням поля F, великим 82, - за допомогою правого покажчика. Інші елементи набору індексів слід інтерпретувати подібним чином. Зверніть увагу, що завдяки переходу на другий рівень по лівому вказівником надалі пошук по правому вказівником буде здійснюватися до всіх записів зі значенням поля F, великим 32 і рівним або меншим 50.
Взагалі, Б-дерево порядку п містить не менше п і не більше 2п записів з даними в кожному з елементів структури (для кожних k записів потрібне також k +1 покажчиків). Крім того, жодна із записів не може використовуватися двома різними елементами.
Одним з недоліків ієрархічних структур є незбалансованість їх роботи після видалення або вставки деяких елементів. Справа в тому, що в результаті таких змін структури елементи з реальними даними можуть опинитися на різних рівнях і на різних відстанях від кореневого елемента. Оскільки під час пошуку при кожному відвідуванні елементів структури відбувається звернення до диска, загальна тривалість пошуку в незбалансованої деревоподібної структурі може виявитися зовсім непередбачуваною.
Перевагою структури типу Б-дерева є можливість збалансованої вставки або видалення значень. (Ось чому для англійського написання такого індексу, "B-tree", іноді вживають замість символу "В" епітет від "збалансований" (balanced).) Нижче наводиться короткий алгоритм вставки нового значення V в структуру типу Б-дерева порядку п. Він розрахований на вставку значення тільки лише в набір індексів, але може бути досить просто розширений для вставки запису з даними в набір послідовностей.
1. На самому низькому рівні набору індексів слід знайти елемент (припустимо, що це елемент N), з яким логічно пов'язане вставляється значення V. Якщо елемент N містить вільний простір, то значення V вставляється в нього і на цьому процес завершується.
2. В іншому випадку (якщо вільного простору немає, тобто доведеться створити ще один рівень) елемент N (припустимо, що він містить 2n індексних записів) поділяється на два елементи - N1 і N2. Позначимо символом 5 безліч з 2n +1 значень, в якому 2n вихідних значень і одне нове значення V. Тоді n перших значень цієї логічної (вже впорядкованої) послідовності необхідно помістити в елемент N1, n останніх - в елемент N2, а середнє між ними значення W-в батьківський елемент Р на більш високому структурному рівні. Згодом, при здійсненні пошуку значення U і досягненні елементу P, пошук буде перенаправлений в бік елемента N1, якщо V <W, або у бік елемента N2, якщо U> W.
3. Далі цей процес слід повторити для вставки середнього значення W в батьківський елемент Р на більш високому структурному рівні.
У гіршому випадку процес поділу елементів структури може тривати аж до кореневого елемента всієї структури з утворенням нового ієрархічного рівня.
Для видалення деякого значення слід застосувати аналогічний алгоритм, але тільки у зворотному порядку. А для зміни значення його можна видалити, а потім вставити нове.
|
13.5 Хешування
Хешування, хеш-адресацією або хеш-індексуванням називається технологія швидкого прямого доступу до збереженої запису на основі заданого значення деякого поля. При цьому не обов'язково, щоб поле було ключовим.Нижче перераховані основні риси цієї технології:
1. кожна збережена запис бази даних розміщується за адресою, який обчислюється за допомогою спеціальної хеш-функції на основі значення деякого поля цього запису, тобто хеш-поля, або хеш-ключа. Обчислений адреса називається хеш-адресою.
2. для збереження запису в СУБД спочатку обчислюється хеш-адресу нового запису, а потім диспетчер файлів поміщає цю запис по обчисленому адресою.
3. Для витягання потрібної запису по заданому значенню хеш-поля в СУБД спочатку обчислюється хеш-адресу, а потім диспетчеру файлів надсилається запит для вилучення запису за обчисленому адресою.
Як простий ілюстрації припустимо, що у нас є записи з даними про студентів з кодами 100, 200, 300, 400, 500, а в якості хеш-функції h використовується наступна: h = StNo mod 13, де h - хеш-адресу, StNo - код студента.
Це найпростіший приклад загального класу хеш-функцій типу поділ / залишок. (Як дільника слід вибирати просте натуральне число). У цьому прикладі хеш-адресами для заданих записів будуть 9, 5, 1, 10 і 6 відповідно. Схематично взаємне розташування записів на сторінках показано на REF _Ref11637535 \ h \ * MERGEFORMAT рис. 13.6
| 0 | 1 | Іванов | ... | ... | 2 | 3 | 4 | ||||||||
| 300 | |||||||||||||||
| 5 | Петров | ... | ... | 6 | Сидоров | ... | ... | 7 | 8 | 9 | Стрільців | ... | ... | ||
| 200 | 500 | 100 | |||||||||||||
| 10 | Кузнєцов | ... | ... | 11 | 12 | ||||||||||
| 400 | |||||||||||||||
Недоліком хешування є можливість виникнення колізій, тобто ситуацій, коли дві або більше різних записи мають однакові адреси. Припустимо, що файл студентів з попереднього прикладу (із записами 100, 200 і т.д.) містить також запис з номером 1400. При використанні хеш-функції h = StNo mod 13 виникне колізія (за адресою 9) з записом 100.
Для вирішення таких колізій можна використовувати значення хеш-функції в якості адреси не який-небудь записи з даними, а точки прив'язки. Точка прив'язки - це початковий адресу ланцюжка покажчиків (ланцюжки колізій), що зв'язує разом усі записи або всі сторінки із записами, які викликають колізії за адресою. Усередині ланцюжка колізій запису також можуть бути упорядковані відповідно до хеш-полем для спрощення подальшого пошуку.
Один з недоліків описаного вище способу хешування - зростання числа колізій із збільшенням розміру зберігається файлу. Це, у свою чергу, може призвести до значного збільшення середнього часу доступу, оскільки все більше і більше часу доведеться витрачати на пошук записів у наборах конфліктуючих записів. Однак цей недолік можна усунути, якщо реорганізувати файл, тобто вивантажити даний файл і завантажити його знову, використовуючи нову хеш-функцію.
Існує ряд модифікацій алгоритму хешування, наприклад, розширюване хешування та ін, що застосовуються для оптимізації операцій оновлення і пошуку в БД.
Література:
1. Дейт К.Дж. Введення в системи баз даних. -Пер. з англ. -6-е вид. -К. Діалектика, 1998. Стор. 674-696.
ЛЕКЦІЯ 14. Оптимізація запитів
14.1 Оптимізація в реляційних СУБД.14.2 Приклад оптимізації реляційного вираження
14.3 Огляд процесу оптимізації
14.4 Перетворення виразів
14.1 Оптимізація в реляційних СУБД.
Для реляційних систем оптимізація є як проблемою, так і можливістю підвищення продуктивності. Проблема оптимізації полягає в тому, що деякі системи для досягнення певного рівня продуктивності вимагають оптимізації. Оптимізація дозволяє поліпшити роботу системи, так як однією з сильних сторін реляційного підходу є те, що перше застосування оптимізації до реляційному висловом переводить цей вираз на більш ефективний семантичний рівень. Загальне призначення оптимізатора полягає у виборі ефективної стратегії для обчислення цього реляційного вираження.Перевага автоматичної оптимізації полягає в тому, що користувач може не замислюватися над найкращим способом вираження своїх запитів (тобто над тим, як сформулювати запит, щоб система виконала його з максимально можливою продуктивністю). Але це далеко не все. Існує реальна можливість, що оптимізатор сформулює запит краще, ніж програміст-користувач. Для такого твердження є низка причин. Нижче наведено лише деякі з них:
1. Хороший оптимізатор - зверніть увагу на слово "гарний" - володіє достатньою кількістю інформації, якої користувач може не мати. Точніше, оптимізатор повинен володіти деякими статистичними даними, такими як кардинальне число кожного базового відносини, кількість розрізняються значень для кожного атрибуту в відношенні, кількість входжень кожного значення в атрибутах і т.п. Завдяки наявності цих даних оптимізатор здатний більш точно оцінювати ефективність будь-якої стратегії реалізації конкретного запиту. Тому оптимізатор зможе вибрати найкращу стратегію реалізації запиту.
2. Якщо з часом статистика бази даних значно зміниться (наприклад, база даних буде фізично реорганізована), то для реалізації запиту може знадобитися зовсім інша стратегія, ніж до реорганізації. Іншими словами, може знадобитися повторна оптимізація, або реоптімізація. У реляційних системах процес реоптімізаціі досить тривіальний - це просто повторна обробка початкового запиту системним оптимізатором. З іншого боку, в нереляційних системах реоптімізація вимагає переписування програми, і, можливо, нездійсненна взагалі.
3. Оптимізатор - це програма, тому він більше "наполегливий" у порівнянні з людиною. Оптимізатор цілком здатний розглядати буквально сотні різних стратегій реалізації даного запиту, в той час як програміст навряд чи вивчає більше трьох-чотирьох стратегій (принаймні, досить глибоко).
4. У оптимізатор вбудовані знання і досвід "кращих з кращих" програмістів, що робить ці знання і досвід доступними для всіх. Природно, у противному випадку широкому колу користувачів буде наданий явно недостатній набір дешевих і неефективних можливостей.
14.2 Приклад оптимізації реляційного вираження
Почнемо переказ з простого прикладу, що дає уявлення про результати, які можна отримати за допомогою оптимізації. Розглянемо запит "Отримати список прізвищ студентів, які у групі А-98-51". Алгебраїчна запис цього запиту така:((Students JOIN Groups) WHERE GrName = 'А-98-51') [StName]
Припустимо, що база даних містить інформацію про 100 групах і 10000 студентів, тільки 30 з яких навчаються у групі А-98-51. У такому випадку, якщо система буде обчислювати вираз прямо (тобто взагалі без оптимізації), то послідовність виконуваних дій буде виглядати так:
1. З'єднання відносин Students і Groups (по атрибуту GrNo). На цьому етапі зчитується інформація про 10000 студентів і 10000 раз зчитується інформація про 100 групах (один раз для кожного студента). Після цього створюється проміжний результат, що складається з 10000 з'єднаних кортежів.
2. Вибірка кортежів з даними тільки про групу А-98-51 з результату, отриманого на етапі 1. На цьому етапі створюється нове ставлення, яке складається з 30 кортежів.
3. Проекція результату, отриманого на етапі 2, по атрибуту StName. На цьому етапі створюється необхідний результат, що складається з 30 кортежів.
Показана нижче процедура еквівалентна описаної в тому сенсі, що обов'язково створить той же кінцевий результат, але більш ефективним способом:
1. Вибірка кортежів з даними тільки про групу А-98-51 з відносини Groups. На цьому етапі виконується читання 100 кортежів і створюється результат, що складається тільки з 1 кортежу.
2. З'єднання результату, отриманого на етапі 1, з відношенням Students (по атрибуту GrNo). На цьому етапі виконується зчитування даних про 10000 студентів і 10000 раз зчитується інформація про групу А-98-51, отримана на 1 етапі. Результат містить 30 кортежів.
3. Проектування результату, отриманого на етапі 2, по атрибуту StName (аналогічно етапу 3 попередньої послідовності дій). Необхідний результат містить 30 кортежів.
Перша з показаних процедур виконує в загальному 1010000 операцій введення-виведення кортежу, в той час як друга процедура виконує тільки 20000 операції введення-виведення. Отже, якщо взяти "кількість операції введення-виведення кортежу" в якості заходів продуктивності, то друга процедура в 50 разів ефективніше першою. (На практиці мірою продуктивності є кількість операцій введення-виведення сторінки, а не одного кортежу, але для цього прикладу цю поправку можна ігнорувати.)
14.3 Огляд процесу оптимізації
14.3.1 Стадія 1. Перетворення запиту у внутрішню форму
На цій стадії виконується перетворення запиту в деяке внутрішнє уявлення, більш зручне для машинних маніпуляцій. Це повністю виключає з розгляду конструкції зовнішнього рівня (такі як "гра слів" конкретного синтаксису аналізованого мови запитів) і готує грунт для подальших стадій оптимізації.Зазвичай внутрішнє подання запитів є певною модифікацією абстрактного синтаксичного дерева, або дерева запиту.
Наприклад, на малюнку показано дерево розглянутого вище в цьому розділі запиту ("Отримати список прізвищ студентів, які у групі А-98-51").
Остаточний результат |
Проекція по атрибуту StName |
Вибірка кортежів, в яких GrName = 'А-98-51' |
З'єднання по атрибут GrNo |
Groups |
Students |
14.3.2 Стадія 2. Перетворення в канонічну форму
На цій стадії оптимізатор виконує кілька операцій оптимізації, які "гарантовано є хорошими" незалежно від реальних даних, що зберігаються в базі даних, і шляхів доступу до них. Суть в тому, що всі запити (за винятком найпростіших) реляційні мови зазвичай дозволяють виразити декількома різними (принаймні, зовні) способами.Зауваження про канонічну формі. Поняття канонічної форми вживається, в багатьох розділах математики та пов'язаних з нею дисциплін. Канонічна форма може бути визначена наступним чином. Нехай Q - безліч об'єктів (запитів), і нехай існує поняття про еквівалентність цих об'єктів (а саме: запити q1 і q2 еквівалентні тоді і тільки тоді, коли дають ідентичні результати) Кажуть, що підмножина C безлічі Q є підмножиною канонічних форм для запитів з Q в сенсі певної вище еквівалентності тоді і тільки тоді, коли кожному об'єкту q з Q відповідає тільки один об'єкт c з C. Тоді кажуть, що об'єкт з є канонічною формою об'єкта q. Всі "цікавлять" властивості, якими володіє об'єкт q, також притаманні і об'єкту с. Тому, щоб довести різні "цікавлять" результати, досить вивчити менш потужне безліч об'єктів C, а не більш потужне безліч Q.
Щоб перетворити результати стадії 1 в деяку еквівалентну, але більш ефективну форму, оптимізатор використовує певні і добре відомі правила перетворення, або закони.
14.3.3 Стадія 3. Вибір потенційних низькорівневих процедур
Після перетворення внутрішньої форми запиту у більш відповідну (канонічну) форму оптимізатор повинен вирішити, як виконувати запит, поданий в канонічній формі. На цій стадії береться до уваги наявність індексів та інших шляхів доступу, розподіл збережених значень даних, фізична кластеризація збережених даних і т.п. Зауважте, що на стадіях 1 і 2 цих питань зовсім не приділялося увагиДля кожної низькорівневої операції оптимізатор має набір низькорівневих процедур реалізації.
Зауваження. З кожною процедурою також пов'язана вартісна формула, яка вказує "вартість" виконання процедури (тобто рівень необхідних витрат на її виконання). Зазвичай вартість обчислюється в контексті операцій введення-виведення з диска, але деякі системи враховують також час використання процесора і інші фактори. Ці вартісні формули використовуються на стадії 4.
Отже, далі за допомогою інформації з каталогу про стан бази даних (існуючі індекси, кардинальні числа відносин тощо) і даних про залежності, описаних вище, оптимізатор вибере одну або декілька процедур-кандидатів для кожної низькорівневої операції в запиті. Цей процес зазвичай називають вибором шляху доступу.
14.3.4 Стадія 4. Генерація планів обчислення запиту і вибір плану з найменшою вартістю
На останній стадії процесу оптимізації конструюються потенційні плани запитів, після чого слідує вибір кращого (тобто найменш дорогого) плану виконання запиту. Кожен план виконання будується як комбінація набору процедур реалізації, при цьому кожній низькорівневої операції в запиті відповідає одна процедура.Для вибору плану з найменшою вартістю необхідний метод прив'язки вартості до даного плану. В основному вартість плану - це просто сума вартостей окремих процедур, які використані для його виконання. Таким чином, робота оптимізатора зводиться до обчислення вартісних формул для кожної такої процедури. Проблема полягає в тому, що вартість виконання процедури залежить від розміру відносини (або відносин), яке обрана процедура обробляє.
14.4 Перетворення виразів
14.4.1 Вибірки і проекції
1. Послідовність вибірок даного відношення може бути перетворена в одну (об'єднану операцією AND) вибірку цього відношення. Наприклад, вираз(A WHERE виборка_1) WHERE виборка_2
еквівалентно висловом
A WHERE виборка_1 AND виборка_2
2. У послідовності проекцій даного відносини можна ігнорувати всі проекції, окрім останньої. Таким чином, вираз
(А [проекція_1]) [проекція_2]
еквівалентно висловом
А [Проекція_2]
Звичайно, щоб перший вираз мало сенс, кожен атрибут, який використовується в проекціі_2, повинен бути присутнім і в проекціі_1.
3. Вибірку проекції можна трансформувати в проекцію вибірки. Наприклад, вираз
(А [проекція]) WHERE вибірка
еквівалентно висловом
(A WHERE вибірка) [проекція]
Зауважте, що в основному завжди корисно виконувати операцію вибірки перед операцією проекції, так як вибірка призведе до зменшення розміру вхідних даних для операції проекції і, отже, до зменшення кількості даних, які потрібно сортувати для виключення дубльованих записів у процесі обчислення проекції.
14.4.2 Розподільний закон
Кажуть, що унарний оператор розподіляється по бінарної операції О, якщо для всіх А і В виконується умоваF (А О В) º f (А) Про f (В).
У реляційній алгебрі операція вибірки розподіляється за операціями об'єднання, перетину і віднімання. Операція вибірки також розподіляється по oneраціі з'єднання, але тільки тоді, коли умова вибірки складається (у самому складному випадку) з об'єднаних операцією AND двох окремих умов вибірки - по одному для кожного операнда операції з'єднання. Для розглянутого вище в цьому розділі прикладу сформульоване умова дотримана (умова вибірки дуже просте і відноситься лише до одного операнду), і можна використовувати розподільний закон для заміни аналізованого у прикладі вираження його більш ефективним еквівалентом. Чистий ефект цього закону полягає в тому, що можна виконувати "ранню вибірку". Виконання ранньої вибірки майже завжди себе виправдовує, оскільки призводить до значного зменшення кількості кортежів, які потрібно розглядати в такій операції. Крім того, рання вибірка може призвести до зменшення кількості кортежів і на виході наступної операції.
Далі наведено кілька більш специфічних прикладів розподільного закону, на цей раз з операцією проекції. По-перше, операція проекції розподіляється за операціями об'єднання та перетину (але не за операції віднімання). По-друге, ця операція також розподіляється по операції з'єднання, але тільки в тому випадку, якщо в проекцію включені всі атрибути з'єднання. Точніше, вираз
(A JOIN В) [проекція]
еквівалентно висловом
(А [А_проекція]) JOIN (В [В_проекція])
тоді і тільки тоді, коли безліч використаних в проекції атрибутів дорівнює об'єднанню множин атрибутів у А_проекціі і В_проекціі і включає атрибути, за якими виконано з'єднання. Цей закон можна використовувати для виконання ранніх "проекцій", які зазвичай себе виправдовують з тих же причин, що й операції вибірки.
14.4.3 Комутативність і асоціативність
Закони комутативності та асоціативності - це ще два загальні правила перетворення. Кажуть, що бінарна операція Про є комутативною, якщо для всіх А і В істинно рівністьА О В º У О А
Наприклад, у звичайній арифметиці операції множення і складання є комутативними, а операції ділення і віднімання - ні. У реляційній алгебрі комутативними є операції об'єднання, перетину і з'єднання, а операції віднімання і ділення такими не є.
Перейдемо до асоціативності. Прийнято вважати, що бінарна операція Про є асоціативною, якщо для всіх А, В і З істинно рівність
А О (В И) º (А О В) Про С.
Наприклад, у звичайній арифметиці твір і додавання - асоціативні операції, розподіл і віднімання - ні. У реляційній алгебрі асоціативними є операції об'єднання, перетину і з'єднання, а операції віднімання і ділення такими не є. Так, наприклад, якщо в запиті використовується поєднання трьох відносин, А, В і С, то із законів комутативності та асоціативності
14.4.4 Ідемпотентний
Ще одним важливим правилом є закон Ідемпотентний. Ідемпотентний називають таку бінарну операцію О, для якої для всіх А виконується рівністьA О А = А.
Можна очікувати, що властивість Ідемпотентний також може бути корисним у процесі трансформації виразів. У реляційній алгебрі операції об'єднання, перетину і з'єднання є Ідемпотентний, а операції ділення і віднімання - ні.
14.4.5 Обчислювані скалярні вирази
Предметом застосування законів трансформації є не тільки реляційні висловлювання. Наприклад, вже було показано, що деякі закони трансформації застосовні і до арифметичним виразам. Нижче наведено приклад. ВиразА * В + А * З
можна трансформувати у вираз
А * (В + С)
внаслідок того, що операція множення "*" розподіляється по операції додавання "+". Оптимізатор реляційних виразів повинен володіти інформацією про подібні перетвореннях, так як він враховує обчислювані скалярні вирази в контексті операцій EXTEND і SUMMARIZE.
Кажуть, що бінарна операція Про розподіляється по бінарної операції О, якщо для всіх А, В і З істинно рівність
A '(B Про C) = (A' B) O (A 'C)
(Для наведеного вище арифметичного прикладу замініть 'на "*", а О на "+").
14.4.6 Умови
Перейдемо до обговорення умов або виразів, результатами яких можуть бути істина або брехня. Припустимо, що А і В - атрибути двох різних відносин, тоді умоваА> У AND У> 3
(Яке може бути частиною запиту) абсолютно еквівалентно висловом
А> У AND У> 3 AND A> 3
і тому може бути перетворено в цей вираз.
Дана еквівалентність базується на тому, що операція ">" є транзитивної. Зауважте, що виконання подібного перетворення вельми корисно, тому що дозволяє системі створити додаткову вибірку (за допомогою умови "А> З") перед виконанням з'єднання "більше ніж", необхідного умовою "А> В".
Зауваження. Цей прийом реалізований в різних комерційних продуктах, включаючи систему DB2, в якій його називають транзитивним замиканням предикатів. А ось інший приклад. Умова
А> У OR (С = D AND Е <F)
можна перетворити в умову
(A> B OR С = D) AND (А> В OR Е <F)
внаслідок того, що операція OR розподіляється по операції AND. Цей приклад демонструє інший загальний закон: "Будь-яка умова може бути перетворено в еквівалентну умова, зване кон'юнктивній нормальною формою (КНФ)". КНФ-вираз має вигляд:
C1 AND C2 AND ... AND Cn,
де С1, C2, ..., Cn - умови (звані часткова сполучення), в яких не використовується операція AND. Перевага КНФ полягає в тому, що КНФ-вираз істинний, тільки якщо правдиві всі його часткові кон'юнкції. Аналогічно, КНФ-вираз помилково, якщо брехня є результатом хоча б однієї часткової кон'юнкції. Так як операція AND коммутативна (A AND У одно У AND А), то оптимізатор може обчислювати окремі часткові кон'юнкції в будь-якому порядку, зокрема за зростанням складності (спочатку прості). І як тільки знайдена часткова кон'юнкція, результатом якої є брехня, весь процес обчислення КНФ-вирази можна зупиняти.
Більше того, у середовищі паралельних обчислень можливе паралельне обчислення всіх часткових кон'юнкція. Знову ж таки, як тільки знайдена перша часткова кон'юнкція, результатом якої є брехня, весь процес обчислення КНФ-вирази можна зупиняти.
14.4.7 Семантичні перетворення
Розглянемо такий вираз:(Students JOIN Groups) [StName]
Дане з'єднання належить до сполук типу зовнішній-к-узгодженим-потенціал'ному-ключу. У цьому з'єднанні зовнішньому ключу щодо Students ставиться у відповідність потенційний ключ відносини Groups. Отже, кортеж у відношенні Students пов'язаний з певним кортежем щодо Groups. Таким чином, з кожного кортежу щодо Students в загальний результат надходить тільки значення атрибуту StName. Іншими словами, з'єднання можна не виконувати! Розглянуте вираз можна замінити виразом
Students [StName]
Перетворення, коректне чинності певної умови цілісності, називають семантичним перетворенням, а оптимізацію, отриману в результаті подібних перетворень, - семантичної оптимізацією. Семантичну оптимізацію можна визначити як процес перетворення запиту в інший, якісно відмінний запит, який, тим не менше, дає результат, ідентичний результату початкового запиту, завдяки тому що дані задовольняють певній умові цілісності.
Важливо розуміти, що в принципі будь-яка умова цілісності може бути використано для семантичної оптимізації (якщо ця умова не відстрочено і в даний момент діє на базу даних).
14.4.8 Статистики бази даних
На стадіях 3 і 4 загального процесу оптимізації (вони називаються стадіями "вибору шляху доступу") використовуються так звані статистики бази даних, які зберігаються в каталозі.Література:
1. Дейт К.Дж. Введення в системи баз даних. -Пер. з англ. -6-е вид. -К. Діалектика, 1998. Стор. 474-516.
ЛЕКЦІЯ 15. Відновлення після збоїв
15.1 Поняття відновлення системи15.2 Транзакції
15.3 Алгоритм відновлення після збою системи
15.4 Паралелізм. Проблеми паралелізму
15.5 Поняття блокування
15.6 Рішення проблем паралелізму
15.7 Статті без ситуації
15.8 Здатність до впорядкування
15.9 Рівні ізоляції транзакції
15.10 Підтримка в мові SQL
15.1 Поняття відновлення системи
Відновлення в системі управління базами даних, означає в першу чергу відновлення самої бази даних, тобто повернення бази даних в нормальний стан, якщо який-небудь збій зробив поточний стан неправильним або підозрілим. Основний принцип, на якому будується таке відновлення, - це надмірність. Надмірність організується на фізичному рівні. Така надмірність буде прихована від користувача, а отже, не видно на логічному рівні. Іншими словами, якщо будь-яка частина інформації, що міститься в базі даних, може бути реконструйована з іншої зберiгається в системі надлишкової інформації, значить, база даних відновлюваність.15.2 Транзакції
15.2.1 Поняття транзакції
Транзакція - це логічна одиниця роботи. Наприклад. Припустимо спочатку, що ставлення Students (відношення студентів) включає додатковий атрибут AvgMark, що представляє собою середній бал студента, за результатами складання поточної сесії. Значення AvgMark для будь-якої певної деталі передбачається рівним середньому арифметичному всіх значень Mark з таблиці Marks для всіх оцінок отриманих в поточному семестрі.У наведеному прикладі передбачається, що мова йде про одиночної, атомарної операції. Насправді додавання нової оцінки в таблицю Marks - це виконання двох оновлень в базі даних (під оновленнями тут, звичайно, розуміються операції insert, delete, а також самі по собі операції update). Більш того, в базі даних між цими двома оновленнями тимчасово порушується вимога, що значення AvgMark для студента 1 дорівнює середньому арифметичному всіх значень поля Mark для студента 1 у поточному семестрі. Таким чином, логічна одиниця роботи (тобто транзакція) - не просто одиночна операція системи баз даних, а скоріше погодження кількох таких операцій. Загалом, це перетворення одного узгодженого стану бази даних в інше, причому в проміжних точках база даних знаходиться в неузгодженому стані.
З цього випливає, що неприпустимо, щоб одне з оновлень було виконано, а інше немає, тому що база даних залишиться в неузгодженому стані. В ідеальному випадку повинні бути виконані обидва оновлення. Однак не можна забезпечити стовідсоткову гарантію, що так і буде. Не виключена ймовірність того, що, система, наприклад, буде зруйнована між двома оновленнями, або ж на другому оновленні відбудеться арифметичне переповнення і т.п. Система, що підтримує транзакції, гарантує, що якщо під час виконання якихось оновлень сталася помилка (за будь-якої причини), то всі ці оновлення будуть анульовані. Таким чином, транзакція або виконується повністю, або повністю скасовується (неначе вона взагалі не виконувалася).
Системний компонент, який забезпечує атомарность (або її подоба), називається адміністратором транзакцій (або диспетчером транзакцій), а ключами до його виконання служать оператори COMMIT TRANSACTION і ROLLBACK TRANSACTION.
Оператор COMMIT TRANSACTION (для стислості commit) сигналізує про успішне закінчення транзакції. Він повідомляє адміністратора транзакцій, що логічна одиниця роботи завершено успішно, база даних знову перебуває (або буде перебувати) у погодженому стані, а всі оновлення, виконані логічною одиницею роботи, тепер можуть бути зафіксовані, тобто стати постійними.
Оператор ROLLBACK TRANSACTION (для стислості ROLLBACK) сигналізує про невдалий закінчення транзакції. Він повідомляє адміністратора транзакцій, що сталася якась помилка, база даних знаходиться в неузгодженому стані і всі оновлення можуть бути скасовані, тобто анульовані.
Для скасування оновлень система підтримує файл реєстрації, або журнал, на диску, де записуються деталі всіх операцій оновлення, зокрема нове і старе значення модифікованого об'єкта. Таким чином, при необхідності скасування деякого оновлення система може використовувати відповідний файл реєстрації для повернення об'єкта в первинний стан.
Ще один важливий момент. Система повинна гарантувати, що індивідуальні оператори самі по собі атомарні (тобто виконуються повністю або не виконуються зовсім). Це особливо важливо для реляційних систем, в яких оператори багаторівневі і зазвичай оперують безліччю кортежів одночасно; такий оператор просто не може бути порушений посеред операції і привести систему в неузгоджене стан. Іншими словами, якщо сталася помилка під час роботи такого оператора, база даних повинна залишитися повністю незміненою. Більш того, це має бути справедливо навіть у тому випадку, коли дії оператора є причиною додаткової, наприклад каскадної, операції.
15.2.2 Відновлення транзакції.
Транзакція починається з успішного виконання оператора BEGIN TRANSACTION) і закінчується успішним виконанням небудь оператора COMMIT, або ROLLBACK. Оператор COMMIT встановлює так звану точку фіксації (яка в комерційних продуктах також називається точкою синхронізації (syncpoint). Точка фіксації відповідає кінця логічної одиниці роботи і, отже, точці, в якій база даних знаходиться (або буде перебувати) у стані узгодженості. На противагу цьому , виконання оператора ROLLBACK знову повертає базу даних у стан, в якому вона була під час операції BEGIN TRANSACTION, тобто в попередню точку фіксації.Випадки установки точки фіксації:
1. Всі оновлення, вчинені програмою з тих пір, як встановлена попередня точка фіксації, виконані, тобто стали постійними. Під час виконання всі такі оновлення можуть розцінюватися тільки як пробні (в тому сенсі, що вони можуть бути не виконані, наприклад прокручені тому). Гарантується, що одного разу зафіксоване оновлення так і залишиться зафіксованим (це і є визначення поняття "зафіксовано").
2. Всі позиціонування бази даних втрачено, і всі блокування кортежів реалізовані. Позиціонування бази даних тут означає, що у будь-який конкретний час програма зазвичай адресована певним кортежам. Ця адресовані точці фіксації втрачається.
Отже, система може виконати відкат транзакції як явно - наприклад по команді ПЗ з яким працює користувач, так і неявно - для будь-якої програми, яка за будь-якої причини не досягла запланованого завершення операцій, що входять до транзакцію.
З цього видно, що транзакції - це не тільки логічні одиниці роботи, але також і одиниці відновлення при невдалому виконанні операцій. При успішному завершенні транзакції система гарантує, що оновлення постійно встановлені в базі даних, навіть якщо система зазнає краху в наступний момент. Можливо, що в системі відбудеться збій після успішного виконання COMMIT, але перед тим, як, оновлення будуть фізично записано в базу даних (вони все ще можуть залишатися в буфері оперативної пам'яті і таким чином можуть бути загублені в момент збою системи). Навіть якщо подібне трапилося, процедура перезавантаження системи все одно повинна встановлювати ці оновлення в базу даних, досліджуючи відповідні записи у файлі реєстрації. З цього випливає, що файл реєстрації повинен бути фізично записаний перед завершенням операції COMMIT. Це важливе правило ведення файлу реєстрації відомо як протокол попереднього запису в журнал (тобто запис про операції здійснюється перед її виконанням). Таким чином, процедура перезавантаження зможе відновити будь-які успішно завершені транзакції, хоча їх оновлення не були записані фізично до аварійної відмови системи. Отже, як зазначалося раніше, транзакція дійсно є одиницею відновлення.
15.2.3 Властивості Асіда.
З попередніх розділів слід, що транзакції володіють чотирма важливими властивостями: атомарность, узгодженість, ізоляція і довговічність (назвемо це властивостями Асіда).1. Атомарність. Транзакції атомарний (виконується все або нічого).
2. Узгодженість. Транзакції захищають базу даних узгоджено. Це означає, що транзакції переводять одне узгоджене стан бази даних в інше без обов'язкової підтримки узгодженості у всіх проміжних точках.
3. Ізоляція. Транзакції відокремлені одна від одної. Це означає, що, якщо навіть буде запущено безліч конкурують один з одним транзакцій, будь-яке оновлення певної транзакції буде приховано від інших до тих пір, поки ця транзакція виконується. Іншими словами, для будь-яких двох віддалених транзакцій Т1 і Т2 справедливо наступне твердження: Т1 зможе побачити оновлення Т2 тільки після виконання Т2, а Т2 зможе побачити оновлення Т1 лише після виконання Т1.
4. Довговічність. Коли транзакція виконана, її оновлення зберігаються, навіть якщо в наступний момент станеться збій системи.
15.3 Алгоритм відновлення після збою системи
Система повинна бути готова до відновлення не тільки після невеликих локальних порушень, таких як невиконання операції в межах певної транзакції, а також і після глобальних порушень типу збоїв у харчуванні обчислювального пристрою та ін Місцевий порушення за визначенням вражає тільки транзакцію, в якій воно власне і відбулося. Глобальне порушення вражає відразу всі транзакції і, отже, призводить до значних для системи наслідків.Існує два види глобальних порушень:
1. Відмови системи (наприклад, збої в харчуванні), що вражають всі запущені в даний момент транзакції, але фізично не порушують базу даних в цілому. Такі порушення в системі також називають аварійним відмовою програмного забезпечення.
2. Відмови носіїв (наприклад, поломка головок дискового накопичувача), які можуть представляти загрозу для бази даних або для будь-якої її частини і вражати. принаймні, ті транзакції, які використовують цю частину бази даних. Відмови носіїв також називають аварійним відмовою апаратури.
15.3.1 Відновлення після відмов системи
Критичною точкою у відмові системи є втрата вмісту оперативної пам'яті (зокрема, робочих буферів бази даних). Оскільки точне стан будь-якої виконується в момент порушення транзакції не відомо, транзакція може не завершитися успішно і, таким чином, буде скасована при перезавантаженні системи.Більше того, можливо, буде потрібно повторно виконати певну успішно завершилася до аварійної відмови транзакцію при перезавантаженні системи, якщо не були фізично виконані оновлення цієї транзакції.
Для визначення під час перезавантаження, яку транзакцію скасувати, а яку виконати повторно система в деякому встановленому інтервалі (коли в журналі накопичується певна кількість записів) автоматично приймає контрольну точку. Прийняття контрольної точки включає фізичну запис вмісту робочих буферів бази даних безпосередньо в базу даних і спеціальну фізичну запис контрольної точки, яка надає список всіх здійснюваних в даний момент транзакцій. На REF _Ref9934890 \ h \ * MERGEFORMAT рис. 15.1
t c |
t f |
T1 |
T2 |
T3 |
T4 |
Контрольна точка (Час t c) |
Відмова системи (Час t f) |
Транзакції |
Час |
T5 |
Пояснення до REF _Ref9934890 \ h \ * MERGEFORMAT рис. 15.1
1. Відмова системи відбувся в момент часу tf.
2. Прилегла до моменту часу tf контрольна крапка була прийнята в момент часу tc.
3. Транзакція Т1 успішно завершена до моменту часу tc.
4. Транзакція Т2 розпочата до моменту часу tc і успішно завершена після моменту часу tc, але до моменту часу tf.
5. Транзакція ТЗ також розпочато до моменту часу tc, але не завершена на момент часу tf
6. Транзакція Т4 почата після моменту часу tc і успішно завершена до моменту часу tf.
7. Транзакція Т5 також розпочато після моменту часу tc, але не завершена на момент часу tf.
Очевидно, що при перезавантаженні системи транзакції типу ТЗ і Т5 повинні бути скасовані, а транзакції типу Т2 і Т4 - виконані повторно. Тим не менш зауважте, що транзакції типу Т1 взагалі не включаються в процес перезавантаження, тому що оновлення потрапили в базу даних ще до моменту часу tc (тобто зафіксовані ще до прийняття контрольної точки). Відзначте також, що транзакції, що завершилися невдало (в тому числі скасовані) перед моментом часу tf, взагалі не будуть залучені до процесу перезавантаження.
15.4 Паралелізм. Проблеми паралелізму
Термін паралелізм означає можливість одночасної обробки в СУБД багатьох транзакцій з доступом до одних і тих же даних, причому в один і той же час. У такій системі для коректної обробки паралельних транзакцій без виникнення конфліктних ситуацій необхідно використовувати певний метод управління паралелізмом.Кожен метод управління паралелізмом призначений для вирішення певної конкретної задачі. Тим не менш, при обробці правильно складених транзакцій виникають ситуації, які можуть призвести до отримання неправильного результату через взаємних перешкод серед деяких транзакцій. (Зверніть увагу, що вносить перешкоду транзакція сама по собі може бути правильною. Неправильний кінцевий результат виникає унаслідок безконтрольного чергування операцій з двох правильних транзакцій). Основні проблеми, що виникають при паралельній обробці транзакцій наступні:
1. проблема втрати результатів оновлення;
2. проблема незафіксованою залежності;
3. проблема несумісного аналізу.
15.4.1 Проблема втрати результатів поновлення
Розглянемо ситуацію, показану на REF _Ref10022190 \ h \ * MERGEFORMAT рис. 15.2| Транзакція A | Час | Транзакція B |
| Витяг кортежу р | t1 | - |
| - | t2 | Витяг кортежу р |
| Оновлення кортежу р | t3 | - |
| - | t4 | Оновлення кортежу р |
15.4.2 Проблема незафіксованою залежності
Проблема незафіксованою залежності з'являється, якщо за допомогою деякої транзакції здійснюється вилучення (або, що ще гірше, оновлення) деякого кортежу, який в даний момент оновлюється інший транзакцією, але це оновлення ще не закінчено. Таким чином, якщо оновлення не завершено, існує певна ймовірність того, що вона не буде завершена ніколи. (Більше того, у подібному випадку може бути виконаний повернення до попереднього стану кортежу зі скасуванням виконання транзакції.) B такому разі, в першій транзакції будуть приймати участь дані, яких більше не існує. Ця ситуація показана на REF _Ref10022022 \ h \ * MERGEFORMAT рис. 15.3У першому прикладі (REF _Ref10022022 \ h \ * MERGEFORMAT рис. 15.3
| Транзакція A | Час | Транзакція B |
| - | t1 | Оновлення кортежу р |
| Витяг кортежу р | t2 | - |
| - | t3 | Скасування виконання транзакції |
| Транзакція A | Час | Транзакція B |
| - | t1 | Оновлення кортежу р |
| Оновлення кортежу р | t2 | - |
| - | t3 | Скасування виконання транзакції |
Другий приклад, наведений на REF _Ref10022061 \ h \ * MERGEFORMAT рис. 15.4
15.4.3 Проблема несумісного аналізу
На REF _Ref10022275 \ h \ * MERGEFORMAT рис. 15.5табл. STYLEREF 1 \ s 15. SEQ табл. \ * ARABIC \ s 1 січня Залишки на рахунках до виконання транзакцій.
| Рахунок | РАХУНОК 1 | РАХУНОК 2 | РАХУНОК 3 |
| Залишок | 40 | 50 | 30 |
| Транзакція A | Час | Транзакція B |
| Витяг кортежу РАХУНОК 1: СУМА = 40 | t1 | - |
| Витяг кортежу РАХУНОК 1: СУМА = 90 | t2 | - |
| - | t3 | Витяг кортежу РАХУНОК 3: |
| - | t4 | Оновлення кортежу РАХУНОК 3: 30 ® 20 |
| - | t5 | Витяг кортежу РАХУНОК 1: |
| - | t6 | Оновлення кортежу РАХУНОК 1: 40 ® 50 |
| - | t7 | Завершення виконання транзакції |
| Витяг кортежу РАХУНОК 3: СУМА = 110 (а не 120) | t8 | - |
15.5 Поняття блокування
Описані вище проблеми можуть бути вирішені за допомогою методики управління паралельним виконанням процесів під назвою блокування. Її основна ідея дуже проста: у випадку, коли для виконання деякої транзакції необхідно, щоб деякий об'єкт (зазвичай це кортеж бази даних) не змінювався непередбачувано і без відома цієї транзакції (як це зазвичай буває), такий об'єкт блокується. Таким чином, ефект блокування полягає в тому, щоб "заблокувати доступ до цього об'єкту з боку інших транзакцій", а значить, запобігти непередбачуване зміна цього об'єкта. Отже, перша транзакція в змозі виконати всю необхідну обробку з урахуванням того, що оброблюваний об'єкт залишається у стабільному стані настільки довго, наскільки це потрібно.Припустимо, що в системі підтримується два типи блокувань: блокування без взаємного доступу (монопольне блокування), звана Х-блокуванням (X locks - exclusive locks), і блокування з взаємним доступом, звана S-блокуванням (S locks - Shared locks). Зауваження. Х-і S-блокування іноді називають блокуваннями запису і читання відповідно. Припустимо, що Х-і S-блокування єдино можливі, хоча в комерційних системах існують блокування інших типів. Крім того, припустимо, що в кортежі є єдиним типом "блокується об'єкта", хоча знову ж н в комерційних системах можуть блокуватися і інші об'єкти. Нижче показано функціонування механізму блокувань.
1. Якщо транзакція A блокує кортеж р без можливості взаємного доступу (Х-блокування), то запит іншої транзакції B з блокуванням цього кортежу p буде відмінено.
2. Якщо транзакція A блокує кортеж р з можливістю взаємного доступу (S-блокування), то:
2.1. запит з боку деякої транзакції B на Х-блокування кортежу буде знехтуваний;
2.2. запит з боку деякої транзакції B на S-блокування кортежу р буде прийнятий (тобто транзакція B також буде блокувати кортеж р з допомогою S-блокування).
Ці правила можна наочно представити у вигляді матриці сумісності, показаної на REF _Ref9935050 \ h \ * MERGEFORMAT рис. 15.6
| X | S | - | |
| X | N | N | Y |
| S | N | Y | Y |
| - | Y | Y | Y |
Введемо протокол доступу до даних, який на основі введення тільки що описаних Х-і S-блокування дозволяє уникнути виникнення проблем паралелізму.
1. Транзакція, призначена для вилучення кортежу, перш за все повинна накласти S-блокування на цей кортеж.
2. Транзакція, призначена для оновлення кортежу, перш за все повинна накласти Х-блокування на цей кортеж. Інакше кажучи, якщо, наприклад, для послідовності дій типу витяг / оновлення для кортежу вже задана S-блокування, то її необхідно замінити Х-блокуванням. Блокування у транзакціях звичайно задаються неявним чином: наприклад, запит на "вилучення кортежу" є неявним запитом з S-блокуванням, а запит на "оновлення кортежу" - неявним запитом з Х-блокуванням відповідного кортежу. При цьому під терміном "оновлення" (як і раніше) маються на увазі крім самих операцій оновлення також операції вставки та видалення.
3. Якщо запитувана блокування з боку транзакції B відкидається через конфлікт з деякої іншої блокуванням з боку транзакції A, то транзакція B переходить в стан очікування. Причому транзакція B буде перебувати в стані очікування до тих пір, поки не буде знято блокування, задана транзакцією A. У системі обов'язково повинні бути передбачені способи усунення нескінченно довгого стану очікування транзакції B.
4. Х-блокування зберігаються аж до кінця виконання транзакції (до операції "завершення виконання" або "скасування виконання"). S-блокування також зазвичай зберігаються аж до цього моменту.
15.6 Рішення проблем паралелізму
Розглянемо рішення проблем паралелізму за допомогою механізму блокувань.15.6.1 Проблема втрати результатів оновлення.
На REF _Ref10022336 \ h \ * MERGEFORMAT рис. 15.7| Транзакція A | Час | Транзакція B |
| Витяг кортежу р (Завдання S-блокування для p) | t1 | - |
| - | t2 | Витяг кортежу р (Завдання S-блокування для p) |
| Оновлення кортежу р (Завдання X-блокування для p) | t3 | - |
| Очікування | t4 | Оновлення кортежу р (Завдання X-блокування для p) |
| Очікування | Очікування |
15.6.2 Проблема незафіксованою залежності.
На REF _Ref10022391 \ h \ * MERGEFORMAT рис. 15.8| Транзакція A | Час | Транзакція B |
| - | t1 | Оновлення кортежу р (Завдання X-блокування для p) |
| Витяг кортежу р (Завдання S-блокування для p) | t2 | - |
| Очікування | t3 | Скасування виконання транзакції (Зняття X-блокування для p) |
| Підсумок: Витяг кортежу р (Завдання S-блокування для p) Цей текст може містити помилки. Програмування, комп'ютери, інформатика і кібернетика | Лекція Схожі роботи: Фізична організація баз даних на машинних носіях Просопографіческіе бази даних Росії на прикладі баз даних Comandarm і Duma1 Створення бази даних критичних властивостей речовин в редакторі баз даних MS Access Проектування баз даних MS Access Паралельні машини баз даних Історія розвитку баз даних Особливості проектування баз даних Розробка машини баз даних |
© Усі права захищені
написати до нас
написати до нас