Lösung - WI

Datenbanksysteme 2
WS 11/12
Fachbereich Angewandte Informatik
Dipl.-Inf. Christian Pape
5. Übung
Aufgabe 1:
a.) Ihre Aufgabe ist es Studenten und Prüfungen in einer Datenbank zu verwalten. In der Tabelle Student wird nur der Name und Vorname, das
Geschlecht und die Matrikelnummer verwaltet. Die Prüfungsergebnisse
werden in einer seperaten Tabelle gespeichert. Dabei soll die Tabelle
STUDENT folgende Daten enthalten:
Spaltenname
MATRIKELNR
NAME
VORNAME
GESCHLECHT
Typ
Ganzzahl, Primärschlüssel
Zeichenkette, Länge 40
Zeichenkette, Länge 40
Zeichenkette, Länge 1
Um Falscheingaben zu vermeiden, sollen zusätzlich folgende benannten
Integritätsbedingungen definiert werden:
Name
CHK_MATRIKELNR
CHK_GESCHLECHT
Einschränkung
Werte in MATRIKELNR dürfen nur 6stellig sein
Die Spalte GESCHLECHT darf nur die
Werte ’m’ oder ’w’ annehmen
Lösung:
CREATE TABLE STUDENT (
MATRIKELNR INT PRIMARY KEY,
NAME VARCHAR(40) NOT NULL,
VORNAME VARCHAR(40) NOT NULL,
GESCHLECHT VARCHAR(1) NOT NULL,
CONSTRAINT CHK_MATRIKELNR
CHECK (MATRIKELNR>=100000 AND MATRIKELNR<=999999),
CONSTRAINT CHK_GESCHLECHT
CHECK (GESCHLECHT=’w’ OR GESCHLECHT=’m’)
);
Die Tabelle PRUEFUNG soll folgende Attribute enthalten:
Spaltenname
MATRIKELNR
LEHRVERANSTALTUNG
NOTE
VERSUCH
Typ
Ganzzahl
Zeichenkette, Länge 40
Dezimalzahl, eine Nachkommastelle
Ganzzahl
Auch hier werden zusätzlich benannte Integritätsbedingungen definiert:
Name
PK
CHK_VERSUCH
CHK_NOTE
FK_MATRIKELNR
Einschränkung
die Kombination (MATRIKELNR, VERSUCH, LEHRVERANSTALTUNG) soll
Primärschlüssel sein
das Attribut VERSUCH darf nur die
Werte 1,2 oder 3 annehmen
das Attribut NOTE darf nur folgende
Werte enthalten 1.0, 1.3, 1.7, 2.0, 2.3, 2.7,
3.0, 3.3, 3.7, 4.0, 5.0
das Attribut MATRIKELNR ist Fremdschlüssel auf die Spalte MATRIKELNR
aus Tabelle STUDENT
Lösung:
CREATE TABLE PRUEFUNG (
MATRIKELNR INT,
LEHRVERANSTALTUNG VARCHAR(40),
NOTE DECIMAL(2,1),
VERSUCH INT,
CONSTRAINT PK PRIMARY KEY(MATRIKELNR, VERSUCH, LEHRVERANSTALTUNG),
CONSTRAINT CHK_VERSUCH CHECK (VERSUCH IN (1,2,3)),
CONSTRAINT CHK_NOTE CHECK (NOTE IN
(1.0,1.3,1.7,2.0,2.3,2.7,3.0,3.3,3.7,4.0,5.0)),
CONSTRAINT FK_MATRIKELNR FOREIGN KEY (MATRIKELNR)
REFERENCES STUDENT(MATRIKELNR) ON DELETE CASCADE
);
b.) Nachdem Sie die Tabellen erstellt haben können Sie Testdaten einfügen,
um die Korrektheit Ihrer Integritätsbedingungen zu prüfen. Dies Daten
finden Sie unter:
http://wi-labor.informatik.hs-fulda.de/dbs2/fehlerhafte-studentendaten.sql
Wenn Ihre Integritätsbedingungen korrekt sind, werden alle bis auf
zwei Einfügeoperationen abgelehnt. Die Tabelle STUDENT sollte jetzt
nur einen Studenten Karl Dall enthalten bzw. in der Tabelle PRUEFUNG einen Eintrag zu diesem Studenten bzgl. dem Fach ’DBSII/C’
mit der Note 5,0.
MATRIKELN NAME VORNAME
GESCHLECHT
--------- ----- --------- ---------100000
Dall Karl
m
1 rows selected
MATRIKELNR LEHRVERANSTALTUNG NOTE VERSUCH
---------- ------------------ ---- ------100000
DBSII/C
5
1
Falls Sie an dieser Stelle mehr bzw. fehlerhafte Einträge haben, überprüfen Sie Ihre Tabellendefinitionen. Sie können die Tabellen entfernen
indem Sie folgende Befehle absetzen:
DROP TABLE STUDENT CASCADE CONSTRAINTS;
DROP TABLE PRUEFUNG CASCADE CONSTRAINTS;
c.) Falls alle Daten korrekt sind werden nun die "Produktivdaten" in die
Tabellen geschrieben. Laden Sie hierzu die Daten der Datei:
http://wi-labor.informatik.hs-fulda.de/dbs2/korrekte-studentendaten.sql
Die Tabelle PRUEFUNG sollte nun 33 Einträge, die Tabelle STUDENT 11 Einträge enthalten.
d.) Ihre Aufgabe ist nun die Anzahl der weiblichen bzw. männlichen Studenten zu bestimmen. Dazu führen Sie folgende Anfrage aus:
SELECT GESCHLECHT, COUNT(*) FROM STUDENT GROUP BY GESCHLECHT;
Hier möchten wir nun den Ausführungsplan untersuchen. Dafür geben
Sie bitte folgende Anfrage ein:
EXPLAIN PLAN SET STATEMENT_ID=’stmt1’ FOR
SELECT GESCHLECHT, COUNT(*) FROM STUDENT GROUP BY GESCHLECHT;
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, ’stmt1’,’TYPICAL’));
Über die Spalte GESCHLECHT ist bisher kein Index definiert. Wie
aus der Vorlesung bekannt, eignet sich für diesen Typ von Daten ein
Bitmap-Index. Erzeugen Sie nun einen solchen Index mit:
CREATE BITMAP INDEX IDX_STUDENT_GESCHLECHT ON STUDENT(GESCHLECHT);
Untersuchen Sie nun den Ausführungsplan erneut. Dieser sollte nun
weniger Kosten für diese Anfrage aufzeigen.
Aufgabe 2:
a.) Erzeugen Sie eine Sicht LISTE_ANZAHL, welche die Matrikelnummer,
Lehrveranstaltung und die Anzahl der Versuche aus der Tabelle PRUEFUNG anzeigt. Die Sicht sollte folgende Ausgabe generieren:
MATRIKELNR
---------------------100000
100001
100001
100002
100002
100003
100003
100004
100004
100005
100005
100006
100006
100007
100007
100008
100008
100009
100009
100010
100010
LEHRVERANSTALTUNG
---------------------------------------DBSII/C
DBSII/C
Prog I
DBSII/C
Prog I
DBSII/C
Prog I
DBSII/C
Prog I
DBSII/C
Prog I
DBSII/C
Prog I
DBSII/C
Prog I
DBSII/C
Prog I
DBSII/C
Prog I
DBSII/C
Prog I
ANZAHL
-----1
2
1
2
1
1
2
1
1
2
2
1
1
1
2
2
3
1
1
2
3
Lösung:
CREATE OR REPLACE VIEW LISTE_ANZAHL AS
SELECT MATRIKELNR, LEHRVERANSTALTUNG, MAX(VERSUCH) ANZAHL FROM
PRUEFUNG GROUP BY MATRIKELNR, LEHRVERANSTALTUNG;
b.) Als weiterer Schritt soll nun eine Anfrage formuliert werden, die die
durchschnittliche Anzahl von Versuchen bzgl. Geschlecht anzeigt. Hierbei sollte folgendes Ergebnis ausgegeben werden:
GESCHLECHT
---------w
m
Durchschnittliche Versuche
-------------------------1,5
1,61538461538461538461538461538461538462
Lösung:
SELECT GESCHLECHT, AVG(ANZAHL) AS "Durchschnittliche Versuche" FROM
STUDENT, LISTE_ANZAHL WHERE LISTE_ANZAHL.MATRIKELNR=STUDENT.MATRIKELNR
GROUP BY GESCHLECHT;
c.) Es sollen nun eine Sicht erstellt werden, welche die Versuche der Studenten bei Prüfungen nebeneinander anzeigt. Die Sicht soll also die
folgende Darstellung erzeugen:
MATRIKELNR
---------------------100000
100001
100001
100002
100002
100003
100003
100004
100004
100005
100005
100006
100006
100007
100007
100008
100008
100009
100009
100010
100010
LEHRVERANSTALTUNG
------------------DBSII/C
DBSII/C
Prog I
DBSII/C
Prog I
DBSII/C
Prog I
DBSII/C
Prog I
DBSII/C
Prog I
DBSII/C
Prog I
DBSII/C
Prog I
DBSII/C
Prog I
DBSII/C
Prog I
DBSII/C
Prog I
VERSUCHE
------------------1
2
1
2
1
1
2
1
1
2
2
1
1
1
2
2
3
1
1
2
3
VERSUCH1
------------------5
5
2,3
5
4
2,3
5
1
1,3
5
5
1
1
2,3
5
5
5
2,3
1,3
5
5
VERSUCH2
VERSUCH3
------------------- ---------------------3,3
1,7
3,3
1,3
2
1,7
1,3
5
3,3
2,3
5
5
Lösung:
CREATE OR REPLACE VIEW LISTE_VERSUCHE AS
SELECT
P1.MATRIKELNR,
P1.LEHRVERANSTALTUNG,
COUNT(*) VERSUCHE,
(SELECT P2.NOTE FROM PRUEFUNG P2 WHERE P1.MATRIKELNR=P2.MATRIKELNR AND
P1.LEHRVERANSTALTUNG=P2.LEHRVERANSTALTUNG AND P2.VERSUCH=1) "VERSUCH1",
(SELECT P2.NOTE FROM PRUEFUNG P2 WHERE P1.MATRIKELNR=P2.MATRIKELNR AND
P1.LEHRVERANSTALTUNG=P2.LEHRVERANSTALTUNG AND P2.VERSUCH=2) "VERSUCH2",
(SELECT P2.NOTE FROM PRUEFUNG P2 WHERE P1.MATRIKELNR=P2.MATRIKELNR AND
P1.LEHRVERANSTALTUNG=P2.LEHRVERANSTALTUNG AND P2.VERSUCH=3) "VERSUCH3"
FROM PRUEFUNG P1 GROUP BY P1.MATRIKELNR, P1.LEHRVERANSTALTUNG
ORDER BY VERSUCHE;