Doc ID URL

1. Tag DocID ID Name Doc ID URL Data TagID Data Ptr Parent Child Attr TagID Name Value

2. (SELECT parent FROM GROUP BY parent HAVING COUNT(*)=2) Doc ID URL Tag DocID ID Name Data TagID Data Attr TagID Name Value Ptr Parent Child SELECT DATA FROM fabian_data WHERE tagid IN ( INTERSECT SELECT id FROM fabian_tag WHERE name=X AND id IN (SELECT tagid FROM fabian_attr WHERE name=Y AND value=Z) (SELECT child FROM fabian_ptr WHERE parent IN (SELECT * FROM fabian_ptr WHERE child IN (SELECT id FROM fabian_tag WHERE name=X ) )

3. Doc ID URL Tag DocID ID Name Data TagID Data Attr TagID Name Value Ptr Parent Child CREATE OR REPLACE TRIGGER fabian_ptr_del_trg BEFORE DELETE ON FABIAN_PTR FOR EACH ROW BEGIN DELETE FROM fabian_tag WHERE id=:new.child; END; /

4. Doc ID URL Tag DocID ID Name Data TagID Data CREATE OR REPLACE TRIGGER mo_aufgabe_a BEFORE INSERT ON fabian_data FOR EACH ROW DECLARE tag_name VARCHAR2(255); v_authors INTEGER; v_artikel INTEGER; v_artikels INTEGER; v_issue INTEGER; anzahl INTEGER; -- no_insert3 EXCEPTION; BEGIN SELECT name INTO tag_name FROM fabian_tag WHERE id = :NEW.tagid; IF ( tag_name = 'author') THEN SELECT parent INTO v_authors FROM fabian_ptr WHERE child = :NEW.tagid; SELECT COUNT (*) INTO anzahl FROM fabian_ptr WHERE parent = v_authors; IF (anzahl > 3) THEN SELECT parent INTO v_artikel FROM fabian_ptr WHERE child = v_authors; SELECT parent INTO v_artikels FROM fabian_ptr WHERE child = v_artikel; SELECT parent INTO v_issue FROM fabian_ptr WHERE child = v_artikels; DELETE FROM fabian_tag WHERE id = v_issue; -- RAISE no_insert3; END IF; END IF; END; / Attr TagID Name Value Ptr Parent Child Aufgabe a

5. CREATE OR REPLACE TRIGGER mo_aufgabe_c BEFORE INSERT ON fabian_data FOR EACH ROW DECLARE tag_name VARCHAR2(255); anzahl INTEGER; v_authors INTEGER; v_tagid INTEGER; v_artikel INTEGER; v_artikels INTEGER; v_issue INTEGER; -- no_insert10 EXCEPTION; BEGIN SELECT name INTO tag_name FROM fabian_tag WHERE id = :NEW.tagid; IF ( tag_name = 'author') THEN SELECT COUNT(*) INTO anzahl FROM fabian_tag WHERE name = 'author' AND id IN (SELECT tagid FROM fabian_data WHERE data = :NEW.data); IF (anzahl > 10) THEN SELECT parent INTO v_authors FROM fabian_ptr WHERE child = :NEW.tagid; SELECT parent INTO v_artikel FROM fabian_ptr WHERE child = v_authors; SELECT parent INTO v_artikels FROM fabian_ptr WHERE child = v_artikel; SELECT parent INTO v_issue FROM fabian_ptr WHERE child = v_artikels; DELETE FROM fabian_tag WHERE id = v_issue; -- RAISE no_insert10; END IF; END IF; END; -- end mo_aufgabe_c / Doc ID URL Tag DocID ID Name Data TagID Data Attr TagID Name Value Ptr Parent Child Aufgabe c

6. Doc ID URL Tag DocID ID Name Data TagID Data CREATE OR REPLACE TRIGGER olaf_title_trg AFTER INSERT ON FABIAN_DATA FOR EACH ROW DECLARE VTag fabian_tag.name%type; VIssueID fabian_tag.id%type; VArticleID fabian_tag.id%type; VVolumeID fabian_tag.id%type; VNumberID fabian_tag.id%type; BEGIN SELECT name INTO VTag FROM fabian_tag WHERE id=:new.tagid; IF (VTag = 'title') THEN IF (:new.data LIKE '%Database%') THEN SELECT parent INTO VArticleID FROM fabian_ptr WHERE child=:new.tagid; SELECT parent INTO VIssueID FROM fabian_ptr WHERE child IN (SELECT parent FROM fabian_ptr WHERE child=VArticleID); SELECT id INTO VVolumeID FROM fabian_tag WHERE name='volume' AND id IN (SELECT child FROM fabian_ptr WHERE parent=VIssueID); SELECT id INTO VNumberID FROM fabian_tag WHERE name='number' AND id IN (SELECT child FROM fabian_ptr WHERE parent=VIssueID); INSERT INTO fabian_art (articleid, VolumeID, NumberID, title) VALUES (VArticleID, VVolumeID, VNumberID, :new.data); END IF; END IF; END; / Attr TagID Name Value Ptr Parent Child Aufgabe b

7. Doc ID URL Tag DocID ID Name Data TagID Data Attr TagID Name Value Ptr Parent Child CREATE OR REPLACE TRIGGER olaf_author_trg AFTER INSERT ON FABIAN_DATA FOR EACH ROW DECLARE VTagtype fabian_tag.name%type; VArticleID fabian_tag.id%type; VName fabian_data.data%type; VPosition fabian_attr.value%type; BEGIN SELECT name INTO VTagtype FROM fabian_tag WHERE id=:new.tagid; IF (VTagtype = 'author') THEN SELECT value INTO VPosition FROM fabian_attr WHERE name='position' AND tagid = :new.tagid; IF (VPosition = '00') THEN SELECT parent INTO VArticleID FROM fabian_ptr WHERE child IN (SELECT parent FROM fabian_ptr WHERE child=:new.tagid); UPDATE fabian_art SET authorid=:new.tagid WHERE articleid=VArticleID; END IF; END IF; END; / Aufgabe b