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 – 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ę