Організація баз даних

Copyright by
© Я.І. Соколовський, М.В. Дендюк, І.М. Крошний, І.Б. Пірко, М.М. Паславський, 2023,
© НЛТУ України, 2023,
© Gdańska Szkoła Wyższa, 2023

Передмова … 15

Розділ 1. Теоретичні основи баз даних … 17

• Тема 1. Структура баз даних … 17
• 1.1. Основні поняття та визначення … 17
• 1.2. Системи керування базами даних … 18
• 1.3. Загальна архітектура баз даних … 20
• 1.4. Класифікація баз даних … 21
• 1.4.1. Бази даних за типом доступу (локальні, клієнт-серверні) … 21
• 1.4.2. Бази даних за типом керування (реляційні, NoSQL, NewSQL) … 24
• 1.5. Тенденції використання різних типів баз даних … 28
• Контрольні запитання … 29
• Тема 2. Моделі баз даних і моделі даних … 31
• 2.1. Поняття про моделі баз даних … 31
• 2.1.1. Міфологічні (концептуальні) моделі … 32
• 2.1.2. Даталогічні (логічні) моделі … 33
• 2.1.3. Фізичні моделі … 33
• 2.1.4. Взаємозв’язок моделей даних … 34
• 2.2. Типи моделей даних … 35
• 2.2.1. Файлова модель … 36
• 2.2.2. Ієрархічна модель … 38
• 2.2.3. Мережна модель … 42
• 2.2.4. Реляційна модель … 44
• 2.2.5. Постреляційна модель … 47
• 2.2.6. Багатовимірна модель. OLAP технологія … 49
• 2.2.7. Об’єктно-орієнтована модель … 56
• 2.2.8. Об’єктно-реляційна модель даних … 60
• 2.3. Проблеми маніпулювання даними та обмеження цілісності даних … 61
• Контрольні запитання … 63
• Тема 3. Реляційна модель баз даних … 65
• 3.1. Обмеження цілісності даних … 65
• 3.2. Реляційна алгебра … 67
• 3.2.1. Традиційні операції над множинами … 68
• 3.2.2. Спеціальні реляційні операції … 70
• 3.3. Нормалізація реляційних баз даних … 75
• 3.3.1. Перша нормальна форма … 77
• 3.3.2. Друга нормальна форма … 79
• 3.3.3. Третя нормальна форма … 81
• 3.3.4. Нормальна форма Бойса-Кодда … 82
• 3.3.5. Четверта нормальна форма … 85
• 3.3.6. П’ята нормальна форма … 86
• 3.3.7. Доменно-ключова нормальна форма. Денормалізація … 89
• 3.3.8. Переваги та недоліки нормалізації … 90
• Контрольні запитання … 92
• Тема 4. Етапи проектування баз даних … 93
• 4.1. Етап системного аналізу і словесного опису … 93
• 4.2. Етап концептуального (інфологічного) проектування. ER-діаграми … 94
• 4.2.1. ER-діаграма в нотації Чена … 99
• 4.2.2. ER-діаграми в нотації Баркера і Мартіна (Information Engineering) … 103
• 4.2.3. Проектування баз даних з використанням методології IDEF1X … 105
• 4.2.4. Нотація бази даних UML … 109
• 4.2.5. CASE-засоби … 112
• 4.3. Основи роботи з MySQL Workbench … 117
• 4.3.1. Проектування моделі бази даних … 120
• 4.3.2. Підключення MySQL Workbench до MуSQL-сервера … 131
• 4.3.3. Імпорт схеми даних з MySQL WorkBench на MySQL-сервер … 134
• Контрольні запитання … 138

Розділ 2. Реляційні бази даних MySQL … 140

• Тема 5. Основи мови SQL … 140
• 5.1. Роль і місце мови SQL … 140
• 5.2. Основні поняття мови SQL … 141
• 5.7.1. Склад мови SQL … 141
• 5.7.2. Оператори мови SQL … 142
• 5.7.3. Типи даних мови SQL … 143
• 5.3. Робота з доменами (Domain) … 146
• 5.4. Команда створення таблиць … 147
• 5.5. Команди модифікації структури таблиці … 152
• 5.6. Команда вилучення таблиці … 156
• 5.7. Команди модифікації вмісту таблиць … 156
• 5.7.1. Команда додавання записів … 156
• 5.7.2. Команда редагування записів … 159
• 5.7.3. Команда видалення записів … 160
• 5.8. Використання індексів … 161
• Контрольні запитання … 162
• Тема 6. Команда вибірки SELECT … 164
• 6.1. Формат запису команди SELECT … 164
• 6.2. Використання запиту SELECT для вибору записів з однієї таблиці … 165
• 6.2.1. Використання оператора AS … 165
• 6.2.2. Задавання умови WHERE … 166
• 6.2.3. Порівняння за шаблоном LIKE та REGEXP … 167
• 6.2.4. Використання оператора IN для задавання списку можливих значень … 170
• 6.2.5. Задавання діапазону значень оператором BETWEEN … 170
• 6.2.6. Використання оператора IS NULL … 171
• 6.2.7. Об’єднання декількох записів в результуючий за допомогою групових операцій … 171
• 6.2.8. Сортування записів оператором ORDER BY … 174
• 6.2.9. Отримання діапазону записів оператором LIMIT … 174
• 6.3. Використання SELECT для вибору записів з декількох таблиць … 176
• 6.3.1. Об’єднання внутрішнє … 177
• 6.3.2. Об’єднання зовнішнє … 178
• 6.3.3. Декартовий добуток CROSS JOIN … 180
• 6.3.4. Віднімання таблиць … 181
• 6.3.5. Додавання таблиць … 182
• 6.3.6. Самооб’єднання таблиць … 184
• 6.4. Використання запитів SELECT … INTO … 186
• 6.5. Використання запитів SELECT в інших запитах … 189
• 6.5.1. Створення таблиці на основі існуючої … 189
• 6.5.2. Створення таблиці на основі існуючої за умовою … 190
• 6.5.3. Створення гібридних таблиць … 190
• 6.5.4. Створення тимчасових таблиць … 191
• 6.5.5. Створення тимчасових таблиць на основі існуючих … 191
• 6.5.6. Задавання первинних ключів у створених таблицях на основі існуючих … 191
• 6.5.7. Додавання записів з іншої таблиці … 192
• Контрольні запитання … 193
• Тема 7. SQL функції … 195
• 7.1. Оператори загального призначення … 195
• 7.1.1. Змінні в MySQL … 195
• 7.1.2. Значення NULL та операції над ним … 196
• 7.1.3. Оператори керування в запитах … 198
• 7.2. Агрегатні функції … 201
• 7.3. Числові функції … 203
• 7.3.1. Математичні функції … 203
• 7.3.2. Функції дати і часу … 205
• 7.4. Функції роботи з рядками … 210
• 7.4.1. Функції визначення властивостей рядків … 210
• 7.4.2. Функції перетворення у рядках … 212
• 7.4.3. Пошук та видобування частини рядка … 218
• 7.4.4. Функції порівняння рядків … 222
• 7.4.5. Чутливість до регістру … 224
• 7.5. Системні функції … 225
• Контрольні запитання … 226
• Тема 8. Використання підзапитів в MySQL … 228
• 8.1. Визначення підзапитів … 228
• 8.2. Використання підзапитів в якості розрахункових полів … 229
• 8.3. Підзапити в реченні FROM … 230
• 8.4. Вкладений запит в умові WHERE … 230
• 8.5. Вкладений запит в умові HAVING … 231
• 8.6. Оператор EXIST у вкладених запитах … 232
• 8.7. Підзапити з операторами IN, ANY, ALL … 232
• 8.8. Підзапити в операторах маніпулювання даними … 233
• 8.8.1. Підзапити в операторі DELETE … 233
• 8.8.2. Підзапити в операторі UPDATE … 234
• 8.8.3. Підзапити в операторі INSERT … 235
• 8.9. Корельовані підзапити … 237
• Контрольні запитання … 238

Розділ 3. OLTP та OLAP системи … 240

• Тема 9. Збережені підпрограми. OLTP технологія … 240
• 9.1. Збережені процедури і функції на стороні сервера … 240
• 9.2.3. Поняття збережених процедур і функцій … 240
• 9.2.4. Створення процедур і функцій та звертання до них … 241
• 9.2.4.1. Збережені процедури … 241
• 9.2.4.2. Збережені функції … 253
• 9.2.5. Редагування і видалення збережених процедур і функцій … 254
• 9.2. Технологія OLTP. Основні поняття та вимоги до транзакцій … 255
• 9.2.1. Реалізація транзакцій в SQL … 259
• 9.2.2. Точки відкату … 265
• 9.2.3. Блокування таблиць … 266
• 9.2.3.1. Типи таблиць в MySQL … 266
• 9.2.3.2. Типи блокувань … 267
• 9.2.3.3. Оператори LOCK TABLES та UNLOCK TABLES … 268
• Контрольні запитання … 270
• Тема 10. Big Data рішення. OLAP технологія … 272
• 10.1. Великі дані (Big Data) … 272
• 10.1.1. Принципи роботи з великими даними (VVV) … 273
• 10.1.2. Технології і тенденції роботи з Big Data … 274
• 10.1.3. Методи і техніка аналізу великих даних … 276
• 10.2. Технологія OLAP … 277
• 10.2.1. Основні поняття OLAP … 277
• 10.2.2. Структура OLAP-куба … 278
• 10.2.3. Ієрархія вимірів OLAP-кубів … 280
• 10.2.4. Операції над гіперкубом … 280
• 10.3. Сховища даних (Data Warehouse) … 283
• 10.3.1. Поняття сховища даних … 283
• 10.3.2. Категорії даних, що зберігаються в сховищах даних … 284
• 10.3.3. Властивості сховища даних. Вітрина даних … 285
• 10.3.4. Глобальне сховище даних … 286
• 10.3.5. Архітектура та наповнення сховищ даних … 287
• 10.3.6. Моделі сховищ даних … 289
• 10.3.6.1. MOLAP (Multidimensional OLAP) … 289
• 10.3.6.2. ROLAP (Relational OLAP) … 291
• 10.3.6.3. Інші моделі побудови сховищ даних … 295
• 10.4. Інтелектуальний аналіз даних (Data Mining) … 295
• Контрольні запитання … 297

Розділ 4. Адміністрування та захист баз даних … 298

• Тема 11. Перегляди, курсори та тригери в MySQL … 298
• 11.1. Поняття переглядів VIEW … 298
• 11.1.1. Призначення переглядів … 298
• 11.1.2. Переваги використання переглядів … 299
• 11.1.3. Обмеження для переглядів в MySQL … 299
• 11.1.4. Алгоритми переглядів … 300
• 11.1.5. Створення переглядів … 300
• 11.2. Модифікації переглядів … 303
• 11.2.1. Групові перегляди … 303
• 11.2.2. Перегляди і об’єднання та підзапити … 304
• 11.2.3. Перегляди та підзапити … 305
• 11.3. Перегляди в операторах маніпулювання даними … 305
• 11.4. Модифікація і видалення переглядів … 307
• 11.5. Курсори … 308
• 11.6. Тригери … 311
• Контрольні запитання … 314
• Тема 12. Управління обліковими записами користувачів та виняткові ситуації в MySQL … 316
• 12.1. Управління обліковими записами користувачів … 316
• 12.2.1. Права (привілеї) в MySQL … 316
• 12.2.2. Створення користувачів БД … 320
• 12.2.2.1. Режим командного рядка … 320
• 12.2.2.2. Режим конструктора … 324
• 12.2.3. Відміна привілеїв і видалення користувачів … 328
• 12.2. Резервне копіювання і відновлення БД MySQL … 330
• 12.2.1. Копія БД або її частини на тому ж сервері … 332
• 12.2.2. Резервне копіювання і відновлення за допомогою MySQL Workbench … 334
• 12.2.2.1. Інструмент Reverse Engineer … 334
• 12.2.2.2. Майстер експорту та імпорту SQL-даних … 338
• 12.2.2.3. Майстер експорту та імпорту однієї таблиці … 339
• 12.2.2.4. Майстер експорту та імпорту даних результатів … 343
• 12.3. Характерні помилки в MySQL … 343
• 12.3.1. Діагностика причин виняткових ситуацій … 343
• 12.3.2. Помилки Cannot Connect to Database Server … 345
• 12.3.3. Проблеми при роботі з сервером … 347
• 12.3.3.1. Причина «server has gone away» … 347
• 12.3.3.2. Помилка Host ‘…’ is blocked … 348
• 12.3.3.3. Помилка Too many connections … 349
• 12.3.3.4. Помилка Out of memory … 350
• 12.3.3.5. Помилка Table ‘xxx’ doesn’t exist … 352
• 12.3.3.6. Помилка Can’t initialize character set xxx … 354
• 12.3.4. Інші виняткові ситуації … 355
• Контрольні запитання … 356

Розділ 5. Бази даних NoSQL … 357

• Тема 13. Основи баз даних NoSQL … 357
• 13.1. Основні поняття NoSQL … 357
• 13.2. Категорії NoSQL … 358
• 13.2.1. Документно-орієнтовані бази даних … 358
• 13.2.2. Бази даних типу "ключ/значення" … 360
• 13.2.3. Графові бази даних … 361
• 13.2.4. Стовпцеві бази даних … 363
• 13.3. Основи MongoDB … 364
• 13.3.1. Колекції … 365
• 13.3.2. Документи замість рядків … 366
• 13.3.3. Формат JSON … 369
• Контрольні запитання … 374
• Тема 14. Консоль MongoDB … 376
• 14.1. Основи роботи з MongoDB … 376
• 14.1.1. Початок роботи з MongoDB … 376
• 14.1.2. Типи даних … 378
• 14.1.3. Колекції і документи … 379
• 14.2. Додавання даних … 381
• 14.2.1. Додавання даних командами … 381
• 14.2.2. Завантаження даних з файлу … 385
• 14.3. Вибірка з БД … 386
• 14.3.1. Функція find … 386
• 14.3.2. Проекція … 390
• 14.3.3. Запит до вкладених об’єктів … 392
• 14.3.4. Використання регулярних виразів … 393
• 14.3.5. Налаштування запитів та сортування … 395
• 14.3.6. Курсори … 397
• 14.4. Команди групування … 400
• 14.4.1. Кількість елементів в колекції … 400
• 14.4.2. Функція distinct … 401
• 14.4.3. Агрегація і метод group … 402
• 14.5. Оператори керування … 404
• 14.5.1. Умовні оператори … 404
• 14.5.2. Логічні оператори … 408
• 14.5.3. Пошук у масивах … 410
• 14.5.4. Оператори $exists, $type та $regex … 412
• 14.6. Оновлення даних … 414
• 14.6.1. Метод save … 414
• 14.6.2. Функція update … 415
• 14.6.3. Оновлення окремого поля … 416
• 14.6.4. Методи updateOne і updateMany … 419
• 14.6.5. Оновлення масивів … 420
• 14.7. Видалення даних … 422
• 14.7.1. Видалення поля … 422
• 14.7.2. Видалення елемента з масиву … 423
• 14.7.3. Видалення колекцій і баз даних … 426
• 14.8. Встановлення посилань в БД … 426
• 14.8.1. Ручне встановлення посилань … 427
• 14.8.2. Автоматичне зв’язування … 428
• 14.9. Робота з індексами … 429
• 14.10. Керування колекцією … 433
• 14.11. Резервне копіювання і відновлення БД MongoDB … 435
• 14.11.1. Резервне копіювання БД … 435
• 14.11.2. Відновлення даних … 438
• Контрольні запитання … 440
• Тема 15. Графічний клієнт MongoDB Compass … 442
• 15.1. Основні відомості про Compass … 442
• 15.2. Підключення Compass до сервера MongoDB … 444
• 15.3. Створення баз даних та колекцій … 447
• 15.4. Додавання документів … 448
• 15.4.1. Імпорт файлу … 449
• 15.4.2. Інтерактивне додавання документу … 450
• 15.4.3. Клонування документів … 451
• 15.5. Перегляд даних (документів) … 452
• 15.6. Редагування документів … 454
• 15.7. Видалення документів … 454
• 15.8. Видалення колекції та бази даних … 455
• 15.9. Консольний клієнт Mongosh … 456
• Контрольні запитання … 457

Предметний покажчик … 459

Література … 463

[PL]

Podręcznik jest efektem współpracy Gdańskiej Szkoły Wyższej z uczelniami z Ukrainy w ramach realizowanych umów o współpracy naukowej.

"Organizacja baz danych" może być przydatna dla studentów Zarządzania i inżynierii produkcji oraz Administracji w ramach realizacji zajęć dotyczących zarówno informatyki, jak i modułów związanych z cyberbezpieczeństwem.

Podręcznik do samodzielnej nauki omawia teoretyczne aspekty baz danych zarówno klasycznych relacyjnych SQL, jak i nierelacyjnych technologii NoSQL, OLTP i OLAP, w tym zasady pracy z dużymi zbiorami danych. Dużą uwagę poświęcono administrowaniu i ochronie baz danych, podano typowe sytuacje wyjątkowe w MySQL oraz sposoby ich eliminacji. Podręcznik może być wykorzystany do sprawdzenia stanu wiedzy, nauczania praktyk laboratoryjnych, projektowania, kursów. Przeznaczony jest dla studentów szkół wyższych na kierunku "Technologie informacyjne", w szczególności specjalnościach: "Informatyka", "Systemy i technologie informacyjne" oraz "Inżynieria oprogramowania". Podręcznik przyda się tym, którzy chcą samodzielnie opanować podstawy teoretyczne i umiejętności tworzenia i utrzymywania baz danych

[UKR]

У навчальному посібнику розглянуто теоретичні аспекти баз даних, як класичних реляційних SQL, так і нереляційних NoSQL, розглянуто технології OLTP та OLAP, в тому числі принципи роботи з великими даними. Значна увага приділяється адмініструванню та захисту баз даних, наведено характерні виняткові ситуації в MySQL та шляхи їх усунення. Посібник може бути використаний для розроблення баз даних MySQL та MongoDB, контролю знань, викладання лабораторних практикумів, курсового та дипломного проектування. Для студентів закладів вищої освіти в галузі знань "Інформаційні технології", зокрема спеціальностей "Комп’ютерні науки", "Інформаційні системи та технології" та "Інженерія програмного забезпечення". Посібник буде корисним бажаючим самостійно оволодіти теоретичними основами та навиками розроблення і супроводу баз даних
Іл. 304, Табл. 10, Бібліографій 39

Різноманіття типів баз даних (БД) зумовлене, насамперед, різноманіттям задач, для яких використовуються відповідні БД.

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

Адже від вибору типу бази даних, розробленої схеми, її зв’язків залежить подальша коректна робота з даними. І не тільки на стороні сервера, ще більш залежні від розробленої структури БД будуть застосунки користувача.

Для розроблення і супроводу БД на стороні сервера акцент ставиться на середовища, що поширюються під вільною ліцензією, зокрема MySQL Workbench для реляційних БД та MongoDB для NoSQL БД.

У багатьох сферах, таких як у банківській, страховій, біржовій, обліковій, комерційній, тощо, використовується спосіб організації баз даних (БД), при якому система працює в режимі реального часу з короткими, не пов’язаними одна з одною транзакціями, організованими у великий потік. Така онлайнова обробка транзакцій отримала назву OLTP-системи та забезпечує обов’язковий набір властивостей – атомарність, узгодженість, ізольованість та довговічність (ACID).

Зростаючі об’єми інформації вимагають нових технологій для зберігання, модифікації, отримання та адміністрування «великих даних». Адже такі дані повинні бути горизонтально масштабовані, тобто зберігатися на багатьох ресурсах і забезпечувати хорошу продуктивність їх оброблення.

В останні роки у світі сформувався ряд нових технологій збереження і аналізу великих обсягів даних:

1. сховища даних, або склади даних (Data Warehouse);
2. оперативна аналітична обробка (On-Line Analytical Processing, OLAP);
3. інтелектуальний аналіз даних – ІАД (Data Mining).

Для аналітичного опрацювання «великих даних» використовується технологія OLAP як ключовий компонент організації сховищ даних. Інтелектуальний аналіз даних використовується для підтримки прийняття рішень, заснований на пошуку в даних схованих закономірностей.

Оскільки реляційні бази даних погано пристосовані до роботи з дійсно великими обсягами даних, так як вони спроектовані для роботи на одній машині, тому для розподіленої роботи використовується інша технологія баз даних – NoSQL (Not only SQL) – це не тільки реляційні бази даних.

Такі бази даних добре масштабуються горизонтально, але не зберігають всі принципи SQL (ACID). Їх можна умовно розділити на чотири групи: документо-орієнтовані бази даних, бази даних типу "ключ/значення", графові бази даних та стовпцеві бази даних.

Автори висловлюють сподівання, що даний посібник буде корисний не тільки студентам, які вивчають дисципліну «Організація баз даних і знань», але безпосередньо і розробникам баз даних.

Також автори висловлюють подяку усім колегам, співпраця з якими позитив-но впливала на написання посібника, особливо Євгену Бегену з ІТ-компанії SoftServe Confidential за проведений ґрунтовний аналіз робочої програми дисципліни «Організація баз даних і знань» і наданим рекомендаціям щодо вдосконалення її вмісту. Окрему вдячність автори висловлюють шановним рецен-зентам – д.ф.-м.н., проф. Дияк І.І., д.ф.-м.н., проф. Терещенко В.М., д.ф.-м.н., проф. Процах Н.П. за цінні поради та зауваження, які сприяли покращенню посібника. Ми будемо вдячні всім, хто знайде за можливе подати свої зауваження та конструктивні відгуки про посібник за адресою dendiuk@ukr.net. Вони слугуватимуть добрим підґрунтям нашої подальшої роботи.

З повагою – автори