Проектування реляційної бази даних: метод нормальних форм; метод суть-зв'язок (ER-діаграм); засоби автоматизації проектування

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

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

Етапи проектування бази даних:

• дослідження предметної області;

• аналіз даних;

• визначення відносин між елементами даних. Визначення первинних і вторинних (зовнішніх) ключів відносин. Реальні наочні області є системами взаємозв'язаних об'єктів, наприклад облік товарів, які реалізовані різним фірмам з декількох складів. Проектуючи таку базу даних, користувач повинен передбачити, яка інформація йому може знадобитися в майбутньому.

Етапи розробки бази даних:

1. Інфологічна модель

2. Даталогічна модель

3. Фізична модель

4. База даних

Розглянемо більш докладно інфологічний етап проектування.

В предметній області (діловому середовищі) існують сутності, інформацію про які необхідно зберігати, і ці сутності пов'язані одна з одною певними зв’язками.

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

Значення поняття бази даних таке, що користувач — або людина, що взаємодіє з нею, або прикладна програма — не повинен піклуватися про те, яким чином дані фізично зберігаються у зовнішній пам’яті комп’ютера. Користувач висловлює запити маніпулювання даними на мові взаємовідношення даних. Програмні ж засоби, звані системами управління базами даних (СУБД), організовують зв'язок між запитом користувача і фізичною областю зберігання інформації.

Перед початком проектування бази даних необхідно визначити інформаційні об’єкти (сутності) і взаємозв’язки, що існують між ними.

Взаємовідношення між даними не залежать від моделі даних і СУБД, яка застосовується. А модель даних – навпаки — визначається системою управління базою даних.

Сутності і їх атрибути. Сутність (entity) — це щось, про що зберігається інформація. Клієнт — це сутність, як і товар, що зберігається на складі. Зовсім не обов'язково, щоб сутності були матеріальні. Так, деяка подія, наприклад концерт, є сутність; звернення до лікаря — теж сутність.

Сутність описують даними, званими атрибутами (attributes). Якщо сутність є економічним об’єктом, то атрибутами є його реквізити. Наприклад, клієнт як сутність звичайно описується номером, ім'ям, прізвищем, вулицею, містом, країною, поштовим індексом і номером телефону. сутність концертів можна описати назвою, датою, місцем проведення і ім'ям виконавця.

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

Ідентифікатори сутностей.Єдиною метою розміщення в базі даних інформації, що описує сутність, є подальше прочитування цієї інформації. Отже, необхідно мати певний метод, що дозволяє відрізняти одну сутність від іншої і тим самим забезпечувати прочитування потрібної сутності. Для цього кожній сутності привласнюються певні значення атрибутів, що відрізняють її від будь–якої іншої сутності бази даних; сукупність значень називається ідентифікатором сутності (entity identifier).

Припустимо, що у фірми є два клієнти з ім'ям Петро Сидоров. Якщо службовець шукає пункти замовлення Петра Сидорова, відомості про якого з Петрів Сидорових вибере СУБД? В даному випадку — про обох. Оскільки не існує способу розрізнити двох клієнтів, результати запиту будуть неточні.

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

Іншим рішенням може стати об'єднання імені і прізвища клієнта з його телефонним номером. Подібна комбінація (складений ідентифікатор) також однозначно визначає кожного клієнта. Проте тут є два недоліки. По–перше, такий ідентифікатор довгий і громіздкий, і при введенні будь–якого з його компонентів велика вірогідність помилки. По–друге, при зміні номера телефону клієнта ідентифікатор теж повинен змінитися. Зміна ідентифікатора сутності може привести до виникнення серйозних проблем в базі даних.

У деяких сутностей, наприклад у рахунків фактур, є природні ідентифікатори (номери рахунків–фактур). Іншим сутностям — головним чином людям, населеним пунктам і предметам — привласнюються унікальні номери без певного значення. Для третіх необхідні складені ідентифікатори.

При збереженні екземпляра сутності в базі даних потрібно, щоб СУБД забезпечувала наявність у нового екземпляра унікального ідентифікатора. Це є прикладом обмеження в базі даних — правила, якому повинні відповідати дані. Реалізація різних обмежень допомагає підтримувати узгодженість і точність інформації.

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

Хоча вірним є те, що модель взаємовідношення сутностей в бази даних не залежить від формальної моделі даних, що використовується для відображення структури даних в СУБД, часто рішення про моделювання даних ухвалюються виходячи з вимог тієї формальної моделі, яка застосовуватиметься згодом. Одне з подібних рішень — видалення багатозначних атрибутів. Аналогічний приклад буде приведений при розгляді взаємостосунків "багато-до багатьох" між сутностями.

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

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

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

Про сукупність сутностей.На початку роботи з сутностями може здатися, що природа сутності досить туманна. Розглянемо, наприклад, запас товарів. Чи є "запас товарів" сутністю? Ні. Цей запас складається з окремих товарів, з якими працює магазин. Справжньою сутністю є окремий товар. Товарний запас визначається переглядом усіх екземплярів сутності окремих товарів як єдиного цілого.

Щоб прояснити ситуацію, розглянемо атрибути, які необхідні у разі створення сутності запасу товарів: число товарів, назва товару, кількість на складі, роздрібна ціна і т.д. Оскільки весь запас товарів ми намагаємося описати за допомогою однієї сутності, для кожного з цих атрибутів потрібна по декілька значень. Проте, як було показано вище, атрибути не можуть бути багатозначними, тобто запас товарів сутністю бути не може. Його необхідно представляти у вигляді сукупності екземплярів сутності окремих товарів.

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

Документування сутностей і атрибутів.Діаграми взаємозв’язків сутностей дозволяють документувати сутності в базі даних, а також атрибути, що описують ці сутності. Є декілька типів ER–діаграм. Найчастіше використовуються діаграми Чена (Chen) (на ім'я винахідника ER–моделювання) і діаграми інформаційного проектування (Information Engineering). Дозволяється застосовувати діаграми будь–якого типу, головне, щоб той, хто використовує діаграму, розумів її символіку.

У обох моделях для представлення сутностей застосовуються прямокутники. Ім'я кожної сутності вказується у прямокутнику і ставиться в однині, як показано на рис.5.1.9.

Рис.5.1.9. Зображення сутності Клієнт.

У початковій ER–моделі (entity relationship) Чена не передбачена можливість відображення атрибутів безпосередньо на ER–діаграмі. Проте багато хто розширює цю модель, вносячи атрибути і укладаючи їх в овали, наприклад як на рис.5.1.10.

Рис.5.1.10. Зображення сутності Клієнт та її атрибутів.

Ідентифікатор (ключ) сутності — це атрибут, що позначається зірочкою (*Номер реєстрації.).

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

Рис.5.1.11. Зображення сутності та її атрибутів у моделі інформаційного проектування.

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

Домени.У кожного атрибуту є домен (domain) — вираз або перелік значень, дозволених для даного атрибуту. Домен може бути дуже малий. Так, в магазині, значеннями атрибуту Size (розмір) товарів можливо можуть бути лише S, M, L, XL і XXL; ці значення і складають весь домен. І навпаки, домен імені клієнта дуже великий, і його можна вказати тільки як "текст" або "імена людей".

У СУБД домен реалізується за допомогою обмеження. Всякий раз при записі деякого значення в базу даних СУБД перевіряє його відповідність домену, вказаному для його атрибуту. Хоча у багатьох випадках невеликі домени визначити неможливо, домен, принаймні, забезпечує отримання даних коректного типу. Наприклад, СУБД може заборонити користувачу запис значення 123 х 50 в той атрибут, доменом якого є значення грошових одиниць. Крім цього, в більшості СУБД за допомогою доменів забезпечується досить жорстка перевірка атрибутів дати і часу, що допомагає уникнути появи таких дат, як, наприклад, 30 лютого.

Документування доменів.Найчастіше домени не вказуються безпосередньо на ER–діаграмах, а записуються в спеціальний документ (зазвичай в словник даних). Проте у варіанті методу Чена з вказівкою атрибутів домени можуть бути задані. Вираз, що визначає домен, розміщується нижче за кожен атрибут.

Взаємозв’язки між сутностями відображаються за допомогою ER–діаграми.

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

Метод Чена.На використанні різновидностей ER–моделі ґрунтується більшість сучасних підходів до проектування реляційних баз даних. Модель була запропонована Ченом (Chen) у 1976 році. Моделювання предметної області базується на використанні графічних діаграм, які включають невелике число різновидних компонентів. У зв’язку з наочністю подання концептуальних (логічних інформаційних) схем баз даних ER–моделі набули значного поширення в системах CASE, які підтримують автоматизоване проектування реляційних баз даних.

У методі Чена для зображення взаємозв’язків використовуються ромби, а для зображення типу взаємовідношення між сутностями — лінії із стрілками. Наприклад, на рис.5.1.12. представлено взаємовідношення між фірмою–клієнтом і замовленням.

Рис.5.1.12. Зображення взаємозв’язків між сутностями у методі Чена.

Одиночна стрілка, направлена на сутність Фірма–клієнт, указує на те, що замовлення належить максимум одному клієнту, подвійна стрілка, направлена на сутність Замовлення, означає, що клієнт може зробити один або декілька замовлень.

У рамках методу Чена існують два альтернативні способи представлення взаємозв’язків. Перший (див. мал. 5.1.12.а) припускає використання стрілок з цифрами і буквами. Тут "1" указує на те, що замовлення поступає від одного клієнта, а "М" — на те, що клієнт може робити багато замовлень.

Рис. 5.1.12.а. Використання стрілок з цифрою і буквою для зображення взаємовідношень між сутностями на ER–діаграмі.

При використанні другого способу усувається недолік, пов'язаний з легкістю для читання взаємовідношення в обох напрямах, коли ім'я взаємовідношення знаходиться усередині ромба. Вираз «Клієнт робить замовлення» має цілком певний сенс, але «Замовлення робить клієнта» — нісенітниця. Для вирішення цієї задачі ім'я взаємовідношення видаляється з ромба і додається як до взаємовідношення, так і до його інверсії на діаграмі (див. рис.5.1.12.б). Цей варіант діаграми можна читати в будь–якому напрямі: «Клієнт робить багато замовлень» і «Замовлення робиться одним клієнтом».

Рис. 5.1.12.б. Винесення імені взаємовідношення за межі ромба на ER–діаграмі для зображення взаємовідношень між сутностями.

При зображенні ER–діаграм за допомогою методу Чена існує одне вельми важливе обмеження: відсутній очевидний спосіб вказівки слабких сутностей і обов'язкових взаємозв’язків. Так, замовлення не повинно бути присутнім в базі даних без клієнта. Отже, замовлення є слабкою сутністю, і його взаємовідношення з клієнтом обов'язкове. Саме тому багато розробників баз даних, що використовують метод Чена, стали застосовувати для слабкої сутності новий символ — прямокутник з подвійною межею (рис.5.1.13.).

Рис. 5.1.13. Зображення слабкої сутності.

Метод інформаційного проектування.У методі інформаційного проектування додаткова інформація на ER–діаграмі відображається графічними символами на кінцях ліній.

На кінцях ліній діаграми інформаційного проектування можуть використовуватися такі символи:

Кінці ліній указують, які з взаємозв’язків є обов'язковими. Як перший приклад розглянемо мал. 5.1.14.

Рис.5.1.14. Взаємовідношення типу "один–ко многим" на діаграмі, побудованій методом інформаційного проектування.

Подвійна лінія нижче за сутністю "Адреси" означає, що кожне замовлення пов'язано з однією і лише однією фірмою–клієнтом. Оскільки нуль не є одним з варіантів, це взаємовідношення обов'язкове. Навпаки, 0 і комбінація з трьох ліній, сполучена з сутністю "Замовлення", указують на те, що клієнт може зробити нуль, один або декілька замовлень.

Ці символи часто зображаються поверненими на 90° (див. мал. 5.1.14).

При використовуванні методу інформаційного проектування атрибути часто указуються безпосередньо на діаграмі. Ідентифікатори сутностей позначені зірочками.

На рис.5.1.15. наведено інформаційну діаграму.

Рис.5.1.16. Приклади ER – діаграм