Po co trzymać dane w kilku tabelach ?

Wyobraź sobie sytuację, że baza danych składa się tylko i wyłącznie z jednej tabeli. W takiej sytuacji pozbywamy się problemu łączenia tabel i do wszystkich danych mamy bezpośrednio dostęp. Czy to nie lepsze rozwiązanie? Dlaczego bazy danych zawierają setki a czasami nawet tysiące tabel?

Jest kilka powodów tego, jednym z ważniejszych jest tak zwana  redundancja danych. Redundancja danych oznacza nic innego jak nadmiarowość danych.Przypomnijmy sobie model bazy danych z ostatniego artykułu składającego się z 2 tabel : operatorzy i maszyn przy których pracują. Wielu pracowników może pracować przy tej samej maszynie, co oznacza, że wszystkie wartości jakie przechowuje tabela maszyny ( nazwa maszyny, data uruchomienia, id hali ) są takie same dla każdego pracownika. Posiadając w tabeli operatorzy, id maszyny, a pozostałe informacje o maszynie będą wypisane jako jeden rekord w tabeli maszyny, dane te będziemy w stanie odczytać dzięki poleceniu join lub możemy stworzyć tabele pomocniczą w której zestawimy id operatorów z id maszyn.

Gdybyśmy używali tylko jednej tabeli dla każdego operatora musielibyśmy przypisać te same 3 kolumny (nazwa maszyny, data uruchomienia, id hali), które będą przechowywały dokładnie te same dane.Wówczas kiedy chcielibyśmy zmienić dane o jakieś maszynie musielibyśmy ręcznie poprawiać dane, przy każdym operatorze tej maszyny, co przy większej ilości pracowników byłoby czasochłonne i męczące.

Tworzenie relacji

Na początek stworzymy relację pomiędzy stworzoną wcześniej tabelą maszyny i nowo stworzoną tabelą hala. Stworzymy nową tabelę by przypomnieć sobie i poćwicz poznane ostatnio klauzule CREATE TABLE oraz INSERT INTO 

CREATE TABLE HALE
(
ID_HALI NUMBER(2,0),
NAZWA VARCHAR2(30 BYTE),
MIASTO VARCHAR2(30 BYTE),
ADRES VARCHAR2(30 BYTE)
);

Następnie wypełniamy ją danymi

INSERT INTO HALE (ID_HALI,NAZWA,MIASTO,ADRES) VALUES (1,'Hala produkcyjna 1','Warszawa','Stalowa2');
INSERT INTO HALE (ID_HALI,NAZWA,MIASTO,ADRES) VALUES (2,'Magazyn A','Krakow','Czerwona32');
INSERT INTO HALE (ID_HALI,NAZWA,MIASTO,ADRES) VALUES (3 ,'Budynek administracyjny','Wroclaw','Centrum124');

Po wykonaniu tych operacji otrzymamy taką o to tabele:

Następnym krokiem będzie dodanie constreinów do obydwu tabel by zadbać o poprawność struktury bazy danych. Co to są constreiny?

Są to obiekty bazodanowe które tworzą ograniczenia na poszczególnych kolumnach, tak by zostały do nich wprowadzone odpowiednie dane. Do tworzenia relacji potrzebujemy dwóch constreinów:

PRIMARY KEY –  Klucz główny, zapewnia unikalność danych, czyli mamy pewność, że klucz główny występuję tylko raz .

FOREIGN KEY – klucz obcy,  przyjmuje tylko wartości takie same jak są w powiązanym kluczu głównym.

Dodajemy klucze do naszych tabel

Modyfikacja tabeli Hala:

ALTER TABLE HALE ADD CONSTRAINT "Hala PK" PRIMARY KEY (ID_HALI);
ALTER TABLE HALE MODIFY (NAZWA NOT NULL );
ALTER TABLE HALE MODIFY (ID_HALI NOT NULL );
ALTER TABLE HALE MODIFY (ID_HALI NOT NULL );
ALTER TABLE HALE MODIFY (ID_HALI NOT NULL );

Modyfikacja tabeli Maszyny

ALTER TABLE MASZYNY ADD CONSTRAINT "Maszyna PK" PRIMARY KEY (ID_MASZYNY);
ALTER TABLE MASZYNY MODIFY (ID_HALI NOT NULL );
ALTER TABLE MASZYNY MODIFY (NAZWA NOT NULL );
ALTER TABLE MASZYNY MODIFY (ID_MASZYNY NOT NULL);

ALTER TABLE MASZYNY ADD CONSTRAINT "HALA_MASZYNA" FOREIGN KEY (ID_HALI) REFERENCES HALE (ID_HALI);

 

Użycie klauzuli JOIN

Połączymy ze sobą dwie tabele. Tabele maszyny i hale

Robimy to w ten o to sposób

SELECT * FROM Maszyny JOIN Hale ON maszyny.id_hali=hale.id_hali

Otrzymujemy taką o to sklejkę dwóch tabel 

Zajmijmy się teraz opisem co tu się stało 

SELECT * FROM                                      Ta część  odpowiada za wyświetlenie wszystkich kolumn z wybranej tabeli                                                     

Maszyny JOIN Hale                                 Tutaj łączymy ze sobą tabele maszyny i hale za pomocą klauzuli JOIN

ON maszyny.id_hali=hale.id_hali        W tym fragmencie informujemy względem których danych łączmy tabelę, dzięki temu że użyliśmy                                                                                                      constreinów mamy  pewność, że wartość w kolumnie  maszyny.id_hali jest unikatowa i wartości się                                                                                                  nie powtarzają, a dane w kolumnie  hala.id_hali  odpowiadają tym z maszyny.id_hali.

 

Łączenie kilku tabel

A co zrobić kiedy chce się połączyć więcej niż 2 tabele? Żeby to sprawdzić stwórzmy kolejną tabele w której umieścimy id maszyn oraz id operatorów.

CREATE TABLE OPERATOR_MASZYNA
(
ID_OPERATORA NUMBER(4,0),
ID_MASZYNY NUMBER(4,0)
) ;

 

INSERT INTO OPERATOR_MASZYNA (ID_OPERATORA,ID_MASZYNY) VALUES ('10','30');
INSERT INTO OPERATOR_MASZYNA (ID_OPERATORA,ID_MASZYNY) VALUES ('10','60');
INSERT INTO OPERATOR_MASZYNA (ID_OPERATORA,ID_MASZYNY) VALUES ('20','10');
INSERT INTO OPERATOR_MASZYNA (ID_OPERATORA,ID_MASZYNY) VALUES ('30','20');
INSERT INTO OPERATOR_MASZYNA (ID_OPERATORA,ID_MASZYNY) VALUES ('30','70');
INSERT INTO OPERATOR_MASZYNA (ID_OPERATORA,ID_MASZYNY) VALUES ('40','50');
INSERT INTO OPERATOR_MASZYNA (ID_OPERATORA,ID_MASZYNY) VALUES ('60','40');

Kiedy wyświetlimy tą tabele 

To dane które w niej widzimy to id operatora oraz id maszyny, ale nic nam to nie mówi, żeby się to zmieniło najpierw dodamy parę constreinów

Do tabeli Operatorzy dodamy klucz główny do kolumny Id operatora 

ALTER TABLE OPERATORZY ADD CONSTRAINT "Operator PK" PRIMARY KEY (ID_OPERATORA);
ALTER TABLE OPERATORZY MODIFY (IMIE NOT NULL );
ALTER TABLE OPERATORZY MODIFY (NAZWISKO NOT NULL );
ALTER TABLE OPERATORZY MODIFY (ID_OPERATORA NOT NULL );

A do tabeli operator_maszyna dodamy dwa klucze obce by powiązać ją z tabelą operatorzy oraz maszyny 

ALTER TABLE OPERATOR_MASZYNA ADD CONSTRAINT "FK_ASS_3" FOREIGN KEY (ID_OPERATORA)
REFERENCES OPERATORZY (ID_OPERATORA);


ALTER TABLE OPERATOR_MASZYNA ADD CONSTRAINT "FK_ASS_4" FOREIGN KEY (ID_MASZYNY)
REFERENCES MASZYNY (ID_MASZYNY);

 

Kiedy zrobimy wszystkie powyższe czynności możemy zabrać się za stworzenie zapytanie wypisujące zrozumiałe dla nas informację

SELECT * FROM operator_maszyna                                                                                  JOIN operatorzy ON operatorzy.id_operatora = operator_maszyna.id_operatora
JOIN maszyny ON maszyny.id_maszyny=operator_maszyna.id_maszyny;

Dzięki temu zapytaniu otrzymamy znacznie różniącą się od poprzedniej tabele z której możemy już więcej wywnioskować. 

Łączenie tabeli do samej siebie

Tabele możemy również łączyć samą ze sobą, za przykład w tym posłuży nam tabela operatorzy w której mamy kolumnie przełożony. Kolumna ta posiada wartości odpowiadające danym z id_operator.

Żeby w jednym selekcie wypisać w miejscu przełożony imię i nazwisko, musimy użyć JOIN by połączyć to tabelę ze sobą.

Zapytanie będzie wyglądać następująco

SELECT o.nazwisko, o.imie, p.imie ImiePrzelozonego, p.nazwisko NazwiskoPrzelozonego FROM
operatorzy o
JOIN operatorzy p ON o.przełożony=p.id_operatora; 

(na pomarańczowo zostały zaznaczone aliasy, służą one do nadania tabeli lub kolumnie w tabeli tymczasowej nazwy. są często używane, aby nazwy kolumn były bardziej czytelne i ​​istnieją tylko na czas trwania zapytania.)

Podsumowując 

Nauczyłeś się dzisiaj dlaczego dane trzymamy w kilku tabelach, a nie ograniczamy się do jednej. Nauczyłeś się co to jest constrein i jak go dodawać do tabel oraz jak się korzysta z klauzuli JOIN by połączyć kilka tabel a nawet połączyliśmy tabele samom ze sobą . W kolejnym artykule poznasz różne odmiany JOINA jak również poznamy klauzule agregujące dane.