Sammlung alt DB

Datenbanken und Statistiken sind im täglichen Leben ständig präsent, sowohl im privatwirtschaftlichen als auch staatlichen Bereich.
Zum ersten Bereich gehören beispielsweise die Datenbanken der Versicherungen, der Reisebüros, der Online-Händler oder der SCHUFA (Schutzgemeinschaft zur allgemeinen Kreditsicherung).
Zum staatlichen Bereich zählen z.B. die statistischen Landesämter, die Meldebehörden oder das Verkehrszentralregister ("Flensburg").

In den folgenden Wochen werden wir uns mit

• der Modellierung von Datenbanken sowie der Optimierung dieser Modelle
• Eigenschaften von Datenbankmanagementsystemen
• Möglichkeiten der Auswertung von Daten

beschäftigen.

Bevor wir uns der Modellierung widmen, soll anhand der SQL-Syntax die Auswertung einer bereits bestehenden 
Datenbank durchgeführt, eine DB erstellt werden sowie ein weiteres Beispiel analysiert werden.

1. SQL und SQL-Syntax am Beispiel der Terra-Datenbank
2. Datenbank erstellen Datenbanken vorbereiten
3. Beispiel Bankraub
4. Bibliothek Bibliothek
5. Abitur Abitur
6. CD-Versand CD-Versand
7. Datenbankbeleg

Die in der Praxis am meisten verwendete relationale Datenbanksprache ist SQL.
SQL ist eine deskriptive Sprache, d.h. eine Anfrage hat das Ergebnis ohne Angabe des Algorithmus zu beschreiben.

Grundlegend basieren die Anfrageanweisungen auf der SELECT-FROM-WHERE - Klausel

Beispiel:

SELECT Feldnamen
FROM Tabelle(n)
WHERE Bedingung;

Dabei erfolgt unter SELECT eine Projektion auf die Spalten der Tabelle(n) und unter WHERE eine Selektion (Auswahl) der gewünschten Datensätze bzw. Datenfelder!

Für die WHERE-Klausel:
Vergleichsoperatoren =; <>; <; <=; >; >=

Prädikate können mit AND, OR und NOT zu komplexeren Bedingungen verknüpft werden.
Priorität: NOT vor AND vor OR oder Klammerung!
BETWEEN-Prädikat: entspricht einer Verknüpfung >= AND <=
Sortierung: ORDER BY <spaltenname> ASC/DESC (auf- bzw. absteigend)</spaltenname>

SELECT Feldnamen
FROM Tabelle(n)
WHERE Bedingung
ORDER BY Feldname ASC/DESC;

weitere Hinweise zur Syntax siehe SQL-Tutorial
bzw. speziell für das PWiki siehe hier

Die ersten Übungen zur Abfrage an Datenbanken erfolgt mit Hilfe der Terra-Datenbank online

Lösen Sie dazu folgende Aufgaben:

1. Ermitteln Sie die Namen aller Kontinente
2. Gesucht sind alle Inseln (Name) der Inselgruppe der Philippinen.
3. Gesucht sind die Einwohnerzahlen der deutschsprachigen Länder (D, A, CH).
4. Gesucht ist die Einwohnerzahl der deutschsprachigen Länder (D, A, CH).
5. Gesucht sind alle Berge der Alpen (Beachten Sie, dass die Alpen in Teilgebirge aufgegliedert sind).
6. Gesucht sind alle Hochgebirge (Gebirge mit Bergen über 3000 m Höhe).
7. Gesucht sind alle Flüsse (Name), die in die Ostsee oder Nordsee münden und über 1000 km lang sind.
8. Gesucht sind alle Wüsten (Name) der Art 'Sandwueste' mit einer Fläche größer als 25000km².
9. Geben Sie für alle Millionenstädte, die in den Tropen liegen, die Namen und ihre Koordinaten an. (Die Tropen liegen zwischen 23.27 Grad nördlicher und 23.27 Grad südlicher Breite.)
10. Geben Sie die Ländernamen und die Zahl der Einwohner für alle Länder an, die mehr als 45 Millionen Einwohner haben. Ordnen Sie nach der Einwohnerzahl!

Z: Geben Sie von jedem Land in Afrika den Namen, die Anzahl der Einwohner und die Fläche an.

• Diese Einleitung entstammt der Seite von Michael Hielscher.

• Es wird eine Datenbank angelegt.
• Die Datenfelder werden gefüllt.
• Eine Abfrage gestellt.

Eine kleine DB vorbereiten: <eval id="4c8fc835525fc"> DROP TABLE Place IF EXISTS; CREATE TABLE Place (Code INTEGER,Name VARCHAR(255)); CREATE INDEX iCode ON Place (Code);</eval>

DROP TABLE Customer IF EXISTS; CREATE TABLE Customer (Nr INTEGER,Name VARCHAR(255),Place INTEGER); CREATE INDEX iNr ON Customer (Nr);

Führen wir ein paar Inserts durch: <eval id="4c8fc8355270c"> INSERT INTO Place VALUES (4900,'Langenthal'); INSERT INTO Place VALUES (8000,'Zurich'); INSERT INTO Place VALUES (3000,'Berne'); INSERT INTO Place VALUES (1200,'Geneva'); INSERT INTO Place VALUES (6900,'Lugano');</eval>

INSERT INTO Customer VALUES (1,'Meier',3000); INSERT INTO Customer VALUES (2,'Mueller',8000); INSERT INTO Customer VALUES (3,'Devaux',1200); INSERT INTO Customer VALUES (4,'Rossi',6900); INSERT INTO Customer VALUES (5,'Rickli',3000); INSERT INTO Customer VALUES (6,'Graf',3000); INSERT INTO Customer VALUES (7,'Mueller',4900); INSERT INTO Customer VALUES (8,'May',1200); INSERT INTO Customer VALUES (9,'Berger',8000); INSERT INTO Customer VALUES (10,'D''Ascoli',6900); INSERT INTO Customer VALUES (11,'Padruz',1200); INSERT INTO Customer VALUES (12,'Hug',4900);

Ein Query dazu: <eval id="4c8fc83552820"> SELECT * FROM Customer; SELECT * FROM Customer,place WHERE Customer.place=place.code AND place.name='Berne' </eval>

Natürlich kann man auch CODE Boxen verwenden oder versteckten Code für das anlegen einer Datenbank 
mit Beispielinhalten usw.
Elemente, die in einer CODE Box hinterlegt sind, werden IMMER vor jedem EVAL mit ausgeführt. 
Damit eignen Sie sich prima, um Tabellen und Datensätze anzulegen:

<eval id="4c8fc8355292f"> SELECT * FROM Artikel;</eval>

SELECT * FROM Artikel WHERE A_Preis = (SELECT MAX(A.A_Preis) FROM Artikel As A)

Dieses Beispiel stammt von der Programming-Wiki-Seite von Michael Hielscher

Was ist passiert

Ein Gängstertrio hat am 23.08.2009 die Central Bank in New York ausgeräumt. Es waren Profis wie der Polizeichef bestätigte: "Hier waren ganz klar Kenner mit Erfahrung am Werk. Sie haben den Coup sicher schon über Wochen im Vorfeld geplant."

Die Hinweise

Von den Augenzeugen haben wir nur zwei Hinweise auf die drei männliche Täter erhalten:

1. Hinweis: 
Ein Täter bedrohte die Angestellten und wurde von einem Komplizen „Lie“ gerufen. 
Er soll mindestens 1'80 groß gewesen sein, zwischen 20 und 30 Jahre alt und trug 
einen auffallend chinesischen Kleidungsstil. 

2.Hinweis:
Eine Augenzeugin erinnert sich an das Fluchtfahrzeug. Es ist ein blauer oder 
schwarzer BMW gewesen sagt sie und das Kennzeichen soll auf 557 geendet haben.

Die Datenbank

Als FBI-Ermittler und Datenbank-Spezialist bist du für diesen Fall zuständig. Du hast bereits 100 Personen herausgesucht, die als Täter in Frage kommen könnten. Du hast Zugriff auf folgende Tabellen der FBI-Datenbank:

Die Spalten Vater, Mutter, Fahrzeughalter und Person sind Fremdschlüssel auf die Spalte ID der Tabelle Einwohner.

Hier kannst du einen Blick in die Datenbank werfen. Es werden jeweils 10 Datensätze angezeigt.

<eval id="4c8fc263e000b"> SELECT * FROM Einwohner AS Ausschnitt LIMIT 10; SELECT * FROM Vorstrafen AS Ausschnitt LIMIT 10; SELECT * FROM KFZ AS Ausschnitt LIMIT 10; SELECT * FROM Strafzettel AS Ausschnitt LIMIT 10; </eval>

SELECT * FROM Einwohner;
SELECT * FROM Vorstrafen;
SELECT * FROM KFZ;
SELECT * FROM Strafzettel

Die Aufgabe

Als FBI-Ermittler sollst du die Datenbank verwenden, um den Tätern auf die Spur zu kommen. Du kannst davon ausgehen, dass alle Täter in der Datenbank erfasst sind und eindeutig nach dem Ausschlussprinzip bestimmt werden können. Wie bereits vom Polizeichef erwähnt handelt es sich um Profis. Du kannst davon ausgehen, dass jeder Täter eine oder mehrere Vorstrafen besitzt.

Vorgehen:

• Skizziere dir zunächst ein Datenbankschema auf einem Blatt Papier und kennzeichne die Beziehungen zwischen den einzelnen Tabellen.
• Verwende geeignete SQL Anfragen, um die Datenbank zu durchsuchen.
• Notiere dir die Namen der drei Täter und Begründe deine Entscheidung. Durch logische Schlussfolgerungen kannst du den Täterkreis einengen.

<eval id="4a9790521d0b8"> // Gib hier deine SQL Befehle ein, um zu probieren und die 3 Täter zu bestimmen. </eval>

<eval id="4c8fc5e200452"> // Gib hier deine SQL Befehle ein... </eval>

<eval id="4c8fc5e2005a0"> // Gib hier deine SQL Befehle ein... </eval>

<eval id="4c8fc5e2006e3"> // Gib hier deine SQL Befehle ein... </eval>

Anlegen einer Tabelle Leser:

<eval id="4cac9c50ee28a"> select * from leser; </eval>

SELECT buch2.Schlagwort, buch1.Titel, buch1.Verlag, buch1.Jahr
FROM  buch1,  buch2 
WHERE buch1.T_NR = buch2.T_NR and ((buch2.Schlagwort)='LISP') ;

select T_Nr, Titel, Verlag, Aus_datum
from leser, ausleihe, bestand, buch1
where leser.L_NR=ausleihe.L_NR and ausleihe.reg_nr=bestand.reg_nr and bestand.T_nr=buch1.T_Nr    and L_Nr='001';

Außerdem wird eine Tabelle Buch1 benötigt, die die Angaben zu den Büchern enthält:

Lassen Sie sich die Inhalte der Tabelle buch1 anzeigen!

<eval id="4caca38a0afb3"></eval>

Im Folgenden sind noch die Tabellen buch2, bestand und ausleihe angelegt und mit Datensätzen gefüllt.
Lassen Sie sich ebenfalls die Inhalte anzeigen.

<eval id="4cacd69914d8d"> </eval>

Analysieren Sie die Tabellen und finden Sie die Beziehungen zwischen diesen heraus. (vergleiche hier)
Abschließend stellen Sie folgende Anfragen:

1. Welche Bücher beinhalten Ausführungen zu der Programmiersprache LISP?
2. Welche Bücher (T_Nr, Titel, Verlag) hat der Leser mit der L_Nr '001' wann ausgeliehen?

SELECT buch2.Schlagwort, buch1.Titel, buch1.Verlag, buch1.Jahr
FROM  buch1,  buch2 
WHERE buch1.T_NR = buch2.T_NR and ((buch2.Schlagwort)='LISP') ;

select T_Nr, Titel, Verlag, Aus_datum
from leser, ausleihe, bestand, buch1
where leser.L_NR=ausleihe.L_NR and ausleihe.reg_nr=bestand.reg_nr and bestand.T_nr=buch1.T_Nr and  L_Nr='001';

<eval id="4cacdaf17bbc6"></eval>