Relációsémák létrehozása SQL nyelvben SQL (Structured Query Language) • lekérdezés • módosítás • relációséma leírására alkalmas utasítások: – attribútumnevek, attribútumok adattípusa – megszorításokat is megadhatunk, melyeket az adatoknak ki kell elégíteniük, hogy bekerüljenek az adatbázisba.
Minden relációs ABKR rendelkezik egy SQL interpreterrel, melynek segítségével az SQL nyelv parancsait begépelhetjük és végrehajthatjuk. – Oracle SQL interpreterje az SQL-PLUS – MS SQL Server-é a Query Analyzer.
Adattípusok Egy relációséma definiálása esetén minden attribútumnak meg kell adni az adattípusát. Fontosabb adattípusok: – Karaktersorok, melyek lehetnek rögzített (CHAR(n)) vagy változó hosszúságúak (VARCHAR(n)). – Egész számok (INT vagy INTEGER, esetleg SHORTINT, melyet kevesebb biten tárol a rendszer). – Lebeg pontos értékek (REAL, vagy nagyobb pontossággal a DOUBLE PRECISION). – Dátumok és id k (DATE és TIME). – BLOB-bak (Binary Large Objects).
Relációk létrehozása A CREATE TABLE parancs segítségével relációkat (táblákat) tudunk létrehozni. A parancs komplex, lásd a megszorításoknál. CREATE TABLE Alkalmazottak ( SzemSzám INT, Név VARCHAR(30), Fizetés REAL, SzülDat DATE, Nem CHAR(1), RészlegID INT );
Relációsémák módosítása El fordul, hogy a létez tábla szerkezetét meg kell változtatnunk, ALTER TABLE Alkalmazottak ADD COLUMN Telefon CHAR(16); ALTER TABLE Alkalmazottak DROP COLUMN SzülDat; Relációk törlése DROP TABLE
;
Alapértelmezés szerinti értékek – új sor beszúrása esetén, nem mindig ismerjük minden oszlop értékét. – megoldás: NULL érték, amit magyarul mondhatnánk úgy, hogy „nem tudom”, kivéve, ha van NOT NULL megszorítás. egy alapértelmezés szerinti érték a NULL helyett, ezt a DEFAULT kulcsszó segítségével adhatjuk meg.
CREATE TABLE Alkalmazottak ( SzemSzám INT, Név VARCHAR(30), Fizetés REAL, SzülDat DATE DEFAULT ’1900-01-01’, Nem CHAR(1) DEFAULT ’?’, RészlegID INT, Telefon CHAR(16) DEFAULT ’ismeretlen’ );
Értéktartományok Értéktartomány: egy adattípus, esetleg alapértelmezett értékkel és megszorításokkal. Attribútum típusának definiálhatunk egy értéktartományt. Több attribútumnak is lehet ugyanaz az értéktartománya, ami sok esetben hasznos lehet. Ha változik valami, csak egy helyen kell változtatni. CREATE DOMAIN AS ;
példa: A címet gyakran használjuk személyek, vev k, szállítók stb. esetén, ezért definiálhatunk neki egy értéktartományt: CREATE DOMAIN CímDom AS VARCHAR(50) DEFAULT ’nem ismert’; A tábla definíciójában a Cím attribútum típusa CímDom: Cím CímDom; létez értéktartomány módosítása: ALTER DOMAIN parancs törlése DROP DOMAIN utasítással.
CREATE TABLE Alkalmazottak ( SzemSzám INT, Név VARCHAR(30), Cím CímDom; Fizetés REAL, SzülDat DATE DEFAULT ’1900-01-01’, Nem CHAR(1) DEFAULT ’?’, RészlegID INT, Telefon CHAR(16) DEFAULT ’ismeretlen’ );
Megszorítások • az adatbázis módosításakor az új információ nagyon sokféleképpen lehet hibás. • ahhoz, hogy az adatbázis adatai helyesek legyenek, különböz feltételeknek kell eleget tenniük. A megszorítások azon követelmények, melyeket az adatbázis adatai ki kell elégítsenek, ahhoz, hogy helyeseknek tekinthessék ket.
Megszorításokat osztályozása 1. Egyedi kulcs feltétel: egy relációban nem lehet két sor, melyeknek ugyanaz a kulcsértéke, vagyis ha C egy R reláció kulcsa, ∀t1 , t2 ∈ R sorok esetén π C (t1 ) ≠ π C (t2 ) (lásd relációs algebrai m veletek Error! Reference source not found.). 2. Hivatkozási épség megszorítás: megköveteli, hogy egy objektum által hivatkozott érték létezzen az adatbázisban. Ez analóg azzal, hogy a hagyományos programokban tilosak azok a mutatók, amelyek sehova se mutatnak. 3. Értelmezéstartomány-megszorítások: azt jelentik, hogy egy attribútum az értékeit a megadott értékhalmazból vagy értéktartományból veheti fel.
4. Általános megszorítások: tetsz leges követelmények, amelyeket be kell tartani az adatbázisban.
Hivatkozásiépség megszorítás:
Küls kulcs egy KK attribútum (attribútumhalmaz) egy R1 relációból: • értékeinek halmaza ugyanaz, mint egy R2 reláció els dleges kulcsának az értékhalmaza, • és az a feladata, hogy az R1 és R2 közötti kapcsolatot modellezze. R1 az a reláció, mely hivatkozik, az R2 pedig, amelyre hivatkozik. Más megnevezés: az R2 az apa és az R1 a fiú – egy sorhoz az R2-b l tartozhat több sor az R1-b l, – R2-ben els dleges kulcs az attribútum ami a kapcsolatot megteremti. (Fordítva nem állhat fenn a kapcsolat)
A hivatkozási épség megszorítás a következ ket jelenti: – az R2 relációban azt az attribútumot (esetleg attribútumhalmazt), melyre az R1 hivatkozik els dleges kulcsnak kell deklarálni, – KK minden értéke az R1-b l kell létezzen az R2 relációban, mint els dleges kulcs értéke. példa: Az Alkalmazottak (R1) reláció hivatkozik a Részlegek (R2) relációra a RészlegID (KK) küls kulcs segítségével.
Alkalmazottak (a fiú): SzemSzám 111111 222222 456777 234555 123444 333333
Részlegek (az apa):
Név Nagy Éva Kiss Csaba Szabó János Szilágyi Pál Vincze Ildikó Kovács István
RészlegID 2 9 9 2 1 2
RészlegID
RNév
1 2 9
Tervezés Könyvelés Beszerzés
Fizetés 300 400 900 700 800 500
Manager SzemSzám 123444 234555 456777
példa: Szállítók (SzállID, SzállNév, SzállCím) Áruk (ÁruID, ÁruNév, MértEgys) Szállít (SzállID, ÁruID, Ár) A Szállít reláció hivatkozik a Szállítók relációra a SzállID küls kulcs segítségével, illetve az Áruk relációra az ÁruID segítségével. (Szállítók apa, Áruk apa, Szállít fiú.)
A hivatkozási épség megszorítás az adatbázis aktualizálása esetén három esetben sérülhet. 1) Új sor hozzáillesztése az R1 (fiú) relációba. 2) Törlés az R2 (apa) relációból. 3) Módosítás R1 (fiú) relációba, illetve R2 (apa) relációban.
1) Új sor hozzáillesztése az R1 relációba: a KK küls kulcs értékét csak akkor vihetjük be az R1 (fiú) reláció megfelel oszlopába, ha az már létezik els dleges kulcsként az R2 (apa) relációban. példa: A példa esetén nem illeszthetünk olyan alkalmazottat az Alkalmazottak relációba, amelynek a RészlegID attributúm értéke például 5, mivel ez nem létezik a Részlegek relációban. Lehetséges értékek az aktuális állapotban: 1, 2 és 9. 2) R2 (apa) relációból nem törölhetjük ki azokat az els dleges kulcsértékeket, melyekre van hivatkozás az R1 (fiú) relációból. példa: A példa esetén nem törölhetjük ki egyik részleget sem, mert mindegyikre van hivatkozás, ha netán kitörölnénk, az Alkalmazottak relációban maradnának ún. „lógó” sorok.
3) Módosítás felmerülhet mind a két relációban. – Ha az R1 (fiú) relációban módosítunk egy KK értéket, csak az R2 (apa) relációban létez re módosíthatjuk. – Ha az R2 (apa) relációban lev els dleges kulcsot akarjuk módosítani, csak akkor tehetjük meg, ha nincs rá hivatkozás az R1 (fiú) relációból. példa: Az Alkalmazottak relációban a „Kovács István”-ra vonatkozó bejegyzés esetén a RészlegID-t módosíthatjuk 1 vagy 9-re, de nem módosíthatjuk például 3-ra. A Részlegek egy RészlegID-jét sem módosíthatjuk, mivel mindegyikre van hivatkozás. Esetleg módosíthatnánk egy már létez értékre a Részlegek táblából, de mivel a RészlegID els dleges kulcs a Részlegek táblában, nem tehetjük meg.
Megszorítások SQL-ben – Az SQL2-ben az épségi megszorításokat az adatbázisséma részeként adjuk meg. – Mikor egy adatbázis alkalmazás fut, az ABKR ellen rzi, hogy a megszorítások teljesülnek-e, és ha nem, nem engedi meg a változtatást.
Egyedi kulcsok SQL-ben CREATE TABLE Áruk ( ÁruID INT PRIMARY KEY, ÁruNév CHAR(30), MértEgys CHAR(10) );
• összetett kulcs esetén CREATE TABLE Szállít ( SzállID INT, ÁruID INT, Ár REAL, PRIMARY KEY (SzállID, ÁruID) ); • Más lehet ség kulcsok deklárálására a UNIQUE kulcsszó • egy relációnak lehet több kulcsjelöltje, ezeket adjuk meg a UNIQUE kulcsszó segítségével. • A kulcsjelöltek közül egyet kiválasztunk els dleges kulcsnak és azt a PRIMARY KEY kulcsszó segítségével adjuk meg.
példa: Az Áruk tábla esetén kérhetjük, hogy az ÁruNév értékei egyediek legyenek: CREATE TABLE Áruk ( ÁruID INT PRIMARY KEY, ÁruNév CHAR(30) UNIQUE, MértEgys CHAR(10) ); CREATE TABLE Szállítók ( SzállID INT PRIMARY KEY, SzállNév CHAR(30) UNIQUE, CodFisc CHAR(10) UNIQUE, Cím CímDom; );
Els dleges kulcs és egyedi attribútumok Hasonlóság: • az ABKR-nek van gondja arra, hogy ne lehessen két sor a táblában, amelyben a kulcsként (PRIMARY KEY, UNIQUE) deklarált attribútumérték ugyanaz. Különbség: • Els dleges kulcs csak egy lehet, egyedi attribútum több is lehet. • Küls kulcs csak els dleges kulcsára hivatkozhat egy relációnak. • ABKR-t l függ en, az els dleges kulcsnak megfelel en létrehoz az ABKR indexállományt, egyedi attribútum deklarálása esetén az adatbázis adminisztrátor kell az indexállományt létrehozza, a keresés gyorsítása érdekében.
Küls kulcsok SQL-ben Küls kulcsot a CREATE TABLE utasításon belül kétféleképpen deklarálhatunk: 1. Ha a küls kulcs egyetlen attribútum, az attribútum után: REFERENCES ()
példa: CREATE TABLE Szállít ( SzállID INT REFERENCES Szállítók (SzállID), ÁruID INT REFERENCES Áruk (ÁruID), Ár REAL, PRIMARY KEY (SzállID, ÁruID) );
2. Ha a küls kulcs több attribútumból áll
FOREIGN KEY REFERENCES () ÁruCsoportok (ÁruCsopKod, Név); Anyagok (AnyagKod, Név), Kinek (KinekKod, Név) Gyártók (GyártóID, Név, Cím); Üzletek (ÜzletID, Név, Cím, TelefonSzám) Modellek (ModellID, Név, ÁruCsopKod, KinekKod, Fels részAnyagKod, BélésAnyagKod, TalpAnyagKod, GyártóID)
Gyárt (ModellID, Szám, Szín, GyártásiÁr) Árul (ÜzletID, ModellID, Szám, Szín, EladásiÁr, DbRaktáron) CREATE TABLE Gyárt ( ModellID INT, Szám INT, Szín VARCHAR(15), GyártásiÁr REAL, PRIMARY KEY (ModellID, Szám, Szín) );
CREATE TABLE Arul ( UzletID INT, ModellID INT, Szám INT, Szín VARCHAR(15), GyártásiÁr REAL, PRIMARY KEY (UzletID, ModellID, Szám, Szín) FOREIGN KEY (ModellID, Szám, Szín) REFERENCES Gyárt (ModellID, Szám, Szín), );
Hivatkozási épség fenntartása • Ha küls kulcsot deklarálunk, azt jelenti, hogy a küls kulcs bármely nem-NULL értéke el kell hogy forduljon a hivatkozott reláció megfelel attribútumában. • Az ABKR három lehetséges megoldást ajánl az adatbázis tervez jének, ahhoz, hogy ezt a megszorítást az adatbázis módosításai közben fenn tudja tartani.
1) Alapértelmezés szerinti eljárás: ha a feltétel megsérülne a módosítást az ABKR visszautasítja. 2) Továbbgy r z eljárás (CASCADE): a hivatkozott (apa) táblában történ törlés és módosítás esetén alkalmazható. A törölt sorral együtt a hivatkozott (apa) táblából törli a neki megfelel értékeket a hivatkozó (fiú) táblából. Ha módosítunk egy értéket a hivatkozott (apa) táblában, az ABKR módosítja a hivatkozó (fiú) táblában is a megfelel értékeket. 3) NULL értékre állítás módszere (SET NULL): a törölt vagy módosított apa sorhoz tartozó fiú sorokban a küls kulcs értékét NULLra változtatja.
példa: CREATE TABLE Szállít ( SzállID INT REFERENCES Szállítók (SzállID) ON DELETE SET NULL ON UPDATE CASCADE, ÁruID INT REFERENCES Áruk (ÁruID) ON DELETE SET NULL ON UPDATE CASCADE, Ár REAL, PRIMARY KEY (SzállID, ÁruID) );
Egymásra hivatkozás CREATE TABLE Alkalmazottak ( SzemSzám INT, Név VARCHAR(30), Fizetés REAL, SzülDat DATE DEFAULT ’1900-01-01’, RészlegID INT NOT NULL REFERENCES Részlegek (RészlegID), ); CREATE TABLE Részlegek ( RészlegID INT, Rnév VARCHAR(20), ManagerSzemSzám INT NOT NULL REFERENCES Alkalmazottak (SzemSzám));
Beállíthatjuk-e ezt az egymásra hivatkozást? • Az SQL2 ad erre lehet séget, úgy, hogy egy tranzakció keretén belül illesztenénk be egy sort a Részlegek táblába és az adott részleg managerét pedig az Alkalmazottak táblába és csak utána ellen rzi az ABKR a hivatkozási épséget, miután mind a két sort beillesztette a táblákba. Tranzakció végén ellen rzött megszorítást a DEFERED kulcsszó segítségével adhatjuk meg, a megszorítás deklarálásában. • Más megoldás: az egyik tábla esetében engedjünk NULL értéket a küls kulcsnak. Például a Részlegek relációban a ManagerSzemSzám attribútum értéke lehet NULL.
Attribútumértékekre vonatkozó megszorítások • a relációséma definálásakor adhatók meg. 1) attribútum értéke nem lehet NULL. NOT NULL kulcsszó-val CREATE TABLE Áruk ( ÁruID INT PRIMARY KEY, ÁruNév CHAR(30) NOT NULL, MértEgys CHAR(10) ); Fontos: Egyedi kulcs értéke sohasem lehet NULL, mivel így elveszítené azonosító szerepét. Az ABKR-ek általában nem engedik meg, hogy egyedi kulcsnak deklaráljunk olyan attribútumot, mely értéke lehet NULL.
2) Bonyolultabb megszorítás rendelhet egy attribútumhoz a CHECK kulcsszó segítségével. CREATE TABLE Csoportok ( CsopKod CHAR(3) PRIMARY KEY, Evfolyam INT CHECK (Evfolyam >= 1 and Evfolyam <= 5), SzakKod CHAR(3) ); vagy, csak a kérdéses sort megismételve: Evfolyam INT CHECK (Evfolyam IN (1, 2, 3, 4, 5)), Az ABKR visszautasítja azokat a hozzáillesztéseket, módosításokat, ahol ez a feltétel nem áll fenn.
Értéktartományokra vonatkozó megszorítások Egy attribútum értékeit úgy is korlátozhatjuk, hogy deklarálunk egy értéktartományt, és ezt az értéktartományt adjuk meg az attribútum típusaként. példa: Az Evfolyam lehetséges értékeit megadhatjuk az EvfolyamErtekek nev értéktartomány segítségével: CREATE DOMAIN EvfolyamErtekek INT CHECK (VALUE >= 1 and VALUE <= 5); A CREATE TABLE-ben a megfelel sor: Evfolyam EvfolyamErtekek,
Globális megszorítások Egy megszorítás hivatkozhat több attribútumra is, és akár több relációt érint feltételeket is tartalmazhat. 1) Sorra vonatkozó CHECK feltételek, amelyek egyetlen reláció soraira tesznek megszorításokat. példa: Kikötjük, hogy egy manager fizetése legalább 500 euró kell legyen.
CREATE TABLE Alkalmazottak ( SzemSzám INT PRIMARY KEY, Név CHAR(30), RészlegID INT REFERENCES Részlegek (RészlegID), Fizetés INT); CREATE TABLE Részlegek ( RészlegID INT PRIMARY KEY, RNév CHAR(30), ManagerSzemSzám INT REFERENCES Alkalmazottak (SzemSzám), CHECK (ManagerSzemSzám NOT IN (SELECT SzemSzám FROM Alkalmazottak WHERE Fizetés < 500) ) );
2) Önálló megszorítások, amelyek a feltételükben teljes relációkat vagy ugyanazt a relációt befutó több sorváltozót is tartalmazhatnak. CREATE ASSERTION utasítás segítségével adhatjuk meg és különálló sémaelemek lesznek. példa: A fenti példát megadhatjuk önálló megszorításként is a következ képpen: CREATE ASSERTION ManagerFizetes CHECK (NOT EXISTS (SELECT * FROM Alkalmazottak, Részlegek WHERE SzemSzám = ManagerSzemSzám AND Fizetés < 500) ); A megszorítások nagyon komplexek lehetnek.
Megszorítások módosítása Ahhoz, hogy módosítani, törölni tudjunk egy megszorítást, nevet kell adjunk neki. A CREATE ASSERTION esetén az utasítás részeként adtuk meg a nevet. A többi megszorítás esetén a CONTRAINT kulcsszó segítségével adhatunk neki nevet. Példa: RészlegID INT CONSTRAINT PK_Reszleg PRIMARY KEY,
Attribútumra vonatkozó megszorításnak is adhatunk nevet: Evfolyam INT CONSTRAINT EgyOt CHECK (Evfolyam >= 1 and Evfolyam <= 5), Táblához rendelt megszorításokat az ALTER TABLE utasítás segítségével, azon belül a DROP CONSTRAINT kulcsszóval törölhetjük. ALTER TABLE Csoportok DROP CONSTRAINT EgyOt; Táblához rendelhetünk plusz megszorításokat, a tábla deklaráció után is az ALTER TABLE utasítással, ADD CONSTRAINT kulcsszó segítségével. ALTER TABLE Csoportok ADD CONSTRAINT EgyOt CHECK (Evfolyam >= 1 and Evfolyam <= 5);
Ajánlott nevet adni a hivatkozási épség megszorításoknak is, hogy törölhessük ket, esetleg újakat értelmezhessünk, anélkül, hogy a tábladeklarációt megváltoztatnánk.
ALTER TABLE Alkalmazottak ADD CONSTRAINT FK_AlkReszl FOREIGN KEY (RészlegID) REFERENCES Részlegek (RészlegID), ALTER TABLE Alkalmazottak DROP CONSTRAINT FK_AlkReszl
Az önálló megszorításokat a DROP ASSERTION utasítással törölhetjük. DROP ASSERTION ManagerFizetes;
