CREATE TABLE Assistenten
Werbung
Kapitel 5 Dr. Brigitte Mathiak SQL DDL + DML Lernziele Fähigkeit zur praktischen Anwendung von SQL-Anweisungen in den Bereichen: Schema-Definitionen einschl. referentieller Integrität Einfügen von Daten Ändern von Daten Löschen von Daten Datenbanken für Mathematiker, WS 11/12 Kapitel 5: SQL2 2 SQL: Komponenten der vollständigen DB-Sprache Datenmanipulation (Data Manipulation Language DML) Einfügen, Löschen und Ändern von individuellen Tupeln In und von Mengen von Tupeln Zuweisung von ganzen Relationen diesem Kapitel Datendefinition (Data Definition Language DDL) Definition von Wertebereichen, Attributen und Relationen In Definition von verschiedenen Sichten auf Relationen diesem Kapitel Datenkontrolle Spezifikation von Bedingungen zur Zugriffskontrolle Spezifikation von Zusicherungen (assertions) zur semantischen Integritätskontrolle Datenabfragen Abbildung von Eingaberelationen durch Auswertung von Bedingungen Das war Kapitel auf die Ergebnisrelation 2 Kopplung mit einer Wirtssprache (z.B. C++) deskriptive Auswahl von Mengen von Tupeln sukzessive Bereitstellung einzelner Tupeln Datenbanken für Mathematiker, WS 11/12 Kapitel 5: SQL2 3 Beispiel: Universitätsschema * Studenten MatrNr Name Semester hören * * voraussetzen Vorlesungen * VorlNr Titel SWS * * lesen Prüfen Note 1 1 Professoren PersNr Name Rang Raum Datenbanken für Mathematiker, WS 11/12 Kapitel 5: SQL2 Assistenten arbeitenFür 1 * PersNr Name Fachgebiet 4 Studenten Professoren Rang Raum MatrNr Vorlesungen Name Semester 24002 Xenokrates 18 232 25403 Jonas 2127 Kopernikus C3 310 26120 2133 C3 52 26830 2134 Augustinus C3 309 2136 Curie C4 36 2137 Kant C4 7 PersNr Name 2125 Sokrates C4 226 2126 Russel C4 Popper voraussetzen Vorgänger Nachfolger VorlNr Titel 12 5001 Grundzüge 4 2137 Fichte 10 5041 Ethik 4 2125 Aristoxenos 8 5043 Erkenntnistheorie 3 2126 27550 Schopenhaue r 6 5049 Mäeutik 2 2125 4052 Logik 4 2125 28106 3 5052 Wissenschaftstheori e 3 2126 5216 Bioethik 2 2126 5259 Der Wiener Kreis 2 2133 5022 Glaube und Wissen 2 2134 4630 Die 3 Kritiken 4 2137 Carnap 29120 Theophrastos 2 29555 2 Feuerbach hören 5001 5041 MatrNr VorlNr 5001 5043 26120 5001 5001 5049 27550 5001 5041 5216 27550 4052 5043 5052 28106 5041 5041 5052 28106 5052 5052 5259 28106 5216 prüfen 29120 5001 MatrNr VorlNr PersNr Note 29120 5041 28106 5001 2126 1 29120 5049 25403 5041 2125 2 25403 5022 Datenbanken für Mathematiker, WS 11/12 Kapitel 5: SQL2 SWS gelesenV on Assistenten PerslNr Name Fachgebiet Boss 3002 Platon Ideenlehre 2125 3003 Aristoteles Syllogistik 2125 3004 Wittgenstein Sprachtheorie 2126 3005 Rhetikus Planetenbewegung 2127 3006 Newton Keplersche Gesetze 2127 3007 Spinoza Gott und Natur 2126 5 Relationale Invarianten Integritätsbedingungen des Relationenmodells Primärschlüsselbedingung (Entity-Integrität) - Eindeutigkeit des Primärschlüssels - keine Nullwerte! Referentielle Integrität: - Darstellung von Beziehungen durch Fremdschlüssel (foreign key): Attribut, das in Bezug auf den Primärschlüssel einer anderen (oder derselben) Relation definiert ist - zugehöriger Primärschlüssel muss existieren, d.h. zu jedem Wert (ungleich Null) eines Fremdschlüsselattributs einer Relation R2 muss ein gleicher Wert des Primärschlüssels in irgendeinem Tupel von Relation R1 vorhanden sein Datenbanken für Mathematiker, WS 11/12 Kapitel 5: SQL2 6 Relationale Invarianten (1) Fremdschlüssel und zugehöriger Primärschlüssel tragen wichtige interrelationale Informationen sie sind auf dem gleichen Wertebereich definiert sie gestatten die Verknüpfung von Relationen mit Hilfe von Relationenoperationen Fremdschlüssel können Nullwerte aufweisen, wenn sie nicht Teil eines Primärschlüssels sind. ein Fremdschlüssel ist „zusammengesetzt“, wenn der zugehörige Primärschlüssel „zusammengesetzt“ ist Eine Relation kann mehrere Fremdschlüssel besitzen, die die gleiche oder verschiedene Relationen referenzieren Zyklen sind möglich (geschlossener referentieller Pfad) Eine Relation kann zugleich referenzierende und referenzierte Relation sein („self-referencing table“). Datenbanken für Mathematiker, WS 11/12 Kapitel 5: SQL2 7 SQL: DDL Anweisungen DDL-Spezifikation in SQL bei CREATE TABLE: Professoren Assistenten PersNr Name Fachgebiet Boss PersNr Name Rang Raum 3002 Platon Ideenlehre 2125 2125 Sokrates C4 226 3003 Aristoteles Syllogistik 2125 2126 Russel C4 232 3004 Wittgenstein Sprachtheorie 2126 2127 Kopernikus C3 310 3005 Rhetikus Planetenbewegung 2127 2133 Popper C3 52 3006 Newton Keplersche Gesetze 2127 2134 Augustinus C3 309 3007 Spinoza Gott und Natur 2126 2136 Curie C4 36 2137 Kant C4 7 CREATE TABLE Assistenten (PersNr INT PRIMARY KEY, Name VARCHAR2 (100) NOT NULL, Fachgebiet VARCHAR2(100), Boss INT REFERENCES Professoren(PersNr)); Datenbanken für Mathematiker, WS 11/12 Kapitel 5: SQL2 8 (Einfache) Datendefinition in SQL CREATE TABLE <tablename> ( <columnname> <Datentyp> <Constraint>, <columnname2> …, … ) Wichtige Datentypen in Oracle: varchar2 (n) für Strings variabler Länge NUMBER(p,s) für Zahlen mit der angegebenen Genauigkeit PLS_INTEGER für Integerwert (bessere Performanz) blob oder raw für sehr große binäre Daten clob für sehr große String-Attribute date für Datumsangaben xml für XML-Dokumente … Datenbanken für Mathematiker, WS 11/12 Kapitel 5: SQL2 9 Constraints Mehr zu Constraints im Kapitel Integritätsbedingungen hier nur kurz die wichtigsten: • PRIMARY KEY für den Primärschlüssel • REFERENCES rel(col) für einen Fremdschlüssel, der auf die Relation rel und die Spalte col zeigt • UNIQUE wenn es keine Duplikate in der Spalte geben darf (PRIMARY KEY ist automatisch UNIQUE) • NOT NULL wenn Nullwerte nicht zugelassen sind Datenbanken für Mathematiker, WS 11/12 Kapitel 5: SQL2 10 Schemaänderungen ALTER TABLE <table_name> RENAME TO <new_table_name>; Beispiel: ALTER TABLE suppliers RENAME TO vendors; Statt RENAME TO gibt es auch eine Reihe anderer Änderungsmöglichkeiten z.B. • ADD <column> <Datentyp> <Constraint> • MODIFY <column> <neuer_Datentyp> <neues_Constraint> • DROP <column> • RENAME COLUMN <column> TO <neuer_Name> Datenbanken für Mathematiker, WS 11/12 Kapitel 5: SQL2 11 Veränderung am Datenbestand Einfügen von Tupeln: MatrNr Studenten Name Semester 29120 29555 28121 Theophrastos Feuerbach Archimedes 2 2 - insert into Studenten (MatrNr, Name) values (28121, 'Archimedes'); insert into hören select MatrNr, VorlNr Null-Wert from Studenten, Vorlesungen where Titel= 'Logik' ; Datenbanken für Mathematiker, WS 11/12 Kapitel 5: SQL2 12 Veränderungen am Datenbestand Löschen von Tupeln delete Studenten where Semester > 13; Verändern von Tupeln update Studenten set Semester = Semester + 1; Datenbanken für Mathematiker, WS 11/12 Kapitel 5: SQL2 13 Assistenten Professoren PersNr Name Fachgebiet Boss PersNr Name Rang Raum 3002 Platon Ideenlehre 2125 2125 Sokrates C4 226 3003 Aristoteles Syllogistik 2125 2126 Russel C4 232 3004 Wittgenstein Sprachtheorie 2126 2127 Kopernikus C3 310 3005 Rhetikus Planetenbewegung 2127 2133 Popper C3 52 3006 Newton Keplersche Gesetze 2127 2134 Augustinus C3 309 3007 Spinoza Gott und Natur 2126 2136 Curie C4 36 2137 Kant C4 7 CREATE TABLE Assistenten (PersNr INT PRIMARY KEY, Name VARCHAR2 (100) NOT NULL, Fachgebiet VARCHAR2(100), Boss INT REFERENCES Professoren(PersNr)); Aufgabe: Denken Sie sich eine Insert, eine Delete und eine Update Anweisung aus, die vom System abgelehnt werden. (Jeder für sich mit Zettel und Stift; 5 min) Zusatzaufgabe: Denken Sie sich ein Alter Table aus, dass vom System abgelehnt wird Datenbanken für Mathematiker, WS 11/12 Kapitel 5: SQL2 14 Bootstrapping CREATE TABLE Sekretärin (PersNr INT PRIMARY KEY, … Boss INT REFERENCES Professor(PersNr)); CREATE TABLE Professor (PersNr INT PRIMARY KEY, … Sekräterin INT REFERENCES Sekretärin(PersNr)); Was geht da schief? Welche Tabelle lege ich zuerst an? Welche Daten füge ich zuerst ein? Datenbanken für Mathematiker, WS 11/12 Kapitel 5: SQL2 15 Bootstrapping (Lösungen) •ALTER TABLE MODIFY COLUMN Boss INT REFERENCES Professor(PersNr); •Bei der Datenmodellierung darauf achten, solche zyklischen Abhängigkeiten zu vermeiden •Transaktionen (s. spätere Kapitel) Es werden mehrere Operationen gleichzeitig ausgeführt •Insert into Sekretärin values (4, …, Null); Insert into Professor values (7, …, 4); Update Sekretärin Where PersNr = 4 Set Boss = 7; Datenbanken für Mathematiker, WS 11/12 Kapitel 5: SQL2 16 Constraints... ..to be continued. Datenbanken für Mathematiker, WS 11/12 Kapitel 5: SQL2 17
