SQL Server 2005: T-SQL – Programmierung und
Werbung
Grundlagen 1 Grundlagen 19 Grundlagen 20 Grundlagen 1 Grundlagen Dieses Buch geht davon aus, dass allgemeine Grundkenntnisse zu Datenbanken vorhanden sind, dass allerdings die Beschäftigung mit dem Datenbanksystem von Microsoft erst ab Version 2005 zu den Arbeitsinhalten gehört. Dieses Buch, als erstes in der MS SQL Server-Serie, soll daher auch einige einleitende Worte zur Installation, zur Architektur und zu den ersten Schritten mit dem Management Studio als Programm zur Nutzung der Datenbank verlieren. Für Leser, die bereits eine fertige Installation besitzen und sich schon mit der Version 2000 auskennen, sind daher die Abschnitte zur Installation und den ersten Schritten nicht relevant, sondern möglicherweise nur der Abschnitt zur Architektur. 1.1 Installation Für gewöhnlich stellt die Installation von Software heute kein größeres Problem mehr da, sodass wir dieses Thema auch auf die notwendigen Informationen beschränken möchten. Grundsätzlich ist die Installation des SQL Servers 2005 ebenfalls sehr einfach, solange nur Testzwecke oder reine Anwendungsentwicklung betroffen ist und nicht etwa der Aufbau eines realistischen Systems. Dies soll dann auch im Rahmen des Buchs zur Administration thematisiert werden. Wichtig ist vielmehr, dass für die Funktionstüchtigkeit der Beispiele deutlich ist, welche Version zum Einsatz kommt und welche Voraussetzungen im Rahmen der Installation getroffen werden. Der SQL Server 2005 ist in unterschiedlichen Varianten erhältlich. Eine sehr umfassende Übersicht der möglichen Download-Dateien befindet sich unter www.microsoft.com/ germany/sql/downloads/default.mspx. Im Normalfall interessiert man sich für die Testversion (www.microsoft.com/germany/sql/downloads/testsoftware.mspx), die 180 Tage gültig ist oder die Developer Edition, da beide Versionen den vollen Umfang der Datenbank bieten. Zusätzlich ist auch noch eine SQL Server 2005 Express Edition verfügbar, welche die Datenbankgröße auf 4 Gigabyte und maximal eine CPU sowie bis zu 1 GB Arbeitsspeicher unterstützt. Sie ist für die kostenlose Weiterverteilung und Einbettung in Anwendungen gedacht. Eine Übersicht über die verschiedenen Editionen und ihre einzelnen Fähigkeiten findet man 21 Grundlagen unter http://www.microsoft.com/ germany/sql/editionen/default.mspx. Wie man sich leicht denken kann, unterscheiden sich die Versionen insbesondere in leichter Bedienbarkeit und den sehr fortgeschrittenen Anwendungen. Für gewöhnlich verhält es sich so, dass Anwendungen, die überhaupt mit einer Großdatenbank wie dem MS SQL Server oder Oracle erstellt werden, auch umfangreiche Technologien erfordern, sodass die Enterprise-Ausgabe notwendig ist. Sie bietet als einziges System folgende Möglichkeiten an, die für fortgeschrittene Anwendungsszenarien notwendig sein können. Allerdings ist insbesondere der Bereich Business Intelligence besonders an der neuen Version hervorzuheben, da hier auf der einen Seite deutliche Erweiterungen und Verbesserungen im Gegensatz zur Vorgängerversion zu begrüßen sind, und auf der anderen Seite die totale Integration mit anderen Microsoft-Produkten, wodurch vielfältige Anforderungen mit geringem oder wenigstens akzeptablem Aufwand umgesetzt werden können. Die nachfolgende kurze Aufstellung stellt für die einzelnen Bereiche dar, welche Fähigkeiten die Enterprise-Version bietet, welche die anderen nicht bieten. 22 Bereich Skalierbarkeit und Leistung: – Partitionierung für umfangreiche Datenbanken – Parallelindexoperationen für die Parallelverarbeitung von Indexoperationen – Indizierte Ansichten mit Vergleichen Bereich Hochverfügbarkeit: – Onlineindizierung – Onlinewiederherstellung – Schnelle Wiederherstellung Bereich Integration und Interoperabilität: – Integration Services mit erweiterten Transformationen mit Data Mining, Text Mining und Datenbereinigung – Oracle-Replikation mit Transaktionsreplikation mit Oracle als Publisher Bereich Business Intelligence: Grundlagen – Berichtsserver mit Lastverteilung – Datengesteuerte Abonnements – Uneingeschränktes Durchklicken – Erweiterte Geschäftsanalyse bietet Kontointelligenz, Metadatenübersetzung, perspektivische und semiadditive Measures – Proaktives Caching bietet automatische Zwischenspeicherung für verbesserte Skalierbarkeit und Leistung – Erweiterte Datenverwaltung – Erweiterte Leistungsoptimierung – Datenflussintegration in SQL Server Integration Services – Text Mining Für die Beispiele verwenden wir die Developer-Version, wobei Sie bei einem Test zuhause oder in der Firma auf die angesprochene 180-Tage-Testversion zurückgreifen. Wir haben uns ganz bewusst gegen die Express-Ausgabe entschieden (so wie wir dies auch im Rahmen eines Oracle-Buchs machen würden), da die Software, die wir im Rahmen von Projekten betreuen, typischerweise damit bei Weitem nicht lauffähig wäre und wir hier daher nur wenig sinnvollen Einsatz sehen würden. Nichtsdestoweniger gibt es eine Reihe Spezialliteratur zu diesem Thema, die diese leichtgewichtige Alternative im Einsatz zeigt. Die meisten Beispiele dieses Buch funktionieren auch mit den unterschiedlichen Versionen, da in diesem Band die Fähigkeiten von der EnterpriseAusgabe noch nicht genutzt werden. Folgende Schritte sind für eine Standardinstallation notwendig: 1. Nach dem Starten der Installation öffnet sich das Dialogfenster Installationsvoraussetzungen, das darüber informiert, dass das .NET Framework 2.0 (immer eine gute Wahl), das entsprechende Sprachpaket sowie der Microsoft SQL Native Client (eine unverzichtbare Voraussetzung) installiert werden müssen. Es bleibt nicht mehr zu tun, als die Schaltfläche INSTALLIEREN zu wählen. 23 Grundlagen 2. Nachdem die genannten Voraussetzungen geschaffen wurden, erscheint ein ähnliches Dialogfenster mit Bestätigungen der installierten Teile. Hier wählt man WEITER. 3. Es folgt die so genannte Systemkonfigurationsüberprüfung, welche im Normalfall mit einer Reihen an Erfolgen beendet wird. Bei einem gewöhnlichen Windows XP Professional-Rechner sollten keine Schwierigkeiten und damit keine Misserfolge auftreten. Ansonsten müssen die entsprechenden Korrekturen vorgenommen werden, auf die allerdings in deutlichen Fehlermeldungen hingewiesen wird. Dieses Dialogfenster verlassen Sie mit WEITER. 4. Schließlich gelangen Sie in das Dialogfenster Zu installierende Komponenten. Je nach ausgewählter Datenbankversion bieten sich hier auch unterschiedliche Komponentenlisten, da ja nicht alle Komponenten in jeder Version verfügbar sind. Für dieses Buch und auch für andere Bücher aus dieser Reihe werden insbesondere auch die Reporting- und AnalysisServices ausgewählt. 5. Im Dialofenster Instanzname wählen Sie die Option Standardinstanz aus und bestätigen mit WEITER. 6. Im Dialogfenster Dienstkonto können Sie die Anmeldekonten angeben. Hier verzichten wir darauf, für jedes Dienstkonto eigene Anmeldeinformationen anzugeben. Stattdessen genügt die einfachste Lösung, die aus dem integrierten Systemkonto besteht. Da die Beispieldatenbank AdventureWorks verwendet wird und wenigstens im Rahmen des Buchs keine geheimen Informationen in die Datenbank gelangen, ist dies völlig ausreichend. Zusätzlich wählen Sie im unteren Bereich des Dialogfensters aus, welche Dienste am Ende der Installation gestartet werden sollen. Für dieses Buch sind zwar die Reporting- und Analysis-Services noch nicht wichtig, doch sind sie derart interessant, dass es sich lohnt, sie für die Zeit nach Transact SQL zu starten. Das Dialogfenster verlassen Sie mit WEITER. 7. Auch eine Anmeldung über die Windows-Informationen ist völlig ausreichend, sodass Sie im Dialogfenster Authentifizierungsmodus die Option Windows-Authentifizierungsmodus auswählen. Klicken Sie auf WEITER. 24 Grundlagen 8. Schließlich müssen Sie noch die Sortierreihenfolge sowie einige Sprachmerkmale angeben. Wir haben uns für eine Unterscheidung von Groß-/Kleinschreibung entschieden, da man durch Funktionen wie UPPER und LOWER leicht die Schreibung ignorieren kann und sie ansonsten sehr wohl auf exakt Gleichheit prüfen kann. Auch Akzente unterscheiden einen Buchstaben erheblich, sodass diese Unterscheidungsoption ebenfalls gewählt wird. Klicken Sie auf WEITER. 9. Im Dialogfenster Berichtsserver-Installationsoptionen belassen Sie die Voreinstellungen, um später eine Konfiguration durchzuführen. 10. Im Dialogfenster Einstellungen für Fehler- und Verwendungsberichte entscheiden Sie, ob Sie Microsoft Hilfeinformationen zur Verbesserung senden wollen. 11. Zum Schluss erscheinen noch zwei letzte Bestätigungsfenster. 25 Grundlagen 1 2 3 4 Abbildung 1.1: Installation (1) 26 Grundlagen 1 2 3 4 Abbildung 1.2: Installation (2) 27 Grundlagen 1 2 3 4 Abbildung 1.3: Installation (3) 28 Grundlagen 1.2 Erste Schritte Wie alle Microsoft-Produkte ist auch der MS SQL Server 2005 auf eine einfache Bedienung ausgelegt, damit bereits von Anfang an einfache Aufgaben erledigt werden können. In diesem Zusammenhang ist dies für den Programmier oder für den DB-Anfänger die Verwendung von SQL in der Datenbank und die Ausführung von Abfragen und Anweisungen. In diesem Kapitel soll die für die Datenbank wesentliche Desktop-Anwendung SQL Server Management Studio vorgestellt werden. Im Vergleich zur Vorgängerversion haben sich allerlei Änderungen ergeben, doch der Grundaufbau ähnelt natürlich der Version 2000 und auch anderen Anwendungen, die einen GUI-orientierten Zugriff auf Datenbanken erlauben. 1.2.1 Management Studio Abbildung 1.4: Management Studio Starten Das Management Studio ist die zentrale Anlaufstelle für die Arbeit mit dem neuen MS SQL Server 2005. Starten Sie das Programm, indem Sie unter Start / Programme / Microsoft SQL Server 2005 / SQL Server Management Studio auswählen. Zusätzlich zeigt die nachfolgende Abbildung auch noch, wie Sie die 29 Grundlagen überaus wichtige Hilfe zur Datenbank öffnen. Dazu öffnen Sie innerhalb des Eintrags Microsoft SQL Server 2005 den Eintrag Dokumentation und Lernprogramme / SQL Server-Onlinedokumentation. Auf der linken Seite des sich öffnenden Fensters befindet sich der so genannte Objekt-Explorer, der - wie sein Name schon verspricht - die Objekten des Servers anzeigt. Dies sind zunächst die Datenbanken selbst, danach gefolgt von weiteren Bereichen wie Sicherheit, Verwaltung, unterschiedliche Dienste und Datensicherung. Abbildung 1.5: Objekt-Explorer einer Datenbank 1. Wählen Sie den Eintrag Datenbanken / AdventureWorks aus, um die AdventureWorks-Datenbank zu öffnen. Wie Sie sehen, sind bereits zwei verschiedene Beispiel-Datenbanken installiert, nämlich zum einen die gewöhnliche AdventureWorks-DB und zum anderen die für die Vorführung 30 Grundlagen der Data Warehouse-Funktionalitäten des SQL Servers angepasste AdventureWorksDW-Datenbank. Sie wird in einem anderen Buch verwendet. 1 3 2 4 Abbildung 1.6: Abrufen von Tabellen- und Spalteninformationen 2. Navigieren Sie zu einer einzelnen Tabelle, indem Sie Datenbanken / AdventureWorks / Tabellen / Production.Product auswählen. Es öffnet sich eine Übersicht über die in dieser Tabelle vorhandenen Spalten, Schlüssel, 31 Grundlagen Einschränkungen Eigenschaften. und Programmierobjekte mit ihren jeweiligen So gibt die Übersicht der Spalte ProductID der Tabelle Production.Product in einer kleinen Übersicht neben dem Spaltennamen bereits an, dass es sich hierbei um den Primärschlüssel (PS) handelt, der vom Datentyp einer Ganzzahl (int) und nicht leer (Nicht NULL) sein darf. Da der Primärschlüssel eine Spalte eindeutig referenziert und daher für die Tabelle von entscheidender Bedeutung für die Identifikation einer Datenzeile ist, ist zusätzlich als Erkennungsmerkmal noch ein Schlüsselsymbol neben der Spalte angebracht. Dieser Schlüssel hat eine gelbe Färbung, während ein grauer Schlüssel neben den so genannten Fremdschlüsselspalten (FS) angebracht ist. Bspw. ist die Spalte SizeUnitMeasureCodes ein solcher Fremdschlüssel vom Datentyp nchar(3). Diese Spalte darf allerdings auch leer sein, d.h. den Standardwert NULL enthalten. Der Wert in diesen Spalten ist in einer anderen Tabelle ein Primärschlüsselwert, sodass man diesen Wert für die Verknüpfung von beiden Tabellen verwenden kann. Diese Verknüpfung ermöglicht es, zusätzliche Informationen zu diesem Fremdschlüsselwert abzurufen. Die Zahlen neben den einzelnen Datentypen wie bspw. bei nchar(3) gibt an, dass insgesamt drei Zeichen (Buchstaben oder Zahlen) in dieser Spalte gespeichert werden dürfen. Sie informiert also über die Länge des zulässigen Spaltenwerts. 3. Die Schlüssel befinden sich zusätzlich auch noch im Ordner Schlüssel, wobei auch hier wiederum die gelb gefärbten Schlüssel die Primärschlüssel und die grau gefärbten die Fremdschlüssel enthalten. Die meisten Tabellen haben nur eine Primärschlüsselspalte, wobei grundsätzlich auch mehrere zulässig sind. Der vollständige Primärschlüsselwert setzt sich dann aus den einzelnen Werten der Primärschlüsselspalten zusammen. Es ist allerdings keine Seltenheit, dass eine Tabelle mehrere Fremdschüsselspalten enthält. Diese sind normalerweise einzeln zu betrachten und führen im Regelfall auch zu verschiedenen Tabellen. Lediglich in Sonderfällen und bei Tabellen mit einem zusammengesetzten Primärschlüssel müssen natürlich die einzelnen Schlüssel auch wieder als Fremdschlüssel in einer anderen Tabelle erscheinen. 4. Weitere Informationen zu einem einzigen Schlüsselwert erhalten Sie über das Detailmenü, das mit Hilfe eines Doppelklicks zu öffnen ist. 32 Grundlagen 1 2 Abbildung 1.7: Schlüssel und Fremdschlüssel Dieses Detailmenü hat den Titel Spalteneigenschaften und entspricht der Anzeige wie sie auch für gewöhnliche Spalten existiert. Insbesondere für Schlüsselfelder ist diese Übersicht allerdings besonders interessant. Unter der Überschrift IDENTITÄT befindet sich der Name für dieses Schlüsselfeld sowie eine allgemeine Beschreibung in Form eines Kommentars. Der Name soll genau diese Schlüsselangabe identifizieren, da ja grundsätzlich der Spaltenname auch in einer anderen Tabelle als Fremdschlüssel oder in ganz anderer Bedeutung erscheinen kann. Über den eindeutigen Namen lassen sich dann auch Fehlermeldungen viel besser interpretieren, da man aus ihnen ablesen kann, welche Schlüsselangabe oder -beziehung verletzt wurde. Unter der Überschrift TABELLEN-DESIGNER sind 33 Grundlagen verschiedene zusätzliche Aktionen angegeben, die bspw. automatisch geschehen sollen, sobald eine Datenänderung (eintragen, löschen oder aktualisieren) erfolgt. 1 2 Abbildung 1.8: Check-Bedingungen 5. Wählen Sie den Order EINSCHRÄNKUNGEN aus, um die für die Tabelle angegebenen Überprüfungsregeln für Wertänderungen und Werterfassungen zu kontrollieren. Unter einer Einschränkung oder einer CHECK-Bedingung, welche ihren Namen von der sie erstellenden SQL-Syntax herleitet, versteht man eine Überprüfung des Wertebereichs. Diese Überprüfung hat nur indirekt etwas mit der reinen Überprüfung zu tun, ob die einzutragenden Daten zu dem für die Spalte angegebenen Datentyp passen. Sie enthalten darüber hinaus vielmehr weitere Regeln und Ausdrücke, die zur genauen Überprüfung von zulässigen Werten herangezogen werden. Dies können Bereiche, Grenzen und Intervalle genauso sein wie der Vergleich mit anderen Spalten oder sonstigen Vergleichen und Wertbeschreibungen von zulässigen Einträgen in dieser Spalte. Auch diese Einschrän- 34 Grundlagen kungen besitzen einen eindeutigen Namen, damit man im Fehlerfall exakt nachvollziehen kann, gegen welche Einschränkung eine Operation verstoßen hat. 6. Öffnen Sie den Ordner TRIGGER, um die für die Tabelle zugewiesenen automatischen Operationen zu lesen. 2 1 ALTER TRIGGER [uProduct] ON [Production].[Product] AFTER UPDATE NOT FOR REPLICATION AS BEGIN SET NOCOUNT ON; UPDATE [Production].[Product] SET [Production].[Product].[ModifiedDate] = GETDATE() FROM inserted WHERE inserted.[ProductID] = [Production].[Product].[ProductID]; END; Abbildung 1.9: Trigger Bei einem Trigger handelt es sich um ein programmierbares Objekt in der Syntax von Transact SQL, der Spracherweiterung von SQL für den MS SQL Server. Bisweilen bezeichnet man auch die gesamte SQL-Sprache für den SQL Server als Transact SQL. Ein Trigger wird automatisch durch eine in ihm angegebene DB-Aktion ausgelöst, indem bspw. Daten in eine Tabelle neu eingetragen oder einer Tabelle gelöscht oder aktualisiert werden sollen. Einen Trigger kann man nur über eine solche Aktion auslösen, d.h. ein direkter Aufruf wie bei einer Funktion oder Prozedur ist ausgeschlossen. Die Anweisungen eines Triggers dienen der erweiterten Überprüfung von Benutzeraktionen und sollen die Datenintegrität noch besser schützen als bspw. ein einfacher Wertebereich oder eine Einschränkung. Erlauben die Einschränkungen nur einfache Überprüfungsregeln, so kann man in einem Trigger nahezu beliebige Programmanweisungen vorgeben. 7. Für die Optimierung von Abfragen, d.h. für ihre beschleunigte Ausführung, kann man Indizes (Singular: Index) für eine Tabelle angeben. Öffnen Sie mit einem Klick den Ordner Indizes und wählen Sie einen der 35 Grundlagen verschiedenen schon vorhandenen Indizes aus. Sie gelangen in die Detailansicht des gewählten Index, indem neben seinem Typ auch solche Eigenschaften wie die indizierte Spalte, Sortierreihenfolge, Datentyp und Größe angegeben werden. An dieser Stelle befinden sich auch in einer Ordner-Hierarchie verschiedene weitere Dialogfenster für die IndexBearbeitung bzw. ihre Speicherung und sonstige Eigenschaften. Mit Hilfe des in einem späteren Kapitel erläuterten Schlüsselworts SELECT ist es möglich, die Daten einer Tabelle auszugeben bzw. zu bestimmen, welche Spalten in die Ergebnismenge übernommen werden sollen und welchen Bedingungen die Daten genügen sollen. Dabei kann die Suche nach passenden Datensätzen entweder sequenziell oder indiziert vorgenommen werden. Die Möglichkeit eines sequenziellen Vorgehens bedeutet, dass die Bedingung für jede einzelne Zeile der Tabelle überprüft wird. Man kann sich hier vorstellen, dass man mit dem Finger jeden Datensatz einzeln berührt und ihn untersucht. Dies geschieht in der gespeicherten Reihenfolge der Daten auf der Festplatte. Da dies bei sehr großen Tabellen lange dauern kann, gibt es die Möglichkeit, die Spalten, in denen häufig gesucht wird bzw. in denen die Bedingungen enthalten sind, nach denen die Daten gefiltert werden sollen, zu indizieren. Dabei handelt es sich hierbei um eine gezielte Suche wie in einem Lexikon oder Wörterbuch. Mit einem so genannten Clustered Index ordnet man die Reihen der Tabelle nach der angegebenen Sortierung physikalisch, d.h. auf der Festplatte, bereits in der benötigten Reihenfolge. Da die Daten nur einmal physikalisch geordnet werden können, ist auch nur ein solcher Index pro Tabelle zulässig. Sofern die Tabelle einen Primärschlüssel besitzt, wird dieser Clustered Index automatisch erstellt. Im Gegensatz dazu kann man auch einen Non-Clustered Index erstellen. Er betrifft nicht die physikalische Speicherung bzw. Sortierung der Daten. Stattdessen befindet sich die Sortierung in einer zusätzlichen Baumstruktur, die wie die schon erwähnten Techniken Lexikon, Wörterbuch oder Stichwortverzeichnis fungieren. Neben diesen beiden Hauptformen lassen sich noch weitere Arten unterscheiden. 36 Grundlagen 2 1 3 Abbildung 1.10: Indizes 8. Wählen Sie den Ordner Statistiken aus, um die Entscheidungsgrundlage für die Verwendung eines Index zu sehen. Für die Bearbeitung einer Anfrage stehen meistens unterschiedliche Vorgehensweisen zur Verfügung, die unter dem Namen Ausführungsplan bekannt sind. Welcher konkreter Ausführungsplan und damit auch welcher Index tatsächlich genutzt wird, um eine Anfrage zu beantworten, beeinflusst die so genannte Indexselektivität. 37 Grundlagen 1 2 Abbildung 1.11: Statistiken Je höher diese Indexselektivität ist, desto höher ist die Wahrscheinlichkeit, dass der entsprechende Ausführungsplan zum Einsatz kommt. Der Optimierer ermittelt den benötigten Wert anhand der erstellten Statistiken. Sie enthalten solche Informationen wie die Anzahl der Tabellenzeilen, die Verteilungsschritte oder die physikalischen Speicherseiten. 1.2.2 Abfragen direkt ausführen Das Management Studio bietet die Möglichkeit, über einen einfachen Texteditor Abfragen an die Datenbank zu senden bzw. T-SQL-Programme wie die erwähnten Trigger und Prozeduren zu erstellen. Neben diesem Programm gibt es auch noch rein kommandozeilenbasierte Werkzeuge. Im Wesentlichen sollen Sie in diesem Buch lernen, welche verschiedenen Syntaxanweisungen ein Programmierer (im Gegensatz zu einem Administrator) in dieses Textfeld eingetragen 38 Grundlagen soll. Empfehlenswert ist natürlich immer, sowohl Datenbankadministrationskenntnisse als auch Programmierkenntnisse zu erwerben. 1. Um eine Abfrage oder allgemein eine DB-Anweisung auszuführen, wählen Sie die Schaltfläche NEUE ABFRAGE. 2. In der Auswahlliste, welche die verschiedenen Datenbanken enthält, die gerade innerhalb des Datenbankservers enthalten sind, wählen Sie die AdventureWorks-Datenbank aus, da die Anweisung in dieser DB ausgeführt werden soll. 3. Tragen Sie dann Ihre SQL-Anweisungen wie bspw. SELECT * FROM Production.Product in das sich öffnende Textfenster ein. 4. Wählen Sie die AUSFÜHREN-Schaltfläche oder drücken Sie die F5-Taste. 5. Das Ergebnis erscheint standardmäßig in der so genannten Raster-Ansicht (engl. Grid View) im Bereich ERGEBNISSE. Zusätzliche Meldungen oder natürlich Fehlermeldungen erscheinen dagegen im Bereich MELDUNGEN. Die vorher genannten Schritte sind neben der Anmeldung die nahezu wichtigsten für die Arbeit mit der Datenbank. Sie lassen sich noch um weitere Schritte wie das Öffnen einer fertigen SQL-Datei mit Anweisungen oder die Speicherung von Anweisungen und Ergebnissen vervollständigen, doch für den Einstieg in die Arbeit mit dem SQL Server sind dies die wesentlichen Aktivitäten. Im Bereich der Administration und natürlich der Erstellung von Datenbankobjekten bietet das Management Studio noch weitere Möglichkeiten. Um eine Folge von SQL-Anweisungen wie bspw. eine Abfrage, die Sie später noch einmal ausführen wollen, als Textdatei zu speichern, wählen Sie DATEI / SQL1QUERY.SQL SPEICHERN oder DATEI / SQL1QUERY.SQL SPEICHERN ALS... aus. Die Endung der Datei ist für die Funktionstüchtigkeit der Datei bedeutungslos, d.h. die Endung .txt wäre genauso sinnvoll. Allerdings können Sie die .sql-Endung mit dem Management Studio verknüpfen und ist sie die gebräuchliche Endung für Datenbankanweisungen. Eine solchermaßen gespeicherte Datei lässt sich dann über Datei / Öffnen wieder laden und zur Ausführung bringen. 39 Grundlagen 1 4 2 3 Abbildung 1.12: Ausführen einer Abfrage Bisweilen hat man allerdings gerade nicht nur eine einzige Datei, sondern für einen bestimmten Arbeitsbereich eine ganze Reihe von zusammenhängenden Skripten. Zu ihrer Organisation lässt sich ein ganzes Projekt erstellen. Es enthält neben den Skripten auch die Verbindungsangaben zur Datenbank. 1. Wählen Sie DATEI / NEU / PROJEKT. 2. Wählen Sie aus den drei verschiedenen Projektarten den Eintrag SQL SERVER-SKRIPTS aus. Geben Sie einen neuen Namen für das Projekt sowie auch über die DURCHSUCHEN-Schaltfläche einen Speicherort an. Bestätigen Sie alle Einstellungen mit OK. 40 Grundlagen 1 2 3 Abbildung 1.13: Erstellen eines Projekts 3. Richten Sie mit dem Kontextmenü unter Verbindungen eine neue Verbindung ein oder nutzen Sie die bereits bestehende. Fügen Sie neue Skripte, Abfragen und Anweisungen mit Hilfe des Kontextmenüs unter Abfragen hinzu. Es öffnet sich in diesem Fall ein neues Abfragefenster, welche Sie direkt im Projekt speichern können. Die Abbildung zeigt, wie eine einfache Abfrage ausgeführt wurde und dieses Skript inklusive der benutzten Datenbankverbindung in der Projektmappe gespeichert wurde. Beachten Sie bitte, dass Sie die Möglichkeit haben, in einer Projektmappe mehrere Projekte zu speichern. In diesem Beispiel haben sowohl die Projektmappe als auch das Projekt den selben Namen. Dies ist allerdings nicht notwendig. Stattdessen könnte man sich für AdventureWorks-Datenbank 41 Grundlagen eine große Projektmappe vorstellen, in der Ihre im Rahmen des Buchs erstellten Skripte nach Kapiteln sortiert enthalten sind. Abbildung 1.14: Projektmappe im Einsatz Um dann neue Elemente in einer Projektmappe hinzuzufügen, stehen die diversen Kontextmenüs zur Verfügung. Im Kontextmenü der Projektmappe fügen Sie neue oder vorhandene Projekte hinzu, während sie im Kontextmenü des Projekts sowohl neue Datenbankverbindungen als auch neue Abfragen hinzufügen. Diese beiden letzteren Elemente lassen sich auch in den jeweiligen Projektabteilungen neu erstellen. 42 Grundlagen Abbildung 1.15: Projekte und Abfragen einer Projektmappe hinzufügen 1.2.3 Abfragen im Editor ausführen Eigentlich müsste man gar kein T-SQL lernen, um einfache Daten aus dem MS SQL Server zu extrahieren oder solche Operationen wie Daten einfügen, löschen oder aktualisieren durchzuführen. Mit einigem Geschick, wenngleich auch nicht mit demselben professionellen Effekt, könnte man sich diese vier typischen Operationen einfach auch im Abfrage-Editor grafisch zusammenstellen. Dabei handelt es sich um eine einfach zu bedienende Oberfläche, wie sie in ähnlicher Weise auch bspw. in MS Access vorhanden ist. Leider ist dies auf einfachste Abfragen beschränkt, sodass man wohl doch besser mit T-SQL arbeitet. Für die Nutzung des Abfrage-Editors müssen Sie zunächst ein leeres AbfrageFenster öffnen, da ansonsten der Menü-Eintrag für den Editor nicht erscheint. Um eine Abfrage auszuführen sind folgende Schritte notwendig: 1. Wählen Sie ABFRAGE / ABFRAGE IN EDITOR ENTWERFEN. Im sich öffnenden großen Dialogfenster sieht man drei Bereiche. Im oberen Bereiche befindet sich der Tabellenbereich, in welchem die verschiedenen ausgewählten Tabellen mit ihren Spalten und Verknüpfungen angezeigt 43 Grundlagen werden. Im mittleren Teil befindet sich der Bearbeitungsbereich, in dem man Spalten, ihre Aliasnamen, Werte für Filter, Sortierungen etc. angeben kann. Einige Werte lassen sich aus Listenfeldern abrufen, andere müssen dagegen tatsächlich eingegeben werden. 1 2 3 Abbildung 1.16: Tabellen im Editor auswählen 2. Zunächst wählen Sie aus der sich öffnenden Tabellenliste die von Ihnen gewünschte Tabelle aus. In diesem Beispiel handelt es sich um die beiden Tabellen Production.Product und Production.ProductSubcategory, wobei der erste Namensteil für das so genannte Datenbankschema (übergeordnete Struktur) und der zweite Teil für den Tabellennamen steht. Beide Tabellen sind über eine Primärschlüssel-FremdschlüsselVerknüpfung verbunden, sodass automatisch ein entsprechender Pfeil gezogen wird. Dabei gibt das Schlüsselsymbol an, dass der Primärschlüssel in der Tabelle ProductSubcategory gespeichert ist, während das Symbol 44 Grundlagen für die Unendlichkeit angibt, dass dieser Schlüssel beliebig oft in der Product-Tabelle referenziert wird. N Produkte aus der Product-Tabelle stehen mit einem Datensatz aus der ProductSubcategory-Tabelle in Beziehung. Möchten Sie später weitere Tabellen auswählen, öffnen Sie das Kontextmenü im Tabellenbereich. 1 2 3 Abbildung 1.17: Spalten(namen), Filter und Sortierung vorgeben 3. Wählen Sie danach die Spalten aus den benötigten Tabellen aus. Dies gelingt entweder im Tabellenbereich, indem Sie die Spalten markieren, oder Sie können unten im Eingabebereich die Spalten aus einer Auswahlliste auswählen. Die markierten Spalten aus den Tabellen werden automatisch auch als anzuzeigende Spalten in der Ergebnismenge verwendet. 45 Grundlagen Es ist allerdings nicht nowendig, tatsächlich alle Spalten, auf die Sie im Laufe der Abfrage zugreifen, auch tatsächlich auszugeben. Wenn eine Spalte nur für einen Filter verwendet werden soll, besteht keine Notwendigkeit, sie auch tatsächlich auszugeben. Dies ist dann umso wichtiger, wenn der Filter immer zum gleichem Wert führen würde und dieser Wert dann in jeder Spalte wieder genannt wird. In der Spalte AUSGABE im Bearbeitungsbereich können Sie daher noch einmal genau angeben, dass zwar diese Spalte bspw. für eine Sortierung oder für einen Filter verwendet wird, aber gerade nicht ausgegeben werden soll. 4. In der Spalte ALIAS im Bearbeitungsbereich geben Sie dann bei den Spalten, welche in der Ergebnismenge erscheinen sollen, einen möglichen Ersatznamen für die Spaltenköpfe an. Dieser Name wird als Alias(name) bezeichnet, und sollte einer guten Bezeichnung dienen, wenn der originale Tabellenspaltenname nicht für eine Ausgabe geeignet ist, weil bspw. eine Abkürzung verwendet wurde. Im aktuellen Beispiel sollen bspw. der Name des Produktkategorie und der Produktname ausgegeben werden. Da beide Spalte in der Tabelle Name heißen, erhalten Sie für die Abfrage einen Aliasnamen. 5. Sofern das Ergebnis nach einer Spalte sortiert werden soll, geben Sie einfach in der Spalte SORTIERUNGSART für die zu sortierende Spalte die entsprechende Reihenfolge mit Aufsteigend oder Absteigend an. Die Sortierung erfolgt automatisch so, dass sie für den Spaltendatentyp korrekt ist, d.h. von A-Z, 1-x oder auch nach dem Kalender. 6. Beim Auswahl der Sortierung werden automatisch in der Reihenfolge der Auswahl die Werte für die Spalte SOTIERREIHENFOLGE gesetzt. Wenn man also erst nach dem Produktnamen und dann nach der Kategorie sortieren möchte, dann trägt man in die Spalte für den Produktnamen den Wert 1 und in der Spalte für den Kategorienamen den Wert 2 ein. 7. Schließlich möchte man möglicherweise gar nicht alle Werte aus der Tabelle abrufen, sondern einen so genannten Filter anwenden, d.h. nur alle Produkte einer bestimmten Kategorie, mit einer bestimmten Produktnummer oder einem bestimmten Preis. Es ist dabei auch möglich, mehrere Bedingungen anzugeben, da neben der Spalte FILTER noch weitere 46 Grundlagen Oder-Spalten sind, in denen zusätzliche Bedingungen eingetragen werden können. Die Angabe einer solchen Bedingung gelingt über einen Ausdruck, der die Vergleichsoperatoren >, >=, <, <=, =, != sowie die besonderen SQLOperatoren, die in einem späteren Kapitel vorgestellt werden, erwartet. Im aktuellen Beispiel soll der Produktnamen kleiner C und der Preis größer 100 sein. Abbildung 1.18: Ausgabe und Einsatz der Abfrage 8. Nachdem alle Angabe getroffen sind, klicken Sie auf OK, verlassen dadurch den Abfrage-Editor und gelangen mit dem fertig erstellten Quelltext in das Abfragefenster. Dort führen Sie die Abfrage so aus, als hätten Sie sie selbst nicht nur erstellt, sondern sogar selbst nieder geschrieben. Neben Abfragen werden Sie in diesem Buch auch lernen, wie Sie Daten über SQL aktualisieren können. Dies gelingt nicht über den SELECT-Befehl, den Sie gerade unter Einsatz des Abfrage-Editors erstellt haben, sondern über UPDATE. Einige Grundbestandteile in dieser Anweisung sind allerdings gleich denen der Abfrage. 1. Wählen Sie ABFRAGE / ABFRAGE IN EDITOR ENTWERFEN. 2. Ändern Sie den Typ der zu erstellenden Abfrage über das Kontextmenü über TYP ÄNDERN / AKTUALISIEREN in eine Aktualisierungsabfrage. 47 Grundlagen 1 2 3 Abbildung 1.19: Aktualisierungsabfrage 3. Wählen Sie wie zuvor eine Tabelle aus, die Sie aktualisieren möchten. Dies gelingt über das Kontextmenü mit dem Eintrag TABELLE HINZUFÜGEN. In diesem Beispiel wählen Sie die Product-Tabelle aus. 4. Wählen Sie im Bearbeitungsbereich die Spalten aus, die Sie für die Abfrage benötigen. Dies sind im Normalfall zwei Spaltentypen, nämlich zum einen eine Spalte, die einen neuen Wert erhalten soll, und zum anderen eine Spalte, auf die ein Filter angewendet wird, um die entsprechenden zu aktualisierenden Datensätze zu finden. Geben Sie keinen Filter an, werden alle Datensätze aktualisiert. Möchten Sie allerdings einen Filter verwenden, können Sie sich aussuchen, ob der Filter auf eine Spalte als die zu aktualisierende angewandt werden soll, oder ob Filter und neuer Wert auf die gleiche Spalte angewendet werden sollen. In diesem Fall soll der ListPrice den Wert 100 erhalten, wenn das zuvor ausgewählte Produkt gefunden wird. Daher ist der Filter in der Name-Spalte auf den Ausdruck 48 Grundlagen ='All-Purpose Bike Stand'. Das zusätzlich eingefügte N ist für die Abfrage nicht von Bedeutung, sondern kennzeichnet die Zeichenkette. Weitere Bedingungen können Sie wie zuvor in die ODER-Spalten einfügen. 5. Wählen Sie OK und führen Sie, wenn Sie wollen, die Abfrage auch tatsächlich aus. Um die Änderungen nachher wieder ungeschehen zu machen, geben Sie zusätzlich noch die Anweisungen GO und ROLLBACK ein. Neben Abfragen und Aktualisierungen lässt sich auch das SQL erstellen, mit dessen Hilfe Datensätze aus der Datenbank gelöscht werden können. Es wird letztendlich mit den gleichen Mechanismen erstellt, die auch schon bei den vorherigen Aktionen angewandt wurden. 1. Wählen Sie ABFRAGE / ABFRAGE IN EDITOR ENTWERFEN. 2. Ändern Sie den Typ der zu erstellenden Abfrage über das Kontextmenü über TYP ÄNDERN / LÖSCHEN in eine Löschabfrage. 3. Wählen Sie wie zuvor eine Tabelle aus, aus der Sie Daten löschen möchten. Dies gelingt über das Kontextmenü mit dem Eintrag TABELLE HINZUFÜGEN. In diesem Beispiel wählen Sie die Product-Tabelle aus. 4. Wählen Sie im Bearbeitungsbereich die Spalten aus, die Sie für die Abfrage benötigen. Dies sind bei einer Löschabfrage nur die Spalten, für die ein Filter vorgegeben werden soll. Bei einer Abfrage waren es im Vergleich dazu Filter- und Anzeigespalten, bei einer Aktualisierungsabfrage Spalten, die einen neuen Wert erhalten sollten, und natürlich auch Filter. Da einfach der gesamte Datensatz entfernt werden soll, benötigt man nur Filter-Spalten. In diesem Fall sollen dies Datensätze mit einer Produktnummer BETWEEN 879 AND 882 sein. 5. Wählen Sie OK und führen Sie, wenn Sie wollen, die Abfrage auch tatsächlich aus. Um die Änderungen nachher wieder ungeschehen zu machen, geben Sie zusätzlich noch die Anweisungen GO und ROLLBACK ein. 49 Grundlagen 1 2 3 Abbildung 1.20: Löschabfrage Die beiden Möglichkeiten, eine Tabelle zu erstellen und eine so genannte Einfügeabfrage zu erstellen, sind untereinander sehr ähnlich und ähneln auch sehr der gewöhnlichen Abfrage. Während diese einfach nur eine flüchtige Ergebnismenge erzeugt, erlauben es die beiden anderen diese Ergebnismenge entweder in einer neu zu erstellenden Tabelle oder in einer bereits vorhandenen zu speichern. 1. Wählen Sie ABFRAGE / ABFRAGE IN EDITOR ENTWERFEN. 2. Ändern Sie den Typ der zu erstellenden Abfrage über das Kontextmenü über TYP ÄNDERN / TABELLE ERSTELLEN in eine Erstellungsabfrage. 3. Geben Sie den Namen der zu erstellenden Tabelle ein. 4. Geben Sie die Namen der zu erstellenden Spalten vor. Die Datentypen werden direkt aus der Abfrage bzw. aus der befragten Tabelle übernommen. Die Strukturen von Abfrage und Zieltabelle müssen also übereinstimmen. Alternativ können Sie auch zunächst wie zuvor eine Tabelle bestimmen, 50 Grundlagen aus der Sie Daten auswählen möchten. Dies gelingt über das Kontextmenü mit dem Eintrag TABELLE HINZUFÜGEN. 5. Geben Sie mögliche Abfrageeigenschaften wie Filter, Aliasnamen für die Spaltennamen der zu erzeugenden Tabelle und Sortierungen vor. 6. Wählen Sie OK und führen Sie, wenn Sie wollen, die Abfrage auch tatsächlich aus. Da die Tabelle tatsächlich sofort angelegt wird, folgt im nächsten Quelltext eine Erweiterung zu dem automatisch erzeugten Quelltext. Er enthält zu Beginn noch eine Löschanweisung für die gesamte Tabelle mit Hilfe des DROP-Befehls von SQL und am Ende noch eine Testabfrage der gesamten erstellten Tabelle. Dies soll nachweisen, dass sie erstens existiert und zweitens auch Daten übernommen worden. Die einzelnen Anweisungen werden durch das GOSchlüsselwort getrennt. DROP TABLE [Shirts-Produkte] GO SELECT ProductID, Name, ListPrice INTO [Shirts-Produkte] FROM Production.Product WHERE (Name LIKE N'%Shorts%') GO SELECT * FROM [Shirts-Produkte] selectinto.sql: Löschen, Neuerstellung und Abfrage 51 Grundlagen 1 2 3 4 5 Abbildung 1.21: Erstellungsabfrage Man erhält als Ergebnis der gesamten Abfrage folgende Daten in der erzeugten Tabelle: ProductID ListPrice ----------841 849 … 868 869 52 Name -------------------------------------- -------Men's Sports Shorts, S Men's Sports Shorts, M 59,99 59,99 Women's Mountain Shorts, M Women's Mountain Shorts, L 69,99 69,99 Grundlagen Ein letztes Wort soll noch zu dem sehr schönen Werkzeug des Abfrage-Editors verloren werden. In den zurückliegenden Beispielen wurden immer nur Tabellen ausgewählt, in denen Daten zu bearbeiten oder auszuwählen waren. Im Dialogfenster Tabelle hinzufügen, welches sich im Kontextmenü des Editors des Tabellenbereichs öffnet, sind allerdings vier verschiedene Reiter angebracht, die - je nach Operation - auch alle genutzt werden können. Die drei zusätzlichen Möglichkeiten, um Daten auszuwählen oder überhaupt Zugriff auf Daten zu haben, sollen hier nur kurz erwähnt werden. Sie sind allerdings Themen späterer Kapitel: Tabellen (Relationen) bilden in jeder Datenbank den zentralen Speicherort für Daten bzw. bieten auch für die anderen Möglichkeiten die eigentliche Datenquelle. Sie enthalten die Daten tatsächlich und erlauben bei entsprechender Berechtigung Zugriff auf den gesamten Datenbestand. Ansichten oder auch Sichten stellen gespeicherte Abfragen dar. Sie enthalten also nicht tatsächlich die Daten, sondern nur das SQL der Abfrage, welches zu den Abfragen führt. Dies wird auch oft als virtuelle Tabelle bezeichnet, da man die Sicht in SQL wie eine gewöhnliche Tabelle nutzen kann, sie in Wirklichkeit aber nur eine gespeicherte Abfrage mit einem eigenen Namen darstellt. Sofern eine Sicht wiederum indiziert ist, werden dann auch die Daten in der Sicht gespeichert. 53 Grundlagen Abbildung 1.22: Mögliche Datenquellen Funktionen stellen nicht nur wie Prozeduren eine Möglichkeit dar, um Softwarebausteine in der Datenbank zu entwickeln, sondern erlauben auch die Erstellung von parametrisierten gespeicherten Abfragen. Dabei enthält die Funktion die SQL-Anweisung für die Abfrage, während die möglichen Filter-Werte über die Funktionsparameter angegeben werden können. Dieser Parameter ist bei Sichten nicht möglich, sodass Funktionen als zusätzliche Möglichkeit dienen, Abfragen aus Zeitersparnis- oder auch Sicherheitsgründen vorzubereiten. Synonyme stellen einen Spitz- oder Aliasnamen für DB-Objekte dar. Im Fall des Datenabrufs ist dies vor allen Dingen für Tabellen wichtig. So hat man die Möglichkeit, eine Abstraktionsschicht zwischen den so genannten Basis-Objekten und den DB-Klienten einzuziehen. Unterliegende Namen 54 Grundlagen können sich verändern, wobei das Synonym seinen Namen behält. Ein weiterer Vorteil liegt darin, dass lange Namenskonstrukte wie Server1.AdventureWorks. Person.Employee für server.datenbank.schema.[tabelle|sicht] überflüssig werden, da das kürzere Synonym zum Einsatz kommen kann. 1.2.4 Vorlagen-Editor Für häufig wiederkehrende Aufgaben, die in Form von Skripten ausgeführt werden können, gibt es die Möglichkeit, so genannte Vorlagen zu erstellen. Dies sind halb fertige T-SQL-Skripte, in denen Textbereiche wie Objektnamen oder einfache Werte angegeben und beim konkreten Aufruf gegen konkrete Werte ausgetauscht werden können. Verschiedene fertige Vorlagen sind ebenfalls bereits vorhanden. Gehen Sie für die Erstellung und den Aufruf solcher Vorlagen folgendermaßen vor: 1. Wählen Sie ANSICHT / VORLAGEN-EXPLORER. 2. In diesem Vorlagen-Explorer können Sie entweder eine bereits vorhandene und/oder von Ihnen erstellte Vorlage aus einem von Ihnen benannten Ordner auswählen. Alternativ können Sie aber auch eine neue Vorlage erstellen. Wählen Sie dazu aus dem Kontextmenü NEU / VORLAGE. Möchten Sie einen neuen Ordner für die Sortierung Ihrer Vorlagen einfügen, wählen Sie an dieser Stelle den Eintrag ORDNER. 3. Die eingefügte Vorlage können Sie mit Befehlen aus dem Kontextmenü bearbeiten. Wählen Sie UMBENENNEN für einen neuen Namen, LÖSCHEN zum Löschen etc. Wählen Sie BEARBEITEN, um T-SQL-Quelltext anzugeben. 55 Grundlagen 1 2 4 3 5 6 7 Abbildung 1.23: Vorlagen erstellen und nutzen 56 Grundlagen 4. Die Erstellung eines Vorlagen-Textes ist relativ simpel. Die wichtigste Überlegung besteht darin, den Quelltext so zu formulieren, dass er nachher durch die Vorgabe von einigen Parameterwerten in möglichst vielen Fällen eingesetzt werden kann. Diese Parameter werden mit Hilfe von spitzen Klammern in der Form <Parametername, Datentyp, Vorgabewert> angegeben. Sie müssen nicht extra angemeldet werden, sondern werden automatisch anhand dieser Syntax aus dem Vorlagentext extrahiert und nachher beim Verwenden dieser Vorlage in einem Dialogfenster angegeben, um dort ersetzt zu werden. An dieser Stelle soll nicht auf die T-SQL-Syntax eingegangen werden, da dies ja Thema der vielen nachfolgenden Kapitel des Buchs ist. Wesentlich ist daher nur die Erstellung, Speicherung und Verwendung einer Vorlage neben der Angabe und Verwendung eines Parameters. 5. Wenn Sie die Vorlage erstellt, Parameter hinzugefügt und die Vorlage gespeichert haben, wollen Sie sie beizeiten aufrufen. Dazu öffnen Sie einfach die gewünschte Vorlage aus dem Vorlagen-Explorer und wählen dann ABFRAGE / WERTE FÜR VORLAGENPARAMETER ANGEBEN. 6. In dem sich öffnenden Dialogfenster stehen die verschiedenen Parameter mit ihren Datentypen und ihren möglichen Vorgabewerten. Ersetzen Sie ggf. die Werte und bestätigen Sie dieses Dialogfenster. Dadurch ersetzen Sie die Parameter durch die Vorgabewerte oder die an ihrer Stelle eingegebenen Werte im Vorlagentext. 7. Führen Sie die geänderte Anweisung aus und erfreuen Sie sich an der gesparten Arbeitszeit durch Ihre hervorragende Vorlage. 1.2.5 Dokumentation Trotz der MS SQL Server-Literatur, die Sie bspw. in Form dieses Buchs oder anderer Bücher dieser Reihe in der Hand halten bzw. im Schrank besitzen, werden Sie letztendlich in der Dokumentation noch mehr Informationen finden. Diese sind vielleicht nicht mit so vielen Beispielen erläutert wie in einem Buch, doch dafür sind sie sehr detailreich hinsichtlich der verwendeten Technik. 57 Grundlagen Abbildung 1.24: Dokumentation Verwenden Sie die Dokumentation folgendermaßen: 1. Rufen Sie die Dokumentation auf, indem Sie START / PROGRAMME / MS SQL SERVER 2005 / DOKUMENTATION UND LERNPROGRAMME / SQL SERVER-ONLINEDOKUMENTATION auswählen. 2. Sofern Sie einen bestimmten SQL-Befehl suchen, ist dies eigentlich besonders einfach, denn dann sollten Sie einfach unter dem Reiter SUCHEN diesen SQL-Befehl eingeben und die SUCHEN-Schaltfläche betätigen. 3. Wählen Sie innerhalb der verschiedenen Reiter auf der linken Seite einen Bereich aus, indem Sie die Hilfetexte anzeigen wollen. Dies kann die lokale Hilfe, die MSDN-Online-Dokumentation auf Deutsch oder Englisch oder sonstige Informationsplattformen sein. Innerhalb dieser einzelnen Reiter erscheinen dann im mittleren Bereich verschiedene Artikel, wobei für den T-SQL-Programmierer in vielen Fällen der erste Artikel bereits der richtige ist, da hier die Suchfunktion anhand eines SQL-Schlüsselworts aus verständlichen Gründen besonders gut funktioniert. 1.3 Programmierbarkeit Unter dem sehr ungewöhnlichen deutschen Wort Programmierbarkeit versteht der MS SQL Server nicht nur die Fähigkeit, überhaupt nützlichen Quelltext in verschiedenen Sprachen in der Datenbank zu speichern oder - wie im Fall von T-SQL - direkt auszuführen, sondern auch die verschiedenen programmierten 58 Grundlagen Objekte. Die Syntax, mit der diese Objekte erstellt werden können, ist neben der sehr umfangreichen Darstellung von Abfragen und Analysen ein zentrales Thema dieses Buchs. Daher dient dieser Abschnitt nur als Appetithappen. Sie sollen also sehen, welche Objekte programmiert und sogar in der Datenbank gespeichert werden können. Die AdventureWorks-Datenbank ist bereits mit sehr vielen programmierten Objekten und damit, um im Jargon von MS SQL Server zu bleiben, mit sehr viel Programmierbarkeit ausgestattet. Daher kann es sehr hilfreich sein, diese verschiedenen Objekte aufzurufen und versuchen, auf ihre Einsatzweise hin zu verstehen. 1.3.1.1 Prozeduren Eine Prozedur stellt ein kleines Programm dar, das in der Datenbank gespeichert ist und das innerhalb der Datenbank über SQL oder außerhalb der Datenbank über eine beliebige Programmiersprache aufgerufen werden kann. Typische Einsatzbereiche von Prozeduren sind vereinfachte Datenbearbeitungsroutinen. In diesem Fall verwendet man in der Anwendung, welche die Datenbank nutzt, nicht den entsprechenden SQL-Befehl für die Erfassung, Löschung oder Aktualisierung von Daten, sondern ruft die entsprechende Prozedur auf und übergibt die Daten. Dadurch kann man Validierungen oder beliebige, über die einfache Datenbearbeitung hinausgehende Anweisungen automatisiert ausführen, ohne in der äußeren Anwendung darauf Rücksicht zu nehmen. Ein weiterer Einsatzbereich, der gerade für den MS SQL Server sehr wichtig ist, stellt gespeicherte Abfrage in Form von Prozeduren dar, wobei hier Filterwerte als Parameter übergeben werden können und dadurch der Datenabruf besonders einfach gestaltet wird. Eine solche Prozedur liefert eine Ergebnismenge wie eine Abfrage in SQL zurück. 59 Grundlagen 1 2 Abbildung 1.25: Prozeduren untersuchen Rufen Sie vorhandene Prozeduren folgendermaßen auf. Sie können über den folgenden Weg auch neue Prozeduren erstellen, sofern Sie nicht einfach den dazu notwendigen T-SQL-Quelltext in ein leeres Abfragefenster eingeben. Interessant ist das gleich erwähnte Kontextmenü noch für den Aufruf der Objekte, die von der Prozedur abhängig sind. Sofern solche abhängigen Objekte existieren, kann diese Prozedur nicht einfach gelöscht werden, weil die abhängigen Objekte dadurch ungültig würden. 1. Öffnen Sie im Objekt-Explorer im Knoten PROGRAMMIERBARKEIT einer Datenbank den Knoten GESPEICHERTE PROZEDUREN. Dort sind die verschiedenen Datenbankprozeduren aufgelistet. Im Knoten GESPEICHERTE SYSTEMPROZEDUREN befinden sich dagegen für das gesamte Datenbanksystem nutzbare Prozeduren. 60 Grundlagen 2. Wählen Sie für eine Sie interessierende Prozedur aus dem Kontextmenü den Eintrag SKRIPT FÜR GESPEICHERTE PROZEDUREN ALS und wählen Sie dann aus der sich öffnenden Liste einen der Einträge. CREATE steht für Erstellung, ALTER für Änderung, DROP für Löschung und EXECUTE für Ausführung. Jeder Befehl kann in einem neuen Abfrage-Fenster geöffnet in eine Datei bzw. in die Zwischenablage kopiert werden. Um sich also einfach das Erstellungsskript einer solchen Prozedur anzusehen, wählen Sie den Eintrag CREATE IN / NEUES ABFRAGE-EDITORFENSTER. 3. Sofern Sie über die Schaltfläche CREATE den Quelltext der Prozedur in einem neuen Abfragefenster geöffnet haben, sehen Sie die Prozedur so, wie Sie sie in T-SQL nach der Lektüre des Buchs ebenfalls hätten vorgeben können. Mit dem sich öffnenden Quelltext lässt sich eine Prozedur in der Datenbank speichern. Er sieht im Falle einer Änderung bis auf die erste Zeile genauso aus, wobei in dieser ersten Zeile dann die ALTERAnweisung steht. Diese Ansicht erhalten Sie über die entsprechende ALTER-Schaltfläche im vorher beschriebenen Kontextmenü. Eine Änderung löscht eine Prozedur nicht, sodass abhängige Objekte Schaden nehmen könnten, sondern verändert ihren Quelltext, um bspw. neue Anforderungen zu berücksichtigen oder Fehler zu korrigieren. Während diese Schatlflächen mehr für einen Leser des Quelltextes interessant sind, aber nicht besonders viele Möglichkeiten bieten, mit der Prozedur kreativ umzugehen, ist die Schaltfläche EXECUTE dagegen darauf ausgerichtet, in einem T-SQL-Skript die Prozedur auch tatsächlich auszuführen. Wenn die Verwendung von T-SQL nicht gewünscht wird, obwohl nur noch die Parameterwerte vorgegeben werden müssen, dann kann man auch die Schaltfläche GESPEICHERTE PROZEDUR AUSFÜHREN aus dem Kontextmenü verwenden. Es führt nicht zu einem T-SQL-Skript, sondern vielmehr zu einem Dialogfenster, in welchem die benötigten bzw. gewünschten Parameterwerte eingetragen werden können. Dies ist eine vereinfachte grafische Darstellung der in diesem Schritt beschriebenen T-SQL-Lösung. 61 Grundlagen 1 2 2 3 Abbildung 1.26: Ausführen einer Prozedur 4. Es öffnet sich wiederum ein neues Fenster, in dem nun allerdings nicht die Erstellung der Prozedur und damit auch ihr Quelltext angegeben wird, sondern die Ausführung derselben. Das T-SQL-Skript stellt nicht das bereits zuvor kurz vorgestellte Standard-SQL dar, sondern bietet eine Variablendeklaration und die Ausführung der Prozedur über die EXECAnweisung, wobei die erstellten Variablen als Parameter übergeben werden. Die Parameter werden nur deklariert, erhalten allerdings noch 62 Grundlagen keinen Wert. Dies ist vom Benutzer durchzuführen, wobei hier jede beliebige T-SQL-Anweisung zum Einsatz kommen kann. Dies schließt einfache und direkte Wertvorgaben genauso ein wie auch komplexe Ausdrücke, den Abruf von Abfrageergebnissen, die Verwendung von Funktionen oder Berechnungen. Im Beispiel, das im Bildschirmfoto für die Nachwelt festgehalten wurde, handelt es sich zum einen um den klassischen Fall einer Wertvorgabe und zum anderen um den Abruf eines Aggregats (größtes Datum) aus der von der Prozedur abgefragten Tabelle. -- TODO: Set parameter values here. SET @StartProductID = 970 SET @CheckDate = (SELECT MAX(StartDate) FROM Production.BillOfMaterials) 5. Schließlich kann man die ausgewählte Prozedur über das bearbeitete Skript starten, indem man die AUSFÜHREN-Schaltfläche wählt. Im Fall der ausgewählten Prozedur uspGetBillOfMaterials erhält man für eine StartProduktnummer und ein Vergleichsdatum die zugehörigen Materiallisten in Form eines Abfrageergebnisse zurück. In diesem Fall hat man also eine in einer Prozedur versteckte parametrisierte Abfrage ausgeführt. In einem anderen Fall hat man möglicherweise Daten- oder Systemänderungen vorgenommen. 1.3.1.2 Funktionen Eine Funktion hat viele Gemeinsamkeiten mit einer Prozedur, was sich insbesondere auch in ihrer Darstellung in der grafischen Oberfläche und in diesem einleitenden Niveau dieses Abschnitts deutlich widerspiegelt. Es handelt sich ebenfalls um ein kleines Programm, das in der Datenbank gespeichert ist und das einen klar begrenzten Verantwortungsbereich im Rahmen der Datenbankbenutzung ausfüllt. Eine Funktion hat ebenfalls die Fähigkeit, Übergabeparameter anzunehmen, kann über das gleiche Kontextmenü verschiedentlich in ihrem Quelltext betrachtet oder ausgeführt werden. Daher soll aus Platzgründen auf eine erneute Darstellung dieses Kontextmenüs verzichtet werden. Man findet im vorherigen Abschnitt ausreichendes Bildmaterial dazu. 63 Grundlagen Im Gegensatz zu einer Prozedur besitzt eine Funktion allerdings einen so genannten Rückgabewert, sodass man sie mit Methoden oder Funktionen einer gewöhnlichen Programmiersprache vergleichen kann, wenn in diesem Vergleich vorausgesetzt wird, dass die erwähnte Funktion oder Methode ebenfalls einen Rückgabewert liefert. Einige Programmiersprachen unterscheiden ja auch mit Hilfe verschiedener syntaktischer Elemente, ob sich eine Funktionalität eher als Prozedur oder eher als Funktion bezeichnen lassen würde - auch dann, wenn die Programmiersprache an sich diese Unterscheidung nicht trifft. Entweder handelt es sich um das Schlüsselwort void, um anzugeben, dass diese Methode keinen Rückgabewert liefert, oder nur um die Verwendung der returnAnweisung für die tatsächliche Rückgabe eines Wertes. Eine Prozedur kann über einen Ausgabeparameter ebenfalls einen Wert an das aufrufende Programm zurückgeben, doch ein Rückgabewert zeichnet sich dadurch aus, dass man den Funktionsaufruf auf die rechte Seite einer Zuweisung bzw. überall dort platzieren kann, wo ein Ausdruck erwartet wird. Eine Prozedur ist kein solcher Ausdruck, da man den Ausgabeparameter zunächst abrufen und dann die Variable mit dem abgerufenen Wert wieder als Ausdruck verwenden könnte. Wenn eine Funktion sich dadurch auszeichnet, einen Rückgabewert zu haben und als Ausdruck verwendet werden zu können, dann kann man sie so gestalten, dass sie auch direkt in SQL zum Einsatz kommen können. Dies bedeutet, dass sie neben solchen Standardfunktionen wie COUNT, MIN oder SUM in einer SQLAnweisung stehen und Werte für einen Filter oder eine Berechnung liefern können. Sie ermöglichen es damit genauso wie Prozeduren, die Arbeit mit der Datenbank sehr zu vereinfachen, wobei in einem solchen Fall allerdings ganz gewöhnliches SQL dadurch vereinfacht werden kann, weil die selbst erstellte Funktion komplexe Berechnungen, Auswertungen oder Verknüpfungen selbst vornimmt und nur noch die gewünschten, vielleicht sogar parametrisierten Werte zurückliefert. Wie gerade schon gesehen, ist eine Prozedur in der Lage, ein Abfrageergebnis zurückzuliefern. Diese Fähigkeit besitzt eine Funktion auch, wobei sie allerdings in der FROM-Klausel einer Abfrage erscheinen kann, die normalerweise eine Tabellen- oder Sichtreferenz erwartet. Über eine solche Funktion ist es möglich, fertige Teilabfragen mit bspw. komplexen, sicherheitsrelevanten Bedingungen sowie Verknüpfungen parametrisiert aufzurufen. 64 Grundlagen 1.3.1.3 Trigger Trigger sind ein weiteres programmierbares Schema-Objekt. Es wird allerdings nur in ganz wenigen Beispielen genutzt und im Rahmen des Buchs nicht weiter vertieft. Im Wesentlichen ist die Erstellung von Triggern zwar mit den T-SQLFähigkeiten, die in diesem Buch vermittelt werden, zu bewerkstelligen. Allerdings handelt es sich um ein hauptsächlich administratives Thema, sodass es besonders gut im Administrationsbuch aufgehoben ist. Während eine Prozedur ausdrücklich über ihren Namen aufgerufen wird, ist ein Trigger entweder einem Schema-Objekt wie einer Tabelle oder einer Sicht zugeordnet oder wartet auf die Ausführung bestimmter DDL (Data Definition Language)-Befehle. Die eine Trigger-Art wird als DML (Data Manipulation Language)-Trigger bezeichnet, da sie auf die SQL-Anweisungen INSERT, UPDATE und DELETE wartet, welche den Trigger auslösen und damit seine Anweisungen zur Ausführung bringen. Die andere Art bezeichnet man als DDLTrigger, weil diese Trigger auf Anweisungen wie CREATE, ALTER oder DROP warten, welche zur DDL gehören. Innerhalb eines solchen Triggers lassen sich nahezu beliebige Anweisungen wie auch in einer Prozedur oder Funktion angeben. Die Besonderheit von Trigger liegt tatsächlich ausschließlich in der automatischen Ausführung auf Basis von anderen Befehlen. Dadurch ist es möglich, bestimmte Sicherheits- oder Datenkonsistenzregeln zu programmieren, die mit gewöhnlichem SQL oder sonstigen Datenbankeinstellungen administrativer Art nicht abgebildet werden können. Da innerhalb eines Triggers die gesamte TSQL-Syntax zur Verfügung steht, stellen Trigger eine wesentliche Fähigkeit von Datenbanken ab, um sicher zu sein und konsistent zu bleiben. 1.3.1.4 Assemblies Mit Hilfe der Anweisung CREATE ASSEMBLY name FROM 'C:\assembly.dll' lassen sich .NET-Assemblies in der Datenbank verankern. Dies eröffnet für den MS SQL Server ganz neue Möglichkeiten der Datenbank- und Softwareentwicklung. Bislang konnte besonders Oracle neben der datenbankeigenen Programmiersprachen PL/SQL auch noch zusätzlich anbieten, kompilierte Klassen einer so umfangreichen Programmiersprache wie Java für die Entwicklung von Datenbankmodulen zu verwenden. Dies ist nun für den MS SQL Server auch in Form der .NET-Technologie möglich geworden. Assemblies aus den Sprachen 65 Grundlagen C#.NET oder VB.NET sowie natürlich anderen .NET-fähigen Sprachen lassen sich nun direkt in die Datenbank laden. Dies eröffnet Möglichkeiten, objektrelational zu arbeiten, indem komplexe Datenstrukturen in Form von Klassen mit mehreren Eigenschaften/Feldern und Methoden abgebildet werden, als auch Prozeduren, Funktionen und Trigger nicht mehr über T-SQL, sondern direkt über .NET zu erstellen und sie dann wie gewöhnliche, in T-SQL erstellte Module zu verwenden. T-SQL-Vorwissen ist dennoch notwendig, weil die Organisation und Verwaltung der Assemblies über T-SQL funktioniert und Abfragen sowie die Erstellung von sonstigen Schema-Objekten weiterhin über T-SQL erfolgt. 1.4 Beispieldatenbank AdventureWorks Im Normalfall haben wir für die verschiedenen Bücher im Bereich Datenbanken immer auch eigene Datenbanken entwickelt. In diesem Fall allerdings hat sich dieses Vorgehen als nicht sonderlich weltverbessernd herausgestellt. Da die AdventureWorks-Datenbank die Schwächen der sehr vereinfachten NordwindDatenbank vollständig überwindet und sich seit längerer Zeit auch steigender Beliebtheit erfreut sowie, was eigentlich sehr viel wichtiger ist, derart umfangreich ist, dass eine selbst erstellte Datenbank (sogar im Vergleich zu unseren vorherigen Datenbanken) nicht besser sein kann, haben wir uns entschlossen, zum ersten Mal auf eine vom Hersteller bereitgestellte Beispieldatenbank zurückzugreifen. 1.4.1 Allgemeine Design-Prinzipien Die Datenbank ist in unterschiedliche Schemata eingestellt, die mit ihren wesentlichen Tabellen im nachfolgenden kurz vorgestellt werden. Es werden in diesem Buch aus Gründen der Übersichtlichkeit nicht alle Tabellen genutzt. Der Umfang der gesamten Datenbank wäre dafür ein wenig zu groß, doch um keine Langeweile aufkommen zu lassen, sollten auch nicht nur solch klassische Tabellen wie solche zur Produkt- und Kundendaten genutzt werden. Bis auf wenige Fälle werden keine eigenen Tabellen erstellt oder benötigt, sodass die zusammen mit dem Datenbanksystem installierte AdventureWorks-DB unmittelbar mit den bereitgestellten Skripten dieses Buchs Ergebnisse produzieren sollte. 66 Grundlagen Daten von Angestellten befinden sich im Schema HumanResources. Dabei ist ganz wesentlich, dass solche typischen Spalten für Name und Adresse gerade nicht in einer Tabelle wie Employee gespeichert werden. Stattdessen existieren eigene Tabellen namens Contact und Address im Schema Person, welche sämtliche Personen wie Verkäufer und Kunden jeweils verknüpft. Dies ist eine Design-Entscheidung, die es verhindert, die gesamten Kontaktattribute für jedes Objekt des Weltmodells jeweils neu zu erfassen. Es erschwert allerdings auch gleichzeitig Abfragen, da bereits eine Verknüpfung durchgeführt werden muss, um für Daten aus einer Tabelle wie Employee, SalesOrderHeader oder VendorContact auch die entsprechenden Kontaktinformationen abzurufen. Eine weitere wichtige Designentscheidung, die in der AdventureWorks-DB vorgestellt wird, ist in der Tabelle Address zu erkennen. Auch sie sammelt für alle möglichen Objekte im Weltmodell von AdventureWorks, die Adressen besitzen können, zentral diese große Anzahl von Adressen. Eine andere Möglichkeit wäre gewesen, die Felder dieser Tabelle ebenfalls wie die Felder von der Contact-Tabelle in die einzelnen Tabellen der referenzierenden Tabellen einzufügen. Da im Gegensatz zu den Contact-Feldern allerdings auch mehrere Adressen pro Objekt gespeichert werden sollten, d.h. eine 1:n- (ein Objekt hat mehrere Adressen) oder sogar eine n:m-Beziehung (mehrere Objekte teilen sich eine Adresse bzw. mehrere Objekte teilen sich mehrere Adressen) notwendig ist, hätte man neben den Beziehungstabellen, die nun von den einzelnen Objekten zu dieser zentralen Address-Tabelle führen, die gesamte Address-Tabelle für die einzelnen Objekte neu erstellen müssen. Entstanden wären dann solche Tabellen wie EmployeeAddress oder VendorAddress. Darüber hinaus zeigt die Datenbank genau in diesem Bereich auch noch die Technik einer so genannten Werteliste. Bei wiederholenden Begriffen, die allerdings nur in einem einzigen Feld einer Tabelle auftauchen, ist es oftmals eine wichtige Frage des Datenbankdesigns, inwieweit hier noch einmal eine eigene Tabelle für diese sich wiederholenden Werte entwickelt werden sollen. Mit Blick auf solche Tabellenkalkulationsprogramme wie MS Excel, in denen Werte, die bereits in der Spalte benutzt wurden, automatisch angezeigt werden, sobald die ersten identifizierenden Buchstaben eingegeben werden, findet man oftmals kleinere Datenbanklösungen, die solche Wertelisten (oder besser gesagt: Kandidaten für Wertelisten) nicht eigens auslagern, sondern stattdessen direkt in vollem Wortlaut in der Spalte speichern. Auswahlmenüs in einer äußeren Anwendung lassen sich dann hervorragend mit SELECT DISTINCT-Abfragen 67 Grundlagen ermitteln, die mögliche Duplikate ausblenden und daher quasi genau die Werte anzeigen, die in einer solchen Wertelistentabelle erscheinen könnten. Allerdings besteht immer auch die Gefahr, dass unkorrekte Schreibweisen (zusätzliche Leerzeichen, Bindestriche, allgemeine Rechtschreibfehler sowie korrekte Schreibvarianten oder Singular-/Plural-Unterschiede) dazu führen, dass gleichartige Werte verschieden gespeichert werden und daher Filter schlecht funktionieren oder inkonsistente Daten entstehen. Dies kann teilweise durch eine weitere Normalisierung (Prozess der Bildung eines guten relationalen Modells) verhindert werden, was konkret bedeutet, Wertelistentabellen zu erstellen. Solche Tabelle sind die ganzen Type-Tabelle, d.h. Tabellen wie ContactType oder AddressType. Im klassischen Fall besteht eine solche Tabelle wie ein Array in einer beliebigen Programmiersprache nur aus dem Primärschlüsselfeld und dem gespeicherten Wert für diesen Schlüssel. Im beschriebenen Fall von AdventureWorks gibt es noch weitere Spalten wie bspw. das Änderungsdatum, die allerdings für das grundlegende Verständnis unwichtig sind. Eine solche Werteliste kann man ebenfalls hervorragend in einem Auswahlmenü in einer äußeren Anwendung abrufen. Man kann darüber hinaus allerdings auch für konsistente Daten sorgen, sofern Einträge in dieser Wertelistentabelle nicht einfach unkontrolliert von jedem Benutzer vorgenommen werden können und plötzlich doch wieder die erwähnten verschiedenen Schreibweise für den gleichen Begriff erscheinen. Neben diesem Vorteil bietet dieses Vorgehen den Nachteil, dass ein Datenabruf mit Textinhalten und nicht nur den Schlüsselwerten zu vielen Verknüpfungen führt, um die ganzen Primärschlüssel-Fremdschlüssel-Beziehungen wieder aufzulösen, die nötig sind, um aus den Schlüsselwerten wieder die eigentlich gemeinten Werte zu erzeugen. Ob solche Lösungen gut oder schlecht sind, kann abschließend nicht beurteilt werden. Wesentlich ist vielmehr, dass man im Rahmen des DB-Designs (was nicht Thema dieses Buchs ist, sondern eines anderen Buchs im Bereich Datenbanken von Comelio Medien sein wird) verschiedene typische Lösungsansätze kennt und sich bewusst für den einen oder anderen Weg entscheidet. Im Fall von AdventureWorks muss man davon ausgehen, dass bei einer so großen Datenstruktur dieses Vorgehen dafür sorgt, dass von jeder Person die gleichen Kontaktdaten gespeichert werden können, ohne dass die Feldstrukturen in mehreren Tabellen erscheinen müssen. Auch wenn objektrelationale Techniken aus Gründen der Didaktik in der AdventureWorks umgesetzt sind und auch in diesem Buch diskutiert werden, so 68 Grundlagen zeigt dieses Datenmodell bereits Grenzen des relationalen Modells. Dies soll keinesfalls als Technologiekritik verstanden werden, denn die vielen so genannten Alternativen und Verbesserungen konnten sich bislang in keiner Weise durchsetzen. Im Rahmen von Beratungsveranstaltungen und Seminaren gibt es auch an uns oft Fragen mit fast schon spionageartigem Unterton, ob wir denn Datenbanken bzw. Unternehmen wüssten, welche neue Techniken des relationalen Modells einsetzen. Ab und an treffen wir tatsächlich auf solche Ansätze, doch im Großen und Ganzen bleibt es bei einigen Buchkapiteln oder Zeitschriftenbeiträgen, die sich diesem Thema immer wieder annehmen. Was ist damit gemeint? Ohne bereits die Vorstellung der Technik umfassend vorweg zu nehmen, soll dennoch kurz auf dieses Thema eingegangen werden, weil die Tabellen, an denen man es sehr gut erläutern kann, bereits genannt wurden. Darüber hinaus handelt es sich dazu auch noch um solche eigentlich trivialen Datenstrukturen wie Kontaktdaten. Diese müssten doch selbstverständlich hervorragend und vor allen Dingen problemlos mit geschlossenen Augen diskussionsfrei modelliert werden können. Interessanterweise bieten sie allerdings gerade das Paradebeispiel für die scheinbare Notwendigkeit von weiter gehenden Techniken der Datenbankmodellierung. Das traditionelle relationale Modell kennt keine Vererbung und keine Möglichkeit, gleichartige Feldstrukturen mehrfach zu benutzen. Wenn Kunden, Angestellte und Verkäufer die gleichen zehn Felder für Kontaktdaten (Name und Adresse mit jeweiligen Unterfeldern) benutzen, verwendet man folgendes Vorgehen: jede Tabelle für die drei genannten Objekte erhält die gleichen zehn Felder. Der Aspekt, dass zusätzlich auch noch mehrere Adressen einem Objekt zugeordnet werden können, kann vernachlässigt werden, da er nur ein Spezialproblem darstellt. Wenn man sich eine solche Datenbankstruktur näher ansieht, scheint man auch nicht zufrieden, vor allen Dingen dann nicht, wenn ähnliche (Teil-)SQL-Befehle für die Datenerfassung notwendig sind oder Änderungen an den zehn Feldern (Datentypen oder Namen) an allen betroffenen Objekten gleichzeitig vorgenommen werden müssen, um das gesamte Modell wieder konsistent zu machen. Möglicherweise wird sogar eine Änderung vergessen, sodass die eigentlich gleich strukturierten zehn Felder doch in einigen wenigen Punkten in den einzelnen Objekten fehlerhaft verschieden modelliert sind. Schaut man sich in einem solchen Fall dagegen eine mögliche objektorientierte Softwaremodellierung an, so gibt es eine ganze Reihe von Techniken, um mit 69 Grundlagen solchen gleichartigen (Unter-)Strukturen umzugehen. Man könnte eine Elternklasse oder eine abstrakte Klasse erstellen, welche die gemeinsamen Felder/Eigenschaften besitzt und welche als Elternklasse für die erwähnten Objekte dient. Änderungen in der abstrakten Klasse oder der Elternklasse würden sich dadurch auf die Kindklassen auswirken. Um die Vererbungshierarchie nicht auf Basis einer vermutlich nicht so zentralen Struktur wie den Kontaktdaten aufzubauen (Mehrfachvererbung nicht möglich), kann man stattdessen eine eigene Klasse für die Kontaktfelder entwickeln, welche als Typ in einer/m Eigenschaft/Feld der erwähnten Objekte genutzt wird. So wirken sich Änderungen der Oberstruktur weiterhin in den Klienten aus. Als Ergänzung könnte man sich auch noch eine Schnittstelle denken, welche Methoden anbietet, um Klassen mit Kontakt(unter)feldern zu nutzen und bspw. Adresszeilen oder zusammen gesetzte Namensbestandteile zu liefern. In einer relationalen Datenbank ist dies alles nicht möglich. Weder Vererbung von Tabellenstrukturen noch Datentypen mit mehreren Unterfeldern sind möglich. Erst die objektrelationalen sowie natürlich hierarchischen Techniken bieten hier einen Ansatz an, wobei insbesondere die objektrelationalen oft diskutiert werden. Dies bedeutet nichts Anderes, als dass man in einer nahezu beliebigen .NET-Sprache für den MS SQL Server (andere Datenbanksysteme wie Oracle nutzen hier PL/SQL oder Java) die erwähnte Klasse erstellt und sie mit den benötigten Feldern ausstattet, um die in verschiedenen Objekten verwendeten gleichen Feldstrukturen gut und zentral abzubilden. Eine Alternative stellt dagegen eine zentrale Tabelle dar, welche von allen betroffenen Objekten referenziert wird. Dies ermöglicht es, die gleichartigen Strukturen auszulagern und dadurch zentral ggf. sogar zu verändern. Es erfordert allerdings Verknüpfungen zwischen den Tabellen, um auf diese Strukturen zuzugreifen. Dieses Verfahren wurde in der AdventureWorks-DB verwendet. 1.4.2 Darstellung einzelner Tabellenbereiche Innerhalb der Personaldaten werden die Angestellten in einer Employee-Tabelle gespeichert. Sie arbeiten in einer Abteilung, die in einer Department-Tabelle dargestellt ist. Da ein Angestellter nicht notwendigerweise permanent in der gleichen Abteilung arbeitet, sondern von Zeit zu Zeit auch wechselt, ist keine direkte Beziehung zwischen Department und Employee vorhanden, sondern stattdessen eine Beziehungstabelle EmployeeDepartmentHistory eingefügt, welche insbesondere den Eintritt in eine neue Abteilung und den möglichen 70 Grundlagen Austritt enthält. Darüber hinaus arbeiten die Mitarbeiter in Schichten, wobei die Schichteinteilung in der Shift-Tabelle angegeben ist. Betreten Sie eine Abteilung werden sie ebenfalls zu einer Schicht zugeordnet, sodass in der erwähnten EmployeeDepartmentHistory-Tabelle auch eine Verknüpfung zu dieser ShiftTabelle existiert. Abbildung 1.27: Schema HumanResources Die Kontaktdaten aller Objekte des modellierten Weltausschnitts, die Kontaktdaten aufweisen können, sind zentral in zwei Tabellen gespeichert. Die Contact-Tabelle enthält die allgemeinen persönlichen Daten wie Vorname, Nachname etc. Die Address-Tabelle dagegen enthält Addressinformationen mit 71 Grundlagen Straße, Stadt und PLZ. Für beide Tabellen existieren dann auch noch jeweils zwei Type-Tabelle, d.h. eine Tabelle namens ContactType und eine AddressType-Tabelle, durch die die jeweiligen Datensätze kategorisiert werden können. Länder und allgemeine geografische Bereiche, die außerhalb von konkreten Adressen liegen bzw. die wie Wertelisten angesehen werden können, sind dann in verschiedenen weiteren Tabellen untergebracht. Dies sind solche Tabellen wie CountryRegion oder StateProvince. Abbildung 1.28: Schema Person Die Produkte werden im Schema Production in eine Vielzahl von Tabellen eingeteilt. Davon werden in den Beispielen in diesem Buch nicht alle verwendet, da die Gesamtzahl der Informationen nicht für alle Beispiele didaktisch wertvoll ist, sofern man nicht gerade ein Beispiel benötigt, um zehn Tabellen zu verknüpfen. Die zentrale Tabelle ist Product, in der die wesentlichen Informationen eines Produkts wie Nummer, Bezeichnung, Farbe, Sicherheitsbestand im Lager, Standardkosten und Listenpreis enthalten sind. Für die beiden Attribute Größe und Verkaufseinheit gibt es ausgelagerte Tabellen mit Wertelisten. Kunden bewerten die Produkte in einer ProductReview-Tabelle. Fotos zu den Produkten speichert die Tabelle ProductPhoto in Form eines Pfads. Die histori72 Grundlagen schen Preise werden in einer ProductListPriceHistory gespeichert. Die Kategorien schließlich, die wieder für verschiedene Beispiele sehr hilfreich sind, um Produkte zu kategorisieren und über diese Kategorien Aggregate abzubilden, sind in zwei Tabellen gespeichert. Man unterscheidet zwei Kategorieebenen: zum eine die ProductSubcategory und zum anderen die ProductCategory, wobei die Product-Tabelle zunächst die Unterkategorie verknüpft und diese Unterkategorie wiederum die Oberkategorien. Abbildung 1.29: Schema Production Die Verkaufsinformationen befinden sich im Schema Sales, wobei aus den sehr vielen Tabellen in den Beispielen im Wesentlichen nur die SalesOrderHeaderTabelle benutzt wird, da sie die zusammenfassenden Informationen für einen Verkauf (Kopfdaten) enthält. Sie referenziert in sehr vielen Fremdschlüsselspalten eine große Menge an anderen Tabellen, da sie als typische Buchungstabelle die Geschäftsaktivitäten der Firma abbildet. Zu den Kopfdaten eines Auftrags gehören solche Informationen wie drei verschiedene Zeiten (Bestell-, Auslieferungs-, Fälligkeitsdatum), Preise (Netto, Steuer, Total, Fracht) und die erwähnten Referenzen wie Adressen (Liefer-, Rechnungsadresse), Kunden- und Kontaktinformationen sowie Verkaufsgebiet. 73 Grundlagen Abbildung 1.30: Schema Sales Neben diesen Kopfdaten, die ja bereits umfangreich genug sind, wenn man nur die ganzen Referenzen auflöst, gibt es noch weitere Tabellen in diesem Schema. Jede Bestellung enthält natürlich auch einzelne Positionen, die in SalesOrderDetail gespeichert werden, welche insbesondere eine Referenz zur ProductTabelle enthält. Die Bestelldetails werden in fast allen Beispielen außer Acht gelassen. Ähnliches gilt auch für die verschiedenen Währungen, die man eigentlich für die Bildung von Umsatzaggregaten mit Wechselkursen berücksichtigen müsste. Dazu gibt es die Tabellen CurrencyRate-Tabelle mit den zeitbezogenen Wechselkursdaten und die Currency-Tabelle mit dem Währungsnamen als Werteliste. Die ermittelten Umsatzzahlen, die in den verschiedenen Aggregatbeispielen genutzt werden, müssten also eigentlich noch jeweils in eine gemeinsame Währung umgerechnet werden, was jedoch zu nebensächlichem Zusatzquelltext führt, der das eigentliche Beispiel nur vernebelt. Die einzelnen Bestellungen, sofern Sie nicht über den Webshop eingegangen sind, können einem Verkäufer zugeordnet werden, die Personen darstellen, einem Gebiet zugeordnet sind, Verkaufszahlengeschichte besitzen und Quoten erfüllen müssen. Dieser Bereich wird nur in wenigen Beispielen betrachtet. 74 Grundlagen Abbildung 1.31: Schema Purchasing Die Produkte werden teilweise hergestellt, teilweise aus verschiedenen Bestandteilen montiert und teilweise nur weiterverkauft. Für die verschiedenen Fertigteile, die beschafft werden müssen, existiert ein eigenes Schema namens Purchasing, in dem diese Einkäufe abgebildet werden. Dieses Schema wird in den verschiedenen Beispielen nur wenig genutzt, kann allerdings hervorragend für eigenes Arbeiten auf Basis der Beispiele zum Sales-Schema genutzt werden, da der Einkauf von den Datenstrukturen her ähnlich aufgebaut ist. Die zentrale Tabelle ist hier PurchaseOrderHeader, welche die Kopfdaten einer Bestellung enthält. Die eine Bestellung konstituierenden Posten befinden sich in der PurchaseOrderDetail-Tabelle, welche eine Referenz zur Product-Tabelle enthält, da diese Tabelle sowohl die zu kaufenden als auch die zu verkaufenden Produkte enthält. Die Kopfdaten enthalten wiederum Zeitinformationen (Bestell-, Lieferdatum) sowie Referenzen wie den Angestellten, der die Bestellung ausgelöst hat, und den Verkäufer. 75 Grundlagen Die letzte Abbildung enthält eine Komplettansicht des Datenmodells, welches zwar völlig unleserlich ist, aber kurz zeigen soll, wie umfangreich die Datenstruktur in Wirklichkeit ist. Auch wenn in den Beispielen bereits deutlich mehr Tabellen genutzt werden als bei einem Buch, welches die Nordwind- oder die Pubs-DB einsetzen, so gibt es in Wirklichkeit noch viel mehr interessante Daten und Strukturen zu entdecken. Für Beispiele stehen damit sehr viel mehr Möglichkeiten zur Verfügung als früher, sodass es für uns nur logisch schien, bei einer neu beginnenden Reihe zum MS SQL Server auch die neue, sehr viel bessere Datenbank zu verwenden. Abbildung 1.32: Komplettansicht 76
