Класифікація таблиць у реляційних базах даних за ознаками цілісності та надмірності даних

Зміст статті
Обґрунтування статті та деякі ключові поняття;
1. Довідники і зв'язки;
1.1. Види таблиць;
1.2. Види довідників;
1.3. Види зв'язок;
2. Узагальнення класифікації;
2.1. Класифікація в табличному вигляді;
2.2. Класифікація в схематичному вигляді;
3. Деякі коментарі щодо застосування класифікації;
3.1. Застосування класифікації при нормалізації таблиць;
Висновок.

Обґрунтування статті та деякі ключові поняття
Дуже часто присутній на навчанні дисципліни «Бази даних». Навчався колись сам… якось навіть довелося проводити цілий курс для друзів і знайомих. Під час навчання мною було помічено, що труднощі виникають вже на етапі розуміння таблиць і того, як ними користуватися. Багато хто просто не могли і не можуть розробити найпростіші бази даних. Після більш детального розгляду такого поняття як таблиці і маленькою класифікації, труднощі сприйняття таблиць у реляційних базах даних майже завжди зникають. Отже!

У даній статті буде розглянуто маленька класифікація таблиць за ознаками цілісності і надмірності. Що це означає? Це означає, що будуть наведені приклади з описом, яку структуру таблиць можна робити, щоб запобігати (намагатися запобігати) надмірність і домагатися цілісності в реляційних базах даних.

Для розуміння дамо короткі визначення цілісності та надмірності даних:

Цілісність даних — це властивість здатності за одними даними відновлювати інші, при цьому не втрачаючи семантичне і реляційне єдність цих даних.

Надмірність даних — це стан бази даних, при якому в таблицях присутні зайві дані.

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

1. Довідники та зв'язки

1.1. Види таблиць

Трохи заглибимося в маленьку класифікацію таблиць за видами їх структури. Розділимо таблиці на два загальні види. Першим видом будуть таблиці-довідники, другим таблиці-зв'язки.

Довідники і зв'язки
Малюнок 1. Довідники та зв'язки

Інформацію в таблицях можна розділити на два види. На інформацію, яка описує об'єкти (суб'єкти), зв'язку та інформацію, яка описує дії, процеси, події, інше.

В довідниках містяться відомості про об'єкти та суб'єктів, зв'язки. В зв'язках містяться відомості про дії, процеси, події і так далі.

В зв'язках зберігаються дані, взяті з таблиць довідників. Оскільки невигідно повторювати одні і ті ж дані при описі об'єктів (суб'єктів) і при описі їх взаємодії, дані про об'єкти (суб'єкти) заносяться в довідники, а в таблицях-зв'язках не зберігаються дані об'єктів (суб'єктів) в чистому вигляді, а лише посилання на них (зовнішній ключ). Таким чином, у зв'язках зберігаються дані по взаємодії об'єктів (суб'єктів) і посилання на самих об'єктів (суб'єктів) (зовнішній ключ). Ці «посилання» є первинними ключами в таблицях довідниках. Але про це потім…

1.2. Види довідників

Довідники можуть підрозділятися на кілька видів. Це статичні, статично-динамічні і динамічні довідники. Зрозуміло, навряд чи можна назвати абсолютно статичний довідник, так як в цьому світі може змінитися все. Або майже все.

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

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















Номер Назва 1 Січень 2 Лютий 3 Березень 4 Квітень 5 Травень 6 Червень 7 Липень 8 Серпень 9 Вересень 10 Жовтень 11 Листопад 12 Грудень
Таблиця 1. Приклад статичних довідників

Статично-динамічний довідник — довідник, в якому зберігаються дані про зв'язки. У такому довіднику можуть бути зовнішні ключі.

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









Код посади Оклад Дата оновлення 1001 12 000 05.02.2015 1002 17 000 01.02.2015 1003 11 500 01.02.2015 1004 25 450 01.02.2015 1005 10 000 01.02.2015 1006 6 000 04.02.2015
Таблиця 2. Приклад статично-динамічних довідників

Динамічні довідники — це таблиці, дані про об'єкти, суб'єкти, зв'язки в яких змінюються часто і використовуються в інших таблицях. Від статичних довідників відрізняються тільки частотою модифікації в них даних.

Прикладом таких таблиць можуть бути списки проектів. Насправді, дані про відкриття або закриття проектів можуть знаходитися в самому довіднику проектів, що в більшості випадків неправильно і порушує цілісність. З іншого боку, якщо зберігати історію змін щодо відкриття і закриття (припинення) проектів, то можна отримати надмірність даних. Цілісність і надмірність даних будуть боротися один з одним ще довго, також як і зима з літом.








Код проекту Проект Нормативний термін виконання Дата додавання Користувач PT102 Фарбування вікон 15 03.01.2014 1547 PT103 Установка дверей 10 04.01.2014 9874 PT587 Перевірка пожежних кранів 2 04.01.2014 1456 PT588 Заміна люків 3 02.01.2014 0147 PT133 Очищення каналів 11 09.02.2015 1547
Таблиця 3. Приклад динамічних довідників

Види довідників
Малюнок 2. Види довідників

1.3. Види зв'язок

Таблиці-зв'язки можна розділити на два виду.

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

Прикладом довідника-зв'язки буде таблиця платіжних транзакцій. Або таблиця з даними про футбольному матчі.






Код транзакції Платник Одержувач Сума Дата Коментар EEVS-doodi4 100045 57457 -10 000 25.07.2014 На чоботи UDFD-ioeed9 455780 10024 -900 24.06.2014 NULL PEDD-jdksl4 144770 56698 -6980 01.01.2015 NULL FDFE-keiiii0 447757 1 120 08.07.2014 NULL
Таблиця 4. Приклад довідника-зв'язки

І зв'язка (так, просто зв'язка). Це таблиця, в якій зберігаються лише зовнішні ключі та дані, які не можна віднести до довідкових, наприклад, дата, або значення логічних полів.

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







Код Код клієнта Показання лічильника Місяць 2334 35643 50 01.01.2015 2335 235673 49 01.01.2015 2335 436345 56  01.01.2015 2335 574733 24  01.01.2015
Таблиця 5. Приклад зв'язки

Необхідно пояснити, що це за поля, які утворюють довідник, але не можуть бути виділені в окрему таблицю-довідник. Прикладом таких полів є поле «коментар», «скарга», «опис», «пропозиція». Словом, якщо приводити популярний приклад, у полі «повідомлення» в таблиці бази даних будь-якої соціальної мережі…

Види зв'язок
Малюнок 3. Види зв'язок

2. Узагальнення класифікації

2.1. Класифікація в табличному вигляді








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

2.2. Класифікація в схематичному вигляді

Загальна схема
Малюнок 4. Схема класифікації таблиць у реляційних базах даних за ознаками цілісності та надмірності даних

3. Деякі коментарі щодо застосування класифікації

3.1. Застосування класифікації при нормалізації таблиць

Процес нормалізації, якщо не враховувати деякі етапи (Але враховувати результати цих етапів!) — це звичайне «дроблення» таблиць на більш дрібні таблиці із створенням реляційної зв'язку між ними безпосередньо або через проміжні таблиці зв'язок «Багато до багатьох»). Під реляційної зв'язком може не завжди розумітися реляційне відношення!

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

Для прикладу. Нехай є база даних, в якій єдина операція по модифікації даних — це додавання. В такому разі стає неефективним кожен раз при зміні якого-небудь окремого атрибута сутності, «копіювати» інші значення атрибутів вже в інший кортеж. У цьому випадку використовуються NULL або ж створення статично-динамічного довідника, де описується ряд атрибутів однієї семантики або один атрибут, а дублюється лише зовнішній ключ з первинним ключем послідовності. Цей же метод може використовуватися у традиційній схемі модифікації даних з оновленням і видаленням даних.

Висновок
Дана класифікація була створена мною на основі спостережень при проектуванні баз даних, а також виходячи з прочитаної теорії з проектування реляційних СУБД. Моїм друзям і знайомим, що вивчають дисципліну «бази даних» і займаються проектуванням баз даних, і мені ця класифікація досить серйозно спростила «життя» і дозволила у багатьох ситуаціях заздалегідь вибрати найбільш підходящий і, як виявлялося згодом, правильний вигляд таблиці для зберігання в ній тих чи інших даних.

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

Сподіваюся, кому-небудь ще допоможе ця класифікація при освоєнні дисципліни «Бази даних» і при проектуванні баз даних в реляційних СУБД.

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.