1 / 52

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

Bazy Danych W03: Spójność relacji i JOINy Wojciech St. Mościbrodzki wojmos@pjwstk.edu.pl. 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

kaz
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related