Fakultät Bauingenieurwesen Institut für Bauinformatik ... · D1=sind nur die tragenden Bauteile, D2= sind alle Bauteile des Gebäudes. • Jede Sicht, jedes Modell, also auch das

Relationale und Objekt-orientierte Datenstrukturen

Fakultät Bauingenieurwesen Institut für Bauinformatik, Prof. Dr.-Ing. Raimar J. Scherer

6. Semester 1. Vorlesung: SQL I

Informationsmanagement u. Numerische Methoden

Übersicht 1) Daten im Bauwesen 2) Von Tabellen zu Relationen 3) Die Datenbanksprache SQL

a) Historie b) Eigenschaften c) Sprachelemente

i. Schlüsselwörter ii. Syntax iii. Datendefinition: Create, Datentypen iv. Datenmanipulation: INSERT, UPDATE, DELETE v. Abfrage: Select

TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer

1. VO Informationsmanagement: SQL Folie 2



Daten im Bauwesen Schon bei kleinen bis mittleren Bauvorhaben Datenmengen im

GB-Bereich effiziente Strukturierung u. Verwaltung erforderlich Problematik unstrukturierter Daten „Datensalat“, z.B. Word-

Dateien sind einzelne von einander unabhängige Dateien Verwaltung u. Strukturierung großer Datenmengen: Projekt-,

Dokumenten-, Facilitymanagement etc. (relationale) Datenbanken als Grundlage für Informations- und Managementsysteme im Bauwesen

Architektur

HKLS

Elektro

Statik Vermessung

Hersteller/Lieferanten Betreiber

Gutachter/Prüfer

Behörden

Bauherr

BI 1-1 Folie-Nr.: 4

Bauablauf

Maschinen- einsatz

Material zur

Beschaffung

Material- verbrauch

Auf ein und die selbe Objektmenge

Werkzeug 1

Werkzeug 6

Werkzeug 5

Werkzeug 4

Werkzeug 3

Werkzeug 2

Die Funktionalität jeder Sicht wird in einem anderen Software-Werkzeug repräsentiert

Zeit- verbrauch

Kosten- kalkulation

Fach-Sichten (aus 1.Sem, 1.V)

BI 1-1 Folie-Nr.: 5

• Verschiedene Sichten können die selben Objekte zum Gegenstand ihrer Betrachtung haben.

• Jede Sicht stellt ein anderes (Teil-)Modell des Gesamtmodells dar. • Jede Sicht kann andere Relationen benötigen und andere Topologien bilden:

D1=(O,R1); D2=(O,R2). Dann bilden die D verschiedenen Funktionalitäten ab, z.B.: D1= Kostenkalkulation: Objekte geordnet nach Einkauf, Gewerk, etc. D2= Bauablauf: Objekte geordnet nach Abfolge Ihres Einbaus / Erstellung.

• Eine Sicht kann auch nur eine (gefilterte) Untermenge anderer Sicht sein. Dann gilt D1=(O1,R) und D2=(O2,R) mit O1 Teilmenge von O2, z.B.: D1=sind nur die tragenden Bauteile, D2= sind alle Bauteile des Gebäudes.

• Jede Sicht, jedes Modell, also auch das Gesamtmodell ist eine Instanziierung einer vorgegebenen Klassenstruktur.

• Das Gesamtmodell ist sinnvoll nur in einer Datenbank zu verwalten. Die Verwaltung der Werkzeug-Dateien ist fehleranfällig und nicht effektiv.

Fach-Sichten (aus 1.Sem, 1.V)



Datenstruktur: Relation (Tabelle) Alle Informationen durch Werte dargestellt Integritätsbedingungen auf/zwischen Relationen Relationsart 1: alle Werte einer Zeile Relationsart 2: Beziehung zwischen 2 Tabellen

Basis Relationale Algebra - Operatoren: Vereinigung, Differenz Kartesisches Produkt Projektion Selektion Zusätzlich: Grundoperationen: Einfügen, Löschen, Ändern Verknüpfung zu komplexeren Operationen möglich

Entwurfstheorie: Normalformenlehre Datenkonsistenz Synthese von Relationen Sagt was “gute“ Relationen sind (sichere Ausdrücke)

Von Tabellen zu Relationen

Ziel: Speichern, Verwalten und Selektieren von Daten in strukturierter Form

Anlegen, Speichern und Abrufen… …von Daten erfordert eine Sprache



Die Sprache SQL (Structured Query Language ) hat sich als wichtigste Zugriffssprache und internationale, standardisierte Schnittstelle für (relationale) Datenbanken entwickelt.

Historie der Sprache: 1970 von E. F. Codd (IBM) unter dem Namen SEQEL entwickelt 1979 von ORACLE unter dem Namen SQL vorgestellt 1986 erste Normierung (SQL-1 NORM) 1989 ergänzt (SQL-1+ NORM) 1992 erheblich erweitert (SQL-2 Norm) - 600 Seiten – 1999 zahlreiche Erweiterungen (SQL-3), vor allem in Richtung Objektorientierung - 1300 Seiten – 2003 SQL:2003 offizieller Nachfolger des SQL:1999-Standards 2006 SQL:2006 definiert die Verwendung von SQL im

Zusammenhang mit XML (ISO-Standard)

Sie wird gegenwärtig von den meisten Datenbanksystemen wie MySQL, ORACLE, dBase, MS-ACCESS, Sybase, Informix, Ingres, UDS, DB2, Adabas, Foxpro, Parado u.a. unterstützt.

Die Datenbanksprache SQL





Die Datenbanksprache SQL ist… eine Sprache der 4. Generation (Anwendersprache) eine nicht-prozedurale Sprache, d. h. es gibt

1) keine Schleifen 2) keine Verzweigungen

nicht objektorientiert eine unvollständige Sprache, es gibt

1. keine Strukturen wie Unterprogramme 2. keine Hauptprogramme

auf eine Wirtssprache angewiesen, z. B. C++: Aufruf von SQL durch „EXEC SQL“, um

1) ein vollständiges Programm, eine Transaktion, einen Prüfalgorithmus etc. zu erstellen

2) oder mit Batch-File zu arbeiten 3) oder interaktiv (von Hand)

eine vollständige relationale Zugriffssprache, d. h. alle 8 Operationen der relationalen Algebra sind vorhanden bzw. darstellbar (Anm.: nur 5 sind unabhängig. Die restlichen 3 können aus diesen gebildet werden)

ein „Quasi-Standard“ u. wird bei den meisten DBMS unterschiedlich umgesetzt, d.h. die Syntax kann von Standard-SQL abweichen

Ziel ist es, Programme durch Datenaustausch zusammenzuschließen oder das gleiche Datenmodelle für mehrere Untersuchungen zu verwenden. Es werden nicht nur durch den Anwender Objekte erstellt oder verändert, sondern es werden Objekte auch durch Datenaustausch erstellt.

Programm: Abbilden in eine programminterne Klassen-Struktur

Das Programm muss alle Klassen und alle Methoden kennen

Es werden so viele Objekte instanziiert wie im Datenaus-tausch-Container enthalten sind.

Datenaustausch: geg.: Klassen-Struktur a) Datei oder b) Datenbank

alle Informationen der Objekte sind zu repräsentieren

Das Datenobjekt sollte methodenneutral darstellbar sein. Sind methodenbedingte Approximationen notwendig, so ist dies explizit in den Klassendefinitionen zu berücksichtigen.

BI 1-6 Folie-Nr.: 10

SQL

Programmintegration, Datenaustausch und Datensicherung

Folie 11 TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer

1. VO Informationsmanagement: SQL

SQL – Sprachelemente: Schlüsselwörter

Die wesentlichen Schlüsselwörter (Befehle, reservierte Wörter, Kommandos) sind:

CREATE Erstellen, Anlegen z. B. Erstellen der Struktur einer Tabelle mit CREATE TABLE SELECT Auflisten des Inhalts (Informationen, Daten) einer Tabelle INSERT Einfügen von Daten in eine Tabelle UPDATE Ändern von Daten einer Tabelle DELETE Löschen von Daten einer Tabelle

Datendefinition

Datenmanipulation

Abfrage



SQL – Befehle

Die Kommandos beginnen stets mit einem Schlüsselwort (CREATE, SELECT, INSERT, UPDATE oder DELETE), dem Angaben des Anwenders über die Datenbanktabelle folgen. Listen-/Objekt-Programmiersprache

Außerdem können weitere Schlüsselwörter mit entsprechenden Angaben folgen (geschachtelt).

Jeder Befehl wird mit einem Semikolon abgeschlossen. Zwischen den einzelnen Wörtern sind beliebig viele Leerzeichen und

Zeilenwechsel möglich. Groß- und Kleinschreibung wird nicht unterschieden, d.h. sie ist

beliebig. Für Bezeichnungen wie Attribute, Tabellennamen usw. können nur

(Groß- und Klein-) Buchstaben, Ziffern und Unterstrich ( _ ) verwendet werden.

Jede Bezeichnung muss mit einem Buchstaben oder Unterstrich beginnen.



SQL – Darstellungs-Syntax

Ausdrücke in eckigen Klammern [ ] sind optional und können auch weggelassen werden.

Sind mehrere Angaben durch einen senkrechten Strich | getrennt, darf nur genau eine dieser Angaben verwendet werden.

Die hier großgeschriebenen Wörter sind reservierte Wörter und müssen bis auf Groß- und Kleinschreibung exakt im Befehl wiedergegeben werden.

Die kursiv geschriebenen Bezeichnungen sind wahlfrei.



SQL – Der Befehl CREATE

CREATE TABLE Tabellenname ( { Spalte { Datentyp | Gebietsname } [ Spaltenbedingung [ . . . ] ] | Tabellenbedingung } [ , . . . ] ) ;

Syntax:



SQL – Der Befehl CREATE Erstellen der Struktur einer Tabelle – einfache Grundform:

Semikolon schließt den Befehl ab schließende Klammer für Liste der Attribute Komma trennt die Attribute Attribute (Bezeichnungen der anzulegenden Spalten) öffnende Klammer für Liste der Attribute Name der anzulegenden Tabelle Schlüsselwort zum Anlegen einer Tabelle Schlüsselwort für Erstellen

CREATE TABLE Tabellenname ( Sp1 , Sp2 , . . . , Spi . . . , Spn ) ;



SQL – Der Befehl CREATE Erstellen der Struktur einer Tabelle – Angaben zu den Attributen:

CREATE TABLE Tabellenname ( Sp1 , Sp2 , . . . , Spi . . . , Spn ) ;

Festlegungen für die zu erstellende Spalte wie: PRIMARY KEY (Primärschlüssel) REFERENCES Tabellenname (Fremdschlüssel zu einer bereits existierenden Tabelle) NOT NULL (Wert d. Spalte darf nie Null sein) Schließende Klammer für Stellenzahl Angaben der Stellenzahl Öffnende Klammer für Stellenzahl Typ der Information dieser Spalte, wie CHARACTER, NUMERIC, INTEGER usw. Attribut = Name der zu erstellenden Spalte

ATTRIBUT Datentyp ( Stellenzahl ) weitere Angaben



SQL – Der Befehl CREATE: Beispiel Anlegen von 2 Tabellen einer relationalen Datenbank, die über Fremdschlüssel korrespondieren:

CREATE TABLE Studenten ( MatrNr INTEGER PRIMARY KEY , Name CHARACTER (20) NOT NULL , Vorname CHARACTER (20) NOT NULL, Adresse CHARACTER (60) BINr INTEGER REFERENCES Bauinfo3 ON DELETE SET NULL ON UPDATE CASCADE ) ;

CREATE TABLE Bauinfo3 ( BINr INTEGER PRIMARY KEY , Beleg NUMERIC (5, 1) , Klausur NUMERIC (5, 1) , NoteGrdl NUMERIC (5, 1) ) ;

Beachte: Bevor eine Tabelle mit einem (oder mehreren) Fremd- schlüssel(n) angelegt werden kann, muss die Tabelle, auf die sich der (die) Fremd-Schlüssel bezieht(en), vorher angelegt worden sein ! (Early Binding)



SQL – Der Befehl CREATE: Beispiel

BINr Beleg Klausur NoteGrdl

MatrNr Name Vorname Adresse BINr

Tabelle: Bauinfo3

Tabelle: Studenten

Datentypen:

Datentypen:

integer (Ganze Zahl)

numeric (Festkommazahl: max. 5 Vorkomma- u.

genau 1 Nachkommastellen)

integer Character (Zeichenkette mit 20 bzw. 60 Zeichen)

integer

Relation (2)



Primär- und Fremdschlüssel Ein Schlüssel einer Relation ist eine Menge von Attributen, für die gilt: 1) Es gibt keine zwei unterschiedlichen Tupel (Datensätze), die in allen Schlüsselattributen den gleichen Wert haben (Eindeutigkeitseigenschaft) 2) Es gibt keine Teilmenge von Attributen des Schlüssels, die Eigenschaft 1 erfüllt

Ein Fremdschlüssel bezüglich einer Relation R1 ist ein (ggf. zusammengesetztes) Attribut FS einer Relation R2, für das zu jedem Zeitpunkt gilt: Zu jedem Wert (ungleich NULL) von FS muss ein gleicher Wert des Primärschlüssels PS (oder eines Schlüsselkandidaten) in irgendeinem Tupel von R1 enthalten sein. Ein Fremdschlüssel ist wie ein “logischer Pointer“

Fremdschlüssel und zugehöriger Primärschlüssel gestatten die Realisierung von Beziehungen zwischen Relationen (Tabellen).




BINr Beleg Klausur NoteGrdl … … … …

5684 2,7 2,0 2,3 … … … …

MatrNr Name Vorname Adresse BINr 2508112 Muster Theo Freystr. 1 5684

… … … … …

Tabelle: Bauinfo3

Tabelle: Studenten

Primärschlüssel Fremdschlüssel erzeugt Beziehung

Anmerkungen: Primär- und Fremdschlüssel können aus Werten mehrerer Spalten (zusammengesetztes Attribut) bestehen. Ein Fremdschlüssel kann, muss aber nicht Bestandteil des Primärschlüssels seiner Relation sein.




BINr Beleg Klausur NoteGrdl … … … …

5684 2,7 2,0 2,3 … … … …

MatrNr Name Vorname Adresse BINr 2508112 Muster Theo Freystr. 1 5684

… … … … …

Tabelle: Bauinfo3

Tabelle: Studenten

XXXXXXX XXXXXX XXXX XXXXXXXX

XXXX XXX XXX XXX

„Kaskadierendes Löschen“: Vermeidung nicht zuordenbarer Datensätze Wahrung der Datenkonsistenz

1

2 Löschen eines Datensatzes

Nachgeordnetes, automatisiertes Löschen des über Fremdschlüssel abhängigen Datensatzes



Datentyp Bedeutung

INTEGER Ganzzahl

INT Kurzform von INTEGER

SMALLINT Ganzzahl

NUMERIC(x,y) x-stellige Dezimalzahl mit y Nachkommastellen

DECIMAL(x,y) mind. x-stellige Dezimalzahl mit y Nachkommastellen, d.h. mit führenden Nullen

FLOAT(x) Gleitpunktzahl mit Nachkommastellen

CHARACTER(n) Zeichenkette der festen Länge n

CHAR(n) Kurzform von CHARATCER(n)

CHARACTER VARYING(n) Variable Zeichenkette mit bis zu n Zeichen

VARCHAR(n) Kurzform von CHARACTER VARYING(n)

BIT(n) Bitleiste der festen Länge n

DATE Datum (Jahr,Monat,Tag)

TIME Uhrzeit (Stunde,Minute,Sekunde)

SQL – Datentypen



SQL – Datentypen Besonderheiten: Größe von INTEGER u. SMALLINT implementierungsabhängig wobei

garantiert wird, dass INT ≥ SMALLINT (z.B. Oracle: INT 38 stellig, SMALLINT auch 38stellig; MS Access: INT 4 Byte, SMALLINT 2 Byte)

Wenn die x–Werte bei den Datentypen weggelassen werden, werden implementierungsbedingte Werte angenommen. Beim Weglassen der y-Werte wird die Zahl als Null angenommen.

Die Angabe CHARACTER ohne Parameter entspricht dem Vorgabewert CHARACTER (1).

Der Datentyp BIT ohne Parameter hat die Bedeutung BIT(1). Bei SQL-2 können auch die Datentypen REAL und DOUBLE

PRECISION verwendet werden. Sie entsprechen dem Typ FLOAT mit einer implementierungsabhängigen Anzahl von Nachkommastellen.

Wahrheitswert: Einen Wahrheitswert BOOLEAN (wie z. B. in MS-ACCESS) gibt es z.B. in ORACLE nicht. Er muss durch CHARACTER(1) oder BIT(1) umschrieben werden.



SQL – Der Datentyp DATE Daten vom Typ DATE (Datum) können miteinander verglichen werden. Sie können auch voneinander subtrahiert werden. Das Ergebnis ist dann eine INTEGER-Zahl, die die Anzahl der Tage zwischen diesen beiden Daten enthält. Die Addition oder Subtraktion einer Variablen vom Typ DATE mit einer INTEGER-Zahl ist ebenfalls möglich. Das Datum wird entsprechend um die Anzahl der Tage erhöht bzw. erniedrigt. Beachte: Wegen der unterschiedlichen nationalen Schreibweisen und Zeitzonenangaben können bei den Datentypen DATE und TIME (Uhrzeit) Probleme auftreten, z.B. beim subtrahieren von Englischer und italienischer Zeit.



SQL – Der Befehl INSERT

INSERT INTO { Tabellenname | Sichtname } [ ( Spaltenliste )] Tabellenausdruck

Syntax:

Mit INSERT können einzelne Datensätze in eine Tabelle eingespeichert werden.



SQL – INSERT: Einfache Grundform

Semikolon schließt den Befehl ab schließende Klammer für einzufügende Daten aktuelle Werte der einzufügenden Daten öffnende Klammer für einzufügende Daten Schlüsselwort für einzufügende Daten schließende Klammer für Liste der Attribute trennt die einzelnen Attribute (Spaltennamen) Attribute (formal) der einzufügenden Spalten öffnende Klammer für Liste der Attribute Name der Tabelle, in die d. Datensatz eingefügt wird Schlüsselwort für Angabe des Tabellennamens Schlüsselwort zum Einfügen eines Datensatzes

INSERT INTO . . . ( . . . , . . . , . . . ) VALUES ( . . . , . . . , . . . , ) ;




In die Tabelle „Mitarbeiter“ werden für diesen Datensatz die Spalten: PersNR = 123456, Name = ‘Berger‘, Vorname = ‘Felix‘ und Gehalt = 4900 in der angegebenen Reihenfolge eingegeben. Der Datensatz wird am Ende der Tabelle angefügt.

INSERT INTO Mitarbeiter ( PersNR , Name , Vorname , Gehalt ) ( 123456 , ‘Berger‘ , ‘Felix‘ , 4900) ;

Einfügen eines Datensatzes:




INSERT INTO Mitarbeiter ( Vorname, Name, PersNr ) ( ‘Felix‘ , ‘Berger‘ , 123456 ) ;

Die Spalten werden in der angegebenen Reihenfolge eingegeben, wobei die Reihenfolge in den beiden Klammern genau überein- stimmen muss (korrespondierende Listen). Ob die einzugebenden Daten in Hochkommas ( ‘ . . . ‘) eingeschlossen werden, hängt davon ab, unter welchem Typ die betreffende Spalte vereinbart wurde.

Einfügen eines Datensatzes in abweichender Reihenfolge der Spalten:




In die Tabelle „Mitarbeiter“ wird ein Datensatz nur mit den Spalten PersNr und Name ein- bzw. angefügt. Es müssen hier nicht alle Spalten, mit denen die Tabelle vereinbart wurde, angegeben werden.

INSERT INTO Mitarbeiter ( PersNR , Name ) ( 123456 , ‘Berger‘ ) ;

Einfügen eines unvollständigen Datensatzes:



SQL – Der Befehl UPDATE

UPDATE { Tabellenname | Sichtname } SET { Spalte = { Spaltenausdruck | NULL }} [ , . . . ] [ WHERE { Bedingung | CURRENT OF Cursorname } ]

Syntax:

Mit UPDATE können einzelne Datensätze einer Tabelle geändert werden.



SQL – UPDATE: Einfache Grundform

In der Tabelle „Mitarbeiter“ wird bei alle Datensätzen, bei denen die Spalte „Gehalt“ < 5000 ist, diese Spalte mit 1,2 multipliziert.

UPDATE Mitarbeiter SET Gehalt = 1.20 * Gehalt WHERE Gehalt < 5000 ;

Ändern bestimmter Werte einer Spalte in Abhängigkeit von einer Bedingung:



SQL – UPDATE

Anmerkung: Die Bedeutung dieses Befehls liegt in den vielfältigen Möglichkeiten, Tupel mittels WHERE-Klausel auszuwählen. Außerdem kann im UPDATE-Befehl einem Attribut nicht nur ein neuer Wert zugewiesen werden, es können auch existierende Werte auf Null gesetzt werden. Hierzu wird der reservierte gleich lautende Bezeichner NULL auf der rechten Seite einer Zuweisung verwendet.



SQL – Der Befehl DELETE

DELETE FROM { Tabellenname | Sichtname } [ WHERE { Bedingung | CURRENT OF Cursorname } ]

Syntax:



SQL – DELETE

Aus der Tabelle „Mitarbeiter“ wird der Datensatz, bei dem die Spalte „PersNr“ = 233123 ist, gelöscht.

DELETE FROM Mitarbeiter WHERE PersNr = 234123 ;

Löschen eines Datensatzes:

Documents

Fakultät Bauingenieurwesen Institut für Bauinformatik ... · D1=sind nur die tragenden Bauteile, D2= sind alle Bauteile des Gebäudes. • Jede Sicht, jedes Modell, also auch das