Datenbanken - home.hs

Datenbanken
1
Vorlesung IT-Management
Teilbereich Datenbanken
SS 2007
Prof.Dr.Kühn
Die Vorlesungskopien sollen die Studierenden entlasten – sie enthalten nicht den gesamten klausurrelevanten Stoff und ersetzen nicht den Besuch der
Vorlesung
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
2
Datenbanken: Überblick
Datenbanken bestehen aus einer Ansammlung vieler verschiedener Daten,
auf die über ein Verwaltungsprogramm zugegriffen werden kann.
Unterscheidungskriterien:
‹ Was wird gespeichert
‹ formatierte Daten
‹ unformatierte Daten
‹ Für welchen Zweck
‹ operativen Einsatz: OLTP
‹ Managementinformation: OLAP
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
3
1.4 Typen von Datenbanksystemen
¾ Hierarchische DBMS: seit ca.1960
¾ Netzwerk-DBMS: seit ca.1970
¾ Relationale DBMS:
seit ca.1980
¾ Objektorientierte DBMS:
seit ca.1990
Gemeinsamkeiten
• Speicherung von Daten
• Verwaltung v. Beziehungen
• Strukturänderungen
möglich
• alles vom DBMS verwaltet
Prof.Dr.Kühn /Fb W
Unterschiede
• Welche Datentypen
• wie Beziehungen hergestellt werden
• Flexibilität bzgl. Strukturänderungen
• Effizienz bei vorgesehenen Abfragen
• Flexibilität bzgl. ad-hoc Abfragen
• Unterstützung objektorientierter
Konzepte
2007
IMM-Kap1-SQL
Datenbanken
4
SQL
SQL ist eine deskriptive, mengenorientierte Sprache und enthält alle
Komponenten, die für die Einrichtung einer Datenbank, die
Datenmanipulation und den Datenbankbetrieb nötig sind
Normung von SQL:
• 1982: Normungsbeginn
• 1986: erster Standard (SQL/86).
• 1989: eine Revision (SQL/89), wichtigste Neuerung: Elemente zur Unterstützung
der Datenbankintegrität.
• 1992: weitere Standardisierungsrichtlinien (SQL/92 oder SQL2);
Hauptthema: Constraint-Bearbeitung, Transaktionsverarbeitung
Erstmals gewisse Normung von embedded-SQL
• 1999: SQL3: Erweiterung in Richtung Objektorientierung.
Die Anbieter objektrelationaler Systeme (s. dort) beziehen sich alle auf diesen
neuen Standard, allerdings sind auch herstellerspezifische Lösungen üblich.
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
5
Relationenalgebra:
Die Basisbefehle zur Manipulation relationaler Datenbanken
Tabellenoperationen
Mengenoperationen
• Selektion von Zeilen
• Vereinigung
σ[Auswahlbedingung] (Tabelle)
• Projektion auf Spalten
π[Attributliste] (Tabelle)
• Verbund von Tabellen (Join)
M1 U M2
• Durchschnitt
M1 ∩ M2
•
Differenz
Tab1
M1 - M2
Tab2 [Verbundbedingung]
• Zuordnung von Tabellen
• Produkt
Tab1=Tab2
M1 x M2
Die Relationenalgebra arbeitet mengenorientiert und prozedural
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
6
Relationenalgebra Beispiel-1:
Relationen R1, R2, R3
R1
A
a1
a1
a2
B
b1
b1
b2
C
c1
c2
c2
R2 C D
c2 d1
c3 d2
R3 A B C
a2 b2 c1
a2 b2 c2
Anwendung von Mengenoperationen
Vereinigung A
R1 U R3:
a1
E= R1 U R3 a1
a2
a2
Prof.Dr.Kühn /Fb W
B C
b1
b1
b2
b2
c1
c2
c2
c1
Durchschnitt A B C
R1 ∩ R3
a2 b2 c2
E= R1 ∩ R3
2007
Differenz
R1 - R3
A
B
C
a1 b1 c1
E=R1 - R3 a1 b1 c2
IMM-Kap1-SQL
Datenbanken
7
Kartesisches
Produkt R1 x R2
E= R1 x R2
A
B
C
C
D
a1
a1
a1
a1
a2
a2
b1
b1
b1
b1
b2
b2
c1
c1
c2
c2
c2
c2
c2
c3
c2
c3
c2
c3
d1
d2
d1
d2
d1
d2
Anwendung von Tabellenoperationen
Selektion in R1 mit
C=c2
E= σ[C=c2] (R1)
A
B
C
a1
a2
b1
b2
c2
c2
Join von R1 mit R2 mit
Bedingung (inner Join)
(C in R1:= C in R2)
Projektion in R1
bzgl. A und B
E= π[A,B] (R1)
A B C
a1 b1 c2
a2 b2 c2
Prof.Dr.Kühn /Fb W
C
c2
c2
D
d1
d1
E=R1
A
B
a1
a2
b1
b2
R2 [R1.C=R2.C]
2007
IMM-Kap1-SQL
Datenbanken
8
Beispiel 2
Relation Rechnung
RechNr
0081
0082
0083
0084
Relation Kunden
KdNr Rechdat RechBetrag
2133 1.7.02
10600
7533 23.7.02
28400
2133 4.8.02
1000
8511 5.8.02
20900
KdNr
2133
3557
7533
8511
Relation Vertreter
V-Nr
211
115
224
V-Name
Berger
Adam
Zeiss
Relation
liefnr
L1
L2
L3
L4
L5
Lieferant
liefname
Alu-GmbH
Bike- GmbH
Cross- GmbH
Wichtel&Sachs
Rad AG
Prof.Dr.Kühn /Fb W
KdName
Meier
Müller
Schmitz
Schneider
Ort
Bayreuth
Bonn
Köln
Bonn
V-Nr
224
115
115
115
Relation Lieferprogramm
Bezirk
Nord
Mitte
Süd
Ums-Sum
74 800
127 900
91750
2007
liefnr
L1
L1
L2
L2
L2
L3
L3
L4
teilenr
0009
0010
0009
0010
0011
0003
0004
0012
IMM-Kap1-SQL
Datenbanken
9
SQL:
TCL:
GRANT,
REVOKE,
DDL:
CREATE
DML:
DML:
INSERT
SELECT
UPDATE
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
10
SELECT [DISTINCT] < * | Liste der Attribute, evt. aus
verschiedenen Relationen>
FROM <Liste der benötigten Relationen>
[WHERE <Bedingungen auf Zeilen>]
[GROUP BY <Gruppenangabe>]
[HAVING <Bedingungen auf Gruppen>]
[ORDER BY <Sortierspalten> [asc | desc]] ;
Das Ergebnis eines SELECT-Befehls ist immer eine unbenannte und temporäre
Tabelle, die i.a. auf dem Bildschirm angezeigt wird.
Bedeutung der Klammern (die Klammern werden NICHT eingegeben)
[…] : Inhalt kann weggelassen werden;
<…> : Inhalt vom Benutzer sinngemäß anzugeben
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
Basisoperationen der
Relationenalgebra
1. Vereinigung v. Mengen
2. Durchschnitt v. Mengen
3. Differenz v. Mengen
4. Kartesisches Produkt
5. Selektion
6. Projektion
7. Join
Prof.Dr.Kühn /Fb W
11
Realisierung in SQL
<Menge1 > UNION < Menge2> ;
<Menge1 > INTERSECT < Menge2 > ;
<Menge1 > MINUS < Menge2 > ;
SELECT * FROM <Tabelle1>,
keine Verbundbedingung
<Tabelle2>;
SELECT * FROM <Tabelle> WHERE
<Bedingung>;
SELECT <Attributliste> FROM
<Tabelle>;
SELECT * FROM
<Tabelle1>, <Tabelle2>
WHERE <Verbundbedingung>;
2007
IMM-Kap1-SQL
Datenbanken
12
Wie lauten die folgenden Aktionen in SQL (jeweils 1 Befehl)
1. Projektion von Rechnung auf KdNr, RechBetrag
2. Selektion in Rechnung mit Rechbetrag>20 000
3. Join von Rechnung und Kunde mitRechnung.KdNr = Kunde.KdNr
4. Gesucht sind Name und Wohnort der Kunden die vom Vertreter des
Bezirks ‚Mitte’ betreut werden
5. Gesucht sind Teilenummern und Name der Lieferanten, die Teile mit den
Nummern 9 bis 11 liefern.
6. Gesucht ist eine Liste mit allen Namen (Vertreter und Kunden), die in
der Datenbank vorkommen. Formulierung mit Mengenoperator
7. Gibt es Namen, die nur bei Kunden, aber nicht bei Vertretern
vorkommen
8. Gibt es Namen, die in beiden Tabellen vorkommen
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
13
Weitere Beispiele zu SELECT
select * from Kunde where KdName IS NULL;
select * from Kunde where KdName = ‘ ‘;
select * from Kunde where KdName LIKE ‘M%’;
select * from Kunde where KdName LIKE ‘Me_er’;
select * from Kunde where UPPER(KdName)=’MEIER’;
select liefnr from lieferprogramm where teilenr in (3,9,10);
select KdName, VName from Kunde K, Vertreter V where V.Vnr=K.VNr
and Bezirk=’West’;
select distinct Ort from Kunde;
select count(KdNr) “Anzahl Kunden” from Kunde;
select count(*) “Anzahl Kunden” from Kunde;
select VNr, count(VNr) from Kunde group by VNr;
select sum(UmsSum) from Vertreter;
select VNr, sum(UmsSum) from Vertreter group by VNr;
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
14
Noch DML: SQL-Befehle zur Datenmanipulation
INSERT: neue Zeilen in vorhandene Tabellen einfügen
a) INSERT INTO <Tabname>
VALUES <Auflisten aller Attributwerte>;
INSERT INTO Vertreter VALUES (117,‘Becker’,’Mitte’,0);
b) INSERT INTO <Tabname (Attributnamen) >
VALUES < zugehörige Attributwerte>;
INSERT INTO Vertreter (VName, VNr, UmsSum, Bezirk)
VALUES ( ‘Becker’,117, 0,‘Mitte’);
c) INSERT INTO <Tabname >
SELECT <Auswahlbedingung>;
INSERT INTO Vertreter
SELECT * FROM Vertreter_alt WHERE Bezirk=‘Nord’;
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
15
UPDATE: einzelne Attributwerte in vorhandenen Tabellen
ändern
UPDATE <Tabname>
SET <Wertezuordnung zu den Attributen>
WHERE <Bedingung> ;
UPDATE Vertreter SET VName=‘Becker’ WHERE VNr=115;
UPDATE Vertreter SET Bezirk =
(SELECT Bezirk FROM Vertreter WHERE VNr=224)
WHERE VNr=115;
UPDATE Kunde SET VNr=211;
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
16
DELETE: Zeilen aus Tabellen entfernen
DELETE FROM <Tabname> WHERE <Bedingung>;
DELETE FROM Kunde WHERE K-NR=7557;
DELETE FROM Kunde WHERE Ort=‘Karlsruhe’;
DELETE FROM Kunde WHERE VNr=115;
DELETE FROM KUNDE;
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
17
Befehle zur Definition und Änderung von Tabellen (DDL)
•
CREATE TABLE
Definieren einer Tabellenstruktur und evt. Festlegen von
Constraints
•
CREATE INDEX
Erzeugen einer Zugriffshilfe (Indextabelle)
•
ALTER TABLE
Ändern einer Tabelle (bzgl. der Struktur u./o. der Constraints)
•
DROP TABLE
Löschen einer Tabelle
•
CREATE VIEW
Definieren einer virtuellen Tabelle
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
18
CREATE TABLE <tabname>
( <Attr1 Typ1 [column constraint 1],
<Attr2 Typ2 [column constraint 2],
.... , [table constraint] );
a) Erstellen einer neuen Tabelle und Füllen mit Inhalten:
CREATE TABLE Vertreter (VNr Number(3), VName Varchar2(30),
Bezirk Varchar2(30), UmsSum Number(7) );
INSERT INTO Vertreter Values (250,’Kaiser’,’B111’,0);
b) Kopieren einer anderen Tabelle: (Aufbau und Inhalt; auch teilweise)
(es gebe bereits eine gefüllte Tabelle Vertrieb (MANr,Name,Bezirk,UmsSum)
CREATE TABLE Vertreter AS SELECT * FROM Vertrieb;
CREATE TABLE Vertreter (VNr,VName)
AS SELECT MANr,Name FROM Vertrieb;
CREATE TABLE Vertreter (VNr,VName)
AS SELECT MANr,Name FROM Vertrieb WHERE UmsSum>10000;
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
19
mögliche Datentypen bei Oracle: (vordefiniert)
CHAR (n)
feste Länge, max 2000; default: n=1;
VARCHAR2(n)
VARCHAR(n)
variable Länge , n<=4000,
DATE
Datum; Standard: ‘Mon-TT-JJJJ’
LONG
CLOB
BLOB
NUMBER
alphanum, variabel, bis 4 Gbyte
character large object, bis 4 Gbyte
binary large object; bis 4 GByte
ganze Zahlen u. Dezimalzahlen; Speicherung wie
Eingabe
Speicherung als Ganzzahl
NUMBER(n)
NUMBER(n,0)
NUMBER(n,m)
Speicherung als Festkomma Dezimalzahl mit n
Stellen insgesamt, dabei m Nachkommastellen
u.a.
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
20
Beispiele (Quelle: Oracle Handbuch):
Specified As
Stored As
Actual Data
7456123.89
NUMBER
7456123.89
7456123.89
NUMBER(9)
7456124
7456123.89
NUMBER(9,2) 7456123.89
7456123.89
NUMBER(9,1) 7456123.9
7456123.89
NUMBER(6)
exceeds precision
7456123.89
NUMBER(7,-2) 7456100
.01234
NUMBER(4,5) .01234
.00012
NUMBER(4,5) .00012
.000127
NUMBER(4,5) .00013
.0000012
NUMBER(2,7) .0000012
.00000123
NUMBER(2,7) .0000012
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
21
Beispiel:
R1
Benutzer
1
R2
n
Ausleihe
1
BenNr, Name, Adresse,... ANr, RueckDat, VKennz,...
1
Buch
BuchNr, Titel,...
1. Minimaldefinition:
CREATE TABLE Buch (BuchNr Char(10), Titel Char(40), ....);
CREATE TABLE Benutzer
(BenNr Char(8), Name Char(20), Adresse Char(40));
CREATE TABLE Ausleihe
(ANr Number(5), RueckDat DATE, Vkennz Number(1),
BenNr Char(8), BuchNr Char(10));
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
22
Constraints (Randbedingungen)
Sie werden beim Erstellen der Tabellenstruktur vereinbart
(CREATE TABLE) , können aber auch nachträglich
hinzugefügt werden (ALTER TABLE).
Es gibt 2 Arten der Constraint-Formulierung:
♦ column constraint
column constraint:
<Spaltenname> <Typ> [CONSTRAINT <constraintname>]
<constraintart>
♦ table constraint:
[CONSTRAINT <constraintname> ] <constraintart>
(<Spaltennamename>)...;
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
23
mögliche Constraints:
• NULL / NOT NULL (nur column constraint);
• UNIQUE (column constraint oder table constraint)
Für ein UNIQUE-Attribut wird stets eine Indextabelle angelegt
• CHECK (nur column constraint)
Bedingung für den Wert eines Attributs
Beispiel:
CREATE TABLE Ausleihe
(Anr Number(5) NOT NULL,
….
Mahnung NUMBER(1) CHECK (Mahnung IN (1,2,3)),
.......);
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
24
• PRIMARY KEY (column constraint oder table constraint)
automatisch NOT NULL UNIQUE / pro Tabelle nur ein PK
Es wird stets eine Indextabelle angelegt
Der PK kann zusätzlich Fremdschlüsssel sein
• FOREIGN KEY (column constraint oder table constraint)
(„referential integrity constraint“); stellt eine Beziehung her,
die das DBMS überprüft.
Es ist erlaubt, daß der Fremdschlüssel und der Bezugsschlüssel
sich in derselben Tabelle befinden (Selbstbezug)
• ON DELETE CASCADE (Option b. foreign key constraint)
Legt fest, daß beim Löschen einer Tabellenzeile, auf die sich ein
„foreign key“ bezieht, alle Zeilen mit diesem Schlüsselwert in
der untergeordneten Tabelle ebenfalls gelöscht werden sollen
(gegebenenfalls rekursiv).
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
25
Beispiel-1:
CREATE TABLE Buch
(BuchNr CHAR(10) PRIMARY KEY, Titel CHAR(40),
AutorNr NUMBER(5) REFERENCES Autoren(PersNr));
oder
CREATE TABLE Buch
(BuchNr CHAR(10),
Titel CHAR(40),
AutorNr NUMBER(5),
PRIMARY KEY (BNr),
FOREIGN KEY (AutorNr) REFERENCES Autoren(PersNr));
CREATE TABLE Ausleihe
(Anr Number(5) CONSTRAINT Ausl_PK PRIMARY KEY,
RueckDat (DATE), Vkennz Char(2),
BenNr Char(8) REFERENCES Benutzer(BenNr) ON DELETE
CASCADE,
BuchNr CHAR(10) REFERENCES Buch (BuchNr));
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
26
Beispiel-2: (Formulierungen mit column constraints)
CREATE TABLE Vertreter (
VNr Number(3) PRIMARY KEY,
VName Varchar2(30) ,
Bezirk Varchar2(30) CHECK (Bezirk IN (’Nord’, ’Mitte’, ’Sued’)),
UmsSum Number(7) DEFAULT 0);
CREATE TABLE Kunde (
KdNr Number(4) UNIQUE NOT NULL,
KdName Varchar2(30) NOT NULL,
Ort Varchar2(30),
VNr Number(3) REFERENCES Vertreter(VNr) );
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
27
Beispiel-2: (Formulierungen mit table constraints)
CREATE TABLE Vertreter (
VNr Number(3), VName Varchar2(30) ,
Bezirk Varchar2(30), UmsSum Number(7) DEFAULT 0,
PRIMARY KEY (VNr),
CHECK (Bezirk IN (’Nord’, ’Mitte’, ’Sued’)) );
CREATE TABLE Kunde (
KdNr Number(4) NOT NULL, KdName Varchar2(30) NOT NULL,
Ort Varchar2(30), VNr Number(3),
UNIQUE (KdNr),
FOREIGN KEY (VNr) REFERENCES Vertreter(VNr));
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
28
Tabellen nachträglich verändern:
ALTER TABLE <tabname> ADD (<spaltenname spaltentyp>);
ALTER TABLE emp ADD (Beurteilung varchar2(50));
ALTER TABLE <tabname> DROP COLUMN <spaltenname>;
ALTER TABLE emp DROP COLUMN Beurteilung;
ALTER TABLE <tabname> MODIFY (<spaltenname neuer_spaltentyp>);
ALTER TABLE emp MODIFY (ename varchar2(50));
ALTER TABLE emp MODIFY (ename varchar2(30) NOT NULL);
ALTER TABLE <tabname> ADD <constrainttyp (<spaltenname>);
ALTER TABLE emp ADD CONSTRAINT emp_PK primary key (empno);
ALTER TABLE <tabname> [ENABLE | DISABLE] CONSTRAINT
<constraintname>;
ALTER TABLE emp DISABLE constraint emp_PK;
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
29
VIEWS (virtuelle Tabellen; Benutzersichten)
• VIEWS werden stets aus (einer oder mehreren) permanenten
Tabellen (Basistabellen) mit Hilfe eines SELECT-Befehls erzeugt.
• Sie erhalten einen Namen und werden über diesen Namen wie
Tabellen angesprochen und verwendet.
• In der Datenbank wird nicht die View, sondern der sie erzeugende
Select-Befehl gespeichert und bei jedem Aufruf der View neu
ausgeführt.
Bsp.:
CREATE VIEW Katalog AS SELECT BNr,Titel FROM Buch;
CREATE VIEW Mahnung AS
SELECT Name, Adresse, RueckDat, BNr FROM Benutzer,
Ausleihe
WHERE RueckDat < Sysdate-7 AND
Ausleihe.BenNr=Benutzer.BenNr;
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
30
Bzgl. SELECT-Befehlen besteht kein Unterschied in der Verwendung
von Tabellen bzw. Views
Unter bestimmten Bedingungen können auf eine View auch ändernde
DML-Befehle angewandt werden (insert, update, delete)
Dies ist nicht zulässig, wenn
- in der View „NOT NULL“-Spalten fehlen
- die View sog. „virtuelle Spalten“ (aus mehreren Basisspalten) enthält
- die View auf mehreren permanenten Tabellen basiert (s.Beispiel 2).
Views dienen 2 Zwecken:
1. Datenschutz und Datensicherheit.
2. Benutzungskomfort
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL
Datenbanken
31
Views und Tabellen:
CREATE VIEW Mahnung1 AS
SELECT Name, Adresse, RueckDat, BNr FROM Benutzer,
Ausleihe WHERE RueckDat < Sysdate-7 AND
Ausleihe.BenNr=Benutzer.BenNr;
SELECT * FROM Mahnung1
CREATE TABLE Mahnung2 AS
SELECT Name, Adresse, RueckDat, BNr FROM Benutzer,
Ausleihe WHERE RueckDat < Sysdate-7 AND
Ausleihe.BenNr=Benutzer.BenNr;
SELECT * FROM Mahnung2
Würden Sie die View Mahnung1 oder die Tabelle Mahnung2 erstellen?
Begründung?
Prof.Dr.Kühn /Fb W
2007
IMM-Kap1-SQL