Übungsblatt 6 - Lösung - Informatik

Universität Augsburg, Institut für Informatik
Prof. Dr. W. Kießling
Dr. A. Huhn, M. Endres, T. Preisinger
WS 2008/2009
28. Nov. 2008
Lösungsblatt 6
Datenbanksysteme I
Aufgabe 1: Joins
a) Bestimmen Sie zu jedem Professor den geleisteten Lehrbeitrag als die Summe der Semesterwochenstunden der
von ihm/ihr gehaltenen Vorlesungen. Dabei sollen jedoch auch die Professoren ausgegeben werden, die gar keine
Vorlesung halten.
SELECT PersNr, Name, SUM(SWS)
FROM Professoren LEFT OUTER JOIN Vorlesungen
ON PersNr = gelesenVon
GROUP BY PersNr, Name;
b) Bestimmen Sie die Prüfungshäufigkeit von Vorlesungen. Listen Sie auch die Vorlesungen auf, die bisher nicht
geprüft wurden.
SELECT COUNT(p.VorlNr) AS wieoft, v.VorlNr, v.Titel
FROM prüfen p RIGHT OUTER JOIN Vorlesungen v
ON p.VorlNr = v.VorlNr
GROUP BY v.VorlNr, v.Titel;
c) Finden Sie die Kombinationen von Professoren und zugehörigen Assistenten. Listen Sie auch alle Professoren auf,
die keine Assistenten haben sowie alle Assistenten, die gegenwärtig keinem Professor zugeordnet sind.
SELECT p.PersNr, p.Name, a.PersNr, a.Name
FROM Professoren p FULL OUTER JOIN Assistenten a
ON a.Boss = p.PersNr;
Aufgabe 2: ER-Transformation nach SQL
CREATE TABLE Studenten
(MatrNr
INTEGER PRIMARY KEY REFERENCES prüfen(MatrNr),
Name
VARCHAR(30) NOT NULL,
Semester
INTEGER NOT NULL);
CREATE TABLE Professoren
(PersNr
INTEGER PRIMARY KEY REFERENCES prüfen(PersNr),
Name
VARCHAR(30) NOT NULL,
Rang
CHAR(2) CHECK (Rang in (’C2’, ’C3’, ’C4’)),
Raum
INTEGER NOT NULL);
CREATE TABLE prüfen
(MatrNr
PersNr
PRIMARY KEY
INTEGER NOT NULL REFERENCES Studenten,
INTEGER NOT NULL REFERENCES Professoren,
(MatrNr, PersNr));
Falls eine Entität optional ist, so existiert für diese Entität kein Fremdschlüssel zur Relationsship.
Anmerkung:
Obige CREATE TABLE Statements entsprechen dem SQL-92 Standard, werden aber von keiner Datenbank unterstützt. Für das Anlegen der entsprechenden Fremdschlüsselbeziehungen muss daher ADD CONSTRAINT verwendet werden. Allerdings ist es in Oracle nicht möglich Fremdschlüssel auf nicht UNIQUE-Attribute zu erzeugen.
So ist z.B. das Anlegen der Referenz REFERENCE prüfen (MatrNr) in der Relation Studenten selbst mit
ADD CONSTRAINT in Oracle nicht möglich, da MatrNr in prüfen nicht UNIQUE ist. Andere Datenbanken
unterstützen dies allerdings.
1
Aufgabe 3: SQL: DDL und DML
a) DDL:
CREATE TABLE Bundesländer
(Name VARCHAR(20) PRIMARY KEY, Einwohner INTEGER CHECK (Einwohner > 0));
CREATE TABLE Wahlkreise
(Nr INTEGER PRIMARY KEY, Bezeichnung VARCHAR(20),
Bundesland VARCHAR(20) REFERENCES Bundesländer);
CREATE TABLE Wahlbezirke
(Nr INTEGER PRIMARY KEY,
Wahlberechtigte INTEGER CHECK (Wahlberechtigte > 0),
Wahllokal VARCHAR(20),
Wahlkreis INTEGER REFERENCES Wahlkreise ON DELETE CASCADE);
CREATE TABLE Parteien
(Name VARCHAR(20) PRIMARY KEY, Mitglieder INTEGER CHECK (Mitglieder > 0));
CREATE TABLE Direktkandidaten
(SozialVNr VARCHAR(20) PRIMARY KEY, Name VARCHAR(20),
Partei VARCHAR(10) REFERENCES Parteien ON DELETE SET NULL,
Wahlkreis INTEGER REFERENCES Wahlkreise ON DELETE CASCADE);
CREATE TABLE Erststimmen
(Wahlbezirk INTEGER NOT NULL REFERENCES Wahlbezirke ON DELETE CASCADE,
Jahr INTEGER NOT NULL,
Kandidat VARCHAR(20) NOT NULL REFERENCES Direktkandidaten ON DELETE CASCADE,
Stimmen INTEGER CHECK (Stimmen >=0), PRIMARY KEY (Wahlbezirk, Jahr, Kandidat));
CREATE TABLE Zweitstimmen
(Wahlbezirk INTEGER NOT NULL REFERENCES Wahlbezirke ON DELETE CASCADE,
Jahr INTEGER NOT NULL,
Partei VARCHAR(10) NOT NULL REFERENCES parteien ON DELETE CASCADE,
Stimmen INTEGER CHECK (Stimmen >=0), PRIMARY KEY(Wahlbezirk, Jahr, Partei));
b) DML:
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
Bundesländer VALUES(’Bayern’, 12000000);
Wahlkreise VALUES(153, ’Oberbach’, ’Bayern’);
Wahlbezirke VALUES(21921, 6700, ’Bürgerhaus’, 153);
Parteien VALUES(’P1’, 580000);
Direktkandidaten VALUES(’2005-DK02’, ’Huhn’, ’P1’, 153);
Erststimmen VALUES(21921, 2005, ’2005-DK02’, 750);
Zweitstimmen VALUES(21921, 2005, ’P1’, 535);
Aufgabe 4: Zyklische Fremdschlüsselbeziehungen
create table chicken(cID integer primary key);
create table egg(
eID integer primary key,
cID integer constraint FKEC references chicken deferrable);
alter table chicken
add eID integer constraint FKCE references egg deferrable;
2
set constraints FKEC, FKCE deferred;
insert into chicken values (1,2);
insert into egg values (2,1);
commit;
Aufgabe 5: Views
Ein wichtiges Konzept, um ein DB-System an Benutzerbedürfnisse anzupassen sind sog. Views.
a) Erstellen Sie einen View StudProf, der Studenten mit den Professoren assoziiert, bei denen sie Vorlesungen gehört
haben. Formulieren Sie anschließend eine SQL-Anweisung, die herausfindet, in welchen Semestern die Studenten
von Sokrates sind.
CREATE VIEW StudProf(SName, Semester, Titel, PName) AS
SELECT s.Name, s.SEmester, v.Titel, p.Name
FROM Studenten s, hören h, Vorlesungen v, Professoren p
WHERE s.MatrNr = h.MatrNr
AND h.VorlNr = v.VorlNr
AND v.gelesenVon = p.PersNr;
SELECT DISTINCT Semester
FROM StudProf
WHERE PName = ’Sokrates’;
b) Formulieren Sie eine Anfrage, die eine Liste aller Professoren liefert, die in den nächsten 180 Tagen Geburtstag
haben. Gehen Sie dazu wie folgt vor:
1) Erweiteren Sie die Relation Professoren in Ihrem Oracle-Account um ein Attribut GebDatum vom Datentyp
date.
2) Ergänzen Sie folgende Daten. Verwenden Sie im Umgang mit dem Datum die Datumsfunktion TO DATE.
ALTER TABLE Professoren ADD GebDatum DATE;
UPDATE Professoren SET GebDatum = NULL WHERE PersNr = 2125;
UPDATE Professoren SET GebDatum = TO_DATE(’18-MAI-1872’, ’DD-MM-YYYY’)
WHERE PersNr = 2126;
UPDATE Professoren SET GebDatum = TO_DATE(’24-MAI-1543’, ’DD-MM-YYYY’)
WHERE PersNr = 2127;
UPDATE Professoren SET GebDatum = TO_DATE(’28-JUL-1902’, ’DD-MM-YYYY’)
WHERE PersNr = 2133;
UPDATE Professoren SET GebDatum = TO_DATE(’13-NOV-354’, ’DD-MM-YYYY’)
WHERE PersNr = 2134;
UPDATE Professoren SET GebDatum = TO_DATE(’07-NOV-1867’, ’DD-MM-YYYY’)
WHERE PersNr = 2136;
UPDATE Professoren SET GebDatum = TO_DATE(’22-APR-1724’, ’DD-MM-YYYY’)
WHERE PersNr = 2137;
3
3) Definieren Sie einen View NextBirthday, der u.a. ein Attribut Geburtstag beinhaltet, welches das vollständige
Datum des nächsten fälligen Geburtstages enthält (z.B. feiert Kopernikus erst am 24. Mai 2009 wieder Geburtstag). Ggf. sind Befehle wie SYSDATE oder ADD MONTHS hilfreich.
CREATE VIEW Birthday AS
SELECT PersNr, Name, Rang, Raum, GebDatum,
GebDatum TRUNC(TO_DATE(GebDatum, ’DD-MM-YYYY’), ’YEAR’) +
TRUNC(TO_DATE(SYSDATE, ’DD-MM-YYYY’), ’YEAR’)
AS Birthday
FROM Professoren;
CREATE VIEW NextBirthday AS
SELECT PersNr, Name, Rang, Raum, GebDatum, Birthday AS Geburtstag
FROM Birthday
WHERE Birthday >= SYSDATE
UNION
SELECT PersNr, Name, Rang, Raum, GebDatum,
ADD_MONTHS(Birthday, 12) AS Geburtstag
FROM Birthday
WHERE Birthday < SYSDATE;
4) Finden Sie nun unter zu Hilfenahme von NextBirthday alle Professoren, die in den nächsten 180 Tagen Geburtstag haben. Geben Sie auch aus, welches Datum in 180 Tage sein wird.
SELECT PersNr, Name, Geburtstag, SYSDATE + 180
FROM NextBirthday
WHERE TO_DATE(Geburtstag) <= TO_DATE(SYSDATE + 180);
4