pdf-1 - Universität Bremen
Werbung
Anwendungssystem eines Reisebüros Hausarbeit im Bereich Datenbanksysteme Bremen, 17. Februar 2003 Christian Duddeck Bismarckstr. 272 28205 Bremen Email: [email protected] Matrikelnr.: 1187241 Inhaltsverzeichnis Einleitung...................................................................................................................... 1 1 Anwendungsbeschreibung ........................................................................... 1 2 Entity-Relationship-Diagramm.................................................................... 2 2.1 Graphische Darstellung....................................................................................................2 2.2 Integritätsbedingungen .....................................................................................................4 2.3 Verwendete Sprachmittel .................................................................................................5 2.3.1 Entity-Typ, Attribut ..........................................................................................................5 2.3.2 Relationship........................................................................................................................5 2.3.3 Funktionale Beziehung .....................................................................................................6 2.3.4 Vererbungsbeziehung (ist-Beziehung) ...........................................................................6 2.3.5 (Primär-)Schlüssel..............................................................................................................6 2.3.6 Kardinalitäten.....................................................................................................................7 2.4 Textuelle Notation ............................................................................................................7 2.4.1 Entity-Typen ......................................................................................................................7 2.4.2 Relationships ......................................................................................................................8 3 Übersetzung in ein Relationales Schema ..................................................... 9 3.1 Übersetzung der Entity-Typen........................................................................................9 3.2 Übersetzung der Relationships......................................................................................10 3.3 Übersetzung der Integritätsbedingungen.....................................................................11 4 Sichten ........................................................................................................ 12 4.1 Drei-Ebenen-Architektur...............................................................................................12 4.2 Beispiele für Sichten in SQL..........................................................................................12 5 Standardanfragen........................................................................................ 13 Einleitung Diese Hausarbeit beschäftigt sich mit dem Datenbankentwurf für ein Reisebüro-Anwendungssystem. Zunächst wird das Anwendungssystem informell beschrieben und anschließend das zugehörige Datenbankmodell in einem konzeptionellen Entwurf mittels eines Entity-Relationship-Diagramms graphisch dargestellt. Mit in dem Diagramm enthalten sind auch Integritätsbedingungen. Das ER-Diagramm inkl. der Integritätsbedingungen ist Grundlage für eine Übersetzung in ein relationales Schema. Beispielhaft werden abschließend verschiedene Sichten und einige Standardanfragen in SQL vorgestellt. Quellen für diese Ausarbeitung: • Andreas Heuer, Gunter Saake: Datenbanken, Konzepte und Sprachen 2., aktualisierte und erweiterte Auflage 2000, MITP-Verlag, Bonn • Material zur Vorlesung „Datenbanksysteme“ im WS 2002/03, VAK 03-731 http://db.informatik.uni-bremen.de/teaching/courses/ws2002_dbs (Stand: 30.01.2003) Prof. Dr. Martin Gogolla, Universität Bremen 1 Anwendungsbeschreibung In einem Reisebüro wird ein Anwendungssystem benötigt, das Kundeninformationen im Zusammenhang mit Vorgangsbuchungen verwaltet. Für die jeweiligen Kunden bietet das Reisebüro verschiedene Leistungen an, die dieser in einem Vorgang als Paket kaufen kann. Zu diesen Leistungen gehören Flüge, Hotelzimmer, Veranstaltungen (bspw. an einem Urlaubsort) und das Ausleihen von Mietwagen. Für zusätzliche Wünsche des Kunden gibt es die Möglichkeit, eine Sonderleistung zu buchen. Weitere Leistungsangebote sind natürlich vorstellbar und auch modellierbar. Die verschiedenen Leistungen müssen in dem Anwendungssystem genau beschrieben und einem bestimmten Zeitraum zugeordnet werden, in dem die Leistung gültig ist bzw. in dem sie erbracht wird. Jede Leistung wird für eine bestimmte Menge von Personen gebucht, wobei der Kunde nicht Teil dieser Menge sein muss. Die benötigten Informationen zu diesen Personen müssen ebenfalls in dem Anwendungssystem des Reisebüros erfasst werden können. In diesem Modell sind das Name, Adresse, Geburtsdatum, Telefon, Email und Zusatzinformationen (Raucher/Nichtraucher, Allergien, Rollstuhlfahrer). Der Kunde ist in diesem Anwendungsbeispiel eine Person mit einer Kundennummer. Die möglichen Typen von Leistungen sind an einen bestimmten Leistungsgegenstand gebunden, welcher jeweils spezieller als die allgemeine Leistung definiert werden muss. Eine Flugleistung wird einer bestimmten eindeutigen Flugnummer, sowie einem Start- und Zielflughafen zugeordnet. Wegen der Größe eines Flughafens ist es zudem wichtig, das Abflug- bzw. Ankunftsterminal zu benennen. Zu jedem Flug hat das Reisebüro ein bestimmtes Kontingent von Fluggastsitzplätzen zur Verfügung, die gebucht werden können. Wenn der Kunde ein Hotelzimmer mietet, muss das Hotel mit einer bestimmten Kategorie (Anzahl der Sterne) und Ausstattung (Swimmingpool, Golfanlage) gewählt werden können. Die Hotelzimmer werden anhand der Zimmernummer innerhalb eines Hotels unterschieden. Wichtige Informationen sind auch die Zimmerlage (Meerblick) und der Tarif (Vollpension, Halbpension). Eine Veranstaltungsleistung wird zusätzlich beschrieben durch einen Veranstaltungstitel und optionale Sitzplatzinformationen. In einer Mietwagenleistung wird für den Kunden ein Auto aus der gewünschten Wagenklasse (Kleinwagen, Limousine) gebucht. Die Zusatzinformationen dieser Leistung sind das Kennzeichen des Wagens und die konkrete Bezeichnung (Mercedes E-Klasse). Außerdem wird festgehalten, an welcher Mietwagenstation einer Mietwagenfirma er das Auto abholt und wo er es wieder abgibt. Die Sonderleistung bietet dem Reisebüro die Möglichkeit, dem Kunden eine zusätzliche Leistung zu verkaufen, die von keiner der vorherigen Leistungen beschrieben wurde. Informationen, 1 die nicht in der Leistungsbeschreibung auftauchen, sollen in einem speziellen Notizfeld festgehalten werden können. Die meisten Leistungen werden an einem bestimmten Ort erbracht. Deshalb müssen die jeweiligen Orte in dem Anwendungssystem integriert und nach Kategorien geordnet sein. Als Kategorien werden Flughäfen, Hotels, Veranstaltungsorte und Mietwagenstationen unterschieden. Jeder Ort trägt einen eindeutigen Kurzcode und zusätzlich eine KlartextBezeichnung. Der Preis jeder Leistung soll nicht direkt an eine Leistung gebunden sein, damit das Reisebüro Rabatt- oder Lastminuteangebote flexibler abwickeln kann. Alle Leistungen in einem Vorgang erzeugen bzgl. des Leistungspreises eine Zahlungssumme, die der Kunde zu begleichen hat. Wenn diese Zahlungssumme feststeht, muss sofort entschieden werden, ob der Kunde eine Anzahlung leisten soll und zu welchen Konditionen. Der Kunde könnte aufgefordert werden, einen bestimmten Anteil der Leistungen direkt zu zahlen oder alternativ einen festen (Mindest-)Betrag. Nach der Anzahlung folgen dann die Restzahlungen. Bei jeder Zahlung (auch der Anzahlung) soll der Kunde eine Zahlungsart auswählen können (z.B. bar, per Scheck, Kreditkarte oder auf Rechnung). Zusätzlich zu dem System der Vorgangsbuchungen für seine Kunden, möchte das Reisebüro Direktmailing durchführen und hat dafür speziell Mitarbeiter angestellt. Diese kümmern sich um Prospektinformationen und besondere Angebote, die den Kunden geschickt werden sollen. Die Reisebüro-Mitarbeiter sollen dabei die Möglichkeit haben, Kundeninformationen wie Name, Adresse, Telefon und Email in der Datenbank zu pflegen, benötigen aber nicht den Zugriff auf Vorgänge, Leistungen, Orte und Zahlungsvorgänge.1 2 Entity-Relationship-Diagramm 2.1 Graphische Darstellung Auf der folgenden Seite ist das konzeptionelle Schema der Reisebüro-Datenbank als ERDiagramm dargestellt. Danach schließt sich eine Erläuterung der verwendeten (graphischen) Sprachmittel an. 1 Auf diese Systemanforderung wird in dem Kapitel 4.2 Beispiele für Sichten in SQL eingegangen. 2 3 ist ist Bezeichnung Kurzcode ORT ist [0,1] Firmenname MIETWAGENSTATION miZiel miStart Notiz SONDERLEISTUNG Wagenklasse Bezeichnung Kennzeichen MIETWAGEN Platzangaben Titel VERANSTALTUNG Tarif Zimmerlage HOTELZIMMER MaxFluggäste Flug# FLUG Ausstattung veranOrt [1,1] Ankunftsterminal hzGehörtZu flNach [1,*] [1,*] Zimmer# [0,*] flVon Kategorie HOTEL FLUGHAFEN [0,*] Abflugsterminal ist ist ist ist ist [1,1] gebuchtFür [1,*] LEISTUNG Leistungsbeschr EndeDatumZeit BeginnDatumZeit LeistIndex# teilVon Preis [0,*] Adresse Name Kunden# Zusatzinformation Email Telefon Geburtsdatum PERSON ist KUNDE gebuchtVon [1,1] VORGANG Vorgangs# Konzeptionelles Schema (Ausschnitt) einer Reisebüro-Datenbank zaGehörtZu [1,1] ANZAHLUNG ist ZAHLUNG Mindestbetrag Prozent Zahlart DatumZeit Betrag Zahlung# 2.2 Integritätsbedingungen Integritätsbedingungen (oder Zusicherungen) sollen gewährleisten, dass in einem Datenbanksystem nur Daten gespeichert werden, die bzgl. des Anwendungssystems semantisch korrekt sind. In dem konzeptionellen Schema der Reisebüro-Datenbank sind bereits Integritätsbedingungen enthalten und zwar in Form von Kardinalitäten, auf die in Kapitel 2.3.6 eingegangen wird, und Primärschlüsseln, die an den Unterstreichungen erkennbar sind und die in Kapitel 2.3.5 erklärt werden. Dazu werden nachfolgend verschiedene weitere Zusicherungen in einer Ad-hoc-Notation beschrieben. Die Kunden# des Entity-Typs KUNDE muss eindeutig sein, wird also als weiterer Schlüssel verlangt: I: forAll k1, k2 : KUNDE ▪ k1.Kunden# = k2.Kunden# ⇔ k1 = k2 Außerdem sollten Kunden voll geschäftsfähig, d.h. mindestens 18 Jahre alt sein. Die PseudoFunktion decrementYears() subtrahiert von einem Datum eine bestimmte Anzahl von Jahren und liefert als Ergebnis wieder ein Datum. II: forAll k : KUNDE ▪ decrementYears( today(), 18 ) >= k.Geburtsdatum Eine Leistung kann nur für eine Person erbracht werden, die bereits geboren wurde. III: forAll l : LEISTUNG, p : PERSON ▪ p ∈ l->gebuchtFür ⇒ p.Geburtsdatum >= date( l.BeginnDatumZeit ) Sofern eine Leistungsbeschreibung sich auf einen Zeitraum bezieht, sollte das Enddatum dem Beginndatum folgen. IV: forAll l : LEISTUNG ▪ l.BeginnDatumZeit is not null and l.EndeDatumZeit is not null ⇒ l.BeginnDatumZeit <= l.EndeDatumZeit Anzahlungen sollten im Bereich von 0 bis 100 Prozent liegen. V: forAll a : ANZAHLUNG ▪ a.Prozent >= 0 and a.Prozent <= 100 Der Mindestbetrag einer Anzahlung darf den Gesamtpreis der Leistungen des entsprechenden Vorgangs nicht überschreiten. sum() bildet die Summe über eine Menge von Leistungspreisen. VI: forAll v : VORGANG, a : ANZAHLUNG ▪ a->zaGehörtZu.Vorgangs# = v.Vorgangs# and a.Mindestbetrag is not null ⇒ a.Mindestbetrag <= sum( { l.Preis | l ∈ v->teilVon } ) Die Summe der Zahlungen zu einem Vorgang darf ebenfalls den Gesamtpreis der entsprechend zugeordneten Leistungen überschreiten. VII: forAll v : VORGANG ▪ sum( { l.Preis | l ∈ v->teilVon } ) <= sum( { z.Betrag | z ∈ v->zaGehörtZu } ) ANZAHLUNGS-Entities bilden immer den Anfang einer Kette von Zahlungen und haben deswegen die Zahlung# 1. VIII: forAll a : ANZAHLUNG ▪ a.Zahlung# = 1 4 Es darf immer nur eine Anzahlung pro Vorgang geben. Danach folgen normale Zahlungen. IX: forAll a1, a2 : ANZAHLUNG ▪ a1->zaGehörtZu.Vorgangs# = a2->zaGehörtZu.Vorgangs# ⇔ a1 = a2 Mietwagen-Kennzeichen sind eindeutig. X: forAll mw1, mw2 : MIETWAGEN ▪ mw1.Kennzeichen = mw2.Kennzeichen ⇔ mw1 = mw2 Die Menge von Zusicherungen, die hier beschrieben sind, ist sicherlich nicht erschöpfend. Als Beispiel mag dies aber wohl genügen. 2.3 Verwendete Sprachmittel 2.3.1 Entity-Typ, Attribut Jedes Objekt aus der Anwendungsbeschreibung ist als Entity-Typ2 mittels eines Rechtecks dargestellt. Attribute einer Entity sind durch eine Kante mit der Entity verbunden und werden in einem abgerundeten Rechteck angezeigt. Attribute bezeichnen Eigenschaften von Objekten. KUNDE Kunden# Abbildung 1: Entity mit Attribut In der obigen Abbildung ist die Entity KUNDE zu sehen. Das Attribut Kunden# bezeichnet eine eindeutige Kundennummer und ist hier ohne Angabe eines Datentyps. Es wäre an dieser Stelle denkbar, Kunden# als Integer zu deklarieren: Kunden#: integer.3 2.3.2 Relationship Beziehungen (Relationships – Abbildung 2) zwischen einzelnen Objekten bzw. Entities werden durch Rauten graphisch definiert, die durch eine Kante mit den jeweiligen Entity verbunden ist. gebuchtFür ist eine Beziehung, die LEISTUNG mit PERSON verknüpft und damit ausdrückt, dass Personen für eine Leistung gebucht werden können. LEISTUNG gebuchtFür PERSON Abbildung 2: Relationship Es gibt auch Attribute, die Relationships zugeordnet werden. Dies ist der Fall bei der FLUGleistung, die über flVon in Beziehung steht mit dem Start-FLUGHAFEN, wobei die Angabe des Abflugterminals hier eine zusätzliche Information ist, die an die konkrete Beziehung flVon geknüpft ist: Abflugsterminal FLUGHAFEN flVon FLUG Abbildung 3: Attributverknüpfung mit Relationship Sprachlich wird nicht unbedingt zwischen einem Entity-Typ und einer Entity unterschieden. Entity meint eine Instanz eines Entity-Typs. Anstelle von «Entity-Typ» wird aber oft einfach «Entity» verwendet. 3 Da die Datentypen nicht im Vordergrund stehen, habe ich darauf verzichtet, den Attributen Typen zuzuordnen. 2 5 2.3.3 Funktionale Beziehung Wenn ein Entity immer maximal zu einer weiteren Entity in Relation steht, spricht man auch von einer funktionalen Beziehung. Ein besonders gutes Beispiel ist in Abbildung 4 zu sehen. Jedem HOTELZIMMER ist maximal ein HOTEL zugeordnet.4 Die Entity-Typen VERANSTALTUNG und ORT stehen ebenfalls in einer funktionalen Beziehung (veranOrt), und zwar in einer partiellen. Nach der Modellierung ist es nicht zwingend erforderlich, einer Veranstaltung einen Ort zuzuordnen, und falls doch, dann höchstens ein Mal.5 HOTEL HOTELZIMMER hzGehörtZu Kategorie Zimmer# Ausstattung Zimmerlage Tarif Abbildung 4: Funktionale Beziehung 2.3.4 Vererbungsbeziehung (ist-Beziehung) Eine besondere Art der Relationships ist die Vererbungsbeziehung (Abbildung 5), die auch ist-Beziehung genannt wird. Mit diesem Sprachmittel können vorhandene Objekte bzw. Entities spezialisiert werden. Zum Beispiel ist ein KUNDE eine spezialisierte Form von PERSON. Er besitzt dieselben Eigenschaften bzw. Attribute wie eine PERSON. Zusätzlich wird ihm allerdings eine Kundennummer zugeordnet. KUNDE ist PERSON Kunden# Name Adresse Geburtsdatum Telefon Email Zusatzinformation Abbildung 5: Vererbung Der Pfeil der ist-Beziehung aus Abbildung 5 zeigt die Richtung der Vererbung an. 2.3.5 (Primär-)Schlüssel Die Attribute Name, Adresse und Geburtsdatum der Entity PERSON sind unterstrichen, da es sich hierbei um den Primärschlüssel für eine Person handelt. Schlüssel sind allgemein eine (minimierte) Untermenge der Attribute eines Entity-Typs, die dazu ausreicht, mit den Werten dieser Attribute Entities eindeutig zu unterscheiden. Der Primärschlüssel ist nur ein besonders ausgezeichneter Schlüssel von mehreren möglichen Schlüsseln. Der KUNDE erbt in Abbildung 5 den Primärschlüssel von PERSON. Die Kunden# ist ein weiterer eindeutiger Schlüssel für Entities vom Typ KUNDE, wird aber in dem ER-Diagramm nicht angezeigt. Im ER-Diagramm auf Seite 3 ist es immer exakt ein Hotel, das sich mit Hotelzimmern in Relation befindet, so dass hier genauer eine totale funktionale Beziehung gemeint ist. Dies nimmt darauf Bezug, dass ein Hotelzimmer ohne Hotel keinen Sinn ergibt. Auf Kardinalitäten wird aber später noch eingegangen. 5 Denkbar ist eine Veranstaltung, der kein konkreter Ort zugeordnet werden kann, wobei aber die Leistungsbeschreibung evtl. Hinweise auf Örtlichkeiten enthalten kann. An dieser Stelle wollte ich ein Beispiel geben für eine partielle funktionale Beziehung. 4 6 Neben Schlüsseln, die auf Attributen basieren, gibt es auch Schlüssel auf Grundlage von funktionalen Beziehungen. In Abbildung 6 ist ein Beispiel zu sehen, das aus dem ERDiagramm auf Seite 3 entnommen ist. Durch die funktionale Abhängigkeit zu einem bestimmten HOTEL und unter Verwendung eines Schlüsselattributs Zimmer# sind HOTELZIMMER eindeutig identifizierbar. Die Kombination aus der Relation hzGehörtZu und dem HOTELZIMMER-Attribut Zimmer# können also in diesem Beispiel als Schlüssel dienen.6 Ein HOTELZIMMER-Entity ist nur abhängig von einer HOTEL-Entity denkbar. HOTELZIMMER ist ein abhängiger Entity-Typ, da eine HOTELZIMMER-Instanz nur existieren kann, wenn auch eine HOTEL-Entity in einer funktionalen Beziehung dazu vorhanden ist. HOTEL HOTELZIMMER hzGehörtZu Kategorie Zimmer# Ausstattung Zimmerlage Tarif Abbildung 6: Funktionale Beziehung als Schlüssel 2.3.6 Kardinalitäten Zu den Integritätsbedingungen gehören neben Schlüsseln auch Kardinalitäten. Sie besagen, wie oft eine Entity-Instanz an einer Relationship beteiligt sein kann. Dazu wird ein Intervall innerhalb der Natürlichen Zahlen (inkl. der Null) angegeben. In Abbildung 7 ist ein Beispiel zu sehen, das ebenso auch im Datenbankschema von Seite 3 enthalten ist. [1,*] [0,*] gebuchtFür LEISTUNG PERSON Abbildung 7: Kardinalitäten Eine LEISTUNG nimmt mindestens ein Mal, höchstens jedoch beliebig oft an der Relationship gebuchtFür teil. Eine nach oben offene Schranke wird durch einen Stern (*) symbolisiert. Eine LEISTUNG-Entity ist also immer für mindestens eine PERSON gebucht. Nach oben gibt es allerdings keine Beschränkung. PERSONen können kein Mal bis beliebig oft an der Relationship gebuchtFür teilnehmen. Dieses Intervall braucht nicht unbedingt angegeben werden, da es als Standard gilt. 2.4 Textuelle Notation 2.4.1 Entity-Typen A) PERSON( Name, Adresse, Geburtsdatum, Telefon, Email, Zusatzinformation ) B) KUNDE( Kunden#, Name, Adresse, Geburtsdatum, Telefon, Email, Zusatzinformation ) Da der KUNDE von PERSON abgeleitet ist, werden die Attribute von PERSON für KUNDE übernommen. Außerdem gilt für KUNDE auch der Primärschlüssel von PERSON. C) VORGANG( Vorgangs# ) 6 Normalerweise werden nur Primärschlüssel-Attribute bzw. -Relationen unterstrichen dargestellt. HOTELZIMMER erbt seinen Primärschlüssel aber von dem Entity-Typ LEISTUNG. Um die besonderen Schlüssel basierend auf funktionalen Beziehungen zu erläutern, hätte es im ER-Diagramm von Seite 3 auch andere Beispiel-Kandidaten gegeben. Diese sind jedoch alle wesentlich abstrakter, so dass ich mir eine Ausnahme von der Regel zugunsten des Hotelzimmer-Beispiels erlaubt habe. 7 D) LEISTUNG( BeginnDatumZeit, EndeDatumZeit, Leistungsbeschr ) E) FLUG( Flug#, MaxFluggäste, BeginnDatumZeit, EndeDatumZeit, Leistungsbeschr ) F) HOTELZIMMER( Zimmer#, Zimmerlage, Tarif, BeginnDatumZeit, EndeDatumZeit, Leistungsbeschr ) G) VERANSTALTUNG( Titel, Platzangaben, BeginnDatumZeit, EndeDatumZeit, Leistungsbeschr ) H) MIETWAGEN( Kennzeichen, Bezeichnung, Wagenklasse, BeginnDatumZeit, EndeDatumZeit, Leistungsbeschr ) I) SONDERLEISTUNG( Notiz, BeginnDatumZeit, EndeDatumZeit, Leistungsbeschr ) J) ORT( Kurzcode, Bezeichnung ) K) FLUGHAFEN( Kurzcode, Bezeichnung ) L) HOTEL( Kategorie, Ausstattung, Kurzcode, Bezeichnung ) M) MIETWAGENSTATION( Firmenname, Kurzcode, Bezeichnung ) N) ZAHLUNG( Zahlung#, Betrag, DatumZeit, Zahlart ) O) ANZAHLUNG( Prozent, Mindestbetrag, Zahlung#, Betrag, DatumZeit, Zahlart ) 2.4.2 Relationships a) gebuchtVon( VORGANG, KUNDE ) b) gebuchtFür( LEISTUNG, PERSON ) c) teilVon( LEISTUNG, VORGANG, LeistIndex#, Preis ) d) flVon( FLUG, FLUGHAFEN, Abflugsterminal ) e) flNach( FLUG, FLUGHAFEN, Ankunftsterminal ) f) hzGehörtZu( HOTELZIMMER, HOTEL ) g) veranOrt( VERANSTALTUNG, ORT ) h) miStart( MIETWAGEN, MIETWAGENSTATION ) i) miZiel( MIETWAGEN, MIETWAGENSTATION ) j) zaGehörtZu( ZAHLUNG, VORGANG ) k) ist( KUNDE, PERSON ) l) ist( FLUG, LEISTUNG ) m) ist( HOTELZIMMER, LEISTUNG ) n) ist( VERANSTALTUNG, LEISTUNG ) o) ist( MIETWAGEN, LEISTUNG ) p) ist( SONDERLEISTUNG, LEISTUNG ) 8 q) ist( FLUGHAFEN, ORT ) r) ist( HOTEL, ORT ) s) ist( MIETWAGENSTATION, ORT ) t) ist( ANZAHLUNG, ZAHLUNG ) 3 Übersetzung in ein Relationales Schema Die oben beschriebenen Entity- und Beziehungs-Typen, sowie die Integritätsbedingungen werden nun in ein Relationales Schema übersetzt. Besonderheiten bei dieser Übersetzung werden jeweils darunter erläutert. Darin wird Bezug genommen auf die, mit einem Buchstaben oder römischen Zahl gekennzeichneten Zeilen aus Kapitel 2.4.1, 2.4.2 bzw. 2.2. 3.1 Übersetzung der Entity-Typen zu A) PERSON( Name, Adresse, Geburtsdatum, Telefon, Email, Zusatzinformation ) Aus A) wird ein gleichnamiges Relationenschema PERSON, dass sämtliche Attribute und auch Schlüssel übernimmt. zu B) KUNDE( Kunden#, Name, Adresse, Geburtsdatum ) KUNDE ist über eine ist-Beziehung mit PERSON verknüpft. Daher werden in das Relationenschema KUNDE zusätzlich zum Attribut Kunden# der Entity KUNDE die Primärschlüsselattribute Name, Adresse und Geburtsdatum von PERSON übernommen. Als Primärschlüssel gilt dann der gleiche wie bei PERSON. zu C) Der VORGANG-Entity muss nicht in das relationale Schema übertragen werden, da zu jedem Vorgang eine Relationship gebuchtVon existiert. Dies geht aus den Kardinalitäten hervor und aus der Tatsache, dass gebuchtVon ein total funktionaler Beziehungs-Typ ist. zu D) LEISTUNG( BeginnDatumZeit, EndeDatumZeit, Leistungsbeschr, Preis, Vorgangs#, LeistIndex# ) Das Relationenschema LEISTUNG wird gebildet aus den Attributen des Entity-Typs LEISTUNG und der funktionalen Beziehung teilVon, die als (Primär-)Schlüssel verwendet wird und weitere Attribute mit sich bringt. Daher gibt es auch keine Relation teilVon, denn alle Informationen werden in der Relation LEISTUNG abgebildet. Der Primärschlüssel wird zusammengesetzt aus dem Primärschlüssel des Entity-Typs VORGANG und dem Beziehungsattribut LeistIndex#. zu E-I) FLUG( Flug#, MaxFluggäste, Vorgangs#, LeistIndex# ) HOTELZIMMER( Zimmer#, Zimmerlage, Tarif, Vorgangs#, LeistIndex# ) VERANSTALTUNG( Titel, Platzangaben, Vorgangs#, LeistIndex# ) MIETWAGEN( Kennzeichen, Bezeichnung, Wagenklasse, Vorgangs#, LeistIndex# ) SONDERLEISTUNG( Notiz, Vorgangs#, LeistIndex# ) Die Entity-Typen, die über eine ist-Beziehung mit dem Entity-Typ LEISTUNG verbunden sind, behalten in den jeweiligen Relationenschemata ihre eigenen Attribute. Dazu bekommen sie die Attribute, die zum Primärschlüssel der Relation LEISTUNG gehören, um eine eindeutige Abbildung auf eine Leistung zu erhalten. zu J-M) ORT( Kurzcode, Bezeichnung ) 9 ist der Primärschlüssel des Entity-Typs ORT, der in das Relationenschema übernommen wird. Für die, über ist-Beziehung verbundenen Entities FLUGHAFEN, HOTEL und MIETWAGENSTATION wird dieser Primärschlüssel jeweils vererbt. Kurzcode FLUGHAFEN( Kurzcode ) HOTEL( Kategorie, Ausstattung, Kurzcode ) MIETWAGENSTATION( Firmenname, Kurzcode ) zu N) ZAHLUNG( Zahlung#, Betrag, DatumZeit, Zahlart, Vorgangs# ) Die Relation ZAHLUNG erhält als Primärschlüsselattribute Vorgangs# und Zahlung#. Zahlung# ist wie ein zusätzlicher Indize zur Vorgangs# zu betrachten. Die Übersetzung musste hier analog zur Übersetzung der Relation LEISTUNG erfolgen. zu O) ANZAHLUNG( Prozent, Mindestbetrag, Zahlung#, Vorgangs# ) ANZAHLUNG erbt den Primärschlüssel aus der Übersetzung von ZAHLUNG. 3.2 Übersetzung der Relationships In der folgenden Übersetzung der Beziehungstypen werden Primärschlüsselattribute der verknüpften Entity-Typen geeignet übernommen. gebuchtVon( Vorgangs#, Kunden# ) gebuchtVon setzt den VORGANG-Entity-Typ mit dem KUNDE-Entity-Typ in Beziehung. Vorgänge werden mittels Vorgangs# eindeutig identifiziert, Kunden über die Kunden#. Diese beiden Attribute bilden den neuen Primärschlüssel der Relation gebuchtVon. Analog läuft die Übersetzung auch bei den weiteren Beziehungstypen. Attribute einer Relationship werden in das Relationenschema übernommen. Dies ist der Fall bei flVon und flNach. gebuchtFür( Vorgangs#, LeistIndex#, Name, Adresse, Geburtsdatum ) flVon( Abflugsterminal, Vorgangs#, LeistIndex#, Kurzcode ) flNach( Ankunftsterminal, Vorgangs#, LeistIndex#, Kurzcode ) hzGehörtZu( Kurzcode, Vorgangs#, LeistIndex# ) veranOrt( Kurzcode, Vorgangs#, LeistIndex# ) miStart( Kurzcode, Vorgangs#, LeistIndex# ) miZiel( Kurzcode, Vorgangs#, LeistIndex# ) Der Beziehungs-Entity-Typ zaGehörtZu musste nicht als Relationenschema gebildet werden, da die Verknüpfung von VORGANG und ZAHLUNG bereits in der Relation ZAHLUNG enthalten ist. ist-Beziehungen werden nicht in relationale Schemata übertragen. 10 3.3 Übersetzung der Integritätsbedingungen Für die Darstellung der Integrationsbedingungen im Relationenschema wird wieder die Adhoc-Notation wie in Kapitel 2.2 verwendet. Allerdings kann nicht über Relationship-Namen navigiert werden. Dennoch ist es bspw. über Verbünde möglich, Relationen miteinander zu kombinieren. Außerdem sind die Entities eines Entity-Typs nach der Übersetzung Elemente bzw. Tupel in einer Relation (weswegen ∈ statt : gebraucht wird). zu I-II: Da der Entity-Typ KUNDE auch im Relationenschema mit den Attributen Kunden# und Geburtsdatum vorhanden ist, können die Integritätsbedingungen I und II ohne Änderung übernommen werden. zu III: Die Möglichkeit über eine LEISTUNG-Instanz eine Menge von gebuchten Personen auszuwählen, besteht im relationalen Schema nur indirekt. Sie könnte wie folgt umgesetzt werden: forAll l ∈ LEISTUNG, gf ∈ gebuchtFür ▪ l.Vorgangs# = gf.Vorgangs# and l.LeistIndex# = gf.LeistIndex# ⇒ gf.Geburtsdatum >= date( l.BeginnDatumZeit ) zu IV: Da alle Attribute des LEISTUNG-Entities in eine gleichnamige Relation übernommen wurden, ist es kein Problem, die entsprechende Integritätsbedingung IV analog im Relationenschema zu verwenden. zu V: Hier gilt dasselbe wie oben zu IV. zu VI: Die Zusicherung muss übersetzt werden auf die Relationen LEISTUNG und ANZAHLUNG. Alle LEISTUNG-Tupel zu einer Vorgangs# bilden gemeinsam den Gesamtpreis, den der Kunde mittels Zahlungen ausgleichen muss. Hierbei geht es aber um die Tatsache, dass die Anzahlung den Gesamtpreis nicht überschreiten darf. forAll a ∈ ANZAHLUNG ▪ a.Mindestbetrag is not null ⇒ a.Mindestbetrag <= sum( { l.Preis | l ∈ LEISTUNG and l.Vorgangs# = a.Vorgangs# } ) zu VII: Die zu vergleichenden Werte finden sich nun in den Relationen LEISTUNG und ZAHLUNG. Die Relationships teilVon und zaGehörtZu werden nicht berührt – sie sind in den beiden zuvor genannten Relationen enthalten. Der Bezug zur VORGANG-Entity wird mittels Relation gebuchtVon hergestellt. forAll v ∈ gebuchtVon ▪ sum( { l.Preis | l ∈ LEISTUNG and l.Vorgangs# = v.Vorgangs# } ) <= sum( { z.Betrag | z ∈ ZAHLUNG and z.Vorgangs# = v.Vorgangs# } ) zu VIII: Die Zusicherung VIII kann direkt übernommen werden. zu IX: Für die Integritätsbedingung IX muss nur geringfügig angepasst werden. Die Navigation über die Relationship zaGehörtZu fällt weg. forAll a1, a2 ∈ ANZAHLUNG ▪ a1.Vorgangs# = a2.Vorgangs# ⇔ a1 = a2 zu X: Die Integrationsbedingung aus X muss nicht verändert werden, da der Entity-Typ MIETWAGEN bzgl. des Kennzeichen-Attributs zur Relation MIETWAGEN passt. 11 4 Sichten 4.1 Drei-Ebenen-Architektur Datenbanksysteme sind häufig in einer Drei-Ebenen-Architektur aufgebaut. In Abbildung 8 ist dies zu sehen. Das interne Schema bezeichnet die konkrete Implementierung des Datenbanksystems, welches vom konzeptionellen Schema gekapselt wird. Indexe (von denen einer als Primärindex verwendet wird) sorgen für eine Tupel-Ordnung innerhalb der verschiedenen Relationen. Das Konzept überträgt sich oft von (Primär-)Schlüsseln im konzeptionellen Schema auf Indexe im internen Schema. Beispiel: Für die Relation PERSON wird es wahrscheinlich einen aus Name, Adresse und Geburtsdatum zusammengesetzten (Primär-)Index geben, der die Tupel der Relation PERSON nach eben diesen Attributen sortiert und damit einen schnelleren Zugriff auf bestimmte Anfrage-Ergebnisse ermöglicht. Für die Relation gebuchtVon wird man im internen Schema höchstwahrscheinlich das Attribut Vorgangs# für den Primärindex verwenden, da bereits Vorgangs# für sich genommen eindeutig ist und damit alle Zugriffe auf Vorgänge sehr schnell umzusetzen sind. In der dritten Architekturebene liegen dann möglicherweise verschiedene externe Schemata, in denen ein Teil des konzeptionellen bzw. internen Schemas der Datenbank zur Verfügung gestellt wird. Diese virtuelle Teilansicht der Datenbank innerhalb eines Datenbanksystems wird auch als Sicht (engl. View) bezeichnet. Unterschiedlichen Anwendungen kann so eine individuell definierbare Menge von Daten in einer virtuellen Datenbank angeboten werden. Ebene Externes Schema 1 ... Externes Schema n Konzeptionelles Schema Internes Schema 3 2 1 Abbildung 8: Drei-Ebenen-Architektur 4.2 Beispiele für Sichten in SQL Möchte man dem Benutzer des Beispieldatenbanksystems eine Anwendung zur Verfügung stellen, in der ausschließlich Personenangaben wie Name, Adresse, Telefon und Email bearbeitet werden können, ist folgende Sichtdefinition in SQL eine Möglichkeit. create view Adressbuch as select Name, Adresse, Telefon, Email from PERSON Aus dem Relationenschema PERSON werden per Selektion die Attribute entnommen, die der Anwender in dem View Adressbuch sehen und verändern können soll. Häufig kommt es vor, dass Daten aus verschiedenen Relationenschemata zu einer Sicht verknüpft werden müssen. Dazu bietet SQL die Verbundbildung an. Um beispielsweise Flughäfen zu bearbeiten, lassen sich aus ORT- und FLUGHAFEN-Relation die Orte ermitteln, die gleichzeitig Flughäfen sind. Der ORT-Kurzcode muss nur gleichzeitig auch in der FLUGHAFEN-Relation vorhanden sein. Das wird in dem folgenden Beispiel in der whereKlausel überprüft. create view Flughäfen as select ORT.Kurzcode, Bezeichnung from ORT, FLUGHAFEN where ORT.Kurzcode = FLUGHAFEN.Kurzcode Bei manchen Sichten (wie auch beim Flughäfen-View) sind einige Operationen nicht eindeutig auszuführen bzgl. der zu Grunde liegenden Datenbank (internes Schema). Der 12 Löschvorgang eines Flughäfen-Tupels kann dazu führen, dass nicht nur aus der FLUGHAFEN-Relation der Kurzcode verschwindet, sondern gleichzeitig auch in der ORTRelation der komplette Ort entfernt wird. Was soll geschehen, wenn der Kurzcode in dem Flughäfen-Tupel geändert wird? Soll auch der Kurzcode in der ORT-Relation überschrieben werden? In einigen Fällen sind Änderungen auf Sichten wegen der Nicht-Eindeutigkeit nur eingeschränkt möglich oder gänzlich verboten. In der Anwendungsbeschreibung (→ Kapitel 1 Ende) wurde eine Unterstützung für Direktmailing gefordert. Die dafür benötigten Daten können den Mitarbeitern des Reisebüros durch eine Sicht in SQL angeboten werden. Dabei war es, im Gegensatz zum einfachen Adressbuch, wichtig, dass nur Kunden in die Direktmailing-Bearbeitung eingehen. create view Direktmailing as select Name, Adresse, Telefon, Email from PERSON natural join KUNDE An dieser Stelle wird der natürliche Verbund zwischen den Relationen PERSON und KUNDE gebildet, womit eine Tabelle entsteht, in der nur Kunden enthalten sind. Aus dieser Tabelle werden dann vier Attribute mit Kontaktdaten selektiert und unter dem Sichtnamen Direktmailing zur Verfügung gestellt. 5 Standardanfragen SQL ist die verbreitetste relationale Datenbanksprache. In diesem Kapitel werden einige Beispiele für SQL-Anfragen gegeben, die bezogen sind auf das relationale DatenbankSchema dieser Ausarbeitung. Standardanfragen in SQL enthalten einen so genannten SWF-Block, der nach den drei Klauseln select, from und where benannt ist. Die Auswertung dieser SWF-Blöcke beginnt jeweils mit der from-Klausel, die zu verwendende Relationen angibt und diese ggf. geeignet verknüpft. Anschließend wird der optionale where-Klausel-Teil bearbeitet. Darin werden Selektionsbedingungen genannt. Die select-Klausel ist nicht optional und sorgt dafür, dass die Ergebnisrelation entweder komplett übernommen wird (wie das folgende Beispiel zeigt) oder Spalten daraus selektiert werden. select * from PERSON where Email is not null Obige SQL-Anfrage wählt alle Tupel aus der Relation PERSON aus, bei denen die EmailAdresse nicht leer ist. Der Stern hinter dem select-Statement besagt, dass alle Spalten aus der PERSON-Relation in das Ergebnis übernommen werden sollen. select * from ORT natural join HOTEL where Kategorie > 3 Das Ergebnis dieser Anfrage ist eine Relation mit vier Spalten Kurzcode, Bezeichnung, Kategorie und Ausstattung aus dem natürlichen Verbund der Relationen ORT und HOTEL. Dabei werden nur die Tupel übernommen, die zu Hotels mit mindestens drei Sternen gehören. Dasselbe Ergebnis würde von folgender SQL-Anfrage erzeugt: select ORT.Kurzcode, Bezeichnung, Kategorie, Ausstattung from ORT, HOTEL where Kategorie > 3 and ORT.Kurzcode = HOTEL.Kurzcode 13 Wenn in zwei Relationen gleiche Attributbezeichnungen existieren, muss zur eindeutigen Identifizierung ein Relationsname, von einem Punkt getrennt, vor dem Attribut angegeben werden. Außerdem könnte in der select-Klausel nicht der Stern verwendet werden, da sonst ein Namenskonflikt bzgl. des Attributs Kurzcode vorläge. Ein weiteres Beispiel verwendet eine, in SQL fest integrierte Aggregatsfunktion, die auf einer Menge von Tupeln Berechnungen ausführen kann. select Vorgangs#, sum(Preis) as Gesamtpreis from LEISTUNG group by Vorgangs# Hier wird die Summe über dem Attribut Preis in der LEISTUNG-Relation gebildet. In der Ergebnisrelation stehen zwei Spalten Vorgangs# und Gesamtpreis. Die Spalte Preis, auf der die Summe aggregiert wurde, ist mittels Schlüsselwort as in Gesamtpreis umbenannt worden. Die group by-Klausel, die hinter der optionalen where-Klausel verwendet werden kann, sorgt dafür, dass der Preis nicht auf allen LEISTUNG-Tupeln summiert, sondern in diesem Fall nach Vorgangsnummern gruppiert wird. Alle Vorgänge zu denen noch keine Zahlungen existieren, lassen sich durch folgende geschachtelte Anfrage ermitteln: select Vorgangs# from gebuchtVon where Vorgangs# not in ( select Vorgangs# from ZAHLUNG ) Der innere SWF-Block wird zuerst ausgewertet. Die entsprechende Ergebnisrelation enthält alle Vorgangsnummern, die in der ZAHLUNG-Relation vorkommen. Anschließend werden in der Relation gebuchtVon die Tupel selektiert, bei denen Vorgangs# aus gebuchtVon nicht in dem geschachtelten SWF-Block ermittelt wurde. Im Gesamtergebnis werden dann nur die Vorgangsnummern angezeigt. Der Anwender des beschriebenen Datenbanksystems möchte sicher auch wissen, welche Kunden noch Zahlungen zu leisten haben und wie hoch die Restzahlungen sind. Um eine entsprechende geschachtelte Anfrage zu demonstrieren, werden erst die benötigten Einzelelemente vorgestellt. Die folgende Anfrage berechnet alle Vorgänge, zu denen noch keine Zahlungen existieren und summiert den Preis der Einzelleistungen. select Vorgangs#, sum(Preis) as Offen from LEISTUNG where Vorgangs# not in ( select Vorgangs# from ZAHLUNG ) group by Vorgangs# Eine weitere SQL-Anfrage ermittelt alle Vorgänge, zu denen Zahlungen existieren und bildet die Differenz der Summe vom Preis der Einzelleistungen und der Summe der Zahlungsbeträge, um den offenen Geldbetrag zu ermitteln. select Erg.Vorgangs#, (sum(Erg.Preis) - sum(Erg.Betrag)) as Offen from (LEISTUNG natural join ZAHLUNG) as Erg where sum(Erg.Preis) < sum(Erg.Betrag) group by Erg.Vorgangs# 14 Die beiden Ergebnis-Mengen können mittels union vereinigt werden: select Vorgangs#, sum(Preis) as Offen from LEISTUNG where Vorgangs# not in ( select Vorgangs# from ZAHLUNG ) group by Vorgangs# union select Erg.Vorgangs#, (sum(Erg.Preis) - sum(Erg.Betrag)) as Offen from (LEISTUNG natural join ZAHLUNG) as Erg where sum(Erg.Preis) < sum(Erg.Betrag) group by Erg.Vorgangs# Nun müssen nur noch die Kundeninformationen integriert werden. select LEISTUNG.Vorgangs#, sum(Preis) as Offen, Kunden# from LEISTUNG, gebuchtVon where LEISTUNG.Vorgangs# = gebuchtVon.Vorgangs# and LEISTUNG.Vorgangs# not in ( select Vorgangs# from ZAHLUNG ) group by LEISTUNG.Vorgangs# union select Erg.Vorgangs#, (sum(Erg.Preis) - sum(Erg.Betrag)) as Offen, Kunden# from (LEISTUNG natural join ZAHLUNG) as Erg, gebuchtVon where Erg.Vorgangs# = gebuchtVon.Vorgangs# and sum(Erg.Preis) < sum(Erg.Betrag) group by Erg.Vorgangs# 15
