#213
Część III
Zarządzanie bazą danych
#215
Rozdział 10.
Widoki
Podczas użytkowania bazy danych często zachodzi konieczność wielokrotnego korzystania z tych samych zapytań. Widok pozwala zapisać kryteria tworzące określone zapytanie, a następnie odwoływać się do tego zapytania w dowolnej chwili, bez konieczności ponownego wpisywania całej instrukcji. Widoki są tak naprawdę wirtualnymi tabelami, które traktują dane generowane przez zapytanie SELECT, tak jakby była to faktycznie tabela bazy danych.
W standardzie ANSI SQL widoki określane są mianem tabel widokowych (ang. viewed tables), natomiast prawdziwe tabele, stanowiące podstawę do tworzenia widoków, noszą miano tabel bazowych (ang. base tables).
Tabele widokowe (lub widoki) umożliwiają zmianę sposobu prezentowania danych w zależności od użytkownika, a także zapamiętywanie często stosowanych zapytań, dzięki czemu nie trzeba tworzyć ich od nowa za każdym razem, kiedy zachodzi potrzeba ich użycia. Stanowią one również użyteczną warstwę abstrakcji pomiędzy zawartością tabeli bazowej, a tym co widzi użytkownik.
Najważniejszą rzeczą, jaką należy zrozumieć w odniesieniu do widoków, jest to, iż nie są one chwilowymi zbiorami danych zwracanymi przez zapytanie, lecz raczej kopią samego wyrażenia SELECT. Za każdym razem, kiedy odwołujemy się do widoku, uruchamiane jest związane z nim zapytanie, po czym zwracane są aktualne dane z tabel bazowych.
Tworzenie widoków
Podczas stosowania widoków można korzystać z operacji relacyjnych, takich jak projekcja, restrykcja i złączenie, do tworzenia pseudotabel zawierających jedynie dane ograniczone do zbioru najbardziej użytecznego z punktu widzenia użytkownika zadającego pytania do bazy danych. Przeanalizujmy wyrażenie SQL z listingu 10.1.
#216
-----------------------------
Listing 10.1. Standardowe wyrażenie SELECT
SELECT movie_title, director_id
FROM Movies
MOVIE_TITLE DIRECTOR_ID
-----------------------------
Minerał House 1
Prince Kong 10
The Code Warrior 2
Bili Durham 9
Codependence Day 1
The Linux Files 2
SQL Strikes Back 9
The Programmer 1
Hard Code 10
The Rear Windows 1
10 rows selected.
-----------------------------
Rezultatem w listingu 10.1 jest zwykła projekcja tabeli Movies. Projekcję taką można zachować do przyszłego użytku - w tym celu zamiast zwykłego zapytania należy stworzyć widok, co demonstruje listing 10.2.
-----------------------------
Listing 10.2. Wyrażenie CREATE VIEW
CREATE VIEW Movie_Director
AS
SELECT movie_title, dlrector_id
FROM Movies
View created.
-----------------------------
Po utworzeniu widoku można używać go jako źródła danych w miejsce tabeli - przykład takiej operacji pokazuje listing 10.3.
-----------------------------
Listing 10.3. Zapytanie wydobywające dane z widoku
SELECT *
FROM Movie_Director
MOVIE_TITLE DIRECTOR_ID
-----------------------------
Minerał House 1
Prince Kong 10
The Code Warrior 2
Bili Durham 9
Codependence Day 1
The Linux Files 2
SQL Strikes Back 9
The Programmer 1
Hard Code 10
The Rear Windows 1
10 rows selected.
-----------------------------
Najistotniejszą cechą widoków jest to, że nie przechowują one zbioru danych zwróconego przez wyrażenie SELECT, lecz tworzą swego rodzaju pseudotabelę bazującą na wynikach zapytania SELECT zapisanego w danym widoku.
#217
Innymi słowy, jeśli zmodyfikujemy dane w tabeli stanowiącej źródło widoku, a następnie pobierzemy z niego dane, to otrzymamy wynik z odpowiednio odzwierciedlonymi zmianami. Przykład takiego działania przedstawia listing 10.4.
-----------------------------
Listing 10.4. Widoki odzwierciedlają zmiany -wprowadzane do tabel bazowych
UPDATE Movies
SET movie_title = 'Vegetable House'
WHERE movie_title = 'Mineral House'
1 row updated.
SELECT *
FROM Movie_Director
WHERE movie_title = 'Vegetable House'
MOVIE_TITLE DIRECTOR_ID
-----------------------------
Vegetable House 1
-----------------------------
Wyniki zapytania z listingu 10.4 odzwierciedlają zmianę wprowadzoną do tabeli Movies. Przy odpytywaniu widoków następuje zawsze odwołanie do ich tabel bazowych, dzięki czemu zapytanie wydobywa dane aktualne. W niektórych przypadkach istnieje również możliwość uaktualnienia tabel bazowych poprzez widok (zagadnienie to będzie omawiane w dalszej części tego rozdziału).
Inną istotną cechą widoków, którą można wywnioskować na podstawie listingu 10.4, jest możliwość stosowania klauzuli WHERE w wyrażeniach SELECT wykonywanych na widokach. W pierwszej kolejności wykonana zostaje klauzula WHERE widoku, w wyniku której generowana jest pewna wirtualna tabela wyników. Na tabeli tej wykonywana jest następnie klauzula WHERE samego zapytania.
Korzyści płynące ze stosowania widoków
Kilka korzyści, płynących ze stosowania widoków, zostało przedstawionych we wstępie do niniejszego rozdziału. Użytkowanie widoków ma charakter całkowicie dobrowolny - jednak w kilku przypadkach ich zastosowanie może ułatwić realizację określonych zadań. Poniższe sekcje opisują niektóre z zalet zastosowania widoków we własnej bazy danych.
Wygoda
Największą zaletą, wynikającą ze stosowania widoków, jest wygoda. Widoki umożliwiają prezentowanie danych w sposób najbardziej wygodny dla użytkowników, niezależnie od struktury tabel fizycznych. Użytkownikom o wiele łatwiej wpisać polecenie SELECT * dla widoku zawierającego wyłącznie kolumny najczęściej przez nich używane, niż wypisywać poszczególne nazwy kolumn przy korzystaniu
#218
z tabeli bazowej (oczywiście, istnieje również możliwość wybierania konkretnych kolumn z widoku). Jeżeli w trakcie pracy bazy danych okazuje się, że bardzo często wykonywany jest pewien typ złączenia lub podzapytania, zapytania tego typu można utrwalić w postaci widoków, które ułatwią pracę użytkownikom bazy.
Ukrywanie efektów normalizacji
Zgodnie z tym, o czym mówiliśmy w rozdziale drugim, zachowanie integralności danych podczas operacji uaktualniania, usuwania i wstawiania jest możliwe tylko w przypadku ich znormalizowania. Na ogół wymaga to rozbicia dużych tabel na mniejsze, bazując na zależności funkcjonalnej.
Niestety, po znormalizowaniu danych i rozbiciu ich na kilka tabel, ponowne ich połączenie wymaga użycia operacji złączenia w zapytaniu.
Używając widoków można zapamiętać takie zapytania, dzięki czemu dane w postaci pierwotnej staną się dostępne bez potrzeby pisania zapytania łączącego tabele. Widok sam w sobie zawiera pytanie złączające, natomiast pisane przez użytkowników zapytania operują na wynikach tego złączenia.
Widoki używające złączeń zostaną omówione w dalszej części rozdziału - teraz ograniczymy się jedynie do przedstawienia przykładu demonstrującego takie rozwiązanie. W wyniku procesu normalizacji informacje związane ze studiami filmowymi znajdują się w tabeli studios, natomiast dane dotyczące filmów pozostają w tabeli Movies. Używając widoku można stworzyć wirtualną tabelę, która zawierać będzie listę filmów z faktycznymi nazwami studiów filmowych, zamiast ich identyfikatorów - listing 10.5.
-----------------------------
Listing 10.5. Widok łączący tabele Moyies i Studios
CREATE VIEW Movie_With_Studio
AS
SELECT movie_title, budget, gross, studio_name,
studio_city, studio_state
FROM Movies, Studios
WHERE Movies.studio_id = Studios.studio_id
SELECT movie_title, studio_name
FROM Movie_With_Studio
MOVIE_TITLE STUDIO_NAME
-----------------------------
Vegetable House Giant
Prince Kong MPM
The Code Warrior MPM
Bili Durham Delighted Artists
Codependence Day Giant
The Linux Files MPM
SQL Strikes Back Delighted Artists
The Programmer Delighted Artists
Hard Code FKG
The Rear Windows Giant
10 rows selected
-----------------------------
#219
Ograniczanie danych dostępnych dla użytkowników
W praktyce zdarzają się sytuacje, kiedy określonej grupie użytkowników trzeba udostępnić dane zapisane w określonej tabeli, ale bez możliwości przeglądania zawartości tej tabeli w całości. W naszym przypadku nie chcielibyśmy na przykład, aby pewni użytkownicy mieli dostęp do kolumn przechowujących budżety i dochody filmów. Jeżeli będą oni mieli prawo do przeglądania tabeli Movies, informacje te będą dla nich dostępne. Tworząc i udostępniając użytkownikom jedynie widok tabeli Movies pozbawiony pól z danymi o budżecie i dochodach, zapobiegamy możliwości zobaczenia przez nich danych, do których nie mają prawa. Rozwiązanie takie demonstruje listing 10.6.
-----------------------------
Listing 10.6. Widok prezentujący okrojoną wersję tabeli Movies
CREATE VIEW Movies_Minus_Financials
AS
SELECT movie_id, movie_title, studio_id, release_date, director_id
FROM Movies
SELECT*
FROM Movies_Minus_Financials
MOVIE_ID MCWTE_TITLE STUDIO_ID RELEASE_D DIRECTOR_ID
-----------------------------
1 Vegetable House 1 01-JAN-75 1
2 Prince Kong 2 01-MAY-79 10
3 The Code Warrior 2 01-SEP-91 2
4 Bili Durham 3 15-JUL-88 9
5 Codependence Day 1 01-JUL-97 1
6 The Linux Files 1 22-AUG-93 2
7 SQL Strikes Back 2 01-NOY-98 9
8 The Prograiraner 3 17-APR-93 1
9 Hard Code 4 18-APR-95 10
10 The Rear Windows 1 11-JUL-87 1
10 rows selected.
-----------------------------
Oczywiście, do tej pory nie mówiliśmy jeszcze, w jaki sposób określa się dostęp użytkowników do określonych zasobów, takich jak tabele lub widoki. Prawa dostępu do bazy danych kontrolowane są przez wyrażanie GRANT i REVOKE, które zostaną omówione w rozdziale 11. „Model bezpieczeństwa SQL".
Tworzenie warstwy abstrakcji
Według podstawowych zasad projektowania, istnienie warstwy abstrakcji uznaje się za rzecz wskazaną. Zaimplementowanie jej w systemie umożliwia ukrycie przed użytkownikiem korzystającym z danych ich faktycznej struktury. Motor bazy danych (ang. database engine) udostępnia warstwę abstrakcji pomiędzy danymi przechowywanymi w określonej strukturze na dysku twardym, a użytkownikiem. Gdy użytkownik wykonuje zapytanie SQL, motor bazy danych tłumaczy je na instrukcje niezbędne do wydobycia potrzebnych danych. Na podobnej zasadzie widoki mogą zostać użyte do wprowadzenia warstwy abstrakcji pomiędzy użytkownika a tabele bazowe, w oparciu o które widoki te zostały zbudowane.
#220
Po wprowadzeniu zmian do schematu tabeli bazowej można uaktualnić widoki w taki sposób, aby odzwierciedliły one nowe zmiany bez zakłócenia struktury danych widocznej dla użytkownika.
Wykorzystanie tego mechanizmu wymaga tworzenia widoków w taki sposób, aby zmiany w tabelach bazowych nie powodowały zmian w widokach. Jednym ze sposobów osiągnięcia takiej sytuacji jest stosowanie aliasów kolum.
Aliasy kolumn
Kolumnom widoku można nadać nazwy w wyrażeniu CREATE tworzącym ten widok. Poszczególne nazwy, oddzielone od siebie przecinkami, umieszcza się w formie listy bezpośrednio za nazwą widoku - listing 10.7.
-----------------------------
Listing 10.7. Widok z aliasami kolumn
CREATE VIEW Movie_Financials (title, budget, gross)
AS
SELECT movie_title, budget, gross
FROM Movies
SELECT *
FROM Movie_Financials
TITLE BUDGET GROSS
-----------------------------
Vegetable House 20 30
Prince Kong 3.25 51.5
The Code Warrior 10.3 17.8
Bili Durham 10.1 15.6
Codependence Day 15 30
The Linux Files 22.2 17.5
SQL Strikes Back 5 10
The Programmer 50 45.3
Hard Code 77 30
The Rear Windows 50 17.5
10 rows selected.
-----------------------------
Jak widać, nazwa kolumny dla pola movie_title została na potrzeby widoku zmieniona na title. Ponieważ pierwotne nazwy kolumn są jasne i czytelne, utworzenie aliasów kolumn dla tego widoku wydaje się być bezcelowe.
Mechanizm aliasów przydaje się tak naprawdę w widokach zawierających na liście instrukcji SELECT wyrażenia lub w widokach tworzonych przez złączenia, których kolumny specyfikowane są przy użyciu notacji tabela.kolumna.
Niektóre bazy danych umożliwiają również tworzenie aliasów kolumn przy użyciu słowa kluczowego AS wewnątrz wyrażenia CREATE, niemniej jednak metoda standardowa opiera się na liście nazw kolumn umieszczonej za nazwą widoku w wyrażeniu CREATE VIEW.
#221
Widoki pojedynczych tabel
Widok utworzony w listingu 10.2 był widokiem pojedynczej tabeli. Bazował on na projekcji kolumn wydobytych wyłącznie z tabeli Movies. Widoki pojedynczych tabel są często stosowane, gdy tabela zawiera dużą liczbę kolumn i wierszy, umożliwiając użytkownikom wykonywanie zapytań w odniesieniu do podzbiorów danych tej tabeli. Rozwiązanie takie upraszcza pracę użytkownika, który nie musi borykać się z pełną zawartością tabeli. Gdy administrator bazy danych wykryje, że użytkownicy często wykonują zapytania z takim samym zestawem warunków w klauzuli WHERE lub listą argumentów wyrażenia SELECT, może ułatwić im pracę przez stworzenie widoku bazującego na regularnie wykonywanym zapytaniu SELECT.
Widok pojedynczej tabeli jest również przydatny, gdy niezbędne jest ograniczenie zakresu danych, jakie widzi użytkownik do pewnych kolumn i/lub wierszy tabeli. Dając użytkownikowi dostęp do widoku zamiast do całej tabeli, można ukryć przed nim dane, których nie powinien widzieć ze względów bezpieczeństwa lub po prostu dlatego, że nie są one przedmiotem jego zainteresowania.
Podczas tworzenia widoków można posługiwać się zarówno operacjami restrykcji, jak i projekcji. W poniższym przykładzie (listing 10.8) tworzony jest widok zawierający wyłącznie nazwiska i numery telefonów osób pochodzących ze stanu Teksas.
-----------------------------
Listing 10.8. Widok zawierający nazwiska i numery telefonów osób pochodzących ze stanu Teksas
CREATE VIEW Texas_Phone_List
AS SELECT person_fname, person_lname, person_phone
FROM People
WHERE person_state = 'TX'
View created.
SELECT *
FROM Texas_Phone_list
PERSON_FNA PERSON_LNA PERSON_PHO
-----------------------------
Brian Smith 2185551355
Paul Monk 7135558193
Reece Randall 7135550143
Carol Delano 2818691355
-----------------------------
Widoki z wyrażeniami i funkcjami
Na liście argumentów wyrażenia SELECT oprócz kolumn mogą być również umieszczane wywołania funkcji i wyrażenia matematyczne. Tego typu wyrażenia mogą również być umieszczane w widokach. Załóżmy dla przykładu, że wartości pól budget i gross w tabeli Movies powinny być wyświetlane z zaokrągleniem do miliona. Używając odpowiedniego wyrażenia matematycznego w instrukcji SELECT tworzącej widok, można pomnożyć wartości wymienionych pól przez milion, zanim jeszcze zostaną wyświetlone. Demonstruje to listing 10.9.
#222
-----------------------------
Listing 10.9. Widok korzystający z wyrażenia matematycznego w instrukcji SELECT
CREATE VIEW Movie_Financials
AS
SELECT movie_title, gross * 1000000, budget * 1000000
FROM Movies
ERROR at line 3:
ORA-00998: must name this expression with a column alias
-----------------------------
Przy próbie utworzenia tego widoku wiele baz danych zwróci błąd ze względu na nieprawidłowe nazwy kolumn. Jeżeli argumentami instrukcji SELECT są wyrażenia matematyczne, nie można zapomnieć również o dodaniu aliasów kolumn. Powodem, dla którego większość baz danych nie pozwoli na utworzenie widoku w formie, jaką prezentuje listing 10.9, jest brak możliwości wyspecyfikowania kolumn podczas pisania zapytań odwołujących się do tego widoku.
Problem ten można rozwiązać stosując aliasy kolumn. Listing 10.10 prezentuje widok z listingu 10.9 uzupełniony o aliasy kolumn.
-----------------------------
Listing 10.10. Widok korzystający z wyrażeń matematycznych i aliasów kolumn
CREATE VIEW Movie_Financials (title, actual_gross, actual_budget)
AS
SELECT raovie_title, gross * 1000000, budget * 1000000
FROM Movies
View created.
SELECT title, actual_budget, actual_gross
FROM Movie_Financials
MOVIE_TITLE ACTOAL_BUDGET ACTUAL_GROSS
-----------------------------
Vegetable House 20000000 30000000
Prince Kong 3250000 51500000
The Code Warrior 10300000 17800000
Bili Durham 10100000 15600000
Codependence Day 15000000 30000000
The Linux Files 22200000 17500000
SQL Strikes Back 500000 10000000
The Programmer 50000000 45300000
Hard Code 77000000 30000000
The Rear Windows 50000000 17500000
10 rows selected.
-----------------------------
Kolumny w powyższym widoku posiadają prawidłowe nazwy. Używanie aliasów dla nazw kolumn w przypadku stosowania wyrażeń na liście polecenia SELECT widoku jest szczególnie ważne, jeżeli zależy nam na możliwości pobierania kolumn z widoku w oparciu o ich nazwy.
Gdybyśmy chcieli wydobyć jedynie wartość dochodu (kolumnę gross) z widoku przedstawionego w listingu 10.9, okazałoby się to niemożliwe, ponieważ kolumna ta nie posiadała nadającej się do użytku nazwy. Utworzenie aliasów kolumn przez wyrażenie CREATE VIEW w listingu 10.10 zlikwidowało ten problem i umożliwiło wykonywanie poleceń typu SELECT, jakie prezentowane jest w przykładowym listingu 10.11.
#223
-----------------------------
Listing 10.11. Wyrażenie SELECT wydobywające pojedynczą kolumnę, z widoku
SELECT actual_gross
FROM Movie_Financials
WHERE title = 'The Code Warrior'
ACTUAL_GROSS
------------
17800000
-----------------------------
Jak wspomniano wcześniej, na liście argumentów polecenia SELECT widoku można również umieszczać wywołania funkcji. Przykładowo do stworzenia widoku nie rozróżniającego wielkich i małych liter można użyć funkcji UPPER (), umieszczając ją na liście argumentów polecenia SELECT i jednocześnie tworząc aliasy kolumn w wyrażeniu CREATE VIEW, tak jak przedstawia to listing 10.12.
-----------------------------
Listing 10.12. Widok korzystający z wywołań funkcji na liście argumentów polecenia SELECT
CREATE VIEW Movies_0pper (title)
AS
SELECT UPPER(movie_title)
FROM Movies
SELECT title
FROM Movies_Upper
VEGETABLE HOUSE
PRINCE KONG
THE CODE WARRIOR
BILL DURHAM
CODEPENDENCE DAY
THE LINUX FILES
SQL STRIKES BACK
THE PROGRAMMER
HARD CODE
THE REAR WINDOWS
10 rows selected.
-----------------------------
Widoki z funkcjami agregującymi
Wyrażenia SELECT używane w widokach mogą korzystać z funkcji agregujących. Przykładem niech będzie widok z listingu 10.13.
-----------------------------
Listing 10.13. Widok korzystający z funkcji agregującej
CREATE VIEW Total_Movie_Revenue (total_revenue)
AS
SELECT SUM(gross)
FROM Movies
View created.
SELECT *
FROM Total_Movie_Revenue
TOTAL_REVENUE
--------------
213.1
-----------------------------
#224
Jak widać na podstawie wyników zapytania, powyższy widok nie jest szczególnie użyteczny, gdyż wynikiem jego działania jest tabela zawierająca wyłącznie jeden wiersz i jedną kolumnę. Niemniej jednak, przy zastosowaniu klauzuli GROUP BY można tworzyć widoki zwracające większe i bardziej użyteczne zbiory danych. Dla przykładu widok utworzony w listingu 10.14 zwraca sumę oraz średnią wartość budżetu dla każdego studia.
-----------------------------
Listing 10.14. Widok korzystający z funkcji agregujących oraz klauzuli GROUP BY
CREATE VIEW Budget_By_Studio (studio, total_budget, avg_budget)
AS
SELECT studio_id, SUM(budget), AVG(budget)
FROM Movies
GROUP BY studio_id
View created.
SELECT *
FROM Budget_By_Studio
STUDIO TOTAL_BUDGET AVG_BUDGET
-----------------------------
1 85 28.3333333
2 35.75 11.9166667
3 65.1 21.7
4 77 77
-----------------------------
Tak, jak należy używać aliasów dla nazw kolumn w widoku, w którym zastosowane zostały funkcje matematyczne, tak samo należy stosować je przy tworzeniu widoków z funkcjami agregującymi. Nawet jeśli baza danych umożliwia tworzenie widoków nie posiadających prawidłowych nazw dla zapytań, pozostawienie takich nazw na liście argumentów wyrażenia SELECT utrudnia czytanie wyników.
Potencjalnym punktem niejasności związanych z użyciem funkcji agregujących w widokach jest klauzula HAVING. Jak wiadomo, klauzula HAVING jest używana w wyrażeniach SELECT, które mają za zadanie filtrować dane w oparciu o wynik funkcji agregującej. Kiedy wyniki agregacji zostaną przechwycone przez widok, zapytania SELECT odwołujące się do tego widoku, zamiast klauzuli HAVING do filtrowania wyników używają klauzuli WHERE. Rozważmy przykład z listingów 10.15 i 10.16.
-----------------------------
Listing 10.15. Filtrowanie wyników agregacji przy użyciu klauzuli HAVING
SELECT studio_id, AVG(budget) AS avg_budget
FROM Movies
GROUP BY studio_id
HAVING AVG(budget) > 50
STUDIO_ID AVG_BUDGET
-----------------------------
4 77
-----------------------------
-----------------------------
Listing 10.16. Filtrowanie wyników agregacji udostępnionych poprzez widok
SELECT studio, avg_budget
FROM Budget_By_Studio
WHERE avg_budget > 50
STUDIO_ID AVG_BUDGET
-----------------------------
4 77
#225
Jak widać, rezultaty zapytań z listingów 10.15 i 10.16 są takie same. Wyniki zapytania agregującego w widoku traktowane są tak, jakby były zwykłą tabelą, zatem do ograniczania wierszy zwracanych przez widok (przy odpytywaniu go wyrażeniem SELECT) używana jest zwykła klauzula WHERE.
Co ciekawe, istnieje możliwość używania funkcji agregujących w zapytaniach zwracających dane z widoków zbudowanych w oparciu o funkcje agregujące. Przykładem niech będzie zapytanie z listingu 10.17, które zwraca sumę średnich wartości budżetu dla każdego ze studiów w tabeli Movies.
-----------------------------
Listing 10.17. Zapytanie wykonujące funkcję agregującą w odniesieniu do widoku
SELECT SUM(avg_budget)
FROM Budget_By_Studio
SUM(AVG_BUDGET) -----------------
138.95
-----------------------------
Widoki tworzone ze złączeń
Oprócz widoków budowanych przy użyciu zapytania pobierającego dane z pojedynczej tabeli, istnieje również możliwość tworzenia widoków ze złączeń tabel. Jest to sposób pozwalający na obejście zasad normalizacji, nakładających wymóg rozbijania danych na odrębne tabele w celu uniknięcia problemów z integralnością informacji. Dla przykładu, jeśli dane mają być w jakimkolwiek stopniu znormalizowane, żaden ze związków jeden-do-wielu i wiele-do-wielu nie może zostać wyrażony poprzez pojedynczą tabelę.
Ponieważ widoki są reprezentacją danych, które faktycznie przechowywane są w inny sposób, nie ma żadnych przeszkód, by tworzyć widoki łamiące zasady normalizacji. W listingu 10.18 można zobaczyć widok zawierający nazwy osób, mieszkających w tym samym stanie, w którym zlokalizowane jest studio filmowe.
-----------------------------
Listing 10.18. Ludzie mieszkający w tym samym stanie, w którym zlokalizowane jest studio filmowe
CREATE VIEW People_In_Studio_State
AS
SELECT person_fname, person_lname, studio_name, person_state,
FROM People, Studios
WHERE person_state = studio_state
View created.
SELECT *
FROM People_In_Studio_State
PERSON_FNA PERSON_LNA STUDIO_NAME PE
-----------------------------
Maggie Davis Metaversal Studios LA
Jeff Price FKG NC
Chuck Peterson FKG NC
Frań Friend FKG NC
Brian Smith Delighted Artists TX
#226
Paul Monk Delighted Artists TX
Reece Randall Delighted Artists TX
Carol Delano Delighted Artists TX
8 rows selected.
-----------------------------
Jeżeli tabele używane w złączeniu na potrzeby tworzonego widoku zawierają w sobie kolumny o jednakowych nazwach, istotnego znaczenia nabiera lista aliasów. Podobnie, jak w przypadku stosowania funkcji lub wyrażeń na liście argumentów polecenia SELECT generującego dane widoku, aliasy są również obowiązkowe w przypadku wyspecyfikowania nazwy tabeli na liście argumentów złączenia służącego do utworzenia widoku. Przykład widoku wykorzystującego aliasy kolumn w związku z użyciem identyfikatorów tabel na liście argumentów wyrażenia SELECT przedstawia listing 10.19.
-----------------------------
Listing 10.19. Widok używający aliasów kolumn oraz złączenia
CREATE VIEW Local_Actors (movie, person, State)
AS
SELECT Loc.movie_id, P.person_id, Loc.State
FROM Locations Loc, People P, Cast_Movies CM
WHERE Loc.state = P.person_state
AND Loc.movie_id = CM.movie_id
AND P.person_id = CM.person_id
View created.
SELECT *
FROM Local_Actors
MOVIE PERSON ST
-----------------------------
3 1 NC
-----------------------------
Widok z listingu 10.19 zawiera listę aktorów, pojawiających się w filmach nakręconych w stanie, w którym osoby te mieszkają. Dzięki temu władze studia są w stanie stwierdzić, które z filmów zostały nakręcone z udziałem lokalnych aktorów. Aliasy kolumn zostały użyte po to, aby umożliwić dostęp do kolumn widoku.
Widoki z podzapytaniami
Kolejną konstrukcją, jaka może zostać wykorzystana do utworzenia widoku, są zapytania z podzapytaniami. Podzapytania dla widoków konstruowane są dokładnie tak samo, jak w przypadku zwykłego wyrażenia SELECT. Przykład widoku zawierającego podzapytanie przedstawia listing 10.20.
-----------------------------
Listing 10.20. Widok wykorzystujący podzapytanie w -wyrażeniu SELECT
CREATE VIEW Movies_From_Big_Budget_Studios
AS
SELECT movie_title, budget, gross
FROM Movies
WHERE studio_id IN
(SELECT studio_id
FROM Movies
#227
GROUP BY studio_id
HAVING AVG(budget) > 50)
View created.
SELECT *
FROM Movies From_Big_Budget_Studios
MOVIE_TITLE BUDGET GROSS
-----------------------------
Hard Code 77 30
-----------------------------
Zapytanie użyte do stworzenia powyższego widoku zawiera podzapytanie zwracające listę studiów, w których średni budżet jest większy niż 50 milionów dolarów. Wyniki tego podzapytania przekazywane są do klauzuli IN zapytania zewnętrznego, które z kolei tworzy sam widok. Widoki o takiej strukturze są bardzo przydatne, gdyż umożliwiają ukrycie przed niedoświadczonym użytkownikiem bazy danych zapytań o dużym stopniu skomplikowania. O wiele łatwiej jest wskazać osobie widok, który realizuje konkretne zadanie, niż tłumaczyć jej zasady konstruowania zapytań wykorzystujących funkcje agregujące wewnątrz podzapytań.
Korzystanie z innych operacji złączeń w widokach
Podczas tworzenia widoków mogą być używane inne operacje złączeń, takie jak UNION, MINUS, czy INTERSECT. Operacja UNION jest szczególnie przydatna, jeżeli zamierzamy potraktować dwie tabele w widoku, tak jakby stanowiły jedną całość. Dla przykładu, załóżmy, że chcemy stworzyć widok zawierający listę wszystkich miast i stanów obecnych w bazie danych. Większość baz danych odrzuca powtarzające się wiersze generowane przez zapytanie typu UNION, zatem efektem odwołania się do widoku z listingu 10.21 będzie lista wszystkich unikalnych kombinacji miast i stanów dostępnych w bazie danych.
-----------------------------
Listing 10.21. Widok bazujący na zapytaniu typu UNION
CREATE VIEW Cities_And_States (city, state)
AS
SELECT studio_city, studio_state
FROM Studios
UNION
SELECT person_city, person_state
FROM People
UNION
SELECT city, state
FROM Locations
View created.
SELECT *
FROM Cities And States
#228
CITY ST
----------------
Apex NC
Austin TX
Bellaire TX
Buffalo NY
Burbank CA
Cary NC
Dallas TX
Houston TX
Knoxville TN
Los Angeles CA
Madison WI
CITY ST
---------------
New Orleans LA
12 rows selected.
-----------------------------
Zapytanie z listingu 10.21 wydobywa wszystkie nazwy miast i stanów z trzech różnych tabel, po czym z tak przygotowanego zbioru danych zwraca zestaw wartości unikalnych.
Zagnieżdżanie widoków
Nie istnieje żadna zasada zabraniająca zagnieżdżania widoków. Oznacza to, że widoki mogą być budowane nie tylko w oparciu o zapytania do tabel bazowych, ale również w oparciu o zapytania do innych widoków. Jeżeli więc wcześniej utworzyliśmy widok wykorzystujący wyrażenia matematyczne, funkcje agregujące lub inny kod manipulujący w nim kolumnami danych, możemy teraz stworzyć nowy widok łączący widok istniejący z tabelą bazową lub innym widokiem.
Wróćmy do widoku Movie_Financials utworzonego w listingu 10.10. Mając go do dyspozycji, możemy w łatwy sposób stworzyć nowy widok, który będzie uwzględniał rzeczywisty budżet filmu oraz wynagrodzenie wypłacone każdemu z aktorów biorących w nim udział - pokazuje to listing 10.22.
-----------------------------
Listing 10.22. Widok utworzony z tabel bazowych oraz innego widoku
CREATE VIEW Movie_Cast (id, title, budget, role, payment)
AS
SELECT Movies.movie_id, Movie_Financials.title,
Movie_Financials.actual_budget, Cast_Movies.role, Cast_Movies.payment
FROM Movies, Movie_Financials, Cast_Movies
WHERE Movies.movie_id = Cast_Movies.movie_id
AND Movies.movie_title = Movie_Financials.title
View created.
SELECT title, budget, role, payment
FROM Movie_Cast
TITLE BUDGET ROLE PAYMENT
--------------------------------------
The Code Warrior 10300000 Thomas Black 50000
The Code Warrior 10300000 Robert Maxwell 653000
The Code Warrior 10300000 Malcolm Richards 137000
#229
The Code Warrior 10300000 Nina Smith 822000
The Code Warrior 10300000 Pam Green 28500
SQL Strikes Back 5000000 George Blake 332100
SQL Strikes Back 5000000 Mitch Crane 155000
SQL Strikes Back 5000000 Chris Parker 809000
SQL Strikes Back 5000000 Paul Nero 513200
SQL Strikes Back 5000000 Sheila Slade 1580000
The Rear Windows 50000000 Manfred Powell 18500000
11 rows selected.
--------------------------------------
Odwołanie się do widoku Movie_Financials we wnętrzu widoku Movie_Cast daje wyniki zgodne z oczekiwaniami - wśród danych zwracanych przez zapytanie pojawia się kolumna actual_budget. W tym przypadku, dzięki użyciu widoku Movie_Financials zaoszczędzona została konieczność ponownego tworzenia wyrażenia generującego kolumnę actual_budget na podstawie zawartości pola budget tabeli Movies.
Poprzez zagnieżdżenie jednego widoku w drugim odizolowaliśmy się od wszelkich zmian, jakie nastąpić mogą w formule określającej wartość pola actual_budget. Jeżeli pole budget z dowolnego powodu przestanie przechowywać budżety filmów wyrażane w milionach dolarów, zmian wymagać będzie jedynie widok Movie_Financials, natomiast Movie_Cast będzie wciąż działał zgodnie z oczekiwaniami. Koncepcja ta dokładnie odpowiada omawianej wcześniej możliwości stosowania widoków jako warstwy abstrakcji.
Modyfikowanie danych poprzez widoki
Do tej pory mowa była wyłącznie o tym, w jaki sposób widoki mogą służyć do wydobywania danych z bazy. Nie powiedzieliśmy natomiast nic na temat modyfikowania danych przy użyciu widoków. Istnieje możliwość wstawiania, usuwania lub uaktualniania danych poprzez widoki, niemniej jednak na operacje te nałożone są pewne restrykcje. Widoki, w odniesieniu do których mogą być wykonywane operacje INSERT, DELETE i UPDATE, nazywane są widokami modyfikowalnymi (ang. updatable views).
==================
Rada
Reguły określające, które z widoków mogą być modyfikowane, różnią się w zależności od implementacji bazy danych. Niektóre bazy danych nie dopuszczają wyrażeń modyfikujących jedynie dla tych widoków, które (z logicznego punktu widzenia) są niemożliwe do uaktualniania, podczas gdy inne narzucają znacznie szersze ograniczenia na widoki modyfikowalne. Zanim przyjmiemy, iż dany widok akceptuje wyrażenia modyfikujące, należy skonsultować to założenie z dokumentacją bazy danych lub przetestować działanie widoku na danych testowych. Dopiero po upewnieniu się, że widok będzie współpracował z zapytaniami modyfikującymi, można zastosować go w aplikacji.
=========================
#230
Mówiąc najkrócej, wymienione wyżej komendy działają tylko w przypadku zawężonego kręgu widoków. Ponadto wszelkie wyrażenia INSERT, DELETE lub UPDATE wykonywane na widoku muszą spełniać warunki wymuszone przez tabele bazowe, na których oparty jest dany widok.
Przykładowo nie istnieje możliwość wstawiania rekordów do tabeli bazowej poprzez widok, jeżeli tabela ta zawiera pole typu NOT NULL nie uwzględnione w samym widoku.
Zgodnie z pierwszą fundamentalną regułą określającą zdolność do uaktualniania widoków, każdy wiersz widoku musi odpowiadać dokładnie jednemu wierszowi w tabeli lub tabelach bazowych, na których widok jest zbudowany. Poniższa lista zawiera konstrukcje, które łamią tę zasadę, czyniąc tym samym widok niemożliwym do uaktualniania:
* Funkcje agregujące na liście argumentów wyrażenia SELECT.
* Operator DISTINCT.
* Użycie w widoku klauzul GROUP BY i HAVING.
* Wyrażenia matematyczne na liście argumentów wyrażenia SELECT.
Nie ma możliwości, aby uaktualnianie działało w odniesieniu do tego typu widoków, ponieważ nie istnieje żadna logiczna metoda, pozwalająca zapytaniu SQL modyfikującemu widok wyodrębnić rekordy przeznaczone do uaktualnienia w tabeli bazowej. Standard ANSI odrobinę ściślej określa typy widoków modyfikowalnych, chociaż - jak wiadomo - nie wszystkie bazy danych są zgodne z tym standardem. Reguły standardu ANSI nie dopuszczają modyfikacji danych w widokach, które wykorzystują jedną z wymienionych poniżej konstrukcji:
* Odwołania do wielu tabel poprzez podzapytania, dowolny typ złączenia, łącznie z tymi, które używają klauzuli FROM lub operatorów takich jak UNION.
* Wywołania funkcji, wyrażeń matematycznych i agregujących na liście instrukcji SELECT.
* Odwołania w widoku do innych niemodyfikowalnych widoków.
* Użycie operatora DISTINCT na liście wyrażenia SELECT.
Po upewnieniu się, że stworzony przez nas widok jest modyfikowalny, można dokonywać na nim operacji typu UPDATE, DELETE i INSERT w taki sam sposób, jak w przypadku tabel bazowych. Przyjrzyjmy się w pierwszej kolejności wyrażeniu INSERT, które poprzez widok dodaje rekord do tabeli. Pierwszy krokiem do tego celu jest utworzenie modyfikowalnego widoku, tak jak przedstawia to listing 10.23.
--------------------------------------
Listing 10.23. Widok modyfikowalny
CREATE VIEW Basic_Movies
AS
SELECT movie_id, movie_title, studio_id, director_id, release_date
FROM Movies
--------------------------------------
#231
Po utworzeniu widoku Basic_Movies można wstawić nowy rekord do tabeli Movies poprzez ten właśnie widok. Operacje taką demonstruje listing 10.24.
--------------------------------------
Listing 10.24. Wyrażenie modyfikujące rekord poprzez widok
INSERT INTO Basic_Movies
VALUES (11, 'StarWares1, 1, 5, '01-JAN-19991)
1 row created.
SELECT *
FROM Basic_Movies
WHERE movie_id = 11
MOVIE_ID MOVIE_TITLE STUDIO_ID DIRECTOR_ID RELEASE_D
--------------------------------------
11 Star Wares 1 5 01-JAN-09
--------------------------------------
Nowy rekord został wstawiony do tabeli Movies i jest dostępny poprzez widok. Po wykonaniu zapytania z listingu 10.25 można przekonać się, że wartości dwóch kolumn tabeli Movies, (które nie są uwzględnione w widoku Basic_Movies), wynoszą null.
--------------------------------------
Listing 10.25. Zawartość rekordu w tabeli bazowej, który został wstawiony poprzez widok
SELECT movie_id, movie_title, gross, budget
FROM Movies
WHERE movie_id = 11
MOVIE_ID MOVIE_TITLE GROSS BUDGET
--------------------------------------
1 Star Wares
--------------------------------------
Zobaczmy teraz, co się stanie, jeśli spróbujemy uaktualnić pola tabeli bazowej poprzez widok w sytuacji, gdy sam widok nie zawiera tych pól. Polecenie z listingu 10.26 ma za zadanie uaktualnić pole gross w tabeli Movies poprzez widok Basic_ Movies.
--------------------------------------
Listing 10.26. Uaktualnianie nieistniejących pól widoku
UPDATE Basic_Movies
SET gross = 50
WHERE movie_id = 11
ERROR at line 2:
ORA-00904:invalid column name
--------------------------------------
Jak łatwo można było się tego domyślić, nie można uaktualnić kolumn, które nie zostały w sposób jawny włączone do widoku, nawet jeżeli stanowią one część tabeli bazowej, na której oparty jest widok. Widok z punktu widzenia użytkownika jest niczym innym jak tabelą, tyle tylko, że dane tej tabeli (widoku) pobierane są z innych tabel. Listing 10.27 zawiera wyrażenie, które faktycznie uaktualnia widok Basic_Movies.
--------------------------------------
Listing 10.27. Wyrażenie UPDATE wykonane na widoku
UPDATE Basic_Movies
SET movie_title = 'Stare Wars'
WHERE movie id = 11
#232
1 row updated.
SELECT *
FROM Basic_Movies WHERE movie_id = 11
MOVIE_ID MOVIE_TITLE STUDIO_ID DIRECTOR_ID RELEASE_D
--------------------------------------
11 Stare Wares 14 5 01-JAN-09
--------------------------------------
Na modyfikowalnych widokach mogą być również wykonywane zapytania typu DELETE - wiersze objęte działaniem takiego zapytania zostają usunięte z tabeli lub tabel bazowych, które posłużyły do stworzenia widoku. Przykład usuwania wiersza z widoku przedstawia listing 10.28.
--------------------------------------
Listing 10.28. Wyrażenie usuwające rekord poprzez widok
DELETE FROM Basic_Movies
WHERE movie_id =11
1 row deleted.
SELECT *
FROM Basic_Movies WHERE movie_id = 11
no rows selected
SELECT movie_id, movie_title
FROM Movies
WHERE movie_id - 11
no rows selected
--------------------------------------
Zadania realizowane za pomocą widoków
Z dotychczasowej lektury tego rozdziału wynika, iż istnieje wiele zastosowań dla widoków, które znacznie ułatwiają życie programiście oraz użytkownikom bazy danych. Okazuje się również, że za pomocą widoków można zrobić pewne rzeczy, których realizacja w inny sposób w ogóle nie byłaby możliwa.
Jak wiadomo, w standardowym języku SQL nie ma zmiennych. Z tego względu nie istnieje metoda pozwalająca na łączenie wyrażeń w grupy; każde zapytanie występuje samodzielnie. Potrzeba przenoszenia danych z jednego zapytania SQL do kolejnego jest częściowo realizowana przez podzapytania (są one wstawiane w tych miejscach, gdzie normalnie pojawiałaby się zmienna w standardowych językach programowania).
Dostępne dla większości baz danych języki z obsługą procedur udostępniają wyrażenia warunkowe, zmienne i inne elementy, jakie znaleźć można w niemal każdym proceduralnym języku programowania, jednak sam SQL nie posiada takich możliwości.
#233
Widoki mogą być postrzegane jako pewnego rodzaju zmienne, umożliwiające przenoszenie wyników zapytania z jednego wyrażenia do kolejnego. Istnieje możliwość tworzenia widoków zawierających rezultaty wyliczone przy użyciu funkcji agregujących i matematycznych, a następnie łączenia ich z innymi tabelami, tak jakby były to rzeczywiste tabele bazy.
==============
Rada
Użytkownicy korzystający z języka Transact-SQL mogą zapisywać wyniki zapytań w tabelach tymczasowych, a następnie korzystać z tych wyników w innych zapytaniach. Rozwiązanie takie eliminuje potrzebę tworzenia specjalistycznych widoków danych. Podobnie język PL/SQL (Oracle) pozwala na tworzenie tabel przechowujących wyniki zapytań w pamięci, dzięki czemu możliwe jest przeprowadzanie na nich różnych operacji.
================
Najprostszym wyjaśnieniem opisanego sposobu wykorzystania widoków jest przedstawienie przykładu, który wykorzystuje wspomnianą funkcjonalność. Przykład ten został zaczerpnięty z projektu mającego na celu umożliwienie prezentacji w sieci WWW artykułów pewnego czasopisma, w oparciu o słowa kluczowe przypisywane do artykułów przez ich autorów oraz profile tworzone przez zarejestrowanych użytkowników.
Tabele w przykładzie
Zacznijmy od krótkiego przedstawienia tabel bazowych używanych w przykładzie. Na potrzeby realizacji zadania użyte zostały cztery tabele bazowe. Pierwsza z nich przechowuje faktyczną zawartość artykułu, tzn. nagłówek, podpis autora oraz treść. Wyrażenie tworzące tę tabelę znajduje się w listingu 10.29.
--------------------------------------
Listing 10.29. Zapytanie tworzące tabelę Articles
CREATE TABLE Articles
(id INT PRIMARY KEY,
headline VARCHAR2 (255),
byline VARCHAR2(80),
body TEXT,
pub_date DATE)
Table created.
--------------------------------------
Druga tabela zawiera listę słów kluczowych używanych zarówno dla artykułów, jak i profili. Służy ona do przypisywania słów kluczowych do poszczególnych kategorii, a także przechowuje opisy każdego słowa kluczowego. Listing 10.30 pozwala zorientować się w wyglądzie schematu tej tabeli.
--------------------------------------
Listing 10.30. Wyrażenie CREATE dla tabeli Keywords
CREATE TABLE Keywords
(id INT PRIMARY KEY,
categoryVARCHAR2(12)
descript VARCHAR2(40) )
Table created.
-------------------------------------
#234
Pozostałe dwie tabele wiążą słowa kluczowe z użytkownikami oraz artykułami. Obie tabele są tabelami asocjacyjnymi (łączącymi) implementującymi związki wiele-do-wielu między tabelami Articles i Keywords oraz Users i Keywords. Tabela Users nie zostanie tu opisana, ponieważ nie wchodzi w skład omawianego systemu. Wyrażenia CREATE TABLE, tworzące obie tabele asocjacyjne przedstawione zostały w listingach 10.31 i 10.32.
--------------------------------------
Listing 10.31. Wyrażenie CREATE dla tabeli Article_Keywords
CREATE TABLE Article_Keywords
(article_id INT,
keyword_id INT)
Table created.
--------------------------------------
--------------------------------------
Listing 10.32. Wyrażenie CREATE dla tabeli User_Keywords
CREATE TABLE User_Keywords
(user_id INT,
keyword_id INT)
Table created
--------------------------------------
Dla uproszczenia w tabelach pozostawione zostały jedynie pola mające znaczenie dla omawianego przykładu. W rzeczywistym rozwiązaniu liczba pól w poszczególnych tabelach jest większa.
Działanie systemu
Za każdym razem, gdy użytkownik wyświetla listę artykułów, są one sortowane według wartości jakie przypisał im system wagowy. Istnieją dwie kategorie słów kluczowych: dla każdego słowa kluczowego dopasowanego pomiędzy artykułem a użytkownikiem w pierwszej kategorii przypisywane są dwa punkty, natomiast za każde dopasowanie w drugiej kategorii przypisywany jest jeden punkt. Ponadto liczba przypisywanych punktów zależy od wartości artykułu. Jeżeli został on opublikowany mniej niż tydzień temu, przypisywanych jest pięć punktów, natomiast jeśli publikacja miała miejsce w przedziale od dwóch tygodni do jednego licząc wstecz, przypisywane są cztery punkty.
Celem tego systemu jest umożliwienie osobie piszącej program, który wyświetla listy artykułów na stronie WWW, uzyskanie wagowej listy artykułów przy użyciu pojedynczego zapytania do bazy danych. Niestety, ponieważ system używa wielu funkcji agregujących oraz innych wyrażeń matematycznych, nie istnieje możliwość napisania pojedynczego polecenia SELECT, które zwracać będzie wagową listę artykułów. Jednym z możliwych rozwiązań tego problemu mogłoby być stworzenie kilku zapytań, wpisanie zwróconych przez nie rezultatów do tabel, a następnie podpięcie wartości z tych tabel do kolejnych zapytań generujących niezbędne wyniki. Rozwiązanie takie jednak w oczywisty sposób łamie wymóg tworzenia listy w oparciu o pojedyncze zapytanie.
#235
Zadanie zostało zrealizowane poprzez kilka widoków zawierających kolejne części formuły wagowej oraz jeszcze jeden dodatkowy widok, sumujący wszystkie podwidoki i łączący je z rzeczywistymi danymi w tabeli Articles.
Widoki użyte w systemie
System korzysta z czterech widoków, z których trzy przechowują komponenty wagi artykułu, natomiast czwarty łączy całkowitą wagę artykułu z rzeczywistymi polami przechowującymi jego dane. W pierwszej kolejności przeanalizujemy dwa widoki służące do oceny wagowej artykułów w oparciu o słowa kluczowe. Widoki te są identyczne, z wyjątkiem tego, że pierwszy z nich prowadzi rejestr słów kluczowych w kategorii zainteresowania, natomiast drugi rejestr słów kluczowych w kategorii nie związanej z zainteresowaniem (danym artykułem).
Zadaniem każdego z tych widoków jest łączenie tabel Article_Keywords i User_Keywords poprzez pole keyword_id oraz zliczanie liczby wierszy zawierających pasujące słowa kluczowe i znajdujących się w odpowiedniej kategorii. Wyniki grupowane są w oparciu o pola user_id i article_id. Wyrażenia tworzące oba widoki przedstawione zostały w listingach 10.33 i 10.34.
--------------------------------------
Listing 10.33. Widok Interest_Weight
CREATE VIEW Interest_Weight (user_id, article_id, weight)
AS
SELECT user_id, article_id,
COUNT(Article_Keywords.keyword_id)
FROM User_Keywords, Article_Keywords
WHERE Article_Keywords.keyword_id = User_Keywords.keyword_id
AND Article_Keywords.keyword_id IN
(SELECT id ,
FROM Keywords
WHERE category = 'interest')
GROUP BY user_id, article_id
View created.
SELECT *
FROM Interest_Weight
USER_ID ARTICLE_ID WEIGHT
--------------------------------------
1 1 1
1 2 2
1 3 3
2 2 1
2 3 2
--------------------------------------
--------------------------------------
Listing 10.34. Widok Non_Interest_Weight
CREATE VIEW Non_Interest_Weight (user_id, article_id, weight)
AS
SELECT user_id, article_id,
COUNT(Article_Keywords.keyword_id)
FROM User_Keywords, Article_Keywords
WHERE Article_Keywords.keyword_id = User_Keywords.keyword_id
AND Article_Keywords.keyword_id IN
(SELECT id
FROM Keywords
WHERE category = 'non-interest')
GROUP BY user id, article id
#236
View created.
SELECT *
FROM Non_Interest Weight
USER_ID ARTICLE_ID WEIGHT
--------------------------------------
1 1 2
1 2 1
2 1 1
2 2 1
--------------------------------------
Gdyby powyższe widoki były prawdziwymi tabelami, ich kluczem głównym byłaby kombinacja pól user_id i article_id. Widoki przechowują w sobie (a raczej generują z bazy danych) wagę każdego artykułu w odniesieniu do użytkownika. W chwili, kiedy użytkownik żąda listy artykułów, z bazy danych pobierane są wiersze związane z konkretnym identyfikatorem użytkownika - w ten sposób można określić zbiór artykułów, którymi najprawdopodobniej zainteresowany będzie użytkownik.
Kolejny widok, jaki musi zostać utworzony, zawiera wagę każdego artykułu wyli-; czaną w oparciu o to, jak dawno temu dany artykuł został opublikowany. Widok ten
używa operatorów UNION do połączenia kilku zapytań pobierających artykuły w oparciu o wartość pola pub_date. W tym przypadku operator UNION jest używany jako konstrukcja wyrażenia warunkowego IF. Mówiąc najprościej, chcemy przypisać określoną wartość do każdego artykułu w oparciu o czas jego publikacji. Aby móc przypisywać zróżnicowane wartości na podstawie zawartości pola pub_date, niezbędne jest powiązanie ze sobą kilku zapytań przy użyciu operatora UNION. Rozwiązanie zadania bez operatora UNION wymagałoby napisania indywidualnych zapytań dla każdego przedziału czasowego wymaganego przez system wagowy, wskutek czego niemożliwe stałoby się przechwycenie generowanych informacji w jednym widoku. Wyrażenie tworzące widok czasowej oceny artykułów przedstawia listing 10.35.
--------------------------------------
Listing 10.35. Widok Time_Veight
CREATE VIEW Time_Weight (article_id, weight)
AS SELECT id, 5
FROM Articles WHERE pub_date BETWEEN (SYSDATE - 2) AND SYSDATE
UNION
SELECT id, 3
FROM Articles
WHERE pub_date BETWEEN (SYSDATE - 7) AND (SYSDATE -2)
UNION
SELECT id, 0
FROM Articles
WHERE pub_date < (SYSDATE - 7)
View created.
SELECT *
FROM Tlme_Weight
ARTICLE_ID WEIGHT
1 3
2 5
3 0
--------------------------------------
#237
Wyrażenia w klauzulach BETWEEN widoku odejmują określoną liczbę dni od daty bieżącej, zatem SYSDATE - 2 oznacza „dwa dni temu", a SYSDATE - 7 „siedem dni temu." Artykułom opublikowanym w ciągu ostatnich dwóch dni (pierwsze wyrażenie BETWEEN) przypisywana jest waga pięć, natomiast artykułom opublikowanym w przedziale od siedmiu do dwóch dni wstecz licząc od daty bieżącej (drugie wyrażenie BETWEEN) przypisywana jest waga trzy. Tekstom starszym niż siedem dni nadawana jest waga zerowa.
Po utworzeniu widoku Time_Weight, można wygenerować widok zawierający całkowitą wagę każdego z artykułów. Widok ten łączy wagi wygenerowane przez trzy poprzednie widoki i daje w efekcie tabelę ogólnej oceny każdego artykułu. Formuła wyliczająca wagę artykułu została ujęta w pojedynczym wyrażeniu umieszczonym na liście argumentów wyrażenia SELECT. Podobnie, jak w przypadku dwóch poprzednich widoków, kluczem widoku jest kombinacja pól artic-le_id i user_id. Implementacja widoku jest połączeniem trzech widoków już istniejących - listing 10.36.
--------------------------------------
Listing 10.36. Widok Article_Weight agregujący wagę dla każdej kombinacji użytkownika i artykułu
CREATE VIEW Article_Weight (article_id, user_id, weight)
AS
SELECT IW.article_ld, IW.user_id, (IW.weight * 2) + NIW.weight + TW.weight
FROM Time_Weight TW, Interest_Weight IW, Non_Interest_Weight NIW
WHERE IW.user_id = NIW.user_id
AND IW.article_id = NIW.article_id
AND IW.article_id = TW.article_id
View created.
SELECT user_id, article_id, weight
FROM Article_Weight
ORDER BY user_id, article_id
USER_ID ARTICLE_ID WEIGHT
--------------------------------------
1 1 7
1 2 10
2 1 8
--------------------------------------
W widoku łączone są trzy tabele wag, natomiast same wagi są sumowane w celu określenia całkowitej wagi danego artykułu dla wszystkich zarejestrowanych użytkowników. Zatem widok zawiera jeden wiersz dla każdej kombinacji artykułu i użytkownika. Jeżeli para artykuł-użytkownik nie posiada żadnych pasujących do siebie słów kluczowych, uznawana jest za parę z wagą zero i pomijana w widoku Article Weight.
Zapytanie wydobywające artykuły
Wszystkie widoki systemu sąjuż gotowe, do napisania pozostało jedynie zapytanie, które wydobywa rzeczywiste artykuły z bazy danych. Zapytanie to będzie łączyć tabelę (widok) Article_Weight z tabelą Articles i zwracać jedynie wiersze odnoszące się do użytkownika, który w danej chwili korzysta z systemu. Przykład tego zapytania przedstawia listing 10.37.
#238
--------------------------------------
Listing 10.37. Zapytanie wydobywające artykuły z oceną wagową
SELECT weight, id, headline
FROM Article_Weight, Articles
WHERE id = article_id
AND user_id - l
ORDER BY weight DESC
WEIGHT_ID HEADLINE
--------------------------------------
10 2 Linux przejmuje prowadzenie na rynku.
7 1 Problem Y2K przyczyną bólu głowy?
--------------------------------------
Numer 1 jest przykładem potencjalnego identyfikatora użytkownika. Powyższe zapytanie zwróci tylko te artykuły, których słowa kluczowe zostały dopasowane do słów kluczowych bieżącego użytkownika w obu kategoriach.
W praktyce
Duże znaczenie przy projektowaniu bazy ma sposób użytkowania danych, ale jeszcze ważniejsze są związki pomiędzy nimi. Niezbędne jest znormalizowanie danych w stopniu zapewniającym integralność oraz akceptowalną wydajność bazy. Niestety, zachowanie odpowiedniego balansu pomiędzy tymi dwoma czynnikami sprawia, iż sposób organizacji danych nie zawsze będzie umożliwiał ich prezentację użytkownikom. Problem ten jest szczególnie istotny w przypadku, gdy z bazy danych korzysta wielu użytkownikach o różnych profilach.
Weźmy pod uwagę bazę danych filmów (Movies). Zawiera ona szereg różnych informacji, z których nie wszystkie muszą zawsze interesować określoną grupę użytkowników. Dla przykładu typowymi użytkownikami bazy danych Movies są ludzie z działu księgowości, kierownicy studiów oraz pracownicy zajmujący się obsadą filmów. Każda taka grupa ma różne potrzeby podczas korzystania z bazy danych. Tworząc odpowiednie widoki można ułatwić danej grupie pobieranie interesujących ją danych i tym samym zwolnić z konieczności pisania różnych skomplikowanych zapytań.
Zacznijmy od kierowników - dla nich najbardziej użyteczne są szczegółowe zestawienia danych. Tworzymy zatem widok zawierający analizę wyników finansowych każdego studia - listing 10.38.
--------------------------------------
Listing 10.38. Widok dla kierowników zawierających szczegółowe dane finansowe studiów
CREATE VIEW Executive_Studio_Breakdown
(name, avg_budget, avg_gross, avg_profit
total_budget, total_gross,total_profit)
AS
SELECT Studios.studio_name, AVG(budget), AVG(gross), AVG(gross - budget),
SUM(budget), SUM(gross), SUM(gross - budget)
FROM Studios, Movies
WHERE Studios.studio_id = Movies.studio_id
GROUP BY Studios.studio_name
--------------------------------------
#239
Użytkownicy działu księgowości są również zainteresowani danymi finansowymi, ale w bardziej szczegółowym zakresie niż kierownicy. Interesuje ich np., jak wygląda budżet każdego filmu w porównaniu do sumy pieniędzy wydanych na obsadzenie zespołu filmowego oraz do średniego kosztu utrzymania jego członków. Informacje takie można zebrać w jednym widoku, tworzonym przez wyrażenie z listingu 10.39.
--------------------------------------
Listing 10.39. Widok dla księgowych analizujący kwoty pieniężne wydawane na obsadę filmu
CREATE VIEW Movie_Cast_Cost
(movie_id, budget, totał_cast_payment, avg_cast_payment, num_cast)
AS
SELECT Cast_Movies.movie_id, Movies.budget,
SUM(Cast_Movies.payment) ,
AVG(Cast_Movies.payment) ,
COUNT(Cast_Movies.payment)
FROM Cast_Movies, Movies
WHERE Cast_Movies.movie_id = Movies .movie_id
GROUP BY Cast_Movies.movie_id, Movies .budget
--------------------------------------
Widok Movie_Cast_Cost pobiera każdy film z bazy danych i dołącza do niego jego budżet, całkowitą sumę pieniędzy wypłaconą członkom obsady, średnie wynagrodzenie, a także liczbę opłaconych osób zespołu filmowego. Wartości takie umożliwiają pracownikom księgowości śledzenie kwot pieniężnych wydawanych na każdy z filmów.
Na końcu pozostaje stworzenie raportu dla pracowników obsady. Dla nich znaczenie ma lista członków obsady oraz lista ról do odegrania w każdym z filmów. Poniższy widok (listing 10.40) umożliwia obejrzenie, jakie osoby pojawiły się w jakich filmach, jak wiele im zapłacono oraz jakie role odgrywały.
--------------------------------------
Listing 10.40. Widok zawierający dane o obsadzie filmów
CREATE VIEW Movie_Cast_Info
AS
SELECT Movies-movie_title, People.person_fname,
People.person_lname, Cast_Movies.role, Cast_Movies.payment
FROM Movies, People, Cast_Movies
WHERE Movies.movie_id = Cast_Movies .movie_id
AND People.person_id - Cast_Movies.movie_id
--------------------------------------
Są to tylko wybrane przykłady widoków, jakie mogą być tworzone dla różnorodnych typów użytkowników posiadających dostęp do bazy danych. Zaszywanie złożonych zapytań w widokach znacznie ułatwia wykonywanie obowiązków poszczególnym osobom, których praca nieodłącznie wiąże się z użytkowaniem bazy danych.
Wyszukiwarka
Podobne podstrony:
Część III, Wyposażenie i stateczność 1996 errataSiderek12 Tom I Część III Rozdział 14PRZEPOWIEDNIA część III by MattRixCZESC III fizyka wyklad przewodzenieBAZA DANYCH GEOLOGICZNYCHSiderek12 Tom I Część III Rozdział 16Część III Dziadów dramatem romantycznym22 Część VII Udostępnianie baz danych w sieci WWW PodstaBAZA DANYCH GMINNEJ EWIDENCJI ZABYTKÓWSiderek12 Tom I Część III Rozdział 13więcej podobnych podstron