Übungen zu Datenbanksysteme

Institut für Informatik
Prof. Dr. Oliver Vornberger
Nils Haldenwang, M.Sc.
Universität Osnabrück, 12.05.2015
http://www-lehre.inf.uos.de/~dbs
Testat bis 03.06.2015, 14:00 Uhr
Übungen zu Datenbanksysteme
Sommersemester 2015
Blatt 5: SQL
Hinweis zum Bearbeitungszeitraum
Dieses Aufgabenblatt hat auf Grund der Feiertage doppelten Umfang. Die Testate zu diesem Aufgabenblatt finden in der Woche vom 01.06. - 03.06. statt. Davor gibt es keine Testate. Die Übung zu
diesem Aufgabenblatt findet am 21.05. statt. Sowohl am 14.05., als auch am 28.05. findet damit keine
Übung statt. Die Vorlesung fällt ebenfalls sowohl am 25.05. als auch 26.05. aus.
Das Thema dieses Aufgabenblattes ist eines der Kernthemen der gesamten Veranstaltung und das Formulieren von Queries in SQL eine wesentliche Kompetenz, die Sie am Ende der Vorlesung gemeistert
haben sollten. Die Bearbeitungszeit wird auf diesem Aufgabenblatt deutlich höher sein, als auf den
bisherigen - nehmen Sie dies nicht auf die leichte Schulter.
Aufgabe 5.1: SQL-Grundlagen (6 Punkte)
Auf diesem Aufgabenblatt sollen Sie sich mit der Abfragesprache SQL beschäftigen. Dazu steht Ihnen
der MySQL-Datenbankserver dbs2.informatik.uos.de unter Port 3306 zur Verfügung. Mit
dem Benutzer erika und dem Passwort mustermann erhalten Sie Zugriff auf die Datenbanken
UniWeb und imdb.
Loggen Sie sich zum Beantworten der folgenden Fragen über die Kommandozeile von einem
der CIP-Rechner auf dem MySQL-Server ein. Geben Sie dazu mysql -u erika -p -h
dbs2.informatik.uos.de ein.
1. Welches SQL-Statement listet alle Datenbanken auf? Welche Datenbanken genau werden damit
aufgelistet?
2. Mit welchem SQL-Statement wechseln Sie in die Datenbank UniWeb?
3. Welches SQL-Statement listet die Tabellen in der Datenbank UniWeb auf?
4. Welches SQL-Statement listet die Namen und sonstigen Informationen der Spalten der Tabelle
Professoren auf?
Musterlösung:
1. show databases; Dieser Befehl listet alle Datenbanken auf, auf die der eingeloggte Nutzer
mindestens lesenden Zugriff hat.
2. use UniWeb;
3. show tables;
4. show columns from Professoren;
Aufgabe 5.2: SQL-Queries 1 (14 Punkte)
Die folgenden Aufgaben können Sie mit der Web-Schnittstelle phpMyAdmin oder dem Programm
MySQL Workbench lösen. Notieren Sie alle formulierten Queries dieser und der folgenden Aufgaben
in einer Textdatei, die Sie im Anschluss an Ihr Testat Ihrem Tutor zukommen lassen.
Formulieren Sie die folgenden acht Abfragen in SQL für die Datenbank UniWeb und versuchen Sie
Ihre Ergebnisse anhand der überschaubaren Daten nachzuvollziehen.
1. Wie lauten die Namen aller Assistenten?
2. Wie lauten die Namen der Studierenden, die nach 1975 geboren sind?
3. Für welchen Professor arbeitet Platon?
4. Welche Studenten hören die Vorlesung Logik?
5. Welche Vorlesungen setzen die Vorlesung Ethik voraus und welche Professoren lesen sie?
6. Wie hoch ist die durchschnittliche Lehrbelastung eines Professors?
7. Welche Studenten haben welche Vorlesungen gehört, für die ihnen die direkten Voraussetzungen fehlen?
8. Wie viele Semesterwochenstunden hat jeder Student insgesamt mit dem Hören von Vorlesungen
verbracht? Geben Sie das Ergebnis absteigend nach Semesterwochenstunden sortiert aus.
Musterlösung:
1. select Name from Assistenten;
2. select * from Studenten where GebDatum > ’1945-01-01’; (7 Records)
3. select Professoren.Name from Assistenten, Professoren where
Assistenten.Boss = Professoren.PersNr and Assistenten.Name =
’Platon’; (Sokrates)
4. select s.Name from Vorlesungen v, hoeren h, Studenten s where
v.Titel = ’Logik’ and h.VorlNr = v.VorlNr and h.MatrNr =
s.MatrNr; (Fichte, Schopenhauer, Carnap)
2
5. select v2.Titel, p.Name from Vorlesungen v1, Vorlesungen v2,
voraussetzen vr, Professoren p where v1.Titel = ’Ethik’ and
vr.Vorgaenger = v1.VorlNr and vr.Nachfolger = v2.VorlNr and
v2.gelesenVon = p.PersNr; (Wissenschaftstheorie und Bioethik von Russel)
6. select avg(sum) from (select sum(SWS) as sum from Vorlesungen
group by gelesenVon) s; (6.0)
7. select distinct s.Name, vl.Titel from Vorlesungen vl, Studenten
s, hoeren h, voraussetzen v where h.MatrNr = s.MatrNr and
v.Nachfolger = h.VorlNr and vl.VorlNr = v.Nachfolger and
v.Vorgaenger not in (select h2.VorlNr from hoeren h2 where
h2.MatrNr = s.MatrNr); (5 Studenten, 6 ohne distinct bzw. mit Vorlesung)
8. select s.Name, sum(v.SWS) as sum from Vorlesungen v, hoeren h,
Studenten s where s.MatrNr = h.MatrNr and h.VorlNr = v.VorlNr
group by s.MatrNr order by sum desc; (6 Records, 18, 10, 8, 6, 6, 2)
Aufgabe 5.3: SQL-Queries 2 (30 Punkte)
Wechseln Sie in die Datenbank imdb. Hier finden Sie den (Obacht: unzensierten!) Datenbestand der
Internet Movie Database wie unter http://www.imdb.com/interfaces zu beziehen in ein
relationales Datenbankschema überführt. Ziel dieser Aufgabe ist es, sich mit einem praxisrelevanten Datenbankschema auseinanderzusetzen und Anfragen für ein Beispiel nicht-akademischer Natur
zu formulieren. Die Datenbank hat knapp 75 Millionen Einträge, Ihre Anfragen können also unter
Umständen in ihrer Antwortzeit im Sekundenbereich liegen.
Machen Sie sich zunächst mit dem Schema der Datenbank vertraut und formulieren Sie anschließend
die folgenden Abfragen in SQL. Hinweis: Versuchen Sie Ihre Anfragen schrittweise aufzubauen.
1. Liefern Sie die Titel aller Filme des Produktionsjahres 1942 in alphabetischer Reihenfolge.
Hinweis: Casablanca sollte in der Ergebnismenge vorkommen.
2. Geben Sie alle Filme in denen Sean Connery (Connery, Sean) mitgewirkt hat mit ihrem Produktionsjahr sortiert nach diesem zurück.
3. Geben Sie alle Filme in denen der Charakter James Bond vorkommt mit ihrem Produktionsjahr
sortiert nach diesem zurück.
4. In wie vielen Filmen wird der Film The Godfather (dt. Der Pate) aus dem Jahr 1972 referenziert?
5. Wie viele Votes (also Stimmabgaben) hat jeder Film, für den es Informationen zur Stimmabgabe
gibt, im Durchschnitt erhalten?
6. Geben Sie die 100 schlechtesten Filme (laut Rating) mit ihrer Bewertung zurück, die mindestens
5000 Votes erhalten haben. Hinweis: Das Ergebnis beginnt mit dem dem Film Justin Bieber:
Never Say Never.
7. Liefern Sie die 100 besten Filme mit einem Rating von 8 oder mehr mit 5000 oder mehr Votes
mit ihrem Produktionsbudget, ihrem Produktionsjahr und ihrem deutschen Titel zurück. Die
Verurteilten wird als bester Film zurückgeliefert, der 100. hat ein Rating von 8.3
3
8. Liefern Sie alle Filme, von denen es mindestens ein Remake gibt und die besser sind (laut Rating) als alle ihre Remakes. Hinweis: The Karate Kid sollte in der Ergebnismenge vorkommen.
Musterlösung:
1. select t.title from title t where t.production_year = 1942 and
kind_id = 1 order by t.title; (2176 Records)
2. select distinct t.title, t.production_year from title t, name
n, cast_info c where n.name = ’Connery, Sean’ and c.person_id
= n.id and c.movie_id = t.id and t.kind_id = 1 order by
t.production_year; (92 Records, 1954 bis 2012)
3. select distinct t.title, t.production_year from title t,
char_name n, cast_info c where n.name = ’James Bond’ and
c.person_role_id = n.id and c.movie_id = t.id and t.kind_id
= 1 order by t.production_year; (51 Records)
4. select count(*) from title t1, title t2, movie_link l where
t1.title = ’The Godfather’ and t1.production_year = 1972
and l.movie_id = t1.id and l.linked_movie_id = t2.id and
link_type_id = 6 and t2.kind_id = 1; (317)
5. select avg(sum) from (select sum(info) as sum from
movie_info_idx where info_type_id = 100 group by movie_id) s;
(773... streng genommen müsste man noch mit title joinen, um nur Filme zu berücksichtigen.)
6. select t.title, i1.info from title t, movie_info_idx
i1, movie_info_idx i2 where i1.info_type_id = 101 and
i2.info_type_id = 100 and i2.info >= 5000 and t.id =
i1.movie_id and i1.movie_id = i2.movie_id and t.kind_id = 1
group by t.id order by i1.info asc limit 0,100; (100ster ist Stan
Helsing)
7. select t.title, aka.title, i.info, i1.info from title
t, aka_title aka, movie_info_idx i1, movie_info_idx i2,
movie_info i where i1.info_type_id = 101 and i1.info >= 8
and i2.info_type_id = 100 and i2.info >= 5000 and t.id =
i1.movie_id and i1.movie_id = i2.movie_id and i2.movie_id =
i.movie_id and i.info_type_id = 105 and aka.movie_id = t.id and
t.kind_id = 1 and aka.note like ’%German%’ group by t.id order
by i1.info desc limit 0,100;
8. select t.title, t.production_year from title t, movie_info_idx
i where i.info_type_id = 101 and i.movie_id = t.id and
t.kind_id = 1 and exists (select i2.info from movie_info_idx
i2, movie_link l where l.movie_id = t.id and l.linked_movie_id
= i2.movie_id and l.link_type_id = 4) and i.info > all
(select i3.info from movie_info_idx i3, movie_link l2
4
where i3.info_type_id = 101 and l2.movie_id = t.id and
l2.linked_movie_id = i3.movie_id and l2.link_type_id = 4);
Aufgabe 5.4: Tabellen und Fremdschlüssel (30 Punkte)
Überführen Sie das folgende Datenbankschema einer Verkaufsplattform in ein (verfeinertes) relationales Schema für die Ihnen zur Verfügung gestellte MySQL-Datenbank. Ihre persönliche Datenbank ist auf dem Server dbs2.informatik.uos.de zu erreichen. Loggen Sie sich mit Ihrem
RZ-Nutzernamen und als Passwort Ihrer Matrikelnummer ein. Damit erhalten Sie Zugriff auf eine
Datenbank, die genauso wie Ihr Nutzername heißt.
Achten Sie bei der Überführung darauf, dass Sie auf Datenbankebene soweit möglich und sinnvoll die
referentielle Integrität Ihrer Daten sicherstellen. Verwenden Sie hierzu FOREIGN KEY-Constraints.
Damit die Datenbank diese versteht, müssen Sie die InnoDB-Engine verwenden, ergänzen Sie Ihre
CREATE TABLE-Statements dazu um das passende Schlüsselwort: CREATE TABLE xy (...)
ENGINE = INNODB;.
Beachten Sie außerdem folgende Hinweise: In der Relation invoices sollen für das Attribute
status nur die Werte posted, paid, packing, shipped erlaubt sein - stellen Sie hier
mit Hilfe des Datentyps ENUM statische Integrität sicher.
Beim Löschen von Nutzern, Produkten oder Herstellern dürfen – um beispielsweise eine saubere
Buchführung zu garantieren – weder Rechnungen noch die bestellten Produkte gelöscht werden.
Achten Sie darauf, die gleichen Datentypen und Namen für Ihre Tabellen und Relationen zu verwenden wie im ER-Diagramm angegeben, damit Sie die Folgeaufgaben lösen können. Bei Beziehungen
5
ist nach dem Doppelpunkt der zu verwendende Name der Fremdschlüssel angegeben.
Musterlösung:
CREATE TABLE users (
email VARCHAR( 255 ) NOT NULL ,
password VARCHAR( 255 ) NOT NULL ,
firstname VARCHAR( 255 ) NOT NULL ,
lastname VARCHAR( 255 ) NOT NULL ,
birthdate DATE NOT NULL ,
PRIMARY KEY ( email )
) ENGINE = InnoDB;
CREATE TABLE addresses (
id INT( 32 ) NOT NULL ,
street VARCHAR( 255 ) NOT NULL ,
number VARCHAR( 16 ) NOT NULL ,
zipcode INT( 32 ) NOT NULL ,
city VARCHAR( 255 ) NOT NULL ,
user VARCHAR( 255 ) NOT NULL,
PRIMARY KEY ( id ),
FOREIGN KEY(user) REFERENCES users(email) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = InnoDB;
CREATE TABLE manufacturers (
name VARCHAR( 255 ) NOT NULL ,
infourl TEXT ,
PRIMARY KEY ( name )
) ENGINE = INNODB;
CREATE TABLE products (
id INT( 32 ) NOT NULL ,
name VARCHAR( 255 ) NOT NULL ,
manufacturer VARCHAR( 255 ) ,
price DOUBLE NOT NULL ,
description TEXT ,
PRIMARY KEY ( id ),
FOREIGN KEY (manufacturer) REFERENCES manufacturers(name) ON UPDATE CASCADE ON DEL
) ENGINE = INNODB;
CREATE TABLE invoices (
invoiceno INT( 32 ) NOT NULL,
user VARCHAR( 255 ) ,
date DATE NOT NULL ,
status ENUM (’posted’, ’packing’, ’shipped’, ’paid’) ,
address INT ( 32 ) ,
PRIMARY KEY ( invoiceno ),
6
FOREIGN KEY (user) REFERENCES users(email) ON UPDATE CASCADE ON DELETE SET NULL,
FOREIGN KEY (address) REFERENCES addresses(id) ON UPDATE CASCADE ON DELETE SET NUL
) ENGINE = INNODB;
CREATE TABLE orders (
invoiceno INT( 32 ) NOT NULL ,
productid INT( 32 ) NOT NULL ,
price DOUBLE NOT NULL ,
quantity INT( 32 ) NOT NULL,
PRIMARY KEY (invoiceno, productid),
FOREIGN KEY (invoiceno) REFERENCES invoices(invoiceno) ON UPDATE CASCADE,
FOREIGN KEY (productid) REFERENCES products(id) ON UPDATE CASCADE
) ENGINE = INNODB;
Aufgabe 5.5: Views (12 Punkte)
Fügen Sie für die folgenden Aufgaben die Beispieldaten aus der anhängenden Datei
verkaufsdaten.sql in Ihre Datenbank ein. Sollte dies nicht möglich sein, überprüfen Sie Ihr
Datenbankdesign aus Aufgabe 1.
In der Tabelle invoices sind die Rechnungen für Kunden gespeichert. Da zu einer Rechnung mehrere Produkte gehören, muss der Gesamtwert einer Bestellung mit Hilfe der zugehörigen Tabelle
orders ermittelt werden. Ergänzen Sie Ihre Datenbank um eine View, die das Layout der Tabelle
invoices um Felder für die Anzahl der bestellten Produkte, den Gesamtrechnungsbetrag und die
prozentuale Differenz des Rechnungsbetrages zum derzeitigen Produktwert laut Tabelle products
erweitert.
Musterlösung:
CREATE VIEW invoices_information as
select i.*, count(*) as anzahl, sum(o.price * o.quantity) as rechnungsbetrag,
sum(o.price * o.quantity) / sum(p.price * o.quantity) * 100 - 100 as prozentabweic
from invoices i, products p, orders o where o.invoiceno = i.invoiceno and
o.productid = p.id group by i.invoiceno;
Aufgabe 5.6: Stored Function (8 Punkte)
Die Preisangaben der Produkte der Verkaufsplattform seien in Euro angegeben. Zu Vergleichszwecken
wird häufig eine Umrechnung in US-Dollar benötigt. Schreiben Sie eine Stored Function, die einen
Preis in Euro in US-Dollar umrechnet. Erweitern Sie die in Aufgabe 2 angelegte View um Angaben
in US-Dollar auf Basis Ihrer Stored Function.
Musterlösung:
CREATE FUNCTION eur2usd(amount double)
RETURNS double
BEGIN
7
return 1.31 * amount;
END|
CREATE VIEW invoices_information as
select i.*, count(*) as anzahl, sum(o.price * o.quantity) as rechnungsbetrag, eur2
sum(o.price * o.quantity) / sum(p.price * o.quantity) * 100 - 100 as prozentabweic
from invoices i, products p, orders o where o.invoiceno = i.invoiceno and
o.productid = p.id group by i.invoiceno;
8