Technische Universitt Berlin Department of Geoinformation Science GeoDatabases
Technische Universität Berlin Department of Geoinformation Science Geo-Databases: lecture 6 Data Integrity Prof. Dr. Thomas H. Kolbe Institute for Geodesy and Geoinformation Science Technische Universität Berlin Credits: This material is mostly an english translation of the course module no. 8 (‘Geo-Datenbanksysteme‘) of the open e-content platform www. geoinformation. net. 12. 10. 2006
Data Integrity Department of Geoinformation Science 2 T. H. Kolbe – Geo-Databases: lecture 6 11/12/2021
Motivation Department of Geoinformation Science “Invalid“ states must be avoided: A student is assigned the mark -6 (in Germany from 1 to 6). A lecture is assigned a nonexistent lecturer. A lecturer is neither assigned a lecture nor is he listed in the table “sabbatical semester“. real world‘ mini-world‘ Integrity constraints describe valid states of the system. Their compliance is controlled by the DBMS. 3 T. H. Kolbe – Geo-Databases: lecture 6 11/12/2021
Data Integrity – Methods presented so far Department of Geoinformation Science Already mentioned methods for the definition of integrity constraints are: Specification of the value domain for each column: ZIP NUMERIC(5, 0) A postal code (ZIP number) is allowed a maximum length of 5 digits (in Germany). Prohibition of NULL values: Name VARCHAR(30) NOT NULL Guarantees that no tuple will have a NULL entry in “Name“ (or vice-versa: every tuple must have a “Name“ value different from NULL) Primary key: Guarantees that there are no two tuples with identical key attributes No tuple is allowed the NULL value in a primary key attribute 4 T. H. Kolbe – Geo-Databases: lecture 6 11/12/2021
Referential Integrity - Motivation Department of Geoinformation Science The constraint “Every lecture is held by a lecturer. “ is formally soecified wrt. the database as follows : For each tuple within Vorlesungen (lectures) there is a tuple within professors, such that Vorlesungen. Pers. Nr = Professoren. Pers. Nr In general: referential integrity foreign key integrity violation! Referential integrity cannot be guaranteed by the previously introduced integrity constraints. 5 T. H. Kolbe – Geo-Databases: lecture 6 11/12/2021
Referential integrity in SQL (1) Department of Geoinformation Science We need language constructs that can be used to introduce primary/foreign key relations to the system. CREATE TABLE Professoren (Pers. Nr INTEGER PRIMARY KEY, . . . ); CREATE TABLE Vorlesungen (. . . , Pers. Nr INTEGER, FOREIGN KEY(Pers. Nr) REFERENCES Professoren(Pers. Nr)); When to check? At the end of a data manipulation operation (default) At the end of a transaction Principle: 6 The database is in a state of referential integrity before the manipulation Changes are only applied, if they are “valid“ The database is in a state of referential integrity after the manipulation T. H. Kolbe – Geo-Databases: lecture 6 11/12/2021
Referential integrity in SQL (2) Department of Geoinformation Science Default strategy: Rejection of invalid changes 2 nd strategy: cascading changes CREATE TABLE Professoren (Pers. Nr INTEGER PRIMARY KEY, . . . ); CREATE TABLE Vorlesungen (. . . , Pers. Nr INTEGER, FOREIGN KEY(Pers. Nr) REFERENCES Professoren (Pers. Nr) ON DELETE CASCADE); foreign key foreignkey foreign …analogous ON UPDATE CASCADE for cascading UPDATE 7 T. H. Kolbe – Geo-Databases: lecture 6 11/12/2021
Referential integrity in SQL (3) Department of Geoinformation Science 3. strategy: insertion of a default value Similar syntax to ON DELETE / UPDATE CASCADE: ON DELETE / UPDATE SET NULL: …on deletion / updating the foreign key is set to NULL ON DELETE / UPDATE SET DEFAULT: …on deletion / updating the foreign key is set to a default value 8 T. H. Kolbe – Geo-Databases: lecture 6 11/12/2021
CHECK Clause Department of Geoinformation Science Allows additional constraints on the level of attributes and tables. Example: Graduates must have studied for at least 8 semesters CREATE TABLE Graduates (. . . , Semester INTEGER CHECK Semester >= 8) CHECK conditions can be as complex as the WHERE conditions: Only Professors are allowed to hold an exam. CREATE TABLE Exams ( Name VARCHAR(30) NOT NULL, . . . CHECK (Name IN (SELECT Name FROM Professoren)) Attention! The CHECK constraint is carried out only in case of data manipulations of the respective table (no checking if “Professoren“ is changed)! 9 T. H. Kolbe – Geo-Databases: lecture 6 11/12/2021
Outlook Department of Geoinformation Science If an integrity constraint is not only to be checked on the change of a single table, it has to be formulated on the database schema level. In order to ensure integrity propagation of changes might become necessary. Assertions CREATE ASSERTION <name> CHECK <condition> like in the WHERE clause Check is performed on changes to any of the tables specified in <condition> Resolving of change propagations to ensure integrity (Trigger) User-defined procedures that are launched automatically if a certain condition is fulfilled Since SQL: 1999 standardised For more details, see literature 10 T. H. Kolbe – Geo-Databases: lecture 6 11/12/2021
References Department of Geoinformation Science Overview: Hector Garcia-Molina, Jeffrey D. Ullman, Database Systems: The Complete Book, Prentice Hall, 2002 Alfons Kemper, André Eickler, Datenbanksysteme - Eine Einführung, Oldenbourg Verlag, München, 1996 Jim Melton, Alan R. Simon, SQL 1999: Understanding Relational Language Components, Morgan Kaufmann Publishers, 2001 Gottfried Vossen, Datenbankmodelle; Datenbanksprachen und Datenbankmanagement-Systeme, Oldenbourg Verlag, München, 1999 11 T. H. Kolbe – Geo-Databases: lecture 6 11/12/2021
- Slides: 11