ADATBÁZISOK I. Az esetleges hibákat kérlek a címen jelezd! Utolsó módosítás: március 20

ADATBÁZISOK I. Szerkesztette: Bókay Csongor

Az esetleges hibákat kérlek a [email protected] címen jelezd! Utolsó módosítás: 2013. március 20.

Ez a Mű a Creative Commons Nevezd meg! - Ne add el! - Így add tovább! 3.0 Unported Licenc feltételeinek megfelelően szabadon felhasználható.

1. RELÁCIÓS ALGEBRA

Az algebra műveleteket és atomi operandusokat tartalmaz. A relációs algebrában az atomi operandusokon (relációkhoz tartozó változók; konstansok, melyek véges relációkat fejeznek ki) és az algebrai kifejezéseken végzett műveletek alkalmazásával kapott relációkon műveleteket adunk meg, kifejezéseket építünk. A relációs algebrában minden művelet végeredménye egy reláció, amelyen további műveletek adhatóak meg.

1.1. Alapfogalmak 1.1.1. Reláció. A D1 × D2 × · · · × Dn Descartes-szorzat bármely R részhalmazát (R ⊆ D1 × ×D2 ×· · ·×Dn ) relációnak nevezzük. A D1 , D2 , . . . , Dn adott halmazok a reláció értéktartományai. 1.1.2. Attribútum. Az értéktartományok elemeit felvevő jellemzőket attribútumoknak, vagy mezőknek nevezzük. Az attribútumok a relációk fejrészében találhatóak, tehát ezek a relációban szereplő oszlopok nevei, melyek megadják az oszlopban lévő adatok jelentését. A reláción belül ezeknek a neveknek egyedieknek kell lenniük. 1.1.3. Sor, sortípus. A reláció egy eleme a táblázat sora. A táblázatnak nem lehetnek azonos sorai, illetve a sorok sorrendje tetszőleges. A sorokat rekordnak is szokás nevezni. A sortípus megadható az , vagy röviden az formában. 1.1.4. Komponens. A sor egy elemét komponensnek vagy cellának nevezzük. 1.1.5. Relációséma. A reláció neve és a reláció attribútumainak a halmaza együttesen alkotja a reláció sémáját. A reláció sémáját a reláció nevével és a sortípussal adjuk meg, például: R(A1 : e´rt´ ekt´ıpus1 , A2 : e´rt´ ekt´ıpus2 , . . . , An : e´rt´ ekt´ıpusn ) vagy rövidebben R(A1 , A2 , . . . , An ). 1.1.6. Kulcsok. Attribútumok egy halmaza kulcsot alkot egy relációra nézve, ha a reláció bármely előfordulásában nincs két olyan sor, amelyek a kulcs összes attribútumának értékein A1 , A2 , A3 , . . . ). megegyeznének. A reláció sémájában a kulcsot aláhúzással jelöljük, például: R(A Egy relációban több kulcs is lehet, de kijelölt kulcs csak egy, ez az elsődleges kulcs (primary key). Ha egy kulcs egy másik reláció elsődleges kulcsára utal, akkor idegen kulcsnak (foreign key) nevezzük.

2

1. Relációs algebra

1.2. Alapműveletek A relációs algebrának hat alapművelete van. Ez egy minimális készlet, tehát bármelyiket elhagyva az a többivel nem fejezhető ki. 1.2.1. Halmazműveletek. Mivel a relációk esetén sorok halmazáról van szó, így értelmezhetőek a szokásos halmazműveletek: az unió, a metszet és a különbség. Az alapműveletkehez az unió és a különbség tartozik, a metszet műveletet származtatjuk. Legyen R és S két azonos típusú reláció. Ekkor a halmazműveleteket a következőképp definiáljuk: R ∪ S := {t | t ∈ R ∨ t ∈ S}, R − S := {t | t ∈ R ∧ t ∈ / S} és R ∩ S := R − (R − S). R A B a b c d g a

C c e d

A a c g

S B b d d

C c e f

R∪S A B C a b c c d e g d f g a d

R−S A B C g a d

1.2.2. Projekció. Egy adott relációt vetít le az alsó indexben szereplő attribútumokra, illetve kiszűri a keletkező duplikátumokat, hogy továbbra is fennáljon a halmaz tulajdonság. Jelölés:

πAi ,...,Aj (R) ahol A1 , . . . , Aj az R reláció sémájában lévő attribútomok egy részhalmazának felsorolása. R A B a b c d c d

πA,B (R) A B a b c d

C c e d

1.2.3. Szelekció. Kiválasztja az argumentumban szereplő reláció azon sorait, melyek eleget tesznek az alsó indexben szereplő feltételnek. Jelölés:

σL (R) ahol L vagy elemi feltétel (Ai ◦ Aj vagy Ai ◦ c, ahol c konstans és ◦ ∈ {=, 6=, <, >, ≤, ≥}), vagy összetett feltétel (ha B1 , B2 feltételek, akkor ¬B1 , B1 ∧ B2 , B1 ∨ B2 , és zárójelezésekkel is feltételek). R A B a b c d g a

C c e d

σA=a∨C=d (R) A B C a b c g a d

1.3. További műveletek

3

1.2.4. Természetes összekapcsolás. Két reláció természetes összekapcsolása azon sorpárokat tartalmazza, amelyek a relációk azonos attribútumain megegyeznek. Jelölés:

R ./ S Vegyünk két relációt, amelyek sémái R(A1 , . . . , An , B1 , . . . , Bk ), illetve S(B1 , . . . , Bk , C1 , . . . , Cm ). Ekkor R ./ S típusa (A1 , . . . , An , B1 , . . . , Bk , C1 , . . . , Cm ), vagyis a két attribútumhalmaz uniója. Ha R és S attribútumhalmazai diszjunktak, akkor R ./ S = R × S, ha pedig egyenlőek, akkor R ./ S = R∩S. Fontos megjegyezni, hogy a relációs algebrában nem változik az oszlopok sorrendje. R A a c b

S B a b c

B a a b e

R A a a c

C a c d d

./ S B C a a a c b d

1.2.5. Átnevezés. Szükség lehet egy adott relációnak vagy a reláció attribútumainak átnevezésére. Ha ugyan azt a táblát szeretnék használni többször, akkor az attribútumok átnevezése nem szükséges. Jelölés:

ρR(A1 ,...,An ) (S (B1 , . . . , Bn )) Egy adott S relációt a ρR (S) kifejezéssel nevezhetünk át R-re.

1.3. További műveletek 1.3.1. Descartes-szorzat. Két vagy több reláció Descartes-szorzata során minden reláció sora párban összefűződik. Két reláció esetén az első tábla minden sorához hozzáfűzzük a rákövetkező tábla minden sorát. Jelölés:

R × S × ··· A relációk azonos nevű attribútumait meg kell különböztetni egymástól, így vagy átnevezést, vagy az R.A1 , . . . , R.An , S.A1 , . . . S.An , . . . hivatkozási sémát kell alkalmazni. A a c

R B b d

S C c e

B b q

D r s

A a a c c

R×S R.B C S.B b c b b c q d e b d e q

D r s r s

1.3.2. Osztás. Két reláció hányadosa megadja az osztás bal oldalán álló reláció minden olyan sorát, amelyek mellet az osztás jobb oldalán álló reláció mindegyik sora előfordul. R(A1 , . . . , An , B1 , . . . , Bm ) és S(B1 , . . . , Bm ) relációk esetén R és S hányadosa megadja azon A1 , . . . , An attribútumú v sorok halmazát, amelyekre igaz, hogy az S reláció minden w sorára a vw sor benne van az R relációban. Jelölés:

R÷S

4

1. Relációs algebra

Az osztás művelet kifejezhető más relációs algebrai alapműveletekkel: 



R ÷ S = πL (R) − πL πL (R) × S − R



ahol L egy olyan attribútumlista, amelynek elemei az R reláció azon attribútumai, amelyek nincsenek benne az S relációban. R A B a b a d c d f b f d c f

S C c e e c e e

B b d

C c e

R÷S A a f

1.3.3. Théta-összekapcsolás. A természetes összekapcsolás előírja, hogy egyetlen speciális feltétel szerint párosítsuk a sorokat, mégpedig a közös attribútumok azonos értéke alapján. Néha szükség lehet két reláció sorainak más szempontból történő párosítására, ezért vezetjük be a természetes összekapcsolást kiterjesztve a théta-összekapcsolás műveletet. Jelölés:

R ./ L S ahol L egyszerű aritmetikai összehasonlítás, amely L = Ai ◦ Bj alakú, és ◦ ∈ {=, 6=, <, >, ≤, ≥}. Ezt az összehasonlítást R × S soraira alkalmazzuk, így R ./ L S = σL (R × S). R A B a b c d

S C c e

B b q

D c c

A c c

R ./ A=D S R.B C S.B d e b d e q

D c c

2. SQL

2.1. Áttérés relációs algebráról Relációs algebra

SQL (Oracle)

R πt (R)

SELECT * FROM R;

σf elt (R)

SELECT * FROM R WHERE felt;

πt (σf elt (R))

SELECT t FROM R WHERE felt;

R∪S R∩S R−S R×S R ./ S R ./ f elt S

SELECT * FROM R UNION SELECT * FROM S;

R÷S

SELECT t FROM R;

SELECT * FROM R INTERSECT SELECT * FROM S; SELECT * FROM R MINUS SELECT * FROM S; SELECT * FROM R, S; (vagy CROSS JOIN) SELECT * FROM R NATURAL JOIN S; SELECT * FROM R JOIN S ON felt; SELECT L FROM R MINUS SELECT L FROM ( SELECT * FROM (SELECT L FROM R) CROSS JOIN (SELECT * FROM S) MINUS SELECT * FROM R); -- ahol L az R azon oszlopait jelöli, -- amelyek nincsenek benne az S-ben

6

2. SQL

2.2. SELECT 2.2.1. A dual tábla. A dual pszeudotáblát használva tudunk olyan lekérdezéseket végezni, amelyekhez nem szükséges tábla. Példák: -- Az attr.név ’HELLO’ lesz SELECT ’hello’ FROM dual; -- Az attr.név ’EREDMÉNY’ lesz (átnevezés), a mez˝ o értéke pedig 2 SELECT 1+1 eredmény FROM dual; SELECT ’hello’ köszön, n név, sz.* FROM sz; SELECT ’hello’ köszönés, 2*1 "szorzás eredménye" FROM dual;

2.2.2. ROWNUM. Néha szükség lehet egy lekérdezés sorainak explicit megszámozására. Ezt a ROWNUM pszeudomező segítségével tehetjük meg. Példa: SELECT ROWNUM, n FROM sz;

2.2.3. DISTINCT. A relációs algebrával ellentétben az SQL-ben a táblák nem rendelkeznek halmaz tulajdonsággal, így egy sor többször is előfordulhat. A DISTINCT kulcsszó segítségével a duplikátumokat kiszűrhetjük. Példa: SELECT DISTINCT gy FROM sz; -- Az összes gyümölcs

2.2.4. Műveletek, IN, NOT. A lekérdezésekben használhatóak a megszokott műveletek (= =, ! =, <, <=, >, >=), továbbá a rendezett n-esek esetén használható az IN, illetve ennek tagadása, a NOT IN kulcsszó. A NOT kulcsszó bármely logikai kifejezés tagadására használható. Példák: SELECT ’igaz’ eredmény FROM dual WHERE 3>2; SELECT ’igaz’ eredmény FROM dual WHERE NOT 1>2; SELECT ’igaz’ eredmény FROM dual WHERE 1 IN (1, 2, 3); SELECT ’igaz’ eredmény FROM dual WHERE ’Fülés’ IN (’Malacka’, ’FÜles’, ’Kanga’); SELECT ’igaz’ eredmény FROM dual WHERE (1, 3) IN ((1, 2), (1, 4), (1, 3)); SELECT ’igaz’ eredmény FROM dual WHERE ’Füles’ NOT IN (SELECT n FROM sz WHERE gy=’dió’); SELECT ’igaz’ eredmény FROM dual WHERE (’Micimackó’, ’dió’) IN (SELECT * FROM sz);

2.2.5. LIKE. Szöveg típusú mezők esetén a LIKE kulcsszóval mintaillesztés szerűen szűrhetünk. A ’_’ karakter pontosan egy, míg a ’%’ bármennyi karakterre illeszkedik. Példák: SELECT SELECT SELECT SELECT SELECT

* * * * *

FROM FROM FROM FROM FROM

sz sz sz sz sz

WHERE WHERE WHERE WHERE WHERE

n n n n n

LIKE LIKE LIKE LIKE LIKE

’F%’; ’%F%’; ’%c%a’; ’_i%s’; ’_____’;

2.2. SELECT

7

2.2.6. BETWEEN. A BETWEEN kulcsszóval eldönthető, hogy egy mező benne van-e az adott intervallumban. Példák: -- Azon dolgozók, akinek a fizetése 1000 és 2000 között van SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000; -- Aki nevének a kezd˝ obet˝ uje ’J’ és ’S’ között van SELECT * FROM emp WHERE ename BETWEEN ’J’ AND ’S’;

2.2.7. EXISTS. Annak eldöntésére, hogy egy allekérdezés adott-e vissza legalább egy sort az EXISTS kulcsszót használjuk, amely hamis értéket ad vissza, ha nem tért vissza sorral az allekérdezés, különben igazat. Példa: SELECT ’igaz’ FROM dual WHERE EXISTS (SELECT * FROM sz WHERE gy=’alma’);

2.2.8. ALL, ANY. Az ALL és az ANY kulcsszóval összehasonlíthatunk egy mezőt egy allekérdezés vagy rendezett n-es minden elemével. Az ANY akkor ad igazat, ha legalább egy elemre, míg az ALL akkor ad igazat, ha minden elemre igaz az összehasonlítás. Példák: SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE job=’MANAGER’); SELECT * FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE job=’MANAGER’);

2.2.9. A NULL érték. A relációs algebrával ellentétben SQL-ben egy mező felvehet NULL értéket is (kivéve, ha explicit megtiltjuk). Fontos megjegyezni, hogy a NULL nem egyenlő az üres szöveggel vagy a 0-val, illetve NULL = NULL és NULL != NULL is hamisat ad vissza, így csak az IS NULL kulcsszóval tudjuk eldönteni, hogy egy mező értéke NULL-e. Példák: SELECT ’igaz’ FROM dual WHERE NULL != NULL; -- ∅ SELECT ’igaz’ FROM dual WHERE NULL = NULL; -- ∅ SELECT * FROM emp WHERE comm IS NULL; SELECT * FROM emp WHERE comm IS NOT NULL;

2.2.10. COALESCE. Mivel bármely NULL-al végzett művelet NULL-t eredményez, ezért szükség lehet a NULL értékeket „átalakítani” egy másik értékké. Erre szolgál a COALESCE függvény, amely visszaadja az első nem NULL értéket a paraméterei közül. Példa: -- A sal+comm NULL, ha a comm értéke NULL, ám a -- COALESCE segítségével ezt a NULL értéket 0-nak vesszük SELECT ename, sal, comm,sal + COALESCE(comm, 0) FROM emp;

2.2.11. Aggregátor függvények. Az SQL tartalmaz olyan csoportfüggvényeket, amelyek a lekérdezés minden során végrehajtódnak, majd egy értékkel térnek vissza. Ilyen a SUM, COUNT, AVG, MAX és a MIN. Ezen függvények a COUNT-ot kivéve (0-t ad vissza) nem veszik figyelembe a NULL-t. Példa: SELECT SUM(comm), AVG(comm), SUM(comm)/COUNT(comm), COUNT(comm), SUM(comm)/COUNT(*), COUNT(*) FROM emp;

8

2. SQL

2.2.12. GROUP BY. Adatok csoportosítására a GROUP BY kulcsszót használjuk. A WHERE kulcsszóval a csoportosítás előtt, míg a HAVING-el a létrejövő csoportokat lehet szűrni. -- A NULL egy értéknek számít SELECT job FROM emp GROUP BY job; SELECT job, SUM(sal), COUNT(sal), COUNT(*) FROM emp WHERE sal > 1000 GROUP BY job HAVING SUM(sal) > 5000;

2.2.13. ORDER BY. Az adatok rendezéséhez az ORDER BY kulcsszót használjuk. Rendezhetünk csökkenő (DESC), illetve növekvő (ASC, alapértelmezett) sorrendben is. SELECT job, deptno FROM emp GROUP BY job, deptno ORDER BY job, deptno DESC;

2.2.14. Théta-összekapcsolás, külső összekapcsolás. Théta-összekapcsolás SQL-ben a következő kifejezéssel írható le: JOIN ON . Példa: -- A látogat táblában ivó oszlopként szerepelnek a sörivók nevei SELECT * FROM ivók JOIN látogat ON név = ivó;

A külső összekapcsolás megőrzi az egyik tábla azon sorait, amelyeknek nincs a másik táblában párja (lógó sorok), és NULL értékekkel helyettesíti a hiányzó mezőket. Példa: R A a c

S B b d

B b f

C c s

R OUTER JOIN S A B C a b c c d (null) (null) f s

2.2.15. Megjegyzések. A SELECT-FROM-WHERE állítások multihalmaz szemantikát használnak, de a halmazműveleteknél (UNION, INTERSECT, MINUS) a halmaz szemantika érvényes. Az összesítésekben az ismétlődések kiküszöbölése érdekében DISTINCT-et használunk. Példa: -- Hány különféle áron árulják a ’Bud’ sört? SELECT COUNT(DISTINCT ár) FROM felszolgál WHERE sör = ’Bud’;

Néha szükségünk lehet lekérdezések átírására, mivel nem minden relációsadatbázis-kezelő támogatja a használt kifejezéseket. A MINUS-t tartalmazó lekérdezéseket átírhatjuk NOT EXISTS-et használva. Példa: SELECT d.dname FROM dept d MINUS SELECT d.dname FROM dept d, emp e WHERE d.deptno = e.deptno ORDER BY dname; SELECT d.dname FROM dept d WHERE NOT EXISTS (SELECT NULL FROM emp e WHERE e.deptno = d.deptno) ORDER BY dname;

3. FELADATOK

3.1. Micimackó és barátai (relációs algebra) Szeret Név Gyümölcs Micimackó málna Micimackó méz Füles körte Malacka méz Malacka málna Malacka körte Kanga banán Tigris méz A feladatok egyszerű megoldása érdekében alkalmazzuk az alábbi átnevezést: 



ρSZ(N,GY ) Szeret(N e´v, Gy¨ um¨ olcs)

3.1.1. Feladat. Melyek azok a gyümölcsök, amelyeket Micimackó szeret? 



πGY σN =0 M icimack´o0 (SZ)

3.1.2. Feladat. Melyek azok a gyümölcsök, amelyeket Micimackó nem szeret? 



πGY (SZ) − πGY σN =0 M icimack´o0 (SZ)

3.1.3. Feladat. Melyek azok a gyümölcsök, amelyeket valaki szeret, és nem csak egyedül Micimackó? 







πGY SZ − σN =0 M icimack´o0 (SZ) = πGY σN 6=0 M icimack´o0 (SZ)

10

3. Feladatok

3.1.4. Feladat. Kik azok, akik legalább azokat a gyümölcsöket szeretik, mint Micimackó? 



X := SZ ÷ πGY σN =0 M icimack´o0 (SZ) = πN (SZ) − πN

Csak azok szeretik a Micimackó által szeretett gyümölcsöket, akik eredetileg nem szerették }| {   πN (SZ) × πGY σN =0 M icimack´o0 (SZ) −SZ | {z } Mindenki szereti a Micimackó által szeretett gyümölcsöket

z

3.1.5. Feladat. Kik azok, akik legfeljebb azokat a gyümölcsöket szeretik, mint Micimackó? 



 



Y := πN (SZ) − πN SZ − πN (SZ) × πGY σN =0 M icimack´o0 (SZ) |

{z Akik mást is szeretnek a Micimackó által szeretett gyümölcsökön kívül

}

3.1.6. Feladat. Kik azok, akik pontosan azokat a gyümölcsöket szeretik, mint Micimackó?

X ∩Y 3.1.7. Feladat. Kik szeretik az almát? 



πN σGY =0 alma0 (SZ) 3.1.8. Feladat. Kik nem szeretik az almát? 



πN (SZ) − πN σGY =0 alma0 (SZ)

3.1.9. Feladat. Kik azok, akik szeretnek legalább egy almán kívüli gyümölcsöt? 



πN (SZ) − πN σGY =0 alma0 (SZ) 3.1.10. Feladat. Kik szeretik az almát és a diót is? 







πN σGY =0 alma0 (SZ) ∩ πN σGY =0 di´o0 (SZ) 3.1.11. Feladat. Kik szeretik vagy az almát, vagy a diót? 







πN σGY =0 alma0 (SZ) ∪ πN σGY =0 di´o0 (SZ) 3.1.12. Feladat. Kik szeretik az almát, de a diót nem? 







πN σGY =0 alma0 (SZ) − πN σGY =0 di´o0 (SZ)

A következő feladatokhoz alkalmazzuk a ρSZ1 (SZ), . . . , ρSZn (SZ), n ∈ N átnevezéseket. 3.1.13. Feladat. Kik szeretnek legalább kétféle gyümölcsöt? 

Z := πSZ1 .N σSZ1 .GY 6=SZ2 .GY



∧ SZ1 .N =SZ2 .N (SZ1 × SZ2 )

3.1. Micimackó és barátai (relációs algebra)

11

3.1.14. Feladat. Kik szeretnek legalább háromféle gyümölcsöt? 



∧ SZ1 .GY 6=SZ3 .GY ∧ (SZ1 × SZ2 × SZ3 )

V := πSZ1 .N σSZ1 .GY 6=SZ2 .GY

∧ SZ2 .GY 6=SZ3 .GY ∧ SZ1 .N =SZ2 .N ∧ SZ2 .N =SZ3 .N

3.1.15. Feladat. Kik szeretnek legfeljebb kétféle gyümölcsöt?

πN (SZ) − V 3.1.16. Feladat. Kik szeretnek pontosan kétféle gyümölcsöt? 



πN (SZ) − V ∩ Z

3.1.17. Feladat. Kik, mit nem szeretnek?

πN (SZ) × πGY (SZ) − SZ 3.1.18. Feladat. Kik nem szeretnek minden gyümölcsöt? 



W := πN πN (SZ) × πGY (SZ) − SZ = 



= πN (SZ) − SZ ÷ πGY (SZ)

3.1.19. Feladat. Kik azok, akik minden gyümölcsöt szeretnek?

SZ ÷ πGY (SZ) = πN (SZ) − W 3.1.20. Feladat. Melyek azok a gyümölcsök, amiket legalább ketten szeretnek? 

πGY σSZ1 .GY =SZ2 .GY



∧ SZ1 .N 6=SZ2 .N (SZ1 × SZ2 )

3.1.21. Feladat. Melyek azok a gyümölcsök, amit mindenki szeret?

SZ ÷ πN (SZ) Egészítsük ki a relációt egy M ennyis´ eg attribútummal, amely az állatok által fogyasztott   gyümölcsmennyiséget jelenti. Alkalmazzuk a ρSZ(N,GY,M ) Szeret(N e´v, Gy¨ um¨ olcs, M ennyis´ eg) átnevezést. 3.1.22. Feladat. Kik fogyasztják a legtöbb gyümölcsöt? Nem lehet megoldani, hisz relációs algebrában nem lehet számolni, az eredmény sorrendje nem befolyásolható, nem lehet összesítést csinálni, nem lehet csoportítsani, nem lehet a relációkat módosítani, illetve nem alkalmazhatunk rekurziót. 3.1.23. Feladat. Kik fogyasztják a legtöbb almát? Nem lehet megoldani. 3.1.24. Feladat. Kik fogyasztják a legkevesebb almát? Nem lehet megoldani. 3.1.25. Feladat. Kinek a neve mellett van a legnagyobb számérték? 





πN SZ − πSZ2 .N,SZ2 .GY,SZ2 .M σSZ1 .M >SZ2 .M (SZ1 × SZ2 )

12

3. Feladatok

3.2. Micimackó és barátai (SQL) Ha egy feladathoz több lekérdezés is tartozik, akkor mindegyik ugyan azt az eredményt adja. Néhány feladathoz nem tartozik megoldás, ám ezek könnyen kitalálhatóak az előző feladatokból, vagy egyszerűen átírhatóak a relációs algebrai kifejezésekből. -- [3.1.1.] Melyek azok a gyümölcsök, amelyeket Micimackó szeret? SELECT gy FROM sz WHERE n = ’Micimackó’; -- [3.1.2.] Melyek azok a gyümölcsök, amelyeket Micimackó nem szeret? SELECT gy FROM sz MINUS SELECT gy FROM sz WHERE m = ’Micimackó’; -- [3.1.3.] Melyek azok a gyümölcsök, amelyeket valaki szeret, -és nem csak egyedül Micimackó? SELECT DISTINCT gy FROM sz WHERE n <> ’Micimackó’; SELECT DISTINCT gy FROM (SELECT * FROM sz MINUS SELECT * FROM sz WHERE n = ’Micimackó’); -- [3.1.4.] Kik azok, akik legalább azokat a gyümölcsöket szeretik, -mint Micimackó? SELECT n FROM sz MINUS SELECT n FROM (SELECT * FROM (SELECT n FROM sz), (SELECT gy FROM sz WHERE n = ’Micimackó’) MINUS SELECT * FROM sz); -- [3.1.5.] Kik azok, akik legfeljebb azokat a gyümölcsöket szeretik, -mint Micimackó? SELECT n FROM sz MINUS SELECT n FROM (SELECT * FROM sz MINUS SELECT * FROM (SELECT n FROM sz), (SELECT gy FROM sz WHERE n = ’Micimackó’)); -- [3.1.6.] Kik azok, akik pontosan azokat a gyümölcsöket szeretik, -mint Micimackó? SELECT n FROM sz MINUS SELECT n FROM (SELECT * FROM (SELECT n FROM sz), (SELECT gy FROM sz WHERE n = ’Micimackó’) MINUS SELECT * FROM sz) INTERSECT SELECT n FROM sz MINUS

3.2. Micimackó és barátai (SQL)

SELECT n FROM (SELECT * FROM sz MINUS SELECT * FROM (SELECT n FROM sz), (SELECT gy FROM sz WHERE n = ’Micimackó’)); -- [3.1.7.] Kik szeretik az almát? SELECT DISTINCT n FROM sz WHERE gy = ’alma’; -- [3.1.8.] Kik nem szeretik az almát? SELECT n FROM sz MINUS SELECT n FROM sz WHERE gy = ’alma’; -- [3.1.10.] Kik szeretik az almát és a diót is? SELECT n FROM sz sz1, sz sz2 WHERE sz1.n = sz2.n AND sz1.gy = ’alma’ AND sz2.gy = ’dió’; SELECT n FROM sz WHERE gy = ’alma’ INTERSECT SELECT n FROM sz WHERE gy = ’dió’; -- [3.1.11.] Kik szeretik vagy az almát, vagy a diót? SELECT n FROM sz WHERE gy = ’alma’ UNION SELECT n FROM sz WHERE gy = ’dió’; -- [3.1.12.] Kik szeretik az almát, de a diót nem? SELECT n FROM sz WHERE gy = ’alma’ MINUS SELECT n FROM sz WHERE gy = ’dió’; -- [3.1.17.] Kik, mit nem szeretnek? SELECT * FROM (SELECT n FROM sz), (SELECT gy FROM sz) MINUS SELECT * FROM sz; -- [3.1.21.] Melyek azok a gyümölcsök, amelyeket mindenki szeret? SELECT n FROM sz MINUS SELECT n FROM (SELECT * FROM (SELECT n FROM sz), (SELECT gy FROM sz) MINUS SELECT * FROM sz); -- [3.1.22.] Kik fogyasztják a legtöbb gyümölcsöt? SELECT n FROM (SELECT n, SUM(m) FROM sz GROUP BY n ORDER BY 2 DESC) WHERE ROWNUM <= 1;

13

14

3. Feladatok

-- [3.1.23.] Kik fogyasztják a legtöbb almát? SELECT * FROM (SELECT n FROM sz WHERE gy = ’alma’ ORDER BY m DESC) WHERE ROWNUM <= 1; -- [3.1.25.] Kinek a neve melett van a legnagyobb számérték? SELECT n FROM (SELECT * FROM sz MINUS SELECT sz2.n, sz2.gy, sz2.m FROM sz sz1, sz sz2 WHERE sz1.m > sz2.m);

3.3. Hajók (relációs algebra) A feladatok megoldásához használjuk az alábbi átnevezéseket:

ρHO (Haj o´oszt´ alyok), ρH (Haj o´k), ρK (Kimenetelek), ρCs (Csat´ ak) ρHO1 (HO), . . . , ρHOn (HO), ρH1 (H), . . . , ρHn (H), ρCs1 (Cs), . . . , ρCsn (Cs) 3.3.1. Feladat. Melyek azok a hajók, amelyeket 1921 előtt avattak fel? 



πHaj o´n´ev σF elavatva<1921 (H)

3.3.2. Feladat. Adjuk meg azokat a hajóosztályokat a gyártó országok nevével együtt, amelyeknek az ágyúi legalább 16-os kaliberűek. 



πOszt´aly,Orsz´ag σKaliber≥16 (HO)

3.3.3. Feladat. Adja meg a Denmark Strait-csatában elsüllyedt hajók nevét. 



πHaj o´n´ev σEredm´eny=0 els¨ullyedt0 ∧ Csatan´ev=0 Denmark Strait0 (K)

3.3.4. Feladat. Adjuk meg az adatbázisban szereplő összes hadihajó nevét. Ne feledjük, hogy a Hajók relációban nem feltétlen szerepel az összes hajó.

πHaj o´n´ev (K) ∪ πHaj o´n´ev (H) 3.3.5. Feladat. Melyek azok az országok, amelyeknek csatahajóik és cirkálóhajóik is voltak? 







πOrsz´ag σHO.T´ıpus=0 bb0 (HO) ∩ πOrsz´ag σHO.T´ıpus=0 bc0 (HO) 



πHO1 .Orsz´ag σHO1 .T´ıpus=0 bb0 ∧ HO2 .T´ıpus=0 bc0 ∧ HO1 .Orsz´ag=HO2 .Orsz´ag (HO1 × HO2 ) 3.3.6. Feladat. Melyik hajó, melyik országban készült?

πHaj o´n´ev,Orsz´ag (H ./ HO)

3.4. Hajók (SQL)

15

3.3.7. Feladat. Adjuk meg a Guadalcanal csatában részt vett hajók nevét, vízkiszorítását és ágyúinak számát. 



πHaj o´n´ev,Agy´ ´ ukSz´ ev=0 Guadalcanal0 (K ./ H ./ HO) ama,V ´ızkiszor´ıt´ as σCsatan´ 3.3.8. Feladat. Soroljuk fel a biztosan 1943 előtt épült hajókat! 







πHaj o´n´ev σF elavatva<1943 (H) ∪ πHaj o´n´ev σD´atum<0 1/1/430 (Cs ./ K) 3.3.9. Feladat. Melyik csatában volt mindenféle eredmény?

πEredm´eny,Csatan´ev (K) ÷ πEredm´eny (K) 3.3.10. Feladat. Melyik években avattak legalább 3 hajót? 



πF elavatva σH1 .F elavatva=H2 .F elavatva ∧ H2 .F elavatva=H3 .F elavatva ∧ (H1 × H2 × H3 ) ∧ H1 .Haj o´n´ ev6=H2 .Haj o´n´ ev ∧ H2 .Haj o´n´ ev6=H3 .Haj o´n´ ev ∧ ∧ H1 .Haj o´n´ ev6=H2 .Haj o´n´ ev

3.3.11. Feladat. Az 1921-es washingtoni egyezmény betiltotta a 35000 tonnánál súlyosabb hajókat. Adjuk meg azokat a hajókat, amelyek megszegték az egyezményt. 



πHaj o´n´ev σV ´ızkiszor´ıt´as>35000 ∧ F elavatva≥1921 (H ./ HO)

3.3.12. Feladat. Adjuk meg azokat a hajókat, amelyek „újjáéledtek”, azaz egyszer már megsérültek egy csatában, de egy későbbi csatában újra harcoltak. 

πK1 .Haj o´n´ev σK1 .Eredm´eny= ok 0

0

0

0

∧ K2 .Eredm´ eny= s´ er¨ ult ∧ ∧ K1 .Haj o´n´ ev=K2 .Haj o´n´ ev ∧ ∧ Cs1 .D´ atum>Cs2 .D´ atum





(K1 ./ Cs1 ) × (K2 ./ Cs2 )

3.3.13. Feladat. Adjuk meg azokat az osztályokat, amelyekbe csak egyetlenegy hajó tartozik. 



πOszt´aly (H) − πH1 .Oszt´aly σH1 .Oszt´aly=H2 .Oszt´aly ∧ H1 .Haj o´n´ev6=H2 .Haj o´n´ev (H1 × H2 ) 3.3.14. Feladat. Évenkénti bontásban hány hajót avattak? Nem lehet megoldani, csak kiterjesztett relációs algebrával. 3.3.15. Feladat. Mely hajóosztályból, mikor avatták az utolsó hajót? Nem lehet megoldani.

3.4. Hajók (SQL) -- [3.3.1.] Melyek azok a hajók, amelyeket 1921 el˝ ott avattak fel? SELECT hajónév FROM hajók WHERE felavatva < 1921; -- [3.3.2.] Adjuk meg azokat a hajóosztályokat a gyártó országok -nevével együtt, amelyeknek az ágyúi legalább 16-os kaliber˝ uek. SELECT osztály, ország FROM hajóosztályok where kaliber >= 16;

16

3. Feladatok

-- [3.3.3.] Adja meg a Denmark Strait-csatában elsüllyedt hajók nevét. SELECT hajónév FROM kimenetelek WHERE eredmény = ’elsüllyedt’ AND csatanév = ’Denmark Strait’; -- [3.3.4.] Adjuk meg az adatbázisban szerepl˝ o összes hadihajó nevét. SELECT hajónév FROM kimenetelek UNION SELECT hajónév FROM hajók; -- [3.3.5.] Melyek azok az országok, amelyeknek csatahajóik -és cirkálóhajóik is voltak? SELECT ország FROM hajóosztályok WHERE típus = ’bb’ INTERSECT SELECT ország FROM hajóosztályok WHERE típus = ’bc’; -- [3.3.7.] Adjuk meg a Guadalcanal csatában részt vett hajók nevét, -vízkiszorítását és ágyúinak számát. SELECT hajónév, ágyúkszáma, vízkiszorítás FROM kimenetelek NATURAL JOIN hajók NATURAL JOIN hajóosztályok WHERE csatanév = ’Guadalcanal’; -- [3.3.12.] Adjuk meg azokat a hajókat, amelyek ,,újjáéledtek’’, -azaz egyszer már megsérültek egy csatában, de egy kés˝ obbi -csatában újra harcoltak. SELECT k1.hajónév FROM (kimenetelek k1 NATURAL JOIN csaták cs1) CROSS JOIN (kimenetelek k2 NATURAL JOIN csaták cs2) WHERE k1.eredmény = ’ok’ AND k2.eredmény = ’sérült’ AND k1.hajónév = k2.hajónév AND cs1.dátum > cs2.dátum; -- [3.3.13.] Adjuk meg azokat az osztályokat, amelyekbe csak egyetlenegy -hajó tartozik. SELECT osztály FROM hajók MINUS SELECT h1.osztály FROM hajók h1, hajók h2 WHERE h1.osztály = h2.osztály AND h1.hajónév != h2.hajónév; -- [3.3.14.] Évenkénti bontásban hány hajót avattak? SELECT felavatva, COUNT(*) FROM hajók GROUP BY felavatva; -- [3.3.15.] Mely hajóosztályból, mikor avatták az utolsó hajót? SELECT osztály, MAX(felavatva) FROM hajók GROUP BY osztály;

3.5. Minta ZH.

17

3.5. Minta ZH. Adottak a következő relációsémák: szigsz, vezetéknév, keresztnév, életévek) Családtag(szigsz Házasság(házid házid, ffiszig, nőiszig, vendégekszáma) szigsz, gyerekszig gyerekszig) Szülő(szigsz Járt_ott(szigsz szigsz, házid házid, ajándékdb) 3.5.1. Feladat. Ki(k) a legfiatalabb családtagok? 





πszigsz Cs − πCs2 σCs1 .´elet´evek


πf f iszig σvend´egeksz´ama≤50 ∧ vend´egeksz´ama≥20 (H) 3.5.3. Feladat. Ki(k)nek van Gergő nevű unokájuk? 





πSz1 .szigsz σk.n´ev=0 Gergő Sz1 ./ Sz1 .gyerekszig=Sz2 .szigsz (Sz2 ./ Sz2 .gyerekszig=Cs.szigsz Cs) 0





πSz2 .szigsz σSz2 .gyerekszig=Sz1 .szigsz (Sz1 × Sz2 ) ./ Sz1 .gyerekszig=Cs.szigsz ∧ Cs) ∧ Cs.k.n´ ev=0 Gergő0

3.5.4. Feladat. Ki járt minden olyan esküvőn, ahol ’Kiss Éva’ is? 

πszigsz,h´azid (J) ÷ πh´azid σv.n´ev=0 Kiss0 ∧ k.n´ev=0 Eva ´ 0 (Cs) ./ J



3.5.5. Feladat. Melyik férfi esküvőjén járt minden ’Öreg János’-nál idősebb családtag? 

πf f iszig πszigsz,h´azid (J) ÷ πCs2 .szigsz (Cs1 ./ Cs1 .v.n´ev=0 Oreg 0 ¨ ∧ Cs1 .k.n´ ev=0 J a ´nos0 ∧ Cs2 ) ./ H ∧ Cs1 .kor
3.5.6. Feladat. Kinek (név) van pontosan egy gyereke? 



πszigsz (Sz) − πSz1 .szigsz σ

(Sz1 Sz1 .szigsz=Sz2 .szigsz ∧ ∧ Sz1 .gyerekszig6=Sz2 .gyerekszig



× Sz2 )

./ Cs

3.5.7. Feladat. Ki (szigsz, vezetéknév) nem adta egyik gyerekének sem a ’László’ keresztnevet? SELECT szigsz, vezetéknév FROM családtag NATURAL JOIN (SELECT szigsz FROM szül˝ o WHERE gyerekszig NOT IN (SELECT szigsz FROM családtag WHERE keresztnév = ’László’));

3.5.8. Feladat. Ki(k) az(ok) (név), aki(k) minden esküvőre, ahova elment(ek), annyi ajándékot vitt(ek), aminél soha senki nem vitt többet (azaz legtöbbet)? SELECT vezetéknév, keresztnév FROM családtag NATURAL JOIN (SELECT szigsz FROM járt_ott WHERE ajándékdb = (SELECT MAX(ajándékdb) FROM járt_ott));



18

3. Feladatok

3.5.9. Feladat. Ki járt minden olyan esküvőn, ahol járt minden olyan családtag, akinek van ’Anita’ nevű gyereke? A megoldáshoz két osztást használunk. SELECT szigsz FROM járt_ott -- els˝ o osztás kezdete MINUS SELECT szigsz FROM ( SELECT * FROM (SELECT szigsz FROM járt_ott) CROSS JOIN (SELECT házid FROM járt_ott -- második osztás MINUS SELECT házid FROM ( SELECT * FROM (SELECT házid FROM járt_ott) CROSS JOIN (SELECT szigsz FROM szül˝ o WHERE gyerekszig IN (SELECT szigsz FROM családtag WHERE vezetéknév = ’Anita’)) MINUS SELECT házid, szigsz FROM járt_ott)) MINUS SELECT házid, szigsz FROM járt_ott); 



3.5.10. Feladat. πszigsz,v.n´ev (Cs) ÷ ρJH(szigsz) πf f iszig σaj´and´ekdb>5 (J) ./ H



SELECT vezetéknév FROM családtag MINUS SELECT vezetéknév FROM ( SELECT * FROM (SELECT vezetéknév FROM családtag) CROSS JOIN (SELECT ffiszig szigsz FROM ((SELECT * FROM járt_ott WHERE ajándékdb > 5) NATURAL JOIN házasság)) MINUS SELECT vezetéknév, szigsz FROM családtag);

3.5.11. Feladat. Táblázatos formában írja ki, hogy egy gyerek szüleinek az esküvőjén hány vendég volt! (gyerekszig, vendégekszáma) -- A gyereknek egy vagy kett˝ o szül˝ oje van (tágabb) SELECT DISTINCT gyerekszig, vendégekszáma FROM szül˝ o JOIN házasság ON szigsz = n˝ oiszig OR szigsz = ffiszig; -- A gyereknek pontosan kett˝ o szül˝ oje van (sz˝ ukebb) SELECT gyerek gyerekszig, vendégekszáma FROM házasság NATURAL JOIN ( SELECT sz1.szigsz ffiszig, sz2.szigsz n˝ oiszig, sz1.gyerekszig gyerek FROM szül˝ o sz1 CROSS JOIN szül˝ o sz2 WHERE sz1.gyerekszig = sz2.gyerekszig AND sz1.szigsz != sz2.szigsz);

FORRÁSOK

[1] Hajas Cs.: Előadás. ELTE IK, 2013. http://people.inf.elte.hu/sila/AB1EA/AB1_eloadasanyag.html [2] Balogh T.: Előadás és gyakorlat jegyzet (Brányi L. gyakorlatai, illetve Hajas Cs. előadásai alapján). ELTE IK, 2013. március 20. http://baloghtamas.hu/download/gyakorlatadatb.pdf [3] Bekő T.: Gyakorlat jegyzet (Brányi L. gyakorlatai alapján). ELTE IK, 2013.