U01_CreateTables
Werbung
Modul 141 – Übung Tabellen erstellen M141 - Übungen und Praktikum UEB-01 - 1 Übung 01 – Tabellen erstellen Die folgende Musterrechnung dokumentiert eine Miniwelt "Rechnung" in einer Firma. 1. Welche Objekte und Beziehungen lassen sich daraus ableiten? 2. Erstellen Sie ein Datenmodell (ERD) mit allen benötigten Entitäten und Attributen 3. Implementieren Sie das physische Modell in einer Datenbank DBBW001. Die Tabellen sollen im Schema M141 erstellt werden. Hinweis: Die ANSI-SPARC-Architektur, auch Drei-Ebenen-Architektur genannt, beschreibt den grundlegenden Aufbau eines relationalen Datenbanksystems, wobei drei Schemata verwendet werden: 1. Die externen Schemata, die formal beschreiben, wie sich die Datenbank den Benutzern und Anwendungen darstellt (individuelle anwendungsorientierte Sicht). 2. Das konzeptionelle oder konzeptuelle Schema, in dem auf Basis des semantischen Datenmodells die Sachlogik formal beschrieben wird (fachliche Sicht). 3. Das interne Schema, das formal darstellt, wie und wo die Daten in der Datenbank gespeichert werden (technische Sicht). Auf der technischen Ebene können Tabellen in verschiedenen Schemata definiert werden. Das Schema wird dabei als Erweiterung des Tabellennamens verwendet, d.h. eine Tabelle PERSON kann in einer Datenbank in verschiedenen Schematas existieren (z.B. In Schema ABC und Schema XYZ). Die einzelnen Entitäten (Tabellen) werden dabei via Schema und Tabellenname referenziert. Beispiel: SELECT * FROM ABC.PERSON SELECT * FROM XYZ.PERSON oder Welche Objekte und Beziehungen lassen sich daraus ableiten? Modul 141 – Datenbanken - M141-Uebung01-CreateTables.odt © 2015 - B. Hofstetter Modul 141 – Übung Tabellen erstellen UEB-01 - 2 M141 - Übungen und Praktikum Lösungsvorschlag Welche Objekte und Beziehungen lassen sich daraus ableiten? Basierend auf der Musterrechnung lassen sich die folgenden vier Objekte (Entitäten) identifizieren: Daraus ergibt sich das folgende einfache logische Datenmodel: • • • Entität Kunde enthält die Kundendaten Entität Artikel enthält die Artikeldaten Entität Rechnung enthält die Rechnungsdaten • Zwischen den Entitäten Artikel und Rechnung existiert eine many-to-many Beziehung (ein Artikel kann in mehreren Rechnungen enthalten sein und eine Rechnung kann mehrere Artikel enthalten). Da n:m Beziehungen physisch nicht nicht implementiert werden können, wird diese Beziehung später via Beziehungsentität Rechnungsposition.implementiert. Modul 141 – Datenbanken - M141-Uebung01-CreateTables.odt © 2015 - B. Hofstetter Modul 141 – Übung Tabellen erstellen UEB-01 - 3 M141 - Übungen und Praktikum Datenmodell mit allen benötigten Entitäten: In diesem Datenmodel wurde die Beziehungsentität Rechnungsposition eingefügt. Beachten Sie dabei, dass die Beziehungen zu den Entitäten Rechnung resp. Artikel als „nicht identifizierend“ definiert sind: • Die Primärschlüssel der Parent-Entitäten (Artikel resp. Rechnung) werden dabei lediglich als Fremdschlüssel übernommen, sind jedoch nicht Bestandteil des Primärschlüssels der Entität Rechnungsposition. • Die beiden Fremdschlüssel (ArtikelID + RechnungID) bilden zusammen ebenfalls eine eindeutige Identifikation eines einzelnen Datensatzes, sie bilden zusammen einen Alternativschlüssel (potentiellen Primärschlüssel). Zu beachten sind auch die Kardinalitäten der Beziehungen: • • ein Kunde hat 0 oder mehrere Rechnungen (Kardinalität: mc) eine Rechnung gehört genau einem Kunden (Kardinalität: 1) • • eine Rechnung enthält 1 oder mehrere Rechnungspositionen (Kardinalität: m) eine Rechnungsposition gehört zu einer Rechnung (Kardinalität: 1) • • ein Artikel ist in 0 oder mehreren Rechnungspositionen aufgeführt (Kardinalität: mc) eine Rechnungsposition enthält einen Artikel (Kardinalität: 1) Modul 141 – Datenbanken - M141-Uebung01-CreateTables.odt © 2015 - B. Hofstetter Modul 141 – Übung Tabellen erstellen M141 - Übungen und Praktikum UEB-01 - 4 Erweitert man das Model mit den Attributen, so ergibt sich folgende Darstellung: Zusätzlich zu den normalen Datenattributen aus den Anforderungen wurde in jede Entität ein Attribut LastUpdate aufgenommen. Mittels diesem Attribut soll festgehalten werden WANN ein Datensatz letztmals geändert wurde. Modul 141 – Datenbanken - M141-Uebung01-CreateTables.odt © 2015 - B. Hofstetter Modul 141 – Übung Tabellen erstellen M141 - Übungen und Praktikum UEB-01 - 5 Im physischen Datenmodell werden schliesslich alle Attribute (Spalten) und Indizes definiert, die für das Erstellen der Tabellen benötigt werden. Das folgende Diagramm zeigt eine mögliche Implementierung des physischen Datenmodells inkl. Indizes): Im Diagramm sind jetzt auch die zusätzlich definierten Indizes enthalten. Zu beachten gilt dabei: • Ein Index kann verwendet werden um nur eindeutige Werte (Unique) für eine Spalte zuzulassen. Für alle Primärschlüssel muss somit immer ein eindeutiger Index (Unique Index) definiert werden (die benötigten Indizes für die Primärschlüssel werden vom DBMS in der Regel automatisch erstellt) • Für Alternate-Keys (Schlüsselkandidaten oder fachliche Schlüssel) müssen die eindeutigen Indizes selber definiert werden. In unserem Beispiel existieren die fachlichen Schlüssel KUNDENNR, ARTIKELNR und RECHNUNGSNR, die vom Benutzer resp. den Anwendungen vergeben werden. Die Werte in diesen Spalten müssen ebenfalls eindeutig sein (analog den technischen Primärschlüsseln). Für diese Spalten muss jeweils ein eindeutiger Index definiert werden, damit das DBMS garantieren kann, dass ein fachlicher Schlüssel nur einmal vergeben wird. • Ein Index kann zudem die Zugriffsgeschwindigkeit beim Lesen der Tabellen steigern. Entsprechend sollten für häufig benötigte Zugriffe Indizes über die entsprechenden Spalten gelegt werden. Jeder Index muss jedoch bei einer Mutation (INSERT, UPDATE, DELETE) vom DBMS nachgeführt werden, d.h. Indizes sollen nur für die wichtigsten Spalten definiert werden. Beispiel: Suche über den Namen eines Kunden => Index über Spalte NAME + VORNAME Suche eines Artikels über die Bezeichnung ==> Index über Spalte BEZEICHNUNG • Für alle Fremdschlüssel sollten immer Indizes definiert werden, da dadurch die Join-Operationen wesentlich beschleunigt werden. Modul 141 – Datenbanken - M141-Uebung01-CreateTables.odt © 2015 - B. Hofstetter Modul 141 – Übung Tabellen erstellen UEB-01 - 6 M141 - Übungen und Praktikum Beispiel für das SQL Statement CREATE TABLE (erstellen einer Tabelle) Erstellen der Tabelle mit implizitem Schema (die Tabelle wird im Default-Schema erstellt): CREATE TABLE KUNDE ( KUNDEID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 20) , KUNDENNR ANREDE VORNAME NAME STRASSE POSTLEITZAHL ORT LAND LASTUPDATE CHAR(10) VARCHAR(25), VARCHAR(50), VARCHAR(50) VARCHAR(50), VARCHAR(20), VARCHAR(50), VARCHAR(50), TIMESTAMP NOT NULL, NOT NULL, NOT NULL WITH DEFAULT ) IN INDEX IN M141_TABLESPACE M141_INDEXSPACE ; ALTER TABLE KUNDE ADD CONSTRAINT PK_KUNDE PRIMARY KEY (KUNDEID); Erstellen der Tabelle mit explizitem Schema (M141) CREATE TABLE M141.KUNDE ( KUNDEID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 20), KUNDENNR ANREDE VORNAME NAME STRASSE POSTLEITZAHL ORT LAND LASTUPDATE CHAR(10) VARCHAR(25), VARCHAR(50), VARCHAR(50) VARCHAR(50), VARCHAR(20), VARCHAR(50), VARCHAR(50), TIMESTAMP NOT NULL, NOT NULL, NOT NULL WITH DEFAULT ) IN INDEX IN M141_TABLESPACE M141_INDEXSPACE ; ALTER TABLE M141.KUNDE ADD CONSTRAINT PK_KUNDE PRIMARY KEY (KUNDEID); Modul 141 – Datenbanken - M141-Uebung01-CreateTables.odt © 2015 - B. Hofstetter Modul 141 – Übung Tabellen erstellen M141 - Übungen und Praktikum UEB-01 - 7 Das folgende SQL-Script erstellt die Tabellen, Indizes und Beziehungen, im Schema M141. Zu beachten ist dabei, dass im SQL-Script der Name der Datenbank nicht definiert ist, damit das gleiche Script ohne Modifikation für beliebige Datenbanken (z.B. Entwicklung, Test, Produktion) verwendet werden kann. Damit das SQL-Script ausgeführt werden kann, müssen die beiden folgenden Voraussetzungen erfüllt sein: • die Datenbank in der die Objekte erstellt werden sollen muss existieren. Das CREATE DATABASE Statement ist also nicht Bestandteil des SQL-Scripts! • eine Connection (Verbindung) zur entsprechenden Datenbank muss existieren. Die UserID, die für das Erstellen der Objekte verwendet wird muss die entsprechenden Autorisierungen haben, damit sie die Objekte (Tabellen, Indizes, Beziehungen, etc.) auch erstellen kann. In der Regel werden die Objekte mit einer ID erstellt, die das Privileg „Datenbank-Administrator“ hat. Beachten Sie, ein Objekt kann nur einmal erstellt werden, d.h. existiert ein Objekt bereits,so wird ein CREATE Statement immer eine Fehler-Meldung ausgeben (Object xyz already exists). D.h. Sie müssen die Objekte mittels dem Command DROP zuerst löschen, wenn das Objekt bereits existiert. Das folgende Beispiel zeigt eine SQL-Scripts für das Erstellen der vier Tabellen. Beachten Sie dabei, dass die verwendeten Tablespaces nicht in diesem SQL-Script erstellt werden, d.h. sie müssen bereits existieren Hinweis: In SQL wird ein Kommentar mit -- (zwei Minuszeichen) gekennzeichnet, d.h. Sie können in einem SQL-Script beliebigen Kommentar einfügen um das Script zu dokumentieren. Modul 141 – Datenbanken - M141-Uebung01-CreateTables.odt © 2015 - B. Hofstetter Modul 141 – Übung Tabellen erstellen M141 - Übungen und Praktikum UEB-01 - 8 Script: U01_DropObjects.sql --<ScriptOptions statementTerminator=";"/> -- Setzen des Schemas M141. Damit werden alle Objekte automatisch im Schema M141 gelöscht oder erstellt -- und nicht im Schema das Benutzers der das SQL-Script ausführt. SET SCHEMA M141; -- Löschen aller Constraints, d.h. Beziehungen und Definitionen von Schlüsseln. -- Zu Beachten gilt dabei die Reihenfolge, d.h. Zuerst müssen die Beziehungen gelöscht werden und erst -- nachdem die Beziehungen gelöscht sind, können die Definitionen der Primärschlüssel gelöscht werden. ALTER TABLE RECHNUNG DROP CONSTRAINT FK_RECHNUNG_KUNDE; ALTER TABLE RECHNUNGSPOSITION DROP CONSTRAINT FK_RECHNUNGSPOSITION_ARTIKEL; ALTER TABLE RECHNUNGSPOSITION DROP CONSTRAINT FK_RECHNUNGSPOSITION_RECHNUNG; ALTER TABLE KUNDE DROP CONSTRAINT PK_TKUNDE; ALTER TABLE RECHNUNG DROP CONSTRAINT PK_RECHNUNG; ALTER TABLE ARTIKEL DROP CONSTRAINT PK_ARTIKEL; ALTER TABLE RECHNUNGSPOSITION DROP CONSTRAINT PK_RECHNUNGSPOSITION; -- Löschen der Indizes DROP INDEX IDX_KUNDE_NAME; DROP INDEX IDX_KUNDE_KUNDENNR; DROP INDEX IDX_RECHNUNG_KUNDEID; DROP INDEX IDX_RECHNUNG_RECHNUNGSNR; DROP INDEX IDX_ARTIKEL_BEZEICHNUNG; DROP INDEX IDX_ARTIKEL_ARTIKELNR; DROP INDEX IDX_RECHNUNGSPOSITION; DROP INDEX IDX_RECHNUNGSPOSITION_RECHNUNGID_POSNR; -- Löschen der Tabellen DROP TABLE KUNDE; DROP TABLE RECHNUNG; DROP TABLE ARTIKEL; DROP TABLE RECHNUNGSPOSITION; -- Offene UOW (Unit Of Work) persistent in DB schreiben COMMIT WORK; Modul 141 – Datenbanken - M141-Uebung01-CreateTables.odt © 2015 - B. Hofstetter Modul 141 – Übung Tabellen erstellen M141 - Übungen und Praktikum UEB-01 - 9 Script: U01_CreateObjects.sql -- Erstellen der Tabellen mit den Spalten. Beachten Sie dabei, dass die Tabellen ohne Primärschlüssel definierte -- werden. Die Primärschlüssel und Beziehungen werden erst zu einem späteren Zeitpunkt hinzugefügt. -- Die Tabellen werden im Tablespace M141_TABLESPACE erstellt -- Die Indizes der Tabellen werden im Tablespace M141_INDEXSPACE erstellt CREATE TABLE KUNDE ( KUNDEID KUNDENNR ANREDE VORNAME NAME STRASSE POSTLEITZAHL ORT LAND LASTUPDATE INTEGER NOT NULL CHAR(10) VARCHAR(25), VARCHAR(50), VARCHAR(50) VARCHAR(50), VARCHAR(20), VARCHAR(50), VARCHAR(50), TIMESTAMP GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 20), NOT NULL, NOT NULL, NOT NULL WITH DEFAULT ) IN M141_TABLESPACE INDEX IN M141_INDEXSPACE; CREATE TABLE RECHNUNG ( RECHNUNGID KUNDEID RECHNUNGSNR RECHNUNGSDATUM RECHNUNGSBETRAG LASTUPDATE INTEGER NOT NULL INTEGER CHAR(10) DATE DECIMAL(10 , 2) TIMESTAMP GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 20), NOT NULL, NOT NULL, NOT NULL, NOT NULL, NOT NULL WITH DEFAULT ) IN M141_TABLESPACE INDEX IN M141_INDEXSPACE; CREATE TABLE ARTIKEL ( ARTIKELID ARTIKELNR BEZEICHNUNG EINZELPREIS LASTUPDATE INTEGER NOT NULL CHAR(10) VARCHAR(100) DECIMAL(10 , 2) TIMESTAMP GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 20), NOT NULL, NOT NULL, NOT NULL, NOT NULL WITH DEFAULT ) IN M141_TABLESPACE INDEX IN M141_INDEXSPACE; CREATE TABLE RECHNUNGSPOSITION ( RGPOSID INTEGER NOT NULL ARTIKELID RECHNUNGID POSNR ANZAHL GESAMTPREIS LASTUPDATE INTEGER INTEGER INTEGER INTEGER DECIMAL(10 , 2) TIMESTAMP GENERATED BY DEFAULT AS IDENTITY( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 20), NOT NULL, NOT NULL, NOT NULL, NOT NULL, NOT NULL, NOT NULL WITH DEFAULT ) IN M141_TABLESPACE INDEX IN M141_INDEXSPACE; Modul 141 – Datenbanken - M141-Uebung01-CreateTables.odt © 2015 - B. Hofstetter Modul 141 – Übung Tabellen erstellen M141 - Übungen und Praktikum UEB-01 - 10 --- Erstellen der Indizes für alle Tabellen. -CREATE INDEX IDX_KUNDE_NAME ON KUNDE ( NAME ASC, VORNAME ASC ) PCTFREE 0 ALLOW REVERSE SCANS; CREATE UNIQUE INDEX IDX_KUNDE_KUNDENNR KUNDENNR ASC ) PCTFREE 0 ALLOW REVERSE SCANS; ON KUNDE ( CREATE INDEX IDX_RECHNUNG_KUNDEID ON RECHNUNG ( KUNDEID ASC ) PCTFREE 0 ALLOW REVERSE SCANS; CREATE UNIQUE INDEX IDX_RECHNUNG_RECHNUNGSNR ON RECHNUNG (RECHNUNGSNR ASC) PCTFREE 0 ALLOW REVERSE SCANS; CREATE INDEX IDX_ARTIKEL_BEZEICHNUNG ON ARTIKEL (BEZEICHNUNG ASC) PCTFREE 0 ALLOW REVERSE SCANS; CREATE UNIQUE INDEX IDX_ARTIKEL_ARTIKELNR ON ARTIKEL (ARTIKELNR ASC) PCTFREE 0 ALLOW REVERSE SCANS; CREATE UNIQUE INDEX IDX_RECHNUNGSPOSITION ON RECHNUNGSPOSITION (ARTIKELID ASC, RECHNUNGID ASC) PCTFREE 0 ALLOW REVERSE SCANS; CREATE UNIQUE INDEX IDX_RECHNUNGSPOSITION_RECHNUNGID_POSNR ON RECHNUNGSPOSITION (RECHNUNGID ASC, POSNR ASC) PCTFREE 0 ALLOW REVERSE SCANS; Modul 141 – Datenbanken - M141-Uebung01-CreateTables.odt © 2015 - B. Hofstetter Modul 141 – Übung Tabellen erstellen M141 - Übungen und Praktikum UEB-01 - 11 -- Nachdem die Tabellen und Indizes erstellt sind, werden die Constraints hinzugefügt, d.h. es werden die -- Primärschlüssel der einzelnen Tabellen definiert und die Beziehungen zwischen den einzelnen Tabellen -- In einem ersten Schritt werden die Primärschlüssel definiert. Das DBMS generiert automatisch einen UNIQUE -- Index über die entsprechenden Spalten, sofern noch passender Index (UNIQE INDEX) existiert. ALTER TABLE KUNDE ADD CONSTRAINT PK_TKUNDE PRIMARY KEY (KUNDEID); ALTER TABLE RECHNUNG ADD CONSTRAINT PK_RECHNUNG PRIMARY KEY (RECHNUNGID); ALTER TABLE ARTIKEL ADD CONSTRAINT PK_ARTIKEL PRIMARY KEY (ARTIKELID); ALTER TABLE RECHNUNGSPOSITION ADD CONSTRAINT PK_RECHNUNGSPOSITION PRIMARY KEY (RGPOSID); -- In einem zweiten Schritt werden die Beziehungen zwischen den einzelnen Tabellen definiert, d.h. es werden -- die Fremdschlüssel festgelegt und was das DBMS zur Laufzeit validieren muss. -- RESTRICT bedeutet dabei, dass keine Schlüsselverletzungen zugelassen sind. ALTER TABLE RECHNUNG ADD CONSTRAINT FK_RECHNUNG_KUNDE FOREIGN KEY (KUNDEID) REFERENCES KUNDE (KUNDEID) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE RECHNUNGSPOSITION ADD CONSTRAINT FK_RECHNUNGSPOSITION_ARTIKEL FOREIGN KEY (ARTIKELID) REFERENCES ARTIKEL (ARTIKELID) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE RECHNUNGSPOSITION ADD CONSTRAINT FK_RECHNUNGSPOSITION_RECHNUNG FOREIGN KEY (RECHNUNGID) REFERENCES RECHNUNG (RECHNUNGID) ON DELETE RESTRICT ON UPDATE RESTRICT; -- Mit COMMIT werden alle pendenten Änderungen (UOW) persistent in die Datenbank geschrieben, COMMIT WORK; --- Ende des Scripts -- Modul 141 – Datenbanken - M141-Uebung01-CreateTables.odt © 2015 - B. Hofstetter Modul 141 – Übung Tabellen erstellen UEB-01 - 12 M141 - Übungen und Praktikum Aufgabe: Erstellen Sie diese Objekte (Tabllen, Indizes, etc.) in den Datenbanken DBBW001, DBBW002, DBBW003 und DBBW004 in der Instanz (Database Manager) db2inst1. Die Objekte sollen mit dem Instanz-User erstellt werden: Instanz-User: _______________________ Sie können dafür die vorbereiteten SQL Scripts U01_DropObjects.sql und U01_CreateObjects.sql verwenden. Kopieren Sie dazu diese Scripts vom Klassenlaufwerk in Ihre VM (Verzeichnis: /bbw). Beachten Sie, dass diese SQL-Scripts allenfalls noch Fehler enthalten und für die Übung noch korrigiert werden müssen! Alle Tabellen sollen im Tablespace M141_TABLESPACE und die Indizes im Tablespace M141_INDEXSPACE erstellt werden. Kontrollieren Sie via Aqua Data Studio, Squirrel oder einem anderen SQL Client ob diese Objekte existieren. Falls nein sollen diese Objekte erstellt werden (Syntax finden Sie in der Dokumentation). Die Scripts sollen via CLP (Command Line Interpreter ausgeführt werden. Überlegen Sie sich genau, was Sie bei der Ausführung erwarten (z.B. Wann soll COMMIT gemacht werden, was soll bei einem Syntax-Fehler im Script passieren, etc.). Suchen Sie anschliessend in der Dokumentation ob Sie das gewünschte Verhalten via CommandLine Options beim Aufruf des CLP's einstellen können. Ziel dieser Übung ist es: • Die Objekte existieren in allen vier Datenbanken • Sie können SQL Scripts mittels CLP ausführen und wissen was vorher gemacht werden muss • Sie können Fehler-Meldungen des CLP's interpretieren und wissen wo Sie Hilfe finden resp. wissen wo Sie die entsprechenden Dokumentationen abgelegt haben • Sie können einfache Fehler in einem SQL Script lokalisieren und korrigieren • Sie wissen wie Sie das Resultat eines erfolgreich ausgeführten SQL-Scripts in der Datenbank kontrollieren können • Sie machen sich Ihre eigenen Notizen (können an den Prüfungen verwendet werden) Modul 141 – Datenbanken - M141-Uebung01-CreateTables.odt © 2015 - B. Hofstetter
