1. Datenbanken Klasse 9
Johannes Piontek1. Grundlagen von Datenbanken
Datenstrukturen
-
Datenstrukturen beschreiben die Art, wie Daten zusammengefasst oder geordnet werden.
-
In der Praxis werden Daten häufig in einer linearen Struktur in Form von Tabellen abgebildet
-
Dabei können folgende Strukturen unterschieden werden (Auswahl):
-
- Lineare (Tabellen, Texte)
- Hierarchische (Baumstruktur, Rangfolgen)
- Vernetzte (Verknüpfung von Daten untereinander, z.B. Wikipedia, WWW)
2. Modell und Modellierung
Model oder Realität?
-
Schaue den Trailer an. Finde mindestens 3 Unterschiede zwischen der Barbie-Welt und der realen Welt.
-
Erkläre, warum die Barbie-Welt nicht der Realität entspricht.
3. Aufbau eines Datenbanksystems
Einstieg
-
inf-schule.de LINK
Relationale Datenbank
Grundelemente eines Datenbanksystems
schematischer Aufbau
-
In der Datenbasis befinden sich die "Rohdaten" in Tabellen, die nach dem Prozess der Datenbankentwicklung und der Erfassung der Daten entstanden sind. Die Daten sind dynamisch und können sich permanent ändern. Beispiel: Jemand kauft einen Artikel in einem Online-Shop, dann verändern sich die Daten, die Anzahl verringert sich.
-
Das Datenbankmanagementsystem verwaltet die Tabellen und kann über Befehle die Daten nach bestimmten Kriterien abrufen, verändern, aber auch ganze Tabellen anlegen bzw. löschen. Dazu wird oft die Sprache SQL benutzt (siehe 6.) Beispiel: Du kaufst etwas im Onlineshop, das DBMS ändert die Anzahl in der entsprechenden Tabelle und trägt in deinem Warenkorb den Artikel ein.
-
Die Anwenderschnittstelle bietet unterschiedlichen Benutzern eine auf ihren Zweck angpasste Oberfläche an. Beispiel: In der App des Onlineshops kannst du als Kunde nach bestimmten Merkmalen wie Größe, Farbe, Preis usw. suchen. Dazu hast du Auswahlmenüs oder Schieberegler. Dies ist für dich als Kunde speziell gestaltet. Das DBMS wertet deine Eingaben aus und macht das Ergebnis der Anfrage in der App sichtbar.
Beispiel Webshop
-
Beispiel-Webshop
https://www.dbiu.de/shop/ -
www.dbiu.de LINK
Übungen zu Aufbau und Grundlagen
3a Übungen Datenbanksystem
Spickzettel für Libre Office Datenbank
Relationale Datenbanken in der Praxis
-
Marktanteile und praktische Bedeutung verschiedener Dantenbanksysteme
-
db-engines.com LINK
4. Entwurf von Datenbanken ERM
Aufgabe
-
Arbeitet euch den Text in inf-schule.de unter diesem Link bis zum Punkt 4. - Beziehungen durch.
Löst dabei die Aufgaben aus dem Text. -
Link zum inf-schule.de:
-
inf-schule.de LINK
Übungen zum ERM
-
Für alle Aufgaben sind die folgenden Schritte zu erledigen:
-
- Identifiziere die Entitäten.
- Bestimme die Attribute für jede Entität.
- Bestimme die Primärschlüssel.
- Identifiziere die Beziehungen (Relationships) zwischen den Entitäten (inkl. Kardinalitäten).
- Erstelle die grafische Darstellung des ER-Modells
-
Aufgabe 1
Schule
Eine Schule hat eine eindeutige Schulnummer, einen Namen und eine Anschrift. In der Schule gibt es mehrere Klassen, diese haben einen eindeutigen Namen eine Raumnummer und eine Schülerzahl. Eine Klasse wird von mehreren SchülerInnen besucht. Diese besitzen eine eindeutige Katalognummer, einen Namen und eine Adresse. -
Aufgabe 2
Firma
Eine Firma hat eine eindeutige Firmenbuchnummer, einen Namen und eine Adresse. Ein Produkt kann von mehreren Firmen verkauft werden, aber eine Firma verkauft auch mehrere Produkte. Jedes Produkt hat eine eindeutige Produktnummer, eine Bezeichnung, einen Einkaufspreis und einen Verkaufspreis. Diese Produkte werden von bestimmten Lieferanten zur Verfügung gestellt. Die Lieferanten werden durch eine eindeutige Nummer, den Firmennamen, einer Kontaktadresse, einer Telefonnummer und einer E-Mailadresse gekennzeichnet. -
Aufgabe 3
Handelskette -
Aufgabe 4:
Schulbibliothek
Diskursbereich: Die Verwaltung des Buchbestands und der Ausleihen in einer Schulbibliothek. -
Problemstellung:
Die Schulbibliothek möchte ein Datenbanksystem einführen, um die Organisation zu verbessern. Entwickle ein Entity-Relationship-Modell (ERM), das folgende Aspekte des Bibliotheksbetriebs abbilden kann: -
- Bücher werden nach Titel, Autor und ISBN verwaltet. Jedes Buch gehört zu einer bestimmten Kategorie (z.B. "Fantasy", "Sachbuch").
- Schüler (Leser) werden mit Name, Klasse und einer eindeutigen Schülernummer erfasst.
- Die Bibliothek hält fest, welche Exemplare eines Buches existieren (jedes Exemplar hat eine eindeutige Inventarnummer und kann ausgeliehen werden).
-
- Es muss gespeichert werden, welcher Schüler welches Exemplar wann ausgeliehen hat (mit Ausleihdatum und Rückgabedatum).
-
Aufgabe 5:
Kleiner Flughafen
Diskursbereich: Die Verwaltung von Flügen, Flugzeugen und Piloten auf einem kleinen Regionalflughafen. -
Problemstellung:
Ein Regionalflughafen plant, seine Betriebsabläufe zu digitalisieren.
Erstelle ein Entity-Relationship-Modell (ERM), um die grundlegenden Informationen über Flüge, Flugzeuge und das Personal zu speichern. -
- Flüge werden durch eine eindeutige Flugnummer, Startflughafen, Zielflughafen und die geplante Abflugzeit identifiziert.
- Flugzeuge haben eine eindeutige Registriernummer, einen Typ (z.B. "Cessna 172") und eine bestimmte Sitzplatzkapazität.
- Piloten werden mit Mitarbeiternummer, Name und Jahre an Flugerfahrung erfasst.
- Jeder Flug wird von einem Flugzeug durchgeführt.
- Jeder Flug muss von einem oder mehreren Piloten besetzt werden (wobei jeder Pilot zur gleichen Zeit nur für einen Flug zuständig sein kann).
Lösungen zu den Übungen ERM
-
Lösung Aufgabe 3
-
-
Lösung Aufgabe 4
-
Lösungsvorschlag Aufgabe 5
-
5 Relationenmodell RM
Redundanzen
-
Redundanzen sind Daten, die unnötigerweise mehrfach gespeichert werden. Hier ein Beispiel:
-
Redundanzen können vermieden werden, indem die Daten auf mehrere Tabellen aufgeteilt werden.
-
Dadurch werden auch Fehler vermieden, die beim vielfachen ändern der Datensätze entstehen könnten.
Lösungen zu den Übungen
-
Lösung Aufgabe 4:
-
Zusammenfassende Übersicht des relationalen Schemas:
-
- LESER (LeserNr, Name, Klasse)
- KATEGORIE (KategorieID, Bezeichnung)
- BUCH (ISBN, Titel, Autor, #KategorieID)
- EXEMPLAR (InventarNr, Zustand, #ISBN)
- AUSLEIHE (AusleihID, Ausleihdatum, Rückgabedatum, SchuelerNr, #InventarNr)
-
Lösung Aufgabe 5:
-
- FLUGZEUG (Registriernummer, Typ, Sitzplatzkapazität)
- PILOT (Mitarbeiternummer, Name, Flugerfahrung)
- FLUG (Flugnummer, Startflughafen, Zielflughafen, Abflugzeit, RegNummer FK)
- BESETZUNG (Flugnummer, Mitarbeiternummer)
6 Praxis und SQL mit Instahub
Einführung
- Instahub (Link: instahub.org) ist ein soziales Netzwerk für Unterrichtszwecke und lehnt sich an bekannte Social Networks an.
-
Jeder Schüler♂♀ ist Administrator seines eigenen Instahub, dem zu Begin etwa 200 Mitglieder angehören. Andere Personen können Mitglied in diesem Netzwerk werden und müssen dazu vom Admin freigeschaltet werden.
-
Die Verwaltung der Instahubs aller SuS liegt beim Lehrer, der diese freischalten muss.
-
Erläuterungen zur Bedienung findest du hier:
-
wi-wissen.github.io LINK
Übungen zum Webshop
-
Der Webshop ist hier zu finden:
https://www.dbiu.de/shop/ -
Aufgaben Webshop
1. Finde alle Kleidungsstücke, die für Männer sind in der Farbe Grün.
2. Finde alle Kleidungsstücke, die zwischen 15 und 20 Euro kosten. Zeige nur den Typ, den Preis und die Farbe an.
3. Finde alle Kleidungsstücke, die rot oder gelb sind und für Frauen bestimmt sind. -
Lösungen:
1. SELECT * FROM Kleidung WHERE (farbe="grün") and (geschlecht="m")
2. SELECT typ, farbe, preis FROM Kleidung WHERE preis>=15 and preis<=20
3. SELECT * FROM Kleidung WHERE (farbe="rot" or farbe="gelb") and (geschlecht="w")
SQL
-
SQL (Structured Query Language) ist eine universale Sprache, die von verschiedenen Datenbankmanagementsystemen verarbeitet werden kann.
Damit kann auf die Datenbasis zugegriffen werden. Abfragen, Auswertungen und Änderungen der Daten werden damit umgesetzt. -
Abfragen von DABA:
-
- SELECT attribut (-s) FROM table (-s) WHERE bedingung
-
Anlegen von DABA:
-
- CREATE DATABASE datenbankname
- CREATE TABLE tablename (attribut1 typ1, attribut2 typ2, …)
-
Daten erfassen:
-
- INSERT
- UPDATE
- DELETE
-
Bei Abfragen wird die SELECT-FROM-WHERE-Form verwendet. Sie führt die Prozesse Projektion, Selection und Join aus.
-
-
-
Aufgaben
-
Die Aufgaben lehnen sich an die Unterrichtsplanung von Anja Eckstein an - vielen Dank dafür.
-
Am Anfang haben die Schüler nur die Relation (Tabelle) "users" zur Verfügung. Darin sind Daten der Mitglieder gespeichert.
-
DB-Schema der Tabelle users in Instahub mit Bezeichnung der Datenfelder und dem zugehörigen Datentyp:
-
users (id:INTEGER, username:TEXT, email:TEXT, password:TEXT, name:TEXT, bio:TEXT, gender:TEXT, birthday:DATETIME, city:TEXT, …)
-
Beispielabfrage an users:
Auflistung der Geburtsdaten aller User: -
SELECT birthday
FROM users -
Syntax einfacher Datenbankabfragen
-
SELECT Spalte1, Spalte2, ...
FROM Tabellenname -
Mit
-
SELECT * FROM Tabellenname
-
wird die gesamte Tabelle ausgegeben.
Lösungen Übung1
Material zu Instahub
-
buch.informatik.cc LINK
7 Selektion mit WHERE
Selektion als Suchfilter
-
Mit der Projektion werden die Attribute (Datenfelder, Spalten) ausgewählt, die im Ergebnis sichtbar sein sollen.
-
Die SELEKTION erfolgt nach dem Schlüsselwort where und legt mittels logischer Verknüpfungen die Kriterien fest, nach denen die Datensätze ausgewählt werden.
-
Übung 2
Lösungen zu Übung 2
Wiederholung+Zusammenfassung
interaktives SQL-Spiel
- SQL-Island
-
-
SQL-Bankraub
-
programmingwiki.de LINK
8 Aggregatfunktionen
Aggregatfunktionen
-
In SQL werden Aggregatfunktionen verwendet, um zusammenfassende Daten zu berechnen, die über mehrere Zeilen hinweg aggregiert werden.
Hier ist eine Übersicht gängiger Aggregatfunktionen mit Beispielen aus einer fiktiven Datenbank. Diese könnte beispielsweise eine Tabelle namens Produkte mit den Spalten Produkt_ID, Kategorie, Preis und Verkauf_Datum enthalten. -
Aggregatfunktionen in SQL
-
- COUNT()
-
Beschreibung: Zählt die Anzahl der Zeilen oder nicht NULL-Werte in einer bestimmten Spalte.
Beispiele:
SELECT COUNT(*) FROM Produkte;
- Zählt alle Produkte in der Tabelle.
SELECT COUNT(Kategorie) FROM Produkte;
- Zählt die Anzahl der Produkte, die einer Kategorie zugeordnet sind. -
- SUM()
-
Beschreibung: Berechnet die Summe eines numerischen Feldes.
Beispiele:
SELECT SUM(Preis) FROM Produkte;
- Gibt die gesamte Summe der Preise aller Produkte aus.
SELECT SUM(Preis) FROM Produkte WHERE Kategorie = 'Getränke';
- Summiert die Preise nur der Produkte, die zur Kategorie "Getränke" gehören. -
- AVG()
-
Beschreibung: Berechnet den Durchschnittswert eines numerischen Feldes.
Beispiele:
SELECT AVG(Preis) FROM Produkte;
- Gibt den durchschnittlichen Preis der Produkte zurück.
SELECT AVG(Preis) FROM Produkte WHERE Kategorie = 'Snacks';
- Berechnet den Durchschnittspreis der Produkte in der Kategorie "Snacks". -
- MIN()
-
Beschreibung: Gibt den kleinsten Wert eines Feldes zurück.
Beispiele:
SELECT MIN(Preis) FROM Produkte;
- Gibt den niedrigsten Preis in der Produkttabelle zurück.
SELECT MIN(Preis) FROM Produkte WHERE Kategorie = 'Obst';
- Findet den niedrigsten Preis unter den Obstprodukten. -
- MAX()
-
Beschreibung: Gibt den größten Wert eines Feldes zurück.
-
Beispiele:
-
SELECT MAX(Preis) FROM Produkte;
- Gibt den höchsten Preis in der Produkttabelle zurück. -
SELECT MAX(Preis) FROM Produkte WHERE Kategorie = 'Getränke';
- Findet den höchsten Preis unter den Getränken. -
- GROUP BY
-
Beschreibung: Gruppiert die Ergebnisse nach einer bestimmten Spalte und ermöglicht die Anwendung der Aggregatfunktionen auf diese Gruppen.
Beispiele:
SELECT Kategorie, COUNT(*) FROM Produkte GROUP BY Kategorie;
- Zählt die Anzahl der Produkte pro Kategorie.
SELECT Kategorie, AVG(Preis) FROM Produkte GROUP BY Kategorie;
- Berechnet den Durchschnittspreis der Produkte pro Kategorie. -
- HAVING
-
Beschreibung: Filtert Gruppen basierend auf einer Bedingung, die mit Aggregatfunktionen arbeitet.
Beispiele:
SELECT Kategorie, SUM(Preis)
FROM Produkte
GROUP BY Kategorie
HAVING SUM(Preis) > 100;
- Gibt Kategorien zurück, deren Gesamtpreis über 100 liegt.
SELECT Kategorie, COUNT(*)
FROM Produkte
GROUP BY Kategorie
HAVING COUNT(*) > 10;
- Zählt Kategorien, die mehr als 10 Produkte enthalten.
9 SQL mit mehreren Tabellen - Join
weitere Tabellen in Instahub
-
Neben der Relation (Tabelle) "users" hat Instahub weitere Tabellen, die für die Funktionen eines sozialen Netzwerks erforderlich sind. Diese sind aus dem ERM abgeleitet.
-
wi-wissen.github.io LINK
-
Fremdschlüssel (Foreign Key)
-
Für die Verbindung von zwei Relationen für Entities in einer 1:n-Relationship werden sogenannte Fremdschlüssel (Foreign Keys) benötigt. Diese sind die Primärschlüssel der Relation mit der Kardinalität 1 und werden in der Relation mit der Kardinalität n eingetragen (siehe 5).
-
Damit können in einer SQL-Anfrage mehrere Tabellen verknüpft werden. Diese Verknüpfung nennt man "Join".
Natural Join
- Ein "Natural Join" (Wikipedia) verbindet die Datensätze der beiden beteiligten Tabellen miteinander, deren Werte in den angebenen Attributen übereinstimmen.
-
Stell dir vor, du hast zwei Tabellen in einer Datenbank: eine mit den Namen deiner Klassenkameraden und ihren Lieblingsfarben, und eine andere mit ihren Namen und ihren Lieblingssportarten.
-
Draus ergeben sich die Tabellen:
-
PERSONEN (P-Nr., Name, Lieblingsfarbe)
SPORTARTEN (SP_Nr., Sp.-Name, Lieblingssportart)
HABEN (P-Nr., SP-Nr.) -
Die erste Tabelle könnte so aussehen:
-
Jetzt möchtest du herausfinden, welche Farbe und welche Sportart zu welchem Namen gehören. Hier kommt der NATURAL JOIN ins Spiel!
-
Er sucht nach Spalten, die in beiden Tabellen den gleichen Namen haben (in unserem Beispiel ist das die Spalte "Name").
-
Er verbindet die Zeilen aus beiden Tabellen, die in dieser Spalte den gleichen Wert haben.
-
Im Ergebnis bekommst du eine neue Tabelle:
Natural Join in SQL
-
Für das Beispiel kann folgende SQL-Anweisung genutzt werden:
-
SELECT *
FROM Tabelle1
NATURAL JOIN Tabelle2; -
Hier geht man davon aus, dass Tabelle 1 und Tabelle 2 jeweils ein Attribut "Lieblingssportart" besitzen und dass nur ein Attribut gleich ist.
-
Eine andere Variante ist der Natural Join mit folgender SQL-Anweisung:
-
SELECT *
FROM Tabelle1, Tabelle2
WHERE Tabelle1.Attribut1 = Tabelle2.Attribut1 -
Beispiel aus Instahub:
