8.5. General approach to the organization of representations, integrity constraints and access control

We have combined these three seemingly not very close topics, because Ingres takes a unified approach based on modifying SQL statements to solve relevant problems. Let's start with the submissions. As in System R (more precisely, in the SQL language), a database view is some named query with the named fields of the resulting relation.

For example, the operator

  DEFINE VIEW GROUP310
     (STUD_NUMBER = S.STUD_NUMBER,
      STUD_NAME = S.STUD_NAME,
      STUD_STATUS = S.STUD_STATUS)
 WHERE (S.GROUP_NUMBER = 310) 

determines the relationship being represented, including student ID numbers and the names of students from group 310.

Suppose that we now want to find underperforming students with respect to GROUP310:

  RANGE OF G310 IS GROUP310
 RETRIEVE (G310.STUD_NAME)
 WHERE (G310.STUD_STATUS = "NO") 

Then after modification this query will look like this:

  RETRIEVE (STUD_NUMBER = S.STUD_NUMBER, STUD_NAME =
           S.STUD_NAME, STUD_STATUS = S.STUD_STATUS)
 WHERE (S.GROUP_NUMBER = 310 AND
        S.STUD_STATUS = "NO") 

On the same principles, data access control and database integrity control are built. For example, the access restriction to the STUDENT relation can be defined as follows:

  DEFINE PERMIT RETRIEVE, REPLACE
        ON S
        TO PETROV
        AT TTA5
        FROM 9:00 TO 17:50
        ON MON TO FRI
        WHERE (S.GROUP_NUMBER = 310) 

This means that Petrov is allowed to read and modify the attitude of the STUDENTS from the TTA5 terminal at a time from 9 to 15:00 on workdays of the week, and only those tuples that satisfy the formulated condition. When compiling any QUEL operator over the STUDENT relation, this operator will be modified so that it is executed when at least one of the access restrictions is met.

Similarly, if an integrity constraint is defined for the STUDENT relationship.

  DEFINE INTEGRITY
     ON S
     WHERE (S.STUD_STIP <150,000) 

then the condition of any integrity constraints defined for this relation will be added through AND to the condition of any operator for changing the tuples of the relation of the STUDENT.

In conclusion of this lecture, we note that, of course, Ingres supports the mechanism of parallel transactions with appropriate access synchronization and logging of database changes. However, we are not aware of any particularities of the mechanisms used. We will dwell on the features of optimization of QUEL operators in a lecture devoted to optimizations in database languages.