Übungsblatt 10 - Technische Universität Kaiserslautern

Informationssysteme
SS 2013 – Übungsblatt 10
Prof. Dr.-Ing. Stefan Deßloch
AG Heterogene Informationssysteme
Fachbereich Informatik
Technische Universität Kaiserslautern
Übungsblatt 10
Für die Übungen in der Woche vom 24. bis 28. Juni 2013
Aufgabe 1: Abbildung vom ERM auf SQL-DDL
Bilden Sie folgendes Entity-Relationship-Modell möglichst präzise auf das SQL-Datenmodell ab.
Wählen Sie geeignete SQL-Typen. Welche Elemente des ERM können mit den aus der Vorlesung
bekannten Mitteln nicht in SQL abgebildet werden?
Vorname
AthNr
GebDat
Bezeichnung
Nachname
Athlet
1 [0;1]
n [0;*]
m [0;*]
für
n [0;*]
Disziplin
DiszNr
n [0;*]
m [0;*]
Mitglied
in
nimmt_
teil
Resultat
in
p [1;*]
m [1;*]
m [1;*]
Name
Ort
Verein
n [0;*]
ausgerichtet
von
Gründung
1
Startplätze
1 [1;1]
Wettkampf
Titel
Datum
Informationssysteme
SS 2013 – Übungsblatt 10
Aufgabe 2: Rekonstruktion eines E/R-Diagramms am Beispiel „Universität“
Rekonstruieren Sie aus dem nachfolgenden SQL-Schema das zugehörige Entity-RelationshipDiagramm Geben Sie zusätzlich die Kardinalitätszahlen so genau wie möglich an.
CREATE TABLE PROF (
PNR
INT,
VORNAME
VARCHAR(30),
NACHNAME
VARCHAR(30),
PRIMARY KEY (PNR))
CREATE TABLE VORLESUNG (
VNR
INT,
NAME
CHAR(20),
GELESEN_VON
INT
NOT NULL,
PRIMARY KEY (VNR),
FOREIGN KEY (GELESEN_VON) REFERENCES PROF)
CREATE TABLE LEHRBUCH (
LBNR
CHAR(20),
TITEL
VARCHAR(50),
UNTERSTUETZT
INT,
EMPFOHLEN_VON
INT
UNIQUE,
PRIMARY KEY (LBNR),
FOREIGN KEY (UNTERSTUETZT) REFERENCES VORLESUNG,
FOREIGN KEY (EMPFOHLEN_VON) REFERENCES PROF)
CREATE TABLE STUDENT (
MATNR
INT,
VORNAME
VARCHAR(30),
NACHNAME
VARCHAR(30),
KAUFT
CHAR(20)
NOT NULL,
PRIMARY KEY (MATNR),
FOREIGN KEY (KAUFT) REFERENCES LEHRBUCH)
CREATE TABLE VORLESUNGSTEILNAHME (
VNR
INT,
MATNR
INT,
DATUM
DATE,
PRIMARY KEY (VNR, MATNR),
FOREIGN KEY (VNR) REFERENCES VORLESUNG,
FOREIGN KEY (MATNR) REFERENCES STUDENT)
Aufgabe 3: Referentielle Aktionen
In dieser Aufgabe sollen verschiedene Schemata in Hinblick auf die Eindeutigkeit bei Löschoperationen untersucht werden, wenn unterschiedliche referentielle Aktionen für die jeweiligen
Fremdschlüssel definiert werden.
a) Gegeben sei folgendes Schema:
refA
FNBR
PROF
FB
refB
FBNR
STUDENT
Diskutieren Sie die Auswirkungen auf das Löschen eines bestimmten FBs, wenn für „refA“ bzw.
„refB“ jeweils eine der referentiellen Aktionen DC (delete cascade), DNA (delete no action), DR
(delete restrict), DSN (delete set null) oder DSD (delete set default) spezifiziert werden.
Existieren irgendwelche Einschränkungen bzgl. DSD oder DSN?
2
Informationssysteme
SS 2013 – Übungsblatt 10
Ist das jeweilige Ergebnis abhängig von der Reihenfolge der referentiellen Aktionen (Beispiele)?
b) Betrachten Sie nun folgendes Schema:
refA
FBNR
FB
refB
Dekan_von
PROF
„refA“ und „refB“ stehen wieder für die oben genannten referentiellen Aktionen.
Wie wirken sich in diesem Fall die unterschiedlichen Kombinationen der referentiellen Aktionen
für „refA“ und „refB“ auf das Löschen eines konkreten FBs aus (Beispiele!)?
c) Diskutieren Sie auch im nächsten Schema die Auswirkungen der unterschiedlichen referentiellen
Aktionen, die anstelle von „refA“, „refB“, „refC“ und „refD“ eingesetzt werden, wenn ein bestimmter FB gelöscht wird.
refA
FBNR
FB
refB
FBNR
PROF
refC
PRNR
STUDENT
PRÜFUNG
refD
MatNR
Aufgabe 4: Hierarchien in SQL
Gegeben sei folgendes Schema einer Personaldatenbank. Zunächst habe der „ranghöchste“ Angestellte im Attribut Vorges einen NULL Wert stehen.
CREATE TABLE Personal
(
PNR
INTEGER PRIMARY KEY,
Name
VARCHAR (40) NOT NULL,
Vorges
INTEGER REFERENCES Personal(PNR)
)
Geben Sie zunächst Beispielinhalt für die Tabelle an und formulieren Sie dann folgende Anfragen in
SQL. Geben Sie das jeweilige Anfrageergebnis für ihre Beispieldaten an.
a) Ermitteln Sie zu jedem Angestellten den direkten Vorgesetzten. Geben Sie sowohl den Namen des
Angestellten als auch den Namen des Vorgesetzten aus.
b) Ermitteln zu jedem Angestellten den nächsthöheren Vorgesetzten (d. h. den Vorgesetzten des direkt Vorgesetzten). Geben Sie auch hier wieder beide Namen aus.
c) Wie ändern sich die Anfragen aus a) und b), wenn Vorges als NOT NULL definiert wurde und der
höchste Vorgesetzte seine eigene Personalnummer im Attribut Vorges vorfindet?
3