Юрчишин В.М., Клим Б.В. і інші. Організація баз даних

Подождите немного. Документ загружается.

Івано-Франківський Національний технічний

університет нафти і газу

В.М. Юрчишин, Б.В. Клим, В.Б. Кропивницька

ОРГАНІЗАЦІЯ БАЗ ДАНИХ

Івано-Франківськ

Факел, 2010

УДК 004.658.2

В.М. Юрчишин, Б.В. Клим, В.Б. Кропивницька. Організація

баз даних. Навчальний посібник – Івано-Франківськ: “Факел”,

2010. – 224 с.

Навчальний посібник містить короткі теоретичні відомості та

опис програмних засобів для роботи з базами даних, варіанти

комплексних завдань, зокрема з нафтогазової тематики, та приклад

виконання одного варіанту.

Посібник призначений для отримання практичних навичок та

засвоєння прийомів побудови і ведення баз даних під час

виконання практичних робіт і самостійної підготовки студентів

спеціальності “Комп’ютерні системи та мережі” і може бути

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

Рецензенти:

д.т.н., професор, зав. кафедри кібернетики хіміко-технологічних

процесів НТУУ “КПІ” Г.О. Статюха

д.т.н., професор, зав. кафедри комп’ютерних систем та мереж

ІФНТУНГ М.І. Горбійчук

к.п.н., доцент кафедри інформатики Прикарпатського

Національного університету Б.М. Дрінь

Зміст стор.

Вступ _______________________________________ 5

1 Короткі відомості з теорії баз даних _____________ 6

1.1 Загальні поняття ______________________________ 6

1.2 Моделі даних ________________________________ 8

1.3 Нормалізація таблиць _________________________ 12

1.4 Зв’язки між таблицями ________________________ 14

2 Короткий опис засобів MS Access для роботи з

базами даних ________________________________

2.1 Візуальні об’єкти MS Access ___________________ 16

2.2 Короткий опис програмних засобів MS Access ____ 17

2.2.1 Модулі ______________________________________ 17

2.2.2 Оголошення даних VBA _______________________ 18

2.2.3 Присвоєння значень та ввід-вивід змінних ________ 19

2.2.4 Розгалуження і цикли _________________________ 19

2.2.5 Підпрограми _________________________________ 21

2.2.6 Об’єкти VBA ________________________________ 22

3 Приклад побудови об’єктів БД __________________ 28

3.1 Постановка задачі ____________________________ 28

3.2 Розробка та обгрунтування структури БД ________ 29

3.3 Побудова таблиць, підбір контрольних даних _____ 30

3.3.1 Типи та імена полів таблиці ____________________ 30

3.3.2 Записи ______________________________________ 32

3.3.3 Засоби MS Access для створення таблиці _________ 32

3.3.4 Приклади створення таблиць ___________________ 33

3.4 Запити ______________________________________ 36

3.4.1 Основні поняття ______________________________ 36

3.4.2 Створення зв’язків між таблицями _______________ 37

3.4.3 Оператори ___________________________________ 38

3.4.4 Вирази ______________________________________ 38

3.4.5 Мова запитів _________________________________ 40

3.4.6 Приклади побудови запитів MS Access ___________ 44

3.5 Виготовлення звітів ___________________________ 48

3.6 Конструювання форм _________________________ 52

3.7 Виготовлення звітів засобами мови VBA _________ 54

3.8 Макроси ____________________________________ 57

3.9 Аналіз результатів виконання програми _________ 58

3.10 Таблиця ідентифікаторів програм _______________ 59

4 Застосування засобів СУБД Oracle для ведення БД _ 60

4.1 Коротка характеристика Oracle _________________ 60

4.2 Архітектура екземпляра Oracle _________________ 65

4.2.1 Структура екземпляра _________________________ 65

4.2.2 Фонові процеси Oracle ________________________ 70

4.3 Транзакції ___________________________________ 77

4.3.1 Анатомія транзакції ___________________________ 77

4.3.2 Несуперечність і паралельна обробка ____________ 80

4.4 Аутентифікація користувача ___________________ 83

4.4.1 Користувачі БД ______________________________ 83

4.4.2 Привілеї ____________________________________ 85

4.4.3 Ролі ________________________________________ 91

4.4.4 Профілі _____________________________________ 94

4.5 Засоби збереження даних ______________________ 96

4.5.1 Типи даних Oracle ____________________________ 96

4.5.2 Послідовності ________________________________ 97

4.5.3 SQL-функції Oracle ___________________________ 98

4.5.4 Табличні області ______________________________ 103

4.5.5 Таблиці _____________________________________ 105

4.5.6 Представлення _______________________________ 108

4.6 Програмні засоби доступу до даних _____________ 112

4.6.1 Структура програми мовою PL/SQL _____________ 112

4.6.2 Підпрограми _________________________________ 116

4.6.3 Курсори _____________________________________ 118

4.6.4 Обробка виняткових ситуацій __________________ 123

4.6.5 Тригери _____________________________________ 126

4.7 Розподілені БД _______________________________ 130

4.7.1 Основні поняття ______________________________ 130

4.7.2 Зв’язок з віддаленою базою даних _______________ 132

4.7.3 Синоніми ____________________________________ 134

4.7.4 Знімки ______________________________________ 135

5 Адміністрування БД __________________________ 138

5.1 Адміністратор БД _____________________________ 138

5.2 Аудит _______________________________________ 140

5.3 Моніторинг екземпляра Oracle __________________ 143

5.4 Ділова гра з адміністрування БД ________________ 150

6 Варіанти завдань для виконання практичних робіт _ 153

Перелік рекомендованих джерел ________________ 195

Додаток А. Зразки даних для побудови таблиць ___ 196

Вступ

Посібник містить короткі теретичні відомості та опис засобів

систем управління базами даних MS Access і Oracle, а також 30

варіантів комплексних завдань зі зразками даних, зокрема з

нафтогазової справи, та приклад виконання одного варіанту.

Кожний варіант містить по 9 задач (всього 270 задач). Задачі

приблизно рівноцінні за складністю, вони підібрані так, що

постановка задачі в кожному окремому випадку може бути

скорегована або доповнена.

Даний навчальний посібник призначений для практичного

оволодіння основними принципами розробки структури бази

даних, засвоєння прийомів побудови таблиць, запитів, звітових

форм та інших об’єктів, засвоєння навичок застосування

програмних засобів сучасних систем управління базами даних.

Задачі посібника можуть бути використані під час виконання

лабораторних, домашніх, курсових або контрольних робіт

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

даних. У прикладах виконання варіанту роботи подано детальне

пояснення команд, що дозволяє користуватися ним під час

самостійної підготовки.

1 Короткі відомості з теорії баз даних

1.1 Загальні поняття

Термін “база даних” говорить про те, що йдеться про дані,

тобто про інформацію, яка характеризує певний об’єкт, та, що ці

дані є базовими, основними. З погляду користувача, який

експлуатує базу даних (БД), вона є моделлю предметної області,

об’єкта, наприклад, підприємства або його частини, підрозділу.

Найважливішою вимогою, яка ставиться до моделі, є її

адекватність, тобто вірне відображення зв’язків і відношень між

елементами того об’єкта, який вона описує. Як правило, кожний

об’єкт з плином часу розвивається, змінюється. Тому БД, як

модель об’єкта, являє собою живий організм, який перебуває в

постійному русі, вона теж змінюється в часі.

З погляду програміста, який будує нову або веде (забезпечує

супровід у ході експлуатації) готову БД, і в межах цього посібника

ми будемо її розглядати саме з цього погляду, БД являє собою

сукупність даних та програм, призначених для їх обробки.

В сучасних БД практично не існує обмежень на вигляд даних.

Це можуть бути числа (облік кількості товарів, працівників,

грошей), літерні або символьні дані (назва та опис властивостей

товару, прізвище працівника), календарна дата протікання події,

текст великого розміру (текст книги), малюнок, кіно– та

відеофільм, адреса мережі Internet, математичний або логічний

вираз і т.д. Тобто даними є будь-яка інформація, яка відображає

стан об’єкта протягом заданого проміжка часу і яку можна

зберігати в пам’яті комп’ютера.

Обробка даних передбачає виконання таких робіт:

– ввід, занесення нових даних;

– забезпечення цілісності БД. Це такі роботи, як контроль і

попередження помилок під час вводу, наприклад, температура

води при нормальному тиску не може бути меншою за 0

С.

Може виникнути потреба у відновленні частини даних,

втрачених через технічні неполадки та ін.;

– вивід на екран монітора, друк тощо;

– вилучення з бази уже непотрібних, зайвих даних;

– редагування, наприклад, виправлення помилок, зроблених під

час вводу, або зміна даних у зв’язку зі зміною стану об’єкта;

– маніпуляція даними, їх підготовка до вигляду, вигідного для

подальшого використання. Це, наприклад, сортування даних;

– вибір потрібної інформації та її представлення користувачеві

у зручному для нього вигляді;

– пошук даних. Як правило, сучасні промислові БД громіздкі,

тому операції пошуку є найбільш трудомісткими під час

відбору даних для запитів. Ця робота виконується переважно

програмним способом за заданою умовою.

Перші БД беруть свій початок з 50-х років ХХ століття. Це

були файлові системи, в яких застосовувалися універсальні

програмні засоби, такі як Фортран, Алгол, Кобол та ін. У 60-х

роках появилися перші системи управління базами даних

(СУБД) – це сукупність програм, призначених для побудови та

обслуговування БД. Для прикладу можна назвати такі СУБД, як

спочатку IMS, яка підтримувала ієрархічну модель даних, а пізніше

dBASE, FoxPro, Delphi, Informix, Oracle, MS Access, MySQL,

SyBase та ін. – реляційного типу. СУБД розвиваються в напрямку

взяття на себе все більшої кількості фукцій, але лише загальних, які

стосуються всіх або більшості робіт. Вони не можуть враховувати

специфіку конкретних потреб, тому виглядають стандартними.

Таким чином, програмне забезпечення БД можна умовно поділити

на стандартне, яке постачається разом із СУБД, та прикладне,

виготовлене програмістами під час побудови та ведення БД за

допомогою спеціальних програмних засобів, які має СУБД.

Крім уже вищеперечислених видів робіт з обробки даних,

прикладні програми забезпечують видачу звітів, у тому числі в

графічному вигляді, надають користувачу БД вигідний інтерфейс,

наприклад, форми, на яких може міститися не тільки інформація,

але й засоби управління даними та програмами, такі як кнопки,

списки тощо, слідкують за станом даних, наприклад, автоматично

видають своєчасне попередження про наявність гарячих курортних

путівок, про стікання терміну зберігання товарів, забезпечують

зв’язок з мережею Internet і багато інших.

В межах цього посібника ознайомимося з двома СУБД, які

будуть використані для демонстрації прикладів робіт у БД, а саме:

MS Access (версія MS Access 2003) і Oracle (версія Oracle 8i).

Перша характерна легкодоступністю, бо це додаток до Windows.

Вона порівняно проста в користуванні, тому вигідна на перших

етапах вивчення організації БД. Друга належить до найбільш

потужних сучасних СУБД, вона забезпечує знакомство з практично

всіма тонкощами, що стосуються побудови та адміністрування БД.

Всі СУБД, як правило, мають власні програмні засоби для

побудови та ведення БД. Універсальною і найбільш поширеною

мовою, яка застосовується практично ними всіма, є SQL (Structured

Query Lanquage) – структурна мова запитів.

1.2 Моделі даних

Велика кількість даних затрудняє роботу з ними, тому з метою

її спрощення дані слід якимось чином упорядкувати, як на

логічному, смисловому рівні, так на рівні розташування їх у

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

впорядкування безпосередньо впливає на сам характер обробки

даних. Розглянемо моделі даних, які в ході історичного розвитку

БД застосовувалися для логічного впорядкування даних.

Ієрархічна модель найбільш придатна для впорядкування

даних про предметну область (ПО), які мають ієрархічну структуру

зв’язків між собою за своєю природою. Прикладом таких даних

може бути схема підпорядкування один одному підрозділів

підприємства, зміна в часі властивості об’єкта, територіально-

адміністративний устрій держави, будова машини, яка складається

з вузлів, підвузлів та деталей і т.д.

Одиничним, елементарним значенням ієрархічної структури є

поіменоване поле. Сукупність полів утворює сегмент, який є

одиницею доступу до даних. На найвищому рівні ієрархічної схеми

розташування даних знаходиться єдиний сегмент, який називається

кореневим або батьківським. Він може бути зв’язаний з одним або

декількома сегментами нижчого рівня, які називають дочірніми.

Декілька дочірніх сегментів, які мають одного батька називаються

близнюками. Сегменти, які не мають дочірніх, називаються

листками.

Ця модель передбачає наявність лише строго вертикальних

зв’язків між даними типу один до одного або один до багатьох

(зверху вниз) та багато до одного (знизу вверх). Допускаються

горизонтальні зв’язки типу один до одного лише між сегментами

одного рівня підпорядкування, що дозволяє виконувати пошукові

операції на цьому рівні без повернення до батьківського сегмента.

Однією із систем, що базувалася на ієрархічній моделі даних,

була система IMS, яка використовувала мову DL/1.

До головних переваг ієрархічної моделі можна віднести такі:

– використання низькорівневих засобів керування даними в

зовнішній пам’яті;

– економне використання пам’яті за рахунок упорядкованого

розташування даних.

Найбільш суттєвими недоліками цієї моделі є такі:

– порівняно складними є алгоритми забезпечення захисту та

цілісності і несуперечності даних;

– якщо дані не мають природної ієрархічної структури, тобто

мають зв’язки типу багато до багатьох, то приходиться

створювати її штучно, а це спричиняє дублювання даних.

Мережна модель даних має практично ті ж переваги та

недоліки, що й ієрархічна, проте дозволяє простіше відображати

складніші зв’зки між даними, зокрема типу багато до багатьох,

описувати цикли та петлі. Одиницею доступу до даних мережної

структури є запис, який теж складається з полів. Запис є аналогом

сегмента ієрархічної структури даних. Прикладом зв’язку типу

багато до багатьох може бути мережа багатьох газопроводів,

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

підприємство експлуатує декілька газопроводів. Циклом

називається форма зв’язку, де предок одночасно є нащадком.

Прикладом циклу може бути підприємство, яке одночасно є

власником газопроводу, який експлуатує інше підприємство. У

петлі підприємство є власником газопроводу, який же й

експлуатує.

Ієрархічна і мережна моделі даних застосовувалися переважно

в ранніх СУБД. Загальним їхнім недоліком є те, що вони залежать

від фізичної організації БД і вимагають від програміста, який

складає прикладні програми для її обслуговування, знань про цю

організацію. Тому логіка маніпулювання даними стає, як правило,

перевантаженою деталями про реалізацію способу доступу до

даних.

Реляційну модель даних запропонував Е. Кодд на початку 70-

тих років ХХ століття. З часом вона набувала все більшої

популярності і сьогодні стала найбільш поширеною. Вона має ряд

переваг, з яких відзначимо такі:

– використовується потужний математичний апарат: теорія

множин, математична логіка, реляційна алгебра та ін., що

дозволило створити струнку теорію організації БД;

– питання фізичної організації БД стали другорядними.