Datenbankentwicklung IV-LK

DatenbankentwicklungIV-LK • Warum Datenbanken? • Anforderungsanalyse für Datenbanken • Ableitung von Tabellenstrukturen • Normalisierung • Relationenalgebra • Formalisierung von Tabellen in SQL • SQL- Anfragen Nicole Rottmann 2011/12

6. Formalisierung von Tabellen in SQLIV-LK • Tabellendefinition mit SQL • Einfügen, Löschen und Ändern von Daten • Datentypen in SQL • NULL-Werte und drei-wertige Logik • Constraints • Aufgaben Nicole Rottmann 2011/12

6. Formalisierung von Tabellen in SQL1. Tabellendefinition mit SQLIV-LK Einfaches Verkaufsbeispiel: Übertragung der Struktur und Information in SQL-Definitionssprache: CREATE TABLE Verkaeufer ( Vnr INTEGER, Name VARCHAR (6), Status VARCHAR (7), Gehalt INTEGER, PRIMARY KEY (Vnr) ) und CREATE TABLE Kunde ( Knr INTEGER, Name VARCHAR (6), Betreuer INTEGER, PRIMARY KEY (Knr) FOREIGN KEY (Betreuer) REFERENCES Verkaeufer (Vnr) ) Verkaeufer Kunde Nicole Rottmann 2011/12

6. Formalisierung von Tabellen in SQL1. Tabellendefinition mit SQLIV-LK Definition von Primärschlüsseln: PRIMARY-KEY (<Spaltenname1>,…,<SpaltennameN>) Definition von Fremdschlüsseln: CONSTRAINT <constraintname> FOREIGN KEY(<meinSpaltenname1>,…,<meinSpaltennameN> REFERENCES <übergeornete Tabelle> <dortigerSpaltenname1>,…,<dortigerSpaltennameN>) Fremdschlüsselbeziehungen können sich nur auf definierte Primärschlüssel anderer Tabellen beziehen. Diese Tabellen müssen vor der Tabelle, in der das Constraint definiert wird, erzeugt werden. Nicole Rottmann 2011/12

6. Formalisierung von Tabellen in SQL2. Einfügen, Löschen und Ändern von DatenIV-LK Einfügen von Daten in Tabellen: INSERT INTO <Tabellenname> VALUES (<WertFürSpalte1>,…,<WertFürLetzteSpalte>) Beispiel: INSERT INTO Verkaeufer VALUES (1001,Ùdo`,`Junior`,1500);INSERT INTO Verkaeufer VALUES (1002,Ùte`,`Senior`,1900);INSERT INTO Verkaeufer VALUES (1003,Ùwe`,`Senior`,2000); Einfügen von Daten in einzelne Spalten INSERT INTO Verkaeufer (Vnr, Name, Status) VALUES (1004,Ùlf`,`Junior`); Spalten, die nicht im INSERT Befehl genannt sind werden entweder mit • NULL-Werten gefüllt, oder mit • einem definierten Standard Wert gefüllt. Definition eines Attributs mit einem Standardwert: STATUS VARCHAR(7) DEFAULT `Junior` Nicole Rottmann 2011/12

6. Formalisierung von Tabellen in SQL2. Einfügen, Löschen und Ändern von Daten IV-LK Kontrolle beim Einfügen: Verstoß gegen die Eindeutigkeit des Primärschlüssels: INSERT INTO Verkaeufer VALUES (1001, `Alf´, ´Senior´, 3000) Kann die Fehlermeldung folgendermaßen aussehen: Verstoß gegen Eindeutigkeit, Regel Versucht man einen Kunden mit einem Betreuer einzutragen, den es nicht gibt, z.B. INSERT INTO Kunde VALUES(4,´Edna´,999); Wird der Eintrag nicht vorgenommen und man erhält z.B. folgende Fehlermeldung: Verstoß gegen Contraint (FK_KUNDE). Übergeordneten Schlüssel nicht gefunden Nicole Rottmann 2011/12

6. Formalisierung von Tabellen in SQL2. Einfügen, Löschen und Ändern von Daten IV-LK Ändern von Tabelleneinträgen: UPDATE Kunde SET Name = ´Edwina´, Betreuer = 1002 WHERE Name = 'Edna' Allgemeine Syntax: UPDATE <Tabellenname> SET <SpaltennameI> = <Wert>, <SpaltennameJ> = <Wert>, WHERE <Bedingung> Änderung in Abhängigkeit von alten Werten: UPDATE Verkaeufer SET Gehalt = Gehalt * 1.05 Nicole Rottmann 2011/12

6. Formalisierung von Tabellen in SQL2. Einfügen, Löschen und Ändern von Daten IV-LK Löschen von Daten: DELETE FROM Kunde WHERE Knr = 3 Allgemeine Syntax: DELETE FROM <Tabellenname> WHERE <Bedingung> Der Verkäufer mit der Vnr 1001 soll gelöscht werden. DELET FROTM Verkaeufer WHERE Vnr = 1001 Fehlermeldung: Verstoß gegen Contraint (FK_KUNDE). Untergeordneter Datensatz gefunden. Lösung: UPDATE Kunde SET Betreuer = NULL, WHERE Betreuer = 1001; DELETE FROM Verkaeufer WHERE Vnr = 1001 Nicole Rottmann 2011/12

6. Formalisierung von Tabellen in SQL2. Einfügen, Löschen und Ändern von Daten IV-LK Definition von Fremdschlüsseln zur Löschung von Daten: CREATE TABLE Kunde( Knr INTEGER, Name VARCHAR (7), Betreuer INTEGER, PRIMARY KEY (Knr), CONSTRAINT FK_KUNDE FOREIGN KEY (Betreuer) REVERENCES Verkaeufer (Vnr) ON DELETE CASCADE ) Reaktionsmöglichkeiten beim Löschen von Daten: NO ACTION: entspricht der Ursprungseinstellung, das Löschen wird abgelehnt, wenn ein abhängiger Datensatz existiert. CASCADE: siehe oben SETNULL: für abhängige Datensätze wird die Referenz auf den gelöschten Datensatz automatisch auf NULL gesetzt. SETDEFAULT: wenn es eine Default – Wert gibt, wird dieser im abhängigen Datensatz eingetragen; existiert ein solcher Wert nicht, wird der Löschvorgang nicht durchgeführt. Nicole Rottmann 2011/12

6. Formalisierung von Tabellen in SQL2. Einfügen, Löschen und Ändern von Daten IV-LK Löschen von Tabellen: DROP TABLE <Tabellenname> RESTRICT Alternative zum Löschen von Tabellen: DROP TABLE <Tabellenname> CASCADE Nicole Rottmann 2011/12

6. Formalisierung von Tabellen in SQL3. Datentypen in SQLIV-LK Definition ganzer Zahlen: SMALLINT, INTEGER, BIGINT Definition von Kommazahlen: DECIMAL, NUMERIC, FLOAT, REAL, DOUBLE PRECISION NUMERIC (5,2) bedeutet insgesamt 5 Stellen und zwei Nachkommastellen. Definition von Texten: CHAR (q) und VARCHAR (q) Definition von Tagen DATE, TIME Nicole Rottmann 2011/12

6. Formalisierung von Tabellen in SQL4. NULL-Werte und drei-wertige LogikIV-LK Formulierung von WHERE Bedingungen: Vnr > 1003 AND Gehalt >= 2000 Syntax von WHRER Bedingungen: <bedingung> := <Spaltenname> <op> <wert> <bedingung> := <bedingung> <AND> <bedingung> <bedingung> := <bedingung> <OR> <bedingung> <bedingung> := NOT <bedingung> <bedingung> := (<bedingung>) op:= < ; <=; >; >=; =; <> Wahrheitstafel der zwei-wertigen Logik Nicole Rottmann 2011/12

6. Formalisierung von Tabellen in SQL 4. NULL-Werte und drei-wertige Logik IV-LK Wahrheitstafel der drei-wertigen Logik Überprüfung auf NULL-Werte Nicole Rottmann 2011/12

6. Formalisierung von Tabellen in SQL5. ConstraintsIV-LK Vermeidung von NULL- Einträgen: CREATE TABLE Verkaeufer( Vnr INTEGER, Name VARCHAR(6) NOT NULL, Status VARCHAR (7) DEFAULT 'Junior' CONSTRAINT StatushatWert CHECK (Status IS NOT NULL), Gehalt INTEGER, PRIMARY KEY (Vnr), CONSTRAINT GehaltImmerAngeben) CHECK (Gehalt IS NOT NULL) ) Syntax: CONSTRAINT <Constraintname> CHECK (<Bedingung>) Nicole Rottmann 2011/12

6. Formalisierung von Tabellen in SQL5. ConstraintsIV-LK Prüfung von Constraints: Die gesamte Aktion wird abgebochen, wenn ein Constraint nach "false" ausgewertet wird. Übersetzung von Wenn – Dann Bedingungen: Beispiel: Jemand mit dem Status 'Junior' darf maximal 2000 verdienen. Analyse einer Wenn- Dann Bedingung: Constraints in der Form, wenn A gilt, dann muss auch B gelten werden immer in der Form NOT (A) OR B geschrieben Nicole Rottmann 2011/12

6. Formalisierung von Tabellen in SQL6. AufgabenIV-LK Wiederholungsfragen: • Wie werden Tabellen in SQL definiert, wie sehen Schlüssel und Fremdschlüssel aus, wozu gibt es Constraints? • Was ist bei Fremdschlüsseln im Zusammenhang mit NULL – Werten zu beachten? • Wie werden Daten in Tabellen eingefügt, geändert und gelöscht? • Welche Reaktionen können beim Einfügen, Ändern und Löschen auftreten, damit die referentielle Integrität der Datenbank gewährleistet wird? • Welche Möglichkeiten gibt es, Fremdschlüssel zu definieren, welchen Einfluss hat dies auf das Löschen von Daten? • Welche Standardtypen in SQL kennst du, warum gibt es unterschiedliche Typen für Zahlen und Texte? • Warum hat SQL eine dreiwertige Logik, was ist das überhaupt? • Wie werden Constraints ausgewertet? • Wie verhalten sich NULL Werte in mathematischen Operationen? • Wie kann man NULL Werte in Spalten ändern? • Wie werden Wenn- Dann Constraints übersetzt? Nicole Rottmann 2011/12

6. Formalisierung von Tabellen in SQL6. AufgabenIV-LK Projekt Übungsaufgaben: Gegeben seien folgende Tabellen zur Projektverwaltung: Arbeitspaket Arbeit Nicole Rottmann 2011/12

6. Formalisierung von Tabellen in SQL6. AufgabenIV-LK • Gib für die drei Tabellen SQL Befehle zur Erzeugung der Tabellen und des Inhalts an. Beachte auch die Fremdschlüssel, diese sollen zur Löschweitergabe genutzt werden. Für die Tabelle Arbeitspaket sollen folgende Bedinungen aufgenommen werden:- Kein Eintrag darf leer sein. - Die Person Winzig darf nie Arbeitspaketleiter sein. - Alle Arbeitspakete mit dem Namen Analyse dürfen nie von der Person Hall geleitet werden. - Alle Arbeitspakete mit dem Namen Implementierung müssen von der Person Mittel oder der Person Hall geleitet werden. • Wie löscht man das Projekt Notendatenbank, was passiert dabei sonst noch in der Datenbank? • Wie kann das Projekt Fehlzeitendatenbank in Anwesenheitsdatenbank umbenannt werden, wobei die Projektnummer gleichzeitig auf 11 geändert wird? Nicole Rottmann 2011/12

Datenbankentwicklung IV-LK