Modifying the Database- deletion, insertion, updating
- Up until now, we have looked at extracting information from the database. We also need to add, remove and change information. Modifications are expressed using the assignment operator.
- Deletion is expressed in much the same way as a query. Instead of displaying, the selected tuples are removed from the database. We can only delete whole tuples.In relational algebra, a deletion is of the form
where is a relation and is a relational algebra query.
Tuples in for which is true are deleted.
- Some examples:1. Delete all of Smith’s account records.
2. Delete all loans with loan numbers between 1300 and 1500.
3. Delete all accounts at Branches located in Needham.
- To insert data into a relation, we either specify a tuple, or write a query whose result is the set of tuples to be inserted. Attribute values for inserted tuples must be members of the attribute’s domain.
- An insertion is expressed by
where is a relation and is a relational algebra expression.
- Some examples:1. To insert a tuple for Smith who has $1200 in account 9372 at the SFU branch.
2. To provide all loan customers in the SFU branch with a $200 savings account.
- Updating allows us to change some values in a tuple without necessarily changing all.We use the update operator, , with the form
where is a relation with attribute , which is assigned the value of expression .
The expression is any arithmetic expression involving constants and attributes in relation .
1. To increase all balances by 5 percent.
This statement is applied to every tuple in deposit.
2. To make two different rates of interest payment, depending on balance amount:
Note: in this example the order of the two operations is important.