Kap3_ErweiterteFunktionalitäten

Vertiefungsmodul Daten-, Informations- und Wissensmanagement
Einige erweiterte Funktionalitäten in SQL
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
1
Gliederung des Kapitels
a) Berechtigungskonzept
a) Benutzer und Rollen
b) Updateable Views
b)
c)
d)
e)
f)
WI
Metadaten
Parametrisierte Abfragen
Stored Procedures
Indices
Constraints und Triggers
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
2
aa) Benutzerverwaltung
•
•
Zentral für alle DBMS
Rollenkonzept
–
•
Rechte werden an Rollen vergeben
– Benutzer sind (n:m) Rollen zugeteilt
Genehmigung gewisser Aktivitäten + logging
−
GRANT privilege_name #
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];
− CREATE ROLE verwalter;
− GRANT verwalter TO joerg, sabine, harald;
− GRANT INSERT, SELECT, UPDATE(gehalt) ON mitarbeiter
TO verwalter WITH GRANT OPTION;
− REVOKE verwalter from joerg
•
WI
Einschränkung mittels VIEWS und updateable views
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
3
ab) Updateable Views
• In besonderen Fällen, in denen das DBMS eine eindeutige Zuordnung zwischen
den in der Sicht zu ändernden Daten und einer physikalischen Tabelle, zu der sie
gehören, herstellen kann, ist ein Update möglich. Beispiel für so eine
Updateable View wäre folgende triviale Sicht:
• CREATE VIEW ''SoftwareVerkaeufe2'' AS ''SELECT verkaeufe.kaeufer''
• Ein Update auf SoftwareVerkaeufe2 kann hier eindeutig
select verkaeufe.kaeufer
zugeordnet werden.
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
4
Updateable Views
•
Im Beispiel unten ist eine eindeutige Zuordnung nicht möglich, da produkt_id in
beiden Quellrelationen enthalten ist, ...
CREATE VIEW SoftwareVerkaeufe AS
SELECT v.kaeufer, v.verkaeufer, p.product_id
FROM produkte p, verkaeufe v
WHERE p.produkt_id = v.produkt_id -- ← Achtung!
AND p.produkt = "Software"
•
... und z. B. bei einer Löschung wie in ...
DELETE FROM SoftwareVerkaeufe WHERE produkt_id = 123456
... nicht entscheidbar ist, ob Datensätze aus Produkte oder Verkaeufe
gelöscht werden sollen.
•
WI
selbst ein Update SoftwareVerkaeufe set p.product_id = „alte_SW“
ist extrem gefährlich. Wenn nicht referentielle Integrität die Änderung verhindert,
kann es sein, dass wir wegen des inner join gar nicht mehr für eine Rückänderung
an den Eintrag rankommen
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
5
Updateable Views: in welchen Situationen verboten
Die geschilderte Anomalie entsteht generell in einer Situation, wo eine Durchführung der Änderung nicht den
Erwartungen des Benutzers entspricht oder nicht entscheidbar ist, welche Änderungen genau durchzuführen sind.
Man kann sie folgendermaßen einteilen:
•
•
•
•
WI
In einer Selektionssicht können Datensätze aus dem sichtbaren Bereich verschwinden, wenn ein in der Sicht
vorhandener Datensatz so geändert wird, dass er aus der Sicht herausfällt.
In einer Projektionssicht kann eine Einfügeoperation dann problematisch werden, wenn in der Originalrelation
Felder vorhanden sind, die belegt sein müssen (NOT NULL), aber nicht in der Sicht vorkommen, oder wenn die
Sicht durch die Angabe von DISTINCT gleiche Ergebnistupel zu einem zusammenfasst.
In einer Verbundsicht ist nicht immer entscheidbar, auf welcher Originalrelation die Operation auszuführen ist.
In einer Aggregationssicht kann nicht entschieden werden, wie die Operation umzusetzen ist. Zum Beispiel ist
nicht klar, wie eine Halbierung aller Verkaufszahlen auf die Originalrelation umzusetzen ist: Entweder kann die
Hälfte der Verkäufe gelöscht oder die einzelnen Verkäufe halbiert werden.
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
6
Probleme mit diesem Updateable(?) view
=„W“
Nname ändern?
Ausleiher auf „Null“
Umbuchung in „Ausleihe“auf gleiches Werk mit anderem Standort
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
7
Updateable Views Beispiel mySQL
(mySQL:) A view is not updatable if any of the following conditions are true:
 the keyword DISTINCT is used in the view definition
 the select list contains components other than column specifications, or contains more than one
specification of the same column
 the FROM clause specifies more than one table reference or refers to a non-updatable view
 the GROUP BY clause is used in the view definition
 the HAVING clause is used in the view definition
Ähnlich DB2
 Im Prinzip sollten Views mit 2+ Tabellen den gleichen Regeln wie Single Table Views unterliegen,
sofern alle Primär- und Fremdschlüssel im View vorkommen, aber: Die Entscheidung, ob ein View
updateable ist, wird letztlich vom DBMS getroffen
 Zusätzlich muss der Benutzer / die Rolle auch noch die entsprechenden Rechte auf den View
besitzen
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
7
b) Metadaten und Data Dictionary im Relationalen Modell
 ERD zum Aufbau „jedes“ ERD  Meta-ERD
 Meta-Tabellen in einem Datenbanksystem
 Ein ERD lässt sich aus den Systemtabellen (ggf. mit graphischer Position im
gezeichneten ERD) jederzeit rekonstruieren
 Ausschnitte aus einem ERD lassen sich als SQL-Abfragen gegen die MetaTabellen formulieren
 Beispiel: welche Tabellen sind in einer Relation mit „HAUPTTABELLE“ verbunden?
 Beobachtung: die Metatabellenstruktur ist für alle ERDs (und damit alle RDBMS)
gleich
 Die Namen und die Struktur der Metatabellen sind zwischen Herstellern
unterschiedlich
 Schreibende Zugriffe sind in aller Regel dem RDBMS vorbehalten
 Oft spezielle Befehle zum Lesen der Tabellen
(statt select … from sysTables ..)
 Auch andere Entwurfsmethoden (z.B. klassische Flussdiagramme) lassen sich
via Meta-ERD syntaktisch beschreiben
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
8
Meta-ERD in Krähenfuß-Notation
Quelle: http://www.infforum.de/themen/anwendungsentwicklung/thema_SE-methode_esa.htm
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
9
SQLITE Metadata
• Extract metadata about a column of a table
SELECT name, type
FROM sqlite_master
WHERE type in ('table', 'view')
AND name not like 'sqlite?_%' escape '?'
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
10
SQL Server
Beispiele (das Ergebnis lässt sich jeweils auch anders erzielen)
Tabellen
SELECT Owner = TABLE_SCHEMA, TableName = TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMsShipped') = 0
ORDER BY TABLE_SCHEMA, TABLE_NAME;
WI
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'test2';
Spalten
SELECT name
FROM sysobjects
WHERE xtype = 'V';
Views
http://www.alberton.info/sql_server_meta_info.html#.UKDjFX_3En0
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
11
c) Parametrisierte Queries
Motivation
? Welche Bücher haben die „Opuszko“s ausgeliehen?
SELECT BuchTitel, PLZ,… FROM Bücher INNER JOIN Personen ON
Bücher.AusleiherID = Personen.PersID WHERE Personen.Nachname =
‚Opuszko‘
Oder parametrisiert
SELECT BuchTitel, PLZ, … FROM Bücher INENR JOIN Personen ON
Bücher.AusleiherID = Personen.PersID WHERE Personen.Nachname
=[Nachname des Ausleihers:]
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
12
Parametrisierte Queries und Stored Procedures
Parametrisierte Queries
• Abfrageparameter werden erst zur Laufzeit eingegeben
• Struktur der Abfrage bleibt
select … from … where EingangsDatum Between [Start date:] And [End date:]
• Vorteile
– Vorformulierung durch den Abfragedesigner
– “Voroptimierung” der Abfrage durch das RDBMS
– Sicherheit vor Manipulationen (insbesondere sogenannte Query
Injection)
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
13
d) Stored Procedures
Stored Procedures
• Programme, die die Datenbank abfragen und ihr Ergebnis i.d.R. als
Tabelle abliefern (und auch SQL-Teile enthalten)
• Gespeichert im DBMS und in der Quelle nur bei entsprechender
Berechtigung zugänglich
• „precompiled“ im Datenbankserver
• Eigene Programmiersprache („T-SQL“ oder ähnlich)
• Oft unmittelbar aufgerufen vom Benutzer (bei entsprechender
Berechtigung)
• Von sehr vielen RDBMS unterstützt
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
14
http://jedi.informatik.unileipzig.de/de/Vorles/Einfuehrung/Tx/TX03.pdf
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
15
Stored Procedures
CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location
• Our Florida warehouse manager can then access inventory levels by
issuing the command
– EXECUTE sp_GetInventory 'FL'
• The New York warehouse manager can use the same stored procedure
to access that area's inventory.
– EXECUTE sp_GetInventory 'NY'
databases.about.com/od/sqlserver/a/storedprocedure.htm
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
16
Beispiele
CREATE PROCEDURE uspGetAddress
@City nvarchar(30)
AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = @City
CREATE PROCEDURE uspGetAddress
@City nvarchar(30)
AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City LIKE @City + '%'
EXEC uspGetAddress @City = 'New York'
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
17
Stored Procedure mit 2 Parametern und Default-Werten
CREATE PROCEDURE uspGetAddress
@City nvarchar(30) = NULL,
@AddressLine1 nvarchar(60) = NULL
AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = ISNULL(@City,City)
AND AddressLine1 LIKE '%' +
ISNULL(@AddressLine1
,AddressLine1) + '%’
WI
EXEC uspGetAddress @City = 'Calgary',
@AddressLine1 = 'A'
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
18
Parametrisierte Queries und Stored Procedures
Stored Procedures Vorteile
•
•
Effizienz durch Prä-Compilation
Information Hiding: die Namen der Tabellen, joins etc interessieren den Anwender nicht
 Sicherheit
 einfachere Wartung
•
•
•
Stored Procedure sitzt in der Datenbank und ist benutzerunabhängig zu warten
Stored Procedure hat u.U. gegenüber dem Aufrufer erweiterte Rechte
Performanz: wenig „round trips“ über das Netz
Vorteile der SP: in jüngster Zeit nicht unumstritten
siehe zB http://forum.de.selfhtml.org/archiv/2010/6/t198475/
vertiefend http://download.microsoft.com/download/0/c/1/0c11f05d-d47d-41eb-9d0b8e1db32ba67b/073_StoredP.ganzneu.mlo.w.pdf
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
19
recap: Probleme des Updates unter Views …
•
buch(buchId,AusleiherId,Titel)  student (MatrNr,Nname)
AusleiherId kann MatrNr, aber auch eine GastStudentId sein, so dass RefInt schwer zu fordern
ist. Wir definieren für die Ausleihe
a) create view ausleihe as select (Titel,Nname,MatrNr) from buch ⋈ student
b) create view ausleihe1 as select (Titel,Nname, AusleiherId) buch ⋈ student
Wir möchten in einer Anwendung ein Buch auf jemand anderes umbuchen
1. wenn ich in (a) einen anderen Namen eingebe, benenne ich den Mann überall um, wenn ich eine neue
(gültige) MatrNr eingebe, dann ändert das auch den Nnamen-spalte, ohne dass ich ein Update gegeben
hätte  lieber diesen view non-updatebale machen!
2. auch ein gleichzeitigen Ändern von Nname und MatrNr auf den neuen Ausleiher in (a) geht komplett
schief, da in student dann gar nichts geändert wird, aber die AusleiherId im view (a) gar nicht zur Verfügung
steht
3. view (b) sieht bis zu einem update genauso aus wie (a). Die Probleme sind wie bei (1). Außerdem (2‘):
Wenn ich bei AusleiherId eine nicht existente MatrNr eingebe, verschwindet das Buch aus dem View und
ich kann es nie mehr zurückändern!  auch dieser View eignet sich nicht zum updaten, selbst wenn das
DBMS das zuließe
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
20
… Lösung durch Update mit Stored Procedure
create procedure umbuchen(@buch,@neuNr,@neuName) AS
if exists select * from student where MatrNr = @neuNr and NName = @neuName
update buch set AusleiherId=@neuNr where buchId=@buch
else
-- Fehlermeldung
endif
then
Wir könnten diese Prozedura auch via „instead“-Trigger an das Update der Views hängen. Auf jeden Fall
brauchen wir der «Ausleihe» -Rolle im DBMS nur „execute“ Rechte an der Prozedur zu geben
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
21
e) Indizes (recap:Parametrisierte Queries)
Wiederholung
? Welche Bücher haben die „Opuszko“s ausgeliehen?
select BuchTitel, PLZ,… from Bücher inner join Personen on
Bücher.AusleiherID = Personen.PersID where
Personen.Nachname = ‘Opuszko‘
Oder parametrisiert
select BuchTitel, PLZ, … from Bücher inner join Personen on
Bücher.AusleiherID = Personen.PersID where
Personen.Nachname =[Nachname des Ausleihers:]
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
22
e) Indizes
Fortsetzung des „Ausleihe“-Beispiels ….
Oder parametrisiert
SELECT BuchTitel, PLZ, … FROM Bücher INNER JOIN Personen ON
Bücher.AusleiherID = Personen.PersID WHERE Personen.Nachname
=[Nachname des Ausleihers:]
Sehr häufig wird auf die Bücher Tabelle mit Fremd-Schlüssel AusleiherID
zugegriffen …
Und sehr häufig auf die Personen Tabelle via Nachname, was gar kein
Schlüssel ist
!! Wirklich schnelle Zugriffe in eine Tabelle erfolgen nur sequentiell, oder
wenn man – ungefähr formuliert – die Zeilennummer kennt
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
23
Organisation auf Platte und Beispiel des Low Level Zugriff
•
Zugriff über „Satznummer“ in der
Programmiersprache Visual Basic
– Get #FileNum, Position, Employee
– Put #FileNum, Position, Employee
– LastRecord = LastRecord + 1 ;
Put #FileNum, LastRecord, Employee
• Problem: in einem DBMS mit
multiuser-Zugriff und –zig
Insert/Deletes kennt man die
Satznummer eigentlich nie …
• … außerdem durchbräche man das
Geheimhaltungsprinzip der DBMS
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
24
Indizes
Lösung:
Neben der Tabelle eine weitere Struktur pflegen, in der die Zuordnung „Suchbegriff 
Satznummer“ rasch nachgeschlagen werden kann: „Index“
Beispiel einer Indexorganisation: Grundmuster der „Inverted file“
Index on
Personen.Nachname
Index on
Bücher.AusleiherID
WI
Suchbegriff
Liste des Auftretens
(Satznummern!
Nicht Primärschlüssel)
Opusko
981
Ruhland
persID
Opuszko
2,99
Müller
Suchbegriff
12,13,14,45,98
Liste des Auftretens
(Satznummern)
789
123,453,121
1901
4
901
45
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
25
Indizes
Beobachtungen
•
Der Index ist erheblich kleiner als die Datenbanktabelle und kann daher oft im
Hauptspeicher gehalten werden
•
Tradeoff
–
–
•
Es ist bei Vorhandensein eines Index zunächst auf den Index und dann auf Teile der Tabelle
zuzugreifen
Der Index muss gepflegt werden (Löschen/Einfügen von Einträgen)
Nicht immer ist ein bestimmter Index vorteilhaft
–
Wieviele Bücher sind derzeit insgesamt ausgeliehen?
lies ein Mal die Buch-Tabelle
–
–
–
•
WI
Wieviel Bücher von Männern?
Pers.Nachname-Index sinnlos, aber Bücher.AusleiheId könnte hilfreich sein
Wieviele von 4 Personen-Familien?
brauchen noch ein paar Indizes zum Joinen der Familie-Tabelle
Wieviele Bücher von Personen, deren Namen mit „O“ beginnt?
Anfrage kann rein aus dem Index beantwortet werden (sog. „Covered query“)
Die Ergebnisse sind mit und ohne Index identisch (reine Performanz-Überlegung)
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
26
Indizes
Konsequenzen
 Die Anlage eines Index auf Spalten/Spaltengruppen muss vom Benutzer
angefordert werden (Ausnahme: Primärschlüssel und UNIQUE constraints)
 Ob ein Index bei der Abfrage verwendet wird, kann und wird meist vom DBMS
allein entschieden (Der Benutzer kann sog. Query Hints setzen)
 Sobald ein Index angelegt ist, muss er bei insert/delete/updates neben den
Tabellen gepflegt werden. Das DBMS macht das automatisch.
 Die optimale Indexstruktur ist ein bedeutendes Problem der Praktischen
Informatik und ist von Eigenschaften der indexierten Spalten abhängig (Beispiel:
„Index auf Primärschlüssel“  maximal 1 Eintrag; „Index auf Geschlecht“ 
~50% aller Zeilen). Die leistungsfähigsten DBMS stellen unterschiedliche
Indexarten bereit
 Beispiel: Buch zu „Gleichberechtigungsartikeln“. Die Indexeinträge zu „Mann“ bzw „Frau“
umfassen jeweils wohl 70% der Seiten  bessere Idee wäre hier, jede Seite am Rand blau
und/oder rot zu markieren  „bitmap“ Indizes in der Informatik
 vertiefend en.wikipedia.org/wiki/Index_(database) ist ein guter Einstieg
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
27
Randbemerkungen
• Btree Indizes und Verwandte sind wie inverted files aufgebaut, nur
die Suche innerhalb des Index (und dessen verwaltung) wird
nochmals beschleunigt (Index eines Buches ist auch alphabetisch
sortiert)
• ein hash Index entspricht der phyischen Anlage eines Buches nach
Bänden mit „A-K“,“L-Z“ (oder Zeitaltern bei Weltgeschichten) und
kann jeweils nur auf einen Index angelegt werden
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
30
Fischers Weltgeschichte
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
31
komplizierter Hash in einer Weltgeschichte
 Region (Europa, Islamische Welt, …)
 + Zeitalter
bestimmen Band
 innerhalb des Bands hilft entweder ein
Index oder lineares Suchen
 … Gliederung nach Datum wäre möglich,
aber nicht sachgerecht
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
32
Syntax zur Index-Verwendung: typische Syntax
•
CREATE INDEX index_name [index_type] ON tbl_name (index_col_name,...)
– index_col_name: col_name [(length)] [ASC | DESC]
– index_type: USING {BTREE | HASH}
•
•
WI
SELECT * FROM table1 IGNORE INDEX (col3_index) WHERE col1=1 AND col2=2 AND
col3=3;
SELECT * FROM table1 USE INDEX (col3_index) WHERE col1=1 AND col2=2 AND col3=3;
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
28
Indizes in SQLite
• http://www.sqlite.org/queryplanner.html:
Most of the time, the query planner in SQLite does a good job
on its own and without outside help. However, the query planner
needs indices to work with and it usually falls to the programmer
to add indices to the schema that are sufficient for the query
planner to accomplish its task.
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
29
partial indices
•
Tabellenspalten werden manchmal von wenigen Einträgen dominiert
–
–
–
•
•
nordkoreanischer Bücher: 90% haben (vermutlich) als Autor Kim Jong-un
Tabelle mit Artikeln zu Gleichberechtigung: 90% haben als Kernthema „Frau“
in allen Fällen gilt aber: die restlichen Anteile verteilen sich auf viele Einträge
in einem realen Buch hätten wir zu den derart dominanten Einträgen keine Indexeinträge
aber: ein sinnvoller Index kann alle anderen Autoren umfassen
–
–
select …. where autor = „Pak Pong-ju“
select … where thema = „Senioren“
CREATE INDEX mypartial ON buchtabelle (autor) WHERE autor <> „Kim Jong-un“
CREATE INDEX indAus ON buchtabelle (ausleiher) WHERE ausleiher IS NOT NULL
CREATE INDEX partSalary ON employee(age) WHERE salary > 2100
•
WI
DBMS entscheidet wie üblich autonom, ob es in der Anfrage den Index verwenden will
SELECT … WHERE autor LIKE „Kim%“
 Index eher nicht verwenden
SELECT … WHERE autor LIKE „Pak%“
 Index eher schon verwenden
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
30
f) Constraints und Triggers
•
Idee: Lege in der Datenbank Eigenschaften fest, die legale Einträge erfüllen müssen  Constraint
– AnzahlBücher
integer <50 OR NULL
– PersID
Start mit 1 oder 2, dann eine gültige 2 stellige Ländernummer,
dann 4 beliebige Ziffern
– PersID in BÜCHER gültige PersID in PERSONEN or NULL
„referentielle Integrität“
•
Beispiele zu Aktionen (meist stored procedures), die von Ereignissen ausgelöst werden: Trigger
– Bei update von PERSON.gehalt durch die Benutzerrolle „Sachbearbeiter“ schreibe
einen Eintrag in eine spezielle log-Tabelle …
• Neben der Gehaltsänderung
• … oder an Stelle der Gehaltsänderung (der Chef stößt die eigentliche Änderung
dann später an)
– Bei ungültigem PersID -Eintrag: Warne Benutzer und biete Hilfe an
– On delete cascade | on delete abort | on delete set Null | on delete exec
procedureName  typische Aktionen bei Verletzung der referentiellen Integrität
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
31
Constraint: Referentielle Integrität (Beispiel SQLite)
CREATE TABLE track(
trackid INTEGER,
trackname TEXT,
trackartist INTEGER Not Null,
FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);
Ein NULL wäre sonst
auch OK
CREATE TABLE song(
songid INTEGER,
songartist TEXT,
songalbum TEXT,
songname TEXT Not Null,
FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname)
);
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
32
Probleme mit Referentieller Integrität


Befüllungsprobleme einer Relation
MaritalTable (PersID, Gender, marriedTo references MaritalTable(persID) )




Einzeleintrag eines Paares muss der 1. Partner mit NULL eingetragen werden
Beim anfänglichen Befüllen der Tabelle („bulk load“) zunächst alle Ehepartner auf NULL
 ??? Dubiose Lösung
Bessere Lösung: Ausschalten der referentiellen Integrität „für gewisse Zeit“
Unterschiedliche Mechanismen je nach DBMS
marriedTo references MaritalTable(persID) DEFERRED INITIALLY DEFERRED
SQLite
SQLServer, DB2 ,,, *‘
BCP- Befehl zum „bulk copy“
Maggie heiratet Hans  OK (außer es
kommt zu einem Systemabsturz, nachdem die
Heirat bei Hans schon eingetragen ist und bei
Maggie noch nicht  Problem des
Transaktionsschutz; siehe später)
Aber das Eintragen der schon immer
verheirateten Grete macht Probleme, da
ihr Tupel bis zum Eintrag von Egon die
RefInt verletzt!
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
persID
First
name
Gen
der
marrie
dTo
10
Guido
M
NULL
11
Hans
M
NULL
12
Grete
F
43
13
Maggie
F
NULL
14
Michael
M
NULL
15
George
M
NULL
43
Egon
M
12
33
Beispiele zum Trigger
CREATE TRIGGER salary_trigger
BEFORE UPDATE ON employee_table
REFERENCING NEW ROW AS n, OLD ROW AS o
FOR EACH ROW
IF n.salary <> o.salary THEN
do something
END IF;
;
Quelle: http://en.wikipedia.org/wiki/Database_trigger
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
34
Beispiele
Assuming that customer records are stored in the "customers" table, and that order records are
stored in the "orders" table, the following trigger ensures that all associated orders are
redirected when a customer changes his or her address:
CREATE TRIGGER update_customer_address UPDATE OF address ON customers
BEGIN
UPDATE orders SET address = new.address WHERE customer_name = old.name;
END;
With this trigger installed, executing the statement:
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
causes the following to be automatically executed:
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
Example from http://www.sqlite.org/lang_createtrigger.html
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
35
Beispiele: Trigger zur Neuverheiratung
CREATE TRIGGER married_trigger
BEFORE UPDATE ON marital_table
REFERENCING NEW ROW AS n, OLD ROW AS o
FOR EACH ROW
IF n.marriedTo <> o.marriedTo THEN
if (n.marriedTo is not null) and ((select m.gender from marriedTo as m where m.persId =
n.marriedTo) = n.gender) THEN
persID
First
Gend marrie
ABORT;
name
er
dTo
END IF;
10
Guido
M
NULL
END IF;
11
Hans
M
13
12
Grete
F
43
;
WI
13
Maggie
F
11
14
Michael
M
NULL
…
…
…
…
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
36
SQLite Syntax
WI
Vorlesung "Daten-, Informations- und Wissensmanagement" (MW31.3) | Prof. Dr. J. Ruhland | WS 2016/17
37