Einführung Datenbanken und SQL

Jörg Walter

26. April 2001


Inhalt

 

 

 

Persistente Datenstrukturen: Hauptwege

Sichere Speicherung von Daten (mit schnellem Zugriff und über die Grenzen eines Prozesses und eines Rechners hinweg) erfolgt auf zwei Hauptarten:

Anforderungen und Grundfragen

Multipler Zugriff:
mehrere Prozesse wollen gleichzeitig Zugang;
 
Datenintegrität:
wenn mehrere auch schreibend zugreifen wollen;
 
Sicherheit
gegen Datenverlust;
Crash beim Schreiben;
Redundanz, Backup-Verfahren;
 
Zugriff auf Datenteilmengen:
lesen/ändern von wenigen Einträgen eines sehr großen Datensatzes;
 
Zugriffsrechte:
Wer darf diese Daten lesen, wer schreiben?
 
Datenstruktur:
muß vereinbart werden um obiges zu ermöglichen;
 
Datentypen:
Basistypen und Erweiterbarkeit der Datenstruktur;
Objektpersistenz OOP;
 
Anfragen:
Teilmengenauswahl mittels Vergleichsoperationen auf Datenstruktur;
Verknüpfen von verschiedenen Daten;
 
Leistungsfähigkeit, Skalierbarkeit:
Was, wenn viel mehr Daten anfallen u./o. von vielen angefragt werden.
Redundanz,
 
Portierbarkeit, Standards:
Abhängigkeit des Datenhaltungskonzept von SW/HW Herstellern,
Spezialisten; Wartbarkeit; Migration auf andere Platformen, s.o.;
Standardisierung;
 

Für einfache Problem ist die direkte Speicherung auf dem Dateisystem die kompakteste Lösung, s.u.


 

 

DBMS - Datenbankmanagementsysteme (DataBase Management Systems)

Vereinfachte Auswahlmatrix:

einfache Daten komplexe Daten
Abfrage relationales DBMS objektrelationes DBMS
keine Abfrage Dateisystem objektorientierte DBMS

 

Sind allgemeinere Datenstrukturen wünschenswert, so ist die Durchsetzung von Standards im OO-DB Bereich vielfach komplizierter und noch lange nicht abgeschlossen.

Anders im linken Bereich relationaler Datenbanken (RDBMS). Die Entwicklung geht auf Codd[#!Codd70!#] 1970 zurück und hat sich als der Standard, nicht nur im kommerziellen Bereich, etabliert.


 

 


RDB Standards: Was heisst SQL, ODBC und JDBC?

Sequencial Query Language (SQL):
Sprache zum Stellen von Anfragen an die DB, siehe 2
 
Open DataBase Connectivity (ODBC):
C/C++ API für R-DB Zugriff (von Mircosoft propagiert mit ANSI backing) Server- und Clientseite: Handling von Connections, Anfragen, Metadaten, Sequenzen von Resultatdaten;
 
Java DataBase Connectivity (JDBC):
Java-API fur R-DB Zugriff (von SUN propagiert)
 
Praktisch alle wichtigen Datenbankhersteller unterstützen SQL und bieten ODBC Treiber an. Ebenso sind direkte JDBC-Treiber oder JDBC-ODBC Bridge-Treiber zur Anbindung an RDBMS verfügbar. NEO/NST bietet einen ODBC-unit und einen ds_ODBC-Circuit an.

Verbreitet ist der SQL-2 (oder SQL-92) Standard. Bis auf Abweichungen im Bereich advanced features sind damit ``SQL-2 EntryLevel''-basierende Programme übertragbar auf Datenbanken wie z.B. MySQL, Oracle, Informix, Sybase. Dies macht das Erlernen und die Nutzung von SQL sehr attraktiv.
 


 

 


Ein erstes RDB Beispiel

Eine relationale Datenbank kann als eine Sammlung von 2-dimensionalen Tabellen gesehen werden. Hier die ersten 6 Zeilen der Tabelle Btyp 


mysql> select * from Btyp  limit 6;
+---------+--------+---------------------------------------+------+------+
| Id      | WKN    | Bez                                   | Pri  | Url1 |
+---------+--------+---------------------------------------+------+------+
| AAC.FSE | 869008 | AAC SOUTH-AFRICA RC-10SVG / FFM       |    0 |      |
| AAR.FSE | 880026 | ABN AMRO HLDG NV FL1,25 / FFM         |    0 |      |
| ABA.FSE | 886334 | ABB SF 50 / FFM                       |    0 |      |
| ABC.FSE | 865178 | ANHEUSER-BUSCH DL 1 / FFM             |    0 |      |
| ABE.FSE | 508860 | AUGUSTA BETEILIGUNGS-AG AKTIEN DM 5.0 |    0 |      |
| ABL.FSE | 850103 | ABBOTT LABS / FFM                     |    0 |      |
+---------+--------+---------------------------------------+------+------+
6 rows in set (0.02 sec)

Jede Tabelle hat einen (eindeutigen) Namen (Btyp). Jede Spalte einer Tabelle hat einen Namen (Id, WKN, ...) und einen Datentyp.

 

 

 

 


Einführung in die Datenbankabfragesprache SQL - Praktische Essentials


 

 

Kontaktaufnahme

Ein Client baut eine Verbindung zu einem laufenden SQL-Server auf, im folgenden ein MySQL-Server auf janus.techfak.uni-bielefeld.de unter dem SQL-Benutzernamen guest und Password *****=guest (die Authentisierung ist vollkommen getrennt von der Unix-Useradministration). 

    mysql -h janus -u guest -p 
    *****  
    use test;
Dies startet einen terminalbasierten SQL-Client und wählt die (Default-) Datenbasis ``test'' aus.

Die vollständige MySQL-Dokumentation

Zur Schreibweise

Was gibt es an Datenbasen, welche Tabellen darin, mit welcher Struktur? 


mysql> SHOW database;   ...
mysql> USE test;        ... 
mysql> SHOW tables;     ...
mysql> DESC Btyp;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| Id    | varchar(10)           |      | PRI |         |       |
| WKN   | mediumint(8) unsigned |      |     | 0       |       |
| Bez   | text                  |      |     | NULL    |       |
| Pri   | int(11)               | YES  |     | NULL    |       |
| Url1  | text                  | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
Das Erzeugen von neuen Datenbasen ist mit Einträgen in die Permissiontables verbunden und sollte dem DB-Admin überlassen werden.

Details zur SQL-Syntax siehe hier.


 

 

Erzeugen von Tabellen: CREATE TABLE 


    CREATE TABLE <table_name>
       (<column1_name> <column1_type> <column1_constraint>,
        <comumn2_name> <column2_type> <column2_constraint>, ... );
Beim Erstellen einer Tabelle (CREATE TABLE) wird jede Spalte benannt und ihr Datentyp festgelegt. Spaltenconstrainsts sind z.B. Festlegung des Primärschlüssels [PRIMARY KEY], Verbieten von Doppelnennung [UNIQUE], Verbieten des NULL-Wertes [NOT NULL], Defaultwerte (Wert oder Autoinkrementierung). Ferner kann Indexbildung (siehe 4.2) bestellt werden.


 

 


Atomare Datentypen: speicherbetont

Die Längenangabe ist bei den meisten Zahlen eine Displaylänge.
SQL Bytes Bemerkung
[ Integer ]    
TINYINT[(dlength)] [UNSIGNED] 1 Integer [-128..127] o. [0..255] UNSIGNED
SMALLINT[(dlength)] [UNSIGNED] 2 Integer [-32768..32767]/[0..65535]
MEDIUMINT[(dlength)] [UNSIGNED] 3 Integer [-8388608..8388607]/[0..16777215]
INTEGER[(dlength)] [UNSIGNED] 4 Integer =INT [-2147483648..2147483647]/[0..4294967295]
BIGINT[(dlength)] [UNSIGNED] 8 Integer [-2**63..]/[0..2**64-1]
[ Float ]    
FLOAT[(dlength,decimals)] 4 Float 0+-[1.2e-38..3.4e38]
DOUBLE[(dlength,decimals)] 8 Float 0+-[2.2e-308..3.4e308] (=REAL)
DECIMAL(length,decimals) [UNSIGNED] length Festkommazahl (unpacked in char)
[ Text +- sortable ]    
CHAR(length) [BINARY] length fix length array of Char (sort+compare case insensitive unless BINARY)
VARCHAR(length) [BINARY] * dyn (max) length array of Char
TINYBLOB 1+* opaque Text array (max length 255) =TINYTEXT
BLOB 2+* opaque Text array (max length 65535) =TEXT
MEDIUMBLOB 3+* opaque Text array (max length 16777215) =MEDIUMTEXT
LONGTEXT 4+* opaque Text array (max length 4294967295)
[ Special ]    
ENUMERATION 1,2 Aufzählung (max 2**16 viele)
SET 1-4 Mehrere aus Liste von max 64 Möglichen (intern Bitmaske)
[ Zeit ]    
DATE 3 Datum
TIME 3 Uhrzeit
TIMESTAMP 4 Datum und Uhrzeit
DATETIME 8 Datum und Uhrzeit


 

 

Zeilen hinzufügen: INSERT 


     INSERT [INTO] <table> [ (column_name,...) ] VALUES (expression,...);

Ein Beispiel 


  CREATE TABLE mitarbeiter (
       id     INT  PRIMARY KEY,
       vname  VARCHAR(15),
       nname  VARCHAR(30),
       gdate  DATE,
       gehalt FLOAT,
       plz    INT,
       str    VARCHAR(30) );
  INSERT INTO mitarbeiter VALUES(007,'James','Bond', '1952-03-02',1600000, 33600,'Goldfingerweg 17') ;
  INSERT mitarbeiter (id,nname,plz) VALUES(0815,'v. Sinnen',33304);
  INSERT mitarbeiter (id,nname,plz) VALUES(0816,'v. Sinnen',33304);
  CREATE TABLE orte ( plz INT PRIMARY KEY, ortsname varchar(30) );
  INSERT orte VALUES (33600, 'Bielefeld');
  INSERT orte VALUES (33304, 'Hintertupfing');
  INSERT orte VALUES (61234, 'Stöckelding);
  INSERT orte VALUES (01238, 'Mysterie');
Mit REPLACE statt INSERT wird ersetzt, falls es zu einem Duplikat kommt.


 

 

Tabelle lesen: SELECT .. 


     SELECT  select_expression,... FROM table_references
        [WHERE where_definition]
        [GROUP BY col_name,...]
        [ORDER BY  {col_name} [ASC | DESC] ,...]
        [LIMIT [offset,] rows]

  SELECT * FROM mitarbeiter;
  SELECT vname, nname FROM mitarbeiter ORDER BY nname;
  SELECT vname, nname, gehalt FROM mitarbeiter WHERE gehalt<1000;
Select_expression:
*, kommaseparierte Liste von Spaltennamen, Expressions. (Aliasbildung mit [[AS] <aliasname>]. Ausfiltern von Mehrfachrückgabe von identischen Zeilen mit DISTINCT).
table_references:
Tabellenname oder Tabellen-Joins (s.u.)
WHERE...
leistungsfähige Restriktion / Selektion. Die where-clause ist ein beliebiger mathematischer, logischer, textueller oder zeitlicher Verleich. Erlaubte Operatoren sind u.a.:

GROUP BY...:
Zeilen mit gleichem Inhalt werden blockweise zusammengefasst. Für die select_expressions stehen dann Sonderfunktionen zur Verfügung, die diese Blöcke aggregieren: 

mysql> SELECT plz, COUNT(gehalt),MIN(gehalt),MAX(gehalt),AVG(gehalt),STD(gehalt)   FROM mitarbeiter GROUP by plz;
+-------+---------------+-------------+-------------+----------------+-------------+
| plz   | COUNT(gehalt) | MIN(gehalt) | MAX(gehalt) | AVG(gehalt)    | STD(gehalt) |
+-------+---------------+-------------+-------------+----------------+-------------+
|  NULL |             1 |     2312.00 |     2312.00 |    2312.000000 |    0.000000 |
|  2312 |             0 |        NULL |        NULL |           NULL |        NULL |
| 33600 |             2 |  1812800.00 |  1812800.00 | 1812800.000000 |    0.000000 |
+-------+---------------+-------------+-------------+----------------+-------------+
3 rows in set (0.00 sec)

ORDER BY...:
Sortieren auf- oder absteigend nach geg. Spalte
LIMIT BY...:
Rückgabedatenmenge in Zeilenzahl eingrenzen
Statt ein SELECT-Ergebnis zum Client zurückzutransportieren, kann es auch in die Datenbank eingefügt werden, siehe nächstes Beispiel.


 

 


Tabellenverknüpfung: JOIN | ,

table_reference1 , table_reference2 oder table_reference1 JOIN table_reference2 an Stelle der table_references im Select-Statement führt ein (semantisch identisches) JOIN von zwei Tabellen durch. Normalerweise wird in der WHERE-clause bestimmt, wie die Tabellen verknüpft werden. 

  INSERT INTO liste (name,plz,ort) 
    SELECT nname,plz,ortsname  FROM  mitarbeiter AS m,  orte 
       WHERE    m.plz=orte.plz
       GROUP BY nname ;


 

 

Zeilen ändern: UPDATE .. SET 


     UPDATE <table> SET column_name1=expression1 [,...] [WHERE ...];
    
  UPDATE mitarbeiter SET nname='Einstein', plz=12345 WHERE id=0816;
  UPDATE mitarbeiter SET gehalt = gehalt * 1.03;
  UPDATE mitarbeiter SET gehalt = 1000 WHERE gehalt IS NULL;


 

 

Zeilen löschen: DELETE FROM .. WHERE 


     DELETE FROM <table>   [WHERE ...];
    
  DELETE FROM mitarbeiter  WHERE id=4711;


 

 

Bulk Save/Load Operations

Zum Laden und Saven von größeren Datenblöcken gibt es meist spezielle Operationen. Bei MySQL gibt es eine ASCII-file Konversion mit SELECT .. INTO OUTFILE '<filename>' .. und umgekehrt mit LOAD DATA [LOCAL] INFILE '<filename>' INTO TABLE <tablename>. 

  SELECT * INTO OUTFILE '/tmp/testfile_m.txt' FROM orte;
  CREATE TABLE city (plz int, oname varchar(25));
  LOAD DATA INFILE '/tmp/testfile_m.txt' INTO TABLE city;


 

 

Index erstellen, Tabellenstruktur ändern

Mit zunehmender Tabellengröße werden die DB-Anfragen immer langsamer. Das Problem verschärft sich exponentiell mit der Zahl von table-joins die in einer query nötig werden. Abhilfe schaffen B-Tree Indizes auf den Spalten (oder Spaltenkominationen), die häufig in WHERE-Klausen benötigt werden. Angenehmerweise übernimmt die Indexverwaltung komplett das DBMS, man muß den INDEX nur bestellen (siehe auch 4.2).

   CREATE TABLE <tn> (<c1name> <c1typ> .. INDEX       (c?name [,..]) ..);
   CREATE TABLE <tn> (<c1name> <c1typ> .. PRIMARY KEY (c?name [,..]) ..);
   CREATE TABLE <tn> (<c1name> <c1typ> .. UNIQUE      (c?name [,..]) ..);
   ALTER TABLE <tn> ADD INDEX (c?name [,..]);
   ...


 

 

Tabellen Löschen

Zur Vollständigkeit des Tabellenlebenszyklus: Das Ende kommt mit 

     DROP TABLE <tablename> ;


 

 

Weitere Features

 

 

   


Das relationale Datenbankmodell - Theorie-Essentials

 

 

 

Database - Datawarehouse - Datamart

Oft findet/fand man in großen Unternehmen eine sehr heterogene, eher unkoorinierte Datenhaltung vor. Der Prozess des Datawarehousings beinhaltet die Systematisierung, Bündelung und Dokumentation der Datenhaltung, so daß Datensätze in Analogie zu einem (oft zentralen) Warenlager kontrolliert bewegt und fortlaufend gelagert werden. Ziel ist ein vereinheitlichtes Datenmodell des gesamten Unternehens zu finden, u.a. zum Zweck der Entscheidungsunterstützung (decision supports und buss-word MIS = Management Information System).

Die Technologie schnell und effektiv Überblick über das aktuelle Geschehen, über Trends und auch historische Informationen zu bekommen wird oft mit dem Schlagwort OLAP - Online Analytical Processing - verknüpft (siehe 4.1). Fundament ist eine solide Datenbasis.

Das Datawarehousing nur zum Teil eine informatisches Problem, zum andern ein Problem der Einbettung in Unternehmensstruktur und Geschäftsprozesse (Kontrolle der Geschäftsprozesse, Einbettung in Enterprise Resource Management / Planing Systeme ``ERM''/``ERP'' etc., z.B. SAP). Die Kooperation der Dateneigner ist entscheidend (Autonomie und Kontrolle von Unternehmensteile ist oft auch ein Politikum). Der Aufbau eines größeren Datawarehouse ist oft ein sehr umfangreiches Unterfangen (Dauer, Kosten).

Der Begriff Datamart bezeichnet die separate Speicherung eines Subsets des Datawarehouses für ein bestimmten Zweck. Zum Beispiel als aggregierte Datenbasis des Controllings auf verschiedenen Geschäftsebenen, für Marketeing, für MIS und Datamining. Dabei ist eine Trennung vom operativen Tagesgeschäft vorteilhaft, oft auch unumgänglich. Häufige Gründe:


 

 


OLAP = Online Analytical Processing

Technologie für schnellen multi-dimensionalen Zugriff auf und Darstellung von Daten zwecks Analyse. Die Quelldaten sind meist in einem Datawarehouse oder Datamart gespeichert und werden teilweise vorher in gewünschter Weise in Cubes aggregiert. Grund: Zugriff auf Rohdaten kostet oft zuviel Zeit.

Beispiel und Begriffe:

Drill Down / Drill Up:
Navigationstechnik durch verschiedene Levels der Aggregierung in den verschiedenen Achsen der möglichen Darstellung. Z.B.:

Zeit-Achse Regionen-Achse Sortiment-Achse ...-Achse
Gesamt Alles Alles ...
Mehrjahresdaten Kontinente Sparten ...
Jahresdaten Länder Fachabteilungen ...
Quartalsdaten Bundesländer Typkategorien grob  
Monatsdaten Regionen Typkategorien fein  
Wochendaten Städte Artikel  
Tagesdaten Filialen    
 

Slice and Dice:
Hypercubes (Aggregationen) mit Restriktionen auf selektierten Kategorien (Auswahl in jeder Achse möglich; z.B. Entwicklungen im Jahr 1999 in Bayern versus NRW);
Pivottechnik:
Wechseln von Spalten und Zeilen (und umgekehrt) als auch Hierarchiefolge in multidimensionalem Tabellenbrowser. Bezeichnet auch das Auswählen von Dimensionen in einer hochdimensionen Datenstruktur.

OLAP Speichermodi:
Die Zwischenergebnisse (Ort, Einheiten, Aggregationswerte) werden in Die Speicherung muß mit dünn besetzten Cubes effizient umgehen können (Curse-of-dimensionality).

Aufgrund der Interaktivität bei OLAP sind stabile Antwortzeiten eine wichtige Voraussetzung.


 

 


Zugriffsgeschwindigkeit, Indextechniken

Parallele DBMS:
Multiprozessorarchitekturen (insb. mit shared memory Architektur);
Datenbankentwurf:
Schemataaspekte siehe u.a. 3;
Table Partitioning:
DBMS-internes Aufteilen der Tabelle auf Speicher-/Fileblöcke.

Z.B. Row-partitioning aufteilen der Daten z.B. nach Jahren oder Monaten (Gefahr von hot spots). (MySQL Tabellentyp: MERGE)

Bei spaltenreichen Tabellen ist das column-partitioning vorteilhaft. Querys beschränken sich meist auf ein oder wenige Spalten, damit vermeidet man eine vollen table-scan;

Balanced-Tree Index:
indiziert selektierte Spalten und zeigt auf die Zeile wo die Einträge zu finden sind. B=Balanciert steht für eine ausgewogene Suchtiefe (=Zugriffszeit), unabhängig vom Schlüssel. Geclusterte Indices (mehrere Spalten gemeinsam) sinnvoll, wenn query-Struktur bekannt - weniger für ad-hoc-query;

Bit-mapped Index:
Der Schlüssel wird in Bitmasken kodiert. Beispiel:

row-Nr M/F 'ledig' 'verheiratet' 'geschieden' 'verwitwet' NRW BY BW BB MP
1 1 1 0 0 0 1 0 0 0 0
2 0 1 0 0 0 0 0 1 0 0
3 1 0 1 0 0 0 1 0 0 0
4 0 0 1 0 0 1 0 0 0 0
5 1 1 0 0 0 0 1 0 0 0

Das Finden von Verheirateten in NRW filtert alle mit dem Indexeintrag *010010000 heraus;

Bemerkung: Der Index wird vom query-optimizer zur Erledigung der SQL-Anfrage eingespannt. Anwesenheit und Technik des verwendeten Index betrifft nicht die Anfrage - sondern die Anwortzeit.


 

 

Metadaten - Informationen über Daten

Beim Zusammentragen von Datensätzen sollte nicht die Speicherung von Metadaten unterschätzt werden. Dies ist auch wichtig für die spätere Beurteilung der Signifikanz der Daten. Dabei können u.a. folgende Elemente wichtig sein:

Jeder Datensatz sollte eine Data Describtion Dokument zugewiesen haben. Es besteht meist aus:

Und für jede Spalte


 

 

Datenbankschema - zwei Begriffe aus dem Bereich DW

Im Gegensatz zur Theorie der Normalformen (3) werden Datawarehouses aus Performanzgründen meist denormalisiert angelegt.
Star-Schema:
relationale Datenbankstruktur mit den Fakten in einer zentralen, großen Fakttabelle mit direkten Verknüpfungen zu mehreren Dimensionstabellen gehalten werden. Querys selektieren die Zeilen-ids der Fakttabelle, die zuletzt gejoint wird.
Snowflake-Schema:
Erweiterung des Star-Schema: mehreren Primärdimensionentabellen bilden die Datenbasis. Zusätzlichen Dimensionstabellen ergänzen diese.


Jörg Walter