Logo Logo
InfoHome Themen Projekte Links Software
Themen
JavaHamster
BlueJ
Java
HTML
XHTML
CSS
XML
Datenbanken
MySQL
SELECT
INSERT
UPDATE
DELETE
Tippspiel-DB
Theoretische Informatik
PHP
Kara
Lego-Roboter
Algorithmen

Tippspiel-Datenbank

Am Beispiel einer Tippspiel-Datenbank sollen die wichtigsten 'Bausteine' aus denen SELECT-Anweisungen zusammengesetzt sind, demonstriert werden.

Hier gibt es zunächst einmal den Datebank-Dump zum Download. [Rechte Maustaste > Ziel speichern unter...]

Zum Wiederherstellen der Datenbank aus dem Dump-File gibt es mehrere Möglichkeiten:

  1. MySQL Administrator öffnen. Mit Server-Host: localhost, Benutzername: root und leerem Passwort beim lokalen MySQL-Server anmelden. Der MySQL-Server muss dazu gestartet sein! Dies kann man im XAMPP Control Panel (erreichbar über Startmenüeintrag!) überprüfen und ggf. veranlassen. Dann 'Wiederherstellung' ('Restore') auswählen und unten rechts auf 'Datei öffnen' ('Open File') klicken und das Dump-File auswählen und öffnen. Abschließend auf 'Starten ' ('Restore') klicken um die Wiederherstellung durchzuführen.
  2. MySQL Query Browser öffnen. Mit Server-Host: localhost, Benutzername: root und leerem Passwort beim lokalen MySQL-Server anmelden. Der MySQL-Server muss dazu gestartet sein! Dies kann man im XAMPP Control Panel (erreichbar über Startmenüeintrag!) überprüfen und ggf. veranlassen. Dann im Datei- (File-) Menü 'Skript öffnen...' ('Open Script...') auswählen und das heruntergeladene Dump-File öffnen. Rechts im Schema-Browser ein neues Schema erstellen und auswählen. Dann einfach das Script ausführen.

1. Kartesisches Produkt

Die Anfrage

SELECT *
FROM wm_user, wm_mannschaften;

liefert als Ergebnis das Kartesische Produkt der beiden Relationen wm_user und wm_mannschaften. Jedes Tupel aus der Relation wm_user wird mit jedem Tupel aus der Relation wm_mannschaften in Verbindung gebracht. Dabei entstehen jede Menge "sinnlose" Verbindungen! Jeder User hat ja nur auf einen Weltmeister getippt und sollte deshalb sinnvoller Weise auch nur mit einem Tupel aus der Relation wm_mannschaften in Verbindung gebracht werden.

2. Join

Eine "sinnvolle" Verbindung stellt erst der Join zweier Relationen über gleiche Werte in inhaltsgleichen Spalten her. Im Beipspiel muss über die Spalten wmtipp (aus wm_user) und mid (aus wm_mannschaften) gejoint werden. Die entsprechende SQL-Anfrage basiert auf dem Kartesischen Produkt und selektiert aus dem Kartesischen Produkt diejenigen Tupel, die in den beiden Spalten gleiche Werte aufweisen.

SELECT *
FROM wm_user, wm_mannschaften
WHERE wmtipp = mid; /* Join-Bedingung */

Auf gleiche Weise lassen sich auch die Relationen wm_tipps und wm_user verbinden. Die Spalten über die sich eine sinnvolle Verbindung herstellen lässt - über die also gejoint wird - heissen in beiden Tabellen uid. Um eine Join-Bedingung formulieren zu können, müssen sich die Spalten aber eindeutig ansprechen lassen. Dies gelingt am einfachsten mit Hilfe von Aliasen.

SELECT *
FROM wm_user u, wm_tipps t /* Aliase u und t werden definiert */
WHERE u.uid = t.uid; /* und für den eindeutigen Zugriff auf die Spalten uid verwendet */

Mit u.uid wird die Spalte uid aus der Relation wm_user angesprochen, mit t.uid die Spalte uid aus der Relation wm_tipps. Es ginge mit etwas mehr Schreibarbeit auch so:

SELECT *
FROM wm_user, wm_tipps
WHERE wm_user.uid = wm_tipps.uid;

Projeziiert man auf eine der beiden Spalten uid, muss auch hier klar gemacht werden, welche der beiden Spalten man meint.

SELECT sid, u.uid, name, tore_a, tore_b
FROM wm_user u, wm_tipps t
WHERE u.uid = t.uid;

3. Join über mehrere Tabellen

Häufig benötigt man Informationen aus mehr als zwei Tabellen. Zur Auswertung des Tippspiels ist es zum Beispiel erforderlich, jeden Spieler mit seinen Tipps und außerdem jeden Tipp mit dem zugehörigen tatsächlichen Spielergebnis in Verbindung zu bringen. An einer entsprechenden SQL-Anfrage sind demnach die drei Relationen wm_user, wm_spiele und wm_tipps beteiligt. Zudem benötigt man zwei Join-Bedingungen: Die erste Join-Bedingung u.uid = t.uid verbindet die Relationen wm_user und wm_tipps, die zweite Join-Bedingung t.sid = s.sid verbindet die Relationen wm_tipps und wm_spiele.

SELECT s.sid, u.uid, name, t.tore_a AS tipp_a, t.tore_b AS tipp_b, s.tore_a, s.tore_b
FROM wm_user u, wm_tipps t, wm_spiele s
WHERE u.uid = t.uid AND t.sid = s.sid;

Die Spaltennamen tore_a bzw. tore_b treten in der Ergebnisrelation doppelt auf, bedeuten aber etwas unterschiedliches. Einmal sind die getippten Tore gemeint, einmal die tatsächlich erzielten. Anhand eines Spaltenaliases lassen sich die Spaltennamen der Spalten t.tore_a bzw. t.tore_b in der Ergebnisrelation wie oben gezeigt ändern.

Anhand einer Teilmenge der Ergebnisrelation lässt sich die Abfrage noch einmal verdeutlichen:

siduidnametipp_atipp_btore_atore_b
162mumm3042
262mumm1102
362mumm2110
462mumm0400
562mumm2221
662mumm1201
762mumm0031

Jeder Datensatz enthält Informationen zu einem Spiel (sid, tore_a und tore_b), ein getipptes Ergebnis (tipp_a und tipp_b), sowie Informationen zu dem Spieler, der diesen Tipp für genau dieses Spiel abgegeben hat (uid und name).

4. Exakt richtige Ergebnisse

Exakt richtige Tipps ermittelt man auf Basis der unter Punkt 3 durchgeführten Anfrage:

SELECT s.sid, u.uid, name, t.tore_a AS tipp_a, t.tore_b AS tipp_b, s.tore_a, s.tore_b
FROM wm_user u, wm_tipps t, wm_spiele s
WHERE u.uid = t.uid AND t.sid = s.sid;

Durch Anfügen einer weiteren Bedingung selektiert man nur diejenigen Tupel, bei denen die getippten Tore für Mannschaft A den tatsächlich erzielten Toren von Mannschaft A und die getippten Tore für Mannschaft B den tatsächlich erzielten Toren von Mannschaft B entsprechen:

SELECT s.sid, u.uid, name, t.tore_a AS tipp_a, t.tore_b AS tipp_b, s.tore_a, s.tore_b
FROM wm_user u, wm_tipps t, wm_spiele s
WHERE u.uid = t.uid AND t.sid = s.sid
AND t.tore_a = s.tore_a AND t.tore_b = s.tore_b;

5. Richtige Tendenzen

Richtige Tendenz-Tipps ergeben sich nur, wenn kein exakt richtiger Tipp vorliegt. Es muss also gerade die Negation der unter Punkt 4 hinzugefügten Bedingung gelten

(1)

  !(t.tore_a = s.tore_a AND t.tore_b = s.tore_b)

Im Folgenden sind drei Fälle (mit OR) zu unterscheiden, in denen ein richtiger Tendenztipp vorliegt.

Entweder der Spieler hat den Sieg von Mannschaft A korrekt vorhergesagt

(2a)

  t.tore_a > t.tore_b AND s.tore_a > s.tore_b

oder aber die Niederlage von Mannschaft A (bzw. den Sieg von Mannschaft B)

(2b)

  t.tore_a < t.tore_b AND s.tore_a < s.tore_b

oder ein Unentschieden

(2c)

  t.tore_a = t.tore_b AND s.tore_a = s.tore_b

Ein richtiger Tendenztipp liegt vor, wenn die Bedingungen (1) UND [(2a) ODER (2b) ODER (2c)] erfüllt sind.

SELECT s.sid, u.uid, name, t.tore_a AS tipp_a, t.tore_b AS tipp_b, s.tore_a, s.tore_b
FROM wm_user u, wm_tipps t, wm_spiele s
WHERE u.uid = t.uid AND t.sid = s.sid
AND !(t.tore_a = s.tore_a AND t.tore_b = s.tore_b)
AND
(
t.tore_a > t.tore_b AND s.tore_a > s.tore_b
OR t.tore_a < t.tore_b AND s.tore_a < s.tore_b
OR t.tore_a = t.tore_b AND s.tore_a = s.tore_b
)

6. Vorrundenspiel oder Playoff-Spiel?

Vorrundenspiele und Playoff-Spiele werden verschieden bepunktet. Aus diesem Grund ist es wichtig beide Spieltypen unterscheiden zu können. In der Spalte gruppe aus der Relation wm_spiele findet sich die benötigte Information. Wenn in diesem Feld eine Zahl steht handelt es sich um ein Playoff-Spiel. Bei Vorrundenspielen steht in dem Feld ein Buchstabe, der anzeigt, in welcher Gruppe dieses Spiel stattfindet.

Die Bedingung

  s.gruppe >= 'A' AND s.gruppe <= 'H'

filtert alle Vorrundenspiele heraus, die Bedingung

  s.gruppe >= '1' AND s.gruppe <= '8'

alle Playoff-Spiele.

7. Gesamtpunktzahlen ermitteln

/* Exakt richtige Tipps: Playoffs */

(
SELECT u.uid, u.name, count(u.uid)*6 AS punkte
FROM wm_user AS u, wm_tipps AS t, wm_spiele AS s
WHERE u.uid=t.uid AND s.sid=t.sid
AND s.tore_a=t.tore_a AND s.tore_b=t.tore_b
AND s.gruppe >= '1' AND s.gruppe <= '8'
GROUP BY uid
)

UNION ALL

/* Exakt richtige Tipps: Vorrunde */
(
SELECT u.uid, u.name, count(u.uid)*3 AS punkte
FROM wm_user AS u, wm_tipps AS t, wm_spiele AS s
WHERE u.uid=t.uid AND s.sid=t.sid
AND s.tore_a=t.tore_a AND s.tore_b=t.tore_b
AND s.gruppe >= 'A' AND s.gruppe <= 'H'
GROUP BY uid
)

UNION ALL

/* Richtige Tendenz: Playoffs */
(
SELECT u.uid, u.name, count(u.uid)*2 AS punkte
FROM wm_user AS u, wm_tipps AS t, wm_spiele AS s
WHERE u.uid=t.uid AND s.sid=t.sid
AND NOT(s.tore_a=t.tore_a AND s.tore_b=t.tore_b)
AND
(
s.tore_a>s.tore_b AND t.tore_a>t.tore_b
OR s.tore_a
OR s.tore_a=s.tore_b AND t.tore_a=t.tore_b
)
AND s.gruppe >= '1' AND s.gruppe <= '8'
GROUP BY uid
)

UNION ALL

/* Richtige Tendenz: Vorrunde */
(
SELECT u.uid, u.name, count(u.uid)*1 AS punkte
FROM wm_user AS u, wm_tipps AS t, wm_spiele AS s
WHERE u.uid=t.uid AND s.sid=t.sid
AND NOT(s.tore_a=t.tore_a AND s.tore_b=t.tore_b)
AND

(
s.tore_a>s.tore_b AND t.tore_a>t.tore_b
OR s.tore_a
OR s.tore_a=s.tore_b AND t.tore_a=t.tore_b
)

AND s.gruppe >= 'A' AND s.gruppe <= 'H'
GROUP BY uid
)

ORDER BY uid

punkte.qbquery

» drucken: pdf | html

© 2004-2024 M. Blanke · Ursulaschule · Kleine Domsfreiheit 11-18 · 49074 Osnabrück