Datenbank erweitern − MainLab

Aufgabe 1: Datenbank öffnen

Lade zunächst die Fahrschuldatenbank hier herunter und speichere die Datei mit einem Rechtsklick / „Speichern unter“ unter dem Pfad H:\Faecher\Informatik\Datenbanken\fahrschule.db.
Öffne das Programm DB Browser for SQLite.
Wähle „Open Database“ aus und lade soeben unter dem Pfad H:\Faecher\Informatik\Datenbanken\fahrschule.db abgespeicherte Datenbank-Datei.

Aufgabe 2: Schema und Daten inspizieren

Der DB Browser for SQLite bietet zunächst an, das Datenbankschema und die Daten zu inspizieren.

Klicke auf „Database Structure“. Vergewissere dich, dass es die gleichen Tabellen mit den gleichen Spalten vorhanden sind, wie du es bereits gewohnt warst.

Für jede Spalte ist ein Datentyp angegeben. Leider gibt es für verschiedene Datenbank-Management-Systeme unterschiedliche Datentypen; teilweise sind sie zusammengelegt... :(

Recherchiere die Bedeutung der folgenden Datentypen:

`TEXT`
`INTEGER`
`BOOL`	(wird manchmal auch als `INTEGER` umgesetzt; dann steht `0` für false und `1` für true)
`DATE`	(wird manchmal auch als `TEXT` umgesetzt; dann steht z.B. `'2020-01-01'` für den 1.1.2020)

Klicke auf „Browse Data“. Wähle die Tabelle fahrschueler aus. Filtere nach den Schülern, welche einen Fahrlehrer mit Kürzel Rad haben. Wie viele sind das? Wer von ihnen hat die meisten Fahrstunden?
Klicke auf „Execute SQL“. Hier kannst du wie gewohnt SQL-Befehle absetzen. Lasse dir alle Schülerdaten mitsamt den Daten ihrer zugehörigen Fahrlehrer anzeigen. Tipp: „Joine“ die beiden Tabellen fahrschueler und fahrlehrer über die Join-Bedingung fahrschueler.fl_kuerzel = fahrlehrer.kuerzel.

Aufgabe 3: Daten verändern

Es wurde ein neuer Fahrlehrer eingstellt, Konrad Stirner. Er erhält das neue Kürzel Sti und er ist unter der Telefonnr. 038828/12345 erreichbar. Klicke unter „Browse Data“ auf , um die neue Zeile einzufügen.

Zu Beginn sind alle Werte auf NULL gesetzt. Doppelklicke auf die einzelnen Zellen und setze die Werte entsprechend.
Die Fahrstundenanzahl von der Schülerin mit Schülernr. 79 (Lara Jäger) hat sich geändert, von 12 auf 14. Führe die Änderung durch.
Die Änderungen werden nicht direkt in der Datenbank gespeichert. Hierfür tue Folgendes: Klicke auf „Write Changes“ .
Die Fahrlehrerin Elaine Schmidt verlässt die Fahrschule. Versuche, ihre Zeile zu löschen. Es erscheint eine Fehlermeldung. Erkläre, warum die Löschung nicht einfach durchgeführt werden kann.

Aufgabe 4: Tabellen anlegen und modifizieren

Jedem Fahrlehrer soll ein Auto zugeordnet werden.

Lege nun eine neue Tabelle namens fahrzeug an, mit den Spalten kennzeichen (TEXT), hersteller (TEXT), ist_automatik (INTEGER − hier soll 0 für "nein"/false und 1 für "ja"/true stehen). Markiere die kennzeichen-Spalte als Primärschlüssel (PK steht für primary key).

Füge eine neue Fremdschlüssel-Spalte in der fahrlehrer-Tabelle an. Diese verweist auf den Primärschlüssel kennzeichen der fahrzeug-Tabelle.

Aufgabe 5: Daten einfügen (mit Hilfe von ChatGPT)

Nun benötigen wir einige Autos! Statt uns alles selbst auszudenken, lassen wir uns von ChatGPT ein paar Autos generieren. Wie sich zeigen wird, versteht ChatGPT ziemlich gut SQL.

Lade die ChatGPT-Website (siehe Anmeldedaten vom Lehrer).

Wie du vielleicht schon gesehen hast, gehört zu jeder Tabelle ein zugehöriger SQL-Befehl. Z.B. hättest du die Tabelle fahrzeug auch mit dem folgenden SQL-Befehl anlegen können. Die Details sind hier gar nicht wichtig, und schon gar nicht brauchst du diese Befehle auswendig lernen. Wenn du sie aber vor dir hast, wirst du sie schnell verstehen können.

CREATE TABLE "fahrzeug" (
	"kennzeichen"	TEXT,
	"hersteller"	TEXT,
	"ist_automatik"	INTEGER,
	PRIMARY KEY("kennzeichen") 
);

Mit Modify Table bekommst du den SQL-Befehl zu Gesicht.

Auch ChatGPT versteht, worum es geht. Gib nun also folgenden prompt bei ChatGPT ein.

ChatGPT wird dir ein paar Beispieldaten angeben, in meinem Fall:

Diese Beispieldaten werden als INSERT INTO-Befehl formuliert und du wirst schnell erkennen, was dieser Befehl mit einer Datenbank anstellt.

Kopiere ihn aus dem ChatGPT-Dialog mit Strg+C und führe ihn im DB Browser for SQLite aus. Speichere die Änderungen mit „Write Changes“. Prüfe, dass die Autos tatsächlich eingefügt worden sind.

Aufgabe 6: Daten ändern (mit Hilfe von ChatGPT)

Die Autos sind eingetragen, aber die Zuordnung zu den Fahrlehrern fehlt noch. Auch hier hilft uns ChatGPT. In der aktuellen ChatGPT-Session sind die Autos nun bekannt. Die fahrlehrer-Tabelle ist noch nicht bekannt.

Die Struktur der fahrlehrer-Tabelle erhälst du wieder über Modify Table.

CREATE TABLE "fahrlehrer" (
	"kuerzel"	TEXT,
	"vorname"	TEXT,
	"nachname"	TEXT,
	"telefonnr"	TEXT,
	"fahrzeug"	TEXT,
	PRIMARY KEY("kuerzel")
);

Die Zeilen der fahrlehrer-Tabelle erhälst du, indem du bei Browse Table alle Zeilen markierst, und dann mit der rechten Maustaste Copy as SQL auswählst.

INSERT INTO "main"."fahrlehrer" ("kuerzel", "vorname", "nachname", "telefonnr", "fahrzeug") VALUES ('Mus', 'Max', 'Mustermann', '123-456-7890', NULL);
INSERT INTO "main"."fahrlehrer" ("kuerzel", "vorname", "nachname", "telefonnr", "fahrzeug") VALUES ('Mu', 'Maria', 'Musterfrau', '987-654-3210', NULL);
INSERT INTO "main"."fahrlehrer" ("kuerzel", "vorname", "nachname", "telefonnr", "fahrzeug") VALUES ('Pet', 'Peter', 'Petersen', '555-123-4567', NULL);
INSERT INTO "main"."fahrlehrer" ("kuerzel", "vorname", "nachname", "telefonnr", "fahrzeug") VALUES ('Schm', 'Elaine', 'Schmidt', '111-222-3333', NULL);
INSERT INTO "main"."fahrlehrer" ("kuerzel", "vorname", "nachname", "telefonnr", "fahrzeug") VALUES ('Mul', 'Edgar', 'Müller', '444-555-6666', NULL);
INSERT INTO "main"."fahrlehrer" ("kuerzel", "vorname", "nachname", "telefonnr", "fahrzeug") VALUES ('Schu', 'Sabine', 'Schubert', '777-888-9999', NULL);
INSERT INTO "main"."fahrlehrer" ("kuerzel", "vorname", "nachname", "telefonnr", "fahrzeug") VALUES ('Hof', 'Markus', 'Hoffmann', '888-777-6666', NULL);
INSERT INTO "main"."fahrlehrer" ("kuerzel", "vorname", "nachname", "telefonnr", "fahrzeug") VALUES ('Ko', 'Christine', 'Koch', '666-555-4444', NULL);
INSERT INTO "main"."fahrlehrer" ("kuerzel", "vorname", "nachname", "telefonnr", "fahrzeug") VALUES ('Bu', 'Andreas', 'Burmann', '333-222-1111', NULL);
INSERT INTO "main"."fahrlehrer" ("kuerzel", "vorname", "nachname", "telefonnr", "fahrzeug") VALUES ('Rad', 'Katrin', 'Rademacher', '222-333-4444', NULL);
INSERT INTO "main"."fahrlehrer" ("kuerzel", "vorname", "nachname", "telefonnr", "fahrzeug") VALUES ('Be', 'Michael', 'Becker', '444-333-2222', NULL);

Beides müssen wir nun an ChatGPT kommunizieren! Kopiere alles zusammen und erstelle eine Abfrage der folgenden Art.

Die Antwort sind genau die Befehle, die man braucht, um die Autos zuzordnen (die Zuordnung übernimmt ChatGPT für uns).