Dipl.-Inf. , Dipl.-Ing. (FH) Michael Wilhelm

(1)

Datenbanksysteme Datenbanksysteme I I

Dipl.-Inf. , Dipl.-Ing. (FH) Michael Wilhelm

Hochschule Harz

FB Automatisierung und Informatik

mwilhelm@hs-harz.de

Raum 2.202

Tel. 03943 / 659 338

(2)

Inhalt

1. Grundlegende Begriffe der Datenbanktechnologie 2. Datenbankentwurf / Datenmodelle

3. ER-Modell / ER-Diagramm 4. SQL-Sprache

5. Normalisierung

6. SQL-Erweiterungen

(3)

SQL-Erweiterungen

CREATE TABLE

UPDATE TABLE

COMPUTED BY

SUB SELECT

CASE

ANY ALL

JOINS

AGGREGAT-FUNKTIONEN

KEYS

INDIZES

CONSTRAINTS

VIEWS

(4)

Create Table

CREATE TABLE [schema.] table

(column datatype [DEFAULT expr], …);

Datentypen Oracle:

Number(m,n), char, varchar, varchar2, date, time, Currency, Raw, Long Raw, CLOB, BLOB, NChar, NChar2, CFile, BFile

Datentypen Interbase:

Numeric, char, Varchar, date, time, timestamp, smallint,

integer, bigint, float, double Presicion , Currency, Bool, Blob

(5)

Create Table

CREATE TABLE emp (

empno number(4) NOT NULL primary key, ename varchar2(10),

job varchar2(9),

mgr number(4),

hiredate date default sysdate,

sal number(7,2),

comm number(7,2),

deptno number(2),

constraint emp_pk primary key (empno), primary key (empno) // Alternativ

);

(6)

Create Table

Schlüsselwörter:

NOT NULL

NULL mySQL

AUTO_INCREMENT mySQL

DEFAULT

DEFAULT NULL Firebird

COMPUTED BY

(7)

Domains definieren

Domains sind benutzerdefinierte Datentypen. Werden häufig für Längendefinitionen von Strings und Zahlen verwendet.

CREATE DOMAINS myNumber as INTEGER DEFAULT 4712 CREATE DOMAINS myString as CHAR(200)

CREATE DOMAINS myNumber as NUMERIC(7,2) CREATE DOMAINS myName as VARCHAR(50)

CREATE DOMAINS myStudiengang as VARCHAR(10)

CHECK (value IN ("IB", "KT", ”II", "AT"));

(8)

Domains verwenden

CREATE DOMAIN myNumber as INTEGER DEFAULT 4712 CREATE TABLE emp (

id AS INTEGER NOT NULL, Hausnummer AS MYNUMBER, Name AS CHAR(200)

);

(9)

Domains ändern

CREATE DOMAIN myNumber as INTEGER DEFAULT 4712 // Defaultwert ändern

ALTER DOMAIN myNumber SET DEFAULT 4711 //

// Domain löschen

DOMAIN DROP myStudiengang

(10)

Tabellenstruktur ändern

ALTER TABLE emp

ADD gehalt float default 12000.00;

ADD gehalt2 float NOT NULL;

ALTER TABLE emp

ADD PRIMARY KEY(empno);

ALTER TABLE emp

ADD CONSTRAINT chpreis CHECK(preis>0.0);

ALTER TABLE emp DROP email;

DROP TABLE emp

DELETE FROM emp;

(11)

Daten einfügen

INSERT INTO emp

VALUES (Attr1, Attr2, Attr3,.Attr4..) INSERT INTO emp (Attr2, Attr3)

VALUES (Attr2, Attr3)

INSERT INTO emp (Attr2, Attr3) SELECT Attr2, Attr3

FROM emp2

WHERE deptno>122;

(12)

Daten ändern

UPDATE emp

SET salary=12345.00;

UPDATE emp

SET salary1=12345.00, salary2=1333.00 WHERE deptno IN (12,33,22);

UPDATE emp

SET salary=salary*2;

(13)

Daten löschen

DELETE FROM emp ; DELETE FROM emp

WHERE deptno IN (12,33,22);

DELETE FROM emp

WHERE salary<1000;

(14)

Datenredundanz ?

CREATE TABLE emp (

empno number(4),

ename varchar2(10), salmonth number(7,2), salyear number(9,2) )

INSERT INTO emp

VALUES (123, "Meier", 2000.00, 24000.00 );

UPDATE emp

SET salmonth=2100, SET salyear=25200

WHERE ename= "Meier”;

(15)

Datenredundanz verhindern mit COMPUTED BY

CREATE TABLE emp (

empno number(4),

first_name varchar2(30), last_name varchar2(40), salmonth number(7,2),

salyear COMPUTED BY (salmonth*12),

full_name COMPUTED BY (last_name || ", " || first_name) )

(16)

SUBSELECT: 1. Variante

Bestimme den Abteilungsnamen des Mitarbeiters „Müller“

SELECT deptname FROM dept

WHERE deptno = ( SELECT deptno FROM emp

WHERE Ename="Müller"

);

(17)

SUBSELECT: 2. Variante

Zeige alle Mitarbeiter mit dem Abteilungsnamen:

SELECT ename, deptno, (

SELECT dname FROM dept

WHERE e.deptno=deptno )

FROM emp e;

SELECT full_name, dept_no, (

SELECT department FROM department

WHERE e.dept_no=dept_no )

FROM employee e;

(18)

CASE

Ermöglichen bedingte Abfragen in der Form einer IF-THEN-ELSE- Anweisung:

CASE expr

WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr

END

(19)

CASE-Beispiel

Alle Angestellte in der Abteilung VP sollen eine 10%

Lohnerhöhung erhalten.

Alle Angestellte in der Abteilung Eng erhalten eine 20%

Lohnerhöhung

last_name, job_code, salary

Vorlesungsübung

Update-SQL: ?

SELECT last_name, job_code, salary, CASE job_code

WHEN "VP" THEN (1.10salary) WHEN "Eng" THEN (1.20salary) ELSE salary

END Neues_Gehalt

FROM employee

(20)

Einfügen einer neuen Spalte:

ALTER TABLE EMPLOYEE

ADD salnew DECIMAL(15,2) Alle Gehälter übernehmen:

UPDATE employee

SET salnew = salary;

Gehaltserhöhung:

UPDATE employee

SET salnew=(1.10*salary) WHERE job_code = "VP" ; UPDATE employee

SET salnew=(1.20*salary)

WHERE job_code = ”Eng" ;

(21)

Mehrfach Operator ANY

Der Mehrfach-Operator ANY vergleicht einen Wert mit jedem von einer Unterabfrage zurückgegebenen Wert. in der Hauptabfrage, ob die Datensätze in der ermittelten Menge enthalten sind.

< ANY bedeutet weniger als das Maximum

> ANY bedeutet mehr als das Minimum

= ANY hat dieselbe Bedeutung wie IN

(22)

Mehrfach Operator ANY

Beispiel:

Gesucht sind die Mitarbeiter, die keine „SRep“ sind, und ein geringeres Gehalt beziehen als einer der Mitarbeiter mit der Jobkennung „SRep“.

SELECT emp_no, last_name, job_code, salary FROM employee

WHERE salary < ANY (

SELECT salary FROM employee

WHERE job_code = "SRep"

) AND job_code <> "SRep";

(23)

Mehrfach Operator ALL

Der Mehrfach-Operator ALL vergleicht einen Wert mit allen von einer Unterabfrage zurückgegebenen Werten in der Hauptabfrage, ob die Datensätze in der ermittelten Menge enthalten sind.

> ALL bedeutet mehr als das Maximum

< ALL bedeutet weniger als das Minimum

(24)

Mehrfach Operator ALL

Beispiel:

Gesucht sind die Mitarbeiter, die keine „SRep“ sind, und deren Gehalt unter dem Gehalt aller Angestellten mit der Jobkennung „SRep“ liegt.

SELECT emp_no, last_name, job_code, salary FROM employee

WHERE salary < ALL (

SELECT salary FROM employee

WHERE job_code = "SRep" ) AND job_code <> "SRep";

(25)

Tabellen verknüpfen

Mit Hilfe von Operatoren werden normalisierte Tabellen verknüpft, so dass eine neue „Virtuelle Tabelle“ erzeugt wird.

Mögliche Varianten:

Vereinigungsmenge JOIN, UNION

Schnittmenge INTERSECT

Differenzmenge MINUS

(26)

Einfacher JOINS

Ein Verbund ist ein JOIN über mehrere Tabellen. Maximal kann man das kartesische Produkt erhalten.

Beispiel: Anzeige der Mitarbeiter mit den Abteilungsnamen.

SELECT e.full_name, e.emp_no, e.dept_no, d.department FROM employee e, department d

WHERE e.dept_no = d.dept_no;

// Kreuzprodukt

SELECT e.full_name, e.emp_no, e.dept_no, d.department FROM employee e, department d

(27)

Typen von JOINS

Cross-JOIN

Theta-JOIN

Inner-JOIN = Equi-JOIN

Natural-JOIN

Left-Outer-JOIN

Right-Outer-JOIN

Full-Outer-JOIN = Full-JOIN

Semi-JOIN

Self-JOIN

(28)

Beispieldatenbank JOIN.fdb

EmpNo Last_Name First_Name

1 Jones Michael

2 Gates Bill

3 Smith Will

4 Lennon John

EmpNo Phone

1 200

4 300

5 230

7 450

(29)

CREATE TABLE emp ( empno integer, last_name char(40), first_name char(40) );

CREATE TABLE phone ( empno integer, phone char(40) );

CREATE TABLE

INSERT INTO emp

VALUES ( 1, 'Jones', 'Michael');

INSERT INTO emp

VALUES ( 2, 'Gates', 'Bill');

INSERT INTO emp

VALUES ( 3, 'Smith', 'Will');

INSERT INTO emp

VALUES ( 4, 'Lennon', 'John');

INSERT INTO

INSERT INTO phone VALUES ( 1, 200);

(30)

Cross- Join

Gebildet wird das Kreuzprodukt. Kardanalität n x m.

Im Beispiel: 16 Zeilen

Theta- Join

Aus dem Kreuzprodukt werden durch eine zusätzliche Bedingung die gültigen Einträge bestimmt.

Beispiel:

emp.empno < phone.empno Ergebnis: 11 Zeilen mod(A.Nr ,2) = mod(B.Nr,2)

A.Nr = B.Nr entspricht dem Equi- Join

(31)

Inner- Join

Aus dem Kreuzprodukt werden durch eine interne bzw. externe Bedingung die gültigen Einträge bestimmt.

Beispiel: // Hier doppelte Spalten, Ergebnis: 2 Zeilen SELECT *

FROM emp, phone

WHERE emp.empno = phone.empno;

Natural- Join

Entspricht dem Inner- Join, zusätzlich existieren KEINE doppelten Spalte.

Beispiel: // Hier keine doppelte Spalten, Ergebnis: 2 Zeilen SELECT e.empno, e.last_name, e.first_name, p.phone

FROM emp e, phone p

WHERE e.empno = p.empno;

(32)

Left- Join

Wieder ein Kreuzprodukt, aber von der linken (ersten) Tabellen werden alle Tupel in die neue Tabelle eingetragen (auch leere Einträge)

Beispiel:

SELECT e.empno Nr, e.last_name Mitarbeiter, p.phone Telefon from emp e LEFT OUTER JOIN phone p

ON e.empno = p.empno;

Nummer Mitarbeiter Telefon 1 Jones 200 2 Gates <null>

3 Smith <null>

4 Lennon 300

(33)

Right- Join

Wieder ein Kreuzprodukt, aber von der rechten (zweiten) Tabellen werden alle Tupel in die neue Tabelle eingetragen (auch leere Einträge)

Beispiel:

SELECT *

from emp e RIGHT OUTER JOIN phone p ON e.empno = p.empno;

EMPNO LAST_NAME FIRST_NAME EMPNO_1 PHONE

1 Jones Michael 1 200

4 Lennon John 4 300

<null> <null> <null> 5 230

Vorlesungsübung

(34)

Full- Outer- Join

Wieder ein Kreuzprodukt, aber von der linke und rechten (ersten und zweiten) Tabellen werden alle Tupel in die neue Tabelle eingetragen (auch leere Einträge) Beispiel:

SELECT *

from emp e FULL OUTER JOIN phone p ON e.empno = p.empno;

EMPNO LAST_NAME FIRST_NAME EMPNO_1 PHONE

4 Lennon John 4 300

2 Gates Bill <null> <null>

3 Smith Will <null> <null>

(35)

Semi- Join

Einen Semi- Join erhält man durch einen Natural- Join (EQUI- Join, ein empno), bei dem dann nur die Spalten der ersten Tabelle angezeigt werden.

EQUI-JOIN

EMPNO LAST_NAME FIRST_NAME EMPNO_1 PHONE

4 Lennon John 4 300

NATURAL-JOIN

EMPNO LAST_NAME FIRST_NAME PHONE

1 Jones Michael 200

4 Lennon John 300

SEMI-JOIN

EMPNO LAST_NAME FIRST_NAME

1 Jones Michael

4 Lennon John

(36)

Self- Join

Wieder ein Kreuzprodukt, aber von der gleichen Tabelle.

Beispiel:

Anzeige aller Mitarbeiter und deren Chefs (empno, mgr, ename) SELECT e1.empno, e1.mgr, e2.empno, e2.mgr, e2.ename

from emp e1 INNER JOIN emp e2

ON e1.mgr = e2.mgr where e1.empno <> e2.empno

order by e1.mgr

(37)

EMPNO MGR EMPNO_1 MGR_1 ENAME

7788 7566 7902 7566 FORD

7902 7566 7788 7566 SCOTT

7499 7698 7521 7698 WARD

7654 7698 7521 7698 WARD

7844 7698 7521 7698 WARD

7900 7698 7521 7698 WARD

7521 7698 7499 7698 ALLEN

7654 7698 7499 7698 ALLEN

7844 7698 7499 7698 ALLEN

7900 7698 7499 7698 ALLEN

7499 7698 7900 7698 JAMES

7521 7698 7900 7698 JAMES

7654 7698 7900 7698 JAMES

7844 7698 7900 7698 JAMES

7499 7698 7844 7698 TURNER

7521 7698 7844 7698 TURNER

7654 7698 7844 7698 TURNER

7900 7698 7844 7698 TURNER

7499 7698 7654 7698 MARTIN

7521 7698 7654 7698 MARTIN

7844 7698 7654 7698 MARTIN

7900 7698 7654 7698 MARTIN

7698 7839 7566 7839 JONES

7782 7839 7566 7839 JONES

7566 7839 7698 7839 BLAKE

7782 7839 7698 7839 BLAKE

7566 7839 7782 7839 CLARK

7698 7839 7782 7839 CLARK

(38)

JOIN Syntax ab SQL 99

Beispiele: INNER-JOIN SELECT *

FROM emp e, phone p

WHERE e.empno = p.empno;

SELECT *

FROM emp e INNER JOIN phone p ON e.empno = p.empno;

SELECT a.A1, a.A2, c.A1, cA2

FROM tableA a INNER JOIN tableB b

ON a.A1 = b.A2 INNER JOIN tableC c ON A.A3 = c.A1

ORDER BY c.A4;

(39)

Beispiele für JOINS

Titel und Name

Name und Abteilung

Verkäufer und Artikel (Auto)

Verkäufer und Kunde

Name und Telefonnummer

Name und Raum

Name und Chef

(40)

Aggreatfunktionen

(41)

Aggreatfunktionen

Aggregatfunktionen werden auf Gruppen von Tupeln angewendet und geben pro Gruppe ein Ergebnis zurück.

AVG

COUNT

MAX

MIN

STDDEV

SUM

VARIANCE

(42)

Aggreatfunktionen: Syntax

Funktion Beschreibung AVG([DISTINCT|ALL]n) Durchschnittswert von n-Werten. NULL-Werte

werden ignoriert.

COUNT({*|[DISTINCT|ALL]expr}) Anzahl der Zeilen, für die expr auf einen anderen Wert als einen NULL-Wert

ausgewertet wird. (Alle mit * ausgewählten Zeilen zählen, einschließlich mehrfach

vorhandener Zeilen und Zeilen mit NULL- Werten.)

MAX([DISTINCT|ALL]expr) Höchster Wert von expr.

NULL-Werte werden ignoriert.

MIN([DISTINCT|ALL]expr) Kleinster Wert von expr.

STDDEV([DISTINCT|ALL]x) Standardabweichung von n-Werten.

SUM([DISTINCT|ALL]n) Summe der Werte von n-Werten.

(43)

Aggreatfunktionen: Syntax

SELECT [column,] group_function(column), ...

FROM table

[WHERE condition]

[GROUP BY column]

[ORDER BY column];

Alle Spalten in der SELECT- Liste, die nicht in Gruppenfunktionen enthalten sind, müssen in der GROUP BY- Klausel angegeben werden.

SELECT department_id, AVG(salary) FROM employees

GROUP BY department_id ;

(44)

Richtlinien:

Wenn Sie eine Gruppenfunktion in einer SELECT-Klausel angeben, können Sie nicht gleichzeitig einzelne Ergebnisse

auswählen, es sei denn, die einzelne Spalte wird in der GROUP BY-Klausel angegeben.

Mit einer WHERE-Klausel kann man Zeilen ausschließen, bevor die übrigen Zeilen in Gruppen aufgeteilt werden.

Man muss die Spalten in der GROUP BY-Klausel angeben.

Man kann dabei keine Spalten-Aliasnamen verwenden.

Standardmäßig werden die Zeilen in aufsteigender Reihenfolge nach der in der GROUP BY-Liste angegebenen Spalten sortiert.

Diese Sortierung kann man mit der ORDER BY-Klausel

verändern.

(45)

Null-Werte

COUNT(*) gibt die Anzahl der Zeilen in einer Tabelle zurück.

SELECT COUNT(*)

FROM employees

WHERE department_id = 50;

COUNT(expr) gibt die Anzahl der Zeilen mit nicht leeren Werten für expr zurück.

COUNT(DISTINCT expr) gibt die Anzahl der eindeutigen, nicht leeren Werte für expr zurück.

Gruppenfunktionen ignorieren NULL- Werte in der Spalte.

Die Funktion NVL zwingt Gruppenfunktionen, NULL- Werte einzubeziehen.

(46)

Unzulässige Abfragen mit Gruppenfunktionen

Sie können die WHERE- Klausel nicht verwenden, um Gruppen einzuschränken.

Sie verwenden die HAVING- Klausel, um Gruppen einzuschränken.

Sie können keine Gruppenfunktionen in der WHERE -Klausel verwenden.

SELECT department_id, AVG(salary) FROM employees

WHERE AVG(salary) > 8000 GROUP BY department_id;

SELECT department_id, AVG(salary) FROM employees

WHERE AVG(salary) > 8000 GROUP BY department_id;

WHERE AVG(salary) > 8000

*

ERROR at line 3:

ORA-00934: group function is not allowed here WHERE AVG(salary) > 8000

*

ERROR at line 3:

ORA-00934: group function is not allowed here

(47)

Gruppenergebnisse filtern: WHERE / HAVING-Klausel

Verwenden Sie die HAVING -Klausel, um Gruppen einzuschränken:

1. Die Tupel werden mit der WHERE-Bedingung ausgewählt 2. Die Zeilen werden gruppiert.

3. Die Gruppenfunktion wird angewandt.

4. Gruppen, die der HAVING -Klausel entsprechen, werden angezeigt.

SELECT column, group_function

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[ORDER BY column];

(48)

SELECT department_id, MAX(salary) FROM employees

GROUP BY department_id

HAVING MAX(salary)>10000 ;

Beispiele:

SELECT job_id, SUM(salary) PAYROLL FROM employees

WHERE job_id NOT LIKE '%REP%' GROUP BY job_id

HAVING SUM(salary) > 13000

GROUP BY-Klauseln über mehrere Spalten verwenden SELECT department_id, job_id, SUM(salary)

FROM employees

GROUP BY department_id, job_id ;

(49)

Primary key, Foreign key, Secundary key

Primary Key:

Definiert den Hauptschlüssel der Tabelle.

Jeder Entrag muss eindeutig sein.

Es können auch mehrere Attribute sein.

CREATE TABLE EMP (

id AS INTEGER NOT NULL,

Hausnummer as myNumber default 123, Name as char(200),

PRIMARY KEY (id) );

(50)

Primary key, Foreign key, Secundary key

Foreign Key:

Definiert den Hauptschlüssel einer anderen Tabelle.

Jeder Entrag muss eindeutig sein.

Es können auch mehrere Attribute sein.

Foreign- Tabelle muss vorher definiert sein!

CREATE TABLE EMP (

Hausnummer AS MYNUMBER DEFAULT 123, Name AS CHAR(200),

deptno1 AS INTEGER NOT NULL, PRIMARY KEY (id)

constraint emp_fk_emp foreign key (deptno1) references dept(deptno) );

(51)

Primary key, Foreign key, Secundary key

Foreign Key: Nachträgliche Definition CREATE TABLE EMP (

EMPNO INTEGER NOT NULL, ENAME CHAR(50),

SAL NUMERIC(7,2),

DEPTNO INTEGER NOT NULL, PRIMARY KEY (EMPNO)

);

ALTER TABLE EMP

ADD CONSTRAINT FK_DEPT_EMP

FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO);

(52)

Primary key, Foreign key, Secundary key

Secundaray Key:

Definiert ein Attribut oder mehrere Attribute, die untereinander eindeutig sein müssen.

Erzwingt NOT NULL CREATE TABLE EMP (

Name AS CHAR(200) NOT NULL, Job AS INTEGER NOT NULL,

deptno AS INTEGER NOT NULL, PRIMARY KEY (id)

UNIQUE sec_key_job_name (Name, Job) );

(53)

Primary key, Foreign key, Secondary key

Weitere Syntaxbeispiele:

ALTER TABLE tablename ADD PRIMARY KEY (A1, A2);

ALTER TABLE tablename DROP PRIMARY KEY;

ALTER TABLE tablename ADD FOREIGN KEY (A1) REFERENCES tablename2(B4, B6);

ALTER TABLE tablename ADD CONSTRAINT name_des_fk FOREIGN KEY (A1) REFERENCES tablname2(B4, B6);

ALTER TABLE tablename DROP FOREIGN KEY;

ALTER TABLE tablename ADD UNIQUE name (A1, A2);

ALTER TABLE tablename DROP INDEX name;

(54)

INDIZES

Indizes beschleunigen meistens den Zugriff auf große Datenbestände.

Sie werden mittels B*- Bäume verwaltet.

Sie können an- und ausgeschaltet werden.

Indizes werden bei Primär- Schlüsseln automatisch gesetzt.

Sie müssen mitverwaltet werden.

Sollten nur bei häufig benutzen Feldern und großen Tabellen gesetzt werden.

CREATE TABLE emp (

Name AS CHAR(200) NOT NULL, Job AS INTEGER NOT NULL, PRIMARY KEY (id)

);

CREATE ASC INDEX idx_Name ON emp (Name);

CREATE ASC INDEX idx_NameVorname ON emp (Name,Vorname);

(55)

CONSTRAINTS

Constraints sind Zusatzbedingungen, die erfüllt sein müssen, damit Daten in die Datenbank geschrieben, gelöscht oder verändert werden können.

1. Beispiel:

Das Datum der Bestellung muss ab dem Jahr 2000 sein.

ALTER TABLE bestellung

ADD ct_year CHECK (YEAR >= 2000)

2. Beispiel:

Der Studiengang eines Studenten nur aus KI, II, WI, KT bestehen.

ALTER TABLE student

ADD CONSTRAINT ct_stdgang

CHECK (studiengang in ('IB', 'KI, 'II', 'WI', 'KT'));

(56)

CONSTRAINTS

3. Beispiel:

Die Bestellmenge muss immer größer Null sein.

ALTER TABLE SALES

ADD CONSTRAINT ct_sales CHECK (qty_ordered >= 1)

4. Beispiel:

Die Postleitzahl muss immer fünfstellig sein

CHECK (

( PLZ>=1000) AND (PLZ<=99999) )

(57)