БД: Практична робота №2.
Практична робота №2.
Побудова реляційної схеми.
Мета роботи :
Отримання практичних навичок проектування баз даних за допомогою методу суть - зв'язок.
Варіанти індивідуальних завдань.
Див. додаток 1.
Теоретичні відомості.
Загальні відомості
Процес проектування БД включає:
Виділення сутей і зв'язків між ними;
Побудова діаграм ER - типа з урахуванням усіх сутностей і їх зв'язків;
Формування попередніх таблиць з вказівкою для кожної з них можливого первинного ключа;
Додавання неключових атрибутів в таблиці;
Приведення таблиць до 3-ої нормальної форми;
Якщо необхідно, перегляд ER -діаграм.
Процес проектування допускає повернення до попередніх етапів для перегляду раніше прийнятих рішень.
Метод сутність-зв'язок
Основні поняття методу
Сутність - об'єкт, інформація про яке зберігається в БД. Наприклад: ВИКЛАДАЧ, ДИСЦИПЛІНА, КАФЕДРА, ГРУПА.
Атрибут сутності - властивість сутності. Сутність ВИКЛАДАЧ має атрибути Прізвище, Посаду, Стаж і т. д.
Ключ сутності - атрибут або набір атрибутів, використовуваний для ідентифікації екземпляра суті.
Зв'язок двох або більш сутностей - залежність між атрибутами цих сутей. Наприклад, ВИКЛАДАЧ BEДЕТ ДИСЦИПЛІНУ (Проскура С.Л. ВЕДЕ "ООП").
ступінь зв'язку - характеристика зв'язку (1:1, 1:М, М:1, М: М). ;
клас приналежності екземплярів сутності - обов'язковий або необов'язковий;
діаграми ER - екземплярів;
діаграми ER -типа.
Ступінь зв'язку сутностей і клас приналежності.
Зв'язок, який завжди сполучає дві сутності, називається бінарним.
Усі питання теми розглядатимемо на прикладі БД "Учбова частина".
Приклад. Побудувати діаграми ЕR - екземплярів і ЕR - типу для сутностей ВИКЛАДАЧ і ДИСЦИПЛІНА із зв'язком ВЕДЕ.
Припустимо що:
кожен викладач веде одну дисципліну ;
кожна дисципліна ведеться одним викладачем;
Діаграма ЕR -екземплярів.
Викладач |
Веде |
Дисципліна |
Акимова |
|
БД |
Проскура |
|
ООП |
Назаренко |
|
WEB |
Тип такого зв'язку - один до одного (1:1). Клас приналежності (КП) – обов’язковий.
Умовний запис: 1:1, О - О.
Діаграма ЕR -типів.
Обов'язкова участь в зв'язку екземплярів сутності відмічається блоком з точкою усередині.
Висновок:
Найважливішим чинником у формуванні бінарного відношення є число пов'язаних екземплярів записів в обох таблицях. Представимо собі єдиний запис в сутності ВИКЛАДАЧ, і задамося питанням, скільки записів в сутності ДИСЦИПЛІНА може бути з ним пов'язано. Один викладач може вести один предмет, тому один запис у ВИКЛАДАЧ може бути пов'язаний відношенням з одним записом в ДИСЦИПЛІНА .
Тепер представимо собі єдиний запис в ДИСЦИПЛІНА і запитаємо, скільки записів у ВИКЛАДАЧ може бути з ним пов'язано. Одну дисципліну може вести один викладач.
Оскільки в кожній з двох сутностей кожен запис може бути пов'язаний з одним записом в іншій суті, то ці сутності знаходяться між собою у зв'язку "один-до-одного".
Припустимо що:
кожен викладач веде не більше однієї дисципліни. Тому деякі викладачі можуть не вести жодної дисципліни. В даному випадку, КП – необов’язковий.
кожна дисципліна ведеться не більше ніж одним викладачем. Тому, є дисципліни, які не веде жоден з викладачів.
Діаграма ЕR - екземплярів.
Викладач |
Веде |
Дисципліна |
Акимова |
|
БД |
Проскура |
|
ООП |
Назаренко |
|
WEB |
Колодяжный |
|
Паскаль |
Діаграма ЕR - типів:
1
1
Тип такого зв'язку - один до одного (1:1).
Умовний запис: 1:1, Н - Н.
Припустимо що:
кожен викладач може вести декілька дисциплін;
кожну дисципліну може вести тільки один викладач;
Діаграма ER - екземплярів
Викладач |
Веде |
Дисципліна |
Акимова |
|
WEB |
Проскура |
|
БД |
Назаренко |
|
ООП |
|
|
Ассемблер |
|
|
Информатика |
|
1 М |
|
Умовний запис: 1:М, О - О.
Зв'язок 1:М, варіант Н - О.
Діаграма ER - екземплярів
Викладач |
Веде |
Дисципліна |
Акимова |
|
WEB |
Проскура |
|
БД |
Назаренко |
|
ООП |
Колодяжный |
|
Ассемблер |
|
|
Информатика |
|
1 М |
|
Діаграма ER - типів
Припустимо що:
кожен викладач може вести тільки одну дисципліну. КП - О
кожну дисципліну може вести декілька викладачів. Є дисципліни, які не веде жоден з викладачів. КП – Н.
Умовний запис: М:1, О - О.
Викладач |
Веде |
Дисципліна |
Акимова |
|
Информатика |
Проскура |
|
|
Назаренко |
|
Основы программирования |
Колодяжный |
|
|
|
М 1 |
|
Зв'язок типу М : М. Варіант О - Н
Діаграма ER – екземплярів
Викладач |
Веде |
Дисципліна |
Акимова |
|
Информатика |
Проскура |
|
Паскаль |
Назаренко |
|
Основы программирования |
Колодяжный |
|
БД |
|
|
ООП |
|
|
Ассемблер |
|
|
Java |
|
М М |
|
Діаграма ER –типів
Правила формування бінарних відношень.
Формування відношень для зв'язку 1:1
Правило 1.
Якщо ступінь зв'язку 1:1 і варіант КП О - О, то формується одна таблиця, первинним ключем якого може бути ключ будь-якою з двох сутностей.
С1, С2 - сутності 1 і 2;
К1, К2 ключі першої і другої сутностей відповідно;
R1 - відношення 1, сформоване на основі першої і другої сутностей;
К1 V К2 - ключем відношення R1 може бути або К1, або К2.
Приклад. Зв'язок 1 : 1, КП О - О
Сутність ВИКЛАДАЧ Сутність ДИСЦИПЛІНА
Ном_препод |
ПІБ |
Стаж |
01 |
Акимова А.В. |
3 |
02 |
Проскура С.Л. |
20 |
03 |
Колодяжный В.А. |
6 |
04 |
Назаренко В.С. |
5 |
-
Код_предмета
Години
11
128
12
128
13
64
14
64
1(О) 1(О)
Згідно з правилом 1, об'єднаємо їх в одне відношення.
Результат:
Таблиця ВИКЛАДАЧ-ДИСЦИПЛІНА
-
Ном_препод
ПІБ
Стаж
Код_предмета
Години
01
Акимова А.В.
3
11
128
02
Проскура С.Л.
20
12
128
03
Колодяжный В.А.
6
13
64
04
Назаренко В.С.
5
14
64
Викладач Акимова веде тільки дисципліну з кодом 11, а дисципліна 11 ведеться тільки Акимовою (зв'язок 1:1). Порожні поля відсутні, отже КП - обов'язковий оскільки немає викладачів, які б щось не вели, і немає дисциплін, які ніхто не веде.
Таким чином, одного відношення достатньо. Первинним ключем може бути або атрибут, Ном_препод або Код_предмета.
Правило 2.
Якщо ступень зв'язку 1:1 і КП О - Н, то під кожну з сутностей формується по таблиці з первинними ключами, що являються ключами відповідних сутей. Далі до таблиці з обов'язковим КП, додається як атрибут ключ таблиці з необов'язковим КП.
Приклад. Зв'язок 1 : 1, КП О – Н - є дисципліни, які ніхто не читає.
Сутність ВИКЛАДАЧ Сутність ДИСЦИПЛІНА
Ном_препод |
ПІБ |
Стаж |
01 |
Акимова А.В. |
3 |
02 |
Проскура С.Л. |
20 |
03 |
Колодяжный В.А. |
6 |
04 |
Назаренко В.С. |
5 |
-
Код_предмета
Години
11
128
12
128
13
64
14
64
15
64
1(О) 1(Н)
Якщо зробимо об’єднану сутність, то в ній будуть присутні пусті поля – це недопустимо.
ВИКЛАДАЧ-ДИСЦИПЛІНА
-
Ном_препод
ПІБ
Стаж
Код_предмета
Години
01
Акимова А.В.
3
11
128
02
Проскура С.Л.
20
12
128
03
Колодяжный В.А.
6
13
64
04
Назаренко В.С.
5
14
64
-
-
-
15
64
Застосуємо правило 2 - виділимо два відношення. Додамо у ВИКЛАДАЧ, як зовнішній ключ, стовпець КОД_ПРЕДМЕТА.
Результат.
Таблиця ВИКЛАДАЧ Таблиця ДИСЦИПЛІНА
Ном_препод |
ПІБ |
Стаж |
Код_предмета |
01 |
Акимова А.В. |
3 |
11 |
02 |
Проскура С.Л. |
20 |
12 |
03 |
Колодяжный В.А. |
6 |
13 |
04 |
Назаренко В.С. |
5 |
14 |
-
Код_предмета
Години
11
128
12
128
13
64
14
64
15
64
Правило 3.
Якщо ступінь зв'язку 1:1 і КП Н - Н, то необхідно створити 3 таблиці. Дві будуть відповідати зв'язаним сутностям. Ключі сутностей будуть первинними ключами таблиць. Третя таблиця буде зв'язувати дві інші, тому її ключ об'єднує ключові атрибути зв'язаних таблиць.
Діаграма ER - типа
Приклад.
Зв'язок 1 : 1, КП Н – Н - є дисципліни, які ніхто не читає і є викладачі, які нічого не ведуть.
ВИКЛАДАЧ ДИСЦИПЛІНА
Ном_препод |
ПІБ |
Стаж |
01 |
Акимова А.В. |
3 |
02 |
Проскура С.Л. |
20 |
03 |
Колодяжный В.А. |
6 |
04 |
Назаренко В.С. |
5 |
-
Код_предмета
Години
11
128
12
128
13
64
14
64
15
64
1 (Н) 1 (Н)
Об’єднана сутність.
ВИКЛАДАЧ-ДИСЦИПЛІНА
-
Ном_препод
ПІБ
Стаж
Код_предмета
Години
01
Акимова А.В.
3
11
128
02
Проскура С.Л.
20
12
128
-
-
-
13
64
03
Колодяжный В.А.
6
-
-
04
Назаренко В.С.
5
14
64
-
-
-
15
64
Результат:
Ном_препод |
Код_предмета |
01 |
11 |
02 |
12 |
04 |
14 |
Код_предмета |
Години |
11 |
128 |
12 |
128 |
13 |
64 |
14 |
64 |
15 |
64 |
Ном_препод |
ФИО |
Стаж |
01 |
Акимова А.В. |
3 |
02 |
Проскура С.Л. |
20 |
03 |
Колодяжный В.А. |
6 |
04 |
Назаренко В.С. |
5 |
Формування відношення для зв'язку 1 :М
Якщо дві сутності С1 і С2 пов'язані як 1:М, сутність С1 називається однозв'язаною (1-зв'язною), а сутність С2 - багатозв'язковою (М зв'язкової).
Правило 4.
Якщо ступінь зв'язку між сутностями 1:М (чи М:1) і клас приналежності М-зв'язкової сутності обов'язковий, то досить формування двох таблиць (по одному на кожну з сутностей). Ключ 1-зв'язної сутності додається як атрибут (зовнішній ключ) у таблицю, що відповідає М-зв'язковою сутності.
Сутність ВИКЛАДАЧ Сутність ДИСЦИПЛІНА
Ном_препод |
ФИО |
Стаж |
01 |
Акимова А.В. |
3 |
02 |
Проскура С.Л. |
20 |
03 |
Колодяжный В.А. |
6 |
04 |
Назаренко В.С. |
5 |
-
Код_предмета
Часы
11
128
12
128
15
128
13
64
14
64
1(Н) М (О)
Об’єднана таблиця.
ВИКЛАДАЧ-ДИСЦИПЛІНА
-
Ном_препод
ПІБ
Стаж
Код_предмета
Години
01
Акимова А.В.
3
11
128
01
Акимова А.В.
3
12
128
02
Проскура С.Л.
20
15
128
02
Проскура С.Л.
20
13
64
03
Колодяжный В.А.
6
-
-
04
Назаренко В.С.
5
14
64
Проблеми:
присутні стовпці з порожніми полями (викладач не веде дисципліни)
надлишкове дублювання даних (повторюється стаж викладача) в стовпцях з відомостями про викладачів, що ведуть декілька дисциплін.
Відповідно до правила 4 перетворимо відношення в два відношення .
Діаграма ER – типа
Результат:
Таблиця ВИКЛАДАЧ (R1) Таблиця ДИСЦИПЛІНА (R2)
Ном_препод |
ФИО |
Стаж |
K1 |
|
|
01 |
Акимова А.В. |
3 |
02 |
Проскура С.Л. |
20 |
03 |
Колодяжный В.А. |
6 |
04 |
Назаренко В.С. |
5 |
-
Код_предмета
Часы
Ном_препод
K1
K2
11
128
01
12
128
01
15
128
02
13
64
02
14
64
04
Правило 5.
Якщо ступень зв'язку 1:М (М:1) і клас приналежності М-зв'язкової сутності є необов'язковим, то потрібне формування трьох таблиць. Дві таблиці відповідають зв'язаним сутностям, ключі яких є первинними в цих таблицях. Третя таблиця є зв'язкою між першими двома (її ключ об'єднує ключові атрибути зв'язаних таблиць).
Приклад. Зв'язок між сутями 1:М, КП Н-Н.
Те, що клас приналежності 1-зв'язної суті необов'язковий не принципово.
Сутність ВИКЛАДАЧ Сутність ДИСЦИПЛІНА
Ном_препод |
ФИО |
Стаж |
01 |
Акимова А.В. |
3 |
02 |
Проскура С.Л. |
20 |
03 |
Колодяжный В.А. |
6 |
04 |
Назаренко В.С. |
5 |
-
Код_предмета
Часы
11
128
12
128
15
128
13
64
14
64
16
128
1(Н) М (Н)
ВИКЛАДАЧ-ДИСЦИПЛІНА
-
Ном_препод
ПІБ
Стаж
Код_предмета
Години
01
Акимова А.В.
3
11
128
01
Акимова А.В.
3
12
128
02
Проскура С.Л.
20
15
128
02
Проскура С.Л.
20
13
64
03
Колодяжный В.А.
6
-
-
04
Назаренко В.С.
5
14
64
-
-
-
16
128
Проблеми:
Є порожні поля в стовпцях, які містять :
а) дані про викладачів, що не ведуть дисциплін;
б) дані про дисципліни, які не ведуться викладачами.
Надлишкове дублювання даних про викладачів, які ведуть більш за одну дисципліну.
Застосуємо правило 5.
Ном_препод |
Код_предмета |
01 |
11 |
01 |
12 |
02 |
15 |
02 |
13 |
04 |
14 |
Ном_препод |
ФИО |
Стаж |
01 |
Акимова А.В. |
3 |
02 |
Проскура С.Л. |
20 |
03 |
Колодяжный В.А. |
6 |
04 |
Назаренко В.С. |
5 |
Код_предмета |
Часы |
11 |
128 |
12 |
128 |
15 |
128 |
13 |
64 |
14 |
64 |
15 |
64 |
16 |
128 |
Формування відношень для зв'язку М:М
Правило 6.
Якщо ступінь зв'язку М:М, то незалежно від класу приналежності сутностей формуються три таблиці. Два відповідають зв'язаним сутностям і їх ключі є первинними ключами цих таблиць. Третя таблиця є зв'язковою між першими двома, а її ключ об'єднує ключові атрибути зв'язаних таблиць.
Розглянемо варіант з класом приналежності сутей Н-Н, хоча, згідно з правилом 6, він може бути довільним.
Приклад.
Ступень зв'язку - М:М, клас приналежності для суті ВИКЛАДАЧ обов'язковий, а для суті ДИСЦИПЛІНА - необов'язковий.
Сутність ВИКЛАДАЧ Сутність ДИСЦИПЛІНА
Ном_препод |
ФИО |
Стаж |
01 |
Акимова А.В. |
3 |
02 |
Проскура С.Л. |
20 |
03 |
Колодяжный В.А. |
6 |
04 |
Назаренко В.С. |
5 |
-
Код_предмета
Часы
11
128
12
128
15
128
13
64
14
64
16
128
1(О) М (Н)
Початкове відношення.
-
Ном_препод
ПІБ
Стаж
Код_предмета
Години
01
Акимова А.В.
3
11
128
01
Акимова А.В.
3
12
128
02
Проскура С.Л.
20
15
128
02
Проскура С.Л.
20
13
64
02
Проскура С.Л.
20
12
128
03
Колодяжный В.А.
6
14
64
04
Назаренко В.С.
5
14
64
-
-
-
16
128
Після застосування правила 6 отримаємо:
Результат:
Ном_препод |
Код_предмета |
01 |
11 |
01 |
12 |
02 |
15 |
02 |
13 |
02 |
12 |
03 |
14 |
04 |
14 |
Ном_препод |
ФИО |
Стаж |
01 |
Акимова А.В. |
3 |
02 |
Проскура С.Л. |
20 |
03 |
Колодяжный В.А. |
6 |
04 |
Назаренко В.С. |
5 |
-
Код_предмета
Часы
11
128
12
128
13
64
14
64
15
64
16
128
Приклад розв'язання задачі.
Побудова реляційної схеми БД "Сесія"
Мінімальний набір характеристик :
ФИО студента |
Семестр |
Дисциплина |
Форма отчетности |
Оценка |
Количество часов |
ФИО преподавателя |
Иванов В.П. |
1 |
БД |
зачет |
3 |
60 |
Преподаватель 1 |
Иванов В.П. |
2 |
БД |
экзамен |
4 |
28 |
Преподаватель 1 |
Иванов В.П. |
2 |
ООП |
экзамен |
5 |
32 |
Преподаватель 2 |
Иванов В.П. |
1 |
ООП |
зачет |
4 |
36 |
Преподаватель 2 |
Петрова В.П. |
1 |
БД |
зачет |
3 |
60 |
Преподаватель 1 |
Петрова В.П. |
2 |
БД |
экзамен |
4 |
28 |
Преподаватель 1 |
Петрова В.П. |
2 |
ООП |
экзамен |
5 |
32 |
Преподаватель 2 |
Петрова В.П. |
1 |
ООП |
зачет |
4 |
36 |
Преподаватель 2 |
Опис предметної області :
Нехай необхідно забезпечити збір і обробку даних за результатами здачі іспитів і заліків студентами факультету.
Організація даних повинна підтримувати:
виконання поточного учбового плану;
формування відомостей по окремих дисциплінах для груп студентів;
формування листів залікових книжок студентів;
формування звідної відомості курсу;
розрахунок середнього балу по дисциплінах і тому подібне
Побудова ER - діаграми
Етапи проектування :