\documentclass{bschlangaul-aufgabe}
\bLadePakete{sql}
\begin{document}
\bAufgabenMetadaten{
  Titel = {Aufgabe 3},
  Thematik = {Schuldatenbank},
  Referenz = 46116-2018-H.T2-TA2-A3,
  RelativerPfad = Examen/46116/2018/09/Thema-2/Teilaufgabe-2/Aufgabe-3.tex,
  ZitatSchluessel = examen:46116:2018:09,
  BearbeitungsStand = mit Lösung,
  Korrektheit = korrekt und überprüft,
  Ueberprueft = {mit Dozentenlösung abgeglichen},
  Stichwoerter = {SQL, SQL mit Übungsdatenbank, CREATE TABLE, INSERT, ALTER TABLE, UPDATE, DELETE, VIEW, GROUP BY, DROP TABLE},
  EinzelpruefungsNr = 46116,
  Jahr = 2018,
  Monat = 09,
  ThemaNr = 2,
  TeilaufgabeNr = 2,
  AufgabeNr = 3,
}

\let\f=\textbf
\let\u=\underline
\let\s=\bSqlCode

Gegeben\index{SQL} \footcite{examen:46116:2018:09} sei das folgende
Datenbank-Schema, das für die Speicherung der Daten einer Schule
entworfen wurde, zusammen mit einem Teil seiner Ausprägung. Die
Primärschlüssel-Attribute sind jeweils unterstrichen.
\footcite{db:ab:3}

Die Relation \emph{Schüler} enthält allgemeine Daten zu den Schülerinnen
und Schülern. Schülerinnen und Schüler nehmen an Prüfungen in
verschiedenen Unterrichtsfächern teil und erhalten dadurch Noten. Diese
werden in der Relation \emph{Noten} abgespeichert. Prüfungen haben ein
unterschiedliches Gewicht. Beispielsweise hat ein mündliches Ausfragen
oder eine Extemporale das Gewicht $1$, während eine Schulaufgabe das
Gewicht $2$ hat.

\bPseudoUeberschrift{Schüler:}

\begin{center}
\begin{tabular}{|llll|}
\hline
\f{\u{SchülerID}} & \f{Vorname} & \f{Nachname}  & \f{Klasse} \\
1                 & Laura       & Müller        & 4A         \\
2                 & Linus       & Schmidt       & 4A         \\
3                 & Jonas       & Schneider     & 4A         \\
4                 & Liam        & Fischer       & 4B         \\
5                 & Tim         & Weber         & 4B         \\
6                 & Lea         & Becker        & 4B         \\
7                 & Emilia      & Klein         & 4C         \\
8                 & Julia       & Wolf          & 4C         \\
\hline
\end{tabular}
\end{center}

\bPseudoUeberschrift{Noten:}

\begin{center}
\begin{tabular}{|lllll|}
\hline
\f{\u{SchülerID{[}Schüler{]}}} & \f{Schulfach}  & \f{Note} & \f{Gewicht} & \f{Datum}  \\
1                              & Mathematik     & 3        & 2           & 23.09.2017 \\
1                              & Mathematik     & 1        & 1           & 03.10.2017 \\
1                              & Mathematik     & 2        & 2           & 15.10.2017 \\
1                              & Mathematik     & 4        & 1           & 11.11.2017 \\
\hline
\end{tabular}
\end{center}

\begin{bAdditum}[Übungsdatenbank]
% Datenbankname: schuldatenbank
\begin{minted}{sql}
CREATE TABLE Schüler (
  SchülerID INTEGER PRIMARY KEY NOT NULL,
  Vorname VARCHAR(20),
  Nachname VARCHAR(20),
  Klasse VARCHAR(5)
);

CREATE TABLE Noten (
  SchülerID INTEGER NOT NULL,
  Schulfach VARCHAR(20),
  Note INTEGER,
  Gewicht INTEGER,
  Datum DATE,
  PRIMARY KEY (SchülerID, Schulfach, Datum),
  FOREIGN KEY (SchülerID) REFERENCES Schüler(SchülerID)
);

INSERT INTO Schüler VALUES
  (1, 'Laura',  'Müller',    '4A'),
  (2, 'Linus',  'Schmidt',   '4A'),
  (3, 'Jonas',  'Schneider', '4A'),
  (4, 'Liam',   'Fischer',   '4B'),
  (5, 'Tim',    'Weber',     '4B'),
  (6, 'Lea',    'Becker',    '4B'),
  (7, 'Emilia', 'Klein',     '4C'),
  (8, 'Julia',  'Wolf',      '4C');

INSERT INTO Noten VALUES
  (1, 'Mathematik', 3, 2, '23.09.2017'),
  (1, 'Mathematik', 1, 1, '03.10.2017'),
  (1, 'Mathematik', 2, 2, '15.10.2017'),
  (1, 'Mathematik', 4, 1, '11.11.2017');
\end{minted}
\index{SQL mit Übungsdatenbank}
\end{bAdditum}

\begin{enumerate}

%%
% (a)
%%

\item Geben Sie die SQL-Befehle an, die notwendig sind, um die oben
dargestellten Tabellen in einer SQL-Datenbank anzulegen.
\index{CREATE TABLE}

\begin{bAntwort}
\begin{minted}{sql}
CREATE TABLE IF NOT EXISTS Schüler (
  SchülerID INTEGER PRIMARY KEY NOT NULL,
  Vorname VARCHAR(20),
  Nachname VARCHAR(20),
  Klasse VARCHAR(5)
);

CREATE TABLE IF NOT EXISTS Noten (
  SchülerID INTEGER NOT NULL,
  Schulfach VARCHAR(20),
  Note INTEGER,
  Gewicht INTEGER,
  Datum DATE,
  PRIMARY KEY (SchülerID, Schulfach, Datum),
  FOREIGN KEY (SchülerID) REFERENCES Schüler(SchülerID)
);
\end{minted}
\end{bAntwort}

%%
% (b)
%%

\item Entscheiden Sie jeweils, ob folgende Einfügeoperationen vom
gegebenen Datenbanksystem (mit der angegebenen Ausprägungen) erfolgreich
verarbeitet werden können und begründen Sie Ihre Antwort kurz.
\index{INSERT}

\begin{minted}{sql}
INSERT INTO Schüler
  (SchülerID, Vorname, Nachname, Klasse)
VALUES
  (6, 'Johannes', 'Schmied', '4C');
\end{minted}

\begin{bAntwort}
Nein. Ein/e Schüler/in mit der ID 6 existiert bereits. Primärschlüssel
müssen eindeutig sein.
\end{bAntwort}

\begin{minted}{sql}
INSERT INTO Noten VALUES (6, 'Chemie', 1, 2, '1.4.2020');
\end{minted}

\begin{bAntwort}
Nein. Ein \emph{Datum} ist zwingend notwendig. Da \emph{Datum} im
Primärschlüssel enthalten ist, darf es nicht NULL sein. Es gibt auch
keine/n Schüler/in mit der ID 9. Der/die Schüler/in  müsste vorher
angelegt werden, da die Spalte \emph{SchülerID} von der Tabelle
\emph{Noten} auf den Fremdschlüssel \emph{SchülerId} aus der
Schülertabelle verweist.
\end{bAntwort}

%%
% (c)
%%

\item Geben Sie die Befehle für die folgenden Aktionen in SQL an.
Beachten Sie dabei, dass die Befehle auch noch bei Änderungen des oben
gegebenen Datenbankzustandes korrekte Ergebnisse zurückliefern müssen.
\index{ALTER TABLE}

\begin{itemize}
\item Die Schule möchte verhindern, dass in die Datenbank mehrere Kinder
mit dem selben Vornamen in die gleiche Klasse kommen. Dies soll bereits
auf Datenbankebene verhindert werden. Dabei sollen die Primärschlüssel
nicht verändert werden. Geben Sie den Befehl an, der diese Änderung
durchführt.

\begin{bAntwort}
\begin{minted}{sql}
ALTER TABLE Schüler
ADD CONSTRAINT eindeutiger_Vorname UNIQUE (Vorname, Klasse);
\end{minted}
\end{bAntwort}

\item Der Schüler \emph{Tim Weber} (SchülerID: 5) wechselt die Klasse.
Geben Sie den SQL-Befehl an, der den genannten Schüler in die Klasse
\emph{„4C“} überführt.
\index{UPDATE}

\begin{bAntwort}
\begin{minted}{sql}
UPDATE Schüler
SET Klasse = '4C'
WHERE
  Vorname = 'Tim' AND
  Nachname = 'Weber' AND
  SchülerID = 5;
\end{minted}
\end{bAntwort}

\item Die Schülerin \emph{Laura Müller} (SchülerID: 1) zieht um und
wechselt die Schule. Löschen Sie die Schülerin aus der Datenbank. Nennen
Sie einen möglichen Effekt, welcher bei der Verwendung von Primär- und
Fremdschlüsseln auftreten kann.
\index{DELETE}

\begin{bAntwort}
Alle Noten von \emph{Laura Müller} werden gelöscht, falls \s{ON DELETE
CASCADE} gesetzt ist. Oder es müssen erst alle Fremdschlüsselverweise
auf diese \emph{SchülerID} in der Tabelle \emph{Noten} gelöscht werden

\begin{minted}{sql}
DELETE FROM Noten
WHERE SchülerID = 1;
\end{minted}
\end{bAntwort}

\item Erstellen Sie eine View \emph{„DurchschnittsNoten“}, die die
folgenden Spalten beinhaltet: \emph{Klasse}, \emph{Schulfach}, \emph{Durchschnittsnote}

Hinweis: Beachten Sie die Gewichte der Noten.
\index{VIEW}
\index{GROUP BY}

\begin{bAntwort}
\begin{minted}{sql}
CREATE VIEW DurchschnittsNoten AS (
  (SELECT s.Klasse, n.Schulfach, (SUM(n.Note * n.Gewicht) / SUM(n.Gewicht)) AS Durchschnittsnote
  FROM Noten n, Schüler s
  WHERE s.SchülerID = n.SchülerID
  GROUP BY s.Klasse, n.Schulfach)
);

SELECT * FROM DurchschnittsNoten;
\end{minted}

\begin{bSqlErgebnis}
 klasse | schulfach  | durchschnittsnote
--------+------------+-------------------
 4A     | Mathematik |                 2
(1 row)
\end{bSqlErgebnis}
\end{bAntwort}

\item Geben Sie den Befehl an, der die komplette Tabelle \emph{„Noten“}
löscht.
\index{DROP TABLE}

\begin{bAntwort}
\begin{minted}{sql}
DROP TABLE Noten;
\end{minted}
\end{bAntwort}

\end{itemize}

%%
% (d)
%%

\item Formulieren Sie die folgenden Anfragen in SQL. Beachten Sie dabei,
dass sie SQL-Befehle auch noch bei Änderungen der Ausprägung die
korrekten Anfrageergebnisse zurückgeben sollen.

\begin{itemize}

%%
%
%%

\item Gesucht ist die durchschnittliche Note, die im Fach Mathematik
vergeben wird.

Hinweis: Das Gewicht ist bei dieser Anfrage nicht relevant

\begin{bAntwort}
\begin{minted}{sql}
SELECT AVG(Note)
FROM Noten
WHERE Schulfach = 'Mathematik';
\end{minted}
\end{bAntwort}

%%
%
%%

\item Berechnen Sie die Anzahl der Schüler, die im Fach Mathematik am
23.09.2017 eine Schulaufgabe (\dh Gewicht=2) geschrieben haben.

\begin{bAntwort}
\begin{minted}{sql}
SELECT COUNT(*) AS Anzahl_Schüler
FROM Noten
WHERE Datum = '23.09.2017' AND Gewicht = 2 AND Schulfach = 'Mathematik';
\end{minted}

\begin{bSqlErgebnis}
 anzahl_schüler
----------------
              1
(1 row)
\end{bSqlErgebnis}
\end{bAntwort}

%%
%
%%

\item Geben Sie die \emph{SchülerID} aller Schüler zurück, die im Fach
Mathematik mindestens drei mal die Schulnote $6$ geschrieben haben.

\begin{bAntwort}
\begin{minted}{sql}
SELECT SchülerID
FROM Noten
WHERE Schulfach = 'Mathematik' AND Note = 6
GROUP BY SchülerID
HAVING COUNT(*) >= 3;
\end{minted}

\begin{bSqlErgebnis}
 schülerid
-----------
(0 rows)
\end{bSqlErgebnis}
\end{bAntwort}

%%
%
%%

\item Gesucht ist der Notendurchschnitt bezüglich jedes Fachs der Klasse
„4A“.

\begin{bAntwort}
\begin{minted}{sql}
SELECT n.Schulfach, AVG(n.Note)
FROM Schüler s, Noten n
WHERE s.SchülerID = n.SchülerID AND s.Klasse = '4A'
GROUP BY n.Schulfach;
\end{minted}

\begin{bSqlErgebnis}
schulfach  |        avg
------------+--------------------
 Mathematik | 2.5000000000000000
(1 row)
\end{bSqlErgebnis}
\end{bAntwort}
\end{itemize}

%%
% (e)
%%

\item Geben Sie jeweils an, welchen Ergebniswert die folgenden
SQL-Befehle für die gegebene Ausprägung zurückliefern.

\begin{minted}{sql}
SELECT COUNT(DISTINCT Klasse)
FROM
Schüler NATURAL JOIN Noten;
\end{minted}

\begin{bAntwort}
\begin{bSqlErgebnis}
 count
-------
     1
(1 row)
\end{bSqlErgebnis}
4A von Laura Müller. Ohne \s{DISTINCT} wäre das Ergebnis 4.
\end{bAntwort}

\begin{minted}{sql}
SELECT COUNT(ALL Klasse)
FROM
Noten, Schüler;
\end{minted}

\begin{bAntwort}
\begin{bSqlErgebnis}
 count
-------
    32
(1 row)
\end{bSqlErgebnis}
Es entsteht das Kreuzprodukt ($8 \cdot 4 = 32$).
\end{bAntwort}

\begin{minted}{sql}
SELECT COUNT(Note)
FROM
Schüler NATURAL LEFT OUTER JOIN Noten;
\end{minted}

\begin{bAntwort}
\begin{minted}{sql}
SELECT * FROM Schüler NATURAL LEFT OUTER JOIN Noten;
\end{minted}

ergibt:

\begin{bSqlErgebnis}
 schülerid | vorname | nachname  | klasse | schulfach  | note | gewicht |   datum
-----------+---------+-----------+--------+------------+------+---------+------------
         1 | Laura   | Müller    | 4A     | Mathematik |    3 |       2 | 2017-09-23
         1 | Laura   | Müller    | 4A     | Mathematik |    1 |       1 | 2017-10-03
         1 | Laura   | Müller    | 4A     | Mathematik |    2 |       2 | 2017-10-15
         1 | Laura   | Müller    | 4A     | Mathematik |    4 |       1 | 2017-11-11
         2 | Linus   | Schmidt   | 4A     |            |      |         |
         5 | Tim     | Weber     | 4B     |            |      |         |
         8 | Julia   | Wolf      | 4C     |            |      |         |
         6 | Lea     | Becker    | 4B     |            |      |         |
         4 | Liam    | Fischer   | 4B     |            |      |         |
         3 | Jonas   | Schneider | 4A     |            |      |         |
         7 | Emilia  | Klein     | 4C     |            |      |         |
(11 rows)
\end{bSqlErgebnis}

\begin{bSqlErgebnis}
 count
-------
     4
(1 row)
\end{bSqlErgebnis}

\s{COUNT} zählt die \s{NULL}-Werte nicht mit. Die \emph{Laura Müller}
hat 4
Noten.
\end{bAntwort}

\begin{minted}{sql}
SELECT COUNT(*)
FROM
Schüler NATURAL LEFT OUTER JOIN Noten;
\end{minted}

\begin{bAntwort}
\begin{bSqlErgebnis}
 count
-------
    11
(1 row)
\end{bSqlErgebnis}
Siehe Zwischenergebistabelle in der obenstehenden Antwort. Alle Schüler
und die Laura 4-mal, weil sie 4 Noten hat.
\end{bAntwort}
\end{enumerate}
\end{document}