Bazy Danych W03: Spójność relacji i JOINy Wojciech St. Mościbrodzki wojmos@pjwstk.pl

1. Bazy DanychW03: Spójność relacji i JOINyWojciech St. Mościbrodzkiwojmos@pjwstk.edu.pl

2. Podstawy SQL - przypomnienie • SQL jest językiem stosunkowo prostym (i archaicznym) • Założeniem SQL była realizacja podstawowych operacji bazodanowych: • wyszukiwanie rekordów - SELECT • dodawanie rekordów - INSERT • usuwanie rekordów - DELETE • modyfikacja rekordów - UPDATE • Poza „wielką czwórką” istnieją polecenia do zarządzania tabelami, bazami, tworzące indeksy, itp. • Polecenia wydawane w SQL nazywamy zapytaniami SQL (a nie – kwerendami; terminologia Microsoft)

3. SQL jako język zapytań • Typowy podział poleceń (zapytań) SQL: • SQL DML (ang. Data Manipulation Language – „język manipulacji danymi”), • SELECT, INSERT, UPDATE, DELETE • SQL DDL (ang. Data Definition Language – „język definicji danych”), • CREATE, DROP, ALTER, … • SQL DCL (ang. Data Control Language – „język kontroli nad danymi”). • GRANT, REVOKE, … • Inny podział: • polecenia standardowego (lub dialektowego) SQL (wykonuje je serwer) • polecenia typowe dla serwera konkretnego producenta (wykonuje je serwer) • polecenia dla klienta (oprogramowanie klienckie) – np. DELIMITER (mysql) • UWAGA: średnik (;) nie jest elementem zapytania, tylko znakiem terminalnym (po jego odebraniu klient przesyła zapytanie do serwera)

4. Podstawy SQL • Typy danych: • Numeryczne (mogą być SIGNED lub UNSIGNED): • Całkowite: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT • Rzeczywiste: FLOAT, DOUBLE, DECIMAL, NUMERIC • Bitowe: BIT • Logiczne: BOOL lub BOOLEAN (implementowane w Mysql jako TINYINT(1)) • Tekstowe: • CHAR – 0..255 znaków, przy przechowywaniu uzupełniane spacjami do podanego rozmiaru • VARCHAR – 0..65535 znaków, zajmują długość łańcucha + informacja o dłougości (1 lub 2 bajty) • BINARY i VARBINARY – przechowują łańcuchy w postaci binarnej • TEXT – duży obiekt tekstowy (BLOB – wersja binarna) • ENUM – typ wyliczeniowy, elementami są napisy • SET – typ specjalny, może zawierać dowolną liczbę (!!!) wartości ze zbioru

5. Typy danych: • Typy związane z czasem: • DATETIME – data (domyślnie) w formacie: YYYY-MM-DD HH:MM:SS • DATE – data w formacie: YYYY-MM-DD • TIME – czas w formacie: HH:MM:SS • Typy specjalne: • BLOB – duże obiekty binarne (np. obrazki, pliki mp3) • TIMESTAMP – specjalny typ (znacznik czasowy, w formacie DATETIME)

6. Spójność relacyjna • Spójność relacyjna (relational integrity) – sytuacja, w której dane są w takiej postaci, która jest "wysokiej jakości": • Spójność encji (entity integrity) – każde dwa elementy danych (obiekty) są rozróżnialne. Innymi słowy: istnieje klucz główny, a wartości tego klucza są ustalone (nie występuje NULL, czyli wartość nieustalona) • Spójność wartości nieustalonych (null integrity) – dla określonych danych wartość NULL jest nieakceptowalna • Spójność dziedzinowa (domain integrity) – wartości atrybutów są w dopuszczalnym zakresie (np. wiek nie może być ujemny, liczba pracowników – wyrażona ułamkiem itp.). Spójność domenowa sugeruje, że dane z różnych dziedzin nie powinny być porównywane "wprost". • Spójność referencyjna (referential integrity) – odnosi się do relacji pomiędzy dwoma tabelami; wymaga aby wartości klucza obcego odpowiadały istniejącym wartościom tabeli wskazywanej (klucz główny)

7. Spójność referencyjna • Przypomnienie: • Klucz kandydujący – jeden z zestawów atrybutów, który może pełnić rolę klucza • Klucz główny – wybrany arbitralnie klucz spośród kluczy kandydujących • Klucz obcy – zestaw atrybutów, który odpowiada kluczowi głównemu innej tabeli

8. Naruszenie spójności referencyjnej • Naruszenia spójności • DELETE: usunięcie rekordu, do którego ID jest wartością klucza obcego • UPDATE: zmiana wartości klucza obcego na nieistniejący (brak wartości klucza głównego) lub zmiana wartości klucza głównego, któremu odpowiada klucz obcy • INSERT: wstawienie rekordu, którego klucz obcy odpowiada nieistniejącej wartości klucza głównego

9. Więzy integralności • Obsługa więzów integralności w mysql (UWAGA: tylko InnoDB): [CONSTRAINT] FOREIGN KEY (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION • Tabela "główna" musi istnieć podczas tworzenia tabeli "potomnej": create table dzial ( id int auto_increment primary key, nazwa char(10) ) ENGINE=InnoDB; create table pracownik ( id int auto_increment primary key, imie char(10), nazwisko char(15), id_dzi int, CONSTRAINT FOREIGN KEY (id_dzi) REFERENCES dzial(id) ) ENGINE=InnoDB; • Usuwanie tabel również wymaga odpowiedniej kolejności!

10. Spójność referencyjna • Więzy typu RESTRICT (w MySQL = NO ACTION) zabraniają wykonania operacji create table pracownik ( id int auto_increment primary key, nazwisko char(15), id_dzi int, CONSTRAINT FOREIGN KEY (id_dzi) REFERENCES dzial(id) ) ENGINE=InnoDB; insert into pracownik values (1,'Jan','Nowak',1); ERROR 1452 (23000): Cannot add or update a child row:a foreign key constraint fails (`rbd3/pracownik`, CONSTRAINT `pracownik_ibfk_1` FOREIGN KEY (`id_dzi`) REFERENCES `dzial` (`id`)) mysql> delete from dzial where id=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`rbd3/pracownik`, CONSTRAINT `pracownik_ibfk_1` FOREIGN KEY (`id_dzi`) REFERENCES `dzial` (`id`))

11. Spójność referencyjna • Więzy typu CASCADE przenoszą operacje (DELETE lub CASCADE) z rekordów tabeli "rodzica" na tabelę "potomną" create table pracownik ( id int auto_increment primary key, nazwisko char(15), id_dzi int, CONSTRAINT FOREIGN KEY (id_dzi) REFERENCES dzial(id) ON DELETE CASCADE ) ENGINE=InnoDB; mysql> select count(id) from pracownik; delete from dzial where id=1; select count(id) from pracownik; +-----------+ | count(id) | +-----------+ | 4 | +-----------+ +-----------+ | count(id) | +-----------+ | 2 | +-----------+

12. Spójność referencyjna • Więzy typu SET NULL po operacji DELETE/UPDATE na rekordach tabeli "rodzica" ustawiają wartości klucza obcego na NULL create table pracownik ( id int auto_increment primary key, nazwisko char(15), id_dzi int, CONSTRAINT FOREIGN KEY (id_dzi) REFERENCES dzial(id) ON DELETE CASCADE ) ENGINE=InnoDB; select * from telefon; delete from pracownik where id=3; +----+--------+--------+ | id | serial | id_pra | +----+--------+--------+ | 1 | NP3451 | 1 | | 2 | ED2221 | 2 | | 3 | AP1111 | 3 | | 4 | AP1311 | 3 | | 5 | AS8751 | 4 | | 6 | SU9458 | 3 | +----+--------+--------+ +----+--------+--------+ | id | serial | id_pra | +----+--------+--------+ | 1 | NP3451 | 1 | | 2 | ED2221 | 2 | | 3 | AP1111 | NULL | | 4 | AP1311 | NULL | | 5 | AS8751 | 4 | | 6 | SU9458 | NULL | +----+--------+--------+

13. Spójność dziedzinowa - CHECK • CHECK zapewnia ochronę przed wstawieniem danych nie spełniających określonego warunku CREATE TABLE user(     id auto_increment PRIMARY KEY,     login VARCHAR(16),     password VARCHAR(20), CONSTRAINTCHECK(LENGTH(password) BETWEEN 8 AND 16) ) CREATE TABLE Person( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes'))

14. Dane przykładowe do operacji JOIN • Rozpatrzmy dwa zbiory danych: encje STUDENT i JĘZYK create table pracownik ( id int auto_increment primary key, imie char(10), nazwisko char(30), placa int ); create table jezyk ( id int auto_increment primary key, nazwa char(15) ); insert into pracownik values (1,'Jan','Nowak',3400); insert into pracownik values (2,'Ewa','Malina',2100); insert into pracownik values (3,'Iza','Trus',4000); insert into jezyk values (11,'polski'); insert into jezyk values (12,'angielski'); insert into jezyk values (13,'niemiecki'); (w tej chwili nie ma pomiędzy naszymi encjami relacji)

15. Iloczyn kartezjański • Iloczyn kartezjański • Wersja algebry relacji: Iloczynem kartezjańskim zbiorów A i B nazywamy zbiór wszystkich par (a,b), takich, że aA bB • Wersja bazodanowa: iloczyn kartezjański dwóch encji A i B to zbiór wszystkich par (a,b), takich, że a jest elementem encji A, zaś b jest elementem encji B • Iloczyn kartezjański zawiera KAŻDĄ możliwą parę (zbiór wszystkich par) mysql> select * from pracownik, jezyk; +----+------+----------+-------+----+-----------+ | id | imie | nazwisko | placa | id | nazwa | +----+------+----------+-------+----+-----------+ | 1 | Jan | Nowak | 3400 | 11 | polski | | 2 | Ewa | Malina | 2100 | 11 | polski | | 3 | Iza | Trus | 4000 | 11 | polski | | 1 | Jan | Nowak | 3400 | 12 | angielski | | 2 | Ewa | Malina | 2100 | 12 | angielski | | 3 | Iza | Trus | 4000 | 12 | angielski | | 1 | Jan | Nowak | 3400 | 13 | niemiecki | | 2 | Ewa | Malina | 2100 | 13 | niemiecki | | 3 | Iza | Trus | 4000 | 13 | niemiecki | +----+------+----------+-------+----+-----------+

16. Iloczyn kartezjański a relacja • Relację tworzą tylko te pary, które mają określoną właściwość (spełniają określony warunek). • Innymi słowy: relacja to jeden z podzbiorów iloczynu kartezjańskiego • Istnieje DUŻO możliwych relacji na dwóch encjach 1 1 11 11 2 2 12 12 3 3 13 13 Relacja A (1-do-1): jaki język obowiązuje w kraju zamieszkania pracownika Relacja B (1-do-): jakie są języki ojczyste pracowników 1 1 11 11 2 2 12 12 3 3 13 13 Relacja D (-do-): jakimi językami mówią pracownicy Relacja C (1-do-1): jakiego języka uczą się pracownicy

17. Relacje zaimplementowane w bazie danych • Implementacja relacji wymaga dodania kolumny (zakładamy relację 1-do-) alter table pracownik add column id_jez int; • Oczywiście istnieje wiele możliwych relacji 1-do- na tych encjach: 1 1 11 11 2 2 12 12 3 3 13 13 Relacja A (1-do-): jakie są języki ojczyste pracowników Relacja B (1-do-): główny język projektów naszych pracowników

18. Wykorzystanie operatora JOIN • Operatory JOIN (binarne, czyli na dwóch encjach) dzielimy na: • operatory INNER JOIN (odnoszą się tylko do elementów będących w relacji): • EQUI JOIN (klasyczny INNER, wymaga równości pomiędzy kolumnami) • NATURAL JOIN (wymaga równości, budowany w oparciu o nazwy kolumn) • operatory OUTER JOIN (dopuszczają elementy niebędące w relacji) • LEFT OUTER (wszystkie elementy z encji po lewej stronie operatora) • RIGHT OUTER (wszystkie elementy z encji po prawej stronie operatora) • FULL OUTER (złożenie LEFT i RIGHT) • NATURAL OUTER JOIN (OUTER budowany w oparciu o nazwy kolumn) • operator THETA JOIN (opiera się na warunku innym niż równość; rzadki) • operator ANTI JOIN (opiera się na warunku różności; rzadki) • operator CROSS JOIN (pełny iloczyn kartezjański) • Operator JOIN może występować także w wersji unarnej: • Operator SELF JOIN (tabela joinowana sama z sobą; raczej rzadki) Najważniejszą rolę pełnią INNER JOIN oraz OUTER JOIN (LEFT i RIGHT)

19. Dane do analizy • Wprowadzamy dane relacji update pracownik set id_jez=11 where id=1; update pracownik set id_jez=11 where id=2; update pracownik set id_jez=12 where id=3; 1 11 2 12 3 13 Relacja (1-do-): główny język projektów naszych pracowników mysql> select * from jezyk; +----+-----------+ | id | nazwa | +----+-----------+ | 11 | polski | | 12 | angielski | | 13 | niemiecki | +----+-----------+ 3 rows in set (0.01 sec) mysql> select * from pracownik; +----+------+----------+-------+--------+ | id | imie | nazwisko | placa | id_jez | +----+------+----------+-------+--------+ | 1 | Jan | Nowak | 3400 | 11 | | 2 | Ewa | Malina | 2100 | 11 | | 3 | Iza | Trus | 4000 | 12 | +----+------+----------+-------+--------+ 3 rows in set (0.00 sec)

20. INNER JOIN 1 11 2 12 3 13 • INNER JOIN: • wymaga klauzuli ON (opisującej kolumny używane w warunku złączenia) • warunek zawsze zbudowany jest na równości (stąd nazwa: EQUI JOIN) • zwraca tylko te pary, które należą do relacji (WAŻNE!) mysql> SELECT * from pracownik INNER JOIN jezyk ON(pracownik.id_jez=jezyk.id); +----+------+----------+-------+--------+----+-----------+ | id | imie | nazwisko | placa | id_jez | id | nazwa | +----+------+----------+-------+--------+----+-----------+ | 1 | Jan | Nowak | 3400 | 11 | 11 | polski | | 2 | Ewa | Malina | 2100 | 11 | 11 | polski | | 3 | Iza | Trus | 4000 | 12 | 12 | angielski | +----+------+----------+-------+--------+----+-----------+ • równość id_jez=id występuje tylko 3 przypadkach • operator INNER JOIN zwraca podzbiór iloczynu kartezjańskiego • element, który nie znajduje się w relacji (nie wychodzi z niego żaden łuk) NIE ZOSTANIE zawarty w wyniku INNER JOIN

21. OUTER JOIN 1 11 2 12 3 13 • OUTER JOIN: • wymaga klauzuli ON (opisującej kolumny używane w warunku złączenia) • warunek równość LUB brak elementu po "drugiej" stronie • zwraca te pary, które należą do relacji ORAZ pary (element encji-NULL)(WAŻNE!) SELECT * from jezyk LEFT OUTER JOIN pracownik ON (jezyk.id=pracownik.id_jez); +----+-----------+------+------+----------+-------+--------+ | id | nazwa | id | imie | nazwisko | placa | id_jez | +----+-----------+------+------+----------+-------+--------+ | 11 | polski | 1 | Jan | Nowak | 3400 | 11 | | 11 | polski | 2 | Ewa | Malina | 2100 | 11 | | 12 | angielski | 3 | Iza | Trus | 4000 | 12 | | 13 | niemiecki | NULL | NULL | NULL | NULL | NULL | +----+-----------+------+------+----------+-------+--------+ • warunek (id=id_jez) lub (jezyk.id nie jest elementem relacji) występuje w 4 przypadkach • operator OUTER JOIN zwraca podzbiór ilocz. kart. ORAZ pary zbudowane "sztucznie" z elementu NULL • element, który nie znajduje się w relacji (nie wychodzi z niego żaden łuk) ZOSTANIE zawarty w wyniku OUTER JOIN (jeśli jest po "odpowiedniej stronie")

22. LEFT vs RIGHT OUTER JOIN 1 1 1 1 11 11 11 11 2 2 2 2 12 12 12 12 3 3 3 3 13 13 13 13 4 4 4 4 LEFT OUTER JOIN select * from ksiazka LEFT OUTER JOIN autor on (ksiazka.id_aut=autor.id); KSIĄŻKA AUTOR +----+--------+--------+------+----------+ | id | tytul | id_aut | id | nazwisko | +----+--------+--------+------+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | | 4 | Dziady | NULL | NULL | NULL | +----+--------+--------+------+----------+ KSIĄŻKA AUTOR NULL select * from ksiazka RIGHT OUTER JOIN autor on (ksiazka.id_aut=autor.id); RIGHT OUTER JOIN Autorzy są "twórcami" książek KSIĄŻKA AUTOR +------+--------+--------+----+----------+ | id | tytul | id_aut | id | nazwisko | +------+--------+--------+----+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | | NULL | NULL | NULL | 13 | Reymont | +------+--------+--------+----+----------+ NULL

23. LEFT, RIGHT i FULL OUTER JOIN 1 1 1 11 11 11 2 2 2 12 12 12 3 3 3 13 13 13 4 4 4 select * from ksiazka RIGHT OUTER JOIN autor on (ksiazka.id_aut=autor.id); select * from ksiazka FULL OUTER JOIN autor on (ksiazka.id_aut=autor.id); +------+--------+--------+----+----------+ | id | tytul | id_aut | id | nazwisko | +------+--------+--------+----+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | | NULL | NULL | NULL | 13 | Reymont | +------+--------+--------+----+----------+ +----+--------+--------+------+----------+ | id | tytul | id_aut | id | nazwisko | +----+--------+--------+------+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | | 4 | Dziady | NULL | NULL | NULL | |NULL| NULL | NULL | 13 | Reymont | +----+--------+--------+------+----------+ select * from ksiazka LEFT OUTER JOIN autor on (ksiazka.id_aut=autor.id); LEFT OUTER RIGHT OUTER FULL OUTER +----+--------+--------+------+----------+ | id | tytul | id_aut | id | nazwisko | +----+--------+--------+------+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | | 4 | Dziady | NULL | NULL | NULL | +----+--------+--------+------+----------+ NULL NULL NULL NULL

24. OUTER JOIN vs INNER JOIN 1 1 1 1 11 11 11 11 2 2 2 2 12 12 12 12 3 3 3 3 13 13 13 13 4 4 4 4 INNER JOIN INNER JOIN +----+--------+--------+------+----------+ | id | tytul | id_aut | id | nazwisko | +----+--------+--------+------+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | +----+--------+--------+------+----------+ RIGHT OUTER FULL OUTER +------+--------+--------+----+----------+ | id | tytul | id_aut | id | nazwisko | +------+--------+--------+----+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | | NULL | NULL | NULL | 13 | Reymont | +------+--------+--------+----+----------+ +----+--------+--------+------+----------+ | id | tytul | id_aut | id | nazwisko | +----+--------+--------+------+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | | 4 | Dziady | NULL | NULL | NULL | |NULL| NULL | NULL | 13 | Reymont | +----+--------+--------+------+----------+ LEFT OUTER +----+--------+--------+------+----------+ | id | tytul | id_aut | id | nazwisko | +----+--------+--------+------+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | | 4 | Dziady | NULL | NULL | NULL | +----+--------+--------+------+----------+ LEFT OUTER RIGHT OUTER FULL OUTER NULL NULL NULL NULL

25. NATURAL JOIN • NATURAL JOIN jest przykładem JOINA, w którym: • warunek ON nie jest podawany w zapytaniu, • jest automatycznie generowany na podstawie NAZW kolumn • jeśli używamy klauzuli USING, to specyfikujemy kolumnę (jeśli nie – brane są pod uwagę WSZYSTKIE pary o jednakowych nazwach) • istnieje NATURAL JOIN (inner) oraz NATURAL [LEFT|RIGHT|FULL] OUTER select * from pracownikNATURAL JOIN dzial USING (ID_dzial); • NATURAL JOIN wymaga odpowiedniego nazywania kolumn • (jest to potencjalnie niebezpieczne dla nieuważnego programisty) Create table pracownik ( id int auto_increment primary key, nazwisko char(30), ID_dzial int ); Create table dzial ( ID_dzial int auto_increment primary key, nazwa char(30) );

26. CROSS JOIN • CROSS JOIN zwraca iloczyn kartezjański select * from pracownik CROSS JOIN jezyk +----+------+----------+-------+--------+----+-----------+ | id | imie | nazwisko | placa | id_jez | id | nazwa | +----+------+----------+-------+--------+----+-----------+ | 1 | Jan | Nowak | 3400 | 11 | 11 | polski | | 2 | Ewa | Malina | 2100 | 11 | 11 | polski | | 3 | Iza | Trus | 4000 | 12 | 11 | polski | | 1 | Jan | Nowak | 3400 | 11 | 12 | angielski | | 2 | Ewa | Malina | 2100 | 11 | 12 | angielski | | 3 | Iza | Trus | 4000 | 12 | 12 | angielski | | 1 | Jan | Nowak | 3400 | 11 | 13 | niemiecki | | 2 | Ewa | Malina | 2100 | 11 | 13 | niemiecki | | 3 | Iza | Trus | 4000 | 12 | 13 | niemiecki | +----+------+----------+-------+--------+----+-----------+ select * from pracownik, jezyk

27. GROUP BY • Rozważmy następującą bazę: create table paragon ( id int auto_increment primary key, numer char(10), wartosc numeric(5,2), data_zakupu date, id_kli int ); create table klient ( id int auto_increment primary key, nazwa char(15) );

28. GROUP BY • Dotychczas tworzyliśmy po prostu tablicę wynikową za pomocą JOIN: select nazwa, numer, wartosc from klient left outer join paragon on (klient.id=paragon.id_kli); +-----------------+-------+---------+ | nazwa | numer | wartosc | +-----------------+-------+---------+ | Alfa sp. z o.o. | P001 | 110.2 | | Alfa sp. z o.o. | P002 | 20.25 | | Alfa sp. z o.o. | P003 | 311.5 | | Alfa sp. z o.o. | P008 | 45 | | Beta SA | P004 | 100.25 | | Beta SA | P005 | 70 | | Beta SA | P006 | 19.2 | | Beta SA | P007 | 30 | | Gamma Inc. | NULL | NULL | +-----------------+-------+---------+ • Teraz chcemy przeprowadzić operacje na grupach danych

29. GROUP BY • GROUP BY pozwala na zastosowanie SUM dogrup(a nie całości) select nazwa, wartosc from klient left outer join paragon on (klient.id=paragon.id_kli); +-----------------+---------+ | nazwa | wartosc | +-----------------+---------+ | Alfa sp. z o.o. | 110.2 | | Alfa sp. z o.o. | 20.25 | | Alfa sp. z o.o. | 311.5 | | Alfa sp. z o.o. | 45 | | Beta SA | 100.25 | | Beta SA | 70 | | Beta SA | 19.2 | | Beta SA | 30 | | Gamma Inc. | NULL | +-----------------+---------+ select klient.id, nazwa, sum(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli) GROUP BY klient.id; +----+-----------------+--------------+ | id | nazwa | sum(wartosc) | +----+-----------------+--------------+ | 1 | Alfa sp. z o.o. | 486.95 | | 2 | Beta SA | 219.45 | | 3 | Gamma Inc. | NULL | +----+-----------------+--------------+ select sum(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli); +--------------+ | sum(wartosc) | +--------------+ | 706.40 | +--------------+

30. GROUP • Klauzuli GROUP BY używamy ze wszystkimi funkcjami, które mają działać na grupach danych i wyliczać dla każdej grupy reprezentującą ją wartość • Wyliczoną wartość nazywamy agregatem, a funkcje – funkcjami agregującymi • Funkcje agregujące to m.in. COUNT, MAX, MIN, SUM i AVG # wylicz wartość zakupów # dla każdej z firm select nazwa, sum(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli) group by klient.id; # wylicz średni zakup # dla każdej z firm select nazwa, avg(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli) group by klient.id; # jaki jest największy zakup # każdej z firm select nazwa, max(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli) group by klient.id; # ile zakupów zrobiła # każda z firm select nazwa, count(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli) group by klient.id;

31. HAVING • Klauzula HAVING pozwala wyfiltrować krotki, dla których agregat spełnia określony warunek (agregat – czyli WARTOŚĆ REPREZENTUJĄCA KAŻDĄ Z GRUP) select nazwa, sum(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli) group by klient.id HAVING sum(wartosc)>300 +-----------------+--------------+ | nazwa | sum(wartosc) | +-----------------+--------------+ | Alfa sp. z o.o. | 486.95 | +-----------------+--------------+ +-----------------+---------+ | nazwa | wartosc | +-----------------+---------+ | Alfa sp. z o.o. | 110.2 | | Alfa sp. z o.o. | 20.25 | | Alfa sp. z o.o. | 311.5 | | Alfa sp. z o.o. | 45 | | Beta SA | 100.25 | | Beta SA | 70 | | Beta SA | 19.2 | | Beta SA | 30 | | Gamma Inc. | NULL | +-----------------+---------+ HAVING +-----------------+--------------+ | nazwa | sum(wartosc) | +-----------------+--------------+ | Alfa sp. z o.o. | 486.95 | | Beta SA | 219.45 | | Gamma Inc. | NULL | +-----------------+--------------+ GROUP + SUM

32. HAVING vs WHERE • WHERE jest filtrem dla danych PRZED agregacją, • HAVING jest filtrem dla agregatów (wyników PO agregacji) select nazwa, sum(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli) WHERE wartosc>50 group by klient.id HAVING sum(wartosc)>200; +-----------------+---------+ | Alfa sp. z o.o. | 421.70 | +-----------------+---------+ HAVING +-----------------+---------+ | nazwa | wartosc | +-----------------+---------+ | Alfa sp. z o.o. | 421.70 | | Beta SA | 170.25 | +-----------------+---------+ +-----------------+---------+ | nazwa | wartosc | +-----------------+---------+ | Alfa sp. z o.o. | 110.2 | | Alfa sp. z o.o. | 20.25 | | Alfa sp. z o.o. | 311.5 | | Alfa sp. z o.o. | 45 | | Beta SA | 100.25 | | Beta SA | 70 | | Beta SA | 19.2 | | Beta SA | 30 | | Gamma Inc. | NULL | +-----------------+---------+ GROUP+SUM +-----------------+---------+ | nazwa | wartosc | +-----------------+---------+ | Alfa sp. z o.o. | 110.2 | | Alfa sp. z o.o. | 311.5 | | Beta SA | 100.25 | | Beta SA | 70 | +-----------------+---------+ WHERE

33. JOIN z użyciem aliasów • Użycie aliasów pozwala za skrócenie długich zapytań JOIN (ale czasem może zmniejszyć czytelność polecenia) select nazwa, sum(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli) group by klient.id HAVING sum(wartosc)>300 select nazwa, sum(wartosc) from klient as k left outer join paragon as p on (k.id=p.id_kli) group by k.id HAVING sum(wartosc)>300 select nazwa, sum(wartosc) as ile from klient as k left outer join paragon as p on (k.id=p.id_kli) group by k.id HAVING ile>300

34. JOIN przez kilka tabel REDUNDANCJA Ta kolumna jest niepotrzebna, dlatego należy ją usunąć!

35. JOIN przez kilka tabel • Gdzie sprzedajemy (ile faktur w poszczególnych miastach) select miasto.nazwa, count(faktura.id) from miasto left outer join kupujacy on (miasto.id=kupujacy.id_mia) left outer join faktura on (kupujacy.id=faktura.id_kup) group by miasto.id +----------+-------------------+ | nazwa | count(faktura.id) | +----------+-------------------+ | Poznan | 4 | | Krakow | 3 | | Gdansk | 2 | | Warszawa | 4 | | Szczecin | 2 | | Tczew | 2 | | Sanok | 2 | | Radom | 0 | +----------+-------------------+

36. JOIN przez kilka tabel • Jakie są wartości poszczególnych faktur? select faktura.numer, sum(linia.ilosc*towar.cena) as wartosc from faktura left outer join linia on (faktura.id=linia.id_fak) left outer join towar on (linia.id_tow=towar.id) group by faktura.id +-----------+----------+ | numer | wartosc | +-----------+----------+ | FV3434531 | 235.3960 | | FV3497971 | 160.7850 | | FV3543322 | 140.4060 | (...) | FV3908888 | 14.0000 | | FV1138881 | 384.0000 | | FV2333531 | 368.0000 | | FV3911188 | 42.5300 | +-----------+----------+

37. JOIN przez kilka tabel • Ile faktur wystawili sprzedawcy? select sprzedawca.nazwisko, count(faktura.numer) as ile from sprzedawca left outer join faktura on (sprzedawca.id=faktura.id_spr) group by sprzedawca.id +----------+-----+ | nazwisko | ile | +----------+-----+ | Nowak | 4 | | Kuna | 2 | | Trus | 2 | | Pokora | 2 | | Gisz | 4 | | Wist | 1 | | Kunera | 1 | | Pokora | 1 | +----------+-----+

38. Postaci normalne O dobrym i złym projektowaniu baz danych

39. Dobry diagram baz danych powinien: • umożliwiać szybkie wyszukiwanie danych • zapewniać łatwą modyfikowalność • minimalizować szanse na wprowadzenie niespójności • zapewniać brak redundancji

40. Problemy: • jak wyszukać pracowników z Gdańska: • SELECT * from PRACOWNIK WHERE adres like '%gdańsk%' • jak wyszukać pracowników z Wydziału A • SELECT * from PRACOWNIK WHERE dzial like '%A%' • Powyższy diagram nie jest BŁĘDNY – jest MARNEJ JAKOŚCI

41. Jak dobrze projektować ERD? • Jak oceniać jakość diagramu ERD? • POSTAĆ NORMALNA: to zbiór własności, którymi muszą się charakteryzować dane, aby mogły być uznane za znormalizowane do danej postaci. Mówimy: te dane są (lub nie) w pierwszej (drugiej, trzeciej...) postaci normalnej.

42. 1NF • Pierwsza postać normalna (1NF) wg Ch. Date: • Zbiór danych jest w 1NF wtedy, gdy: • nie ma powtarzających się krotek • każdy atrybut jest jednowartościowy (czyli, że w danym polu można zapisać tylko jedną wartość z dopuszczalnego zbioru) • nie ma wartości pustych (NULL) • Wersja dla teoretyków: • „zbiór danych jest w pierwszej postaci normalnej wtedy i tylko wtedy, gdy istnieje taka relacja, z którą zbiór ten jest izomorficzny

43. 1NF • 1NF: nie ma powtarzających się krotek: • zapewniamy przez UNIQUE albo przez kolumnę ID: • 1NF: jednowartościowość atrybutów i NULL: ?

44. postulat Codda do 1NF: • dane powinny być "atomowe" (niepodzielne) • 1 NF w wersji Codda: • rekordy (krotki) są rozróżnialne • atrybuty są atomowe • UWAGA: czy DATE albo CHAR(50) jest atomem?

45. 2NF • 2NF odnosi się do wykluczenia redundancji ? • W jaki sposób opisać to zjawisko formalnie? • klucz kandydujący - jest to każdy atrybut (lub najmniejsza z możliwych grupa atrybutów), których wartość jest unikalna w danej tabeli • klucz główny – arbitralnie wybrany klucz kandydujący • Zależność funkcyjna pomiędzy dwoma atrybutami (kolumnami tabeli) A i B oznacza, że dla każdej wartości atrybutu A występuje zawsze jedna wartość B

46. Zależność funkcyjna atrybutów encji • Zależność funkcyjna pomiędzy dwoma atrybutami (kolumnami tabeli) A i B oznacza, że dla każdej wartości atrybutu A występuje zawsze jedna wartość B • B1 = f(A1) ale B2 nie jest f(A2)

47. 2 NF • Jakie są klucze kandydujące tabeli: • Tabela jest w 2NF wtedy i tylko wtedy gdy: 1. jest w 1NF 2. żaden z atrybutów, które nie wchodzą w skład klucza kandydującego nie jest funkcjonalnie zależny od części tego klucza (inaczej: żaden z atrybutów nie jest w częściowej zależności funkcyjnej od klucza głównego) • Tabela jest w 2NF wtedy i tylko wtedy gdy (mniej formalnie): 1. jest w 1NF 2. jeżeli weźmiemy dowolny klucz kandydujący i dowolny atrybut nie będący jego częścią to atrybut ten nie może być funkcją części klucza kandydującego.

48. 2 NF • Sprawdzenie: • klucz kandydujący: (imię, nazwisko, język) • atrybut spoza klucza: miasto • klucz kandydujący vs atrybut: • pełna zależność funkcyjna – ZAWSZE (dlaczego?) • częściowa zależność funkcyjna – TAK MIASTO=f(imie, nazwisko) • WNIOSEK: relacja nie jest w 2NF

49. 1NF  2 NF • Normalizacja do 2NF powoduje powstanie dodatkowych tablic

50. 3NF • Trzecia postać normalna jest "silniejszą wersją" 2NF • Tabela jest w 3NF wtedy i tylko wtedy gdy: 1. jest w 2NF 2. nie istnieją przechodnie zależności funkcyjne • Sprawdzenie częściowych zależności funkcyjnych 2NF: • klucz kandydujący: Tor-Rok, atrybuty zewnętrzne: nazwisko, kraj • nazwisko nie jest f(Rok), nazwisko nie jest f(Tor), • kraj nie jest f(Rok), kraj nie jest f(Tor) 2NF