Спонтанна • дива • и • торта

Кой е спонтанен, кой е див и преди всичко: къде е тортата?

В първата част на поредицата открихме, че използването на бази данни е нещо добро, тъй като разработчикът вече не трябва да се занимава с основните функции на съхранението на данни. Вече видяхме обаче, че разумно моделиране на данни е от съществено значение. Или с други думи:

Преди да започне смислен дизайн на база данни, от първостепенно значение е първо да сте наясно за частта от реалния свят, която трябва да бъде картографирана. За тази цел се препоръчва моделът на връзката между личността, въведен от Питър Чен през 1976 г. Той описва образувания, т.е. неща в реалния свят, техните свойства и взаимовръзки между тях. В тази статия ще разгледаме основите на този модел. В учебника на Kemper и Eickler моделът на връзката между субектите е разгледан в Глава 2.

Тъй като действителното съхранение на данни в общата СУБД се основава на релационния модел, т.е. в таблична форма, ние също трябва да се справим с това как да преобразуваме модел на връзка на обект в релационен модел. В много случаи това е съвсем просто и води до добри схеми за бази данни, които нямат проблеми като съкращения, подчертани в първата статия от поредицата.

Моделът E/R

За да моделираме частта от реалния свят, която ни интересува, трябва да сме наясно със следните точки:

Какви неща има в реалния свят?
Какви са техните свойства?
Как се отнасят помежду си?

Споменатите неща от реалния свят се наричат обекти, а свойствата се наричат атрибути в Entity-Relationship-Model (или просто E/R-Model).

Набор от подобни обекти също се нарича тип на обекта. По същия начин се говори и за типове отношения, когато се има предвид общата връзка между два типа обекти.

За да стане ясно това, нека се върнем, например, от първата част: списъка с адреси.

По отношение на трите току-що споменати въпроса можем да направим следните наблюдения: Нещата в реалния свят, които принадлежат към нашия списък с адреси, са преди всичко хора. Хората живеят в апартаменти, а апартаментите са на места. Характеристиките на хората, които са от значение за нашия списък с адреси, са фамилиите и имената. Улицата и номерът на къщата са собственост на апартамента, а мястото има име на място и пощенски код.

В нотация на Chen типовете обекти са маркирани с правоъгълници, а типовете връзки са маркирани с диаманти. Атрибутите обикновено се представят с елипси в диаграмата на връзката на обекта. Ако има атрибути, които са подходящи за еднозначно идентифициране на отделен обект, това може да се посочи чрез подчертаване. Такива атрибути се наричат ключови атрибути. В нашия пример пощенският код на място е ключов атрибут, тъй като мястото е ясно определено, ако пощенският код е известен.

E/R диаграма за списък с адреси

Връзките между типовете обекти могат да бъдат характеризирани още по-точно. Както обсъдихме в нашия пример в последната част, един човек може да има и няколко апартамента. Няколко души могат да живеят в един и същи апартамент. Това е така наречената връзка N: M: обектът може да бъде свързан с произволен брой обекти от другата страна и обратно.

От друга страна, връзката между дома и местоположението е връзка 1: N: В дадено място може да има няколко апартамента, но апартаментът е само на едно място.

Внедряване в таблици

Релационните системи за управление на бази данни (RDBMS) съхраняват данни в таблици. Така че ние трябва да внедрим модела E/R за практическа употреба. Вижда се, че това, което искаме да съхраним, са свойствата на обектите. По отношение на таблиците това се свежда до атрибутите, които се превръщат в колони на таблица. За всеки тип обект се създава отделна таблица.

Типове обекти

Засега имаме таблица за хора, в която се въвеждат фамилии и имена, таблица за апартаменти с колони за улици и номера на къщи и таблица за градове с колони за пощенския код и името на мястото. Отделните лица, апартаменти и места, т.е.отделните обекти, съответстват на редове в таблиците.

1: N връзки

Сега връзките все още трябва да бъдат картографирани. В случая на апартаменти и места картирането на връзката е лесно. Тъй като един апартамент може да бъде разположен само на най-много едно място и пощенският код ясно определя местоположението, достатъчно е да включите допълнителна колона за пощенския код в таблицата за апартаментите. Тогава стойността в тази колона служи като препратка към местоположението. Името на мястото, от друга страна, не трябва да се копира в таблицата на апартаментите, в противен случай бихме създали излишък: Фактът, че определен пощенски код принадлежи на определено място, вече е представен в таблицата Място и не трябва да се повтаря другаде.

Илюстрацията на връзката 1: N между апартаменти и места е толкова сложна, тъй като вече има ключ с пощенския код на местата и този ключ може да се използва директно като справка в таблицата Апартаменти, тъй като апартамент може да бъде само на едно място мога. Колоната с пощенски код в таблицата City е известна също като първичен ключ (PK). Първичният ключ уникално идентифицира ред в таблицата. В таблицата Апартамент пощенският код се превръща в чужд ключ (FK). Пощенският код не е собственост на самия апартамент, а на мястото, в което се намира апартаментът. Ето защо пощенският код не се записва като атрибут на апартамента в модела E/R! Колона с външен ключ се използва само за установяване на връзка между две обекти.

Важно е установяването на връзката чрез проста колона с външен ключ да работи само ако опаковаме външния ключ от страната N в случай на връзки 1: N. Тъй като има няколко апартамента на едно място, една колона в таблицата за местоположенията не би била достатъчна, за да покаже връзката!

Изкуствени ключове

В случая на връзката между хората и апартаментите, изпълнението на връзката в таблици е малко по-сложно. На първо място, не записахме никакви свойства в модела E/R, които уникално да идентифицират хора или апартаменти. В първата част вече разгледахме възможността просто да присвоим числа за тази цел. След това можем да различим двама души с едно и също име, които все още са различни хора, ако им присвоим различни номера. Такъв номер е известен още като изкуствен ключ или заместващ ключ. Това не е реална собственост на обекта, но е добавено от нас и се използва само за ясна идентификация.

С пощенския код на мястото, разбира се, може да се твърди, че той всъщност е и изкуствен ключ, тъй като пощенският код е направен от хора и е назначен произволно. Генерирането на пощенския код обаче е извън нашия контрол, тъй като е посочено от друг орган (а именно пощенската служба). Поради тази причина пощенският код може да се разглежда като естествен ключ. Човек би могъл да говори за изкуствен ключ само ако след това сами бяхме добавили тази колона с ключ и той вероятно не беше включен в оригиналната E/R диаграма. Между другото, не е необходимо да залагаме конкретна стойност за изкуствения ключ, когато вмъкваме данни. СУБД може да генерира стойността за самия изкуствен ключ. Това има предимството, например, че ако има няколко едновременни процеса на вмъкване, двама потребители не могат да изберат една и съща стойност, което би довело до грешки.

N: M връзки

Обратно в нашия пример, първо добавяме колона за изкуствен първичен ключ в таблицата за лицата, а в таблицата за апартаментите ги наричаме „PNr“ за номера на лицето и „WNr“ за номера на апартамента . Вече можем да използваме тези ключове, за да покажем връзката между човек и домовете му. Въпреки това, както при връзката един към много, не можем просто да зададем колона като външен ключ от двете страни. Човек може да има няколко апартамента според нашия концептуален модел, така че не можем да се справим с една колона тук. Според нашия модел обаче няколко души могат да живеят в един апартамент, така че колоната с външен ключ също няма да помогне в таблицата на апартамента.

Решението е да се създаде отделна таблица за връзката N: M тип „животи“. В тази така наречена таблица на връзките се създава ред за всяка връзка между човек и апартамент. Колоните на таблицата на връзките са колони с външен ключ за всички типове обекти, участващи в типа връзка. В нашия пример се нуждаем от таблица „живее“ с колоните „PNr“ и „WNr“. Например, ако човекът с номер 2 живее в апартамента с номер 1, ние въвеждаме тази комбинация в ред в таблицата на отношенията.

Композитен първичен ключ

Нямаме нужда от изкуствен първичен ключ за самата таблица на връзките. Тъй като обаче комбинацията от номер на човек и номер на апартамент в таблицата на връзката е уникална, тя може да се използва като композитен първичен ключ. (В края на краищата един човек може да живее в различни апартаменти и различни хора в един апартамент, но фактът, че човек живее в един и същ апартамент няколко пъти, не би бил разумен факт и следователно не бива да се допуска в таблицата.)

Таблици за адресна база данни

Атрибути на типа връзка

Между другото, типовете връзки също могат да имат свойства. Като разширение на нашия пример за списък с адреси, нека разгледаме накратко E/R диаграма, която моделира продукти и поръчки. Един човек може да изпрати няколко формуляра за поръчка, но поръчка се прави само от един човек. Във всяка поръчка могат да бъдат включени множество продукти. Продукт, разбира се, може да бъде поръчан и няколко пъти.

Ако даден продукт трябва да бъде включен няколко пъти в поръчка - напр. клиент поръчва пет розови четки за зъби наведнъж - така че количеството на поръчката е характеристика на връзката! И накрая, може да има други клиенти, които искат да поръчат само една четка за зъби, а същата поръчка, която съдържа пет четки за зъби, може да съдържа и една жълта гумена патица. Следователно количеството на поръчката не може да бъде нито свойство на продукта само по себе си, нито на формуляра за поръчка, но, както вече беше казано, свойство на връзката между продукта и поръчката.

E/R модел чрез поръчки

Както при типовете обекти, атрибутът е реализиран като колона в таблица. Тъй като типът на връзката „съдържа“ е тип на връзката N: M, така или иначе е необходима таблица на връзките за преобразуване, към която просто се добавя колоната за количеството на поръчката. Комбинацията от двата външни ключа остава основният ключ на таблицата на връзките. Една поръчка не може да съдържа един и същ продукт два пъти с различни количества. Концептуално това също няма да има смисъл.

Маси за поръчки и продукти; обърнете внимание на колоната "сума"

метод

Така че нека обобщим стъпките, които ни отвели от модела E/R до таблици:

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

Както ще анализираме по-подробно по-късно, този подход стъпка по стъпка ни доведе до дизайн на база данни от таблици, който няма съкращения.

Първичен и външен ключ

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

Стойността на PK трябва да е уникална и да идентифицира ред в таблицата
Ако PK се състои от няколко колони, комбинацията от стойностите в колоните трябва да е уникална
FK създава връзка с друга таблица
FK колона в една таблица се отнася до колона в друга таблица (това обикновено е PK колона)
За колоната FK са разрешени само стойности, които се съдържат в колоната, за която се отнася

Нива на проектиране

Току-що сме работили върху две различни нива на дизайн в нашия пример. На концептуално ниво установихме модела E/R. Като чисто концептуален модел той е напълно независим от базите данни! Можем също така да използваме модела 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 се задълбочава и се обсъждат допълнителни типове взаимоотношения и тяхното прилагане в релационния модел.