Rozszerzenia języka relacyjnych baz danych SQL

Wykład 3
Rozszerzenia języka relacyjnych
baz danych SQL
SZB, L.Banachowski
1
Spis treści
1.
2.
3.
4.
5.
Typy danych obiektowe
Typy danych LOB (multimedialnych)
Obiekty i kod Javy
Typ danych XML
Konstrukcje dla hurtowni danych
SZB, L.Banachowski
2
Niezgodność typów
(impedance mismatch)
Bazy danych są na ogół relacyjne, a aplikacje klienckie obiektowe.
1. Dwa odwzorowania:
obiekty aplikacji -> tabele bazy danych -> pliki
2. Relacyjne bazy danych stanowią najlepszy kompromis:
wydajność/naturalne modelowanie obiektów
między poziomem aplikacyjnym a fizycznym.
•
Odwzorowanie danych obiektowych na relacyjne w samej aplikacji przy
użyciu interfejsów programistycznych API takich jak JDBC, ODBC.
3. Jak zbliżyć do siebie oba niespójne względem siebie modele
danych (bazy danych i obiektowej aplikacji):
• tabele obiektowe – od strony bazy danych,
• odwzorowania obiektowo-relacyjne ORM – od strony
aplikacji.
SZB, L.Banachowski
3
Typ obiektowy
•
Złożony typ danych definiowany przez użytkownika.
Metoda
Wyślij
Id_zam
Info_klient
Pozycje
Status
Anuluj
Atrybut
Wyśw_Status
• Hermetyzuje strukturę danych łącznie z metodami
potrzebnymi do operowania na strukturze danych.
Zatrzymaj
Metody to kod składowany w słowniku danych Oracle – wraz z
opisem typu obiektowego.
SZB, L.Banachowski
4
Struktura typu obiektowego
Specyfikacja typu
Publiczny
interfejs
Deklaracje atrybutów
Specyfikacje metod
Ciało typu
Prywatna
implementacja
SZB, L.Banachowski
Ciała metod
5
Tworzenie specyfikacji typu
obiektowego
Składnia
CREATE TYPE nazwa_typu AS OBJECT
[(atrybut1 typdanych,
atrybut2 typdanych,
. . .]
[MEMBER procedura1 | funkcja1 spec,
procedura2 | funkcja2 spec,
. . .)]
SZB, L.Banachowski
6
Tworzenie ciała typu obiektowego
Składnia
CREATE TYPE BODY nazwa_typu AS
[MEMBER procedura1 | funkcja1 body,
procedura2 | funkcja2 body,
. . .]
END
SZB, L.Banachowski
7
Przykład
SQL> CREATE TYPE name_type AS OBJECT(
f_name VARCHAR2(25),
l_name VARCHAR2(25),
initials VARCHAR2(7),
MEMBER FUNCTION full_name RETURN VARCHAR2
);
SZB, L.Banachowski
8
Przykład
SQL> CREATE TYPE BODY name_type AS
MEMBER FUNCTION full_name RETURN VARCHAR2
IS
BEGIN
RETURN (l_name || ' ' || f_name );
END full_name;
END;
SZB, L.Banachowski
9
Przykład
SQL> CREATE TYPE emp_type AS OBJECT(
emp_id
NUMBER(7),
name
name_type, -- typ obiektowy
street
VARCHAR2(25),
city
VARCHAR2(15),
state
CHAR(2),
zip
INTEGER,
MEMBER FUNCTION get_name RETURN VARCHAR2,
MEMBER PROCEDURE set_l_name (v_name VARCHAR2));
SZB, L.Banachowski
10
Przykład
SQL> CREATE TYPE BODY emp_type AS
MEMBER FUNCTION get_name RETURN VARCHAR2
IS
BEGIN
RETURN (name.l_name ||' '|| name.f_name);
END;
MEMBER PROCEDURE set_l_name (v_name VARCHAR2)
IS
BEGIN
name.l_name := v_name;
END;
END;
SZB, L.Banachowski
11
Rodzaje metod
v
MEMBER – z niejawnym pierwszym parametrem
SELF – definiowanego typu danych.
– Obejmuje metody służące do porównywania obiektów.
v
v
STATIC – bez SELF; wywoływane type_name.method
np. metody konstruktorów obiektów definiowane
przez użytkowników.
Implementowane w PL/SQL lub w językach
programowania np. Java, C.
SZB, L.Banachowski
12
Dziedziczenie (tylko jednokrotne)
SQL> CREATE TYPE Person AS OBJECT (
first VARCHAR2(50),
last VARCHAR2(50)) NOT FINAL;
SQL>
CREATE TYPE Emp UNDER Person (
salary NUMBER) FINAL;
SQL> DECLARE
x Emp:= Emp('Jan','Kowalski',10000);
BEGIN
DBMS_OUTPUT.Put_line(x.first||' '||x.last
||' '||x.salary);
END;
NOT FINAL – wymagane aby móc definiować podtypy
SZB, L.Banachowski
13
Polimorfizm
• Przedefiniując dziedziczone metody w podtypie mamy
możliwość zdefiniowania innego sposobu wykonania metod na
obiektach podtypu.
• Obiekt podtypu może być użyty w kodzie w miejsce obiektu
nadtypu – definiując dla różnych podtypów różne działania.
SZB, L.Banachowski
14
Przykład
CREATE TYPE person_type AS OBJECT (
idno
NUMBER,
name
VARCHAR2(30),
phone
VARCHAR2(20),
MEMBER FUNCTION show RETURN VARCHAR2
) NOT FINAL;
CREATE TYPE BODY person_type AS
MEMBER FUNCTION show RETURN VARCHAR2 IS
BEGIN
RETURN 'Id: ' || TO_CHAR(idno) || ', Name: ' || name;
END;
END;
SZB, L.Banachowski
15
c.d.
CREATE TYPE student_type UNDER person_type (
dept_id NUMBER,
major VARCHAR2(30),
OVERRIDING MEMBER FUNCTION show
RETURN VARCHAR2) NOT FINAL;
CREATE TYPE BODY student_type AS
OVERRIDING MEMBER FUNCTION show
RETURN VARCHAR2 IS
BEGIN
RETURN (self AS person_type).show || ' -- Major: ' || major ;
END;
END;
SZB, L.Banachowski
16
Informacje w słowniku danych Oracle
Informacje o typach obiektowych znajdują się w
• USER_OBJECTS
Informacje o metodach typów obiektowych znajdują się w
• USER_METHOD_PARAMS
• USER_METHOD_RESULTS
• USER_TYPE_METHODS
SZB, L.Banachowski
17
Tworzenie tabeli obiektowej i
wywoływanie metod
SQL>
SQL>
SQL>
CREATE TABLE name_table OF name_type;
INSERT INTO name_table
VALUES('Marilyn','Monroe','MM');
SELECT nt.f_name, nt.full_name()
FROM
name_table nt;
INSERT INTO name_table
VALUES (name_type('Marilyn','Monroe','MM'));
SZB, L.Banachowski
18
Spojrzenia na tabelę obiektową
v
v
Tabela jedno-kolumnowa: każdy wiersz jest
obiektem; można wykonywać operacje
obiektowe.
Tabela wielokolumnowa: każdemu atrybutowi
obiektu odpowiada jedna kolumna.
SZB, L.Banachowski
19
Rodzaje obiektów
Składowane, trwałe
1. Wierszowe (elementy tabeli obiektowej)
2. Kolumnowe (zapisywane w kolumnie tabeli relacyjnej)
3. Atrybutowe (zapisywane jako wartość atrybutu innego
trwałego obiektu np. wierszowego)
Nie składowane, nietrwałe
Tymczasowe – tworzone w RAM i tam używane:
• w kodzie PL/SQL
• w kodzie Javy
SZB, L.Banachowski
20
Metoda konstruktora obiektu
SQL> CREATE TYPE tv_type AS OBJECT (
tv_category VARCHAR2(20),
screen_size NUMBER(4));
DECLARE
v_new_tv
tv_type := tv_type('WEB tv', 32);
v_alt_tv
tv_type;
BEGIN
v_alt_tv := tv_type('Big Screen', 72);
END;
CREATE TABLE tv OF tv_type;
INSERT INTO tv VALUES (tv_type('Color tv', '28'));
SZB, L.Banachowski
21
SELECT na tabeli obiektowej
Wynikiem zbiór wierszy:
SELECT * FROM name_table p
WHERE p.l_name LIKE 'M%';
Wynikiem zbiór obiektów:
SELECT VALUE(p) FROM name_table p
WHERE p.l_name LIKE 'M%';
„Kanoniczna” reprezentacja obiektu w SQL*Plus - napis:
-- NAME_TYPE('Marilyn','Monroe','MM')
SZB, L.Banachowski
22
INSERT do tabeli obiektowej
Bezpośrednio:
INSERT INTO name_table
VALUES('Marilyn','Monroe','MM');
Z użyciem konstruktora typu obiektowego:
INSERT INTO name_table
VALUES(name_type('Marilyn','Monroe','MM'));
SZB, L.Banachowski
23
UPDATE na tabeli obiektowej
UPDATE name_table p
SET
p.l_name = 'Janusz'
WHERE p.l_name = 'Jan';
Z użyciem konstruktora typu obiektowego:
UPDATE name_table p
SET
p = name_type('Janusz','Kowalski','JK')
WHERE p.l_name = 'Jan';
SZB, L.Banachowski
24
Tabela relacyjna
v
CREATE TABLE Emp1(
Empno NUMBER PRIMARY KEY,
Name Name_type, -- kolumna typu obiektowego
Sal NUMBER,
Kier REFERENCES Emp1);
SZB, L.Banachowski
25
Typ referencyjny
CREATE TYPE Dept_Type AS OBJECT(
Name VARCHAR2(10),
Loc VARCHAR2(50));
CREATE TABLE Obj_Dept
OF Dept_Type;
CREATE TYPE Emp_Type AS OBJECT(
Name VARCHAR2(20),
Sal Number,
Dept_ref REF Dept_Type);
CREATE TABLE Obj_Emp OF Emp_Type
(Dept_ref SCOPE IS Obj_Dept);
-- odpowiednik klucza obcego
SZB, L.Banachowski
CREATE TABLE Rel_emp(
Id NUMBER PRIMARY KEY,
Name VARCHAR2(20),
Sal Number,
Dept_ref REF Dept_Type
SCOPE IS Obj_Dept);
26
Użycie referencji
SELECT o.name, o.Dept_Ref.name
FROM obj_emp o;
NAME
DEPT_REF.NAME
------------------ ------------------------KOWALSKI KADRY
SZB, L.Banachowski
27
Tworzenie tabeli obiektowej z obiektami
różnych podtypów
CREATE TABLE person_obj_table OF person_type;
INSERT INTO person_obj_table
VALUES (person_type(12, 'Bob Jones', '111-555-121'));
INSERT INTO person_obj_table
VALUES (student_type(5, 'Joe Lan', ‘6555-131', 12, 'HISTORY'));
INSERT INTO person_obj_table
VALUES (employee_type(55, 'Jane Smith', ‘6555-776', 100,
'Jennifer Nelson'));
INSERT INTO person_obj_table
VALUES (part_time_student_type(52, 'Kim Patel', '1555-1232', 14,
'PHYSICS', 20));
SZB, L.Banachowski
28
Kolekcje
Atrybut typu obiektowego lub kolumna tabeli może być
typu kolekcji:
1. VARRAY – typ tablicy jednowymiarowej (jak
wektor) o ustalonym maksymalnym rozmiarze;
2.
TABLE – typ tabeli zagnieżdżonej o nie ustalonym
maksymalnym rozmiarze.
SZB, L.Banachowski
29
Składowanie obiektów
1. Każdy obiektowy typ danych określa drzewo, w którego liściach
znajdują się:
•
•
atrybuty prostych typów danych jak NUMBER,
VARCHAR2, REF;
atrybuty typów kolekcji.
2. Atrybuty typów obiektowych rozwijają się w poddrzewa
odpowiadające ich typom, jak również w poddrzewa wszystkich
podtypów ich typów.
3. Tabela obiektowa jest reprezentowana przez tabelę relacyjną,
której kolumny odpowiadają atrybutom tego drzewa plus
kolumny odpowiadające atrybutom drzew wszystkich podtypów
danego typu.
SZB, L.Banachowski
30
Przykład
CREATE TYPE Person AS OBJECT (
first VARCHAR2(50),
last VARCHAR2(50)) NOT FINAL;
CREATE TYPE Emp UNDER Person (
salary NUMBER) FINAL;
CREATE TYPE Stud UNDER Person (
StudId NUMBER) FINAL;
CREATE TABLE Osoby OF Person;
Oracle reprezentuje wewnętrznie tabelę Osoby przez tabelę
relacyjną:
OsobyRel (OID, CzyPustyObiekt, TypObiektu, first, last, salary,
StudId)
SZB, L.Banachowski
31
Perspektywy obiektowe
Alternatywnie, można samemu zdefiniować relacyjną bazę danych
jako podstawę składowania zawartości tabel obiektowych i
udostępniać ją jako obiektową za pomocą perspektyw
obiektowych.
1. Zdefiniowanie wymaganych typów obiektowych w oparciu o
atrybuty odpowiadające istniejącym kolumnom tabel relacyjnych.
2. Zdefiniowanie perspektyw obiektowych w postaci zapytań
określających jak wydobywać dane z tabel relacyjnych.
3. Określenie jednoznacznych identyfikatorów umożliwiających
tworzenie wskaźników do obiektów w perspektywie (np. istniejący
klucz główny).
4. W przypadku złożonej perspektywy można użyć wyzwalaczy
INSTEAD OF do określenia sposobu modyfikacji danych w
perspektywie obiektowej.
SZB, L.Banachowski
32
Duże obiekty LOB
• Atrybut typu obiektowego
• Kolumna w tabeli
• Wartość zmiennej w PL/SQL
Foto
(BLOB)
Przepis
(CLOB)
Film
(BFILE)
FILESTREAM w SQLServer w miejsce BFILE.
SZB, L.Banachowski
33
Tabela z obiektami LOB
SQL>
2
3
4
5
CREATE TABLE employee
(emp_id
NUMBER,
emp_name
VARCHAR2(35),
resume
CLOB,
picture
BLOB);
SZB, L.Banachowski
34
Programowanie obiektowe w bazie danych Java składowana w bazie danych
Kod Javy może być zapisywany w bazie danych oraz może być
realizowany na serwerze bazy danych, tak jak kod języka SQL – w
ramach tej samej sesji i tej samej transakcji.
Obiekty Javy tworzone przy wykonywaniu kodu nie są
składowane w bazie danych (tylko kod w słowniku danych).
Z kodu SQL lub PL/SQL można wywoływać tylko metody
statyczne klas Javy.
Z kodu Javy można sięgać do bazy danych przez JDBC.
SZB, L.Banachowski
35
XML - ułatwienie wymiany danych
biznesowych między aplikacjami.
Integracja informacji.
• Łatwiej jest wymienić dane między aplikacjami –
wystarczy skupić się na danych i ich strukturze abstrahując
od konkretnych protokołów sieciowych i komunikacyjnych,
bez konieczności interpretowania wewnętrznych i
wzajemnie niezgodnych formatów przesyłania danych w
sieci.
SZB, L.Banachowski
36
Przykład XML: Lista publikacji
<LISTA>
<PUBLIKACJA>
<AUTOR>
<IMIĘ>Milan</IMIĘ><NAZWISKO>Kundera</NAZWISKO>
</AUTOR>
<TYTUŁ>Identity</TYTUŁ>
<ROK>1998</ROK>
</PUBLIKACJA>
<PUBLIKACJA>
<AUTOR>
<IMIĘ>Richard</IMIĘ><NAZWISKO>Feynman</NAZWISKO>
</AUTOR>
<TYTUŁ>The Character of Physical Law</TYTUŁ>
<FORMAT>Twardy</FORMAT>
</PUBLIKACJA>
</LISTA>
SZB, L.Banachowski
37
Metody reprezentowania dokumentów
XML w bazie danych
•
Dokumenty XML mogą być reprezentowane za
pomocą nowego obiektowego typu danych XML (w
Oracle XMLType), którego wartości mogą się pojawić
jako wartości kolumny w tabeli.
• Może być zastosowana struktura CLOB lub BLOB.
• Dokumenty XML o ustalonym schemacie mogą być
reprezentowane przez zbiór zwykłych tabel relacyjnych.
SZB, L.Banachowski
38
Oracle: Wbudowany typ obiektowy
XMLType
CREATE TABLE Kontakty(
Nazwisko VARCHAR2(50),
Karta XMLTYPE,
Data_utworzenia DATE);
lub
CREATE TABLE Obj_Kontakty OF XMLType;
SZB, L.Banachowski
39
Trzy komponenty baz analitycznych
v
Hurtownia danych: Zintegrowane dane
pochodzące z wielu źródeł:
– Ładowanie nowych danych z wielu źródeł.
– Integracja semantyczna (np. uzgodnienie walut, miar, formatów).
v
OLAP:
– Złożone zapytania i perspektywy SQL.
– Zapytania podobne do operacji na arkuszach kalkulacyjnych i na
wielowymiarowych danych.
– Zapytania interakcyjne i “online”.
v
Data Mining: Eksploracyjne wyszukiwanie
interesujących trendów i anomalii.
Wszystkie mogą być realizowane przez serwer bazy danych
SZB, L.Banachowski
40
Schemat gwiazda
W schemacie tym mamy jedną tabelę faktów w środku układu
i zbiór tabel wymiarów, każda z nich opisuje jeden wymiar.
Identyfikatory wartości wymiarów stanowią klucze obce w
tabeli faktów.
Przykład: tabela faktów sprzedaży w podziale względem
wymiarów struktury sprzedaży, czasu, wymiaru geograficznego i
wymiaru produktów.
Struktura
sprzedaży
Wymiar
geograficzny
id_sprz
nazwisko
oddział
id_geo
miasto
wojew
makroreg
Czas
id_czas
miesiąc
kwartał
rok
SZB, L.Banachowski
Sprzedaż
id_sprz
id_geo
id_czas
id_prod
ilość
cena
zniżka
wartość
Produkty
id_prod
produkt
opakow
grupa_pr
41
Indeksy bitmapowe
CREATE BITMAP INDEX Ind_P_Os ON Osoby(Płeć);
CREATE BITMAP INDEX Ind_P_Os ON Osoby(Stanowisko);
Nazwisko
Płeć
Stanowisko
Kruszewski
M
Dyrektor
Jankowski
M
Sprzedawca
Malinowski
M
Sprzedawca
Gazda
K
Kasjer
Wiśniewski
M
Kasjer
Bojanowska
K
Sprzedawca
SZB, L.Banachowski
Indeks na Płeć
Indeks na Stanowisko
Nr wiersza
M
K
D S K
----------------------------------------1
1
0
1 0 0
2
1
0
0 1 0
3
1
0
0 1 0
4
0
1
0 0 1
5
1
0
0 0 1
6
0
1
0 1 0
SELECT Nazwisko FROM Osoby WHERE
(Płeć = 'K' AND Stanowisko ='K') OR
(Płeć = 'M' AND
Stanowisko = 'D')
sprowadza się do wykonania trzech operacji na wektorach
bitowych:
([000101] AND [000110]) OR ([111010] AND [100000])
= [100100]
dając jako wynik zapytania pierwszą i czwartą osobę
(miejsca jedynek w wektorze)
42
Perspektywy zmaterializowane
indeksowane perspektywy w SQLServer, tabele zmaterializowanego zapytania w DB2
Po zaprojektowaniu tabel faktów i wymiarów następnie projektuje się
perspektywy zmaterializowane określające wymagane agregacje danych z
powyższych tabel. Na perspektywie zmaterializowanej można zakładać
indeksy, więc ma ona te same własności co tabela.
CREATE MATERIALIZED VIEW Sprzedaz_mv
ENABLE QUERY REWRITE
AS
SELECT s.Nazwa_sklepu, SUM(f.Wielkosc) AS Suma
FROM Sklep s INNER JOIN Sprzedaz f
ON f.IdSklepu = s.IdSklepu
GROUP BY s.Nazwa_sklepu;
ENABLE QUERY REWRITE - zapytania pisane w terminach tabel faktów i
wymiarów są przekształcane przez optymalizator do zapytań
korzystających z perspektyw zmaterializowanych.
SZB, L.Banachowski
43
Funkcje analityczne
nazwa_funkcji(argument,...) OVER (klauzula_okna)
Dla każdego wynikowego wiersza zapytania
określamy zbiór powiązanych z nim wierszy nazywany oknem tego wiersza. Definiuje się go
za pomocą tzw. klauzuli okna. Funkcja
analityczna podaje pewną statystykę wartości w
wierszu względem wartości w jego oknie.
Występują od standardu SQL’2003.
SZB, L.Banachowski
44
PARTITION BY
v
v
v
PARTITION BY jest podobne do GROUP BY z tym, że wartości
podsumowujące są załączane do każdego wiersza w grupie, a nie tylko
raz dla całej grupy.
SELECT e.Ename, e.Sal,
SUM(e.Sal) OVER (PARTITION BY e.Deptno) AS Suma,
Round(100*e.Sal/(SUM(e.Sal) OVER (PARTITION BY e.Deptno)))
AS Proc_zarob
FROM Emp e
ORDER BY e.Deptno, e.Ename; -- Oknem każdego wiersza jest jego
grupa określona przez PARTITION
ENAME SAL SUMA PROC_ZAROB
---------- ------- --------- -------------------CLARK 2450 8750
28
KING 5000 8750
57
MILLER 1300 8750
15
ADAMS 1100 10875
10
FORD 3000 10875
28
………………
SZB, L.Banachowski
45