Übungsaufgaben
Szenario
Das BSZ Elektrotechnik in Dresden hat beschlossen elektronische Notenbücher einzuführen. Dafür wurde das folgende Relationen-Modell eingeführt:
Aufgaben
Teil 1: Erstellen der Datenbank
- Aufgabe 1: Erstellen Sie die Tabellen aus dem Relationen-Modell im Microsoft SQL Server Management Studio. Geben Sie die SQL-Anweisungen an und erklären Sie in welcher Reihenfolge diese ausgeführt werden müssen und wieso.
Cheat-Code
-- 1. Tabelle "lehrer" erstellen (keine Abhängigkeiten)
CREATE TABLE lehrer (
lehrer_id INT PRIMARY KEY,
vorname VARCHAR(50) NOT NULL,
nachname VARCHAR(50) NOT NULL
);
-- 2. Tabelle "schueler" erstellen (keine Abhängigkeiten)
CREATE TABLE schueler (
schueler_id INT PRIMARY KEY,
vorname VARCHAR(50) NOT NULL,
nachname VARCHAR(50) NOT NULL,
geburtsdatum DATE NOT NULL
);
-- 3. Tabelle "kurs" erstellen (Fremdschlüssel auf "lehrer")
CREATE TABLE kurs (
kurs_id INT PRIMARY KEY,
lehrer_id INT NOT NULL,
name VARCHAR(50) NOT NULL,
FOREIGN KEY (lehrer_id) REFERENCES lehrer(lehrer_id)
);
-- 4. Tabelle "note" erstellen (Fremdschlüssel auf "kurs" und "schueler")
CREATE TABLE note (
note_id INT PRIMARY KEY,
kurs_id INT NOT NULL,
schueler_id INT NOT NULL,
note INT NOT NULL,
datum DATE NOT NULL,
thema VARCHAR(50) NOT NULL,
FOREIGN KEY (kurs_id) REFERENCES kurs(kurs_id),
FOREIGN KEY (schueler_id) REFERENCES schueler(schueler_id)
);
- Aufgabe 2: Fügen Sie folgende Datensätze ein:
Datensätze
| lehrer_id | vorname | nachname |
|---|---|---|
| 1 | Max | Mustermann |
| 2 | Anna | Schmidt |
| 3 | Klaus | Bauer |
| kurs_id | lehrer_id | name |
|---|---|---|
| 1 | 1 | Mathematik |
| 2 | 2 | Physik |
| 3 | 1 | Informatik |
| schueler_id | vorname | nachname | geburtsdatum |
|---|---|---|---|
| 1 | Lisa | Müller | 2005-03-15 |
| 2 | Tom | Meier | 2004-11-22 |
| 3 | Emma | Schneider | 2006-01-30 |
| note_id | kurs_id | schueler_id | note | datum | thema |
|---|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 2025-03-06 | Analysis |
| 2 | 1 | 2 | 3 | 2025-03-06 | Analysis |
| 3 | 1 | 1 | 2 | 2025-04-05 | Vektorgeometrie |
| 4 | 1 | 2 | 5 | 2025-04-05 | Vektorgeometrie |
| 5 | 2 | 1 | 3 | 2025-01-13 | Quantenphysik |
| 6 | 2 | 2 | 2 | 2025-01-13 | Quantenphysik |
| 7 | 3 | 2 | 5 | 2025-03-18 | Datenbanken |
| 8 | 3 | 3 | 4 | 2025-03-18 | Datenbanken |
Cheat-Code
-- Lehrer
INSERT INTO lehrer (lehrer_id, vorname, nachname) VALUES
(1, 'Max', 'Mustermann'),
(2, 'Anna', 'Schmidt'),
(3, 'Klaus', 'Bauer');
-- Kurse
INSERT INTO kurs (kurs_id, lehrer_id, name) VALUES
(1, 1, 'Mathematik'),
(2, 2, 'Physik'),
(3, 1, 'Informatik');
-- Schüler
INSERT INTO schueler (schueler_id, vorname, nachname, geburtsdatum) VALUES
(1, 'Lisa', 'Müller', '2005-03-15'),
(2, 'Tom', 'Meier', '2004-11-22'),
(3, 'Emma', 'Schneider', '2006-01-30');
-- Noten
INSERT INTO note (note_id, kurs_id, schueler_id, note, datum, thema) VALUES
(1, 1, 1, 1, '2025-03-06', 'Analysis'),
(2, 1, 2, 3, '2025-03-06', 'Analysis'),
(3, 1, 1, 2, '2025-04-05', 'Vektorgeometrie'),
(4, 1, 2, 5, '2025-04-05', 'Vektorgeometrie'),
(5, 2, 1, 3, '2025-01-13', 'Quantenphysik'),
(6, 2, 2, 2, '2025-01-13', 'Quantenphysik'),
(7, 3, 2, 5, '2025-03-18', 'Datenbanken'),
(8, 3, 3, 4, '2025-03-18', 'Datenbanken');
Teil 2: SQL Abfragen
Formulieren Sie die SQL-Abfragen für die folgenden Aufgaben.
- Aufgabe 3: Gesucht ist eine Liste der angebotenen Kurse mit dem Kursnamen und der Anzahl an Kursteilnehmern. Die Kursnamen sollen aufsteigend sortiert sein.
erwartete Ausgabe
| Kursname | Teilnehmerzahl |
|---|---|
| Informatik | 2 |
| Mathematik | 2 |
| Physik | 2 |
- Aufgabe 4: Gesucht ist eine Liste der angebotenen Kurse mit dem Kursnamen und den Namen des Lehrer (Vor- und Nachname in einem Feld).
erwartete Ausgabe
| Kursname | Lehrername |
|---|---|
| Informatik | Max Mustermann |
| Mathematik | Max Mustermann |
| Physik | Anna Schmidt |
- Aufgabe 5: Gesucht ist eine Liste an Schülern, die ein Test nicht bestanden haben (ab Note 5). Geben Sie den Schülernamen, den Kursnamen und das Thema des Tests aus.
erwartete Ausgabe
| Schueler_Vorname | Schueler_Nachname | Kursname | Thema |
|---|---|---|---|
| Tom | Meier | Mathematik | Vektorgeometrie |
| Tom | Meier | Informatik | Datenbanken |
- Aufgabe 6: Gesucht ist eine Liste der Durchschnittsnoten der Kurse mit dem Kursnamen, Schüleranzahl und Notendurchschnitt.
erwartete Ausgabe
| Kursname | Schueleranzahl | Notendurchschnitt |
|---|---|---|
| Mathematik | 2 | 2 |
| Physik | 2 | 2 |
| Informatik | 2 | 4 |
- Aufgabe 7: Gesucht ist eine Liste der erbrachten Noten im März 2025. Geben Sie den Kursnamen, die Note und die teilnehmenden Schüler an.
erwartete Ausgabe
| Kursname | Note | Schueler |
|---|---|---|
| Mathematik | 1 | Lisa Müller |
| Mathematik | 3 | Tom Meier |
| Informatik | 5 | Tom Meier |
| Informatik | 4 | Emma Schneider |
Teil 3: Daten Manipulation
- Aufgabe 8: Max Mustermann hat die Note vom Schüler Tom Meier zum Thema Vektorgeometrie falsch eingetragen. Der Schüler hat ein Notenergebnis von 4. Geben Sie die SQL-Anweisung zum Aktualisieren der Note an.
- Aufgabe 9: Der Lehrer Klaus Bauer ist bereits vor der Einführung des digitalen Notenbuchs in Rente gegangen. Geben Sie die SQL-Anweisung an, die ihn aus der Lehrer-Tabelle entfernt.
Teil 3: Datenbank-Theorie
- Aufgabe 10: Definieren Sie den Begriff Datenbankmanagementsystem und erklären Sie in diesem Zusammenhang die Begriffe Front-End und Back-End.
- Aufgabe 11: Erklären Sie den Begriff Datenbanksystem. Gehen Sie dabei auf dessen Bestandteile und deren Aufgaben ein.