sbd, Baz-danych---sciaga, Baza danych -------------


Składniki systemu baz danych:

System baz danych = Trwała pamięć zewnętrzna + System Zarządzania Bazami Danych (DBMS) + Język zapytań

Środki służące zwiększeniu niezawodności pamięci zewnętrznej:

Funkcje DBMS:

Zadania DBA:

Narzędzia do dyspozycji DBA:

Zawartość słownika danych:

Dostęp do słownika danych:

Architektura systemu baz danych:

zbiór zewnętrznych widoków, poprzez które użytkownicy widzą zawartość bazy danych. Widok jest wyabstrahowanym fragmentem bazy danych, zdefiniowanym w terminach języka zapytań

Zawiera model konceptualny bazy. Model ten obejmuje całą zawartość bazy, tak jak ją widzi administrator lub właściciel bazy

Fizyczna reprezentacja bazy w postaci plików dyskowych i algorytmów dostępu do nich. Jest to reprezentacja niskiego poziomu.

Program jest zależny od danych jeżeli wiedza na temat struktury danych i metod dostępu do nich jest wbudowana w program. Tzn. nie jest możliwa zmiana:

bez zmian w programie.

Niezależność programu od danych oznacza sytuację odwrotną.

Korzyści:

bez konieczności dokonywania zmian w kodzie aplikacji bazodanowych.

Przetwarzanie transakcyjne:

Transakcja przeprowadza bazę danych z jednego stanu spójnego do innego. Po drodze mogą wystąpić stany niespójne.

Przetwarzanie transakcyjne gwarantuje, że baza nie pozostanie w stanie spójnym mimo przerwania transakcji.

Każda encja jest inna. W celu uproszczenia świata rzeczywistego, grupujemy (klasyfikujemy) podobne encje w zbiory encji (stosujemy zasadę abstrakcji).

Encje w zbiorze encji są rozróżniane przez wartości atrybutów.

Własności atrybutów zbioru encji:

Klucz zbioru encji.

Własności klucza zbioru encji:

Nie istnieją takie 2 encje w zbiorze, które mają takie same wartości atrybutów kluczowych. Innymi słowy: wartość klucza jednoznacznie identyfikuje encję.

Nie można z klucza usunąć żadnego atrybutu bez naruszenia wymogu jednoznaczności. Innymi słowy: każda składowa klucza złożonego jest potrzebna.

Każdy związek stopnia n > 2 można zanienić na n związków binarnych.

Jeśli klucz encji reprezentującej związek staje się długi (wieloczłonowy), warto rozważyć zastąpienie go kluczem prostym typu identyfikator.

Zbiory encji pozostające w związku nie muszą być różne.

Pomiędzy tymi samymi zbiorami encji może zachodzić kilka różnych związków.

Związek IS_A (E1, E2) oznacza, że każda encja ze zbioru E1 jest także encją ze zbioru E2.

Innymi słowy, zbiór encji E1 jest podzbiorem zbioru encji E2.

Związek IS_A jest związkiem wbudowanym w model związków encji (ERM), typu 0..1:1

Każda encja e є E1 ma wszystkie atrybuty encji ze zbioru E2 i atrybuty dodatkowe, nie istniejące w zbiorze E2.

Niech D­­1, D­2, ..., Dn są to dziedziny proste, 0<n<∞.

D1 х D2 х ... х Dn - produkt kartezjański tych dziedzin prostych: zbiór wszystkich krotek (d1, d2, ... dn) takich, że d1 є D1, d2 є D2, ..., dn є Dn.

Liczba n to stopień relacji.

Poszczególne pozycje krotek relacji nazywamy atrybutami relacji.

Relację reprezentujemy w postaci tabeli, w której każdy wiersz odpowiada jednej krotce, a każda kolumna jednemu atrybutowi.

Nazwa kolumny jest nazwą atrybutu. Nazwy atrybutów nie mogą się powtarzać.

Tablica jest tylko wygodną reprezentacją, a nie relacją.

Własności relacji:

Każda relacja ma klucz główny.

Klucze relacji - warunki:

  1. albo być równa wartości PK w pewnej krotce R1,

  2. albo musi być cała równa NULL

Reprezentacja zbiorów encji:

Każdy zbiór encji reprezentujemy jako oddzielną relację o kluczu głównym równym kluczowi głównemu zbioru encji.

Reprezentacja związków pomiędzy zbiorami encji:

Związki „jeden do jeden”

Reprezentujemy przez klucz obcy wstawiony do dowolnej z dwóch relacji.

Związki „jeden do wiele”

Reprezentujemy przez klucz obcy wstawiony do relacji po stronie n związku.

Związki „wiele do wiele”

Reprezentujemy przez oddzielną relację o kluczu będącym złożeniem kluczy głównych związanych zbiorów encji.

Wynikiem każdego operatora algebry relacji jest relacja. Zatem algebra relacji jest domknięta. Umożliwia to budowę dowolnie zagnieżdżonych wyrażeń relacyjnych.

Jak wybrać tylko określone atrybuty relacji?

Do takich celów służy operacja projekcji (PROJECT).

Jak wybrać tylko określone krotki relacji?

Do takich celów służy operacja selekcji (SELECT).

Jak powiązać ze sobą dane z dwóch relacji?

Do takich celów służy operacja złączenia (JOIN).

Złączenie można stosować do kilku tablic.

Złączenia są zwykle składane z projekcją, w przeciwnym razie wynik może mieć bardzo dużą liczbę kolumn.

Składniki SQL:

Data Definition Language (DDL) + Data Manipulation Language (DML) + Data Control Language (DCL).

TWORZENIE TABLIC:

Typy danych:

INTEGER, SMALLINT - liczba całkowita (zakres zależy od DBMS)

DECIMAL[(m[,n])], NUMERIC[(m[,n])] - stałoprzecinkowa liczba rzeczywista o m cyfrach znaczących i n cyfrach po przecinku

FLOAT[(m)] - zmiennoprzecinkowa liczba rzeczywista o m cyfrach

REAL - to samo co DECIMAL

DOUBLE PRECISION - to samo co FLOAT

CHARACTER(n) - łańcuch znaków o długości do n znaków (separator: „lub”)

DATE - data

LOGICAL - wartości logiczne: yes/no, false/true

Inne typy danych:

VARCHAR, DATETIME, MONEY, TEXT, IMAGE, BLOB, CLOB ...

Opcje (przykładowe):

NOT NULL [UNIQUE] - w kolumnie musi być wartość, jeśli UNIQUE to unikatowa

[NOT] CASE-SENSITIVE - określa, czy przy porównywaniu wartości w kolumnie znakowej ma być brana pod uwagę wielkość liter

COLUMN-LABEL łańcuch - etykieta (nagłówek) kolumny przy wyświetlaniu tabelarycznym

LABEL łańcuch - etykieta kolumny przy wyświetlaniu swobodnym

FORMAT łańcuch - format wyświetlania wartości kolumny

DEFAULT wartość-początkowa - domyślna wartość kolumny

AUTOINCREMENT - powoduje automatyczne nadawanie wartości kolumny przez system podczas wstawiania nowego wiersza

PRIMARY KEY - określa kolumnę jako klucz główny

REFERENCES [nazwa tablicy] - określa kolumnę jako klucz obcy

Zmiana schematu tablicy:

ALTER TABLE <nazwa tablicy>

{ADD COLUMN <kolumna> <typ danych> [opcje]|

DROP COLUMN <kolumna>|

ALTER COLUMN <kolumna> [opcje] }

Wstawianie danych do tablicy:

INSERT INTO <nazwa tablicy>

[(<lista kolumn>)]

VALUES (<lista wartości>)

SELECT - przegląd:

SELECT [DISTINCT] <lista kolumn/wyrażeń>

FROM <lista tablic>

[ WHERE <warunek>]

[ GROUP BY <lista kolumn>]

[ HAVING <warunek>]

[ UNION <instrukcja SELECT>]

[ ORDER BY <lista kolumn/numerów>]

SELECT - zapytania proste:

SELECT [DISTINCT] <lista kolumn/wyrażeń>

FROM <nazwa tablicy>

[WHERE <warunek>]

Zapytanie proste przeprowadza kombinację projekcji i selekcji (jeśli występuje fraza WHERE) w stosunku do jednej tablicy.

Uwaga: <lista kolumn> może przybrać formę * (co oznacza wybór wszystkich kolumn)

Wyrażenia:

Operatory języka SQL występujące w wyrażeniach we frazie SELECT, WHERE i in:

Operatory arytmetyczne:

** ^ - potęgowanie

* / - dzielenie

+ - - dodawanie, odejmowanie

Operatory logiczne:

NOT AND OR

Operatory porównania:

= > < >= <= <>

Operatory specjalne:

IN BETWEEN LIKE MATCHES

Operatory porównania z wartością pustą:

IS NULL IS NOT NULL

Funkcje agregujące:

COUNT() - zwraca liczbę wierszy wybranych w zapytaniu

AVG() - oblicza średnią arytmetyczną w kolumnie numerycznej

SUM() - sumuje wartości kolumn numerycznych

MIN() - znajduje wartość minimalną w kolumnie znakowej, numerycznej lub daty

MAX() - znajduje wartość maksymalną

Operatory specjalne:

IN - sprawdza, czy wartość w kolumnie jest równa jednej z wartości określonej na liście wartości lub będących wynikiem instrukcji SELECT.

BETWEEN - sprawdza, czy wartość w kolumnie jest zawarta w przedziale wartości.

LIKE - porównuje zawartość kolumny znakowej z łańcuchem znaków, który może zawierać symbole wieloznaczne (wildcards). Łańcuch ze znakami wieloznacznymi to wzorzec.

Symbole wieloznaczne:

_ (podkreślenie) - pasuje do dowolnego pojedynczego znaku,

% (procent) - pasuje do dowolnej liczby dowolnych znaków.

CONTAINS - sprawdza, czy wartość w kolumnie znakowej zawiera wskazany łańcuch.

Funkcje agregujące:

COUNT()

COUNT(*) - zwraca liczbę wierszy w tablicy wynikowej

COUNT(DISTINCT <nazwa kolumny>) - zwraca liczbę różnych wartości we wskazanej kolumnie.

SUM([DISTINCT] <nazwa kolumny>)

MIN([DISTINCT] <nazwa kolumny>)

MAX([DISTINCT] <nazwa kolumny>)

AVG([DISTINCT] <nazwa kolumny>)

Zwracają odpowiednio: sumę, minimum, maksimum i średnią z wartości podanej kolumny. Dla SUM() i AVG() kolumna musi być numeryczna, dla MIN() i MAX() kolumna może być też kolumną znakową lub daty.

SELECT - porządkowanie wyników:

Bez frazy ORDER BY wyniki zapytania ukazują się w porządku nieokreślonym (zwykle w porządku wstawienia ich do tablicy).

ORDER BY <nazwa kolumny/numer> [ASC/DESC]

[,<nazwa kolumny/numer> [ASC/DESC]...]

Uwagi:

  1. Numer kolumny we frazie ORDER BY oznacza pozycję kolumny na liście wyboru instrukcji SELECT. Trzeba go użyć, jeśli porządkujemy wg wartości wyrażenia.

  2. Domyślnie przyjmowany jest porządek rosnący (ASC).

SELECT - grupowanie:

GROUP BY <nazwa kolumny>

[,<nazwa kolumny>...]

[HAVING <warunek>]

Fraza ta powoduje powiązanie ze sobą wierszy wynikowych z instrukcji SELECT w grupy wierszy, w których wskazane we frazie GROUP BY kolumny mają tę samą wartość. Następnie każda grupa jest redukowana do pojedynczego wiersza. W tym wierszu występują kolumny z frazy GROUP BY oraz ew. kolumny będące wynikiem funkcji agregujących. Funkcje te wówczas działają na każdej grupie osobno. Następnie eliminowane są wiersze nie spełniające warunku HAVING.

Uwagi:

  1. Każda kolumna występująca we frazie GROUP BY musi wystąpić na liście wyboru instrukcji SELECT i na odwrót.

  2. W warunku HAVING może wystąpić funkcja agregująca (ale nie w warunku WHERE!).

SELECT - złączenia:

Jeśli fraza FROM zawiera więcej niż jedną tablicę, instrukcja SELECT wykonuje produkt kartezjański (operator TIMES) wskazanych tablic. Jeśli dodatkowo występuje fraza WHERE, wykonywana jest operacja relacyjnego złączenia.

Samozłączenie umożliwia wiązanie ze sobą informacji zawartych w różnych wierszach.

Do dokonania samozłączenia konieczne jest zastosowanie aliasów (nazw korelacji).

SELECT - podzapytania:

W podzapytaniu instrukcja SELECT jest zagnieżdżona we frazie WHERE lub HAVING. Zagnieżdżona instrukcja SELECT, zwana zapytaniem wewnętrznym, dostarcza wartości dla warunku WHERE lub HAVING. Instrukcja SELECT zawierająca podzapytanie to zapytanie zewnętrzne.

W warunku WHERE może wystąpić operator EXISTS (predykat). EXISTS zwraca True, jeśli podzapytanie zwraca choć jeden wiersz, a False w przeciwnym wypadku.

Wstawianie danych z innych tablic:

INSERT INTO <nazwa tablicy>

[(<lista kolumn>)]

<instrukcja SELECT>

Cel: wstawienie do jednej tablicy wierszy wybranych z innej tablicy lub innych tablic.

Zachowanie wyników zapytania:

Niektóre DBMS pozwalają na zachowanie wyników instrukcji SELECT w tablicy tymczasowej, przez zastosowanie frazy podobnej do poniższej:

INTO TEMP <nazwa tablicy> (<lista kolumn>)

Tablica tymczasowa jest tworzona przez instrukcję SELECT zgodnie z typami danych umieszczonych na liście wyboru instrukcji SELECT. Tablica taka jest automatycznie kasowana podczas zamykania bazy danych przez bieżącego użytkownika.

Tablica tymczasowa może być używana we frazach FROM następnych instrukcji SELECT. W ten sposób można rozbijać skomplikowane zapytania na kilka etapów.

Niektóre systemy DBMS nie dają możliwości bezpośredniego zachowywania wyników instrukcji SELECT w tablicy tymczasowej. W takich wypadkach należy jawnie utworzyć nową tablicę i zastosować „wsadową” wersję instrukcji INSERT.

Aktualizowanie danych:

UPDATE <nazwa tablicy>

SET <nazwa kolumny> = <wyrażenie>

[,<nazwa kolumny> = <wyrażenie>...]

[WHERE <warunek>]

Cel: zmiana wartości w kolumnach jednego lub wielu wierszy tablicy.

Usuwanie danych:

DELETE FROM <nazwa tablicy>

[WHERE <warunek>]

Cel: usunięcie z tablicy jednego lub wielu wierszy.

SELECT - sumowanie tablic:

<instrukcja SELECT>

UNION

<instrukcja SELECT>

Cel: Realizacja operacji algebry relacji UNION w stosunku do tablic będących wynikiem instrukcji SELECT. Tablice muszą być zgodne.

Uwagi:

  1. Z tablicy wynikowej usuwane są wiersze powtarzające się (duplikaty).

  2. Jeśli nazwy kolumn sumowanych tablic są różne, tablica wynikowa przyjmuje nazwy kolumn z pierwszej tablicy.

  3. Jeśli po UNION występuje fraza ORDER BY, muszą w niej występować numery, a nie nazwy kolumn.

Tworzenie indeksów:

CREATE [UNIQUE] INDEX <nazwa indeksu>

ON <nazwa tablicy> (<nazwa kolumny> [ASC/DESC]

[,<nazwa kolumny> [ASC/DESC]...]};

Cel: tworzy indeks na określonych kolumnach, zwanych kluczami indeksu. Domyślnie indeks jest rosnący (ASC). Fraza UNIQUE oznacza, że kolumny indeksu zawierają wartości niepowtarzalne.

Co to jest indeks?

Jest to tablica zawierająca w jednej kolumnie wartości klucza, a w drugiej adresy wierszy w indeksowanej tablicy, które zawierają te wartości.

Do czego służy?

  1. Pozwala zwiększyć efektywność przetwarzania żądań dostępu do danych z tablicy (instrukcji SELECT).

  2. Porządkuje wiersze tablicy w określonym porządku.

  3. Zapobiega powtarzaniu się wartości w wierszach tablicy.

Kiedy tworzyć indeks?

  1. Gdy tablica jest często przetwarzana sekwencyjnie zgodnie z wartościami pewnej kolumny (rosnąco lub malejąco).

  2. Gdy konieczne jest zachowanie unikatowości wartości kolumn(y).

  3. Gdy dostęp do tablicy jest często realizowany zgodnie z wartościami danej kolumny lub kolumn (dostęp asocjacyjny)

Jaki jest koszt?

  1. Koszt pamięciowy (utrzymywanie dodatkowych danych).

  2. Koszt czasowy (konieczność aktualizacji indeksu podczas aktualizowania tablicy (instrukcje INSERT, UPDATE, DELETE).

Widoki:

Tworzenie widoków:

CREATE VIEW <nazwa widoku>

[<lista kolumn>]

AS <instrukcja SELECT>

[WITH CHECK OPTION]

Cel: utworzenie widoku. Opcjonalna <lista kolumn> określa nazwy kolumn widoku. Jeśli jej nie ma, nazwy kolumn pochodzą z instrukcji SELECT widoku.

WITH CHECK OPTION - gwarantuje, że wszystkie aktualizacje widoku (jeśli widok jest aktualizowany) spełniają warunki występujące we frazie WHERE instrukcji SELECT.

Aktualizowanie widoków:

Widok może nie być aktualizowany. Zależy to od ograniczeń na aktualizowanie widoków nałożonych przez konkretny DBMS

Przykładowe ograniczenia:

Usuwanie tablic, indeksów i widoków:

DROP TABLE <nazwa tablicy>

Cel: skasowanie istniejącej tablicy. Kasowane są wszystkie dane z tablicy. Kasowane są także wszystkie indeksy i widoki zdefiniowane na tej tablicy.

DROP INDEX <nazwa indeksu>

Cel: skasowanie istniejącego indeksu. Nie wpływa na indeksowaną tablicę.

DROP VIEW <nazwa widoku>

Cel: skasowanie istniejącego widoku. Nie wpływa na tablice występujące w definicji widoku.

Kursory:

Kursory są elementem zanurzonego języka SQL. Umożliwiają:

Po ustaleniu pozycji kursora na wierszu można:

Fazy użycia kursora:

1. Deklaracja kursora: DECLARE CURSOR

2. Otwarcie kursora: OPEN

3. Pobranie danych: FETCH

i po zakończeniu operowania kursorem

4. Zamknięcie kursora: CLOSE

DECLARE CURSOR - kojarzy nazwę kursora z instrukcją SELECT

OPEN - wybiera wszystkie wiersze, które spełniają warunki instrukcji DECLARE CURSOR, po czym ustawia kursor na pozycję przed pierwszym wierszem.

FETCH - pobiera kolejny wiersz i przypisuje wartości z kolumn do zmiennych programowych. Ustawia kursor na pozycji przed następnym wierszem lub, jeśli nie ma następnego wiersza, po ostatnim wierszu.

UPDATE (pozycyjna) - modyfikuje wiersz, na który wskazuje kursor.

DELETE (pozycyjna) - usuwa wiersz, na który wskazuje kursor.

CLOSE - zamyka kursor.

DECLARE <nazwa kursora> CURSOR FOR <instrukcja SELECT>

[FOR{READ [ONLY]|UPDATE}]

nazwa kursora - określa nazwę nowego kursora,

instrukcja SELECT - określa zbiór wierszy, który będzie dostępny po otwarciu kursora,

FOR READ [ONLY] - określa, że wiersze pobierane przez kursor, nie mogą być modyfikowane,

FOR UPDATE - określa, że wiersze pobierane przez kursor mogą być modyfikowane (o ile instrukcja SELECT nie zawiera UNION).

OPEN <nazwa kursora>

CLOSE <nazwa kursora>

nazwa kursora - nazwa otwartego kursora

Uwaga: Po ponownym otwarciu kursora system pobiera od nowa wynikowy zbiór wierszy, który może różnić się od zbioru poprzedniego.

FETCH <nazwa kursora> INTO <lista zmiennych>

lista zmiennych - lista zmiennych programowych, do których są przekazywane wartości kolumn.

DELETE FROM <nazwa tablicy>

WHERE CURRENT OF <nazwa kursora>

UPDATE <nazwa tablicy>

SET <nazwa kolumny> = {NULL | <wyrażenie>}

[,<nazwa kolumny> = {NULL | <wyrażenie>}]...

WHERE CURRENT OF <nazwa kursora>

Fraza WHERE CURRENT OF nazwa kursora identyfikuje kursor wskazujący na wiersz, który ma zostać usunięty lub zaktualizowany.

Relacyjna zupełność SQL:

SQL jest relacyjnie zupełny.

Normalizacja relacyjnych baz danych:

Normalizacja - przegląd:

Tablica PARTICIPANTS wykazuje wiele niepożądanych cech wynikających z redundacji (wiele danych jest powtórzonych).

Celem normalizacji jest usunięcie redundancji tak, by jedna informacja była przechowywana w bazie danych tylko jeden raz.

Zależności funkcjonalne:

Druga postać normalna:

Trzecia postać normalna:

Postać normalna Boyce'a-Codda:

Czwarta postać normalna:

Zależności wielowartościowe:

Normalizacja - podsumowanie:

Dobrze zaprojektowana relacja składa się z klucza głównego (prostego lub złożonego) i z pewnej liczby niezależnych od siebie atrybutów. Każdy atrybut zależy tylko od całego klucza głównego.

2NF dotyczy relacji ze złożonym kluczem głównym. Wymaga by żaden atrybut niekluczowy nie zależał od części klucza głównego.

3NF wymaga by każdy atrybut niekluczowy zależał tylko od klucza głównego.

4NF dotyczy relacji ze złożonym kluczem głównym. Wymaga by relacja zawierała co najwyżej jedną zależność wielowartościową.

W każdym wypadku sprowadzenie do danej postaci normalnej wymaga dekompozycji relacji na kilka innych relacji drogą projekcji.


Wyszukiwarka