SQL #1: schemat bazy danych

Historia

Koncepcja baz danych jest prawie tak stara jak cała informatyka. Ogólna idea baz danych polega na posiadaniu wyspecjalizowanego programu (silnika bazodanowego), który mógłby wyszukiwać dane przede wszystkim po ich zawartości a nie po linkach do nich. Schemat bazy danych, to inaczej sposób opisania relacji i struktur Twoich danych. Kolejnym problemem było powtarzanie się danych oraz powolne ich przetwarzanie. Wyróżniamy różne rodzaje baz danych, np. relacyjne (oparte o ideę B-drzewa*), hierarchiczne lub tzw. NoSQL. Obecnie wciąż najpopularniejsze są bazy relacyjne, chodź coraz częściej ustępują bazom NoSQL (np. Mongo).

Przykładowe relacyjne bazy danych:

• Mysql
• Postgresql (na tej bazie opieram ten kurs)
• Oracle
• Mssql
• DB2

Rodzaje relacji

Relacja(ang. relationship) to powiązanie pomiędzy parą tabel. Istnieje ona wtedy, gdy dwie tabele są połączone przez klucz podstawowy i klucz obcy. Każda relacja, jaką zawiera Twój schemat bazy danych jest opisywana przez typ więzi istniejący między dwoma tabelami, typ uczestnictwa oraz stopień uczestnictwa tych tabel.

• Relacja jeden-do-jednego – występuje, gdy pojedynczemu rekordowi z tabeli A przyporządkowany jest dokładnie jeden rekord z tabeli B i na odwrót.
• Relacja jeden-do-wielu – występuje, gdy pojedynczemu rekordowi z tabeli A może odpowiadać jeden lub więcej rekordów z tabeli B, ale pojedynczemu rekordowi z tabeli B odpowiada najwyżej jeden rekord z tabeli A. Nie jest to więc tak wzajemna więź jak w przypadku jeden-do-jednego.
• Relacja wiele-do-wielu – występuje, gdy jednemu rekordowi z tabeli A przypisanych jest wiele rekordów z tabeli B oraz jednemu rekordowi z tabeli B przyporządkowanych jest wiele rekordów z tabeli

Schemat bazy danych w formie diagramu ERD

W tworzeniu specyfikacji systemowej jest analiza i modelowanie struktury informacji. Taki model to inaczej schemat bazy danych. Dla stworzenia takiego modelu dla struktury danych najczęściej stosuje się diagramy pojęciowe (konceptualne). ERD (ang. entity relationship diagram) jest graficznym odpowiednikiem modelu związku encji ERM (ang. entity relationship model).

Wyróżniane jest kilka konwencji zapisu diagramów ERD:

• notacja Martina
• notacja UML
• Crow’s Foot

Do stworzenia diagramu należy zrozumieć następujące podstawowe pojęcia:

• encja – reprezentacja wyobrażonego lub rzeczywistego obiektu, którego informacje przechowujesz.
• atrybut – element informacji służący do klasyfikowania, identyfikowania, kwalifikowania, określania ilości lub wyrażania stanu encji. Może być liczbą, tekstem, wartością logiczną lub obrazem.
• relacja/związek- jest to powiązanie między dwiema lub kilkoma encjami. Związki na diagramie można opisać poprzez liczebność, opcjonalność i relację między encjami.

Każdy związek ma dwa końce i każdy z nich ma przypisane następujące atrybuty:

• nazwa
• stopień związku

opcjonalność związku :

• związek opcjonalny

• związek wymagany

Normalizacja

Schemat bazy danych można przekształcić, w taki sposób, aby tabele w niej zawarte posiadały taka strukturę, aby nie duplikowały się wzajemnie. Z drugiej strony, ponieważ każda relacja w bazie danych, oznacza tworzenie kolejnych indeksów (co powoduje coraz większe obciążenie silnika bazodanowego), należy znaleźć złoty środek pomiędzy bardziej złożoną strukturą a optymalnym wyszukiwaniem. Kolejne stopnie normalizacji opisują ten proces.

• I postać normalna – zachodzi jeśli wartości atrybutów są niepodzielne. Tabela w tej postaci nie może zawierać powtarzających się grup informacji. Każda kolumna jest wartością skalarną a nie macierzą, listą lub jakąkolwiek złożoną strukturą danych. Można tą definicję rozumieć w ten sposób, że większość informacji trzymasz w jednej tabeli, ale sama tabela posiada już sensowny podział na kolumny.
• II postać normalna – relacja (tabela) jest w II postaci jeśli :
  • jest w I postaci normalnej
  • jeśli każdy atrybut tej relacji niewchodzący w skład żadnego klucza jest w pełni funkcyjnie zależny wyłącznie od podrelacji klucza głównego. Oznacza to po prostu logiczny podział na tabele przechowujące konkretne klasy informacji (np. z tabeli dane_użytkownika wydzielasz osobną tabelę adres).
• III postać normalna (najczęściej używana) – relacja (tabela) jest w III postaci jeśli:
  • jest w II postaci normalnej
  • każdy atrybut jest funkcjonalnie zależny jedynie od klucza głównego. Nie mogą więc istnieć jakiekolwiek zależności przechodnie (brak nadmiarowości, powtarzających się danych). Należy to rozumieć intuicyjnie. Jeśli posiadasz tabelę pojazd, która przechowuje zarówno auta jak i autobusy, być może warto by było wydzielić trzy tabele. Jedną tabelę rodzica, zawierającą kolumny z powtarzającymi się wartościami (np. numer seryjny, silnik, itp.). Natomiast wartości unikatowe trzymać w osobnych tabelach auto i autobus.

Klucze

Ogólne założenia:

• Klucz jest podstawowym zbiorem identyfikującym.
• Jest to taki zbiór atrybutów relacji, których kombinacje wartości jednoznacznie identyfikują każdą, krotkę tej relacji a żaden podzbiór tego zbioru nie posiada tej własności.
• W kluczu głównym nie może zawierać się wartość NULL.
• Służą także do sortowania danych.

Jeśli zbiór tworzący klucz jest jednoelementowy to mówi się wtedy o kluczu prostym, w przeciwnym przypadku jest to klucz złożony.

Klucz unikalny (ang. unique key) – jest to wartość, która w obrębie jednej tabeli jest niepowtarzalna, tak wiec nie mogą pojawia się dwa wiersze o tej samej wartości unikalnej. Np. PESEL lub nr ISBN dla książki. Dla kolumny można wymusić to, by była ona unikalna poprzez ustalenie indeksu unikalnego UNIQUE.

Klucz podstawowy, klucz główny (ang. primary key) – jest to klucz, który służy do jednoznacznej identyfikacji każdego wiersza w tabeli. Takim kluczem może być np. nr albumu studenta lub nr PESEL. Istnieje także możliwość utworzenia sztucznego klucza, który będzie nadawany automatycznie poprzez system bazy danych.

Klucz obcy (ang. foreign key) – ten typ klucza wykorzystywany jest do tworzenia relacji pomiędzy parą tabel. Wartość klucza obcego w jednej tabeli (tabeli zależnej) musi korespondować do wartości klucza podstawowego lub kandydującego w drugiej tabeli (tabeli nadrzędnej). Klucz obcy wymusza więzi integralności (referances).

Referencje

Przy ustalaniu zależności pomiędzy tabelami można ustalić, jakie działania będą podejmowane w przypadku , gdy zostanie zaktualizowany schemat bazy danych lub przy usunięciu rekordu z tabeli nadrzędnej:

• CASCADE
  • ON UPDATE CASCADE – zmiana klucza w tabeli nadrzędnej powoduje zmianę klucza obcego w tabeli zależnej.
  • ON DELETE CASCADE – usuniecie rekordu z tabeli nadrzędnej powoduje usunięcie korespondującego rekordu z tabeli zależnej.
• RESTRICT
  • ON UPDATE RESTRICT – nie może zostać przeprowadzona żadna aktualizacja na rekordzie tabeli nadrzędnej, jeśli istnieje powiązany z nią rekord w tabeli zależnej.
  • ON DELETE RESTRICT – nie można usunąć rekordu z tabeli nadrzędnej, jeśli istnieje powiązany rekord w tabeli zależnej.
• SET NULL
  • ON UPDATE SET NULL – w przypadku zmiany klucza podstawowego w tabeli nadrzędnej, wartość dla klucza obcego w tabeli zależnej będzie NULL.
  • ON DELETE SET NULL – w przypadku usunięcia rekordu w tabeli nadrzędnej, wartość dla klucza obcego w tabeli zależnej będzie NULL.
• SET DEFAULT
  • ON UPDATE SET DEFAULT – w przypadku zmiany klucza podstawowego w tabeli nadrzędnej zostanie ustalona wartość domyślna (DEFAULT) dla klucza obcego.
  • ON DELETE SET DEFAULT – w przypadku usunięcia rekordu w tabeli nadrzędnej zostanie ustalona wartość domyślna (DEFAULT) dla klucza obcego.
• ON UPDATE NO ACTION – w przypadku zmiany klucza podstawowego w tabeli nadrzędnej nie zmienia się wartość dla klucza obcego.

*Długi ale bardzo interesujący artykuł jak działają bazy relacyjne: http://coding-geek.com/how-databases-work/