Bazy danych 2
Wykład 3
Metodologia projektowania baz danych
(projektowanie fizyczne)
Projektowanie fizyczne
- przegląd krok po kroku
1.
Wybór systemu zarządzania bazą danych (BDMS)
2.
Wyrażenie logicznego modelu danych w docelowym BDMS
a)
Zaprojektowanie relacji w systemie
b)
Zaprojektowanie danych pochodnych
c)
Zaprojektowanie więzów ogólnych
3.
Projektowanie reprezentacji fizycznej
a)
Analiza transakcji
b)
Wybór optymalnej reprezentacji plików
c)
Utworzenie indeksów
d)
Oszacowanie wymaganej pamięci dyskowej
4.
Projektowanie perspektyw użytkowników
5.
Projektowanie mechanizmów ochrony bezpieczeństwa
Wybór BDMS
Projektant powinien wiedzieć:
1. Jak utworzyć relacje bazowe
2. Czy system umożliwia definiowanie kluczy głównych, obcych i
alternatywnych
3. Czy system umożliwia definiowanie warunku „wymagana
obecność danych”
4. Czy system umożliwia definiowanie dziedzin
5. Czy system wspiera więzy integralności referencyjnej
6. Czy system umożliwia definiowanie więzów ogólnych
Projektowanie relacji
Do opisu relacji używamy rozszerzonej wersji DBDL
Domain NumerNieruchomości
łańcuch o zmiennej długości, maks. dł 5
Domain Ulica
łańcuch o zmiennej długości, maks. dł 25
Domain Miasto
łańcuch o zmiennej długości, maks. dł 25
Domain TypNieruchomości
jeden znak, wartość ze zbioru ‘B’, ‘C’, ‘F’
Domain NieruchomośćPokoje
liczba całkowita, z przedziału 1-15
Domain NieruchomośćCzynsz
wartość walutowa, z przedziału 0,00-
999,99
Domain NumerWłaściciela
łańcuch o zmiennej długości, maks. dł 5
Domain NumerPracownika
łańcuch o zmiennej długości, maks. dł 5
Domain NumerBiura
łańcuch o stałej długości, dł 4
Nieruchomość(
nieruchomośćNr
NumerNieruchomości
NOT NULL
ulica
Ulica
NOT NULL
miasto
Miasto
NOT NULL
typ
TypNieruchomości
NOT NULL DEFAULT ‘F’
pokoje
NieruchomośćPokoje
NOT NULL DEFAULT 4
czynsz
NieruchomośćCzynsz
NOT NULL DEFAULT 600
właścicielNr
NumerWłaściciela
NOT NULL
pracownikNr
NumerPracownika
NOT NULL
biuroNr
NumerBiura
NOT NULL
kaucja
NieruchomośćCzynsz
NOT NULL
PRIMARY KEY (nieruchomośćNr)
ALTERNATE KEY (ulica, miasto)
FOREIGN KEY (pracownikNr) REFERENCES Personel(pracownikNr) ON
UPDATE CASCADE ON DELETE SET NULL
FOREIGN KEY (właścicielNr) REFERENCES WłaścicielPrywatny(właścicielNr)
and WłaścicielInstytucjonalny(właścicielNr) ON UPDATE
CASCADE ON DELETE NO ACTION
DERIVED kaucja
(czynsz*0,1)
Projektowanie relacji
Sposób implementacji relacji bazowych jest uzależniony od
możliwości oferowanych przez docelowy DBMS
Projekt relacji powinien być w pełni udokumentowany wraz
z informacjami o przyczynach wyboru konkretnych
rozwiązań
Projektowanie danych pochodnych
Z punktu widzenia fizycznego projektu bazy danych wybór
między reprezentacją atrybutu pochodnego w bazie danych a
jego wyliczaniem przy każdym odwołaniu do niego jest
zawsze kompromisem pomiędzy różnymi celami.
Należy oszacować:
Koszt związany z przechowywaniem danych pochodnych
w bazie i zapewnieniem ich zgodności z danymi, na
podstawie których są wyznaczane;
Koszt wyliczenia wartości atrybutów pochodnych przy
każdym odwołanie do nich
Ostateczny wybór powinien służyć zapewnieniu maksymalnej
wydajności systemu.
Projektowanie danych pochodnych
(przykład)
Projektowanie więzów ogólnych
Sposób zaprojektowania więzów ogólnych jest zależny od DBMS.
Zakres wbudowanych w DBMS funkcji, które umożliwiają
definiowanie więzów ogólnych, jest bardzo zróżnicowany.
Niektóre systemy umożliwiają zapewnienie więzów za pomocą
standardowych poleceń SQL, w innych konieczne jest
zastosowanie wyzwalaczy wymuszających spełnienie określonych
więzów.
W standardzie SQL możemy realizować więzy ogólne za pomocą
klauzuli CHECK
Wyróżnia się dwa sposoby stosowania ograniczenia CHECK
Ograniczenie na poziomie kolumny – ograniczenie to dotyczy
tylko i wyłącznie danej kolumny
Ograniczenie na poziomie tabeli – ograniczenie dotyczące
kilku kolumn tabeli
Projektowanie więzów ogólnych
Przykłady ograniczenie na poziomie kolumny
CREATE TABLE products
( product_no integer,
name text,
price numeric CHECK (price > 0) );
Ograniczeniom można nadawać nazwy, ułatwia to odwoływanie do nich
CREATE TABLE products
( product_no integer,
name text,
price numeric CONSTRAINT positive_price CHECK (price > 0) );
Projektowanie więzów ogólnych
Przykłady ograniczenie na poziomie tabel
CREATE TABLE products
( product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0),
CONSTRAINT valid_discount CHECK (price > discounted_price) );
Standard SQL dopuszcza tworzenie podzapytań w klauzuli CHECK
CHECK (NOT EXISTS
(SELECT pracownikNr
FROM Nieruchomość
GROUP BY pracownikNr
HAVING COUNT(*)>100))
Projektowanie reprezentacji fizycznej
CEL: wybór sposobu reprezentacji relacji i krotek w
pamięci zewnętrznej
Na ocenę wydajności systemu wpływa szereg czynników:
Przepustowość transakcyjna –
określa liczbę transakcji, które
mogą zostać przetworzone w ustalonej jednostce czasu
Czas reakcji
– jest to czas potrzebny na realizację jednej
transakcji
Zapotrzebowanie na pamięć zewnętrzna –
parametr ten określa
rozmiar pamięci zewnętrznej zajmowanej przez pliki bazy danych
Analiza transakcji
CEL: Zrozumienie sposobu działania transakcji występujących w
projekcie oraz analiza najważniejszych z nich
Pytania:
Które transakcje są często wykonywane i mają istotny wpływ
na wydajność systemu
Które transakcje są kluczowe dla działania instytucji
W jakich godzinach/dniach tygodnia występuje szczyt odwołań
do bazy danych
Informacje te wykorzystujemy do ustalenia, w których
komponentach bazy danych mogą występować problemy
obniżające wydajność systemu
Na podstawie tych danych wybierzemy organizację plików i
indeksów
Analiza transakcji (przykład)
Analiza transakcji (przykład)
Analiza transakcji (przykład)
Analiza transakcji
Po przeanalizowaniu istotnych transakcji, analizujemy dokładniej każdą z
nich – musimy ustalić
• Relacje i atrybuty do których odwołuje się transakcja oraz typ odwołania
•
Dla transakcji modyfikującej należy ustalić modyfikowane atrybuty –
te atrybuty nie powinny występować w strukturach dostępu do danych
(indeksach)
• Wszystkie atrybuty wykorzystywane w predykatach (w SQL warunki
umieszczone w klauzuli WHERE)
•
Atrybuty te należy traktować jako kandydatów do umieszczenia w
strukturach ułatwiających dostęp do danych
Analiza transakcji
Dla zapytań, atrybuty występujące w złączeniu dwóch lub większej liczby
relacji
Te atrybuty również mogą być kandydatami do umieszczenia w
strukturach ułatwiających dostęp do danych
Przewidywana częstotliwość wykonywania transakcji
Wymagania dotyczące sposobu wykonania transakcji
Atrybuty wykorzystywane w jakichkolwiek predykatach krytycznych
lub w predykatach bardzo często wykonywanych transakcji powinny
mieć większy priorytet przy wyborze elementów struktur
ułatwiających dostęp do danych
Wybór reprezentacji plików
Celem tego kroku jest wybór optymalnej organizacji
plików dla każdej relacji, o ile umożliwia to docelowy
DBMS.
Wyróżnia się kilka różnych metod organizacji plików:
Sterty
Pliki haszowane
B+ - drzewa
klastry
Jeżeli system nie umożliwia wyboru organizacji plików, to krok
ten można pominąć.
Wybór indeksów
Definicja: Indeks jest to struktura danych umożliwiająca szybszy
dostęp do konkretnych rekordów w pliku, a tym samym
przyspieszenie realizacji zapytań.
Indeks w bazie pełni podobną rolę jak skorowidz (indeks) w
książce. Jest on strukturą pomocniczą, stowarzyszoną z
sekwencyjnym
plikiem danych
, w którym poszukiwanymi
elementami są rekordy lub grupy rekordów.
Indeks jest złożony z rekordów - każda jego pozycja zawiera
jedną z wartości klucza wyszukiwania oraz jeden lub więcej
adresów (numerów rekordów) pod którymi można znaleźć
poszukiwaną wartość.
Indeks jest posortowany według
pola indeksującego
,
odpowiadającego kluczowi wyszukiwania (jeden lub więcej
atrybutów)
Wybór indeksów
Do głównych typów indeksów nalezą:
Indeks główny – plik danych jest uporządkowany według pola
porządkującego, które jest kluczem relacji; pole indeksujące
jest równe polu porządkującemu relacji, jego wartości w
poszczególnych rekordach są unikalne
Indeks grupujący – plik danych jest uporządkowany według
pola nie będącego kluczem relacji, które jest też polem
indeksującym; jednej wartości pola indeksującego może
odpowiadać więcej niż jeden rekord; wykorzystane w tym
indeksie pole nazywa się atrybutem grupującym
Indeks pomocniczy – indeks zdefiniowany na podstawie pola,
które nie jest używane przy wyznaczaniu porządku rekordów
w bazie
Wybór indeksów
Wskazówki dotyczące tworzenia „listy pożądanych indeksów”
Należy utworzyć indeks dla klucza głównego (jeśli nie robi tego
DBMS)
Jeśli często występują odwołania do klucza obcego, warto
utworzyć dla niego indeks (jeśli nie robi tego DBMS).
Należy utworzyć indeks pomocniczy dla kolumn, które nie są
kluczami głównymi ani kluczami obcymi, ale mogą być używane
w złożonych powiązaniach
Należy utworzyć indeksy pomocnicze dla atrybutów intensywnie
wykorzystywanych w:
Kryteriach selekcji
ORDER BY
GROUP BY
Innych operacjach wymagających sortowania
Wybór indeksów
Wskazówki dotyczące tworzenia „listy pożądanych indeksów”
Nie należy indeksować małych relacji
Należy unikać indeksowania często modyfikowanego atrybutu
bądź relacji
Nie jest wskazane indeksowanie atrybutu służącego realizacji
zapytań, których wynikiem jest istotna frakcja krotek w relacji
(>25%)
Należy unikać indeksowania atrybutów składających się z
długich łańcuchów
Wybór indeksów
Z utrzymywaniem i używaniem indeksów wiąże się dodatkowy
koszt, na który składa się m.in.:
Koszt dodania nowego rekordu indeksowego do każdego
indeksu pomocniczego przy każdym dodaniu krotki do relacji;
Koszt modyfikacji indeksów pomocniczych wynikający z
odpowiednich modyfikacji krotek w relacji;
Wzrost rozmiaru pamięci dyskowej;
Możliwość obniżenia wydajności na etapie optymalizacji
zapytań, będąca wynikiem rozważania przez optymalizator
użycia każdego z indeksów
Przy dodawaniu indeksów pomocniczych należy rozważyć, czy ten
dodatkowy koszt zostanie zrekompensowany poprzez uzyskaną
dzięki indeksowi poprawę wydajności.
Wybór indeksów
Indeksy tworzymy w SQL za pomocą polecenia
CREATE [ UNIQUE ] INDEX
name
ON
table
[ USING
method
]
(
column1
[ASC|DESC],
column2
[ASC|DESC|,……..)
gdzie
name
– nazwa indeksu
table
– nazwa tabeli
method
– nazwa metody organizacji pliku indeksu,
Column1, column2, .. –
nazwy kolumn tabeli, tworzą
klucz indeksu; powinny być wymienione w kolejności od
najbardziej do najmniej znaczących
ASC|DESC – porządek kolumny rosnący|malejący
Wybór indeksów
Przykład
CREATE TABLE test2 ( major int, minor int, name varchar
);
Utworzymy indeks dla kolumn major i minor
CREATE INDEX test2_mm_idx ON test2 (major, minor);
Oszacowanie rozmiaru pamięci dyskowej
Podstawą szacowania są informacje:
o rozmiarze krotki
liczbie krotek w relacji
Należy oszacować maksymalny rozmiar danych w bieżących
warunkach
Należy uwzględnić możliwość rozrostu bazy danych
Projektowanie perspektyw użytkowników
Perspektywa jest relacja wirtualną, która nie musi istnieć
fizycznie w bazie danych, ale może być wyliczona w każdej
chwili na żądanie użytkownika.
Czasami perspektywy mogą być przechowywane w bazie
danych w postaci tabeli tymczasowej tworzonej w momencie
pierwszego odwołania do perspektywy
Rozróżniamy różne typy perspektyw
Perspektywa pozioma – ogranicza dostęp do wybranych
wierszy jednej lub wielu tabel
Perspektywa pionowa – ogranicza dostęp do wybranych
kolumn jednej lub wielu tabel
Perspektywy oparte na grupowaniu
Projektowanie perspektyw użytkowników
Zalety perspektyw
Niezależność danych – perspektywa może reprezentować
spójny i niezmienny obraz struktury bazy danych pomimo zmian
dokonywanych w tabelach bazowych (pomimo dodawania i
usuwania kolumn, zmiany związków, dzielenia tablic, zmiany ich
struktury i nazwy)
Poprawa bezpieczeństwa – każdy użytkownik może otrzymać
uprawnienia do bazy danych poprzez niewielki zbiór perspektyw,
które zawierają potrzebne mu dane
Uproszczenie zapytań
Wygoda – użytkownik widzi tylko tyle ile potrzebuje
Dostosowanie do użytkownika
Projektowanie perspektyw użytkowników
Wady perspektyw
Ograniczona możliwość modyfikacji – pewnych danych nie
można modyfikować poprzez perspektywy
Ograniczenia struktury – jeśli tworzona była perspektywa ze
wszystkich kolumn tabeli i dodaliśmy nowe kolumny, to nie będą
one występowały w perspektywie
Wydajność – korzystanie z perspektyw może spowodowac
spadek wydajności
Projektowanie mechanizmów ochrony
bezpieczeństwa
Zakres mechanizmów ochrony bezpieczeństwa oferowanych
przez systemy zarządzania bazą danych jest zróżnicowany
Relacyjne DBMS zasadniczo dostarczają dwa poziomy
ochrony bezpieczeństwa
ochronę systemu – stanowią mechanizmy kontroli dostępu i
użytkowania bazy danych na poziomie systemu, takie jak
nazwy użytkowników i hasła
ochronę danych – stanowią mechanizmy dostępu i
użytkowania obiektów bazy danych (relacji i perspektyw) do
których użytkownicy są uprawnieni
Wyszukiwarka
Podobne podstrony:
więcej podobnych podstron