Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Übung Datenbanksysteme

Besprechung Midtermklausur

18.12.2002

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Informationen vorwegWichtig: • Noten gibt es erst bei der Final• Punkte von Midterm (40) und Final (60) werden aufaddiert• Punkteliste am Lehrstuhl oder unter

http://www3.in.tum.de/lehre/WS2002/DBS-uebung/midterm-punkte.pdf

Etwas Statistik:• Teilgenommen haben 153 Studenten/Studentinnen• Der Punkteschnitt ist 27,1• Die Maximalpunktzahl ist 39,5• Die Minimalpunktzahl ist 1• (alles auf einer Datenbank ausgewertet )

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Aufgabe 1: Umsetzung ER zu Relationenschema

Kunde Kontohat

Datum

Betrag

Auszahlung/Einzahlung

1

N

bezahlt

1

KontoNr

GuthabenNachname

Geburtsdatum

Überziehungs-limit

1

Kontenauszuggelistet

N N

M

Datum_Erstellt

Startdatum

erstellt

N

Vorname

Gesamtguthaben

1

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Informationen zum ER-Schema• Ein Kunde wird durch Vorname, Nachname und

Geburtsdatum eindeutig identifiziert. • Jeder Kunde kann ein oder mehrere Konten haben,

die durch Kontonummern eindeutig bestimmt werden.

• Auf seinen Konten kann der Kunde jeweils Ein- oder Auszahlungen durchführen.

• Zu jeder Ein- oder Auszahlung werden der Betrag und das Datum gespeichert.

• Dabei werden für Einzahlungen positive Beträge, für Auszahlungen dagegen negative Beträge angegeben.

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Informationen zum ER-Schema (2)

• Weiterhin kann sich ein Kunde einen Kontenauszug über die Bewegungen auf einem Konto erstellen lassen.

• In einem solchen Kontenauszug werden das Datum der Erstellung, das Gesamtguthaben zu diesem Zeitpunkt und alle Ein-/Auszahlungen zwischen einem Startdatum und dem Erstellungsdatum erfasst.

• Da Kunden diese Zeiträume selbst festlegen können, kann eine Ein-/Auszahlung auf mehreren Kontenauszügen erscheinen.

• Wichtig: Alle Datumsangaben schließen auch die Uhrzeit mit ein, sodass Datensätze (im selben Konto) am selben Tag auch unterschieden werden können.

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Aufgabe 1 a

Ermitteln Sie mögliche Teilschlüsselkandidaten von Auszahlung/Einzahlung

• Kandidaten aus dem Diagramm:– Attribute Datum, Betrag

• Fremdschlüssel KontoNr• Information im Text: Alle Datumsangaben

schließen auch die Uhrzeit mit ein, sodass Datensätze (im selben Konto) am selben Tag auch unterschieden werden können.

• => Datum ist Teilschlüssel in Auszahlung/Einzahlung

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Aufgabe 1b)

Überlegen Sie, wo sie anstelle von wertbasierten Schlüsseln besser Identifier-Schlüssel wie fortlaufende Nummern verwenden. Begründen Sie kurz ihre Entscheidung

• Entity Kunde:– Zusammengesetzter Schlüssel– Schlüssel Fremdschlüssel in Konto– Also ID-Schlüssel einführen– Aber: bisheriger Schlüssel ist eindeutig,

man muss nichts dazuerfinden !

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Aufgabe 1b) (2)

• Konto: – Kontonummer ist bereits ein ID-Schlüssel !

• Auszahlung/Einzahlung & Kontenauszug– Zusammengesetzte Schlüssel– Fremdschlüssel in gelistet

Aber: – In Gelistet nur einmal Overhead für

Kontonummer– Abbildungregel für schwache Entitäten!

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Aufgabe 1cSetzen Sie das oben angegebene E/R-Diagramm mit den Ergebnissen

aus a) und b in ein relationales Schema um. Ergänzen Sie die Datentypen und markieren Sie die Schlüssel.

• KundeKunde: (KundenNr: integer, Vorname: string, Nachname: string,

GebDatum: date)

• Konto & hatKonto: (KontoNr: integer, KundenNr: integer, Guthaben: numeric,

ÜLimit: numeric)

• Für hat wird der Fremdschlüssel KontoNr eingebettet • KundenNr ist nicht Teil des (Primär-)schlüssels !• Beziehung hat wurde oft vergessen oder nicht eingebettet

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Aufgabe 1c (2)• Ein/Auszahlung & bezahlt

EinAuszahlung: (KontoNr: integer, Datum: date, Betrag: numeric)

• Datum haben wir aus Teilaufgabe a)• KontoNr wird durch bezahlt eingebettet• KontoNr ist Teil der Schlüssels, da Ein/Auszahlung schwache

Entität ist• Wenn KontoNr schon eingebettet ist (wg. Schwacher Entität),

dann nicht noch eine Beziehungstabelle für bezahlt erstellen !

• Kontenauszug & erstellt analogKontenauszug: (KontoNr: integer, Datum_Erst: date,

Startdatum: date, Guthaben: numeric)

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Aufgabe 1c (3)

• Gelistet is n-m-Verbindung

Umsetzung mit separater Tabelle

Gelistet: (Datum: date, KontoNr: integer, Datum_Erst: date)

• Alle Bestandteile der Schlüssel müssen eingebettet werden

• KontoNr ist bei beiden Tabellen identisch, also nur einmal !

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Aufgabe 2: Anfragen

Relationenschema

• Kunde: (K_Name, K_Land)

• Wünsche: (K_Name, P_Nr, Wichtigkeit)

• Produkt: (P_Nr, Bezeichnung)

• Hersteller (H Nr, H_Name, H_Land, Rechtsform)

• Angebot (P_Nr, H Nr, Preis, Anzahl_Verfügbar)

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Aufgabe 2a)

Tupelkalkül: Die Namen und die Rechtsform der Hersteller, die in den USA ihren Sitz haben

• Selektion und Projektion auf einer Tabelle

{[h.H_Name, h.Rechtsform] |

h Hersteller h.H_Land='USA'

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Aufgabe 2b)Tupelkalkül: Alle Wünsche (P_Nr, Bezeichnung, Wichtigkeit)

von Kunde „Max Moritz“

• Wir brauchen einen Join mit Produkt, um die Bezeichnung zu erhalten

• Wir brauchen keinen Join mit Kunde, da der Kundenname Teil von Wünsche ist

{[ w.P_Nr, p.Bezeichnung, w.Wichtigkeit] | w Wünsche w.K_Name=”Max Moritz” p Produkt p.P_Nr=w.P_Nr}

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Aufgabe 2c

• SQL: Alle Wünsche (P_Nr, Bezeichnung, Wichtigkeit) von Kunde „Max Moritz“

• Lösung wie in Aufgabe 2b), nur die Syntax ist leicht anders

SELECT w.P_Nr, p.Bezeichnung, w.Wichtigkeit

FROM Wünsche w, Produkt p

WHERE w.K_Name=“Max Moritz“ AND

p.P_Nr=w.P_Nr

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Aufgabe 2dRelationale Algebra: Die Namen der Hersteller, die

mindestens einen der Wünsche vom Kunden „Hinz Kunz“ herstellen

• Wünsche von „Hinz Kunz“

Q1: K_Name=“Hinz Kunz“ (Wünsche) • Wir brauchen keinen Join zu Kunde, da der Kundenname

schon in Wünsche ist• Herstellername zu Produkten in den AngebotenQ2: (Angebot Hersteller) • Wünsche und Angebote „zusammenbringen“

Q1 Q2

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Aufgabe 2d (2)

( K_Name=“Hinz Kunz“ (Wünsche)) (Angebot Hersteller)

• Warum geht das ?• P_Nr Fremdschlüssel in Wünsche und Angebote,

dadurch direkter Join möglich

• Fast fertig, nur noch die Projektion fehlt

H_Name(( K_Name=“Hinz Kunz“ (Wünsche)) (Angebot

Hersteller))

Damit sind auch keine Duplikate mehr da !

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Aufgabe 2eDomänenkalkül: Die P_Nr von Produkten, die sich niemand wünscht und

niemand produziert Idee: Von allen Produkten die abziehen, die einen Eintrag in Wünsche

oder Angebot haben

{[P]|bez ([P,bez] Produkt kn,w ([P,kn,w] Wünsche hnr,pr,v ([P,hnr,pr,v] Angebot

(1) Ist notwendig, da wir sonst alle Werte aus dem Wertebereich von P bekommen

(2) & (3) funktionieren nach dem gleichen Prinzip:P ist P_Nr aus Produkt.Es darf kein Eintrag mit diesem Wert für P_Nr in Wünsche bzw. Angebot gebenDaher darf es keine Belegungen der restlichen Spalten geben, die mit P in Wünsche bzw. Angebot wären

Besprechung Midtermklausur DBS WS 2002/2003 04/19/23

Schlussbemerkungen

• Musterlösung gibt es, wenn ich die Kommentare in der Besprechung eingearbeitet habe – Anfang Januar

• Einsichtnahme: 8.1.2003, 11-12 Uhr oder nach Vereinbarung

• Keine Nachholklausur • Bei Nachfragen zu Formalia oder Inhalt bitte

Mail an mich fischerp@in.tum.de