POIZVEDOVANJE V SQL-3.del + QBE 20. April

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 R

                                                  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