Tooling

Entwicklungsumgebung für die Übung
VU Datenbanksysteme
Wolfgang Fischl
Arbeitsbereich Datenbanken und Artificial Intelligence
Institut für Informationssysteme
Technische Universität Wien
Wintersemester 2016/17
1 / 35
Gliederung
PostgreSQL
PostgreSQL Datenbank
psql
pgAdmin
Bordo Server
Beispiel Datenbank
Beispiel 1
Zusammenfassung
2 / 35
PostgreSQL Datenbank
I
Für die Übung verwenden wir PostgreSQL Server 9.4.9
I
Kann von https://www.postgresql.org/
heruntergeladen werden
I
Enthält
I
I
I
I
Datenbankserver (postgresql),
Datenbankclient (psql) und
grafisches Tool zur Datenbankadministration (pgAdmin)
https://www.postgresql.org/docs/9.4/static/
index.html
3 / 35
PostgreSQL Datenbank
I
Für die Übung verwenden wir PostgreSQL Server 9.4.9
I
Kann von https://www.postgresql.org/
heruntergeladen werden
I
Enthält
I
I
I
I
Datenbankserver (postgresql),
Datenbankclient (psql) und
grafisches Tool zur Datenbankadministration (pgAdmin)
https://www.postgresql.org/docs/9.4/static/
index.html
3 / 35
PostgreSQL Datenbank
I
Für die Übung verwenden wir PostgreSQL Server 9.4.9
I
Kann von https://www.postgresql.org/
heruntergeladen werden
I
Enthält
I
I
I
I
Datenbankserver (postgresql),
Datenbankclient (psql) und
grafisches Tool zur Datenbankadministration (pgAdmin)
https://www.postgresql.org/docs/9.4/static/
index.html
3 / 35
psql - Verbinden zur Datenbank
I
Starten von psql
I
Default DB: postgres
I
Default user: postgres
I
Default PW: =empty= (bei der Installation gesetzt)
4 / 35
psql - Datenbank für DBS anlegen
I
Datenbank für DBS anlegen
I
Zur Datenbank wechseln
5 / 35
psql - Beschreibungsbefehle
\d+
\df+
\d+ table
\df+ functions
Tabellen auflisten
Funktionen auflisten
Tabelle beschreiben
Funktion beschreiben
Beispiel
SELECT column name FROM
INFORMATION SCHEMA .COLUMNS
WHERE table name = ’ t a b l e ’ ;
6 / 35
psql - Weitere Befehle
I
Wichtige Befehle für die Laborübung
\o datei
\o
\i datei
I
Output wird in datei geschrieben
Ende der Ausgabe in datei
Führt die Befehle aus datei aus
Hilfe?!
\h
\?
\q
Hilfe über SQL-Anweisungen
Hilfe über interne Anweisungen
psql beenden
7 / 35
psql - Weitere Befehle
I
Wichtige Befehle für die Laborübung
\o datei
\o
\i datei
I
Output wird in datei geschrieben
Ende der Ausgabe in datei
Führt die Befehle aus datei aus
Hilfe?!
\h
\?
\q
Hilfe über SQL-Anweisungen
Hilfe über interne Anweisungen
psql beenden
7 / 35
pgAdmin
I
Grafisches Tool zur Datenbankadministration
I
SQL Editor, Anzeigen von Tabellen, Datenbanken
I
http://www.pgadmin.org
8 / 35
Einrichtung von pgAdmin
I
Zu PostgreSQL verbunden
I
Schaltfläche SQL öffnet Query Fenster
9 / 35
Einrichtung von pgAdmin - SQL
10 / 35
Gliederung
PostgreSQL
Bordo Server
Übung
Secure Shell
PostgreSQL Datenbank auf Bordo
Editoren auf Bordo
Beispiel Datenbank
Beispiel 1
Zusammenfassung
11 / 35
Übung
I
I
Adresse: bordo.dbai.tuwien.ac.at
Zugriff:
I
I
I
I
Von überall“ her möglich (z.B. Übungsräume, andere
”
Rechner im TU-Netz, zu Hause, in der Firma, etc.)
ausschließlich mittels Secure Shell (egal von wo aus)
Betriebssystem auf bordo: Debian
Linux Kennung:
I
I
I
Alle Übungsteilnehmer erhalten nach der Anmeldung eine
Linux Kennung mit Usernamen u<matrikelnummer>.
Das Passwort wird am 18.10.2016 an die in TISS
hinterlegte E-Mail zugesandt.
Passwort ändern: mit Linux-Befehl passwd. Geändertes
Passwort nicht vergessen!
12 / 35
Secure Shell
I
Von Windows-PC aus:
I
I
I
SSH-Client (PuTTY) und SCP-Client (PSCP):
http://www.chiark.greenend.org.uk/
˜sgtatham/putty/download.html
Bequemer Datentransfer: WinSCP http://winscp.net/
Von Linux-PC aus:
I
I
ssh und scp sind ohnehin vorhanden
Bequemer Datentransfer: sftp mit Konquerer oder Nautilus
Beispiel
I
I
ssh [email protected]
scp bsp1.sql
[email protected]:abgabe1.sql
13 / 35
PostgreSQL Datenbank auf Bordo
DB-Server auf Bordo:
I
PostgreSQL 9.4
DB-Benutzung:
I
Alle LVA-TeilnehmerInnen erhalten eine eigene
DB-Kennung in der Datenbank
I
DB-Username: gleich wie Linux-Username
I
Starten einer interaktiven Session: mit dem Kommando
psql (im ssh-Fenster). Authentifikation über SSH Account,
d.h. kein Passwort notwendig
14 / 35
Editoren auf Bordo
I
emacs: Hilfe im Web, z.B.
http://www.gnu.org/software/emacs/tour/
I
vi: Hilfe im Web, z.B. http://www.library.yale.
edu/wsg/docs/vi_hands_on/
I
pico: Hilfe im Web, z.B. http://www.uic.edu/depts/
accc/software/pine/pico.html
Alternativen:
I
I
I
Entwicklung von SQL und PL/SQL auf dem lokalen PC und
Upload nach Bordo mittels scp (oder sftp).
pgAdmin mit Bordo verbinden (ohne Support: siehe
AdvTooling.pdf)
15 / 35
Gliederung
PostgreSQL
Bordo Server
Beispiel Datenbank
CREATE Tables
SQL Statement
PL/SQL Prozedur
Beispiel 1
Zusammenfassung
16 / 35
CREATE Tables
17 / 35
CREATE Tables
I
Datei 01 create.sql:
CREATE TABLE Vorlesungen (
V o r l N r INTEGER PRIMARY KEY,
SWS INTEGER ,
name VARCHAR( 5 0 )
);
CREATE TABLE Studenten (
MatrNr INTEGER PRIMARY KEY,
semester INTEGER ,
name VARCHAR( 5 0 )
);
CREATE TABLE hoeren (
V o r l N r INTEGER REFERENCES Vorlesungen ,
MatrNr INTEGER REFERENCES Studenten ,
PRIMARY KEY( VorlNr , MatrNr )
);
I
Einlesen und Kompilieren: \i 01 create.sql
18 / 35
CREATE Tables
I
Datei 02 insert . sql:
INSERT INTO Vorlesungen VALUES ( 1 , 4 , ’DBS ’ ) ;
INSERT INTO Vorlesungen VALUES ( 2 , 4 , ’DM ’ ) ;
INSERT INTO Vorlesungen VALUES ( 3 , 2 , ’SSD ’ ) ;
I
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
Studenten
Studenten
Studenten
Studenten
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
hoeren
hoeren
hoeren
hoeren
hoeren
hoeren
hoeren
hoeren
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
(1
(2
(1
(2
(3
(3
(2
(3
(1 ,1 ,
(2 ,2 ,
(3 ,2 ,
(4 ,4 ,
’ Franz ’ ) ;
’ Anna ’ ) ;
’ Maria ’ ) ;
’ Karl ’ ) ;
,2);
,2);
,3);
,3);
,3);
,1);
,4);
,4);
Einlesen und Kompilieren: \i 02 insert . sql
19 / 35
SQL Statement
Beispiel
Suche die Namen jener Studierenden, die alle 4-stündigen
Vorlesungen gehört haben.
I
Ersetzung von for all“ durch exists“:
”
”
Suche die Namen jener Studierenden, für die nicht gilt: Es
gibt eine 4-stündige VO, für die nicht gilt: der/die
Studierende hat diese VO gehört.
I
Geschachteltes SQL-Statement erstellen: schrittweise“
”
von innen nach außen.
20 / 35
SQL Statement
Innerstes SELECT:
I
Wähle MatrNr eines/r beliebigen Studierenden sowie
Vorlesungsnummer einer beliebigen Vorlesung:
SELECT ∗ FROM hoeren ORDER BY V o r l N r
SELECT MatrNr FROM s t u d e n t e n
SELECT DISTINCT MatrNr FROM hoeren
z.B., wähle MatrNr 1 und VorlNr 3
I
SELECT Statement (in Datei test1.sql editieren):
SELECT ∗ FROM hoeren h
WHERE h . V o r l N r = 3 AND h . MatrNr = 1
I
Datei einlesen und ausführen in psql: \i test1 . sql
21 / 35
SQL Statement
Mittleres SELECT:
I
Gesucht: Informationen über alle 4-stündigen Vorlesungen,
die von einem/r bestimmten Studierenden (z.B. wieder
der/die Studierende mit MatrNr 1) nicht gehört wurden.
I
SELECT Statement (in Datei test2.sql editieren):
SELECT ∗ FROM Vorlesungen v
WHERE v .SWS = 4 AND NOT EXISTS
(SELECT ∗ FROM hoeren h
WHERE h . V o r l N r = v . V o r l N r
AND h . MatrNr = 1 )
I
Datei einlesen und ausführen in psql: \i test2 . sql
22 / 35
SQL Statement
Äußeres SELECT:
I
Gesucht: Informationen über Studierende, für die es keine
4-stündigen Vorlesungen gibt, die von diesen/r
Studierenden nicht gehört wurden.
I
SELECT Statement (in Datei abgabe bsp1.sql editieren):
SELECT ∗ FROM Studenten s
WHERE NOT EXISTS
(SELECT ∗ FROM Vorlesungen v
WHERE v .SWS = 4 AND NOT EXISTS
(SELECT ∗ FROM hoeren h
WHERE h . V o r l N r = v . V o r l N r
AND h . MatrNr = s . MatrNr ) )
I
Datei einlesen und ausführen in psql: \i abgabe bsp1.sql
23 / 35
PL/SQL Prozedur
I
I
Gesucht: Prozedur, die den Namen und die
Semesteranzahl eines/r Studierenden ausgibt
Mögliche Vorgangsweise:
I
I
I
I
I
I
Entwicklung mittels pgAdmin
Am Anfang: nur Prozedur-Kopf und NULL“-Body
”
Schrittweise“ den Body hinzufügen
”
Nach jedem Schritt kompilieren.
Test-Treiber“ erstellen
”
Bildschirmausgaben mittels RAISE NOTICE / RAISE
DEBUG zwecks Fehlersuche
24 / 35
PL/SQL Prozedur
CREATE OR REPLACE FUNCTION suche ( m a t r n r i n t e g e r )
RETURNS v o i d AS $$
DECLARE
BEGIN
NULL ;
END;
$$ LANGUAGE p l p g s q l ;
SELECT suche ( 1 ) ;
25 / 35
PL/SQL Prozedur
CREATE OR REPLACE FUNCTION suche ( mnr i n t e g e r )
RETURNS v o i d AS $$
DECLARE
name
varchar ( 3 0 ) ;
semester numeric ( 2 ) ;
BEGIN
SELECT s . name , s . semester INTO name , semester
FROM s t ud e n t en s WHERE s . m a t r n r = mnr ;
RAISE NOTICE ’Name : % , Semester : % ’ , name , semester ;
END;
$$ LANGUAGE p l p g s q l ;
SELECT suche ( 1 ) ;
I
Aber was wenn kein Ergebnis gefunden wurde?
26 / 35
PL/SQL Prozedur
CREATE OR REPLACE FUNCTION suche ( mnr i n t e g e r )
RETURNS v o i d AS $$
DECLARE
name
varchar ( 3 0 ) ;
semester numeric ( 2 ) ;
BEGIN
SELECT s . name , s . semester INTO name , semester
FROM s t ud e n t en s WHERE s . m a t r n r = mnr ;
I F ( name IS NULL) THEN
RAISE NOTICE ’ S t u d i e r e n d e r m i t der MatrNr % n i c h t gefunden ’ , mnr ;
ELSE
RAISE NOTICE ’Name : % , Semester : % ’ , name , semester ;
END I F ;
END;
$$ LANGUAGE p l p g s q l ;
SELECT suche ( 1 0 ) ;
I
Datei einlesen und ausführen in SQL*Plus: \i plpgsql. sql
27 / 35
Gliederung
PostgreSQL
Bordo Server
Beispiel Datenbank
Beispiel 1
Zusammenfassung
28 / 35
Beispiel 1- Aufgabenstellung
I
http://dbai.tuwien.ac.at/education/dbs/
current/uebung/bsp1.html
I
Datenbank einer Theaterverwaltung
I
I
I
I
I
I
Beschreibung und ER-Diagramm gegeben
Anlegen der Tabellen in PostgreSQL
Anlegen von Testdaten
Datenauswertung mit SQL Queries
PL/pgSQL Trigger und Functions
Vollständiges Löschen der angelegten Objekte
29 / 35
Beispiel 1- Punktevergabe
I
Beim Abgabegespräch wird das Verständnis der Konzepte
überprüft!
I
Punkteverteilung
I
I
I
DB anlegen/löschen: max. 4 Punkte
Queries: max. 2 Punkte
PL/pgSQL: max. 4 Punkte
30 / 35
Beispiel 1- Korrekte Abgabe
I
1 ZIP Datei mit folgenden Dateien:
1.
2.
3.
4.
5.
6.
7.
create.sql
insert.sql
drop.sql
queries.sql
plpgsql.sql
test.sql
listing.txt
31 / 35
Beispiel 1- listing.txt
I
Kopiere alle Dateien auf Bordo, und dann auf Bordo:
u0602106@bordo:˜/bsp1$ ls
create.sql drop.sql insert.sql
test.sql
u0602106@bordo:˜/bsp1$ psql
psql (9.4.9)
Type "help" for help.
listing.txt
plpgsql.sql
queries.sql
u0602106=> \o listing.txt
u0602106=> \i create.sql
u0602106=> \i plpgsql.sql
u0602106=> \i insert.sql
u0602106=> \i queries.sql
u0602106=> \i test.sql
psql:test.sql:3: ERROR: new row for relation "rposition" violates check
constraint "rposition_wert_check"
DETAIL: Failing row contains (3, 2, 30, 2, 0.50, RPosition 3-2).
psql:test.sql:6: ERROR: new row for relation "sonstigeposition" violates check
constraint "sonstigeposition_wert_check"
DETAIL: Failing row contains (30, 2, 0.50, SonstigePosition 1).
psql:test.sql:10: ERROR: new row for relation "leistung" violates check
constraint "leistung_preis_check"
DETAIL: Failing row contains (5, Leistung 5, 0.80, 1).
psql:test.sql:14: ERROR: duplicate key value violates unique constraint
"rposition_anr_apnr_key"
DETAIL: Key (anr, apnr)=(30, 1) already exists.
psql:test.sql:18: ERROR: RPosition hat nicht den selben Auftrag
psql:test.sql:22: ERROR: Leistungsposition kann nicht SonstigePosition sein
(und umgekehrt)
psql:test.sql:25: ERROR: Leistungen haben eine zirkulaere Abhaengigkeit!
u0602106=> \i drop.sql
u0602106=> \q
32 / 35
Beispiel 1- beispiel1.zip
I
zB.: auf Bordo:
u0602106@bordo:˜/bsp1$ zip beispiel1.zip *
updating: create.sql (deflated 72%)
updating: drop.sql (deflated 64%)
updating: insert.sql (deflated 64%)
updating: listing.txt (deflated 73%)
updating: plpgsql.sql (deflated 66%)
updating: queries.sql (deflated 57%)
updating: test.sql (deflated 51%)
33 / 35
Gliederung
PostgreSQL
Bordo Server
Beispiel Datenbank
Beispiel 1
Zusammenfassung
34 / 35
Zusammenfassung
I
PostgreSQL installieren
I
Mit PostgreSQL DB verbinden und verwenden
I
psql und pgAdmin
I
Verbindung zu Übungsserver
bordo.dbai.tuwien.ac.at herstellen
I
Entwickeln einer einfachen Beispieldatenbank
I
I
I
I
I
Create Statements
SQL Statements
Stored Procedures
Drop Statements
Durchbesprechung des ersten Übungsbeispiels
35 / 35