Az ER modell Az ER modell komponensei (típus + előfordulás) 1. Egyed 2. Tulajdonság (Attribútum) egyértékű – többértékű egyszerű – összetett forrás – származtatott kulcs attribútum (gyenge egyed) 3. Kapcsolat kapcsolat foka: résztvevő egyedtípusok száma bináris kapcsolat esetén kapcsolat típusa 1:1 vagy 1:N vagy N:M lehetnek saját attribútumai kapcsolat erőssége (totális, parciális) létezésfüggő kapcsolat (gyenge egyed) 1. feladat: Döntsük el a felsorolt fogalmakról, hogy egyedtípus, vagy egyed előfordulás-e, vagy esetleg mindkettő? teknősbéka Kiss Géza virág muskátli kertész cipő 2. feladat: Milyen típusúak a következő attribútumok? kedvenc illatszer lakcím (irányítószám, város, utca, házszám), e-mail cím születési dátum jogosítvány (kategória szerint), kedvenc sütemény ár tanulmányi átlag ÁBRÁZOLÁS erős egyedtípus
név
tulajdonság típus
név
gyenge egyedtípus
név
többértékű tulajdonság típus
név
kapcsolat típus
név
származtatott tulajdonság típus
név
azonosító jellegű kapcsolat
név
elsődleges kulcs
totális kapcsolat
parciális kapcsolat 1
tulajdonság
3. feladat: Milyen típusúak a következő kapcsolatok, szemléltesd ábra segítségével! OLVAS (olvasó, könyv) AJÁNDÉKOZ (ember ajándék, ember) RAJZOL (óvodás, rajzeszköz, rajz) FIZET (tanuló. oktató, időtartam) LAKIK (albérlet, egyetemista) TANÍTJA (tantárgy, oktató) SZÁLLÍT (pizzafutár, megrendelő) ELMOND (mese, mesemondó) 4. feladat: Modellezés a. Embereket tartalmazó adatbázist kell készítenünk. Tárolni kell az emberek igazolvány-számát, nevét és hobbiját. b. Készítsünk egy kutyákat és gazdikat tartalmazó adatbázist. Tárolandó a gazda igazolványszáma, neve, címe, a kutya neve és fajtája. Minden kutyának pontosan egy gazdája van, de egy gazdának több kutyája is lehet. c. Egy vállalat dolgozóit különböző tanfolyamokra küldi. Tárolni kell a dolgozók igazolványszámát, nevét, beosztását és fizetését, a tanfolyamok kódját, nevét, óraszámát, és részvételi díját. Egy dolgozó több tanfolyamon is részt vehet, illetve egy tanfolyamra több dolgozó is mehet. Tárolni kell még, hogy melyik dolgozó melyik tanfolyamon milyen eredményt ért el. Azt adatbázis a vállalat minden dolgozóját tartalmazza, azokat is, akik még nem voltak semmilyen tanfolyamon. Minden tanfolyamot tárolunk, azokat is, amelyeken még egy dolgozó sem vett részt. d. Tárgyfelvétellel kapcsolatos adatbázist kell terveznünk. Tárolni kell a hallgatók kódját és nevét, a tantárgyak kódját, nevét és óraszámát, az oktató kódját és nevét, akinél a tárgy felvehető. Tárolni kell ezen kívül, hogy ki milyen tárgyat, kinél, milyen időpontban vett fel. Természetesen egy tárgyat többen is felvehetnek, egy hallgató több tárgyat is felvesz, illetve egy tárgyat többen oktatnak. e. Készítsünk egy festmények adatait tartalmazó adatbázist. A képnek van címe, mérete, vászon- és festékanyaga (léteznek azonos című képek). A festőknek van neve, anyja neve, nemzetisége, születési és halálozási éve (a még élő festőknél ez ideiglenesen kitöltetlen marad). A kép tulajdonosának van kódja, neve és városa, ahol lakik. Tároljuk még a festés, illetve a tulajdonjog megszerzésének dátumát. Minden festő festett képet, minden képet festett és birtokol is valaki, minden tulajdonosnak van képe. Egy festő több képet is festhetett illetve egytulajdonos több képet is birtokolhat. Minden képnek egy festője és egy birtokosa van. f. Hozzunk létre egy kórházi adatbázist. A betegek különböző betegségek miatt kezelik, különböző gyógyszerekkel, bizonyos orvosok. A beteget több orvos is kezelheti, több betegsége is lehet, többféle gyógyszert is szedhet. Az adatbázisból ki kell tudni olvasni, hogy kit, milyen betegségre, melyik orvos, milyen gyógyszerrel kezelt. Tárolni kell a beteg TAJ számát, nevét, születési dátumát, címét, a betegségének a nevét, lappangási idejét, fertőző jellegét, az orvos kódját, nevét, beosztását, az osztály nevét, ahol dolgozik, az emeletet, ahol az osztály található, az osztály főorvosát, és a kinevezése dátumát. Minden orvos egy osztályon dolgozik, de egy osztályon többen is dolgozhatnak. Minden osztálynak pontosan egy főorvosa van. Tárolni kel még a gyógyszerek nevét, kivitelét, mellékhatásait, forgalmazójának nevét, címét, telefonját, Egy forgalmazó több gyógyszert is forgalmaz, illetve egy gyógyszer több forgalmazótól is beszerezhető 2
ER-modell leképezése relációs modellre ER-modell
Relációs modell
egyedtípus egyedelőfordulás tulajdonság típus összetett tulajdonság kapcsolat erős egyedtípus
reláció rekord, sor mező, sor minden komponensből külön mező reláció, vagy relációk, külső kulcs Reláció, melynek mezői az egyedhez tartozó tulajdonságtípusok. Az erős egyedtípushoz hasonló, még hozzá kell venni a vele létezésfüggő kapcsolatot alkotó erős egyedtípus kulcs-attribútumait. elsődleges kulcs Két reláció. Az első tartalmazza az egyedtípus attribútumait kivéve a többértékűt. A második a többértékű attribútumot és a kulcsot. Ezen reláció elsődleges kulcsa az összes attribútum. Az eredeti kulcs külső kulcsként funkcionál és összeköti a két relációt.
gyenge egyedtípus kulcsattribútum többértékű attribútum 1:1 kapcsolat
totális-totális
totális-parciális
parciális-parciális
A kapcsolatban résztevő egyedekhez tartozó relációkból egyet készítünk az összes attribútum felhasználásával. Az új kulcs valamelyik elsődleges kulcs lesz. Ha valamelyik egyed másik relációban is szerepel, akkor célszerű meghagyni a két relációt. Az egyikhez hozzávesszük a másik elsődleges kulcsát, mely külső kulcsként biztosít kapcsolatot a két tábla között. A totális részvételű relációt kiegészítjük a másik elsődleges kulcsával, mely külső kulcs lesz. A kapcsolat attribútumai is ide kerülnek. Új relációt hozunk létre, melyben a kapcsolat attribútumai és a kapcsolatban résztvevő relációk elsődleges kulcsai szerepelnek, melyek egyben külső kulcsok is. Az új reláció elsődleges kulcsa valamelyik résztvevő elsődleges kulcs, vagy ezek kombinációja.
1:N kapcsolat totális N parciális N
N:M kapcsolat n ágú kapcsolat
Az N-oldali relációt kiegészítjük a kapcsolat attribútumaival és a másik reláció elsődleges kulcsával, mely külső kulcs lesz. Új relációt hozunk létre, melyben szerepelnek a két elsődleges kulcs (külső kulcsként funkcionálnak) és a kapcsolat attribútumai. A reláció elsődleges kulcsa az N-oldal elsődleges kulcs lesz. Új relációt hozunk létre, melyben a kapcsolat attribútumai és a kapcsolatban résztvevő relációk elsődleges kulcsai szerepelnek, melyek egyben külső kulcsok is. Az új reláció elsődleges kulcsa a résztvevők elsődleges kulcsaiból képzett összetett kulcs. Ugyanúgy mint az előbbit.
5. feladat: Add meg az előző feladathoz tartozó relációs modelljét!
3
Normalizálás Problémák redundancia Karbantartási anomáliák: törlés, beszúrás, módosítás Normál formák 1 NF: Nincs többértékű attribútum 2 NF: 1 NF + minden leíró attribútum teljesen függ az elsődleges kulcstól, azaz nincs benne részleges függés 3 NF: 2 NF + a leíró attribútumok között nincs funkcionális függés (belső függés).
6. feladat: KÓRHÁZ (beteg azonosító, beteg neve, betegcíme, betegség, osztály azonosító, osztály név, főorvos, gyógyszer) 7. feladat: EGYETEM (neptun kód, név, város, tantárgy, időpont, terem, oktató, nyelvvizsga) 8. feladat: RECEPT(azonosító, elnevezés, ár, típus, típusnév, anyagkód, anyagnév, egységár, mértékegység, mennyiség) SZÁMLA (számlaszám, dátum, tételazonosító, elnevezés, ár, mennyiség, érték, végösszeg) 9. feladat: KUTYATARTÁS (igsz, gazdinév, gazdicím, kutyanév, kutyafajta, kutyakód) 10. feladat: TANFOLYAM (igsz, d.név, beosztás, fizetés, t.kód, t.név,díj, óraszám, eredmény) 11. feladat: TÁRGYFELVÉTEL (h.kód, h.név, időpont, k.kód, óraszám, k.név, o.kód, o.név) 12. feladat: FESTMÉNYEK (f.név, a.név, nemzet, szülév, halév, időpont, f.cím, méret, vászon, festék, mióta, t.kód, t.név, város) 13. feladat: ELŐADÁS (előadás neve, előadás ideje, színész neve, színész kódja, színész fizetése, rendező, kategória, jegyár, hely)
4
A relációs algebra Műveletei szelekció ( ) projekció ( ) átnevezés ( ) hányados (÷) halmazműveletek o unió ( ), metszet ( ), különbség ( ) o Descartes-szorzat (belső szorzat, ) összekapcsolás (join) o általános összekapcsolás (theta join, feltétel) o egyenlőségen alapú összekapcsolás (equijoin); o természetes összekapcsolás (natural join, ) Adottak a következő táblák: AUTÓ Márka Opel Ford Opel Porshe Mercedes Mercedes Suzuki Suzuki Suzuki Ford Ford Hummer BMW Audi
Típus Astra Ka Meriva 911 Vito Vaneo Liana Alto Swift Mondeo Focus H3 7 A3
Gyártási szám 1234A 3575G 3689V 6661K 4599L 4935K 4936H 6943R 2697T 3691S 3692F 1257G 1639U 4672Z
Gyártási év 2003 1998 2001 2007 2004 2003 2000 2003 1999 2007 2009 2008 2007 2006
Tulajdonosok száma 1 3 4 1 2 1 1 2 4 2 1 1 1 2
Tulajdonos azonosítója a1 null a2 a1 null a3 a4 null a2 null a4 a3 a1 null
EMBER Azonosító a1 a2 a3 a4 a5
Vezetéknév Kiss Nagy Kovács Szabó Nagy
Keresztnév Béla Imre Géza Gréta Bálint
KERESKEDŐ Név Autóker Autóház Kocsilak
Város Debrecen Pécs Budapest Budapest Szeged
Tervezett vételár 3 000 000 null 800 000 null 1 200 000
ÁRUL Telephely Budapest Szeged Pécs
Hitel nincs van van
Gyártási szám 3575G 4599L 6943R 3691S 4672Z 5
Kereskedő Autóker Autóház Autóker Kocsilak Kocsilak
Ár 500 000 2 000 000 400 000 3 500 000 3 550 000
14. feladat:Fogalmazzuk meg a relációs algebra nyelvén az alábbiakat, és határozzuk meg a műveletek eredményét! a. 2007-ben gyártott autók b. A debreceni, autót venni készülő emberek listája c. 2000-nél korábban gyártott, vagy legfeljebb 2 tulajdonossal rendelkező autók listája d. Az Autóker kereskedő adatai e. A tulajdonos nélküli autók listája f. Az adatbázisban szereplő emberek vezeték és keresztneve g. Az adatbázisban szereplő autó-márkák listája h. A Budapesti emberek azonosítója és keresztneve i. Hitelt biztosító kereskedők telephelye j. Tulajdonossal nem rendelkező Ford márkájú autók gyártási száma, és gyártási éve k. Legalább 1 000 000 Ft értékben tervező autóvásárlók vezeték és keresztneve l. Az ÁRUL és a KERESKEDŐ táblák Descartes-szorzata m. Kapcsold össze az EMBER és az ÁRUL táblákat. Kapcsolási feltétel: az EMBER táblában szereplő tervezett vételár kisebb vagy egyenlő mint az ÁRUL táblában szereplő ár. Értelmezzük az eredményt! n. Határozzuk meg, hogy mely kereskedő, milyen autókat árul (a KERESKEDŐ és az ÁRUL táblák összekapcsolása). o. Határozzuk meg, hogy az egyes autókat melyik kereskedőnél tudjuk beszerezni! (Az AUTÓ és az ÁRUL táblák természetes összekapcsolása) p. A D feladat eredménytáblájának és a KERESKEDŐ táblájának uniója, metszete és különbsége q. Add meg azon kereskedők nevét, akik árulnak 2003 évben gyártott, egy tulajdonossal rendelkező autókat. r. Határozzuk meg, hogy Kiss Béla milyen márkájú és típusú autókat birtokol! s. Határozzuk meg azon budapesti emberek vezeték- és keresztnevét, akiknek Mercedes-ük van! t. Nagy Bálint melyik kereskedőnél, milyen áron találhat magának autót, mik az esélyes autók adatai?
6
Olimpia adatbázis tábláinak modellje
7
A select utasítás SELECT szelekciós_lista FROM táblalista [WHERE logikai_oszlopkifejezés] [GROUP BY csoportosító_oszlopkifejezés_lista] [HAVING logikai_oszlopkifejezés] [ORDER BY rendező_oszlopkifejezés_lista]; ASC: növekvő DESC: csökkenő
15. feladat:Listázzuk ki az egyéni versenyzők nevét, illetve azokét, akiknél nincs megadva születési dátum! 16. feladat:Listázzuk ki azokat a férfi versenyszámokat, amelyeknél a sportágazonosító 328 és azokat a női versenyszámokat, ahol a sportágazonosító 314. 17. feladat:Listázzuk ki az erem táblából azokat az országazonosítókat, amelyeknél az érmek összege több, mint 15. 18. feladat:Listázzuk ki azon versenyzők adatait, akik nem csapatban versenyeznek! 19. feladat:Listázzuk ki azon országok azonosítóját, akik legfeljebb 5 arany, 3 ezüst és 7 bronz érmet szereztek! 20. feladat:Listázzuk ki azon országok eredményeit, akik 6-10 arany érmet szereztek! 21. feladat:Rendezzük az orszagok táblát az országok nevei alapján! 22. feladat:Rendezzük az erem_tabla táblát a megszerzett arany, majd ezüst érmek alapján növekvő sorrendbe! 23. feladat:Rendezzük az erem_tabla táblát úgy, hogy az egyes országok adatai az általuk szerzett érmék szerinti csökkenő sorrendben jelenjen meg! 24. feladat:Listázzuk ki a férfi versenyszámokat, majd rendezzük az adatokat névsor szerint! 25. feladat:Listázzuk ki az erem táblából azokat az országazonosítókat, amelyeknél több arany érem van, mint bronz. A lista az aranyérmek szerint csökkenően legyen rendezve. 26. feladat:Listázzuk ki a 100 és 1000 km2 közötti területtel rendelkező országok nevét és fővárosát úgy, hogy a legtöbb lakossal rendelkező legyen az első, és a legkevesebb lakosú ország pedig az utolsó! 27. feladat:Listázzuk ki a versenyzők nevét, születési idejét, valamint születési helyét! Az eredmény táblázat fejlécei név, születési idő és születési hely legyen csupa kisbetűkkel. 28. feladat:Jelenítsük meg az országok azonosítóit és mellette, hogy mennyi érmet szereztek! 29. feladat:Jelenítsük meg az országok nevét, fővárosát, földrészét és népsűrűségét, ez utóbbi oszlop neve népsűrűség legyen! 30. feladat:Listázzuk ki a versenyszámok adatait úgy, hogy a versenyszám típusa (férfi/női) csupa nagybetűkkel jelenjen meg! 31. feladat:Jelenítsük meg a versenyzők nevét és azt, hogy hány karakterből áll! 32. feladat:Kérdezzük le a rendszerdátumot! 33. feladat:Milyen nap van ma? 8
34. feladat:Milyen napon születtél? 35. feladat:Hány napos vagy? 36. feladat:Hány hónapos vagy? 37. feladat:Hány éves vagy? 38. feladat:Listázzuk ki a versenyzők nevét, születési dátumát, milyen napon született és a korát! 39. feladat:Határozzuk meg az adatbázisban szereplő országok számát, maximális és minimális területét, valamint összlakosságát! 40. feladat:Vegyük az országok és a sportágak Descartes-szorzatát és rendezzük ország szerint növekvően! 41. feladat:Listázzuk ki a versenyzők nevét, születési dátumát, országát és fővárosát. 42. feladat:Listázzuk ki az 50000 km2-nél kisebb országok érmeit! 43. feladat:Listázzuk ki az 1162-es azonosítóval rendelkező csapat tagjainak adatait! 44. feladat:Listázzuk ki a 910-es azonosítóval rendelkező versenyző csapatait! 45. feladat:Magyarország egyéni versenyzőinek a nevét és születési dátumát listázzuk ki születési idő szerint csökkenően rendezve. 46. feladat:Listázzuk ki az európai országok érem eredményeit arany, ezüst és bronz szerint is csökkenően rendezve. 47. feladat:Listázzuk ki a magyar első helyezettek nevét, név szerint rendezve. (versenyszám nélkül) 48. feladat:Listázzuk ki az öt arany érmet szerző országok egyéni versenyzőinek nevét, korát és országát! 49. feladat:Listázzuk ki Cseh László eredményeit, azt is, hogy melyik versenyszámban, melyik sportágban szerezte. 50. feladat:Számoljuk meg, hogy sportágazonosítónként hány versenyszám van. 51. feladat:Földrészenként írjuk ki az országok számát, a legnagyobb és legkisebb területet, a lakosok összegét és átlagát. 52. feladat:Számoljuk meg, hány versenyszám tartozik a 304, 324, és a 328-as sportágakhoz. 53. feladat:Átlagosan hány fősek a csapatok?
9
DDL utasítások Táblák létrehozása CREATE TABLE táblanév (oszlopnév adattípus [oszlopmegszorítások] [,oszlopnév adattípus [oszlopmegszorítások]…] [, táblamegszorítások]); Adattípusok: NUMBER(m,[t]) VARCHAR2(n) DATE Oszlopmegszorítások NULL NOT NULL UNIQUE PRIMARY KEY Táblamegszorítások CONSTRAINT név PRIMARY KEY (oszlopnév[, oszlopnév,…]) CONSTRAINT név FOREIGN KEY (oszlopnév[, oszlopnév,…]) REFERENCES táblanév(oszlopnév[, oszlopnév,…]) CONSTRAINT név UNIQUE (oszlopnév[, oszlopnév,…])
Megszorítás hozzáadása ALTER TABLE táblanév ADD megszorítás;
Megszorítás törlése ALTER TABLE táblanév DROP CONSTRAINT megszorításnév;
Tábla törlése DROP TABLE táblanév;
54. feladat: Hozzuk létre a kórház adatbázist! 55. feladat:Töltsük fel a kórház adatbázist néhány adattal! 56. feladat:Töröljük a kórház adatbázist!
10
