-- Fügen Sie zunächst folgende zwei Attribute zur Tabelle "lehrveranstaltung"
-- hinzu:

ALTER TABLE lehrveranstaltung DROP COLUMN IF EXISTS lv_lernziele;
ALTER TABLE lehrveranstaltung DROP COLUMN IF EXISTS lv_inhalte;

ALTER TABLE lehrveranstaltung ADD COLUMN lv_lernziele TEXT;
ALTER TABLE lehrveranstaltung ADD COLUMN lv_inhalte   TEXT;


/******************************************************************************
 * 1. Erzeugen Sie für die Tabelle "lehrveranstaltung" ein 
 *    Volltext-Attribut "volltext" samt Volltext-Index vom Typ 'german'.
 *    In diesem Index sollen die Inhalte de Text-Attribute 'lv_titel', 
 *    'lv_lernziele' und 'lv_inhalte' gespeichert werden.
 ******************************************************************************/

-- Lösung der Aufgabe

 

-- Sobald Sie das Attribut samt Index erzeugt haben, sollten Sie ein paar
-- Texte hinzufügen.

UPDATE lehrveranstaltung
SET    lv_lernziele = 'Die Studierenden beherrschen die Modellierung relationaler Datenbanken. Sie können komplexe Anfragen und Updates in SQL formulieren. Sie sind in der Lage, auch komplexere Datenbank-Anwendungen vor allem für Internet und Intranet zu realisieren.',
       lv_inhalte   = 'ER-Modellierung, Relationenmodell, Transaktionen, Datenbank-Management-Systeme und Multimedia'
WHERE  lv_id = 1;

UPDATE lehrveranstaltung
SET    lv_lernziele = 'Die Studierenden gewinnen einen grundlegenden Einblick in die Entwicklung von interaktiven Multimedia-Anwendungen mit Hilfe von Web-Technologien. Sie sind in der Lage, derartige Anwendungen selbst zu entwickeln. Typische Anwendungen sind Spiele, Apps (für mobile Endgeräte) und interaktive Web-Auftritte.',
       lv_inhalte   = 'Programmierung (HTML 5, JavaScript), Modellierung (Use-Case-Diagramme, Klassendiagramme), komplexe Programmierprinzipien zur Erstellung von interaktiven Multimedia-Anwendungen'
WHERE  lv_id = 2;

UPDATE lehrveranstaltung
SET    lv_lernziele = 'Die Studierenden sind befähigt im Team eine anwendungsbezogene Aufgabenstellung aus dem Bereich der interaktiven Medien sowohl aus gestalterischer als auch aus technischer Perspektive systematisch bearbeiten und innerhalb eines vorgegebenen Zeit- und Budgetrahmens methodisch angemessen bewältigen. Dabei übernehmen sie eigenständig die Teamorganisation und Arbeitsaufteilung sowie die Projektplanung. Sie sind in der Lage mit potentiellen Projektpartnern und Sponsoren in Kontakt zu treten und verstehen es Ideen sowie erzielte Projektergebnisse überzeugend zu präsentieren.',
       lv_inhalte   = 'In Projektgruppen (typische Gruppengröße 5-8 Mitglieder) bearbeiten die Studierenden eine von den Projektbetreuern grob vorgegebene Aufgabenstellung, die unter Zeit- und Ressourcenvorgaben von den Studierenden in eigener Regie zu lösen ist. Die Studierenden sollen dabei selbst herausfinden und erfahren, wie sie sich in Teams fachlich einbringen und organisatorisch einordnen können. In wöchentlichen Projektsitzungen informieren sich die Betreuer über den Fortgang des Projekts und stehen der Projektgruppe beratend zur Seite. Um den Anwendungsbezug der Projektthemen zu gewährleisten, erfolgt die Themenfindung oftmals mit externen Projektpartnern aus Wirtschaft und Kultur.'
WHERE  lv_id = 3;


/******************************************************************************
 * 2. In welchen Lehrveranstaltungen wird JavaScript behandelt?
 *
 * lv_id  lv_titel
 * ============================================
 * 2      Multimedia-Anwendungen/Programmierung
 *
 *****************************************************************************/



/******************************************************************************
 * 3. In welchen Lehrveranstaltungs-Beschreibungen wird auf "Studierende"
 *    Bezug genommen?
 *
 * lv_id  lv_titel
 * =================================================
 * 1      Multimedia-Datenbanken (Datenmanagement II)
 * 2      Multimedia-Anwendungen/Programmierung
 * 3      Medienprojekt
 *
 * Frage: Warum wird dieses Ergebnis geliefert, obwohl das Wort "Studierende"
 * in keiner der Lehrveranstaltungs-Beschreibungen vorkommt?
 * (Es kommt nur "Studierenden" vor.)
 *****************************************************************************/



/*******************************************************************************
 * 4. In welchen Lehrveranstaltungen geht es um Modelle?
 *    Beachten Sie, dass das Wort "Modell" in keinem der Texte vorkommt
 *    (wie Sie mit einer einfachen Anfrage sofort herausfinden können).
 *    Es gibt aber verwandte Wörter, die mit dem Begriff "Modell" anfangen.
 *
 * lv_id  lv_titel
 * =================================================
 * 1      Multimedia-Datenbanken (Datenmanagement II)
 * 2      Multimedia-Anwendungen/Programmierung
 *
 *******************************************************************************/



/*******************************************************************************
 * 5. Für welche Lehrveranstaltungen wurden die Lernziele und/oder die
 *    Inhalte noch nicht erfasst.
 *
 * lv_id  lv_titel
 * ==================
 * 4      Aktzeichnen
 *
 *******************************************************************************/



/*******************************************************************************
 * 6. In welchen Lehrveranstaltungen geht es um Internet oder Web?
 *
 * lv_id  lv_titel
 * =================================================
 * 1      Multimedia-Datenbanken (Datenmanagement II)
 * 2      Multimedia-Anwendungen/Programmierung
 *
 *******************************************************************************/



/*******************************************************************************
 * 7. In welchen Lehrveranstaltungen geht es um Internet oder Web, aber nicht um
 *    Datenbanken?
 *
 * lv_id  lv_titel
 * =================================================
 * 2      Multimedia-Anwendungen/Programmierung
 *
 *******************************************************************************/



/*******************************************************************************
 * 8. Erweitern Sie den Volltextindex so, dass darin auch die
 *    Namen aller Lehrenden, die die jeweilige Veranstaltung durchführen,
 *    enthalten sind.
 * * 
 * Achtung, wenn auch Updates anderer Tabellen berücksichtigt werden sollen
 * (siehe Aufgabe 10), ist diese Aufgabe aufwändiger.
 *
 * Löschen Sie daher zunächst nur den Vollext-Index, befüllen Sie dann 
 * das Volltext-Attribut "volltext" mit den richtigen Werten und erstellen
 * Sie den Index neu. Bearbeiten Sie anschließend Aufgabe 9.
 *
 * Erzeugen Sie nun alle Trigger, die notwendig sind, um den Volltext-Index
 * zu aktualisieren, wenn entsprechende Änderungen an beteiligten Tabellen
 * vorgenommen werden. Bearbeiten Sie anschließend Aufgabe 10. 
 *******************************************************************************/ 

-- Tipp: Definieren Sie sich einen Text-Aggregations-Operator:

CREATE OR REPLACE
  FUNCTION ARRAY_TO_TEXT_COMMA(a ANYARRAY) RETURNS TEXT 
LANGUAGE PLPGSQL IMMUTABLE
AS 
$_plpgsql_$
BEGIN 
  RETURN ARRAY_TO_STRING(a, ', ');
END
$_plpgsql_$
;

DROP AGGREGATE IF EXISTS TEXT_AGGREGATE (ANYELEMENT);
CREATE AGGREGATE TEXT_AGGREGATE (ANYELEMENT)
(
  SFUNC     = ARRAY_APPEND,
  STYPE     = ANYARRAY,
  FINALFUNC = ARRAY_TO_TEXT_COMMA
)
; 
 
-- Nach Ausführen dieser beiden Befehle steht Ihnen dauerhaft
-- eine neue Aggregationsfunktion "TEXT_AGGREGATE" zur
-- Verfügung, die Sie wie andere Aggregationsfunktionen
-- ("COUNT", "SUM" etc.) verwenden können.

SELECT TEXT_AGGREGATE(lv_titel)
FROM   lehrveranstaltung
;

-- Weitere nützliche Aggragationsfunktionen finden Sie unter
-- http://mmdb.hs-augsburg.de/beispiel/postgresql/aggregation/aggregate.sql

-- So, nun können Sie mit der eigentlichen Aufgabe beginnen.

-- 
-- Neuen Index erzeugen
--


-- 
-- Trigger definieren
--

-- Wenn sich die Beschreibung einer Lehrveranstaltung ändert
-- (Tabelle lehrveranstaltung: INSERT, UPDATE), muss das
-- Attribut "volltext" aktualisiert werden.



-- Wenn eine Lehrveranstaltung von einem neuen Lehrenden gehalten
-- wird (Tabelle "lp_haelt_lv": INSERT, UPDATE), muss das
-- Attribut "volltext" in der Tabelle "lehrveranstaltung" 
-- aktualisiert werden.
/* -- Tipp:
   -- Wenn nicht die Tabelle lehrveranstaltung selbst, sondern
   -- eine andere Tabelle aktualisiert wird, kann der 
   -- Vollextindex in der Tabelle lehrveranstaltung mit einem
   -- Dummy-Update aktualisiert werden:
   
   UPDATE lehrveranstaltung
   SET    volltext = NULL -- dummy update that enforces volltext to be computed
   WHERE  lv_id <=/IN Menge aller Lehrveranstaltungen, die aktualisiert werden müssen>;
*/


-- Wenn eine Lehrveranstaltung einen Lehrenden verliert 
-- (Tabelle "lp_haelt_lv": DELETE, UPDATE), muss das
-- Attribut "volltext" in der Tabelle "lehrveranstaltung" 
-- aktualisiert werden.


-- Und zum Schluss muss noch die Namensänderungen von Lehrenden 
-- (Tabelle "lehrender": Update) behandelt werden.



/*******************************************************************************
 * 9. Testen Sie Ihren Volltextindex, indem Sie diejenigen Lehrveranstaltungen 
 *    suchen, die Kowarschick, Klever und Rist gemeinsam durchführen.
 *
 * lv_id  lv_titel
 * ====================
 * 3      Medienprojekt
 *
 *******************************************************************************/ 



/*******************************************************************************
 * 10. Ändern Sie die Datenbank so, dass Herr Seifert das Medienprojekt an Stelle
 *     von Herrn Klever hält. Suchen Sie nun alle Lehrveranstaltungen, die von 
 *     Kowarschick, Rist und Seifert gemeinsam durchgeführt werden.
 *
 * lv_id  lv_titel
 * ====================
 * 3      Medienprojekt
 *
 *******************************************************************************/