Datenintegrität

Datenintegrität
• Referentielle Integrität
• create table
• Intgegritätsregeln
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 1
Referenzielle Integrität
Stehen zwei Relationen so miteinander in Beziehung, daß ein
(Nicht-)Schlüssel-Attribut der einen Relation das
Schlüssel-Attribut der anderen Relation ist, so muß das
relationale Datenbanksystem dafür sorgen, daß die
Beziehung zwischen den Relationen keinen undefinierten
Zustand annimmt.
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 2
Beispiel
M atr
98001
98002
98003
97001
97002
98004
Name
Jutta
Emil
Fritz
Anna
Else
Hans
Adr
WH1
WH1
WH4
WH5
WH2
WH4
Stg.
Inf
FÜ
Inf
FÜ
IM
IM
Sem
2
4
4
2
4
2
Stg.
Inf
FÜ
IM
Regel
9
8
10
Der Studiengang ist in der Studenten-Tabelle ein „Nicht-Schlüsssel“ und in
„Regelstudienzeit-Tabelle“ ein Schlüssel.
Das DBMS muß dies Inhalte der Tabellen konsistent halten.
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 3
Fremdschlüssel
Ein Attribut einer Tabelle,
welches in einer anderen Tabelle das Schlüsselattribut ist,
wird Fremdschlüssel genannt. Das gleiche gilt für eine
Menge von Attributen.
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 4
Integritätsregeln
Die Zusammenhänge zwischen Tabelleninhalten können der
Datenbank bekannt gemacht werden.
Die Datenbank sorgt dann dafür, dass diese Regeln beim
Einfügen, Ändern und Löschen von Daten eingehalten
werden.
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 5
Beispiel
M atr
98001
98002
98003
97001
97002
98004
Name
Jutta
Emil
Fritz
Anna
Else
Hans
Adr
WH1
WH1
WH4
WH5
WH2
WH4
Stg.
Inf
FÜ
Inf
FÜ
IM
IM
Sem
2
4
4
2
4
2
Raum Bezeichnung M atr
Nr
08-012 M ultimedia
98001
Lab
09-012 Audio Lab
98001
09-012 Audio Lab
98002
09-116 Datenbank
98003
Lab
Die Matrikel-Nr ist in der „Schließkarten-Tabelle“ ein „Nicht-Schlüssel“ Element
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 6
Integritätsregeln bei DELETE
• Restricted Delete (Verbotenes Löschen)
Ein Student wird exmatrikuliert und sein Eintrag gelöscht.
Das ist nur möglich, wenn der Student die Schließkarten
abgegeben hat, die ihm Zugang zu Sonderräumen
ermöglichen.
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 7
Beispiel
M atr
98001
98002
98003
97001
97002
Name
Jutta
Emil
Fritz
Anna
Else
Adr
WH1
WH1
WH4
WH5
WH2
Stg.
Inf
FÜ
Inf
FÜ
IM
Sem
2
4
4
2
4
M atr
98001
98002
98003
97001
97002
null
null
98001
Vorlesung
M ath
Prog
DatOrg
M ath
Prog
DBS1
DBS1
Prog
Stg Note
Inf
FÜ
Inf
FÜ
IM
Inf
IM
Inf
1.3
1.7
2.0
4.0
2.3
4.7
2.0
2.7
Der Student Hans ist exmatrikuliert worden. In der „Noten-Tabelle“ wird seine
Matikel-Nr durch „null“ ersetzt.
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 8
Integritätsregeln bei DELETE
• Nullifies Delete (Nicht-definiert Löschen)
Ein Student wird exmatrikuliert und sein Eintrag gelöscht.
Die Einträge in seinen Noten werden auf null gesetzt und
bleiben so zu statistischen Zwecken erhalten.
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 9
Beispiel
M atr
98001
98002
98003
97001
97002
98004
Name
Jutta
Emil
Fritz
Anna
Else
Hans
Stg.
Inf
FÜ
Inf
FÜ
IM
IM
Sem
2
4
4
2
4
2
M atr
98001
98001
98002
98003
98004
97001
97001
97002
98004
Nr
1
2
1
1
1
1
2
1
1
Strasse
Bernburger Str 57/216
Bahnhofstraße 1
M agdeburger Str 1
Bernburger Str 57/315
Hafenstraße 2
M agdeburger Str 3
Waldstraße 2
M agdeburger Str 2
Bernburger Str 57/216
Ort
06366 Köthen
39104 M agdeburg
06366 Köthen
06366 Köthen
06385 Aken
06366 Köthen
30163 Hannover
39240 Calbe
06366 Köthen
Zu jedem Studenten gibt es mindestens eine Adresse.
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 10
Integritätsregeln bei DELETE
• Cascades Delete (Verschachteltes Löschen):
ein Student wird exmatrikuliert und sein Eintrag gelöscht.
Dann werden alle dazugehörenden Adressen gelöscht.
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 11
create table
CREATE TABLE command ::=
CREATE TABLE
table
schema.
,
(
)
column datatype
DEFAULT expr
table constraint
VollständigeBeschreibung in:
Oracle8 SQL Reference Release 8.0
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 12
Tabellen-Integritätsregeln
table_constraint::=
CONSTRAINT constraint
,
| ( column
UNIQUE
PRIMARY KEY
)
,
FOREIGN
KEY
| ( column
)
table
REFERENCES
schema.
,
( column )
ON DELETE
CASCADE
CHECK condition
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 13
Anlegen von Tabellen
CREATE TABLE ta_studiengang
(Studiengang varchar2(20),
Regel number(2) NULL,
CONSTRAINT pk_studiengang
PRIMARY KEY (Studiengang)
);
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 14
Anlegen von Tabellen
CREATE TABLE ta_student
(Matrikel char(5),
Name varchar2(10),
Adresse varchar2(10) null,
Studiengang varchar2(20) null,
Semester number(2) null,
CONSTRAINT pk_student PRIMARY KEY
(Matrikel),
CONSTRAINT fk_student FOREIGN KEY
(Studiengang) REFERENCES
ta_studiengang(Studiengang)
);
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 15
Anlegen von Tabellen
CREATE TABLE ta_pruefung
(Matrikel char(5),
Vorlesung varchar2(10),
Note number (2,1),
CONSTRAINT pk_pruefung PRIMARY KEY
(Matrikel, Vorlesung),
CONSTRAINT fk_pruefung FOREIGN KEY
(Matrikel) REFERENCES
ta_student(Matrikel)
ON DELETE CASCADE
);
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 16
Datenmanipulation
INSERT command ::=
INSERT INTO
table
VALUES
(
,
column
)
Update command ::=
UPDATE
table
SET
column = expr
WHERE condition
DELETE command ::=
DELETE
table
FROM
Worzyk
FH Anhalt
schema.
VollständigeBeschreibung in:
Oacle 8.0: Server SQL Language ReferenceManual
WHERE condition
Datenbanksysteme für FÜ WS 2004/2005
Seite 17
Integritätsregeln bei INSERT
CREATE TABLE ta_studiengang
(Studiengang varchar2(20), Regel number(2) NULL,
CONSTRAINT pk_studiengang PRIMARY KEY (Studiengang));
CREATE TABLE ta_student
(Matrikel char(5), Name varchar2(10),
Studiengang varchar2(20) null,
CONSTRAINT pk_student PRIMARY KEY (Matrikel),
CONSTRAINT fk_student FOREIGN KEY (Studiengang)
REFERENCES ta_studiengang(Studiengang));
INSERT INTO ta_studiengang VALUES ('Inf', 9);
INSERT INTO ta_student VALUES ('98002','Emil',,'Inf‘);
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 18
Integritätsregeln bei INSERT
SQL> insert into ta_studiengang
2 values ('Inf', 9);
SQL> insert into ta_student
2 values('98002','Emil','WH1','FÜ',2);
insert into ta_student
*
FEHLER in Zeile 1:
ORA-02291: Verstoß gegen Integritätsregel
(WORZYK.FK_STUDENT). Übergeordn.
Schlüssel nicht gefunden
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 19
Beispiel ohne Namenskonzept
SQL> create table t1(spalte1 number,
2 primary key (spalte1));
SQL> create table t2 (spalte1 char, spalte2 number,
2 foreign key (spalte2) references t1(spalte1));
SQL> insert into t2 values ('a', 1);
FEHLER in Zeile 1:
ORA-02291: Verstoß gegen Integritätsregel
(WORZYK.SYS_C004317). Übergeordn. Schlüssel nicht
gefunden
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 20
Integritätsregeln bei DELETE
Worzyk
FH Anhalt
SQL> insert into ta_student
2 values('98001','Jutta','WH1','Inf',2);
SQL> insert into ta_pruefung
2 values('98001','Math',1.3);
SQL> insert into ta_pruefung
2 values('98001','Prog',2.7);
SQL> select * from ta_pruefung;
MATRI VORLESUNG
NOTE
----- ---------- --------98001 Math
1,3
98001 Prog
2,7
SQL> delete from ta_student
2 where Matrikel = '98001';
SQL> select * from ta_pruefung;
Datenbanksysteme für FÜ WS 2004/2005
Es wurden keine Zeilen ausgewählt Seite 21
Integritätsregeln bei UPDATE
SQL> update ta_studiengang set studiengang = 'Informatik'
2 where studiengang = 'Inf';
FEHLER in Zeile 1:
ORA-02292: Verstoß gegen Integritätsregel
(WORZYK.FK_STUDENT). Untergeordneter Datensatz gefunden.
SQL> update ta_student set studiengang = 'Informatik'
2 where studiengang = 'Inf';
FEHLER in Zeile 1:
ORA-02291: Verstoß gegen Integritätsregel
(WORZYK.FK_STUDENT). Übergeordn. Schlüssel nicht
gefunden
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 22
Anelgen und Löschen von Tabellen
DROP TABLE ta_student;
DROP TABLE ta_studiengang;
CREATE TABLE ta_studiengang
Studiengang varchar2(20),
Regel number(2) NULL,
CONSTRAINT pk_studiengang PRIMARY KEY (Studiengang));
CREATE TABLE ta_student
(Matrikel char(5),
Studiengang varchar2(20) null,
CONSTRAINT pk_student PRIMARY KEY (Matrikel),
CONSTRAINT fk_student FOREIGN KEY (Studiengang)
REFERENCES ta_studiengang(Studiengang));
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 23
CHECK
CREATE TABLE ta_pruefung
(Matrikel char(5),
Vorlesung varchar2(10),
Note number (2,1),
CONSTRAINT pk_pruefung
PRIMARY KEY (Matrikel, Vorlesung),
CONSTRAINT fk_pruefung FOREIGN KEY (Matrikel)
REFERENCES ta_student(Matrikel)
ON DELETE CASCADE,
CONSTRAINT check_note CHECK
(note in (1.0, 1.3, 1.7, 2.0, 2.3, 2.7, 3.0))
);
Datenbanksysteme für FÜ WS 2004/2005
Worzyk
FH Anhalt
Seite 24
CHECK
Beispiel
SQL> insert into ta_pruefung
values('98001','Prog',3.7);
FEHLER in Zeile 1:
ORA-02290: Verstoß gegen CHECK-Regel
(WORZYK.CHECK_NOTE)
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 25
Zusammenfassung
• Referentielle Integrität sorgt für Datenkonsistenz
• create table kann Integritätsregeln definieren
• Beispiele für
– insert
– delete
– update
Worzyk
FH Anhalt
Datenbanksysteme für FÜ WS 2004/2005
Seite 26