3 Типові рішення

Задача 1

П одати мережеві структури за допомогою надлишкових ієрархічних структур.

Відповідь

а)

б)

в)

Задача 2

Який тип ключа має відношення СТУДЕНТ – ВИКЛАДАЧ.

Студент	Викладач	Дисципліна
Іванов Петров Іванов Петров	Харитонов Харитонов Міщенко Борисов	фізика фізика інформатика інформатика

Відповідь

Розрізняють такі типи ключів: простий, повністю складений, напівскладений, зовнішній, первинний, можливий.

Простим називається ключ, що складається з одного атрибута, причому атрибут повинен бути атомарним, а екземпляри даного атрибута – унікальні.

Атомарним є ключ, значення якого сприймається програмою (СУБД) як неподільний елемент даних, навіть якщо він створений з декількох об'єктів. Наприклад, атомарність атрибута ДАТА ЗДАЧІ ЕКЗАМЕНА в відношенні означає неможливість виділення з дати окремого числа, місяця та року.

Повністю складеним називається ключ, що містить декілька атрибутів, причому між цими атрибутами існує відображення (залежність) БАГАТО-ДО-БАГАТЬОХ.

Атрибути, що складають такий ключ, не залежать один від одного.

Напівскладеним називається ключ, що містить декілька атрибутів, між якими на відміну від повністю складеного ключа існує залежність Б:1 (БАГАТО-ДО-ОДНОГО).

Тут атрибути в ключі впорядковуються за принципом: кожний наступний уточнює попередній.

Отже, маємо приклад повністю складеного ключа в відношенні СТУДЕНТ-ВИКЛАДАЧ.

Задача 3

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

Відповідь

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

ВIДЕОФIЛЬМ, ВIДЕОКАСЕТА, КЛIЄНТ.

Складемо перелік найбільш суттєвих характеристик кожного інформаційного об'єкта.

ВIДЕОФIЛЬМ (назва фільму; назва кiностудiї; прізвище режисера; виконавець головної чоловічої ролі; виконавиця головної жіночої ролі; тривалість фільму; жанр фільму; оцінка фільму кінокритиками).

ВIДЕОКАСЕТА (номер касети; перелік фiльмiв, що записані на касеті; час в хвилинах, що займає на касеті кожен з фiльмiв; фірма, яка виготовляє касети; код, що визначає основні характеристики касети; остання дата видачі касети в прокат).

КЛIЄНТ (код клієнта, що служить для його швидкої iдентифiкацiї; прізвище, ім’я, по батькові; адреса; номер телефону, домашнього або робочого; паспортні дані; критерій вибору клієнтом фільму).

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

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

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

R (Nazva, N_kasset, N_pp, Kinost, Rezh, Akter, Aktrisa, Dl_film, Kod_kat, Naz_kat, Ocenka, Opr_f, Firma, Dlitel, Kod_p, FIO, Adr_p, Tel_p, Ser_pasp, Kr_v, Data_last).

Таблиця 3.1 – Початковий перелік атрибутів для формування універ­сального відношення бази даних студії прокату вiдеофiльмiв

Назва атрибута	Ім’я поля	Коментар
Назва фільма	Nazva	У студії можуть бути фільми з однаковими назвами
Номер касети	N_kaset	Кожна касета має унікальний номер
Номер за поряд-ком	N_pp	Номер фільма за порядком запису на касеті
Кіностудія	Kinost	Назва кiностудiї, що зняла фільм
Режисер	Rezh	Прізвище режисера, що зняв фільм
Актор	Akter	Прізвище актора, що знявся в головній чоловiчiй ролі
Актриса	Aktrisa	Прізвище актриси, що знялась в головній жiночiй ролі
Тривалість фільма	Dl_film	Тривалість фільма в хвилинах
Код категорії	Kod_kat	Числова характеристика якості запису
Категорія фільма	Naz_kat	Жанр фільма
Оцінка	Ocenka	Оцінка фільма критиками
Касета	Opr_F	Показник основної характеристики касети
Фірма	Firma	Виготовлювач касети
Тривалість	Dlitel	Час в хвилинах, що займає фільм
Код користувача	Kod_p	Код користувача, що взяв вiдеофiльм (якщо фільм не на руках "0")
Користувач	FIO	Прізвище, ім’я, по батькові користувача
Адреса	Adr_p	Адреса користувача
Телефон	Tel_p	Телефон користувача
Паспорт	Ser_pasp	Паспортні дані користувача
Критерій вибору	Kr_v	Критерій вибору фiльмiв користувачем
Остання дата про-кату касети	Data_last	Дата, коли касета з фільмом була взята на прокат останній раз

Задача 4

Розробити ER-модель предметної області "Студія прокату відео­фiльмiв".

Відповідь

Для побудови ER-моделі предметної області "Студія прокату вiдеофiльмiв", в якості сутностей оберемо такі об'єкти предметної області (з перерахуванням ключових атрибутів кожної сутності):

Ф IЛЬМ Nazva (назва фільму), N_kasset (номер касети), N_pp (но­мер фільму за порядком запису на касеті);

К АТЕГОРIЯ ФIЛЬМУ Kod_kat (код категорія фільму);

К АСЕТА N_kasset (номер касети);

В ИЗНАЧНИК Opr_f (характеристика параметрів касети);

К ОРИСТУВАЧ Kod_p (код користувача).

Необхiднiсть введення складеного ключа для сутності ФIЛЬМ обумовлена вимогою, що у вiдеотецi можуть бути рiзнi фільми з назвами, які збігаються. До того ж, вони можуть бути записані на одну касету.

Характеристики зв’язків виділених сутностей наведені в таблиці 3.2. ER-модель предметної області "Студія прокату вiдеофiльмiв", що побудована на основі проведеного аналізу, показана на рис. 3.1.

Таблиця 3.2 – Характеристики зв’язків предметної області "Студія прокату вiдеофiльмiв"

Ім'я сутності 1	Ім'я сутності 2	Тип зв'язку	Ім'я зв'язку	Клас належності
Фільм	Категорія	N:1	Має	Обов., обов.
Касета	Визначник	N:1	Характеризує	Обов., обов.
Фільм	Касета	N:M	Записаний	Обов., обов.
Користувач	Касета	N:M	Тримає	Необ., необ.

Рисунок 3.1 – Результуюча ER-модель предметної області "Студія прокату вiдеофiльмiв"

Задача 5

Форми нормалізації БД. Взаємозв’язок між основними нормальними формами відношення.

Відповідь

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

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

Кожній нормальній формі відповідає деякий визначений набір обмежень. Відношення знаходиться в деякій нормальній формі, якщо задовольняється властивий їй набір обмежень.

Кожна нормальна форма є більш обмеженою і більш бажаною, ніж попередня. Це пов'язано з тим, що в (N+1)-ій нормальній формі вилучаються деякі небажані властивості, які характерні N-ій нормальній формі. Теорія нормалізації ґрунтується на наявності тієї або іншої залежності між полями таблиці.

Основні властивості нормальних форм:

• кожна наступна нормальна форма в деякому змісті краща попередньої;

• при переході до наступної нормальної форми властивості попередніх нормальних форм зберігаються.

Найбільш важливі нормальні форми відношень ґрунтуються на фундаментальному, в теорії реляційних баз даних, понятті функціональної залежності.

У відношенні R атрибут Y функціонально залежить від атрибута X (X і Y можуть бути складовими) у тому і тільки в тому випадку, якщо кожному значенню X відповідає в точності одне значення Y: X Y.

Функціональна залежність X Y називається повною, якщо атрибут Y не залежить функціонально від будь-якої підмножини X.

Функціональна залежність називається транзитивною, якщо з функціональних залежностей X Y та Y Z випливає, що X Z.

Наприклад, Вінниця входить до Поділля, а Поділля - до України. Для даного прикладу має місце транзитивна залежність ВІННИЦЯ УКРАЇНА.

Неключовим атрибутом називається будь-який атрибут відношення, що не входить до складу первинного ключа.

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

Відношення R задано в першій нормальній формі, якщо воно задано у вигляді множини своїх кортежів, які не повторюються.

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

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

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

Відношення R знаходиться в третій нормальній формі (3НФ) у тому і тільки в тому випадку, якщо знаходиться в 2НФ і кожний неключовий атрибут нетранзитивно залежить від первинного ключа. На практиці в більшості випадків три нормальні форми схем відношень є достатніми і, як правило, процес проектування реляційної бази закінчується приведенням до третьої нормальної форми. Хоча іноді корисно продовжити процес нормалізації.

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

Відношення R знаходиться в нормальній формі Бойса-Кодда (НФБК) у тому і тільки в тому випадку, якщо кожний детермінант є можливим ключем. Якщо у відношенні є тільки один можливий ключ (який є первинним ключем), то це означення стає еквівалентним означенню третьої нормальної форми.

Послідовність етапів нормалізації:

1. Перехід від структурної моделі даних до плоских двовимірних відношень (таблицям).

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

3. Усунення всіх транзитивних залежностей атрибутів, які не є основними, від усіх ймовірних ключів.

4. Усунення всіх нетривіальних багатозначних залежностей атрибутів, які не є основними, від усіх ймовірних ключів.

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

Задача 6

Виконати нормалізацію відношення СПІВРОБІТНИКИ - ВІДДІЛИ- ПРОЕКТИ (НОМЕР_СПІВРОБІТНИКА, ЗАРПЛАТА_СПІВРОБІТНИКА, НОМЕР_ВІДДІЛУ, НОМЕР_ПРОЕКТУ, ЗАВДАННЯ_СПІВРОБІТНИКА).

Відповідь

Розглянемо схему відношення:

СПІВРОБІТНИКИ - ВІДДІЛИ - ПРОЕКТИ (НОМЕР_СПІВРОБ, ЗАРП_ СПІВРОБ, НОМЕР_ВІДДІЛ, НОМЕР_ПРО, ЗАВДАННЯ_СПІВРОБ).

Використаємо скорочення: СПІВРОБ - співробітник, ЗАРП – зарплата, ПРО – проект.

Первинний ключ:

НОМЕР_СПІВРОБ, НОМЕР_ПРО.

Функціональні залежності:

НОМЕР_СПІВРОБ -> ЗАРП_СПІВРОБ,

НОМЕР_СПІВРОБ -> НОМЕР_ВІДДІЛ,

НОМЕР_ВІДДІЛ -> ЗАРП_СПІВРОБ,

НОМЕР_СПІВРОБ, НОМЕР_ПРО -> ЗАВДАННЯ_СПІВРОБ.

Хоча первинним ключем є складовий атрибут НОМЕР_СПІВРОБ, НОМЕР_ПРО, атрибути ЗАРП_СПІВРОБ і НОМЕР_ВІДДІЛ функціональ­но залежать від частини первинного ключа, тобто атрибута НОМЕР_СПІВРОБ. В результаті, неможливо вставити у відношення СПІВРОБІТНИКИ-ВІДДІЛИ-ПРОЕКТИ кортеж, що описує співробітника, який ще не виконує ніякого проекту (первинний ключ не може містити невизначене значення). При видаленні кортежу не тільки руйнується зв'язок даного співробітника з даним проектом, але й втрачається інформація про те, в якому відділі він працює. При переведенні співробітника в інший відділ необхідно модифікувати всі кортежі, які описують цього співробітника, або одержимо неузгоджений результат. Усунемо зазначені аномалії шляхом нормалізації.

Виконаємо декомпозицію відношення СПІВРОБІТНИКИ-ВІДДІЛИ- ПРОЕКТИ в два відношення СПІВРОБІТНИКИ-ВІДДІЛИ і СПІВРОБІТ­НИКИ- ПРОЕКТИ:

СПІВРОБІТНИКИ-ВІДДІЛИ (НОМЕР_СПІВРОБ, ЗАРП_СПІВРОБ, НОМЕР_ВІДДІЛ).

Первинний ключ:

НОМЕР_СПІВРОБ.

Функціональні залежності:

НОМЕР_СПІВРОБ -> ЗАРП_СПІВРОБ,

НОМЕР_СПІВРОБ -> НОМЕР_ВІДДІЛ,

НОМЕР_ВІДДІЛ -> ЗАРП_СПІВРОБ,

СПІВРОБІТНИКИ-ПРОЕКТИ (НОМЕР_СПІВРОБ, НОМЕР_ПРО, ЗАВ­ДАННЯ_СПІВРОБ).

Первинний ключ:

НОМЕР_СПІВРОБ, НОМЕР_ПРО.

Функціональна залежність:

НОМЕР_СПІВРОБ, НОМЕР_ПРО -> ЗАВДАННЯ_СПІВРОБ.

Кожне з цих двох відношень знаходиться в 2НФ. В них усунуті відзначені вище аномалії.

Задача 7

Навести приклади відношень, які знаходяться у 2НФ. Виконати над ними нормалізацію.

Відповідь

Розглянемо відношення:

ГУРТОЖИТОК (ПІБ_СТУДЕНТА, НОМЕР_ГРУПИ, НОМЕР_КІМНАТИ, СТАРОСТА_КІМНАТИ). Воно знаходиться в другій нормальній формі, але не в третій, тому що атрибут СТАРОСТА_КІМНАТИ залежить від атрибута НОМЕР_КІМНАТИ, який, у свою чергу, залежить від атрибута ПІБ_ СТУДЕНТА і, отже, СТАРОСТА_КІМНАТИ транзитивно залежить від ПІБ_СТУДЕНТА. Це відношення можна привести до необхідної форми шляхом його розщеплення на два:

1. СТУДЕНТ-ГУРТОЖИТОК (ПІБ_СТУДЕНТА, НОМЕР_ГРУПИ, НОМЕР_ КІМНАТИ);

2. КІМНАТА-ГУРТОЖИТОК (НОМЕР_КІМНАТИ, СТА- РОСТА_КІМНАТИ).

Таким чином, усунено транзитивну залежність, а ступінь нормалізації відношення підвищено.

Задача 8

Оцінити спроектовані НФБК-відношення.

Відповідь

Розглядаючи відношення, отримані при проектуванні бази даних «Студія прокату вiдеофiльмiв», можна помітити, що:

- ні одна 3НФ не повторюється більше одного разу;

- цей набір 3НФ є мiнiмальним.

Аналіз відношень показує, що не можна вказати серед них ні одного, всі атрибути якого були б підмножиною атрибутів іншого відношення. Крім того, неможливо з'єднати три відношення так, щоб у підсумку були отримані всі атрибути четвертого відношення. Отже, ні одне з відношень не є надлишковим.

Аналіз результуючих відношень бази даних відеотеки:

FILM (< Nazva, N_kasset, N_pp >, Kinost, Rezh, Akter, Aktrisa, Dl_film, Kod_kat, Ocenka);

KASSET (< N_kassset >, Opr_f, Kod_p, Data_last);

POLS (< Kod_p >, FIO, Adr_p, Tel_p, Ser_pasp, Kr_v);

KODKAT (< Kod_kat >, Naz_kat);

FIRMA (< Opr_f >, Firma, Dlitel)

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

Задача 9

Виконати операцію декартового добутку над реляційними відношеннями.

Відповідь

Операція декартового добутку виконується між кортежами реляційних відношень аргументів, а результатом є конкатенація (“зчеплення”, позначається “”) відповідних кортежів, що є реляційне відношення з такими характеристиками:

• ступінь (R_АR_В)= ступінь(R_А) + ступінь(R_В);

• потужність(R_АR_В)= потужність(R_А) × потужність(R_В).

Таким чином, результуюче відношення матиме характеристики: ступінь результуючого відношення дорівнює 4 (2+2) , а потужність - 8 (4×2).

.