Grundlagen Datenbanken

 Grundlagen Datenbanken Sommersemester 2008 Prof. Dr. Gregor Engels Jan Bals, Carsten Kemper, Hendrik Voigt Rechnerübung SQL Installation SQL‐Server: SQLite ist eine SQL Datenbank, die keine Installation erfordert und direkt über die Kommandozeile angesprochen werden kann. Geht auf http://www.sqlite.org/download.html und ladet das für Euer System (Linux, Mac, Windows) passende vorkompilierte Kommandozeilen‐Programm. Ein ausführliche Dokumentation zu SQLite findet ihr unter: http://www.sqlite.org/docs.html Achtet auf SQL‐Befehle, die nicht unterstützt werden. ANY und ALL fehlen beispielsweise. Extrahiert das Kommandozeilen‐Programm aus dem heruntergeladenen Archiv und legt es zusammen mit der Filmdatendatenbank (film.db) in ein Verzeichnis. Starten des SQL‐Servers: Hängt den Namen der Datenbank (film.db) einfach beim Starten des Kommandozeilen‐
Programms einfach hinten an. Der Screenshot zeigt wie der Aufruf unter Windows aussieht: Beenden des SQL‐Servers: Um den Server zu beenden, gibt man auf der SQLite Konsole (sqlite>) den Befehl „.exit“ ein. Grundlagen Datenbanken Sommersemester 2008 Prof. Dr. Gregor Engels Jan Bals, Carsten Kemper, Hendrik Voigt SQL‐Anfragen an den SQL‐Server: SQL‐Anfragen können nun direkt auf der SQLite‐Konsole eingegeben werden. Eine Anfrage kann dabei über mehrere Zeilen gehen und wird immer mit dem Semikolon abgeschlossen. Wer lieber mit einer grafischen Oberfläche arbeitet kann für Firefox ein Add‐On names „SQLite Manager“ installieren (http://addons.mozilla.org/en‐US/firefox/addon/5817). Grundlagen Datenbanken Sommersemester 2008 Prof. Dr. Gregor Engels Jan Bals, Carsten Kemper, Hendrik Voigt Aufbau der Datenbank: Person = {PID,Name} KPerson={{PID}} Land = {LID,Land} KLand={{LID}} Genre = {GID,Land} KGenre={{GID}} Film = {FID,Wertung,Titel,Originaltitel,Jahr,LID,GID,Dauer} KFilm={{FID}} Film(GID)→ Genre(GID) Film(LID) → Land(FID) Darsteller = {FID,PID} KDarsteller={{FID,PID}} Darsteller(PID)→ Person(PID) Darsteller(FID) → Film(FID) Regie = {FID,PID} KRegie={{FID,PID}} Regie(PID)→ Person(PID) Regie(FID) → Film(FID) Produzent = {FID,PID} KProduzent={{FID,PID}} Produzent(PID)→ Person(PID) Produzent(FID) → Film(FID) Inhalt = {FID,Inhalt} KInhalt={{FID}} Inhalt(FID)→ Film(FID) URL = {FID,URL} KURL={{FID}} URL(FID)→ Film(FID) Zusätzlich könnt ihr über den Befehl .schema auf der SQLite Konsole die Struktur der Tabellen abfragen. Grundlagen Datenbanken Sommersemester 2008 Prof. Dr. Gregor Engels Jan Bals, Carsten Kemper, Hendrik Voigt Formuliert nun SQL‐Ausdrücke für die folgenden Anfragen: 1. Welche Filme(Titel) haben die längste Dauer? 2. Wie viele Regisseure gibt es? 3. Welche Darsteller(Name) haben an mehr als 10 Filmen mitgewirkt? 4. An welchen Filmen(Titel) haben mehr als 25 Darsteller mitgewirkt? 5. Wer (Name) hat an allen Filmen, die eine Wertung von 5,9 haben und deren Dauer länger als 130 Minuten ist, als Darsteller mitgewirkt? 6. Wer(Name) hat an einem Film sowohl als Produzent, Darsteller und Regisseur mitgewirkt? 7. Wer(Name) hat an 7 oder mehr Filmen in irgendeiner Funktion (Darsteller, Regie, Produzent) teilgenommen? 8. In welchen Filmen (Titel) kommt der Begriff „Golf“ in der Inhaltbeschreibung vor? 9. Welche Produzentenpaare(Name Produzent 1, Name Produzent 2) haben mehr als 2 Filme gemeinsam produziert? 10. Wer(Name) war sowohl mal Schauspieler als auch Produzent? 11. Welche Filme (Titel) waren im Jahr 2001 die kürzesten? Grundlagen Datenbanken Sommersemester 2008 Prof. Dr. Gregor Engels Jan Bals, Carsten Kemper, Hendrik Voigt Mögliche Lösungen: Anfrage 1: SELECT f.Titel,f.Dauer FROM
Film f WHERE NOT EXISTS (SELECT *
FROM Film g WHERE g.Dauer > f.Dauer)
SELECT f.Titel,f.Dauer FROM Film f WHERE f.Dauer = (SELECT
MAX(g.Dauer) FROM Film g) Anfrage 2: SELECT COUNT(*) FROM (SELECT DISTINCT PID FROM Regie); Anfrage 3: SELECT Name FROM Person NATURAL JOIN Darsteller GROUP BY PID HAVING
COUNT(FID)>10; SELECT Name FROM Person NATURAL JOIN (SELECT PID FROM Darsteller
GROUP BY PID HAVING COUNT(FID) > 10);
Anfrage 4: SELECT Titel FROM Film NATURAL JOIN (SELECT * FROM Darsteller GROUP
BY FID HAVING COUNT(PID) > 25);
Anfrage 5: SELECT p.Name FROM Person p WHERE NOT EXISTS (SELECT * FROM Film f
WHERE f.Dauer>130 AND f.Wertung=5.9 AND NOT EXISTS (SELECT * FROM
Darsteller d WHERE d.FID = f.FID AND d.PID=p.FID));
(Für welchen Darsteller existieren keine Filme, an denen er nicht mitwirkt?) Anfrage 6: SELECT DISTINCT Name FROM Person NATURAL JOIN(SELECT PID FROM(
SELECT * FROM Darsteller INTERSECT SELECT * FROM Regie INTERSECT
SELECT * FROM Produzent));
Anfrage 7: SELECT DISTINCT Name FROM Person NATURAL JOIN (SELECT PID
FROM(SELECT * FROM Darsteller UNION SELECT * FROM Regie UNION SELECT
* FROM Produzent) GROUP BY PID HAVING COUNT(FID) >=7);
Anfrage 8: SELECT Titel FROM Film NATURAL JOIN Inhalt WHERE Inhalt LIKE
"%Golf%";
Anfrage 9: SELECT p1.name, p2.name FROM Produzent pr1 NATURAL JOIN Person p1,
Grundlagen Datenbanken Sommersemester 2008 Prof. Dr. Gregor Engels Jan Bals, Carsten Kemper, Hendrik Voigt Produzent pr2 NATURAL JOIN Person p2 WHERE pr1.fid=pr2.fid AND
p1.pid<p2.pid GROUP BY p1.name,p2.name HAVING COUNT(pr1.fid) > 2;
Anfrage 10: SELECT name FROM (SELECT pid FROM Produzent INTERSECT SELECT pid
FROM Darsteller) NATURAL JOIN Person; Anfrage 11: SELECT f.Titel FROM Film f WHERE f.Jahr=2001 AND f.dauer = (SELECT
MIN(g.Dauer) FROM Film g WHERE g.Jahr=2001); SELECT f.Titel FROM Film f WHERE f.Jahr=2001 AND NOT EXISTS ( SELECT
g.Titel, g.Dauer FROM film g WHERE g.Jahr=2001 AND NOT
g.dauer>=f.dauer);
SELECT f.Titel FROM Film f WHERE f.Jahr=2001 AND NOT EXISTS ( SELECT
g.Titel, g.Dauer FROM film g WHERE g.Jahr=2001 AND g.dauer<f.dauer);