CRUD - Index of

softlang.wikidot.com/course:dbintro
http://softlang.wikidot.com/course:dbintro
Grundlagen
relationaler Datenbanken
SOFTLANG Team, Universität Koblenz-Landau
http://softlang.wikidot.com/startseite
Prof. Dr. R. Lämmel
Gliederung
1. Einführung!
2. Das Relationale Modell
3. Datendefinition (SQL DDL)
4. Datenmanipulation (SQL DML)
5. Eingebettete Programmierung
6. Objektrelationale Abbildung
7. … ?
Seite
1
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Seite
2
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Einführung
Technologische Räume!
•
SQLware als ein Technologischer Raum
•
Das 101companies-Projekt als didaktische Hilfe
•
Befragung der Seminarteilnehmer
Seite
3
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
!
SQLware ist ein technologischer Raum.
Es ist einer von vielen.
Seite
4
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Technologischer Raum = Technologie
mit gemeinschaftlichen IT-Kontext
Bestandteile eines technologischen Raumes
•
Anwendungsszenarien
•
Programmiersprachen
•
Datenrepräsentationsformate
•
Entwicklungswerkzeuge wie Compiler oder IDEs
•
Laufzeitwerkzeuge wie Bibliotheken
•
Anfrage- und Transformationssprachen
•
SQLware
— Etablierter Raum für Datenverwaltung
•
COBOLware
— Klassischer Raum für geschäftl. Progr.
•
Javaware
— Etablierter Raum für OO-Programmierung
•
XMLware
— Interoperabilität für Datenaustausch
•
JSONware
— Weiterer Raum für Interoperabilität
•
UMLware
— Raum für Softwaremodellierung
•
MDEware
— Model Driven Engineering
•
Lehrbücher und andere Wissensquellen der Gemeinschaft
•
RDFware
— Semantic Web und Linked Data
•
…
Seite
5
Unterteilung der technologischen
Räume nach dem „Daten“paradigma
•
•
•
Seite
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
6
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Bäume versus Tabellen versus Graphen
XML für
eine Firma
Tabellen!
•
Relationale Tabelle (SQL)
•
Schlüsselindizierte Dateien (Cobol)
Bäume!
Es gibt auch „Tricks“, um
in XML und JSON mit
Graphen zu arbeiten.
•
XML-Bäume
•
JSON-Bäume
•
Algebraische Terme (etwa in Haskell und F# und Skala)
Graphen!
http://en.wikipedia.org/wiki/
File:PSM_V18_D630_Restoration
_of_a_lepidodendron.jpg
•
Objektgraphen (etwa in Java)
•
Modelle (etwa Objektdiagramme in UML)
Seite
7
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Seite
8
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Bäume versus Tabellen versus Graphen
Bäume versus Tabellen versus Graphen
Tabellen für
eine Firma
:Company
Employee
Id
Name
Address
Salary
Manager
Department
1
Craig
Redmond
123456
TRUE
42
2
Erik
Utrecht
12345
FALSE
42
3
Ralf
Koblenz
1234
FALSE
42
…
…
…
…
…
…
ACME Corporation
Departments
[o,…]
Objektgraph
für eine Firma
:Department
Fremdschlüssel
Zeiger
Department
Research
Name
Departments
[…]
Employees
[o, o, o]
Company
:Employee
:Employee
:Employee
Id
Name
Department
Company
Id
Name
42
Research
NULL
88
88
ACME Corporation
Name
Craig
Name
Erik
Name
Ralf
43
Development
NULL
88
…
…
Address
Redmond
Address
Utrecht
Address
Koblenz
44
Dev1
43
88
Salary
123456
Salary
12345
Salary
1234
…
…
…
…
Manager
TRUE
Manager
FALSE
Manager
FALSE
Seite
9
Seite
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Unterteilung der technologischen Räume
nach dem „Programmier“paradigma
•
Prozedural (z.B. Cobol)
•
OO (z.B. Java)
•
Deklarativ
http://de.wikipedia.org/wiki/
Programmierparadigma
3.Juni 2014
•
Funktional (z.B. F#)
•
Logisch (z.B. Prolog)
•
Name
Algebraisch (z.B. SQL)
•
Modell-basiert (z.B. UML)
•
Multi-Paradigma
Seite
11
Ein Programmierparadigma ist ein fundamentaler
Programmierstil. […] „Der Programmierung
liegen je nach Design der einzelnen
Programmiersprache verschiedene Prinzipien
zugrunde. […] Programmierparadigmen
unterscheiden sich durch ihre Konzepte für die
Repräsentation von statischen (wie
beispielsweise Objekte, Methoden, Variablen,
Konstanten) und dynamischen (wie
beispielsweise Zuweisungen, Kontrollfluss,
Datenfluss) Programmelementen.
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
10
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Das OO-Paradigma
•
Objekte kapseln Zustand (Daten)
und Verhalten (Methoden).
•
Objekte haben eine Schnittstelle =
aufrufbare Methoden.
•
•
Methode1
Daten Methode2
Allein die Methoden greifen auf die
Daten zu.
Neue Objekte können instanziiert
werden mittels Klasse bzw. Prototyp.
Seite
12
Me
a
ss
ge
Methodenauswahl
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Objekte als Abstraktion von
physischen oder virtuellen Entitäten
Jedes Ding ist ein Objekt!
•
Type of object
Values
Attribute
: Person
Birthday: 15.12.68
Name: Lämmel
First: Ralf
Abstraction
program
eat
Methods
Seite
13
Physische Entitäten:
z.B. ein Student oder ein Haus
•
Beobachtbare Entitäten:
z.B. eine Wettersituation
•
Virtuelle Entitäten:
z.B. ein Konto in einer Online-Bank
•
Programmierte Entitäten:
z.B. ein Fenster oder ein Druckauftrag
Seite
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Einführendes Beispiel: Wir wollen die Struktur für eine Anwendung
im Personalwesen modellieren.
Unter
sc
Objek heidung v
on
ten in
Entwu Analyse,
r
f und
Imple
menta
tion.
14
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
UML-Klassendiagramm für die
Unternehmensstruktur
Unternehmen
MSFT
HR
Unter-
abteilungen
Angestellte
Abteilungen
Dev
Paul
Teil-GanzesBeziehung
VB
C#
Erik
Anders
Wir sehen auch
Methoden vor: „total“
zum Gehaltssummieren
und „cut“ zum Halbieren.
Seite
15
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Ausgewählte
Methoden
http://101companies.org/wiki/
Contribution:argoUML
Seite
16
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Eine Java-Klasse I/II
Eine Java-Klasse II/II
public class Department {
public class Department {
private
private
private
private
public
public
public
public
public
public
public double total() {
double total = 0;
total += getManager().getSalary();
for (Department s : getSubdepts())
total += s.total();
for (Employee e : getEmployees())
total += e.getSalary();
return total;
}
String name;
Employee manager;
List<Department> subdepts = new LinkedList<Department>();
List<Employee> employees = new LinkedList<Employee>();
String getName() { return name; }
void setName(String name) { this.name = name; }
Employee getManager() { return manager; }
void setManager(Employee manager) { this.manager = manager; }
List<Department> getSubdepts() { return subdepts; }
List<Employee> getEmployees() { return employees; }
public void cut() {
getManager().cut();
for (Department s : getSubdepts())
s.cut();
for (Employee e : getEmployees())
e.cut();
}
// Methods omitted
}
Getter und Setter für
Datenzugriff
Nächste Folie
Seite
17
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Vorige Folie
// State omitted
Privater Zustand
Steige in Objekt und
aggregiere Gehälter
Steige auch ab, aber
modifiziere Gehälter
}
Seite
18
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Anforderungen an SQLware
Im Gegensatz zu Cobol und Java
•
Programmiersprachenunabhängige Datenmodellierung
•
Persistenz für Programmdaten
•
Effizienz für schlüsselbasierten Zugriff und große Datenmengen
•
Trennung von Daten und Funktionalität
•
Deklarative Anfragesprache
•
Datenintegrität
Im Gegensatz zu (Basis-) OOP
101companies!
!
Im Gegensatz zu XML
Didaktische Verwendung eines Projektes der
AG Softwaresprachen in diesem Seminar.
Im Gegensatz zu OOP
Im Gegensatz zu OOP und Cobol.
Dies ist die Basis von Optimierungen.
Seite
19
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Seite
20
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Verwendung des 101companies-Projektes
Was ist das 101companies Projekt?
(Ein Projekt der Arbeitsgruppe SOFTLANG)
•
Relevante Ziele des Seminars
•
Beschauen von relationalen Schemabeispielen
•
Beschauen von SQL-Anfragebeispielen
•
Beispielhafte Verbindung zur OO-Programmierung
•
Bereitstellung von Ressourcen zu Konzepten
Seite
21
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Warum der Name „101companies“?
Company X:
Cobol
!
!
Company
Y:
! + Hibernate
Java + Swing
! !
!
Company
Z:
!
Python + Django
+ HTML5
! !
!
!
!
!
!
!
!
Company X:
Cobol
!
!
Company
Y:
! + Hibernate
Java + Swing
! !
Eine Wissensbasis zu Programmier- bzw.
Softwaretechnologien, -techniken, und -
!
Company
Z:
!
Python + Django
+ HTML5
! !
!
!
!
!
!
!
!
...
Seite
22
23
beispielhaften Implementation bzw.
Modellierung eines Informationssystems
für das Personalwesen.
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Ein Informationssystem für das Personalwesen
Aus dem Englischen:
„101 ways of doing something“
Features!
• „total“: Gehälter summieren
• „cut“: Gehälter halbieren
• Nutzerinterface
• Persistenz in der Datenbank
• …
...
Seite
sprachen — auf der Basis der
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Seite
24
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Demo
http://101companies.org/wiki/
Contribution:cobol
Ein paar Zahlen zum Projekt „101“
8
#Files per implementation Technologies
Languages
LOC per implementation
442
Seite
25
Seite
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Fragebogen
26
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
softlang.wikidot.com/course:dbintro
Gliederung
•
Wer ist im technologischen Raum Cobolware unterwegs?
•
Wer hat schon nennenswert SQL-Anfragen benutzt?
•
Wen interessiert die Verbindung Datenbank und OOP?
2. Das Relationale Modell!
•
… die Verbindung Datenbank und WebApp?
3. Datendefinition (SQL DDL)
•
… NoSQL anstatt allein SQL?
4. Datenmanipulation (SQL DML)
•
… fortgeschrittene SQL-Themen wie Trigger und Gespeicherte Prozeduren?
5. Eingebettete Programmierung
•
…
Eine
V
der S orstellung
s
emin
arteil runde
nehm
er?
Seite
27
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
1. Einführung
6. Objektrelationale Abbildung
7. … ?
Seite
28
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Relationen (Tabellen)
Das Relationale Modell!
!
Grundlage der relationalen Datenbanken.
Grundlage des technologischen Raumes SQLware.
Relation (Tabelle)!
Vertikal: Menge von Tupeln (“Zeilen”)!
Horizontal: Menge von Spalten!
Jede Zelle ist von einem primitiven Typ: !
Zeichenkette (String)!
Zahlen (INTEGER, DOUBLE, …)
Seite
29
Seite
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
30
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Relationale Schemata zur
Definition der Tabellenstruktur
Relationale Schemata zur
Definition der Tabellenstruktur
company (id INT, name STR)!
company (id INT, name STR)!
department (id INT, name STR, cid INT, did INT)!
department (id INT, name STR, cid INT, did INT)!
employee (id INT,!! ! ! ! ! ! ! ! ! ! ! !
! name STR, address STR, salary NUM, manager BOOL,!
! cid INT, did INT)
employee (id INT,!! ! ! ! ! ! ! ! ! ! ! !
! name STR, address STR, salary NUM, manager BOOL,!
! cid INT, did INT)
Schlüsselbedingungen („Key constraints“): Ein primärer
Schlüssel (unterstrichen) dient der eindeutigen Identifikation
einer Zeile. Ein sekundärer Schlüssel (kursiv) ist ein Verweis
auf eine Zeile einer anderen Tabelle.
Seite
31
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Vari
ante
Die Firma eines Angestellten ist
auch über die Zuordnung zu einer
Abteilung repräsentiert.
Seite
32
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Relationale Schemata zur
Definition der Tabellenstruktur
Relationale Algebra zur
„Berechnung“ neuer Tabellen
company (id INT, name STR)!
department (id INT, name STR, cid INT, did INT, mid INT)!
employee (id INT,!! ! ! ! ! ! ! ! ! ! ! !
! name STR, address STR, salary NUM, manager BOOL,!
! cid INT, did INT)
Vari
a
Der Manager einer Abteilung kann
auch über die Abteilung direkt
beschrieben werden.
nte
Seite
33
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Projektion
(Wähle Spalten aus.)
Seite
35
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Projektion (Wähle Spalten aus.)!
Selektion (Wähle Zeilen aus.)!
Join (Füge zwei Tabellen horizontal zusammen.)
Seite
34
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Selektion
(Wähle Zeilen aus.)
Seite
36
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Join
(Füge zwei Tabellen horizontal zusammen.)
Company Name
ACME Corporation
ACME Corporation
ACME Corporation
ACME Corporation
ACME Corporation
ACME Corporation
ACME Corporation
Normalformen!
!
Vermeidung von Redundanzen.
Vermeidung von Problemen bei CRUD.
Unterstützung der Datenintegrität.
Ergänze die Tabelle „Employee“ um die Spalte mit
dem Firmennamen aus der Tabelle „Company“
vermöge „Join“ über den Schlüssel für die Firma.
Seite
37
Seite
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
38
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Probleme bei fehlender Normalisierung
Erste Normalform (1NF)
Quelle: http://www.studytonight.com/dbms/database-normalization.php
Quelle: http://www.studytonight.com/dbms/database-normalization.php
Alle Zellen enthalten nur atomare Daten.
Es gibt keine Wiederholungsgruppen von Spalten.
Nicht in 1NF
•
•
•
Änderungsanomalie: Um die Adresse eines Studenten zu ändern,
müssen eventuell mehrere Zeilen geändert werden — sonst werden die
Daten inkonsistent.
Einfügungsanomalie: Das Einfügen eines neuen Studenten ohne die
gleichzeitige Zuordnung zu einem Kurs erfordert einen NULL-Wert für
Subject_opted.
Entfernungsanomalie: Das Entfernen einer Belegung eines Kurses
führt u.U. zum totalen Entfernen des Studierenden aus der Datenbank,
wenn keine weiteren Kursbelegungen vorliegen.
Seite
39
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
In 1NF
Primärschlüssel
sei dies:
Student, Subject
Seite
40
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Zweite Normalform (2NF)
Zweite Normalform (2NF)
Quelle: http://www.studytonight.com/dbms/database-normalization.php
Quelle: http://www.studytonight.com/dbms/database-normalization.php
Es liegt 1NF vor.
Es hängt kein Nichtschlüsselattribut funktional ab von einem Teil des Schlüssels.
Es liegt 1NF vor.
Es hängt kein Nichtschlüsselattribut funktional ab von einem Teil des Schlüssels.
Nicht In 2NF
In 2NF
„Age“ hängt allein von „Student“ ab.
(Es hängt nicht von „Subject“ ab.)
Seite
41
Primärschlüssel
ist noch dies:
Student, Subject
Extra Tabelle
Seite
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Dritte Normalform (3NF)
42
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
softlang.wikidot.com/course:dbintro
Quelle: http://www.studytonight.com/dbms/database-normalization.php
Gliederung
Es liegt 2NF vor.
Es gibt keine transitiven funktionalen Abhängigkeiten.
1. Einführung
Tabelle Student_Detail (Nicht in 3NF)
2. Das Relationale Modell
„State“ und eventuell auch „Street“
und „city“ hängen von „Zip“ ab.
Tabelle Student_Detail (In 3NF)
3. Datendefinition (SQL DDL)!
4. Datenmanipulation (SQL DML)
5. Eingebettete Programmierung
Eine extra Tabelle mit Zip, … als
primärem Schlüssel
Tabelle Address (Zusätzlich)
6. Objektrelationale Abbildung
7. … ?
Seite
43
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Seite
44
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
SQL (Structured Query Language)
http://101companies.org/wiki/Language:SQL
•
Der Fokus auf „Query“ (Anfrage) im
Namen ist eventuell irreführend.
Teile von SQL
SQL (Structured Query Language)!
•
Datendefinition („Definiere relationale Schemata.“)
!
•
Datenmanipulation
Die Datendefinitions-, Anfrage-, und
Transformationssprache des
technologischen Raumes SQLware.
Seite
45
•
Anfragen („Relationale Algebra“)
•
Der Rest von CRUD:!
•
Create:
Einfügen von Zeilen (INSERT in SQL)
•
Read:
Anfragen (SELECT in SQL)
•
Update:
Ändern (UPDATE in SQL)
•
Delete:
Löschen (DELETE in SQL)
Seite
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
46
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Datendefinition für eine Tabelle
Relationales Schema
company (id INT, name STR)
Datendefinition!
SQL DDL
!
(SQL DDL — Data Definition Language)
CREATE TABLE company (!
!
id INTEGER PRIMARY KEY,!
!
name VARCHAR(100) UNIQUE NOT NULL!
)
Seite
47
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Seite
48
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Mehr Details der Datendefinition
Datendefinition für eine weitere Tabelle
CREATE TABLE employee (!
CREATE TABLE department (!
!
id INTEGER PRIMARY KEY,!
!
id INTEGER PRIMARY KEY,!
!
name VARCHAR(50) NOT NULL,!
!
name VARCHAR(100) NOT NULL,!
!
address VARCHAR(50) NOT NULL,!
!
cid INTEGER NOT NULL,!
!
salary DOUBLE NOT NULL,!
!
did INTEGER,!
manager BOOL NOT NULL,!
!
cid INTEGER NOT NULL,!
!
FOREIGN KEY (cid) REFERENCES company(id),!
!
did INTEGER NOT NULL,!
!
FOREIGN KEY (did) REFERENCES department(id)!
!
FOREIGN KEY (cid) REFERENCES company(id),!
!
FOREIGN KEY (did) REFERENCES department(id)!
)
)
Seite
49
Seite
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
50
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
softlang.wikidot.com/course:dbintro
Zusammenfassung zu SQL DDL
Gliederung
1. Einführung
2. Das Relationale Modell
CREATE TABLE!
3. Datendefinition (SQL DDL)
INTEGER, VARCHAR(…), DOUBLE, BOOL!
NOT NULL, UNIQUE!
PRIMARY / FOREIGN KEY … REFERENCES
4. Datenmanipulation (SQL DML)!
5. Eingebettete Programmierung
6. Objektrelationale Abbildung
7. … ?
Seite
51
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Seite
52
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD
Datenmanipulation!
•
C: Create (Insert in SQL)
!
•
R: Read (Select in SQL)
•
U: Update (Update in SQL)
•
D: Delete (Delete in SQL)
(SQL DML — Data Manipulation Language)
Seite
53
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (CREATE)
INSERT INTO company (name) VALUES ("Acme Corporation")
Füge eine Firma in die
Tabelle „Company“ ein.
Seite
55
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Seite
54
CRUD ist älter als SQL. CREATE
im Sinne von SQL ist nicht das
CREATE im Sinne von CRUD.
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (CREATE)
INSERT INTO department (name,cid) VALUES ("Research",1)
INSERT INTO department (name,cid)
VALUES ("Development",1)
...
Füge Zeilen für verschiedene
Abteilungen in die Tabelle
„Department“ ein.
Seite
56
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (READ)
SELECT * FROM department
Demo von SQL DDL und
einfachsten Anfragen
http://101companies.org/wiki/
Contribution:mySqlMany
Selektiere alle Zeilen und Spalten
von der Tabelle „Department“.
Seite
57
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (READ)
SELECT name, salary FROM employee
Seite
58
CRUD (READ)
SELECT name, salary FROM employee
WHERE manager = TRUE
Projektion: Liste die
Spalten für Namen
und Gehalt aus der
Tabelle „Employee“
auf.
Seite
59
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Selektion: Auswahl
der Zeilen von
Angestellten, welche
Manager sind.
Seite 60
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (READ)
CRUD (READ)
SELECT name, salary FROM employee
ORDER BY salary
SELECT name, salary FROM employee
ORDER BY salary ASC
Die Sortierung der
Zeilen erfolgt
aufsteigend nach
Gehalt.
Seite 61
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (READ)
SELECT name, salary FROM employee
ORDER BY salary DESC
Sortierung in
aufsteigender
Ordnung
Seite 62
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (READ)
SELECT department.name, company.name FROM department, company
WHERE cid = company.id
Das ist ein
„Equi-Join“.
Sortierung in
absteigender
Ordnung
Führe ein „Join“ aus, um Namen für
Abteilungen und Firmen zu kombinieren.
Seite 63
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Seite
64
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (READ)
CRUD (READ)
SELECT d1.name, d2.name
SELECT d1.name, d2.name
FROM department AS d1, department AS d2
FROM department AS d1, department AS d2
WHERE d1.did = d2.id
Das ist ein
„Equi-Join“.
Das ist ein
„Cross-Join“.
Alle Kombinationen von
Abteilungsnamen. Dieses
Beispiel ist vermutlich
wenig sinnvoll.
Seite
65
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (READ)
INNER JOIN: Zeige die übergeordnete Abteilung
für jede Abteilung. (Wir verwenden auch Aliasing.)
Seite
66
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (READ)
SELECT d1.name, d2.name
SELECT d1.name, d2.name
FROM department AS d1
FROM department AS d1
INNER JOIN department AS d2
LEFT OUTER JOIN department AS d2
WHERE d1.did = d2.id
Explizite JoinNotation
ON d1.did = d2.id
LEFT OUTER JOIN:
Zeilen mit NULLSchlüssel werden
trotzdem gezeigt.
INNER JOIN: Zeige die übergeordnete Abteilung
für jede Abteilung. (Wir verwenden auch Aliasing.)
Seite
67
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Seite
68
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (READ)
SELECT d1.name, d2.name
FROM department AS d1
LEFT JOIN department AS d2
INNER versus OUTER JOIN
Quelle: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
Zwei Beispieltabellen
ON d1.did = d2.id
Optionales
Schlüsselwort.
Seite
69
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Seite
70
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
INNER JOIN
FULL OUTER JOIN
Quelle: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
Quelle: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
Das Ergebnis enthält allein die
passenden Zeilen aus beiden Tabellen.
Das Ergebnis enthält alle Zeilen aus
beiden Tabellen.
Seite
71
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Seite
72
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
LEFT OUTER JOIN
LEFT OUTER JOIN mit WHERE
Quelle: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
Quelle: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
Das Ergebnis enthält die Zeilen der erste Tabelle und
möglicherweise leere Spalten für die zweite Tabelle.
Wir zeigen hier nur Zeilen mit passenden Zeilen aus
der ersten Tabelle.
Seite
73
Seite
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
FULL OUTER JOIN mit WHERE
74
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (READ)
Quelle: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
SELECT SUM(salary) FROM employee
Das ist eine Aggregationsfunktion
zur Summierung.
Resultat
ist Zahl!
Summiere die Gehälter aller
Angestellten auf,
Seite
75
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Seite
76
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (READ)
CRUD (READ)
SELECT SUM(salary) FROM employee
SELECT SUM(salary) FROM employee
WHERE cid = 1
WHERE manager = true;
Das ist eine Bedingung zu den
Angestellten von Interesse.
Achtung: Unsere Beispieldaten
benutzen ohnehin nur eine Firma.
Resultat
ist Zahl!
Summiere nur die Gehälter
von Managern auf.
Summiere nur die Gehälter
von einer Firma auf.
Seite
77
Seite
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (READ)
SELECT SUM(salary) FROM employee
WHERE cid = 1 AND manager = TRUE
Wir kombinieren zwei
Bedingungen.
Resultat
ist Zahl!
Summiere nur die Gehälter von
Managern aus einer Firma auf.
Seite
79
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Das ist eine Bedingung zu den
Angestellten von Interesse.
Resultat
ist Zahl!
78
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (READ)
SELECT SUM(salary) FROM employee
WHERE cid = (SELECT id FROM company
WHERE name = "Acme Corporation")
Resultat
ist Zahl!
Benutze eine verschachtelte
Anfrage um den bekannten
Namen einer Firma auf den
Schlüsselwert abzubilden.
Seite
80
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (READ)
SELECT manager, SUM(salary)
FROM employee
GROUP BY manager
Eine Aggregation
wird ermitteln für
Gruppen mit
gemeinsamen Wert
für eine Spalte.
Seite 81
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (UPDATE)
UPDATE employee
SET salary = salary / 2
Halbiere alle Gehälter.
Seite
82
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
CRUD (UPDATE)
UPDATE employee
SET salary = salary / 2
WHERE manager = TRUE
Schränke die betroffenen
Angestellten ein.
Seite
83
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Demo von SQL DML
http://101companies.org/wiki/
Contribution:mySqlMany
Seite
84
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
softlang.wikidot.com/course:dbintro
Gliederung
1. Einführung
2. Das Relationale Modell
3. Datendefinition (SQL DDL)
4. Datenmanipulation (SQL DML)
5. Eingebettete Programmierung!
6. Objektrelationale Abbildung
https://github.com/101companies/101repo/tree/master/contributions/mySqlMany/scripts
Seite
85
7. … ?
Seite
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
86
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Eingebettete Anfragen
am Beispiel von „Total“
Eingebettete Programmierung!
String-Repräsentation
von SQL-Anfragen
Explizit Deklaration von
Anfrageparametern
!
Die Verbindung zwischen SQL
(relationalen Datenbanken) und
Programmierung in Java (und Cobol).
Eintragen des
Parameters
Iteration über
die Antwort
Seite
87
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Vorüberprüfung
der Anfrage
Ausführen der
Anfrage
Seite
88
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Eingebettete Anfragen
Wesentliche Typen im Umgang
mit Eingebetteter Programmierung
•
Connection: Verbindung zum Datenbanksystem
•
(Prepapred)Statement: Repräsentation SQL-Anfragen
•
ResultSet: Sequenzen von Resultaten (Zeilen als Felder)
•
SQLException: Besondere Ausnahme in diesem Kontext
Seite
89
am Beispiel von „Cut“
Beim C, U, D von CRUD gibt es
keine Resultatssequenz sondern
nur ein „Return code“.
Seite
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Server- versus Client-seitige Anfragen
90
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Server- versus Client-seitige Anfragen
•
Anfragen nach Möglichkeit auf dem Server
Minimierung des Datenvolumens für Übertragung
Möglichkeit der Optimierung auf dem Server
Client-seitige Ausführung
Server-seitige Ausführung
Quelle: http://www.codeproject.com/Articles/22839/SQL-Server-Interview-Questions-Part
Seite
91
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
SQL-Einschleusung
(SQL Injection)
•
Modifikation der Intention einer parametrischen SQL-Anfragen
durch Ausnutzung von Metazeichen / Maskierung mittels
Nutzereingaben.
•
Beispiel (Quelle: http://en.wikipedia.org/wiki/SQL_injection):
•
Anzeige von Nutzerdetails:
•
•
Demo von SQL DML
"SELECT * FROM users WHERE name ='" + userName + „';"
http://101companies.org/wiki/
Contribution:simplejdbc
Möglicher aktueller Parameter userName:
•
' or '1'='1
Damit wird Information über ALLE Nutzer
preisgegeben anstatt über einen bestimmten Nutzer.
Ähnlich kann man ein „DROP TABLE“ injizieren.
Seite
93
Seite
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
94
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
softlang.wikidot.com/course:dbintro
Gliederung
1. Einführung
2. Das Relationale Modell
3. Datendefinition (SQL DDL)
4. Datenmanipulation (SQL DML)
5. Eingebettete Programmierung
6. Objektrelationale Abbildung!
https://github.com/101companies/101simplejava/tree/master/contributions/simplejdbc
Seite
95
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
7. … ?
Seite
96
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Abbildung zwischen O/R/X
Objekte
Objektrelationale Abbildung!
!
Datenmanagement allein auf der OOEbene etwa mit Persistenz vermöge
gekoppelter (abgebildeter) Tabellen.
Tabellen
XML
Das ist nicht wirklich ein Dreieck.
Beispiele: Cobol and JSON
Seite
97
Seite
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Interplanetarische Reisen zwischen den
technologischen Räumen SQLware und Javaware
98
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Technologien für die Raumfahrt
XMI
Modelware
Teneo
XMLware
JAXB
JMI
EMF.gen
JDOM
Sesame
Dataware
Ontoware
JDBC
Hibernate JPA
Javaware
Jena
Quelle: http://www.nasa.gov/images/content/63114main_highway_med.jpg
Seite
99
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Seite 100
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Warum ist (technologische)
Raumfahrt eine Herausforderung?
•
Verschiedene Terminologien und Koventionen
•
Verschiedene Berechnungsmodelle und Typsysteme
•
Abhängigkeit von konkreten Sprachen und Technologien
•
Unnötige Komplexität
•
…
Seite 101
Zur Erinnerung: Ein Objektmodell für Firmen
public class Company {
private String name;
private List<Department> depts = new LinkedList<Department>();
public String getName() { return name; }
public void setName(String name) { this.name = name; }
public List<Department> getDepts() { return depts; }
}
!
public class Department { ... }
!
nce
peda am
m
I
„
:
n
worte
„Viet
Stich ch“ oder ence“
t
i
a
c
mism mputer S
of Co
public class Employee { ... }
Seite 102
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Zum Vergleich: Ein relationales Schema
Zum Vergleich: Ein XML-Schema für Firmen
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
<xs:element name="company">
<xs:complexType>
<xs:sequence>
<xs:element ref="name"/>
<xs:element maxOccurs="unbounded" minOccurs="0" ref="department"/>
</xs:sequence>
</xs:complexType>
</xs:element>
CREATE TABLE company (!
!
CREATE TABLE employee ( ... )
<xs:element name="department"> ... </xs:element> !
id INTEGER PRIMARY KEY,!
!
name VARCHAR(100) UNIQUE NOT NULL!
)!
CREATE TABLE department ( ... )!
!
<xs:complexType name="employee"> ... </xs:complexType>
Seite 103
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Seite 104
Im Vergleich zum
Objektmodell: Abteilungen
referenzieren die Firma
und nicht umgekehrt.
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Ziel
•
Persistieren von Objekten in einer Datenbank
•
Abbilden von Objektmodellen auf relationale Schemata
•
Abbilden von relationalen Schemata auf Objektmodelle
•
Vermittlung zwischen Modellen/Schemata beidseitig
Persistenz
(am Beispiel von Hibernate/JPA)
http://www.hibernate.org
Fähigkeiten!
Speicherung von Objekten in der DB; eine Zeile pro Objekt.!
Reaktivierung von Objekten in späteren Programmläufen.
Seite 105
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Die Architektur von Hibernate
Seite 106
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Eine persistierbare Klasse (POJO)
public class Cat {
private String id;
private String name;
private char sex;
private float weight;
public String getId() { return id; }
private void setId(String id) { this.id = id; }
// … other getters and setters …
}
Verwendung für
Primärschlüssel
Quelle: http://www.up.ac.za/services/it/intranet/sysops/docs/hibernate/HibernateSingleHTML.htm
Seite 107
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Seite 108
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Metadaten für Abbildung
<hibernate-mapping>
<class name=“Cat" table="CAT“>
<id name="id" type="string" unsaved-value="null" >
<column name="CAT_ID" sql-type="char(32)" not-null="true"/>
<generator class="uuid.hex"/>
</id>
Bilde JavaStrings auf
<property name="name“>
SQL-Strings ab.
<column name="NAME" length="16" not-null="true"/>
</property>
<property name="sex"/>
<property name="weight"/>
</class>
</hibernate-mapping>
Seite 109
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Eine Hibernate-„Sitzung“ in
einem Java-Programm
Session session = HibernateUtil.currentSession();
Transaction tx= session.beginTransaction();
Beginn einer
Transaktion
!
Cat princess = new Cat();
princess.setName("Princess");
princess.setSex('F');
princess.setWeight(7.4f);
Regulärer OOCode
!
session.save(princess);
tx.commit();
HibernateUtil.closeSession();
Seite 111
Die resultierende Tabelle
!
Column | Type
| Modifiers
--------+-----------------------+----------cat_id | character(32)
| not null
name
| character varying(16) | not null
sex
| character(1)
|
weight | real
|
!
Index: cat_pkey primary key btree (cat_id)
Seite 110
Verwendung von Anfragen zur Herstellung von Objekten
Query query = session.createQuery(
"select c from Cat as c where c.sex = :sex");
query.setCharacter("sex", 'F');
for (Iterator it = query.iterate(); it.hasNext();) {
Cat cat = (Cat) it.next();
out.println("Female Cat: " + cat.getName() );
}
Registrierung
eines Objektes
für Persistenz
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Es kommt hier HQL (Hibernate
Query Language) zum Einsatz.
HQL ist sehr ähnlich zu SQL.
Seite 112
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Verzeichnis mit HSQLDB-DB
Konfiguration von Hibernate
Demo von Hibernate für
das 101companies-Projekt
Aufrufbeispiele
Objektmodell mit
Abbildungsdateien
101companies.org/wiki/
Contribution:hibernate
Typische Funktionalität
SQL-Dateien mit relationalem
Schema und Beispieldaten
Seite 113
Seite 114
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Eine POJO-Klasse
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Abbildung für Angestellte
public class Employee {
!
private
private
private
private
private
long id;
String name;
String address;
double salary;
boolean manager;
!
public long getId() {
return id;
}
@SuppressWarnings("unused")
private void setId(long id) {
this.id = id;
}
...
}
Seite 115
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
<hibernate-mapping>
<class name="org.softlang.company.Employee" table="EMPLOYEE">
<id name="id" column="ID">
<generator class="native" />
</id>
<property
<property
<property
<property
name="name" />
name="address" />
name="salary" />
name="manager" />
</class>
</hibernate-mapping>
Seite 116
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Abbildung für Abteilungen
<hibernate-mapping>
Konfiguration von Hibernate
<class name="org.softlang.company.Department" table="DEPARTMENT">
<id name="id" column="ID">
<generator class="native" />
</id>
<hibernate-configuration>
<session-factory>
<!-- Database connection settings. -->
<property name="connection.driver_class">org.hsqldb.jdbcDriver</property>
<property name="connection.url">jdbc:hsqldb:hsql://localhost</property>
<property name="connection.username">sa</property>
<property name="connection.password"></property>
<property name="name" />
<set name="employees" cascade="all">
<key column="DEPT_ID" />
<one-to-many class="org.softlang.company.Employee" />
</set>
<set name="subdepts" cascade="all">
<key column="DEPT_ID" />
<one-to-many class="org.softlang.company.Department" />
</set>
<!-- SQL dialect -->
<property name="dialect">org.hibernate.dialect.HSQLDialect</property>
<!-- Create the database schema, if needed; update otherwise -->
<property name="hbm2ddl.auto">update</property>
<!-- Mapping files in the project -->
<mapping resource="org/softlang/company/Company.hbm.xml" />
<mapping resource="org/softlang/company/Department.hbm.xml" />
<mapping resource="org/softlang/company/Employee.hbm.xml" />
...
</session-factory>
</hibernate-configuration>
</class>
</hibernate-mapping>
Seite 117
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Auslassungen
Seite 118
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
softlang.wikidot.com/course:dbintro
Danke für Ihre Aufmerksamkeit
und Teilnahme. Bitte evaluieren
Sie dieses Seminar.
•
ER-Modell
•
Trigger
•
Gespeicherte Prozeduren
•
Transaktionen
•
No-SQL
•
OO-Datenbanken
•
Datenbanken im Kontext von WebApps
Seite 119
Ihr SOFTLANG Team.
Prof. Dr. R. Lämmel
http://softlang.wikidot.com/startseite
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau
Seite 120
© 2014 Ralf Lämmel & SOFTLANG, Universität Koblenz-Landau