SQL-Übungsaufgaben (Orga-Datenbank für ein Barcamp)

SQL-Übungsaufgaben (Erstellen einer Datenbank zur Kongress-Organisierung)

https://oer-informatik.de/db-kongress-uebungsaufgabe

https://bildung.social/@oerinformatik/111744180002021967

tl/dr; Übungsaufgaben zur Datenbank-Modellierung und einfachen SQL-Abfragen am Beispiel einer Datenbank für die Planung eines Kongresses. Die einzelnen Schritte werden exemplarisch (an Teilbereichen der Datenbank) abgefragt. Die Aufgaben waren Bestandteil einer klassischen Zettel/Stift Klassenarbeit. Es soll ein konzeptuelles Datenmodell erstellt werden, dieses soll in ein logisches Datenmodell transformiert werden. Das Relationenschema soll normalisiert werden, schließlich soll die Datenstruktur erstellt und die Daten einer Tabelle eingefügt, geändert, gelöscht und ausgelesen werden. (Zuletzt geändert am 29.04.2025)

Ausgangslage

Wir planen an unserer Schule einen Hauskongress, ein Barcamp. An Stelle des normalen Berufsschulunterrichts bieten Schülerinnen und Schüler der Fachinformatik-Klassen Vorträge, Tutorials, Workshops, Diskussionen an. Diese Veranstaltungen müssen irgendwie organisiert und dokumentiert werden. Mit dieser Hintergrundinfo stürzen wir uns auf die erste Teilaufgabe:

Konzeptueller Entwurf mit dem Entity-Relationship-Diagramm

Es soll eine Datenbank entworfen werden zur Planung und Umsetzung einer selbstorganisierten Konferenz (ein „Barcamp“). Bei dem Kick-off-Meeting mit dem Planungsteam wurden folgende Anforderungen festgehalten:

• Auf der Konferenz gibt es unterschiedliche Angebote, die sich Sessions nennen.

• Sessions haben ein Thema und eine Beschreibung.

• Sessions können unterschiedliche Formate haben (Beispiele sind Diskussionen, Präsentationen, Workshops). Jedes Format hat dabei einen Namen, eine Beschreibung und eine bestimmte Dauer.

• Genau eine Person ist verantwortlich für eine Session. Es gibt aber oft mehrere Personen, die sie gemeinsam vorbereiteten. Personen werden mit Vor- und Nachname sowie E-Mail-Adresse gespeichert.

• Da Sessions innerhalb der Konferenz mehrfach gehalten werden können, werden sie als Veranstaltungen in einem Timetable organisiert: Dort wird für jede Veranstaltung eine eigene Veranstaltungsnummer festgehalten, außerdem wird dort erfasst, um welche Session es sich handelt, wann die Veranstaltung beginnt. Der Endzeitpunkt wird dort auch angezeigt, aber aus der Dauer des Sessionformats berechnet.

• Im Timetable wird für jede Veranstaltung auch der Ort erfasst, wobei hier bisher nur spezifiziert ist, dass es mehrerer Informationen sind, das genaue Format ist noch nicht gegeben (könnte GebäudeNr, RaumNr, oder Adresse oder Geolocation sein – oder alles. Das muss später erfasst werden.)

• Es gibt Personen, die keine oder mehrere Sessions vorbereiten oder für keine oder mehrere Sessions verantwortlich sind.

• Zu einer Session gehört genau ein Dokument, in dem die Informationen gespeichert werden. Das Dokument soll in einer gesonderten Tabelle erfasst werden.

Erstelle für die genannten Punkte ein Entity-Relationship-Diagramm in Chen-Notation unter Nutzung der Notationsmittel für die oben genannten Eigenschaften!

Punkteverteilung ca.

• 5 Entitätstypen 3P = 15P

• 5 Relationen 1P = 5P

• abgeleitetes Attribut 1P

• mehrwertiges Attribut erkannt 2P

• Optionalität bei Person 1P

Häufige Fehler:

• An Attributen werden keine(!) Kardinalitäten notiert

• Chen verlangt benannte Relations-Rauten! (-2P)

• Es werden die falschen Notationsmittel verwendet (also Attribute nicht mit Ellipse, Entitätstyp nicht mir Rechteck usw.) (-2P)

• Primär- und Fremdschlüssel sind hier noch nicht nötig

Logischer Entwurf mit dem Relationenmodell (21 Punkte)

Hinweis: die folgenden Fragen betreffen Bereiche der Datenbank, die unter Umständen im ER-Diagramm der vorigen Aufgabe so nicht modelliert werden sollten.

Transformation ins Relationenmodell (9 Punkte)

Stelle den folgenden Sachverhalt im Relationenmodell dar. Alle Attribute sind eingabepflichtig.

Das Relationenmodell in Textform:

Timetable(VeranstaltungsID<<PK>>)
Teilnahme(VeranstaltungsID <<PK>> <<FK>>, E-Mail <<PK>> <<FK>>)
Person (E-Mail <<PK>>, Vorname, Nachname)

Das Relationenmodell als Diagramm:

Schlüsselattribute (4 Punkte)

Erkläre an obigem Beispiel den Unterschied zwischen einem künstlichen und einem natürlichen Primärschlüssel.

Transformation (8 Punkte)

Transformiere die Beziehungen und Entitätstypen des folgende ER-Modells auf zwei unterschiedlichen Wegen in ein Relationenmodell. Für jede der beiden Lösungen nenne bitte Randbedingungen, unter denen Du dich für diese Variante entscheiden würdest.

Normalisierung (21 P)

Für das Barcamp wurde ein Formular erstellt, mit dem um Beteiligung gebeten wurde (“Call for Participation”). Die Formularergebnisse liegen als Tabelle vor, die nun normalisiert werden soll, um der dritten Normalform zu genügen.

1. Welche Anpassungen sind nötig, damit das obige Relationenschema der 1. Normalform genügt? (3 Punkte)

2. Benenne den Unterschied zwischen der zweiten und der dritten Normalform von Relationenmodellen! (8 Punkte)

3. Erstelle für die oben dargestellten Formulardaten das Relationenmodell in der 3. Normalform (es müssen nur die Attributnamen genannt werden, nicht die Werte!) (10 Punkte)

Session(SessionID (PK), Thema, Beschreibung, Format, Name (FK)) (4P) Person(Name (PK), E-Mail, Klasse) (3P) Organisatoren(SessionID (PK, FK), Name (PK, FK)) (3P)

(Wenn Name PK ist)

Physisches Modell / SQL-DDL (18 P)

Tabellenerzeugung

Erstelle alle vollständigen DDL-Befehle in korrekter Reihenfolge, um die beiden folgenden Tabellen in eine bestehende Datenbank Barcamp zu erzeugen: (10 Punkte)

Timetable

(Hinweis: SessionNr ist Primärschlüssel der Tabelle Session, VeranstaltungsID soll automatisch vergeben werden, Startzeitpunkt ist nicht eingabepflichtig)

Räume

(Hinweis: Bezeichnung ist eingabepflichtig und muss einzigartig sein)

CREATE TABLE Räume(
  RaumNr CHAR(8) PRIMARY KEY, 
  Gebäude CHAR(8), 
  Bezeichnung CHAR(32) NOT NULL UNIQUE,
  CONSTRAINT pk PRIMARY KEY (RaumNr, Gebäude)
);

CREATE TABLE Timetable (
  VeranstaltungsID AUTO_INCREMENT PRIMARY KEY, 
  RaumNr CHAR(8) FOREIGN_KEY REFERENCES Räume(RaumNr), 
  Gebäude CHAR(8) FOREIGN_KEY REFERENCES Räume(Geebäude),, 
  SessionNr INTEGER FOREIGN_KEY REFERENCES Session(SessionNr), 
  Startzeitpunkt TIME)
);

Änderung der Tabellenstruktur

Es soll in der bestehenden Tabelle Timetable ein Feld Beschreibung (als Zeichenkette) ergänzt werden. Benenne den nötigen SQL-Befehl! (4 Punkte)

ALTER TABLE Timetable ADD Beschreibung CHAR(32)

Datentypen

Die meisten Datenbankmanagement-Systeme bieten zwei unterschiedliche Datentypen für Zeichenketten an: VARCHAR und CHAR. Erläutere, was die beiden Datentypen unterscheidet, und folgere daraus, für welche Anwendungsfälle sie geeignet sind. (4 Punkte)

SQL-DML (14 P)

1. Füge die beiden Zeilen aus den Beispielen der vorigen Aufgabe (Raum156, VeranstaltungNr. 16) in die soeben erstellten Tabellen ein. Gebe alle dafür nötigen SQL-Befehle in der korrekten Reihenfolge an! Es kann vorausgesetzt werden, dass die Session Nr. 123 bereits existiert. (8 Punkte)

INSERT INTO Räume (RaumNr, Gebäude, Bezeichnung) VALUES (‘R156‘, ‘M2‘, ‘Linux-Labor‘); //4P
INSERT INTO Timetable (VeranstaltungsID, RaumNr, Gebäude, SessionNr, Startzeitpunkt) VALUES(NULL, ‘R156‘, ‘M2‘, ‘123‘, ‘10:00‘); );//4P

2. Die Veranstaltung 16 wird in Raum R555 verlegt. Benenne alle erforderlichen SQL-Befehle, wenn der Raum bereits existiert! (3 Punkte)

UPDATE Timetable SET RaumNr = 'R555' WHERE VeranstaltungsID = '16';

3. Es gab eine Fehleingabe, die nun rückgängig gemacht werden muss: Benenne alle SQL-Befehle, die erforderlich sind, um die Veranstaltung 19 aus der Datenbank zu entfernen! (3 Punkte)

Timetable

DELETE FROM Timetable WHERE VeranstaltungsID = 19;

SQL-DQL (8 P)

Die Tabelle `Timetable`` hat folgenden Aufbau und Beispielinhalt:

1. Es sollen alle Veranstaltungen ausgegeben werden, bei denen in der Beschreibung steht, dass sie ausfallen. Benenne den nötigen SQL-Befehl anhand der obigen Beispieldaten! (4 Punkte)

SELECT * FROM Timetable WHERE Beschreibung LIKE "%fällt%"

2. Es sollen eine Liste aller VeranstaltungsID und SessionNr ausgegeben werden, die in R156 stattfinden. Benenne den nötigen SQL-Befehl anhand der obigen Beispieldaten! (4 Punkte)

SELECT VeranstaltungsID, SessionNr FROM Timetable WHERE RaumNr = 'R156' 

Hinweis zur Nachnutzung als Open Educational Resource (OER)

Dieser Artikel und seine Texte, Bilder, Grafiken, Code und sonstiger Inhalt sind - sofern nicht anders angegeben - lizenziert unter CC BY 4.0. Nennung gemäß TULLU-Regel bitte wie folgt: “SQL-Übungsaufgaben (Erstellen einer Datenbank zur Kongress-Organisierung)” von oer-informatik.de (H. Stein), Lizenz: CC BY 4.0. Der Artikel wurde unter https://oer-informatik.de/db-kongress-uebungsaufgabe veröffentlicht, die Quelltexte sind in weiterverarbeitbarer Form verfügbar im Repository unter https://gitlab.com/oer-informatik/db-sql/db-modellierung-messwesen. Stand: 29.04.2025.

[Kommentare zum Artikel lesen, schreiben] / [Artikel teilen] / [gitlab-Issue zum Artikel schreiben]