Übung Datenbanksysteme Besprechung Midtermklausur

1. Übung DatenbanksystemeBesprechung Midtermklausur 18.12.2002

2. Informationen vorweg Wichtig: • 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 )

3. Aufgabe 1: Umsetzung ER zu Relationenschema

4. 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.

5. 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.

6. 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

7. 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 !

8. 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!

9. Aufgabe 1c Setzen 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. • Kunde Kunde: (KundenNr: integer, Vorname: string, Nachname: string, GebDatum: date) • Konto & hat Konto: (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

10. 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 analog Kontenauszug: (KontoNr: integer, Datum_Erst: date, Startdatum: date, Guthaben: numeric)

11. 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 !

12. 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)

13. 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'}

14. 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}

15. 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

16. Aufgabe 2d Relationale Algebra: Die Namen der Hersteller, die mindestens einen der Wünsche vom Kunden „Hinz Kunz“ herstellen • Wünsche von „Hinz Kunz“ Q1: s 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 Angeboten Q2: (Angebot Hersteller) • Wünsche und Angebote „zusammenbringen“ Q1 Q2

17. Aufgabe 2d (2) (sK_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 PH_Name((sK_Name=“Hinz Kunz“ (Wünsche)) (Angebot Hersteller)) Damit sind auch keine Duplikate mehr da !

18. Aufgabe 2e Domä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) Ù (1)  $ kn,w ([P,kn,w] Î Wünsche ) Ù (2) • $ hnr,pr,v ([P,hnr,pr,v] Î Angebot)}(3) • Ist notwendig, da wir sonst alle Werte aus dem Wertebereich von P bekommen • & (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

19. 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