Adatb ázis I. – 1 1 . előadás

1. Adatbázis I. –11. előadás Kulcsok az SQL – ben. Hivatkozás- épségi megszorítások és idegen kulcsok.

2. Kulcsok az SQL-ben • Hivatkozási épségés idegen kulcsok • Attribútum értékre vonatk. megszorítások • Globális megszorítások • Megszorítások módosítása • Triggerek az SQL-ben • Tranzakciók

3. 0. Bevezetés • Minden beszúrás, törlésés módosítás utánellenőrzéstkellene végrehajtani. • Az SQL 2 lehetőséget ad az adatbázis épségi megszorításainak az adatbázissémában való megadására. • A triggerek aktív elemek amelyek bizonyos események hatásáralépnek működésbe.. • Csak az SQL 3 ban vannak benne

4. 1. Kulcsok az SQL-ben Legfontosabb megszorítás a kulcsalkotás deklarációja: egy attribútum vagy attribútumhalamaz egy relációnak a kulcsa ( nem lehet két sornak megegyezőértékű attribútuma vagy attribútumhalmazaa kulcs attribútumo(ko)n) Kulcsok megadása a CREATE TABLE utasításnál PRIMARY KEY es UNIQUE kulcsszavakkal történik. A kulcs megadása: • Attribútum deklarálása relációsémában mint elsődleges kulcs • Hozáadunk egy további deklarációt, ami azt mutatja meg hogy az attribútum vagy attribútumhalmaz kulcsot alkot

5. A kulcs megadása 2 féle képpen. CREATE TABLE szemely( CNP Bigint PRIMARY KEY, Név VARCHAR(50) ); CREATE TABLE szemely(CNP Bigint, Név VARCHAR(50), PRIMARY KEY(CNP) ); Ha több, mint 1 attribútum van, akkor csak így lehet megadni!!!!!!!

6. Feltételek (egy adott oszlopra vonatkoznak): • PRIMARY KEY: elsődleges kulcs • UNIQUE: kulcs • REFERENCES tábla(oszlop) [ON-feltételek]: külső kulcs • Táblafeltételek (az egész táblára vonatkoznak): • PRIMARY KEY (oszloplista): elsődleges kulcs • UNIQUE (oszloplista): kulcs • FOREIGN KEY (oszloplista) REFERENCES tábla(oszloplista) [ON-feltételek]: külső kulcs

7. Zölddel írva a számlaáltalános adatait láthatjuk Pirossal a számlasorokattribútumai vannak Kékkel írva láthatóak a számlasorok.

8. számlaszám ÁFA Sorszám határidő sorai Számlasorok Számlák számlája Dátum egységár darabszám terméke azonosító Ügyfelek termékID név név bank bszámla Termékek mértékegység adószám bejegyzés Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda

9. 1. Példa : a név atribútum mint elsődleges kulcs CREATE TABLE Szamla( SzamlaszamCHAR(14) PRIMARY KEY, Datum DATE,AFA INT, KliensID INT NOT NULL REFERENCES Kliens(ID)); 2. Példa: elsődleges kulcs különálló deklarálása CREATE TABLE Szamlasor( SzamlaszamCHAR(14),Sorszam INT, MennyiségFLOAT,Egysegar FLOAT, Termekkod INT REFERENCES Termek(termekID), PRIMARY KEY(Szamlaszam,Sorszam) FOREIGN KEY (Szamlaszam) REFERENCES Szamla(Szamlaszam);

10. CREATE TABLE Konyv( KOD BIGINT PRIMARY KEY, SzerzoVARCHAR(45), Cim VARCHAR(55) ); CREATE TABLE Konyvpeldany( Leltarszam INT PRIMARY KEY, Vonalkod BIGINT UNIQUE, Konyvkod BIGINT REFERENCES Konyv(KOD) );

11. vagy egymás lehetőség a UNIQUE használata: Példa Számla: Szamlaszam CHAR(14) UNIQUE UNIQUE(Szamlaszam) Számlasorok esetében csak a második használható UNIQUE(Szamlaszam, Sorszam)

12. Kulcsfeltételek teljesítésének biztosítása: • Lehetőség van az index deklarálásához, gyorsabb keresést biztosít • CREATE UNIQUE INDEX nevIndex ON Kliens(Név) • A kulcsfeltételt minden módosítas után ellenőrizni kell. • Csak a beszúrásés modosítás okozgondot, a törlés nem. • Ha nincs index akkor a reláció minden sorát ellenőrizni kell. • Ez nagyon időigényes művelet.

13. elsődleges kulcs és egyedi attribútumok hasonlatossága és különbsége: hasonlatosság PRIMARY KEY es UNIQUE szinonímák különbség • PRIMARY KEY egyszer fordul elő egy táblában amíg UNIQUE többször is előfordulhat • Idegen kulcs csak elsődleges kulcsra vonatkozhat • PRIMARY KEY hez indexet lehet hozzákapcsolni

14. 2. Hivatkozási épség és idegen kulcsok Hivatkozási épség azt jelenti hogy bizonyosattribútumoknak megfelelőérteme KELL legyen. Példa: a Számla relációban a KliensID atribútum értéke 2563, akkor a Kliens relációban van egy sor amelyben az azonosito 2563. Idegen kulcsok más relációra vonatkoznak , más attribútumokra • A másik reláció attribútumai, amelyekre hivatkozunk elsődleges kulcsok • A Hivatkozási épség megszorítás a két attribútumot (attribútum-csoportot)összekapcsolja

15. Idegen kulcsokat kétféleképpen deklarálhatjuk: • Az idegen kulcs egyattribútumra vonatkozik REFERENCES <tábla>(<attribútum>) • A CREATE TABLE utasítás végén deklaráljuk FOREIGN KEY <attibútum(ok)> REFERENCES <tábla> (<attribútum(ok)>)

16. 1. Példa : idegen kulcs egy attribútumra vonatkozik CREATE TABLE Számla ( Számlaszám CHAR(14) PRIMARY KEY, Dátum DATE,ÁFA INT, KliensID INT NOT NULL REFERENCES Kliens(ID)); 2. Példa: mindkét eset előfordul egy tábla készítésénél CREATE TABLE Számlasor ( Számlaszám CHAR(14), Sorszám INT, MennyiségFLOAT, Egységár FLOAT, Termékkód INT REFERENCES Termek(termekID), PRIMARY KEY(Számlaszám,Sorszám) FOREIGN KEY (Számlaszám)REFERENCES Számla(Számlaszám));

17. Hivatkozási épség fenntartása Futási hiba: • Számla relációba beszúrunk egy olyan sort, amelyben a KliensID nem egyezik a Kliens táblából egyik kliens-kóddal sem • Számla reláció egy sorában megpróbáljuk módosítani a KliensID-t, olyanra ami nem létezik a Kliens táblában • Kliens táblából törölni próbálunk egy olyan sort, amelyikben levő klienskód létezik valamelyik sorban a Számla relációban • Kliens relációban módosítása olyan klienskód értékének, amelyik benne van a Számla reláció legalább egy sorában Azadatbaziskezelőnek három lehetősége van: • Alapértelmezés szerinti eljárás . Ha a feltétel megsérül a módosítástvisszautasítjuk.

18. Továbbgyűrűző eljárás

19. ON UPDATE CASCADE

20. ON DELETE SET NULL Törölve volt a 2888-as Kliens, a MacroSoft

21. A tábla készítése, hogy az előbbi módon működjék CREATE TABLE Számla( Számlaszám CHAR(14) PRIMARY KEY, Dátum DATE, határidő DATE, ÁFA INT, Fizetve BOOLEAN, KliensID INT REFERENCES Kliens(Klienskód), ON DELETE SET NULL, ON UPDATE CASCADE );

22. Zölddel írva a számlaáltalános adatait láthatjuk Pirossal a számlasorokattribútumai vannak Kékkel írva láthatóak a számlasorok.

23. számlaszám ÁFA Sorszám határidő sorai Számlasorok Számlák számlája Dátum egységár darabszám terméke azonosító Ügyfelek termékID név név bank bszámla Termékek mértékegység adószám bejegyzés Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda

24. CREATE TABLE Számla(... KliensID INT REFERENCES Kliens(Klienskód) ON DELETE CASCADE ON UPDATE CASCADE ); Ha kitörölünk egy KLIENSt a kliens táblából, akkor az összes hozzá tartozó számla is kitörlődik. Ez sem jó, de az adatbázisunk bizonyos szempontból konszisztens maradt, viszont hiányoznak bizonyos számlák.

25. CREATE TABLE Számlasor(... Termékkód INT REFERENCES Termek(termekID) ON DELETE CASCADE, FOREIGN KEY (Számlaszám) REFERENCES Számla(Számlaszám) ON DELETE CASCADE ON UPDATE CASCADE ); • Az első esetben kitörlődik az összes olyan számlasor, amelyikben egy bizonyos termék megtalálható. Ez teljesen inkonszisztens, mert lehet olyan számlánk, amelyikből csak bizonyos sorok maradtak meg. A számla összértéke is megválozik a törlés miatt. • A második eset valamivel jobb, sőt, konszisztencia őrzője, mivel, ha kitörölünk egy számlát, akkor törlődik vele együtt az összes hozzá tartozó számlasor is.

26. 3. A törlés szerinti CASCADE (továbbgyűrűzés) érdekesen alakulhat az előzőek szerint: Ha kitörlünk egy ügyfelet, akkor azzal együtt kitörlődnek a hozzá tartozó számlák. Mivel a számlasorok a számlákhoz ugyancsak CASCADE szerint vannak kapcsolva, kitörlődnek az összes hozzátartozó számlasorok is. Ez a példa mutatja be az igazi továbbgyűrzést, mert egy táblában való törlés, másik két táblában való törlést eredményez.

27. Lógó sorok es módosító eljárások: Definíció: Lógó sorok azok a sorok amelyek kulcsértéke nincs benne a hivatkozott táblában. Megsértik az adott idegen kulcs megszorítására vonatkozó hivatkozási épséget. Az alapértelmezett eljárás a törlés és módosításokra: Tiltottak azon műveletek, amelyek lógó sorokat hoznak létre abban a relációban, amelyik a másikra hivatkozik. Továbbgyűrűző eljárás: Törli vagy módosítja minden létrejött lógó sort NULL értékre állítás: Az idegen kulcsot NULL értekre változtatja az összes lógó sorban

28. Abban az esetben ha nem adtuk meg ELŐRE a HIVATKOZÁSÉPSÉGI megszorítást, megtörténhet, hogy lógó soraink maradnak, s ezekután nem tudjuk megadni a hivatozás-épségi megszorítást. A lógó sorokat a LEFT JOIN és a RIGHT JOIN összekapcsolás is megmutatja. A LEFT JOIN esetében a hivatkozó tábla a FROM után az első kell legyen: SELECT Számla.Számlaszám, Kliens.nev FROM Számla LEFT JOIN Kliens ON Számla. KliensID=Kliens.ID; Megadja az összes számlát és a hozzátartozó kliens nevét. (ha van)

29. Azon számlák megkeresése, amelyek “lógnak” a következő képpen is lehetségesek: SELECT Számla.Számlaszám FROM Számla WHERE Számla.KliensID NOT IN (SELECT Kliens.ID FROM Kliens);

30. 3. Attribútum értékre vonatkozó megszorítások Kétféleképpen adható meg: • Attribútumokra vonatkozó megszorítás a relacióséma definálásakor adható meg. • Értéktartományra vonatkozó megszorítás, majd az attribútum hozzárendelése az értéktartományhoz. • Attribútumokra vonatkozóCHECK feltételek korlátozzák az attribútumértéket.Példa: • CHECK (elnökazon >=100000), CHECK (nem In (‘F’, ‘N’)

31. A NOT NULL feltételek Példa (a Számla relációban) : KliensID INT REFERENCES Kliens (ID) NOT NULL Következmények: Nem módosíthatjuk a kliensID attribútumot NULL ra Nem szúrhatunk egy sort be a Számla relációba amelyre csak számlaszámot, dátumot, határidőt és ÁFA – t írunk mertakkor a KliensID NULL lenne Nem alkalmazhatjuk a NULL értékre állítás módszerét

32. Értéktartományra vonatkozómegszorítások: Értéktartomány megadásával az attribútum értékeit korlátozhatjuk. Az értéktartományra való hivatkozás a VALUE kulcsszóval történik CREATE DOMAIN NemÉrték CHAR (1) CHECK (VALUE IN (‘F’,’N’)); Mikor kell egy megszorítást ellenőrizni? TRANZAKCIÓ után, ha DEFERRED – ként deklaráljuk. Létezhet olyan kapcsolat, amelyik nem engedi a bevitelt, csak ha 2 helyre is bevisszük egyszerre.

33. 4. Globális megszorítások Több attribútumra vonatkozó megszorítások. A problémát két részre osztjuk: 1. Sorra vonatkozó CHECK fetétel 2. Önálló megszorítások Sorra vonatkozó CHECK feltétel: • CREATE TABLE utasításban adjuk meg az attribútumok, kulcsok és idegen kulcsok deklarációja után. Bármi lehet, ami a WHERE után szerepel. • A rendszer akkor ellenőrzi, amikor egy új sort szúrunk be R – be, vagy amikor R egy sorát módosítjuk

34. Önálló megszorítások -Reláció egészére : pl. oszlopbeli értékek összegére ad feltételt • CREATE ASSERTION kulcsszó, • Az önálló megszorítás neve, • A CHECK kulcsszó • Egy zárójelben megadott feltétel CREATE ASSERTION <név> CHECK (<feltétel>)

35. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda Megszorítások összehasonlítása:

36. 5. Megszorítások módosítása Megszorításokat módosíthatjuk, törölhetjük vagy helyettesíthetjük: Megszorítások elnevezése A törléshez szükség van arra, hogy a Megszorításoknak neve legyen. A CREATE ASSERTION és CONSTRAINT kulcsszavakkal történik Példa: Nev CHaR (30) CONSTRAINT Nevkulcs PRIMARY KEY, Nem CHAR (1) CONSTRAINT ferfivagyno CHECK (nem IN (‘F’, ‘N’));

37. Tablákra vonatkozó megszorítások megváltoztatása: ALTER TABLE kulcsszavakkal, DROP-al törölünk ADD-all hozzáteszünk egy új megszorítást Példa: ALTER TABLE Filmszinesz DROP CONSTRAIN nevkulcs; ALTER TABLE Filmszinesz ADD CONSTRAIN nevkulcs PRIMARY KEY (nev); Értéktartományra vonatkozó megszorítások megváltoztatása Példa: ALTER DOMAIN Azonertek DROP CONSTRAIN Hatjegyu ALTER DOMAIN Azonertek ADD CONSTRAIN Hatjegyu CHECK (VALUE<=100000);

38. Önálló megszorítások megváltoztatása: A DROP ASSERTION kulcsszavakkal törölünk Létrehozás új deklarációval végezzük el Példa : DROP ASSERTION gazdagelnok