Adatbázis rendszerek 5. előadás Adatbázis tervezés Koppányi Zoltán
[email protected] [email protected]
Előző óra — Redundancia, — Anomáliák:
beszúrás, törlés, módosítás
— Funkcionális — Normalizálás — Normál — Táblák
konzisztencia
függőség és szerepe
formák dekompozíciója
Tervezési minták
Tervezési minták (design patterns) — Tervezés
történhet:
—
Tapasztalat alapján
—
Szabványok, előírások alapján
—
Tervezési minták alapján
—A
tervezési minták összegyűjtött tapasztalatok, amelyek mindegyike egy-egy gyakran előforduló problémára ad általánosított választ.
— Tervezési
mintákkal találkozhatunk menedzsmentben, építészetben, stb.
Tervezési minták —
Először Christopher Alexander alkalmazta az építészetben.
—
Wikipédiáról: Ő volt az, aki olyan, az építészetben újra és újra felbukkanó mintákat keresett, amelyek a jól megépített házakat jellemzik. Könyvében, a „The Timeless Way of Buliding”-ben olyan mintákat próbált leírni, amelyek segítségével akár egy kezdő építész is gyorsan jó épületeket tervezhet. A minták a magukban hordozott különböző építészek sok éves tapasztalata miatt szebb, jobb vagy használhatóbb házakat eredményeztek, mintha a tervezőnek csupán saját erejére támaszkodva kellett volna megterveznie azokat.
—
Később, elsősorban az informaiktában, azon belül is szoftverfejlesztésben terjedt el: pl.: programtervezési minták
Tervezési minták — Adatbázisok
lehetőség van.
— Azonban
kialakítására
végtelen
sok
ezek közül csak néhány optimális.
— Akik
már sok hasonló problémával találkoztak (azaz tapasztaltak) könnyen előhúzhatnak egy megoldást.
—A
kezdők számára könnyebb dokumentálva kézhez kaphatják ezeket.
— Az
ha
itt bemutatásra kerülő minták, az előadók és a házifeladatok tapasztalatai alapján készültek.
A „Jegyzőkönyv” — Feladat:
Tipikus probléma, amikor katalógusokat, listákat kell létre hozni és azokat táblába tárolni. Ekkor gyakran előfordul például, hogy egy katalógus listában ugyanaz a gyártó szerepel.
— Megoldás:
A redundancia csökkentése végett, ezeket az ismétlődéseket külön táblába emeljük ki. A kiemelt tábla kulcsát szerepeltetjük az eredeti táblában, mint idegen kulcsot.
A „Mérés” + „Jegyzőkönyv” Jegyzőkönyv ID: Számláló Lista1: Egész (IK) Lista2: Egész (IK) Érték: Bármi …
1 N N
Lista1 ID: Számláló Osztály: Szöveg ... Lista2
ID: Számláló 1 Osztály: Szöveg ...
A „Jegyzőkönyv” Példa: kiindulás Katalógus ID
Megnevezés Típus
Gyártó
Keresztmetszet
Ár
1
T-1
beton
BetonGyártó Kft.
1500
150
2
T-2
acél
AcélGyártó Kft.
2500
250
3
P-2
beton
Védmű Zrt.
4500
450
4
K-1
acél
BetonGyártó Kft.
1200
650
A „Jegyzőkönyv” Példa: megoldás Katalógus ID
Megnevezés
TípusID
GyártóID
Keresztmetszet
Ár
1
T-1
1
1
1500
150
2
T-2
2
2
2500
250
3
P-2
1
3
4500
450
4
K-1
2
1
1200
650
Típus
Gyártó
ID
Típus
Egyéb
ID
Gyártó
Cím
1
Beton
...
1
BetonGyártó Kft.
...
2
Acél
...
2
AcélGyártó Kft.
...
3
Védmű Zrt.
...
A „Jegyzőkönyv” Példa: megoldás Katalógus ID
Megnevezés
TípusID
GyártóID
Keresztmetszet
Ár
1
T-1
1
1
1500
150
2
T-2
2
2
2500
250
3
P-2
1
3
4500
450
4
K-1
2
1
1200
650
Típus
Gyártó
ID
Típus
Egyéb
ID
Gyártó
Cím
1
Beton
...
1
BetonGyártó Kft.
...
2
Acél
...
2
AcélGyártó Kft.
...
3
Védmű Zrt.
...
A „Jegyzőkönyv” előnyök, hátrányok —
—
Előnyök —
Redundancia csökkenés
—
Kiemelt táblában további információk (új oszlopok) könnyen megadhatóak. (pl. cím oszlop bevezetése az előző példában)
—
A kiemelt táblában az értékek egyszerűen megváltoztathatóak, adatbázis anomáliák elkerülhetőek. (pl.: előző példában a gyártó neve)
—
A kiemelt táblában a többszörös névfelvétel elkerülhető, ha arra egyediséget előírunk. (pl.: gyártó csak egyszer forduljon elő)
Hátrányok: —
Az ID-k nehezen értelmezhetőek.
—
A táblák között kapcsolat kialakítása szükséges egy esetleges lekérdezés esetén. (pl.: gyártó nevére való szűréskor)
A „Mérés” — Feladat:
Egy adott műszer több mérést végez, és azokat táblába rögzítjük. Szeretnénk nyilvántartani a műszerek méréseit egy adatbázisban
— Megoldás:
Ahelyett, hogy minden műszerhez létrehozunk egy külön táblát, a méréseket egy táblában tároljuk és az egyes műszerekhez tartozó méréseket külön sorszámmal azonosítjuk.
A „Mérés” Mérés ID: Számláló SzenzorNév: Szöveg MérésSorszám: Egész MérésÉrték: Bármi …
A „Mérés” Mérés ID
Szenzor
MérésSorszám
Nyúlás
1
Bélyeg1
1
10
2
Bélyeg2
1
10
3
Bélyeg1
2
20
4
Bélyeg2
2
30
Gyakran használjuk az időbélyeget (timestamp), mint a mérés azonosítóját
Mérés ID
Szenzor
Timestamp
Nyúlás
1
Bélyeg1
2015.05.05 12:02:02
10
2
Bélyeg2
2015.05.05 12:02:02
10
3
Bélyeg1
2015.05.05 12:02:03
20
4
Bélyeg2
2015.05.05 12:02:03
30
A „Mérés” + „Jegyzőkönyv”
Mérés ID: Számláló ListaElemID: Int (IK) MérésSorszám: Int MérésÉrték: Bármi …
N
1
Lista ID: Számláló Név: Szöveg ...
A „Mérés” + „Jegyzőkönyv” Példa Mérés ID
SzenzorID
Timestamp
Nyúlás
1
1
2015.05.05 12:02:02
10
2
2
2015.05.05 12:02:02
10
3
1
2015.05.05 12:02:03
20
4
2
2015.05.05 12:02:03
30
Szenzor ID
Típus
Egyéb
1
Bélyeg1
...
2
Bélyeg2
...
A „Mérés” előnyök, hátrányok — Előnyök —
Könnyű új „szenzor” felvétele.
—
A mérések együtt könnyebben kezelhetőek.
—
Kevesebb tábla.
— Hátrányok: —
Egy adott műszer elemzéséhez a műszerre rá kell szűrni
A „Flexibilis tábla” —
Feladat: Gyakori probléma, hogy —
egy tábla attribútumai későbbiekben változhatnak, például a specifikáció változása miatt,
—
vagy egy reláció (tábla) nagyon sok attribútumot tartalmaz,
—
vagy a táblában gyakran fordulnak elő NULL elemek különböző attribútumokban.
Megjegyzés: ebben az esetben általában az E/K diagram nem megfelelő, mivel az egyed több dolgot ír le- Ekkor szükséges az E/K diagram felülvizsgálata és további egyedek bevezetése. Azonban néha tervezési döntésként ezeket egy táblába karjuk tárolni. — Ez általában hierarchikus kapcsolat megtartására utal. Megoldás: Egy táblába felvesszük az attribútumokat. Az adatokat tartalmazó táblába egy oszlopba megadjuk az attribútum nevét, egy másik oszlopba pedig az azokhoz tartozó értékeket. —
—
A „Flexibilis Tábla”
FelxibilisTábla ID: Számláló Név: Szöveg AttribútumID: Egész (IK) N AttribútumÉrték: Bármi …
1
Attribútum ID: Számláló AttribútumNév: Szöveg ...
Adatok hierarchikus kapcsolata Járművek
Mérés Repülőgép
Bélyeges mérés
Gépkocsi Repülőgép
Gyorsulás mérés
Szintezési mérés
Nagyobb halmaz Kisebb halmaz I.
Kisebb halmaz III.
Kisebb halmaz II.
A „Flexibilis Tábla”
FelxibilisTábla ID: Számláló Név: Szöveg AttribútumID: Egész (IK) N AttribútumÉrték: Bármi …
1
Attribútum ID: Számláló AttribútumNév: Szöveg ...
A „Flexibilis Tábla” + Változó attribútum típus (komplikált)
FelxibilisTábla ID: Számláló Név: Szöveg AttribútumID: Egész (IK) AttribútumÉrték: Szöveg …
N
Adata konverzióra van szükség az AttribútumTípus alapján az AttriútumÉrtéken
Attribútum
1 ID: Számláló
AttribútumNév: Szöveg AttribútumTípus: Szöveg ...
A „Flexibilis tábla” Példa: kiindulás Mérés ID
Szenzor
Gyorsulás
Nyúlás
Elmozdulás
1
Bélyeg
NULL
10
NULL
2
Gyorsulás Mérő
20
NULL
NULL
3
Szintező
NULL
NULL
120
4
Bélyeg
NULL
10
NULL
Járművek ID
Szenzor Név
Ajtók Gyorsulás száma
Szárnyak Nyúlásfesztávja
Elmozdulás Kerekek száma
1
Gépkocsi Bélyeg 1
NULL 4
10 NULL
NULL 4
2
Gyorsulás Motor
NULL 20
NULL NULL
NULL 2
3
Gépkocsi Szintező2
NULL 2
NULL NULL
1204
4
Repülőgép Bélyeg
2 8
1120
1200 3
A „Flexibilis tábla” Példa: megoldás 1. Mérés ID
Szenzor
MérésTípusID
Érték
1
Bélyeg
2
10
2
Gyorsulás Mérő
1
20
3
Szintező
3
120
4
Bélyeg
2
10
MérésTípus ID
Mérés Típus
1
Gyorsulás
2
Nyúlás
3
Elmozdulás
A „Flexibilis Tábla” + „Mérés”
FelxibilisTábla ID: Számláló Szenzor: Szöveg MérésSorszám: Egész AttribútumID: Egész AttribútumÉrték: Bármi …
N
1
Attribútum ID: Számláló AttribútumNév: Szöveg ...
A „Flexibilis tábla” + „Mérés” Mérés ID
Szenzor
MérésSzámláló
MérésTípusID
Érték
1
Bélyeg
1
2
10
2
Gyorsulás Mérő
1
1
20
3
Szintező
1
3
120
4
Bélyeg
2
2
10
MérésTípus ID
Mérés Típus
1
Gyorsulás
2
Nyúlás
3
Elmozdulás
A „Flexibilis Tábla” + „Mérés” + „Jegyzőkönyv” 1 FelxibilisTábla ID: Számláló ListaElemID: Egész (IK) MérésSorszám: Egész (IK) AttribútumID: Egész AttribútumÉrték: Bármi …
N N
Lista ID: Számláló Név: Szöveg ... Attribútum
ID: Számláló 1 AttribútumNév: Szöveg ...
A „Flexibilis tábla” + „Mérés” + „Jegyzőkönyv” Mérés ID
SzenzorID
MérésSzámláló
MérésTípusID
Érték
1
1
1
2
10
2
2
1
1
20
3
3
1
3
120
4
1
2
2
10
5
3
2
3
35
6
1
3
2
125
MérésTípus
Szenzor ID
Mérés Típus
ID
Mérés Típus
1
Bélyeg
1
Gyorsulás
2
Gyorsulás Mérő
2
Nyúlás
3
Szintező
3
Elmozdulás
A „Flexibilis tábla” Példa: megoldás 2. Járművek ID
Név
AttribútumID
Érték
1
Gépkocsi 1
1
4
2
Gépkocsi 1
3
4
3
Motor
3
2
4
Gépkocsi 2
1
2
5
Gépkocsi 2
3
3
6
Repülőgép
1
8
7
Repülőgép
2
120
8
Repülőgép
3
8
ID
Attribútum
1
Ajtók száma
2
Szárnyak feszt.
3
Kerekek száma
A „Flexibilis tábla” előnyök, hátrányok — Előnyök —
Könnyű új attribútum felvétele
—
Elkerülhető sok NULL érték használata
—
Hiarerchikus adatok együtt kezelhetőek
— Hátrányok: —
Nehezebb és bonyolultabb lekérdezések
—
Nem minden esetben optimális megoldás
A „Csomópont” — Feladat:
Egyes esetekben előfordul, hogy egy entitás önmagára mutat, így például egy csomópontba befutó utak.
— Megoldás:
Amennyiben a csomóponthoz két entitás tartozik, akkor azt egy táblába tárolhatjuk, ahol az összetartozást a tábla egy oszlopa adja, mely idegen kulcs a tábla elsődleges kulcsára. Több csatlakozás esetén egy külön kapcsoló táblába emeljük ki a kapcsolódó elemeket.
A „Csomópont” - egy ágú kapcsolat Út
1
ID: Számláló Név: Szöveg Kapcsoló: Egész (IK) N …
A „Csomópont” Példa – egy ágú kapcsolat Út ID
Út név
Csatlakozás
1
M0
2
2
M1
1
3
M2
1
4
M3
3
A „Csomópont” - több ágú kapcsolat Út ID: Számláló Név: Szöveg …
1
Csomópont ID: Számláló CspSzám: Egész Sorszám: Egész ÚtID: Egész (IK) …
N
A „Csomópont” Példa – több ágú kapcsolat Út ID
Út név
1
M0
2
M1
3
M2
4
M3
Csomópont ID
Csomópont név
CspSzám
Sorszám
ÚtID
1
M0-M1-M3
1
1
1
2
M0-M1-M3
1
2
2
3
M0-M1-M3
1
3
4
4
M0-M3
2
1
1
5
M0-M3
2
2
4
Kiterjesztett relációs algebra
Az ismétlődések megszüntetése – Delta op. —A
műveleteinket multihalmazon definiáltuk
— Így
a sorok ismétlődhetnek
— Előfordul
hogy csak vagyunk kíváncsiak
— Ekkor
δ (S ) δ (
a
különálló
sorokra
használható a delta operátor. Név
Jegy Jelenlét
Kiss Pista
1
14
Nagy Péter 3
14
Kiss Péter
1
14
Nagy Ákos
3
10
Név
Jegy Jelenlét
)= Nagy Péter
1
14
3
14
Nagy Ákos
3
10
Kiss Pista
Attribútum átnevezés — Az
attribútumok nevei átnevezhetőek
a nyilat (→ ) használhatjuk egy operátor, művelet belsejében
— Erre — Így
például projekcióesetén:
π réginév → újnév (S ) Név
π Név → Hallgató (
Kiss Pista
Jegy Jelenlét 1
14
Nagy Péter 3
14
Kiss Péter
1
14
Nagy Ákos
3
10
Hallgató
)= Nagy Péter Kiss Pista
Nagy Ákos
Attribútum átnevezés — Az
átnevezés után ezzel az új attribútum névvel végezhetünk műveleteket.
σ Hallgató=' Kiss Pista '( π Név → Hallgató (
=
Név
Jegy Jelenlét
Kiss Pista
1
14
Nagy Péter 3
14
Kiss Péter
1
14
Nagy Ákos
3
10
Hallgató Kiss Pista
))
Kiterjesztett projekció — Új
attribútum vezethető le
πTermék , Darab∗Ár
(
=
Termék
Jegy Jelenlét
Tej
1
250
Kifli
3
50
Kenyér
2
120
Túró Rudi
3
200
Termék
Darab*Ár
Tej
250
Kifli
150
Kenyér
240
Túró Rudi
600
)
Kiterjesztett projekció + Átnevezés —A
levezetett átnevezzük
új
πTermék , Darab∗Ár →ÖsszÁr(
=
attribútumot
Termék
Jegy Jelenlét
Tej
1
250
Kifli
3
50
Kenyér
2
120
Túró Rudi
3
200
Termék
ÖsszÁr
Tej
250
Kifli
150
Kenyér
240
Túró Rudi
600
legtöbbször
)
Csoportosítás — Csoportosítás
a reláció sorainak „csoportokba” történő beosztása a reláció egy vagy több attribútumának értékétől függően.
— Jele:
γ attr 1, attr 2, ... (S ) Termék
γTermék (πTermék (
Darab
Kenyér
1
Kifli
3
Tej
1
Kifli
3
))
Lépésről lépésre Termék
π Termék (
Darab
Kenyér
1
Kifli
3
Tej
1
Tej
Kifli
3
Kifli
Termék
γTermék (
Termék
Kenyér Kifli Tej Kifli
)=
Termék
)=
Kenyér Kifli Tej
Kenyér Kifli
Csoportosítás + Összegzés — Ahogy
láttuk a csoportosítás ugyanolyan elemeket von össze az adott attribútumon.
— Azonban
sorokat, módon.
a többi attribútum is tartalmaz ezeket összesíthetjük valamilyen
— Az
összesítéshez különböző függvényeket alkalmazhatunk a gamma operátoron belül.
— Ezek
a következőek: SUM, AVG, MIN, MAX, COUNT, FIRST, LAST
Az eredmény reláció felépítése —
Osszuk a reláció sorait csoportokba. Egy csoport azokat a sorokat tartalmazza, amelyeknek az {attr1, attr2, …} listán szereplő csoportosítási attribútumokhoz tartozó értékei megegyeznek. Ha nincs csoportosítási attribútum, akkor az egész R reláció egy csoportot képez.
—
Minden csoporthoz hozzunk amelyik tartalmazza: csoport
létre
olyan
sort,
—
Szóban forgó attribútumait.
csoportosítási
—
Az {attr1, attr2, …} lista összesítési attribútumaira vonatkozó összesítéseket.
Csoportosítás + Összegzés (Példa - SUM) Termék
γTermék , SUM( Darab) (
Darab
Kenyér
1
Kifli
3
Tej
2
Kifli
5
Termék
SumDarab
Kenyér
1
Kifli
8
Tej
2
)=
Kiterjesztett projekció — Új
attribútum vezethető le
πTermék , Darab∗Ár
(
=
Termék
Jegy Jelenlét
Tej
1
250
Kifli
3
50
Kenyér
2
120
Túró Rudi
3
200
Termék
Darab*Ár
Tej
250
Kifli
150
Kenyér
240
Túró Rudi
600
)
Csoportosítás + Összegzés (Példa – Több attribútumra)
γTermék , SUM( Darab)→ÖsszDb , SUM (ÖsszDb∗Ár )→ÖsszÁr
(
Termék
Darab
Ár
Kenyér
1
100
Kifli
3
100
Kifli
3
150
Kenyér
2
150
Tej
2
100
)=
Termék
ÖsszDb
ÖsszÁr
Kenyér
3
400
Kifli
6
750
Tej
2
200
Csoportosítás + Összegzés (Példa - COUNT) Termék
γTermék , COUNT( Darab ) (
Darab
Kenyér
1
Kifli
3
Tej
2
Kifli
5
Termék
CountDarab
Kenyér
1
Kifli
2
Tej
1
)=
Csoportosítás + Összegzés (Példa - FIRST) Termék
γTermék , FIRST ( Darab)
(
Darab
Kenyér
1
Kifli
3
Tej
2
Kifli
5
Termék
FirstDarab
Kenyér
1
Kifli
3
Tej
2
)=
Csoportosítás + Összegzés (Példa – Több attribútumra)
γTermék , SUM( Darab)→ÖsszDb , SUM (ÖsszDb∗Ár )→ÖsszÁr
(
Termék
Darab
Ár
Kenyér
1
100
Kifli
3
100
Kifli
3
150
Kenyér
2
150
Tej
2
100
)=
Termék
ÖsszDb
ÖsszÁr
Kenyér
3
400
Kifli
6
750
Tej
2
200
Sorrendezés —A
rendezetlen halmazon a sorba rendezés operátor segítéségével a rekordok rendezhetőek. ABC sorrend, növekvő, stb.
— Jele:
τ Név (
τ attr 1, attr 2, ... (S ) Név
Jegy
Jelenlét
Név
Kiss Pista
1
14
Kiss Aladár 2
14
Nagy Péter
4
14
Kiss Pista
1
14
Kiss Aladár
2
14
Nagy Péter 4
14 10
Nagy Péter
3
10
Nagy Péter 3
)=
Jegy Jelenlét
Sorrendezés, több attribútumon —
Először az első attribútumon rendezünk, majd az azonos csoportba lévőket a következőn, és így tovább.
τ Név , Jegy (
Név
Jegy
Jelen lét
Kiss Pista
1
14
Nagy Péter
4
14
Kiss Aladár
2
14
Név
Jegy
Jelenlét
Nagy Péter
3
10
Kiss Aladár
2
14
Kiss Pista
1
14
Nagy Péter
3
10
Nagy Péter
4
14
)=
Köszönöm a figyelmet!
