Bazy danych

1. Relacyjny model danych i algebra relacji: atrybuty, dziedziny atrybutów,

krotki i relacje; operacje na relacjach.

1.1. Relacyjny model danych.

Relacja - definicja.

Relacja jest strukturą danych, którą można utożsamić z tabelą spełniającą

następujące założenia (Codda):

1. Każda relacja w bazie danych ma jednoznaczną nazwę.

2. Każda kolumna w relacji ma jednoznaczną nazwę w ramach jednej relacji.

3. Wszystkie wartości w kolumnie muszą być tego samego typu.

4. Porządek kolumn w relacji nie jest istotny.

5. Porządek wierszy nie jest istotny.

6. Każdy wiersz w relacji musi być różny.

7. Każde pole leżące na przecięciu kolumny/wiersza w relacji powinno zawierać

wartość atomową.

Słownik (dla opornych):

Relacja - tabela

Atrybut - kolumna

Dziedzina atrybutu - typ danych ustalonej kolumny

Krotka - wiersz w tabeli

Schemat relacji - nazwa tabeli z nazwami atrybutów

Schemat bazy danych - zbiór schematów relacji

1.2. Operacje na relacjach.

1.2.1. Rzutowanie - Relacja wyjściowa składa się ze wszystkich krotek obciętych

do wybranego zbioru atrybutów.

S:= PI zbiór_atrybutów(R)

OsobaWaga:= PI {Imię, Waga}(Osoba)

1.2.2. Selekcja - Z danej relacji wybieramy tylko krotki spełniające określony

warunek logiczny.

S:= ?warunek_logiczny(R)

OsobaLekka := ?Waga<=70(Osoba)

WiekWagaCiężka:=?{Wiek, Waga} (?Waga>70(Osoba))

Operatory 3 - 5 to wymagają, aby relacje miały równoważne schematy, tj. aby

odpowiednie atrybuty miały takie same nazwy i dziedziny.

1.2.3. Suma - Relacja złożona z krotek, które występują w którejkolwiek z

relacji składowych.

T := R

∪ S

1.2.4. Iloczyn - Relacja złożona z krotek, które występują w każdej z relacji

składowych.

T := R

∩ S

1.2.5. Różnica - Relacja złożona z krotek, które występują w pierwszej relacji

ale nie występują w drugiej.

T := R

∖ S

1.2.6. Iloczyn kartezjański - W wyniku powstaje relacja złożona ze wszystkich

kombinacji krotek z relacji składowych.

T := R × S

1.2.7. Złączenie naturalne

a) Ustalamy maksymalny (co do ilości) zbiór wspólnych atrybutów.

b) Wybieramy te pary krotek z relacji składowych, które dla wspólnych

atrybutów mają te same wartości, resztę uzupełniając pozostałymi elementami

krotek.

T := R ?? S

1.2.8. Złączenie theta - Złączenie naturalne jest przykładem tzw.

równozłączenia. Ogólniejszy przypadek stanowi złączenie theta. Wynikiem tej

operacji jest relacja zawierająca krotki iloczynu kartezjańskiego R i S,

spełniające warunek F.

T := R ??F S

Warunek F jest postaci R.a q S.b, gdzie q jest jednym z operatorów

{=,<,<=,>=,>,<>}.

1.2.9. Złączenie zewnętrzne lewostronne

T := R??LS

Do złączenia naturalnego dopisujemy te krotki relacji R, dla których nie

znaleziono odpowiednich krotek w relacji S (tzw. krotki wiszące).

Brakujące wartości uzupełniamy wartością NULL.

1.2.10. Złączenie zewnętrzne prawostronne

T := R??RS

Analogicznie, do złączenia naturalnego dopisujemy te krotki relacji S, dla

których nie znaleziono odpowiednich krotek w relacji R (tzw.

krotki wiszące). Brakujące wartości uzupełniamy wartością NULL.

11. Złączenie zewnętrzne obustronne - Jest to suma złączenia zewnętrznego

lewostronnego i prawostronnego.

T := R??LRS

Zmiany schematów relacji przy działaniu operatorów:

2. Język SQL – zapytania, selekcja, sortowanie, grupowanie, funkcje

agregujące, podzapytania.

Język SQL (Structured Query Language):

- język zapytań służący do zapisywania wyrażeń relacji,

modyfikacji relacji, tworzenia relacji

- stworzony w IBM w latach 70-tych

DML (Data Manipulation Language) - pobieranie danych z

relacji (zapytania), dopisywanie, usuwanie, modyfikowanie

danych w relacjach

DDL (Data Definition Language) - tworzenie nowych relacji

DCL (Data Control Language ) - nadzór nad ustanawianiem

praw dostępu do danych dla poszczególnych użytkowników

SELECT atrybuty

FROM relacja

WHERE warunek;

?atrybuty(?warunek(relacja))

Rzutowanie w języku SQL

S:= ?zbiór_atrybutów(R) SELECT zbiór_atrybutów

FROM R;

Selekcja w języku SQL

S:= ?warunek_logiczny(R) SELECT *

FROM R

WHERE warunek_logiczny;

Zapytania dotyczące jednej tabeli

a) zawartość całej tabeli;

SELECT * FROM Projekt;

b) wybrane atrybuty relacji

SELECT Nazwa, Dzial, MaxGodzin

FROM Projekt;

c) wybrane atrybuty relacji w innej kolejności

SELECT Dzial, MaxGodzin, Nazwa

FROM Projekt;

d) pojedynczy atrybut

SELECT Dzial

FROM Projekt;

e) pojedynczy atrybut z usunięciem duplikatów

SELECT DISTINCT Dzial

FROM Projekt;

f) wyznaczanie krotek spełniających określony warunek

SELECT ProjektID, Nazwa, Dzial, MaxGodzin

FROM Projekt

WHERE Dzial = 'Finanse';

g) wyznaczanie wszystkich atrybutów dla krotek spełniających

określony warunek

SELECT *

FROM Projekt

WHERE Dzial = 'Finanse';

h) małe i wielkie litery w zapytaniach

select *

from projekt

where dzial = 'Finanse';

Oracle:

rozróżnia wielkie i małe litery w napisach

SELECT *

FROM Projekt

WHERE dzial = 'FINANSE';

Empty set

MySQL:

- standardowo nie rozróżnia wielkich i małych liter w

napisach

- można to zmienić używając opcji BINARY przy tworzeniu

tabeli

SELECT *

FROM Projekt

WHERE dzial = 'FINANSE';

i) wyznaczenie projektów o maksymalnej ilości godzin

większej niż 100

SELECT *

FROM Projekt

WHERE MaxGodzin>100;

j) wyznaczenie projektów o maksymalnej ilości godzin

większej niż 100 w dziale finansowym

SELECT *

FROM Projekt

WHERE Dzial='Finanse' AND MaxGodzin>100;

k) wyznaczenie danych o nazwisku i dziale pracowników

działu księgowego

SELECT Nazwisko, Dzial

FROM Pracownik

WHERE Dzial='Ksiegowosc';

l) wyznaczenie danych o nazwisku i dziale pracowników działu

księgowego, finansowego i marketingu

SELECT Nazwisko, Dzial

FROM Pracownik

WHERE Dzial='Ksiegowosc' OR Dzial='Finanse'

OR Dzial='Marketing';

SELECT Nazwisko, Dzial

FROM Pracownik

WHERE Dzial IN ('Ksiegowosc','Finanse','Marketing');

m) wyznaczenie danych o nazwisku i dziale pracowników

działów różnych od księgowego, finansowego i marketingu

SELECT Nazwisko, Dzial

FROM Pracownik

WHERE NOT (Dzial='Ksiegowosc' OR

Dzial='Finanse' OR Dzial='Marketing');

SELECT Nazwisko, Dzial

FROM Pracownik

WHERE Dzial NOT IN

('Ksiegowosc','Finanse','Marketing');

n) sortowanie kolumn

SELECT Nazwisko, Dzial

FROM Pracownik

ORDER BY Dzial;

SELECT Nazwisko, Dzial

FROM Pracownik

ORDER BY Dzial DESC;

SELECT Nazwisko, Dzial

FROM Pracownik

ORDER BY Dzial DESC, Nazwisko ASC;

o) wyszukiwanie z zadanym zakresem

SELECT Nazwisko, Dzial

FROM Pracownik

WHERE NrPrac >= 200 AND NrPrac <= 500;

SELECT Nazwisko, Dzial

FROM Pracownik

WHERE NrPrac BETWEEN 200 AND 500;

p) wyszukiwanie wg zadanego wzorca:

_ oznacza dowolny jeden znak

% oznacza dowolny ciąg znaków

SELECT *

FROM Projekt

WHERE Nazwa LIKE 'Q_ Analiza Portfela';

SELECT *

FROM Pracownik

WHERE Telefon LIKE '285____';

SELECT *

FROM Pracownik

WHERE Telefon LIKE '285%';

SELECT *

FROM Pracownik

WHERE Dzial LIKE '%ing';

SELECT *

FROM Pracownik

WHERE Nazwisko LIKE '% Ja%';

SELECT *

FROM Pracownik

WHERE Nazwisko LIKE '%Ja%';

q) wyszukiwanie wartości NULL

SELECT *

FROM Pracownik

WHERE Telefon IS NULL;

r) funkcje agregujące: COUNT, MAX, MIN, SUM, AVG

SELECT COUNT(*)

FROM Projekt;

SELECT COUNT(Dzial)

FROM Projekt;

SELECT COUNT(DISTINCT Dzial)

FROM Projekt;

COUNT(DISTINCT Dzial)

3

SELECT MIN(MaxGodzin),MAX(MaxGodzin),

SUM(MaxGodzin)

FROM Projekt;

SELECT MIN(MaxGodzin),MAX(MaxGodzin),

SUM(MaxGodzin)

FROM Projekt

WHERE ProjektID < 1500;

niedozwolone przypadki:

- mieszanie nazw kolumn i funkcji agregujących

SELECT Name, SUM(MaxGodzin)

FROM Projekt

WHERE ProjektID < 1500;

- używanie funkcji agregujących w klauzuli WHERE

SELECT Name

FROM Projekt

WHERE MaxGodzin < AVG(MaxGodziny);

s) grupowanie krotek

- zliczyć pracowników w poszczególnych działach

SELECT Dzial, COUNT(*)

FROM Pracownik

GROUP BY Dzial;

tylko w tym przypadku nazwa kolumny i funkcja agregująca

mogą pojawić się obok siebie

- wypisać ilość pracowników w poszczególnych działach ale

tylko, jeśli ich więcej niż 1

SELECT Dzial, COUNT(*)

FROM Pracownik

GROUP BY Dzial

HAVING COUNT(*)>1;

- wypisać ilość pracowników o numerach powyżej 600 w

poszczególnych działach ale tylko, jeśli ich więcej niż 1

SELECT Dzial, COUNT(*)

FROM Pracownik

WHERE NrPrac < 600

GROUP BY Dzial

HAVING COUNT(*)>1;

- najpierw warunek WHERE, potem grupowanie, na końcu

funkcje agregujące

Podzapytania - dopisać.(?)

3. Zapytania DML – usuwanie, aktualizacja i dołączanie danych.

Definicja

DML (Data Manipulation Language) służy do wykonywania operacji na danych –

do ich umieszczania w bazie, kasowania, przeglądania, zmiany. Najważniejsze

polecenia z tego zbioru to:

◦ SELECT - pobieranie danych z bazy

◦ INSERT - umieszczanie danych w bazie

◦ UPDATE - zmiana danych

◦ DELETE - usuniecie bazy danych

◦ COMMIT - stałe zatwierdzenie tymczasowo zmienionych danych

◦ ROLLBACK - przywracanie struktury bazy danych do czasu z przed

określonego przedziału czasu

Dane tekstowe muszą być zawsze ujęte w znaki pojedynczego cudzysłowu (').

Najbardziej rozwinięte z pośród wyżej wymienionych poleceń to SELECT i

INSERT.

SELECT

Jest to polecenie pozwalające na odczytywanie danych z bazy danych oraz

wykonywanie na tych danych prostych obliczeń i przekształceń. Polecenie

SELECT pobiera krotki z relacji w bazie danych, przetwarza je (opcjonalnie) i

zwraca wynik w postaci zbioru odczytanych krotek.

SELECT atrybuty FROM table_name

SELECT atrybuty AS nazwa FROM table_name

gdzie AS jest to alias, nie są obowiązkowe i stosuje się je przy przy bardziej

skomplikowanych wyrażeniach

SELECT DISTINCT atrybut FROM table_name

gdzie DISTINCT - klauzula służąca do pozbycia się duplikatów z relacji

wyjsciowej

SELECT atrybut FROM table_name ORDER BY atrybut

gdzie ORDER BY służy do posortowania rosnąco (ASC) i malejąco (DESC)

SELECT atrybut FROM table_name WHERE atrybut OPERATO_LOGICZNY value

gdzie WHERE pozwalającą zdefiniować warunki jakie muszą spełniać zwracane

krotki za pomocą jednego z operatorów logicznych ( =, !=, <, >, <=, =>, <>

). Wyróżnia się tutaj operatory BETWEEN AND, IN oraz LIKE.

Operator BETWEEN AND sprawdza, czy jedna wartość zawiera się pomiędzy

dwoma innymi (włącznie): WHERE x BETWEEN y AND z

Operator IN sprawdza czy jedna wartość jest równa przynajmniej jednej z

wartości wymienionych na liście: WHERE x IN (value, value)

Operator LIKE jest specjalnym operatorem stosowanym do złożonego

porównywania łańcuchów WHERE x LIKE 'value' (występuje tutaj jeszcze znak

'%' - porównanie od dowolnego ciągu znaków, i '_' - dokładnie jeden znak)

Istnieją jeszcze zanegowane odpowiedniki operatorów logicznych

• NOT BETWEEN AND – wartość NIE znajduje się w zadanym przedziale

• NOT IN – wartość NIE znajduje się na zadanej liście

• NOT LIKE – łańcuch NIE pasuje do maski

• IS NOT NULL – wartość wyrażenia NIE jest nieznana

Przy złożonych zapytaniach stosuję się spójniki AND i OR

SELECT atrybut FROM table_name WHERE warunek AND warunek OR warunek

INSERT

Polecenie INSERT można wykorzystać do wstawiania do relacji wyników

zapytania. Aby to zrobić, należy zastąpić zapytaniem (polecenie SELECT) słowo

kluczowe VALUES i listę wartości.

INSERT INTO table_name

VALUES ('value',[NULL][DEFAULT],value);

gdzie

NULL - nowa krotka będzie miała pustą wartość

DEFAULT - w odpowiednim atrybucie zostanie zapisana jego wartość domyślna

Wady

trzeba znać kolejności jakiej są wstawiane atrybuty do relacji

trzeba podawać wartości dla wszystkich atrybutów bądź sprecyzować wartość

NULL

4. Zapytania DDL – tworzenie tabel, modyfikacje struktury tabel, usuwanie

tabel.

Definicja

Dzięki DDL (Data Definition Language) można operować na strukturach, w których dane są

przechowywane

– czyli np. dodawać, zmieniać i kasować

tabele lub bazy. Najważniejsze polecenia tej grupy to:

◦ CREATE - utworzenie struktury (bazy, tabeli, indeksu itp.)
◦ DROP i RENAME - usunięcie struktury i zmiana nazwy
◦ ALTER - zmiana struktury (dodanie kolumny do tabeli, zmiana typu danych w

kolumnie tabeli)

CREATE

Nowe relacje tworzy się za pomocą polecenia CREATE TABLE, za którym podaje

się nazwę relacji i listę atrybutów tej relacji. Listę atrybutów

tworzy się wymieniając po przecinku wyrażenia złożone z nazwy atrybutu, jego

typu i rozmiaru typu oraz opcjonalnie domyślnej wartości

atrybutu. Domyślna wartość jest wstawiana do relacji w sytuacji, gdy nie podano

w poleceniu INSERT wartości dla wszystkich atrybutów.

CREATE TABLE nazwa_relacji

(nazwa_atrybutu typ (rozmiar) [DEFAULT wartość_domyślna],

nazwa_atrybutu typ (rozmiar) [DEFAULT wartość_domyślna]);

Przykład

CREATE TABLE projekty (

nazwa CHARACTER VARYING (100),

budzet NUMERIC(10,2) DEFAULT 200000,

termin_zak DATE DEFAULT SYSDATE +

INTERVAL '5-6' YEAR (1) TO MONTH

);

Atrybut NAZWA reprezentuje nazwę projektu i jest typu łańcuchowego o

maksymalnym rozmiarze 100 .

Atrybut BUDZET reprezentuje budżet projektu i jest typu liczbowego. Domyślną

wartością tego atrybutu jest 200000.

Atrybut TERMIN_ZAK reprezentuje termin zakończenia projektu, a domyślną

wartością tego atrybutu jest wartość wyrażenia SYSDATE +

INTERVAL '5-6' YEAR (1) TO MONTH, które oznacza datę systemową w momencie

wstawiania krotki powiększoną o 5 lat i 6 miesięcy.

Przykładowe polecenia wykorzystujące wartości domyślne dla atrybutów:

INSERT INTO projekty(nazwa) VALUES (‘System informatyczny dziekanatu’);

Polecenie to spowoduje wstawienie do relacji PROJEKTY projektu o nazwie

„System informatyczny dziekanatu”, o budżecie 200000 zł i

terminie zakończenia za pięć i pół roku od daty wstawienia krotki.

INSERT INTO projekty(nazwa, budzet) VALUES (‘System informatyczny

dziekanatu’,100000);

Polecenie to spowoduje wstawienie do relacji PROJEKTY projektu o nazwie

„System informatyczny dziekanatu”, o budżecie 100000 zł (podana

wartość „nadpisuje” wartość domyślną) i terminie zakończenia za pięć i pół roku

od daty wstawienia krotki.

Możliwe jest definiowanie ograniczenia integralnościowego za pomocą polecenia

CONSTRAINT. Ograniczenie integralnościowe jest pewną

regułą gwarantującą, że dane znajdujące się w relacji spełniają tę regułę.

CREATE TABLE nazwa_relacji

(nazwa_atrybutu typ (rozmiar) [DEFAULT wartość_domyślna]

[ [CONSTRAINT nazwa_ogr] ograniczenie_atr]...,

Wyróżnia się następujące ograniczenia integralnościowe:

◦ klucz podstawowy (primary key) -atrybut (lub zbiór atrybutów), którego

wartość jednoznacznie identyfikuje krotkę

◦ klucz obcy (foreign key) - atrybut (lub zbiór atrybutów), który wskazuje

na klucz podstawowy

◦ unikalność (unique) - przypomina swoim działaniem ograniczenie „klucz

podstawowy”. Żadna kombinacja wartości atrybutów wchodzących w

skład ograniczenia nie może wystąpić w relacji więcej niż raz.

◦ zawężenie domeny/dziedziny (check) - jest nim warunek logiczny

zdefiniowany na wartościach atrybutów krotki, który musi być zawsze

spełniony

◦ wartość pusta/niepusta (NULL/NOT NULL) - jest to wartość obowiązkowa

Możliwe jest również tworzenie relacji poprzez zapytanie

CREATE TABLE nazwa_relacji

(nazwa_atrybutu [DEFAULT wartość_domyślna]

[ [CONSTRAINT nazwa_ogr] ograniczenie_atr]...,

AS SELECT zapytanie;

UWAGI

W niektórych SZBD (systemach zarządzania bazą danych) wykonanie dowolnego

polecenia DDL (w tym i CREATE TABLE) wiąże się z

niejawnym wykonaniem zatwierdzenia wszystkich zmian (COMMIT). W

konsekwencji nie jest możliwe wycofanie poleceń DDL. Jest tak np. w

SZBD Oracle.

ALTER

Polecenie ALTER TABLE pozwala na modyfikowanie relacji. Dzięki niemu możliwe

jest dodawanie i usuwanie atrybutów i

ograniczeń integralnościowych oraz modyfikowanie definicji atrybutu.

ALTER TABLE nazwa_relacji

ADD [ nazwa typ(rozmiar) [DEFAULT wartość] ograniczenia |

CONSTRAINT nazwa typ ograniczenie ];

Polecenia ALTER TABLE służącą do dodawania nowych atrybutów lub ograniczeń

integralnościowych. Polecenie to rozpoczyna się od słów

kluczowych ALTER TABLE, po których podaje się nazwę relacji, słowo kluczowe

ADD i: albo definicję atrybutu (łącznie z ograniczeniami

atrybutu), albo definicję ograniczenia relacji.

ALTER TABLE nazwa_relacji

MODIFY ( nazwa typ(rozmiar) [DEFAULT wartość] ograniczenia );

Polecenie ALTER TABLE służącą do modyfikacji definicji atrybutu. Polecenie to

rozpoczyna się od słów kluczowych ALTER TABLE, po których

podaje się: nazwę relacji, słowo kluczowe MODIFY i nową definicję atrybutu.

Ważne jest tutaj, aby atrybut, którego nazwę podaje się w

definicji, znajdował się już w relacji.

ALTER TABLE nazwa_relacji

DROP [ COLUMN ( nazwa ) | CONSTRAINT ( nazwa ) ];

Polecenie ALTER TABLE służącą do usuwania atrybutu lub ograniczenia

integralnościowego. Polecenie to rozpoczyna się od słów kluczowych

ALTER TABLE, po których podaje się nazwę relacji, słowo kluczowe DROP i: albo

słowo kluczowe COLUMN i nazwa atrybutu, albo słowo

kluczowe CONSTRAINT i nazwa ograniczenia.

DROP i RENAME

Polecenia RENAME i DROP TABLE pozwalające odpowiednio na: zmianę nazwy

relacji i usunięcie relacji.

RENAME stara_nazwa TO nowa_nazwa;

Polecenie to rozpoczyna się od słowa kluczowego RENAME, następnie podaje się

nazwę modyfikowanej relacji, słowo kluczowe TO i nową

nazwę relacji.

DROP TABLE nazwa_relacji [CASCADE CONSTRAINTS];

Polecenie rozpoczyna się od słów kluczowych DROP TABLE, po których podaje się

nazwę usuwanej relacji i opcjonalnie słowa kluczowe

CASCADE CONSTRAINTS. CASCADE CONSTRAINTS oznacza, że usuwając relację

należy również usunąć również wszystkie ograniczenia „klucz

obcy”, które wskazują na atrybuty usuwanej relacji.

UWAGA

Do poleceń DDL zaliczamy również polecenia za pomocą których tworzymy

sekwencje, indeksy czy widoki.

Typy danych

Typ

Opis

INT(długość)

Pole liczbowe przechowujące liczby cłkowite z przedziału

-/+2.147E+9, ale może zostać ograniczone parametrem

długość. Parametr ten ogranicza liczbę przekazywanych cyfr

a nie wartość. Dla wartości z tego pola można

przeprowadzać operacje matematyczne

TINYINT(długość)

Pole liczbowe przechowujące wartości z przedziału -128 do

127 (dodanie parametru unsigned umożliwia

przechowywanie wartości z zakresu od 0 do 255)

SMALLINT(długość)

Pole liczbowe przechowujące wartości z przedziału -32 768

do 32 767 (dodanie parametru unsigned umożliwia

przechowywanie wartości z zakresu od 0 do 65 535)

MEDIUMINT(długość)

Pole liczbowe przechowujące wartości z przedziału - 8 388

608 do 8 388 607 (dodanie parametru unsigned umożliwia

przechowywanie wartości z zakresu od 0 do 16 777 215)

BIGINT(długość)

Pole liczbowe przechowujące wartości z przedziału

-/+9.223E+18 ( dodanie parametru unsigned umożliwia

przechowywanie wartości z zakresu od 0 do 18.45E+18)

FLOAT(długość,

miejsca_po_przecinku)

Typ zmiennoprzecinkowy przyjmujący wartości min=+/

-1.175E-38 i max=+/-3.403E+38. Parametr długość określa

liczbę wyświetlanych cyfr, a miejsca_po_przecinku liczbę

wyświetlanych cyfr po przecinku. Możliwe jest użycie

parametry unsigned

FLOAT(prezycja)

Równoważne z FLOAT(długość, miejsca_po_przeciwnku)

jednak parametry długość i miejsca po przecinku nie są

zdefiniowane. Parametr precyzji może wynosić od 0 do 24

dla pojedynczej precyzji (FLOAT) lub od 25 do 53 dla

podwójnej (DOUBLE)

DOUBLE(długość,

miejsca_po_przecinku)

Typ zmiennoprzecinkowy o średniej wielkości przyjmujący

wartości min=+/-2.225E-308 oraz max=+/-1.798E+308.

Parametr długość określa liczbę wyświetlanych cyfr, a

miejsca_po_przecinku liczbę wyświetlanych cyfr po

przecinku. Możliwe jest użycie parametry unsigned

DECIMAL(długość,

miejsca_po_przeciwnku)

Pole liczbowe przechowujące wartości ułamkowe. Parametr

długość określa liczbę wyświetlanych cyfr, a

miejsca_po_przecinku liczbę zapamiętywanych miejsc po

przecinku. Parametr długość nie bierze znaku pod uwagę.

BIT

Równoważny z TINYINT(1). Wartość 0 reprezentuje fałsz,

niezerowa prawdę.

TINYINT(długość)

Pole liczbowe przechowujące wartości od -128 do 127.

Parametr długość określa liczbę wyświetlanych cyfr. Możliwe

jest użycie parametry unsigned

CHAR(długość)

W polu można przechowywać dowolny znak, ale samo pole

ma ścisłą długość. Parametr długość może mieć wartości z

przedziału od 0 do 255

VARCHAR(długość)

Dowolny znak oraz dane z przedziału od 0 do 255

TINYTEXT

Umożliwia przechowywanie do 255 znaków

TEXT

Dowolny znak o max długości 65 535

MEDIUMTEXT

Umożliwia przechowywanie do ponad 16,5 miliona znaków

LONGTEXT

Umożliwia przechowywanie do 4 miliardów znaków

BINARY(długość)

Binarny odpowiednik CHAR. Przechowuje 0 do 255 danych

binarnych

VARBINARY(długość)

Binarny odpowiednik VARCHAR. Przechowuje 0 do 65 535

danych binarnych

TINYBLOB

Równoważne z TINYTEXT, ale uwzględnia wielkość liter przy

sortowaniu i porównywaniu

BLOB

Równoważne z TEXT, ale uwzględnia wielkość liter przy

sortowaniu i porównywaniu

MEDIUMBLOB

Równoważne z MEDIUMTEXT, ale uwzględnia wielkość liter

przy sortowaniu i porównywaniu

LONGBLOB

Równoważne z LONGTEXT, ale uwzględnia wielkość liter przy

sortowaniu i porównywaniu

ENUM("opcja1", "opcja2",

...)

Umożliwia przechowywanie w polu jedynie wartości

wcześniej wskazanych, na przykład "true", "false"

SET("opcja1", "opcja2",

...)

Podobnie jak ENUM, jednak ustawionych może być od zera

do więcej opcji

DATE

Przechowuje datę w formacie RRRR-MM-DD

DATETIME

Przechowuje datę i czas w formacie RRRR_MM_DD

GG:MM:SS

TIME

Przechowuje czas w formacie GG:MM:SS

TIMESTAMP

Jak DATETIME, przydatne przy automatycznym

aktualizowaniu pola

YEAR(długość)

Przechowuje rok z formacie czterocyfrowym od roku 1900 do

2155

5. Zależności funkcyjne. Aksjomaty Armstronga. Integralność danych (klucze,

klucze obce). Opcje propagacji.

Zależności funkcyjne

Definicja

Jeśli A1,..., An oraz B1,...,Bm są zbiorami atrybutów, to mówimy, że A1,...,An

określają funkcyjnie B1,...,Bm (zachodzi

zależność funkcyjna między A1,...,An a B1,...,Bm):

(A1,...,An) -> (B1,...,Bm)

jeśli KAŻDE dwie krotki, które mają takie same wartości dla atrybutów A1,...,An

mają również takie same wartości dla atrybutów B1,...,Bm.

Zbiór atrybutów A1,..., An nazywamy wyznacznikiem (determinantem) zależności

funkcyjnej.

Przykład

Zależności funkcyjnych dla relacji PersonelBiuro:

pracNr -> nazwisko, stanowisko, pensja, biuroNr, adres

biuroNr -> adres

adres -> biuroNr

biuroNr, stanowisko -> pensja

adres, stanowisko -> pensja

Aksjomaty Armstronga

Definicja

Aksjomaty Armstronga – pozwalają znaleźć nowe zależności funkcyjne na

podstawie już znalezionych

Istnieją trzy główne rodzaje aksjomatów

Rodzaj

Opis

zwrotność

Jeśli B jest podzbiorem A,

to A -> B

rozszerzenie

Jeśli A -> B,

to A,C -> B, C

przechodniość

Jeśli A -> B i B -> C,

to A -> C

Pozostałe reguły

Rodzaj

Opis

samookreślenie

A -> A

rozkład

Jeśli A -> B,C,

to A -> B i A -> C

suma

Jeśli A -> B i A -> C,

to A -> B,C

złożenie

Jeśli A -> B i C -> D,

to A,C -> B,D

NIEPRAWDA, że

jeśli A -> B, to B -> A
jeśli A, B -> C, to A -> C
jeśli A -> B, to A -> B, C

Integralność danych

Definicja

Mówimy, że atrybut lub zbiór atrybutów { A1,...,An } tworzy KLUCZ RELACJI,

jeśli:

1. Wszystkie atrybuty relacji są funkcyjnie zależne od tych atrybutów

2. Żaden właściwy podzbiór zbioru { A1,...,An } nie ma własności 1.

W szczególności:

z 1. wynika, że żadne dwie krotki nie mogą być zgodne dla wszystkich atrybutów

A1,...,An

z 2. wynika, że klucz jest MINIMALNYM zbiorem o własności 1.

Zbiór atrybutów, który zawiera klucz nazywamy NADKLUCZEM.

Klucz obcy - atrybut, który odwołuje się do klucza głównego innej relacji.

Przykład - zadanie z egzaminu

Rozważmy relację R(A,B,C,D,E,F,G,H) z następującymi zależnościami

funkcyjnymi:

A

→B,C,D ; A,D →E ; E,F,G → H ; F → G,H .

a) Wyznacz klucz główny relacji R (odpowiedź uzasadnij):

Do klucza muszą należeć atrybuty A i F, gdyż nie można ich wywnioskować z

żadnej zależności funkcyjnej. Sprawdzamy, czy {A,F} jest

kluczem kandydującym:

{A,F} *= {A,F,B,C,D,E,G,H}, bo

F

→ G,H

A

→ B,C,D

Przechodniość:

A,D

→ E

Zatem {A,F} jest kluczem kandydującym.

Ponieważ zarówno A, jak i F muszą należeć do klucza {A,F} jest kluczem

minimalnym, więc wybieramy go na nasz klucz główny dla relacji

R.

b) Którą z powyższych zależności można usunąć tak, aby nie zmienić tego

klucza?

Można usunąć zależność trzecią, czyli: E,F,G

→ H.

Przykład

Relacja ma schemat R(A, B, C, D) oraz zależności A, B -> C, C -> D i D -> A.

a) Określić wszystkie klucze R.

b) Określić wszystkie nadklucze w R, które nie są kluczami.

Klucze:

B musi być w kluczu, więć

K1 = {A,B},

K2 = {B,C},

K3 = {B,D},

Nadklucze: nadzbiory K1, K2, K3, czyli

{A,B,C}, {A,B,D}, {B,C,D}, {A,B,C,D}

Opcje propagacji -

SPRAWDZIĆ !!!

Używane w czasie tworzenia kluczy obcych mozemy uzywac pewnych

dodatkowych opcji. Wyróżniamy następujące opcje

propagacji:

◦ CASCADE,

◦ SET NULL,

◦ NO ACTION

CASCADE oznacza, że modyfikacja lub usunięcie powinno dotyczyć rekordów

zależnych

SET NULL oznacza, że w rekordzie zależnym ustawi się wartość NULL

NO ACTION oznacza, że serwer odrzuci nasze zapytanie (ponieważ złamało

ograniczenie)

6. Postacie normalne: 1NF, 2NF, 3NF, BCNF. Normalizacja.

6.1 1NF

Definicja

Każda relacja spełniająca warunki definiujące relację jest w pierwszej postaci

normalnej. Czyli relacja jest w 1NF jeżeli wartości atrybutów są atomowe

(niepodzielne).

Przykład

Przed 1NF

Płeć

Imię

Żeńska

Anna, Eliza

Męska

Jan, Piotr

Reclacja w 1NF

Płeć

Imię

Żeńska Anna
Żeńska Eliza
Męska Jan
Męska Piotr

Własności

◦ Pierwsza postać normalna zabrania definiowania złożonych atrybutów,

które są wielowartościowe

◦ Relacje, które dopuszczają definiowanie złożonych atrybutów nazywamy

relacjami zagnieżdżonymi (ang. nested relations)

◦ W relacjach zagnieżdżonych każda krotka może zawierać inną

relację Pracownicy (idPrac, Nazwisko, {Projekty (nr, godziny)})

Normalizacja do 1NF

◦ dana jest relacja R, zawierająca inną relację P

◦ dekompozycja relacji R do zbioru relacji w 1NF:

▪ utwórz osobną relację dla relacji zewnętrznej

▪ utwórz osobną relację dla relacji wewnętrznej (zagnieżdżonej),

do której dodaj klucz relacji zewnętrznej

▪ kluczem nowej relacji wewnętrznej (klucz relacji wewnętrznej +

klucz relacji zewnętrznej)

6.2 2NF

Definicja

Relacja jest w drugiej postaci normalnej, jeśli każdy niekluczowy atrybut jest

funkcyjnie zależny od CAŁEGO klucza głównego.

Przykład

Schemat relacji uczestnictwo nie jest w 2NF, gdyż istnieją atrybuty wtórne

(Nazwisko, NazwaProj, Lokalizacja), które są częściowo zależne od klucza.

IdPrac

NrProj

Funkcja

Nazwisko

NazwaProj Lokalizacja

Mamy następujące zależności funkcyjne:

fd1: {IdPrac, NrProj}

→ Funkcja

fd2: {IdPrac, NrProj}

→ Nazwisko

fd3: {IdPrac, NrProj}

→ NazwaProj

fd4: {IdPrac, NrProj}

→ Lokalizacja

fd5: {IdPrac}

→ Nazwisko

fd6: {NrProj}

→ NazwaProj

fd7: {NrProj}

→ Lokalizacja

Relacja w 2NF

IdPrac

NrProj

Funkcja

fd1: {IdPrac, NrProj}

→ Funkcja

IdPrac

Nazwisko

fd5: {IdPrac}

→ Nazwisko

NrProj

NazwaProj Lokalizacja

fd6: {NrProj}

→ NazwaProj

fd7: {NrProj}

→ Lokalizacja

6.3 3NF

Definicja

Relacja jest w trzeciej postaci normalnej, jeśli jest w drugiej postaci normalnej i

każdy niekluczowy atrybut jest bezpośrednio (a nie przechodnio) zależny od

klucza głównego.

Przykład

Występuje zjawisko anomalii wstawiania – do relacji Pracownicy nie można

wstawić informacji o nowoutworzonym na Wydziale Elektrycznym

Sterowania, tak długo jak długo nie zostanie zatrudniony pierwszy pracownik w

tym instytucie.

Występuje zjawisko redundancji (nadmiarowość).

Występuje zjawisko anomalii usuwania - usuwając kolejno pracowników z

Instytutu ElektroEne usuniemy informacje o przypisaniu Instytutu

ElektroEnei do Wydziału Elektrycznego.

Przed 3NF

Pracownicy

Nazwisko

Instytut

Wydział

Kowalski

Informatyki Elektryczny

Nowak

Informatyki Elektryczny

...

...

...

Domek

ElektroEne Elektryczny

Zapora

ElektroEne Elektryczny

Relacje w 2NF - tworzymy dwie nowe relacje. Dekompozycja ta rozwiązuje problem

anomali wstawiania, usuwania jak i aktualizacji.

Pracownicy1

Nazwisko

Instytut

Kowalski

Informatyki

Nowak

Informatyki

...

...

Domek

ElektroEne

Zapora

ElektroEne

Pracownicy2

Instytut

Wydział

Informatyki Elektryczny

...

Elektryczny

Elektroniki Elektryczny

6.4 BCNF

Definicja

Relacja jest w postaci normalnej Boyce'a-Codda, jeśli każdy determinant jest

kluczem kandydującym.

6.5 Normalizacja

Proces normalizacji relacji można traktować jako proces, podczas którego

schematy relacji posiadające pewne niepożądane cechy są

dekomponowane na mniejsze schematy relacji o pożądanych własnościach.

Własności

◦ własność zachowania atrybutów - żaden atrybut nie zostanie zagubiony

w trakcie procesu normalizacji

◦ własność zachowania informacji - dekompozycja relacji nie prowadzi do

utraty informacji

◦ własność zachowania zależności - wszystkie zależności funkcyjne są

reprezentowane w pojedynczych schematach relacji

7. Indeksy. Widoki. Procedury składowane. Wyzwalacze.

7.1 Indeksy

Definicja

Indeksy

Tworzenie

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX

index_name

[index_type]

ON tbl_name (index_col_name,...)

[index_type]

gdzie:

index_col_name:

col_name [(length)] [ASC | DESC] – nazwa kolumny, podaje sie wielkość oraz

czy dane maja być podane w porządku rosnącym, bądź w porządku malejącym

index_type:

USING {BTREE | HASH | RTREE} – nazwa struktury przez jaka indeks będzie

reprezentowany w bazie

Usuwanie

DROP INDEX index_name

Jeśli mamy strukture drzewa B-Tree to:

◦ każdy węzeł ma <= m potomków

◦ każdy węzeł (z wyjątkiem korzenia i liści) ma >= m/2

potomków

◦ korzeń ma co najmniej 2 potomków

◦ wszystkie liście występują na tym samym poziomie

◦ każdy węzeł, który nie jest liściem i ma k potomków

zawiera k-1 kluczy.

Zastosowanie

- szybkie wyszukiwanie krotek spełniających warunek

WHERE

- szybkie wyszukiwanie krotek w przypadku złączeń

(JOIN)

- szybkie wyszukiwanie wartości MIN i MAX

- sortowanie ORDER BY

- grupowanie GROUP BY

Wady

Indeksy muszą być aktualizowane, gdy modyfikowane są tabele co jest

czasochłonne

7.2 Widoki (perspektywy)

Definicja

Widok (perspektywa) to wirtualna relacja, która nie musi fizycznie istnieć w

bazie danych, ale może być WYLICZONA w każdej chwili na żądanie

użytkownika. Widoki definiuje się jako zapytanie dotyczące jednej lub wielu

tabel bazowych lub innych widoków.

Tworzenie

Perspektywa pozioma

CREATE VIEW view_name

AS

SELECT * FROM table_name

WHERE condition;

Perspektywa pionowa

CREATE VIEW view_name

AS

SELECT columna_name, columna_name...

FROM table_name;

Perspektywa oparta na grupach i złączeniach

CREATE VIEW view_name

AS

SELECT column_name, column_name...

FROM table_name LEFT JOIN table_name USING (table_collumn)

GROUP BY table_column;

Usuwanie

DROP VIEW view_name;

Aktualizowanie

UPDATE view_name SET column_name=value WHERE column_name=value

Perspektywa jest modyfikowalna, gdy:

◦ nie występuje w niej opcja DISTINCT, czyli z wyniku

zapytania nie są usuwane powtórzenia wierszy

◦ każdy element na liście SELECT jest nazwą kolumny,

a nie wyrażeniem bądź funkcją agregującą

◦ w klauzuli FROM występuje tylko jedna tabela

◦ w definicji nie ma podzapytań

◦ nie ma grupowań

◦ każdy wiersz dodany przez perspektywę musi

spełniać więzy integralności tabeli bazowej (NOT NULL, UNIQUE)

Zalety

◦ niezależność danych

◦ wielodostęp

◦ poprawa bezpieczeństwa

◦ uproszczenie zapytań

◦ wygoda

◦ dostosowania do użytkownika

◦ integralność danych

Wady

◦ ograniczona możliwość modyfikacji

◦ ograniczenia struktury

◦ wydajność

7.3 Procedury składowane

Definicja

Procedury są obiektami (wyrażeniami) związanymi z bieżącą bazą danych.

Przechowywane na serwerze bazodanowym. Moga byc definiowane z

parametrami wejsciowymi i wyjsciowymi.

Tworzenie

CREATE PROCEDURE / CREATE FUNCTION

CREATE PROCEDURE EmpCnt (OUT cnt INT)

BEGIN

SELECT COUNT(*) INTO column_name FROM table_name;

END;

Wywołanie

CALL procedure_name

Uusuwanie

DROP PROCEDURA procedure_name

Zastosowanie

◦ uporządkowanie/centralizacja operacji na bazie danych

◦ wprowadzanie reguł bezpieczeństwa (klient ma prawo wykonać

procedurę, a nie na wykonać dowolne zapytanie)

◦ zmniejszenie liczby interakcji z bazą danych

Wady

◦ zmiana procedury wpływa natychmiast na działanie wszystkich

elementów systemu (w bazie i poza nia), które z danej procedury

korzystają

◦ nie są zbyt wydajne

7.4 Wyzwalacze (triggery)

Definicja

Wyzwalacz służy do zdefiniowania akcji, która powinna być podjęta, gdy w bazie

danych wystąpi określone zdarzenie. Są skojarzone z konkretną tabelą bazy

danych mogą być aktywowane przez polecenia: INSERT, DELETE, UPDATE, oraz

mogą być wywołane przed wykonaniem polecenia

(BEFORE) lub po wywołaniu (AFTER).

Tworzenie

CREATE

[DEFINER = { user | CURRENT_USER }]

TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt

trigger_time:

BEFORE / AFTER

trigger_event:

CREATE

[DEFINER = { user | CURRENT_USER }]

TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt

trigger_time:

BEFORE / AFTER

trigger_event:

trigger_time:

BEFORE / AFTER

trigger_event:

INSERT – wstawianie nowego wiersza (INSERT, LOAD

DATA, REPLACE)

UPDATE – modyfikacja istniejącego wiersza (UPDATE)

DELETE – usuwanie wiersza (DELETE, REPLACE)

Polecenie REPLACE działa tak samo jak INSERT, z tą różnicą, że jeśli

wstawiana krotka ma taką samą wartość klucza głównego, co

istniejąca w tabeli, to zostaje zmieniona.

Polecenie REPLACE działa tak samo jak INSERT, z tą różnicą, że jeśli

wstawiana krotka ma taką samą wartość klucza głównego, co

istniejąca w tabeli, to zostaje zmieniona.

Usuwanie

DROP TRIGGER trigger_name

Zastosowanie

◦ zapewnienie integralności referencyjnej

◦ implementacja skomplikowanych więzów ogólnych

◦ kontrola modyfikacji danych

Zasady obsługi błędów

Jeżeli wyzwalacz BEFORE nie wykona się poprawnie, operacja wywołująca ten

wyzwalacz nie jest wykonywana. Wyzwalacz AFTER jest wykonywany tylko, jeśli

wyzwalacz BEFORE (o ile istnieje) i sama instrukcja wywołująca zostały

wykonane poprawnie.

8. Modelowanie danych: diagramy związków encji (E/R).

8.1 Modelowanie

Modelowanie - odwzorowanie rzeczywistych obiektów świata rzeczywistego w

systemie informatycznym (bazie danych)

Wyróżniamy modele

▪ konceptualne - reprezentacja obiektów w uniwersalnym modelu

niezależnym od modelu implementacyjnego

▪ model związków-encji

▪ model UML

▪ implementacyjne

▪ modele wykorzystywane do implementacji modeli

konceptualnych

▪ modele danych (relacyjne, obiektowe, itp.)

Model związków-encji

Model związków-encji (entity-relationship model - ER)

▪ obiekty świata rzeczywistego reprezentowane za pomocą encji

(entities), która:

▪ reprezentuje zbiór obiektów opisany tymi samymi

cechami (atrybutami, własnościami)

▪ informacje o tych obiektach będą przechowywane w

bazie danych

▪ konkretny obiekt świata rzeczywistego jest

reprezentowanyjako wystąpienie encji (instancję encji)

▪ powiązania między obiektami świata rzeczywistego

reprezentowane za pomocą związków (relationships) pomiędzy

encjami

Własności modelowania encji

▪ Każda encja posiada

▪ unikalną nazwę

▪ zbiór cech (atrybutów)

▪ Encje wchodzą w związki z innymi encjami

▪ wyjątkiem są encje reprezentujące dane słownikowe i

konfiguracyjne

▪ Dowolna rzecz lub obiekt może być reprezentowana tylko przez

jedną encję

▪ Nazwa encji powinna być rzeczownikiem w liczbie pojedynczej

8.2 Atrybuty encji

Wyróżnia się dwa rodzaje atrybutów, tj. identyfikatory i deskryptory.

▪ Identyfikator

▪ atrybut lub zbiór atrybutów jednoznacznie identyfikujący

wystąpienie encji

▪ zbiór atrybutów + związki

▪ związki

▪ Identyfikatory naturalne

I

▪ PESEL, NIP, nr dowodu, nr paszportu

▪ Identyfikatory sztuczne

▪ numer pozycji katalogowej, identyfikator pracownika

▪ Deskryptory (atrybuty deskrypcyjne)

▪ wszystkie inne atrybuty poza identyfikatorami

▪ reprezentują podstawowe cechy/własności encji

▪ cechy te będą przechowywane w bazie danych

▪ atrybuty z wartościami opcjonalnymi

▪ atrybuty z wartościami obowiązkowymi

Definicja atrybutu encji

Definicja pojedynczego atrybuty encji obejmują:

- Nazwa

- Dziedzina(typ danych i maksymalny rozmiar, zbiór dozwolonych

wartości, zakres dozwolonych wartości)

- Dozwolone / niedozwolone wartości puste

- Opcjonalnie unikalność wartości

Przykład

Pracownicy firmy są opisani numerem PESEL, adresem zamieszkania,

pensją i opcjonalnie numerem telefonu

8.3 Związki

Związek (asocjacja) reprezentuje powiązania pomiędzyobiektami świata

rzeczywistego

◦ klienci posiadają rachunki bankowe

◦ studenci otrzymują oceny z egzaminów

W modelu ER związek łączy encje

Związek z każdego końca posiada krótki opis ułatwiający interpretację związku

Cechy związku

Stopien związku

▪ unarny

▪ binarny

▪ ternarny

▪ n-arny

Typ kardynalności

▪ jeden-do-jeden (1:1)

▪ jeden-do-wiele (1:n)

▪ wiele-do-wiele (n:n)

Istnienie

▪ opcjonalny

▪ obowiązkowy

Na diagramie opcjonalność związku jest oznaczana linią przerywaną, a

obowiązkowość - liną ciągłą. Jeżeli związek jest typu N:N to

wyprowadzamy dodatkowa relację pośrednią.

9. Transakcje. Własności ACID. Poziomy izolacji.

9.1. Transakcje

Definicja

Transakcja - atomowa/niepodzielna jednostka pracy składająca się z jednej lub

kilku instrukcji SQL

Własność

ALBO zostaną zatwierdzone wszystkie instrukcje transakcji ALBO transakcja

jako całość zostanie cofnięta a baza powróci do stanu sprzed wykonania

transakcji

Polecenia dotyczące transakcji

COMMIT - zatwierdza transakcje

ROLLBACK - cofa transakcje

SET AUTOCOMMIT = 1 (standard w MySQL) każde polecenie stanowi

pojedynczą transakcję, która automatycznie jest zatwierdzana

SAVEPOINT - Punkty powrotu, pozwalające na częściowe odtworzenie stanu

bazy danych.

Działania kończące transakcje:

1. Akceptacja zmian z użyciem COMMIT

2. Odrzucenie zmian za pomocą ROLLBACK

3. Wykonanie jakiegoś polecenia DDL (CREATE TABLE, DROP TABLE itp.)

4. Wyjście z SQL*Plus

5. Utrata połączenia z bazą danych

6. Wykonanie polecenia SET AUTOCOMMIT On

9.2.Własności ACID

◦ Atomicity

- atomowość

▪ transakcja jest niepodzielną jednostką, co oznacza, że musi być

wykonana w całości lub wcale

◦ Consistency - spójność

▪ transakcja musi przekształcać bazę danych z jednego stanu

spójnego w inny stan spójny (kontrola więzów, poprawna praca

aplikacji – kontrola logiki aplikacji)

◦ Isolation - niezależność

▪ transakcje są wykonywane niezależnie od siebie (częściowe

skutki będącej w trakcie wykonywania transakcji nie powinny być

widoczne dla innych transakcji)

◦ Durability - trwałość

▪ rezultaty zakończonej z powodzeniem (wypełnionej) transakcji są

na trwale zapisane w bazie danych i nie mogą zostać utracone w

wyniku późniejszej awarii

9.3. Poziomy izolacji w MySQL

Problem utraty zmian

Wynik całkowicie poprawnie zakończonej operacji modyfikacji dokonanej przez

jednego użytkownika może zostać przysłonięty przez wynik modyfikacji

wykonanej przez innego użytkownika

Utracie zmian wprowadzonych przez T2 zapobiega się, zakazując odczytu

wartości konto przez T1, do czasu, aż zostanie zakończona modyfikacja

przeprowadzana przez T2.

Problem niezatwierdzonych zależności (problem odczytu niepewnych

lub „brudnych” danych)

Jedna transakcja może widzieć pośrednie wyniki tworzone przez inną

transakcję, zanim ta druga transakcja zostanie wypełniona.

Wartość konto odczytana przez T3 – „brudne” dane

Błędne założenie – T4 zakończy się poprawnie

Można zapobiec temu błędowi, zakazując odczytu wartości konto przez

transakcję T3 do czasu zakończenia bądź odrzucenia transakcji T4.

Problem analizy niespójności

Transakcja odczytuje kilka wartości z bazy danych a inna transakcja w tym

czasie modyfikuje niektóre z tych wartości

Problemu można uniknąć, zabraniając czytania wartości A i C przez T6, dopóki

T5 nie zakończy modyfikacji.

Niepowtarzalne (mylące) odczyty:

transakcja T ponownie odczytuje wartość danej, którą czytała już wcześniej,

lecz w międzyczasie inna transakcja zmodyfikowała tę wartość (transakcja T

otrzymuje dwie różne wartości tej samej danej)

Odczyty fantomów:

transakcja T wykonuje zapytanie wyszukujące w relacji zbiór krotek

spełniających pewien warunek, a potem powtórnie oblicza wynik tego

zapytania, ale okazuje się, że nowy wynik zawiera dodatkowe krotki (fantomy),

które zostały dopisane w międzyczasie przez inną transakcję.

Poziomy izolacji w MySQL

brudne odczyty

niepowtarzalne

odczyty

odczyty fantomów

READ

UNCOMMITED

T

T

T

READ COMMITED

N

T

T

REPEATABLE READ N

N

T

SERIALIZABLE

N

N

N

set transaction isolation level [read uncommitted | read committed| repeatable

read | serializable];

InnoDB – standardowo REPEATABLE READ

10.Administrowanie użytkownikami bazy danych.

tworzenie konta nowego użytkownika

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'] [, user

[IDENTIFIED BY [PASSWORD] 'password']] ...

usuwanie konta użytkownika

DROP USER user;

nadawanie uprawnień

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]

ON {tbl_name | * | *.* | db_name.*}

TO user_name [IDENTIFIED BY [PASSWORD] 'password']

[, user_name [IDENTIFIED BY 'password'] ...]

[REQUIRE

NONE |

[{SSL| X509}]

[CIPHER cipher [AND]]

[ISSUER issuer [AND]]

[SUBJECT subject]]

[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |

MAX_UPDATES_PER_HOUR # |

MAX_CONNECTIONS_PER_HOUR #]]

uprawnienia poziomu użytkownika

CREATE

tworzenie tabel

DELETE

usuwanie wierszy

EXECUTE

wykonywanie procedur składowanych

INDEX

tworzenie indeksów

INSERT

wstawianie wierszy

LOCK TABLES

blokowanie tabel

SELECT

zapytanie SELECT

SHOW DATABASES

wykonanie polecenia SHOW

DATABASES

UPDATE

aktualizacja wierszy

USAGE

zalogowanie i nic więcej

ALL

wszystkie bez opcji udzielania

przywilejów

ALTER

zmiana struktury tabel

DROP

usuwanie tabel

FILE

ładowanie danych z plików

WITH GRANT OPTION

wszystkie z opcją udzielania

przywilejów

Przykłady:

grant all on *.* to fred; - dostęp do wszystkich baz danych i wszystkich tabel

grant all on employee.* to fred; - dostęp do bazy employee

grant select on department to fred; - dostęp do SELECT dla tabeli department

grant select (employeeID) on employee to fred; - dostęp do SELECT dla

kolumny employeeID tabeli employee

zmiana hasła:

set password for fred@localhost = password('newpassword');

odbieranie uprawnień

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]...]ON {tbl_name

| * | *.* | db_name.*}

FROM user_name [, user_name ...]

revoke all on employee.* from fred;