Bazy danych

Małgorzata Kr towska

Katedra Oprogramowania

e-mail:

[e-mail]

Wykład 5: Projektowanie relacyjnych

schematów baz danych. SQL - funkcje

grupuj ce

Bazy danych (studia dzienne)

2

Plan wykładu

• Proces dobrego projektowania relacyjnego schematu bazy

danych:

– szczegółowy opis problemów, które wynikaj przy tworzenu

schematu

– przedstawienie metody dekompozycji, która polega na podziale

schematu relacji (zbioru atrybutów) na dwa mniejsze schematy

– opis „postaci normalnej Boyce’a-Codda” (BCNF) czyli taki warunek

nało ony na schemat, dzi ki któremu mo na wyeliminowa jego

niedoskonało ci

– informacja o tym, w jaki sposób zapewni spełnienie warunków

BCNF przez dekompozycj schematów relacyjnych

• SQL cd

Bazy danych (studia dzienne)

3

Anomalie

• Anomalie - problemy, jakie powstaj , gdy próbujemy do

pojedynczej relacji wł czy zbyt wiele danych

– redundancja - dane niepotrzebnie powtarzaj si w kilku krotkach

– anomalie modyfikacji - sytuacje, w których warto zostaje

zmodyfikowana w jednej krotce, a w innej nie

– anomalie usuni - usuni cie krotki mo e powodowa usuni cie

wa nej informacji z bazy danych

Bazy danych (studia dzienne)

4

Dekompozycja relacji

• Dekompozycja relacji - sposób eliminowania wymienionych

anomalii przez podział atrybutów relacji R mi dzy dwa schematy

nowych relacji.

• Relacj R o schemacie {A

1

, A

2

,..., A

n

} dekomponujemy mi dzy

dwie relacji S i T o schematach odpowiednio {B

1

, B

2

,..., B

m

} i {C

1

,

C

2

,..., C

k

} według nast puj cych zasad:

– {A

1

, A

2

,..., A

n

} = {B

1

, B

2

,..., B

m

}

∪

{C

1

, C

2

,..., C

k

}

– Krotki relacji S powstaj przez rzutowanie wszystkich krotek relacji

R na zbiór atrybutów {B

1

, B

2

,..., B

m

}, tzn. z ka dej krotki t bie cej

instancji relacji R pobieramy warto ci atrybutów {B

1

, B

2

,..., B

m

} i

tworzymy w ten sposób krotk relacji S. Je eli z relacji R

otrzymamy kilka jednakowych krotek w relacji S, w S umieszczamy

tylko jedn kopi .

– W podobny sposób uzyskuje si krotki relacji T.

Bazy danych (studia dzienne)

5

Posta normalna Boyce’a-Codda

• Posta normalna Boyce’a-Codda (BCNF) - warunek, którego

spełnienie zapewnia, e w schemacie nie wyst puj omówione

wcze niej anomalie.

• Relacja R jest w postaci normalnej BCNF wtedy i tylko wtedy,

gdy dla ka dej nietrywialnej zale no ci A

1

, A

2

,..., A

n

→

B, zbiór

{A

1

, A

2

,..., A

n

} jest nadkluczem R

Bazy danych (studia dzienne)

6

Dekompozycja do postaci BCNF

• Je li proces dekompozycji b dziemy powtarza dostatecznie długo, to

ka da otrzymana relacja b dzie si składała z kolekcji podzbiorów

atrybutów, które:

– b d schematami relacji w postaci BCNF
– dane z pierwotnej relacji b d wiernie reprezentowane w relacjach

powstałych w wyniku dekompozycji => b dzie istniała mo liwo

dokładnego odtworzenia pierwotnej relacji, na podstawie relacji

utworzonych przez wielokrotne dekompozycje.

• Strategia dekompozycji:

– Dane: relacja R z zale no ciami funkcyjnymi ZF
– Znalezienie pewnej nietrywialnej zale no ci funkcyjnej {A

1

, A

2

,..., A

n

}

→

{B

1

, B

2

,..., B

m

} , która narusza warunek BCNF (tzn. {A

1

, A

2

,..., A

n

} nie jest

nadkluczem).

– Wyliczenie dopełnienia zbioru atrybutów {A

1

, A

2

,..., A

n

}

+

.

• Dopełnienie zawiera wszystkie atrybuty, gdy {A

1

, A

2

,..., A

n

} jest

nadkluczem.

Bazy danych (studia dzienne)

7

Dekompozycja R do postaci BCNF

– Zamie relacj R na relacje o schematach:

• R

1

=

{A

1

, A

2

,..., A

n

}

+

• R

2

= (R-{A

1

, A

2

,..., A

n

}

+

)

∪

{A

1

, A

2

,..., A

n

}

R-X

+

X

X

+

-X

R

R

1

R

2

Bazy danych (studia dzienne)

8

Projektowanie zale no ci funkcyjnych

• Zało enia:

– w wyniku dekompozycji relacji R powstaje relacja S oraz jeszcze

inna relacja.

– F - zbiór zale no ci funkcyjnych prawdziwych w R
– Aby wyznaczy zbiór zale no ci funkcyjnych prawdziwych w S,

nale y

• rozwa y wszystkie podzbiory X atrybutów S i dla ka dego

wyznaczy X

+

. Je li atrybut B spełnia nast puj ce warunki

– B nale y do S
– B nale y do X

+

– B nie nale y do X,

to zale no funkcyjna X

→

B jest spełniona w relacji S

Bazy danych (studia dzienne)

9

Problem

• Wyst puje jedna struktura zale no ci funkcyjnych, która mo e

powodowa problem w trakcie dekompozycji.

AB

→

C i C

→

B

Bazy danych (studia dzienne)

10

Trzecia posta normalna

Mówimy, e relacja jest w trzeciej postaci normalnej (3NF) wtedy i

tylko wtedy, gdy jest spełniony nast puj cy warunek:

je li

A

1

, A

2

,..., A

n

→

B jest zale no ci nietrywaln , to albo {A

1

, A

2

,..., A

n

} jest

nadkluczem

albo B jest elementem pewnego klucza

.

Bazy danych (studia dzienne)

11

SQL

• Funkcje operuj ce na datach
• Funkcje konwersji
• Funkcje polimorficzne

• Funkcje grupuj ce

Bazy danych (studia dzienne)

12

Wyra enia arytmetyczne dla dat

• data + liczba - dodanie do daty pewnej liczby dni - wynikiem jest

data

• data-liczba - odj cie od daty pewnej liczby dni - wynikiem jest

data

• data-data - odjecie od daty innej daty, wynikiem jest liczba dni

mi dzy nimi

UWAGA: liczba mo e by ułamkiem, wówczas do daty dodana

zostanie odpowiednia liczba godzin, minut, sekund.

Bazy danych (studia dzienne)

13

Funkcja MONTHS_BETWEEN

• MONTHS_BETWEEN (data1, data2) - zwraca odst p pomi dzy

wyspecyfikowanymi datami wyra ony w miesi cach. Wynik

dodatni, je li data1 pó niejsza od data2, lub ujemny w

przeciwnym przypadku.

Bazy danych (studia dzienne)

14

Funkcja ADD_MONTHS

• ADD_MONTHS (data, n) - zwraca dat plus n miesi cy

kalendarzowych, n- całkowite, ale mo e by ujemne. Je li

otrzymamy bł dn dat (np. 30 lutego) wynikiem b dzie ostatnia

sensowna data w otrzymanym miesi cu.

Bazy danych (studia dzienne)

15

Funkcja NEXT_DAY

• NEXT_DAY(data1, dzie _tygodnia) - zwraca pierwsz po

wskazanej dat , której dzie tygodnia jest taki jak wskazuje drugi

argument. W argumencie podajemy numer lub nazw dnia

tygodnia.

Bazy danych (studia dzienne)

16

Funkcja LAST_DAY

• LAST_DAY(data1) - wynikiem jest data ostatniego dnia w

miesi cu, do którego nale y data1.

Bazy danych (studia dzienne)

17

Funkcja ROUND

• ROUND(data1) - wynikiem jest data1 ze wska nikiem

godzinowym ustalonym na północ, je li data1 wskazuje na

godzin przed południem, lub na północ dnia nast pnego, gdy

data1 wskazuje na godzin przed południem

• ROUND(data1,’MONTH’) - wynikiem jest data pierwszego dnia

miesi ca zawieraj cego data1, gdy jest ona z pierwszej połowy

miesi ca, lub pierwszego dnia nast pnego miesi ca w

przeciwnym przypadku.

• ROUND(data1,’YEAR’) - zaokr glenie do pierwszego dnia roku.

Bazy danych (studia dzienne)

18

Funkcja TRUNC

• TRUNC(data1, dokładno ) - wynikiem jest data pierwszego dnia

miesi ca zawieraj cego data1, je li dokładno jest ‘MONTH”

lub pierwszego dnia roku, je li dokładno jest ‘YEAR’. Bez

drugiego argumentu funkcja powoduje usuni cie składnika

godzinowego z daty.

Bazy danych (studia dzienne)

19

Funkcje konwersji

• TO_CHAR (liczba|data[,format’]) - zamiana liczby lub daty na

ci g znaków zgodny z formatem opisanym w parametrze ‘format’

• TO_NUMBER (tekst) - zamiana ci gu znaków zawieraj cych

liczb na dan typu NUMBER

• TO_DATE(‘tekst’, ‘format’) - zamiana ci gu znaków

reprezentuj cych dat w formacie opisanym w parametrze

‘format’ na dan typu DATE

Bazy danych (studia dzienne)

20

Funkcja TO_CHAR

konwersja dat

Przykłady formatów dat:

YYYY, YYY, YY, Y - 4, 3, 2,lub ostatnia cyfra roku
MM - miesi c
MONTH - nazwa miesi ca
DDD, DD, D - dzie roku, miesi ca lub tygodnia
DAY - nazwa dnia tygodnia
HH - godzina
MI - minuta; SS - sekunda

Bazy danych (studia dzienne)

21

Funkcja TO_CHAR

konwersja liczb

Formaty dla liczb:

Wzorzec Znaczenie

Przykład

9

Pozycja cyfry – liczba

dziewi tek okre la

szeroko wy wietlania)

99999 1234

0

Wy wietlani zer

wiod cych

099999 001234

$

Ruchomy znak dolara

$99999 $1234

.

Pozycja kropki

dziesi tnej

99999.99 1234.00

Bazy danych (studia dzienne)

22

Funkcje polimorficzne

Funkcja DECODE

Funkcje polimorficzne - funkcje, które nie s zwi zane ze

szczególnym typem danych, działaj podobnie dla wielu ró nych

danych.

• DECODE - umo liwia warunkow realizacj zapyta , gdy działa

na zasadzie typu „case” czy „if-then-else” z innych j zyków.

• DECODE(wyra enie, wyr1, wynik1,

[wyr2, wynik2,
......................]

wynik domy lny)

Uwagi:

– wyra enie mo e by dowolnego typu
– warto wyr musz by takiego samego typu jak wyra enie

Bazy danych (studia dzienne)

23

Inne funkcje

• NVL (wyra enie1, wyra enie2) - zmienia warto NULL w

pierwszym argumencie na warto 2

• GREATEST (warto 1, warto 2,...) - zwraca najwi ksz

warto z listy

• LEAST(warto 1, warto 2,...) - zwraca najmniejsz warto

sposród podanych argumentów

Bazy danych (studia dzienne)

24

Funkcje grupowe

• Funkcje grupowe s przeznaczone do działania na grupach wierszy.

Wynikiem działania funkcji grupowej jest pojedyncza warto dla całej

grupy, a nie jedna warto dla ka dego wiersza.

• Przykłady funkcji:

– AVG ([DISTINCT] wyra enie)
– COUNT ([DISTINCT] wyra enie)
– COUNT(*)
– MAX ([DISTINCT] wyra enie)
– MIN ([DISTINCT] wyra enie)
– STDDEV ([DISTINCT] wyra enie)
– SUM ([DISTINCT] wyra enie)
– VARIANCE ([DISTINCT] wyra enie)

• Wszystkie funkcje grupowe, z wyj tkiem count(*) ignoruj NULL.

Bazy danych (studia dzienne)

25

Zapytania grupuj ce

SELECT lista pól
FROM tabele
WHERE warunki przed grupowaniem
GROUP BY pola grupuj ce
HAVING warunki po grupowaniu

Bazy danych (studia dzienne)

26

Zasady wykonania zapytania

grupuj cego

• Rozwa wszystkie kombinacje wierszy tabel

wyst puj cych w klauzuli FROM

• Do ka dego wiersza zastosuj warunek WHERE
• Podziel wiersze na grupy
• Do ka dej grupy zastosuj warunek w klauzuli

HAVING

• Dla ka dego wiersza reprezenuj cego grup oblicz

warto ci wyra e po SELECT

Bazy danych (studia dzienne)

27

Zapytania grupuj ce - ograniczenia

• Na li cie wyboru polecenia SELECT u ywaj cego

grupowania wolno umieszcza tylko te kolumny,

które s przedmiotem działania klauzuli GROUP BY

chyba, e wyst puj one wewn trz funkcji

grupujacej.

• Ka da kolumna lub wyra enie wystepuj ce na li cie

SELECT, nie obj te funkcj grupow musi by

przedmiotem grupowania klauzul GROUP BY.