Bazy Danych W03: Spójność relacji i JOINy Wojciech St. Mościbrodzki [email protected]

Bazy DanychW03: Spójność relacji i JOINy

Wojciech St. Moś[email protected]

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)

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)

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

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)

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)

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

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

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

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;

Tabela "główna" musi istnieć podczas tworzenia tabeli "potomnej":

Usuwanie tabel również wymaga odpowiedniej kolejności!


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 (ìd_dzi`) REFERENCES `dzial` (ìd`))

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 (ìd_dzi`) REFERENCES `dzial` (ìd`))


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 |+-----------+


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;

+----+--------+--------+| id | serial | id_pra |+----+--------+--------+| 1 | NP3451 | 1 || 2 | ED2221 | 2 || 3 | AP1111 | 3 || 4 | AP1311 | 3 || 5 | AS8751 | 4 || 6 | SU9458 | 3 |+----+--------+--------+

select * from telefon; delete from pracownik where id=3;

+----+--------+--------+| id | serial | id_pra |+----+--------+--------+| 1 | NP3451 | 1 || 2 | ED2221 | 2 || 3 | AP1111 | NULL || 4 | AP1311 | NULL || 5 | AS8751 | 4 || 6 | SU9458 | NULL |+----+--------+--------+

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), CONSTRAINT CHECK(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'))

Dane przykładowe do operacji JOIN

Rozpatrzmy dwa zbiory danych: encje STUDENT i JĘZYKcreate 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)

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 aA bB 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 |+----+------+----------+-------+----+-----------+

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

2

3

11

12

13

Relacja A (1-do-1): jaki język obowiązuje w kraju

zamieszkania pracownika

1

2

3

11

12

13

Relacja B (1-do-): jakie są języki ojczyste pracowników

1

2

3

11

12

13

Relacja D (-do-): jakimi językami mówią pracownicy

1

2

3

11

12

13

Relacja C (1-do-1): jakiego języka uczą się pracownicy

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

2

3

11

12

13

Relacja A (1-do-): jakie są języki ojczyste pracowników

1

2

3

11

12

13

Relacja B (1-do-): główny język projektów naszych pracowników

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)

Dane do analizy

Wprowadzamy dane relacjiupdate 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

2

3

11

12

13

Relacja (1-do-): główny język projektów naszych pracowników

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)

mysql> select * from jezyk;+----+-----------+| id | nazwa |+----+-----------+| 11 | polski || 12 | angielski || 13 | niemiecki |+----+-----------+3 rows in set (0.01 sec)

INNER JOIN

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);

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

1

2

3

11

12

13

+----+------+----------+-------+--------+----+-----------+| 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 |+----+------+----------+-------+--------+----+-----------+

OUTER JOIN

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);

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")

1

2

3

11

12

13

+----+-----------+------+------+----------+-------+--------+| 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 |+----+-----------+------+------+----------+-------+--------+

LEFT vs RIGHT OUTER JOIN

1

2

3

11

12

13

4

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 || 4 | Dziady | NULL | NULL | NULL |+----+--------+--------+------+----------+

+------+--------+--------+----+----------+| 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 |+------+--------+--------+----+----------+

select * from ksiazka RIGHT OUTER JOIN autor on (ksiazka.id_aut=autor.id);

select * from ksiazka LEFT OUTER JOIN autor on (ksiazka.id_aut=autor.id);

1

2

3

11

12

13

4KSIĄŻKA AUTOR

1

2

3

11

12

13

4

KSIĄŻKA AUTOR

1

2

3

11

12

13

4

LEFT OUTER JOIN

RIGHT OUTER JOIN

NULL

NULL

LEFT, RIGHT i FULL OUTER JOIN

+----+--------+--------+------+----------+| 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 RIGHT OUTER JOIN autor on (ksiazka.id_aut=autor.id);

select * from ksiazka FULL OUTER JOIN autor on (ksiazka.id_aut=autor.id);

123

111213

4

LEFT OUTER

NULL

+----+--------+--------+------+----------+| 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 |+----+--------+--------+------+----------+

select * from ksiazka LEFT OUTER JOIN autor on (ksiazka.id_aut=autor.id);

123

111213

4

123

111213

4

RIGHT OUTER FULL OUTER

NULL NULL NULL

OUTER JOIN vs INNER JOIN

+----+--------+--------+------+----------+| 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 |+----+--------+--------+------+----------+


123

111213

4

LEFT OUTER

NULL

+----+--------+--------+------+----------+| 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 |+----+--------+--------+------+----------+

123

111213

4

123

111213

4


NULL NULL NULL

+----+--------+--------+------+----------+| id | tytul | id_aut | id | nazwisko |+----+--------+--------+------+----------+| 1 | LOTR | 11 | 11 | Tolkien || 2 | Hobbit | 11 | 11 | Tolkien || 3 | Eden | 12 | 12 | Lem |+----+--------+--------+------+----------+

INNER JOIN

123

111213

4

INNER JOIN

LEFT OUTER


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 pracownik NATURAL 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));

CROSS JOIN

CROSS JOIN zwraca iloczyn kartezjańskiselect * from pracownik CROSS JOIN jezyk

select * from pracownik, 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 |+----+------+----------+-------+--------+----+-----------+

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));

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

GROUP BY

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 |+----+-----------------+--------------+

GROUP BY pozwala na zastosowanie SUM do grup (a nie całości)

select sum(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli);+--------------+| sum(wartosc) |+--------------+| 706.40 |+--------------+

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 firmselect 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 firmselect 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 firmselect 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 firmselect nazwa, count(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli) group by klient.id;

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 |+-----------------+---------+

+-----------------+--------------+| nazwa | sum(wartosc) |+-----------------+--------------+| Alfa sp. z o.o. | 486.95 || Beta SA | 219.45 || Gamma Inc. | NULL |+-----------------+--------------+

HAVINGG

RO

UP

+ SU

M

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;

+-----------------+---------+| nazwa | wartosc |+-----------------+---------+| Alfa sp. z o.o. | 110.2 || Alfa sp. z o.o. | 311.5 || Beta SA | 100.25 || Beta SA | 70 |+-----------------+---------+

WH

ERE

+-----------------+---------+| 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

HAVING

+-----------------+---------+| Alfa sp. z o.o. | 421.70 |+-----------------+---------+

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

JOIN przez kilka tabel

REDUNDANCJA

Ta kolumna jest niepotrzebna, dlatego należy ją usunąć!


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 |+----------+-------------------+


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 |+-----------+----------+


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 |+----------+-----+

Postaci normalneO dobrym i złym projektowaniu baz danych

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

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

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.

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

1NF

1NF: nie ma powtarzających się krotek:

zapewniamy przez UNIQUE albo przez kolumnę ID:

1NF: jednowartościowość atrybutów i NULL:

?

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?

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

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)

2 NF

Jakie są klucze kandydujące tabeli:

Tabela jest w 2NF wtedy i tylko wtedy gdy:1. jest w 1NF2. ż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 1NF2. 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.

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

1NF 2 NF

Normalizacja do 2NF powoduje powstanie dodatkowych tablic

3NF

Trzecia postać normalna jest "silniejszą wersją" 2NF Tabela jest w 3NF wtedy i tylko wtedy gdy:

1. jest w 2NF2. 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

3NF

Problem:

Zależność funkcyjna przechodnia: Kraj = f(Nazwisko)

UWAGA: To nie jest złamanie 2NF, bo ani kraj, ani nazwisko nie są częściami klucza kandydującego Tak wyglądać może problem wynikający z niespełnienia 3NF:

Postaci Normalne - podsumowanie

UWAGA: postaci normalne można "łamać" – np. świadomie godzić się na wprowadzenie

(potencjalnej) niespójności do bazy danych przykład: utworzenie tabeli przechowującej wartości zagregowane,

aktualizowanej po DELETE lub INSERT przykład: dodanie kolumny kod_miasta obok kolumny miasto

WNIOSKI: postaci normalne to formalny sposób oceny jakości diagramu stosujemy je po to, aby sprawdzić jakość naszych danych nie robimy tego tylko po to, aby robić – możliwe jest złamanie PN, byle

ŚWIADOME

Bazy Danych W03: Spójność relacji i JOINy Wojciech St. Mościbrodzki [email protected]

Documents

Transcript of Bazy Danych W03: Spójność relacji i JOINy Wojciech St. Mościbrodzki [email protected]