Bazy danych

Grzegorz Bancerek
Uniwersytet w Białymstoku, III rok, V semestr, informatyka, 2006

-- Utworzenie bazy danych i połączenie
CREATE DATABASE 'e:\firebird\test.fdb' USER 'sysdba' PASSWORD 'masterkey' PAGE_SIZE 8192;

-- Połączenie z baza danych
CONNECT 'e:\firebird\test.fdb' USER 'sysdba' PASSWORD 'masterkey';

-- Usunięcie bazy danych (po połączeniu) UWAGA: likwiduje bazę ze wszystkimi tebalami itd.
DROP DATABASE;

-- Utworzenie tabeli grafy
CREATE TABLE grafy (
  ID int not null primary key,
  nazwa varchar(20) not null unique,
  zakres int not null,
  opis varchar(100),
  check(zakres > 0)
);

-- Utworzenie tabeli wierzcholki
CREATE TABLE wierzcholki (
  graf_id int not null REFERENCES grafy,
  nr int not null,
  opis varchar(20),
  PRIMARY KEY(graf_id, nr)
);

-- Utworzenie tabeli krawedzie
CREATE TABLE krawedzie (
  graf_id int not null REFERENCES grafy,
--  nazwa varchar(10) not null, --> potem
  start int not null,
  koniec int not null,
--  waga int not null, -- będzie dodana później
  opis varchar(20) not null,
  FOREIGN KEY(graf_id, start) REFERENCES wierzcholki(graf_id, nr),
  FOREIGN KEY(graf_id, koniec) REFERENCES wierzcholki(graf_id, nr),
  PRIMARY KEY(graf_id, start, koniec)  --> do usuniecia potem
--  PRIMARY KEY(graf_id, nazwa) --> potem
);

-- Wyświetlenie listy tabel

SHOW TABLE;

-- Wyświetlenie deklaracji tabli grafy

SHOW TABLE grafy;

-- Utworzenie generatora i wyzwalacza, który nadaje nowy numer ID dla nowych grafów

CREATE GENERATOR grafy_gen;

SET GENERATOR grafy_gen TO 100;

SET TERM ^ ;
CREATE TRIGGER grafy_tri1 FOR grafy ACTIVE BEFORE INSERT
AS
BEGIN
  new.ID = gen_id(grafy_gen, 1);
end^
SET TERM ; ^

-- UWAGA: "SET TERM ^ ;" powoduje, że '^' jest traktowany przez ISQL jako ';'
--        a ';' nie jest sygnałem wysyłającym zapytanie.

-- Utworzenie wyzwalacza, który nie pozwala na zmianę ID

SET TERM ^ ;
CREATE TRIGGER grafy_tri2 FOR grafy ACTIVE BEFORE UPDATE
AS
BEGIN
  IF (new.ID <> old.ID) THEN new.ID = old.ID;
end^
SET TERM ; ^

-- Utworzenie wyzwalacza zamieniającego zbyt mały zakres

SET TERM ^ ;
CREATE TRIGGER grafy_tri3 FOR grafy ACTIVE BEFORE INSERT OR UPDATE
AS
BEGIN
  IF (new.zakres < 10) THEN new.zakres = 10;
end^
SET TERM ; ^

-- Utworzenie wyzwalacza zamieniającego zbyt duży wierzchołek

SET TERM ^ ;
CREATE TRIGGER wierzcholki_tri1 FOR wierzcholki ACTIVE BEFORE INSERT OR UPDATE
AS
DECLARE VARIABLE z int;
BEGIN
  FOR SELECT zakres FROM grafy INTO :z
  DO
  IF (new.nr > :z) then new.nr = :z;
end^
SET TERM ; ^

-- Wstawienie grafu G1

INSERT INTO grafy (nazwa, zakres, opis) VALUES ('G1', 5, 'prosty graf');

-- Wyświetlenie zawartości tabeli grafy

SELECT * FROM grafy;

-- Wstawienie wierzchołków 1,2,3,4,5 do grafu G1

INSERT INTO wierzcholki (graf_id, nr, opis)
SELECT ID, 1, 'pierwszy' FROM grafy WHERE nazwa = 'G1';

-- Wstawienie krawędzi 1->2, 2->3, 2->4, 3->4, 3->1, 4->5, 1->5 do grafu G1

INSERT INTO krawedzie (graf_id, start, koniec, opis)
SELECT ID, 1, 2, 'a' FROM grafy WHERE nazwa = 'G1';

-- Wyświetlenie dróg o długości 2

SELECT a1.start AS X0, a1.koniec AS X1, a2.koniec AS X2
FROM krawedzie a1, krawedzie a2
WHERE a1.graf_id = a2.graf_id and a1.koniec = a2.start;

-- Usunięcie  PRIMARY KEY(graf_id, start, koniec) z tabeli krawedzie

SHOW TABLE krawedzie;
   -- uzyskujemy nazwę wezła integralności, np. INTEG_7
ALTER TABLE krawedzie DROP CONSTRAINT "INTEG_7";

-- Dodanie kolumny 'nazwa' do tabeli krawedzie

ALTER TABLE krawedzie ADD nazwa varchar(10) NOT NULL;
UPDATE krawedzie SET nazwa = opis;  --> aby pozbyć sie NULL'ów

-- Utworzenie  PRIMARY KEY(graf_id, nazwa) w tabeli krawedzie

ALTER TABLE krawedzie ADD PRIMARY KEY(graf_id, nazwa);

-- Utworzenie procedury wyliczającej drogi określonej długości

SET TERM ^ ;
CREATE PROCEDURE path(g int, n int) RETURNS (s int, k int) AS
BEGIN
  IF (n <= 0) THEN BEGIN
    FOR SELECT nr FROM wierzcholki WHERE graf_id = :g INTO :s DO
    BEGIN
      k = :s;
      SUSPEND;
    END
  END
  ELSE
    IF (n = 1) THEN BEGIN
      FOR SELECT start, koniec FROM krawedzie
          WHERE graf_id = :g
          INTO :s, :k
      DO SUSPEND;
    END
    ELSE BEGIN
      FOR SELECT a1.s, a2.koniec FROM path(:g,:n-1) a1, krawedzie a2
          WHERE a1.k = a2.start AND a2.graf_id = :g
          INTO :s, :k
      DO SUSPEND;
    END
END^
SET TERM ; ^

-- Wszystkie drogi długości 5

SELECT DISTINCT grafy.nazwa, p.s, p.k FROM grafy, path(1,5) p WHERE grafy.ID = 1;

-- Tabela drogi

CREATE TABLE drogi (
  graf_id int REFERENCES grafy,
  dlugosc int not null
);

-- Dodanie kolumny ID do wierzcholkow
-- Dodanie kolumny ID do krawedzi
Bazy danych

Grzegorz Bancerek Uniwersytet w Białymstoku, III rok, V semestr, informatyka, 2006

Grzegorz Bancerek
Uniwersytet w Białymstoku, III rok, V semestr, informatyka, 2006
