Aspekty aktywne baz danych

Aspekty aktywne baz danych
Aktywne aspekty baz danych
• Baza danych powinna zapewniać pewne
własności i niezmienniki;
• Własności te powinny mogą być zapisane
do bazy danych, a baza danych powinna
zapewniać sama, żeby były spełnione;
• Baza danych powinna się sama bronić
przed wprowadzeniem błędnych danych.
Spójność bazy danych
• Spójność oznacza poprawność, niesprzeczność danych
w bazie danych. Standard SQL zawiera środki do
definiowania warunków spójności (lub więzów
integralności), np. w obrębie zdania CREATE TABLE.
• Każda próba naruszenia zdefiniowanych warunków
spójności (podczas modyfikacji bazy danych) zostaje
udaremniona, operacja ją podejmująca jest odrzucana, a
baza danych pozostaje niezmieniona.
• W systemie pamiętana jest więc baza danych (dane
podstawowe) oraz wiedza o bazie danych (dane
opisujące struktury i warunki spójności).
Rodzaje warunków spójności
•
•
•
•
•
•
Typ kolumny – typ może być standardowy lub pochodny zdefiniowany
przez użytkownika.
UNIQUE – dla określenia, że kolumna lub zestaw kolumn ma mieć unikalną
wartość w tabeli (jest kluczem potencjalnym - alternatywnym, kandydującym
– w tabeli), taki klucz może przyjmować wartości NULL.
PRIMARY KEY – specjalny przypadek UNIQUE dla zdefiniowania klucza
głównego – nie może przyjmować wartości NULL.
FOREIGN KEY - dla zdefiniowania zależności referencyjnych (zależności
odniesień). Wartość klucza obcego musi występować jako wartość klucza
głównego w powoływanej tabeli.
CHECK - dla określenia, że kolumna lub zestaw kolumn (z jednej krotki)
mają mieć wartości spełniające określony warunek. Taka spójność może
być zdefiniowane jako reguła w wyrażeniu CREATE RULE.
Wyzwalacze - bardziej złożone warunki spójności można definiować za
pomocą procedur wyzwalanych w wyrażeniach CREATE TRIGGER.
Procedury te są automatycznie wyzwalane przy aktualizacji tabeli.
Typy danych w SQL Server
• char(n), nchar(n) – tekst o ustalonej długości n znaków;
• varchar(n), nvarchar(n) – tekst o długości co najwyżej n
znaków;
• binary(n) – binarny ciąg znaków o długości n znaków
• varbinary(n) - binarny ciąg znaków o długości co
najwyżej n znaków;
• bigint, int, smallint, tinyint – liczba całkowita;
• bit – przyjmuje wartość 0, 1, NULL;
• float (8 bytes), real(4 bytes) – liczby zmiennopozycyjne;
• decimal(n, d), numeric(n, d) – dane numeryczne: n
miejsc na liczby całkowite, d na ułamkowe;
• datetime, smalldatetime – typ daty;
Elementy aktywne
• Klucze (primery key,unique)
• Integralność referencyjną i klucze obce
(foreign key)
• Więzy wartości atrybutów
– Więzy NOT-NULL
– Więzy CHECK
– Więzy dziedziny
• Wyzwalacze (triggery)
Klucze
Klucze jednoznacznie identyfikują relację. Klucze mogą
być proste i złożone.
Relacja:
Osoba (Nazwisko, Imie, DataUr, NIP, PESEL, Adres)
•Klucz złożony: {Nazwisko, Imie, DataUr}
•Klucz prosty: {PESEL}, {NIP},
•Klucz sztuczny: {OsID}
Klucze kandydujące, jeden klucz główny
Klucz główny: powinien być prosty, wartości nie powinny zawierać białych
znaków, wartość klucza nie powinna się zmieniać, powinien być oparty o
wartość numeryczną.
Definiowanie schematu bazy danych
create table Osoba(
Nazwisko nchar(20),
Imie nchar(20),
DataUr datetime,
NIP nchar(20),
PESEL nchar(20))
Tworzenie tabeli z kluczami
create table Osoba(
Nazwisko nchar(20) primary key,
Imie nchar(20),
DataUr datetime,
NIP nchar(20) unique,
PESEL nchar(20) unique)
Atrybut Nazwisko jest kluczem głównym (primary key)
Atrybuty NIP, PESEL – posiadają wartości unikalne (unique)
Definiowanie tabeli ze złożonym
kluczem głównym
create table Osoba(
Nazwisko nchar(20),
Imie nchar(20),
DataUr datetime,
NIP nchar(20) unique,
PESEL nchar(20) unique,
primary key (Nazwisko, Imie))
Wartość (Nazwisko, Imie) jest kluczem głównym (primary key)
Atrybuty NIP, PESEL – posiadają wartości unikalne (unique)
Dodawanie kolumny z własnością
IDENTITY
ALTER TABLE Osoba ADD OsobaId INT IDENTITY ( 200, 2)
• ADD – dodawanie kolumny;
• IDENTITY [ (seed , increment) ]
– Seed – wartość pierwszego wiersza załadowanego do tabeli;
– Increment – inkrementacja wartości przy każdym następnym ładowaniu
danych.
• IDENTITY stosuje się przy definiowaniu kluczy sztucznych.
• Wykonanie następujących instrukcji spowoduje:
insert into Osoba values ('Padewski', 'Marek', '1971-01-06', 1111, 1234)
insert into Osoba values ('Nijaki', 'Przemyslaw', '1971-03-12', 2222, 8980)
Osoba
Definiowanie tabeli z kluczem
obcym
create table Studio(
Nazwa nchar(20) primary key,
Kierownik nchar(20) references dbo.Osoba(Nazwisko))
• Zależność pomiędzy kluczem głównym jednej
tabeli a atrybutem w drugiej tabeli nazywa się
zależnością referencyjną.
Ograniczenia zależności
referencyjnej
• Atrybuty klucza obcego muszą być
zadeklarowane jako klucz główny w oryginalnej
relacji.
– W tym przypadku Kierownik z relacji Studio odnosi się
do atrybutu Nazwisko z relacji Osoba, który jest
kluczem głównym.
Ograniczenia zależności
referencyjnej
Osoba
Studio
• Wartość atrybutu klucza obcego musi wystąpić
również jako wartość klucza głównego w drugiej
relacji.
– W tym przypadku każda wartość występująca w
kolumnie Kierownik musi wystąpić jak wartość
Nazwisko.
Usuwanie powiązanych krotek
Osoba
Studio
delete from Osoba where Nazwisko = 'Nijaki‘
Wykonanie instrukcji spowoduje błąd, gdyż w relacji
Studio byłaby taka wartość w kolumnie Kierownik, której
nie byłoby w atrybucie Nazwisko relacji Osoba.
Usuwanie powiązanych krotek
• Aby uniknąć tej sytuacji, można
zdefiniować relację w sposób następujący:
create table Studio(
Nazwa nchar(20) primary key,
Kierownik nchar(20) references
dbo.Osoba(Nazwisko) ON DELETE SET NULL)
Usuwanie powiązanych krotek
Osoba
Studio
delete from Osoba where Nazwisko = 'Nijaki‘
W wyniku operacji będzie:
Osoba
Studio
Usuwanie powiązanych krotek
• Można zdefiniować kaskadowe usuwanie
krotek:
create table Studio(
Nazwa nchar(20) primary key,
Kierownik nchar(20) references
dbo.Osoba(Nazwisko) ON DELETE CASCADE)
Usuwanie powiązanych krotek
Osoba
Studio
delete from Osoba where Nazwisko = 'Nijaki‘
Osoba
Studio
Modyfikowanie powiązanych krotek
• Można zdefiniować kaskadowe
modyfikowanie krotek:
create table Studio(
Nazwa nchar(20) primary key,
Kierownik nchar(20) references dbo.Osoba(Nazwisko)
ON DELETE CASCADE
ON UPDATE CASCADE)
Zastosowanie ON UPDATE CASCADE spowoduje, że zmiana nazwiska w
krotce Osoba, spowoduje zmianę w krotce Studio.
Modyfikowanie powiązanych krotek
Osoba
Studio
update Osoba
set Nazwisko = 'Madej'
where Nazwisko = 'Padewski'
Osoba
Studio
Ograniczenia na wartościach
atrybutów
ALTER TABLE Osoba
ADD Date smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES
Dodawana jest kolumna Date typu smalldate mogąca
przyjmować wartość NULL. Wartość domyślna jest
ustawiana na obecną datę (GETDATE()). Dodatkowo WITH
VALUES ustawia wartość domyślą we wszystkich
istniejących krotkach.
Ograniczenia CHECK
• CHECK <condition>
– condition – warunek definiowany podobnie jak
przy WHERE;
• Ograniczenia typu CHECK mogą być
nakładane na atrybuty.
• Zmiana wartości ograniczonego atrybutu
może nastąpić tylko wtedy, gdy warunek
jest spełniony.
Ograniczenia CHECK
• alter table Osoba add NIP1 int check (NIP1>100)
– Zostanie zaakceptowana tylko taka wartość atrybutu NIP1, która
jest większa od 100.
• alter table Osoba add Plec char(2)
• alter table Osoba Add constraint P1 check (Plec IN ('K',
'M'))
– Dodawanie ograniczenia na kolumnę Plec
• alter table Osoba Add constraint P2 check (PESEL>NIP)
– Sprawdzane są zawsze przy wstawianiu i modyfikowaniu krotki
relacji. Jeśli warunek jest fałszywy wówczas operacja nie
powiedzie się. Warunki nałożone są na krotki.
• Usuwanie ograniczeń:
– alter table Osoba drop constraint P1
Więzy dziedziny
W SQL3:
CREATE DOMAIN DziedzinaPlci CHAR(1) CHECK (VALUE IN
('K', 'M'))
– Można zdefiniować kolumnę o danej
dziedzinie. Np.:
Plec DziedzinaPlci
Asercje
• Wszystkie dotychczasowe więzy dotyczyły
pojedynczej krotki (więzy wartości
atrybutów), ewentualnie prostych
związków między krotkami z różnych
relacji (więzy integralności);
• Jeśli jest potrzeba dodania bardziej
skomplikowanych zależności pomiędzy
krotkami z różnych relacji, wówczas
można zastosować asercje.
Asercje
create assertion BogatyPrezes check
(NOT exists (select * from Studio, Osoba
where Studio.Nazwisko = Osoba.Nazwisko
and Kapital < 10000))
– Warunek oznacza, że nie powinien istnieć prezes, którego
kapitał jest mniejszy od 10000.
Sekwencje
CREATE SEQUENCE ID_Seq
START WITH 24329
INCREMENT BY 1
MINVALUE 24329
MAXVALUE 24332
CYCLE;
GO
SELECT NEXT VALUE FOR ID_Seq;
CREATE TABLE Sequence_Table
(Sequence_Table_id INTEGER DEFAULT NEXT VALUE FOR ID_Seq,
code VARCHAR(15) NOT NULL
);
Wyzwalacze (triggers)
• Reguły ECA (Event – Condition – Action)
• Asercje są drogie – system sam decyduje kiedy powinny
być wykonywane;
• Wyzwalacze są mechanizmami bardziej elastycznymi –
użytkownik sam decyduje kiedy mają być wywoływane;
• Wyzwalacze są testowane tylko przy zajściu
określonego zdarzenia (dołączanie, usuwanie,
modyfikacja krotki) określonego przez programistę
(projektanta bazy);
• Jeśli warunek zostanie spełniony to przetwarzana jest
akcja związania z wyzwalaczem.
Wyzwalacze - przykład
•
Dodajemy kolumnę Placa do tabeli Osoba, oraz kolumnę Kapitał w tabeli Studio.
• Reguła:
– Przy każdej zmianie płacy osoby. Jeśli płaca osoby się zwiększa i ta osoba jest
kierownikiem jakiegoś Studia, wówczas zwiększamy Kapitał o poprzednią płacę
kierownika
•
W standardzie SQL3 mamy następujący wyzwalacz:
CREATE TRIGGER TrgKapital
AFTER UPDATE OF Placa ON Osoba
REFERENCING
OLD AS Stara
NEW AS Nowa
WHEN (Stara.Placa < Nowa.Placa)
UPDATE Studio SET Kapital= Kapital + Stara.Placa
WHERE Kierownik = Nowa.Nazwisko
FOR EACH ROW
Wyzwalacze - przykład
• Reguła:
– Przy każdej zmianie płacy osoby. Jeśli płaca osoby się zwiększa i ta osoba jest
kierownikiem jakiegoś Studia, wówczas zwiększamy Kapitał o poprzednią płacę
kierownika
•
W T-SQL mamy następujący wyzwalacz
CREATE TRIGGER TrgKapital ON Osoba
AFTER UPDATE
AS
BEGIN
IF Update(Placa)
IF ((SELECT Placa FROM deleted) < (select Placa from
inserted))
UPDATE Studio
SET Kapital= Kapital + (select Placa from deleted)
WHERE Kierownik = (select Nazwisko from inserted)
END
Wyzwalacze - przykład
• Reguła:
– Przy każdej zmianie płacy osoby. Jeśli płaca osoby się zwiększa
i ta osoba jest kierownikiem jakiegoś Studia, wówczas
zwiększamy Kapitał o poprzednią płacę kierownika
• W MySQL mamy następujący wyzwalacz
CREATE TRIGGER UpStudio AFTER UPDATE ON Osoba
FOR EACH ROW BEGIN
If (OLD.Placa < NEW.Placa) THEN
UPDATE Studio
SET Kapital= Kapital + OLD.Placa
WHERE Kierownik = NEW.Nazwisko;
END IF;
END
Wyzwalacze
• W standardzie SQL3 akcja może być
wykonywana:
– przed zajściem zdarzenia: BEFORE (wówczas
warunek w WHEN jest sprawdzany przed zajściem
zdarzenia)
– po zajściu zdarzenia AFTER (wówczas warunek w
WHEN jest sprawdzany po zajściu zdarzenia) .
• Wyzwalacze mogą być nałożone na instrukcje
INSERT, UPDATE oraz DELETE.
• Jeśli wyzwalacz jest nałożony na UPDATE
wówczas występuje słowo OF, które specyfikuje
modyfikowaną kolumnę.
Wyzwalacze – przykład2
• Reguła:
– Przy każdym dodaniu osoby. Jeśli płaca osoby jest większa od średniego
kapitału studia, wówczas należy dodać nowe studio dla dodawanej osoby.
CREATE TRIGGER InsStudio ON Osoba
AFTER INSERT
AS
BEGIN
IF ((SELECT Placa FROM inserted) > (select avg(Kapital) from Studio))
BEGIN
declare @nazwisko varchar(20)
select @nazwisko = Nazwisko from inserted
INSERT INTO Studio values (@nazwisko, @nazwisko,1000)
END
END
Wyzwalacze – przykład2
• insert into Osoba values ('Koronowski', 'Andrzej', '1971-01-06', 1284,
5111, null, null, null, 9000)
• insert into Osoba values ('Maryniak', 'Krzysztof', '1971-01-06', 121,
51113, null, null, null, 50000)
Pierwszy INSERT nie spowoduje dodania nowej krotki w relacji Studio.
Drugi INSERT spowoduje dodania nowej krotki w Studio.
Wyzwalacze – przykład3
• Reguła:
– Przy każdym usuwaniu osoby. Jeśli płaca usuwanej osoby jest większa
od średniej płacy wszystkich osób, wówczas można tę osobę usunąć.
CREATE TRIGGER DelStudio ON Osoba
INSTEAD OF DELETE
AS
BEGIN
IF ((SELECT Placa FROM deleted) > (select avg(Placa) from Osoba))
delete from Osoba where Nazwisko = (select Nazwisko from deleted)
END
Wyzwalacze – przykład2
• delete from Osoba where Nazwisko = 'Koronowski'
• delete from Osoba where Nazwisko = 'Maryniak‘
Pierwszy DELETE nie spowoduje usunięcia krotki.
Drugi DELETE spowoduje usunięcie jednej krotki z tabeli Studio i
jednej z tabeli Osoba.
Dziękuję