UPDATE OPERATIONS ON RELATIONS

There are three basic update operations on relations. They are ;

1.Insert Operation

2.Delete Operation

3.Modify Operation

Insert operation is used to insert a new tuple or tuples in a relation.Delete operation is used to delete tuples. Modify operation is used to change the value of some attributes. The integrity constraints specified on the relational database schema should not be violated whenever update operations are applied.

Insert Operation

The insert operation provides a list of attributes values for a new tuple t that is to be inserted into a relation R. Insert can violate any of the following constraints.

1. Domain constraints can be violated if an attribute value is given that does not appear in the corresponding domain.

2. Key constraints can be violated if a key value in the new tuple t already exists in another tuple in the relation r(R).

3. Entity integrity can be violated if the primary key of the new tuple t is null.

4. Referential integrity can be violated if the value of any foreign key in t refers to a tuple that does not exist in the referenced relation.

Delete Operation

The delete operation can violate only referential integrity, if the tuple being deleted is referenced by the foreign keys from other tuples in the database. To specify deletion, a condition on the attributes of the relation selects the tuple to be deleted.

Three options are available if a deletion operations causes a violation.

1. Reject the deletion.

2. The second option is to attempt to cascade or propagate the deletion by deleting tuples that reference the tuple that is being deleted.

3. A third option is to modify the referencing attribute values that cause the violation; each such values is either set to null or changed to reference another valid tuple. If referencing attribute that causes a violation is part of the primary key, it cannot be set to null; otherwise, it would violate entity integrity.

Modify Operation

The modify operation is used to change the values of one or more attributes in a tuple or tuples of same relation R. It is necessary to specify a condition on the attributes of the relation R to select the tuple or tuples to be modified.

Assertions and Triggers

Assertion

An assertion is a predicate expressing a condition we wish the database to always satisfy.

Domain constraints, functional dependency and referential integrity are special forms of assertion.

Triggers

A trigger is a special type of stored procedure that's automatically invoked when the user try to modify data that it's designed to protect.