SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Relacyjne bazy danych – pojęcia podstawowe

Zanim rozpoczniesz przygodę z językiem SQL, warto abyś poznał ogólną koncepcję środowiska bazodanowego dla którego język ten został opracowany.

Model relacyjny to tylko jeden z kilku istniejących i komercyjnie obecnych na rynku. Można spotkać bazy obiektowe czy hierarchiczne, jednak spośród wszystkich koncepcji, ten zdobył największą popularność. Jest on intuicyjny i relatywnie prosty w implementacji.

Opracowany w latach 70-tych ubiegłego wieku, jest już dojrzałym 40-latkiem ! Warto poznać ten model, ponieważ z pewnością spotkasz jego wdrożenia praktycznie w każdej firmie. Opisywane tutaj podstawy, dotyczą wszystkich implementacji relacyjnych baz danych. W szczególności takich systemów jak MS SQL Server, Oracle, MySQL, PostgreSQL, DB2 czy FireBird.

Pełne zrozumienie całej teorii projektowania baz danych, nie jest niezbędne do opanowania techniki pisania zapytań SQL. Przydaje się jednak w unikaniu błędów logicznych czy bardziej świadomym pisaniu kwerend.

W artykule tym przedstawię tylko fundamentalne, podstawowe pojęcia, związane z relacyjnymi bazami danych. Niektóre z nich stosować będę wymiennie w dalszej części kursu (np. kolumna / atrybut).

Postaram się ograniczyć zakres teorii do minimum. Z drugiej strony, nie jestem pewien czy tak skondensowana forma wiedzy będzie zrozumiała (jeśli nie – koniecznie dajcie mi znać). Tak czy siak, uważam że jest ona niezbędna do zbudowania odpowiedniego obrazu przestrzeni w której będziemy się poruszać w trakcie tego kursu.

Jeśli chodzi o zagadnienia bezpośrednio związane z pisaniem zapytań – największą uwagę skup proszę na matematycznej teorii zbiorów, którą przywołuję tutaj kilkukrotnie. Jest ona podstawą modelu relacyjnego.


Teoria relacyjna, Model ER oraz relacyjny

Fundamentem relacyjnych baz danych jest teoria relacyjna, przedstawiona przez Franka Edgara Codda w 1970 roku.

Projekt każdej relacyjnej bazy danych, rozpoczyna etap konceptualny (abstrakcyjny), opierając się o model E-R (Entity-Relationship Model), którego autorem jest dr. Peter Chen.

Jest to opis czysto teoretyczny, wymagający przełożenia na język praktyki. Ma on na celu, opisanie fragmentu rzeczywistości za pomocą związków encji. W tym modelu używamy definicji, które mają swoje odzwierciedlenie na późniejszym etapie – wdrożenia projektu w życie.

W terminologii dotyczącej relacyjnych baz danych, pojawia się często wiele pojęć z różnych płaszczyzn, modeli i będę starał się w tym artykule je przybliżyć i usystematyzować. Taki jest w zasadzie główny cel tej publikacji.

Krótki rzut oka na nomenklaturę podstawowych obiektów bazodanowe w różnych terminologiach. Od czystej teorii – do wdrożenia.

Teoria relacyjna Model ER Relacyjne bazy Aplikacje
Relacja Encja Tabela Klasa
Krotka Instancja Wiersz Instancja klasy (obiekt)
Atrybut Atrybut Kolumna Właściwość, atrybut
Dziedzina Dziedzina/Typ Typ danych Typ danych


W dalszej części, będę przedstawiał pojęcia w uporządkowanym kierunku – zawsze od postaci konceptualnej (model E-R) do praktycznej (model relacyjny), spotykanej w konkretnej implementacji środowiska relacyjnego np. MS SQL Server.

BAZA DANYCH

Zgodnie z modelem E-R, to zbiór schematów RELACJI i ZWIĄZKÓW między nimi. Czyli struktur służących do przechowywania danych w ściśle zorganizowany sposób.

W praktyce będzie to zawsze zbiór tabel, w których przechowywane są dane. Ponadto tabele, posiadać będą określone powiązania (relacje) między sobą.

RELACJA

W modelu E-R, to po prostu TABELA czyli struktura, przechowująca informacje o obiektach określonego typu. Używając języka programistów – KLASA obiektów określonego typu.

KLASA ENCJI, RELACJA (Model ER) = TABELA (RDBMS) = KLASA (języki obiektowe)

Tabela (inaczej encja, klasa obiektów, relacja) – to podstawowa struktura modelowania niezależnych, odrębnych obiektów, o których informacje chcemy przechowywać w bazie.
Każda tabela powinna przechowywać informacje jedynie o ściśle określonych obiektach konkretnego typu. Na przykład informacje dotyczące pracowników (tabela Pracownicy), albo tylko samochodów, zamówień, produktów itp. – każda typ = nowa tabela . Informacje w ramach tabeli, powinny być więc jednorodne ze względu na typ obiektu którego dotyczą.

Samo określenie RELACJA w nomenklaturze języka polskiego, może być trochę tutaj mylące. W końcu RELACJA i ZWIĄZEK to synonimy a jak widać dotyczą dwóch zupełnie różnych światów RELACJA=TABELA a ZWIĄZEK to tak naprawdę RELACJA :).

ATRYBUT

Każda RELACJA jest opisana za pomocą zbioru ATRYBUTÓW. Warto dodać że ten zbiór powinien być zawsze minimalny, niezbędny do realizacji celów biznesowych.

Każdy z ATRYBUTÓW należy do określonej DZIEDZINY, czyli może przyjmować określone wartości (np. liczbowe, tekstowe, daty etc.) oraz posiada unikalną w ramach RELACJI nazwę.

ATRYBUT w praktyce, to nic innego jako KOLUMNA. Zatem tłumacząc na model relacyjny, każda TABELA opisana jest za pomocą zbioru KOLUMN. Każda KOLUMNA jest ściśle określona TYPEM DANYCH, czyli przechowuje wartości jednorodne, z określonej DZIEDZINY (tego samego typu np. liczby, znaki, daty etc). Nazwa kolumny w ramach tabeli musi być unikalna, bo silnik musi jednoznacznie wiedzieć do którego atrybutu będziemy się odnosić.

ATRYBUTY danej RELACJI, tworzą zbiór nieuporządkowany. Ich kolejność w teorii nie ma żadnego znaczenia. Sami decydujemy które z nich są w danym momencie dla nas istotne i w jakiej kolejności chcemy je zobaczyć (SELECT). Teraz małe ćwiczenie – wytęż wzrok i znajdź różnice pomiędzy zbiorem 1 a 2 :

Teoria_relacyjnych_baz_danych_01

Zgodnie z teorią zbiorów, oba SETy są takie same ! Nie ma znaczenia ani kolejność atrybutów opisujących elementy, ani kolejność elementów (wierszy) w zbiorze.

Poza tymi własnościami, warto dodać, że wartość danego atrybutu powinna być zawsze atomowa, czyli niepodzielna. Jest to jedna z właściwości dobrze znormalizowanych baz. Temat ten opisuję w dedykowanym artykule na temat projektowania i normalizacji baz danych.

SCHEMAT RELACJI

To po prostu jej definicja. Czyli informacja o strukturze, ATRYBUTACH które opisują daną RELACJĘ.

W praktyce będzie to struktura tabeli – czyli informacja przez jakie kolumny, jakiego typu jest ona opisana. Mówimy więc o specjalnym typie informacji. Schematy relacji to tzw. metadane czyli informacje o strukturach w bazie danych. Więcej na temat metadanych w SQL Server znajdziesz np. w artykule na temat widoków systemowych.

KROTKA

To pojedynczy egzemplarz, czyli obiekt opisany wszystkimi ATRYBUTAMI danej RELACJI. KROTKA to nic innego jak WIERSZ czy REKORD. W języku programistów możemy mówić o EGZEMPLARZU danej KLASY.

Każda tabela to zbiór wierszy. Zgodnie z matematyczną teorią zbiorów, każdy z definicji jest nieuporządkowany. Stąd każda tabela to zbiór elementów (wierszy) w którym zakładamy, że kolejność nie jest ustalona. W praktyce może być wymuszona np. przez klucz podstawowy czy mechanizmy składowania danych, ale w rozważaniach i działaniach musimy zawsze patrzeć przez pryzmat teorii. Jeśli chcesz, żeby wiersze były uporządkowane – trzeba to w zapytaniu określić (np. sortowanie przez ORDER BY) lub świadomie stosować mniej lub bardziej jawne mechanizmy, które ją zapewnią (np. indeks klastrowy).

Teoria zbiorów, mówi o jeszcze jednej bardzo ważnej zasadzie. Braku duplikatów w zbiorze.

Wartość informacyjna każdego duplikatu w zbiorze jest równa zero. Co więcej wprowadza dużo zamieszania. Jak obsłużyć sytuację, kiedy chcielibyśmy zmodyfikować tylko jeden z nich? Jak mielibyśmy wskazać ten o który nam chodzi skoro są identyczne?

Oczywiście możemy przechowywać wiele takich samych obiektów, ale każdy z nich powinien być jednoznacznie identyfikowalny. Na przykład mamy 100 identycznych samochodów, tego samego rocznika z tym samym wyposażeniem, kolorem etc. ale identyfikowane jednoznacznie za pomocą numeru VIN. SQL Server dopuszcza możliwość tworzenia zduplikowanych rekordów, ale z punktu widzenia teorii jest to niezgodne z zasadami i rodzi same problemy.

KLUCZE

Klucze to zbiory atrybutów mających określoną właściwość. Dzięki nim, możemy jednoznacznie identyfikować każdy pojedynczy wiersz. Znajomość pojęć kluczy podstawowych i obcych jest niezbędna do tworzenia zapytań, odwołujących się do wielu tabel. Możesz się spotkać z następującymi pojęciami typów kluczy :

SUPERKLUCZ (NADKLUCZ)

Superkluczem nazywamy dowolny podzbiór atrybutów, identyfikujący jednoznacznie każdy wiersz. Każda RELACJA (tabela) może zawierać wiele takich kluczy. Szczególnym przypadkiem jest superklucz składający się ze wszystkich atrybutów (kolumn) danej tabeli.

KLUCZ KANDYDUJĄCY

To dowolny z SUPERKLUCZY, mogący zostać kluczem podstawowym. W implementacji bazy danych, w praktyce nie istnieje jako niezależny osobny (zmaterializowany byt) jako taki. Jest to tylko założenie teoretyczne.

KLUCZ PODSTAWOWY (PRIMARY KEY)

To wybrany (zazwyczaj najkrótszy), jednoznacznie identyfikujący każdy, pojedynczy wiersz, zbiór atrybutów (kolumn) danej relacji (tabeli). Jest to pierwszy z wymienionych do tej pory kluczy, któy ma faktyczne, fizyczne odwzorowania w implementacji bazy danych. Każda tabela może mieć tylko jeden taki klucz.

W praktyce, będzie to najczęściej jedna lub dwie kolumny w tabeli, jednoznacznie (UNIKALNIE) identyfikujący każdy wiersz. Nie można stworzyć klucza podstawowego, na zbiorze atrybutów nieunikalnych. Dwa wiersze nie mogą mieć takiej samej wartości klucza podstawowego.

W odniesieniu do klucza podstawowego, możesz spotkać określenie KLUCZ NATURALNY i SZTUCZNY. Kluczem naturalnym, będzie kolumna (lub zbiór kolumn) opisująca daną klasę obiektów – np. NIP. Jest to atrybut, który z punktu widzenia systemu postrzegany jest tak samo naturalnie jak Nazwa firmy czy jej REGON. W rzeczywistości jednak jest to nadany identyfikator sztuczny, ale jest on na tyle powszechny, że możemy traktować go jako klucz naturalny. Innym przykładem klucza naturalnego może być adres email użytkownika systemu. Przeważnie zakładamy, że dwóch użytkowników nie może mieć takiego samego adresu.

Klucz sztuczny to zazwyczaj dodatkowa kolumna stworzona przez projektanta bazy danych w celu identyfikacji rekordów, możliwie krótkim kluczem. Zazwyczaj będzie to wartość liczbowa typu całkowitego (INT, SMALLINT, BIGINT). Jest to związane z wydajnością, lub innymi aspektami które zasługują na osobny artykuł.
Najważniejsze jest to, żeby klucz podstawowy unikalnie identyfikował rekordy i był możliwie krótki.

KLUCZ OBCY

To atrybut lub zbiór atrybutów, wskazujący na KLUCZ GŁÓWNY w innej RELACJI (tabeli). Klucz obcy to nic innego jak związek, relacja między dwoma tabelami.

Cecha dobrego klucza głównego (możliwie krótki) tutaj staje się klarowna. W tabeli powiązanej kluczem obcym, trzeba powielić tą strukturę (zbiór atrybutów) aby móc jednoznacznie wiązać rekordy z dwóch tabel.

Definicja klucza obcego, pilnuje aby w tabeli powiązanej, w określonych atrybutach, znaleźć się mogły tylko takie wartości które istnieją w tabeli docelowej jako klucz główny. Klucz obcy może dotyczyć również tej samej tabeli.


Powiązania pomiędzy tabelami (związki pomiędzy relacjami)

Omawiane do tej pory zagadnienia, są związane bezpośrednio z samą strukturą przechowywania danych w oderwaniu narazie od innych tabel (RELACJI). W praktyce spotkać możemy trzy fundamentralne związki między tabelami. Dzięki nim, możemy zapewnić integralność referencyjną danych i zamodelować odpowiednią logikę naszej struktury. Abstrahując od szczegółowej analizy wszystkich rodzajów związków jakie są możliwe w modelu E-R (opcjonalne, obowiązkowe, tetralne), skupimy się tylko na binarnych – czyli dwuargumentowych.

Wiedzę o ich istnieniu i sposobie modelowania wykorzystamy chociażby w pisaniu zapytań do wielu tabel.

ZWIĄZEK 1:1 (jeden do jeden)

Każdy wiersz z tabeli A może mieć tylko jednego odpowiednika w tabeli B (i na odwrót)
Ten rodzaj relacji może być postrzegany jako podzielenie tabeli na dwie (bo relacja jest jeden do jeden). Stosowany np. wtedy, gdy zbiór dodatkowych atrybutów jest określony tylko dla wąskiego podzbioru wierszy w tabeli podstawowej.

Przykładem mogą być RELACJE pochodne. W bazie AdventureWorks2008 znajdziemy następujący przykład :
Relacja_1_to_1_01
Tabela Person.Person jest relacją główną, przechowującą informacje o osobach. Tabela Employee jest relacją pochodną, w której dla części z osób, o których mamy wiedzę w tabeli Person.Person są określone dodatkowe atrybuty. Każdy pracownik jest przecież osobą w naszym modelu, ale tylko część z osób jest pracownikami. Jeden pracownik nie może być jednocześnie dwoma osobami, a jedna osoba dwoma pracownikami (w tym modelu rzeczywistości).
Innym zastosowaniem związku 1:1, jest wydzielenie pewnej grupy atrybutów które są rzadko odpytywane. Mogą być, więc umiejscowione w tabeli przechowywanej na osobnym wolniejszym, nośniku danych.

Kolejny scenariusz to dodatkowa ochrona części atrybutów określonego typu (np. informacji wrażliwych takich jak wynagrodzenie, preferencje etc.). Wydzielając je do osobnej tabeli, możemy zapewnić dodatkowy poziom zabezpieczeń (dostęp, szyfrowanie), inną politykę backupową etc..

ZWIĄZEK 1:N (jeden do wiele)

Jest to najczęściej spotykana relacja. Określamy w niej że każdy element ze zbioru A (wiersz tabeli A), może być powiązany z wieloma elementami zbioru B. Przykład z bazy Northwind – Produkty i Kategorie. W tym modelu wiele produktów może należeć do jednej kategorii.
Relacja_1_to_N_01
Można sobie wyobrazić inny scenariusz. Chcielibyśmy aby jeden produkt był w wielu kategoriach. Wtedy konieczne byłoby zastosowanie ostatniego z możliwych rodzajów związków – wiele do wiele.

ZWIĄZEK N:M (wiele do wiele)

Realizowana jest zawsze jako dwie relacje 1:N. Zatem jeśli chcemy między dwoma tabelami zamodelować związek N:M potrzebujemy trzecią tabelę – łącznikową. Przykładem niech będzie fragment każdego systemu zamówień, np. taki jak w bazie NorthWind. Mamy zamówienia i produkty. Każdy z produktów może być zamawiany wielokrotnie, w różnych zamówieniach. Każde z zamówień może zawierać wiele pozycji (produktów). Tabelą łączącą, realizującą relacje 1:N będzie tu tabela Order Details.
Relacja_wiele_do_wiele_01


Podsumowanie

Teoria relacyjnych baz danych jest dobrym tematem na osobny kurs. Jej znajomość jest szczególnie konieczna dla projektantów struktur, architektury bazy danych. Ponieważ artykuł ten jest częścią kursu z pisania zapytań SQL, ograniczyłem się tylko do najważniejszych, fundamentalnych zagadnień.