SQL, adatdefiníció, adatok megváltoztatása, megszorítások

1. SQL, adatdefiníció, adatok megváltoztatása, megszorítások Adatbázis-kezelés

2. Adatdefiníció • Data Definining Language (DDL): az adatbázisséma létrehozatalára valamint megszorítások megfogalmazására szolgál. • Az utasítás általában az ALTER kulcsszóval kezdődik.

3. Táblák definíciója, törlése • Az Oracle-ben használt adattípusok egy összefoglalását lásd az SQL, PL/SQL típusok és függvények bemutatóban (SQL_tip_fv.ppt). • Létrehozatal a CREATE TABLE utasítással. • Törlés a DROP TABLE utasítással. • Példa: CREATE TABLE szamla ( bank_azon VARCHAR2(4), azon VARCHAR2(4), osszeg NUMBER(7), lejarat DATE); DROP TABLE szamla;

4. Táblák módosítása • Oszlop hozzáadása: ADD oszlop_nev tipus; • Oszlop törlése: DROP COLUMN oszlop_nev [CASCADE CONSTRAINTS]; • Oszlop módosítása: MODIFY oszlop_nev típus [DEFAULT kifejezés][megszorítások] • Oszlop átnevezése: RENAME COLUMN regi TO uj; • Példa: ALTER TABLE szamla ADD tulajdonos VARCHAR2(20); ALTER TABLE szamla DROP (tulajdonos); ALTER TABLE bank_azon VARCHAR2(6); ALTER TABLE szamla RENAME bank_azon TO b_azon;

5. Alapértelmezés szerinti értékek • Ha egy attribútum értéke nem ismert, akkor ott a NULL érték szerepel. Egy-egy tábla definíciójánál, módosításánál megadhatjuk, mi legyen egy-egy attribútum alapértelmezés szerinti értéke. • Példa: ALTER TABLE szamla ADD modositas DATE DEFAULT SYSDATE;

6. Nézettáblák I. • A nézettáblák virtuális táblák, vagyis fizikailag sohasem tárolódnak az adatbázisban. • Egy SELECT-FROM-WHERE lekérdezés segítségével hozzuk létre már létező táblákból és nézettáblákból. • Szerepe (például): • ha egy részeredményt gyakran használunk lekérdezésekben (Példa I), • ha azt szeretnénk, hogy valaki egy-egy táblának csak bizonyos részeihez férjen hozzá, és ne lássa az ott tárolt összes adatot (Példa II). • A CREATE VIEW nev AS sql_lekerdezes; utasítással hozzuk létre. • DROP VIEW nev; utasítással töröljük.

7. Nézettáblák II. • Példa I: CREATE VIEW ugyfel_szamla AS SELECT nev, azon, osszeg, lejarat FROM ugyfel, szamla WHERE azon = szamla_azon; • Példa II: CREATE VIEW resz_szamla AS SELECT azon, lejarat FROM szamla; DROP VIEW ugyfel_szamla; DROP VIEW resz_szamla;

8. Nézettáblák III. • Attribútumok átnevezése nézettáblákban, példa: CREATE VIEW ugyfel_szamla(tulaj, sz_azon, osszeg, lejarat) AS SELECT nev, azon, osszeg, lejarat FROM ugyfel, szamla WHERE azon = szamla_azon; • Megjegyzés: természetesen a SELECT záradékban a korábban a zárójelek közt megadott sorrendnek megfelelően kell megadni az attribútumokat.

9. Nézettáblák IV. • A létrehozó utasítás általános alakja: CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW nev [masodlagos_nev [, masodlagos_nev]…)] AS s-f-w lekerdezes [WITH READ ONLY | WITH CHECK OPTION];

10. Megszorítások: NOT NULL • A NOT NULL megszorítással előírhatjuk, hogy egy attribútum értéke nem lehet ismeretlen, azaz NULL érték. • Példa: CREATE TABLE szamla ( bank_azon VARCHAR2(4) NOT NULL, azon VARCHAR2(4) NOT NULL, osszeg NUMBER(7) NOT NULL, lejarat DATE NOT NULL);

11. Kulcsok I. • Egy R relációban az X attribútumhalmaz kulcs, ha bármely két t1, t2 sorra, ha t1[X] = t2[X], vagyis a két sor megegyezik X értékein, akkor az összes többi attribútumon is megegyezik az értékük. • Tipikusan kulcsot szoktak alkotni a különféle azonosítók, például személyi igazolvány szám. • Egy táblának egyetlen elsődleges kulcsa lehet. Az Oracle az elsődleges kulcs attribútumaira automatikusan létrehoz egy indexet. • Egy táblának ettől függetlenül természetesen több kulcsa is lehet.

12. Kulcsok II. • Példa: CREATE TABLE ugyfel ( azon VARCHAR2(3) PRIMARY KEY, nev VARCHAR2(12), szamla_azon VARCHAR2(4), szuletes DATE, munkahely VARCHAR2 (10), fizetes VARCHAR2(6));

13. Kulcsok III. • Példa: CREATE TABLE ugyfel ( azon VARCHAR2(3), nev VARCHAR2(12), szamla_azon VARCHAR2(4), szuletes DATE, munkahely VARCHAR2 (10), fizetes VARCHAR2(6), PRIMARY KEY (azon), UNIQUE (nev, szuletes)); • Megjegyzés: gyakran érdemes a többi kulcsra is indexet létrehozni, ellenkező esetben a megszorítás ellenőrzése nagyon időigényessé válhat beszúrás és módosítás esetén. • CREATE INDEX nev_szul ON ugyfel(nev, szuletes);

14. Idegen kulcsok I. • Egy X attribútumhalmaz a Y attribútumhalmazra vonatkozóan idegen kulcs, ha Y minden értéke X értékei közt is előfordul. • X-nek ilyenkor elsődleges kulcsnak kell lennie. • Példa: a szamla tábla azon attribútuma az ugyfel tábla szamla_azon attribútumának idegen kulcsa. • Hivatkozási épség fenntartása: • ha olyan sort szúrunk be az ugyfel táblába, melynek szamla_azon értéke nem NULL és nem szerepel a szamla tábla azon attribútumának értékei közt, vagy ilyen értékre módosítjuk a szamla_azon értékét, akkor az utasítást nem hajtja végre a rendszer (alapeset); • ha a szamla táblából olyan sort törlünk, ahol az azon érték szerepel a szamla_azon értékei közt, vagy egy ilyen azon értéket módosítanánk, a rendszer nem hajtja végre az utasítást (alapeset); • ez utóbbi esetben azt is előírhatjuk, hogy törlés esetén az ugyfel tábla megfelelő sorai is törlődjenek, módosításnál pedig módosuljanak (továbbgyűrűző eljárás); • illetve a megfelelő szamla_azon értékeket NULL-ra is állíttathatjuk (NULL értékre állítás módszere).

15. Idegen kulcsok II. • Példa: CREATE TABLE ugyfel ( azon VARCHAR2(3), nev VARCHAR2(12), szamla_azon VARCHAR2(4), szuletes DATE, munkahely VARCHAR2 (10), fizetes VARCHAR2(6), PRIMARY KEY (azon), UNIQUE (nev, szuletes), FOREIGN KEY (szamla_azon) REFERENCES szamla(azon));

16. Idegen kulcsok III. • Példa: CREATE TABLE ugyfel ( azon VARCHAR2(3), nev VARCHAR2(12), szamla_azon VARCHAR2(4), szuletes DATE, munkahely VARCHAR2 (10), fizetes VARCHAR2(6), PRIMARY KEY (azon), UNIQUE (nev, szuletes), FOREIGN KEY (szamla_azon) REFERENCES szamla(azon) ON DELETE SET NULL, ON UPDATE CASCADE);

17. CHECK feltétel I. • A CHECK feltétel segítségével bonyolultabb megszorítások is megfogalmazhatók. • Egy zárójelbe tett kifejezés szerepel utána, amelyben minden olyan feltétel szerepelhet, ami WHERE záradék után szerepelhet, alkérdéseket viszont már nem adhatunk meg. • Ha e kifejezésben más táblák is szerepelnek, akkor ha abban a táblában módosulnak az értékek, akkor a rendszer nem ellenőrzi, hogy az adott megszorítás továbbra is teljesül-e. Ilyen megszorítások esetén inkább triggereket használnak.

18. CHECK feltétel II. • Példa: CREATE TABLE szamla ( bank_azon VARCHAR2(4), azon VARCHAR2(4) PRIMARY KEY, osszeg NUMBER(7) CHECK (osszeg > 10000), lejarat DATE CHECK (lejarat > DATE ‘1970-01-01’));

19. CHECK feltétel III. • Példa: CREATE TABLE szamla ( bank_azon VARCHAR2(4), azon VARCHAR2(4), osszeg NUMBER(7), lejarat DATE, CHECK (osszeg > 10000 AND lejarat > DATE ‘1970-01-01’));

20. Megszorítás elnevezése I. • A megszorításokat gyakorlatilag minden esetben névvel szokták ellátni, mert így lehetőség nyílik ezek módosítására, amire egyébként nem lenne mód. • Elnevezni a CONSTRAINT kulcsszó használatával lehet. • Módosítás hozzáadása: ALTER TABLE tabla_nev ADD CONSTRAINT megszor_nev megszoritas; • Megszorítás törlése: ALTER TABLE tabla_nev DROP CONSTRAINT megszor_nev;

21. Megszorítás elnevezése II. • Példa: CREATE TABLE szamla ( bank_azon VARCHAR2(4), azon VARCHAR2(4), osszeg NUMBER(7), lejarat DATE, CONSTRAINT megfelelo_ertek CHECK (osszeg > 10000 AND lejarat > DATE ‘1970-01-01’), CONSTRAINT elsodl_kulcs PRIMARY KEY (azon)); ALTER TABLE szamla DROP CONSTRAINT megfelelo_ertek; ALTER TABLE szamla ADD CONSTRAINT jo_ertek CHECK (osszeg > 10000 AND lejarat > DATE ‘1970-01-01’);

22. Megszorítás lekérdezése • user_constraints, user_cons_columns adatszótár nézetekből kérdezhetők le. SELECT * FROM user_constraints; SELECT * FROM user_cons_columns;

23. Adatok megváltoztatása I. • Data Manipulating Language (DML): az adatok megváltoztatására: beszúrásra, módosításra, törlésre szolgál. • Beszúrás: INSERT INTO R(A1,… , An) VALUES (v1,… , vn); Itt vi konstans és S-F-W lekérdezés is lehet. • Törlés: DELETE FROM tabla WHERE feltetel; • Módosítás: UPDATE tabla SET ertek_adasok WHERE feltetel;

24. Adatok megváltoztatása II. • Példák: INSERT INTO szamla VALUES ('BB01', 'SZ00', 23000, '2007-03-15'); INSERT INTO szamla VALUES ('BBO1', 'SZ01', 1000000, '2006-02-13'); DELETE FROM szamla WHERE azon = ‘SZ01’; UPDATE szamla SET osszeg = 123000 WHERE azon = ‘SZ00’;

25. Tranzakció • A logikailag egybetartozó utasításokat általában egy munkamenetként kezelik. Az utasításoknak vagy mindegyike végrehajtódik vagy egyik sem. Az ilyen munkameneteket tranzakciónak nevezzük. • Egy-egy adatbáziskezelő általában több párhuzamos tranzakciót kezel egyszerre. Nemzetközi példák: helyfoglalás egy-egy repülőgép járatra, bankjegyautomaták. • Kívánalmak. • Atomosság: egy tranzakció vagy teljes egészében végrehajtódik vagy egyik része sem hajtódik végre; • Konzisztencia: minden adatbázisban adottak bizonyos konzisztencia-feltételek. Pl. egy járat ugyanazon helyét nem foglalhatja le ugyanaz a két utas. A tranzakciók befejeződése után az adatbázisnak konzisztensnek kell lennie. • Elkülönítés: a tranzakciók konkurencia-vezérlését úgy kell megoldani, mintha a tranzakciók egymás után futnának. • Tartósság: ha egy tranzakció befejezte működését, annak eredménye nem veszhet el azonnali rendszerhiba esetén sem.

26. Zárak, visszagörgetés • A zár átmeneti tulajdonosi jogkört biztosít egy felhasználónak egy-egy adatbázisbeli objektum: pl. tábla, tábla bizonyos sorai fölött. Más felhasználó nem módosíthatja az adatokat mindaddig, amíg a zárolás fennáll. • Ha F1 felhasználó egy táblát éppen lekérdez, F2 ugyanazt módosítja, akkor az Oracle egy nézetet hoz létre F1 számára, F2 módosításai pedig a tábla egy pillanatfelvételén történnek meg. • Az Oracle egy visszagörgető szegmenst használ a módosítások tárolására és az olvasási konzisztencia biztosítására. • Sikeres végrehajtás esetén a visszagörgető szegmens információi átkerülnek az adatbázisba, véglegesítődnek. • Ha hiba történik, a tranzakció „visszagörgetődik”, az adatbázis tranzakció előtti állapota nem változik meg.

27. COMMIT, SAVEPOINT, ROLLBACK • Tranzakció véglegesítése: COMMIT [WORK] • Megjegyzés: a WORK alapszó csak a jobb olvashatóságot biztosíthatja. • A mentési pontok egy tranzakció részleges visszagörgetését szolgálhatják: SAVEPOINT nev; • Visszagörgetés: ROLLBACK [WORK] [TO [SAVEPOINT] mentesi_pont]; • Az egyszerű ROLLBACK érvényteleníti a teljes tranzakció hatását, oldja a zárakat, törli a mentési pontokat. • A TO utasítással rendelkező ROLLBACK a megadott mentési pontig görgeti vissza a tranzakciót. A mentési pont után a zárak feloldásra kerülnek, a tranzakció a mentési ponttól folytatódik. • Megjegyzés: az INSERT, DELETE, UPDATE utasítások előtt az Oracle egy implicit mentési pontot helyez. Ha az utasítás sikertelen, visszagörgetés történik. Kezeletlen kivétel esetén a gazdakörnyezet dönt. Emiatt érdemes explicit módon elhelyezni a fenti utasításokat.

28. Feladatok I. • Adott a következő adatbázisséma… Ember (név, kor, TAJ) Orvos (TAJ, szakt, tud_fok) Korzeti_orvos (TAJ, szakt, tud_fok ,varos) Paciens (TAJ, foglalkozas, orvos_TAJ) Gyogyszer (ar, nev) Betegseg (nev, lefutas, torzs_nev) Bacilus_torzs (nev, elterjedtseg) Bacilus (nev, torzs_nev, gy_nev) Felir (orvos_TAJ, paciens_TAJ, gy_nev)

29. Feladat II. • a következő megszorításokkal: • elsődleges kulcsok: Ember (TAJ), Orvos (TAJ), Korzeti_orvos (TAJ), Paciens (TAJ), Gyogyszer (nev), Betegseg (nev), Bacilus_torzs (nev), Bacilus (nev, torzs_nev); • egyéb kulcs: Ember (nev, kor); • idegen kulcsok Gyogyszer (nev) a Bacilus (gy_nev)-re, Gyogyszer (nev) a Felir (gy_nev)-re, Korzeti_orvos (TAJ) a Paciens (orvos_TAJ)-ra, Korzeti_orvos (TAJ) a Felir (orvos_TAJ)-ra, Paciens (TAJ) a Felir (paciens_TAJ)-ra, Bacilus_torzs (nev) a Betegseg (torzs_nev)-re, Bacilus_torzs (nev) a Bacilus (torzs_nev)-re, Ember (TAJ) az Orvos (TAJ)-ra, Ember (TAJ) a Korzeti_orvos (TAJ)-ra, Ember (TAJ) a Paciens (TAJ)-ra; • egyéb megszorítások: csak a budapesti, kecskeméti és gyöngyösi körzeti orvosokat tároljuk az adatbázisban • a törlés a TAJ attribútumok esetén továbbgyűrűző eljárás szerint történjen.

30. Feladat III. • Adjuk meg azok nevét, akiknek a legtöbb pénze van a számláin összesen. • Adjuk meg azok nevét, akiknek összesen legalább egymillió forint van a számláin. • Adjuk meg azok nevét, akiknek legalább százezer forinttal több pénzük van összesen a számláin, mint amennyivel egy átlagos ügyfél rendelkezik összesen.