Vom ERM zum Datenbankschema - BFH-TI Staff

Vom ERM zum
Datenbankschema
Contents
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
Chapter 7
Vom ERM zum Datenbankschema
Datendefinitionssprache
(SQL/DDL)
1 Datendefinitionssprache (SQL/DDL)
Integritätsbedingungen
(Constraint)
Lecture Datenbanken
25.04.2014
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
Views
2 Integritätsbedingungen (Constraint)
ERD und
Datenbankschema
Überprüfen der 3NF
ERD und
Datenbankschema
Überprüfen der 3NF
3 Views
4 ERD und Datenbankschema
5 Überprüfen der 3NF
Pierre Fierz
Berner Fachhochschule
Technik und Informatik
7.1
Die Lernziele dieses Kapitels.
Vom ERM zum
Datenbankschema
7.2
Outline
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
1 Datendefinitionssprache (SQL/DDL)
Integritätsbedingungen
(Constraint)
Integritätsbedingungen
(Constraint)
Views
1
Syntax der Daten Definition Language
2
Übersetzen eines ERM in ein korrektes Datenbankschema
3
Normalisierung des Schemas
Datendefinitionssprache
(SQL/DDL)
Views
2 Integritätsbedingungen (Constraint)
ERD und
Datenbankschema
Überprüfen der 3NF
ERD und
Datenbankschema
Überprüfen der 3NF
3 Views
4 ERD und Datenbankschema
5 Überprüfen der 3NF
7.3
7.4
Vom ERM zum
Datenbankschema
DDL
Vom ERM zum
Datenbankschema
Datentypen
Pierre Fierz
Pierre Fierz
• Die nächste Tabelle zeigt alle SQL-Datentypen
Datendefinitionssprache
(SQL/DDL)
• Die Datendefinitionssprache (kurz DDL für Data Definition
Language) dient dazu, das Datenbankschema zu
definieren.
• Dazu gehören die bekannten Konzepte:
•
•
•
•
•
•
Typ
Integritätsbedingungen
(Constraint)
BOOLEAN
Views
INTEGER
ERD und
Datenbankschema
BIGINT
SMALLINT
DECIMAL(p,q)
Überprüfen der 3NF
Beispielwert
TRUE
4771
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
Festkommazahl
1003.65
Fliesskommazahl
1.5E-4
alphanumerische
Zeichenkette
’Ein String’
binäre
Zeichenkette
B’110100101’
Datum
Zeit
Zeitstempel
Zeitintervall
DATE’1951-11-24’
TIME’11:39:49’
TIMESTAMP’2002-08-23 14:15:00’
INTERVAL’48’ HOUR
NUMERIC(p,q)
FLOAT(p)
Tabelle (Relationenschema)
Attribut
Primärschlüssel
Kandidatschlüssel
Fremdschlüssel
Integritätsbedingung.
Instanz
Wahrheitswert
ganze Zahl
ERD und
Datenbankschema
Überprüfen der 3NF
REAL
DOUBLE PRECISION
CHAR(q)
VARCHAR(q)
CLOB
BINARY(q)
BINARY VARYING(q)
BLOB
DATE
TIME
TIMESTAMP
INTERVAL
7.5
Definition von Tabellen
Vom ERM zum
Datenbankschema
7.6
Definition von Tabellen (2)
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
• In SQL spricht man von Tabellen und nicht von
Relationenschemata
• Eine Tabelle wird im wesentlichen durch Ihre Attribute
gegeben
SQL Syntax (CREATE TABLE)
::= CREATE TABLE <table-name>
(<table-element> [,<table-element>]. . . )
<table-element>
::= <attribut-definition> | <table-constraint>
<attribut-definition> ::= <attribut-name>
<data-typ>
[<default-definition>]
[<attribut-constraint>]. . .
<default-definition> ::= DEFAULT {<literal> | <niladic-function> | NULL}
<niladic-function> ::= {USER | CURRENT_USER | SESSION_USER |
SYSTEM_USER | CURRENT_DATE |
CURRENT_TIME | CURRENT_TIMESTAMP}
<table-definition>
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Mitarbeiter Tabelle
• Im folgenden Beispiel wird die Mitarbeitertabelle definiert.
Views
ERD und
Datenbankschema
Überprüfen der 3NF
CREATE TABLE Mitarbeiter (
mNr
INTEGER,
Name
VARCHAR(30),
AhvNr
VARCHAR(16),
Plz
CHAR(8),
Ort
VARCHAR(30),
Geschlecht
CHAR(1),
Geburtsdatum DATE
)
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
• Die Definition von <table-constraint> und
<attribut-constraint> werden wir später behandeln
7.7
7.8
Ändern von Tabellen
Vom ERM zum
Datenbankschema
Ändern von Tabellen (2)
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
• In SQL werden Änderungen im Schema mit dem ALTER
Befehl durchgeführt.
SQL Syntax (ALTER TABLE)
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
<table-alteration> ::=
<action>
::=
|
|
|
|
ALTER TABLE <table-name> <action>
ADD [COLUMN] <attribut-definition>
ALTER [COLUMN] <attribut-name>
{SET <default-definition> | DROP DEFAULT}
DROP [COLUMN] <attribut-name>
[RESTRICT | CASCADE]
ADD <table-constraint>
DROP CONSTRAINT <constraint-name>
[RESTRICT | CASCADE]
Ändern der Mitarbeiter Tabelle
• Nachfolgend einige Beispiele zum ALTER TABLE Befehl
Views
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
-- Wir führen ein neues Attribut Strasse ein.
ERD und
Datenbankschema
Überprüfen der 3NF
ALTER TABLE Mitarbeiter ADD Strasse VARCHAR(30);
ERD und
Datenbankschema
Überprüfen der 3NF
-- Nun entfernen wir das Attribut Geschlecht
ALTER TABLE Mitarbeiter DROP Geschlecht RESTRICT;
-- Wir setzen den Default des Attributs Plz auf 3000
ALTER TABLE Mitarbeiter ALTER Plz SET DEFAULT ’3000’;
• RESTRICT bedeutet, dass ein Objekt nicht gelöscht wird,
falls es noch (in eine View oder Constraint) gebraucht wird.
• CASCADE bedeutet, dass eventuell referenzierende
Objekte auch gelöscht werden.
7.9
Löschen einer Tabelle
Vom ERM zum
Datenbankschema
7.10
Outline
Vom ERM zum
Datenbankschema
Pierre Fierz
• Eine Tabelle kann mit dem DROP TABLE Befehl aus der
Datenbank entfernt werden.
• Mit diesem Befehl werden auch alle Tupel in der
entsprechenden Tabelle gelöscht.
SQL Syntax (DROP TABLE)
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
1 Datendefinitionssprache (SQL/DDL)
Integritätsbedingungen
(Constraint)
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
Views
ERD und
Datenbankschema
2 Integritätsbedingungen (Constraint)
Überprüfen der 3NF
ERD und
Datenbankschema
Überprüfen der 3NF
3 Views
<drop-table> ::= DROP TABLE <table-name> [RESTRICT |
CASCADE]
4 ERD und Datenbankschema
Löschen der Mitarbeiter Tabelle
DROP TABLE Mitarbeiter CASCADE
5 Überprüfen der 3NF
7.11
7.12
Vom ERM zum
Datenbankschema
Integritätsbedingungen
Vom ERM zum
Datenbankschema
Integritätsbedingungen
Pierre Fierz
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
Datendefinitionssprache
(SQL/DDL)
• Integritätsbedingungen dienen dem Ausschluss von
“semantisch inkorrekten” Datenzuständen.
• SQL:1992 unterstützt die deklarative Formulierung von
Integritätsbedingungen auf Attributten und Tabellen.
• Globale Integritätsbedingungen heissen Assertions.
Integritätsbedingungen
(Constraint)
• Die Syntax für Integritätsbedingung ist die folgende
Views
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
SQL Syntax (Constraints)
<constraint>
::=
<constraint-type> ::=
• Alle Integritätsbedingungen haben einen eindeutigen
Namen.
ERD und
Datenbankschema
[CONSTRAINT <constraint-name>]
<constraint-type>
<attribut-constraint> |
<table-constraint>
Überprüfen der 3NF
• Der Name wird bei einer Verletzung der Bedingung in der
Fehlermeldung ausgegeben.
• Falls der Name in der Definition nicht explizit angegeben
wird, so kreiert das DBMS automatisch einen eindeutigen
Namen.
7.13
Integritätsbedingungen auf Attributen
Vom ERM zum
Datenbankschema
7.14
Integritätsbedingungen auf Attributen (2)
Vom ERM zum
Datenbankschema
Pierre Fierz
• Diese Integritätsbedingungen werden bei der Definition
von Attributen angegeben.
• Wie der Name andeutet, sind solche Bedingungen genau
auf einem Attribut in einer Tabelle beschränkt.
• Eine Attributbedingung hat die folgende Form:
Pierre Fierz
• NOT NULL schliesst den Wert NULL für dieses Attribut aus.
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
Überprüfen der 3NF
|
|
|
|
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
SQL Syntax (Attributconstraints)
<attribut-constraint> ::=
• UNIQUE testet, dass der Attributwert eindeutig ist.
• Ausnahme ist der Wert NULL, der beliebig oft vorkommen
darf.
• PRIMARY KEY definiert das Attribut als Primärschlüssel
der Tabelle.
• Diese Angabe impliziert gleichzeitig eine Not Null
ERD und
Datenbankschema
Überprüfen der 3NF
Bedingung.
• Pro Tabelle darf nur ein Attribut diese Constraint haben.
NOT NULL
UNIQUE
PRIMARY KEY
REFERENCES <table-name(attribut-name)>
[ON DELETE <referential-action>]
[ON UPDATE <referential-action>]
CHECK(<conditional-expression>)
• REFERENCES spezifiziert das Attribut als Fremdschlüssel.
• CHECK schränkt den Wertebereich der Spalte durch die
Angabe eines boolschen Ausdrucks ein.
• der Ausdruck darf nur eine Variable enthalten und zwar das
Attribut selbst.
• Der Ausdruck darf die Operatoren OR, AND und NOT
enthalten.
7.15
7.16
Integritätsbedingungen auf Attributen (3)
Vom ERM zum
Datenbankschema
Integritätsbedingungen auf Tabellen
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
• Diese Integritätsbedingungen werden bei der Definition
• Das nächste Beispiel zeigt die Mitarbeiter Tabelle mit
Constraints
Mitarbeiter Tabelle
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
• Die Integritätsbedingungen haben die folgende Form:
Views
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
CREATE TABLE Mitarbeiter (
mNr
INTEGER PRIMARY KEY,
Name
VARCHAR(30) NOT NULL,
AhvNr
VARCHAR(16) UNIQUE NOT NULL,
Plz
CHAR(8) NOT NULL,
Ort
VARCHAR(30) NOT NULL,
Geschlecht
CHAR(1) NOT NULL CHECK(VALUE IN (’W’,’M’)),
Geburtsdatum DATE NOT NULL,
aNr
INTEGER NOT NULL REFERENCES Abteilung(aNr)
)
einer Tabelle angegeben.
SQL Syntax (Tableconstraints)
Überprüfen der 3NF
ERD und
Datenbankschema
Überprüfen der 3NF
{PRIMARY KEY | UNIQUE} (<attr-list>)
| FOREING KEY (<attribut-list>)
REFERENCES table-name [(<attr-list>)]
[MATCH {SIMPLE | PARTIAL | FULL}]
[ON DELETE <referential-action>]
[ON UPDATE <referential-action>]
| CHECK (conditional-expression)
<attr-list>
::= <attribut-name> [,<attribut-name>]...
<referential-action> ::= {NO ACTION | CASCADE | SET DEFAULT | SET NULL}
<table-constraint> ::=
7.17
Integritätsbedingungen auf Tabellen (2)
Vom ERM zum
Datenbankschema
7.18
Integritätsbedingungen auf Tabellen (3)
Vom ERM zum
Datenbankschema
Pierre Fierz
• Falls der Primärschlüssel, ein Kandidatschlüssel oder ein
Fremdschlüssel aus mehr als einem Attribut besteht, so
muss dieser in einer Tableconstraint definiert werden.
• Für den Fremdschlüssel sind die folgenden Match-Regeln
möglich:
• SIMPLE: Falls ein Attribut des Fremdschlüssels NULL ist,
wird die referentielle Integrität nicht geprüft. Dies ist die
Defaultregel.
• PARTIAL: Entweder haben alle Attribute den Wert NULL
oder jeder Nicht-Nullwert stimmt mit dem
korespondierenden Schlüsselwert eines Tupels in der
referenzierten Tabelle überein.
• FULL: Entweder haben alle Attribute den Wert NULL oder
es gibt ein Tupel in der referenzierten Tabelle, welches
dieselben Werte für die korespondierenden Schlüsselwerte
hat.
Pierre Fierz
• Referenzielle Aktionen geben an, was passieren soll, falls
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
7.19
eine Zeile in der referenzierten Tabelle gelöscht wird (ON
DELETE) oder der Schlüsselwert geändert wird (ON
UPDATE).
• Folgende referenzielle Aktionen sind spezifizierbar:
• NO ACTION: Das Löschen bzw. Ändern in der
referenzierten Tabelle wird zurückgewiesen, wenn dadurch
die Fremdschlüsselbedingung verletzt wird. Dies ist die
Defaulteinstellung.
• CASCADE: Das Löschen bzw. Ändern wird kaskadierend
auf allen Tupeln durchgeführt, welche das gelöschte bzw.
das geänderte Tupel referenzieren.
• SET NULL: Felder des Fremdschlüssels werden auf Null
gesetzt.
• SET DEFAULT: Der Fremdschlüssel wird auf den
definierten Defautwert gesetzt, falls in der referenzierten
Tabelle ein Tupel mit dem entsprechenden Schlüssel
existiert.
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
7.20
Integritätsbedingungen auf Tabellen (4)
Vom ERM zum
Datenbankschema
Integritätsbedingungen auf Tabellen (5)
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
• Das nächste Beispiel zeigt die Mitarbeiter Tabelle mit
Tabellen Constraints
Datendefinitionssprache
(SQL/DDL)
• Die CHECK Constraint formuliert eine allgemeine
Integritätsbedingung.
• In der Bedingung dürfen alle Felder der Tabelle
vorkommen.
• Komplexere Bedingungen können mit den boolschen
Operatoren OR, AND und NOT gebildet werden.
Integritätsbedingungen
(Constraint)
Abteilung Tabelle
Views
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
CREATE TABLE Abteilung (
aNr
INTEGER,
AName
VARCHAR(30) NOT NULL,
mNr
INTEGER,
LeiterSeit
Date,
• Das Prädikat darf nach SQL-Standard ebenfalls
ERD und
Datenbankschema
Überprüfen der 3NF
PRIMARY KEY (aNr),
UNIQUE (AName),
UNIQUE (mNr),
FOREIGN KEY (mNr) REFERENCES Mitarbeiter (mNr)
ON DELETE NO ACTION,
CHECK(not((mNr is null and LeiterSeit is not null) OR
(mNr is not null and LeiterSeit is null)))
Subqueries enthalten
• Achtung: Viele Implementationen erlauben keine
Subqueries in der CHECK Klausel.
)
7.21
Globale Integritätsbedingungen
Vom ERM zum
Datenbankschema
7.22
Globale Integritätsbedingungen (2)
Vom ERM zum
Datenbankschema
Pierre Fierz
• Eine Assertion ist eine Integritätsbedingung, die
ausserhalb einer Tabellendefinition mit der folgenden
Anweisung angelegt wird:
SQL Syntax (ASSERTION)
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
<assertion> ::=
<assertion-name>
CHECK (<conditional-expression>)
CREATE ASSERTION
• <conditional-expression> ist ein beliebiger SQL-Befehl,
der als wahr oder falsch evaluiert wird.
• Im Ausdruck können beliebige Tabellen miteinander
Abteilungen haben einen Standort
Im Mitarbeiter Beispiel können wir so erzwingen, dass jede
Abteilung mindestens 1 Standort besitzt. Die Assertion würde
etwa so aussehen.
CREATE ASSERTION AbteilungStandort
CHECK (NOT EXISTS
(SELECT *
FROM
Abteilung a
WHERE NOT EXISTS
(SELECT *
FROM
Standort s
WHERE a.aNr = s.aNr)))
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
verknüpft werden.
7.23
7.24
Überprüfungsmodi für Integritätsbedingungen
• Jede Integritätsbedingung hat einen initialen
Überprüfungsmodus und einen Verzögerungsmodus.
• Der Überprüfungsmodus bestimmt innerhalb einer
laufenden Transaktion den relativen Zeitpunkt, an dem die
Integritätsbedingung überprüft wird:
1 IMMEDIATE führt die Überprüfung unmittelbar nach jeder
Vom ERM zum
Datenbankschema
Überprüfungsmodi für Integritätsbedingungen (2)
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
• Zusammengefasst gilt die folgende Klausel für die
Spezifikation von Integritätsbedingungen:
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
SQL Syntax (Constraint Modus)
Überprüfen der 3NF
Überprüfen der 3NF
[CONSTRAINT <constraint-name>] <constraint-definition>
[[NOT] DEFERRABLE]
[INITIALLY {IMMEDIATE | DEFERRED}]
SQL-Anweisung durch.
2 DEFERRED verzögert die Überprüfung an das Ende der
jeweiligen Transaktion.
• Der Modus DEFERRED können nur verzögerbare
Integritätsbedingungen annehmen.
• Die Defaulteinstellungen sind
• Die Verzögerbarkeit wird entweder implizit durch die Klausel
oder explizit durch die Klausel
gesetzt.
INITIALLY DEFERRED
DEFERRABLE
NOT DEFERRABLE
und INITIALLY
IMMEDIATE.
7.25
Überprüfungsmodi für Integritätsbedingungen (3)
Vom ERM zum
Datenbankschema
7.26
Überprüfungsmodi für Integritätsbedingungen (4)
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
• Es gibt viele Beispiele, in denen die Überprüfung einer
Datendefinitionssprache
(SQL/DDL)
• Der Verzögerungsmodus kann innerhalb einer Transaktion
verändert werden.
• Dies geschieht mit dem folgenden Statement:
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
SQL Syntax (Setzen des Verzögerungsmodus)
<setconstraint> ::= SET CONSTRAINTS <name-list> {DEFERRED |
IMMEDIAT}
<name-list>
::= ALL | <constraint-name> [, <constraint-name>]...
Integritätsbedingung verzögert werden muss.
Abteilungen haben mindestens 1 Standort
In unserem Beispiel kann keine Abteilung eingefügt werden,
weil zu diesem Zeitpunkt zur Abteilung noch kein Standort
existiert.
Ein Standort kann aber wegen der referenziellen Integrität
nicht vor der Abteilung eingefügt werden.
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
CREATE ASSERTION AbteilungStandort
CHECK (NOT EXISTS
(SELECT *
FROM
Abteilung a
WHERE NOT EXISTS
(SELECT *
FROM
Standort s
WHERE a.aNr = s.aNr)))
DEFERRABLE INITIALLY DEFERRED
7.27
7.28
Outline
1 Datendefinitionssprache (SQL/DDL)
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
2 Integritätsbedingungen (Constraint)
Vom ERM zum
Datenbankschema
Was sind Views
ERD und
Datenbankschema
Überprüfen der 3NF
3 Views
• In einer Datenbank gibt es zwei Arten von Relationen:
1 Basisrelationen und
2 Views (oder Sichten)
• Die Basisrelationen sind alle mit dem Befehl CREATE
TABLE erzeugten Relationen der Datenbank
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
• Die Views sind virtuelle Tabellen, die aufgrund eines SQL
SELECT-Befehls aus Basisrelationen und anderen Views
4 ERD und Datenbankschema
zusammengestellt werden.
5 Überprüfen der 3NF
7.29
Eigenschaften von Views
Vom ERM zum
Datenbankschema
Pierre Fierz
7.30
Vom ERM zum
Datenbankschema
Kreieren einer View
• Views werden in SQL mit dem CREATE VIEW Befehl
Pierre Fierz
definiert.
Datendefinitionssprache
(SQL/DDL)
• In einer View werden keine Daten physisch gespeichert.
• Bei einer Anfrage auf einer View werden die Informationen
aus den Basisrelationen zusammengebaut, so dass die
Informationen auch immer aktuell sind.
• Auf einer View sind dieselben Anfrage-Operationen
Integritätsbedingungen
(Constraint)
Views
SQL Syntax (CREATE VIEW)
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
<view-definition> ::= CREATE VIEW <view-name>
(Constraint)
[(<attribut-name> [,<attribut-name>]...)]Views
ERD und
Datenbankschema
AS
ERD und
Datenbankschema
Überprüfen der 3NF
<selection>
[WITH CHECK OPTION]
Überprüfen der 3NF
erlaubt wie auf einer Basisrelation.
• Update-Operationen auf Views müssenin
Update-Operationen der zugrundeliegenden
Basisrelationen übersetzt werden.
• <selection> ist ein beliebiges Select-Statement.
• Eine View definiert, als Resultat eines SELECT Befehls,
• Die Übersetung ist jedoch nicht immer eindeutig möglich.
wieder eine Relation.
• Vom Standpunkt des Benutzers gibt es keinen
7.31
Unterschied zwischen einer Basisrelation und einer View
(ausser bei Update-Operationen).
• Da eine View selbst eine Relation ist, darf bei der
Definition keine ORDER BY Klausel verwendet werden.
7.32
Eine View löschen
Vom ERM zum
Datenbankschema
Pierre Fierz
SQL Syntax (DROP VIEW)
• Die folgende View zeigt pro Abteilung die gesammten
Datendefinitionssprache
(SQL/DDL)
Arbeitsstunden, die an Projekten geleistet werden.
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
CREATE VIEW ProjektStunden
(aNr, AName, Stunden)
AS
SELECT
aNr, AName, SUM(Wochenstunden)
FROM
MitProj NATURAL JOIN Projekt JOIN
Abteilung A ON paNr = A.aNr
GROUP BY aNr, AName;
Integritätsbedingungen
(Constraint)
Views
<delete-view> ::= DROP VIEW <view-name>
[RESTRICT | CASCADE]
Pierre Fierz
Kreieren einer View
• Eine View kann mit dem DROP VIEW Befehl wieder
gelöscht werden.
Vom ERM zum
Datenbankschema
Beispiele
ERD und
Datenbankschema
Überprüfen der 3NF
• Wird RESTRICT angegeben, wird die View nur gelöscht,
Views
ERD und
Datenbankschema
Überprüfen der 3NF
SELECT * FROM ProjektStunden
falls sie in keinen weiteren Definitionen (Views,
Procedures, Triggers usw.) verwendet wird.
• Dies ergibt die folgende Relation
aNr
1
2
3
4
5
• Wird CASCADE angegeben, werden alle Datenbank
Objekte in denen die View verwendet wird kaskadiert
gelöscht.
• RESTRICT ist der Default
AName
A
B
C
D
E
Stunden
181
122
97.5
130
29
7.33
Relationale Operationen auf Views
Vom ERM zum
Datenbankschema
7.34
Vom ERM zum
Datenbankschema
Relationale Operationen auf Views 2
Pierre Fierz
Pierre Fierz
• Auf Views können beliebige relationale Operationen
ausgeführt werden.
• Es bestehen also zwischen einer View und einer
Basisrelation von diesem Standpunkt aus keine
Unterschiede.
• Im folgenden Beispiel definieren wir eine View mit allen
• Nun selektieren wir mit Hilfe der View MitarbeiterProjekt
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
alle Mitarbeiter die zwischen 20 und 30 Stunden an
Projekten arbeiten.
Überprüfen der 3NF
Mitarbeitern und die Projekte an denen Sie arbeiten.
Mitarbeiter Projekt View
SELECT mNr, Name, sum(WochenStunden) AS Stunden
FROM MitarbeiterProjekt
GROUP BY mNr, Name
HAVING Stunden > 20 and Stunden < 30;
mNr
8
10
11
23
DROP VIEW MitarbeiterProjekt;
CREATE VIEW MitarbeiterProjekt AS
SELECT *
FROM
Mitarbeiter natural join MitProj
natural join Projekt;
7.35
Name
Meier
Glauser
Eicher
Ackermann
Integritätsbedingungen
(Constraint)
Views
Selektieren auf einer View
ERD und
Datenbankschema
Datendefinitionssprache
(SQL/DDL)
ERD und
Datenbankschema
Überprüfen der 3NF
Stunden
26
22
21
23
7.36
Update Operationen auf Views
Vom ERM zum
Datenbankschema
Update Operationen auf Views 2
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
• Nicht jede View ist Updatable.
• Wir betrachten folgendes UPDATE auf der View
MitarbeiterProjekt.
Update auf einer View
UPDATE MitarbeiterProjekt
SET
Ort = ’Basel’
WHERE mNr = 9 AND PStandort = ’Genf’;
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
• Die folgende unvollständige Liste gibt an, wenn eine View
nicht updatable ist.
1
Views
ERD und
Datenbankschema
2
Überprüfen der 3NF
3
4
• Das Problem ist hier, dass der Ort für einen Teil der
Projekte des Mitarbeiters verändert werden muss und für
einen andern Teil nicht.
5
• Dies ist nicht möglich, da der Ort nur einmal physisch
gespeichert ist.
Falls die View eine DISTINCT Klausel enthält, so sind
Updateoperationen nicht zugelassen.
Eine View mit UNION Operationen ist nicht updatable.
In einer View ist ein Feld, das aus einem Ausdruck gebildet
ist nicht updatable.
Views mit einer GROUP BY-Clausel und Felder, die mit
einer SQL Built-In Funktion wie SUM, AVG usw. berechnet
werden, sind nicht updatable.
Sind NOT NULL Attribute ohne DEFAULT oder Felder des
Primärschlüssels der Basisrelation in der View
“ausgeblendet”, so ist das Einfügen von Tupel in dieser
View nicht möglich.
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
• Dieses Problem tritt dann auf, wenn in der SET Klausel
und in der WHERE Klausel Attribute aus verschiedenen
Tabellen vorkommen.
7.37
Update Operationen auf Views 3
Vom ERM zum
Datenbankschema
7.38
With Check Option
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
• Die With Check Option Klausel macht nur einen Sinn,
Datendefinitionssprache
(SQL/DDL)
• Der folgende Spezialfall ist wichtig, falls der Zugriff auf
einzelne Tupel und Felder mit Hilfe von Views geregelt
wird.
• Falls die drei folgenden Bedingungen erfüllt sind, ist eine
View sicher updatable
1
2
3
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
Die View enthält nur Attribute aus einer einzigen
Basisrelation.
Der Primärschlüssel der Basisrelation ist vollständig in der
View enthalten.
Alle Attribute mit der NOT NULL Klausel sind in der View
vorhanden.
wenn die View verändert werden kan.
• Wenn Attributwerte in einem Tupel der View verändert
werden, ist es möglich, dass das Tupel anschliessend
nicht mehr zur View gehört.
• Ist die View mit der WITH CHECK OPTION definiert, so sind
solche Updates nicht erlaubt.
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
• Das nächste Beispiel illustriert diesen Effekt.
Check Option
CREATE VIEW Mitarbeiter1000_3100 AS
SELECT * FROM Mitarbeiter
WHERE Plz < 3100
WITH CHECK OPTION;
UPDATE Mitarbeiter1000_3100 Set Ort = ’Biel’, Plz = ’3210’
WHERE Plz = 3006;
ERROR 1369 (HY000): CHECK OPTION failed ’Mitarbeiter.Mitarbeiter1000_3100’
7.39
7.40
Sichten auf Daten
Vom ERM zum
Datenbankschema
Vereinfachen von Abfragen
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
• Views erlauben Abfragen zu vereinfachen.
• Komplizierte und häufig verwendete Join-Operationen
Datendefinitionssprache
(SQL/DDL)
• Wir wollen die Vorteile von Views hier zusammenfassen.
• Views sind ein wichtiger Bestandteil der
3-Schemen-Architektur.
• Mit Hilfe von Views können für verschiedene Benutzer
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
verschiedene Sichten auf die Daten definiert werden.
können in der Selektions-Klausel der View verpackt
werden.
• Ein Beispiel dafür ist die View MitarbeiterProjekt
• Der Benutzer kann mit Hilfe dieser View ohne komplizierte
Operationen sofort für beliebige Projekte und Mitarbeiter
die geplanten WochenStunden erfahren.
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
Vereinfachte Abfrage
• Jeder Benutzer kann mit Hilfe von Views genau auf die
Informationen zugreifen, die für ihn relevant sind und
andere Informationen “ausblenden”.
-- Dank dieser View
CREATE VIEW MitarbeiterProjekt AS
SELECT *
FROM
Mitarbeiter natural join MitProj
natural join Projekt;
-- wird der Query einfacher
SELECT mNr, Name, WochenStunden, PName
FROM MitarbeiterProjekt
WHERE WochenStunden > 40;
7.41
Datenschutz
Vom ERM zum
Datenbankschema
7.42
Logische Datenunabhängigkeit
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
• Zu einem Teil können die Applikationen gegenüber
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
• Datenschutz kann mit Hilfe von Views realisiert werden.
Views
• Daten, die mit Hilfe einer View “ausgeblendet” werden,
ERD und
Datenbankschema
sind für den Benutzer der View nicht sichtbar.
Änderungen im Datenbankschema mit Hilfe von Views
geschützt werden.
• Das folgende Beispiel soll das illustrieren.
• In unserer Beispieldatenbank wird die Tabelle Mitarbeiter
in die zwei neuen Tabellen MitarbeiterNeu und OrtTabelle
aufgeteilt.
Überprüfen der 3NF
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
Neues Schema
• Solche Daten können auch nicht verändert werden.
• Mit Views ist es also möglich, Datenschutz auf Feld sowie
CREATE TABLE OrtTabelle (
Plz CHAR(8) PRIMARY KEY,
Ort VARCHAR(30) NOT NULL
);
CREATE TABLE MitarbeiterNeu (
mNr
INTEGER PRIMARY KEY,
Name
VARCHAR(30) NOT NULL,
AhvNr
CHAR(16) NOT NULL,
Plz
CHAR(8) NOT NULL REFERENCES OrtTabelle (Plz)
ON DELETE NO ACTION,
Geschlecht
CHAR(1) NOT NULL,
Geburtsdatum DATE NOT NULL,
aNr
INTEGER NOT NULL REFERENCES Abteilung (aNr)
ON DELETE NO ACTION,
Vorgesetzter INTEGER REFERENCES Mitarbeiter (mNr)
ON DELETE SET NULL
);
auf Tupelebene einzuführen.
7.43
7.44
Logische Datenunabhängigkeit 3
Vom ERM zum
Datenbankschema
Vom ERM zum
Datenbankschema
Outline
Pierre Fierz
Pierre Fierz
• Nun müssten alle Applikationen, die auf Mitarbeiter
zugreifen, geändert werden.
• Durch die Definition der folgenden View Mitarbeiter kann
dies zu einem grossen Teil verhindert werden.
Datendefinitionssprache
(SQL/DDL)
1 Datendefinitionssprache (SQL/DDL)
Integritätsbedingungen
(Constraint)
Integritätsbedingungen
(Constraint)
Views
Neue View
CREATE VIEW Mitarbeiter
AS
SELECT *
FROM
MitarbeiterNeu natural join OrtTabelle
Datendefinitionssprache
(SQL/DDL)
Views
ERD und
Datenbankschema
2 Integritätsbedingungen (Constraint)
Überprüfen der 3NF
ERD und
Datenbankschema
Überprüfen der 3NF
3 Views
4 ERD und Datenbankschema
• Alle Programme, die Lieferantendaten nur lesen,
funktionieren jetzt unverändert weiter.
5 Überprüfen der 3NF
• Leider müssen Programme, die Mitarbeiterdaten
schreiben angepasst werden, da die View Mitarbeiter im
Allgemeinen nicht verändert werden kann.
7.45
Einleitung
Vom ERM zum
Datenbankschema
7.46
Entitätsmengen und Attributte
Vom ERM zum
Datenbankschema
Pierre Fierz
• Wir wollen nun zeigen, wie aus einem ERM ein
Datenbankschema erstellt wird.
• Dabei ist es wichtig, dass das entstehende
Datenbankschema die folgenden Eigenschaften aufweist.
• Alle im ERM definierten Beziehungen müssen im
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
relationalen Datenbankschema abgebildet sein.
• Alle Tabellen müssen in 3NF (BCNF) sein
• Die Anzahl Tabellen soll minimal sein.
• Die Übersetzung geschieht im Prinzip nach dem
Syntheseverfahren
• Für jedes Konstruktionselement im ERM wird eine Tabelle
oder ein Attribut im relationalen Modell erstellt.
7.47
• Für jede Entitätsmenge im ERM wird eine Tabelle im
Datenbankschema erstellt.
• Hier die einzelnen Schritte:
• Kreieren einer Tabelle mit dem Namen der Entitätsmenge
und dem Entitätsschlüssel als primär Schlüssel.
• Nun können alle Attribute mit Kardinalität 1 zum Schema
hinzugefügt werden. Wichtig, ist dass in diesem Fall die
Klausel NOT NULL angegeben wird.
• Anschliessend werden alle Attribute mit Kardinalität C im
Schema eingefügt. Für diese Attribute sind aber auch
Nullwerte erlaubt.
• Als letztes werden Zusammengesetzte Attribute durch ihre
Komponenten ersetzt.
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
7.48
Entitätsmengen und Attributte (2)
Vom ERM zum
Datenbankschema
Pierre Fierz
Entitätsmengen und Attributte (3)
Vom ERM zum
Datenbankschema
Pierre Fierz
• Jedes Attribut mit Kardinalität M oder MC muss in einer
eigenen Tabelle ausgelagert werden.
Entität Mitarbeiter
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
• Der Primärschlüssel der neuen Tabelle besteht aus dem
Primärschlüssel PK des ursprünglichen Schemas plus
dem Wert des ausgelagerten Attributs.
• Zusätzlich ist PK Fremdschlüssel auf die ursprüngliche
ERD und
Datenbankschema
Überprüfen der 3NF
Tabelle.
• Die referentielle Aktion ist CASCADE.
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
Hobbies des Mitarbeiters
CREATE TABLE Mitarbeiter (
mNr
INTEGER PRIMARY KEY,
Name
VARCHAR(30) NOT NULL,
Vorname
VARCHAR(30) NOT NULL,
Titel
VARCHAR(15),
Strasse
VARCHAR(30) NOT NULL,
Nr
INTEGER NOT NULL,
Plz
CHAR(8) NOT NULL,
Ort
VARCHAR(30) NOT NULL,
)
CREATE TABLE Hobby (
mNr
INTEGER REFERENCES Mitarbeiter(mNr)
ON DELETE CASCADE,
Hobby VARCHAR(20)
CHECK(Hobby IN (’Fussball’,’Klavierspielen’,
’Theater’,’Boxen’,’Kochen’,
’Gleitschirm’)),
PRIMARY KEY(mNr, Hobby)
)
7.49
Entitätsmengen und Attributte (4)
Vom ERM zum
Datenbankschema
7.50
Schwache Entitätsmengen
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
Datendefinitionssprache
(SQL/DDL)
• Die Darstellung von mehrfach Attributen gilt nur für den
Fall MC.
• Mit einem Fremdschlüssel kann nicht erzwungen werden,
dass zu jedem Mitarbeiter mindestens ein Hobby existiert.
• Dazu ist eine ASSERTION notwendig
Attribut vom Typ M
Integritätsbedingungen
(Constraint)
• Für schwache Entitätsmengen wird ebenfalls eine neue
Tabelle angelegt.
Views
ERD und
Datenbankschema
Überprüfen der 3NF
CREATE ASSERTION ’Hobby Kardinalitaet M’
CHECK (NOT EXISTS (SELECT *
FROM Mitarbeiter LEFT JOIN Hobby
WHERE Hobby IS NULL))
DEFERRABLE INITIALLY DEFERRED
• Der Primärschlüssel besteht aus dem Primärschlüssel PK
der dazugehörigen starken Entität plus dem
identifizierenden Attribut der schwachen Entität.
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
• Zusätzlich ist PK Fremdschlüssel auf die starke Entität.
• Die referentielle Aktion ist CASCADE.
• Für die Attribute gelten dieselben Regeln wie für starke
Entitäten.
• Achtung: Kommerzielle Systeme unterstützen keine
Assertions.
• Der obige Test muss entweder mit Triggers oder in der
Applikation durchgeführt werden.
7.51
7.52
Schwache Entitätsmengen (2)
Vom ERM zum
Datenbankschema
Generalisierung und Spezialisierung
Vom ERM zum
Datenbankschema
Pierre Fierz
Angehöriger eines Mitarbeiters
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
• Die Generalisierung (bzw. Spezialisierung) kann
grundsätzlich auf zwei verschiedene Arten realisiert
werden:
• Vertical: Bei dieser Art werden die Subentitäten auch als
Tabellen definiert
• Der Schlüssel der Tabelle für die Subentität ist gleichzeitig
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
Fremdschlüssel auf die Tabelle der Superentität.
CREATE TABLE Angehöriger (
mNr
INTEGER REFERENCES Mitarbeiter(mNr),
Vorname VARCHAR(30),
-- ... (weitere Attribute)
• Die Attribute der Superentität werden in der Subentität nicht
wiederhohlt, sondern vererbt.
• Horizontal: Alle beteiligten Entitäten werden mit nur einer
Tabelle realisiert.
PRIMARY KEY (mNr, Vorname)
• Für jede Subentität wird ein boolsches Attribut geführt.
• Das Attribut enthält true, falls die Entität auch Subentität
)
des entsprechenden Typs ist.
7.53
Generalisierung und Spezialisierung (2)
Vom ERM zum
Datenbankschema
7.54
Generalisierung und Spezialisierung (3)
Vom ERM zum
Datenbankschema
Pierre Fierz
Vertikale Darstellung
Pierre Fierz
• In dieser Darstellung ist nur der Fall
{incomplete, overlapping} abgedeckt.
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
• Für die anderen Fälle braucht es Assertions:
Disjoint
Views
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
CREATE TABLE
pNr
Titel
);
CREATE TABLE
pNr
Datendefinitionssprache
(SQL/DDL)
Publikation (
INTEGER PRIMARY KEY,
VARCHAR(30) NOT NULL
CREATE ASSERTION ’Buch und Zeitschrift disjunkt’
CHECK (NOT EXISTS (SELECT *
FROM Buch NATURAL JOIN Zeitschrift))
ERD und
Datenbankschema
Überprüfen der 3NF
Complete
CREATE ASSERTION ’Publikation ist abstrakt’
CHECK(NOT EXISTS
(SELECT *
FROM Publikation NATURAL LEFT JOIN Buch
NATURAL LEFT JOIN Zeitschrift
WHERE ISBN IS NULL AND Nummer IS NULL))
DEFERRABLE INITIALLY DEFERRED
Buch (
INTEGER PRIMARY KEY REFERENCES Publikation(pNr)
ON DELETE CASCADE,
VARCHAR(13) NOT NULL
ISBN
);
CREATE TABLE Zeitschrift (
pNr
INTEGER PRIMARY KEY REFERENCES Publikation(pNr)
ON DELETE CASCADE,
Nummer
INTEGER NOT NULL,
Jahr
INTEGER NOT NULL
);
7.55
7.56
Generalisierung und Spezialisierung (4)
Vom ERM zum
Datenbankschema
Vom ERM zum
Datenbankschema
Pierre Fierz
Horizontale Darstellung
CREATE TABLE Publikation (
pNr
INTEGER PRIMARY KEY,
Titel
VARCHAR(30) NOT NULL,
isBuch
BOOLEAN NOT NULL,
ISBN
VARCHAR(13),
isZeitschrift BOOLEAN NOT NULL,
Nummer
INTEGER NOT NULL,
Jahr
INTEGER NOT NULL
);
Beziehungsmengen
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
• Für Beziehungen gibt es im wesentlichen zwei Fälle: 1:M
und M:M
• Zusätzlich können die Kardinalitäten noch konditionell
ERD und
Datenbankschema
Überprüfen der 3NF
sein.
1:M und M:M Beziehungen
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
/* Falls Buch und Zeitschrift disjunkt sind, so gilt
die folgende Integritätsbedingung.*/
ALTER TABLE Publikation
ADD CONSTRAINT ’Buch und Zeitschrift disjunkt’
CHECK (NOT(isBuch AND isZeitschrift));
/* Falls Publikation abstrakt ist, so gilt die folgende
Integritätsbedingung. */
ALTER TABLE Publikation
ADD CONSTRAINT ’Publikation ist abstrakt’
CHECK (isBuch OR isZeitschrift);
7.57
Beziehungsmengen 1:M
Vom ERM zum
Datenbankschema
Pierre Fierz
• In diesem Fall kann die Beziehung durch einen
Fremdschlüssel realisiert werden.
Mitarbeiter Abteilung
Beziehungsmengen M:M
Pierre Fierz
eingeführt werden.
• Die Tabelle enthält zwei Fremdschlüssel, die zusammen
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
den Primärschlüssel bilden.
Mitarbeiter und Projekte
Views
CREATE TABLE Abteilung (
aNr
INTEGER PRIMARY KEY,
.
.
);
Vom ERM zum
Datenbankschema
• Für eine M:M Beziehung muss eine neue Tabelle
• Der Fremdschlüssel wird in der Tabelle auf der M-Seite
eingefügt.
7.58
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
CREATE TABLE Mitarbeiter (
mNr
INTEGER PRIMARY KEY,
...
);
ERD und
Datenbankschema
Überprüfen der 3NF
CREATE TABLE Projekt (
pNr
INTEGER PRIMARY KEY,
...
);
CREATE TABLE Mitarbeiter (
mNr
INTEGER PRIMARY KEY,
aNr
INTEGER NOT NULL REFERENCES Abteilung(aNr),
.
.
);
CREATE TABLE MitProj (
mNr
INTEGER REFERENCES Mitarbeiter(mNr),
pNr
INTEGER REFERENCES Projekt(pNr),
PRIMARY KEY (mNr, pNr)
);
• Für eine C:MC Beziehung können wir für aNr einfach
Nullwerte zulassen.
• Für eine 1:M Beziehung braucht es wieder eine Assertion
7.59
• Es ist der Fall MC:MC realisiert.
• Für die Fälle M:MC und M:M sind Assertions notwendig.
7.60
Beziehungsattribut 1:M
Vom ERM zum
Datenbankschema
Pierre Fierz
• Ein Beziehungsattribut in einer 1:M oder 1:1 Beziehung
wird in der Tabelle auf der M-Seite gemeinsam mit dem
Fremdschlüssel aufgenommen.
Beziehungsattribut 1:1
Datendefinitionssprache
(SQL/DDL)
Beziehungsattribut M:M
• Ein Beziehungattribut in einer M:M Beziehung wird in die
entsprechende Zwischentabelle aufgenommen.
Mitarbeiter, Projekte und Wochenstunden
Vom ERM zum
Datenbankschema
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Integritätsbedingungen
(Constraint)
Views
Views
ERD und
Datenbankschema
ERD und
Datenbankschema
Überprüfen der 3NF
Überprüfen der 3NF
CREATE TABLE Mitarbeiter (
mNr
INTEGER PRIMARY KEY,
...
);
CREATE TABLE Abteilung (
aNr
INTEGER PRIMARY KEY,
mNr
INTEGER UNIQUE
REFERENCES Mitarbeiter(mNr),
leitetSeit
DATE,
...
);
CREATE TABLE Projekt (
pNr
INTEGER PRIMARY KEY,
...
);
CREATE TABLE MitProj (
mNr
INTEGER REFERENCES Mitarbeiter(mNr),
pNr
INTEGER REFERENCES Projekt(pNr),
wochenstunden FLOAT NOT NULL,
CREATE TABLE Mitarbeiter (
mNr
INTEGER PRIMARY KEY,
...
);
PRIMARY KEY (mNr, pNr)
);
7.61
Outline
Vom ERM zum
Datenbankschema
7.62
Lokale Betrachtungen
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
• Die Transformation des ERM in ein Datenbankschema
1 Datendefinitionssprache (SQL/DDL)
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
2 Integritätsbedingungen (Constraint)
erfolgt im Prinzip nach dem Syntheseverfahren.
• Mit den folgenden Annahmen entsteht ein
Datenbankschema in dritter Normalform:
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
• Alle Attribute einer Entität sind voll vom Entitätsschlüssel
Views
ERD und
Datenbankschema
funktional abhängig.
• In einer Entität gibt es keine transitiven funktionalen
Abhängigkeiten.
ERD und
Datenbankschema
Überprüfen der 3NF
3 Views
Überprüfen der 3NF
• Eine transitive Abhängigkeit kann folgendermassen
entstehen:
• Die Abhängigkeit wurde nicht erkannt.
• In diesem Fall ist das ERM nicht korrekt und muss korrigiert
4 ERD und Datenbankschema
werden.
• Die Abhängigkeit ist im ERM als Integritätsbedingung
5 Überprüfen der 3NF
formuliert.
• In diesem Fall muss diese Abhängigkeit im
Datenbankschema nach den Regeln der Normalisierung
eliminiert werden.
7.63
7.64
Lokale Betrachtungen (2)
• Wir betrachten unser Beispiel aus dem Kapitel über
Normalisation
Lieferant Ort und Distanz
Im ERM ist die folgende Entitätsmenge definiert:
Vom ERM zum
Datenbankschema
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Zusätzlich sei die folgende Integritätsbedingung im ERM
angegeben:
Globale Betrachtungen
Überprüfen der 3NF
• Eine Verletzung der 3NF kann aber auch global (über
mehr als eine Tabelle) entstehen.
• Dies passiert, wenn im ERM redundante Informationen
vorhanden sind.
Mitarbeiter, Maschine und Produkt
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
(∀l1 , l2 ∈ Lieferant)(l1 (Ort) = l2 (ORT ) =⇒ l1 (DISTANZ ) = l2 (DISTANZ ))
•
•
•
•
In diesem Fall muss im Datenbankschema der Lieferant auf
zwei Tabellen aufgeteilt werden.
CREATE TABLE Lieferant(
lNr
INTEGER PRIMARY KEY,
oNr
INTEGER REFERENCES Ort(oNr)
);
Die Ausagen sind:
Eine Maschine wird nur von einem Mitarbeiter bedient
Ein Produkt wird nur von einer Maschine produziert
Ein Produkt wird nur von einem Mitarbeiter produziert
CREATE TABLE Ort(
oNr
INTEGER PRIMARY KEY,
Ort
VARCHAR(30) NOT NULL UNIQUE,
Distanz FLOAT NOT NULL
);
7.65
Globale Betrachtungen (2)
Vom ERM zum
Datenbankschema
7.66
Globale Betrachtungen (3)
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
• Aus dem vorigen ERM entsteht nun das folgende
Datenbankschema:
Datenbankschema Mitarbeiter, Maschine und Produkt
CREATE TABLE Mitarbeiter (
mNr
INTEGER PRIMARY KEY,
...
);
• In unserem Beispiel verletzt die Tabelle Produkt die 3NF
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
• Es gilt die FD maNr → mNr und somit ist mNr von prNr
transitiv abhängig.
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
• Die FD ist schon in der Tabelle Maschine festgehalten.
Views
ERD und
Datenbankschema
• In beiden Tabellen hat die FD die semantische Bedeutung
ERD und
Datenbankschema
Überprüfen der 3NF
“Maschine wird von einem Mitarbeiter bedient”
Überprüfen der 3NF
• In diesem Fall können wir mNr aus der Tabelle Produkt
CREATE TABLE Produkt (
prNr
INTEGER PRIMARY KEY,
maNr
INTEGER REFERENCES Maschine(maNr)
mNr
INTEGER REFERENCES Mitarbeiter(mNr)
...
);
entfernen und erhalten ein Schema in 3NF.
• Wichtig ist, dass beide FDs dieselbe Semantik haben.
• Ist dies nicht der Fall, so liegt keine Verletzung der 3NF
vor.
CREATE TABLE Maschine (
maNr
INTEGER PRIMARY KEY,
mNr
INTEGER REFERENCES Mitarbeiter(mNr)
...
);
• Zum Beispiel könnte mit mNr der Mitarbeiter bezeichnet
sein, der das Produkt verkauft.
7.67
7.68
Globale Betrachtungen (4)
• Aus dem Beispiel können wir ein Kriterium für eine globale
Verletzung der 3NF ableiten. Eine Tabelle kommt für eine
globale Verletzung der 3NF in Frage falls:
1
2
Die Tabelle (z.B. Produkt) hat einen Fremdschlüssel (maNr)
auf eine andere Tabelle (Maschine).
Die beiden Tabellen (Produkt, Maschine) haben ein
zusätzliches identisches Attribut (mNr).
Vom ERM zum
Datenbankschema
Betrachtungen zur 4NF
Vom ERM zum
Datenbankschema
Pierre Fierz
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
• Probleme mit der 4NF sollten bei der Übersetzung nicht
entstehen.
• Mehrfachattribute werden von begin weg in eine eigene
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
Tabelle ausgelagert.
• Diese Tabelle enthält dann nur triviale MVDs.
• Damit die 3NF verletzt ist, muss in beiden Tabellen eine
• Alle anderen Tabellen sollten keine MVDs mehr enthalten,
entsprechende FD existieren (maNr → mNr ).
falls das ERM richtig aufgebaut ist.
• Beide FDs müssen denselben semantischen Sachverhalt
darstellen (“Maschine wird von einem Mitarbeiter bedient”)
7.69
7.70