20.4. 2010
Stavek CREATE VIEW
SQL ukaz, fizično ne obstajajo na disku.
Sintaksa:
CREATE VIEW ViewName [(newColumnName [,...]) ]
AS subselect
[WITH [[CASCADED | LOCAL] CHECK OPTION]
Obcijska možnost: Določimo pogled kot SELECT stavek.
Dodatne obcije:
[WITH [CASCADED | LOCAL] CHECK OPTION]
Posebna opcija: WITH CHECK OPTION:
Ne omogoča, da bi v pogled ki ima določene pogoje, vpisali nekaj kar temu pogledu ne ustrza.
( Če te obcije ne določimo, tak pogled lahko kreiramo. Zgolj do njega nikoli ne bi prišlo. )
Za ukinitev pogleda ukaz: DROP VIEW
Tudi tu možnost: Ristict / CASCADE
Primer:
Promblemska domena sob in hotelov. Želimo narediti poglede, ki bodo služili posameznim hotelom.
§ Kreiraj pogled RecepcijaSlon tako, da bodo v recepciji hotela Slon videli samo svoje rezervacije.
CREATE VIEW RecepcijaSlon
AS SELECT *
FROM Booking B, Hotel H
WHERE B.hotelNo = H.hotelNo AND
H.hotelName = ‘Hotel Slon’;
Primer vertikalnega/ horizontalnega pogleda.
Vertikalni: Izključimo določene stolpce. Tiste ki so za konkretnega uporabnka uporabni.
Horizontalni: Prikaz vrstic.
Primer horizontalnega pogleda:
§ Kreiraj pogled RecepcijaSlon tako, da bodo v recepciji hotela Slon videli samo svoje rezervacije.
CREATE VIEW RecepcijaSlon
AS SELECT *
FROM Booking B, Hotel H
WHERE B.hotelNo = H.hotelNo AND
H.hotelName = ‘Hotel Slon’;
Primer vertikalnega pogleda:
§ Izdelaj pogled vseh sob hotela Slon brez cen.
CREATE VIEW RoomsSlon
AS SELECT R.roomNo, R.type
FROM Room R, Hotel H
WHERE R.hotelNo = H.hotelNo AND
H.hotelName = ‘Hotel Slon’;
Primer pogleda z grupiranjem podatkov:
Primer: Število rezervacij v vsakem hotelu.
CREATE VIEW HotelRez (hNo, hName, hStRez)
AS (
SELECT H.hotelNo, H.hotelName, COUNT(*) AS Cnt
FROM Hotel H, Booking B
WHERE H.hotelNo = B.hotelNo AND
B.dateFrom >= date()
GROUP BY H.hotelNo, H.hotelName)
Izvedba pogleda:
Imamo SELECT stavek, ki izpisuje iz pogleda, ki smo ga prej kreirari , podatke.
Izpisujemo št. hotela, št. rezervaci in ime hotela. Sortiramo jih po imenu hotela.
-Imena stolpcev pogleda iz SELECT stavka so
prevedena v imena SELECT stavka, ki definira pogled.
-Ime iz FROM sklopa pogleda, zamenjamo z imeni iz FROM sklopa SELECT stavka, ki definira pogled.
- WHERE sklop SELECT stavka se združi z WHERE sklopom iz SELECT stavka, ki definira pogled.
OMEJITVE POGLEDOV:
Kakšna pravila morajo veljati, da po ISO standardu lahko spreminjamo poglede?
§ Pri kreiranju in uporabi pogledov veljajo številne omejitve:
– Če je stolpec A v pogledu V definiran z agregatno funkcijo:
§ Stolpec A lahko nastopa le v SELECT ali ORDER BY sklopu v stavkih, ki do pogleda V dostopajo;
§ Stolpec A ne smemo uporabiti v WHERE sklopu niti ne sme nastopati kot argument v agregatni funkciji poizvedbe nad pogledom V.
– Pogled, ki je kreiran z združevanjem (GROUP BY), ne smemo stakniti z osnovno tabelo ali pogledom.
§ Spreminjanje vsebine pogledov ni vedno možno
§ Veljajo mnoge omejitve
§ ISO standard določa, da je pogled možno spreminjati samo, če veljajo naslednji pogoji:
– Opcija DISTINCT v pogledu ni uporabljena;
– Vsak element v SELECT seznamu stavka, ki definira pogled, se nanaša na stolpec ene izmed osnovnih tabel; noben stolpec se ne pojavi več kot enkrat;
– FROM sklop v pogledu se nanaša samo na eno tabelo ali pogled, pri čemer pogled ne sme temeljiti na stiku, uniji, preseku ali razliki;
– V pogledu ni vgnezdenih poizvedb;
– Sklopa GROUP BY in HAVING v pogledu nista uporabljena;
Za primere glej prosojnice!
WITH CHECK OPTION:
Vrstice v pogledu obstajajo, če izpolnjujejo WHERE pogoj SELECT stavka, ki pogled definira.
-če se vrstica spremeni, tako da ne zadošča več pogoju, se izbriše iz pogleda.
-Nove vrstice se v pogledu pojavijo lle, če ustrezajo WHERE pogoju.
WITH CHECK OPTION prepoveduje selitev vrstic iz pogleda.
- na volju sta tudi opciji LOCAL in CASCADED.
Nad pogledom lahko naredimo še en pogled, itd.
PREDNOSTI/ SLABOSTI POGLEDOV:
§ PREDNOSTI
– Podatkovna neodvisnost
– Ažurnost
– Večja varnost
– Manjša kompleksnost
– Udobnost
– Prilagodljivost
– Podatkovna celovitost
. SLABOSTI:
– Omejitve spreminjanja
– Omejitve strukture
– Slabša učinkovitost
MATERIALIZIRANI POGLEDI:
Dodaten način izvedbe pogleda, ki ga približa tabeli.
Ima podatke dejansko shranjene na disku.
Iskanje je kasnejše.
To stanje moramo potem vzdrževati. ( L )
Vaja:
Napiši poizvedbo v SQL jeziku, ki bo izpisala naziv hotela z najvišjo povprečno ceno hotelskih sob.
SELECT H.hotelName, avg(R.price) AS AvgPrice
FROM Room AS R, Hotel AS H
WHERE R.hotelNo = H.hotelNo
GROUP BY R.hotelNo, H.hotelName
HAVING avg(R.price) = (SELECT max(AvgPrice) AS maxAvgPrice
FROM (SELECT R.hotelNo, avg(R.price) AS AvgPrice
FROM Room AS
GROUP BY R.hotelNo))
TRANSAKCIJE:
SQL definira tranaskcijski model z ukazoma COMMIT in ROLLBACK.
Vse kar naredimo znotraj transakcije še ni vidno navbzven drugim transakcijam.
Transakcijo zaključimo s:
COMMIT ukazom—zadeve so dokončne.
ROLLBACK: eksplicitni ukaz. Stanje podatkovne baze naj se ponovi nazaj.
Implicitno zaključevanje transakcije.
Sintaksa za definiranje transakcije:
SET TRANSACTION
[READ ONLY | READ WRITE] |
[ISOLATION LEVEL READ UNCOMMITTED |
READ COMMITTED|REPEATABLE READ |SERIALIZABLE ]
Določiti moramo izolacijski nivo.
READ ONLY- pove, da transakcija vključuje samo operacije, ki se iz baze berejo.
INSOLATION LAVEL- pove stopnjo interakcije, ki jo SUPB dovoli med to in drugimi transakcijami.
KRŠENJE SERABILNOSTI:
V transakcijah je mozno brati podatke, tudi če še niso dokončane.
– DIRTY READ: transakcija prebere podatek, ki je bil zapisan s strani druge transakcije, čeprav je še nepotrjen.
– NONREPEATABLE READ read: transakcija ponovno prebere podatek, ki ga je predhodno že prebrala, če ga je neka druga potrjena transakcija spremenila ali brisala v vmesnem obdobju.
– PHANTOM READ: transakcija izvede poizvedbo, ki vrača množico zapisov, ki ustrezajo iskalnem pogojem. Ko transakcija ponovi poizvedbo kasneje, se v rezultatu pojavijo dodatne vrstice, ki so bile v vmesnem času dodane s strani druge potrjene transakcije.
TAKOJŠNE IN ZAPOZNELE TRANSAKCIJE:
Včasih želimo, da se omejitve ne bi upoštevale takoj, po vsakem SQL stavku, temveč ob zaključku transakcije.
Omejitve lahko definiramo kot:
INITIALLY IMMEDIATE –takojšno previrjanje
INITIALLY DEFERRED – preverjanje šele na koncu
§ Če izberemo INITIALLY IMMEDIATE (privzeta možnost), lahko določimo tudi, ali je zakasnitev moč določiti kasneje. Uporabimo [NOT] DEFERRABLE.
NADZOR DOSTOPA ( Z VIDIKA SQLa) :
-subjektivno določen nadzor dostopa
-Obvezen nadzor dostopa
SUBJEKTIVNI: Vsak uporanik ima določene pravice, omejitve. Za vsak objekt imamo lastnika, ki določi kaj bo dovolil in kaj ne. Nekega centralnega nadzora ni. Tak način je tvegan.
OBVEZEN: Vsak objekt PB ima določeno stopnjo zaupanja. Vsak subjek potrebuje za delo raven zaupanja nad delom. Ne moremo dodaljevati pravic. Precej bolj varen in kompleksen sistem.
Vsak objekt ki ga v SQLu kreiramo ima lastnika. Običajno tisti, ki ga je kreiral. Pravice: SELECT, INSERT, UPDATE, DELETE ;
REFERENCES, USAGE
– SELECT – pravica branja podatkov
– INSERT – pravica dodajanja podatkov
– UPDATE – pravica spreminjanja podatkov (ne tudi brisanja)
– DELETE – pravica brisanja podatkov
– REFERENCES – pravica sklicevanja na stolpce določene tabela v omejitvah
– USAGE – pravica uporabe domen, sinonimov, znakovnih nizov in drugih posebnih objektov podatkovne baze
Ukaz GRANT – dodeljevanje pravice ostalim uporabnikom
Pri pogledu: ne dobimo nujno vseh pravic.
Sintaksa GRAND na primeru:
GRANT {PrivilegeList | ALL PRIVILEGES}
ON ObjectName
TO {AuthorizationIdList | PUBLIC}
[WITH GRANT OPTION]
§ PrivilegeList – je sestavljen iz ene ali več pravic, ločenih z vejico (INSERT, UPDATE,...)
§ ALL PRIVILEGES – dodeli vse pravice.
§ PUBLIC – omogoča dodelitev pravic vsem trenutnim in bodočim uporabnikom.
§ ObjectName – se nanaša na osnovno tabelo, pogled, domeno, znakovni niz, dodelitve in prevedbe.
§ WITH GRANT OPTION – dovoljuje, da uporabnik naprej dodeljuje pravice.
WITH GRAND OPTION: pravica nadaljnega dodaljevanja pravic naprej.
Primeri dodaljevanja pravic:
§ Vsem vodjem oddelkov dodaj vse pravice nad tabelo Delavec.
GRANT ALL PRIVILEGES
ON Delavec
TO VodjaOddelka WITH GRANT OPTION;
§ Uporabnikom Osebje in Direktor dodeli SELECT in UPDATE pravice nad stolpcem Plača v tabeli Delavec.
GRANT SELECT, UPDATE (Plača)
ON Delavec
TO Osebje, Direktor;
Z ukazom REVOKE pravice odvzamemo!
POMEN DOSTOPA PRAVIC:
§ ALL PRIVILEGES določa vse pravice, ki jih je uporabnik, ki REVOKE uporabi, dodelil uporabniku ali uporabnikom, na katere se REVOKE nanaša.
§ GRANT OPTION FOR – omogoča, da se pravice, ki so bile dodeljene prek opcije WITH GRANT OPTION ukaza GRANT, odvzema posebej in ne kaskadno.
§ RESTRICT, CASCADE – enako kot pri ukazu DROP
REVOKE ukaz ne uspe, kadar SUPB ugotovi, da bi njegova poizvedba povzročila zapuščenost objektov. (== Nihče več nima pravice do objektov.)
§ Za kreiranje določenih objektov so lahko potrebne pravice. Če take pravice odstranimo, lahko dobimo zapuščene objekte.
– Če uporabimo opcijo CASCADE, bo REVOKE ukaz uspel tudi v primeru, da privede do zapuščenih objektov. Kot posledica bodo ti ukinjeni.
§ Primeri: Če uporabnik Ua odvzema pravice uporabniku Ub potem pravice, ki so bile uporabniku Ub dodeljene s strani drugih uporabnikov, ne bodo odvzete.
§ Odvzemi SELECT pravice nad tabelo Delavec vsem uporabnikom.
REVOKE SELECT
ON Delavec
FROM PUBLIC;
§ Uporabnikom iz skupine Osebje in Direktor odvzemi vse pravice na tabelo Delavec.
REVOKE ALL PRIVILEGES
ON Delavec
FROM Osebje, Direktor;
QBE – Query By Example
Vizualen način dostopa do podatkov.
Z vzorcem povemo kakšni podatki nas iz podatkovne baze zanimajo.
Temelji na domensknem relacijskem računu.
Za pisanje ukazov ad-hock.
QBE uporabnikom obogoča:
§ QBE uporabnikom omogoča:
– Poizvedovati po podatkih ene ali več tabel.
– Določiti stolpce, ki jih želimo imeti v odgovoru (projekcija).
– Določiti kriterije za izbiro vrstic (selekcija).
– Izvajati izračune nad podatki v tabelah.
– Dodajati in brisati zapise.
– Spreminjati vrednosti v poljih.
– Kreirati nove tabele in stolpce.
Za primere iz Accessa glej prosojnice!
NASLEDNJIČ: 4. maj
NAČRTOVANJE PB
Objave
