1 Themen heute Rückblick Erweiterung funktionale Beziehungen Relationenmodell Integritätsregeln...

1

Themen heute

• Rückblick• Erweiterung funktionale Beziehungen• Relationenmodell• Integritätsregeln• Übersetzung ERM in Tabellenmodell• Übung• Grundlegende SQL-Kommandos

Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

2

Rückblick

• E/R-Modell• Entity, Entity-Typen, Attribute, Domänen• Relationship-Typ / Beziehungstyp• Funktionale Beziehungen(1:1, 1:n, n:1, n:m)• ISA-Relationship

– total / partiell– disjunkt / nicht disjunkt

• Schlüsselattribute– Schlüsselkandidat– Primärschlüssel– „Fremdschlüssel“– Zusammengesetzter Schlüssel


3

Graphische Bausteine des ERM


E Entity-Typ E

A Attribut A

A Schlüssel-Attribut A

E A Verbindung zwischen Entity-Typ E und Attribut AA kann undefiniert sein

E1 E2R Binäre Relationship zwischen den Entity-Typen E1 und E2

4

Funktionale Beziehungen / Beispiele


Abteilungsleiter Abteilungleitet

Angestellter Abteilungarbeitet-in

Person Hausbesitzt

Student Vorlesunghört

1 1

N 1

1 N

N M

5

ISA-Relationship


p

Isa-Beziehung:p: partiellt: total

t

nicht disjunkt disjunkt

total partiell

Web-ShopVertriebsdatenbank

7

Produkt Bestellung Kunde

Lieferant

Spediteur Mitarbeiter

Kategorie

liefert beschreibt

liefertaus

Posten bestellt

bearbeitet

PNr BNr KuNr

LNr KaNr

SNr MNr

n m n 1

n

1

n

1

n

1

n

1

Anzahl

8


Lieferant


Kategorie

Offerte

liefert beschreibt

liefertaus

Posten bestellt

bearbeitetofferiert

PNr BNr KuNr

LNr KaNr

SNr MNr ONrbearbeitet

Offerte

n m n 1

n

1

n

1

n

1

n

1

1 n

1

n

Anzahl BDatumPreis

LPreisLDATUM

OPosten

n

mAnzahlPreis

n

Das Relationenmodell

10

Sinn der 3 Modellierungssprachen

Relationale Modell (Algebra)

• mathematische Fundierung,

• Semantisch eindeutige Formulierung

„Tabellenmodell“

• Implementierung / Realisierung

E/R-Modell

• Kommunikation, Dokumentation

• Übersichtliche Darstellung


11

Definition: Datenbankschema

Unter einem Datenbankschema versteht man eine Spezifikation der Datenstrukturen einer Datenbank mit den zugehörigen Integritätsbedingungen.

D.h.: ein Datenbankschema enthält die Definition der

– Tabellen

– Attribute

– Primärschlüssel

– Integritätsbedingungen (Einschränkungen der Wertebereiche der Attribute)


12

Grundkonzepte des Relationenmodells

• Wertebereich / DomäneMögliche Werte eines Attributs, Beispiel „string“

• RelationEine Relation R ist eine Teilmenge des kartesischen Produktes von Domänen Di (1 i n): R D1 x ... x Dn Relationen kann man auch als die Zeilen einer zweidimensionale Tabellen ansehen.

• TupelTupel sind Elemente von Relationen. In Tabellen entsprechen sie den Zeilen.


13


• AttributSpalte einer Tabelle

• AttributwertElement eines dem Attribut zugeordneten Wertebereichs

• RelationenschemaEin Relationenschema besteht aus einem Relationennamen, gefolgt von der Liste ihrer Attribute und den zugeordneten Domänen: R(A1:D1,...,An:Dn)


14


• Relationales DB-SchemaMenge aller Relationenschema in der Datenbank

• Relationale DBDas relationale DB-Schema zusammen mit den momentanen Werten der Relationen


15

Integritätsregeln

Integritätsbedingungen sind Forderungen an die Zusammenhänge über die in der relationalen DBen enthaltenen Daten. Es gibt zwei verschiedene Typen von Integritätsbedingungen:

• Strukturelle Regeln: Sie sind inhärent für das Datenmodell.

• Verhaltensregeln: sie sind abhängig von der jeweiligen Anwendung.


16

Integritätsregeln

Eindeutigkeit von Schlüsseln

Relationen dürfen als Tupelmengen keine Duplikate enthalten. Eine noch schärfere Einschränkung wird durch den Begriff des Schlüsselkandidaten ermöglicht.

Definition:

Eine Menge S von Attributen einer Relation R heißt Schlüsselkandidat, wenn gilt:

1. Keine Instanz von R kann zwei verschiedene Tupel enthalten, die in S übereinstimmen.

2. Keine echte Teilmenge von S hat Eigenschaft 1.


17

Integritätsregel 1 - Primärschlüssel

Primärschlüssel

Jede Relation muß mindestens einen ausgewählten Schlüsselkandidaten besitzen, den sogenannten Primärschlüssel.


18

Definition: Fremdschlüssel

Ein Fremdschlüssel einer Tabelle ist ein Attribut, oder eine Attributs-kombination, das (bzw. die) in einer anderen Tabelle als Primärschlüssel auftritt.

Fremdschlüssel stellen die gewünschten Beziehungen zwischen Tabellen her.


19

Integritätsregel 2 - Fremdschlüssel

FremdschlüsselDer Primärschlüssel eines Relationenschemas R, das einen Relationship-Typ eines E/R-Diagramms modelliert, enthält Fremdschlüssel.

Beispiel:In der Relation „liefert(LName,ArtName,Preis)“ ist „Lname“ ein Fremdschlüssel aus „LIEFERANT“, und „ArtName“ ein Fremdschlüssel aus „ARTIKEL“.

Eine Relationship kann nur existieren, wenn die beteiligten Entities existieren.


20

Integritätsregel 2 – Referentielle Integrität

Falls ein Relationenschema R einen Fremdschlüssel

F = {A1,..., Al}

des Relationenschemas S enthält, muß zu jedem Tupel

t = (A1 = a1,..., Al = al, ...) aus R

ein Tupel

t' = (A1 = a1,..., Al = al, ...) in S

existieren.

Diese Integritätsregel bezeichnet man auch als referentielle Integrität.


21

Integritätsregel 2 – Referentielle Integrität

Bemerkung:

• Verletzungen der referentiellen Integrität können auftreten, wenn neue Tupel in R eingefügt oder existierende Tupel aus S gelöscht werden.

• Fremdschlüssel sind meist Teil des Primärschlüssels.


22

Integritätsregel 3 – NULL-Werte

Manchmal tritt der Fall auf, daß ein neues Tupel t in die Relation R eingefügt werden soll, ohne daß alle Attributwerte von t bekannt oder relevant sind. Für solche Situationen verwendet man den sogenannten NULL-Wert anstelle unbekannter oder irrelevanter Attributwerte.


23


Beispiel:

Der neue Angestellte Robert Ford wird eingestellt; die Festsetzung seines Gehalts steht aber noch aus.

ANGESTELLTER AngName Gehalt

'Robert Ford' NULL

Außerdem soll die Zuordnung von Robert Ford zu einer ABTEILUNG erst später getroffen werden. Analog zum Eintrag in ANGESTELLTER ergibt sich daher:

arbeitet_in AngName AbtName

'Robert Ford' NULL


24


NULL-Werte können nicht auf allen Attributpositionen sinnvoll eingesetzt werden


25


Kein Attributwert des Primärschlüssels

einer Relation darf NULL sein.


26


Bemerkung

• Dadurch soll die eindeutige Identifikation von Tupeln, die Entities oder Relationships beschreiben, gesichert sein.

• Für Fremdschlüssel, die nicht Teil des Primärschlüssels sind, gilt Integritätsregel 3 nicht.


27

Integritätsregeln - alle

Alle drei Integritätsregeln sind strukturelle Integritätsregeln für das relationale Modell.

Sie sollten von einem relationalen DBMS automatisch überprüft werden!!!


Übersetzung E/R-Modell in „Tabellen“-Modell

(in Relationales Datenbankschema)

29

Datenmodellierung im Relationalen Modell

• Bei der Erstellung des relationalen Schemadesigns wird folgende Vorgehens-weise nachdrücklich empfohlen:

–Erstellung eines E/R-Diagramms. –Konvertierung des E/R-Diagramms in

ein relationales DB-Schema. • Grund: Grössere Übersichtlichkeit und

grössere semantische Ausdruckskraft des E/R-Modell gegenüber dem Relationen Modell.


30

Regel 1

Jeder Entity-Typ

muss

als eigenständige Tabelle

mit eindeutigem Primärschlüssel

definiert werden.


31

Konvertierung in Relationales Modell

Konvertierungsregel 1:

E/R-Modell Relationen Modell

Ein Entity-Typ E mit Attributen A1,...,Ak aus den Domänen D1,...,Dk wird abgebildet auf ein k-stelliges Relationenschema E(A1:D1, ..., Ak:Dk).

Falls dabei ein Relationship-Typ E isa F vorliegt, kann man die Attributvererbung (z.B.) durch Hinzunahme aller Schlüsselattribute von F berücksichtigen.


32

Regel 2

Jede Relationship

kann

als eigenständige Tabelle

definiert werden.

Die Primärschlüssel der zugehörigen Entity-Typen treten als Fremd-schlüssel in dieser Tabelle auf.


33

Konvertierung in Relationales Modell

Konvertierungsregel 2:

E/R-Modell Relationen Modell

Ein Relationship-Typ R zwischen den Entity-Typen E1, ..., En wird dargestellt durch ein Relationenschema R, dessen Attribute aus allen Schlüsselattributen der Ei bestehen. Gleiche Attribute werden dabei durch Umbenennung in R eindeutig gemacht. Falls R eigene Attribute besitzt, nimmt man diese hinzu.


34

Regel 2

Der Primärschlüssel der Relationship kann sich aus den Fremdschlüsseln zusammensetzen oder ein anderer Schlüsselkandidat sein,

z.B. ein neuer künstlich eingeführter Schlüssel.


35

Beispiel: ERM Regel 1 und 2


Abteilung

ProjektMitarbeiter Zugehörig-keit

Abteilungs-Leiter

Unter-stellung

mn

n

1

1

1

%-AnteilM#

A#

P#

Name Ort Inhalt

Bezeichnung

36

Alternative Kennzeichnung von Schlüsseln

A# := A


37

Beispiel: Tabellen Regel 1


Abteilung Projekt Mitarbeiter

A# Bezeichnung P# Inhalt M# Name Ort

38

Beispiel: Tabellen Regel 2


Abteilungsleiter Unterstellung Zugehörigkeit

A# M# M# A# M# P# %-Anteil

39

Regel 2 (Beispiel)

Da zu jeder Abteilung genau ein Abteilungsleiter gehört, genügt die Abteilungsnummer A# in der Tabelle ABTEILUNGSLEITER als Primärschlüssel.

Analoges gilt für M# in der Tabelle UNTERSTELLUNG


40

Regel 2 (Beispiel)

In der Tabelle ZUGEHÖRIGKEIT müssen die Fremdschlüssel M# und P# zusammen als Primärschlüssel definiert werden.


Abbildungsregeln für

Beziehungsmengen

42

Regel 3 (n:m-Beziehung)

Jede n:m Beziehungsmenge muss als eigenständige Tabelle definiert werden.

Die Primärschlüssel der zugehörigen Entity-Typen treten in der Relationship als Fremdschlüssel auf.

Der Primärschlüssel der Relationship setzt sich aus den enthaltenen Fremdschlüsseln zusammen oder ist ein anderer Schlüsselkandidat.


43

Regel 4 (1:n - Relationship)

Jede 1:n Relationship kann ohne zusätzlicheeigenständige Tabelle definiert werden.

Dazu wird in einer der beiden Tabellen mit Assoziationstyp n ein Fremdschlüssel auf die damit verknüpfte Tabelle geführt.

Die Fremdschlüsselbeziehung wird (i.B.) durch ein Attribut gegeben, das sich aus dem entliehenen Primärschlüssel und dem Entity-Namen „Unterstellung“ zusammensetzt.


44

Beispiel: Regel 4 (1:n)


Mitarbeiter AbteilungUnter-stellung 1n

AbteilungMitarbeiter

A# BezeichnungM# Name Ort A#_Unterstellung

Fremdschlüssel-Beziehung

45

Regel 5 (1:1- Relationship)

Jede 1:1 Relationship kann ohne zusätzlicheeigenständige Tabelle definiert werden.

Dazu wird in einer der beiden Tabellen mit Assoziationstyp 1 ein Fremdschlüssel auf die damit verknüpfte Tabelle geführt.

Die Fremdschlüsselbeziehung wird (i.B.) durch ein Attribut gegeben, das sich aus dem entliehenen Primärschlüssel und dem Entity-Namen „ Abteilungsleiter“ zusammensetzt.


46

Beispiel: Regel 5 (1:1)


Mitarbeiter AbteilungAbteilungs-leiter 11

AbteilungMitarbeiter

A# BezeichnungM# Name Ort

Fremdschlüssel-Beziehung

M#_Abteilungsleiter

Abbildungsregeln für die

Generalisation

48

Regel 6

Jeder Entitytyp einer Generalisationshierarchie verlangt eine eigenständige Tabelle, wobei der Primärschlüssel der übergeordneten Tabelle auch Primärschlüssel der untergeordneten Tabelle wird.


49

Regel 6

Die Nicht-Disjunkt Eigenschaft erfordert keine spezielle Regelung.

Die Disjunkt-Eigenschaft erfordert die Einführung eines Attributes „Kategorie“ in der übergeordneten Tabelle. Es enthält die Information zu welcher „Unterklasse“ das Objekt gehört.

Bei einer disjunkten und totalen Generalisation muss garantiert werden, dass pro Eintrag in der übergeordneten Tabelle ein Eintrag in einer der Spezialisierungen existiert und umgekehrt.


50

Regel 6 (Beispiel)


Führungskraft Fachspezialist Lehrling

Mitarbeiter

t

Mitarbeiter

M# Name Ort

Führungskraft

M# Stellung

Fachspezialist

M# Know-how

Lehrling

M# Lehrjahr

Kategorie

M#

Name

Ort

KategorieStellung

Lehrjahr

Know how

51

Regel 6 (Beispiel)

Damit ein bestimmter Mitarbeiter nicht mehreren Kategorien gleichzeitig angehört, führen wir das Merkmal Kategorie ein.

Dieses kann die folgenden Werte annehmen:– „Führungskraft“– „Fachspezialist“– „Lehrling“

Es garantiert die Eigenschaften der disjunkten Generalisierungshierarchie.

Die Eigenschaft der Vollständigkeit kann nicht im Datenbankschema ausgedrückt werden.


ÜbungVertriebsdatenbank

in Tabellenformübersetzen

53


Lieferant


Kategorie

Offerte

liefert beschreibt

liefertaus

Posten bestellt

bearbeitetofferiert

PNr BNr KuNr

LNr KaNr

SNr MNr ONrbearbeitet

Offerte

n m n 1

n

1

n

1

n

1

n

1

1 n

1

n

Anzahl BDatumPreis

LPreisLDATUM

OPosten

n

mAnzahlPreis

54

Tabellen Vertriebsdatenbank

Kunde(KuNr, Name, Adresse, Rabatt)Produkt(PNr, P-Name, KaNr, LNr, Preis, LPreis)Bestellung(BNr,KuNr,MNr,SNr,Bestelldatum,

Lieferdatum)Lieferant(LNr,Name, Adresse)Kategorie(KaNr,Name)Spediteur(SNr, Name, Adresse)Mitarbeiter(MNr,Name, Adresse)Offerte(Onr, KuNr, MNr,Posten(PNr,BNr,Anzahl)Oposten(PNr,ONr,Anzahl,Preis)


55

Themen heute

Nachtrag Funktionale Beziehungen (Beziehungstypen / Assoziationstypen)

SQL-Einführung / Übersicht


56

Nachtrag Funktionale Beziehungen

Es gibt in der Datenbank-Literatur diverse Notationen mit unterschiedlicher Bedeutung

insbesondere eine „genau“ entgegen- gesetzte Definition / Interpretation der funktionalen Beziehung (1:N).


57

Kardinalitäten als Teilnahmezahl an der Beziehung

„Kardinalitäten als Teilnahmezahl an der Beziehung“ ist die ausdrucksstärkste Variante zur Beschreibung von Beziehungstypen.

Sie soll zur Klärung der Situation deshalb zunächst vorgestellt werden.


58



E1 E2R[a,b] [c,d]

Allgemeine Form für diese zweistellige Beziehungstypen:

59


Die Kardinalitätsangaben

[a,b] und [c,d]

schränken die möglichen Teilnahmen von Instanzen der beteiligten Entity-Typen an der Beziehung ein,

indem jeweils minimale Werte a, c und maximale Werte b, d vorgegeben werden.


60



Mitarbeiter Raumarbeitet-in

E1 E2R[a,b] [c,d]

[0,1] [0,3]

Hierbei beschreiben die numerischen Werte, wie oft (mindestens (a bzw. c) und höchstens (b bzw. d)) ein Entity des zugehörigen Entity-Typs (E1 bzw. E2) in der Beziehung auftauchen muss (mindestens) und kann (höchstens). a, b, c, d (0, 1, ...., *)

61



Mitarbeiter Raumarbeitet

in

[0,1] [0,3]

Jedem Mitarbeiter ist höchstens ein Raum zugeordnet.Es kann aber auch Mitarbeiter geben, denen kein Raum zugeordnet ist.

Es kann Räume geben, denen kein Mitarbeiter zugeordnet ist.Einem Raum können maximal 3 Mitarbeiter zugeordnet sein.

R#M#

62




in

[0,1] [0,3]

M# R#

M1M2M3M4

R1R1R1R2

M1M2M3M4M5

R1R2R3R4

3mal

Maximal 3 maloder kein mal

Maximal 1 maloder kein mal

R#M#

63




in

[0,1] [0,3]

M# R#

M1M2M3M4M5

R1R2R3R4

R#M#

64




in

[1,1] [1,3]

M# R#

M1M2M3M4M5

R1R1R2R3R4

M1M2M3M4M5

R1R2R3R4

R#M#

65



Mitarbeiter Abteilungleitet

Mitarbeiter Abteilungarbeitet-in

Person Hausbesitzt

Student Vorlesunghört

[0,1] [1,1]

[1,1] [1,*]

[0,*] [1,1]

[0,*] [0,*]

66



Die numerischen Werte [min, max] beschreiben, wie oft ein Entity des Entity-Typs an der Beziehung teilnehmen kann,

d.h., sie schränken die Teilnahmen von Instanzen der beteiligten Entity-Typen an der Beziehung ein.

min, max (0, 1, ...., *), (* = unbegrenzt oft)

[0, *] legt keine Einschränkungen fest

[2, 5] mindestens 2 mal, höchstens 5 mal

mögliche Anzahl: 2, 3, 4 ,5

67

Assoziationstypen

Alternative Notation:

1 [1,1] genau ein

c [0,1] kein oder ein

m,n [1,*] mehrere

mc, nc [0,*] kein, ein oder mehrere


68

Beispiel: Alternative Notation


Abteilung

ProjektMitarbeiter Zugehörig-keit

Abteilungs-Leiter

Unter-stellung

mnc

1

m

c

1

%-AnteilM#

A#

P#

Name Ort Inhalt

Bezeichnung

69

Alternative - Funktionale Beziehungen


......

......[0,1] : [1,1]

.........

Mitarbeiter Abteilung


1:m

leitet

Unterstellung

......

[1,1] : [1,*]

c:1

Alternative Notation: 1: m

Alternative Notation: c: 1

......

70

Alternative - Funktionale Beziehungen


Alternative Notation: 1: nc

.........


1:nc

Unterstellung

[1,1] : [0,*]

..

..

Variante von Unterstellung mit der Möglichkeit vonAbteilungen ohne Mitarbeiter

71

Funktionale Beziehungen (ERM Ursprung)


[0,1] : [0,*]Alternative Notation: c: nc

Mitarbeiter AbteilungUnter-

stellung

1N

.........


N:1

Unterstellung

..

..

..

......

Bedeutung in alternativer Notation

72

Funktionale Beziehungen (ERM Ursprung)

bedeutet demnach:• Jeder Mitarbeiter kann höchstens zu einer Abteilung

gehören• Einer Abteilung können mehrere Mitarbeiter

zugeordnet sein• Abteilungen und Mitarbeiter müssen aber nicht alle in

der Beziehung auftauchen


Mitarbeiter AbteilungUnter-

stellung1n

73

Funktionale Beziehungen (ERM Ursprung) / Bemerkung

Was folgt daraus?

Übernahme der „C-Notation“ ins ürsprüngliche ERM

ist nicht sinnvoll!!!


74

Funktionale Beziehungen (ERM Ursprung) / Bemerkung

• Falls man Fremdschlüssel von Abteilung in der Tabelle Mitarbeiter führt, kann der Wert gegebenenfalls NULL werden.

• Falls man eine zusätzliche Verknüpfungstabelle einführt, kann man die nicht vorhandenen Verknüpfungen einfach weglassen, kommt dabei ohne NULL-Werte aus.

• Die referentielle Integrität ist verletzt,wenn ein Fremdschlüsselwert exisitiert für den kein zugehöriger Primärschlüssel existiert.


75

Referentielle Integrität

Falls ein referenziertes Tupel in der Vaterrelation gelöscht oder geändert wird, sind folgende Regeln anwendbar:

• Restricted: Die (Lösch-) Operation auf der Vaterrelation wird nicht zugelassen

• Cascade: die Referenztupel werden mitgelöscht bzw. die geänderten Schlüsselwerte werden übernommen

• Set NULL / Set Default: Die Werte in den zugehörigen Fremdschlüsseln werden auf NULL oder den Default-Wert gesetzt.


SQLStructured Query Language

77

SQL Geschichte

• 1974 erste relationale Anfragesprache SEQUEL• 1979 Oracle liefert erstes relationales DBMS mit

SQL aus• 1983 IBM stellt DB2 vor• 1985 INGRES und Informix stellen auf SQL um• 1987 ISO 9075 Standard (SQL86)• 1988 ADABAS erhält SQL• 1989 ISO 9075 SQL mit Integrity Enhancement (SQL

89)• 1992 ISO 9075 (SQL2 bzw. SQL-92)• 1999 SQL:1999 (SQL3)


78

SQL - Implementationen

Standard-SQL und die verfügbaren SQL-Implementierung in kommerziell verfügbaren Datenbanksystemen sind nicht identisch!!!Man unterscheidet– Entry-Level SQL– Intermediate-Level SQL– Full-Level SQL

Der Entry-Level wird von den meisten verfügbaren DBMS unterstützt


79


Unterschiede ergeben sich aus:

unterschiedlichen Funktionalitäten– Teilweise werden Anforderungen des Standards

nicht erfüllt (MySQL)– Teilweise werden zusätzliche Funktionen

angeboten

• unterschiedlicher Syntax (MS-Access)


80


Beispiel: MySQL

unterstützt folgende Funktionen nicht:

• Transaktionen• Referentielle Integrität• Sichten• Unterabfragen


81

Sprachelemente SQL

• DDL (Data Defintion Language)– Datenbank anlegen– Definition von Domänen– Definition von Relationen und

Integritätsbedingungen– CREATE TABLE (...)– Definiton von Datensichten (Views /

Abfragen)


82

Sprachelemente SQL

• DML (Data Manipulation Language)– Eingabe (INSERT INTO) – Änderung (UPDATE)– Löschen (DELETE FROM)

• Query-Language– SELECT ... FROM ... WHERE ...


83

Sprachelemente SQL

Zusätzliche Anweisungen

• zur Steuerung von Transaktionen• zur Definition von Indexen• zur Verwaltung von Benutzern und

deren Zugriffsrechten


84

Datenbank anlegen, löschen, schliessen

• CREATE DATABASE datenbankname

• DROP DATABASE datenbankname

• CLOSE DATABASE datenbankname


Nicht standardisiert!!!

85

Datenbank anlegen, löschen, schliessen

Beispiele:

• CREATE DATABASE Kunden

• DROP DATABASE Kunden

• CLOSE DATABASE Kunden


86

SQL Domänen

Neben den vordefinierten Datentypen können selbstdefinierte Domänen bei der Tabellendefinition zur Spezifikation der zulässigen Attributwerte verwendet werden (natürlich auch mehrfach).


87

SQL Domänen

Allgemein:CREATE DOMAIN domänenname [AS]

Datentyp[default][bedingung];

„bedingung“ wird grundsätzlich durch CHECK-Klausel realisiert.


88

SQL Domänen

Beispiele:CREATE DOMAIN Zahlungsart AS CHAR(1)

CHECK (VALUE IN (‘R‘, ‘B‘, ‘N‘,‘V‘,‘K‘))DEFAULT ‘N‘;

CREATE DOMAIN Kunden-Key AS INTEGERCHECK (VALUE > 100);

CREATE DOMAIN Kunden-Status AS CHAR(1)CHECK (VALUE IN (‘W‘, ‘G‘, ‘S‘));


89

SQL Domänen

Beispiel:

CREATE TABLE kunde (kunden_nr Kunden_Key NOT NULL,status Kunden-Status,zahlung Zahlungsart,...);


90

SQL Datentypen

• INTEGER vier Byte• SMALLINT zwei Byte• DECIMAL (p,q) Dezimalzahlen p-Stellen• REAL Gleitpunktzahlen (einf.)• CHARACTER(n) Zeichenketten, Länge=n• CHARACTER VARYING(n) höchstens n Zeichen

• DATE Kalenderdaten 1 - 9999• TIMESTAMP(p) Datum und Uhrzeit• TIME(p) Stunden, Min., Sek., • BOOLEAN (True, False, Unknown)


91

Operationen mit Datentypen

Für numerische Datentypen• Addition: +• Subtraktion: -• Multiplikation: *• Division: /Reihenfolge der Abarbeitung

– Klammern – Punktrechnung vor Strichrechnung– von links nach rechts


92

SELECT (WHERE - Klausel)

6 Vergleichsoperatoren für Attributwerte:

= gleich

<> oder != ungleich (DB-spezifisch)

< kleiner

> grösser

<= kleiner oder gleich

>= grösser oder gleich


93


Ausführungsreihenfolge der logischen Operatoren:

1 NOT

2 AND

3 OR


94

CREATE TABLE

Allgemein:

CREATE TABLE table_name(spaltendefinitionsliste[,tabellenintegritätsregelliste]);

spaltendefinition ::=spaltenname typangabe [default-Klausel]

[spaltenintegritätsregelliste]

tabellenintegritätsregel ::=

check-klausel | primary_key-klausel |

unique-klausel | foreign-key-klausel


95

CREATE TABLE

Einfach Allgemein:

CREATE TABLE table_name(Spalte1 Daten-Typ-für-Spalte-1, Spalte2 Daten-Typ-für-Spalte-2,... ) ;

Beispiel: für eine Kundentabelle

CREATE TABLE Kunde(Vorname char(50),Name char(50),Addresse char(50),Stadt char(50),Land char(25),Geburtstag date);


96

DDL CREATE TABLE

CREATE TABLE mitarbeiter

(personnr INTEGER NOT NULL,

name CHAR(18) NOT NULL

vorname CHAR(18),

geschlecht CHAR(1),

abtnr INTEGER,

Gehalt DECIMAL(8,2));


97

DDL CREATE TABLE

CREATE TABLE Kunde (kundennr INTEGER NOT NULL,status CHAR(1) NOT NULL,Name CHAR(30) NOT NULL,zahlungsart CHAR(1) NOT NULL DEFAULT

‘N',Ort CHAR (39) NOT NULL,CHECK (status IN (‘W‘, ‘G‘, ‘S‘)),CHECK (zahlungsart IN (‘R‘, ‘B‘, ‘N‘,‘V‘,‘K‘)),PRIMARY KEY (kundennr));


98

DDL CREATE TABLE

Mögliche Konsistenzbedingungen:• Primärschlüssel

• weitere Schlüssel (Schlüsselkandidaten)

• Fremdschlüssel mit Bezugstabelle

• Einschränkungen für die Wertebereiche der Spalten

• Verbot von Nullmarken in Spalten

• Spaltenübergreifende Integritätsbedingungen

• tabellenübergreifende Integritätsbedingungen


SELECT

100

SELECT

Der Queryblock hat in seiner Grundform die Gestalt:

SELECT [DISTINCT] A1,..., Ak

FROM R1, ..., Rl

WHERE

Erläuterungen: • Die Ai sind Attribute der Relationen R1, ..., Rl.

• Bei Mehrdeutigkeit kann die ausführliche Form Rj.Ai gewählt werden.

• „WHERE“ ist eine Formel über den Relationen Rj. Die üblichen logischen Konnektoren und arithmetischen Vergleichsoperatoren sind erlaubt.


101

SELECT

Beispiel:

Ermittle die Namen aller Kunden mit negativem Saldo.

SELECT KundenName

FROM KUNDEN

WHERE Saldo < 0;


102

SELECT

Beispiel:

Ermittle alle Kundendaten aller Kunden mit negativem Kontostand.

SELECT *

FROM KUNDEN

WHERE Saldo < 0;


SELECTBeispiel

104

DDL CREATE

CREATE TABLE Ladeninfo

(Ladenname CHAR(50),

Verkauf INTEGER,

Datum DATE);


105

Beispieltabelle

Ladenname Verkauf Datum

Lörrach 1500 Jan-05-2001

Konstanz 250 Jan-07-2001


Freiburg 700 Jan-08-2001

Ladeninfo

106

SELECT

Allgemein:

SELECT column_name

FROM table_name;

Beispiel:

SELECT Ladenname

FROM Ladeninfo ;


107

SELECT

Resultat:

Ladenname

Lörrach

Konstanz

Lörrach

Freiburg

Es können mehrere Spaltennamen und mehrere Tabellennamen angegeben werden.


Doppelwerte

108

SELECT - DISTINCT

Allgemein:

SELECT DISTINCT column_name FROM table_name ;

Beispiel:

SELECT DISTINCT Ladenname FROM Ladeninfo ;


109

SELECT - DISTINCT

Resultat:

Ladenname

Lörrach

Konstanz

Freiburg


Keine doppelten Wertemehr!

110

SELECT (WHERE-Klausel)

Allgemein:

SELECT column_name FROM table_name WHERE condition ;

Beispiel:

SELECT Ladenname FROM Ladeninfo WHERE Verkauf > 1000 ;

selektiert alle Läden mit mehr als 1000.- DM Verkauf an einem Tag.


111

SELECT

Resultat:

Ladenname

Lörrach


112


AND-Operator / OR-Operator / NOT-OperatorSELECT Ladenname, Verkauf, Datum

FROM Ladeninfo WHERE Verkauf > 1000 AND Datum != Jan-05-2001 ;

SELECT *FROM Ladeninfo WHERE Verkauf > 1000 OR Datum = Jan-05-2001 ;

SELECT Ladenname , Verkauf

FROM Ladeninfo WHERE NOT Verkauf > 1000;


113


Kombination von OperatorenSELECT Ladenname

FROM Ladeninfo WHERE Verkauf > 1000 AND (Datum != Jan-05-2001 OR DATUM != Jan-06-2001) ;

SELECT Ladenname FROM Ladeninfo WHERE Verkauf > 1000 OR Datum != Jan-05-2001 AND Datum != Jan-07-2001;

SELECT Ladenname FROM Ladeninfo WHERE NOT Verkauf > 1000 AND Verkauf > 400 ;


114

SELECT- Funktionen

Allgemein:

SELECT function type(column_name) FROM table_name;

Beispiel:

SELECT SUM(Verkauf)

FROM Ladeninfo;

Das Kommando liefert die Summe aller Verkaufswerte (Einträge in der Spalte Verkauf)


115

SELECT - Funktionen

Resultat:

SUM(Verkauf)

2750

2750 ist die Summe aller Einträge in der Spalte Verkauf.

SUM(Verkauf)=1500 + 250 + 300 + 700.


116

SELECT - Funktionen

Standard-Aggregatfunktionen

• SUM (Summe)

• COUNT (Anzahl Tupel)

• MIN (Minimum)

• MAX (Maximum)

• AVG (Mittelwert)


117

SELECT - COUNT

Allgemein:

SELECT COUNT(column_name) FROM table_name ;

Beispiel:

SELECT COUNT(Ladenname) FROM Ladeninfo ;

Das Beispiel liefert die Anzahl der in der Tabelle eingetragenen Läden.


118

SELECT - COUNT

Resultat:

Count(Ladenname)

4


119

SELECT - COUNT

COUNT und DISTINCT können auch zusammen in einer Anfrage verwendet werden, um die Anzahl der unterschiedlichen Einträge in der Tabelle zu ermitteln.Z.B.:

SELECT COUNT(DISTINCT Ladenname) FROM Ladeninfo

ermittelt die Anzahl der verschiedenen Ladeneinträge in der Tabelle


120

SELECT - COUNT

Resultat:

Count(DISTINCT Ladenname)

3


121

SELECT - AVG

Allgemein:

SELECT AVG (column_name) FROM table_name ;

Beispiel:

SELECT AVG (Verkauf) FROM Ladeninfo ;

Das Beispiel liefert den Mittelwert der in der Tabelle eingetragenen Verkaufswerte.


122

SELECT - MIN

Allgemein:

SELECT MIN (column_name) FROM table_name ;

Beispiel:

SELECT MIN (Verkauf) FROM Ladeninfo ;

Das Beispiel liefert den kleinsten Wert der in der Tabelle eingetragenen Verkaufswerte.


123

SELECT - MAX

Allgemein:

SELECT MAX (column_name) FROM table_name ;

Beispiel:

SELECT MAX (Verkauf) FROM Ladeninfo ;

Das Beispiel liefert den grössten Wert der in der Tabelle eingetragenen Verkaufswerte.


INSERT INTO

125

INSERT INTO

Es gibt zwei Möglichkeiten, Daten in eine Tabelle einzufügen:

• Jede Zeile für sich allein

• mehrere Zeilen gleichzeitig


126

INSERT INTO

Allgemein:

INSERT INTO table_name (column1, column2, ...)VALUES (value1, value2, ...) ;

Beispiel:

INSERT INTO Ladeninfo (Ladenname, Verkauf, Datum)VALUES ('Lörrach', 900, 'Jan-10-2001') ;

fügt eine zusätzliche Zeile in die Tabelle Ladeninfo mit den Verkaufsinformationen für Lörrach am 10.Januar 2001 ein.


127

INSERT INTO








Ladeninfo

Tabelle nach der Insert-Operation

128

INSERT INTO

Allgemein:

INSERT INTO Tabellen_Name (Spalte1, Spalte2, ...)SELECT Spalte1, Spalte2, ...FROM Tabelle1 ;

Diese Form des Kommandos ermöglicht es gleichzeitig mehrere Zeilen in eine Tabelle einzufügen. Mit dem SELECT-Kommando spezifizieren wir die Daten, die wir in die Tabelle einfügen wollen. Dazu verwenden wir Informationen aus einer anderen Tabelle.


129

INSERT INTO

INSERT INTO Ladeninfo (Ladenname, Verkauf, Datum)SELECT Ladenname, Verkauf, DatumFROM Verkauf_InformationWHERE Year(Datum) = 1998;

Wenn Sie z.B. gerne eine Tabelle hätten, die die Verkaufsinformationen für das Jahr 1998 enthält, und wenn es bereits eine Verkaufstabelle gibt, die die Werte von 1995 bis 2000 enthält und wenn beide Tabellen die gleiche Struktur haben, liefert das Kommando das gewünschte Ergebnis.


UPDATE

131

UPDATE

Allgemein:

UPDATE table_nameSET column_1 = [new value]WHERE {condition} ;

Beispiel:

UPDATE LadeninfoSET Verkauf = 500WHERE Ladenname = "Lörrach" AND Datum = "Jan-08-2001“;


132

UPDATE

Ladeninfo

Beispieltabelle

vor dem UPDATE:







133

UPDATE

Das Kommando ändert die Verkaufswerte für den Laden Lörrach am 01/08/2001auf 500.- DM anstatt wie bisher 300.- DM.


134

UPDATE

Ladeninfo

Ergebnistabelle

nach dem UPDATE:







135

UPDATE

In diesem Fall gibt es nur eine Zeile in der betrachteten Tabelle, die der Bedingung (WHERE-Klausel) genügt.

Würden mehrere Zeilen der Bedingung genügen, würden sie alle entsprechend geändert.


136

UPDATE

Es ist auch möglich mehrere Spalten gleichzeitig zu ändern. Die Syntax dafür lautet:

UPDATE table_nameSET column_1 = new value 1,

column_2 = new value 2)

WHERE {condition} ;


DELETE FROM

138

DELETE FROM

Allgemein:

DELETE FROM table_nameWHERE {condition} ;

Beispiel:

DELETE FROM LadeninfoWHERE Ladenname = "Lörrach“;

löscht alle Datensätze (Zeilen) aus der Tabelle „Ladeninfo“deren Ladenname identisch ist mit „Lörrach“.


139

DELETE FROM

Ladeninfo

Die Ergebnistabelle

sieht dann

folgendermassen aus:





Date post:	05-Apr-2015
Category:	Documents
Upload:	emmeline-lafrenz
View:	111 times
Download:	1 times

1 Themen heute Rückblick Erweiterung funktionale Beziehungen Relationenmodell Integritätsregeln...

Documents