Fakultät Bauingenieurwesen Institut für Bauinformatik, Prof. Dr.-Ing. Raimar J. Scherer Informationsmanagement u. Numerische Methoden Relationale und Objekt-orientierte Datenstrukturen 6. Semester 1. Übung: Relationale Datenstrukturen I Informationsmanagement u. Numerische Methoden Relationale und Objekt-orientierte Datenstrukturen Institut für Bauinformatik Prof. Dr.-Ing. Raimar J. Scherer Bürogebäude Nürnberger Ei Nürnberger Str. 32a, 2. OG Dipl.-Ing. Frank Opitz E-Mail: [email protected] Tel: 463 35741 Zimmer: 210 TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer ÜO Informationsmanagement Einführung Folie 2 Übersicht Lehr- bzw. Übungsinhalte 1) Relationale Datenstrukturen a) Datenbanken/ DB-Systeme/ DBManagementsysteme b) Relationen-Theorie und Normalisierung c) Datenbankentwurf d) Datenbankabfragesprache SQL i. Datendefinitionssprache (DDL) ii. Datenmanipulationssprache (DML) iii. Datenaufsichtssprache (DCL) 2) Objekt-orientierte Datenstrukturen a) Modellieren mit EXPRESS /EXPRESS-G b) IFC-Datenmodell (Industry Foundation Classes) c) Datenaustausch mit SPF (Step Physical File – Format) TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer ÜO Informationsmanagement Einführung Folie 3 Übersicht 1) Literaturhinweise – Teil Rel. Datenstrukturen 2) Daten im Bauwesen 3) Nachteile von Dateisystemen 4) Grundbegriffe 5) Vorteile von Datenbanksystemen 6) Nachteile von Datenbanksystemen 7) Warum MS Access? 8) Aufgaben: Anlegen einer Datenbank „Baunternehmen“ (SQL-DDL: create, alter, drop – erstellen, ändern, löschen) 9) Integritätsbedingungen 10) Aufgaben: Eintragen und Ändern der Daten (SQLDML: insert, update, delete) TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 4 Literaturhinweise Literatur (Datenbanken u. SQL): (1) G. Pernul & R. Unland: „Datenbanken im Unternehmen – Analyse, Modellbildung und Einsatz“, 2. Aufl., Verlag Oldenburg, 2003 (2) E. Schicker: „Datenbanken und SQL : eine praxisorientierte Einführung mit Hinweisen zu Oracle und MS-Access“, Teubner Verlag, 2000 (3) G. Matthiessen & M. Unterstein: „Relationale Datenbanken und StandardSQL : Konzepte der Entwicklung und Anwendung“, 4. Aufl., Verlag Addison Wesley, 2008 Lernmaterial SQL im WWW: (1) http://sql.1keydata.com/de/ (2) http://w3schools.com/sql/ (engl. sprachig) (3) http://www.sql-und-xml.de/sql-tutorial/ TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 5 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- oder Excel-Dateien mit allen Projektinformationen Verwaltung und Strukturierung großer Datenmengen: Projekt-, Dokumenten-, Facilitymanagement etc. Betreiber Hersteller/Lieferanten Architektur Vermessung Statik Elektro Behörden HKLS Gutachter/Prüfer (relationale) Datenbanken als Grundlage für Informationsund Managementsysteme im Bauwesen TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 6 Nachteile von Dateisystemen Wiederholte Speicherung gleicher Daten (Redundanz) Integritäts- u. Konsistenzprobleme (Mehrbenutzerbetrieb) erhöhter Speicherplatzbedarf erhöhter Verwaltungsaufwand erhöhte Kosten Verantwortung des Programmierers für Datenintegrität Datensicherheit effizienten Zugriff Bindung von Datenstrukturen an Programmstrukturen/-funktionen Hoher Änderungsaufwand Problem der Interoperabilität Bedienung bedarfsabhängiger Sichten nur bedingt möglich TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 7 Grundbegriffe Datenbank (DB) eine sehr große integrierte Sammlung von Daten Beschreibt einen Ausschnitt aus der realen Welt: Entitäten (z.B. Studenten, Kurse) Beziehungen (z.B. Student besucht Kurs) Datenbank-Management-System (DBMS) Software-Paket zum Speichern und Verarbeiten von Datenbanken (Einfügen, Lesen, Ändern und Löschen von Daten) Beispiele: Oracle, DB2 (IBM), MS SQL-Server Datenbank-System (DBS) DBS = DBMS + DB Ermöglicht die anwendungsübergreifende Nutzung von Daten Isoliert Anwendungsprogramme von Hardware und Betriebssystem (und deren Änderungen) Datenbankmodell (DM) Struktur Operationen Konsistenzregeln der Daten TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 8 Vorteile von Datenbanksystemen Datenunabhängigkeit von Programmen, Wiederverwendbarkeit, effizienter Zugriff Einheitliche Datenadministration, Kontrolle der Datenintegrität Vermeidung von Redundanzen, geringerer Aufwand bei Änderungen Erzeugung unterschiedlicher, bedarfsabhängiger Sichten auf dieselben Daten - Filtern Verminderte Entwicklungszeit hohe Skalierbarkeit Datensicherheit und Zugriffskontrolle auf die Daten (Korrektheit bei fehlerhaftem Ablauf einzelner Anwendungen und Systemabsturz) TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 9 Nachteile von Datenbanksystemen General-Purpose Software oft weniger effizient als spezialisierte Software Bei konkurrierenden Anforderungen kann DBS nur für einen Teil der Anforderungsprofile optimiert werden Kosten: DBMS und zusätzliche Hardware, z.B. Logging-, Backup-Systeme Hochqualifiziertes Personal (DB-Administration) Verwundbarkeit durch Zentralisierung (Ausweg: Verteilung) TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 10 Warum MS Access? I.d.R. überall verfügbar Kompatibel mit gängiger MS Office-Software (Excel, Word etc.) Erstellung von DB-Anwendungen ohne Programmieraufwand Relevanz für kleinere und mittlere Unternehmen Unterstützt relationales Datenbankmodell SQL-basiert: Komplexere Abfragen auch mit SQL-Anweisungen möglich Erweiterbar durch Microsoft SQL-Server Externer Zugriff auf Access-DBs mittels vorhandener Schnittstellen z.B. für Visual Basic, Delphi Es existiert eine Vielzahl ähnlicher Produkte (z.B. Base in LibreOffice) TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 11 Anlegen der Datenbank „Bauunternehmen“ Beschäftigte Betonsorten PersNr Name Vorname Tätigkeit BNr Qualität Preis 12363 Ahrend Gerd Maurer 1 C15/20 200,7 13332 Bayer Andreas Kranführer 26661 Berger Sven Kranführer 2 C20/25 196,5 26663 Czerny Petra Baggerführer 3 C25/30 197,2 35558 Müller Felix Bauleiter 4 C35/45 185 36663 Müller Paul Gerüstbauer 36886 Berger Bert Baggerführer Bauobjekte ObjNr Bauliches Objekt Baumaschinen MNr Bezeichnung Typ Wert 1 Planierraupe PL 20/40 75000 1 Wohnpark I 2 Turmdrehkran TK 260 868000 2 Stadtvilla 3 Autokran ADK 60 650000 3 Wohnpark II 4 Bagger BG 70 820000 4 Einf.haus Wendt 5 Bagger BG 62 760000 5 Stadthaus 6 Str.baumasch. H 1/7 68888 6 Einf.haus Wagner 7 Mischfahrzeug HR 50 65000 TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 12 Aufgabe 1: Erstellen einer Datenbank „Bauunternehmen“ a) Anlegen der Tabelle „Beschäftigte“ in der MSA-Entwurfsansicht b) Anlegen der Tabelle „Beschäftigte“ mit SQL c) Festlegung der Integritätsbedingungen Beschäftigte PersNr Name Vorname Tätigkeit 12363 Ahrend Gerd Maurer 13332 Bayer Andreas Kranführer 26661 Berger Sven Kranführer 26663 Czerny Petra Baggerführer 35558 Müller Felix Bauleiter 36663 Müller Paul Gerüstbauer 36886 Berger Bert Baggerführer TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 13 Aufgabe 1: Erstellen einer Datenbank „Bauunternehmen“ b) Anlegen der Tabelle „Beschäftigte“ mit SQL CREATE TABLE Beschäftigte ( PersNr integer, Vorname char(40), Nachname char(50), Tätigkeit char(100)); TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 14 Aufgabe 1: Erstellen einer Datenbank „Bauunternehmen“ b) Anlegen der Tabelle „Beschäftigte“ mit SQL CREATE TABLE Beschäftigte ( PersNr integer, Vorname char(40), Nachname char(50), Tätigkeit char(100)); Definition Primärschlüssel fehlt! CREATE TABLE Beschäftigte ( PersNr integer PRIMARY KEY, Vorname char(40), Nachname char(50), Tätigkeit char(100)); TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 15 Aufgabe 1: Erstellen einer Datenbank „Bauunternehmen“ c) Festlegen der Integritätsbedignungen Sehr große Datenmengen hohe Wahrscheinlichkeit für Fehler DBMS unterstützt durch Integritätsbedingungen das Erkennen nicht korrekter Daten bzw. Situationen und hilft diese zu verhindern. Statische Dynamische Bedingungen, die in einem Zustand erfüllt sein müssen, damit er gültig ist Bedingungen, die beim Übergang von einem in einen anderen Zustand beachtet werden müssen. Beispiele: - Anzahl der Semester muss > 0 sein - Betonpreis von C12/15 muss < sein als der von C20/25 Beispiele: - Anzahl der Semester einer/s Stud. darf nicht kleiner werden - Betonpreise dürfen innerhalb von einem Jahr nicht mehr als 15% steigen TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 16 Aufgabe 1: Erstellen einer Datenbank „Bauunternehmen“ c) Festlegen der Integritätsbedignungen Statische 1) Dynamische Wertebereichseinschränkungen von Attributen - NOT NULL, DEFAULT, CREATE DOMAIN etc. 2) Eindeutigkeit von Attributwerten - UNIQUE, PRIMARY KEY 3) Fremdschlüsselbedingungen (vgl. Referentielle Integrität) - FOREIGN KEY – Klausel 4) Allgemeine Integr.-Bedingungen (Tabellenzusicherungen) - CHECK-Klausel (spalten- und tabellenbzw. zeilenbezogen) TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1) Trigger: Festlegung von ereignisgesteuerten Folgeaktionen für Änderungsoperationen (INSERT, UPDATE, DELETE) 2) Allgemeine (tabellenübergreifende) Zusicherungen (ASSERTION) – werden ohne direkten Kontext auf Schemaebene definiert 3) Datenbankprozeduren (Stored Procedures) – eine beliebige, in einer konventionellen Programmiersprache geschriebene Prozedur (d.h. Ausführung nur bei explizitem Aufruf) 1. ÜO Informationsmanagement Folie 17 Aufgabe 1: Erstellen einer Datenbank „Bauunternehmen“ c) Festlegen der Integritätsbedignungen Was passiert bei verletzten Integritätsbedingungen? 1) Standardreaktion: • ROLLBACK, d.h. alle Änderungen einer Transaktion werden zurückgenommen (Alles oder Nichts – Prinzip). 2) Spezifikation von Folgeaktionen (um Einhaltung der Integr.-Bed. zu erreichen): • SQL92: deklarative Festlegung referentieller Folgeaktionen (CASCADE, SET NULL, ...) • SQL99: Trigger (sind eine wesentlicher Mechanismus in DBS) • DB-Prozeduren 3) Verallgemeinerte Regeln für Folgeaktionen durch ECA-Rules (Event / Condition / Action) in aktiven DBS TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 18 Aufgabe 1: Erstellen einer Datenbank „Bauunternehmen“ c) Festlegen der Integritätsbedignungen CREATE TABLE Beschäftigte ( PersNr integer UNIQUE NOT NULL, Vorname char(40), Nachname char(50), Tätigkeit char(100)); konventionelle Definition des Primärschlüssels Nutzung von NOT NULL und UNIQUE als (spaltenbezogene) Integritätsbedingungen, z.B.: CREATE TABLE Beschäftigte ( PersNr integer PRIMARY KEY, Vorname char(40), Nachname char(50) Steuernummer char(10) UNIQUE, Tätigkeit char(100) NOT NULL); TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Wird dann als Schlüsselkandidat (oder auch Sekundärschlüssel) bezeichnet Folie 19 Aufgabe 1: Erstellen einer Datenbank „Bauunternehmen“ c) Festlegen der Integritätsbedingungen Festlegen des Wertebereiches für eine Spalte (Feld) mit der CHECK-Klausel: (in MS Access nur über Entwurfsansicht Feldeigensch.: Gültigkeitsregel) CREATE TABLE Beschäftigte ( PersNr integer PRIMARY KEY CHECK (VALUE BETWEEN 10000 AND 20000), Vorname char(40), Nachname char(50) Einschränkung des Wertebereiches Tätigkeit char(100) NOT NULL); für die Spalte PersNr: die ckeck-Klausel weist das DBS an, bei Eingabe der Daten deren Zulässigkeit gemäß der Gültigkeitsregel zu überprüfen TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 20 Aufgabe 2: Betonsorten Hinzufügen der Tabellen a) „Bauobjekte“ b) „Baumaschinen“ c) „Betonsorten“ Qualität Preis 1 C15/20 200,7 2 C20/25 196,5 3 C25/30 197,2 4 C35/45 185 Baumaschinen Bauobjekte ObjNr BNr Bauliches Objekt MNr Bezeichnung Typ Wert 1 Wohnpark I 1 Planierraupe PL 20/40 75000 2 Stadtvilla 2 Turmdrehkran TK 260 868000 3 Wohnpark II 3 Autokran ADK 60 650000 4 Einf.haus Wendt 4 Bagger BG 70 820000 5 Stadthaus 5 Bagger BG 62 760000 6 Einf.haus Wagner 6 Str.baumasch. H 1/7 68888 7 Mischfahrzeug HR 50 65000 TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 21 Aufgabe 3: Ändern der Tabelle „Baumaschinen“ Erweiterung um das Attribut „Stundensatz“ Baumaschinen MNr Bezeichnung Typ Wert 1 Planierraupe PL 20/40 75000 2 Turmdrehkran TK 260 868000 3 Autokran ADK 60 650000 4 Bagger BG 70 820000 5 Bagger BG 62 760000 6 Str.baumasch. H 1/7 68888 7 Mischfahrzeug HR 50 65000 TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 22 ALTER Mit der ALTER TABLE Anweisung können in vorhandenen Tabellen Spalten hinzugefügt, gelöscht und verändert werden. Hinzufügen einer Spalte: ALTER TABLE Tabellenname ADD Spaltenname Datentyp; Löschen einer Spalte: ALTER TABLE Tabellenname DROP Spaltenname; Ändern (des Datentyps) einer Spalte: ALTER TABLE Tabellenname ALTER Spaltenname Datentyp_neu; TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 23 Aufgabe 3: Ändern der Tabelle „Baumaschinen“ Erweiterung um das Attribut „Stundensatz“ a) ALTER TABLE Baumaschinen ADD Stundensatz single; TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 24 Aufgabe 4: Erweitern der Tabelle „Baumaschinen“ a) Eingabe der Werte mit INSERT b) Eintragen der Stundensätze: 1) 0,1 % des Wertes bei Wert < 100000 2) 0,03 % des Wertes bei Wert zwischen 100000 und 700000 3) 0,02 % des Wertes bei Wert > 700000 Baumaschinen MNr Bezeichnung Typ Wert Stundensatz 1 Planierraupe PL 20/40 75000 … 2 Turmdrehkran TK 260 868000 … 3 Autokran ADK 60 650000 … 4 Bagger BG 70 820000 … 5 Bagger BG 62 760000 … 6 Str.baumasch. H 1/7 68888 … 7 Mischfahrzeug HR 50 65000 … TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 25 Aufgabe 4: Erweitern der Tabelle „Baumaschinen“ a) Eingabe der Werte mit INSERT INSERT INTO Baumaschinen (MNr, Baumaschine, Typ, Wert) VALUES (1, “Planierraupe“, “PL 20/40“, 75000); … Baumaschinen MNr Bezeichnung Typ Wert 1 Planierraupe PL 20/40 75000 2 Turmdrehkran TK 260 868000 3 Autokran ADK 60 650000 4 Bagger BG 70 820000 5 Bagger BG 62 760000 6 Str.baumasch. H 1/7 68888 7 Mischfahrzeug HR 50 65000 TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Stundensatz Folie 26 UPDATE Mit der UPDATE Anweisung können vorh. Einträge (Datensätze) in Tabellen aktualisiert werden. UPDATE Tabellen_Name Name der betreffenden Tabelle SET Spalten_Name = Wert Zuweisung von Werten zu Spalten; Werte können über mathematische Operationen in Abhängigkeit der Werte anderer Spalten definiert werden WHERE Bedingung; TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Zuweisungsbedingung: definiert die Zeilen, in denen die entsprechenden Werte geändert werden sollen Folie 27 Aufgabe 4: Erweitern der Tabelle „Baumaschinen“ b) Eintragen der Stundensätze: 1) 0,1 % des Wertes bei Wert < 100000 2) 0,03 % des Wertes bei Wert zwischen 100000 und 700000 3) 0,02 % des Wertes bei Wert > 700000 2) UPDATE Baumaschinen SET Baumaschinen.Stundensatz=0.0003*Baumaschinen.Wert WHERE *) Baumaschinen.Wert>100000 AND Baumaschinen.Wert<=700000; *) alternativ: WHERE Baumaschinen.Wert BETWEEN 100000 AND 700000 Hinweis: INSERT nicht möglich, da hiermit neue Zeilen/ DatenSätze eingefügt werden und bestehende Datensätze nicht geändert werden können. TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 28 Aufgabe 4: Erweitern der Tabelle „Baumaschinen“ b) Eintragen der Stundensätze: 1) 0,1 % des Wertes bei Wert < 100000 2) 0,03 % des Wertes bei Wert zwischen 100000 und 700000 3) 0,02 % des Wertes bei Wert > 700000 1) UPDATE Baumaschinen SET Baumaschinen.Stundensatz=0.001*Baumaschinen.Wert WHERE Baumaschinen.Wert<100000; 3) UPDATE Baumaschinen SET Baumaschinen.Stundensatz=0.0002*Baumaschinen.Wert WHERE Baumaschinen.Wert>700000; TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 29 DROP & DELETE DROP TABLE Tabellenname; löscht Tabellen DELETE FROM Tabellenname WHERE Bedingung; löscht Datensätze TUD, Institut f. Bauinformatik Prof. Dr.-Ing. R.-J. Scherer 1. ÜO Informationsmanagement Folie 30 Anhang: Datentypen SQL-Datentypen in MS Access und ihre Spezifikation Quelle: http://office.microsoft.com/de-de/access-help/sql-datentypen-HA001231504.aspx?CTT=3 Anhang: Datentypen ANSI SQL-Datentypen, gleichwertige MS Access Datentypen und gültige Synonyme, Gleichwertige MS SQL Server Datentypen Quelle: http://office.microsoft.com/de-de/access-help/gleichwertige-ansi-sql-datentypen-HA001231447.aspx DB-Schemaarchitektur (nach ANSI) Nutzer-/Anwendungspez. Sichten auf das konzeptionelle Schema Auf Basis des DB-Modells entwickelte logische Beschreibung des Realweltausschnittes Legt Speicherungsstrukturen und Zugriffspfade bzw. Indexstrukturen für schnelleren Datenzugriff fest. Für (normalen) DB-Nutzer nicht relevant und nicht sichtbar.