Musterl osung - WordPress.comon nicht jede Buchung kon-trolliert werden, das geht bei dem tern aren...

Westfalische Wilhelms-Universitat Munster

Datenbanken

Musterlosung

Julia Wolters

Sommersemester 2009

Dies sind die kompletten Musterlosungen der Ubungen, die sich aus den Mitschriften derUbung und den gegebenen Musterlosungen im OpenUss ergeben

1

Ubung SS 2009 DatenbankenMusterlosungen

Mittwochs, 08 – 10 UhrMusterlosung

Aufgabe 1

a)

dateibasierte Datenhaltung Datenbanksystem

Speicherung der Daten in einem csv-Format und damit in jedem Editor ver-wendbar.

Keine offene Sichtbarkeit der Speiche-rung moglich.

Durch Erlauben der Bearbeitung derDatensatze ist keine allgemeine Sicher-heit gegeben.

Hohe Datensicherheit vorhanden.

Große Datenmengen konnen effizientverwaltet werden.

inflexible und Fehleranfallige Datenhal-tung

Einfache Bedienbarkeit.

Datensatze konnen redundanz und in-konsistente Daten enthalten

Ermoglichung von redundanzfreier undkonsistenter Datenhaltung.Verbindung der Datenbank mit einerVerwaltungssoftware fur Datenbanken.

keine parallelen Transaktionen moglich mehrere parallele Transaktionenmoglich

Nachteile von dateibasierter Datenhaltung

• partielle Inkonsistenz

– Integritat von Programm gepruft– Redundanz– keine Transaktionen

• Mehrfachbenutzung schwierig• Abfragesprache (z.B. SQL) fehlt, Keine Ad-hoc-Abfrage

b) Der Einstieg in die Datenhaltung wird durch die dateibasierte Datenhaltung verein-facht. Dadurch, dass keine bestimmten Programme benotigt werden, ist die Bearbei-tung und Benutzung auf Standard–PC gegeben.

Dateibasierte Datenhaltung nutzlich fur:

• keine / einfacherere Anwendungen / kein komplexes DBMS• begrenzte Ressourcen (z.B. embedded systems)• kein Multiuserbetrieb notwendig

2 Julia Wolters


DatenbankenMusterlosungen

Ubung SS 2009

Aufgabe 2

Geben Sie fur die folgenden Begriffe jeweils eine moglichst kurze Deffnition und nachMoglichkeit zusatzlich ein Beispiel an:

Datenbank Sammlung von (zusammenhangenden) DatenBeispiel: CD–Sammlung, die alle (relevanten) Informationen zu den gesammeltenCDs enthalt.

Datenbanksystem Verbindung einer Datenbank mit einer VerwaltungssoftwareBeispiel: MS Access, OO Base

Datenmodell Darstellung und Erfassung der in der Datenbank enthaltenen Informatio-nen.Beispiel: ER-Modell

Datenunabhangigkeit Unterscheidung zwischen physische (Implementierung) und logi-sche (Anwendung) Datenunabhangigkeit.

Data Dictionary Datenworterbuch. Zugriff auf die Datenbeschreibung der Datenbank,enthalt alle relevanten Informationen

DBA Datenbank-Administrator

DDL Data Definition Language – Sprache zur Datenbankdefinition

DML Data Manipulation Language – Sprache fur Anfragen und Anderungsoptionen.

Indexstruktur realisiert Zugriff auf Dateien und Zugriffsstrukturen.

Redundanz Mehrfachvorhandensein von Informationen.

Sicht Verteilung der Zugriffsrechte.

Transaktion Zusammenfassung von Datenbank-Anderungen zu Funktionssicherheiten,die als Ganzes ausgefuhrt werden sollen und deren Effekt bei Erfolg permanentin der Datenbank gespeichert werden soll.

Julia Wolters 3



Aufgabe 3

Erlautern Sie am Beispiel einer Literaturdatenbank den Unterschied zwischen dem Schemaund der Auspragung einer Datenbank.

© © © © © ←− Schema∼ ∼ ∼ ∼ ∼ ←− Auspragung∼ ∼ ∼ ∼ ∼ ←− Auspragung

4 Julia Wolters



Ubung SS 2009

Aufgabe 4

Erlautern Sie die folgenden aus der Vorlesung bekannten Begriffe am Beispiel eines Arraysder Große m× n in einer Ihnen vertrauten Programmiersprache:

• Physikalische Ebene: ︸︷︷︸m·n

• Konzeptionelle Ebene:

n

m

• logische Ebene: int[][] a = new int[m][n]

• Sichtebene: int[] sicht = a[b]

Julia Wolters 5



Aufgabe 5

(a) Beschreiben Sie informell das konzeptionelle Schema einer Datenbank, die als Reser-vierungssystem fur Flugreisen eingesetzt werden kann.

Flug: FlugNr, Startzeit, ZielZeit, ZielOrt, ...Passagier: KundenNr, Name, AdresseBuchung: FlugNr, KundenNr

(b) Identifizieren Sie nun unterschiedliche Benutzergruppen fur diese Datenbank. GebenSie fur jede Benutzergruppe eine eigene Sicht an. Welche Aspekte gilt es dabei zuberucksichtigen?

(c) Formulieren Sie abschließend umgangssprachliche Beispiele fur Anfragen und Aktua-lisierungen bezuglich dieser Datenbank fur die unterschiedlichen Benutzergruppen.

6 Julia Wolters



Ubung SS 2009

Aufgabe 6

Erlautern Sie die folgenden Begriffe jeweils kurz(!) allgemein und an einem Beispiel:

Beziehung Assoziation zwischen Entitaten

Beziehungsmenge Menge aller Beziehungen eines Beziehungstyps

Entitat Basisobjekt mit einer in der realen Welt unabhangigen ExistenzObjekt der Anwendungsdomane, vor anderen Objekten unterscheidbar

Entitatstyp definiert das Format von Objekten einer Entitatsmenge

Entitatsmenge Sammlung aller Entitaten eies Entitatstypen in einer DatenbankMenge von Objekten mit gleichen Attibuten

Superschlussel Menge von Attributen, deren Werte jede Entitat der zugehorigen En-titatsmenge eindeutig identifizieren

Kandidatenschlussel minimaler Superschlussel

Primarschlussel ausgezeichneter Kandidatenschlussel

Relation (mathematisch) Teilmenge des Karthesischen Produkts

Rolle Verdeutlichen die Semantik der BeziehungenAufgabe eines Entitatstyps innerhalb ener Beziehung

Konzeptioneller Entwurf Erstellung eines konzeptionellen Schemas fur die DatenbankGlobalansicht (ERM)

Logischer Entwurf Transformation des konzeptionellen Schemas in das Implementierungs-datenmodellKonversion, Normalisierung in Ziel-DBMS

Physischer Entwurf Festlegung der internen Speicherstrukturen, Zugriffspfade und Da-teiorganistionDateiformate, Indexstruktur, Denomalisierung

Julia Wolters 7



Aufgabe 7

Sie kennen aus der Vorlesung ternare Beziehungstypen. Die ersten beiden Abbildungenstellen dar, wie ein ternarer Beziehungstyp durch die Einfuhrung eines weiteren Entitats-typs aufgelost werden kann. In der rechten Abbildung ist eine weitere Alternative zurAuflosung des ternaren Beziehungstyps durch drei binare Beziehungstypen dargestellt.Untersuchen Sie, ob diese zweite Alternative sinnvoll ist.

Lehrer|

Klasse — 4 — Fach

Es gehen Informationen verloren- welcher Lehrer unterichtet welche Klasse in welchem Fach?

A = {A1, A2} , B = {B1, B2} , C = {C1, C2}R = {(A1, B1, C1), (A1, B2, C2), (A2, B1, C2), (A2, B2, C1)}R1 = {(A1, B1), (A1, B2), (A2, B1), (A2, B2)}R2 = {(B1, C1), (B1, C2), (B2, C1), (B2, C2)}R3 = {(A1, C1), (A1, C2), (A2, C1), (A2, C2)}

Ein Ruckschluss von R1, R2 und R3 nach R ist nicht moglich.

8 Julia Wolters



Ubung SS 2009

Aufgabe 8

Wissenschaftliche Artikel werden unter anderem in speziellen Fachzeitschriften veroffent-licht. Betrachten Sie dazu das folgende vereinfachte Szenario:

• Ein Wissenschaftler hat einen Namen, eine Dienstadresse und ein Forschungsgebiet.Er reicht Artikel alleine oder gemeinsam mit Kollegen solange zur Veroffentlichungbei unterschiedlichen Zeitschriften ein, bis der Artikel veroffentlicht wird oder derWissenschaftler sein Vorhaben aufgibt.

• Ein Artikel erscheint in hochstens einer Zeitschrift. Dabei wird ein Artikel nurverofentlicht, wenn er zuvor von mehreren unterschiedlichen Wissenschaftlern begut-achtet und fur gut befunden wurde. Ein Artikel enthalt gewohnlich ein Literaturver-zeichnis mit einer beliebigen Anzahl von Verweisen auf bereits fruher veroffentlichteArtikel.

• Eine Zeitschrift wird von einem oder mehreren Wissenschaftlern herausgegeben.Ausgaben der Zeitschrift erscheinen mehrmals jahrlich und jede dieser Ausgabenist genau einem Verlag zuzuordnen. Ein Verlag kann jedoch durchaus mehrereZeitschriften veroffentlichen, andererseits konnen verschiedene Ausgaben einer Zeit-schrift bei verschiedenen Verlagen erscheinen.

(a) Arbeiten Sie alle Entitatstypen und zugehorige Attribute des obigen Beispiels heraus.Bestimmen Sie zudem weitere erforderliche Attribute sowie Beziehungstypen und ge-ben Sie zu jedem Entitatstyp einen Primarschlussel an.

Entitaten: Primarschlussel,::::::::::::::::Diskriminator

• Wissenschaftler (Name, Dienstadresse, Forschungsgebiet• Zeitschrift (ISSN, Name)• Artikel (Titel, Erstdatum)• Verlag (Name, Adresse)• Ausgabe (

::::::::::Nummer,

:::::::::::::Augabejahr) } schwache Entitat

Beziehungen:

• verfasst: Wissenschaftler - Artikel• begutachter: Wissenschaftler - Artikel• gibt-heraus: Wissenschaftler - Zeitschrift• verweist-auf: Artikel - Artikel (Literaturverzeichnis)• eingereicht-bei: Artikel - Zeitschrift• erscheint-in: Artikel - Ausgabe• von: Ausgabe - Zeitschrift• erscheint-bei: Ausgabe - Verlag

Julia Wolters 9



(b) Setzen Sie das Szenario in ein Entity–Relationship–Diagramm um.

Wissenschaftler

Artikel

Zeitschrift

Verlag

NameDienstadresse

Forschungsgebiet

verfasstverweist-auf

Titel Erstellungsdatum

istGut

erscheint-in

SeitenzahlBeginn SeitenzahlEnde

Ausgabe

AusgabenNr AusgabenJahr

gibt-heraus

eingereicht-bei

erscheint-bei

ISSN Name

Name Adresse

begutachtetQuelle

Referenz

von(1, 1)

(1, 1)

(0, n)

(0, n)(0, n)

(0, 1)

(0, n)

(1, 1)

(1, n)

(0, n)

(0, n)

(0, n)

(0, n)

(0, n)

(1, n)

(0, n)

(0, n)

10 Julia Wolters



Ubung SS 2009

Aufgabe 9

Entwickeln Sie ein Entity–Relationship–Diagramm zur Modellierung eines kleinen Flug-hafens. Berucksichtigen Sie dabei zumindest folgende Informationen uber die zu verwal-tenden Daten des Flughafens:

• Der Flughafen besitzt mehrere Flugzeuge, welche jeweils eine eindeutige Registrie-rungsnummer besitzen. Jedes Flugzeug ist assoziiert mit einem festen Stellplatz ineinem Hangar, von welchem jeweils Nummer und Kapazitat bekannt sind. JedemFlugzeug ist außerdem eindeutig ein Flugzeugtyp zugeordnet. Zu jedem Typ werdenspezifische Informationen wie Modellbezeichnung, Gewicht und Kapazitat vermerkt.

• Ein Flughafen speichert die Daten mehrerer Personen (die uber Name und Adresseunterscheidbar sind und deren Geburtstage bekannt sind), u.a. die Daten von Pi-loten und Mitarbeitern. Letztere besitzen eine Personalnummer; zu einem Pilotensollte seine Lizenznummer gespeichert werden und es sollte ersichtlich sein, welcheFlugzeugtypen er fliegen darf.

• Jedes Flugzeug muss gelegentlich gewartet werden. Das Datum, die Art und Dauereiner Wartung, sowie das beteiligte Personal sollten vermerkt werden.

• Jedes Flugzeug hat mindestens einen Besitzer. Ein Besitzer kann dabei sowohl einUnternehmen als auch eine Privatperson sein.

Julia Wolters 11



Flugzeugtyp

ist vom Typ

ModBezeichnung Kapazität Gewicht

Flugzeug

Hangar

steht in

RegNr

Nummer

PlatzNr

Ort

Kapazität

wird gewartet Wartung

Datum Dauer

wartet

besitztP

besitztU Unternehmen

Name

Person

Name Adresse GebDatum

Pilot

Mitarbeiter

LizenzNr

PersonalNr

darfFliegen

o

(0,n) (1,n)

(0,n)

(1,1)

(1,1)

(0,n)

(0,n) (1,1) (1,n) (0,n)

(0,n) (0,n)

(0,n) (0,n)

(0,n)

12 Julia Wolters



Ubung SS 2009

Aufgabe 10

Betrachten Sie nochmals ternare Relationen und deren Auflosung aus Aufgabe 7.

(a) Zeigen Sie, dass die gezeigte Auflosung der ternaren Relation nicht aquivalent zurursprunglichen Relation ist.

ε = {e1, e2} A = {a1, a2} B = {b1}, C = {c1}RA = {(e1, a1), (e2, a2)}, RB = {(e1, b1)}, RC = {(e1, c1)}

(b) Geben Sie Constraints fur die gezeigte Abbildung an, die gewahrleisten, dass jedeInstanz von A, B, C, E, RA, RB, RC eine Entsprechung in der ursprunglichen ternarenRelation besitzt.

(c) Modifizieren Sie die Abbildung, um vollstandige Teilnahme in der ternaren Relationdarzustellen.

⇒

(d) Modifizieren Sie die Abbildung so, dass E zu einem schwachen Entitatstyp wird.

Julia Wolters 13



Aufgabe 11

Ein System zur Tischreservierung in Restaurants sei wie folgt modelliert: Fugen Sie in

einem ersten Schritt die benotigten Attribute zum Diagramm hinzu. Gehen Sie nun vonder Situation aus, dass ein Stammgast seinen Lieblingstisch an mehreren Tagen reserviertund geben Sie dafur explizit eine Beispielinstanz fur die Beziehungsmenge reserviert an.Welches Problem tritt auf und wie kann man es durch eine andere Modellierung losen?

• temporare Beziehung:

• zusatzlice Entitat: Reservierung

Zusatzlicher Vorteil:Man kann nur eine Reservierung in einem Zeitraum haben

14 Julia Wolters



Ubung SS 2009

Aufgabe 12

Betrachten Sie das Konzept der Aggregation im E–R–Modell.

a) Beschreiben Sie das Konzept und typische Anwendungsgebiete der Aggregation imAllgemeinen.

Behandeln von Beziehungen als abstrakte Entitat ⇒”Beziehungen von Beziehungen“

b) Geben Sie zwei Beispiele an, in denen es sinnvoll ist, eine Aggregation bei der Erstellungeines E–R–Diagramms anzuwenden.

c) Zeigen oder widerlegen Sie, dass sich jede Aggregation durch einen n–aren Beziehungs-typ simulieren lasst. Falls die Aussage stimmt, gehen Sie auf moglicherweise entste-hende Nachteile ein.

z.B. kann beide Aggregati-on nicht jede Buchung kon-trolliert werden, das gehtbei dem ternaren Bezie-hungen nicht.

d) Zeigen oder widerlegen Sie, dass sich jeder n–are Beziehungstyp durch eine Aggregationersetzen lasst. Falls die Aussage stimmt, gehen Sie auf moglicherweise entstehendeNachteile ein.

richtig, gezeigt durch Induktion

Julia Wolters 15



Nachteil: viel mehr Beziehungstypen

16 Julia Wolters



Ubung SS 2009

Aufgabe 13

Nehmen Sie an, dass sich zwei Firmen zu einer zusammenschließen. Beide Firmen besitzenDatenbestande, die auf dem gleichen Entity-Relationship-Diagramm (Folie 56 aus Kapitel2) beruhen. Welche Probleme konnen auftreten, wenn die bisher separat verwalteten Datenin einer gemeinsamen Datenbank vereint werden sollen? Welche Losungsmoglichkeitengibt es fur diese Probleme?

• haben beide DBMS die gleiche Strucktur

• gleiche Personal-/ Abteilungs-/ Projekt Nr.→ Nr neu vergeben

• gemeinsame Projekte→ Dublikate entfernen (welche Abteilung erhalt Projekt?)

• Angestellter in beiden Firmen

• Kinder mit Mutter in Firma 1 und Vater in Firma 2→ Schema andern

• Zusammenlegung der Abteilungen (wer wird Abteilungsleiter?)

Julia Wolters 17



Aufgabe 14

Gehen Sie von einem einfachen Schema fur eine Hoteldatenbank aus:

Hotel (HotelNr, HotelName, Stadt)Zimmer (ZimmerNr, HotelNr, Typ, Preis)Buchung (HotelNr, GastNr, AnreiseDatum, AbreiseDatum, ZimmerNr)Gast (GastNr, GastName, GastAdresse)

Was wird durch die folgenden Ausdrucke der Relationalen Algebra abgefragt?

(a) ΠHotelNr(σPreis>50(Zimmer))

Nr. aller Hotels mit Zimmerpreis > 50 ohne Dublikate

(b) σHotel.HotelNr=Zimmer.HotenNr(Hotel × Zimmer)

Alle Zimmer aller Hotels (mit jeweils der gleichen HotelNr.)→ einfacher Join verwenden, Hoten 1 Zimmer

(c) ΠHotenName(Hotel 1Hotel.HotelNr=Zimmer.HotelNr. (σPreis>50(Zimmer)))

Namen aller Hotels, die mindestens Zimmer haben mit Preis > 50→ normaler Join wurde ausreichen

(d) ΠZimmerNr(σHotelName=”Interconti“(Hotel) 1 Zimmer 1 (σAnreiseDatum=01.06.2009(Buchung)))

Nummer aller Zimmer in Hotels mit Namen”Interconti“ fur dieses eine Buchung mit

Anreisedatum 01.06.09 gibt. Dublikate sind nicht erlaubt, werden bei gleicher ZimmerNr. in verschiedene Hotelsnur eine Nr. ausgegeben.

18 Julia Wolters



Ubung SS 2009

Aufgabe 15

Betrachten Sie die Umsetzung des E-R-Diagramms fur den Veroffentlichungsprozess wis-senschaftlicher Artikel (Aufgabe 8 auf Blatt 2) auf folgendes Schema:

Wissenschaftler (Name, Dienstadresse, Forschungsgebiet)Artikel (Titel, ErstDatum)Ausgabe (AusgabenNr, AusgabenJahr, ZeitschriftISSN, VerlagName)Zeitschrift (ISSN, Name)Verlag (Name, Adresse)verfasst (Name, Dienstadresse, Titel, ErstDatum)begutachtet (Name, Dienstadresse, Titel, ErstDatum, istGut)verweist-auf (QuellenTitel, QuellenErstDatum, ReferenzTitel, ReferenzErstDatum)eingereicht-bei (Titel, ErstDatum, ZeitschriftISSN)erscheint-in (Titel, ErstDatum, SeiteBeginn, SeiteEnde, AusgabenNr, AusgabenJahr, ZeitschriftISSN)gibt-heraus (Name, Dienstadresse, ZeitschriftISSN)

Geben Sie nun Ausdrucke der Relationalen Algebra an, die folgende Mengen als Ergeb-nisrelation besitzen.

Allgemeines Vorgehen:

1) Welche Relationen? hinschreiben, evtl. Join (bei unterschiedlichen Namen θ-Join)

2) Datensatze? Selektionen

3) welche Attribute? ProjektionenWie kann man optimieren? 4 eindeutige Namen

a) Alle Zeitschriften.

Zeitschrift

b) Die Namen aller Wissenschaftler, die bereits Artikel veroffntlicht haben.

ΠName(erscheint− in 1 verfasst)

c) Die Namen aller Wissenschaftler, die im Jahr 2000 gemeinsam mit Herrn Schmidt ausMunchen (Spezialgebiet Datenbanken) mindestens einen Artikel veroffentlicht haben.

Herr SchmidtArtikel Schmidt ←− σName=

”Schmidt“∧Dienstadresse=

”Munchen“(verfasst)

ΠName(verfasst− ArtikelSchmidt) 1 (ΠTitel,Erstdatum(

(ArtikelSchmidt 1 σAusgabejahr=2000(erscheint− in)︸︷︷︸Alle Artikel die Herr Schmidt 2000 veroffentlicht hat

)

Julia Wolters 19



d) Die Namen aller Verlage, die jemals die”Datenbanken-Zeitung“ herausgegeben haben.

ΠV erlagName(Ausgabe 1ZeitschriftISSN=ISSN σName=”Datenbank-Zeitung“(Zeitschrift)

e) Verlagsname, Seitenzahlen und Autorenname, die zu einem im Jahr 1999 in der”Datenbanken-

Zeitung“ veroffentlichten Artikel”Geschichte von Datenbankensystemen“ gehoren.

Name der Zeitung”Datenbank-Zeitung“ Zeitschrift

AusgabeJahr 1999 Ausgabe, erscheint-inTitel des Artikels verfasst

(GeschichteArtikel ← σTitel=”Geschichte von Datenbanksystemen“(verfasst)

1 σAugabeJahr=1999(erscheint− in)

1 ΠAusgabeNr,AusgabeJahr,ZeitschriftISSN,verlagName(

Ausgabe 1ZeitschriftISSN=ISSN σName=”Datenbank-Zeitung“(Zeitschrift))

ΠV erlagName,SeiteBeginn,SeiteEnde,Name(GeschichteArtikel)

f) Die Namen aller Wissenschaftler, die diesen Artikel begutachtet haben.

ΠName(Begutachtet 1 ΠT itel,erstdatum(GeschichteArtikel)

g) Die ISSN und den Verlagsnamen aller Zeitschriften mit Artikeln, die auf diesen Artikelverweisen.

verweise verweist-aufISSN,VerlagName Ausgabe

ΠV erlagName,ZeitschriftISSN(ΠQuellenT itel,QuellenErstdatum(verweist− auf)

1 ReferenzT itel = Titel ∧ReferenzErstdatum = Erstdatum(GeschichteArtikel)

1 QuellenT itel = Titel ∧QuellenErstdatum = Erstdatum

1 Ausgabe)

20 Julia Wolters



Ubung SS 2009

Aufgabe 16

Gegeben seien zwei Relationenschemata R und S, Relationen r(R) und s(S) sowie einPradikat θ =

∧A∈R∩S

(r.A = s.A). Beschreiben Sie allgemein die Schemata und Ergebnisre-

lationen fur

(a) r × sSchema: • besteht (in dieser Reihenfolge) aus allen Attributnamen aus r, sowie

allen Attributnamen aus S• tritt ein Attributname in R als auch in S auf, so wird dieser in rA und sA

umbenanntErgebnisrelation: Kreuzprodukt aller Tupel aus r und s

(b) r 1 s = ΠR∪S(σθ(r × s))Schema: wie (a), alle doppelten Attributnamen werden entferntErgebnisrelation: die Teilmenge des Kreuzproduktes, die θ erfullt (Untersuchung von

Mehrfachnennung von Attributen wergen Π)

(c) r 1θ s = σθ(r × s)Schema wie (a)Ergebnisrelation: wie bei r 1 s, nur ohne die Unterschlagung der Mehrfachnennung

Gilt nur fur Pradikate wie in der Aufgabenstellung!

Julia Wolters 21



Aufgabe 17

Gegeben seien zwei Relationen r(R) und s(S) mit |r| = n > 0 sowie |s| = m > 0 Elemen-ten. Geben Sie fur die folgenden Ausdrucke an, wieviele Elemente die Ergebnisrelationmindestens und hochstens enthalten kann. Die Schemata R und S seien dabei so de

niert, dass alle Ausdrucke gultig sind.

Mindestens hochstens

(a) πX(r) 1 n(b) r ∪ s max(n,m) n + m(c) r ∩ s 0 min(n,m)(d) r × s n ·m n ·m(e) r 1 s 0 n ·m(f) σP (r × s) 0 n ·m(g) r ÷ s 0

⌊ nm

⌋(h) r 1θ πS(s× (r × s)) r 1 s= σθ(r × s) wie (f)

22 Julia Wolters



Ubung SS 2009

Aufgabe 18

Gegeben seien zwei Relationenschemata R und S sowie Relationen r(R) und s(S). Unterwelchen Voraussetzungen liefern die beiden Ausdrucke in den folgenden Aufgabenteilenjeweils identische Resultatrelationen?

attrn(P ) = in der Formel P auftretende AttributnamenY ⊕ Z = Schema, das aus dem kartesischen Produkt von Relationen zu Schemata Y bzw.Z entsteht.

(a) X = Y ⊕ Z ∧ attrn(θ) ⊆ Y ⊕ Z

(b) attrn(P ) ⊆ X

(c) attrn(P ) ⊆ R ∩ (R ∩ S) = ∅

(d) attrn(P ) ⊆ S ∩ (R ∩ S) = ∅

(e) attrn(P ) ⊆ R ∩ S

(f) gilt immer

(g) X = Y ∧ domY = domZ ∧ attrn(P ) ⊆ Y

(h) attrn(P ) ⊆ R⊕ Y ∧ attrnθ ⊆ R⊕ Y ∧X = R⊕ Y

Julia Wolters 23



Aufgabe 19

Seien im Folgenden r(R), s(S) und t(T) Relationen uber den Schemata R, S und T. Dannheißen rn s := πR(r 1 s)

”Semijoin von r mit s“ und rns := r−πR(r 1 s)

”Antisemijoin

von r mit s“ Zeigen oder widerlegen Sie nun die Allgemeingultigkeit folgender Aussagen.

(a) r 1 s, s 1 r verursachen unterschiedliche Reihenfolge, die ΠR∪S aber wieder verein-fachticht

(b) r − (rns) = r − (r − ΠR(r 1 s)) = ΠR(r 1 s) = r n s

(c) r n s = ΠR(r 1 s) = ΠR∩R(r)︸︷︷︸=r

1 ΠR∩S(s)

(d) (r n s) 1 s = r 1 ΠR∩S(s) o s︸︷︷︸=s

= r o s

(e) (r n s) 1 (sn (r n s)) = r 1 ΠR∩S(s) 1 ΠR∩S(ΠR(r × s)) = r 1 s 1 ΠR∩S(r 1 s) =r 1 s

(f) nicht allgemein gultigGegenbeispiel: R = (A,B), S = (B,C)r = {(1, 1)}, s = {(1, 2), (2, 3)}zz: ΠS(r × (sn r)) = ΠS(s× (r n s))Links:

sn r = s 1 ΠS(s 1 r) = s 1 s = s = {(1, 2)}r × (sn r) = {(1, 1), (1, 2)}

ΠS(r × (sn r)) = {(1, 2)}

Rechts:

r n s = {(1, 1)}s× (r n s) = {(1, 2, 1, 1), (2, 3, 1, 1)}

ΠS(s× (r n s)) = {(1, 2), (2, 3)}

(g) nicht allgemein gultigGegenbeispiel: R = S = {A}, T = {B}

24 Julia Wolters



Ubung SS 2009

s = {(1)}, t = {(3)}, r = {(2)}

Links:

r × t = {(2, 3)}sn(r × t) = {(1)} = s

ΠS(sn(r × t)) = {(1)}

Rechts:

s× t = {(1, 3)}(s× t) n r = {}

ΠS((s× t) n r) = {}s ∩ ΠS((s× t) n r) = {}

(h) r ∩ (r − (rns))︸︷︷︸⊂r

= r − (rns) = r − (r − ΠR(r 1 s)) = r − (r − (r n s))

Julia Wolters 25



Aufgabe 20

Seien R=(A,B,C) ein Schema und r(R) und s(R) Relationen. Geben Sie zu folgendenAusdrucken der Relationalen Algebra jeweils aquivalente Ausdrucke des TupelorientiertenRelationalen Kalkuls an:

(a) πA(r) = {t | ∃q ∈ r(t[A] = q[A]}

(b) σB=42(r) = {t | t ∈ r ∧ t[B] = 42}

(c) r ∪ s = {t | t ∈ r ∨ t ∈ s}

(d) r ∩ s = {t | t ∈ r ∧ t ∈ s}

(e) r − s = {t | t ∈ ∧¬(t ∈ s)}

(f) πA,B(r) 1 πB,C(s) = {t | ∃m ∈ r(∃n ∈ S(t[A] = m[B] ∧ t[B] = m[B] ∧ m[B] =n[B] ∧ t[C] = n[C]))}

(g) r ÷ πC(s) = {t | ∀u ∈ S(∃w ∈ r(t[A] = w[A] ∧ t[B] = w[B] ∧ w[C] = u[C]))}

(h) πr.A(r 1r.B>s.B s) = {t | ∃m ∈ r(t[A] = n[A] ∧m[B] > n[B]))}

26 Julia Wolters



Ubung SS 2009

Aufgabe 21

Seien R=(A,B) und S=(A,C) Schemata sowie r(R) und s(S) Relationen. Geben Sie Aus-drucke der Relationalen Algebra an, die aquivalent zu den folgenden Ausdrucken desTupelorientierten Relationalen Kalkuls sind:

(a) {t | ∃q ∈ r(t[A] = q[A] ∧ q[B] < 58)} ⇔ πA(σB<58(r))

(b) {t | ∃q ∈ r, v ∈ s(t[A] = q[A] ∧ q[A] = v[A] ∧ t[B] = q[B] ∧ t[C] = v[C])g ⇔ r 1 S

(c) {t | ∃q1, q2 ∈ r, v ∈ s(t[A] = v[A] ∧ q1[A] = t[A] ∧ q2[A] = v[C] ∧ q1[B] > q2[B])} ⇔πs.A(σs.c=r2.A∧s.A=r1.A∧r1.B>r2.B(s× pr1(r)× pr2(r)))

Julia Wolters 27



Aufgabe 22

Untersuchen Sie, ob die folgenden Anfragen im Werteorientierten Relationalen Kalkulsicher sind. Begrunden Sie jeweils Ihre Aussage. Geben Sie zudem die Domane der jewei-ligen Anfrage an. dom(P): Werte in Relation in P, expliziete Werte in P

sicher: falls

(1) alle Werte des Ergebnistupels liegen in dom(P)(2)

”∃xP1(x)“: ∃ Wert aus dom(P1) der (P1(x)) erfullt ⇔ ∃x(P1(x))

(3)”∀xP1(x)“: ∀ Werte aus dom(P1) ist (P1(x)) erfullt ⇔ ∀x(P1(x)) erfullt ist.

(a) {〈a〉| a = 0}

dom(P ) = {0} (expliziter Wert a = 0)⇒ a = 0 ∈ dom(P ) (kein

”∃“ oder

”∀“ daher muss nur (1) erfullt sein. ⇒ Anfrage

sicher

(b) {〈a, b, c〉|(a = c) ∧ (b = c)}

dom(P ) = {} (keine Relation, keine explizieten Werte)⇒ a /∈ dom(P ) (b und c auch nicht) ⇒ Anfrage nicht sicher

(c) {〈i, n, s, c〉|〈i, n, s, c〉 ∈ customer}

dom(P ) = values(customer)⇒ i, n, s, c ∈ dom(P )⇒ Anfrage sicher

(d) 〈a〉|b(〈a, b〉 ∈ account ∨ (b > 0))}

dom(P ) = values(account) ∪ {0}⇒ z.B. account = {}⇒ a 6= dom(P )⇒ Anfrage unsicher

(e) {〈l〉|∀a(¬(〈l, a〉 ∈ loan) ∧ (a > 500))}

dom(P ) = values(loan) ∪ {500} = dom(P1) = (¬(〈l, a〉 ∈ loan ∧ (a > 500))∀a(P1) immer falsch, wegen a = 500⇒ Resultat ist leer⇒ Anfrage sicher

(f) + (g) {〈l, n〉|∃a(〈l, a〉 ∈ loan) ∨ ∃i, s, c(〈i, n, s, c〉 ∈ customer)}{〈l, n〉|∃a(〈l, a〉 ∈ loan) ∧ ∃i, s, c(〈i, n, s, c〉 ∈ customer)}

dom(P ) = values(loan) ∪ values(customer)

28 Julia Wolters



Ubung SS 2009

zu 1) Alle Komponenten von 〈l, n〉 ∈ dom(P )zu 2) ∃a : 〈l, n〉 ∈ loan erfullt ⇔ ∃ Tupel 〈l, a〉 ∈ loan, das 〈l, a〉 ∈ loan erfullt.∃i, s, c〈i, n, s, c〉 = customer erfullt ⇔ ∃ Tupel λi, n, s, c〉 ∈ customer das 〈i, n, s, c〉 ∈customer erfullt

Julia Wolters 29



Aufgabe 24

Superschlussel: bestimmt eindeutigKandidatenschlussel: minimaler Superschlussel

Es sei r(R) eine Relation uber dem Schema R. Zeigen oder widerlegen Sie:

(a) Sind K1 und K2 Kandidatenschlussel fur r, so auch K1 ∪K2.

K1 6= K2

K1 ( K1 ∪K2

K1 Superschlussel ⇒ K1 ∩K2 nicht minimal, also kein Kandidatenschlussel

(b) Sind K1 und K2 Kandidatenschlussel fur r, so auch K1 ∩K2.

K1 6= K2

K1∩K2 ( K1 ⇒ K1∩K2 kein Superschlussel, also erst recht kein Kandidatenschlussel

(c) Sind K1 und K2 Superschlussel fur r, so auch K1 ∩K2.

zz. ∀r(R) ∀t1, t2 ∈ r:

ΠK1∪K2(t1) = ΠK1∪K2(t2)⇒ t1 = t2

ΠK1∪K2(t1) = ΠK1∪K2(t2)⇒ ΠK1(t1) = ΠK2(t2)⇒ t1 = t2

(d) Sind K1 und K2 Superschlussel fur r, so auch K1 ∪K2.

Gegenbeispiel: R = (A,B,C,D)K1 = (A), K2 = (B) SuperschlusselK1 ∩K2 = {} ist kein Superschlussel

30 Julia Wolters



Ubung SS 2009

Aufgabe 25

Funktionale Abhangigkeit:

α → β gilt auf R

⇔ ∀r(R) ∀t1, t2 ∈ r : t1[α]→ t2[α]

⇒ t1[β] = t2[β]

Erlautern Sie, wie mit Hilfe von funktionalen Abhangigkeiten die folgenden Einschrankun-gen ausgedruckt werden konnen:

Sei PK(r) der Primarschlussel der Relation r

a) Eine 1:1-Beziehung existiert zwischen den Entitatsmenge account und customer.

PK(account) → PK(customer)PK(customer) → PK(account)

b) Eine n:1-Beziehung existiert zwischen den Entitatsmenge account und customer.

PK(customer) → PK(account)

Julia Wolters 31



Aufgabe 26

Betrachten Sie das folgende relationale Schema:

FAMILIE(Kind, Kind-GebJahr, Mutter, Mutter-GebJahr,

Vater, Vater-GebJahr, Hochzeitsjahr)

In Relationen zu diesem Schema seien fur die darin enthaltenen Daten die folgenden(vereinfachten) Annahmen gultig:

• Jede Person ist eindeutig identi

ziert durch ihren Namen, der als Attributwert vermerkt ist (in den Spalten Kind,Mutter oder Vater).

• Werte in Kind-GebJahr, Mutter-GebJahr, Vater-GebJahr bezeichnen das Geburts-jahr der jeweiligen Person.

• Ein Paar (bestehend generell aus Mutter und Vater) kann sich nur ein einziges Malgegenseitig heiraten.

• Eine Person kann mehrfach heiraten, aber wegen der vorgeschriebenen Trennungs-zeit nicht mehrere Male in ein und demselben Jahr.

• Eine Person kann beliebig viele Kinder haben.

Folgern Sie aus diesen Annahmen nun die wesentlichen funktionalen Abhangigkeiten dieauf dem Schema FAMILIE gelten.

Kind → Kind GebJahrMutter → Mutter GebJahrVater → Vater GebJahrMutter, Vater → HochzeitsjahrMutter, Hochzeitsjahr → VaterVater, Hochzeitsjahr → Mutter

32 Julia Wolters



Ubung SS 2009

Aufgabe 27

Armstrong Axiome A1: Reflexivitat: β ⊆ α⇒ α→ βA2: Erweiterung: α→ β ⇒ αγ → αγA3: Transititvitat: α→ β ∧ β → γ ⇒ α→ γ

Kommutativ: α→ βγ ⇔ αγβ

Beweisen Sie unter Benutzung der ArmstrongAxiome oder widerlegen Sie die Gultigkeitfolgender Regeln fur funktionale Abhangigkeiten fur ein beliebiges Relationenschema Rmit α, β, γ, δ ⊆ R:

a) Vereinigungsregel: (α→ β ∧ α→ γ) =⇒ (α→ βγ)

α → βα → γ

}A2⇒

α︷︸︸︷αα → βααβ → γβ

}A3⇒ α→ βγ

b) Schnittregel: (αβ → δ ∧ βγ → δ) =⇒ (β → δ)

falsch, Gegenbeispiel:A B C D2 1 2 33 1 3 4

α = (A), β = (B), γ = (C), δ = (D)

c) Dekompositionsregel: (α→ βγ) =⇒ (α→ β ∧ α→ γ)

α→ βγ ∧ β ⊆ βγγ ⊆ βγ

A1⇒ βγ → ββγ → γ

A3⇒ α→ βα→ γ

d) Differenzregel: (α→ β ∧ γ → δ) =⇒ ((α− γ)→ (β − δ))

falsch, Gegenbeispiel:A B C D2 1 1 12 2 2 2

α = (AB), β = (CD), γ = (B), δ = (D)α− γ = (A)β − δ = (C)

}6=

e) Pseudosymmetrieregel: (α→∵ ∧γ → β) =⇒ (α→ γ)

falsch, Gegenbeispiel:A B C1 2 11 2 2

α = (A), β = (B), γ = (C)

f) Pseudotransitivitatsregel: (α→ β ∧ γβ → γ) =⇒ (αγ → δ)

α→ βγβ → C

}A2⇒ αγ → βγ

γβ → δ

}A3⇒ αγ → δ

Julia Wolters 33



Aufgabe 28

Das Schema R = (A,B,C,D,E) werde zerlegt in (A,B,C) und (A,D,E). Zeigen Sie, dassdies eine verlustlose Zerlegung darstellt, falls die folgenden funktionalen Abhangigkeitenauf R gelten:

A → BC CD → E B → D E → A

Zerlegung: R = R1 ∪R2

Verlustlos: r = ΠR1(r) ./ ΠR2(r)(Hinreichendes Kriterium (HK)) R1 ∩R2 → R1 ∨R1 ∩R2 → R2 ∈ F+

Abhangigkeitserhaltend: (F1 ∪R2)+ = F+

R = (A,B,C,D,E)

R1 = (A,B,C) R2 = (A,D,E)

R1 ∪R2 = (A)

A+ = R ⊆ R1 ⇒ verlustlose Zerlegung

34 Julia Wolters



Ubung SS 2009

Aufgabe 29

Gegeben seien ein Schema R=(A,B,C,D,E) und eine Menge funktionaler Abhangigkeiten

F = {A→ BC,A→ D,CD → E,B → D,E → A}.

R = (A,B,C,D,E)

F = {A→ BC,A→ D,CD → E,B → D,E → A}

a) Bestimmen Sie (BD)+ und F+.

BD+ = {B,D}

”Superschlussel bestimmen“:

A+ = R, CD+ = R, B+ = {B,D}, E+ = R, C+ = {C}, D+ = {D}, BC+ = R

Wo K+ = R ist K Kandidatenschlussel

F+ = {α→ β, αA→ γ, αCD → γ, αE → γ, αBC → γ, αB → D | β ⊆ α ⊆ R, ∅ 6= γ ⊆ R}

b) Sind folgende Zerlegungen von R abhangigkeitserhaltend und verlustlos?

(i) (A,B,C), (A,D,E)

R1 = (A,B,C), R2 = (A,D,E)R1 ∩R2 = (A), A+ = R ⊆ R1 ⇒ verlustlosB → D ∈ F+ aber (B → D) /∈ (F1 ∪ F2)+

(ii) (A,B,C), (C,D,E))

R1 = (A,B,C), R2 = (C,D,E)R1 ∩R2 = (C) C+ = {C} (HK ist erfullt)

A B C D E0 0 0 0 01 1 0 1 1︸︷︷︸

R1 ︸︷︷︸R2

6=

ΠR1(r) ./ ΠR2(r)A B C D E0 0 0 0 00 0 0 0 11 1 0 0 01 1 0 1 1

⇒ keine Verlustlosigkeitwie (i): B → D in F+ aber (F1 ∪ F2)+

Julia Wolters 35



(iii) (A,B,C,E), (B,D)

R1 = (A,B,C,E), R2 = (B,D)R1 ∩R2 = (B) B+{B,D} ⊇ R2 ⇒ verlustlosCD → E ∈ F+ aber nicht in (F1 ∪ F2)+

⇒ nicht abhangigkeitserhalten

(iv) (A,B,C), (A,C,E)

R1 = (A,B,C), R2 = (A,C,E)R1 ∪R2 6= R ⇒ keine Zerlegung

36 Julia Wolters



Ubung SS 2009

Aufgabe 30

Definitionen:

Was ist BCNF:

∀α→ β ∈ F+ gilt(1) α→ β trivial, dh. β ⊆ α(2) α ist Superschlussel, dh. α+ = R

Was ist 3NF:

∀α→ β ∈ F+ gilt(1), (2) oder

(3) Jedes Attribut in β − α ist in Kandidatenschlussel entalten.

Gegeben seien wie in Aufgabe 29 ein Schema R=(A,B,C,D,E) und eine Menge funktionalerAbhangigkeiten F = {A→ BC,A→ D,CD → E,B → D,E → A}.

F+ = {α→ β, αA→ γ, αE → γ, αCD → γ, αBC → γ, αB → βD | β ⊆ α ⊆ R, ∅ 6= γ ⊆ R}

a) Geben Sie eine verlustlose BCNF–Zerlegung fur R bezuglich F an. Ist diese abhangig-keitserhaltend?

Algorithmusresult = {R}

done = false

berechne F+

while not done

if Ri in result nicht BCNF then

α→ β die BCNF-Bedingung verletzt

result = (result - Ri ) ∪ ( Ri → β ) ∪ { α, β }};

else

done = true;

result = {R}, done = false, denn B → D nicht trivial und B kein Superschlussel

→ Ri = R betrachte B → D

result = {(result −R)︸︷︷︸∅

∪ Ri − β︸︷︷︸{A,B,C,E}

∪ {α, β}︸︷︷︸{B,D}

}

= {A,B,C,E} ∪ {B,D}

→ R1→ in BCNF

= {A,B,C,E} R2→ in BCNF B Superschlussel

= {B,D}→ done := true

Wir haben Zerlegungen gefunden, dies ist verlustlos, aber nicht abhangigkeitserhaltent(29.b.iii))!

Julia Wolters 37



b) Geben Sie ein Beispiel fur ein relationales Schema R′ und eine Menge funktionalerAbhangigkeiten F ′ an, sodass mindestens zwei verschiedene BCNF–Zerlegungen vonR bezuglich F existieren.

R′ = (A,B,C,D), F ′ = {A→ B,C → B,B → C}B ist kein Superschlussel → Zerlegung notwendigZ1 = {(A,B), (C,D), (B,D)}Z2 = {(A,B), (C,D), (A,D)}

c) Bestimmen Sie Fc.

”Fc ist F ohne uberflussige Attribute“α→ β ∈ FA ∈ α uberflussig in α⇔ β ⊆ (α− {A})+ aus FB ∈ β uberflussig in β ⇔ B ∈ α+ aus F ′ = {F − {α→ β} ∪ {α→ (β −B)}}F = { A→ BC, A→ D︸︷︷︸, CD → E︸︷︷︸, B → D, E → A }

| CC+=C+E

DD+=D+E

→ E︷︸︸︷A→ B

B/∈A+=ACDEC

C/∈A+=ABDD

D∈A+=ABCDEDDa D∈A+ ist D uberflussig.

F = {A→ BC,CD → E,B → D,E → A}

d) Geben Sie eine verlustlose 3NF–Zerlegung fur R bezuglich F an. Ist diese abhangig-keitserhaltend?

R bereits in 3NFB → D erfullt (3), denn D in Kandidatenschlussel CD enthalten.Algorithmus fur 3NF-Zerlegung:Fur jede funktionale Abhangigkeit in Fc ein Schema, eins davon muss Kandidaten-schlussel enthalten.

FC = {A→ CV,CD → E,B → D,E → A}⇒ {{A,B,C}, {C,D,E}, {B,D}, {A,E}}

ist 3-NF Zerlegung, dies ist verlustlos und abhangigkeitserhaltend

38 Julia Wolters



Ubung SS 2009

Aufgabe 31

Im Jahr 1962 wurden in der Bundesrepublik Deutschland vierstellige Postleitzahlen ein-gefuhrt, die am 01.07.1993 durch funfstellige Postleitzahlen ersetzt wurden. InformierenSie sich gegebenenfalls uber die Einzelheiten und bearbeiten Sie die folgenden Aufgabenunter Vernachlassigung der Besonderheiten fur Postfach- und Großkunden. Gehen Sie zurweiteren Vereinfachung davon aus, dass eine Postleitzahl hochstens einem Ort zugeordnetist.

a) Definieren Sie jeweils entsprechende Schemata, die alle erforderlichen Attribute fur denpraktischen Einsatz von vier- und funfstelligen Postleitzahlen besitzen.

Ralt = (Ort, PLZ)Rneu = (Ort, Stadtteil, Straße,HausNr, PLZ)

b) Bestimmen Sie den Abschluss der funktionalen Abhangigkeiten uber diesen Schemata.

F+alt = {α→ β,Ortα→ γ, PLZα→ γ | β ⊆ α ⊆ Ralt, ∅ 6= γ ⊆ Ralt}F+neu = {α → β, (Ort, Stadtteil, Straße,HausNr)α → γ, PLZα → Ortβ | β ⊆ α ⊆Rneu, ∅ 6= γ ⊆ Rneu}

c) Bestimmen Sie eine kanonische Uberdeckung der funktionalen Abhangigkeiten dieserSchemata.

Faltc = {Ort→ PLZ, PLZ → Ort}Fneuc = {(Ort, Stadtteil, Straße,HausNr)→ PLZ, PLZ → 0rt}

d) Bestimmen Sie mit Begrundung den Typ der besten Normalform, in der sich IhreSchemata be

nden.

Ralt ist bzgl. F+alt in BCNF

α→ β, trivialOrtα→ γ, Ortα ist SuperschlusselPLZα→ γ, PLZα ist Superschlussel

Rneu ist bzgl. F+neu in 3NF.

α→ β, trivial(Ort, Stadtteil, Straße,HausNr)α ist SuperschlusselPLZα→ Ortβ PLZα ist kein Superschlussel, α ist nicht trivial⇒ nicht in BCNFOrt ist in Kandidatenschlussel (Ort, Stadtteil, Straße,HausNr)⇒ in 3NF

Julia Wolters 39



e) Zerlegen Sie Ihre Schemata gegebenenfalls, um moglichst gute abhangigkeitserhaltendeZerlegungen zu erhalten.

Ralt ist bereits in”bester“ Normalform

Behauptung: Es gibt keine abhangigkeitserhaltende Zerlegung von Rneu bzgl. Fneu,die

”besser“ als 3NF sind.

Beweis: (Ort, Stadtteil, Straße,HausNr)→ PLZ /∈ Fi ∀i⇒ (Ort, Stadtteil, Straße,HausNr) /∈ (F1 ∪ . . . ∪ Fn)+

Außerdem existiert in F+neu keine nicht triviale Abhangigkeit α → β mit PLZ ∈

β ∧ PLZ /∈ α ⇒ A /∈ (F1 ∪ . . . ∪ Fn)+

aber A ∈ F+

Jede Zerlegung ist also nicht abhangigkeitsherhaltend.

40 Julia Wolters



Ubung SS 2009

Aufgabe 32

Gegeben sei das Schema einer einfachen Buchereidatenbank:

Bucher (BuchNr, ISBN, Titel, Autor)

Ausleihen (AusleihNr, LeserNr, LeserName, LeserGebDatum, BuchNr)

Beachten Sie dabei, dass von einem Buch durchaus mehrere Exemplare vorhanden seinkonnen.

(a) Erlautern Sie die Nachteile dieses Schemas.Nachteile:

• Redundanz

– ISBN, Titel, Autor bei mehreren Exemplaren eines Buchs– LeserName, LeserGebDatum bei jeder Ausleihe

• Verbessern Sie das Schema, indem Sie eine BCNF-Zerlegung berechnen. StellenSie dazu die funktionalen Abhangigkeiten auf und wenden Sie dann den BCNF-Algorithmus ohne Berechnung von F+ auf ein Schema an, das alle Attribute vonBucher und Ausleihen enthalt.Leser kann ohne Ausleihe nicht existieren

(b) Algorithmus ohne F+ fur BCNF result = {R}

done in BCNF?

while (not done) do

done := true

for each Ri in result do

if(∃α ⊆ Ri mit α+ 6= α und Ri − α 6= ∅) then

β := (α+ − α) ∩Ri

result = {( result -Ri) ∪ {Ri − β} ∪ {αβ}}done := false

F = { LeserNR ⇒ LeserName, LeserGebDatum;

ISBN ⇒ Titel, Autor;

BuchNR ⇒ ISBN;

AusleihNr ⇒ LeserNr, BuchNr }

result = Rn− {BuchNr, ISBN, Titel, AusleihNr, ...}

↪→ done = false; z.B. BuchNr. → ISBNdone = true; Ri = Rα = BuchNr (α+ = { BuchNr, ISBN, Titel, Autor })

Julia Wolters 41



β = {α+ − α} ∩Ri = { ISBN, Titel, Autor }

result = {AusleihNr, LeserNr, LeserName, LeserGebDatum,BuchNr}∪{BuchNr, ISBN, T itel, Autor}

done = false

↪→ BetrachteR1 := { AusleihNr, LeserNr, LeserName, LeserGebDatum, BuchNr }

α = LeserNr (α+ = { LeserNr, LeserName, LeserGebDatum })β = { LeserName, LeserGebDatum }

result := {BuchNr, ISBN, T itel, Autor}︸︷︷︸R2

∪ {AusleihNr, LeserNr,BuchNr}︸︷︷︸R3

∪{LeserNr, LeserName, LeserGebDatum}︸︷︷︸R4

↪→ Betrachte R2 = {BuchNr, ISBN, T itel, Autor}α = ISBN (α+ = { ISBN, Titel, Autor })β = { Titel, Autor }

result := R3 ∪R4 ∪ {BuchNr, ISBN} ∪ {ISBN, T itel, Autor}︸︷︷︸R5

Zerlegung ist R3 ∪R4 ∪R5

42 Julia Wolters



Ubung SS 2009

Aufgabe 33

zu zeigen: M4 ( MBC ( M3 ( M1

Beweis:

M4 ( MBC M4 ⊆ MBC : R in 4NF: A: R nicht in BCNF ⇒ ∃α → β nicht trivial, α kein

Superschlussel fur RReplikationsregel

=⇒ ∃α→→ β nicht trivial, α kein Superschlussel furR⇒ R nicht in 4NF �M4 6= MBC : R = (A,B,C,D), D)(A →→ BC), R in BCNF, da alle funktionalenAbhangigkeiten trivial, R nicht in 4NF, weil A→→ BC nicht trivial und A→→ BCkein Superschlussel

MBC ( M3 MBC ⊆ M3: R in BCNF ⇒ ∀α → β ∈ F+ α → β trivial, α Superschlussel⇒ ∀α → β ∈ F+ α → β trivial oder α Superschlussel oder ∀A ∈ β − α, A ∈ K, KKandidatenschlussel ⇒ in 3NFMBC 6= M3: R = (A,B,C,D), F = (AD → CD,C → B), F+ = {α → β,ABα →β, Cα→ Bβ|β ⊆ α ⊆ R, ∅ 6= γ ⊆ R}α → β trivial ABα → γ ⇒ ABα Superschlussel Cα → Bβ ⇒ nicht in BCNF,nicht trivial, Cα kein Superschlussel ⇒ in 3NF, weil Bβ − Cα = B und B in ABKandidatenschlussel enthalten.

M3 ( M1 M3 ⊆M1: klar, da nach Definition alle Relationen, die wir betrachten, in 1NFsind.M3 6= M1: R = (A,B,C,D), F = {A→ D,B → D,C → AB},R in 1NF klar, B → D ∈ F+, nicht trivial, B kein Superschlussel und D−B = D (C Kandidatenschlussel ⇒ R nicht in 3NF

Julia Wolters 43



Aufgabe 34

zz Es gibt Joinabhangigkeiten, die nicht aquivalent zu einer mehrwertigen Abhangigkeitsind.R = (A,B,C) ∗ ((A,B), (B,C), (A,C))A B C1 3 62 3 51 4 51 3 5

Dies gilt, wenn r = Π(R1(r)) 1 Π(R2(r)) 1 Π(R3(r))

ΠR1(r) 1 ΠR2(r):

A B B C1 3 3 62 3 3 51 4 4 5

A B C1 3 62 3 51 4 51 3 5

1

A C1 52 51 6

=

A B C1 3 62 3 51 4 51 3 5

R in 3 nicht leeren Teilmengen, Y, Z, W, partioniert, Y →→ Z gilt⇔ ∀r(R) 〈y1, z1, w1〉 ∈r ∧ 〈y1, z2, w2〉 ∈ r ⇒ 〈y1, z1, w2〉 ∈ r ∧ 〈y1, z2, w1〉 ∈ rA→→ B : 〈1, 3, 5〉 ∈ r ∧ 〈1, 4, 5〉 ∈ r��⇒〈1, 3, 5〉 ∈ r ∧ 〈1, 4, 6〉 ∈ R⇒ gilt nicht auf R

Es gibt eine Normalform, die”besser“ ist als 4NF

Projekt-Join-Normalform (PJNF, 5NF)R ist PJNF bzgl. einer Menge funktionaler mehrwertige und Joinabhangigkeiten ⇔ ∀ ∗(R1, . . . , Rn) mit Ri ⊆ R, R = R1 ∪ . . . Rn gilt

(1) ∗(R1, . . . , Rn) trivial oder(2) jedes Ri ist Superschlussel fur R

MPJNF ( M4NF lasst sich nicht weiterverkleinern, ohne dass Informationen verlorengehen.

44 Julia Wolters



Ubung SS 2009

Aufgabe 35

(a) Nachteile des Schema KIGA

• Hohe Redundanz(pro Kind z.B. Speicherung von Spiel, Tag, Zeit, Raum)⇒ unubersichtlich, hoher Sortier- und Speicheraufwand und vor allem Aktua-listatsaufwand

• Ein Kind ohne Gruppe kann nicht reprasentiert werden

(b) F+ = {α → β, (Name, V orname,GebDatum)α → (Gruppe,Raum)β,Kindα →Raumβ,Kindα→ Gruppeβ, (Tag, Zeit)α→ (Spiel, Gruppe,Raum)β, (Tag, Zeit)α→(Spiel, Raum)β, (Tag, Zeit)α→ (Spiel, Gruppe)β, (Kind, Tag, Zeit)→ γ}.

Gruppe →→ Spiel, Tag, ZeitGruppe →→ Name, Vorname, GebDatum, RaumGruppe, Raum →→ Spiel, Tag, ZeitGruppe, Raum →→ Name, Vorname, GebDatum

KIGA ist nicht in 3NF.Gruppe→ Raum nicht trivial, Gruppe kein Superschlussel, Raum – Gruppe = Raumin keinem Kandidatenschlussek enthalten ⇒ KIGA ist 1NF

(c) Wende 4NF-Algorithmus an:

1. result = {KIGA}

betrachte Gruppe →→ Spiel, Tag, Zeit

Julia Wolters 45



2. result = {(Gruppe, Raum, Kind), (Spiel, Tag, Zeit, Gruppe)}

betrachte Gruppe →→ Name, Vorname, GebDatum

3. result = {(Gruppe, Kind),(Gruppe, Raum), (Spiel, Tag, Zeit, Gruppe)}

ER-Diagramm:

1 −− urspruengliches KIGA−Schema2 CREATE TABLE kiga (3 Name CHAR (15) ,4 Vorname CHAR (15) ,5 GebDatum CHAR (10) ,6 Gruppe INTEGER NOT NULL ,7 Raum INTEGER NOT NULL ,8 Tag CHAR (12) ,9 Zeit CHAR (6 ) ,

10 Spiel CHAR (12) NOT NULL ,11 PRIMARY KEY (Name , Vorname , GebDatum , Tag , Zeit ) ) ;121314151617 −− neues KIGA−Schema , resultierend aus Zerlegung oder ER−Entwurf und

Beispieldaten1819 CREATE TABLE Kind (20 Name CHAR (15) ,21 Vorname CHAR (15) ,

46 Julia Wolters



Ubung SS 2009

22 GebDatum CHAR (10) ,23 Gruppe INTEGER ,24 PRIMARY KEY (Name , Vorname , GebDatum ) ,25 FOREIGN KEY ( Gruppe ) REFERENCES GruppenEnt26 ON DELETE SET NULL27 ON UPDATE CASCADE ) ;2829 CREATE TABLE GruppenEnt (30 Gruppe INTEGER ,31 Raum INTEGER ,32 PRIMARY KEY ( Gruppe ) ,33 UNIQUE ( Raum ) ) ;3435 CREATE TABLE GruppenSpielTermin (36 Gruppe INTEGER NOT NULL ,37 Spiel CHAR (12) NOT NULL ,38 Tag CHAR (12) ,39 Zeit CHAR (6 ) ,40 PRIMARY KEY (Tag , Zeit )41 FOREIGN KEY ( Gruppe ) REFERENCES GruppenEnt42 ON DELETE CASCADE43 ON UPDATE CASCADE ) ;

Julia Wolters 47



Aufgabe 36

1 −− Aufgabe 3623 −− a )4 SELECT ∗ FROM ACCOUNT ;56 −− b )7 SELECT customer_name , customer_street , customer_city8 FROM customer NATURAL JOIN9 ( SELECT DISTINCT customer_name FROM depositor NATURAL JOIN account

NATURAL JOIN branch10 WHERE branch_city = ’Brooklyn’ ) AS temp111 UNION12 SELECT customer_name , customer_street , customer_city13 FROM customer NATURAL JOIN14 ( SELECT DISTINCT customer_name FROM borrower NATURAL JOIN loan

NATURAL JOIN branch15 WHERE branch_city = ’Brooklyn’ ) AS temp2 ;161718 −− c )19 SELECT branch_name , AVG ( balance ) FROM account20 GROUP BY branch_name21 HAVING AVG ( balance ) >= ALL ( SELECT AVG ( balance )22 FROM account GROUP BY branch_name ) ;2324 −− d )25 −− Kredite26 CREATE VIEW borrowingView AS27 SELECT count ( customer_name ) , loan_number FROM borrower GROUP BY loan_number

;2829 CREATE VIEW loaningView AS30 SELECT customer_name , loan_number , ( amount / count ) AS newamount FROM

borrower31 NATURAL JOIN borrowingView NATURAL JOIN loan ;3233 CREATE VIEW loaningCityView AS34 SELECT customer_city , SUM ( newamount ) AS sum_of_newamount35 FROM loaningView NATURAL JOIN customer GROUP BY ( customer_city ) ;363738 −− Kontoguthaben39 CREATE VIEW deposingView AS SELECT count ( customer_name ) , account_number40 FROM depositor GROUP BY account_number ;4142 CREATE VIEW accountingView AS

48 Julia Wolters



Ubung SS 2009

43 SELECT customer_name , account_number , ( balance / count ) AS newbalance44 FROM depositor NATURAL JOIN deposingView NATURAL JOIN account ;4546 CREATE VIEW accountingCityView AS47 SELECT customer_city , SUM ( newBalance ) AS sum_of_newbalance48 FROM accountingView NATURAL JOIN customer GROUP BY ( customer_city ) ;495051 −− Resultat52 CREATE VIEW resultingView AS53 ( SELECT ∗ FROM loaningCityView NATURAL FULL OUTER JOIN accountingCityView ) ;

Julia Wolters 49



Aufgabe 37

1 −− Losungen zur Vorlesung Datenbanken2 −− Losungsvorschlag zu Aufgabe 3734 −−a )5 SELECT ∗ FROM s ;67 −−b )8 SELECT DISTINCT A FROM r ;9

10 −−c )11 SELECT ∗ FROM r WHERE B = 42 ;1213 −−d )14 SELECT ∗ FROM r , s ;1516 −−e )17 SELECT DISTINCT A , F FROM r , s where C = D ;1819 −−f )20 ( SELECT ∗ FROM r1 ) UNION ( SELECT ∗ FROM r2 ) ;2122 −−g )23 ( SELECT ∗ FROM r1 ) INTERSECT ( SELECT ∗ FROM r2 ) ;2425 −−h )26 ( SELECT ∗ FROM r1 ) EXCEPT ( SELECT ∗ FROM r2 ) ;2728 −−i )29 SELECT A , r3 . B , r3 . C FROM (r1 NATURAL JOIN ( SELECT DISTINCT B , C FROM

r2 ) AS r3 ) ;30 −− oder31 SELECT ∗ FROM (r1 NATURAL JOIN ( SELECT DISTINCT B , C FROM r2 ) AS r3 )

;3233 −−j )34 SELECT ∗ FROM r JOIN s ON A = D ;3536 −−k )37 SELECT ∗ FROM r JOIN s ON B = E WHERE A < D ;3839 −−l )40 SELECT DISTINCT ∗ FROM ( SELECT D FROM s ) AS s1 , ( SELECT E , F FROM s

) AS s2 ;41 −− oder42 SELECT ∗ from ( SELECT DISTINCT D FROM s ) AS s1 , ( SELECT DISTINCT E ,

F FROM s ) AS s2 ;

50 Julia Wolters



Ubung SS 2009

43 −− oder44 SELECT DISTINCT s1 . D , s2 . E , s2 . F FROM s AS s1 , s AS s2 ;

1 −− Ubungen zur Vorlesung Datenbanken2 −− Beispielrelationen zur Aufgabenstellung von Aufgabe 3734 drop table r ;5 drop table s ;67 drop table r1 ;8 drop table r2 ;9

1011 create table r (12 A integer ,13 B integer ,14 C integer ) ;1516 create table s (17 D integer ,18 E integer ,19 F integer ) ;20 create table r1 (21 A integer ,22 B integer ,23 C integer ) ;24 create table r2 (25 A integer ,26 B integer ,27 C integer ) ;28293031 insert into r values (42 ,42 ,42) ;32 insert into r values ( 1 , 2 , 4 ) ;33 insert into r values (2 , 2 , NULL ) ;34 insert into r values ( 4 , 3 , 2 ) ;35 insert into r values ( 2 , 5 , 9 ) ;36 insert into r values ( 7 , 5 , 7 ) ;3738 insert into s values ( 4 , 5 , 6 ) ;39 insert into s values ( 4 , 5 , 8 ) ;40 insert into s values ( 5 , 5 , 8 ) ;4142 insert into r1 values ( 2 , 3 , 4 ) ;43 insert into r1 values ( 7 , 6 , 7 ) ;44 insert into r1 values ( 5 , 6 , 7 ) ;45

Julia Wolters 51



46 insert into r2 values ( 2 , 3 , 4 ) ;47 insert into r2 values ( 6 , 3 , 4 ) ;48 insert into r2 values ( 5 , 6 , 7 ) ;

52 Julia Wolters



Ubung SS 2009

Aufgabe 38

Zeigen oder widerlegen Sie ausgehend von den in der Vorlesung prasentierten Definitionen,dass in SQL folgende Aquivalenzen gelten:

(a) <> all ⇔ not in

T <> all m ⇔ T 6= all m

⇔ ∀t ∈ m(T 6= t)

⇔ ¬∃t ∈ m¬(T 6= t)

⇔ ¬(∃t ∈ m(T = t)

⇔ ¬T in m

⇔ T not in m

(b) <> some ⇔ not in

Gegenbeispiel: T = {5}, m = {0, 2, 5}T <> some m wahr T 6= 0, T 6= 2, 0, 2 ∈ mT not in m falsch T = 5 ∈ m⇒<> some��⇔ not in

(c) = some ⇔ inT = some m⇔ ∃t ∈ m T = t⇔ T in m

Julia Wolters 53



Aufgabe 39

Zeigen oder widerlegen Sie, dass die folgenden SQL-Anweisungen fur eine Relation r uberdem Schema R = (A,B) jeweils aquivalent sind. Was gilt fur eine Relation r uber demSchema R = (A)?

r rA B A1 3 1 % 2,3 ware hier gleich

null 4 null1 3 2

• select count(∗) from r

= 3

• select count(A) from r

= 2

• select count(DISTINCT A) from r

= 1

54 Julia Wolters



Ubung SS 2009

Aufgabe 40

Richten Sie sich mit den Skripten Airport.sql bzw. AirportForMySQL.sql eine Flughafen-Datenbank mit PostgreSQL oder MySQL ein und formulieren sie folgende Anfragen inder jeweiligen SQL-Syntax:

1 drop table besitztVonUnternehmen cascade ;2 drop table besitztVonPerson cascade ;3 drop table darfFliegen cascade ;4 drop table wartet cascade ;5 drop table wartung cascade ;6 drop table unternehmen cascade ;7 drop table mitarbeiter cascade ;8 drop table pilot cascade ;9 drop table person cascade ;

10 drop table flugzeug cascade ;11 drop table hangar cascade ;12 drop table flugzeugtyp cascade ;131415 create table flugzeugtyp (16 modellbez varchar (30) primary key ,17 kapazitaet smallint ,18 gewicht i n t ) ;1920 create table hangar (21 hangarNr smallint primary key ,22 kapazitaet smallint ) ;2324 create table flugzeug (25 regNr char (10) primary key ,26 modellBez varchar (30) references flugzeugtyp ,27 hangarNr smallint references hangar ,28 platzNr int ,29 unique ( hangarNr , platzNr ) ) ;3031 create table person (32 name varchar (24) ,33 adresse varchar (50) ,34 gebDatum date ,35 primary key (name , adresse ) ) ;3637 create table pilot (38 name varchar (24) ,39 adresse varchar (50) ,40 lizenzNr char (11) ,41 primary key (name , adresse ) ,42 foreign key (name , adresse ) references person ) ;

Julia Wolters 55



4344 create table mitarbeiter (45 name varchar (24) ,46 adresse varchar (50) ,47 personalNr int ,48 primary key (name , adresse ) ,49 foreign key (name , adresse ) references person ) ;5051 create table unternehmen (52 name varchar (24) primary key ) ;5354 create table wartung (55 datum date ,56 dauer interval hour to minute ,57 regNr char (10) references flugzeug ,58 primary key ( regNr , datum ) ) ;5960 create table wartet (61 regNr char (10) ,62 datum date ,63 name varchar (24) ,64 adresse varchar (50) ,65 primary key ( regNr , datum , name , adresse ) ,66 foreign key ( regNr , datum ) references wartung ,67 foreign key (name , adresse ) references mitarbeiter ) ;6869 create table darfFliegen (70 modellBez varchar (30) references flugzeugtyp ,71 name varchar (24) ,72 adresse varchar (50) ,73 primary key ( modellBez , name , adresse ) ,74 foreign key (name , adresse ) references pilot ) ;7576 create table besitztVonPerson (77 regNr char (10) references flugzeug ,78 name varchar (24) ,79 adresse varchar (50) ,80 primary key ( regNr , name , adresse ) ,81 foreign key (name , adresse ) references person ) ;8283 create table besitztVonUnternehmen (84 regNr char (10) references flugzeug ,85 name varchar (24) ,86 primary key ( regNr , name ) ,87 foreign key ( name ) references unternehmen ) ;888990

56 Julia Wolters



Ubung SS 2009

91 insert into hangar values (1 , 5) ;92 insert into hangar values (2 , 6) ;93 insert into hangar values (3 , 10) ;94 insert into hangar values (4 , 8) ;9596 insert into flugzeugtyp values (’Cessna -22-C16’ , 3 , 500) ;97 insert into flugzeugtyp values (’188 Cessna Agwagon’ , 8 , 1720) ;98 insert into flugzeugtyp values (’Convair CV 990’ , 4 , 800) ;99 insert into flugzeugtyp values (’Convair F2Y Sea Dart’ , 2 , 420) ;

100 insert into flugzeugtyp values (’Saab Tunnan’ , 4 , 1200) ;101 insert into flugzeugtyp values (’Saab J 21R’ , 9 , 170) ;102103104105 insert into flugzeug values (’CA-22-3100’ , ’Cessna -22-C16’ , 1 , 5) ;106 insert into flugzeug values (’CA-34-2346’ , ’Cessna -22-C16’ , 3 , 10) ;107 insert into flugzeug values (’CA-10-4431’ , ’188 Cessna Agwagon’ , 4 , 1) ;108 insert into flugzeug values (’CA-21-0234’ , ’188 Cessna Agwagon’ , 4 , 4) ;109 insert into flugzeug values (’CO-09-9563’ , ’Convair F2Y Sea Dart’ , 3 , 9) ;110 insert into flugzeug values (’SA-01-1823’ , ’Saab Tunnan’ , 1 , 1) ;111 insert into flugzeug values (’SA-45-0034’ , ’Saab Tunnan’ , 2 , 1) ;112 insert into flugzeug values (’SA-21-0234’ , ’Saab J 21R’ , 2 , 3) ;113114115 insert into person values (’Luther Vandros’ , ’Parkway 7, Witchtown’ , ’

1955-12-13’ ) ;116 insert into person values (’Grant Green’ , ’Main Street 231, Dallas’ , ’

1967-03-20’ ) ;117 insert into person values (’Herbie Hancock’ , ’4th Street 43, Dallas’ , ’

1971-04-11’ ) ;118 insert into person values (’John Lee Hooker’ , ’Brodway 121, Yorktown’ , ’

1923-06-17’ ) ;119 insert into person values (’Johnny Ray’ , ’Uptown 332, New York’ , ’

1973-12-22’ ) ;120 insert into person values (’Miles Davis’ , ’34th Avenue 376, New York’ , ’

1944-01-11’ ) ;121 insert into person values (’Keith Jarret’ , ’23th Avenue 12, New York’ , ’

1948-11-01’ ) ;122 insert into person values (’Joe Cool’ , ’South Road 3, Santa Barbara’ , ’

1950-12-31’ ) ;123124 insert into pilot values (’Luther Vandros’ , ’Parkway 7, Witchtown’ , ’PP-CA

-23652’ ) ;125 insert into pilot values (’Joe Cool’ , ’South Road 3, Santa Barbara’ , ’PL-CC

-10200’ ) ;126 insert into pilot values (’Johnny Ray’ , ’Uptown 332, New York’ , ’PP-CA

-20052’ ) ;127 insert into pilot values (’Miles Davis’ , ’34th Avenue 376, New York’ , ’PP-

Julia Wolters 57



SC-30000’ ) ;128 insert into pilot values (’Keith Jarret’ , ’23th Avenue 12, New York’ , ’PP-

SC-31020’ ) ;129130 insert into mitarbeiter values (’Luther Vandros’ , ’Parkway 7, Witchtown’ ,

34451) ;131 insert into mitarbeiter values (’Grant Green’ , ’Main Street 231, Dallas’ ,

54234) ;132 insert into mitarbeiter values (’Herbie Hancock’ , ’4th Street 43, Dallas’ ,

43321) ;133134 insert into unternehmen values (’Dixie’ ) ;135 insert into unternehmen values (’Carlsberg’ ) ;136137 insert into Wartung values (’2005-11-15’ ,’2 hour 10 minute’ ,’CA-22-3100’ ) ;138 insert into Wartung values (’2005-11-16’ ,’2 hour 20 minute’ ,’CA-22-3100’ ) ;139 insert into Wartung values (’2005-11-23’ ,’2 hour 10 minute’ ,’CA-22-3100’ ) ;140 insert into Wartung values (’2005-11-19’ ,’6 hour 00 minute’ ,’SA-21-0234’ ) ;141 insert into Wartung values (’2005-11-20’ ,’3 hour’ ,’SA-01-1823’ ) ;142 insert into Wartung values (’2003-11-21’ ,’4 hour’ ,’SA-01-1823’ ) ;143144 insert into wartet values (’CA-22-3100’ , ’2005-11-15’ , ’Grant Green’ , ’Main

Street 231, Dallas’ ) ;145 insert into wartet values (’CA-22-3100’ , ’2005-11-16’ , ’Herbie Hancock’ , ’4

th Street 43, Dallas’ ) ;146 insert into wartet values (’CA-22-3100’ , ’2005-11-16’ , ’Grant Green’ , ’Main

Street 231, Dallas’ ) ;147 insert into wartet values (’CA-22-3100’ , ’2005-11-23’ , ’Herbie Hancock’ , ’4

th Street 43, Dallas’ ) ;148149 insert into wartet values (’SA-01-1823’ , ’2005-11-20’ , ’Herbie Hancock’ , ’4

th Street 43, Dallas’ ) ;150 insert into wartet values (’SA-01-1823’ , ’2003-11-21’ , ’Herbie Hancock’ , ’4

th Street 43, Dallas’ ) ;151 insert into wartet values (’SA-21-0234’ , ’2005-11-19’ , ’Grant Green’ , ’Main

Street 231, Dallas’ ) ;152153 insert into darfFliegen values (’Cessna -22-C16’ , ’Luther Vandros’ , ’Parkway

7, Witchtown’ ) ;154 insert into darfFliegen values (’188 Cessna Agwagon’ , ’Luther Vandros’ , ’

Parkway 7, Witchtown’ ) ;155 insert into darfFliegen values (’Convair CV 990’ , ’Luther Vandros’ , ’

Parkway 7, Witchtown’ ) ;156 insert into darfFliegen values (’Saab Tunnan’ , ’Luther Vandros’ , ’Parkway

7, Witchtown’ ) ;157 insert into darfFliegen values (’Saab J 21R’ , ’Luther Vandros’ , ’Parkway 7,

Witchtown’ ) ;158 insert into darfFliegen values (’Convair CV 990’ , ’Joe Cool’ , ’South Road

58 Julia Wolters



Ubung SS 2009

3, Santa Barbara’ ) ;159 insert into darfFliegen values (’Cessna -22-C16’ , ’Joe Cool’ , ’South Road 3,

Santa Barbara’ ) ;160 insert into darfFliegen values (’Saab Tunnan’ , ’Joe Cool’ , ’South Road 3,

Santa Barbara’ ) ;161 insert into darfFliegen values (’Saab J 21R’ , ’Joe Cool’ , ’South Road 3,

Santa Barbara’ ) ;162 insert into darfFliegen values (’Convair CV 990’ , ’Johnny Ray’ , ’Uptown

332, New York’ ) ;163 insert into darfFliegen values (’Convair F2Y Sea Dart’ , ’Miles Davis’ , ’34

th Avenue 376, New York’ ) ;164 insert into darfFliegen values (’Convair CV 990’ , ’Miles Davis’ , ’34th

Avenue 376, New York’ ) ;165 insert into darfFliegen values (’Cessna -22-C16’ , ’Keith Jarret’ , ’23th

Avenue 12, New York’ ) ;166 insert into darfFliegen values (’188 Cessna Agwagon’ , ’Keith Jarret’ , ’23th

Avenue 12, New York’ ) ;167 insert into darfFliegen values (’Convair CV 990’ , ’Keith Jarret’ , ’23th

Avenue 12, New York’ ) ;168169 insert into besitztVonPerson values (’CA-22-3100’ , ’Luther Vandros’ , ’

Parkway 7, Witchtown’ ) ;170 insert into besitztVonPerson values (’CA-21-0234’ , ’John Lee Hooker’ , ’

Brodway 121, Yorktown’ ) ;171 insert into besitztVonPerson values (’SA-45-0034’ , ’John Lee Hooker’ , ’

Brodway 121, Yorktown’ ) ;172 insert into besitztVonPerson values (’SA-01-1823’ , ’Keith Jarret’ , ’23th

Avenue 12, New York’ ) ;173 insert into besitztVonPerson values (’SA-21-0234’ , ’Keith Jarret’ , ’23th

Avenue 12, New York’ ) ;174 insert into besitztVonPerson values (’CO-09-9563’ , ’Keith Jarret’ , ’23th

Avenue 12, New York’ ) ;175176 insert into besitztVonUnternehmen values (’CA-34-2346’ , ’Dixie’ ) ;177 insert into besitztVonUnternehmen values (’CA-10-4431’ , ’Carlsberg’ ) ;

1 drop table besitztVonUnternehmen cascade ;2 drop table besitztVonPerson cascade ;3 drop table darfFliegen cascade ;4 drop table wartet cascade ;5 drop table wartung cascade ;6 drop table unternehmen cascade ;7 drop table mitarbeiter cascade ;8 drop table pilot cascade ;9 drop table person cascade ;

10 drop table flugzeug cascade ;11 drop table hangar cascade ;12 drop table flugzeugtyp cascade ;

Julia Wolters 59



131415 create table flugzeugtyp (16 modellbez varchar (30) primary key ,17 kapazitaet smallint ,18 gewicht i n t ) ;1920 create table hangar (21 hangarNr smallint primary key ,22 kapazitaet smallint ) ;2324 create table flugzeug (25 regNr char (10) primary key ,26 modellBez varchar (30) references flugzeugtyp ,27 hangarNr smallint references hangar ,28 platzNr int ,29 unique ( hangarNr , platzNr ) ) ;3031 create table person (32 name varchar (24) ,33 adresse varchar (50) ,34 gebDatum date ,35 primary key (name , adresse ) ) ;3637 create table pilot (38 name varchar (24) ,39 adresse varchar (50) ,40 lizenzNr char (11) ,41 primary key (name , adresse ) ,42 foreign key (name , adresse ) references person ) ;4344 create table mitarbeiter (45 name varchar (24) ,46 adresse varchar (50) ,47 personalNr int ,48 primary key (name , adresse ) ,49 foreign key (name , adresse ) references person ) ;5051 create table unternehmen (52 name varchar (24) primary key ) ;5354 create table wartung (55 datum date ,56 dauer time ,57 regNr char (10) references flugzeug ,58 primary key ( regNr , datum ) ) ;5960 create table wartet (

60 Julia Wolters



Ubung SS 2009

61 regNr char (10) ,62 datum date ,63 name varchar (24) ,64 adresse varchar (50) ,65 primary key ( regNr , datum , name , adresse ) ,66 foreign key ( regNr , datum ) references wartung ,67 foreign key (name , adresse ) references mitarbeiter ) ;6869 create table darfFliegen (70 modellBez varchar (30) references flugzeugtyp ,71 name varchar (24) ,72 adresse varchar (50) ,73 primary key ( modellBez , name , adresse ) ,74 foreign key (name , adresse ) references pilot ) ;7576 create table besitztVonPerson (77 regNr char (10) references flugzeug ,78 name varchar (24) ,79 adresse varchar (50) ,80 primary key ( regNr , name , adresse ) ,81 foreign key (name , adresse ) references person ) ;8283 create table besitztVonUnternehmen (84 regNr char (10) references flugzeug ,85 name varchar (24) ,86 primary key ( regNr , name ) ,87 foreign key ( name ) references unternehmen ) ;88899091 insert into hangar values (1 , 5) ;92 insert into hangar values (2 , 6) ;93 insert into hangar values (3 , 10) ;94 insert into hangar values (4 , 8) ;9596 insert into flugzeugtyp values (’Cessna -22-C16’ , 3 , 500) ;97 insert into flugzeugtyp values (’188 Cessna Agwagon’ , 8 , 1720) ;98 insert into flugzeugtyp values (’Convair CV 990’ , 4 , 800) ;99 insert into flugzeugtyp values (’Convair F2Y Sea Dart’ , 2 , 420) ;

100 insert into flugzeugtyp values (’Saab Tunnan’ , 4 , 1200) ;101 insert into flugzeugtyp values (’Saab J 21R’ , 9 , 170) ;102103104105 insert into flugzeug values (’CA-22-3100’ , ’Cessna -22-C16’ , 1 , 5) ;106 insert into flugzeug values (’CA-34-2346’ , ’Cessna -22-C16’ , 3 , 10) ;107 insert into flugzeug values (’CA-10-4431’ , ’188 Cessna Agwagon’ , 4 , 1) ;108 insert into flugzeug values (’CA-21-0234’ , ’188 Cessna Agwagon’ , 4 , 4) ;

Julia Wolters 61



109 insert into flugzeug values (’CO-09-9563’ , ’Convair F2Y Sea Dart’ , 3 , 9) ;110 insert into flugzeug values (’SA-01-1823’ , ’Saab Tunnan’ , 1 , 1) ;111 insert into flugzeug values (’SA-45-0034’ , ’Saab Tunnan’ , 2 , 1) ;112 insert into flugzeug values (’SA-21-0234’ , ’Saab J 21R’ , 2 , 3) ;113114115 insert into person values (’Luther Vandros’ , ’Parkway 7, Witchtown’ , ’

12-13-1955’ ) ;116 insert into person values (’Grant Green’ , ’Main Street 231, Dallas’ , ’

03-20-1967’ ) ;117 insert into person values (’Herbie Hancock’ , ’4th Street 43, Dallas’ , ’

04-11-1971’ ) ;118 insert into person values (’John Lee Hooker’ , ’Brodway 121, Yorktown’ , ’

06-17-1923’ ) ;119 insert into person values (’Johnny Ray’ , ’Uptown 332, New York’ , ’

12-22-1973’ ) ;120 insert into person values (’Miles Davis’ , ’34th Avenue 376, New York’ , ’

01-11-1944’ ) ;121 insert into person values (’Keith Jarret’ , ’23th Avenue 12, New York’ , ’

11-01-1948’ ) ;122 insert into person values (’Joe Cool’ , ’South Road 3, Santa Barbara’ , ’

12-31-1950’ ) ;123124 insert into pilot values (’Luther Vandros’ , ’Parkway 7, Witchtown’ , ’PP-CA

-23652’ ) ;125 insert into pilot values (’Joe Cool’ , ’South Road 3, Santa Barbara’ , ’PL-CC

-10200’ ) ;126 insert into pilot values (’Johnny Ray’ , ’Uptown 332, New York’ , ’PP-CA

-20042’ ) ;127 insert into pilot values (’Miles Davis’ , ’34th Avenue 376, New York’ , ’PP-

SC-30000’ ) ;128 insert into pilot values (’Keith Jarret’ , ’23th Avenue 12, New York’ , ’PP-

SC-31020’ ) ;129130 insert into mitarbeiter values (’Luther Vandros’ , ’Parkway 7, Witchtown’ ,

34451) ;131 insert into mitarbeiter values (’Grant Green’ , ’Main Street 231, Dallas’ ,

54234) ;132 insert into mitarbeiter values (’Herbie Hancock’ , ’4th Street 43, Dallas’ ,

43321) ;133134 insert into unternehmen values (’Dixie’ ) ;135 insert into unternehmen values (’Carlsberg’ ) ;136137 insert into wartung values (’04-11-15’ ,’2:10:00’ ,’CA-22-3100’ ) ;138 insert into wartung values (’04-11-16’ ,’2:20:00’ ,’CA-22-3100’ ) ;139 insert into wartung values (’04-11-23’ ,’2:10:00’ ,’CA-22-3100’ ) ;140 insert into wartung values (’04-11-19’ ,’6:00:00’ ,’SA-21-0234’ ) ;

62 Julia Wolters



Ubung SS 2009

141 insert into wartung values (’04-11-20’ ,’3:00:00’ ,’SA-01-1823’ ) ;142 insert into wartung values (’03-11-21’ ,’4:00:00’ ,’SA-01-1823’ ) ;143144 insert into wartet values (’CA-22-3100’ , ’04-11-15’ , ’Grant Green’ , ’Main

Street 231, Dallas’ ) ;145 insert into wartet values (’CA-22-3100’ , ’04-11-16’ , ’Herbie Hancock’ , ’4th

Street 43, Dallas’ ) ;146 insert into wartet values (’CA-22-3100’ , ’04-11-16’ , ’Grant Green’ , ’Main

Street 231, Dallas’ ) ;147 insert into wartet values (’CA-22-3100’ , ’04-11-23’ , ’Herbie Hancock’ , ’4th

Street 43, Dallas’ ) ;148149 insert into wartet values (’SA-01-1823’ , ’04-11-20’ , ’Herbie Hancock’ , ’4th

Street 43, Dallas’ ) ;150 insert into wartet values (’SA-01-1823’ , ’04-11-21’ , ’Herbie Hancock’ , ’4th

Street 43, Dallas’ ) ;151 insert into wartet values (’SA-21-0234’ , ’04-11-19’ , ’Grant Green’ , ’Main

Street 231, Dallas’ ) ;152153 insert into darfFliegen values (’Cessna -22-C16’ , ’Luther Vandros’ , ’Parkway

7, Witchtown’ ) ;154 insert into darfFliegen values (’188 Cessna Agwagon’ , ’Luther Vandros’ , ’

Parkway 7, Witchtown’ ) ;155 insert into darfFliegen values (’Convair CV 990’ , ’Luther Vandros’ , ’

Parkway 7, Witchtown’ ) ;156 insert into darfFliegen values (’Saab Tunnan’ , ’Luther Vandros’ , ’Parkway

7, Witchtown’ ) ;157 insert into darfFliegen values (’Saab J 21R’ , ’Luther Vandros’ , ’Parkway 7,

Witchtown’ ) ;158 insert into darfFliegen values (’Convair CV 990’ , ’Joe Cool’ , ’South Road

3, Santa Barbara’ ) ;159 insert into darfFliegen values (’Cessna -22-C16’ , ’Joe Cool’ , ’South Road 3,

Santa Barbara’ ) ;160 insert into darfFliegen values (’Saab Tunnan’ , ’Joe Cool’ , ’South Road 3,

Santa Barbara’ ) ;161 insert into darfFliegen values (’Saab J 21R’ , ’Joe Cool’ , ’South Road 3,

Santa Barbara’ ) ;162 insert into darfFliegen values (’Convair CV 990’ , ’Johnny Ray’ , ’Uptown

332, New York’ ) ;163 insert into darfFliegen values (’Convair F2Y Sea Dart’ , ’Miles Davis’ , ’34

th Avenue 376, New York’ ) ;164 insert into darfFliegen values (’Convair CV 990’ , ’Miles Davis’ , ’34th

Avenue 376, New York’ ) ;165 insert into darfFliegen values (’Cessna -22-C16’ , ’Keith Jarret’ , ’23th

Avenue 12, New York’ ) ;166 insert into darfFliegen values (’188 Cessna Agwagon’ , ’Keith Jarret’ , ’23th

Avenue 12, New York’ ) ;

Julia Wolters 63



167 insert into darfFliegen values (’Convair CV 990’ , ’Keith Jarret’ , ’23thAvenue 12, New York’ ) ;

168169 insert into besitztVonPerson values (’CA-22-3100’ , ’Luther Vandros’ , ’

Parkway 7, Witchtown’ ) ;170 insert into besitztVonPerson values (’CA-21-0234’ , ’John Lee Hooker’ , ’

Brodway 121, Yorktown’ ) ;171 insert into besitztVonPerson values (’SA-45-0034’ , ’John Lee Hooker’ , ’

Brodway 121, Yorktown’ ) ;172 insert into besitztVonPerson values (’SA-01-1823’ , ’Keith Jarret’ , ’23th

Avenue 12, New York’ ) ;173 insert into besitztVonPerson values (’SA-21-0234’ , ’Keith Jarret’ , ’23th

Avenue 12, New York’ ) ;174 insert into besitztVonPerson values (’CO-09-9563’ , ’Keith Jarret’ , ’23th

Avenue 12, New York’ ) ;175176 insert into besitztVonUnternehmen values (’CA-34-2346’ , ’Dixie’ ) ;177 insert into besitztVonUnternehmen values (’CA-10-4431’ , ’Carlsberg’ ) ;

1. Geben Sie die Registrierungsnummern und die Typen aller Flugzeuge zuruck.

2. Bestimmen Sie alle Piloten, die Flugzeugtypen fliegen durfen, deren Modellbezeich-nung den Teilausdruck

”Cessna“ enthalt.

3. Ermitteln Sie die Namen aller Piloten, deren Lizenznummern mit einer Doppelnullendet.

4. Bestimmen Sie alle Flugzeuge, deren Registrierungsnummer mit”CA“ beginnt oder

mindestens siebenstellig ist.

5. Geben Sie die Registriernummern aller Flugzeuge zuruck, die zwischen dem 16.11.2006und dem 23.11.2006 mehrfach gewartet wurden.

1 −− Ubungen zur Vorlesung Datenbanken2 −− Losungsvorschlag zu Aufgabe 4034 −− a )5 SELECT regNr , flugzeugtyp FROM flugzeug NATURAL JOIN flugzeugtyp ;67 −− b )8 SELECT DISTINCT pilot .∗ FROM pilot NATURAL JOIN darfFliegen WHERE modellBez

LIKE ’%Cessna%’ ;9

10 −− c )11 SELECT name FROM pilot WHERE lizenzNr LIKE ’%00’ ;12

64 Julia Wolters



Ubung SS 2009

13 −− d )14 SELECT ∗ FROM flugzeug WHERE regnr LIKE ’CA%’ OR char_length ( regnr ) >= 7 ;15 −− alternativ :16 SELECT ∗ FROM flugzeug WHERE regnr LIKE ’CA%’ OR regnr LIKE ’_______%’ ;1718 −− e )19 SELECT regNr FROM wartung20 WHERE datum BETWEEN ’2005-11-16’ and ’2005-11-23’21 GROUP BY regNr HAVING COUNT ( DATUM ) > 1 ;

Julia Wolters 65



1 −− Aufgabe 4123 CREATE TABLE auftraege (4 anfang TIMESTAMP NOT NULL ,5 ende TIMESTAMP ,6 kunde VARCHAR ,7 beschreibung VARCHAR ,8 PRIMARY KEY ( anfang , kunde , beschreibung )9 ) ;

1011 INSERT INTO auftraege VALUES (’2006-12-06 09:00’ , ’2006-12-06 12:00’ , ’

Mueller’ , ’Nikolausen’ ) ;12 INSERT INTO auftraege VALUES (’2006-12-02 15:00’ , ’2006-12-02 15:32’ , ’Meier

’ , ’Foo’ ) ;13 INSERT INTO auftraege VALUES (’2006-12-08 12:38’ , ’2006-12-08 22:05’ , ’Meier

’ , ’Bar’ ) ;14 INSERT INTO auftraege VALUES (’2006-12-04 12:38’ , ’2006-12-05 11:11’ , ’Meier

’ , ’Foobar2’ ) ;15 INSERT INTO auftraege VALUES (’2006-04-28 23:30’ , ’2006-05-02 10:00’ , ’Meier

’ , ’Foobar3’ ) ;16 INSERT INTO auftraege VALUES (’2006-04-20 12:00’ , ’2006-12-01 12:00’ , ’

Muster AG’ , ’Testen’ ) ;1718 −− a ) Arbeitszeit pro Auftrag ausrechnen19 DROP VIEW auftraege_dauer ;20 CREATE VIEW auftraege_dauer AS21 SELECT ∗ , ( ende − anfang ) AS dauer FROM auftraege ;2223 SELECT ∗ FROM auftraege_dauer ;2425 SELECT EXTRACT ( MINUTE FROM dauer ) as minuten , EXTRACT ( HOUR FROM dauer ) as

stunden ,26 ( CASE27 WHEN EXTRACT ( MINUTE FROM dauer ) = 0 THEN 028 WHEN EXTRACT ( MINUTE FROM dauer ) < 15 THEN 1529 WHEN EXTRACT ( MINUTE FROM dauer ) < 30 THEN 3030 WHEN EXTRACT ( MINUTE FROM dauer ) < 45 THEN 4531 ELSE 6032 END ) AS minuten15 FROM auftraege_dauer ;3334 SELECT kunde , beschreibung , dauer , dauer + ((35 ( CASE36 WHEN EXTRACT ( MINUTE FROM dauer ) = 0 THEN 037 WHEN EXTRACT ( MINUTE FROM dauer ) < 15 THEN 1538 WHEN EXTRACT ( MINUTE FROM dauer ) < 30 THEN 3039 WHEN EXTRACT ( MINUTE FROM dauer ) < 45 THEN 4540 ELSE 60

66 Julia Wolters



Ubung SS 2009

41 END ) − EXTRACT ( MINUTE FROM dauer ) ) ∗ interval ’1 minute’ ) AS dauer1542 FROM auftraege_dauer ;4344 −− b ) Auftragsdauer fur jeden Auftraggeber aufaddieren4546 SELECT kunde , sum ( dauer ) FROM auftraege_dauer GROUP BY kunde ;4748 −− c ) Kunden mit Auftragen zwischen 01 .05 .2006 und 30 .11 .20064950 SELECT DISTINCT kunde from auftraege51 WHERE ende >= DATE ’2006-05-01’ AND anfang <= DATE ’2006-11-30’ ;5253 −− oder5455 SELECT DISTINCT kunde from auftraege56 WHERE ( anfang , ende ) OVERLAPS ( DATE ’2006-05-01’ , DATE ’2006-11-30’ ) ;5758 −− d ) Auftrage zwischen 20 :00 und 06 :00 Uhr oder an Wochenenden5960 SELECT ∗ FROM auftraege_dauer WHERE61 −− Zwischen 20 und 6 Uhr?62 ( extract ( hour from anfang ) between 20 and 24) or63 ( extract ( hour from anfang ) between 0 and 6) or64 ( extract ( hour from ende ) between 20 and 24) or65 ( extract ( hour from ende ) between 0 and 6) or66 ( extract ( dow from anfang ) < extract ( dow from ende ) ) or67 ( dauer > INTERVAL ’1 day’ )68 −− Anfang am Wochenende?69 or extract ( dow from anfang ) = 0 or extract ( dow from anfang ) = 670 −− Ende am Wochenende?71 or extract ( dow from ende ) = 0 or extract ( dow from ende ) = 672 −− Anfang und Ende in der Woche , aber in unterschiedlichen Wochen −

eigentlich unnotig73 or ( extract ( dow from anfang ) between 1 and 574 and extract ( dow from ende ) between 1 and 575 and ( extract ( week from anfang ) < extract ( week from ende )76 or extract ( year from anfang ) < extract ( year from ende ) ) ) ;7778 −− e ) Auftrage an denen gerade gearbeitet wird7980 SELECT ∗ FROM auftraege WHERE anfang < now ( ) AND ( now ( ) < ende OR ende IS

NULL ) ;8182 −− f ) Durchschnittliche Wochenarbeitszeit fur abgeschlossene Auftrage8384 SELECT min ( anfang ) , max ( ende ) , max ( ende ) − min ( anfang ) AS zeitraum ,85 extract ( days from ( max ( ende ) − min ( anfang ) ) ) as gesamttage ,86 sum ( dauer ) AS gesamtdauer ,

Julia Wolters 67



87 ( sum ( dauer ) / extract ( days from ( max ( ende ) − min ( anfang ) ) ) ) ∗ 7 ASwochendauer

88 FROM auftraege_dauer89 WHERE ende < now ( ) AND ende IS NOT NULL ;

68 Julia Wolters

1

Aufgabe 42

■ Beispieldatenbank (zu E-R-Diagramm Folie II-56):■ angestellter:

2000“Schmidt”“Klaus”7

1500“Langeschon”“Hans”1

GehaltNachnameVornameSsn

■angehöriger:

7“2.2.1966”1“Barbara“

AngestellterSsnGdatumGradName

■ arbeitet-für:

57

101

AbteilungNummerAngestellterSsn

2

Aufgabe 42

■ Verletzungstypen mit Beispielen: Insert • 1. Domain constraints

– a. INSERT INTO angestellter VALUES(34567, ‘Andrea’, ‘Hausmann’, - 3000);

– b. INSERT INTO arbeitet-für VALUES(1, ‘keineZahl‘);• 2. Referentielle Integritätsbedingungen

– a. INSERT INTO angehöriger VALUES(’Peter’, 1, ’31.01.1976’, 34567);

– b. INSERT INTO arbeitet-für VALUES(34567, 10);• 3. Schlüsselbedingungen

– a. INSERT INTO angestellter VALUES(null, ’Andrea’, ‘Hausmann’, 2500);

– b. INSERT INTO angestellter VALUES(1, ’Andrea’, ‘Hausmann’, 3000);

• 4. semantische Assertions– INSERT INTO angestellter VALUES(45362, ‘Barbara’, ‘Langeschon’, 1500); („Verwandte von Angestellten dürfen nicht eingestellt werden.“)

3

Aufgabe 42

■ Verletzungstypen mit Beispielen: Delete

• 2. Referentielle Integritätsbedingungen– DELETE FROM angestellter WHERE Ssn = 1;

• 4. semantische Assertions– DELETE FROM angehöriger WHERE AngestellterSsn = 7; („Angehörige ersten Grades dürfen nicht entfernt werden.“)

4

Aufgabe 42

■ Verletzungstypen mit Beispielen: Update• 1. Domain constraints

– UPDATE angestellter SET Gehalt = -3000 WHERE Ssn = 1;

• 2. Referentielle Integritätsbedingungen– UPDATE angestellter SET Ssn = 12 WHERE Ssn = 1;

• 3. Schlüsselbedingungen– UPDATE angestellter SET P-Nr = null WHERE Ssn = 1;

• 4. semantische Assertions– UPDATE angestellter SET Gehalt = 1000 WHERE Ssn = 1; ("Zu drastische Kürzung" oder "zu geringes Gehalt")



Ubung SS 2009

Aufgabe 43

Betrachten und erlautern Sie folgende SQL-Anweisung:

create table hierarchie (

angestellter varchar(20) not null,

vorgesetzter varchar(20) not null,

primary key (angestellter),

foreign key (vorgesetzter) references hierarchie on delete cascade);

(a) Erklaren Sie allgemein die Wirkung des Loschens eines Datensatzes aus der erzeugtenTabelle der zu Grunde liegenden Relationalen Datenbank.

(b) Verdeutlichen Sie Ihre Aussage aus dem vorherigen Aufgabenteil, indem Sie ausfuhr-lich die Wirkung des Loschens des Datensatzes

”(Muller, Meier)“ aus folgender Tabelle

darstellen.

angestellter vorgesetzter

Schulz SchulzMeier SchulzMuller Meier

Schmidt SchulzTupelnikus MullerRelationica TupelnikusTadagoliker Muller

(a) Beim Loschen eines Tupels werden alle referenzierenden (nicht referenzierten) Tupelauch geloscht.

(b)

angestellter vorgesetzter

Schulz SchulzMeier Schulz

(1) ��Muller ��

�MeierSchmidt Schulz

(2) ((((((Tupelnikus ��

�Muller(3) ((((

((Relationica ((((((Tupelnikus

(2) (((((((Tadagoliker ��

�Muller

Julia Wolters 73



Aufgabe 44

(Prasenzubung) Auf der Homepage zur Vorlesung steht das Gerust eines Java-ProgrammsPersonenJDBC zur Verwendung von JDBC bereit. Mit Hilfe dieses Programms ist esbisher nur moglich, Instanzen der bereitgestellen Klasse Person anzulegen. Erweitern Siedas Programm nun wie folgt:

(a) Stellen Sie eine Verbindung zu einer Datenbank im bereitgestellten PostgreSQL- oderMySQLDatenbanksystem her. Hinweise zum Aufruf des Programms finden Sie in derDatei readme.txt.

(b) Erzeugen Sie in dieser Datenbank eine Tabelle”Personendaten“, die der Speicherung

von Personendaten dient. Sorgen Sie dafur, dass diese Tabelle genau dann erzeugtwird, wenn sie noch nicht in der Datenbank existiert.

(c) Speichern sie alle vorhandenen Objektdaten in dieser Tabelle und geben Sie ihrenInhalt auf den Bildschirm aus. Loschen Sie anschließend alle Objekte und erzeugenSie diese dann erneut mit Hilfe der gespeicherten Datensatze. Geben Sie nun die neuerzeugten Objekte aus.

(d) Fuhren Sie drei SQL-Anfragen auf der Datenbank durch und geben Sie das Resultatjeweils aus.

(e) Loschen Sie alle”Jacksons“ aus der Datenbank und fugen Sie zusatzlich die Person

namens”Jackson, Five“, von der keine weiteren Daten bekannt sind hinzu. Diese

beiden Operationen sollen zusammen als eine Transaktion deklariert und ausgefuhrtwerden.

(f) Schließen Sie die Verbindung zur Datenbank.

1 import java . sql . ∗ ;2 import java . util . GregorianCalendar ;3 import java . util . StringTokenizer ;45 /∗∗6 ∗ Diese Klasse s t e l l t d i e Verbindung zu e iner SQL−Datenbank uber JDBC her

und f u h r t7 ∗ Operationen auf e iner e v e n t u e l l noch zu erzeugenden Tabe l l e ”

personendaten” durch . Die Daten8 ∗ der Tabe l l e entstammen einem Array {@link PersonenJDBC . persons } von {

@link Person}−Objekten ,9 ∗ d ie j e w e i l s d i e Daten b e z u g l i c h e iner Person en tha l t en .

10 ∗11 ∗ @author Henrik Blunck12 ∗ @author Jorg Mensmann

74 Julia Wolters



Ubung SS 2009

13 ∗ @version 1.114 ∗/15 pub l i c c l a s s PersonenJDBC {1617 pub l i c s t a t i c f i n a l String PSQL_DRIVER = "org.postgresql.Driver" ;18 pub l i c s t a t i c f i n a l String MYSQL_DRIVER = "com.mysql.jdbc.Driver" ;1920 p r i va t e Connection db ;21 p r i va t e String dbName ;22 p r i va t e String userName ;23 p r i va t e String password ;2425 // Der Datenbestand , der in d i e Datenbank i n t e g r i e r t werden s o l l26 p r i va t e Person [ ] persons = {27 new Person ("Jackson" , "Michael" , 1732345 , new GregorianCalendar

(1966 , 11 , 9) , Person . MALE ) ,28 new Person ("Bolton" , "Michael" , 5322345 , new GregorianCalendar

(1965 , 1 , 17) , Person . MALE ) ,29 new Person ("Gabriel" , "Peter" , 4632345 , new GregorianCalendar

(1959 , 4 , 5) , Person . MALE ) ,30 new Person ("Jackson" , "Jennifer" , 2323345 , new GregorianCalendar

(1980 , 7 , 23) , Person . FEMALE ) ,31 new Person ("Hooker" , "John Lee" , 1342345 , new GregorianCalendar

(1916 , 3 , 7) , Person . MALE ) ,32 new Person ("Turner" , "Tina" , 2532345 , new GregorianCalendar

(1958 , 1 , 17) , Person . FEMALE ) ,33 new Person ("Rush" , "Jennifer" , 6132345 , new GregorianCalendar

(1961 , 3 , 21) , Person . FEMALE )34 } ;3536 pub l i c PersonenJDBC ( ) {37 }3839 /∗∗40 ∗ Abarbei tung der in der Au fgabens t e l l ung angegebenen Anforderungen41 ∗/42 pub l i c void doRequiredOperations ( ) {43 // Str ing−Variab le , das j e w e i l s d i e SQL−Statements a l s Ze i chenke t t e

h a l t44 String sqlString ;45 // Das Resu l tSe t , das j e w e i l s d i e Ergebn i s se e i n z e l n e r Anfragen

h a l t46 ResultSet rs ;47 boolean tableExists = true ;48 // Test , ob Tabe l l e mit Namen ”personendaten” b e r e i t s e x i s t i e r t49 try {50 // db . getMetaData () l i e f e r t DatabaseMetaData−Objekt . g e tTab l e s

( . . ) l i e f e r t

Julia Wolters 75



51 // Resu l tSe t , dass a l l e Tabe l l en en th a l t , d i e d i e mit den durchd i e Parametern

52 // gegebenen Kr i t e r i en ubereinstimmen . next ( ) g i b t f a l s e zuruck, f a l l s ke in

53 // n achs t e s Element im Resu l tSe t e x i s t i e r t e .54 rs = db . getMetaData ( ) . getTables ("" , "" , "personendaten" , n u l l ) ;55 rs . beforeFirst ( ) ;56 tableExists = rs . next ( ) ;57 } catch ( SQLException e ) {58 reactOnSQLException (e ) ;59 }6061 // Fa l l s Tabe l l en n i ch t e x i s t i e r t e , er zeuge und f u l l e s i e62 i f ( ! tableExists ) {63 // Tabel lenschema erzeugen64 try {65 System . out . println ("Erzeuge Tabellen..." ) ;66 constructPersonDatabase ( ) ;67 } catch ( SQLException sqlException ) {68 reactOnSQLException ( sqlException ) ;69 }70 }7172 // Tabe l l e f u l l e n mit den Daten aus dem Person−Array persons73 try {74 fillPersonDatabase ( ) ;75 } catch ( SQLException sqlException ) {76 reactOnSQLException ( sqlException ) ;77 }7879 // Auslesen des gesamten Datenbestandes der Tabe l l e ” personendaten”

und Ausgabe80 try {81 sqlString = "SELECT * FROM personendaten" ;82 rs = executeQuery ( sqlString ) ;83 System . out . println ("Datenbank nach dem Einlesen des

Datenbestandes:" ) ;84 printResultSet (rs , 5) ;85 } catch ( SQLException sqlException ) {86 reactOnSQLException ( sqlException ) ;87 }8889 // Loschen des Person−Arrays und Neuanlegen mit den den Daten aus

der Personen−Datenbank90 try {91 persons = nu l l ;92 persons = extractPersonsFromDatabase ( ) ;93 } catch ( SQLException sqlException ) {

76 Julia Wolters



Ubung SS 2009

94 reactOnSQLException ( sqlException ) ;95 }969798 // Ausgabe der Objek te in persons [ ]99 System . out . println ("Ausgabe der Objekte in persons[]:" ) ;

100 f o r ( i n t i = 0 ; i < persons . length ; i++) {101 persons [ i ] . printPerson ( ) ;102 }103104 // Anfrage an d i e Datenbank und Anzeigen in der Konsole : Die

Anfrage g i b t Vor− und105 // Nachnamen a l l e r Personen , deren Nachname mit ”J” beg innt ,

geordnet nach106 // au f s t e i g enden Nachnamen zur uck .107 try {108 sqlString = "SELECT lastname, firstname FROM personendaten

WHERE" +109 " lastname LIKE ’J%’ " ;110 rs = executeQuery ( sqlString ) ;111 System . out . println ("Datenbestand selektiert mit:’ " + sqlString112 + "’" ) ;113 printResultSet (rs , 2) ;114 } catch ( SQLException sqlException ) {115 reactOnSQLException ( sqlException ) ;116 }117118 // Anfrage an d i e Datenbank und Anzeigen in der Konsole : Die

Anfrage g i b t Vor− und119 // Nachnamen und Geburtsdaten a l l e r Personen , d i e vor 1960 geboren

sind , geordnet nach120 // au f s t e i g enden Geburts tagen zur uck .121 try {122 sqlString = "SELECT lastname, firstname , dayofbirth FROM" +123 " personendaten WHERE dayofbirth < ’1960 1 1’ order" +124 " by dayofbirth" ;125 rs = executeQuery ( sqlString ) ;126 System . out . println ("Datenbestand selektiert mit:’ " + sqlString127 + "’" ) ;128 printResultSet (rs , 3) ;129 } catch ( SQLException sqlException ) {130 reactOnSQLException ( sqlException ) ;131 }132133 // Anfrage an d i e Datenbank und Anzeigen in der Konsole : Die

Anfrage g i b t Vor− und134 // Nachnamen und Geburtsdaten a l l e r we i b l i c h en Personen , d i e

zwischen 1960 und 1970

Julia Wolters 77



135 // geboren sind , geordnet nach au f s t e i g enden Geburts tagen zur uck .136 try {137 sqlString = "SELECT lastname, firstname , dayofbirth FROM" +138 " personendaten WHERE dayofbirth >= ’1960 1 1’ " +139 "and dayofbirth < ’1970 1 1’ and gender = ’female’" +140 " order by dayofbirth" ;141 rs = executeQuery ( sqlString ) ;142 System . out . println ("Datenbestand selektiert mit:’ " + sqlString143 + "’" ) ;144 printResultSet (rs , 3) ;145 } catch ( SQLException sqlException ) {146 reactOnSQLException ( sqlException ) ;147 }148149 // Einf ugen des genannten neuen Eintrages150 try {151 db . setAutoCommit ( f a l s e ) ;152 sqlString = "DELETE FROM personendaten WHERE lastname = ’

Jackson’" ;153 executeUpdate ( sqlString ) ;154 sqlString = "INSERT INTO personendaten values (’Jackson’, ’Five

’, 5, NULL, NULL)" ;155 executeUpdate ( sqlString ) ;156 db . commit ( ) ;157 db . setAutoCommit ( t rue ) ;158 } catch ( SQLException sqlException ) {159 reactOnSQLException ( sqlException ) ;160 }161162163 // Sch l i e s s en der Verbindung164 try {165 db . close ( ) ;166 } catch ( SQLException sqlException ) {167 reactOnSQLException ( sqlException ) ;168 }169 }170171 /∗∗172 ∗ Legt d i e Tabe l l e ” persondendaten” in der Datenbank an173 ∗174 ∗ @throws SQLException175 ∗/176 pub l i c void constructPersonDatabase ( ) throws SQLException {177 String sqlString ;178 Statement stmt ;179180 //Erzeugen der Tabe l l e

78 Julia Wolters



Ubung SS 2009

181 sqlString = "CREATE TABLE personendaten (lastname VARCHAR(20)," +182 " firstname VARCHAR(20), ssn integer, dayofbirth CHAR(12)," +183 " gender CHAR(6), PRIMARY KEY (ssn), CHECK" +184 " (gender in (’male’,’female’)));" ;185 stmt = db . createStatement ( ) ;186 stmt . execute ( sqlString ) ;187 }188189 /∗∗190 ∗ Fuhrt e ine Anfrage an d ie verbundende Datenbank aus .191 ∗192 ∗ @param s q l S t r i n g Das auszuf uhrende SQL−Statement a l s S t r ing .193 ∗ @return Das Resu l tSe t , das das Ergbnis der Anfrage an d i e Datenbank

en t h a l t .194 ∗ @throws SQLException195 ∗/196 pub l i c ResultSet executeQuery ( String sqlString ) throws SQLException {197 Statement statement = db . createStatement ( ResultSet .

TYPE_SCROLL_INSENSITIVE ,198 ResultSet . CONCUR_UPDATABLE

) ;199 re turn statement . executeQuery ( sqlString ) ;200 }201202 /∗∗203 ∗ Fuhrt e in Update in der verbundenden Datenbank aus .204 ∗205 ∗ @param s q l S t r i n g Das auszuf uhrende SQL−Statement a l s S t r ing .206 ∗ @return Angabe uber den Er f o l g des Updates207 ∗ @throws SQLException208 ∗/209 pub l i c i n t executeUpdate ( String sqlString ) throws SQLException {210 Statement statement = db . createStatement ( ) ;211 re turn statement . executeUpdate ( sqlString ) ;212 }213214 /∗∗215 ∗ F u l l t d i e Tabe l l e ” personendaten” mit dem Datenbestand im

PersonenArray . Vorab werden216 ∗ d ie a l t e n Tab e l l e n i n ha l t e g e l o s c h t .217 ∗218 ∗ @throws SQLException219 ∗/220 pub l i c void fillPersonDatabase ( ) throws SQLException {221 Statement stmt = db . createStatement ( ) ;222 String sqlString = "DELETE FROM personendaten" ;223 stmt . executeUpdate ( sqlString ) ;224 f o r ( i n t i = 0 ; i < persons . length ; i++) {

Julia Wolters 79



225 sqlString = "INSERT INTO personendaten VALUES(’"226 + persons [ i ] . getLastname ( ) + "’, ’"227 + persons [ i ] . getFirstname ( ) + "’, "228 + Integer . toString ( persons [ i ] . getSsn ( ) ) + ",’"229 + getSqlDateByJavaGregorianCalendar ( persons [ i ] .

getDayOfBirth ( ) ) + "’, ’"230 + persons [ i ] . getGender ( ) + "’)" ;231 stmt . executeUpdate ( sqlString ) ;232 }233 }234235 /∗∗236 ∗ Konver t i e r t e in Objekt vom Typ ein Objekt vom Typ GregorianCalendar

in237 ∗ einen S t r ing im SQL−Date−Format238 ∗239 ∗ @param ca lendar240 ∗ @return ein S t r ing im SQL−Date−Format241 ∗/242 pub l i c s t a t i c String getSqlDateByJavaGregorianCalendar (243 GregorianCalendar calendar ) {244 String ret ;245 ret = calendar . get ( java . util . Calendar . YEAR ) + " "246 + calendar . get ( java . util . Calendar . MONTH ) + " "247 + calendar . get ( java . util . Calendar . DAY_OF_MONTH ) ;248 re turn ret ;249 }250251 /∗∗252 ∗ Konver t i e r t einen S t r ing im SQL−Date−Format ( z .B. 1999 2 12) in e in253 ∗ Objekt vom Typ GregorianCalendar .254 ∗255 ∗ @param sq lDate256 ∗ e in S t r ing im SQL−Date−Format257 ∗ @return ein Objekt vom Typ GregorianCalendar .258 ∗/259 pub l i c s t a t i c GregorianCalendar getJavaGreogorianCalendareBysqlDate (260 String sqlDate ) {261 i f ( sqlDate == nu l l ) r e turn new GregorianCalendar ( ) ;262 e l s e {263 StringTokenizer tokenizer = new StringTokenizer ( sqlDate , " " ) ;264 re turn new GregorianCalendar ( Integer . parseInt ( tokenizer .

nextToken ( ) ) ,265 Integer . parseInt ( tokenizer .

nextToken ( ) ) , Integer266 . parseInt ( tokenizer . nextToken ( ) ) ) ;267 }268 }

80 Julia Wolters



Ubung SS 2009

269270 /∗∗271 ∗ Erzeugt aus dem Inha l t der Tabe l l e ” personendaten” der Datenbank e in

Array von Ins tanzen272 ∗ der Klasse Person . Ruft ex tractPersonsFromResu l tSet ( ) auf .273 ∗274 ∗ @return @throws SQLException275 ∗/276 pub l i c Person [ ] extractPersonsFromDatabase ( ) throws SQLException {277 Statement statement = db . createStatement ( ResultSet .

TYPE_SCROLL_INSENSITIVE ,278 ResultSet . CONCUR_UPDATABLE

) ;279 String sqlString = "SELECT * FROM personendaten" ;280 ResultSet rs = statement . executeQuery ( sqlString ) ;281 Person [ ] ret = extractPersonsFromResultSet (rs ) ;282 re turn ret ;283 }284285 /∗∗286 ∗ Erzeugt aus einem Resu l tSe t−Objekt e in Array von Ins tanzen der

Klasse287 ∗ Person . Wird von extractPersonsFromDatabase ( ) au f ge ru f en .288 ∗289 ∗ @param rs Das Resu l tSe t , aus dem die Personendaten zu ex t r ah i e r en

s ind .290 ∗ @return Ein Array von Person−Objekten , das d i e e x t r a h i e r t e n Daten291 ∗ e n t h a l t .292 ∗ @throws SQLException293 ∗/294 pub l i c s t a t i c Person [ ] extractPersonsFromResultSet ( ResultSet rs )295 throws SQLException {296 i n t count = 0 ;297 rs . beforeFirst ( ) ;298 whi l e ( ! rs . isLast ( ) ) {299 rs . next ( ) ;300 count++;301 }302 Person [ ] ret = new Person [ count ] ;303304 rs . beforeFirst ( ) ;305 count = −1;306 whi l e ( ! rs . isLast ( ) ) {307 rs . next ( ) ;308 count++;309 boolean female ;310 i f (rs . getString ("gender" ) != nu l l && rs . getString ("gender" ) .

equalsIgnoreCase ( Person . FEMALE_STRING ) ) {

Julia Wolters 81



311 female = true ;312 } e l s e {313 female = f a l s e ;314 }315 Person person = new Person (rs . getString ("lastname" ) , rs .

getString ("firstname" ) , rs . getInt ("ssn" ) ,getJavaGreogorianCalendareBysqlDate (rs . getString ("dayofbirth" ) ) , female ) ;

316 ret [ count ] = person ;317 }318 return ret ;319 }320321 /∗∗322 ∗ Loscht a l l e Tupel aus der Tabe l l e ” personendaten” der Datenbank .323 ∗324 ∗ @throws SQLException325 ∗/326 pub l i c void clearPersonDatabase ( ) throws SQLException {327 Statement stmt = db . createStatement ( ) ;328 String sqlString = "DELETE FROM personendaten" ;329 stmt . executeUpdate ( sqlString ) ;330 }331332 /∗∗333 ∗ @depricated He r s t e l l e n der Verbindung zur Datenbank und Setzen der

d i e334 ∗ Verbindung c ha r a k t i e r i s i e r end en A t t r i b u t e des

aufru fenden335 ∗ Objek te s336 ∗ @param inDriver337 ∗ Der Name des Tre iber s . En t sp r i ch t dem abso lu t en

Klassenpfad338 ∗ der Tr e i b e r k l a s s e339 ∗ @throws ClassNotFoundException340 ∗ f a l l s e in f a l s c h e r Tre iber s p e z i f i z i e r t wurde341 ∗ @throws SQLException342 ∗ f a l l s beim Ansprechen der Datenbank Feh ler von d i e s e r343 ∗ gemeldet werden344 ∗/345 pub l i c void establishAndConnect ( String inDriver )346 throws ClassNotFoundException , SQLException {347 Class . forName ( inDriver ) ;348 db = DriverManager . getConnection ( dbName , userName , password ) ;349 }350351 /∗∗352 ∗ Gibt e in Resu l tSe t in Tabe l lenform auf den Bi ldsch irm aus .

82 Julia Wolters



Ubung SS 2009

353 ∗354 ∗ @param rs Das Resu l tSe t , das ausgegeben werden s o l l355 ∗ @param numOfColumns356 ∗ Die Anzahl der A t t r i b u twe r t e ( d . h . Spa l t en ) der

anzuzeigenden357 ∗ Tupel ( d . h . Ze i l en )358 ∗ @throws SQLException359 ∗/360 pub l i c s t a t i c void printResultSet ( ResultSet rs , i n t numOfColumns )361 throws SQLException {362 rs . beforeFirst ( ) ;363 whi l e ( ! rs . isLast ( ) && rs . next ( ) ) {364 f o r ( i n t i = 1 ; i <= numOfColumns ; i++) {365 System . out . print (rs . getString (i ) + " " ) ;366 }367 System . out . println ( ) ;368 }369 }370371 /∗∗372 ∗ Methode zur Ausnahmebehandlung wahrend des Versuches , mit der

Datenbank373 ∗ zu kommunizieren .374 ∗375 ∗ @param sq lExcep t i on Die geworfene Ausnahme376 ∗/377 p r i va t e void reactOnSQLException ( SQLException sqlException ) {378 boolean isFirstException = true ;379 whi l e ( isFirstException | | ( sqlException . getNextException ( ) != nu l l

) ) {380 System . out381 . println ("Wahrend der Arbeit mit der Datenbank trat

folgender Fehler auf: "382 + sqlException . toString ( )383 + " Code:"384 + sqlException . getErrorCode ( ) ) ;385 System . out . println ("Die Verbindung verblieb in folgendem Status

: "386 + sqlException . getSQLState ( ) ) ;387 isFirstException = f a l s e ;388 }389 }390391 /∗∗392 ∗ @param args393 ∗/394 pub l i c s t a t i c void main ( String args [ ] ) {395 PersonenJDBC personenJDBC = new PersonenJDBC ( ) ;

Julia Wolters 83



396 // B e r e i t s t e l l e n der Eingabeparameter f u r d i e Verbindung mit derDatenbank ; p r u f t

397 // vorab , ob e in Passwort a l s Aufrufparameter ubergeben wurde398 i f ( args . length == 3) {399 personenJDBC . dbName = args [ 0 ] ;400 personenJDBC . userName = args [ 1 ] ;401 personenJDBC . password = args [ 2 ] ;402 } e l s e i f ( args . length == 2) {403 personenJDBC . dbName = args [ 0 ] ;404 personenJDBC . userName = args [ 1 ] ;405 // Fa l l s ke in Passwort ubergeben wurde , s e t z e das Passwort a l s

l e e r en S t r ing406 personenJDBC . password = "" ;407 } e l s e {408 // Fa l l s d i e Parameteranzahl n i ch t s i n n v o l l i s t , beende das

Programm409 // mit Fehlermeldung410 System . err411 . println ("Benutzung: java PersonenJDBC <dbname> <dbusername

> "412 + "[<dbuserpasswd >] \n" ) ;413 System . err414 . println ("Beispiel: java PersonenJDBC jdbc:postgresql://

padme.uni-muenster.de/mustermanndb mustermann}\n" +415 " oder java PersonenJDBC jdbc:mysql://cpc.uni-

muenster.de/mustermanndb mustermann" ) ;416417 System . exit (1 ) ;418 }419420 try {421 i f ( personenJDBC . dbName . startsWith ("jdbc:postgresql:" ) ) {422 System . out . println ("Benutze PostgreSQL." ) ;423 personenJDBC . establishAndConnect ( PersonenJDBC . PSQL_DRIVER ) ;424 } e l s e i f ( personenJDBC . dbName . startsWith ("jdbc:mysql:" ) ) {425 System . out . println ("Benutze MySQL." ) ;426 personenJDBC . establishAndConnect ( PersonenJDBC . MYSQL_DRIVER )

;427 } e l s e {428 System . err . println ("Unbekanntes Datenbankssystem.\n" ) ;429 System . exit (1 ) ;430 }431 } catch ( java . lang . ClassNotFoundException e ) {432 System . out433 . println ("Verbindung mit der Datenbank fehlgeschlagen:

Treiber nicht gefunden. "434 + e . toString ( ) ) ;435 } catch ( SQLException sqlException ) {

84 Julia Wolters



Ubung SS 2009

436 personenJDBC . reactOnSQLException ( sqlException ) ;437 }438439 // Fuhrt d i e in der Au f gabens t e l l ung genannten Anforderungen aus .440 personenJDBC . doRequiredOperations ( ) ;441 }442 }

Julia Wolters 85



Aufgabe 45

1 −− Aufgabe 4523 −− geanderte Definition von depositor4 create table depositor (5 customer_name char (20) not nul l ,6 account_number char (10) not nul l ,7 primary key ( customer_name , account_number ) ,8 foreign key ( customer_name )9 references customer

10 on delete cascade ,11 foreign key ( account_number )12 references account13 on delete cascade14 ) ;1516 −− alle Konten mit Anzahl ihrer Inhaber17 create view account_customer_count as18 select account_number , count ( customer_id ) as customer_count19 from depositor20 group by account_number ;2122 −− Loschen eines Kunden verhindern , wenn es ein Konto mit Kontostand > 0

gibt , das nur diesem Kunden gehort23 create trigger customer_delete124 before delete on customer25 referencing old row as orow26 f o r each row27 when28 ( exists29 ( select account_number30 from account natural join

account_customer_count natural joindepositor

31 where customer_id = orow . customer_id andbalance > 0 and customer_count = 1) )

32 begin33 rollback work34 end ;3536 −− Beim Loschen eines Kunden alle Konten mit Kontostand 0 loschen , die nur

diesem Kunden gehoren37 create trigger customer_delete238 before delete on customer39 referencing old row as orow40 f o r each row41 begin

86 Julia Wolters



Ubung SS 2009

42 delete from account43 where account_number in44 ( select account_number45 from account natural join

account_customer_count naturaljoin depositor

46 where customer_id = orow .customer_id and balance = 0 andcustomer_count = 1)

47 end ;

Julia Wolters 87



Aufgabe 46

1 −− Aufgabe 4623 −− Erzeugen der Sicht :45 CREATE VIEW customersInBrooklyn AS6 SELECT customer_name , customer_street , customer_city7 FROM customer NATURAL JOIN8 ( SELECT DISTINCT customer_name9 FROM depositor NATURAL JOIN account NATURAL JOIN branch

10 WHERE branch_city = ’Brooklyn’ ) AS temp111 UNION12 SELECT customer_name , customer_street , customer_city13 FROM customer NATURAL JOIN14 ( SELECT DISTINCT customer_name15 FROM borrower NATURAL JOIN loan NATURAL JOIN branch16 WHERE branch_city = ’Brooklyn’ ) AS temp2 ;1718 −− Entziehen aller Rechte an den an der Sicht beteiligten Tabellen :1920 REVOKE ALL ON customer FROM Joe ;21 REVOKE ALL ON branch FROM Joe ;22 REVOKE ALL ON depositor FROM Joe ;23 REVOKE ALL ON account FROM Joe ;24 REVOKE ALL ON borrower FROM Joe ;25 REVOKE ALL ON loan FROM Joe ;2627 −− Vergeben eines Leserechtes fur die erzeugte Sicht :2829 GRANT SELECT ON customersInBrooklyn TO Joe ;3031 −− analog fur Mary3233 −− Bemerkung : Reihenfolge ist unerheblich , Revoken der Recht fur Tabellen

wirkt34 −− sich nicht auf Rechte fur Sichten aus .

88 Julia Wolters



Ubung SS 2009

Aufgabe 47

1 Aufgabe 47 :23 c l a s s Person ( extent personen key name ) {45 attribute struct Pname {string Vorname , string Nachname} name ;67 relationship set<Person> bin−Mutter−von−Kind8 inverse Person : : bin−Kind−von−Mutter ;9

10 relationship set<Person> bin−Vater−von−Kind11 inverse Person : : bin−Kind−von−Vater ;1213 relationship Person bin−Ehepartner−von14 inverse Person : : bin−Ehepartner−von ;1516 relationship Person bin−Kind−von−Mutter17 inverse Person : : bin−Mutter−von−Kind ;1819 relationship Person bin−Kind−von−Vater20 inverse Person : : bin−Vater−von−Kind ;2122 relationship set<Person> bin−Kind−von23 inverse Person : : bin−Elternteil−von−Kind ;2425 relationship set<Person> bin−Elternteil−von−Kind26 inverse Person : : bin−Kind−von ;27 } ;28293031323334 Inverse Beziehung zur n :1−Beziehung "bin-Kind-von-Mutter" ist :35 "bin-Mutter-von-Kind" ( 1 : n−Beziehung )36 und nicht : bin−Elternteil−von−Kind" (2:n-Beziehung)3738394041 select42 struct (43 nachname: p.name.Nachname ,44 mutter: p.mutter.name,45 vater: p.vater.name46 )

Julia Wolters 89



47 from p in personen48 where p.name.Vorname = ’Hans’

90 Julia Wolters



Ubung SS 2009

Aufgabe 48

(a) • Entitat = Ansammlung von Attributwerten

• Objekte enthalten Werte von Methoden (Zugang auf Werte sollte uber Methodenerfolgen)

• Gleichheitsbegriff

(b) OO : Unterschied auch bei gleichen Attributwerten (z.B. Speicheradressen) relationa-le: Gleichheit von zwei Tupeln bei Gleichheit aller Alttributwerte

(c) Geschindigkeit / Aufwand

• aufwandiger beim Erzeugen, Loschen, Modifizieren

• notig wegen

– Transaktionsmanagement

– Sichherheitsmanagement

– Integritatssicherung

(d) • Nicht moglich durch Programm, Objekte ja gerade zugreifbar uber Referenz.

• 1. Losung: Garbage Collection (mark and sweep)

– markiere Objekte wie folgt:1. direkt durch das Programm referenzierte Objekte2. von solchen Objekten referenzierte Objekte

– Losche markierte Objekte

• 2. Losung: Zahler von Referenzen

– jedes Objekt zahlt die auf sich verwiesenen Objekte

– Erzeugen / Entfernen einer Referenz→ Benachrichtigung und Aktualitat des ZahlersZahler = 0 ⇒ Objekt zerstoren.

(e) weitere Elemente des OODBMS

• Transaktionsmanagement

• Sicherheitsmanagement

• Integritatssicherung

Julia Wolters 91



Aufgabe 49

Geben Sie in Stichpunkten an, in welchen Punkten ein objektorientiertes DBMS einem re-lationalen DBMS uberlegen ist, welche dieser Punkte bereits ein objektrelationales DBMSerfullt und worin ein objektrelationales DBMS einem objektorientierten DBMS uberlegenist. Nennen Sie jeweils ein typisches Anwendungsbeispiel.Empfehlen Sie dann fur jeden der folgenden Anwendungsbereiche eine der oben genanntenDBMSTechnologien und begrunden Sie kurz Ihre Empfehlung.

(a) Entwicklung eines Systems zur Unterstutzung der Konstruktionsabteilung im Ma-schinenbau. Hier werden vor allem Vektordaten erstellt, manipuliert und gespeichert.Zusatzlich sind in der Arbeitsvorbereitung betriebsspezifische Informationen und An-gaben zur Arbeitszeit hinzuzufugen.

• Komplexe Datentypen

• Viele Datentypen - spezifische Manipulation / komplexe Anfragen→ Methoden fur einzelne Objektklassen erwunscht, hohere Programmiersprachefur Anfragen

⇒ OO-DBMS

(b) Entwicklung eines Systems zur Unterstutzung elektronisch abzugebener Stimmzettelbei der nachsten Kommunalwahl. Unabhangig von Sicherheitsaspekten liegt die Auf-gabe Ihrer Komponente insbesondere in der schnellen Analyse und Aufbereitung derWahlerstimmen unter verschiedenen Gesichtspunkten.

• Simple Datentypen

• große Datenmenge, schnelle Analyse wichtig

⇒ relationale DBMS

(c) Entwicklung eines Systems zur Unterstutzung von Jingles im Horfunk. Zum einenmussen in Anwendungsprogrammen erzeugte Tondateien abgespeichert und geladenwerden, zum anderen sollen bei Angabe eines Titels bestimmte vorher festgelegteAusschnitte eines Musikstucks aus der Datenbank geladen und abgespielt werden.

• große Datentypen

• Jingels enthalten Teile von Musikstucken→ Verweise erwunscht zwecks Vermei-dung von Redundanz

• Anfragen, Anforderungen sind simpel und fest vorgegeben

⇒ OR-DBMS

92 Julia Wolters



Ubung SS 2009

Thomas’ Schreibregel • uberflussige Schreiboperationen werden nicht durchgefuhrt• Transaktion Ti will write(A) ausfuhren

– TS(Ti) < W-timestamp(A)· der zu schreibende Wert ist veraltet· der Wert muss nicht geschrieben werden

– ansonsten keine Anderungen

• resultierende Scheldules sind Sicht-serialisierbar

Aufgabe 52

Geben Sie zu dem folgenden Schedule fur jede Schreib- und Leseoperation die jeweilsveranderten Werte der Zeitstempel fur das Zeitstempelprotokoll an.Werden Transaktionenzuruckgesetzt?

T1 T2 T31 read(A)2 read(A)3 read(B)4 read(B)5 write(A)6 read(A)7 write(A)8 write(B)9 write(B)

Chronologische Reihenfolge:

1 TS(T1) = 1 R - TS(A) = 12 TS(T2) = 2 R - TS(A) = 23 TS(T3) = 3 R - TS(B) = 34 R - TS(B) = 35 W - TS(A) = 26 R - TS(A) = 37 W - TS(A) = 38 W - TS(B) = 39 TS(1) = 1 < R - TS(B) = 3

Moglichkeit 1: T1 zurucksetztenMoglichkeit 2: Thomas’ Schreibregel

Julia Wolters 93

Musterl osung - WordPress.comon nicht jede Buchung kon-trolliert werden, das geht bei dem tern aren...

Documents

Transcript of Musterl osung - WordPress.comon nicht jede Buchung kon-trolliert werden, das geht bei dem tern aren...