Fach:Informationssysteme(IS-M) Stundenlog Remo
Aus StudyWiki
Stundenlog:
Informationssysteme (IS-M)
3.9.2007 Einführung in DB's
Einführung
Kontakt
Karl Meier
karl.meier@kasec.ch Ausfälle
10.9.07 Knabenschiessen
22.12.07-6.1.08 Weihnachtsferien Unterlagen
Www.schulstoff.ch
Buch: ISBN: 978-3-8273-7266-6
Preise:
www.buch.ch 50 Fr.
www.amazon.de 30 Euro. Benotung
* Eine Semesterprüfungen welche 50% zählen (ca. 2.5 h) * eine Abschlussprüfung welche auch 50% zählt (ca. 3 Stunden) (wird in Richtung dieser Note gerundet)
- An den Prüfungen können alle schriftlichen Unterlagen verwendet werden (nur Papier und Bücher)
Prüfungen
* Montag, 12.11.2007 Semesterprüfung
Montag 12.11.07, 19:00 - 21:30 Uhr
Ort: Zimmer ...
Inhalt: DB-Design, Normalisierung, SQL Abfragen, Transaktionen
Zur Prüfung sind sämtliche schriftlichen Unterlagen erlaubt (open book).
* Montag, 3.12.2007 Nachholprüfung
Mittwoch ..., 18:30 - 21:00 Uhr
Termin noch nicht definitiv festgelegt (28.11. oder 5.12)
* Montag 21.01.2008, 18:30 - 21:30 Uhr
Termin noch nicht festgelegt
( Prüfungsperiode HS0708: 21.01.-01.02.08 )
Ort: Zimmer ...
Inhalt: kompletter Vorlesungstoff
Die Schlussprüfung ist schriftlich und dauert 3 Stunden.
Zur Prüfung sind sämtliche schriftlichen Unterlagen erlaubt (open book).
Behandelter Stoff
Folien „1 Einführung Datenbanken“ Datenbankstrukturen
*
Hirarchische Datenbank: Mit Knoten umgesetzt *
Netzwerkdatenbanken: Mit Beziehungen in nur EINE Richtung *
Relationale Datenbanken: Tabellenform (Relational) mit Beziehungen.
-> Datensatz (Zeile, Row oder Tupel)
-> ERD
-> SQL
*
Objektorientierte Datenbank: Objekt mit Attribut, Beziehungen, Methoden -> Komplexeste Strukturen könnnen verwaltet werden. * Objektorientierte Rationale Datenbanken
Relationale Datenbanken Management System Abgekürzt RDBMS -> 12 Regeln nach Codd = relationale DB 3-Ebenen-Modell
1. Externe Ebene: DQL und DML (View) -> ERM 2. Konzeptionelle Ebene: DDL (Tabellen und Datenbanken erstellen) -> Rel. DB 3. Interne Ebene: DCL (Handling mit Datenspeicher, Administrator...) -> Performance, Index, ...
Datenbankentwurf
1. Analyse (1)* 2. Design/Redesign (10)* 3. Implementation (/Testing) (100)* 4. Betrieb (1000)*
- Gewichtung/Kosten bei Fehlerkorrekturen
Zentralisierte DBS
* DB und Appl. auf einem rechner * thin client (appl. auf einem Server) * fat client (appl. auf dem Client) * ...
Verteilte DBS
* Teildatenbanken auf getrennten DBMS verteilt * Mechanismen um die verteilten Daten zusammen zu führen * offline fähig (zentale und filliale) / gute Performance * schwere administrierbarkeit * ... -> siehe Folien für mehr Nach teile und Vorteile gegenüber zentraler DBS
Datenverarbeitung
* homogene Verteilung
* heterogene Verteilung (verschiedene Datenbankensysteme)
Multidatenbanksystem (MDBS)
* Datenbankreplikation
tier-Architekturen 1-tier: (GUI, Applikation, DBMS) 2-tier: (GUI, Applikation) (DBMS) -> fat client 3-tier: (GUI)(Applikation)(DBMS) -> thin client
Bei 3-tier sind alle komponenten auf 3 Rechner verteilt (2-tier 2 Rechner, 1-tier ein Rechner). Vorteil: Jeder DB Admin muss sich nur um den DB Server kümmern (flexibel, skalierbar...). jedoch etwas Teuer. 17.9.2007 Analyse und Design Repetition 3 Phasen Modell ( beschreibung des Aufbaus einer Datenbank) Phasenmodell der Datenbankentwicklung (Buch Kapitel 2) Aspekte
1. Logische Struktur 2. Physische Struktur 3. Zusätzliche Bedingungen
Entwickungsprozess Design->Implementation->Betrieb->Analyse->Design->... Anforderungsanalyse • Anforderungskatalog / Lastenheft (vom Auftraggeber) • Pflichtenheft (vom Auftraggeber) -> vertraglich Verbindlich • Benutzergruppen • Anwendungen • Prozesse / Vorgänge • Randbedingungen • Dialog mit Anwendern Inhalt eines Pflichtenheftes
1. Zielbestimmung: muss / soll / kann und Abgrenzungen
2. Produkteinsatz: Anwendungsbereiche / Zielgruppen / Betriebsbedingungen
3. Produktübersicht: kurze Übersicht über das Produkt
4. Produktfunktionen: genaue und detaillierte Beschreibung
5. Produktdaten: langfristig zu speichernde Daten aus Benutzersicht
6. Produktleistungen: Anforderungen bezüglich Zeit und Genauigkeit
7. Qualitätsanforderungen
8. Benutzeroberfläche: grundlegende Anforderungen, Zugriffsrechte
9. Nichtfunktionale Anforderungen: einzuhaltende Gesetze und Normen, Sicherheitsanforderungen, Plattformabhängigkeiten
10. Technische Produktumgebung: Software / Hardware / Organisatorisches / Schnittstellen
11. Spezielle Anforderungen an die Entwicklungs-Umgebung
12. Gliederung in Teilprodukte
13. Ergänzungen Konzeptioneller Entwurf
* Modelierung der Sichten * Integration in Gesamtschema * ER - Modell (ERM) * UML Diagramme * Datenintegrität
Implementierung
* Physischer Entwurf (Raid 5?....) * Datenbankerstellung (Tabellen anlegen) * SQL * Datenkonvertierung * Datenimport
zu Beachten! Performance, Datensicherheit, Wartbarkeit Test Nicht zu vergessen die Qualtitätsicherung durch Test (DB Abfragen, Systemtest, Benutzertest, Anforderungen) Betrieb
* Wartung * Änderungen * Administration von DB und Daten
Datenmodelation (Konzeptioneller Entwurf) (Buch Kapitel 3)
*
Abstraktionsprozesse
* ERM oder auch ERD genannt (unabhängig vom Datenmodell)
- Entitäten (Substantiv, bedeutendes Objekt aus der Realen Welt):
- Konvention: Box
- Instanz = Datensatz (Z.B. Entität Mitarbeiter hat die Instanz Hans Müller)
* Beziehungen/Relationship
- Optionalität (Kann/Muss)-> "Kreis"
- Kardinalität (eine oder Mehrere)->"Krähenfuss"
- Andere Notation: 1:1 (muss-muss), m:n (kann-kann), 1:m (muss-kann)
- Immer genau zu Prüfen: 1:1 und 1:m, muss-muss -> Achtung DB Technisch muss es auch aufgehen!
* Attribute
- Beschreiben die Entität
- Zusammen gesetzte Namen auflösen
- einziger Wert für jede Instanz
- Formatinformationen (=Datentyp)
- Domäne: zulässiger Wertebereich der Attribute
* Spezialitäten
- Verknüpfungsentitäten bilden bei m:n Verknüpfungen (kann schon im ERD gemacht werden)
- Rekursive Entitäten
Aufgaben Aufgabe Videotheke:
* Entitäten bilden (achtung ENUM Typen verwenden)
* Relationen einzeichnen * Attribute zu den Entitäten ergänzen * UID erstellen (oder zusammensetzen aus mehreren Atributen) = Primärschlüssel
Zusatzaufgaben von Präsentation
* M:N Beziehung auflösen
Praxis DB Designer:
* Fabforce (dbdesigner 4) -> wird nicht mehr weiter entwickelt * mysql workbench -> inkludiert dbdesigner 4, ist aber noch beta * DBVisulizer -> Java basiert auf jeder Platform, für verschiedene DB's!
24.9.2007 ER Diagramm ER Diagramm Tücken
*
Zeit
- History -> dazu muss eine Verknüpfungsentität eingeführt werden. Wobei ein Zusammengesetzter Primärschlüssel entsteht.
- Abrundfalle -> Bei z.B. der letzte Mitarbeiter aus einer Abteilung austritt, gehört der Firmenwagen plötzlich keiner Abteilung mehr an. Das Problem muss umgangen werden
*
Verbindungen
- Konvergenz ->Vereinfachung des Modells, aber nicht zu stark! (Beziehungen Reduzieren, Verschachtelte Subtypen, rekursion)
- Divergenz -> Detaillierung des Modells
*
Exlusive Entitäten
- Supertyp/Subtyp -> Es kann nu reine Tabelle erstellt werden, jedoch sind jenachdem nur vom Subtyp die Attribute abgefüllt.
* Qualität -> Am Ende alle Entitäten, Attribute, Beziehungen und Domänen kontrollieren und Fragen stellen.
Beziehungstypen
* unär -> auf sich selbst * binär -> zwischen zwei * trinär -> zwischen drei
Nicht Prüfungsrelevant:
* identifizierend -> durchgezogen Linie, bei Primärschlüssen die Fremschlüssel beinhalten * nicht identifizierend -> gestrichelte Linie, wenn alle Entitäten einene eigenen Priärschlüssel haben
Fazit: nur Eckige Boxen mit durchgezogenen Linien verwenden
Relationale Datenmodell
12 Regelen nach Codd
Begriffe
Domäne = bennante Menge von Atomaren Werten
Relation = Untermenge des kartesischen Produktes von einer oder mehreren Domänen (R <= D1 x D2 x D3....)
Zeile = Tupel
Spalte = Attribut
NULL != 0 und ""
Enitätsintegrität = gültigen Priärschlüssel (nicht NULL oder bereits bestehend)
Referenzielle Integrität = Gültigen Fremdschlüssel.
Vereinigung = R U S, R vereinigt mit S (UNION)
Differenz = R - S, R abzüglich S (MINUS)
Kartesischen Produkt = R x S, Menge aller möglichen Kombinationen
Projektion = extrahiere bestimmte Attribute (vertikal) -> vertikale Teilrelation
Selektion = extrahiere bestimte Tupel -> horizontale Teilrelation
Schlüssel
Primary Key
* einer Pro Relation * eindeutig * künstlicher möglich (ID) Alternate Key
* eindeutiger wert (unique), jedoch nicht als schlüssel verwendet Foreign Key
* Primay von einer anderen entität
Transformation
* Entitäten werden zu Tabellen * Attribute zu Spalten * UID werden zu Primärschlüssel * Beziehungen zu Fremdschlüssel * Exkl. Entitäten werden zu Tabellen * bei 1:1 kann gewählt werden wo der FK reingenommen wird * bei 1:n kann eine Beziehungsentität genommen werden, oder besser den FK in die "n"-Tabelle nehmen (keine Zusätzliche Tabelle nötig) * m:n werden mit Verknüpfungsentität aufgelöst mit den Beziehungen 1:n * Rekursive Beziehung wird durch ein FK (Zeiger) gelöst, der auf den PK zeigt.
Exklusive Entitäten
*
1. Supertype: Alle Attribute (der Subtypen) in eine Entität nehmen. wobei die Attribute dann Optional abgefüllt werden können. Zusätzliches Feld ebenfalls nötig für die Unterscheidung von Subentitäten -> eine Entität
2. Subtypen: In jeder Subentität die Attribute der Superentität einfügen. -> zwei Entitäten
3. Implizites Subtyp-Design: Subtypen zu eigenen Entitäten bilden und den PK der Superentität bei allen Sybtypen als FK einfüge
-> Fazit: Es wird die Lösung gwählt: Wo sind mehr Attribute? Auf welche Entitäten hat es mehr Relationen (Super oder Sub)?
Normalisierung Ziel der Normalisierung:
* Qualtiätsicherung * Anomalien beheben * Redundanzen vermeiden! * Gewährleistung der Konsistenz * einfache Datenpflegen
0. Normalform
Nicht normalisierte Tabelle. Tabelle ist nicht normalisiert, wenn z.B ein Werteliste in einem Attribut steht (Werte der Attribute sind nicht Atomar)
1. Normalform
Alle Attribute müssen einen atomaren Wert beinhalten. = Jede Zelle beinhaltet einen Wert.
Praxis: Primärschlüssel definieren/einführen
2. Normalform
Sie muss in der 1. Normalform sein und
nur nicht-Schlüssel-Attribute die vom Primärschlüssel abhängig sind dürfen in einer Tabelle sein.
Praxis: Tabellen aufteilen FK Schlüssel einführen, Alle Attribute sind vom PK abhängig, aber nicht direkt.
3. Normalform
Sie muss in der 2. Normalform sein und
keine transitive Abhängigkeiten mehr bestehen (Falls ein Attribut nur zu einem Teil vom Schlüssel abhängig ist, bildet dieser eine eigenen Tabelle)
Praxis: Tabellen weiter aufteilen und FK Schlüssel einführen
Hausaufgabe
ERD erstellen von dem Aufgabenblatt.
1.10.2007 Aufgaben und Besprechung Leitfaden für die Prüfung ER Es müssen keine FK eingezeichnet werden, sowie auch N:M Verbindungen sind zulässig, oder sogar erwünscht. Ansonsten wird unübersichtlich. Tabellenschema Keytype Attribut Datatype Eigenschaften (NN oder nichts) Beispieldaten FK/PK Name String NN/" " Muster Normalformen
* Nicht vergessen die Begründung angeben (nur wenn Verlangt natürlich) * In der Form angeben, damit schneller geht! -> Tabellenname: PK, Attribut1(FK), Attribut2 * ER zeichnen fürs Verständnis, wenns unübersichtlich wird
Zusätzliche Übungen Buch Kapitel 4 Aufgabe 4.7 - 4.9 Abhängigkeiten Aufgabe 4.10 - 4.16 Normalformen Fehler in den Lösungen 4.7: Es müsste nach der Beschreibung nur die Produktnr als eindeutiger Schlüssel bestimmt sein -> Arbeitszeiten:Mitarbeiternr, Maschienennr, Arbeitszeit 4.10: Tabelle Arbeitszeit: MitarbeiterNr und MaschienenNr müssten auch als schlüssel maktiert sein 8.10.2007 Funktionen und einfache Abfragen Implementierung mit SQL Einloggen bei HSZ 1. ssh einloggen bei hsz 2. mysql -h margaret -p 3. Passwort: FooBar -> wurde geändert ind f.... 4. show databases; 5. use "databasename"; 6. show tables; 7. select * from .........;
-> Passwort ändern: Einloggen und folgenden Befehl absetzen:
set password=password('MeinNeuesPasswort');
Befehle
* create database [db name]; * create database if not exists [db name]; (bricht dann nicht mit einem Fehler ab, falls die db bereits existiert) * use [db name]; * drop database [db name]; * drop database if not exists [db name]; (bricht dann nicht mit einem Fehler ab, falls die db nicht existiert)
SQL Structured Query Language
* Relationalealgebra * Standartiserung
Sprachumfang von SQL
* DDL: erstellen von Dbs * DQL: datenbabfrage * DML: datenmanipulation * DCL. sicherheit und berechtigungen
Tabellen
* create table [tabellen name] (id INTEGER NOT NULL, vname VARCHAR(100), name VARCHAR(100), adr TEXT);
* Datentypen:
- tinyint
- smalint
- mediumint
- int, integer
- bigint
- float
- double
- real
-....
* Optionale Parameter
- NOT NULL
- NULL
- DEFAULT [wert]
- AUTO_INCREMENT
- CONSTRAINT (Rahmenbedingung)
- CREATE DOMAIN (Benutzerdefinierte Datentypen, der in der ganzen Datenbank zur Verfügung steht -> ENU und SET sind nur in der Tabelle gültig)
* Tabelle Modifizieren
- ALTER TABLE -> meistens wir der index aus performance gründen noch hinzugefügt
-> hinzugefügte constraints gelten nicht für die bestehenden datensätze (ausser sie werden geändert)
- DROP TABLE [tabellennamen];
INFO: Beschreibung der Tabelle anzeigen: desc [tablellen name];
-> Siehe Buch Kapitel 6 Schlüssel / Indizes Primary Key ist automatisch indiziert Schlüssel zur identifikation eines Datensatzen Typen: Primärschlüssel, sekundärschlüssel (wert der unique ist, also ein pk kandidat ist -> UNIQUE KEY setzen), Fremdschlüssel
Fremdschlüssel: CREATE TABLE ... .... INDEX atttributbezeichnung (attribut), FOREINGN KEY (Tabelle) REFERENCES referenztabelle(attribut); -> Index muss bei Fremdschlüssel gesetzt sein! -> mit ON UPDATE oder ON DELETE werden in imgekehrter Richtung die Tabellen updated, bzw. gelöscht. Index Geeignet für Suchen in grossen Datenmengen, jedoch langsam bei Daten, die oft ändern. -> Indizes erst nach einfügen der Daten erstellen, z.B bei grossen Änderungen. Primary Key und Unique Key werden immer auch indeziert. Unique = Unique Index -> Löschung erfolgt mit: ALTER TABLE tabelle DROP INDEX schlüsselname;
-> Siehe Buch Kapitel 7 15.10.2007 Funktionen und Abfragen Besprochene Folien 8_Daten.pdf 9_Einfache_Datenabfragen.pdf 10_Funktionen_in_Abfragen.pdf
Daten modifizieren
* Daten einfügen
o INSERT INTO tabellenname (feld1, ..., feldX) VALUES (wert1, ..., wertX);
+ Reihenfolge egal
o INSERT INTO tabellenname VALUES (wert1, ..., wertX);
+ Reihenfolge muss richtig sein
+ alle Daten müssen abgefüllt werden
* Daten von bestehender Tabelle in eine neue Abfüllen
o SELECT INTO tabelle1 SELECT feldx FROM tabelle WHERE feldy = ...;
* Daten aktualisieren
o UPDATE ... SET feld1 =..., feld2 = ..., feldX = ... WHERE attribut = .. ;
* Daten Löschen
o DELETE FROM tabelle WHERE feld = ...;
Übungen Datenbank erstellen:
* source bibliotheke.sql * \. bibliotheke.sql * oder einfacht copy paste in mysql>-Prompt
-- Aufräumen --
DROP TABLE IF EXISTS t_baelle;
-- Erstelle die Tabelle mit baelle mit id und farbe (rot, weiss, blau) -- CREATE TABLE t_baelle
(id SMALLINT NOT NULL AUTO_INCREMENT,
farbe ENUM('rot','weiss','blau'),
PRIMARY KEY(id));
-- Füge die Spalte typ hinzu --
alter table t_baelle add type varchar (30);
-- Daten abfüllen -- insert into t_baele (type, farbe) values ("Gymnastikball", "rot"), ("Gymnastikball", "blau"), ("Gymnastikball", "weiss"), ("Basketball", "rot");
-- Warnings anschauen -- show warnings;
-- Feld ändern (neuer Name des feldes: farbe, alter Name des Feldes: farbe) alter table t_baelle change farbe farbe ENUM("rot","weiss","blau", "gelb"); Daten Anzeigen
* Alles ausgeben: SELECT * FROM tabelle;
* Spalten beim Ausgeben umbenennnen: SELECT vname AS Vorname, name AS Name FROM tabelle;
* Konstanter Wert abfüllen: SELECT "Mitarbeiter" AS status, vname, name FROM tabelle; -> Füllt alle Felder der Spalte status den Wert "Mitarbeiter" ab.
* Rechnereien Mysql spezifisch:
SELECT 2*4; -> resultat = 8
SELECT now(); -> jetztiges Datum mit Zeit
* Rechnereien ORACLE spezifisch:
DESCRIBE dual;
SELECT * FROM dual; -> SELECT 2*4 FROM DUAL -> SQL Syntax wird eingehalten
* SELECT COUTN(*) FROM tabelle; -> zählt die Tupels/Zeilen einer Tabelle;
* SELECT DISTINCT * feld FROM tabelle -> DISTINCT unterdrückt doppelte Datesatzkombinatione
o DISTINCT
o GROUP BY
o HAVING
o ORDER BY
o LIMIT
Bedingungen
* WHERE mit folgenden Operatoren verwendbar: =, >, <, >=, <=, <>/!=, LIKE
o SELECT feld FROM tabelle WHERE feld LIKE "%er"; -> Alle Datensätze mit der Endung "er".
o SELECT feld FROM tabelle WHERE feld LIKE "_er"; -> Alle Datensätze mit genau EINEM Zeichen vor "er".
* SELECT field1, field2 FROM tabelle GROUPE BY field1;
* SELECT field1 COUNT( field2) AS anzahl FROM tabelle GROUPE BY field1; -> listet die anzahl von field2
* SELECT field1 COUNT( field2) AS anzahl FROM tabelle GROUPE BY field1 HAVING count(field2) > 1; -> Lieftert alle die mehr als 1 mal das gleiche field2 haben
* SELECT field1, field2 FROM tabelle ORDER BY field1 DESC; -> ASC = Aufsteigend ist Default, DESC = Absteigend.
* SELECT * FROM tabelle ORDER BY RAND() LIMIT 3; -> Einträge werden nach Zufall geordnet und nur 3 Zeilen werden ausgegeben (Spart Rechenzeit und Netzwerkkapazität)
* SELECT * FROM tabelle LIMIT 3, 4; -> 4 ist da der Offset und es beginnt mit dem 5. Datensatz und gibt bis und mit 7. aus.
Übungen
-- 1. Aufgabe -- select vname, name from t_ma limit 12; select vname, name from t_ma order by name limit 12; select vname, name from t_ma order by rand() limit 12;
-- 2. Aufgabe -- select vname, name, plz, ort from t_ma limit 12;
-- 3. Aufgabe -- select vname as Vorname, name as Name, plz as PLZ, ort as ORT from t_ma limit 12;
-- 4. Aufgabe -- select vname as Vorname, name as Name, plz as PLZ, ort as ORT from t_ma where ort="Chur" or ort="Zuerich"; -- oder besser -- select vname as Vorname, name as Name, plz as PLZ, ort as ORT from t_ma where ort in ("Chur", "Zuerich");
-- 5. Aufgabe -- select ort, count(vname) from t_ma group by ort order by count(vname) desc;
-- 6. Aufgabe -- select ort, count(vname) from t_ma group by ort order by count(vname) desc, ort;
-- 7. Aufgabe -- select ort, count(vname) from t_ma group by ort having count(vname)>1 order by count(vname) desc, ort; Funktionen Standart SQL Funktionen Funktionen werden von Klammern gefolgt, jedoch ohne Lerzeichen!
* ROUND(23.4) -> rundet auf nicht Komma Zahl
* Aggregatsfunktionen:
o COUNT(spalte) -> Anzahl Datensäzte, welche nicht NULL sind -> Primary Key nehmen, oder COUNT(*)!!! Erfodert meistens GROUPE BY!!!
o COUNT(DISTINCT spalte) -> Anzahl unterschiedliche Werte
o AVG() -> Durchschnitt
o MIN() -> Minimalwert
o MAX() -> Maximalwert
o SUM() -> Summe
o STD() / STDDEV() -> Standartabweichung
o VARIANCE() -> Varianz
->Bsp: SELECT COUNT(ID) AS Anzahl, AVG(salär) AS Durchschnittsloh, MIN(salär) AS Minimallohn;
nicht Standartisierte Funktionen
* SELECT ohne FROM (nur MySQL) * ABS() * CEILING() * COS(), COT(), SIN(), TAN() * EXP(x) * FLOOR() * LOG(x) * LOG10(x) * MOD(x,y) * PI() * POW() * RAN() * ROUND(x,y) * SIGN * SQRT(x) * TRUNCATE(x,y) * CASE * ...
Übungen 1. Welche Namen kommen mehrfach vor? mysql> select name, count(name) AS vorkommen from t_ma group by vname having vor kommen>1 order by count(name);
2. Spalte bonus einfügen und mit Zufallswerten füllen zwischen 500 und 5000. alter table t_ma add bonus integer; UPDATE t_ma SET bonus=500+round(rand()*4500, -2);
3. Lohnerhöhung für gehläter unter 75000 5% und sonst 4%, auf 100 Fr. Geruntet aktualisieren. a) UPDATE t_ma SET sal =ROUND(sal*1.04,-2) WHERE sal >=7500; UPDATE t_ma SET sal =ROUND(sal*1.05,-2) WHERE sal <=7500;
b) UPDATE t_ma SET sal = IF(sal>= 75000, ROUND(sal*1.04,-2), ROUND(sal*1.05,-2));
c) UPDATE t_ma SET sal = ROUND(sal * IF(sal>=7500, 1.04, 1.05),-2); 22.10.2007 Abfragen über mehrere Tabellen -> Haupthema bei der Prüfung!!! (Kapitel 11) -> Self-Join -> Equi-Join -> Outer-Join -> kürzel setzen für Tabellen -> UNION Befehl Tabellen verknüpfen 1. SELECT was? 2. FROM wo? 3. WHERE verknüpfen (AND...AND) 4. (WHERE) Selektion
Sortieren
Gruppieren
Notation: Tabelle Spalte
Beispiel: mysql> SELECT t_ma.name "Name", -> t_ma.vname "Vorname", -> t_abt.name "Abteilung" -> FROM t_ma, t_abt, t_ma_abt -> WHERE t_ma.id = t_ma_abt.ma_nr -> AND t_abt.id = t_ma_abt.id -> AND ort = "Chur" -> ORDER BY t_ma.name; Abkürzungen Abkürzungen für Tabellen können im "FROM" gesetzt werden: "FROM t_ma m, t_abt a.... WHERE m.id = a.ma_nr"
!!! Wenn eine Abkürzung gesetzt wird, MUSS dieser verwendet werden. JOINs Cross-Join / Full-Join Cross und Full Join sind identisch und bilden das kartesische Produkt.
Bsp: jeder Datensatz von Tabelle 1(3 Datensätze) mit jedem Datensatz von Tabelle 2(3 Datensätze) kobiniert -> 3*3= 9 Datensätze als Resultat.
Syntax:
SELECT spalten FROM tabelle1 CROSS JOIN tabelle2; Inner-Join Inner-Join und Equi-Join machen grundsätzlich das Selbe. Syntax: SELECT spalten FROM tabelle1 INNER JOIN tabelle2 ON tabelle1.spalte = tabelle2.spalte [WHERE …] [ORDER BY …] [GROUP BY …];
Syntax über mehr als zwei Tabellen:
SELECT spalten FROM ((tabelle1 INNER JOIN tabelle2 ON bedingung) INNER JOIN tabelle3 ON bedingung) [WHERE …] [ORDER BY …] [GROUP BY …];
Equi-Join Inner-Join und Equi-Join machen grundsätzlich das Selbe. Mit em Equi-Join kann man alles lösen (Self-Join ist nur ein spezialfall davon)
Syntax: SELECT spalte FROM tabelle1, tabelle2, . WHERE tabelle1.spalte = tabelle2.spalte [AND...];
Anzahl Tabellen -1 = Anzahl Verküpfungsbedingungen (Weitere Verknüpfungen dienen der Selektion) Natural-Join Entspricht dem Inner-Join, hat aber keine doppleten Datensätze (DISTINCT)
Syntax: SELECT DISTINCT spalten FROM tabelle1 INNER JOIN tabelle2 ON tabelle1.spalte = tabelle2.spalte [WHERE …] [ORDER BY …] [GROUP BY …]; Theta-Join Der Theta-Join ist ein Inner-Join, bei welchem in der ON-Klausel nicht auf Gleichheit, sondern einen anderen logischen Operator geprüft wird.
Syntax: SELECT spalten FROM tabelle1 INNER JOIN tabelle2 ON tabelle1.spalte <func> tabelle2.spalte [WHERE …] [ORDER BY …] [GROUP BY …];
<func>: >, <, >=, <=, <>
-> Wird an der Prüfung nicht verlangt Outer – Join Ziel des Outer-Joins ist es, alle Datensätze einer Tabelle mit Daten der verknüpften Tabelle zu erweitern. Ist in der Verknüpfung kein passender Datensatz vorhanden, werden die Felder mit NULL belegt.
Syntax: SELECT spalten FROM tabelle1 LEFT|RIGHT OUTER JOIN tabelle2 ON tabelle1.spalte = tabelle2.spalte [WHERE …] [ORDER BY …] [GROUP BY …];
Erklärung: -> LEFT nimt alle Datensätze der tabelle1 in das Ergebnis. Wo bei der tabelle1 keinen Datensatz der tabelle2 hinzugefügt werden kann, wird mit NULL augefüllt -> RIGHT nimt alle Datensätze der tabelle2 in das Ergebnis. ... -> Auf die Tabelle, welches das Schlüsselwort verweist, kommen alle Datensätze MINDESTENS EINMAL vor. Self-Join Tabelle mit sich selbst verknüpfen.
Syntax: SELECT spalten FROM tabelle tab1, tabelle tab2 WHERE tab1.spalte = tab2.spalte [AND …];
Beispiel: Gesucht ist eine Liste mit Vor- und Nachname der Mitarbeiter sowie deren Vorgesetzten. Aus einer Liste werden virtuell deren zwei erstellt und mit den bekannten Join Funktionen verknüpft.
SELECT ma.name, ma.vname, mgr.name, mgr.vname FROM mitarbeiter ma, mitarbeiter mgr WHERE ma.mgr-id = mgr.ma-id;
-> Immer dann sinnvoll, wenn sich ein Feld einer Tabelle auf ein anderes in der gleichen Tabelle bezieht. -> Kann aber auch verwendet werden, um Daten einer Tabelle zu analysieren. Verknüpfung über Mengenoperationen Die Tabellen müssen eine Identische Struktur aufweisen!
- UNION (Vereinigung) - INTERSECT (Schnittmenge) - MINUS (Differentzmenge)
Beispiele:
SELECT name, vname FROM t_ma WHERE … UNION SELECT name, vname FROM t_ma_ahv;
SELECT name, vname FROM t_ma WHERE …
INTERSECT SELECT name, vname FROM t_ma_fc;
SELECT name, vname FROM t_ma WHERE …
MINUS SELECT name, vname FROM t_ma_fc;
-> Aktuell ist nur "UNION" in MySQL implementiert
Übung
Aufgab im Unterricht
Erstellen Sie eine Liste mit Name, Vorname und
Abteilungsname (t_ma, t_abt, t_ma_abt)
• Sortiert nach Mitarbeiternamen
• Alle Mitarbeiter in Ergebnistabelle
• Alle Abteilungen in Ergebnistabelle
• Alle Mitarbeiter aus Zürich und Chur
Tabellen Übersicht
mysql> describe t_ma;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| vname | char(30) | YES | | NULL | |
| name | char(40) | NO | | | |
| str | char(40) | YES | | NULL | |
| plz | smallint(4) | YES | | NULL | |
| ort | char(20) | YES | | NULL | |
| sal | decimal(9,2) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| bonus | int(11) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
8 rows in set (0.30 sec)
mysql> describe t_abt; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.09 sec)
mysql> describe t_ma_abt; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | ma_nr | int(11) | NO | PRI | 0 | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.06 sec)
Lösungen • Sortiert nach Mitarbeiternamen SELECT t_ma.name "Name" , t_ma.vname "Vorname", t_abt.name "Abteilung" FROM t_ma, t_abt, t_ma_abt WHERE t_ma.id = t_ma_abt.ma_nr AND t_abt.id = t_ma_abt.id ORDER BY t_ma.name;
• Alle Mitarbeiter in Ergebnistabelle SELECT t_ma.name "Name" , t_ma.vname "Vorname", t_abt.name "Abteilung" FROM (t_ma LEFT OUTER JOIN t_ma_abt ON t_ma.id = t_ma_abt.ma_nr) LEFT OUTER JOIN t_abt ON t_ma_abt.id=t_abt.id ORDER BY t_ma.name;
• Alle Abteilungen in Ergebnistabelle SELECT t_ma.name "Name" , t_ma.vname "Vorname", t_abt.name "Abteilung" FROM (t_abt LEFT OUTER JOIN t_ma_abt ON t_abt.id = t_ma_abt.id) LEFT OUTER JOIN t_ma ON t_ma_abt.id=t_ma.id ORDER BY t_ma.name;
oder
SELECT t_ma.name "Name" , t_ma.vname "Vorname", t_abt.name "Abteilung" FROM (t_ma RIGHT OUTER JOIN t_ma_abt ON t_ma.id = t_ma_abt.ma_nr) RIGHT OUTER JOIN t_abt ON t_ma_abt.id=t_abt.id ORDER BY t_ma.name;
• Alle Mitarbeiter aus Zürich und Chur
Aufgabe: SQL Aufgaben HS07/08 SQL_Aufgaben.pdf SQL_Loesungen.pdf
Aufgabe 1: a) SELECT * FROM Reperaturauftrag WHERE Numer = "SS625000"; b) SELECT * FROM Kunde WHERE Nachname like "A%" ORDER BY Nachnamen; c) SELECT * FROM Reparaturauftrag WHERE Abschlussdatum >"1999-05-01" AND Abschlussdatum < "1999-05-10"; d) SELECT AVG(Gesamtpreis) FROM Reperaturauftrag WHRERE NOT ISNULL(abschlussdatum); e) SELECT COUNT(r.nummer), g.type
FROM reparaturauftrag r, gerät g WHERE r.gerätid=g.gerätid WHERE r.Abschlussdatum > "1999-01-01" GROUP BY g.type;
f) SELECT @a:=AVG(gesamtpreis) FROM reparaturauftrag;
SELECT * FROM reparaturauftrag WHERE gesamtpreis>@a;
Alternativ ab MySQL 4.1 (SUBSELECT): SELECT * FROM reparaturauftrag WHERE gesamtpreis>(SELECT AVG(gesamtpreis) FROM reparaturauftrag);
g) SELECT gerät.gerätid, gerät.bezeichnung
FROM gerät, kunde, reparaturauftrag, mitarbeiter WHERE kunde.kdnr=reparaturauftrag.kdnr AND reparaturauftrag.svnr=mitarbeiter.svnr AND reparaturauftrag.gerätid=gerät.gerätid AND kunde.vorname LIKE „Alf%“ AND mitarbeiter.nachname=”Hasi”;
Aufgabe 2: a) SELECT Universitaet.Name, COUNT(Student.Matnr) FROM Universitaet, Student WHERE Universitaet.bezeichner = Student.Stammuni GROUP by Universitaet.Name; b) SELECT Studium.Bezeichnung FROM inskribiert LEFT OUTER JOIN Studium ON Studium.Nr = inskribiert.Studium WHERE inskribiert.Student = 9460231; 29.10.2007 Transaktionen Kapitel 12 im Buch -> Prüfung in zwei Wuchen (alle schriftlichen Unterlagen dürfen mitgenommen werden) -> nächstes mal machen wir Übungen Transaktion Transaktion (TA) = Logische zusammengehörige Folg von SQL Befehlen.
Ziele:
* Concurrency controll (Mehrbenutzerbetrieb, Gleichzeitigkeit) * recovery (Fehlertoleranz)
Komponenten der Transatkonsverwaltung
Concurrency control
* Isolation von parallel laufenden Transaktionen * keine Inkonsistenz bei Mehrbenutzerbetrieb * automatische Synchronisationsmechanismen
Recovery
* Fehlerhandling (Hardware und Software) * Abbruch (system-, bzw. anwendungsinitiiert)
ACID
Atomicity
Die Transaktion, die aus einer oder mehreren Operationen bestehen kann
wird entweder vollständig oder gar nicht ausgeführt. Bei einem Fehler werden alle bisherigen Operationen der Transaktion rückgängig gemacht (ROLLBACK).
-> ´Alles oder nichts´-Regel
Consistency
Eine Transaktion überführt die Datenbank von einem konsistenten Zustand DBt1 in einen anderen konsistenten Zustand DBt2.
-> Konsistenzerhaltung
Isolation
Eine Transaktion sieht keine Zwischenresultate einer anderen, parallel laufenden Transaktion.
-> Unabhängigkeit
Durability
Änderungen einer Transaktion gehen auch bei nachfolgenden Fehlersituationen nie verloren ( Backup / Recovery )
-> Dauerhaftigkeit
Isolationsebenen
Durch den Transaction Isolation Level wird der Grad der Parallelität von Transaktionen gesteuert.
-> Je mehr Transaktionen paralell abläuft, desto schneller, jedoch auch weniger isoliert und somit mehr Fehlergefahr. Nach SQL2 wurden 4 Isolations-Ebenen (0-3) bestimmt
* Ebene 0
set tx_isolation = read-uncommitted;
-> Es werden werte gelesen, die noch nicht committed worden sind (von anderen Sessions)
Lesezugriff auf alle Daten, Keine Sperren ->dirty read
* Ebene 1
set tx_isolation = read-committed;
-> Es werden nur noch Werte die commited worden sind gelesen (von anderen Sessions)
Modifizierte Datensätze gesperrt, Non-repeatable read / Phantom
* Ebene 2
set tx_isolation = repeatable-read;
-> Default Setting von MySQL, Man liest immer das selbe Resultat, auch wenn inzwischen änderunge commited wurden!
Alle Datensätze gesperrt, Phantom-Problem
* Ebene 3
set tx_isolation = serializable;
-> Zweite Transaktion muss auf erste warten, bis diese ausgeführt wird
Volle Serialisierbarkeit, Keine Probleme
Syntax
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE]
Probleme
-> Siehe Folie 12_Transaktionen.pdf
* Dirty Read * Fuzzy Read * Phantom * Dirty Write * Lost Update
Locking Ein Datensatz kann drei Zustände aufweisen:
* unlocked * read-locked (shared lock) * write-locked (exclusive lock)
Verschieden Lock Typen:
* Read (Share) Lock * Write (Exclusive) Lock * Read Predicate Lock * Write Predicate Lock
2 Phasen Sperren Protokoll
* R1: Sperranforderung vor jedem Objektzugriff * R2: Sperren paralleler Transaktionen dürfen nicht in Konflikt zueinander stehen * R3: Nicht mehrere Sperren für ein Objekt innerhalb einer Transaktion * R4: Bei Transaktionsende sind alle Sperren freizugeben
Strict Two-Phase-Locking:
Transaktion behalten die Locks bis zum commit oder abort, um Domino Effekte zu verhindern
Alternative zum Locking -> Zeitstempelverfahren (Optimistischen Verfahren) Deathlock
Zwei Transaktionen verhängen auf den gleichen Datensatz einen Lock.
Vermeidung
* einfach, aber nicht unbedingt gut * zu restriktiv, Zugriffsbeschränkung
Erkennung
* Aufgabe des Lock Managers
Timeout
* Lock wird nach Timeout „verletzlich“ und die ursprüngliche Transaktion bricht ab * Gefahr des Abbruchs auch ohne Deadlock * Problem bei lange andauernden Transaktionen
Lock Manager
Die Clients haben natürlich keinen Zugriff zu Operationen für das Locking oder Unlocking, da ja sonst die Protokolle von trickreichen Programmierern verletzt werden könnten. Diese Funktionen werden vom lock manager - einem Serverprogramm - durchgeführt. MySQL Tabellentypen Typen von MyISAM:
* MyISAM-Static * MyISAM-Dynamic * MyISAM-Compressed
MyISAM Tabellentyp (default) ist nicht Transaktionsfähig. InnoDB, HEAP, BDB können Transaktionen handeln.
Bei MyISAM (Default):
* .frm (frame) je Tabelle * .myd (data) je Tabelle * .myi (Index) je Tabelle
Bei InnoDB:
* ibdata für alle Datenbanken im Datenbankfolder * ib_logfile für alle Datenbanken im Datenbanfolder * .frm je Tabelle
Merge Typ: Kann zwei Tabellen zusammen fügen (join). Kommt aus der Zeit, wo Dateien nicht grösser al 2GB sein konnten.
InnoDB vs MyISAM
Inno DB braucht mehr Platz
Inno DB ist schneller, bei vielen Transaktionen
Loging auf Datensatzebene bei InnoDB, bei MyISAM auf ganzer Tabelle
Transaktionen erstellen
MySQL verwendet Autocommit als standart. Man deaktiviert es durch den Befehl: SET AUTOCOMMIT = 0;
Bei Programmiertun immer folgende Befehler verwenden, dann spielt der Autocommit keine Rolle mehr: BEGIN; .. COMMIT; BEGIN; ... ROLLBACK; Savepoints SAVEPOINT identifier ROLLBACK TO SAVEPOINT identifiere Demo Notizen zu Transaktionen Variablen von MySQL anzeigen show variables;
Isolationsebene verändern (Nur auf Session-Ebene)
* Ebene 0, set tx_isolation = read-uncommitted; -> Es werden werte gelesen, die noch nicht committed worden sind (von anderen Sessions) * Ebene 1, set tx_isolation = read-committed; -> Es werden nur noch Werte die commited worden sind gelesen (von anderen Sessions) * Ebene 2, set tx_isolation = repeatable-read; -> Default Setting von MySQL, es werden erst die Updates angezeigt in der zweiten Session, wenn die Transaktion in der zweiten Session commited wird (commit;) * Ebene 3, set tx_isolation = serializable; -> Zweite Transaktion muss auf erste warten, bis diese ausgeführt wird
Änderungen nicht sofort an DB senden
set autocommit = 0;
Änderungen an DB manuell übermitteln commit;
Änderungen rückgängig machen rollback;
Engine / Type einer Tabelle anschauen show create table tabelle Aufgaben zu Transaktionen Aufgabe 1 Nach ACID wurden folgende Kriterien verletzt: Isolation: Meiers Transaktion wird von Huber beeinflusst, er Liest nur ein Zwischenergebnis aus!
Isolationsebene 0 -> read-uncommited Aufgabe 2 Locking (2-Face-Locking) oder Timestamping Aufgabe 3 Kontonr. 100 = 2000 Kontorn. 200 = 0 5.11.2007 Übungen Transaktionen
Prüfungsvorbereitungen mit der Semesterprüfung 2 WS2006. 11.11.2007 Fragestunde und Prüfung Fragen Wie kann man die 2. NF von der 3. NF unterscheiden. Erläuterung der transitiven Abhängigkeit 19.11.2007 Backup/Recovery Sicherungen müssen Regelmässig gemacht werden. Nach möglichkeit auch an einem anderen Ort aufbewahren. Ausfall Gründe
* Systemabsturz * Medien Fehler * Softwarefehler * Handlingfehler * Mutwillige Zerstörung und Manipulation
Mediantypen der Datenspeicherung
* Hauptspeicher * Fesplatte * Magnetband * Optische Speicherung
In dieser Reihenfolgen vom teuersten zum güstigsten Speicher
Magnetband
- LTO: Schnell und Teuer, bis 800GB Kapazizät ohne Kompression (Eine Spule)
- DLT: Längsämer als LTO
- AIT: Kleiner, längsämer, Lebensdauer von nur 4 Jahre, höhere Kompression, bis 400 GB
- DAT: Langsam, Klein bis 36 GB aber Günstig!
Dauerhaftigkeit
Dauerhaftigkeit müssen auf nicht flüchtige Speicher geschrieben werden. Dateninformation können durch RAID wärend der Übertragen abgesichert werden, wenn zum Beispiel eine Disk aussetzt.
RAID
- Redundanz
- Perfomrnace
- grosse logische Platte
- erhöhung der Speicherplatzes währed des Betriebes
- Kostenreduktion durch einsatz preiswerter Platten
- steigerung der Systemleistungsfähigkeit
Raid 0: Striping Raid 1: Spiegelung Raid 5: Ausfallsicher, steigerung der Performance, am meisten "Platzverlust" Transaktionen Müssen die ACID-Eigenschaften erfüllen. Siehe Log 29.10.2007 Der Transactionsmanager muss die Rollbacks nachfahren (commit), falls ein Systemabsturz geschehen ist. Recovery Sicherungsmechanismus - Automatische Erstellung von regelmässigen Sicherungskopien der Datenbank - Regelmässige Erstellung von Sicherungskopien der Protokolldatei - Das System sollte zur Sicherung Online bleiben können - Full / Differential / Incremental Save - Rechnerunabhängiges Speichermedium Protokollierung - Enthält Informationen über Änderungen der DB - Transaktionseinträge - Checkpointeinträge - Protokolle müssen mehrfach gespeichert werden (Raid), da sehr wichtig - Hat Einfluss auf die Gesamtleistung des DB-Systems Checkpoint Der Punkt der Synchronisierung zwischen der Datenbank und der Transaktionsprotokolldatei. Alle Buffer werden in den Sekundärspeicher zwangsgeschrieben.
Checkpoints sind in vorbestimmten Abständen zeitlich geplant und umfassen: • Protokolleinträge • Geänderte Blöcke im Data Buffer • Checkpointeintrag (Alle aktiven Transaktionen)
Techniken der Wiederherstellung
Transaktionen können sehr gross werden und somit auch anfälliger für Fehler. Man sollte in diesem Falle Savepoints verwenden! Verzögerte Aktualisierung
* Datenbankeintrag erfolgt erst nach COMMIT * Allfälliges Rollforward erforderlich
- Transaktionsstart > Protokoll - Aktualisierung > Protokoll - Transaktionscommit > Protokoll - Protokolleinträge > Disk (Protokoll) - COMMIT wird ausgeführt - Disk (Protokoll) > Datenbank Unmittelbare Aktualisierung
* Datenbankeintrag erfolgt laufend (vor COMMIT) * Allfälliges Rollback erforderlich
- Transaktionsstart > Protokoll - Aktualisierung > Protokoll - Aktualisierung > Data Buffer - Protokoll Buffer > Disk - Data Buffer > Disk - Transaktionscommit > Protokoll Backup Methoden - Online (SQL Kommandos-Datei): Portabilität!, langsam - Offline (binary-Datei): schnell, einfacht, sicherungsfehler könne nicht bemerkt werden
Replikation Vorteile: Sicherheit und Performance Nachteil: Kein "fail save Replication"
Beispiele:
* Slave für offline Backup Server verwenden * Standortverteilung: Master hat read-write und alle anderen sind Slave (read-only!). Beim Ausfall des Masters müssen manuelle Schritte unternommen werden -> ausfall. * Zuest Slave vom Master trennen und Software upgrad durchführen, danach als Master konfigurieren und Master als Slave.
Zusammenfassung Concurrency Control Ziel: Isolation parallel laufender Transaktionen Datenkonsistenz: Serialisierbarkeit Verschiedene Isolationsstufen Methoden: Sperrverfahren / Nicht-Sperrverfahren pessimistisch / optimistisch Recovery Ziel: Abstraktion von Hardware- und Softwarefehlern Fehlerarten: Transaktions-, System- und Medienfehler Atomarität durch UNDO Information Dauerhaftigkeit durch REDO Information
-> mit NDB können Cluster erstellt werden (99.9999% Verfügbarkeit) 3.11.2007 Performance und Index Performance Performance ist ein subjektives Mass für die Geschwindigkeit, mit der eine Applikation verwendet werden kann. TPC-Benchmarks Messung der "Datenbank Performance":
TPC-App (Application server and web services) Simulates the activities of a business-to-business transactional application server operating in a 24x7 environment.
TPC-C (Order entry transactions, OLTP) Simulates a complete computing environment where a population of users executes transactions against a database.
TPC-H (Decision support, OLAP) Business oriented ad-hoc queries and concurrent data modifications. Effizienzvergleich der verschiedenen Architekturen mit Hilfe des TPC-H Benchmarks
Decision Support Benchmark für Datenbankgrössen von 100-10000 GB: Dabei werden die Zeiten für Ad-Hoc-Queries gemessen, so dass es kein Vorwissen bzgl. der Anfragen gibt, welches zur Optimierung der Datenbank genutzt werden könnte. Benchmarks (SPEC) Zur Messung von "CPU Performance"
Kriterien für Standard-Benchmarks Relevanz Portabilität Skalierbarkeit Einfachheit
Strategien
* Performance Test vs. Stress Test
* „Change one thing at a time“
* Iterative Tests
* Tests wiederholen
o Restart MySQL / Reboot
* Reale Daten verwenden (Art und Menge)
* Nicht zu viele Clients verwenden
* Client/Server trennen
MySQL Benchmark Suite
* Bestandteil der MySQL Distribution * Perl Scripts (DBI und Benchmark Modul) * MySQL, PostgreSQL, MS-SQL, Oracle,… * Nicht für Multi-CPU Rechner geeignet
Die Suite bietet diverse sh-Dateien, um diverse Funktionen der Datenbank zu messen (create tabel, fill data, Wisconsin, ATIS...)
Wisconsin Benchmark „The Wisconsin Benchmark described in is the first effort to systematically measure and compare the performance of relational database systems with database machines. The benchmark is a singleuser and single-factor experiment using a synthetic database and a controlled workload. It measures the query optimization performance of database systems with 32 query types to exercise the components of the proposed systems. The query suites include selection, join, projection, aggregate, and simple update queries.“ MySQL super-smack „Originally developed by Sasha Pachev, MySQL Super Smack is a benchmarking, stress testing, and load generation tool for MySQL. Like the apache bench (ab) tool that ships with Apache, super smack helps to give you a handle on how well your server will perform. It's an invaluable testing and tuning aid.“
* Nicht Bestandteil der MySQL Distribution * C++ * MySQL, PostgrSQL, ( Oracle ) * Für Multi-CPU Rechner geeignet * ($1: Anzahl Clients, $2: Anzahl Runden) * Realistische Testszenarios, daher * aufwändiger als die Benchmark Suite
MyBench „mybench is a simple benchmarking framework for MySQL, written in Perl. I consists of a module (MyBench.pm) and an example script (bench_example) that you should customize to suit your needs. “
* Nicht Bestandteil der MySQL Distribution * Perl * MySQL (DBI, DBD::MySQL, Time::HiRes) * Für Multi-CPU Rechner geeignet * Etwas höherer Client Load, daher sollten * Client und DB-Server getrennt werden
Tuning Tuning ist eine Tätigkeit, welche die Performance steigert. Diese Tätigkeit wird vor, während und nach der Entwicklung einer Applikation durchgeführt. Design - Aufbau der DB bestimmt die möglichen SQL Statements Applikation - Aufbau der Applikationen bestimmt die SQL Statements Speicher - Grösse der Speicher bestimmt die Anzahl Festplattenzugriffe I/O - Festplattenzugriffe und Netzwerkverkehr Contention - Locking Probleme durch Anzahl Benutzer und Prozesse Typische Performance Mängel Nicht optimales Datenbankdesign Ineffiziente Abfragen Fehlende Indizes Konfigurationsmängel Hardware Index Auch bei MySQL ist die Indizierung von Daten eine der einfachsten und effizientesten Massnahmen zu Steigerung der Performance. Die Praxis zeigt, dass etwa 90% aller Probleme bei MySQL wegen mangelhafter Indizes auftreten. Da jeder Index auf ein bestimmtes Datenfeld aber auch den Datenbestand erhöht, gilt es, eine optimale Balance zwischen Speicherbedarf und Performance zu finden.
MySQL verwendet höchstens einen Index pro Tabelle und Abfrage.
Ein Index (Indexdatei) ist ein Zugriffspfad auf eine Datei, der selbst wieder als Datei dargestellt wird. Partieller Index Wird verwendet bei grossen Tabellen, die viel Speicherplatz brauchen:
-> ALTER TABLE t_ma ADD INDEX (name(4));
Besteht der Name aus durchschnittlich 8 Bytes, werden im obigen Beispiel nur die ersten 4 Bytes indiziert. Dazu kommt noch der Datensatzzeiger, der 4-8 Bytes pro Datensatz benötigt. Ein- / Mehrspalten Index -> ALTER TABLE t_ma ADD INDEX (vorname, name, ort); Leftmost Prefixing Mehrspaltenindizes haben in MySQL eine weitere angenehme Eigenschaft, bekannt als „Leftmost Prefixing“
Der Mehrspaltenindex aus dem vorherigen Beispiel kann für die Suche nach folgenden Kombinationen verwendet werden: vorname, name, ort vorname, name vorname Index Typen „Normaler“ Index Meistverwendeter Index, keine Einschränkung CREATE INDEX … Primary Key Werte sind UNIQUE, Spalte als NOT NULL definiert Unique Index Werte sind UNIQUE CREATE UNIQUE INDEX … Fulltext Index Verwendung zur Volltext Suche ALTER TABLE ADD FULLTEXT … Geclusterte Indizes Bei geclusterten Indizes werden der Primärschlüssel und der Datensatz selbst „geclustert“, d.h. zusammengefasst, und die Datensätze werden in der Reihenfolge des Primärschlüssels gespeichert. InnoDB verwendet geclusterte Indizes. Sekundäre Indizides Sekundäre Indizes verweisen auf den Primärschlüssel (K) und nicht auf den Datensatz. Probleme: - Langer Primärschlüssel - Änderung des Primärschlüssels Index Strukturen B-Tree Suchbaum mit mehreren knoten, balaciert
* Am weitesten verbreiteter Indextyp * Kombination aus Flexibilität und Grösse * z.B. für bereichsbasierte Abfragen * O(log n) Performance für einfaches Query * Flacher als binäre Bäume
Kriterien zu erfüllen:
* Jeder Ast ist besetzt und gleich lang (jeder Wert muss mit Pointer versehen sein!) * Jeder Knoten hat m <= x <= 2m elemente (ausser die Wurzel)
Hash
ebenfalls weit verbreitet Flache Strukturen (vgl. mit Bäumen) basierend auf Hash Funktion
Der Hash Wert wird genutzt, um zu ermitteln, in
welchem Fach (Bucket) der Schlüssel abgelegt wird. Sehr schnelle Lookups möglich
O(1), solange Hash Werte gut verteilt sind
Geringere Flexibilität
Bereichsbasierte Abfragen nutzen Index nicht
Hash Index ist der schnellste für einzelne Abfragen.
R-Tree Für Räumliche Daten Weitere Performance Engpässe Wildcard Matching LIKE „%string%“ Reguläre Ausdrücke RLIKE „(ber|ger)$“ Fehlerhafte oder beschädigte Statistiken MySQL interne Indexstatistiken Zu viele passende Datensätze Grenze 30% Index Pflege SHOW INDEX FROM tabelle \G OPTIMIZE TABLE Alle Indizes werden neu aufgebaut Kann lange dauern und Tabelle ist locked myisamchk MyISAM Tabelle kann offline geprüft werden ANALYZE TABLE für InnoDB Daten werden neu gelesen und Statistik verbessert 10.12.2007 MySQL tuning Architektur Grob aufgeteilt bietet MySQL verschiedenen Ebenen: 1. Ebene: Connection management (Dienste von aussen: Netzwerk, Server/Client,...) 2. Ebene: SQL parsing, execution, caching (MySQL Dienste: Storage Engine, query parsing, analyse, casching, optimizer...) 3. Ebene: MyISAM / InnoDB / HEAP / NDB (Storage Engines)
Generelle Gründe für langsame Datenbanken Einzelen Query
* Tabellen design nicht optimal * index fehlt oder schlecht gewählt * query zu aufwändig
Abhilfe: slow-query-log und Analyse mit EXPLAIN
Ganze Datenbank
* Hardware * "Installationsfehler"
Storage Engine Concurrency und Overhead können die Peformance beeinflussen. Multi-Version concurrency controll MVCC - Zusatz zum Row Level Locking - 2 versteckte Werte zur jedem Datensatz: creation ID, deletion ID - database version
Resultat des MVCC ist, dass Read Queries niemals Tabellen, Pages oder Datensätze sperren. Deadlocks Wenn mehrere Transaktionen Datensätze sperren mysql> show variables;
innodb_lock_wait_timeout 50
Tabellenformate Transaktionsorientiert: InnoDB, BDB, Falcon
Nicht transaktionsorientiert: MyISAM, HEAP, MERGE
Erstere sollten eingesetzt werden, wenn die Konsistenz der Datenbank auf keinen Fall gefährdet werden soll. Transaktionssichere Tabellen sind meist langsamer als das am häufigsten verwendete MyISAM Format.
Datentypen
SELECT spalte(n) FROM tabelle PROCEDURE ANALYSE(); Gibt die optimalen Datentypen einer Tabelle aus, aufgrund der enthaltenen Daten Deklaration von Zahlenwerten vorsichtig wählen UNSIGNED, wenn keine negativen Zahlen möglich sind Deklaration von Zeichenketten vorsichtig wählen CHAR, meist schneller gelesen als CHAR ENUM, bei begrenzten Mengen Vorsicht beim Speichern binärer Daten BLOB, beeinträchtigt die Performance sehr stark Alternative: Link auf externe Dateien Index 90 Prozent alles Probleme sind fehlende Indexes. Indexes brauchen jedoch Speicherplatz
Indexauswahl: Spalten, welche im WHERE oder JOIN verwendet werden eignen sich für den Index
Wobei > >= BETWEEN, IN und LIKE verwendet Index. Aber bei Like dar der erste Charakter kein % oder _ sein!
Mittels EXPLAY (Dem Query vorangestellt!) analysiert, ob überhaupt ein Index sinnvoll ist. Cash Abfragen welcher bereits gestellt wurden, werden im RAM abgelegt und beim zweiten Auruf kann die Antwort schneller gegeben werden. Optimizer Findet die effizienteste Query zur Abfrage
* Sind Indizes vorhanden? * welcher Index ist der beste * Tabellenabhängigkeiten? * Optimel JOIN Reihenfolge für die Abfrage
Tuning SELECT
1. Keine Operation auf Indiziertes Attribut
-> Rechenoperation nach möglichkeit bereits ausschreiben und im gleichen Datentyp hinschreiben, damit keine Converstation statt finden muss.
2. Keine Funktionen auf indiziertes Attribut
3. Reihenfolge ändern
-> Zuerst selektivere Kriterien (= Wo mehr rausgefilter wird): WHERE... AND...;
4.
Abfrage mit <> (ungleich) vermeiden 5. Abfragen mit IS NOT NULL 6. Datentypkonversion vermeiden
JOIN
* Optimales Reihenfolge eintscheiden * Schneller mit Subqueries * Kriterien für SELECT gilt auch hier
log-slow-queries Falls MySQL mit der Option --log-slow-queries gestartet wurde, protokolliert mysqld alle SQL-Queries, die für die Ausführung länger benötigen als der definierte Wert „long-query-time“. Ausserdem existiert im MySQL Datenverzeichnis das „hostname.err“ Logfile, das Informationen über Crashes oder Probleme aufzeichnet. „optimize table“ kann bei MyISAM und BDB Tabellen zur Defragmentierung verwendet werden. „analyze table“ gibt Aufschluss über Schlüsselverteilung und Reihenfolge der Tabellenverknüpfung
Überwachung mittels "mytop" kann die Auslastung der Tabellen überwacht werden (ist ein Perl befehl). 17.12.2007 Stored Procedures und Triggers Stored Pozedures Seit MySQL 5.0 implementiert Stored Prozedures = "Kleine Programme" Rechte sind nicht auf der Tabelle, sondern auf der auf der Prozedure. die Prozedure hat die Rechte des erstellers. Bentuzer braucht nur das Recht die SP auszuführen. Vorteile
* Erhöhte Datensicherheit * Verbesserter Datenschutz * Höhere Leistungsfähigkeit
Nachteile
* Datenbankspezifisch
Syntax CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body
CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body
proc_parameter: [ IN | OUT | INOUT ] param_name type func_parameter: param_name type
type: Any valid MySQL data type characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC |{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string‘
routine_body: Valid SQL procedure statement Eigene SP erstellen (Übung) 1. delimiter % // muss geändert werden, da sonst bereits das ende der SP verstanden wird; 2. CREATE PROCEDURE Durchschnitt (IN x INT, IN y INT)
BEGIN
select (x+y)/2;
END;
3. delimiter % 4. Ausführen: CALL Durchschnitt (10, 15);
! "show porcedure status;" zeigt die bestehenden Prozeduren an Trigger = Eventhandlers Kann vor oder nach einer Aktion ausgelöst werden.
-> zum Loggen, auditing, Statistik erheben, DB konsisten gewährleisten.... ! Exception Handling muss gemacht werden Syntax CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt trigger_time: BEFORE | AFTER trigger_event: INSERT | DELETE | UPDATE Beispiel CREATE TRIGGER LogSalaerAenderung AFTER UPDATE ON Angestellter FOR EACH ROW IF (new.Salaer != old.Salaer) THEN INSERT INTO AngAudit VALUES (user, new.PersNr, now(), new.Salaer, „Neues Salaer“); END IF;
! "show triggers;" zeigt die bestehenden Triggers an. Prepared Statement Prepared statements bieten die Möglichkeit, ein SQL Statement mit verschiedenen Parametern mehrmals auszuführen.. Temporäre Speicherung, um die Effizienz und Sicherheit zu optimieren Beispiel mysql> PREPARE stmt_name FROM "SELECT name FROM Country WHERE code = ?"; // Statement anlegen Query OK, 0 rows affected (0.09 sec) Statement prepared
mysql> SET @test_parm := „CH"; Query OK, 0 rows affected (0.00 sec)
mysql> EXECUTE stmt_name USING @test_parm; // Statement ausführen +---------+ | name | +---------+ | Schweiz | +---------+ 1 row in set (0.03 sec)
mysql> DEALLOCATE PREPARE stmt_name; //statement wird wieder freigegeben/Löschen Query OK, 0 rows affected (0.00 sec) Schnittstellen und Programierung Perl Ursprünglich Administrationstool für UNIX. Läuft auf UNIX, OS X und Windows.
Dank dem DBI - Database Interface (und DBD - Database Driver) interface kann mittels Perl auf divere DBs zugegriffen werden (DB2, MySQL, Oracle, Sybase...) PHP 7.1.2008 Sicherheit Zugriffsrechte Benutzerverwaltung Zweistufige Zugriffskontrolle: Die Datenbankbenutzer besitzen System und Objektprivilegien Systemprivilegien werden mit Username, Hostname und Passwort geprüft (MySQL)
Für die Objektprivilegien empfiehlt es sich, eine CRUD Matrix zu erstellen C INSERT R SELECT U UPDATE D DELETE
Entweder über das GUI von MySQL oder direkt in der Datenbank von Mysql die Tabelle user die Berechtigungen editieren. Es existiert auch ein Perlskript "mysql_setpermission.pl" welches ebenfalls eine Hilfestellung mit sich bringt. Ausserdem kann später mit GRAND und REVOKE die Berechtigungen die man selber hat weiter gegeben werden. zu beachten!
* alle default Passwörter ändern * ' ' User löschen -> sonst kann jeder ohne Useraccount sich auf die DB einloggen * DB Admin nur vom Localhost zugriff erteilen, da der mysql Client nicht verschlüsselt ist (vorher mit ssh verbinden)
Sicherheit Folgende Situationen können auftreten: Diebstahl oder Fälschung Verlust der Vertraulichkeit (Geheimhaltung) Verlust der Privatsphäre Verlust der Integrität Verlust der Verfügbarkeit SQL Injection SQL-Injektion (engl. SQL Injection) bezeichnet das Ausnutzen einer Sicherheitslücke in Zusammenhang mit SQL-Datenbanken, die durch mangelnde Maskierung oder Überprüfung von Metazeichen in Benutzereingaben entsteht. Der Angreifer versucht dabei, über die Anwendung, die den Zugriff auf die Datenbank bereitstellt, eigene Datenbankbefehle einzuschleusen. Sein Ziel ist es, Daten in seinem Sinne zu verändern oder Kontrolle über den Server zu erhalten. Gegenmassnahmen
* Überprüfen der Eingabewerte * Prepared Statements * Auslagerung der Business-Logik in Beans * Spezielles Konto für DB-Zugriff * Passwörter nicht in Klartext speichern * Default-Fehlermeldungen vermeiden * Code-Review
14.1.2008 Übungen und Repetition Übungsaufgaben zur Schlussprüfung Aufgabe 1 vorlesung, semester, dozent, fachbereich, matnr., note, bestanden, credits
a) Funktionale Abhängigkeiten: vorlesung, semester, matnr., -> dozent, fachbereich, note, bestanden, credits vorlesung, semester -> dozent, fachbereich, credits vorlesung -> fachbereich, credits note -> bestanden
b) 2. NF
- bewertung: vorlesung(FK), semester, matnr; note, bestanden
vorlesung: vorlesung, fachbereich, credits dozent: vorlesung(FK), semester, dozent
- hat noch transitive abhängigkeiten
3. NF bewertung: vorlesung(FK), semester, matnr; note(FK) notenskala: note, bestanden Aufgabe 2 a) SELECT id, name, vname FROM mitarbeiter, abteilung WHERE mitarbieter.abteilungsnummer = abteilung.abteilungsnummer
AND salär < 7000 AND bezeichnung = "Informatik";
b) SELECT bezeichnung, ROUND(avg(salär), -2) FROM mitarbeiter, abteilung WHERE mitarbeiter.abteilungsnummer = abteilung.abteilungsnummer GROUP BY bezeichnung; c) SELECT m.id, m.name, m.salär FROM mitarbeiter m, mitarbeiter v WHERE m.managerid = v.id
AND m.salär > v.salär;
Aufgabe 3 Benutzer anlegen: CREATE USER 'felix'@'localhost'
IDENTIFIED BY 'geheim';
oder USER mysql; INSERT INTO user VALUES("felix","localhost", PASSWORD("geheim");
Rechte vergeben: GRAND ALL ON Privat.t_freunde TO felix@localhost; Aufgabe 4 Passwort und Benutzername in eine andere Datei auslagern! Allenfalls Systemfehlermeldungen abfangen, dass diese nicht ausgegebn werden Aufgabe 5 a) Atomic: nicht Consistency: Könnte die Konsistenz verletzt werden, da mit anderen Werten weiter gerechnet wird.s Isolation: nicht Durability: nicht
b) Read commited oder read uncommited Aufgabe 6 Konto 100: Konto 200: Aufgabe 7 Änderungen werden dadurch langsamer, da der Index auch nachgeführt werden muss. Braucht mehr Speicherplatz Aufgabe 8 Nicht ausgeglichener Suchbaum mit 3 Pointers (2 Elemente im Node). Aufgabe 9 Balanced Tree!!! Jeder Ast ist besetzt und gleich lang (jeder Wert muss mit Pointer versehen sein!) Jeder Knoten hat m <= x <= 2m elemente (ausser die Wurzel) Aufgabe 10 a) ja, beim ersten statement der erste Index b) ja, der Dritte Index (würde auch die reihenfolge verändern, wegen des optimizers) c) keiner, wegen % am anfange muss er sowieso alle Datensätze durchgehen und bei Ungleich Abfragen ebenfalls nicht Aufgabe 11 Problemsbreich einschränken Query Time messen über eine gewisse Zeit Query schlecht (kein Index), oder Hardware zu langsam? Aufgabe 12 Personal muss da sein Wo sind die Bänder Verfügbarkeit der Backups Geschwindigkeit des Backups medium Ersatzhardware Full oder Incremental Backup (Anzahl Bänder) Aufgabe 13 a) 1. DB Modell erstellen / Normalisieren 2. Datenbank und Tabellen erstellen 3. Excel ein CSV Export ausführen 4. CSV Datei bearbeiten 5. in DB LOAD DATA INFILE ...
c) Direkt im Excel via ODBC d) mysql ...--html --execute='SELECT...' > date; Aufgabe 14 shared lock (= read lock) auf Datensatz, es kann weiter gelesen werden, der nächste User kann wieder gelesen...bis der letzte fertig ist, ist der Datensatz gesperrt für das Schreiben.
write lock (=exklisiv lock) gesetzt ist, kann niemend mehr lesen! -> 2 phase Locking Aufgabe 15 Antwort c ist richtig
