Bazy danych - Politechnika Białostocka

Plan wykáadu
• Diagramy zwizków encji
Bazy danych
–
–
–
–
–
Wykáad 2: Diagramy zwizków encji (ERD)
SQL - áczenie tabel, zapytania grupujce
elementy ERD
licznoci zwizków
podklasy
klucze
zbiory sáabych encji
• SQL - zapytanie proste, áczenie tabel
Maágorzata Kr
towska, Agnieszka Oniko
Wydziaá Informatyki
Politechnika Biaáostocka
2
Bazy danych
Proces modelowania i implementacji
bazy danych
Elementy ERD
Diagram zwizków encji - Entity-Relationship Diagram E/R (ERD)
Analiza informacji,
które b
d zawarte
w bazie danych
System zarzdzania
relacyjn
baz danych
• Zbiory encji
– Encja - rozró*nialny obiekt
– Zbiór encji (typ encji) - zbiór podobnych encji
Zbiór encji
(typ encji)
Diagram E/R
Bazy danych
Schemat bazy
danych
(Model relacyjny)
WykáDGRZFD
Kr
WRZVND0DáJRU]DWD
OniNR$JQLHV]ND
Sosnowski Zenon
.........................
3
Bazy danych
Encje
(Instancje danego
typu encji)
4
Elementy ERD
Elementy diagramu E/R
• Atrybuty - informacje opisujce encj
Imi
• Zwizki - powizania pomi
dzy dwiema lub wi
ksz liczb encji
(zbiorów encji)
Nazwisko
Jakie mog istnieü powizania pomi
dzy nast
pujcymi encjami:
– Szkoáy
– Rodzice
– Dzieci
PRACOWNIK
• Powizania bezporednie
– dziecko-szkoáa
– rodzic - dziecko
Nazwa
zwizku
Encja1
Encja2
• Powizania porednie:
– rodzic - szkoáa
Id
5
Bazy danych
6
Bazy danych
Przykáad
Przykáad
Baza danych filmów
tytuá
rok
Filmy
czas
Baza danych filmów
nazwisko
Wyst
puje
tytuá
adres
Filmy
Aktorzy
czas
rodzaj
Posiada
nazwa
adres
7
Bazy danych
nazwisko
Wyst
puje
adres
Aktorzy
rodzaj
Filmy
Potop
)XUHN
Kingsajz
Kingsajz
Seksmisja
Studia
Bazy danych
rok
Aktorzy
Daniel Olbrychski
Katarzyna Figura
Katarzyna Figura
Jerzy Stuhr
Jerzy Stuhr
8
Przykáad
Liczebnoü zwizków encji
Baza danych filmów
tytuá
rok
Filmy
czas
nazwa
Posiada
rodzaj
Filmy
Potop
)XUHN
Kingsajz
Seksmisja
Studia
adres
Studia
Studio1
Studio2
Studio1
Studio3
N:N
9
Bazy danych
1:N
1:1
10
Bazy danych
Liczebnoü zwizków encji
Zwizki wieloargumentowe
• N:N
Filmy
Wyst
puje
Filmy
Aktorzy
• 1:N
Kontrakty
Aktorzy
Studia
Filmy
Posiada
Studia
• 1:1
Studia
Bazy danych
Kieruje
Prezesi
11
Reprezentacja w postaci trzech wartoci:
(studio, aktor, film)
Bazy danych
12
Role w zwizkach
Atrybuty zwizku
Przykáad Studio, które podpisaáo kontrakt z aktorem mo*e podpisaü
kontrakt z innym studiem po to , by umo*liwiü aktorowi udziaá w filmie.
Reprezentacja zwizku: (studio1, studio2, aktor, film)
Kontrakty
Filmy
Gdzie umieciü wynagrodzenie aktora?
tytuá
Aktorzy
Filmy
Studio
aktora
Studio
producenta
rok
czas
nazwa
13
14
Przeksztaácanie w zwizki binarne
wynagrodzenie
Zwizek kontrakty: (studio1, studio2, film, aktor)
Kontrakty
Filmy
Ga*e
nazwisko
adres
Aktorzy
Studia
rodzaj
•
Studia
nazwa
Bazy danych
•
adres
Aktorzy
Studio
aktora
Studio
producenta
Kontrakty
adres
Bazy danych
Przesuni
cie atrybutu do zbioru encji
czas
Aktorzy
Studia
Bazy danych
Filmy
adres
rodzaj
Role studia:
• producent filmu
• „wáaciciel” aktora
rok
nazwisko
Kontrakty
Studia
tytuá
wynagrodzenie
15
stworzenie dodatkowego zbioru encji, którego elementy traktuje si
tak
samo jak krotki zbioru zwizków dla zwizku wieloargumentowego
(áczcy zbiór encji)
stworzenie zwizków wiele do jeden ze zbioru áczcego do
poszczególnych zbiorów encji wchodzcych w skáad pierwotnego
zwizku wieloargumentowego
Bazy danych
16
Podklasy w ERD
Podklasy i dziedziczenie w ERD
• Zakáadajc, *e C jest podklas klasy D na diagramach E/R
nale*y:
Podklasa = specjalny przypadek = mniej encji = wi
cej wáasnoci
Przykáad: Kreskówki i kryminaáy to podklasy filmów
do aktorów
czas
tytuá
rok
– wprowadziü pomi
dzy zbiorami encji C i D specjalny typ zwizku isa
(„an A is a B”) - wierzchoáek trójkta wskazuje klas
nadrz
dn
– wszystkie atrybuty i zwizki, które odnosz si
wyácznie do encji C
s doczepione do prostokta oznaczajcego C
– atrybuty i zwizki, które opisuj zarówno C jak i D s doczepione do
prostokta oznaczajcego D.
rodzaj
Filmy
• Dziedziczenie w ERD:
dubbinguje
isa
bro
isa
Kreskówki
– encje mo*na ogldaü jako záo*one ze skáadowych , które nale* do
ró*nych zbiorów encji, stanowicych cz
ci hierarchii isa
– skáadowe te s powizane w caáoü zwizkiem isa
– encja ma te wszystkie atrybuty, które nale* do którejkolwiek
skáadowej oraz wchodzi we wszystkie zwizki, w które s wáczone
jej skáadowe
Kryminaáy
17
Bazy danych
18
Bazy danych
Klucze
• Klucze - atrybuty lub zbiory atrybutów,
które jednoznacznie identyfikuj encj
wewntrz zbioru encji.
– W zbiorze encji nie mog wyst
powaü
dwie encje, które miaáyby identyczne
wartoci atrybutów tworzcych klucz.
• Atrybuty, które wchodz w skáad klucza
na diagramach ER s podkrelone.
• W hierarchii isa, tylko zbiór encji z klasy
nadrz
dnej (korze drzewa) mo*e
zawieraü klucz, który jednoznacznie
identyfikuje encje równie* w podklasach.
• Ka*dy
zbiór
encji
musi
mieü
zdefiniowany klucz.
Bazy danych
Integralnoü referencyjna
Wi
zy integralnoci referencyjnej narzucaj wymaganie, aby
wartoü, któr wskazuje encja faktycznie znajdowaáa si
w bazie.
tytuá
rok
E
Filmy
czas
R
F
Interpretacja: strzaáka o zaokrglonym grocie: zwizek jest typu wiele do jeden
ze zbioru E do F i w zbiorze F musi istnieü encja odpowiadajca encji ze zbioru E
rodzaj
E
R
F
Interpretacja: strzaáka o ostrym grocie: zwizek jest typu wiele do jeden ze
zbioru E do F i w zbiorze F mo*e istnieü co najwy*ej jedna encja
odpowiadajca encji ze zbioru E.
19
Bazy danych
20
Integralnoü referencyjna - przykáad
Filmy
Posiada
Interpretacja:
• Studio posiadajce pewien film zawsze musi
istnieü w zbiorze encji Studia
• Studio kierowanego przez pewnego prezesa
musi istnieü w zbiorze Studia
• Studio ma co najwy*ej jednego prezesa, ale w
pewnych momentach mo*e go nie mieü
• Usuni
cie studia z bazy powoduje usuni
cie
prezesa
Inne rodzaje wi
zów
• Wi
zy domenowe - wymagania, aby wartoü atrybutu nale*aáa
do okrelonego zbioru wartoci specyficznych lub znajdowaáa si
w okrelonym zakresie
• Wi
zy zasadnicze - arbitralnie narzucone warunki, których
speánienie musi byü bezwzgl
dnie przestrzegane w definiowanej
bazie danych
Studia
Kieruje
tytuá
Filmy
Prezesi
czas
21
Bazy danych
Zbiory sáabych encji
•
•
Piákarze
wiek
numer
gra
Wyst
puje
<100
adres
Aktorzy
rodzaj
Bazy danych
22
• Je*eli E jest zbiorem sáabych encji , wówczas zbiór encji F, który
dostarcza atrybutów klucza do E, musi pozostawaü z E w pewnym
zwizku R oraz
– Zwizek R musi byü binarny, typu wiele do jeden z E do F
– Atrybuty wchodzce w skáad klucza E a pochodzce z F musz byü
cz
ci klucza w F
– Je*eli zbiór F jest zbiorem sáabym, to kluczowe atrybuty z F
przekazywane do E mog pochodziü z pewnego innego zbioru encji,
z którym F jest powizany poprzez zwizek typu wiele do jednego.
nazwa
Dru*yny
• Uogólniajc:
– je*eli zbiór encji jest oznaczony podwójn ramk, to musi byü sáaby.
Jego klucz skáada si
z tych jego wáasnych atrybutów, które s
podkrelone oraz atrybutów klucza tych zbiorów encji, z którymi zbiór
sáaby jest poáczony przez zwizki typu wiele do jeden, a które
oznaczono podwójnymi kraw
dziami.
zbiór sáabych encji - prostokt o podwójnych kraw
dziach
zwizki áczce ten zbiór z innymi to zwizki wiele do jeden - romby o podwójnych
kraw
dziach
Bazy danych
nazwisko
Wymagania dla zbiorów sáabych encji
Zbiory sáabych encji - zbiory encji, dla których niektóre lub wszystkie
atrybuty klucza wybiera si
z innego zbioru encji.
Przykáad: Mamy dwa zbiory encji: Piákarze (nazwisko, wiek) oraz
Dru*yny (nazwa). Jak mo*na zdefiniowaü klucz dla zbioru encji
Piákarze?
nazwisko
rok
23
Bazy danych
24
Zasady projektowania
Unikanie redundancji
• unikanie redundancji - unikanie powtórze danych
• prostota - unikanie wprowadzania do projektu wi
cej elementów
ni* naprawd
potrzeba
• dobór wáaciwych elementów
– definiowanie zbioru encji - je*eli z elementem wi*e si
kilka
atrybutów
– definiowanie elementu jako atrybutu - z elementem wi*e si
tylko
jego nazwa
• Redundancja wyst
puje wówczas, gdy dana informacja jest
powtórzona w zbiorze danych.
• Redundancja powoduje niespójnoü bazy danych oraz zajmuje
miejsce na dysku.
Przykáad redundancji:
tytuá
• limitowanie zbiorów sáabych encji - tworzenie identyfikatorów jako
kluczy w zbiorach encji, je*eli to ma uzasadnienie.
Filmy
czas
25
Bazy danych
rok
nazwa
Posiada
rodzaj
nazwa
Studia
adres
26
Bazy danych
Baza üwiczeniowa
Záczenia tabel
SELECT [ALL | DISTINCT] wyra*enie [[AS] alias], ...
FROM tabela_1, tabela_2, ..., tabela_n
WHERE warunki_áczce_tabele
minimalna liczba áczcych warunków = liczba tabel -1
Bazy danych
27
Bazy danych
28
Operator konkatenacji
Funkcje grupowe
Operator konkatenacji (||) pozwala na áczenie kolumny z kolumn, z
wyra*eniem arytmetycznym lub staá wartoci w celu utworzenia
wyra*enia. Argumenty wyst
pujce po obu stromach operatora s
áczone i tworz pojedyncz kolumn
wynikow.
•
•
•
select 'pracownik '||ename||' pracuje na stanowisku '||job from emp;
–
–
–
–
–
–
–
–
'PRACOWNIK'||ENAME||'PRACUJENASTANOWISKU'||JOB
---------------------------------------------------pracownik SMITH pracuje na stanowisku CLERK
pracownik ALLEN pracuje na stanowisku SALESMAN
pracownik WARD pracuje na stanowisku SALESMAN
•
select 'pracownik '||ename||' pracuje na stanowisku '||job "pracownicy i
stanowiska" from emp;
pracownicy i stanowiska
---------------------------------------------------pracownik SMITH pracuje na stanowisku CLERK
pracownik ALLEN pracuje na stanowisku SALESMAN
30
Bazy danych
Zasady wykonania zapytania
grupujcego
Zapytania grupujce
• Rozwa* wszystkie kombinacje wierszy tabel
wyst
pujcych 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 reprezenujcego grup
oblicz
wartoci wyra*e po SELECT
SELECT lista pól
FROM tabele
WHERE warunki przed grupowaniem
GROUP BY pola grupujce
HAVING warunki po grupowaniu
Bazy danych
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 wyjtkiem count(*) ignoruj NULL.
29
Bazy danych
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:
31
Bazy danych
32
Zapytania grupujce - ograniczenia
• Na licie wyboru polecenia SELECT u*ywajcego
grupowania wolno umieszczaü tylko te kolumny,
które s przedmiotem dziaáania klauzuli GROUP BY
chyba, *e wyst
puj one wewntrz funkcji
grupujacej.
• Ka*da kolumna lub wyra*enie wystepujce na licie
SELECT, nie obj
te funkcj grupow musi byü
przedmiotem grupowania klauzul GROUP BY.
Bazy danych
33