1. Übungsblatt3.0 VU Datenmodellierung

13. März 2013

Allgemeines

In diesem Übungsteil werden Sie den Entwurf einer kleinen Datenbank üben,deren Überführung in das Relationenschema, sowie die relationale Algebraund den Relationenkalkül kennenlernen.

Lösen Sie die Beispiele eigenständig, denn bei der Prüfung und in der Pra-xis sind Sie auch auf sich alleine gestellt. Wir weisen Sie darauf hin, dassabgeschriebene Lösungen mit 0 Punkten beurteilt werden.

Geben Sie ein einziges PDF Dokument ab. Erstellen Sie Ihr Abgabedoku-ment computerunterstützt. Wir akzeptieren keine gescannten handschriftli-chen PDF-Dateien.

Deadlines

05.04. 06:55 Uhr Upload über den CourseManager13.04. 12:00 Uhr Feedback im CourseManager verfügbar

Hinweise

1. Für dieses Übungsblatt werden maximal 5 Punkte vergeben.

2. Sie müssen mindestens einen Punkt auf die Abgabe bekommen. Wenn Sie wenigerals einen Punkt auf das Blatt bekommen, oder kein Blatt abgegeben haben, sindSie nicht zur ersten Leistungsüberprüfung zugelassen.

3. Im CourseManager ist ersichtlich, wieviele Punkte Sie auf die Abgabe bekom-men haben. Weiters können Sie dort auf das Feedback zu Ihrer Abgabe zugreifen.

1

EER

Aufgabe 1 (EER Diagramm erstellen) [0.5 Punkte]Zeichnen Sie folgenden Sachverhalt in einem EER-Diagramm. Verwenden Sie dazu die(min, max)-Notation. Nullwerte sind nicht erlaubt. Eine Unterstützung bei der Erstellungvon EER Diagrammen bietet das Tool dia (http://live.gnome.org/Dia). Achtung: imDiagramm Editor EER auswählen!

Für eine Firma, die internationale Projekte durchführt, soll eine Datenbankangelegt werden, um bei Schulungen und Projekten zukünftig einen besserenÜberblick zu bekommen.

Die Firma ist in mehreren Stockwerken angesiedelt wobei jeder Stock eindeu-tig durch die Stocknummer (STOCK) identifiziert ist. In einem Stock befindensich mehrere Zimmer mit einer pro Stock eindeutigen Zimmernummer (ZNR)und einer Traktbezeichnung (TRAKT) welche nur mit A, B oder C befülltist. Ein Zimmer kann ein Meetingraum oder ein Büro sein. Meetingräumehaben eine Kapazität (KAP) und eine Info, ob der Meetingraum für Tele-fonkonferenzen nutzbar ist (TELKO). Bei einem Büro sind die Anzahl derSchreibtische bekannt (TISCHE) als auch die Anzahl der digitalen Telefone(VOIP). Eine Abteilung ist in einem oder auch mehreren Stockwerken ange-siedelt, wobei von dieser der Name (NAME) und eine eindeutige Kennnum-mer (KNR) gespeichert werden soll. In einer Abteilung arbeiten verschiedeneTeams an verschiedenen Projekten. Ein Team setzt sich aus mehreren Mit-arbeitern zusammen, hier soll gespeichert werden seit wann (SEIT) der Mit-arbeiter in dem Team ist. Das Team ist durch eine eindeutige Teamnummer(TEAMNR) gekennzeichnet. Zusätzlich hat es auch einen Namen (NAME).Ein Mitarbeiter hat eine eindeutige Personalnummer (PNR), eine eindeutigeSozialversicherungsnummer (SVNR), er hat eine Adresse (ADRESSE), eineHandynummer (MOBILE), ein Gehalt (GEHALT) und ein Eintrittsdatum(SEIT). Eine Abteilung wird von genau einem Mitarbeiter geleitet, das glei-che gilt für ein Team - hier wird genau ein Teamleiter festgelegt. Jeder Mit-arbeiter kann in mehreren Teams mitwirken, jedoch maximal ein Team undmaximal eine Abteilung leiten. Weiters ist anzugeben, wer der Vorgesetzteeines Mitarbeiters ist. Es gibt auch Schulungen, die ein Mitarbeiter besuchenkann. Eine Schulung ist eindeutig identifizierbar durch das Thema (THEMA)und das Datum (DATUM) an dem sie stattfindet. Außerdem wird auch eininterner Preis (PREIS) gespeichert. Es ist bekannt welche Mitarbeiter wel-che Schulungen besucht haben. Ein Projekt wird durch seine Projektnummeridentifiziert (PNR), es hat einen Namen (NAME), eine Durchführungssprache(SPRACHE) und auch ein Projektbudget (BUDGET) zur Verfügung. Pro-jekte werden für verschiedene Länder durchgeführt (z.B. wird das ProjektMaut4712 für Belgien, Russland und Australien realisiert). Ein Land wirddurch den Ländercode (LCODE) identifiziert, eine festgelegte Verhandlungs-sprache (SPRACHE) ist ebenso bekannt. Es soll weiters gespeichert werden,

2

in welchen Projekten welche Mitarbeiter welche Aufgabenbereiche überneh-men. Ein Aufgabenbereit wird durch seine Bezeichnung (BEZEICHNUNG)identifiziert.

Aus strategischen Gründen möchte die Führungsebene die neue Datenbanknutzen, um die Meetingkultur ein wenig verbessern. Dazu ist es notwendig,projektspezifische Meetings in die Datenbank aufzunehmen. Hierfür werdenMeetings gespeichert, die durch eine Meetingnummer (MNR) identifiziertwerden, ein Datum (DATUM), und die Zeitspanne (VON), (BIS) werdenebenso vermerkt. Weiters soll es optional möglich sein, pro Meeting einenLink (LINK) zui vermerken, wo alle Unterlagen aufgelistet sind, damit diesespäter leichter aufzufinden sind. Ein Meeting wird für ein Projekt, in einemMeetingraum, mit mehreren Mitarbeitern abgehalten. Es gibt auch Meetings,die keinem Projekt zugeordnet sind.

Lösung: Siehe Abbildung 1.

3

Abbildung 1: Lösung von Aufgabe 14

Abbildung 2: EER Diagramm zu Aufgabe 3

Aufgabe 2 (EER Beispiel selbst erstellen) [0.5 Punkte]Ihr Kollege Harald hat Sie gebeten, ihm ein wenig Nachhilfe zu geben. Erstellen Siefür ihn ein kleines Übungsbeispiel zum Thema EER-Modellierung. Das Beispiel solltemindestens 5 Entitäten, 5 Beziehungen, eine Generalisierung und eine schwache Entitätenthalten. Vielleicht bringen Sie in dem Beispiel auch noch eine rekursive oder eineternäre Beziehung unter. Sie erhalten die Punkte für die textuell ausformulierte Angabesowie für die Musterlösung.

Aufgabe 3 (Überführung ins Relationenschema) [0.5 Punkte]Überführen Sie folgendes EER-Diagramm (Abbildung 2) ins Relationenschema. Nullwer-te sind nicht erlaubt. Beachten Sie hierbei, dass Sie möglichst wenige Relationen ver-wenden. Unterstreichen Sie sämtliche Primärschlüssel, schreiben Sie die Fremdschlüsselkursiv und stellen Sie sicher, dass ein Fremdschlüssel eindeutig der passenden Relationzugeordnet werden kann.

Lösung:

Adresse(AID, STRASSE, NUMMER, ORT, PLZ)Person(PID, VNAME, NNAME, GEBDAT, Adresse.AID )Zweigstelle(NAME, TELEFON, EMAIL, Adresse.AID,

Mitarbeiter.PID )

5

Abbildung 3: EER Diagramm zu Aufgabe 4

Entlehnkarte(NR, GUELTIG, GEBUEHR, Person.PID )Mitarbeiter(Person.PID, SVNR, EINDAT, KONTONR,

GEHALT, Zweigstelle.NAME )Entlehnung(EID, ADAT, FDAT, Entlehnkarte.NR )Kuenstler(KID, NAME)Media(MNR, JAHR, TITEL)Buch(Media.MNR, UMFANG)Film(Media.MNR, FORMAT)Exemplar(Media.MNR, ENR, KAUFDAT)autor(Buch.MNR, Kuenstler.KID)spielt_mit(Film.MNR, Kuenstler.KID)entleiht(Entlehnung.EID, Exemplar.ENR, Exemplar.MNR)Rueckgabe(Entlehnung.EID, RDAT, Zweigstelle.NAME )

Aufgabe 4 (Fehlersuche) [0.5 Punkte]Gegeben ist das folgende EER-Diagramm (Abbildung 3).Dazu wurde das folgende (fehlerhafte !) Relationenschema erstellt:

Adresse (AID, STRASSE, ORT)Mitarbeiter (SVNR, NAME, POSITION, Adresse.AID )Dienstplan (DATUM, VON, BIS)Ruhepause (Mitarbeiter.SVNR, Dienstplan.DATUM, VON, BIS)Kunde (KID, SEIT, Adresse.AID)Firma (Kunde.KID, USTNR, EMAIL, FNAME)Person (NAME, TELEFON)betreut (Mitarbeiter.SVNR, Kunde.KID)Typ (TID, BEZEICHNUNG)

6

Fahrzeug (FGNR, FARBE, MARKE)ist_von (Type.ID, Fahrzeug.FGNR)hat (Kunde.KID, Fahrzeug.FGNR)

Finden Sie mindestens 4 Fehler, die sich bei der Überführung des EER-Diagrammsins Relationenmodell eingeschlichen haben, und korrigieren Sie das Relationenschemaentsprechend. Wie lautet das korrekte Relationenschema? Beachten Sie, dass NULL-Werte nicht erlaubt sind.

Lösung:Folgende Fehler wurden korrigiert:

• Der schwache Entitätstyp Dienstplan wurde nicht korrekt abgebildet, da zur ein-deutigen Identifizierung auch der Schlüssel von Mitarbeiter notwendig ist.

• Der Entitätstyp Person erbt alle Attribute der Entität Kunde, deshalb muss hierder Schlüssel der Entität Kunde auch in Person als Schlüssel angegeben werden.

• In der Beziehung hat fehlt das Relationsattribut SEIT. Wenn eine Beziehung einAttribut angeführt hat, so muss dies auch in die jeweilige Relation eingefügt werden.

• Beim Entitätstyp Kunde wurde der Schlüssel der Entität Adresse hinzugefügt. Dader Schlüssel der Entität Kunde nur aus der KundenID (KID) besteht, darf AID nichtdem Schlüssel der Entität Kunde hinzugefügt werden.

• Beim Entitätstyp Firma wurden zu viele Attribute als Schlüssel angegeben. DaKunde.KID bereits den eindeutigen Schlüssel darstellt, muss USTNR als normalesAttribut geführt werden.

Korrigiertes Relationenmodell:

Adresse (AID, STRASSE, ORT)Mitarbeiter (SVNR, NAME, POSITION, Adresse.AID )Dienstplan (Mitarbeiter.SVNR, DATUM, VON, BIS)Ruhepause (Mitarbeiter.SVNR, Dienstplan.DATUM, VON, BIS)Kunde (KID, SEIT, Adresse.AID )Firma (Kunde.KID, USTNR, EMAIL, FNAME)Person (Kunde.KID, NAME, TELEFON)betreut (Mitarbeiter.SVNR, Kunde.KID)Typ (TID, BEZEICHNUNG)Fahrzeug (FGNR, FARBE, MARKE)ist_von (Type.ID, Fahrzeug.FGNR)hat (Kunde.KID, Fahrzeug.FGNR, SEIT)

7

Relationale Algebra - Relationenkalkül

Um Ihnen die Erstellung Ihrer Abgabe zu den folgenden Aufgaben zu erleichtern, ha-ben wir unter http://dbai.tuwien.ac.at/education/dm/resources/symbols.html ei-ne Liste mit den wichtigsten Symbolen der relationalen Algebra zusammengestellt. Siekönnen diese per copy/paste in Ihr Word/OpenOffice/... Dokument einfügen. Zusätzlichsind die entsprechenden LATEX Befehle vermerkt.

Aufgabe 5 (Primitive Operatoren) [0.5 Punkte]Drücken Sie die Operationen Join (�), Durchschnitt (∩), Semi-Join (�) und Anti Semi-Join (.) mit Hilfe der primitiven Operatoren (σ, Π, ×, ρ, −, ∪) aus.

Lösung:Es wird angenommen, dass zwei Relationen S und T gebeben sind. Hierbei umfasst dasSchema von S g + s viele Attribute (S1, . . . Ss, G1, . . . Gg) und das Schema von T g + tviele Attribute (T1, . . . Tt, G1, . . . Gg).

(a) S � T ≡ ΠS.G1,...S.Gg ,S1,...,Ss,T1,...,Tt

(σS.G1=T.G1∧...∧S.Gg=T.Gg(S × T )

)(b) S ∩ T ≡ S − (S − T )

(c) S � T ≡ ΠS.G1,...S.Gg ,S1,...,Ss

(σS.G1=T.G1∧...∧S.Gg=T.Gg(S × T )

)(d) S . T ≡ S −

(ΠS.G1,...S.Gg ,S1,...,Ss

(σS.G1=T.G1∧...∧S.Gg=T.Gg(S × T )

))Aufgabe 6 (Äquivalenzen) [0.5 Punkte]Gegeben sind folgende relationale Ausdrücke über R(BCA), S(BD) und T (AC). Über-prüfen Sie, ob die folgenden Ausdrücke in relationaler Algebra äquivalent sind. Falls ja,so erklären Sie warum. Falls nein, geben Sie eine Begründung an.

(a) r1 : σC 6=2

(S � T

)und r2 : S � σC 6=2(T )

(b) r3 : ΠC

(σC≥50

(ΠBC(R) ∩ σC>30

(ΠBC(R)

)))und r4 : σC≥50

(ΠC(R)

)(c) r5 : R � T und r6 : T �ΠBC(R)

(d) r7 : σC 6=12∧¬A=13

(ΠAC(R) ∪ T

)und r8 : σA 6=13

(σC 6=12

(T ∪ΠAC(R)

))(e) r9 : σC=12(T ) ∩ σA>30

(ΠAC(R)

)und r10 : σA>30∧C=12

(ΠAC(R) ∪ T

)(f) r11 : ΠAD(R � S) und r12 : ΠA(R) �ΠD(S)

Lösung:

(a) Ja: Die Selektion bezüglich C 6= 2 kann vor oder nach dem Join erfolgen.

8

(b) Ja: Zunächst gilt ΠBC(R) ∩ σC>30

(ΠBC(R)

)≡ σC>30

(ΠBC(R)

). Die Selektion be-

züglich C > 30 kann vernachlässigt werden, da in der Folge begüglich C ≥ 50 selek-tiert wird. Weiters kann die Projektion auf BC in r3 entfernt werden, weil danachohnehin auf C projiziert wird.

(c) Nein: Es gilt T �R ⊆ T �ΠBC(R). Deshalb kann es sein, daß r6 mehr Tupel umfasstals r5.

(d) Ja: Die Konjunktion in der Selektionsbedingung kann aufgebrochen werden. Weitersgilt X ∪ Y = Y ∪X und ϕ ∧ ψ ≡ ψ ∧ ϕ.

(e) Nein: Falls T keine Tupel und R nur das Tupel (A : 31, B : 0, C : 12) enthält, sogibt r9 – im Gegensatz zu r10 – die leere Menge zurück.

(f) Nein: Der Join in r12 degeneriert zu einem Kreuzprodukt. Dies ist nicht der Fall inr11.

Aufgabe 7 (Größenabschätzung) [0.5 Punkte]Gegeben sind die Relationen R(ABD) mit 11 Tupeln, S(CDE) mit 3 Tupeln undT (BEA) mit 8 Tupeln.Geben Sie die minimale bzw. maximale Größe (= Anzahl der Tupel) der durch die

folgenden Ausdrücke entstehenden Relationen an:

(a) ΠAE(T ) ∪ΠAE(R � S) Lösung: (min 8/max 41)

(b) S� T Lösung: (min 8/max 11)

(c) ΠE(T × σB<5R)−ΠES Lösung: (min 0/max 8)

Aufgabe 8 (Formalisieren von Anfragen) [0.5 Punkte]Gegeben ist das folgende Relationenschema:

• P = Pizza(PizzaName, Kalorien, Preis, Besonderheit)

• K = Kunde(KundenNr, Name, Wohnort, Addresse, Alter)

• S = Sonderangebot(PizzaName, Monat, Jahr, Rabatt)

• B = bestellt(KundenNr, PizzaName, Tag, Monat, Jahr)

Drücken Sie folgende Anfragen in relationaler Algebra aus.

(a) Wir suchen die Kundennummer aller jungen Leute (unter 30) aus Wien, die einePizza mit mehr als 5000 Kalorien bestellt haben, während diese als Sonderangebotangeboten wurde.

(b) Gesucht sind Kundennummer, Preis, und Kalorien aller Bestellungen von vegetari-schen Pizzen (Besonderheit=’vegetarisch’), welche noch nie im Sonderangebot waren.

9

Betrachten Sie nun den folgenden Ausdruck in relationaler Algebra. Beschreiben Sie ineigenen Worten, welches Ergebnis dieser Ausdruck liefert.

(c) σWohnort6=’Wien’

(K �

(ΠPizzaName,KundenNr(B)÷ΠPizzaName

(σJahr=2012(S)

)))Lösung:

(a) ΠKundenNr

(σAlter<30,Wohnort=’Wien’(K) �

((B � S) �

(σKalorien≥5000(P )

)))(b) ΠKundenNr,Preis,Kalorien

(B�(

ΠPizzaName

(σBesonderheit=’vegetarisch’(P )

)−ΠPizzaName(S)

)�

P)

(c) Es werden jene Kunden gesucht, die nicht in Wien wohnen, aber alle Sonderangebotevon 2012 bestellt haben.

Aufgabe 9 (Relationenkalkül) [0.5 Punkte]Übersetzen Sie die Anfragen, die Sie in Aufgabe 8 (a) und Aufgabe 8 (b) in relationalerAlgebra formuliert haben, in sichere Ausdrücke des Tupel- und Domainkalküls. GebenSie bei jeder der Anfragen an, in welchem Kalkül sie formuliert ist.

Lösung:

(a) Tupelkalkül:{k.KundenNr

∣∣∣ k ∈ K ∧ k.Alter < 30 ∧ k.Wohnort = ’Wien’ ∧ ∃b ∈

B(b.KundenNr = k.KundenNr∧∃p ∈ P

(p.PizzaName = b.PizzaName∧p.Kalorien ≥

5000 ∧ ∃s ∈ S(s.PizzaName = b.PizzaName ∧ s.Monat = b.Monat ∧ s.Jahr =

b.Jahr)))}

Domänenkalkül:{

[k]∣∣∣ ∃n,w, d, a([k, n, w, d, a] ∈ K ∧ a < 30 ∧ w = ’Wien’ ∧

∃p, t,m, j(

[k, p, t,m, j] ∈ B ∧ ∃l, s, b(

[p, l, s, b] ∈ P ∧ l ≥ 5000 ∧

∃r([p,m, j, r] ∈ S

))))}(b) Tupelkalkül:

{b.kundennr, p.preis, p.kalorien

∣∣∣ b ∈ B ∧ ∃p ∈ P(p.PizzaName =

b.PizzaName∧p.Besonderheit = ’vegetarisch’∧¬∃s ∈ S(s.PizzaName = b.PizzaName

))}Domänenkalkül:

{[k, l, s]

∣∣∣ ∃p, t,m, j([k, p, t,m, j] ∈ B ∧ ∃b(

[p, l, s, b] ∈ P ∧

b = ’vegetarisch’ ∧ ¬∃m′, j′, r(

[p,m′, j′, r] ∈ S)))}

10

Gesamter Stoff

Aufgabe 10 (Falsche Merksätze) [0.5 Punkte]Ihr Kollege Harald Heast erklärt einige wichtige Zusammenhänge, die er sich für diePrüfung merken will. Leider hat er manche Dinge falsch verstanden. Finden Sie dieFehler, begründen Sie warum manche Erklärungen falsch sind, und stellen Sie die falschenAussagen richtig.

(a) Wenn ich ein ER Diagramm in ein Relationenschema überführe, muß ich für jedeGeneralisierung einen neuen künstlichen Schlüssel erzeugen.

(b) Weiters haben rekursive N:M Beziehungen den Vorteil, daß die Schlüsselattributeim Relationenschema nur einmal gespeichert werden müssen, womit Speicherplatzgespart wird.

(c) Alle Operatoren in relationaler Algebra können mit Hilfe der Basisoperatoren – so-gar ohne Kreuzprodukt “×” – ausgedrückt werden. Dadurch kann man komplexereAusdrücke einfach mit Hilfe dieser wenigen Operatoren formulieren.

(d) Ein Schlüssel muss immer minimal sein. Das heißt ein Schlüssel darf keine überflüs-sigen Attribute enthalten.

(e) Der Begriff eines sicheren Ausdrucks lässt sich für den Tupelkalkül einfacher definie-ren als für relationale Algebra.

Lösung:

(a) Falsch: Es reicht den Schlüssel der allgemeinsten Entität an die Subklassen zu “ver-erben”.

(b) Falsch: Im Relationenschema muß für eine rekursive Beziehung der Fremdschlüsselzweimal gespeichert werden, um beide Partner der rekursiven Beziehung korrektzuordnen zu können.

(c) Falsch: Ohne Kreuzprodukt und Joins ist es unmöglich von einer Menge von gege-benen Relationen eine neue Relationen zu erhalten, die alle Attribute der Relationenin der Menge umfasst.

(d) Richtig: Folgt aus der Definition eines Schlüssels.

(e) Falsch: Jeder Ausdruck in relationaler Algebra sicher. Dies ist allerdings für denTupelkalkül nicht der Fall.

11