20. Adatbázisok tervezése és lekérdezése Flashcards
Relációs adatmodell
Adatok gyűjteményét kezelő modell
E / K modell átalakítása relációs adatmodellbe
- egyedhalmaz séma → relációséma
- tulajdonságok → attribútumok
- (szuper)kulcs → (szuper)kulcs
- egyedhalmaz előfordulása → reláció
- egyed → e(A1)…e(An) sor
- R(E1, …Ep, A1, …Aq) kapcsolati séma (Ei egyedhalmaz, Aj tulajdonság) → R(K1, …Kp, A1, …Aq) relációséma
(Ki az Ei (szuper)kulcsa)
Relációséma
- R: relációnév
- Ai: attribútumok
- Dom(Ai): attribútumok lehetséges értékei, típusa (milyen domain tartozik hozzá)
Előfordulás
- lehetséges értékekből veszünk sorok halmazát
- egy sor a reláció-előfordulás
- egy sor egyszer szerepel (kivéve multihalmaznál)
- sorok sorrendje relációs adatmodell szinten lényegtelen – optimalizálásnál számíthat
- oszlop sorrendje is lényegtelen
Hivatkozási épség
(E / K) megszorítás, kerek végződéssel jelölik. Pontosan egy egyed
tartozik egy kiválasztott egyedhez (pl.: 1 gyártónak pontosan 1 legjobb söre van)
E / K modell
- Egyedhalmaz: hasonló egyedek összessége
- Attribútumok: megfigyelhető tulajdonságok, megfigyelt értékek, egyedek tulajdonságai
- Kapcsolatok: más egyedhalmazzal való kapcsolatok
- Séma: E(Ai, …, An) egyedhalmaz séma. E név, Ai tulajdonság, DOM(Ai) a lehetséges értékek halmaza.
- Előfordulás: konkrét egyedek (entitások). Minden attribútumban nem egyezhetnek meg
- K(E1, E2) bináris kapcsolat (…)
E / K kapcsolat típusok
- egy-egy
- sok-egy
- sok-sok
Szuperkulcs
Az egyedhalmaz szuperkulcsa egy azonosító, vagyis olyan tulajdonság-halmaz, amelyről feltehető,
hogy az egyedhalmaz előfordulásaiban nem szerepel két különböző egyed, amelyek ezeken a tulajdonságokon megegyeznek. Az összes tulajdonság mindig szuperkulcs.
E / K tervezési alapelvek
- valósághű modellezés: megfelelő tulajdonságok tartozzanak az egyedosztályokhoz, például a tanár neve ne a diák tulajdonságai közé tartozzon
- redundancia elkerülése: az index(etr-kód,lakcím,tárgy,dátum,jegy) rossz séma, mert a lakcím annyiszor ismétlődik,
ahány vizsgajegye van a diáknak, helyette 2 sémát érdemes felvenni: hallgató(etr-kód,lakcím), vizsga(etrkód,tárgy,dátum,jegy). - egyszerűség: fölöslegesen ne vegyünk fel egyedosztályokat, például a naptár(év,hónap,nap) helyett a megfelelő helyen inkább dátum tulajdonságot használjunk
- tulajdonság vagy egyedosztály: például a vizsgajegy osztály helyett jegy tulajdonságot használjunk.
Relációs algebrai műveletek
- Unió
- Különbség
- Vetítés (projekció)
- Kiválasztás
- Direktszorzat
- Átnevezés
SQL
Structured Query Language
Főbb részei:
- DDL (Data Definition Language)
- DML (Data Manipulation Language)
- DCL (Data Control Language)
- Tranzakciókezelés
- Procedurális kifejezések
DML
Adatkezelő nyelv - Data Manipulation Language (INSERT, UPDATE, DELETE, SELECT)
DDL
Adatleíró nyelv - Data Definition Language (CREATE, ALTER, DROP)
DCL
Adatvezérlő nyelv - Data Control Language (GRANT, REVOKE)
SQL - Tranzakció-kezelés
COMMIT, ROLLBACK, SAVEPOINT
SQL - Procedurális kifejezések
Oracle PL/SQL, SQL/PSM
PSM definíciója
PSM (Persistent Stored Modules): SQL utasítások és konvencionális elemek (if, while stb) keverékéből áll. Olyan
dolgokat is meg lehet csinálni, amit önmagában az SQL-ben nem.
Eljárás meghívása: CALL <eljárás> (<argumentumlista>)</argumentumlista></eljárás>
Függvénynél a RETURN utasítás határozza meg a visszatérési érték típusát - nem terminál ekkor a függvény (!)
DECLARE - BEGIN - END
értékadás: SET <változó> = <kifejezés></kifejezés></változó>
PL/SQL definíciója
Nem csak eljárások, függvények tárolására alkalmas, ezeket futtatni is tudja.
értékadás: “:=” jellel
PSM-hez képest eltérések:
- több típus (NUMBER -> INT, REAL)
- ROWTYPE: attribútum típusa
Relációs adatbázis-sémák tervezése
Függőségek: funkcionális, többértékű (tervezésnél használják, adatbázisban megszorítások vannak)
Normalizálás: jó sémákra való felbontás, funkcionális függőségek → (1,2,)3NF, BCNF; többértékű függőségek → 4NF
Funkcionális függőség (def.)
Legyen R(U) egy relációséma, továbbá X és Y az U attribútumhalmaz részhalmazai. X-től funkcionálisan
függ Y (jelölésben X → Y), ha bármely R feletti T tábla esetén valahányszor két sor megegyezik X-en, akkor megegyezik Y-on is, ∀t1, t2 ∈ T esetén (t1[X] = t2[X] ⇒ t1[Y ] = t2[Y ]). Ez lényegében azt jelenti, hogy az X-beli
attribútumok értéke egyértelműen meghatározza az Y-beli attribútumok értékét. Jelölés: R |= X → Y, vagyis R
kielégíti X → Y függőséget.
Armstrong-axiómák:
Legyen R(U) relációséma és X, Y ⊆ U, és jelölje XY az X és Y attribútumhalmazok
egyesítését. F legyen funkcionális függőségek tetsz. halmaza.
- FD1 (reflexivitás): Y ⊆ X esetén X → Y .
- FD2 (bővíthetőség): X → Y és tetszőleges Z esetén XZ → Y Z.
- FD3 (tranzitivitás): X → Y és Y → Z esetén X → Z.
Az Armstrong-axiómarendszer helyes és teljes, azaz minden levezethető függőség implikálódik is, illetve azok a
függőségek, amelyeket F implikál azok le is vezethetők F-ből. F |- X → Y ⇐⇒ F |= X → Y
Boyce-Codd normálforma (BCNF)
R reláció BCNF-ben van, ha minden X → Y nemtriviális FF-re R-ben X szuperkulcs. Nemtriviális: Y nem része X-nek. Szuperkulcs: tartalmaz kulcsot (ő maga is lehet kulcs). Ha van
olyan következmény FF F-ben, ami sérti a BCNF-t, akkor egy F-beli FF is sérti. Kiszámítjuk X+-t: Ha itt
nem szerepel az összes attribútum, X nem szuperkulcs.
- normálforma (3NF)
Bizonyos FF halmazok esetén a felbontáskor elveszíthetünk függőségeket. 3. normálformában (3NF) úgy
módosul a BCNF feltétel, hogy az előbbi esetben nem kell dekomponálnunk. Egy attribútum elsődleges
attribútum (prím), ha legalább egy kulcsnak eleme. X → A megsérti 3NF-t akkor és csak akkor, ha X nem
szuperkulcs és A nem prím.
- normálforma (4NF)
A 4.normálforma hasonlít a BCNF-re, azaz minden nem triviális többértékű függőség bal oldala szuperkulcs.
A TÉF-ek okozta redundanciát a BCNF nem szünteti meg. A megoldás: a negyedik normálforma. A negyedik normálformában (4NF), amikor dekomponálunk, a TÉF-eket úgy kezeljük, mint az FF-eket, a kulcsok megtalálásánál azonban nem számítanak.
Egy R reláció 4NF -ben van ha: minden X →→ Y nemtriviális TÉF esetén X szuperkulcs. Nemtriviális
TÉF: Y nem részhalmaza X-nek, és X és Y együtt nem adják ki az összes attribútumot. A szuperkulcs definíciója ugyanaz marad, azaz csak az FF-ektől függ. Definíció: R 4NF-ben van D-re nézve, ha XY != R,
Y !⊂ X, és D |- X →→ Y esetén D |- X → R.
Definíció: d = {R1, …, Rk} dekompozíció 4NF-ben van D-re nézve, ha minden Ri 4NF-ben van ΠRi
(D)-re
nézve.
Állítás: Ha R 4NF-ben van, akkor BCNF-ben is van. Következmény: Nincs mindig függőségörző és veszteségmentes 4NF dekompozíció.
Veszteségmentes 4NF dekompozíciót mindig tudunk készíteni a naiv BCNF dekomponáló algoritmushoz hasonlóan.
Ha R 4NF-ben van, akkor BCNF-ben is.
Dekompozíciók, anomáliák
Rossz tervezés → anomáliák
Cél: anomáliák, redundancia csökkentése
- Módosítási anomália: Egy adat egy előfordulását megváltoztatjuk, más előfordulásait azonban nem. (pl.: terméknél lenne a konkrét márkanév eltárolva)
- Törlési anomália: Törléskor olyan adatot is elveszíthetünk, amit nem szeretnénk. (csak megrendeléseket lennének eltárolva a termékek → megrendelés törlésekor a termék is elveszik)
- Beillesztési anomália: NULL gyártó beszúrása?? → megszorítás, trigger az adatok ellenőrzésére (pl.: megrendeléshez nem tartozna partner)
Dekomponálás
Az anomáliáktól dekomponálással (felbontással) tudunk megszabadulni! Definíció:
d = {R1, …, Rk} az (R,F) dekompozíciója, ha nem marad ki attribútum, azaz R1 ∪ … ∪ Rk = R.(Az adattábla
felbontását projekcióval végezzük).
Elvárások:
1. Veszteségmentes legyen a felbontás, vagyis ne legyen információvesztés. A fenti jelölésekkel: ha r = ΠR1
(r) ./
… ./ ΠRk
(r) teljesül, akkor az előbbi összekapcsolásra azt mondjuk, hogy veszteségmentes. Itt r egy R
sémájú relációt jelöl. Chase-teszt a veszteségmentességhez: Készítünk egy felbontást. A felbontás eleminek
összekapcsolásából veszünk egy sort. Az algoritmussal bebizonyítjuk, hogy ez a sor az eredeti relációnak is
sora.
2. A vetületek legyenek jó tulajdonságúak, és a vetületi függőségi rendszere egyszerű legyen (normálformák:
BCNF, 3NF)
3. Függőségek megőrzése a vetületekben (FO) A dekompozíciókban érvényes függőségekből következzen az eredeti sémákra kirótt összes függőség. Adott (R,F) esetén d = {R1, …, Rk} függőségőrző dekompozíció akkor
és csak akkor, ha minden F-beli függőség levezethető a vetületi függőségekből: minden x → Y ∈ F esetén
ΠR1
(F) ∪ … ∪ ΠRk
(F) |- X → Y .
A függőségőrzésből nem következik a veszteségmentesség és a veszteségmentességből nem következik a függőségőrzés.
