Das Erzeugen und F[Please insert PrerenderUnicode{ü} into
Werbung
Das Erzeugen und Füllen einer Oracle-Datenbank Holger Jakobs – [email protected], [email protected] 2008-05-15 Inhaltsverzeichnis 1 Oracle vs. ANSI-SQL 1 2 Erzeugung einer Datenbank 2.1 Benutzer und Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.2 Anlegen von Tabellen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 3 3 3 Anlegen der Sportvereins-Datenbank 3.1 Erzeugen der Tabellen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3.2 Laden der Daten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3.3 Vergeben von Rechten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 3 4 5 4 Sperrstrategie 6 5 Integritäten und ihre Überwachung 5.1 Primärschlüssel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5.2 Fremdschlüssel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 6 6 6 Andere Integritäten 6.1 CHECK-Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6.2 Prüfzeitpunkt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 8 9 Hinweis auf Online-Dokumentation zu Oracle auf den Unix-Rechnern im b.i.b. Bergisch Gladbach: https://www.bg.bib.de/Bibliothek/oracle/index.htm 1 Oracle vs. ANSI-SQL Grundsätzlich sollte die Handhabung von Datenbanken durch den SQL-Standard genormt sein. Allerdings definiert der SQL-Standard nicht alle Kommandos – insbesondere nicht, 1 1 ORACLE VS. ANSI-SQL wie eine neue Datenbank erzeugt wird. Die Erzeugung von Tabellen und deren Abfrage ist weitestgehend genormt. Die einzelnen Datenbanksysteme setzen aber alle nur eine Teilmenge des Standards um. Um sich von den Unterschieden ein Bild machen zu können, setzen wir neben Oracle auch PostgreSQL1 ein, ein Open Source Datenbanksystem. Tatsächlich war es lange Zeit das einzige Open Source Datenbanksystem, das man als ein richtiges Datenbanksystem bezeichnen kann, weil es Fremdschlüssel und Transaktionen (und auch gespeicherte Prozeduren, Trigger, Vererbung usw.) unterstützt. Alternativ könnte man auch Firebird2 verwenden. Bei Oracle werden die Datenbanken nicht als Dateien im normalen Dateisystem unter dem Homedirectory gespeichert (sowas tun nur ganz popelige Systeme), sondern in einem besonders geschützten Bereich. Die Dateien gehören auch keinem normalen Benutzer. Das ist überhaupt kein Problem, da man nur über die vom DBMS (Datenbank-ManagementSystem) definierten Kommandos auf die Daten zugreift und auch nur so zugreifen darf. Der Sicherheit der Daten vor unbefugtem Zugriff wegen musste man diesen Weg wählen. Ähnlich ist es auch bei allen anderen Datenbanksystemen. Es besteht auch die Möglichkeit, die Datenbank außerhalb des Dateisystems auf einem Raw Device“, ” d. h. einer eigenen Partition ohne Dateisystem anzulegen. Das macht das Datenbanksystem zwar hardwareabhängiger und erschwert die Portierung auf andere Betriebssysteme, aber diesen Aufwand kann man bei Oracle durchaus treiben. Für die Performance ist das auf jeden Fall von Vorteil. Für den Zugriff auf OracleDatenbanken benötigen Sie eine Zugangskennung, die bei uns mit dem Windows-/Unix-Login-Benutzernamen übereinstimmt. Das Kennwort dagegen ist eigenständig. An Ihr Oracle-Kennwort kommen Sie durch Eingeben des Kommandos dabpasswd ora in einem Terminalfenster unter Linux. Nach der Ausführung gibt es in Ihrem HOME-Directory eine nur für Sie lesbare Datei namens dabpw_oracle.sql mit nebenstehendem Inhalt: Sie können Ihr Kennwort anschließend ändern mit SERVER = dbserver2 dem Kommando ALTER USER. Durch erneutes Aufrufen USERNAME = bbXXXXXX des o. g. Kommandos können Sie jederzeit wieder das PASSWORD = YYYYYYYY Standard-Kennwort einstellen. DATABASE = ORA10 SERVICE = ORA10.BG.BIB.DE 1) http://www.postgresql.org 2) http://www.firebirdsql.org/ 2 3 ANLEGEN DER SPORTVEREINS-DATENBANK 2 Erzeugung einer Datenbank 2.1 Benutzer und Schemas Wenn Sie sich bei Oracle mit Hilfe des Worksheet oder des Programms TOra anmelden, haben Sie schon Ihren eigenen logischen Datenbereich (Schema), der von den Systembetreuern gemeinsam mit Ihrer Oracle-Benutzerkennung angelegt wurde. Sie können also gleich mit dem Erzeugen von Tabellen loslegen. Da wir nur eine einzige, große Oracle-Datenbank namens ORA10 haben, die für jeden Benutzer ein Schema enthält, besteht leider keine Möglichkeit, explizit weitere Schemas anzulegen, um den eigenen Bereich weiter zu unterteilen, da es keine Subschemas (vergleichbar Unterverzeichnissen) gibt. Jeder Benutzer hat immer ein gleichnamiges Schema, eigenständige Schemas ohne gleichnamigen Benutzer gibt es bei Oracle nicht. Zitat aus der Oracle-Dokumentation: A schema ” is owned by a database user and has the same name as that user.“ Kurz gesagt: Das Erzeugen einer Datenbank oder eines Schemas fällt bei uns flach, weil Sie keine eigene Datenbank haben und weil Sie keine weiteren Benutzer anlegen dürfen. Sie legen also gleich mit dem Erzeugen von Tabellen los. 2.2 Anlegen von Tabellen Erzeugen Sie Tabellen möglichst nicht interaktiv, sondern machen Sie vorher einen ordentlichen Entwurf und schreiben Sie die Erzeugungskommandos in eine Datei. Auf diese Weise können Sie die Datenbank immer wieder auf dieselbe Art erzeugen, falls Sie sie einmal löschen müssen oder Veränderungen zu Übungszwecken wiederholen möchten. Sollte beim Erzeugen der Tabellen etwas schiefgehen, so kann man sie wieder löschen, die Generatordatei modifizieren und erneut beginnen. Dies wiederholt man so lange, bis die Datenbank mit ihren Tabellen den Wünschen entspricht. Darüber hinaus ist die Generatordatei auch ein sehr schönes Protokoll über die Datenbank. Aus manchen Datenbanken kann man immer wieder eine Generatordatei extrahieren – für Oracle gilt dies leider nicht ohne besondere Hilfsmittel (wie zum Beispiel TOra). Die extrahierte Version ist außerdem meist nicht so schön und übersichtlich wie das von Hand Geschriebene und enthält es evtl. nicht portable Syntax-Elemente. 3 Anlegen der Sportvereins-Datenbank Die Generatordateien stehen im Datenbank-Portal zum Download zur Verfügung. 3.1 Erzeugen der Tabellen -- Version fuer Oracle -- http://www.bg.bib.de/portale/dab/Quelltexte/createspieler_ora.sql CREATE TABLE spieler ( 3 3.2 Laden der Daten 3 ANLEGEN DER SPORTVEREINS-DATENBANK spielnr integer primary key, spielname character varying(15) NOT NULL, vorname varchar(15) NOT NULL, titel character(3), gebjahr integer NOT NULL, geschl character constraint geschlecht check (geschl='M' or geschl='W'), eintritt integer NOT NULL, plz character(5) NOT NULL, ort character(20) NOT NULL, verbnr character(4), constraint "nur_Lebende" check (eintritt >= gebjahr) ); CREATE TABLE teams ( teamnr integer primary key, spielnr integer NOT NULL references spieler, liga character(10) NOT NULL ); CREATE TABLE wettkaempfe ( teamnr integer references teams, spielnr integer references spieler, gewonnen integer NOT NULL, verloren integer NOT NULL, primary key (teamnr, spielnr) ); CREATE TABLE strafen ( zahlnr integer primary key, spielnr integer NOT NULL references spieler, datum date default CURRENT_DATE NOT NULL, betrag numeric(10,2) NOT NULL check (betrag > 0.0) ); Nach der Erzeugung sollte man sich davon überzeugen, dass alle Tabellen auch wirklich angelegt worden sind. Eine Übersicht über die existierenden Tabellen bekommt man mit dem Schema Browser, siehe auch Abbildung 1 auf der nächsten Seite. 3.2 Laden der Daten 4 3 ANLEGEN DER SPORTVEREINS-DATENBANK 3.3 Vergeben von Rechten Abbildung 1: Schema Browser von Tora Zum Importieren von Daten aus externen Dateien (ASCII-Dateien mit fester oder variabler Satzlänge) verwendet man das Tool SQL*Loader“, das im Handbuch Database Utitlities“ in der Oracle” ” Dokumentation erläutert ist. Zum Daten-Im- und -Export gibt es auch ein Kapitel im Datenbank-Portal unter Administration“. ” Ohne Verwendung dieses Loaders kann man Daten mittels INSERT-Statements oder über die Datenansicht im Schema-Browser von TOra eingeben, was allerdings nicht sonderlich komfortabel ist. Darüber hinaus kann man sich auch ein kleines Programm oder Script hierfür schreiben. 3.3 Vergeben von Rechten Rechte können an einen einzelnen Benutzer vergeben werden, indem man das GRANT-Kommando verwendet. Es können einem Benutzer Rechte für die diversen Aktionen auf eine Tabellen gegeben werden. Weggenommen werden diese Rechte mit REVOKE. Die Aktionen sind SELECT (Lesen einer Tabelle), UPDATE (Verändern von Tabelleninhalten), INSERT (Einfügen von neuen Tupeln) und DELETE. Hier werden Fritz und Emma die Lese-, Änderungsund Einfügerechte an der Tabelle spieler gegeben. Anschließend werden Fritz alle Rechte an den Tabellen teams und wettkaempfe entzogen. grant select, update on spieler to fritz, emma; revoke all on teams, wettkaempfe from fritz; Natürlich kann man noch viel mehr Rechte vergeben, z. B. überhaupt das Recht, sich an der Datenbank anzumelden, Rechte an einzelnen Spalten, an Sichten (Views) usw. Dies kann in der Oracle-Dokumentation unter dem Stichwort GRANT nachgelesen werden. Es gibt bei Oracle keine Benutzergruppen wie in einige anderen Datenbanken. Im ANSIStandard kommen sie auch nicht vor. Vielmehr gibt es bei Oracle ein Konzept namens ROLE (Rolle). Dieses Rollenkonzept ist deutlich flexibler und leistungsfähiger als das recht einfache Gruppenkonzept, aber auch komplexer in der Handhabung. 5 5 INTEGRITÄTEN UND IHRE ÜBERWACHUNG 4 Sperrstrategie Bei Oracle werden keine Informationen bezüglich Sperrstrategien in der Datenbank gespeichert. Der Default-Isolation-Level ist read committed“ und kann mittels ALTER SES” SION geändert weren. Im SQL-Standard gibt es READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ und SERIALIZABLE. Der Standard schreibt SERIALIZABLE als Default vor (woran sich aber aus Performance-Gründen kaum ein Datenbank-System hält). Das Standardkommando zum Ändern des Isolation Levels ist SET TRANSACTION ISOLATION LEVEL level. 5 Integritäten und ihre Überwachung 5.1 Primärschlüssel-Integrität Die Primärschlüssel-Integrität legt fest, dass in einer Tabelle ein Primärschlüssel-Wert nur ein einziges Mal vorkommen darf. Man kann also kein zweites Tupel mit demselben Primärschlüssel-Wert eintragen, es wird daher von der Datenbank abgelehnt. Bei Oracle ist man nicht verpflichtet, einen Primärschlüssel festzulegen. Legt man keinen fest, können auch doppelte Tupel vorkommen, was gegen das relationale Modell verstößt. Daher sollte man unbedingt einen Primärschlüssel angeben. 5.2 Fremdschlüssel-Integrität Die Fremdschlüssel-Integrität wird über FOREIGN-KEY-Klauseln der Datenbank bekannt gemacht und von dieser überwacht. Intern werden dazu entsprechende Constraints (Beschränkungen) erzeugt. Viele Beziehungsintegritäten können so über FOREIGN-KEY-Klauseln überwacht werden. Komplexere Integritätsregeln sind nicht durch einfache Klauseln darstellbar und müssen auf andere Weise überwacht werden, z. B. durch entsprechende SQL-Prozeduren und Trigger. Grundsätzlich beziehen sich Fremdschlüssel auf den Primärschlüssel der referenzierten Tabelle. In Ausnahmefällen können sie sich aber auch auf andere Spalten beziehen, für die ein eindeutiger Index existiert (entweder durch CREATE INDEX explizit angelegt oder durch eine UNIQUE-Klausel implizit erzeugt). Dann muss man bei der REFERENCES-Klausel neben der referenzierten Tabelle angeben, auf welche Spalte(n) man sich bezieht, z. B. REFERENCES station (sname), wenn man sich auf die Spalte sname in der Tabelle station bezieht. Was passiert nun bei einem Verstoß gegen eine Integritätsbedingung? Grundsätzlich wird die Aktion abgelehnt. Beim Löschen (und teilweise auch beim Aktualisieren) von Tupeln dagegen kann man bei einigen SQL-Systemen, z. B. Oracle und PostgreSQL, aus einer Reihe von Reaktionsweisen auswählen. Die Möglichkeiten sind in Tabelle 1 auf der nächsten Seite dargestellt. Auch beim Einfügen von Datensätzen haben die Restriktionen eine Auswirkung: Das Einfügen eines untergeordneten Tupels ist nur dann erlaubt, wenn das übergeordnete bereits 6 5 INTEGRITÄTEN UND IHRE ÜBERWACHUNG (ohne Zusatz) ON DELETE CASCADE ON DELETE SET NULL ON DELETE SET DEFAULT 5.2 Fremdschlüssel bewirkt, dass das Löschen eines übergeordneten Tupels bei Vorhandensein untergeordneter Tupel nicht erlaubt wird. Solange eine Spielnummer also bei Wettkämpfen, Strafen oder woanders vorkommt, kann der zugehörige Spieler nicht gelöscht werden – es wird ein Fehler gemeldet. Im ANSI-SQL-1992-Standard und bei manchen anderen Datenbanken kann man dies auch explizit setzen mit ON DELETE NO ACTION oder (nahezu gleichbedeutend) ON DELETE NO ACTION bewirkt, dass beim Löschen eines übergeordneten Tupels die zugehörigen abhängigen Tupel mitgelöscht werden. Beispiel: Ein Spieler wird gelöscht. Als Folge werden alle Tupel in allen abhängigen Tabellen mitgelöscht, d. h. seine Spielergebnisse in der WettkampfTabelle, seine Strafen in der Strafen-Tabelle und auch sein Team in der Teams-Tabelle. Letzteres könnte in einer Art Domino-Effekt auch alle Wettkampf-Ergebnisse dieses Teams löschen. bewirkt, dass beim Löschen eines übergeordneten Tupels die Referenz im abhängigen Tupel auf NULL gesetzt wird. Beispiel: Ein Spieler wird gelöscht, der noch in der Tabelle Wettkämpfe vorhanden ist. Als Folge wird in der Tabelle Wettkämpfe die Spielernummer bei den betroffenen Tupeln auf NULL gesetzt. Dieses Attribut muss hierfür natürlich NULL-Werte erlauben. gibt es zwar im ANSI-SQL-1992-Standard, aber nicht bei Oracle. Es bewirkt, dass beim Löschen eines übergeordneten Tupels die Referenz im abhängigen Tupel auf den default-Wert gesetzt wird. Sofern dieser nicht angegeben ist, ist er ohnehin NULL. Tabelle 1: ON DELETE-Möglichkeiten existiert. Man kann also keinen Spieler bestrafen, der nicht als Spieler selbst eingetragen ist. Bei Oracle gibt es – in Abweichung von ANSI-SQL-1992-Standard und im Unterschied zu PostgreSQL – keine ON UPDATE-Klausel. Man kann hier argumentieren, dies sei bei den bei Oracle üblichen großen Datenmengen auch nicht sinnvoll. Beispiel einer Datenbank-Tabellen-Definition in ORACLE oder PostgreSQL: CREATE TABLE TRAINER ( TRAINER_NR SMALLINT NOT NULL, TRAINER_NAME CHAR (20) NOT NULL, PRIMARY KEY (TRAINER_NR)); CREATE TABLE MITARBEITER ( 7 6 ANDERE INTEGRITÄTEN MITARB_NR SMALLINT NOT NULL, MITARB_NAME CHAR (20) NOT NULL, PRIMARY KEY (MITARB_NR)); CREATE TABLE KURS ( KURS_NR SMALLINT NOT NULL, BEGINN_DATUM DATE, TRAINER_NR SMALLINT, PRIMARY KEY (KURS_NR), FOREIGN KEY (TRAINER_NR) REFERENCES TRAINER ON DELETE SET NULL); CREATE TABLE KURSTEILNEHMER ( KURS_NR SMALLINT NOT NULL, MITARB_NR SMALLINT NOT NULL, BEURTEILUNG SMALLINT, PRIMARY KEY (KURS_NR, MITARB_NR), FOREIGN KEY (MITARB_NR) REFERENCES MITARBEITER ON DELETE CASCADE, FOREIGN KEY (KURS_NR) REFERENCES KURS -- keine ON DELETE-Klausel => ON DELETE NO ACTION ); Oracle erlaubt es nicht, explizit die Klausel ON DELETE NO ACTION anzugeben. Wenn man nichts angibt, gilt dies sowieso als default-Wert. 6 Andere Integritätsbedingungen und ihre Überwachung 6.1 CHECK-Constraints Es gibt noch weitere Möglichkeiten, Integritäten automatisch überprüfen zu lassen, ohne gleich zu Prozeduren und Triggern zu greifen: die CHECK-Bedingungen. Sie werden beim Erzeugen von Tabellen mitgegeben oder nachträglich eingebaut (über ALTER TABLE). Sie können einfache Bedingungen enthalten, üblicherweise Vergleiche mit anderen Spaltenwerten aus demselben Tupel (dann als CHECK-Klausel auf Tabellen-, nicht auf Attribut-Ebene) oder mit festen Werten, um den Wertebereich einzugrenzen. Ein Vergleich mit dem aktuellen Datum (CURRENT_DATE) oder anderen CURRENT-Werten ist bei Oracle leider nicht möglich, sondern kann nur über Trigger erreicht werden. Der Standard verlangt dies allerdings auch nicht. Immerhin lässt sich hiermit in der Sportlerdatenbank sicherstellen, dass niemand vor seiner Geburt in den Verein eintritt, dass keine Strafen mit negativen Beträgen eingetragen werden oder dass bei Geschlecht etwas anderes eingegeben wird als ’M’ oder ’W’. CHECK- 8 6 ANDERE INTEGRITÄTEN 6.2 Prüfzeitpunkt Bedingungen können bei der Definition eines Attributs angegeben werden oder als eigenständige Angaben für die Tabelle erfolgen – genau wie die Schlüssel und Referenzangaben auch, Anwendung siehe Abschnitt 3 auf Seite 3. Gemäß Standard lassen sich CHECK-Bedingungen auch nachträglich einfügen, indem man die Tabelle ändert. Hätten wir die Prüfung, dass das Geburtsjahr kleiner oder gleich dem Eintrittsjahr ist, nicht in der Tabellengenerierung drin, könnten wir das nachträglich einbauen mit: ALTER TABLE spieler ADD CONSTRAINT "keine Ungeborenen" CHECK (gebjahr <= eintritt); Bei Oracle kann man Tabellen jederzeit umbenennen (ALTER TABLE Tabellenname RENAME TO neuerTabellenname . Attribute kann man mittlerweile auch umbenennen mit ALTER TABLE Tabellenname RENAME COLUMN Spaltenname TO neuer-Spaltenname , nicht nur hinzufügen: ALTER TABLE Tabellenname ADD (Spaltenname Typ ). Diese Syntax weicht etwas von der Syntax im Standard ab: ALTER TABLE Tabellenname ADD COLUMN Spaltenname Typ . Dafür ist es bei Oracle (über den Standard hinaus) auch möglich, vorhandene Attribute wieder zu löschen: (ALTER TABLE Tabellenname DROP (Spaltenname ) Beim Eintragen von Constraints werden auch bestehende Tupel auf die Einhaltung der Bedingung geprüft. Sollten sie der Bedingung widersprechen, so können sie nicht eingetragen werden. Löschen kann man alle Arten von Constraints mit ALTER TABLE Tabellenname DROP CONSTRAINT constraintname . Hierzu muss man den Namen des Constraints kennen, wozu es praktisch ist, diese in TOra leicht nachschauen zu können. Allerdings ist es auch eine gute Idee, alle Contraints mit einem Namen zu versehen, weil dieser Name beim Verletzten des Constraints in der Fehlermeldung erscheint und dem Anwender einen Hinweis geben kann. Probieren Sie die CHECK-Bedingungen aus, indem Sie in Ihre Datenbank(en) welche eingeben, sich die veränderte Anzeige in TOra anschauen und sowohl manuell als auch mit Hilfe von Programmen (später, siehe Kapitel Embedded SQL“ und JDBC“) und über den ” ” SQL-Loader Daten eingeben bzw. verändern. Geben Sie Daten ein, die den Constraints nicht genügen und notieren Sie sich, wie die Datenbank auf falsche“ Daten reagiert. ” 6.2 Prüfzeitpunkt von Constraints Bei Constraints kann man wahlweise angeben, ob diese immer sofort, d. h. nach jedem Kommando, oder aber erst am Ende der Transaktion geprüft werden. Dieses Verzögern bis zum Transaktionsende kann man auch nachträglich für eine einzelne Transaktion einstellen, sofern es nicht verboten wurde. Ohne Angaben werden Constraints nach jedem Kommando geprüft, aber die Prüfung kann verzögert werden. Man kann die Einstellungen verändern, indem man am Ende der Constraint-Klausel zusätzlich eine oder beide der folgenden Klauseln angibt: [ NOT ] DEFERRABLE INITIALLY { IMMEDIATE | DEFERRED } 9 6.2 Prüfzeitpunkt 6 ANDERE INTEGRITÄTEN Mit dem Verzögern von Constraints kann man erreichen, dass man voneinander wechselseitig abhängige Tupel überhaupt eintragen kann. Anwendungsbeispiel: Schreibt man für ausnahmslos jeden Mitarbeiter einen von ihm selbst verschiedenen Vertreter vor, wird man kein Tupel in die leere Tabelle eintragen können, weil man vorher schon ein anderes Tupel benötigt, auf das man verweist – den Vertreter. Mit Hilfe der Verzögerung des zugehörigen Constraints wird es möglich, Einträge vorzunehmen. Es muss lediglich sichergestellt werden, dass vor Ende der Transaktion jedes Tupel auf ein anderes verweist. $Id: erzeugen_fuellen_ora.tex,v 1.5 2008-05-15 10:56:00 hj Exp $ 10
