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, 16 (system presentations, handing out of certificates)

--> Results of course evaluation


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

  1. As project preparation and to refresh the relevant material two short reports have to be written concerning the Java/SQL API JDBC and the SQL assertion statement.
  2. Afterwards the syntax and semantic analysis of assertion statements have to be implemented. Although Postgres (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.
  3. The system implemented in phase 2 now is extended to generate "suitable" Postgres 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.
  4. 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. Second, a statement for dropping assertions completes the system.