Lab 8

backback

Link do IDroo: https://idroo.com/board-sVDidbHrLP

Modelowanie relacji

Przykładowy schemat Firmy i Pracowników z relacją 1-wiele i triggerem zapewniającym nieopcjonalność:

CREATE TABLE Firma (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nazwa VARCHAR2(40) NOT NULL UNIQUE,
  adres VARCHAR2(40) NOT NULL,
  email VARCHAR2(40)
);

BEGIN
	INSERT INTO Firma (nazwa, adres, email)
	  VALUES ('Contoso', 'Example Street 1', 'admin@contoso.com');
	INSERT INTO Firma (nazwa, adres, email)
	  VALUES ('Other', 'Other Street 1', 'admin@other.com');
	 
	COMMIT;
END;

SELECT * FROM Firma;

CREATE TABLE Pracownik (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  imie VARCHAR2(20) NOT NULL,
  nazwisko VARCHAR2(20) NOT NULL,
  pracodawca INTEGER NOT NULL REFERENCES Firma
); 

DECLARE 
    contoso_id INTEGER;
    other_id INTEGER;
BEGIN
	
	SELECT id INTO contoso_id FROM Firma  WHERE nazwa = 'Contoso';
	SELECT id INTO other_id FROM Firma  WHERE nazwa = 'Other' ;

	INSERT INTO Pracownik (imie, nazwisko, pracodawca)
	  VALUES ('Alice', 'Alison', contoso_id);
	INSERT INTO Pracownik (imie, nazwisko, pracodawca)
	  VALUES ('Bob', 'Bobinsky', other_id);

    COMMIT;
END;

CREATE OR REPLACE TRIGGER trg_Firma_Pracownik_notnull
AFTER DELETE OR UPDATE OF pracodawca ON Pracownik
DECLARE
    firma_id INTEGER;
BEGIN 
	SELECT f.id INTO firma_id
	  FROM Firma f
	  LEFT JOIN Pracownik p
	    ON f.id = p.pracodawca
	GROUP BY f.id
	HAVING COUNT(p.id) = 0
	FETCH NEXT 1 ROW ONLY;

    IF firma_id IS NOT NULL THEN
        raise_application_error(-20000, 'Firma ' || firma_id || ' musi miec przynajmniej jednego pracownika.');
    END IF;
END;

BEGIN
	DELETE (SELECT * FROM Pracownik p
      JOIN Firma f
        ON p.pracodawca = f.id
      WHERE f.nazwa = 'Contoso');
	
	ROLLBACK;
END;

Modelowanie dziedziczenia

Trzy podejścia:

  1. Table-per-type/Table-per-class
  2. Table-per-hierarchy
  3. “Table-per-concrete-type”

Hierarchia:

Dziedziczenie TPC


CREATE TABLE A (
  id INTEGER PRIMARY KEY,
  a_attr VARCHAR(20) NOT NULL
);

CREATE TABLE B ( --extends A
    id INTEGER REFERENCES A NOT NULL PRIMARY KEY,
    b_attr VARCHAR(20) NOT NULL
);

CREATE TABLE C ( --extends B
    id INTEGER REFERENCES B NOT NULL PRIMARY KEY,
    c_attr VARCHAR(20) NOT NULL
);

CREATE TABLE D ( --extends A
    id INTEGER REFERENCES A NOT NULL PRIMARY KEY,
    d_attr VARCHAR(20) NOT NULL
);

INSERT INTO A(id, a_attr) VALUES(1, 'b_a value');
INSERT INTO B(id, b_attr) VALUES(1, 'b_b value');

INSERT INTO A(id, a_attr) VALUES(2, 'c_a value');
INSERT INTO B(id, b_attr) VALUES(2, 'c_b value');
INSERT INTO C(id, c_attr) VALUES(2, 'c_c value');

INSERT INTO A(id, a_attr) VALUES(3, 'd_a value');
INSERT INTO D(id, d_attr) VALUES(3, 'd_d value');

CREATE VIEW B_full AS
  SELECT a.id, a.a_attr, b.b_attr
    FROM A a
    JOIN B b
      ON a.id = b.id;
  
CREATE VIEW C_full AS
  SELECT a.id, a.a_attr, b.b_attr, c.c_attr
    FROM A a
    JOIN B b
      ON a.id = b.id
    JOIN C c
      ON b.id = c.id;

CREATE VIEW D_full AS
  SELECT a.id, a.a_attr, d.d_attr
    FROM A a
    JOIN D d
      ON a.id = d.id;
      
CREATE VIEW A_hierarchy AS
  SELECT id, a_attr, b_attr, NULL AS c_attr, NULL AS d_attr, 'B' AS discriminator
    FROM B
  UNION ALL
  SELECT id, a_attr, b_attr, c_attr, NULL AS d_attr, 'C' AS discriminator
    FROM C
  UNION ALL
  SELECT id, a_attr, NULL AS b_attr, NULL AS c_attr, d_attr, 'D' AS discriminator
    FROM D;

Dziedziczenie TPH

CREATE TABLE A (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  a_attr VARCHAR(40),
  b_attr VARCHAR(40),
  c_attr VARCHAR(40),
  d_attr VARCHAR(40),
  discriminator VARCHAR(40) NOT NULL
);
   
ALTER TABLE A
ADD CONSTRAINT A_discriminator_check CHECK(discriminator IN ('B', 'C', 'D'));

INSERT INTO A (a_attr, b_attr, c_attr, d_attr, discriminator)
  VALUES ('b_a value', 'b_b value', NULL, NULL, 'B');
 
INSERT INTO A (a_attr, b_attr, c_attr, d_attr, discriminator)
  VALUES ('c_a value','c_b value', 'c_c value', NULL, 'C');
 
INSERT INTO A (a_attr, b_attr, c_attr, d_attr, discriminator)
  VALUES ('d_a value', NULL, NULL, 'd_d value', 'D');

SELECT * FROM A;

Dziedziczenie “TPCT”

CREATE TABLE B ( --extends A
    id INTEGER PRIMARY KEY,
    a_attr VARCHAR(20) NOT NULL,
    b_attr VARCHAR(20) NOT NULL
);

CREATE TABLE C ( --extends B
    id INTEGER PRIMARY KEY,
    a_attr VARCHAR(20) NOT NULL,
    b_attr VARCHAR(20) NOT NULL,
    c_attr VARCHAR(20) NOT NULL
);

CREATE TABLE D ( --extends A
    id INTEGER PRIMARY KEY,
    a_attr VARCHAR(20) NOT NULL,
    d_attr VARCHAR(20) NOT NULL
);

CREATE SEQUENCE A_pk_seq
  START WITH 1
  INCREMENT BY 1
  NOCYCLE;

CREATE OR REPLACE TRIGGER B_pk_seq_insert
BEFORE INSERT ON B
FOR EACH ROW
BEGIN
    SELECT A_pk_seq.nextval
      INTO :NEW.id FROM dual;
END;

CREATE OR REPLACE TRIGGER C_pk_seq_insert
BEFORE INSERT ON C
FOR EACH ROW
BEGIN
    SELECT A_pk_seq.nextval
      INTO :NEW.id FROM dual;
END;

CREATE OR REPLACE TRIGGER D_pk_seq_insert
BEFORE INSERT ON D
FOR EACH ROW
BEGIN
    SELECT A_pk_seq.nextval
      INTO :NEW.id FROM dual;
END;

INSERT INTO B (a_attr, b_attr)
  VALUES ('b_a value', 'b_b value');
 
INSERT INTO C (a_attr, b_attr, c_attr)
  VALUES ('c_a value', 'c_b value', 'c_c value');
 
INSERT INTO D (a_attr, d_attr)
  VALUES ('d_a value', 'd_d value');
 
CREATE VIEW A_hierarchy AS
  SELECT id, b_attr, NULL AS c_attr, NULL AS d_attr, 'B' AS discriminator
    FROM B
  UNION ALL
  SELECT id, b_attr, c_attr, NULL AS d_attr, 'C' AS discriminator
    FROM C
  UNION ALL
  SELECT id, NULL AS b_attr, NULL AS c_attr, d_attr, 'D' AS discriminator
    FROM D;

Model logiczny zadania 6 z poprzedniego skryptu

CREATE TABLE Konferencja (
    rok INTEGER PRIMARY KEY
);

-- TPH
CREATE TABLE Osoba (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    imie VARCHAR(255) NOT NULL,
    nazwisko VARCHAR(255) NOT NULL,
    afiliacja VARCHAR(255) NOT NULL,
    discriminator VARCHAR(255) NOT NULL CHECK(discriminator IN ('Autor', 'CzlonekKomitetu')),
    rok_nominacji INTEGER REFERENCES Konferencja
);

-- Gdybyśmy robili TPT
-- CREATE TABLE Autor (id INTEGER REFERENCES Osoba)
-- CREATE TABLE CzlonekKomitetu (id INTEGER REFERENCES Osoba,
--   rok_nominacji INTEGER REFERENCES Konferencja NOT NULL)

CREATE TABLE Praca (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tytul VARCHAR(255) NOT NULL,
    dziedzina VARCHAR(255) NOT NULL,
    wyrozniona NUMBER(1, 0),
    rok_konferencja INTEGER REFERENCES Konferencja NOT NULL
);

CREATE TABLE AutorstwoPracy (
    id_autor INTEGER REFERENCES Osoba NOT NULL,
    id_praca INTEGER REFERENCES Praca NOT NULL,
    CONSTRAINT pk_autorstwo_pracy PRIMARY KEY (id_autor, id_praca)
);

CREATE TABLE OcenaPracy (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ocena INTEGER NOT NULL,
    opis VARCHAR(255) NOT NULL,
    id_praca INTEGER REFERENCES Praca NOT NULL,
    autor_oceniajacy INTEGER REFERENCES Osoba,
    przydzielony_czlonek_komitetu INTEGER REFERENCES Osoba NOT NULL
);

backback