7. Corporate integrity limitation

Providing relational data integrity (point 4.6.2) using primary and foreign keys is supplemented with corporate integrity limitations [2, 4, 6].

Corporate integrity limitation are represented by rules that determine how the client can operate server data.

Operating rules meant to be actions that depend on particularities of registration and business processes of the organization: adding, deletion of table rows and updating of existing rows. They reflect first of all particularity of interaction between the company and its off-site clients and structures that are superior and secondly the interaction between subjects within the organization. In most cases, external interactions are managed by legislation of different levels and contracts. All these particularities of register and business processes can not be hold within the bounds of relational model.

Corporate integrity limitations are determined on the stage of logical designing and are implemented during DB physical designing. They can be divided into three groups:

1. Support of uniqueness of candidate keys that are not used for establishing links between relations inside the DB.
2. Support of legitimate values area in the attributes of relations.
3. Support of relations corteges quantity limitations caused by legislation and (or) particularities of register processes within organization.
4. Support of cascading update and deletion of the information in DB relations.

All candidate keys are determined on the stage of relations normalization. In DB «Library» it is necessary to support uniqueness of candidate keys values that are not applied for relations links:

ReaderCardNumber (reader ticket number),

PasportCode (passport code) in relation Readers (readers);

ClockNumber (clock number),

PasportCode (passport code) in Librarians (librarians);

Name, IssueYear (name, date of publication) in Books (books);

Series, Number (series, number) in PasportData (passport data);

ReaderCode, PhoneNumber (reader’s code, telephone number) in Phones (telephones);

InventoryNumber (inventory number) in BookInventoryNumbers (books inventory numbers);

Name (books fund name) у BookFunds (books funds);

Name (telephone type name) in PhoneTypes (types of telephones);

Code (operation number) in BookGiveOutRecord (books give out register).

Code (operation number) in BookGiveOutRecord (books give out register).

BookCode, AuthorCode (book and author codes) in Сoаuthorship (co-authors).

It is necessary to determine the range of admissible values first of all for relations attributes that are the part of candidate keys and do not participate in links between DB relations. Fore example, analysis of above listed attributes that are the part of candidate keys and do not participate in links between DB relations demonstrates that there must not be NULL determinant. There is a list of obvious additional limitations. In attributes of INTEGER type – ReaderCardNumber (number of the reader card), ClockNumber (clock number), Number (passport number), InventoryNumber (inventory number) – values must be > 0. In the attributes of CHAR type – Name (title of the book), Series (passport series), PhoneNumber (reader’s telalphone number), Name (books fund name), Name (telephone type name) – there must be no empty rows or rows that contain spaces only.

For other non-key attributes of relations the definition of admissible values range can be also topical. This area depends on demand of users that are determined on the stage of DB logical designing. For example, if it is not necessary to indicate the place of work and position of the reader, there must be no NULL determinant, empty rows or rows that contain only spaces in the attributes Job (place of work) and Post (position).

As an example of the corteges quantity limitation support in relation BookGiveOutRecord (register of books give out) of «Library» DB we can examine determined quantity of books that can be given out to one reader. Another obvious limitation is a prohibition to give out several copies of the same book at one time. For example, if there is a rule that one reader can get not more then five books, then relation BookGiveOutRecord can contain only 5 corteges with similar values in the attribute ReaderCode (reader code) where values FactReturnDate (actual data of return) contain NULL determinant. In addition it is necessary to verify that values of attributes BookGiveOutRecord.InventoryCode (inventory number of a book) for mentioned corteges meet different values of BookGiveOutRecord.BookCode (books code).

The mechanisms of corporate integrity limitations support in architecture client-server DBMS can be placed either on the side of client or on the side of server (see item 1.3.3). We will examine only mechanisms that are implemented on the side of server: SQL-operators, saving procedure and triggers. For each specific case it is necessary to choose the simplest solution that provides corporate integrity limitations support.

Operators CREATE DOMAIN, CREATE INDEX and CREATE TABLE can be applied to support corporate integrity limitations. They give the possibility to provide uniqueness of candidate key values that are not used for establishing links between relations within DB and to determine relations attributes admissible values range.

Operator format

permits CHECK proposition to determine candidate key values area and to cancel NULL determinant, indicating NOT NULL during the domain creation. For the description of candidate key values area are used the same predicates and rules of logic expressions construction as for data sampling according to WHERE, SELECT operator proposition term:

Format of operator CREATE INDEX permits to include replication of key attribute values, indication phrase UNIQUE:

Operator CREATE TABLE provides the possibility to carry out the support of corporate integrity limitations in operators CREATE DOMAIN and CREATE INDEX:

The definition of column determines the column on the base of previously created domain, indicates the area of candidate key values determination and to cancel from it NULL determinant, putting NOT NULL:

Proposition COMPUTED [BY] (<Expression>) provides the possibility to compute the value of attribute. <Expression> must generate scalar value. For example, operator

generates three columns: Cost (price), Number (quantity), TotalCost (total cost). In the last column value is computed as the product of two previous values.

On the level of column constraint integrity limitations are represented in the following format:

You can indicated in <Column constraint description> that values in the column must be unique (proposition UNIQUE or PRIMERY KEY); you can indicate column values determination area (proposition CHECK); you have the possibility to organize cascading update, deletion of values and corteges of relation, indicating relation with attributes values from another table (proposition REFERENCES …).

The description of column constraint determination area differs from domain search condition for the possibility to use SELECT operator:

7.3.1. Definition and classification of saving procedures

Saving procedure is a module that is written by procedural language of DBMS and is saved in DB as metadata (that is data about data).

Saving procedure can be called from client’s application program. There are two kinds of saving procedures: procedures of sampling and procedures of operation [2, 4, 6].

Sampling procedure can generate more than one value. In the application program its name can be presented to the operator SELECT instead of table name.

Operation procedure can even not generate data at all and is used for implementation of any operations.

It is possible to pass parameters to saving procedures. They can return values of parameters, modified according to determined algorithms.

Advantages of using saving procedures are the following:

1. one procedure, that is located on the server can be used by several application programs of the client;
2. simplification of client’s application programs due to the transfer of some part of the code to the server;
3. when saving procedure is changed on the server, all procedures immediately become available for client’s application programs;
4. reduction of network traffic.

7.3.2. Generation of saving procedure

Saving procedure creation is used by operator

Input parameters are used to pass values from application program of the client to the procedure [10, 14, 15]. There is no sence to change input parameters in the procedure body: these modifications will be forgotten after the procedure work end. Output parameters are used to return back final values. Output parameter are placed in the procedure body and when its work comes to the end they are passed to the client’s application program. Both input and output parameters can be dropped if they are not necessary.

Procedure body has the following format:

7.3.3. Algorithmic language of triggers and saving procedures

7.3.3.1. Local variables

Local variables, if they are determined in the procedure exist only from the beginning of procedure execution till its end. Outside the procedure they are unknown and attempt of access will generate error. Local variables are used for saving temporary values.

Determination of local variables has the following format:

Standard types of Interbase DBMS are used.

7.3.3.2. Assignment operator

Operator of assignment is use to enter values to variables. Its format is:

Variable name = expression

where the form of expression can be represented by constants, variables, arithmetical and row values that can be used with nested functions, functions, determined by user, and also generators. For example:

Here row constant is assigned to the variable that has name XBuyer.

7.3.3.3. Operational brackets BEGIN … END

Operational brackets BEGIN … END, firstly limit procedure body and secondly can be used to determine the bounds of composite operator. Simple operator is a single authorized operation (see example above). Composite operator is a group of simple or composite operators that is put in operational brackets BEGIN … END.

7.3.3.4. Operator IF … THEN … ELSE

Operator IF … THEN … ELSE in general are recorded as:

If condition is authentic, then operator is carried out after proposition THEN, if no – operator is carried out after ELSE.

7.3.3.5. Operator SELECT

Operator SELECT is used in saving procedure to generate single row. It is supplemented with proposition:

It is dedicated to indicate variables or output parameters where must be recorded values that are returned by operator SELECT (those final values that are listed after proposition SELECT).

7.3.3.6. Operator SUSPEND

Operator SUSPEND stops execution of saving procedure and parameter values listed after proposition RETURNS are passed to application program where it was called from. For example, saving procedure FindAutorName returns in the output parameter InAutorName author name that meets code - InAutorName:

7.3.3.7. Operator FOR SELECT … DO

Operator FOR SELECT … DO has the following format:

FOR

    <operator SELECT>

DO

    <operator>

After proposition FOR operator SELECT returns corteges set. After that for each cortege is carried out operator or sequence of operators that is the next according to proposition DO. In other words this is a cycle by rows of the table that was generated by operator SELECT.

7.3.3.8. Operator WHILE … DO

To implement operator WHILE … DO the following condtruction is used

WHILE(<condition>)

DO <operator>;

It organizes looping execution of the operator or sequence of operators after proposition DO, till the moment when condition after WHILE becomes authentic.

7.3.3.9. Operator EXIT

Operator EXIT stops the procedure execution and passes control to the application program that made a call.

7.3.3.10. Operator EXECUTE PROCEDURE

Operator EXECUTE PROCEDURE looks like:

Thanks to this operator another saving procedure that is in DB can be run in any place of procedure body. After EXECUTE PROCEDURE its name is required. After that, if it is necessary, parameters that are passed to procedure are indicated. Proposition RETURNING_VALUES permits to get result of procedure execution that is called and to record them in variable.

7.3.3.11. Operator POST_EVENT

Operator POST_EVENT sends messages about some situation referred to the name of event from saving procedure to application client-program. It has format

POST_EVENT "  Event name"

7.3.3.12. Operator CREATE EXCEPTION

Operator CREATE EXCEPTION identifies exceptional situation with determined name and notification:

CREATE EXCEPTION name ' message'

For generation of exceptional situation the following syntax is used inside saving procedure:

EXCEPTION name

When exceptional situation arises, notification about error is returned to application program that is called and saving procedure execution stops. However, exceptional situation can be processed within saving procedure and after that saving procedure execution can be continued.

7.3.4. Support of data integrity corporate limitations

Support of data integrity corporate limitations by means of saving procedures is the most efficient instrument. It must be applied in case when with help of SQL-operators on the stage of DB structure designing you have no possibility to consider all corporative and legislative rules that influence particularities of organization registration. Saving procedures are applied in case of necessity to generate messages for user that describe exceptional situation that arises.

For example above we see the text of saving procedure that changes code of the author of the book in relation СoАuthorship. This procedure is necessary when the name of the author of the book is inserted with mistake. It contains everything that is necessary to check acceptability of this operation for «LIBRARY» DB. If the operation is not acceptable, after generation of the corresponding exceptional situation the process execution stops. It will be found that to keep data integrity during the changing of author’s code it is necessary to carry out some additional actions and verifications, then they must be executed within present saving procedure. As a result, any application program of the client that uses it, will keep equal data integrity limitations.

Trigger is a procedure that is automatically called by SQL-server during the update, deletion or supplement data to relation [10, 14, 15].

It is impossible to make a call directly from the program to trigger. It is also impossible to pass its incoming parameters and to receive from them outcome parameters. Triggers always implement an operation. They process the following events: adding new record, changing attribute value in already existing record or record deletion. Regarding the operation triggers can be divided into those that are implemented before the event and after. CREATE TRIGGER operator has the following format:

After CREATE TRIGGER proposition it necessary to indicate unique trigger name and then to determine name of the table for which trigger is generated in FOR. On default trigger is active (ACTIVE), but if we indicate INACTIVE, that means trigger will not process the event. Propositions BEFORE and AFTER determine when the trigger will be carried out – before or after event of deletion (DELETE), adding (INSERT) record or updating attributes values (UPDATE). Proposition POSITION gives the possibility to determine several triggers for each event. The execution order corresponds to their number.

Structure of trigger body:

[<local variables declaration>]

BEGIN

    <operator>

END

Procedural language that is studied in the chapter dedicated to saving procedures is used to determine the trigger body. It is supplemented with the possibility of access to old (OLD) and new (NEW) values that are updated within the column. It is necessary to underline that this possibility is not available for determination of saving procedures. Value OLD.Column_name permits to access value that was actual before possible modifications were made in the table, and value NEW.Column_name for content that is formed after possible changes were made. In case if values in the column were not updated, OLD.Column_name is equal to NEW.Column_name.

Triggers are an active part of corporate integrity limitation realization. For example, it can be an installation of unique values of the key columns by means of generator, statistics reservation in other tables and many other options. Difficulties that can arise during implementation of business rules by means of triggers include underdevelopment of code logic adjustment mechanisms that compose the trigger body.

Trigger can provide the execution of cascade impact in the child relation during updating, deletion of record in the parent relation. It is reasonable if such impacts can not be provided by mechanism of foreign keys. In such case designer has no need to care about program implementation of cascading update in the application program of client. Such approach reduces network traffic thanks to absence of necessity to pass additional information to the client’s application program for events processing that refer to data handling.

It is not recommended to provide execution of cascade impacts by means of mechanism of foreign keys and triggers at the same time. For implementation of automatic execution of cascading update and modification it is necessary first of all to delete foreign keys that block such changes in DB, second to determine triggers for corresponding tables.

For example for «LIBRARY» DB on the stage of relations generation was determined a foreign key that doesn’t permit to enter into the attribute СoAuthorship.AuthorCode (code of the author of a book) values that is absent in the attribute of relation BookAuthors.Code that organizes directory of authors.

To implement automatic execution of cascade deletion of record and changing author code by means of triggers, first of all it is necessary to delete determination of foreign key from relation СoАuthorship, and after that to determine triggers that will carry out cascading update of the information.

Trigger that carries out cascading update has the following form:

Trigger that carries out cascade deletion:

By means of triggers it is possible to log observations about DB information modification. Change log is a table where operations with whole DB and its separate tables are fixed. Systems with many users such log get the possibility to find the source of unauthentic or wrong data.

1. What components of relational data integrity providing you know?
2. What operations manage corporate integrity limitations?
3. What groups of corporate integrity limitations we can single out?
4. How to choose the «mechanism» of corporate integrity constraints?
5. What SQL-operators implement corporate integrity limitations?
6. What kinds of corporate integrity limitations can be implemented by SQL-operators?
7. What mutual features and differences have different kinds of saving procedures?
8. What advantages has implementation of saving procedures for realization of corporate integrity limitations?
9. What facilities of saving procedures algorithmic language you know?
10. When is it reasonable to apply saving procedures for corporate integrity limitations implementation?
11. What mutual features and differences have saving procedures and triggers?
12. What events can be processed by triggers?
13. What additional options have procedural language of triggers?
14. What particularities of using triggers to provide cascading update and data modification you know?