\documentclass[11pt]{article}

\usepackage{ngerman,a4}
\usepackage{amstext}
\usepackage{amssymb}
\usepackage{amsmath}
\usepackage{stmaryrd}
\usepackage{enumerate}
\usepackage{alltt}
\usepackage[latin1]{inputenc}
\usepackage{graphicx}

\thispagestyle{empty}

\title{Informationssysteme - Übungsblatt 6}
\author{Jan Hendrik Dithmar - Matrikelnummer 2031259 \\
		Mi, 14-16 Uhr, Andreas Kaster
	}
\date{}

\begin{document}

\newcommand{\AND}{\ensuremath{\wedge}}
\newcommand{\OR}{\ensuremath{\vee}}
\newcommand{\SetR}{\ensuremath{\mathbb{R}}}
\newcommand{\SetN}{\ensuremath{\mathbb{N}}}
\newcommand{\SetC}{\ensuremath{\mathbb{C}}}
\newcommand{\SetZ}{\ensuremath{\mathbb{Z}}}
\newcommand{\SetQ}{\ensuremath{\mathbb{Q}}}
\newcommand{\SetB}{\ensuremath{\mathbb{B}}}
\newcommand{\QED}{\ensuremath{\hspace*{\fill}\square}}
\newcommand{\FLASH}{\ensuremath{\hspace*{\fill}\lightning}}
\newcommand{\OnotO}{\ensuremath{\mathcal{O}}}
\newcommand{\bild}[4]{
	\begin{figure}[htpb]
		\centering
			\includegraphics[width=#1cm]{#2}\\[0.2cm]
			\caption{#3}
			\label{#4}
	\end{figure}
	}

\maketitle

\newpage

\section*{Aufgabe 6.1}
\subsubsection*{(a)}
\begin{verbatim}
CREATE TRIGGER LöschePrüfung
AFTER DELETE ON Student
FOR EACH ROW
DELETE FROM Prüfung WHERE Prüfung.Matrikel_Nr = Student.Matrikel_Nr

CREATE TRIGGER LöschePrüfer
AFTER DELETE ON Professor
FOR EACH ROW
UPDATE Prüfung SET Prüfer = NULL WHERE Prüfer = Professor.P_Name
\end{verbatim}

\subsubsection*{(b)}
(i)
\begin{verbatim}
CREATE ASSERTION CONSTRAINT Semester5
CHECK NOT EXISTS (SELECT * FROM Prüfung p, Student s
                  WHERE p.Matrikel_Nr = s.Matrikel_Nr
                  AND Fach LIKE '%Softwaretechnik%'
                  AND Semester < 5
\end{verbatim}
(ii)
\begin{verbatim}
CREATE ASSERTION CONSTRAINT Semester15
CHECK NOT EXISTS ((SELECT Matrikel_Nr FROM Student
                   WHERE Semester > 14)
                   MINUS
                   (SELECT Matrikel_Nr
                    FROM Prüfung
                    WHERE Note <= 4.0))
\end{verbatim}
(iii)
\begin{verbatim}
CREATE ASSERTION CONSTRAINT BüroDesProfessors
CHECK 1=ALL (SELECT COUNT(*) FROM Professor GROUP BY Gebäude,Raum)
\end{verbatim}
(iv)
\begin{verbatim}
CREATE TRIGGER Matr_Nr
BEFORE UPDATE OF Matrikel_Nr ON Student
("Das Ändern einer Matrikel-Nummer ist nicht möglich."; ROLLBACK WORK)
\end{verbatim}
(v)
\begin{verbatim}
CREATE TRIGGER Notenänderung
AFTER UPDATE OF Note ON Prüfung
REFERENCING OLD AS Prüfung_alt NEW AS Prüfung_neu
FOR EACH ROW
WHEN (Prüfung_neu.Note > Prüfung_alt.Note) (ROLLBACK WORK)
\end{verbatim}

\newpage
\section*{Aufgabe 6.2}
\subsubsection*{(a)}
\begin{verbatim}
CREATE ASSERTION CHECK (Lieferdatum>=ALL
                        (SELECT Erscheinungsdatum 
                         FROM Bücher b
                         WHERE b.isbn=isbn))
\end{verbatim}

\subsubsection*{(b)}
\begin{verbatim}
CREATE OR REPLACE TRIGGER Bankverbindungsänderung
BEFORE UPDATE OF Bankverbindung ON Kunden
REFERENCING NEW AS neu
FOR EACH ROW
DECLARE i LONG;
BEGIN
  SELECT COUNT(*) INTO i FROM Verkäufe V WHERE V.Email=neu.Email
  AND V.Bestelldatum IS NOT NULL AND V.Lieferdatum IS NULL;
  IF i>0 THEN
      ROLLBACK WORK;
  END IF;
END;
\end{verbatim}

\end{document}
