Modification of the Database

Modification of the Database

We have limited our attention until now to the extraction of information from the database. In this section, we address how to add, remove, or change information in the database.

We express database modifications by using the assignment operation. We make assignments to actual database relations by using the same notation as that described in Section 3.2.3 for assignment.

Deletion

We express a delete request in much the same way as a query. However, instead of displaying tuples to the user, we remove the selected tuples from the database. We can delete only whole tuples; we cannot delete values on only particular attributes.

In relational algebra a deletion is expressed by

r ← r − E

where r is a relation and E is a relational-algebra query.

Here are several examples of relational-algebra delete requests:

Note that, in the final example, we simplified our expression by using assignment to temporary relations (r1 and r2).

Insertion

To insert data into a relation, we either specify a tuple to be inserted or write a query whose result is a set of tuples to be inserted. Obviously, the attribute values for in- serted tuples must be members of the attribute’s domain. Similarly, tuples inserted must be of the correct arity. The relational algebra expresses an insertion by

r ← r ∪ E

where r is a relation and E is a relational-algebra expression. We express the insertion of a single tuple by letting E be a constant relation containing one tuple.

Suppose that we wish to insert the fact that Smith has $1200 in account A-973 at the Perryridge branch. We write

More generally, we might want to insert tuples on the basis of the result of a query. Suppose that we want to provide as a gift for all loan customers of the Perryridge branch a new $200 savings account. Let the loan number serve as the account number for this savings account. We write

Instead of specifying a tuple as we did earlier, we specify a set of tuples that is inserted into both the account and depositor relation. Each tuple in the account relation has an account-number (which is the same as the loan number), a branch-name (Perryridge), and the initial balance of the new account ($200). Each tuple in the depositor relation has as customer-name the name of the loan customer who is being given the new account and the same account number as the corresponding account tuple.

Updating

In certain situations, we may wish to change a value in a tuple without changing all values in the tuple. We can use the generalized-projection operator to do this task:

where each Fi is either the ith attribute of r, if the ith attribute is not updated, or, if the attribute is to be updated, Fi is an expression, involving only constants and the attributes of r, that gives the new value for the attribute.

If we want to select some tuples from r and to update only them, we can use the following expression; here, P denotes the selection condition that chooses which tuples to update:

To illustrate the use of the update operation, suppose that interest payments are being made, and that all balances are to be increased by 5 percent. We write

Now suppose that accounts with balances over $10,000 receive 6 percent interest, whereas all others receive 5 percent. We write where the abbreviations AN and BN stand for account-number and branch-name, respectively.