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 [email protected]