Übung Datenbanksysteme Besprechung Midtermklausur 18.12.2002 Besprechung Midtermklausur DBS WS 2002/2003 16.05.2016 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/midtermpunkte.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 16.05.2016 Aufgabe 1: Umsetzung ER zu Relationenschema Geburtsdatum KontoNr 1 Nachname hat Kunde N Konto 1 1 Vorname Guthaben Überziehungslimit erstellt bezahlt 1 N N Startdatum Betrag Auszahlung/ Einzahlung N gelistet Kontenauszug M Gesamtgut haben Datum Datum_Ers tellt Besprechung Midtermklausur DBS WS 2002/2003 16.05.2016 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 Einoder 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 16.05.2016 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 16.05.2016 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 16.05.2016 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 16.05.2016 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 16.05.2016 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 Besprechung Midtermklausur DBS WS 2002/2003 16.05.2016 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) Besprechung Midtermklausur DBS WS 2002/2003 16.05.2016 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 16.05.2016 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 16.05.2016 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 16.05.2016 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 16.05.2016 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 16.05.2016 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 Besprechung Midtermklausur DBS WS 2002/2003 16.05.2016 Aufgabe 2d (2) (s 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 PH_Name((s K_Name=“Hinz Kunz“ (Wünsche)) (Angebot Hersteller)) Damit sind auch keine Duplikate mehr da ! Besprechung Midtermklausur DBS WS 2002/2003 16.05.2016 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 (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 geben Daher darf es keine Belegungen der restlichen Spalten geben, die mit P in Wünsche bzw. Angebot wären Besprechung Midtermklausur DBS WS 2002/2003 16.05.2016 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] Besprechung Midtermklausur DBS WS 2002/2003 16.05.2016