8. 2. Логічне проектування
У реляційних базах даних логічне проектування приводить до розробки схеми бази даних, тобто сукупності схем відношень, які адекватно моделюють абстрактні об’єкти предметної області і зв’язки між цими об’єктами. Основою аналізу коректності схеми є функціональні залежності між атрибутами відношень бази даних. Атрибут – це властивості, які описують характеристики елемента (запису) відношення. Коректною буде така схема бази даних, в якій відсутні небажані залежності між атрибутами відношень.
Іншими словами, логічне проектування дозволяє перейти від словесної форми опису предметної області до структурованої табличної форми її опису. Тобто представити всі наявні у предметній області відношення у вигляді таблиць та зв’язків між ними.
У результаті першого етапу проектування ми отримали модель бази даних, яку можна представити у вигляді таблиць (рис. 17):
Облікові дані студентів
Код студента |
ПІП студента |
Дата народження |
Дата вступу в інститут |
Паспортні дані |
Наявність пільг |
Іноземна мова |
Контактні дані студента |
Замовники навчання |
Інформація про групи
Код групи |
Назва групи |
Факультет |
Спеціальність |
Куратор |
Навчальний план |
Навчальні плани
Код навчального плану |
Ознака семестру |
Назва дисципліни |
Кількість годин |
Форма контролю |
Викладачі |
Успішність
ПІП студента |
Назва дисципліни |
Назва групи |
Спеціальність |
ПІП викладача |
Оцінка |
Дата отримання оцінки |
Рис. 17. Таблиці з вихідними даними
В основі процесу проектування лежить метод нормалізації, який полягає в декомпозиції відношень, які знаходяться в попередній нормальній формі, в два або більше відношень і які задовольняють вимоги наступної нормальної форми (див. розділ 4.1).
Нагадаємо, що таблиця знаходиться в першій нормальній формі (1НФ), якщо в будь-якому допустимому значенні цієї таблиці кожний її рядок містить тільки одне значення для кожного атрибуту (стовпця). Для приведення таблиць, зображених на рис. 17, до 1НФ небхідно, щоб кожна з них містила тільки логічно неподільні значення. Наприклад, таблиця Облікові дані студентів містить поля Контактні дані і Паспортні дані, які включають множину значень. Тому доцільно ці дані виділити в окремі таблиці, які матимуть наступний вигляд (рис. 18):
Паспортні дані
Код студента |
Серія паспорта |
Номер |
Ким виданий |
Коли виданий |
Ідентифікаційний код |
Контактні дані
Код студента |
Поштовий індекс |
Код області |
Адреса |
Телефон |
E_mail |
Рис. 18. Таблиці 1НФ
Тепер всі таблиці знаходяться в 1НФ, оскільки кожен стовпець таблиці неподільний і в рамках однієї таблиці немає стовпців з однаковим за змістом значенням.
Наступна задача проектування бази даних зводиться до скорочення надлишковості даних у базі даних, а значить, до економії об’єму пам’яті, зменшення витрат на багаторазові операції поновлення надлишкових копій і усунення можливості виникнення протиріч через зберігання в різних місцях відомостей про один і той самий об’єкт. В приведених таблицях структури бази даних, зображених на рис. 17, спостерігається надлишковість. Наприклад, оскільки атрибут ПІП студента присутній у таблицях Облікові дані студентів та Успішність, то при його зміні необхідно робити виправлення у двох таблицях. Хороша структура бази даних містить по одному елементу інформації і лише в одному місці, що й дозволить уникнути надлишковості.
Таблиця знаходиться у другій нормальній формі у тому випадку, коли вважається, що вона вже є в 1НФ, і кожний неключовий атрибут повністю залежить від первинного ключа. Крім цього будь-яке відношення, яке знаходиться в 1НФ і не знаходиться в 2НФ, завжди можна перетворити і привести до еквівалентного набору відношень, які знаходяться в 2НФ. Цей процес полягає в заміні відношення, яке знаходиться в 1НФ, набором проекцій, еквівалентних початковому. Відповідно об’єднання цих проекцій дасть початкове відношення.
Таблиці структури бази даних, зображених на рис. 17, при приведенні до 2НФ заміняються набором відношень. Відношення Облікові дані студентів можна представити набором відношень Облікові дані студентів_1, Мови, Замовники, Пільги з такими первинними ключами Код мови, Код замовника, Код пільги. Відповідно, якщо над відношеннями Облікові дані студентів_1, Мови, Замовники, Пільги провести операцію з’єднання за атрибутами Код мови, Код замовника, Код пільги, то отримаємо відношення Облікові дані студентів.
Аналогічні міркування можна застосувати й до таблиці Навчалні плани, адже у різних навчальних планах зустрiчатимуться однакові дисципліни, що призведе до надлишковості при збереженні назв дисциплін у цій таблиці. Щоб уникнути цього, виділимо назви дисциплін в окрему таблицю Дисципліни, яка має стовпці Код дисципліни і Назва дисципліни. Поле Назва дисципліни в таблиці Навчальні плани замінимо на Код дисципліни. Тим самим ми сформуємо вторинний ключ, який буде зв’язувати таблицю Дисципліни з таблицею Навчальні плани. Те ж саме зробимо з полем Викладач.
Таблиця Навчальні плани через поля Код навального плану та Код дисципліни зв’язує інформацію про групу з дисципліною, яка читається в цій групі, а через поле Код викладача – з викладачем, який читає цю дисципліну. Оскільки одна дисципліна може читатися в двох семестрах, то для однозначної ідентифікації окремого запису таблиці вводиться поле Семестр, яке разом з полями Код навального плану і Код дисципліни утворює складений ключ.
Таким чином в базі даних формуємо всі таблиці-довідники:
таблиці-довідники, зв’язані з таблицею Облікові дані студентів:
Мови,
Замовники,
Пільги;
таблиці-довідники, зв’язані з таблицею Контактні дані:
Області;
таблиці-довідники, зв’язані з таблицею Інформація про групи:
Спеціальності,
Факультети,
Викладачі;
таблиці-довідники, зв’язані з таблицею Навчальні плани:
Форми контролю,
Дисципліни,
Викладачі.
З метою усунення дублювання необхідно внести зміни в структуру таблиці Успішність, а саме: поле ПІП студента замінити на Код студента і зв’зати з таблицею Облікові дані студентів. Замість полів Назва дисципліни, Назва групи, Спеціальність доцільно ввести поле Код навчального плану і встановити зв’язок з таблицею Навчальні плани. Сукупність полів Код студента і Код навчального плану утворює складений ключ, який однозначно ідентифікує інформацію про успішність кожного студента.
Таблиця Успішність через поля Код студента і Код навчального плану зв’язує інформацію про студента з інформацією про конкретну дисципліну і фіксує оцінку, отриману студентом. Оцінка і дата отримання оцінки однозначно залежать від вмістимого полів Код студента і Код навчального плану, які утворюють складений первинний ключ. Таким чином, всі таблиці мають первинні ключі, які однозначно визначають записи і не надлишкові. Тепер можна говорити про те, що таблиці знаходяться в 2НФ.
В результаті проектування отримуємо реляційну модель бази даних, яка складається з таблиць (рис. 19):
Облікові дані студентів – містить по одному рядку для кожного студента
Код студента |
ПІП студента |
Дата народження |
Дата вступу в інститут |
Паспортні дані |
Наявність пільг |
Іноземна мова |
Контактні дані студента |
Замовники навчання |
Паспортні дані – містить в одному рядку паспортні дані одного студента
Код студента |
Серія паспорта |
Номер |
Ким виданий |
Коли виданий |
Ідентифікаційний код |
Контактні дані – містить в одному рядку контактні дані одного студента
Код студента |
Поштовий індекс |
Код області |
Адреса
|
Телефон |
E_mail |
Мови – довідник іноземних мов, які вивчаються у навчальному закладі
Код мови |
Назва мови |
Замовники – довідник замовників навчання
Код замовника |
Назва замовника |
Пільги – довідник пільг, які можуть надаватися студентові під час навчання
Код пільги |
Назва пільги |
Області – довідник областей, на які адміністративно розділена Україна
Код області |
Назва області |
Інформація про групи – містить в одному рядку дані про одну академічну групу
Код групи |
Назва групи |
Факультет |
Спеціальність |
Куратор |
Навчальний план |
Спеціальності – довідник спеціальностей
Код спеціальності |
Назва спеціальності |
Викладачі – довідник викладачів
Код викладача |
П І П викладача |
Факультети – довідник
Код факультета |
Назва факультета |
Навчальні плани – містить по одному рядку для окремої дисципліни окремого семестру
Код навч. плану |
Код дисципліни |
Ознака семестру |
Кількість годин |
Форма контролю |
Код викладача |
Форми контролю – довідник форм контролю знань
Код форми контролю |
Назва форми контролю |
Дисципліни – довідник дисциплін
Код дисципліни |
Назва дисципліни |
Успішність – містить по одному рядку для кожного результату здачі окремим студентом окремої дисципліни
Код студента |
Код навчального плану |
Код викладача |
Оцінка |
Дата отримання оцінки |
Рис. 19. Таблиці реляційної моделі бази даних
На рис. 20 в графічній формі зображенo перелічені таблиці, їх стовпці, первинні і вторинні ключі, зв’язки між таблицями.
Наша структура
Рис. 20. Структура бази даних „Навчальний процес”
Таблиця знаходиться в 3НФ, якщо вона задовольняє і жоден з її неключових атрибутів не зв’язаний функціональною залежністю з будь-яким іншим неключовим атрибутом.
Всі таблиці бази даних „Навчальний процес” знаходяться в 3НФ:
кожен стовпець таблиці неподільний і в рамках однієї таблиці немає стовпців з однаковими за змістом значеннями (1НФ);
первинні ключі однозначно визначають запис і є ненадлишковими, всі поля кожної із таблиць залежать від її первинного ключа (2НФ);
значення довільного поля, яке не входить в первинний ключ, не залежить від значення іншого поля, яке теж не входить в первинний ключ (3НФ).
Наступний етап проектування – визначення типів даних, які зберігаються в стовпцях таблиць. Паралельно із заданням типу необхідно сформулювати обмеження цілісності – перелік допустимих значень типу. Отже, необхідно задати спосіб представлення і межі можливих змін для кожного із стовпців таблиць. При цьому необхідно відповісти на питання: дані яких типів повинні зберігатися в стовпцях і яка максимальна довжина.
Важливим моментом є виділення стовпців, які обов’язково повинні бути заповненими при створенні окремого рядка таблиці. Задаючи таке обмеження цілісності не дозволяємо, наприклад, ввести в таблицю Інформація про групи рядок, в якому не вказана назва групи. Поява в таблиці Інформація про групи рядка без назви групи приведе до помилки при формуванні екзаменаційних (залікових) відомостей.
На рис. 21 наведені таблиці бази даних „Навчальний процес” з типами даних стовпців і обмеженнями цілісності на них.
Основні таблиці:
Список полів таблиці „Облікові дані студента” (STUDENT)
№ п/п |
Назва поля |
Тип поля |
Властивість |
Розшифровка |
1 |
Kod_st |
Integer |
Primary key |
Код студента |
2 |
Prizv |
Varchar (20) |
Not null |
Прізвище |
3 |
Ima |
Varchar (20) |
Not null |
Ім’я |
4 |
Pob |
Varchar (20) |
Not null |
По батькові |
5 |
Data_nar |
Datetime |
Not null |
Дата народження |
6 |
Data_vst |
Datetime |
Not null |
Дата вступу |
7 |
Kod_mova |
Integer |
Foreign key |
Код іноз. мови |
8 |
Kod_zamov |
Integer |
Foreign key |
Код замовника навчання |
9 |
Kod_pilgy |
Integer |
Foreign key |
Код пільги |
10 |
Kod_grupa |
Integer |
Foreign key |
Код групи |
Список полів таблиці „Контактні дані студента” (KONTAKT)
№ п/п |
Назва поля |
Тип поля |
Властивість |
Розшифровка |
1 |
Kod_st |
Integer |
Foreign key |
Код студента |
2 |
Post_ind |
Varchar (20) |
Not null |
Поштовий індекс |
3 |
Kod_oblast |
Integer |
Foreign key |
Код області |
4 |
Rajon |
Varchar (20) |
|
Район |
5 |
Аdresa |
Varchar (20) |
|
Адреса |
6 |
Telef |
Varchar (15) |
|
Контактний телефон |
Список полів таблиці „Паспортні дані студента” (PASPORT)
№ п/п |
Назва поля |
Тип поля |
Властивість |
Розшифровка |
1 |
Kod_st |
Integer |
Foreign key |
Код студента |
2 |
Seria |
Varchar (7) |
|
Серія паспорта |
3 |
Nomer |
Varchar (6) |
|
Номер паспорта |
4 |
Kum_vud |
Varchar (30) |
|
Ким виданий |
5 |
Kolu_vud |
Datetime |
|
Коли виданий |
6 |
Idetnt_kod |
Varchar (10) |
|
Ідентифікаційний код |
Список полів таблиці „Академічні групи студентів” (GRUPA)
№ п/п |
Назва поля |
Тип поля |
Властивість |
Розшифровка |
1 |
Kod_grupa |
Integer |
Primary key |
Код студента |
2 |
Nazva_ grupa |
Varchar (7) |
Not null |
Назва групи |
3 |
Kod_fakult |
Integer |
Foreign key |
Код факультету |
4 |
Kod_spezial |
Integer |
Foreign key |
Кол спеціальності |
5 |
Kod_vyklad |
Integer |
Foreign key |
Код викладача |
6 |
Kod_np |
Integer |
Foreign key |
Код навчального плану |
Список полів таблиці „Навчальні плани” (NPLAN)
№ п/п |
Назва поля |
Тип поля |
Властивість |
Розшифровка |
1 |
Kod_np |
Integer |
Primary key |
Код навч. плану |
2 |
Kod_discipl |
Integer |
Foreign key |
Код дисципліни |
3 |
Semestr |
Integer |
Not null |
Семестр |
4 |
Godyny |
Integer |
Not null |
Кількість годин у семестрі |
5 |
Kod_fk |
Integer |
Foreign key |
Код підсумкової форми контролю |
6 |
Kod_vyklad |
Integer |
Foreign key |
Код викладача |
Список полів таблиці „Дані про успішність студентів” (USP)
№ п/п |
Назва поля |
Тип поля |
Властивість |
Розшифровка |
1 |
Kod_st |
Integer |
Foreign key |
Код студента |
2 |
Kod_np |
Integer |
Foreign key |
Код навч. плану |
4 |
Ocinka |
Varchar (10) |
Not null |
Оцінка |
5 |
Data_otr |
Datetime |
Not null |
Дата складання іспиту |
6 |
Rating |
Integer |
Not null |
Кількість балів |
Таблиці-довідники:
Список полів таблиці-довідника „Області” (OBLAST)
№ п/п |
Назва поля |
Тип поля |
Властивість |
Розшифровка |
1 |
Kod_oblast |
Integer |
Primary key |
Код області |
2 |
Nazva_oblast |
Varchar (10) |
Not null |
Назва області |
Список полів таблиці-довідника „Мови” (MOVA)
№ п/п |
Назва поля |
Тип поля |
Властивість |
Розшифровка |
1 |
Kod_mova |
Integer |
Primary key |
Код мови |
2 |
Nazva_mova |
Varchar (10) |
Not null |
Назва мови |
Список полів таблиці-довідника „Пільги” (PILGY)
№ п/п |
Назва поля |
Тип поля |
Властивість |
Розшифровка |
1 |
Kod_pilgy |
Integer |
Primary key |
Код пільги |
2 |
Nazva_ pilgy |
Varchar (10) |
Not null |
Назва пільги |
Список полів таблиці-довідника „Замовники” (ZAMOV)
№ п/п |
Назва поля |
Тип поля |
Властивість |
Розшифровка |
1 |
Kod_zamov |
Integer |
Primary key |
Код замовника |
2 |
Nazva_ zamov |
Varchar (10) |
Not null |
Назва замовника |
Список полів таблиці-довідника „Форми контролю” (FK)
№ п/п |
Назва поля |
Тип поля |
Властивість |
Розшифровка |
1 |
Kod_fk |
Integer |
Primary key |
Код форми контролю |
2 |
Nazva_ fk |
Varchar (10) |
Not null |
Назва форми контролю |
Список полів таблиці-довідника „Дисципліни” (DISCIPL)
№ п/п |
Назва поля |
Тип поля |
Властивість |
Розшифровка |
1 |
Kod_discipl |
Integer |
Primary key |
Код дисципліни |
2 |
Nazva_ discipl |
Varchar (60) |
Not null |
Назва дисципліни |
Список полів таблиці-довідника „Факультети” (FAKULT)
№ п/п |
Назва поля |
Тип поля |
Властивість |
Розшифровка |
1 |
Kod_fakult |
Integer |
Primary key |
Код факультету |
2 |
Nazva_ fakult |
Varchar (40) |
Not null |
Назва факультету |
Список полів таблиці-довідника „Спеціальності” (SPEZIAL)
№ п/п |
Назва поля |
Тип поля |
Властивість |
Розшифровка |
1 |
Kod_spezial |
Integer |
Primary key |
Код спеціальності |
2 |
Nazva_ spezial |
Varchar (30) |
Not null |
Назва спеціальності |
Список полів таблиці-довідника „Викладачі” (VYKLAD)
№ п/п |
Назва поля |
Тип поля |
Властивість |
Розшифровка |
1 |
Kod_vyklad |
Integer |
Primary key |
Код викладача |
2 |
Prizv |
Varchar (20) |
Not null |
Прізвище викладача |
3 |
Ima |
Varchar (20) |
Not null |
Імя викладача |
4 |
Pob |
Varchar (20) |
Not null |
По-батькові викладача |
Рис. 21. Таблиці бази даних „Навчальний процес”