01/05/2007 11:40 Daten und Tabellen Normalisierung, Beziehungen, Datenmodell SQL - Structured Query Language Anlegen von Tabellen Datentypen (Spalten), Regeln, Beziehungen Tabellen (Objekt) Beschreibung Daten (Zeilen) einfügen und abfragen, ändern und löschen Ein Einführung in die Nutzung von SQL-Datenbanken am Beispiel Oracle Inhalt 05.01.07 Informationstechnologie 05.01.07 Informationstechnologie das Transaktionsmodell View Funktionen Prozeduren und Pakete (Datenbankanwendungen) WWW-Anwendungen Nils Schmeißer Mitglied der Leibniz-Gemeinschaft Daten und Tabellen - ein Beispiel Daten und Tabellen - Normalisierung Probleme: Redundanzen Inkonsistenzen Lösung: “Normalisierung” eine Tabelle hat eine feste “Breite”, die Spaltenwerte sind atomar eine Tabelle enthält nur die Daten eines Themengebietes (Definitionsbereich) Name Vorname Ort Mustermann Manfred 1 Mustermann Brunhild 1 ID 1 PLZ 1234 Ort Musterstadt Mitglied der Leibniz-Gemeinschaft Nils Schmeißer Straße Straße 1 Aufspalten der Daten in Namen und Orte Zuordnung von Orten zu Namen Frage: wie erfolgt die Zuordnung? → Schlüssel und Beziehung (Relation) 05.01.07 Informationstechnologie Name Vorname PLZ Ort Straße Mustermann Manfred 1234 Musterstadt Straße 1 Msterstadt Straße 1 Mustermann Brunhild Informationstechnologie Nils Schmeißer 05.01.07 Mitglied der Leibniz-Gemeinschaft Mitglied der Leibniz-Gemeinschaft Nils Schmeißer 01/05/2007 11:40 SQL - Structured Query Language deklarative Datenbanksprache für relationale DB-Systeme 1986 ANSI, 1999 ISO/IEC 9075:1999 DDL: Data Definition Language; Befehle zur Definition des Datenbankschemas z.B. create table DML: Data Manipulation Language; Befehle zur Datenmanipulation und Abfrage DCL: Data Control Language; Befehle zur Rechteverwaltung relationale Datenbanksysteme (RDB) DB2, Informix, Microsoft SQL Server, MySQL, Oracle, PostgreSQL, Sybase andere Modelle: OODB Vorraussetzung: Datenmodell Einhaltung der Normalisierungsregeln Festlegen von Definitionsbereichen, Regeln, Schlüsseln Darstellung im sog. entity-relationship Diagramm Datenmodell 05.01.07 Informationstechnologie 05.01.07 Informationstechnologie Mitglied der Leibniz-Gemeinschaft Nils Schmeißer Mitglied der Leibniz-Gemeinschaft <spaltenname> <spaltentyp> [ <regel> ] Beispiel: create table ort ( smallint, int, bigint: ganze Zahlen float, double: rationale Zahlen number: ganze und rationale Zahlen (Oracle spezifisch) varchar2(n): String (maximale Länge n ≤ 4000) Werte werden in Hochkomma (') eingeschlossen (Bsp.: 'hallo') varchar2 ist Oracle spezifisch, SQL-Standard: varchar date: Zeitstempel (Datum und Uhrzeit) blob: binary large object für Objekte größer als 4 kByte kann beliebige Binärdaten enthalten cblob: große Strings boolean: logische Werte (true und false) id number primary key, name varchar(4000), plz number, strasse varchar(4000) null-Wert: der nicht-existente Wert (≠ leerer String) Mitglied der Leibniz-Gemeinschaft Nils Schmeißer 05.01.07 Informationstechnologie 05.01.07 Informationstechnologie Tip: immer mehr Platz vorsehen Tabellen- und Spaltennamen dürfen keine Sonderzeichen und Leerzeichen enthalten ) / ← Kennzeichen für Ende DDL Anweisung Spalten haben Name, Typ und optional Regeln Datentypen create table <tabellenname> ( <spalten> ); Tabellen - Datentypen Tabelle hat Name und Spalten Anlegen von Tabellen Nils Schmeißer Mitglied der Leibniz-Gemeinschaft Nils Schmeißer 01/05/2007 11:40 Tabellen - komplettes Beispiel Regeln uniq: Wertemenge enthält jeden Wert höchstens einmal not null: keine null-Werte zulässig primary key: Primärschlüssel (uniq not null) ein Primärschlüssel kann über mehrere Spalten erklärt werden default <wert>: default Wert Fremdschlüssel references <tabelle> ( <spalte> ) die referenzierte Spalte muß die Eigenschaft uniq not null oder primary key haben alternative Regeldarstellung: constraint <cname> { primary key ( <sname> ) | foreign key ( <sname> ) references <tname> ( <rsname> ) [ on delete cascade ]} create table orte ( id number primary key, name varchar2(4000) not null, plz number not null, strasse varchar2(4000) not null ) / create table orte ( id number, name varchar2(4000) not null, plz number not null, strasse varchar2(4000) not null, constraint pk_orte primary key ( id ) ) / 05.01.07 create table kontakte ( id number primary key, name varchar2(4000) not null, vname varchar2(4000) not null, ort number references orte(id) ) / create table kontakte ( id number primary key, name varchar2(4000) not null, vname varchar2(4000) not null, ort number, constraint fk_orte foreign key (ort) references orte (id) ) Informationstechnologie 05.01.07 Informationstechnologie Mitglied der Leibniz-Gemeinschaft Nils Schmeißer Mitglied der Leibniz-Gemeinschaft insert into <tname> (<spaltenliste>) values (<wertliste>) ; die Angabe der Spaltenliste kann entfallen, wenn alle Spalten mit Werten befüllt werden; Reihefolge beachten ! describe <objektname> ; zeigt die Beschreibung eines Objektes an Beispiel: describe orte Beispiel: insert into orte ( id, plz, name, strasse ) values ( 1, 1234, 'Musterstadt', 'Strasse 1'); insert into orte values ( 2, 'Dresden', 1328,'Strasse 3'); was passiert bei folgenden Anweisungen: drop <type> <objektname> ; Löschen eines Objektes Beispiel: drop table orte; insert into orte values ( 3, 4567, 'Pirna','Strasse 4'); insert into orte values ( 1, 'Pirna', 4567, 'Strasse 4'); insert into orte (id,plz,strasse) values ( 3, 4567, 'Strasse 4'); 05.01.07 Informationstechnologie 05.01.07 Informationstechnologie Nils Schmeißer Datenzeilen einfügen Tabellen beschreiben und löschen Tabellen - Beziehungen Mitglied der Leibniz-Gemeinschaft Nils Schmeißer Mitglied der Leibniz-Gemeinschaft Nils Schmeißer 01/05/2007 11:40 Datenzeilen abfragen Datenzeilen ändern Beispiel: update <tabellenname> set <zuweisungsliste> [ where <bedingung> ] ; die Zuweisungsliste besteht aus Termen der Form <spaltenname>=<Wert> select <spaltenliste> from <tabellenliste> [ where <bedingung> ] ; * meint alle Spalten die Bedingung ist ein bool'scher Term (true oder false), z.B. 1=2 oder <spalte>=<wert> Beispiel: update orte set plz=9999 where id=1; update kontakte set ort=2; select * from orte; select name,strasse from orte where plz=1234; Join: Abfrage über mehrere Tabellen select kontakte.name,orte.name from kontakte,orte where kontakte.ort=orte.id; 05.01.07 Nils Schmeißer Beispiel: Lösung: eine Transaktionsfolge muß bestätigt werden, bevor sie persistent (dauerhaft) in die Datenbank geschrieben wird. commit; macht eine Transaktionsfolge persistent rollback; macht eine Transaktionsfolge ungültig eine neue Transaktionsfolge beginnt nach jedem commit oder rollback bzw. beim Anmelden an der Datenbank delete from kontakte where name='Mustermann'; delete from orte; Beispiel: Das Transaktionsmodell delete from <tabellennname> [ where <bedingung> ]; Informationstechnologie 05.01.07 Informationstechnologie 05.01.07 Nils Schmeißer Mitglied der Leibniz-Gemeinschaft Datenzeilen löschen 05.01.07 Informationstechnologie Informationstechnologie Mitglied der Leibniz-Gemeinschaft Mitglied der Leibniz-Gemeinschaft Nils Schmeißer Mitglied der Leibniz-Gemeinschaft Nils Schmeißer 01/05/2007 11:40 View Funktionen Funktionen berechnen Ausdrücke können in Abfrage verwendet werden Beispiel: select max(id) from orte; Vorname Manfred Brunhild Ort 1 1 Name Vorname Mustermann Manfred Mustermann Brunhild ID 1 PLZ 1234 1234 PLZ 1234 Ort Musterstadt Ort Musterstadt Musterstadt Straße Straße 1 Name Mustermann Mustermann Straße Straße 1 Straße 1 spezifische Sicht auf Daten wird wie Tabelle behandelt Datenmanipulation nur, wenn View über einer Tabelle 05.01.07 05.01.07 Informationstechnologie Informationstechnologie Mitglied der Leibniz-Gemeinschaft Nils Schmeißer Mitglied der Leibniz-Gemeinschaft WWW-Anwendungen spezifische Sicht auf Daten Browser WWW- PL/SQL Server DB Nutzung des CGI - Common Gateway Interfac https://www.fzd.de/pls/rois/<s>.<p>.<m>?a1=v1&...&an=vn https - Protokoll, www.fzd.de - Server, pls - PL/SQL, rois - DAD (Database Access Descriptor), <s>.<p>.<m> - Schema.Paket.Methode, Parameterliste Informationstechnologie → Aufruf an S.P.M(V1,...,Vn) Methode/Prozedur schickt mit der Prozedur htp.prn(<text>) Text an den WWW-Server zurück 05.01.07 Informationstechnologie 05.01.07 Prozeduren und Paket Nils Schmeißer Mitglied der Leibniz-Gemeinschaft Nils Schmeißer Mitglied der Leibniz-Gemeinschaft Nils Schmeißer create view <vname> ( spalten ) as <select-statement>; 01/05/2007 11:40 Beispiel: create or replace procedure h(xname in varchar2 default null) is begin if xname is not null then htp.prn('<html><body>Hello ' || xname || '</body></html>'); else htp.prn('<html><body><form action=”s.h” method=”POST”>'); htp.prn('Name <input type=”text” name=”xname”>'); htp.prn('</form></body></html>'); end if; end; Informationstechnologie 05.01.07 WWW-Anwendungen Beispiel Mitglied der Leibniz-Gemeinschaft Nils Schmeißer