Database Modification

Database Modification

Modification of the database includes the

• Insert: to insert data into the table
• Delete: to delete data from the table
• Update: to update existing data on the table
  

Insert Into Statement

• INSERT INTO table_name VALUES (value1, value2,...)
• INSERT INTO "table_name" ("column1", "column2",....) VALUES ("value1", "value2"....)
• To copy the part of information from one table to another table, we can write SQL in the following form:
  If we have two tables having same domain of a1 and b1, a2 and b2 in T1 (a1,a2,a3,a4,a5) and T2 (b1,b2), then SQL to insert the information of T1 and T2 is
  Insert into T2
  Select a1, a2
  From T1

Delete From Statement

• DELETE FROM "table_name" WHERE {condition}
• EXAMPLE: Delete the record of all accounts with balances below the average at the bank
  Delete from account
  where balance< (select avg (balance)from account)

Update Statement

• UPDATE "table_name" SET "column_1" = [ new value]WHERE {condition}
• UPDATEtbl_name
  set column = case
  When predicate1 then result1
  When predicate2 then result2
  .....................
  When predicate n then result in n
  Else result
  end

Example: Increase all accounts with balances over 20,000 by 10%, all other accounts receive 15%.

Update account

set balance = case

when balance<=20000then balance *1.1

else balance *1.15

end

The above actions such as insertion, deletion, and update can cause violations of referential integrity. Therefore, the system must check that referential integrity is maintained when these operations are performed. If referential integrity is violated during these operations, the default action is to reject the operation. Foreign keys are considered as referential integrity in SQL. Foreign keys are specified as part of the SQL 'create table' statement by using the 'foreign key' clause. By default, foreign key references are the primary key attributes of the referenced table.

Cascading

When referential integrity is violated during modification, we can perform cascading instead of rejecting the modification. Cascading can be done in two types and they are:

• Delete cascade
• Update cascade

Delete cascade: Anything that has references to the deleted item is also deleted in a delete cascade.

Update cascade: When the updated item results in a violation of referential integrity, the system will update accordingly to fix the problem. This process is called update cascade.

References:

1. H.F.Korth and A. Silberschatz,"Database system concepts",McGraw Hill,2010
2. A.K.Majumdar and p, Bhattacharya,"Database Management Systems",Tata McGraw Hill,India,2004
3. F.Korth, Henry. Database System Concepts. 6th edition.