Datenbanksysteme für FÜ SS 2000Seite 8 - 1

WorzykFH Anhalt

Datenintegrität

• Referentielle Integrität

• create table

• Intgegritätsregeln

Datenbanksysteme für FÜ SS 2000Seite 8 - 2

WorzykFH Anhalt

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.

Datenbanksysteme für FÜ SS 2000Seite 8 - 3

WorzykFH Anhalt

Beispiel

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.

Matr Name Adr Stg. Sem98001 J utta WH1 Inf 298002 Emil WH1 FÜ 498003 Fritz WH4 Inf 497001 Anna WH5 FÜ 297002 Else WH2 IM 498004 Hans WH4 IM 2

Stg. RegelInf 9FÜ 8IM 10

Datenbanksysteme für FÜ SS 2000Seite 8 - 4

WorzykFH Anhalt

Fremdschlüssel

Ein Attribut welches in einer anderen Tabele das Schlüsselattribut ist, wird Fremdschlüssel genannt. Das gleiche gilt für eine Menge von Attributen.

Datenbanksysteme für FÜ SS 2000Seite 8 - 5

WorzykFH Anhalt

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.

Datenbanksysteme für FÜ SS 2000Seite 8 - 6

WorzykFH Anhalt

Beispiel

Die Matrikel-Nr ist in der „Schlüsel-Tabelle“ ein

„Nicht-Schlüssel“ Element

Matr Name Adr Stg. Sem98001 J utta WH1 Inf 298002 Emil WH1 FÜ 498003 Fritz WH4 Inf 497001 Anna WH5 FÜ 297002 Else WH2 IM 498004 Hans WH4 IM 2

RaumNr

Bezeichnung Matr

08-012 MultimediaLab

98001

09-012 Audio Lab 9800109-012 Audio Lab 9800209-116 Datenbank

Lab98003

Datenbanksysteme für FÜ SS 2000Seite 8 - 7

WorzykFH Anhalt

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 alle Schlüssel abgegeben hat, die ihm Zugang zu Sonderräumen ermöglichen.

Datenbanksysteme für FÜ SS 2000Seite 8 - 8

WorzykFH Anhalt

Beispiel

Der Student Hans ist exmatrikuliert worden. In der „Noten-Tabelle“ wird seine Matikel-Nr durch „null“ ersetzt.

Matr Name Adr Stg. Sem98001 J utta WH1 Inf 298002 Emil WH1 FÜ 498003 Fritz WH4 Inf 497001 Anna WH5 FÜ 297002 Else WH2 IM 4

Matr Vor-lesung

Stg Note

98001 Math Inf 1.398002 Prog FÜ 1.798003 DatOrg Inf 2.097001 Math FÜ 4.097002 Prog IM 2.3null DBS1 Inf 4.7null DBS1 IM 2.098001 Prog Inf 2.7

Datenbanksysteme für FÜ SS 2000Seite 8 - 9

WorzykFH Anhalt

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.

Datenbanksysteme für FÜ SS 2000Seite 8 - 10

WorzykFH Anhalt

Beispiel

Zu jedem Studenten gibt es mindestens eine

Adresse.

Matr Name Stg. Sem98001 J utta Inf 298002 Emil FÜ 498003 Fritz Inf 497001 Anna FÜ 297002 Else IM 498004 Hans IM 2

Matr Nr Strasse Ort98001 1 Bernburger Str 57/216 06366 Köthen98001 2 Bahnhofstraße 1 39104 Magdeburg98002 1 Magdeburger Str 1 06366 Köthen98003 1 Bernburger Str 57/315 06366 Köthen98004 1 Hafenstraße 2 06385 Aken97001 1 Magdeburger Str 3 06366 Köthen97001 2 Waldstraße 2 30163 Hannover97002 1 Magdeburger Str 2 39240 Calbe98004 1 Bernburger Str 57/216 06366 Köthen

Datenbanksysteme für FÜ SS 2000Seite 8 - 11

WorzykFH Anhalt

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.

Datenbanksysteme für FÜ SS 2000Seite 8 - 12

WorzykFH Anhalt

create tableCREATE TABLE command ::=

CREATE TABLE

schema.

table

( column datatype

DEFAULT expr

)

table constraint

,

VollständigeBeschreibung in: Oracle8 SQL Reference Release 8.0

Datenbanksysteme für FÜ SS 2000Seite 8 - 13

WorzykFH Anhalt

Tabellen-Integritätsregelntable_constraint::=

)

)

CONSTRAINT constraint

UNIQUEPRIMARY KEY

| ( column ,

REFERENCES table

ON DELETE

CASCADE

schema.

FOREIGN

KEY| ( column

,

, ( column )

CHECK condition

Datenbanksysteme für FÜ SS 2000Seite 8 - 14

WorzykFH Anhalt

Anlegen von Tabellen

CREATE TABLE ta_studiengang

(Studiengang varchar2(20),

Regel number(2) NULL,

CONSTRAINT pk_studiengang PRIMARY KEY (Studiengang)

);

Datenbanksysteme für FÜ SS 2000Seite 8 - 15

WorzykFH Anhalt

Anlegen von TabellenCREATE 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)

);

Datenbanksysteme für FÜ SS 2000Seite 8 - 16

WorzykFH Anhalt

Anlegen von TabellenCREATE 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

);

Datenbanksysteme für FÜ SS 2000Seite 8 - 17

WorzykFH Anhalt

DatenmanipulationINSERT command ::=

INSERT INTO table VALUES ( column ),

Update command ::=

DELETE command ::=

VollständigeBeschreibung in:

Oacle 8.0: Server SQL Language ReferenceManual

DELETE

schema.

table

WHERE conditionFROM

column = exprSET

WHERE condition

UPDATE table

Datenbanksysteme für FÜ SS 2000Seite 8 - 18

WorzykFH Anhalt

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‘);

Datenbanksysteme für FÜ SS 2000Seite 8 - 19

WorzykFH Anhalt

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

Datenbanksysteme für FÜ SS 2000Seite 8 - 20

WorzykFH Anhalt

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

Datenbanksysteme für FÜ SS 2000Seite 8 - 21

WorzykFH Anhalt

Integritätsregeln bei DELETESQL> 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;

Es wurden keine Zeilen ausgewählt

Datenbanksysteme für FÜ SS 2000Seite 8 - 22

WorzykFH Anhalt

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

Datenbanksysteme für FÜ SS 2000Seite 8 - 23

WorzykFH Anhalt

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));

Datenbanksysteme für FÜ SS 2000Seite 8 - 24

WorzykFH Anhalt

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Ü SS 2000Seite 8 - 25

WorzykFH Anhalt

CHECKBeispiel

SQL> insert into ta_pruefung values('98001','Prog',3.7);

FEHLER in Zeile 1:

ORA-02290: Verstoß gegen CHECK-Regel (WORZYK.CHECK_NOTE)

Datenbanksysteme für FÜ SS 2000Seite 8 - 26

WorzykFH Anhalt

Zusammenfassung

• Referentielle Integrität sorgt für Datenkonsistenz

• create table kann Integritätsregeln definieren

• Beispiele für– insert– delete– update