Informatika | Adatbázisok » Orosz Dénes Milán - Adatbázis rendszerek

Jegyzőkönyv Adatbázis rendszerek I. Féléves feladat Készítette: Orosz Dénes Milán Neptunkód: HC1Y8Y Gyakorlat: Csütörtök 8-10 Gyak. vezető: Szűcs Miklós Általános bemutatás Az általam választott adatbázis egy szolgáltató hibakezelő részlegének az adatbázisa lenne, amiben megtekinthető, hogy melyik ügyfélnek mikor és milyen problémája volt, melyik kolléga kezelte a hibáját, ha több kollégával is beszélt, akkor lekérdezhető legyen az összes kolléga, megtekinthető legyen, hogy milyen formában érkezett a bejelentés, hogy a vezetőség foglalkozott-e az adott hibával, illetve, hogy milyen gyakorisággal fordul elő ez a hiba és meddig tart. Az ER modell 4 egyedet tartalmaz: Hiba, Ügyfél, Ügyintéző és Vezető. A modellben nincs 1:1 kapcsolat az egyedek között ugyanis több ügyfél tapasztalhatja ugyanazt a hibát, és ez fordítva is igaz. Szintén fennáll a lehetősége annak, hogy egy ügyintéző több ügyfélnek a

hibáját is kezeli, ahogy több ügyfél beszélhet ugyanazzal az ügyintézővel. Az ügyintézőnek egy főnöke van a vezetőségből, viszont egy vezetőségi tag több ügyintéző kollégáért felelős, illetve egy ügyfél, ha a vezetőséggel kommunikál, akkor több vezetőségi tag is beszélhet vele a problémájának megfelelően és egy vezetőségi tag, akár több ügyféllel is foglalkozhat. Egyedek: Hiba egyed: Az egyed tartalmaz egy összetett Időtartam tulajdonságot, ami a Mióta és Hányszor résztulajdonságokat tartalmazza, egy Azonosító kulcstulajdonságot (ez alapján azonosítható egy-egy hiba) és egy Hibaüzenet (egy hibának lehet több hibaüzenet is) többértékű tulajdonságot. Ügyfél egyed: Az egyed tartalmaz egy Azonosító kulcstulajdonságot (ez alapján azonosítható egy-egy ügyfél) és tartalmaz Születési Idő, Születési Hely, Név és Kód (minden ügyfél rendelkezik egy ügyfélkóddal) normáltulajdonságokat. Ügyintéző

egyed: Az egyed tartalmaz egy Azonosító kulcstulajdonságot (ez alapján azonosítható egy- egy ügyintéző) és tartalmaz Szint (ez mutatja meg, hogy az ügyintéző mennyire képzett), Név és Beosztás (ez mutatja, hogy az ügyintéző melyik részlegen lát el feladatot) normál tulajdonságokat. Vezető egyed: Az egyed tartalmaz egy Azonosító kulcstulajdonságot (ez alapján azonosítható egy-egy vezető) és tartalmaz Név, Beosztás (ez a tulajdonság mutatja ,hogy a vezető melyik részlegen lát el feladatot) és Szint (ez mutatja, hogy a vezető mennyire képzett) normál tulajdonságokat. Kapcsolatok: H-Ü kapcsolatok: TV: n:n kapcsolat. Tartalmaz egy Hibaidő és egy Gyakoriság származtatott tulajdonságot, amiből megállapítható, az adott tv hiba mennyi ideje tart és milyen gyakorisággal fordul elő. 1. Telefon: n:n kapcsolat. Tartalmaz egy Hibaidő és egy Gyakoriság származtatott tulajdonságot, amiből megállapítható, az adott telefon hiba

mennyi ideje tart és milyen gyakorisággal fordul elő. 2. Internet: n:n kapcsolat. Tartalmaz egy Hibaidő és egy Gyakoriság származtatott tulajdonságot, amiből megállapítható, az adott internet hiba mennyi ideje tart és milyen gyakorisággal fordul elő. 3. Ü-Ü kapcsolat: Telefon: n:n kapcsolat. Tartalmaz egy Idő összetett tulajdonságot, ami a Mettől és a Meddig résztulajdonságokat tartalmazza és megmutatja, hogy a telefonhívás mennyi ideig tartott. Ügyfél-V kapcsolat: Email: n:n kapcsolat. Megmutatja, hogy az ügyfél interneten küldött email formában jelenti be a hibát, vagy a vezetőségi tag email formában keresi fel az ügyfelet! 1. Levél: n:n kapcsolat. Megmutatja, hogy az ügyfél kézzel aláírt levél formában jelenti be a hibát, vagy a vezetőségi tag levél formában keresi fel az ügyfelet! 2. Ügyintéző-V Kapcsolat: Telefon: n:1 kapcsolat. Tartalmaz egy Idő összetett tulajdonságot, ami a Mettől és a Meddig

résztulajdonságokat tartalmazza és megmutatja, hogy a telefonhívás mennyi ideig tartott. 1. Email: n:1 kapcsolat. Megmutatja, hogy az ügyintéző levelezőrendszeren keresztül emailben kereste fel a felettesét, vagy fordítva! 2. A táblák létrehozása: Elsőnek a 4 egyed tábláját hozzuk létre, majd a többit, az idegen kulcsok miatt. Fontos figyelni rá, hogy a Vezető tábla hamarabb készüljön el, mint az ügyintéző tábla, ugyanis van idegen kulcs ami, a vezető táblából mutat az ügyintéző táblára. Create table Hiba ( Azonosító int primary key not null, Mióta date, Hányszor int ); Create table Ügyfél ( Azonosító int primary key not null, Név varchar(20), Kód int, Szülhely varchar(20), Szülidő date ); Create table Vezető ( Azonosító int primary key not null, Név varchar(20), Szint int check (szint < 3), Beosztás varchar(20) ); Create table Ügyintéző ( Azonosító int primary key not null, Név varchar(20), Szint int default

1 check (szint < 3), Beosztás varchar(20), Email int, foreign key (email) reference Vezető(Azonosító) ); Ezután jön a többi tábla létrehozása, a telefon táblákhoz hozzá kellett adnom a két hozzátartozó egyed kezdőbetűjét ugyanis két ugyan olyan nevű tábla nem létezhet, itt zárójelben jelzem, hogy melyik ez a két egyed. Create table Email ( Üazon int, Vazon int, foreign key (Üazon) reference Ügyfél(Azonosító), foreign key (Vazon) reference Vezető(Azonosító) ); Create table Hibaüzenetek ( Hibaüzenet char(20); Azonosító int, foreign key (Azonosító) reference Hiba(Azonosító) ); Create table Internet ( Üazon int, Hazon int, foreign key (Üazon) reference Ügyfél(Azonosító), foreign key (Hazon) reference Hiba(Azonosító) ); Create table Levél ( Üazon int, Hazon int, foreign key (Üazon) reference Ügyfél(Azonosító), foreign key (Hazon) reference Hiba(Azonosító) ); Create table TelefonHÜ (Hiba és Ügyfél) ( Hazon int, Üazon

int, foreign key (Hazon) reference Hiba(Azonosító), foreign key (Üazon) reference Ügyfél(Azonosító) ); Create table TelefonÜV (Ügyintéző és Vezető) ( Ügyiazon int, Vazon int, Mettől number check (mettől < 60), Meddig number check (meddig < 60), foreign key (Ügyiazon) reference Ügyintéző(Azonosító), foreign key (Vazon) reference Vezető(Azonosító) ); Create table TelefonÜÜ (Ügyfél és Ügyintéző) ( Üazon int, Ügyiazon int, Mettől number check (mettől < 60), Meddig number check (meddig < 60), foreign key (Üazon) reference Ügyfél(Azonosító), foreign key (Ügyiazon) reference Ügyintéző(Azonosító) ); Create table TV ( Üazon int, Hazon int, foreign key (Üazon) reference Ügyfél(Azonosító), foreign key (Hazon) reference Hiba(Azonosító) ); A táblák feltöltése: A hiba táblába betöltünk 5 különböző hibát: begin Insert Into Hiba Values(1, 10/21/2019, 3); Insert Into Hiba Values(2, 04/12/2020, 2); Insert Into Hiba

Values(3, 10/27/2019, 1); Insert Into Hiba Values(4, 08/02/2020, 6); Insert Into Hiba Values(5, 05/08/2020, 11); end; Az ügyfél táblába is betöltünk 5 különböző ügyfelet: begin Insert Into Ügyfél Values(1, Kiss János, 100, Miskolc,04/10/1992); Insert Into Ügyfél Values(2, Szende Pál, 101, Nyiregyháza, 02/10/1988); Insert Into Ügyfél Values(3, Nagy Benedek, 102, Eger, 11/10/1967); Insert Into Ügyfél Values(4, Sipos Petra, 103, Miskolc,03/12/1994); Insert Into Ügyfél Values(5, Kovács Tamás, 104, Debrecen,06/16/1999); end; A vezető táblába is betöltünk 5 különböző vezetőt: begin Insert Into Vezető Values(1000, Boldog Péter,csoport vezető, default); Insert Into Vezető Values(1001, Dom Edit,csoport vezető, 2); Insert Into Vezető Values(1002, Kovács Miklós,csoport vezető, default); Insert Into Vezető Values(1003, Lakatos Imre,csoport vezető,2); Insert Into Vezető Values(1004, Elk Péter,csoport vezető, default); end; Az ügyintéző táblába

is betöltünk 5 különböző ügyintézőt: begin Insert Into Ügyintéző Values(1, Varr Tamás, operátor, 1000, default); Insert Into Ügyintéző Values(2, Tamás Imre, operátor, 1001, 2); Insert Into Ügyintéző Values(3, Nagy Elek, operátor, 1002, 2); Insert Into Ügyintéző Values(4, Sándor István, kiemelt operátor, 1003, default) ; Insert Into Ügyintéző Values(5, Miklós Iván, kiemelt operátor, 1004, 2); end; A tv táblába megadunk 5 tv hiba adatait adjuk meg. begin Insert Into Tv Values(1, 3); Insert Into Tv Values(1, 4); Insert Into Tv Values(3, 1); Insert Into Tv Values(3, 3); Insert Into Tv Values(1, 1); end; A telefonhü táblába 5 telefonhiba adatait adjuk meg. begin Insert Into TelefonHÜ Values(4, 4); Insert Into TelefonHÜ Values(1, 4); Insert Into TelefonHÜ Values(3, 4); Insert Into TelefonHÜ Values(5, 4); Insert Into TelefonHÜ Values(2, 4); end; Az internet táblába 5 internet hiba adatait adjuk meg. begin Insert Into Internet Values(2, 1);

Insert Into Internet Values(2, 2); Insert Into Internet Values(5, 3); Insert Into Internet Values(5, 4); Insert Into Internet Values(5, 5); end; A hibaüzenetek táblába behelyezzük az 5 hibaüzentet begin Insert Into Hibaüzenetek Values(1, Nincs jel); Insert Into Hibaüzenetek Values(2, Lefedettség hiba); Insert Into Hibaüzenetek Values(3, Kábelhiba); Insert Into Hibaüzenetek Values(4, Nincs kábel bedugva); Insert Into Hibaüzenetek Values(5, Nincs internet!); end; A telefonÜÜ táblába 5 telefonhívás adatait helyezzük be begin Insert Into TelefonÜÜ Values(1,1 ,10,15); Insert Into TelefonÜÜ Values(2,2 ,0,13); Insert Into TelefonÜÜ Values(3,3 ,3,22); Insert Into TelefonÜÜ Values(4,4 ,50,55); Insert Into TelefonÜÜ Values(5,5 ,34,53); end; A telefonÜV táblába 5 vezetőségi telefonhívás adatait helyezünk be. begin Insert Into TelefonÜV Values(1,1000 ,13,17); Insert Into TelefonÜV Values(2,1001 ,11,18); Insert Into TelefonÜV Values(3,1003 ,4,10); Insert

Into TelefonÜV Values(4,1004 ,45,51); Insert Into TelefonÜV Values(5,1001 ,32,37); end; A levél táblába 5 ügyfél és vezetőség közötti levelezés adatait helyezünk. begin Insert Into Levél Values(1,1000); Insert Into Levél Values(2,1001); Insert Into Levél Values(3,1003); Insert Into Levél Values(4,1002); Insert Into Levél Values(5,1004); end; Az email táblába 5 ügyfél és vezetőség közötti email adatait helyezünk. begin Insert Into Email Values(1,1000); Insert Into Email Values(2,1001); Insert Into Email Values(3,1003); Insert Into Email Values(4,1002); Insert Into Email Values(5,1004); end; A vezetőségnél előléptetés történt ezért Dom Editből osztályvezető lett Kovács Miklós pedig átvette az ő helyét. update vezető set beosztás=osztályvezető where név=Dom Edit; update vezető set szint=2 where név=Kovács Miklós; Az ügyintézőknél tanulmányok miatt Varr Tamásnak fel kellett mondania, a helyére a testvére Varr András

jelentkezett. update ügyintéző set név=Varr András where név=Varr Tamás; Az ügyfelek visszajelzése alapján 2 ügyfélnek is Miskolc lett megadva születési helynél, de ők Győr-ben születtek ,ezért kérték a javítást. update ügyfél set szülhely=Győr where szülhely=Miskolc; Lekérdezések: 1.Kiírom azoknak a nevét és születési idejét akik győrben születtek(egyszerű select) select név,szülidő from ügyfél where szülhely=Győr 2. Kiírom az összes ügyintéző adatait akik 1-es szinten dolgoznak(egyszerű select) select * from ügyintéző where szint=1; 3. Hány csoportvezető van a különböző szinteken?(csoportképzés) select beosztás count(*) "vezetők" from vezető group by beosztás; 4. Hány operátor van a különböző szinteken?(csoportképzés) select beosztás count(*) "operátorok" from ügyintéző group by beosztás; 5. Hányszor fordult elő egy-egy hibaüzenet?(inner join) select

hibaüzenet,hányszor from Hibaüzenetek inner join Hiba on Hibaüzenetek. Azonosító = Hiba.Azonosító; 6.Melyik vezető melyik ügyintézőnek küldött emailt?(inner join) select ügyintéző.név, vezetőnév from ügyintéző inner join vezető on ügyintéző email = vezető.azonosító; 7. Összesen hány hiba keletkezett(összesítő) select sum(hányszor) from Hiba 8. Átlagosan hány hibája van egy embernek(összesítő) select avg(hányszor) from Hiba 9.Az összes hiba és a hozzátartozó adatok kiírása (outer join) select hiba.azonosító,mióta,hányszor,hibaüzenet from Hiba full outer join Hibaü zenetek on Hiba.azonosító = Hibaüzenetekazonosító; 10. Az összes telefonhibával rendelkező ügyfél és adatainak kiírása(outer join) select ügyfél.azonosító, név,kód,szülhely,szülidő,hazon from Ügyfél full outer jo in TelefonHÜ on ügyfél.azonosító = TelefonHÜÜazon; 11. Az átlagnál kevesebbszer előforduló hibák és

adatai(al-lekérdezés) select hányszor,mióta,hibaüzenet from Hiba inner join Hibaüzenetek on Hiba.az onosító = Hibaüzenetek.azonosító where hányszor < (select avg(hányszor) from Hiba) 12. Azoknak az ügyfeleknek az adatait írjuk ki, akik az átlag telefonon beszélt idő alatt befejezték a beszélgetést.(al-lekérdezés) select ügyfél.azonosító,név,kód,szülhely,szülidő from ügyfél inner join TelefonÜ Ü on ügyfél.azonosító = telefonÜÜüazon where (meddig - mettől) < (select avg (meddig-mettől) from telefonÜÜ); 13.Származtatott lekérdezés, A gyakoriság alapján a ’gyakori’ hibák adatainak a kiírása. 14. komplex lekérdezés, Írjuk ki, hogy melyik ügyfél, melyik internet hibát tapasztalja és mióta tapasztalja