Lab "Integrity Constraints"

Master Informatik, Master Wirtschaftsinformatik
Successful completion of all 4 phases ("Leistungsnachweis")
Regular Dates: 
On Fridays, 9:45-11:15; IZ 161
Last meeting on July, 25 (system presentations, handing out of certificates)

  Results of course evaluation



In this lab ("Praktikum") participants can learn how to implement SQL assertions with the PostgreSQL database system. In small teams a system is designed and implemented (using Java and JDBC) which translates assertions (CREATE ASSERTION ... CHECK (...)) into triggers and functions of the PostgreSQL database system.
This involves four working phases:

As project preparation a database for a spatial data set of Braunschweig (OpenStreetMap-data) has to be designed. All assertions and tests of the following phases will rely on this database.


Afterwards the syntax and semantic analysis of assertion statements have to be implemented. Although PostgreSQL (like many other DBMSs) does not support assertions it is possible to delegate a large part of this work to the database system. In order to achieve this, the relevant formulas from the assertion statements have to be transformed into select statements. The error messages which might be raised by these SQL statements have to be trapped and an appropriate assertion error message has to be thrown.

The system implemented in phase 2 now is extended to generate "suitable" PostgreSQL triggers and functions for syntactically and semantically correct assertions. The triggers have to fire when database changes have occurred that might violate the relevant  assertion, and the functions have to verify that the associated formula really does not apply anymore in the new database state. If this is true, then the activating data modification (insert, update, delete) has to be reset.

In the final phase, the system realized so far is completed by two functionalities: First, a statement is implemented allowing to check whether a new assertion is compatible with alreday existing data. And in the case that the assertion contradicts the current database state, the affected data should be displayed graphically. Second, a statement for dropping assertions completes the system. Finally, every team presents its implemented system.