Advanced Database

Concepts in SQL
Kuribachew Gizaw(PhD)

October
2023
Outline
 View
 Indexes and hashes
 Stored Procedure
 Triggers
Views and Creating views using SQL
 A view in SQL terminology is a single table that is derived from
other tables
 A view does not exist in the physical table, it is considered as a
virtual Table
 This limits the possible update operations that can be applied to
view
 We can think of a view as a way of specifying a table that we
need to reference frequently, even though it may not exist
physically.
Views and Creating views using SQL
Syntax to create View
CREATE VIEW <view name> [ (<column name>{,<column name>})] AS
<select statement>
Syntax to drop View
DROP VIEW <view name>
Indexing and Hashing
Creating indexes using SQL
 Indexing mechanisms used to speed up access to desired data.
 E.g., author catalog in library
 Search Key - attribute to set of attributes used to look up records in a file.
 An index file consists of records (called index entries) of the form
search-key pointer

 Index files are typically much smaller than the original file
 Two basic kinds of indices:
 Ordered indices: search keys are stored in sorted order
 Hash indices: search keys are distributed uniformly across “buckets” using a “hash function”.
Ordered Indices
 In an ordered index, index entries are stored sorted on the search key value. E.g.,
author catalog in library.
 Primary index: in a sequentially ordered file, the index whose search key specifies the
sequential order of the file.
 Also called clustering index
 The search key of a primary index is usually but not necessarily the primary key.
 Secondary index: an index whose search key specifies an order different from the
sequential order of the file. Also called
non-clustering index.
 Index-sequential file: ordered sequential file with a primary index.
Primary and Secondary Indices

 Indices offer substantial benefits when searching for records.

 BUT: Updating indices imposes overhead on database modification --when a file
is modified, every index on the file must be updated,
 Sequential scan using primary index is efficient, but a sequential scan using a
secondary index is expensive
 Each record access may fetch a new block from disk
 Block fetch requires about 5 to 10 milliseconds, versus about 100
nanoseconds for memory access
Secondary Indices
 Frequently, one wants to find all the records whose values in a certain field
(which is not the search-key of the primary index) satisfy some condition.
 Example 1: In the Employee relation stored
sequentially by ID, we may want to find all
instructors in a particular department
 Example 2: as above, but where we want to find all
instructors with a specified salary or with salary in a
specified range of values
 We can have a secondary index with an index record for each search-key value
B+-Tree Index Files
.
 B+-tree indices are an alternative to indexed-sequential files
 Disadvantage of indexed-sequential files
 performance degrades as file grows, since many overflow blocks get created.
 Periodic reorganization of entire file is required.
 Advantage of B+-tree index files:
 automatically reorganizes itself with small, local, changes, in the face of insertions
and deletions.
 Reorganization of entire file is not required to maintain performance.
 (Minor) disadvantage of B+-trees:
 extra insertion and deletion overhead, space overhead.
 Advantages of B+-trees outweigh disadvantages
 B+-trees are used extensively
Creating Index using SQL

 The general Syntax to create an index is:

Create index index_Name
On
Table_Name(AttributeToBeindexed);
 The general Syntax to drope an index is:
Drop index index_Name
On Table_Name;
Hashing
 Another type of primary file organization is based on hashing, which
provides very fast access to records under certain search conditions.
This organization is usually called a hash file.
 The search condition must be an equality condition on a single field,
called the hash field.
 In most cases, the hash field is also a key field of the file, in which
case it is called the hash key.
 The idea behind hashing is to provide a function h, called a hash
function or randomizing function, which is applied to the hash field
value of a record and yields the address of the disk block in which the
record is stored.
 A search for the record within the block can be carried out in a main
memory buffer. For most records, we need only a single-block access
to retrieve that record.
Active Database and Triggers
 Active database is on which active rules are applied. Many commercial packages
include some of the functionality provided by active databases in the form of triggers.
 Rules that specify actions that are automatically triggered by certain events have been
considered important enhancements to database systems for quite some time.
 The model that has been used to specify active database rules is referred to as the Event-
Condition-Action (ECA) model.
 A rule in the ECA model has three components
Project II Section--ONE
 I create the table in SQL( 3mark)

Residents
IDNu FNAme LNAMe SEX PhoneNumb SubCity
mbe er
r
101 Almaz Gebeya F 0911123456 Gullele
w
102 Beken Tulu M 0989786756 Bole
103 Fereweyen Hagos F 0923456789 Yeka
i
104 Sarem Neri F 0912435678 Gullele
105 Hasenan Handeb F 0923436789 Bole
o
 1.
Do update with case by inserting
Grade attribute
>85 A
>75 B
>65 C
>55 D
<F
Query: Get all Students whose name
starts with ‘L’,
select * from student
where Fname like 'L%‘
[from F to M],
ends with D?
Query: List all students whose mark
is between 50 and 70
Each one mark
 II. Create a view of Female Residents
 III. Create an index by Phone Number
 IV.update the Fname of
IDNumber=101 by Hana
 V. Delete the first row
Active Database and Triggers

 1. The event(s) that triggers the rule: These events are usually
database update operations(Insert, delete, modify) that are explicitly
applied to the database.
 However, in the general model ,they could also be temporal events2 or other
kinds of external events.
 2. The condition that determines whether the rule action should
be executed:
 Once the triggering event has occurred, an optional condition may be
evaluated. If no condition is specified, the action will be executed once the
event occurs. If a condition is specified, it is first evaluated, and only if it
evaluates to true will the rule action be executed.
 3. The action to be taken: The action is usually a sequence of SQL
statements, but it could also be a database transaction or an external
program that will be automatically executed.
Creating Triggers using SQL

 General Syntax to create a trigger

Create Trigger Trigger_Name
On [Table_Name]
(Before\After)
[insert/update/delete] [of Attribute_Name]
For each row/for each column
[The action]
Stored Procedures

 stored procedures are program modules that are stored by the DBMS
at the database server.
 These extensions are known as SQL/PSM (SQL/Persistent Stored
Modules) and can be used to write stored procedures.
 SQL/PSM also serves as an example of a database programming
language that extends a database model and language—namely, SQL
—with some programming constructs, such as conditional statements
and loops.
 More involved procedures to enforce rules are popularly called stored
procedures; they become a part of the overall database definition and
are invoked appropriately when certain conditions are met.
Creating Stored Procedures using
SQL
 General Syntax to create a Stored Procedure
Create Procedure Procedure_Name
As
Begin
Select
From
Where
End
 To execute the Procedure
Exec Procedure_Name;
Create Stored Procedure Example

Create A Procedure named Tax for the price where Tax =3%
of the price
Declare Tax INT
Create A Procedure TaX
As
BEGIN
Set Tax = (Select Price
From Product
where tax =price*
END
Create the following Table
Employee

EmpID Fullname Bdate Salary Position

M101 Hagos 11-12-90 25000 Manager

Berehe
IT101 Helen Bederu 9-7-2000 30000 CIO

IT102 Cheru 23-8-2001 21000 System

Mulgeta Admin
IT103 Ayantu Challa 21-10-2001 25000 Web
Designer
IT 104 Keroud Neri 10-6-2000 22000 Network
Admin
Do the following
 2. create a stored procedure Age using Datediff function to have an
updated date of Employees?
create procedure age
as
begin
update Employee
set age=datediff(year,Bdate,getdate())
end

select *from Employee

exec age
 3. Create a trigger that will rollback the transaction when salary is greater
than 30000
CREATE TRIGGER SALARYCHECK
ON EMPLOYEE
FOR INSERT ,UPDATE
AS
BEGIN
IF EXISTS (SELECT 1 FROM INSERTED WHERE SALARY>30000)
BEGIN
RAISERROR('SALARY CANNOT BE GRATER THAN 30000', 16,1);
ROLLBACK TRANSACTION;
END;
END;
Create Stored Procedure Example

Create A Procedure named Tax for the price where Tax =3%
of the price
Declare Tax INT
Create A Procedure TaX
As
BEGIN
Set Tax = (Select Price
From BooK
where tax =price*3%;
END
Create Stored Procedure Example
Using IF/Else Statement

Create A Procedure named Tax2 for

the price where if the price >10,000
Tax =5% of the price and if less than
3%
Use PRINT(like System.Out.println
or Cout>>) command to have a
display in the output