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