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.