Baze de date, partea 2 Modelul entitate-relație-model spontan • sălbatic • și • tort

Cine este spontan, cine este sălbatic și mai presus de toate: unde este tortul?

În prima parte a seriei, am constatat că utilizarea bazelor de date este un lucru bun, deoarece dezvoltatorul nu mai trebuie să se ocupe de funcțiile de bază ale stocării datelor. Cu toate acestea, am văzut deja că modelarea sensibilă a datelor este esențială. Sau cu alte cuvinte:

Înainte de a putea începe o proiectare semnificativă a bazelor de date, este în primul rând crucial să fie clar despre secțiunea lumii reale care urmează să fie cartografiată. În acest scop este recomandat modelul entitate-relație introdus de Peter Chen în 1976. Acesta descrie entități, adică lucrurile din lumea reală, proprietățile și relațiile dintre ele. În acest articol, vom aborda elementele de bază ale acestui model. În manualul lui Kemper și Eickler, modelul relației entității este discutat în capitolul 2.

Deoarece stocarea reală a datelor în SGBD comun se bazează pe modelul relațional, adică sub formă de tabel, trebuie să ne ocupăm și de modul de conversie a unui model de relație entitate în modelul relațional. În multe cazuri, acest lucru este destul de simplu și duce la scheme bune de baze de date care nu au probleme, cum ar fi concedierile evidențiate în primul articol din serie.

Modelul E/R

Pentru a modela secțiunea lumii reale care ne interesează, trebuie să fim clari cu privire la următoarele puncte:

Ce lucruri există în lumea reală?
Care sunt proprietățile lor?
Cum sunt legate între ele?

Lucrurile menționate în lumea reală se numesc entități în modelul relației entității (sau pur și simplu modelul E/R pe scurt) și proprietățile sunt numite atribute.

Un set de entități similare este denumit și tip de entitate. În mod similar, se vorbește și despre tipurile de relații dacă se intenționează relația generală dintre două tipuri de entități.

Pentru a clarifica acest lucru, să revenim, de exemplu, din prima parte: lista de adrese.

În ceea ce privește cele trei întrebări menționate tocmai, putem face următoarele observații: Lucrurile din lumea reală care aparțin listei noastre de adrese sunt în primul rând oameni. Oamenii locuiesc în apartamente, iar apartamentele sunt pe alocuri. Caracteristicile persoanelor relevante pentru lista noastră de adrese sunt numele și prenumele. Strada și numărul casei sunt proprietăți ale apartamentului, iar un loc are un nume de loc și un cod poștal.

În notația Chen, tipurile de entități sunt marcate cu dreptunghiuri, iar tipurile de relații sunt marcate cu diamante. Atributele sunt de obicei reprezentate cu elipse în diagrama relației entității. Dacă există atribute care sunt adecvate pentru identificarea unică a unei entități individuale, acest lucru poate fi indicat prin subliniere. Astfel de atribute se numesc atribute cheie. În exemplul nostru, codul poștal al unui loc este un atribut cheie, deoarece locul este clar determinat dacă codul poștal este cunoscut.

Diagrama E/R pentru o listă de adrese

Relațiile dintre tipurile de entități pot fi caracterizate și mai precis. După cum am discutat în exemplul nostru în ultima parte, o persoană poate avea, de asemenea, mai multe apartamente. Mai multe persoane pot locui în același apartament. Este o așa-numită relație N: M: o entitate poate fi legată de orice număr de entități de cealaltă parte și invers.

Relația dintre apartament și locație, pe de altă parte, este o relație 1: N: Pot exista mai multe apartamente într-o locație, dar un apartament este situat vreodată într-o singură locație.

Implementarea în tabele

Sistemele de gestionare a bazelor de date relaționale (RDBMS) stochează date în tabele. Deci, trebuie să implementăm modelul E/R în consecință pentru o utilizare practică. Se poate vedea că ceea ce vrem să stocăm sunt proprietățile entităților. În ceea ce privește tabelele, acesta se reduce la atributele care devin coloane ale unui tabel. Se creează un tabel separat pentru fiecare tip de entitate.

Tipuri de entități

Până în prezent avem un tabel pentru persoane în care sunt introduse numele și prenumele, un tabel pentru apartamente cu coloane pentru străzi și numere de case și un tabel pentru orașe cu coloane pentru codul poștal și numele locului. Persoanele individuale, apartamentele și locurile, adică entitățile individuale, corespund rândurilor din tabele.

1: N relații

Acum relațiile trebuie încă cartografiate. În cazul apartamentelor și locurilor, cartografierea relației este simplă. Deoarece un apartament poate fi amplasat doar în maximum o locație și codul poștal definește clar locația, este suficient să includeți o coloană suplimentară pentru codul poștal în tabelul pentru apartamente. Valoarea din această coloană servește apoi ca referință la locație. Numele locului, pe de altă parte, nu ar trebui copiat în tabelul apartamentelor, altfel am crea redundanță: Faptul că un anumit cod poștal aparține unui anumit loc este deja reprezentat în tabelul Loc și nu trebuie repetat în altă parte.

Ilustrația relației 1: N dintre apartamente și locuri este atât de simplă, deoarece o cheie este deja disponibilă cu codul poștal al locurilor și această cheie poate fi utilizată direct ca referință în tabelul Apartamente, deoarece un apartament poate fi doar într-un singur loc poate sa. Coloana Cod poștal din tabelul Oraș este, de asemenea, cunoscută sub numele de cheie primară (PK). Cheia principală identifică în mod unic un rând din tabel. În tabelul Apartament, codul poștal devine cheia străină (FK). Codul poștal nu este o proprietate a apartamentului în sine, ci a locului în care este situat apartamentul. Acesta este motivul pentru care codul poștal nu este înregistrat ca un atribut al apartamentului în modelul E/R! O coloană cu cheie străină este utilizată numai pentru a stabili o relație între două entități.

Este important ca stabilirea relației printr-o simplă coloană cu cheie externă să funcționeze numai dacă împachetăm cheia externă pe partea N în cazul relațiilor 1: N. Deoarece există mai multe apartamente într-o singură locație, o singură coloană în tabel pentru locații nu ar fi suficientă pentru a arăta relația!

Chei artificiale

În cazul relației dintre oameni și apartamente, implementarea relației în tabele este puțin mai complicată. În primul rând, nu am înregistrat nicio proprietate în modelul E/R care să identifice în mod unic persoanele sau apartamentele. În prima parte ne-am gândit deja la posibilitatea de a atribui pur și simplu numere în acest scop. Putem distinge apoi două persoane cu același nume care sunt încă persoane diferite dacă le atribuim numere diferite. Un astfel de număr este, de asemenea, cunoscut sub numele de cheie artificială sau cheie surogat. Nu este o proprietate reală a entității, ci este adăugată de noi și este utilizată doar pentru o identificare clară.

Cu codul poștal al locului, s-ar putea argumenta, desigur, că este, de asemenea, o cheie artificială, deoarece codul poștal a fost făcut și de oameni și atribuit în mod arbitrar. Cu toate acestea, generarea codului poștal este dincolo de controlul nostru, așa cum a fost specificat de o altă agenție (și anume oficiul poștal). Din acest motiv, codul poștal ar putea fi bine văzut ca o cheie naturală. S-ar vorbi despre o cheie artificială numai dacă am fi adăugat această coloană de cheie ulterior și probabil că nu ar fi inclusă în diagrama E/R originală. De altfel, nu este necesar să punem o valoare concretă pentru cheia artificială atunci când introducem date. SGBD poate genera valoarea pentru o cheie artificială. Acest lucru are avantajul, de exemplu, că dacă există mai multe operații de inserare simultane, atunci doi utilizatori nu pot selecta aceeași valoare, ceea ce ar duce la erori.

N: M relații

Înapoi la exemplul nostru, mai întâi adăugăm o coloană pentru o cheie primară artificială în tabelul pentru persoane și în tabelul pentru apartamente, le numim „PNr” pentru numărul persoanei și „WNr” pentru numărul apartamentului . Acum putem folosi aceste taste pentru a arăta relația dintre o persoană și casele acesteia. Cu toate acestea, la fel ca în cazul relației unu-la-mulți, nu putem seta pur și simplu o coloană ca cheie străină pe ambele părți. Conform modelului nostru conceptual, o persoană poate avea mai multe apartamente, deci nu putem trece cu o singură coloană aici. Conform modelului nostru, totuși, mai mulți oameni pot locui într-un singur apartament, astfel încât o coloană cu cheie străină nu ar ajuta nici în tabelul Apartament.

Soluția este de a crea un tabel separat pentru tipul de relație N: M „trăiește”. În acest așa-numit tabel de relații, se creează un rând pentru fiecare relație dintre o persoană și un apartament. Coloanele tabelului de relații sunt coloane cu cheie străină pentru toate tipurile de entități implicate în tipul de relație. În exemplul nostru avem nevoie de un tabel „trăiește” cu coloanele „PNr” și „WNr”. De exemplu, dacă persoana cu numărul 2 locuiește în apartamentul cu numărul 1, introducem această combinație într-o linie în tabelul relației.

Cheie primară compusă

Nu avem nevoie de o cheie primară artificială pentru tabelul de relații în sine. Cu toate acestea, deoarece combinația numărului de persoană și a numărului apartamentului în tabelul relației este unică, poate fi utilizată ca cheie primară compusă. (La urma urmei, o persoană poate locui în apartamente diferite și persoane diferite într-un singur apartament, dar faptul că o persoană locuiește în același apartament de mai multe ori nu ar fi un fapt sensibil și, prin urmare, nu ar trebui permis în tabel.)

Tabelele pentru baza de date de adrese

Atribute tip relație

Apropo, tipurile de relații pot avea și proprietăți. Ca o extensie a exemplului listei noastre de adrese, să luăm în considerare pe scurt o diagramă E/R care modelează produse și comenzi. O singură persoană poate trimite mai multe formulare de comandă, dar o comandă este plasată doar de o singură persoană. În fiecare comandă pot fi incluse mai multe produse. Desigur, un produs poate fi comandat de mai multe ori.

Dacă un anumit produs urmează să fie inclus de mai multe ori într-o comandă - de ex. un client comandă cinci periuțe de dinți roz deodată - deci cantitatea comenzii este o caracteristică a relației! În cele din urmă, ar putea exista și alți clienți care vor să comande doar o periuță de dinți, iar aceeași comandă care conține cinci periuțe de dinți ar putea conține și o singură rață de cauciuc galbenă. În consecință, cantitatea comenzii nu poate fi nici o proprietate a produsului în sine, nici a formularului de comandă, ci, așa cum sa spus deja, o proprietate a relației dintre produs și comandă.

Model E/R prin comenzi

Ca și în cazul tipurilor de entități, atributul este implementat ca o coloană într-un tabel. Deoarece tipul de relație „conține” este un tip de relație N: M, oricum este necesar un tabel de relații pentru conversie, la care se adaugă pur și simplu coloana pentru cantitatea de comandă. Combinația celor două chei străine rămâne cheia primară a tabelului de relații. O comandă nu poate conține același produs de două ori cu cantități diferite. Conceptual, acest lucru nu ar avea nici un sens.

Mese pentru comenzi și produse; notează coloana „sumă”

metodă

Deci, să rezumăm pașii care ne-au dus de la modelul E/R la tabele:

Pentru fiecare Tip de entitate: Creați un tabel
Pentru fiecare atribut: Creați o coloană
Pentru orice masă fără natural cheie: Adăugați cheie artificială
Pentru fiecare 1: tip de relație N: Adăugați o coloană cu cheie străină pe partea N
Pentru fiecare N: relația M: Adăugați un tabel de relații cu coloane cu chei străine pentru tipurile de entități implicate în relație și transformați-le într-o cheie primară compusă; notați orice atribute existente ale tipului de relație

Așa cum vom analiza mai detaliat mai târziu, această abordare pas cu pas ne-a condus la o proiectare a bazei de date din tabele care nu are redundanțe.

Cheile primare și străine

Putem indica următoarele puncte pentru cheile primare și externe (PK și FK):

O valoare PK trebuie să fie unică și să identifice un rând din tabel
Dacă PK este compus din mai multe coloane, combinația valorilor din coloane trebuie să fie unică
Un FK creează o relație cu o altă masă
O coloană FK dintr-un tabel se referă la o coloană din alt tabel (aceasta este de obicei o coloană PK)
Numai valorile conținute în coloana la care se referă sunt permise pentru coloana FK

Niveluri de proiectare

Tocmai am lucrat la două niveluri diferite de design în exemplul nostru. La nivel conceptual, am stabilit modelul E/R. Ca model pur conceptual, este complet independent de bazele de date! Cu modelul E/R, am putea, de asemenea, să modelăm lucruri pe care nu dorim să le implementăm ulterior într-o bază de date. Totuși, este util pentru scopul nostru, deoarece ne deschide calea pentru a avea o schemă bună de baze de date.

Implementarea în schema bazei de date în modelul relațional are loc la nivelul implementării.

Există încă nivelul fizic al proiectării bazei de date, dar din moment ce accesul la date fizice, așa cum sa discutat în prima parte, este extras din SGBD, nu trebuie să ne ocupăm mai întâi de acesta.

Modelul relațional

Deoarece există un model de relație entitate și un model relațional și acești termeni sunt similari, există riscul confuziei! Deoarece modelul E/R și modelul relațional sunt două lucruri complet diferite. După cum tocmai am văzut, chiar se joacă la diferite niveluri de design! Cuvântul englez „relație” nu este tradus în germană ca „relație”, ci ca „relație”. Modelul relațional își ia numele din relațiile din matematică. După cum s-a descris deja în prima parte, modelul relațional pentru bazele de date revine la un eseu al lui Edgar F. Codd din 1972.

Relații în matematică

În matematică, o relație este definită ca subsetul produsului cartezian al altor mulțimi D1, ... Dn, care sunt numite și domenii sau intervale de valori. Produsul cartezian sau produsul încrucișat D1 ×… × Dn reprezintă ansamblul tuturor combinațiilor posibile perechi ale elementelor de la D1 la Dn. O relație R este astfel: R ⊆ D1 ×… × Dn

Să luăm în considerare o relație R ⊆ D1 × D2 în care D1 este mulțimea tuturor numerelor întregi din cinci cifre și D2 este mulțimea tuturor șirurilor posibile. D1 × D2 sunt apoi toate combinațiile posibile de numere din cinci cifre și orice șiruri de caractere. O listă de coduri poștale cu nume de locuri asociate, cum ar fi tabelul nostru de locuri, este un subset al acestuia!

Deci, în scopurile noastre, putem înțelege o relație ca un tabel (inclusiv conținutul său). (Vom vedea mai târziu că aceasta este o simplificare și că există detalii în care tabele din SGBD și relații diferă.)

rezumat

În cel de-al doilea articol din seria despre baze de date, am analizat proiectarea bazelor de date. Am ajuns să cunoaștem modelul relației entității ca un model conceptual cu care lucrurile din lumea reală pot fi mapate cu proprietățile lor și modul în care acestea sunt legate între ele. De asemenea, am văzut cum un model de relație entitate poate fi transformat într-un model relațional format din tabele. Această transformare este necesară deoarece DBMS stochează date în tabele și nu poate face nimic cu modelele E/R direct.

perspectivă

În partea următoare vom pune în practică într-un SGBD real modelul bazei de date relaționale pe care l-am proiectat astăzi pentru lista noastră de adrese. De asemenea, descrie modul în care tabelele pot fi create într-un SGBD cu SQL. În partea următoare, dar o singură parte, modelul E/R este aprofundat și sunt discutate tipuri de relații suplimentare și implementarea lor în modelul relațional.