SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Środowisko MS SQL Server

MS SQL Server to System Zarządzania Bazą Danych (SZBD). Najnowsza wersja SQL Server 2017 jest już 14 generacją (zgodnie z oznaczeniami znaczących wersji MS) tego systemu.
Nie wszyscy wiedzą, że początki SQL Server sięgają 1987 roku. Zatem obecnie, mamy do czynienia z produktem którego historia liczy ponad 25 lat. Z punktu widzenia czasu życia produktu, szczególnie w świecie IT to przecież cała wieczność. Możemy więc śmiało stwierdzić, że SQL Server jest dojrzałym produktem.

W artykule tym, znajdziesz ogólne informacje na temat środowiska bazodanowego SQL Server. Kilka słów o historii, dostępnych narzędziach i komponentach.


Historia SQL Server

Zacznijmy jednak od narodzin. W 1987 roku, firma Microsoft wraz Sybase, a później również z Ashton Tate rozpoczęła współpracę biznesową nad produktem serwera bazy danych. Rezultatem była pierwsza wersja produktu – Ashton Tate/Microsoft SQL Server 1.0 wydana w 1989. Od tego czasu przez niemal dekadę, Sybase i Microsoft współpracowali, wydając kolejne wersje systemu. Jeśli miałeś do czynienia z produktami Sybase – znajdziesz wiele podobieństw do SQL Server, chociażby w dialekcie SQL. Być może dlatego, spotkać można teorie, że SQL Server to tak naprawdę produkt Sybase, kupiony w pewnym momencie przez Microsoft, co jednak nie jest prawdą. Więcej informacji na temat historii SQL Server, obalaniu mitów – znajdziesz na blogach Euan Garden oraz Kalen Delaney. Szczególnie polecam historię SQL Server w pigułce w formie foto-posta :)

Od 1989 wydano następujące wersje SQL Server :

Wersja Rok wydania Oficjalna nazwa Projekt
1.0 1989 SQL Server 1.0
1.1 1991 SQL Server 1.1
4.2 1992 SQL Server 4.2
4.21 1994 SQL Server 4.21 SQLNT
6.0 1995 SQL Server 6.0 SQL95
6.5 1996 SQL Server 6.5 Hydra
7.0 1998 SQL Server 7.0 Sphinx
8.0 2000 SQL Server 2000 Shiloh
9.0 2005 SQL Server 2005 Yukon
10.0 2008 SQL Server 2008 Katmai
10.5 2010 SQL Server 2008 R2 Kilimanjaro
11.0 2012 SQL Server 2012 Denali
12.0 2014 SQL Server 2014 Hekaton*
13.0 2016 SQL Server 2016
14.0 2017 SQL Server 2017


Edycje SQL Server

MS SQL Server oferowany jest w różnych edycjach. W zależności od potrzeb, konkretnych funkcjonalności, możemy wybrać z pośród kilku wydań. W praktyce, spotkać się można najczęściej z wersją darmową Express i komercyjnymi – Standard lub w dużych i średnich wdrożeniach – Enterprise (flagowy produkt, najwyższa edycja, ze wszystkimi możliwymi opcjami). Oprócz nich, są jeszcze pośrednie wersje, takie jak Web czy Businness Intelligence, ale są one stosunkowo rzadko spotykane.

Pełne porównanie poszczególnych edycji SQL Server, możliwości, dostępnych komponentów znajdziesz na oficjalnych stronach Microsoft.


Środowisko SQL Server, komponenty

Podstawą platformy SQL Server jest usługa serwera. Realizuje wszystkie zadania związane z obsługą i utrzymaniem baz danych. Jest ona fundamentem i każde wdrożenie, opiera się o trzon silnika bazy danych (Database Engine).

Środowisko SQL Server jest skalowalne i składać się może z wielu komponentów. W zależności od potrzeb i możliwości wybranej edycji, możemy w trakcie instalacji wybierać spośród dostępnych funkcjonalności. Instalacja kompletnego środowiska SQL Server na maszynie fizycznej lub wirtualnej to INSTANCJA. W jej ramach, możemy zarządzać wieloma bazami danych. W SQL Server 2012 jest to maksymalnie aż 32767 baz danych per instancja. Więcej informacji na temat możliwości instancji, ograniczeń znajdziesz tutaj.

Na danym fizycznym (lub wirtualnym) serwerze, możemy zainstalować wiele instancji SQL Server. Są one w pełni niezależne i tworzą odrębne środowiska SQL Server. W praktyce, rzadko spotyka się komercyjne wdrożenia więcej niż dwóch, trzech instancji na maszynie. Podyktowane jest to głównie przez aspekty wydajnościowe – zazwyczaj jest jedna.

Silnik bazy danych

To najważniejszy z komponentów, odpowiedzialny za przetwarzanie zapytań, zarządza składowaniem, ochronie danych. Obsługuje niezbędne mechanizmy bezpieczeństwa, autoryzacji czy autentykacji. Silnik bazy danych jest uruchomiony na serwerze jako usługa, inaczej mówiąc jest to po prostu aplikacja działająca w tle środowiska Windows, obsługująca wszelkie procesy związane z bazami danych.

Usługi analityczne

Jest to tak naprawdę w sumie kilka komponentów z rodziny analitycznej. Analityka (OLAP) , PowerPivot, Data Minning, ogólnie wszystko co związane z Business Intelligence czyli inaczej usługami drążenia danych. Są to w ogólności procesy, mechanizmy, mające na celu przekształcenie danych w informacje a informacje w wiedze. Dzięki nim, możemy szukać zależności między danymi i odkrywać związki.

Usługi analityczne dostarczają mechanizmów raportujących, wydobywających wiedzę, analizując dane pochodzącą z baz transakcyjnych (OLTP). Na jej podstawie możemy wyciągać wnioski, przewidywać pewne zachowania, które mogą wpływać np. wzrost konkurencyjności przedsiębiorstwa.

Usługi raportujące (Reporting Services)

Komponent stworzony z myślą o raportowaniu z baz danych SQL Server. Nie musimy tworzyć własnych aplikacji raportujących. SQL Server oferuje gotowe rozwiązanie w oparciu o środowisko .NET.

Usługi integracji danych (Integration Services)

SSIS – SQL Server Integration Services – jest to dedykowane środowisko dla procesów ETL (Extract Transform and Load). Integracji i przetwarzania danych z różnych źródeł. Dla przykładu firma analityczna z branży farmaceutycznej, otrzymuje co miesiąc dane o sprzedaży leków od różnych producentów w formie plików XML. Są one umieszczane na serwerze plików i przetwarzane przez automatyczne procesy SSIS zasilając bazę danych z plików dostarczanych przez dostawców.


Najważniejsze narzędzia pakietu MS SQL Server

W zależności od edycji, mamy możliwość wyboru w trakcie instalacji spośród dostępnych funkcjonalności i narzędzi. Poniżej ogólny opis najważniejszych z nich.

SQL Server Management Studio (SSMS)

Graficzny interfejs użytkownika (GUI), przeznaczony do administrowania, tworzenia baz, obiektów bazodanowychoraz do pisania i testowania skryptów, zapytań. Jest wygodnym narzędziem dla deweloperów – daje np. możliwość podglądu planów wykonania. Posiada szereg wygodnych funkcjonalności np. mechanizm IntelliSens znany z VS (podpowiadanie nazw obiektów, słów kluczowych). Za pomocą SSMS mamy dostęp do wszystkich obiektów bazodanowych. Możemy tworzyć kompletne rozwiązania, administrować serwerem. Tworzyć projekty, generować skrypty etc. Istnieje na rynku szereg alternatywnych rozwiązań. Spośród nich, godnymi uwagi są z pewnością produkty firmy RedGate oraz TOAD.

SQL Server Configuration Manager

Aplikacja do konfiguracji usług, protokołów komunikacyjnych, aliasów. Podstawowe narzędzie związane z konfiguracją i dostępem (protokoły) usług serwera.

SQL Server Profiler

Aplikacja do śledzenia przetwarzanych poleceń w silniku bazodanowym. Niezastąpione przy szukaniu wąskich gardeł, zbierania danych do optymalizacji systemu. Możemy dzięki niemu przechwycić praktycznie wszystkie procesy, wykonywane przez SQL Server.

Zapytania, procedury składowane, wywoływania triggerów, logowania, transakcje i inne procedowane zdarzenia. SQL Server Profiler umożliwia zapis śladu i jego późniejszą analizę np. w Database Engine Tunning Advisor celem optymalizacji struktur (dodanie indeksów, statystyk etc.)

Database Engine Tunning Advisor (DETA)

Aplikacja do analizy i optymalizacji zapytań – wspiera procesy tworzenie indeksów, widoków indeksowanych, statystyk i partycji. Jako źródło przyjmuje np. zbiór informacji śladu – trace – przechwycony przez Profiler lub konkretny skrypt zapytania.

Business Intelligence Development Studio

IDE (Integrated Development Environment) dedykowane środowisko oparte o .NET framework, do tworzenia raportów w ramach Reporting Services, paczek SSIS (Integration services) oraz usług analitycznych (Analysis Services).

SQLCMD

Prosta aplikacja, za pomocą której mamy możliwość połączenia się z bazą danych z poziomu linii poleceń. Idealnie nadaje się do wszelkich czynności administracyjnych, skryptów. Szczególnie przydatna do automatyzacji zadań w SQL Server Express, w której nie mamy dostępnej usługi SQL Server Agent.


* Projekt Hekaton, dotyczy funkcjonalności przetwarzania transakcyjnego in-memory

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ń.

Środowisko testowe

Nauka jakiegokolwiek języka „na sucho”, mija się z celem. Dotyczy to zarówno tradycyjnych języków obcych ale także programowania czy języka SQL. Wszystkie tematy poruszane w tym kursie, najlepiej przećwiczyć na własnym poligonie. W trakcie pisania, pojawiają się nowe pytania, można szerzej przećwiczyć różne warianty i tym samym lepiej zrozumieć temat.

Do pełnego zrozumienia zagadnień poruszanych tutaj, koniecznie powinieneś zbudować sobie środowisko testowe.

Jeśli chodzi o SQL Server sugeruję zacząć od wersji najnowszej (obecnie 2017).

Serwer testowy

Do celów edukacyjnych, idealne są edycje darmowa Express oraz testowa Evaluation Edition (180-dniowy trail w najwyższej wersji Enterprise). Zagadnienia przedstawione w tym kursie, możesz w pełni przetestować na darmowej wersji Express dostępnej tutaj.

Co prawda jej możliwości są okrojone w stosunku do pełnych, płatnych wersji (pod względem wydajnościowym, funkcji związanych z wysoką dostępnością, BI etc.), ale jeśli chodzi o naukę SQL to w zupełności wystarczy.

Wersja SQL Server Express to platforma, którą możesz używać również we wdrożeniach komercyjnych. Jednymi z bardziej dotkliwych ograniczeń są :

  • maksymalny rozmiar bazy do 10GB
  • procesy SQL Servera będą wykorzystywały max jeden procesor (lub 4 rdzenie) oraz 1 GB RAM.

Pełne porównanie funkcjonalności różnych wersji SQL Server 2012 – znajdziesz na oficjalnych stronach Microsoft.

Instalując serwer, pamiętaj o zaznaczeniu narzędzi do zarządzania bazami danych czyli Microsoft SQL Server Management Studio. Jest to graficzny interfejs użytkownika, dzięki któremu w prosty sposób możesz zarządzać serwerem, bazami i wszystkimi obiektami. Jest to też wygodne środowisko deweloperskie, do pisania, testowania zapytań i skryptów.


Testowe bazy danych

Przykłady prezentowe w tym kursie, bazują na dwóch oficjalnych, testowych bazach danych. Możesz je pobrać tutaj.

  • Northwind – prosta, mała baza, znana bardzo dobrze ze starszych wersji SQL Server. Używam jej bo zawiera proste struktury tabel, na których można prezentować bardziej złożone zapytania, bez „zaciemniania” sedna tego co chce pokazać. Do instalacji, wystarczy uruchomić skrypt SQL (otwórz go i uruchom w Microsoft SQL Server Management Studio) – do pobrania tutaj.
  • AdventureWorks2008 – znacznie bardziej zaawansowana, posiada wiele cech rzeczywistej, produkcyjnej bazy danych. Tu z uwagi na liczbę, różnorodność danych, łatwiej zaprezentować aspekty związane z wydajnością zapytań. Najłatwiej „zainstalować” ją poprzez dołączenie do bazy – prawy strzał w oknie Object Explorera na Databases i Attach. Wystarczy następnie wskazać plik bazy z rozszerzeniem mdf (podstawowy plik bazy danych). Baza dostępna w różnych wersjach (OLTP,DW). W kursie korzystam z AdventureWorks2008, do pobrania na stronach CodePlex, gdzie znajdziesz również jej pozostałe wersje.
    Env_att
  • Twoje własne testowe bazy danych. Nic nie stoi na przeszkodzie, żebyś tworzył własne testowe bazy danych. Oczywiście musisz mieć uprawnienia na serwerze do ich tworzenia, ale zakładając że jesteś sam sobie sterem, żaglem i adminem, możesz to wykonać za pomocą T-SQL :
    CREATE DATABASE Testowa
    

Środowisko testowe i produkcyjne

Z definicji, środowiska testowe i produkcyjne powinny być maksymalnie odseparowane, najlepiej hardware’owo.

Jeśli chcesz testować zapytania na bazach produkcyjnych, powinieneś zawsze uwzględnić fakt, że działanie ich wpływa na pracę serwera. Odczytujesz przecież konkretne dane, zatem korzystasz z zasobów dyskowych, pamięci i mocy procesora maszyny. Niektóre z nich mogą znacząco wpłynąć na ogólną wydajność (np. jeśli przez przypadek napiszesz zapytanie będące iloczynem kartezjańskim, lub gdy będziesz chciał odczytać wszystkie rekordy tabeli, która zawiera wiele milionów rekordów).

Jako administrator baz danych sugeruje, że jeśli musisz testowo odpytywać bazy produkcyjne lub ćwiczyć na nich swoje umiejętności, zastosuj się do kilku poniższych zasad :

  • jeśli możesz, ćwicz zawsze na bazie testowej na serwerze testowym :) (może to być kopia zapasowa bazy produkcyjnej)
  • ustaw poziom izolacji swojej sesji na Read Uncommitted. Przyspieszy to czas odczytów (nie będziesz czekał na zwolnienie blokad) Twoich zapytań i Twojej działalności na danych produkcyjnych. Wykonasz to za pomocą polecenia SET. Jeśli testujesz swoje zapytania, zazwyczaj izolacja między transakcjami nie jest istotna (chyba że testujesz współbieżność).
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
  • próbkuj wyciągane dane z tabel stosując klauzulę TOP (jeśli chcesz na przykład sprawdzić co dana tabela zawiera). Nie pobieraj wszystkiego jak leci – select * from tabela to bardzo złe zapytanie w środowisku produkcyjnym

Obiekty w bazach danych

Artykuł ten opisuje przegląd najważniejszych obiektów dostępnych w SQL Server. Ma na celu pokazać ich różnorodność, wyjaśnić ich ogólne przeznaczenie. Chcę tutaj głównie zaznajomić początkujących użytkowników baz danych ze środowiskiem i obiektami, które można spotkać lub które mogą się przydać w kontekście pisania zapytań.

Serwer czyli System Zarządzania Bazą Danych (SZBD)

Od tego trzeba zacząć. System zarządzania bazami danych (Relational DataBase Management System RDBMS) – to kompletne środowisko w którym tworzymy, zarządzamy i pracujemy na bazach. Najpopularniejszymi są MS SQL Server, Oracle, MySQL, DB2, PostrgeSQL.

Wszystkie obiekty na serwerze tworzą strukturę hierarchiczną – serwer (instancja) jest na czele tej hierarchii.

Zawiera w sobie wszystkie komponenty odpowiedzialne za pracę systemu bazodanowego. Realizuje procesowanie zapytań, zarządzanie składowaniem danych, dostępem itd. . Jest to podobnie jak system operacyjny (tyle że warstwę wyżej w modelu ISO), całe środowisko w którym poruszamy się mówiąc o pracy z bazami danych.

Na danej fizycznej maszynie, możemy mieć zainstalowanych wiele różnych serwerów bazodanowych (np. kilka instancji MS SQL Server). Mówiąc o RDBMS SQL Server – jest to usługa systemowa, czyli aplikacja działająca w tle, realizująca wszystkie zadania związane z obsługą baz danych.

Wszystkie obiekty opisane poniżej, są tworzone i zarządzane właśnie na danym serwerze bazodanowym.


Baza danych

Na każdym serwerze, możemy utworzyć wiele baz danych. W implementacji SQL Server, spotkamy się z bazami systemowymi (master, model, msdb, tempdb) oraz nazwijmy je „regularnymi”, użytkownika – czyli np. bazy sklepu internetowego, firmy produkcyjnej etc..

Hierarchię obiektów dobrze obrazuje drzewo widoczne w oknie Object Explorera w Microsoft SQL Server Management Studio.
DbObjects_01

Każda baza, wraz ze wszystkimi danymi, które zawiera, jest składowana niezależnie, w przynajmniej dwóch plikach (pliku bazy i pliku logu transakcyjnego).

Wszystkie obiekty bazodanowe, muszą posiadać unikalną nazwę, w ramach bazy danych. W pełni kwalifikowana nazwa każdego obiektu jest czteroczłonowa, zgodna ze schematem :

<nazwa_serwera>.<nazwa_bazy_danych>.<nazwa_schematu>.<nazwa_obiektu>

Dzięki temu, jest ona jednoznacznie określona. W skryptach, zapytaniach SQL, możemy odwoływać się do obiektów w różnych bazach i na różnych serwerach – stąd w zależności od potrzeb stosujemy krótszą lub dłuższą formę.

Minimalną zalecaną nazwą po której odwołujemy się do określonego obiektu, jest nazwa dwuczłonowa – czyli <nazwa_schematu>.<nazwa_obiektu>.

Schemat

Ponieważ wszystkie obiekty tworzone są w ramach jakiegoś schematu – ważne abyś dowiedział się o nich na początku. Schemat to niezależna warstwa, związana z zarządzaniem bezpieczeństwem. Jest to kontener, w ramach którego tworzone są obiekty. Dzięki temu, nadając uprawnienia do danego schematu, możemy kontrolować uprawnienia na poziomie wszystkich obiektów należących do niego. Każdy użytkownik posiada schemat domyślny, który jest rozpatrywany wtedy, gdy odwołujemy się do obiektu tylko po jego nazwie.

Żeby sprawdzić jaki jest domyślny schemat w ramach bazy, można użyć do tego, prostej funkcji systemowej :

select SCHEMA_NAME() as DefaultSchema;

Mój schemat domyślny to dbo.Następujące zapytanie, zwróci komunikat o błędzie :

use AdventureWorks2008
go
 
select FirstName,LastName from Person
Msg 208, Level 16, State 1, Line 2
Invalid object name 'Person'.

Stanie się tak, ponieważ nie użyłem w zapytaniu nazwy schematu w którym zawarta jest tabela Person. Został więc określony mój schemat domyślny czyli dbo. W tym zapytaniu odniosłem się do tabeli dbo.Person, której nie ma w bazie AdventureWorks2008.

Jest to bardzo ważna zasada, aby pamiętać o tym, że odwołujemy się zawsze do nazwy obiektu razem z nazwą schematu w której ten obiekt się znajduje. Zapytanie więc powinno wyglądać następująco :

Select * from Person.Person

Tabela

Podstawowymi obiektami w każdej bazie są tabele. W tym miejscu zaczyna się tak naprawdę modelowanie rzeczywistości na postać relacyjną. Tabela – to definicja pewnej klasy obiektów które chcemy przechowywać. Każda tabela zawiera zbiór elementów opisany za pomocą atrybutów (kolumn). Każdy atrybut (kolumna), jest określany typem danych (np. data urodzin powinna być opisana jako typ danych daty, a liczba. Widzimy więc jak mocno hierarchizowana jest ta struktura.
DbObjects_05
Tabela to obiekt nadrzędny w stosunku do :

  • kolumn czyli atrybutów, opisujących elementy przechowywane w tabeli,
  • kluczy – czyli relacji do innych tabel,
  • ograniczeń (constraints) służących do zapewnienia integralności danych,
  • procedur wyzwalanych (triggers) – obiektów programistycznych, za pomocą których możemy realizować rozmaite cele, np. archiwizowanie modyfikowanych elementów.
  • indeksów i statystyk – obiekty poprawiające wydajność zapytań.

W zapytaniach odwołujemy się tylko do nazw tabel, kolumn. Potrzebna jest także wiedza na temat kluczy w kontekście pisania kwerend do wielu tabel (po jakich kolumnach będziemy łączyć ze sobą tabele). Pozostałe obiekty dotyczą projektu samej tabeli, zapewnienia integralności danych (typy danych użyte w kolumnach, ograniczenia i procedury wyzwalane) i służą do optymalizacji zapytań (statystyki i indeksy).
Nie będę ich tu szczegółowo opisywał bo wykraczają poza zakres tego kursu. Nie są bezpośrednio związane z DQL czyli pisaniem zapytań.

Widok

Widok inaczej perspektywa (określenie używane w ORACLE), to po prostu zapisana kwerenda, czyli inne spojrzenie (stąd pewnie nazwa widok) na tabele. Są one nazywane czasem wirtualnymi tabelami – bo przecież zapytanie zwraca nam taką „wirtualną tabelę”. Rezultatem jest przecież zawsze pewien zbiór elementów. Są wygodnym sposobem, na ukrywanie potencjalnie skomplikowanej struktury bazy przed użytkownikami (dla wygody jak również dla bezpieczeństwa). Odwołujemy się do nich w zapytaniach jak do zwykłej tabeli.
DbObjects_06

Widoki, to niezmaterializowane tabele wirtualne. W ogólności nie poprawiają wydajności zapytań, chyba że utworzymy na nich indeksy.

Synonim

To aliasy nazw obiektów. Pozwalają nadać istniejącym obiektom bardziej ludzkie imię :) lub po prostu uniezależnić aplikacje od zmian w strukturze bazy. Szczególnie pomocne przy migracjach lub modyfikacjach środowiska. Są alternatywnymi nazwami obiektów takich jak tabele, widoki, funkcje, procedury składowane.

Obiekty programistyczne

W ramach bazy, możemy tworzyć różne obiekty programistyczne. Ich przeznaczenie jest bardzo szerokie. Ograniczę się tylko do bardzo ogólnego ich opisu bo nie stanowią tematu tego kursu.
DbObjects_07

Stored Procedures

Procedury składowane to niezależne jednostki, skrypty T-SQL, realizujące dowolne funkcje programistyczne. Mogą np. importować dane z pliku, dokonując przy okazji szeregu weryfikacji (czyszczenia), wykonywać określone czynności administracyjne, utrzymaniowe. Procedury mogą przyjmować parametry i zwracać zbiory wynikowe. Dzięki nim (podobnie jak funkcją), możemy zaszyć logikę biznesową na serwerze SQL tworząc wielowarstwową strukturę aplikacji.
Procedury składowane nie mogą być używane w kwerendach, choć za ich pomocą można także realizować dostęp do danych (mogą zwracać wynik kwerendy, zapisanej w ich ciele).

Assemblies

SQL Server umożliwia integrację środowiska CLR. Możemy tworzyć obiekty programistyczne w .NET i używać ich w ramach serwera bazy danych. Jest to znakomite uzupełnienie możliwości języka SQL (który jest zoptymalizowany do działania na zbiorach) językami obiektowymi.

Database Triggers

Jest to specjalny typ procedur wyzwalanych, które mogą być wywoływane na wskutek operacji DDL czyli związane z dowolną modyfikacją obiektów bazodanowych (tworzący. Możemy za ich pomocą np. logować czy też kontrolować wszelkie zmiany w strukturze.

Funkcje

SQL Server oferuje szereg dostępnych wbudowanych funkcji systemowych. Funkcje w ogólności szeregujemy ze względu na wynik jaki zwracają na skalarne lub tabelaryczne.

Funkcje skalarne, zwracają w wyniku jednoelementowy zbiór opisany jednym atrybutem np. funkcja getdate(), która zwraca aktualną datę i czas systemowy. Stosować możemy je np. w klauzuli SELECT czy w warunku filtracji WHERE. Wszędzie tam, gdzie wartością oczekiwaną jest wartość skalarna.

Funkcje tabelaryczne mogą zwracać zbiór wieloelementowy. Stosować możemy je np. we FROM, ale już nie w SELECT. Dobry przykładem jest funkcja OPENQUERY, za pomocą której możemy np. dobrać się w kwerendzie do zawartości danych w pliku excel.
Oprócz funkcji wbudowanych, systemowych, mamy możliwość tworzenia własnych metod. Są trzy rodzaje funkcji użytkownika – skalarne, tabelaryczne proste i tabelaryczne złożone. Dzięki nim zrealizujemy dowolne cele programistyczne. W dalszej części tego kursu opisuję w detalach zastosowanie

Types

Typy danych użytkownika – to typy pochodne z już istniejących. Jeśli np. stosujemy w bazie typ danych char(12) i używamy go w różnych tabelach wiele razy – możemy go zdefiniować jako typ użytkownika globalny i wszędzie tam gdzie będzie potrzebny po prostu używać po nazwie własnej.

Rules

Ten typ obiektu będzie wycofane w przyszłych wersjach SQL Server.
Idea reguł jest bardzo podobna do typów użytkownika. Są to definicje ograniczeń (constraintów), które możemy podpiąć do różnych kolumn, tabel.

Defaults

Ten typ obiektu będzie wycofane w przyszłych wersjach SQL Server.
Wartości domyślne – to trzeci typ obiektów wielokrotnego użytku do określania struktury i własności kolumn po regułach i typach użytkownika. Jest on niezależnym obiektem, który może być „podpięty” pod dowolne kolumny tabel jako definicja ich wartości domyślnej. Zgodnie z nią, będą uzupełniane kolumny w momencie dodawania wierszy jeśli w danym elemencie nie będzie jawnie określona wartość.

Sequences

Sekwencje to nowy typ obiektów w SQL Server 2012. Są to liczniki, generatory kolejnych liczb, nadające numery według określonego porządku, które nie są zależne od konkretnej tabeli.


Pozostałe obiekty

Przedstawione powyżej obiekty są tylko podzbiorem z dostępnych struktur, które oferuje SQL Server. Istnieje szereg obiektów związanych z bezpieczeństwem, usługami Brokera (kolejki, wiadomości etc.) czy indeksami pełnotekstowymi.

Projektowanie i normalizacja bazy danych

Relacyjnadesign_small baza danych opisuje swoim modelem pewien fragment rzeczywistości, niezbędny do realizacji określonych celów dla których została stworzona.

Artykuł ten, przedstawia ogólne zasady projektowania bazy danych. W szczególności wyjaśnia czym jest normalizacja, w kontekście pisania zapytań łączących dane z wielu tabel. Opisuje najważniejsze (trzy pierwsze) postacie normalne – postulaty Codd’a.

Skrypt obrazujący, poniższy proces normalizacji – do pobrania tutaj.


Problemy związane ze składowaniem danych

Model relacyjnych baz danych, opracowany przez Edgara F. Codd’a i przedstawiony w „A Relational Model of Data for Large Shared Data Banks”, definiuje m.in. cechy i strukturę dobrze zaprojektowanej bazy. Najłatwiej streścić sedno i przedyskutować potencjalne zagrożenia (złego projektu) na przykładzie, prezentującym kroki procesu normalizacji.

Po co właściwie wiele tabel, relacji między nimi. Czy nie prościej i łatwiej byłoby wszystko przechowywać w jednym miejscu – np. w jednej tabeli, tak jak w arkuszu Excel ?
Przyjrzyjmy się strukturze tabeli zawierającej informacje o zleceniach i Klientach.

select * from #Zamowienia_UNF

Normalizacja bazy
Zwróć uwagę na (najważniejsze) problemy, których dostarczy nam tak zaprojektowana tabela :

  • te same informacje przechowywane są wiele razy w wielu wierszach (np. AdresKlienta ). Zajmują one niepotrzebnie zasoby. Nie tylko dyskowe ! Ponieważ każdy wiersz, zawiera komplet informacji, zmniejsza się efektywność składowania danych w tabeli (jest to związane z anatomią pliku danych, sposobem zapisu wierszy). Skutkuje to w dalszej kolejności zwiększeniem ilości operacji odczytu /zapisu (kolejka do I/O to jedno z najczęstszych wąskich gardeł) a także późniejszą alokacją większych zasobów pamięci RAM (dla zdublowanych informacji).
  • powtarzające się danej to także problem związany z aktualizacją. Jeśli chcemy poprawić np. adres Klienta, trzeba zmodyfikować wszystkie jego wystąpienia (może to być wiele rekordów). Co jeśli o którymś zapomnimy? Które dane będą prawdziwe? Pojawia się tu problem zachowania integralności danych. Problem aktualizacji eskaluje się gdy te same dane będą przechowywane w kilku tabelach. Nie dość, że trzeba pamiętać o modyfikowaniu wszystkich miejsc, to przecież wpływa to bezpośrednio na czas wykonywanych aktualizacji (transakcji). Zwiększa się prawdopodobieństwo wystąpienia zakleszczeń (deadlocków), następuje degradacja równoległego dostępu do danych. Jest to bardzo namacalny problem, subiektywnego postrzegania szybkości i jakości pracy systemu przez użytkowników.
  • kolumny AdresKlienta oraz SzczegolyZamowienia, zwierają kolekcje wartości. Skutkuje to brakiem możliwości wykonania podstawowych operacji na danych – np. podsumowania według wartości zleceń, liczby pozycji etc. Ponadto przeszukiwanie takich kolekcji jest nieefektywne. Sprowadza się do przeszukiwania ciągów tekstowych (stringów). Brak możliwości zapewnienia pełnej integralności danych bo nie ma nad nimi kontroli.
  • usunięcie rekordu – pozycji zamówienia, skutkuje utratą informacji o Kliencie, czyli takiej której nie chcielibyśmy tracić.
  • analogicznie w drugą stronę – problem z dodawaniem informacji – konieczne określenie informacji, których być może jeszcze nie znamy, lub których może w ogóle nigdy nie będzie – np. Klient, który nie złożył żadnych zamówień.

Normalizacja

Wszystkie powyższe problemy i anomalie, rozwiązują odpowiednie postacie normalne (postulaty Codd’a). Postacie normalne wyższego rzędu, implikują wszystkie niższe. Baza danych jest znormalizowana np. do trzeciej postaci normalnej, jeśli są spełnione 1NF – 3NF.

Normalizacja to bezstratny proces organizowania danych w tabelach mający na celu zmniejszenie ilości danych składowanych w bazie oraz wyeliminowanie potencjalnych anomalii opisanych powyżej.

Pierwsza postać normalna 1NF

Pierwsza postać normalna to podstawa baz – mówi o atomowości danych. Czyli tabela (encja) przechowuje dane w sposób atomowy. Każde pole przechowuje jedną informację, dzięki czemu możemy dokonywać efektywnych zapytań. Wprowadza także pojęcie istnienie klucza głównego identyfikującego bezpośrednio każdy wiersz –unikalności. Warto pamiętać również o tym, że w dziedzinie teorii zbiorów kolejność wierszy jest dowolna (chyba że jawnie taki zbiór posortujemy).

Przejście na 1NF, nie może powodować utraty żadnych informacji, nie ma znaczenia kolejność elementów w zbiorze. Ta zasada dotyczy każdej postaci normlanej.

Mówimy, że tabela (encja) jest w pierwszej postaci normalnej, kiedy wiersz przechowuje informacje o pojedynczym obiekcie, nie zawiera kolekcji, posiada klucz główny (kolumnę lub grupę kolumn jednoznacznie identyfikujących go w zbiorze) a dane są atomowe. Zobaczmy, jak będzie wyglądała nasza struktura, jeśli spróbujemy doprowadzić ją do 1NF :

-- tabela w pierwszej postaci normalnej 
 
select * from #Zamowienia_1NF

Pierwsza postać normalna
Projekt bazy danych powinien uwzględniać wszystkie wymogi biznesowe, obecne i przyszłe. Nie ma jednej recepty na najlepszy design.

Zwróć uwagę, że kolumna Adres nie jest atomowa. Wszystko tak naprawdę zależy od definicji atomowości, jaka nas satysfakcjonuje. Jeśli atomowa informacja o adresie to dla nas nazwa ulicy z numerem to wszystko jest ok. Co innego jeśli wymogi biznesowe potrzebują rozdzielenia tych informacji – np. system zleceń dla firmy kurierskiej. Obsługa jednej ulicy może być realizowana przez kilku kurierów (np. długa ulica Piotrkowska w Łodzi) – tu z pewnością trzeba by rozdzielić te informacje na dwie kolumny.

W tym miejscu, trzeba dodać jeszcze jedną istotną uwagę. Jeśli normalizujemy tabele, czyli rozdzielamy informacje na dane atomowe, konieczne jest określenie typu danych kolumn.

Stosujemy zawsze najmniejsze typy z możliwych, jakie są konieczne do spełnienia wymogów projektowych. Jeśli składujesz dane np. o dacie urodzin – interesuje nas za zwyczaj tylko data, nic więcej (chyba że w bazie szpitalnej, gdzie również interesująca jest informacja o godzinie narodzin). W większości przypadków, wybrany powinien zostać typ danych date – który zajmuje 3 bajty. Powszechnie popełnianym błędem jest nadużywanie dużych typów. Jeśli zostanie wybrany typ datetime – dla każdego rekordu, dla tej kolumny zostanie skonsumowane 8 bajtów. Niby niewiele, ale czy na pewno ?

  • składowanie – różnicę (8-3=5 B) można pomnożyć przez np. N-milionów wystąpień (rekordów) – wtedy zaczyna robić wrażenie.
  • pamięć RAM – podczas joinów tabel zamiast 3+3 czyli 6 bajtów na każdy łączony wiersz – zostanie zalokowane 8+8 = 16 B (10 B więcej, znów możemy rozpatrzeć to razy N-milionów wystąpień)
  • zwiększony ruch sieciowy, obciążenie interfejsów kart sieciowych,
  • zmniejszenie efektywności przechowywania wierszy na stronach – więcej operacji I/O
  • a przecież takich kolumn z nadużyciem możemy mieć wiele w tabeli.

Pamiętajmy o tym podczas procesu projektowania czy też normalizacji tabel, że w dobry projekt bazy zaczyna się od fundamentu – czyli typów danych opisujących obiekty.

Druga postać normalna 2NF

Ta postać określa esencję dobrego projektowania bazy. Mówi o tym, że każda tabela powinna przechowywać dane dotyczące tylko konkretnej klasy obiektów.
Jeśli mówimy o encji (tabeli) np. Klienci, to wszystkie kolumny opisujące elementy takiej encji, powinny dotyczyć Klientów a nie jakiś innych obiektów (np. ich zamówień).

Zatem normalizując do 2NF, wydzielić należy zbiór atrybutów (kolumn) który jest zależny tylko od klucza głównego. Wszystkie atrybuty informacyjne (nie należące do klucza), muszą zawierać informacje o elementach tej konkretnej klasy (encji, tabeli) a nie żadnej innej. Kolumny opisujące inne obiekty, powinny trafić do właściwych encji (tabel) w których te obiekty będziemy przechowywać.

W naszym przykładzie wykonajmy analizę bazy zamówień, która jest już w 1NF. Oznaczmy na początek atrybuty informacyjne które nie należą do klasy Zamówień (nie zależą funkcyjnie od klucza głównego).

-- tabela w pierwszej postaci normalnej 
select * from #Zamowienia_1NF

Normalizacja do 2NF
Widać że istnieją w niej atrybuty związane z różnymi obiektami. Najbardziej rzucającymi się w oczy są z pewnością kolumny opisujące klasę Klientów – zaznaczone na niebiesko. Następnie można wydzielić również informacje o detalach zamówienia (ElementZamowienia, Ilosc, CenaJedn) – w pomarańczowej ramce.

Te wszystkie atrybuty, muszą powędrować do nowych tabeli – właściwych dla obiektów danego typu.
Finalny obraz 2NF będzie składał się więc z 4 tabel : Zamowienia, Klienci, DetaleZamowien i Produkty.
W wyniku tej operacji, tabela z informacjami o zamówieniach będzie wyglądała tak :

-- tabela w drugiej postaci normalnej 
select * from #Zamowienia_2NF

NormalForm_03

Nowe tabele będą wyglądały następująco :

-- nowa tabela przechowujące informacje o obiektach typu Klient
select * from #Klient_2NF

NormalForm_04

-- nowa tabela przechowujące informacje o detalach zamówień
select * from #DetaleZamowien_2NF

NormalForm_05

-- nowa tabela przechowujące informacje o produktach
select * from #Produkty_2NF

NormalForm_06
Jak widać usunięcie redundantnych informacji, skutkuje wzrostem złożoności struktury. Nie dość, że tworzymy nowe tabele, to jeszcze dokładamy kolumny np. IDKlient. Jednak, uwierz mi – nie jest to duża strata, zważywszy na zysk jaki nam daje ten krok normalizacji per saldo.

Relacyjne systemy bazy danych są projektowane do działania na zbiorach. Owszem koszt łączenia tabel bywa wysoki, ale zapewniając odpowiednie indeksy na łączonych kolumnach, jesteśmy w stanie zagwarantować równowagę pomiędzy wydajnością a plusami wynikającymi z normalizacji.

Doprowadziliśmy więc do sytuacji, w której każda tabela (encja) przechowuje informacje opisujące tylko obiekty właściwe dla niej. Cały proces, musi być bezstratny – udowodnijmy to poniższym zapytaniem :

SELECT z.NumerZamowienia, k.NazwaKlienta, k.Adres, k.KodPocztowy, k.Miasto, k.Wojewodztwo, 
       z.DataZamowienia, p.Nazwa, d.Ilosc, d.CenaJedn , z.WartZamNetto, z.Vat , z.WartZamBrutto
 
FROM #Zamowienia_2NF z inner join #Klient_2NF k on z.IDKlient=k.IDKlient
	inner join #DetaleZamowien_2NF d on z.NumerZamowienia=d.NumerZamowienia
	inner join #Produkty_2NF p on d.KodProduktu=p.KodProduktu
OREDER BY 1

Trzecia postać normalna 3NF

Trzecia postać normalna głosi, że kolumna informacyjna nie należąca do klucza nie zależy też od innej kolumny informacyjnej, nie należącej do klucza. Czyli każdy niekluczowy argument jest bezpośrednio zależny tylko od klucza głównego a nie od innej kolumny.

W naszym przypadku widać, że kolumny informacyjne związane z kosztami danego zamówienia oraz stawką podatku VAT, są ze sobą skorelowane. Z jednej z nich można śmiało zrezygnować, nie tracąc żadnej informacji (każda z nich zależy od klucza głównego, ale również od pozostałych dotyczących wartości). Możemy wyznaczyć np. wartość brutto na podstawie stawki VAT i wartości netto itd..

select NumerZamowienia,IDKlient,DataZamowienia,WartZamNetto,WartZamBrutto,
	((WartZamBrutto/WartZamNetto-1)*100) as [Vat %]
from #Zamowienia_3NF

NormalForm_08
Innym przykładem, mogą być kolumny wyliczeniowe wartości netto/brutto itp. ). Łatwo jednak w takich przypadkach podać przykłady, świadomego łamania 3NF na rzecz wydajności. Chociażby skomplikowane wyliczenia które muszą być wykonywane dla każdego wiersza, przy każdym zapytaniu.

Zalety i wady normalizacji

Projektowanie baz danych z pewnością nie jest trywialne. Pierwszym krokiem, powinna być właściwa analiza wymagań biznesowych, uwzględniająca przyszłe potrzebny i możliwości skalowania projektu.

Konieczne jest świadomość istnienia potencjalnych zagrożeń i wyczucie, wynikające z pewnością z doświadczenia – aby odpowiedzieć na pytanie jak mocno normalizować tabele. Najczęściej spotykany poziom to 3 postać normalna. Są sytuacje, zresztą nasuwające się intuicyjnie, w których zależy nam na procesie odwrotnym – denormalizacji celem maksymalnego zwiększenia szybkości wykonywania zapytań.

Poniżej kilka istotnych cech związanych z normalizowaniem baz danych :

  • zmniejszamy ogólną liczbę danych przechowywanych w bazach
  • rozwiązujemy problemy anomalii dodawania, modyfikacji i usuwania informacji (rekordów) z bazy
  • czasem spowalniamy wykonywanie zapytań – cena jaką płacimy za konieczność łączenia tabel.
  • ale też przyspieszamy wykonywanie określonych zapytań – tworzymy osobne tabele więc możemy utworzyć więcej indeksów klastrowych, lepsza efektywność przechowywania danych w tabelach.
  • wąskie tabele to bardziej efektywne przetwarzanie i składowanie ich na dysku, mniej operacji I/O.
  • lepsze zarządzanie transakcjami – szybsze wykonywanie update’ów, mniej blokad.

Oprócz przedstawionych powyżej trzech pierwszych postaci normalnych, spotkać możemy wyższe formy, choć zazwyczaj spotykane jedynie w teoretycznych rozważaniach :

  • postać normalna Boyce-Codd’a (określana również mianem 3.5NF)
  • czwarta postać normalna 4NF
  • piąta postać normalna 5NF