Міністерство освіти і науки, МОЛОДІ ТА СПОРТУ України
ОДЕСЬКА ДЕРЖАВНА АКАДЕМІЯ ХОЛОДУ
Вохменцева Т.Б.
Грищенко І.В.
Колумба І.В.
Основи проектування баз даних
Посібник та варіанти завдань до виконання лабораторних робіт
для студентів напряму підготовки
6.050102 “Компьютерна інженерія”
Одеса 2012
Вохменцева Т.Б., Грищенко І.В., Колумба І.В. Основи проектування баз даних:Посібник та варіанти завдань до виконання лабораторних робіт. Одеська державна академія холоду, 2012. – 76 с.
На дослідженнях баз даних заснована ціла індустрія інформаційних послуг. Досягнення в проектуванні баз даних сталі основою фундаментальних розробок комунікаційних систем, транспорту і логістики, фінансового менеджменту, систем баз знань, методів доступу до наукової літератури, а також великої кількості цивільних і військових додатків.
Роль курсу основ проектування баз даних у технічному ВНЗі дуже важлива - це фундаментальна база для теоретичної підготовки фахівця, без якої його успішна діяльність неможлива. Проектування баз даних є базовою дисципліною більшості тем дипломних робіт фахівців напряму «Комп’ютерна інженерія».
Навчальний посібник призначено для студентів напряму підготовки 6.050102 “Комп’ютерна інженерія ”.
Зав.каф. інформаційних систем і мереж ОДАХ,
д.т.н., професор Князєва Н.О.
ОДАХ, 2012
Зміст
Лабораторна робота № 1 4
Лабораторна робота № 2 12
Лабораторна робота № 3 16
Лабораторна робота № 4 24
Лабораторна робота № 5 29
Лабораторна робота № 7 41
Cписок літератури 59
Додатки 60
Лабораторна робота № 1
Аналіз проектного завдання. Побудова Sadt - діаграм та Dfd-діаграм.
Ціль роботи
Придбання навиків аналізу предметної області завдання, визначення основних функцій і задач інформаційної системи.
Придбання навиків в побудові Sadt-діаграм.
Придбання навиків аналізу предметної області завдання.
Придбання навиків в побудові DFD -диаграм.
Порядок виконання роботи
По номеру варіанту вибрати завдання і відповідно до методичних вказівок, за матеріалами рекомендованої літератури і конспекту лекцій виконати завдання і оформити звіт по роботі.
Зміст звіту
Початкові дані і постановка задачі.
Результати аналізу завдання, перелік функцій і задач майбутньої системи, Sadt-діаграма.
Результати побудови Dfd-діаграми.
Методичні вказівки
SADT - це одна з найвідоміших і широко вживаних систем проектування. SADT (Structured Analysis and Design Technique) - технологія структурного аналізу і проектування - це графічні позначення і підхід до опису систем.
SADT - це методологія, розроблена спеціально для того, щоб полегшити опис і розуміння штучних систем, що потрапляють в розряд середньої складності. В цей розряд потрапляють системи комутацій в телефонних мережах, управління аероповітряними перевезеннями або рухом підводного човна і ін. Широкий спектр областей указує на потужність і універсальність методології SADT. В програмі інтегрованої комп'ютеризації виробництва Міністерства оборони США була визнана корисність SADT, що привело до стандартизації і публікації її частини, IDEFO. В комерційному світі SADT використовується для визначення вимог. В цій якості вона конкурує з методами, орієнтованими на потоки даних, а також з методами структуризації даних. На відміну від цих методів SADT створена для опису системи і її середовища, для визначення вимог до програмного забезпечення або до чого-небудь іншого.
Опис системи за допомогою SADT називається моделлю. В Sadt-моделях використовуються як природна, так і графічна мови. Для передачі інформації про конкретну систему джерелом природної мови служать люди, що описують систему, а джерелом графічної мови - сама методологія SADT. Sadt-модель дає повний, точний і адекватний опис системи, що має конкретне призначення. Таким чином, ціллю моделі є отримання відповідей на деяку сукупність питань. Ці питання неявно присутні в процесі аналізу, і отже, вони керують створенням моделі і направляють його. Якщо модель відповідає не на всі питання або відповіді недостатньо точні, то говорять, що модель не досягла своєї цілі.
Процес моделювання в SADT включає збір інформації про досліджувану область, документування отриманої інформації і представлення її у вигляді моделі і уточнення моделі за допомогою ітеративного рецензування.
В процесі моделювання дані про систему, що вивчається, одержують за допомогою випробуваної методики збору інформації - опитів або інтерв'ю. Для отримання найбільш повної інформації SADT пропонує використовувати різні її джерела (наприклад, читати документи, опитувати людей, спостерігати за роботою систем).
Створення моделі - це другий важливий етап в процесі моделювання, на якому аналітик документує отримані їм знання про предметну область, представляючи їх у вигляді однієї або декількох Sadt-діаграм. Однією з основних компонентів методології є ітеративне рецензування, в процесі якого автор і експерт багато разів радяться щодо достовірності створюваної моделі.
Результатом застосування методології SADT є модель, яка складається з діаграм, фрагментів текстів і глосарію, що має посилання один на одного. Діаграми - головні компоненти моделі, всі функції системи і інтерфейси на них представлені як блоки і дуги. Місце з'єднання дуги з блоком визначає тип інтерфейсу. Управляюча інформація (інструкції, нормативи, стандарти, критерії оцінки результатів) входить в блок зверху, тоді як інформація, яка піддається обробці, показана з лівого боку блоку, а результати виходу показані з правої сторони. Механізм (людина або автоматизована система), який здійснює операцію, представляється дугою, що входить в блок знизу (рис.1).
Рисунок 1 - Функціональний блок і інтерфейсні дуги
В процесі аналізу доцільно класифікувати функції системи по ступеню важливості - виділити необхідні, можливі і відсутні функції.
Перелік функцій системи, складений на підставі опису, має вигляд:
Прийом заявок від клієнтів.
Реєстрація і перевірка орендарів.
Реєстрація орендодавців.
Облік об'єктів оренди.
Підготовка пропозицій для орендарів.
Висновок договорів.
В завданні явно не указується потреба у функціях № 3 і № 4, тому вони віднесені до відсутніх. Діаграма має вигляд:
Рисунок 2 - Sadt-діаграма системи
Як приклад в даній лабораторній роботі і далі буде використовуватися тема роботи агенції нерухомості, що пропонує нерухомість в оренду. В результаті збору даних про досліджувану область і документування отриманої інформації були визначені основні дії майбутньої моделі. Отже, агенція нерухомості одержує запити від потенційних орендарів. Запити приймаються в комерційному відділі компанії з використанням інформації про наявність необхідного об'єкту для оренди. Обробка заявки включає наступні дії: якщо клієнт прийшов вперше, то його спочатку реєструють, якщо ж він вже раніше користувався послугами агенції, то перевіряється його особиста справа. При подачі заявки на послуги проводиться аналіз існуючих об'єктів нерухомості, що здаються в оренду, і підбір одного або декількох варіантів. Потім вся ця інформація надається клієнту, і після обмовляються умови договору. Після обговорення і огляду об'єктів відбувається укладення договору, оформлення відповідної документації і видача договору зі всіма даними клієнту.
Разом з функціональними діаграмами і діаграмами «сутність-зв'язок» застосовується моделювання потоків даних, що дозволяє представити систему з погляду даних і ілюструвати зовнішні механізми подачі даних, що вимагають наявність певного інтерфейсу.
Відповідно до методології модель системи визначається як ієрархія діаграм потоків даних (DFD), що описують асинхронний процес перетворення інформації від її введення в систему до видачі користувачу. Діаграми верхніх рівнів ієрархії визначають основні процеси або підсистеми ІС із зовнішніми входами і виходами. Вони деталізуються за допомогою діаграм нижнього рівня. Така декомпозиція продовжується поки не буде досягнутий такий рівень декомпозиції, на якому процеси стають елементарними і деталізувати їх далі неможливо.
Джерела інформації (зовнішні сутності) породжують інформаційні потоки (потоки даних), що переносять інформацію до підсистем або процесів. Ті у свою чергу перетворюють інформацію і породжують нові потоки, які переносять інформацію до інших процесів, накопичувачів даних або зовнішніх сутностей – споживачам інформації. Таким чином, основними компонентами DFD-діаграм є:
Зовнішні сутності, які є фізичною особою, що є джерелом або приймачем інформації, наприклад, замовники, постачальники, клієнти, склад. Визначення деякого об'єкту або системи як зовнішньої сутності вказує на те, що вона знаходиться за межами границь аналізованої ІС. Зовнішня сутність на діаграмі потоків даних зображається таким чином:
Потенційний
орендар
П
роцеси
є перетворенням вхідних потоків даних
у вихідні відповідно до певного алгоритму.
Фізично процес може бути реалізований
різними способами: це може бути підрозділ
організації (відділ), що виконує обробку
вхідних документів і випуск звітів,
програма, програмно реалізований
логічний пристрій і т.д. Процес на
діаграмі потоків даних зображається
таким чином:
П
оле
імені
В полі імені вводиться найменування процесу у вигляді пропозиції з активним недвозначним дієсловом в невизначеній формі (обчислити, розрахувати, перевірити, визначити, створити, отримати), за якою слідують іменники в знахідному відмінку.
Накопичувачі даних є абстрактними пристроями для інформації, яку можна у будь-який момент помістити в накопичувач і через деякий час витягнути, причому способи розміщення і витягання можуть бути будь-якими. Накопичувач даних може бути реалізований у вигляді ящика в картотеці, таблиці в оперативній пам'яті, файла на магнітному носії і т.д. Накопичувач на діаграмі потоків даних зображається таким чином:
D1 |
Tenant |
Накопичувач даних ідентифікується буквою “D” і довільним числом. Ім'я накопичувача вибирається з міркування найбільшої інформативності для проектувальника. Накопичувач в загальному випадку є прообразом таблиці майбутньої бази даних і опис даних, що зберігаються в ньому, повинен бути пов'язаний з інформаційною моделлю.
Потоки даних визначають інформацію, передану через деяке з'єднання від джерела до приймача. Реальний потік даних може бути інформацією, переданою по кабелю між пристроями, листами, що пересилаються, дискетами, які переносять з одного комп'ютера на інший і т.д. Потік на діаграмі зображується лінією із стрілкою, яка показує напрям потоку. Кожний потік має ім'я, що відображає його зміст.
Передача
договору
Орендар
Як приклад використовується опис роботи агенції нерухомості. Після вивчення предметної області були визначені задачі і дії майбутньої системи. Для побудови моделі потоків даних необхідно визначити основні компоненти діаграми для даного прикладу.
Для створення моделі необхідно:
Визначити зовнішні об'єкти, процеси, потоки даних і накопичувачі
З'ясувати взаємодію компонентів один з одним
Створити діаграму, що ілюструє компоненти і їх взаємозв'язок в єдиній моделі процесу.
Зовнішнім об'єктом є клієнт – потенційний орендар, з яким повинен бути укладений договір. Перший процес – це «прийом заявки». Якщо клієнт звернувся в агенцію вперше, дані про нього зберігаються в накопичувачі «tenant». Після цього дані про необхідну нерухомість передаються в процес «Вибір відповідного об'єкту для оренди» і проводиться аналіз існуючих в базі (накопичувач Realty) об'єктів для оренди. Після підбору об'єкту, обговорення, огляду об'єктів дані передаються в процес «Укладення договору». Цей процес також включає етап оформлення відповідної документації (із занесенням даних в накопичувач «lease») і видачі договору зі всіма даними клієнту.
Частина діаграми для даного прикладу буде мати наступний вигляд.
Рисунок 3 - DFD-діаграма системи
Контрольні питання
Назвіть особливості реляційної моделі даних.
З чого складається функціональна діаграма?
Які основні елементи діаграм потоків даних?
Лабораторна робота № 2
Розробка ER-моделі. Побудова реляційної схеми БД
Ціль роботи
Придбання навиків в розробці Er-моделі.
Придбання навиків в побудові реляційної схеми бази даних.
Порядок виконання роботи
По номеру варіанту вибрати завдання і відповідно до методичних вказівок, за матеріалами рекомендованої літератури і конспекту лекцій виконати завдання і оформити звіт по роботі.
Зміст звіту
Початкові дані і постановка задачі.
Опис послідовності дій при виконанні завдання.
Побудовані Er-схеми і реляційної схеми БД.
Методичні вказівки
Модель «сутність-зв'язок» є набором концепцій, які описують структуру бази даних і пов'язані з нею транзакції оновлення і імпортування даних. Основна ціль розробки високорівневої моделі полягає в створенні моделі призначеної для сприйняття користувачем даних і узгодженні великої кількості технічних аспектів. Концептуальна модель даних не залежить від апаратної платформи, яка використовується для реалізації БД. Основні концепції моделі «сутність-зв'язок» включають типи сутностей, зв'язків і атрибути.
Тип сутності представляє безліч об'єктів реального миру з однаковими властивостями. Сутність – це екземпляр типу сутностей, який може бути ідентифікований унікальним чином. Кожен тип сутності ідентифікується ім'ям і списком властивостей. На діаграмі сутності позначаються таким чином:
Атрибут – властивість типу сутностей або типу зв'язку. Значення атрибутів представляють основну частину відомостей, що зберігаються в базі даних. На діаграмах атрибути зображаються у вигляді еліпсів, приєднаних лінією до відповідної сутності і помічених ім'ям атрибута.
Тип зв'язку – осмислена асоціація між сутностями різних типів. Кожному типу зв'язку привласнюється ім'я, яке повинне описувати його функцію. Кожен зв'язок зображується у вигляді ромба з вказаним на ньому ім'ям зв'язку.
В даній моделі сутностями є Орендарі, Власники, Види, Договори.
Окремі властивості сутностей називаються атрибутами, вони також зображуються на діаграмі. Наприклад, сутність Орендарі описується унікальним кодом (id_tenant), ПІП, адресою, телефоном, додатковим коментарем. Атрибути сутностей містять значення, що описують кожну сутність. Кожен атрибут пов'язаний з набором значень, який називається доменом. Домен визначає всі потенційні значення, які можуть бути привласнені атрибуту. Різні атрибути можуть спільно використовувати один і той же домен. Окрім атрибутів, що описують сутність, існує поняття ключа. Під ключем мається на увазі елемент даних, який дозволяє унікально ідентифікувати окремі екземпляри деякого типу сутності.
Первинний ключ – це один або декілька атрибутів, значення яких унікальним чином ідентифікують кожний екземпляр сутності даного типу. Наприклад, код договору є потенційним ключем типу сутності Договір, оскільки він містить різні значення для кожного окремого договору. Вибір первинного ключа здійснюється з міркувань наявності гарантій унікальності його значень у нинішній момент часу і майбутньому. Проаналізуємо існуючі між сутностями зв'язки. В даній моделі вони будуть наступними
Назва зв'язку |
Сутності |
|
Оформлює |
Працівник |
Договір |
Укладає |
Власник |
Договір |
Укладає |
Орендар |
Договір |
Ураховує |
Нерухомість |
Договір |
Залежить |
Платня |
Нерухомість |
Розглянемо тепер структурні обмеження, які можуть накладатися на сутності. Найбільш поширеними є зв'язки «один до одного», «один до багато», «багато до багато». Види зв'язків визначаються, перш за все, виробничими правилами, встановленими на підприємстві. Ці правила називаються бізнес-правилами організації. Нижче приведена частина ER-моделі даної системи.
Рисунок 4 - ER-модель системи
Отримання реляційної схеми з ER-моделі виконується у декілька етапів.
Крок 1. Кожна проста сутність перетворюється на таблицю. Ім'я сутності стає ім'ям таблиці.
Крок 2. Кожний атрибут стає можливим стовпцем з тим же ім'ям; може вибиратися більш точний формат.
Крок 3. Компоненти унікального ідентифікатора сутності перетворюються на первинний ключ таблиці. Якщо є декілька можливих унікальних ідентифікаторів, вибирається той, що найбільш використовується.
Крок 4. Зв'язки багато-до-одного (і один-до-одного) стають зовнішніми ключами. Т.ч. робиться копія унікального ідентифікатора з кінця зв'язку «один», і відповідні стовпці складають зовнішній ключ.
Р
еляційна
модель даних для даної БД представлена
нижче на рисунку.
Рисунок 5 - Реляційна схема системи
Контрольні питання
Назвіть основні поняття моделі «Сутність – зв’язок».
Як позначаються сутності на діаграмах?
Які типи зв’язків Вам відомі?
Для чого призначений первинний ключ?
Для чого призначений зовнішній ключ?
Лабораторна робота № 3
Створення таблиць бази даних формату InterBase. Створення генераторів, трігерів, уявлень (віртуальних таблиць) і робота з ними.
Ціль роботи
Придбання навиків в роботі з програмою InterBase .
Придбання навиків в створенні бази даних.
Отримання знань про компоненти генератори, трігери, уявлення.
Придбання навиків в створенні генераторів, трігерів, уявлень і роботи з ними.
Порядок виконання роботи
По номеру варіанту вибрати завдання і відповідно до методичних вказівок, за матеріалами рекомендованої літератури і конспекту лекцій виконати завдання і оформити звіт по роботі.
Зміст звіту
Початкові дані і постановка задачі.
Опис послідовності дій при виконанні завдання.
Сценарій створення бази даних.
Початкові дані і постановка задачі.
Опис послідовності дій при виконанні завдання.
Сценарії створення генераторів, трігерів, уявлень.
Методичні вказівки
Останнім часом стратегічним напрямом фірми Borland стала стратегія |UPSIZING|, полягаюча в перенесенні існуючих додатків з персональних платформ до серверу баз даних (InterBase, Oracle, Informix ...). Borland пропонує дійсно зручну технологію переходу на архітектуру клієнт-сервер, дозволяючу використовувати напрацьовані додатки з мінімальними переробками.
Отже, InterBase володіє наступними особливостями:
а) Дає можливість одночасної роботи великої кількості користувачів без зайвих блокувань (в InterBase за умовчанням не використовується блокування по читанню) і без деградації продуктивності
б) Дозволяє вести журнал роботи з базами, проводити резервне копіювання баз в режимі on line.
в) Перенесення даних на сервер InterBase відразу підвищує здатність додатку навантаження і зменшує головний біль адміністратора системи.
Це далеко не всі переваги, які можна витягнути з архітектури клієнт-сервер. Сервер InterBase дозволяє зняти з додатку-клієнта все навантаження по підтримці цілісності бази, обчислювальне навантаження, залишивши додатку тільки функцію відображення даних і функцію введення/редагування даних, завдяки унікальним властивостям InterBase, як активного серверу баз даних.
Сервер, як правило, володіє істотно більшою обчислювальною потужністю, чим клієнти, перенесення інтелекту з клієнта на сервер підвищує швидкодію системи. Але найголовніше, що система стає більш стійкою і більш захищеною. При доступі до баз InterBase завжди відбувається авторизація користувача, а оскільки паролі зберігаються в спеціальній базі даних InterBase, зламати її зовні надзвичайно важко. Крім того, трігери, сигналізатори подій, процедури, UDF, механізми підтримки цілісності даних і розмежування доступу в InterBase зберігаються безпосередньо в базі даних і працюють незалежно від способу доступу до даних. Здатність швидко обробляти велику кількість різних запитів - безумовно одна з найважливіших характеристик InterBase .
По розробленій схемі БД, яка була представлена вище, створимо аналогічну БД у форматі InterBase. Нагадаємо призначення таблиць і полів БД:
-
Таблиця Власник
Owner
№ порядковий власника
Non
ПІБ власника
Ow
Адреса власника
AdO
Таблиця Договір
Lease
№ договори
Nlease
Код власника нерухомості
NOn
Код орендаря
NTn
Місцеположення нерухомості
AdR
Дата укладення договору
Ldate
Таблиця Орендар
Tenant
№ порядковий орендаря
NTn
ПІБ орендаря нерухомості
Tn
Адреса місця проживання орендаря
AdT
Таблиця Нерухомість
Realty
Місцеположення нерухомості
AdR
Тип нерухомості
Typ
Таблиця Платня
Rent
Тип нерухомості
Typ
Щомісячна арендна платня
Rent
Спочатку слід створити базу даних Exmpl.gdb формату InterBase. Для цього необхідно:
Запустити IBConsole і зареєструватися на Local Server, як адміністратор (loginName SYSDBA, password masterkey).
Вибрати меню Databases/Create Database і ввести в полі:
alias - псевдонім Exmpl,
file(s) - повне ім'я файла БД (шлях і ім'я файла з розширенням .GDB)
Далі необхідно створити домени. Домени - опис якого-небудь стовпця таблиці; це тип даних, визначуваний користувачем. Синтаксис створення домена має слідуючий формат
CREATE DOMAIN ім’я домена [AS] <тип_даних>
[DEFAULT { значення за умовчанням }]
[NOT NULL] [CHECK (VALUE <operator> значення )]
Як приклад створення доменів для даної БД приведемо наступний скрипт.
CREATE DOMAIN dnTyp AS CHAR(20)
DEFAULT '1-к. квартира'
CHECK (VALUE IN ('1-к. квартира', '2-к. квартира', 'дім'))
NOT NULL;commit;
CREATE DOMAIN dnDATE AS TIMESTAMP
CHECK (Value <= 'TODAY');commit;
Наступним етапом буде етап створення таблиць. Синтаксис створення таблиці має слідуючий формат
CREATE TABLE ім’я таблиці
( ім’я стовпця 1 тип даних стовпця1( або ім'я домена)
ім’я стовпця 2 тип даних стовпця2(або ім'я домена)
.......
PRIMARY KEY (ім’я стовпця));
Як приклад створення таблиці приведемо наступний скрипт.
CREATE TABLE RENT
( TYP DNTYP
RN DNRN
PRIMARY KEY (TYP));
Зовнішні ключі створимо у вигляді іменованих обмежень.
ALTER TABLE ім’я таблиці1
ADD CONSTRAINT FK ім’я зв’язку
FOREIGN KEY (ім’я стовпця таблиці1)
REFERENCES ім’я таблиці2 (ім’я стовпця таблиці2);
Приклад: ALTER TABLE REALTY
ADD CONSTRAINT FK RENT REALTY
FOREIGN KEY (TYP) REFERENCES RENT (TYP);
Для додавання даних в таблиці можна використовувати sql-оператор INSERT. Наприклад, додамо дані про господарів нерухомості (таблиця Owner).
INSERT INTO Owner (Non, Ow, AdO) VALUES (1, 'Іванов', 'Миру 36');
INSERT INTO Owner (Non, Ow, AdO) VALUES (2, 'Петров', 'Правди 2/36')
Часто в первинний ключ входять цифрові поля, значення яких повинні бути унікальні, тобто не повторюватися ні в якому іншому записі таблиці. Для локальних СУБД для вказаної цілі застосовуються автоінкрементні поля. В InterBase відсутній апарат автоінкрементних стовпців. Натомість для установки унікальних значень стовпців можна використовувати апарат генераторів.
Генератором називається збережений на сервері БД механізм, що повертає унікальні значення, ніколи не співпадаючі із значеннями, виданими тими ж генераторами у минулому.
Для створення генератора використовується оператор
create generator ім’я генератора ;
Приклад створення генератора: create generator owner_gen;
Для генератора необхідно встановити стартове значення за допомогою оператора SET GENERATOR ім’я генератора TO стартове значення;
При цьому стартове значення повинне бути цілим числом. Для отримання унікального значення до генератора потрібно звернутися за допомогою функції GEN_ID (ім’я генератора, крок);
Ця функція повертає збільшене на КРОК попереднє значення, видане генератором (або збільшене на КРОК стартове значення).
Привласнення ключовому стовпцю унікального значення може бути реалізовано за допомогою трігера, що викликається перед запам'ятовуванням нового запису в таблиці БД.
Трігер - це процедура, яка автоматично виконує деякі дії при вставці, видаленні або оновленні запису в таблиці.
Трігер створюється оператором
CREATE TRIGGER ім'я FOR ім’я таблиці
[ACTIVE | INACTIVE] {BEFORE | AFTER} {DELETE | INSERT | UPDATE} [POSITION номер позиції] AS <тіло_тригера> =
<список змінних > BEGIN <оператори >END;
Для визначення тіла трігера використовується процедурна мова. В нього додається можливість доступу до старого і нового значень стовпців змінного запису OLD і NEW. Ця можливість неприступна при визначенні тіла збережених процедур.
Приклад створення трігера для вставки унікального значення ключа:
set term ^;
create trigger ownerinsert for owner
before insert
position 0
as begin
new.non=gen id (owner gen,1);
end^
set term ;^
Також трігери можуть застосовуватися для забезпечення каскадних дій.
Якщо між двома або більш таблицями встановлені відносини посилальної цілісності (відносини «один-до-багато », «один-до-одного»), при зміні стовпця зв'язку в батьківській таблиці повинне бути змінено значення стовпця зв'язку у записів відповідних дочірніх таблиць. Така дія на дочірню таблицю носить назву каскадного оновлення. Якщо в батьківській таблиці видалений запис, повинні бути видалені всі пов'язані з нею записи в дочірній таблиці. Така дія на дочірню таблицю носить назву каскадного видалення.
Обмеження посилальної цілісності таблиць по зовнішньому ключу приводить до блокування зміни і видалення запису в батьківській таблиці, якщо для неї є дочірні записи в дочірній таблиці. Для реалізації автоматичного виконання каскадних оновлень і змін необхідно, по-перше, у визначенні БД видалити посилальні цілісності, блокуючі такі зміни, і, по-друге, визначити самі трігери для батьківської таблиці.
Трігер, що реалізовує каскадне оновлення в дочірній таблиці, буде в числі інших містити оператор
IF (OLD.поле зв'язку батька <> NEW.поле зв'язку батька ) THEN
UPDATE дочірня таблиця
SET поле зв'язку в дочірній таблиці=NEW. поле зв'язку батька
WHERE = OLD. поле зв'язку батька;
Трігер, що реалізовує каскадне видалення в дочірній таблиці, буде містити оператор
DELETE FROM дочірня таблиця
WHERE поле зв'язку дочірньої таблиці=поле зв'язку батька;
В БД може бути визначено уявлення, що виявляє собою віртуальну таблицю, в якій представлені записи з однієї або декількох таблиць. Порядок формування записів в уявленні визначається оператором SELECT. Для створення уявлення застосовується оператор
CREATE VIEW ім'я уявлення
[(ім'я стовпця уявлення 1 [,ім'я стовпця уявлення 2 .])]
AS <оператор_select> [WITH CHECK OPTION];
в якому після імені уявлення слідує необов'язковий список стовпців, оператор select - повноцінний оператор select, а необов'язковий параметр WITH CHECK OPTION визначає, чи допускати для уявлень, що обновляються, введення записів, що не задовольняють умові формування уявлення.
Для видалення уявлення використовується оператор
DROP VIEW ім'я уявлення;
Уявлення може створюватися як:
1. Вертікальній зріз таблиці, коли в уявлення включається підмножина стовпців таблиці.
2. Горізонтальній зріз таблиці, коли в уявлення включаються всі стовпці, але не всі записи таблиці.
3. Вертікально-горізонтальній зріз таблиці, коли в уявлення включається підмножина стовпців і підмножина рядків.
4. Підмножина рядків і стовпців з'єднання різних таблиць.
Створити уявлення, що містить інформацію про договори оренди, укладені за останні 30 днів, можна таким чином
CREATE VIEW LEASES ( NLEASE, NTN, ADT, NON, ADR, LDATE)
AS
select *
from lease
where ldate>'today'-30
Імена стовпців уявлення вказувати необов'язково. Вони повинні бути вказані у тому випадку, коли в стовпці уявлення визначається вираз, наприклад, арифметичний.
Після створення уявлення до нього можна звертатися як до звичайної таблиці.
select * from leases
Переваги створення уявлень:
Одного разу визначивши уявлення, не потрібно всякий раз формувати оператор select; це важливо для складних операторів, що виконують з'єднання декількох таблиць;
Уявлення може надавати підмножину стовпців в таблиці, що важливо для забезпечення збереження даних і, можливо, посилення безпеки.
Контрольні питання
Які типи даних підтримує InterBase?
Для чого створюються домени?
Для чого створюються генератори?
Для чого створюються тригери?
Яким чином реалізуються складні ланцюжки оновлень даних в уявленнях?
Які бувають уявлення?
Назвіть переваги використання уявлень.
Лабораторна робота № 4
Створення збережених процедур
Ціль роботи
Отримання знань про збережені процедури.
Придбання навиків в створенні збережених процедур і роботи з ними.
Порядок виконання роботи
По номеру варіанту вибрати завдання і відповідно до методичних вказівок, за матеріалами рекомендованої літератури і конспекту лекцій виконати завдання і оформити звіт по роботі.
Зміст звіту
Початкові дані і постановка задачі.
Опис послідовності дій при виконанні завдання.
Сценарії створення збережених процедур.
Методичні вказівки
Збережена процедура – це модуль, написаний на процедурній мові InterBase і який зберігається в базі даних як метадані (тобто як дані про дані). Збережені процедури пишуться на процедурній мові, яка залежить від конкретної СУБД. Вони можуть викликати один одного, читати і змінювати дані в таблицях, і їх можна викликати з клієнтського додатку, що працює з базою даних.
Збережені процедури звичайно використовуються при виконанні задач, що часто зустрічаються. Вони можуть мати аргументи, повертати значення, коди помилок і іноді набори рядків і колонок (такий набір даних іноді називається терміном dataset).
Існує два різновиди збережених процедур: процедури вибору та дії.
Процедури вибору можуть повертати більше одного значення. В додатку ім'я збереженої процедури вибору підставляється в оператор SELECT замість імені таблиці або уявлення.
Процедури дії взагалі можуть не повертати ніяких даних і використовуються для реалізації яких-небудь дій.
Збережена процедура створюється оператором
CREATE PROCEDURE ім'я процедури
[(вхідний параметр1<тип даних>[,вхідний параметр2 <тип даних> .])] [RETURNS [(вихідний параметр1 < тип даних > .])] AS <тіло_процедури>
Вхідні параметри служать для передачі в процедуру значень із додатку. Змінювати значення вхідних параметрів в тілі процедури безглуздо: ці зміни будуть забуті після закінчення роботи процедури.
Вихідні параметри служать для повернення результуючих значень. Значення вихідних параметрів встановлюються в тілі процедури, і після закінчення її роботи передаються в програму. І вхідні і вихідні параметри можуть бути опущені, якщо в них немає необхідності.
Тіло процедури має наступний формат:
[<оголошення локальних змінних процедури>]
DECLARE VARIABLE ім'я змінної1 <тип даних>;
[DECLARE VARIABLE ім'я змінної 2 <тип даних >; .]
BEGIN <оператор> [<оператор> .] END
Локальні змінні, якщо вони визначені в процедурі, мають термін життя від початку виконання процедури і до її закінчення. Зовні процедури такі локальні змінні невідомі, і спроба звернення до них викличе помилку. Локальні змінні використовують для зберігання проміжних значень. Приклад оголошення:
Create procedure Full adr (Tovar varchar(20))
Returns (gorod_address varchar(40))
As declare variable naiden_pokupatel varchar(20);
declare variable max_kolvo integer;
begin
. . .
end;
Операторні дужки begin.end, по-перше, обмежують тіло процедури, а по-друге, можуть використовуватися для вказівки меж складового оператора.
Оператор SELECT використовується в збереженій процедурі для видачі єдиного рядка. В порівнянні з синтаксисом звичайного оператора SELECT в процедурний оператор додано команду
INTO : змінна [, змінна..]
Вона служить для вказівки змінних або вихідних параметрів, в які повинні бути записані значення, що повертаються оператором SELECT . Оператор SELECT, що приводиться нижче повертає середнє по стовпцю RN і записує його в AVG RN.
SELECT AVG(RN)
FROM RENT
INTO :AVG_RN
Оператор FOR SELECT. . . DO має наступний формат
FOR
<оператор SELECT>
DO <оператор>;
Алгоритм роботи FOR SELECT. . . DO полягає в наступному. Виконується оператор SELECT, і для кожного рядка отриманого результуючого набору даних виконується оператор, наступний за словом DO. Цим оператором часто буває SUSPEND.
Оператор SUSPEND передає в додаток значення результуючих параметрів (перерахованих після слова RETURNS в описі функції), що мають місце на момент виконання SUSPEND. Після цього виконання процедури припиняється. Коли від оператора SELECT програми приходить запит на наступне значення вихідних параметрів, виконання збереженої процедури поновлюється.
Як приклад використовування операторів FOR SELECT. . . DO і SUSPEND приведемо скрипт створення наступної процедури. Дана процедура повертає всі параметри договорів, укладених орендарем TN NAME.
create procedure GET LEASE DATS (TN Name char(25))
RETURNS (Numb integer, Tn char(25), Typ char(20), Adr char(20), Rn numeric(6,2), Ow char(25), Ado char(20)), LDate Timestamp
AS
begin
for Select lease.NLease, tenant.Tn, rent.Typ, lease.Adr, rent.Rn, Owner.Ow, Owner.Ado, lease.LDate
from lease, Owner, realty, rent, tenant
Where (tenant.Tn=:Tn_Name)
and (Owner.Non=Lease.Non)
and (tenant.Ntn=Lease.Ntn)
and (realty.Adr= lease.Adr)
and (rent.Typ= realty.Typ)
INTO :NUMB : Tn : Typ : Adr : Rn : Ow : Ado : LDate
DO
SUSPEND;
END
Оператор EXECUTE PROCEDURE ім'я процедури [:параметр [ :параметр]] [RETURNING VALUES :параметр [ :параметр] .]];
викликає іншу процедуру. При цьому після імені процедури, що викликається, перераховуються вхідні параметри, якщо вони є, а після RETURNING VALUES – вихідні параметри.
Також за допомогою даного оператора можна викликати збережену процедуру в утиліті WISQL InterBase. Наприклад, виклик процедури GET LEASE DATS з вказівкою вхідного параметра – прізвища орендаря приведе до видачі вихідних параметрів у вікні результатів WISQL InterBase. За допомогою WISQL зручно відладжувати збережені процедури. Після відладки синтаксису і отримання правдоподібних результатів подальшу відладку слід проводити в додатку клієнта.
Зміна збереженої процедури проводиться оператором
ALTER PROCEDURE ім'я процедури
[(вхідний параметр1<тип даних>[,вхідний параметр2 <тип даних> .])] [RETURNS [(вихідний параметр1 < тип даних > .])]
AS <тіло_процедури>
Після виконання оператора попереднє визначення процедури замінюється на нове визначення параметрів, змінних і тіла процедури.
Для видалення береженої процедури з бази даних використовується оператор
DROP PROCEDURE ім'я процедури;
Переваги використовування збережених процедур:
а) одну процедуру можна використовувати багато якими додатками;
б) розвантаження додатків клієнта шляхом перенесення частини коду на сервер і внаслідок цього спрощення клієнтських додатків;
в) при зміні збереженої процедури всі зміни негайно стають доступні для всіх клієнтських додатків; при внесенні ж змін в додаток клієнта потрібне повторне розповсюдження нової версії клієнтського додатку між користувачами;
г) поліпшені характеристики виконання, пов'язані з тим, що збережені процедури виконуються сервером, зокрема – зменшений мережний трафик