Vom ERM zum Datenbankschema - BFH-TI / Organisation

Vom ERM zum
Datenbankschema
Pierre Fierz
Chapter 7
Vom ERM zum Datenbankschema
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Lecture Datenbanken
25.04.2014
Views
ERD und
Datenbankschema
Überprüfen der 3NF
Pierre Fierz
Berner Fachhochschule
Technik und Informatik
7.1
Contents
Vom ERM zum
Datenbankschema
Pierre Fierz
1 Datendefinitionssprache (SQL/DDL)
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
2 Integritätsbedingungen (Constraint)
ERD und
Datenbankschema
Überprüfen der 3NF
3 Views
4 ERD und Datenbankschema
5 Überprüfen der 3NF
7.2
Die Lernziele dieses Kapitels.
Vom ERM zum
Datenbankschema
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
1
Syntax der Daten Definition Language
2
Übersetzen eines ERM in ein korrektes Datenbankschema
3
Normalisierung des Schemas
ERD und
Datenbankschema
Überprüfen der 3NF
7.3
Outline
Vom ERM zum
Datenbankschema
Pierre Fierz
1 Datendefinitionssprache (SQL/DDL)
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
2 Integritätsbedingungen (Constraint)
ERD und
Datenbankschema
Überprüfen der 3NF
3 Views
4 ERD und Datenbankschema
5 Überprüfen der 3NF
7.4
Vom ERM zum
Datenbankschema
DDL
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
• Die Datendefinitionssprache (kurz DDL für Data Definition
Language) dient dazu, das Datenbankschema zu
definieren.
• Dazu gehören die bekannten Konzepte:
•
•
•
•
•
•
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
Tabelle (Relationenschema)
Attribut
Primärschlüssel
Kandidatschlüssel
Fremdschlüssel
Integritätsbedingung.
7.5
Vom ERM zum
Datenbankschema
Datentypen
Pierre Fierz
• Die nächste Tabelle zeigt alle SQL-Datentypen
Typ
BOOLEAN
SMALLINT
Instanz
Wahrheitswert
ganze Zahl
Beispielwert
TRUE
4771
BIGINT
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)
Integritätsbedingungen
(Constraint)
Views
INTEGER
DECIMAL(p,q)
Datendefinitionssprache
(SQL/DDL)
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.6
Definition von Tabellen
Vom ERM zum
Datenbankschema
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)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
• Die Definition von <table-constraint> und
<attribut-constraint> werden wir später behandeln
7.7
Definition von Tabellen (2)
Vom ERM zum
Datenbankschema
Pierre Fierz
Mitarbeiter Tabelle
• Im folgenden Beispiel wird die Mitarbeitertabelle definiert.
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
7.8
Ändern von Tabellen
Vom ERM zum
Datenbankschema
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]
Views
ERD und
Datenbankschema
Überprüfen der 3NF
• 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
Ändern von Tabellen (2)
Vom ERM zum
Datenbankschema
Pierre Fierz
Ändern der Mitarbeiter Tabelle
• Nachfolgend einige Beispiele zum ALTER TABLE Befehl
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
-- Wir führen ein neues Attribut Strasse ein.
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’;
7.10
Löschen einer Tabelle
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)
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
<drop-table> ::= DROP TABLE <table-name> [RESTRICT |
CASCADE]
Löschen der Mitarbeiter Tabelle
DROP TABLE Mitarbeiter CASCADE
7.11
Outline
Vom ERM zum
Datenbankschema
Pierre Fierz
1 Datendefinitionssprache (SQL/DDL)
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
2 Integritätsbedingungen (Constraint)
ERD und
Datenbankschema
Überprüfen der 3NF
3 Views
4 ERD und Datenbankschema
5 Überprüfen der 3NF
7.12
Integritätsbedingungen
Vom ERM zum
Datenbankschema
Pierre Fierz
• 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.
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
• Alle Integritätsbedingungen haben einen eindeutigen
Namen.
• 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
Vom ERM zum
Datenbankschema
Integritätsbedingungen
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
• Die Syntax für Integritätsbedingung ist die folgende
Integritätsbedingungen
(Constraint)
Views
SQL Syntax (Constraints)
<constraint>
::=
<constraint-type> ::=
ERD und
Datenbankschema
[CONSTRAINT <constraint-name>]
<constraint-type>
<attribut-constraint> |
<table-constraint>
Überprüfen der 3NF
7.14
Integritätsbedingungen auf Attributen
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:
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
SQL Syntax (Attributconstraints)
<attribut-constraint> ::=
NOT NULL
| UNIQUE
| PRIMARY KEY
| REFERENCES <table-name(attribut-name)>
[ON DELETE <referential-action>]
[ON UPDATE <referential-action>]
| CHECK(<conditional-expression>)
7.15
Integritätsbedingungen auf Attributen (2)
Vom ERM zum
Datenbankschema
Pierre Fierz
• NOT NULL schliesst den Wert NULL für dieses Attribut aus.
• UNIQUE testet, dass der Attributwert eindeutig ist.
• Ausnahme ist der Wert NULL, der beliebig oft vorkommen
darf.
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
• 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.
• 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.16
Integritätsbedingungen auf Attributen (3)
Vom ERM zum
Datenbankschema
Pierre Fierz
• Das nächste Beispiel zeigt die Mitarbeiter Tabelle mit
Constraints
Mitarbeiter Tabelle
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)
)
Überprüfen der 3NF
7.17
Integritätsbedingungen auf Tabellen
Vom ERM zum
Datenbankschema
Pierre Fierz
• Diese Integritätsbedingungen werden bei der Definition
einer Tabelle angegeben.
• Die Integritätsbedingungen haben die folgende Form:
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
SQL Syntax (Tableconstraints)
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.18
Integritätsbedingungen auf Tabellen (2)
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.
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
7.19
Integritätsbedingungen auf Tabellen (3)
Vom ERM zum
Datenbankschema
Pierre Fierz
• Referenzielle Aktionen geben an, was passieren soll, falls
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
Pierre Fierz
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)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
• Das Prädikat darf nach SQL-Standard ebenfalls
Subqueries enthalten
• Achtung: Viele Implementationen erlauben keine
Subqueries in der CHECK Klausel.
7.21
Integritätsbedingungen auf Tabellen (5)
Vom ERM zum
Datenbankschema
Pierre Fierz
• Das nächste Beispiel zeigt die Mitarbeiter Tabelle mit
Tabellen Constraints
Abteilung Tabelle
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
CREATE TABLE Abteilung (
aNr
INTEGER,
AName
VARCHAR(30) NOT NULL,
mNr
INTEGER,
LeiterSeit
Date,
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)))
)
7.22
Globale Integritätsbedingungen
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)
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
verknüpft werden.
7.23
Globale Integritätsbedingungen (2)
Vom ERM zum
Datenbankschema
Pierre Fierz
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
7.24
Überprüfungsmodi für Integritätsbedingungen
Vom ERM zum
Datenbankschema
Pierre Fierz
• 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
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
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 Verzögerbarkeit wird entweder implizit durch die Klausel
oder explizit durch die Klausel
gesetzt.
INITIALLY DEFERRED
DEFERRABLE
7.25
Überprüfungsmodi für Integritätsbedingungen (2)
Vom ERM zum
Datenbankschema
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
• 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
[CONSTRAINT <constraint-name>] <constraint-definition>
[[NOT] DEFERRABLE]
[INITIALLY {IMMEDIATE | DEFERRED}]
• Die Defaulteinstellungen sind
NOT DEFERRABLE
und INITIALLY
IMMEDIATE.
7.26
Überprüfungsmodi für Integritätsbedingungen (3)
Vom ERM zum
Datenbankschema
Pierre Fierz
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>]...
7.27
Überprüfungsmodi für Integritätsbedingungen (4)
Vom ERM zum
Datenbankschema
Pierre Fierz
• Es gibt viele Beispiele, in denen die Überprüfung einer
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.28
Outline
Vom ERM zum
Datenbankschema
Pierre Fierz
1 Datendefinitionssprache (SQL/DDL)
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
2 Integritätsbedingungen (Constraint)
ERD und
Datenbankschema
Überprüfen der 3NF
3 Views
4 ERD und Datenbankschema
5 Überprüfen der 3NF
7.29
Was sind Views
Vom ERM zum
Datenbankschema
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
• 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
zusammengestellt werden.
7.30
Eigenschaften von Views
Vom ERM zum
Datenbankschema
Pierre Fierz
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
ERD und
Datenbankschema
Überprüfen der 3NF
erlaubt wie auf einer Basisrelation.
• Update-Operationen auf Views müssenin
Update-Operationen der zugrundeliegenden
Basisrelationen übersetzt werden.
• Die Übersetung ist jedoch nicht immer eindeutig möglich.
7.31
Vom ERM zum
Datenbankschema
Kreieren einer View
• Views werden in SQL mit dem CREATE VIEW Befehl
Pierre Fierz
definiert.
SQL Syntax (CREATE VIEW)
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
<view-definition> ::= CREATE VIEW <view-name>
(Constraint)
[(<attribut-name> [,<attribut-name>]...)]Views
AS
ERD und
Datenbankschema
<selection>
[WITH CHECK OPTION]
Überprüfen der 3NF
• <selection> ist ein beliebiges Select-Statement.
• Eine View definiert, als Resultat eines SELECT Befehls,
wieder eine Relation.
• Vom Standpunkt des Benutzers gibt es keinen
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
• Eine View kann mit dem DROP VIEW Befehl wieder
gelöscht werden.
SQL Syntax (DROP VIEW)
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
<delete-view> ::= DROP VIEW <view-name>
[RESTRICT | CASCADE]
ERD und
Datenbankschema
Überprüfen der 3NF
• Wird RESTRICT angegeben, wird die View nur gelöscht,
falls sie in keinen weiteren Definitionen (Views,
Procedures, Triggers usw.) verwendet wird.
• Wird CASCADE angegeben, werden alle Datenbank
Objekte in denen die View verwendet wird kaskadiert
gelöscht.
• RESTRICT ist der Default
7.33
Vom ERM zum
Datenbankschema
Beispiele
Pierre Fierz
Kreieren einer View
• Die folgende View zeigt pro Abteilung die gesammten
Arbeitsstunden, die an Projekten geleistet werden.
Datendefinitionssprache
(SQL/DDL)
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
ERD und
Datenbankschema
Überprüfen der 3NF
SELECT * FROM ProjektStunden
• Dies ergibt die folgende Relation
aNr
1
2
3
4
5
AName
A
B
C
D
E
Stunden
181
122
97.5
130
29
7.34
Relationale Operationen auf Views
Vom ERM zum
Datenbankschema
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
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
Mitarbeitern und die Projekte an denen Sie arbeiten.
Mitarbeiter Projekt View
DROP VIEW MitarbeiterProjekt;
CREATE VIEW MitarbeiterProjekt AS
SELECT *
FROM
Mitarbeiter natural join MitProj
natural join Projekt;
7.35
Vom ERM zum
Datenbankschema
Relationale Operationen auf Views 2
Pierre Fierz
• Nun selektieren wir mit Hilfe der View MitarbeiterProjekt
alle Mitarbeiter die zwischen 20 und 30 Stunden an
Projekten arbeiten.
SELECT mNr, Name, sum(WochenStunden) AS Stunden
FROM MitarbeiterProjekt
GROUP BY mNr, Name
HAVING Stunden > 20 and Stunden < 30;
Name
Meier
Glauser
Eicher
Ackermann
Integritätsbedingungen
(Constraint)
Views
Selektieren auf einer View
mNr
8
10
11
23
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
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)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
• 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.
• Dies ist nicht möglich, da der Ort nur einmal physisch
gespeichert ist.
• 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 2
Vom ERM zum
Datenbankschema
Pierre Fierz
• Die folgende unvollständige Liste gibt an, wenn eine View
nicht updatable ist.
1
2
3
4
5
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
7.38
Update Operationen auf Views 3
Vom ERM zum
Datenbankschema
Pierre Fierz
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.
7.39
With Check Option
Vom ERM zum
Datenbankschema
Pierre Fierz
• Die With Check Option Klausel macht nur einen Sinn,
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.40
Sichten auf Daten
Vom ERM zum
Datenbankschema
Pierre Fierz
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.
• Jeder Benutzer kann mit Hilfe von Views genau auf die
Informationen zugreifen, die für ihn relevant sind und
andere Informationen “ausblenden”.
7.41
Vereinfachen von Abfragen
Vom ERM zum
Datenbankschema
Pierre Fierz
• Views erlauben Abfragen zu vereinfachen.
• Komplizierte und häufig verwendete Join-Operationen
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
-- 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.42
Datenschutz
Vom ERM zum
Datenbankschema
Pierre Fierz
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.
Überprüfen der 3NF
• Solche Daten können auch nicht verändert werden.
• Mit Views ist es also möglich, Datenschutz auf Feld sowie
auf Tupelebene einzuführen.
7.43
Logische Datenunabhängigkeit
Vom ERM zum
Datenbankschema
Pierre Fierz
• Zu einem Teil können die Applikationen gegenüber
Ä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.
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
Neues Schema
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
);
7.44
Logische Datenunabhängigkeit 3
Vom ERM zum
Datenbankschema
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)
Integritätsbedingungen
(Constraint)
Views
Neue View
CREATE VIEW Mitarbeiter
AS
SELECT *
FROM
MitarbeiterNeu natural join OrtTabelle
ERD und
Datenbankschema
Überprüfen der 3NF
• Alle Programme, die Lieferantendaten nur lesen,
funktionieren jetzt unverändert weiter.
• Leider müssen Programme, die Mitarbeiterdaten
schreiben angepasst werden, da die View Mitarbeiter im
Allgemeinen nicht verändert werden kann.
7.45
Outline
Vom ERM zum
Datenbankschema
Pierre Fierz
1 Datendefinitionssprache (SQL/DDL)
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
2 Integritätsbedingungen (Constraint)
ERD und
Datenbankschema
Überprüfen der 3NF
3 Views
4 ERD und Datenbankschema
5 Überprüfen der 3NF
7.46
Einleitung
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
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
Entitätsmengen und Attributte
Vom ERM zum
Datenbankschema
Pierre Fierz
• 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ät Mitarbeiter
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
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,
)
7.49
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.
• 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
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 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.50
Entitätsmengen und Attributte (4)
Vom ERM zum
Datenbankschema
Pierre Fierz
• 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
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
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
• Achtung: Kommerzielle Systeme unterstützen keine
Assertions.
• Der obige Test muss entweder mit Triggers oder in der
Applikation durchgeführt werden.
7.51
Schwache Entitätsmengen
Vom ERM zum
Datenbankschema
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
• Für schwache Entitätsmengen wird ebenfalls eine neue
Tabelle angelegt.
• 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.
7.52
Schwache Entitätsmengen (2)
Vom ERM zum
Datenbankschema
Pierre Fierz
Angehöriger eines Mitarbeiters
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
CREATE TABLE Angehöriger (
mNr
INTEGER REFERENCES Mitarbeiter(mNr),
Vorname VARCHAR(30),
-- ... (weitere Attribute)
PRIMARY KEY (mNr, Vorname)
)
7.53
Generalisierung und Spezialisierung
Vom ERM zum
Datenbankschema
Pierre Fierz
• 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.
• 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.
• 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.54
Generalisierung und Spezialisierung (2)
Vom ERM zum
Datenbankschema
Pierre Fierz
Vertikale Darstellung
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
CREATE TABLE
pNr
Titel
);
CREATE TABLE
pNr
Publikation (
INTEGER PRIMARY KEY,
VARCHAR(30) NOT NULL
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
Generalisierung und Spezialisierung (3)
Vom ERM zum
Datenbankschema
Pierre Fierz
• In dieser Darstellung ist nur der Fall
{incomplete, overlapping} abgedeckt.
• Für die anderen Fälle braucht es Assertions:
Disjoint
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
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
7.56
Generalisierung und Spezialisierung (4)
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
);
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
Vom ERM zum
Datenbankschema
Pierre Fierz
• Für Beziehungen gibt es im wesentlichen zwei Fälle: 1:M
und M:M
• Zusätzlich können die Kardinalitäten noch konditionell
sein.
1:M und M:M Beziehungen
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
7.58
Beziehungsmengen 1:M
Vom ERM zum
Datenbankschema
Pierre Fierz
• In diesem Fall kann die Beziehung durch einen
Fremdschlüssel realisiert werden.
• Der Fremdschlüssel wird in der Tabelle auf der M-Seite
eingefügt.
Mitarbeiter Abteilung
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
CREATE TABLE Abteilung (
aNr
INTEGER PRIMARY KEY,
.
.
);
ERD und
Datenbankschema
Überprüfen der 3NF
CREATE TABLE Mitarbeiter (
mNr
INTEGER PRIMARY KEY,
aNr
INTEGER NOT NULL REFERENCES Abteilung(aNr),
.
.
);
• 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
Beziehungsmengen M:M
Vom ERM zum
Datenbankschema
Pierre Fierz
• Für eine M:M Beziehung muss eine neue Tabelle
eingeführt werden.
• Die Tabelle enthält zwei Fremdschlüssel, die zusammen
den Primärschlüssel bilden.
Mitarbeiter und Projekte
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
CREATE TABLE Mitarbeiter (
mNr
INTEGER PRIMARY KEY,
...
);
ERD und
Datenbankschema
Überprüfen der 3NF
CREATE TABLE Projekt (
pNr
INTEGER PRIMARY KEY,
...
);
CREATE TABLE MitProj (
mNr
INTEGER REFERENCES Mitarbeiter(mNr),
pNr
INTEGER REFERENCES Projekt(pNr),
PRIMARY KEY (mNr, pNr)
);
• 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)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
CREATE TABLE Abteilung (
aNr
INTEGER PRIMARY KEY,
mNr
INTEGER UNIQUE
REFERENCES Mitarbeiter(mNr),
leitetSeit
DATE,
...
);
CREATE TABLE Mitarbeiter (
mNr
INTEGER PRIMARY KEY,
...
);
7.61
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)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
CREATE TABLE Mitarbeiter (
mNr
INTEGER PRIMARY KEY,
...
);
CREATE TABLE Projekt (
pNr
INTEGER PRIMARY KEY,
...
);
CREATE TABLE MitProj (
mNr
INTEGER REFERENCES Mitarbeiter(mNr),
pNr
INTEGER REFERENCES Projekt(pNr),
wochenstunden FLOAT NOT NULL,
PRIMARY KEY (mNr, pNr)
);
7.62
Outline
Vom ERM zum
Datenbankschema
Pierre Fierz
1 Datendefinitionssprache (SQL/DDL)
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
2 Integritätsbedingungen (Constraint)
ERD und
Datenbankschema
Überprüfen der 3NF
3 Views
4 ERD und Datenbankschema
5 Überprüfen der 3NF
7.63
Lokale Betrachtungen
Vom ERM zum
Datenbankschema
Pierre Fierz
• Die Transformation des ERM in ein Datenbankschema
erfolgt im Prinzip nach dem Syntheseverfahren.
• Mit den folgenden Annahmen entsteht ein
Datenbankschema in dritter Normalform:
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
• Alle Attribute einer Entität sind voll vom Entitätsschlüssel
Views
funktional abhängig.
• In einer Entität gibt es keine transitiven funktionalen
Abhängigkeiten.
ERD und
Datenbankschema
Ü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
werden.
• Die Abhängigkeit ist im ERM als Integritätsbedingung
formuliert.
• In diesem Fall muss diese Abhängigkeit im
Datenbankschema nach den Regeln der Normalisierung
eliminiert werden.
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
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Zusätzlich sei die folgende Integritätsbedingung im ERM
angegeben:
Ü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)
);
CREATE TABLE Ort(
oNr
INTEGER PRIMARY KEY,
Ort
VARCHAR(30) NOT NULL UNIQUE,
Distanz FLOAT NOT NULL
);
7.65
Globale Betrachtungen
Vom ERM zum
Datenbankschema
Pierre Fierz
• 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
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
7.66
Globale Betrachtungen (2)
Vom ERM zum
Datenbankschema
Pierre Fierz
• Aus dem vorigen ERM entsteht nun das folgende
Datenbankschema:
Datenbankschema Mitarbeiter, Maschine und Produkt
CREATE TABLE Mitarbeiter (
mNr
INTEGER PRIMARY KEY,
...
);
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
CREATE TABLE Produkt (
prNr
INTEGER PRIMARY KEY,
maNr
INTEGER REFERENCES Maschine(maNr)
mNr
INTEGER REFERENCES Mitarbeiter(mNr)
...
);
CREATE TABLE Maschine (
maNr
INTEGER PRIMARY KEY,
mNr
INTEGER REFERENCES Mitarbeiter(mNr)
...
);
7.67
Globale Betrachtungen (3)
Vom ERM zum
Datenbankschema
Pierre Fierz
• In unserem Beispiel verletzt die Tabelle Produkt die 3NF
• Es gilt die FD maNr → mNr und somit ist mNr von prNr
transitiv abhängig.
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
• Die FD ist schon in der Tabelle Maschine festgehalten.
Views
• In beiden Tabellen hat die FD die semantische Bedeutung
ERD und
Datenbankschema
“Maschine wird von einem Mitarbeiter bedient”
Überprüfen der 3NF
• In diesem Fall können wir mNr aus der Tabelle Produkt
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.
• Zum Beispiel könnte mit mNr der Mitarbeiter bezeichnet
sein, der das Produkt verkauft.
7.68
Globale Betrachtungen (4)
Vom ERM zum
Datenbankschema
Pierre Fierz
• 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).
Datendefinitionssprache
(SQL/DDL)
Integritätsbedingungen
(Constraint)
Views
ERD und
Datenbankschema
Überprüfen der 3NF
• Damit die 3NF verletzt ist, muss in beiden Tabellen eine
entsprechende FD existieren (maNr → mNr ).
• Beide FDs müssen denselben semantischen Sachverhalt
darstellen (“Maschine wird von einem Mitarbeiter bedient”)
7.69
Betrachtungen zur 4NF
Vom ERM zum
Datenbankschema
Pierre Fierz
Datendefinitionssprache
(SQL/DDL)
• 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.
• Alle anderen Tabellen sollten keine MVDs mehr enthalten,
falls das ERM richtig aufgebaut ist.
7.70