Бази даних, Частина 2 Модель Сутність-Зв’язок Спонтанна • дика • та • пиріг

Хто спонтанний, хто дикий і перш за все: де торт?

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

Перш ніж починати змістовне проектування бази даних, дуже важливо чітко визначити той розділ реального світу, який слід нанести на карту. Для цієї мети рекомендована модель взаємозв'язку сутності, запроваджена Пітером Ченом у 1976 р., Яка описує сутності, тобто речі в реальному світі, їх властивості та взаємозв'язки між ними. У цій статті ми заглибимося в основи цієї моделі. У підручнику Кемпера та Ейклера модель взаємовідносин сутності обговорюється в главі 2.

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

Модель E/R

Для того, щоб змоделювати той розділ реального світу, який нас цікавить, нам слід чітко зрозуміти такі моменти:

Які речі існують у реальному світі?
Які їх властивості?
Як вони пов’язані між собою?

Згадані речі реального світу називаються сутностями, а властивості - атрибутами у Моделі Сутності-Зв'язку (або просто E/R-Моделі).

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

Щоб це було зрозуміло, повернімося, наприклад, до першої частини: списку адрес.

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

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

Діаграма E/R для списку адрес

Зв'язок між типами сутностей можна охарактеризувати ще точніше. Як ми обговорювали в нашому прикладі в останній частині, одна людина також може мати кілька квартир. У одній квартирі можуть проживати кілька людей. Це так звані відносини N: M: сутність може бути пов’язана з будь-якою кількістю сутностей з іншого боку і навпаки.

З іншого боку, взаємозв’язок між домом та місцем проживання стосується співвідношення 1: N: У певному районі може бути кілька квартир, але квартира колись знаходиться лише в одному місці.

Реалізація в таблицях

Реляційні системи управління базами даних (СУБД) зберігають дані в таблицях. Тож ми повинні впровадити модель E/R відповідно для практичного використання. Видно, що те, що ми хочемо зберігати, є властивостями сутностей. Що стосується таблиць, це зводиться до того, що атрибути стають стовпцями таблиці. Для кожного типу сутності створюється окрема таблиця.

Типи об'єктів

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

1: N відносин

Тепер стосунки все ще мають бути нанесені на карту. Що стосується квартир та місць, картографування відносин є простим. Оскільки квартира може бути розташована максимум в одному місці, і поштовий індекс чітко визначає місце розташування, достатньо включити додатковий стовпець для поштового індексу в таблицю квартир. Потім значення в цьому стовпці служить посиланням на місце розташування. Ім'я місця, навпаки, не слід копіювати в таблицю квартир, інакше ми створимо надмірність: Той факт, що певний поштовий індекс належить певному місцю, вже представлений у таблиці Місце і не повинен повторюватися в іншому місці.

Ілюстрація взаємозв'язку 1: N між квартирами та місцями настільки нехитра, тому що ключ вже є із поштовим індексом місць, і цей ключ можна використовувати безпосередньо як посилання в таблиці "Квартири", оскільки квартира може бути лише в одному місці може. Стовпець поштового індексу в таблиці міста також називається первинним ключем (PK). Первинний ключ однозначно ідентифікує рядок у таблиці. У таблиці Квартира поштовий індекс стає зовнішнім ключем (FK). Поштовий індекс є не власністю самої квартири, а місця, в якому знаходиться квартира. Ось чому поштовий індекс не записується як атрибут квартири в моделі E/R! Стовпець зовнішнього ключа використовується лише для встановлення зв’язку між двома об’єктами.

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

Штучні ключі

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

З поштовим індексом місця можна, звичайно, стверджувати, що насправді це також штучний ключ, оскільки поштовий індекс був зроблений людьми і призначений довільно. Однак генерація поштового індексу знаходиться поза нашим контролем, як це було визначено іншим агентством (а саме поштовим відділенням). З цієї причини поштовий індекс цілком можна розглядати як природний ключ. Можна було б говорити лише про штучний ключ, якби ми додали цей стовпець ключа потім, і він, можливо, не був би включений в оригінальну діаграму E/R. До речі, необов’язково, коли ми вставляємо дані, ми визначаємо конкретне значення штучного ключа. СУБД може генерувати значення для самого штучного ключа. Це має ту перевагу, наприклад, що якщо існує кілька одночасних операцій вставки, то два користувачі не можуть вибрати одне і те ж значення, що призведе до помилок.

N: M відносини

Повертаючись до нашого прикладу, ми спочатку додаємо стовпець для штучного первинного ключа в таблицю для осіб, а в таблицю для квартир ми називаємо їх "PNr" для номера особи та "WNr" для номера квартири . Тепер ми можемо використовувати ці ключі, щоб показати стосунки між людиною та її будинками. Однак, як і у відношенні "один до багатьох", ми не можемо просто встановити стовпець як зовнішній ключ з будь-якої сторони. Людина може мати кілька квартир відповідно до нашої концептуальної моделі, тому ми не можемо обійтись однією колонкою тут. Однак, згідно з нашою моделлю, в одній квартирі може проживати кілька людей, тому стовпчик із зовнішнім ключем також не допоможе у таблиці квартир.

Рішення полягає у створенні окремої таблиці для типу відносин N: M "життя". У цій так званій таблиці стосунків створюється рядок для всіх відносин між людиною та квартирою. Стовпці таблиці відносин - це стовпці зовнішнього ключа для всіх типів сутності, що беруть участь у типі відносин. У нашому прикладі нам потрібна таблиця “живе” зі стовпцями “PNr” та “WNr”. Наприклад, якщо особа з числом 2 зараз живе в квартирі з номером 1, ми вводимо це поєднання в рядку в таблиці відносин.

Складений первинний ключ

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

Таблиці для адресної бази даних

Атрибути типу відносин

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

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

Модель E/R через замовлення

Як і у типах сутностей, атрибут реалізований як стовпець у таблиці. Оскільки тип взаємозв'язку "містить" є типом відносин N: M, для перетворення в будь-якому випадку потрібна таблиця відносин, до якої просто додається стовпець для кількості замовлення. Поєднання двох зовнішніх ключів залишається основним ключем таблиці відносин. Одне замовлення не може містити один і той же товар двічі з різною кількістю. Концептуально це також не мало б сенсу.

Столи для замовлень та продуктів; зверніть увагу на графу "сума"

метод

Тож давайте узагальнимо кроки, які пройшли нас від моделі E/R до таблиць:

Для кожного Тип сутності: Створити таблицю
Для кожного атрибут: Створити стовпець
Для будь-якого столу без натурального ключ: Додати штучний ключ
Для кожного 1: N тип відносин: Додайте стовпець зовнішнього ключа на стороні N
Для кожного N: M відносини: Додайте таблицю відносин із стовпцями зовнішнього ключа для типів сутності, що беруть участь у відносинах, і зробіть їх складеним первинним ключем; зверніть увагу на будь-які існуючі атрибути типу відносин

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

Первинний та зовнішній ключі

Ми можемо вказати наступні пункти для первинного та зовнішнього ключів (PK та FK):

Значення PK має бути унікальним і визначати рядок у таблиці
Якщо PK складається з декількох стовпців, комбінація значень у стовпцях повинна бути унікальною
FK створює зв'язок з іншою таблицею
Стовпець FK в одній таблиці відноситься до стовпця в іншій таблиці (це зазвичай стовпець PK)
Для стовпця FK дозволяються лише значення, що містяться в стовпці, до якого він відноситься

Дизайн рівнів

У нашому прикладі ми щойно працювали над двома різними рівнями дизайну. На концептуальному рівні ми встановили модель Е/Р. Як чисто концептуальна модель, вона абсолютно не залежить від баз даних! Ми також могли б використовувати модель E/R для моделювання речей, які ми не хочемо впроваджувати в базу даних згодом. Проте це корисно для нашої мети, оскільки відкриває нам шлях до хорошої схеми бази даних.

Реалізація в схемі бази даних в реляційній моделі відбувається на рівні реалізації.

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

Реляційна модель

Оскільки існує модель взаємозв'язку сутності та реляційна модель, і ці терміни схожі, існує ризик плутанини! Оскільки модель E/R та реляційна модель - це дві абсолютно різні речі. Як ми щойно побачили, вони навіть розігруються на різних рівнях дизайну! Англійське слово “relationship” не перекладається німецькою мовою як “Relation”, а як “Relationship”. Реляційна модель бере назву від співвідношень у математиці. Як вже було описано в першій частині, реляційна модель баз даних сягає есе Едгара Ф. Кодда з 1972 р.

Відносини в математиці

У математиці відношення визначається як підмножина декартового добутку з n інших множин D1, ... Dn, які також називаються доменами або діапазонами значень. Декартовий добуток або поперечний добуток D1 ×… × Dn позначає набір усіх можливих попарних комбінацій елементів від D1 до Dn. Таким чином, відношення R таке: R ⊆ D1 ×… × Dn

Розглянемо відношення R ⊆ D1 × D2, де D1 - це набір усіх п’ятизначних цілих чисел, а D2 - набір усіх можливих рядків. D1 × D2 - це всі можливі комбінації п’ятизначних чисел та будь-яких рядків символів. Список поштових індексів із пов’язаними назвами місць, як наша таблиця місць, є його підмножиною!

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

Резюме

У другій статті з серії баз даних ми розглянули дизайн баз даних. Ми пізнали модель взаємовідносин сутності як концептуальну модель, з якою речі в реальному світі можна зіставити зі своїми властивостями та як вони пов’язані між собою. Ми також побачили, як модель взаємозв'язку сутності може бути перетворена в реляційну модель, що складається з таблиць. Це перетворення необхідно, оскільки СУБД зберігає дані в таблицях і не може нічого робити з моделями E/R безпосередньо.

світогляд

У наступній частині ми застосуємо модель реляційної бази даних, яку ми сьогодні розробили для нашого списку адрес, на практиці в реальній СУБД. Також описано, як можна створювати таблиці в СУБД із SQL. У наступній, але одній частині, модель E/R поглиблюється і обговорюються подальші типи відносин та їх реалізація в реляційній моделі.