Transcript Slajd 1

Relacyjne Bazy Danych
Wykład 02/03
Wojciech St. Mościbrodzki
[email protected]
Encje - przypomnienie

Encja i encja-typ





każda unikalna (identyfikowalna) rzecz
posiada atrybuty (cechy) przyjmujące określone wartości
zbiór wartości atrybutu, nazywamy dziedziną
encja (ściślej: encja-typ) to zbiór wszystkich encji identyfikowalnych przez ten
sam zbiór atrybutów
Przykłady:






Jan Kowalski = encja
cechy (atrybuty) Jana Kowalskiego = {wzrost, kolor oczu, imię, nazwisko}
wartości atrybutów Jana Kowalskiego = {174, zielony, Jan, Kowalski}
student (np. Jan Kowalski) = encja (ściślej: encja-typ)
atrybut encji student: kolor oczu
wartości atrybutu kolor oczu (dziedzina atrybutu) = {zielony, piwny, niebieski}
Encja - identyfikowalność

klucz kandydujący



nieformalnie: atrybuty, których wartości identyfikują encje
formalnie: to każdy zbiór atrybutów, który posiada dwie własności:
• wartości atrybutów są różne dla każdej encji
• usunięcie dowolnego atrybutu ze zbioru, sprawia, że warunek powyżej nie
jest spełniony
klucz główny

wybrany (arbitralnie) jeden z kluczy kandydujących
Klucze kandydujące: {imię}, {semestr, miasto}, {semestr, wiek} itd.
Diagram Związków Encji (ERD)

ERD służy do modelowania danych i związków pomiędzy nimi
ENCJA
atrybut

wyliczony
klucz zew.
klucz
Formalnie – na diagramach ER NIE OZNACZAMY TYPÓW DANYCH
encja
klucz główny
atrybut
Diagram Związków Encji (ERD) - RELACJE

ERD służy do modelowania danych i związków pomiędzy nimi
różne notacje
(różni autorzy)
struktura pól
Klasyczna Krucza Stopka
krotność relacji
Notacja Chena
ENCJA2
ENCJA 1
1
1
ENCJA2
ENCJA2
ENCJA 1
1
0
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
N
1
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
N
N
ENCJA2
ENCJA 1
ENCJA 1
0
Notacja J. Martina
Notacja Barkera
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
Diagram Związków Encji (ERD) – przypadki szczególne

Encja słaba

nagłówek faktury
encja słaba to taka encja, która nie jest
rozróżnialna (identyfikowalna) przez własne
atrybuty (wymaga klucza zewnętrznego)
numer
data
linia faktury
numer
towar
ilość

ilość
Asocjacja (encja asocjacyjna, relacja wiele-do-wiele)


asocjacja to relacja wiele-do-wiele (posiada cechy encji, może mieć atrybuty)
w bazie danych odpowiada jej osobna tabela, zwana tabelą pośredniczącą)
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
Klasyfikacja relacji

Kryterium krotności (dla relacji
binarnych):




jeden-do-jeden
jeden-do-wiele
wiele-do-wiele
Kryterium "-narności"




unarne
binarne
trynarne
większe
Model logiczny danych (ERD) a model fizyczny

Model logiczny:




zawiera informacje o encjach i relacjach
nie zawiera informacji o typach danych (np. na poziomie SQL)
jest niezależny od bazy danych
Model fizyczny:



zawiera informacje o encjach, relacjach i typach
określa typy danych
jest związany z implementacją
Relacje a tabele
relacja 1-do-1
relacja 1-do-
Relacja jeden-do-wiele – realizacja w bazie danych
Podstawy SQL
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)
Programowanie w SQL

SQL nie jest językiem programowania – jest językiem zapytań
'70s
'87
SEQEL
System R
(IBM)
RDMS
(MIT)
quel
Ingres
(Berkley)
SQL
Oracle
(Oracle)
SQL-86 (standard ANSI): w kierunku wieloplatformowości
SQL-92: kursory, ALTER, DATA, TIMESTAMP, VARCHAR...
'90s
SQL-1999 (SQL3): triggery, BOOLEAN, control-flow (IF...) ...
SQL-2003: funkcje okien danych, XML, MERGE
SQL-2006: rozbudowa XML, funkcje WWW
'00s
SQL-2008: INSTEAD, TRUNCATE...
SQL jako język zapytań

Typowy podział poleceń (zapytań) SQL:




Inny podział:




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, …
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)
Programowanie z użyciem SQL

Metody na "uprogramowienie" SQL:




Embedded SQL (SQL osadzony):
• konieczny odpowiedni kompilator
• wartości przenoszone przez uwspólnione zmienne (widziane w SQL i w
języku programowania)
Modular SQL (moduły SQL dla języków programowania)
• konieczne są odpowiednie moduły dołączane
• przekazywanie wartości zależne od języka
Dynamic SQL (część "programistyczna" w innym języku – np. PHP)
• wymaga odpowiednich konstrukcji językowych
Programming Extensions for SQL (rozbudowa składni SQL – poza standardem
ANSI)
• wymaga serwera SQL rozumiejące "rozbudowaną" składnię
• np. PL/SQL (Oracle), PSM (MySQL), PL PSM (PostgreSQL)
Podstawy SQL i pracy z klientem MySQL

Polecenia klienta MySQL:


są specyficzne dla dialektu MySQL - NIE NALEŻĄ do standardu SQL
w rzeczywistości są poleceniami, które klient "tłumaczy" na zwykłe zapytania
SELECT i kieruje do systemowej bazy danych
mysql> show tables;
+------------------+
| Tables_in_wojmos |
+------------------+
| czlowiek
|
| faktura
|
+------------------+
mysql> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| gcs
|
| kardio
|
| mysql
|
| wojmos
|
+--------------------+
mysql> explain czlowiek;
+----------+----------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| imie
| char(30) | YES |
| NULL
|
|
| nazwisko | char(50) | YES |
| NULL
|
|
+----------+----------+------+-----+---------+-------+
Podstawowe typy zapytań

Zapytania wyszukujące dane:


Zapytania modyfikujące dane:




INSERT
DELETE
UPDATE
Zapytania modyfikujące bazę danych i jej struktury:





SELECT
CREATE
DROP
ALTER
GRANT / REVOKE
Pseudozapytania MySQL:

SHOW, EXPLAIN, DESCRIBE
CREATE DATABASE

Polecenie CREATE służy do tworzenia baz:
CREATE {DATABASE} [IF NOT EXISTS] db_name[create_specification]
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name

W dokumentacji obowiązują następujące konwencje:






{xxx} – xxx musi wystąpić
xxx | yyy – musi wystąpić xxx ALBO yyy
[xxx] – xxx może wystąpić (albo i nie)
zzz: - wyjaśnienie symbolu zzz
[ xxx | yyy ] – może wystąpić xxx ALBO yyy albo nic
{ xxx | yyy } – musi wystąpić xxx ALBO yyy
CREATE TABLE

Polecenie CREATE TABLE służy do tworzenia tablic:
CREATE TABLE {tablica} [IF NOT EXISTS] (specyfikacja) [opcje]
specyfikacja: nazwa_kolumny, definicja_kolumny
definicja_kolumny: typ [NULL|NOT NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
[reference_definition]
CREATE TABLE player (
ID int auto_increment primary key,
name char(50) NOT NULL,
dex int,
str int,
cha int
);
Podstawowe typy danych SQL

Typy danych języka SQL:




numeryczne:
• całkowite: INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT
• rzeczywiste: FLOAT(M,D), DOUBLE(M,D), DECIMAL(M,D)
• logiczne: BOOL,
znakowe:
• łańcuchy krótkie: CHAR, VARCHAR
• obiekty tekstowe: TEXT
daty i czasu:
• czasowe: DATE, TIME, DATETIME, YEAR
specjalne:
• znacznik czasowy: TIMESTAMP
• obiekt binarny: BLOB
• wyliczeniowy: ENUM
Zapytania typu SELECT

Typowy SELECT składa się z:




wskazania kolumn
wskazania tabeli FROM
filtru rekordów WHERE warunek
żądania sortowania ORDER BY
select imie from czlowiek;
+-------+
| imie |
+-------+
| Roman |
| Roman |
+-------+
mysql> select distinct imie from
czlowiek where imie like 'jan%';
+--------+
| imie
|
+--------+
| Jan
|
| Janusz |
+--------+
mysql> select * from czlowiek;
+-------+-------------+
| imie | nazwisko
|
+-------+-------------+
| Roman | Pipokucki
|
| Roman | Popopizacki |
+-------+-------------+
mysql> select * from czlowiek order by imie DESC, nazwisko ASC;
+-------+-----------+
| imie | nazwisko |
+-------+-----------+
| Roman | Kukurocki |
| Roman | Kupikucki |
+-------+-----------+
Zapytania typu INSERT

Zapytania typu INSERT dodają do tablicy dane
INSERT
[LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
insert into player (name, dex, str, cha) values ('Raven',18,8,10);
Query OK, 1 row affected (0.02 sec)
insert into czlowiek values ('Jan','Nowak');
Query OK, 1 row affected (0.02 sec)

Specjalna forma INSERT (SELECT AS INSERT):
insert into player (name) select imie from czlowiek;
Zapytania typu UPDATE

UPDATE służy do aktualizacji (modyfikacji) rekordów


UWAGA1: WHERE nie jest konieczne do działania UPDATE
UWAGA2: brak WHERE może przynieść opłakane skutki
UPDATE table
SET
col_name1={expr1|DEFAULT}
[, col_name2={expr2|DEFAULT}]
...
[WHERE where_condition]
update player set dex=20 where name like 'Raven';
update pracownik set placa=placa+200 where name like 'Kowalski';
update pracownik set placa=placa+200 where ID=23445;
update pracownik set placa=placa+200;
Polecenie ALTER służy do zarządzania strukturą

Polecenie ALTER służy do zarządzania strukturą:
ALTER TABLE tbl_name alter_specification [, alter_specification] ...
alter_specification:
ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
| ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type]
(index_col_name,...) [index_type]
| ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)
reference_definition
| CHANGE [COLUMN] old_col_name new_col_name column_definition
| MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY}
| RENAME [TO] new_tbl_name
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
Złączenia tablic – operacje JOIN
JOIN – w pytaniach i odpowiedziach

Do czego służą operacje typu JOIN


Na jakich obiektach przeprowadza się JOIN


Do umożliwienia wykonywania operacji (np. SELECT) na danych
przechowywanych w wielu tabelach
Operacje JOIN są wykonywane na dwóch (lub więcej) tablicach (zwykle –
pozostających w pewnej relacji)
Jak wykonywane są "długie" operacje JOIN

Każdą wielotablicową operację JOIN można rozpatrywać jako łańcuch
"binarnych" (dwutablicowych) JOINów.
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)
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 |
+----+------+----------+-------+----+-----------+
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
2
12
3
12
13
Relacja B (1-do-):
jakie są języki ojczyste pracowników
1
11
2
12
3
2
3
13
Relacja A (1-do-1):
jaki język obowiązuje w kraju
zamieszkania pracownika
1
11
13
Relacja C (1-do-1):
jakiego języka uczą się pracownicy
11
2
12
3
13
Relacja D (-do-):
jakimi językami mówią pracownicy
Relacje zaimplementowane w bazie danych
Implementacja relacji wymaga dodania kolumny (zakładamy relację 1-do-)

alter table pracownik add column id_jez int;

1
Oczywiście istnieje wiele możliwych relacji 1-do- na tych encjach:
1
11
2
12
3
13
Relacja A (1-do-):
jakie są języki ojczyste pracowników
11
2
12
3
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 relacji
1
11
2
12
3
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;
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)
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);
+----+------+----------+-------+--------+----+-----------+
| 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 |
+----+------+----------+-------+--------+----+-----------+



1
2
3
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
11
12
13
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);
+----+-----------+------+------+----------+-------+--------+
| 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 |
+----+-----------+------+------+----------+-------+--------+



1
2
3
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")
11
12
13
LEFT vs RIGHT OUTER JOIN
LEFT OUTER JOIN
KSIĄŻKA
AUTOR
1
11
2
12
3
13
4
Autorzy są "twórcami" książek
KSIĄŻKA
AUTOR
1
11
2
12
3
13
4
NULL
RIGHT OUTER JOIN
KSIĄŻKA
1
AUTOR
11
2
12
3
13
4
NULL
select * from
ksiazka LEFT 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
|
| 4 | Dziady |
NULL | NULL | NULL
|
+----+--------+--------+------+----------+
select * from
ksiazka RIGHT 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 |
+------+--------+--------+----+----------+
LEFT, RIGHT i FULL OUTER JOIN
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);
+----+--------+--------+------+----------+
| 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
1
2
3
4
1
2
3
4
11
12
13
NULL
NULL
11
12
13
FULL OUTER
1
2
3
4
NULL
11
12
13
NULL
OUTER JOIN vs 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
|
+----+--------+--------+------+----------+
INNER JOIN
1
2
3
4
11
12
13
RIGHT 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 |
+------+--------+--------+----+----------+
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
|
+----+--------+--------+------+----------+
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 | 13
| Reymont |
+----+--------+--------+------+----------+
LEFT OUTER
RIGHT OUTER
1
2
3
4
1
2
3
4
11
12
13
NULL
NULL
11
12
13
FULL OUTER
1
2
3
4
NULL
11
12
13
NULL
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ń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
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

GROUP BY pozwala na zastosowanie SUM do grup (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 |
+--------------+
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;
HAVING

Klauzula HAVING pozwala wyfiltrować krotki, dla których agregat
spełnia określony warunek (agregat – czyli WARTOŚĆ
REPREZENTUJĄCA KAŻDĄ Z GRUP)
+-----------------+---------+
| 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 |
+-----------------+--------------+
GROUP + SUM
select nazwa, sum(wartosc) from
klient left outer join paragon
on (klient.id=paragon.id_kli)
group by klient.id
HAVING sum(wartosc)>300
HAVING
+-----------------+--------------+
| nazwa
| sum(wartosc) |
+-----------------+--------------+
| Alfa sp. z o.o. |
486.95 |
| Beta SA
|
219.45 |
| Gamma Inc.
|
NULL |
+-----------------+--------------+
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;
HAVING
+-----------------+---------+
| nazwa
| wartosc |
+-----------------+---------+
| Alfa sp. z o.o. | 421.70 |
| Beta SA
| 170.25 |
+-----------------+---------+
GROUP+SUM
WHERE
+-----------------+---------+
| 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 |
+-----------------+---------+
+-----------------+---------+
| Alfa sp. z o.o. | 421.70 |
+-----------------+---------+
+-----------------+---------+
| nazwa
| wartosc |
+-----------------+---------+
| Alfa sp. z o.o. |
110.2 |
| Alfa sp. z o.o. |
311.5 |
| Beta SA
| 100.25 |
| Beta SA
|
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ąć!
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 |
+----------+-------------------+
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 |
+-----------+----------+
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 |
+----------+-----+
Postaci normalne
O 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:


1 NF w wersji Codda:



dane powinny być "atomowe" (niepodzielne)
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 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.
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 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

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