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.

Tables

All data in relational database is stored in tables. Every tables has a table name and a set of columns and rows in which the data is stored. each column is given a column name, a data type(defining characteristics of the data to be entered in the column). Usually in a relational database, some of the columns in different tables contain the same information. In this way, the tables can refer to one another.

Views (virtual Tables) in SQL

Views (virtual Tables) in SQL: The SQL DDL includes commands for defining views.A views is customized presentation of the data from one or more tables. views derive their data from the tables on which they are based, which are known as base ables of the view.

Non procedural Language :SQL is a non procedural language because it:

·         Processess sets of records rather than just one at a time

·         provides automatics navigation to the data

·        system administrators

·         Database administrators

·         Securiuty administrators

·         Application programmers

 

SQL Data Manipulation Language(DML)

SQL (Structured Query Language) is a syntax for executing queries . But the SQL language also includes a syntax to update,insert and delete records.

These query and update commands together  form the Data Manipulation Language (DML) part of SQL:

·SELECT: extracts data from a database table

·UPDATE: updates data in a database  table

·DELETE :deletes data from a database table

Transaction Control Commands:

Transaction Control Commands manage changes made by Data Manipulation Language commands. These commands are listed in the following table.

Command	Purpose
COMMIT	To make permanent the changed made by statements issued at the beginning of transaction
ROLLBACK	To undo all changes since the beginning of a transaction or since a savepoint.
SAVEPOINT	To establish a point back to which you ma y roll.
SET TRANSACTION	To establish properties for the current transaction.

SQL Data Definition Language (DDL)

The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables.

The most important DDL statements in SQL are:

·         CREATE TABLE: create a new database table

·         ALTER TABLE: alters(change ) a database table

·         DROP TABLE: delete a database table

·         CREATE  INDEX: create an index(search key)

·         DROP INDEX: delete an intex

FEATURES OF SQL

SQL is the set of commands that programs and users may use to access data within the database that supports it.  Application programs and database tools often allow users to access the database without directly using SQL, however, these applications also translate the actions into SQL under the hood.

SQL has its root in the paper, “A Relational model of data for Large Shared Data Banks” ,published by DR E. F. Codd in june 1970 in the association of computer machinery (ACM) journal, Communications of the ACM. incidentally , the paper is also contained Codd’s model, which is now accepted as the definitive model for relational database management systems(RDBMS).

·         SQL stands for Structured Query Language

·         SQL allows you to access a data base

·         SQL is an ANSI standard computer language

·         SQL can execute queries against database

·         SQL can retrieve data from a database

·         SQL can insert new records in a database

·         SQL can delete records from a database

·         SQL can update records in a database

·         SQL is easy to learn

The SQL commands can be categorised in different groups for convenience. They are;

·         Basic Queries in SQL

·         More complex SQL Queries