Explanation of Stored Procedure, Trigger, Index, and View

Explain the following database objects/ process.
Stored procedure
They are collection set of sequential query language statement stored together in
Database management system. The stored procedure can be reused by different
applications, this make the execution of the statements to be fast and produce
reliable performance. Stored procedure is executed using a single line of code this
reduce the delay of network between the client and server side.
The following is the stored procedure syntax
Syntax Example
CREATE PROCEDURE
name_of_procedure
AS
BEGIN
Sql_statements
END;
CREATE PROCEDURE student_details
AS
BEGIN
SELECT * FROM students
END;
EXEC name_of_procedure; EXEC student_details;
To execute the procedure we need to execute the stored procedure by calling the
name_of _procedure.
While creating a stored procedure we can pass in parameters to it.
Example: CREATE PROCEDURE student_details @username nvarchar (20)
AS
BEGIN
SELECT * FROM students where username = @username
END;
We call the stored procedure by calling.
EXEC student_details username=”Brinton”;
It is important to use stored procedure when you have a similar line of code that will
be executed several number of times
Stored procedure are easy to write and they make it easy to troubleshoot when a
problem arise in an application.
Trigger
They are procedure that automatically occur in DBMS when an event is fired in a
database. A trigger is described I three parts.

 Event- change to database that activates trigger.
 Condition-query that executes when a trigger is activated. A condition can be
true or false
 Action- executed functions that occur when the trigger is activated and its
condition is true. Action check the condition if it is true and make changes to
the database.
We have two types of triggers.
 Data Definition Language (DDL) triggers
 Data Manipulation Language (DML) triggers
DDL triggers occur when the Data Definition Language event occurs. This events
are DROP, CREATE and ALTER table statements.
DML triggers are occur when the data manipulation languages event occurs. They
are event that occur when a user tries to manipulate data. They include INSERT,
UPDATE, DELETE.
SYNTAX EXAMPLE
create trigger
[trigger_name]
[before | after]
{insert | update | delete}
on [table_name]
[for each row]
[trigger_body]
CREATE TRIGGER student_marks
BEFORE
INSERT
ON students
For each row
Set n.total = sub1 + sub2 + sub3;
In DML we have two types of triggers, AFTER and BEFORE. AFTER is executed after
the DML event has occurred while BEFORE is executed before the DML events
occurs.
Index
An index is a database column that is created to ease retrieving of data from the
database. The index columns are not visible by the user.
There is three different types of indexing:
Primary indexing – the indexing is defined on the primary key where the data is
ordered
Secondary indexing – defined on the candidate key which has unique value on each
column.
Clustering indexing – performed on an ordered non-key field
syntax example

CREATE INDEX index_name
ON table_name (columns);
or
DROP INDEX index_name;
CREATE INDEX stud_id
ON students(username);
DROP INDEX stud_id
Indexing is done to accelerate the retrieval of data from database, it perform well
when is created on primary key column.
View
Is a table whose rows are virtually stored in the database but can be computed as
required. View combine data from different multiple tables to form a single virtual
table. Views offers high level of security because the data is encapsulated from the
user where they can only access the data that Is selected.
Creating a view.
syntax example
CREATE VIEW view_name
AS
SELECT column1, column2,
...
FROM table_name
WHERE condition;
CREATE VIEW stud_marks
SELECT sid, dno
FROM students
Where sid>10;
Explain the following in detail
Transaction isolation level
In isolation level transaction are executed without the consent of other transaction
thus preventing conflict between the transaction.
We have four level of transaction isolation
Read uncommitted
This occurs when one transaction read data that is not committed by another
transaction. When this happen it cause a dirty read. A dirty read is a process of one
transaction reading data that has not been committed by transaction that created
the data. For example let assume transaction A has updated the data on database
without committing the changes and transaction B reads the data that was updated
by transaction A, unfortunately transaction A performs a Rollback then transaction B
will be assumed to read data that didn’t exist.
Read committed
A transaction A cannot read of another transaction that has not been committed.
This level avoids dirty reads but experience non-repeatable read. For example
assume Transaction A read data committed by transaction B, hence within a couple
of minutes transaction B updates the data it has created when transaction A go