Common Relational Database Operations

We will concentrate on the three basic operations that can change the states of relations in the database: Insert, Delete, and Update. Insert is used to insert one or more new tuples in a relation, Delete is used to delete tuples, and Update is used to change the values of some attributes in existing tuples.

Whenever these operations are applied, the integrity constraints specified on the relational database schema should not be violated. So we will also discuss the types of constraints that may be violated by each of these operations along with the types of actions that may be taken if an operation causes a violation.

We will be using the database state illustrated below:

EMPLOYEE

Name	Ssn	Bdate	Salary	Super_Ssn	Dept_Num
John Smith	333445555	1968-05-22	45,000	NULL	3
Emily Taylor	987654321	1972-09-01	30,000	333445555	3
Adam Kovac	666884444	1969-04-09	55,000	333445555	3
Kevin Jaimes	888665555	1979-09-22	20,000	203948506	2

DEPARTMENT

D_Name	D_No	Manager_Ssn
Administration	3	333445555
Research	2	678884823

The insert operation

The insert operation provides a list of attribute values for a new tuple t that is to be inserted into a relation R.

The insert operation can violate any of the four types of constraints:

• Domain constraints can be violated if an attribute value is given that does not appear in the corresponding domain or is not of the appropriate data type.
• Key constraints can be violated if the primary key value in the new tuple t already exists in another tuple in the relation.
• Entity integrity can be violated if any part of the primary key of the new tuple t is NULL.
• 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.

The following slides include examples of the insert operation: