DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING

CST 204 - Database Management Systems

Prof. Smitha Jacob, Prof. Sarju S

5 May 2025
CST 204 - Database Management Systems

https://smitha-jacob.github.io/smithajac20

Page 2
 Module 3

► SQL DML (Data Manipulation Language) - SQL queries on single and multiple
tables, Nested queries (correlated and non-correlated), Aggregation and
grouping, Views, assertions, Triggers,
► SQL data types.
► Physical Data Organization - Review of terms: physical and logical records,
blocking factor, pinned and unpinned organization. Heap files, Indexing,
Single level indices, numerical examples, Multi-level-indices, numerical
examples, B-Trees & B+-Trees (structure only, algorithms notrequired),
Extendible Hashing, Indexing on multiple keys – grid files.

Page 3 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL DML (Data Manipulation Language)

Page 4
 Data Manipulation Language

► DML stands for Data Manipulation Language.

► It is a language used for selecting, inserting, deleting and updating data in a
database.
► It is used to retrieve and manipulate data in a relational database.
► There are three basic constructs which allow database program and user to
enter data and information are:
► INSERT
► UPDATE
► DELETE

Page 5 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL |
INSERT
► The INSERT INTO statement is used to insert new records in a table.
► It is possible to write the INSERT INTO statement in two ways:
► Specify both the column names and the values to be inserted:
► Syntax :
► INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
► If you are adding values for all the columns of the table, you do not need to specify the
column names in the SQL query.
► Make sure that the order of the values is in the same order as the columns in the table.
► Syntax
► INSERT INTO table_name VALUES (value1, value2, value3, ...);

Page 6 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL | INSERT |
Example
CustomerID CustomerName ContactName Address City PostalCode Country
305 - 14th Ave. S. Suite
89 White Clover Markets Karl Jablonski Seattle 98128 USA
3B
90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland

► INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

CustomerID CustomerName ContactName Address City PostalCode Country

305 - 14th Ave. S. Suite
89 White Clover Markets Karl Jablonski Seattle 98128 USA
3B
90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland
92 Cardinal Tom B. Erichsen Skagen 21 Stavanger 4006 Norway

Page 7 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL |
INSERT
► Insert Data Only in Specified Columns
► It is also possible to only insert data in specific columns.
► Syntax
► INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');
► The selection from the "Customers" table will now look like this:

CustomerID CustomerName ContactName Address City PostalCode Country

305 - 14th Ave. S. Suite
89 White Clover Markets Karl Jablonski Seattle 98128 USA
3B
90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland
92 Cardinal null null Stavanger null Norway

Page 8 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL |
INSERT
► To insert multiple rows in a table using Single SQL Statement
INSERT INTO table_name(Column1,Column2,Column3,.......)
VALUES (Value1, Value2,Value3,.....),
(Value1, Value2,Value3,.....),
(Value1, Value2,Value3,.....),
............................. ;
► Example
INSERT INTO STUDENT(ID, NAME,AGE,GRADE,CITY)
VALUES(1,"AMIT KUMAR",15,10,"DELHI"),
(2,"GAURI RAO",18,12,"BANGALORE"),
(3,"MANAV BHATT",17,11,"NEW DELHI"),
(4,"RIYA KAPOOR",10,5,"UDAIPUR");

Page 9 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL |
UPDATE
► The UPDATE statement in SQL is used to update the data of an existing table in
database.
► We can update single columns as well as multiple columns using UPDATE
statement as per our requirement.
► Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
► Note: The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE
clause, all records in the table will be updated!

Page 10 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL | UPDATE | Example

CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
Ana Trujillo
Avda. de la
2 Emparedados y Ana Trujillo México D.F. 05021 Mexico
Constitución 2222
helados
Antonio Moreno
3 Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
Taquería
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
Berglunds
5 Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
snabbköp

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

Page 11 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL | UPDATE | Example

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
CustomerID CustomerName ContactName Address City PostalCode Country
Alfred Schmidt Obere Str. 57 Frankfurt 12209
1 Alfreds Futterkiste Germany
Ana Trujillo
Avda. de la
2 Emparedados y Ana Trujillo México D.F. 05021 Mexico
Constitución 2222
helados
Antonio Moreno
3 Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
Taquería
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
Berglunds
5 Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
snabbköp

Page 12 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL |
UPDATE
► UPDATE Multiple Records
►Itis the WHERE clause that determines how many records will be updated.
UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Alfred Schmidt Obere Str. 57 Frankfurt 12209 Germany
Ana Trujillo
Juan Avda. de la Mexico
2 Emparedados y México D.F. 05021
Constitución 2222
helados
Antonio Moreno
3 Juan Mataderos 2312 México D.F. 05023 Mexico
Taquería
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

Berglunds
5 Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
snabbköp

Page 13 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL |
UPDATE
► If you omit the WHERE clause, ALL records will be updated!
UPDATE Customers
SET ContactName='Juan';

CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Juan Obere Str. 57 Frankfurt 12209 Germany
Ana Trujillo
Avda. de la
2 Emparedados y Juan México D.F. 05021 Mexico
Constitución 2222
helados
Antonio Moreno
3 Juan Mataderos 2312 México D.F. 05023 Mexico
Taquería
4 Around the Horn Juan 120 Hanover Sq. London WA1 1DP UK
Berglunds
5 Juan Berguvsvägen 8 Luleå S-958 22 Sweden
snabbköp

Page 14 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL |
DELETE
► The DELETE Statement in SQL is used to delete existing records from a table.
► We can delete a single record or multiple records depending on the condition
we specify in the WHERE clause.
► Syntax
► DELETE FROM table_name WHERE condition;
► The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records
in the table will be deleted!

Page 15 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL | DELETE |
Example
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
Ana Trujillo
Avda. de la
2 Emparedados y Ana Trujillo México D.F. 05021 Mexico
Constitución 2222
helados
Antonio Moreno
3 Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
Taquería

► DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

CustomerID CustomerName ContactName Address City PostalCode Country
Ana Trujillo
Avda. de la
2 Emparedados y Ana Trujillo México D.F. 05021 Mexico
Constitución 2222
helados
Antonio Moreno
3 Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
Taquería

Page 16 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL |
DELETE
► Delete All Records
► It is possible to delete all rows in a table without deleting the table. This means that the
table structure, attributes, and indexes will be intact:
► Syntax
► DELETE FROM table_name;
► Example
► DELETE FROM customers;

Page 17 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Basic Query
Structure
► A typical SQL query has the form:

select A1, A2, ..., An

from r1, r2, ..., rm
where P

► Ri represents a relation
► Ai represents an attribute
► P is a predicate.
► The result of an SQL query is a relation.

Page 18 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 The select
Clause
► The select clause lists the attributes desired in the result of a query
► corresponds to the projection operation of the relational algebra
► Example: find the names of all instructors:
select name from instructor
► NOTE: SQL names are case insensitive (i.e., you may use upper- or lower-case
letters.)
► E.g., Name = NAME = name

Page 19 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 The select Clause

SELECT name
from instructor

Page 20 Prof. Sarju S, Department of Computer Science and Engineering, SJCET

Palai
 The select
Clause
► SQL allows duplicates in relations as well as in query results.
► To force the elimination of duplicates, insert the keyword distinct after select.
► Find the department names of all instructors, and remove duplicates
select distinct dept_name from instructor
► The keyword all specifies that duplicates should not be removed.

select all dept_name from instructor

Page 21 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 The select
Clause

SELECT DISTINCT
dept_name
FROM instructor

Page 22 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
The select Clause

► An asterisk in the select clause denotes “all attributes”

select * from instructor

Page 23 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 The select
► The select clause can containClause
arithmetic expressions involving the operation, +,
–, , and /, and operating on constants or attributes of tuples.
► The query:

select ID, name, salary/12

from instructor
would return a relation that is the same as the instructor relation, except
that the value of the attribute salary is divided by 12.
► Can rename “salary/12” using the as clause:

select ID, name, salary/12 as monthly_salary

Page 24 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 The select
Clause

SELECT
ID,name,salary/12
FROM instructor

Page 25 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 The where Clause
► The where clause specifies conditions that the result must satisfy
► Corresponds to the selection predicate of the relational algebra.
► To find all instructors in Comp. Sci. dept
select name
from instructor
where dept_name = ‘Comp. Sci.'
► Comparison results can be combined using the logical connectives and, or, and
not
► To find all instructors in Comp. Sci. dept with salary > 80000
select name
from instructor
where dept_name = ‘Comp. Sci.' and salary > 80000
► Comparisons can be applied to results of arithmetic expressions.
Page 26 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 The where Clause

SELECT `name` FROM

`instructor` WHERE
`dept_name`="Comp. Sci."

Page 27 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 The from Clause
► The from clause lists the relations involved in the query
► Corresponds to the Cartesian product operation of the relational algebra.
► Find the Cartesian product instructor X teaches
select *
from instructor, teaches
► generates every possible instructor – teaches pair, with all attributes from both relations.
► For common attributes (e.g., ID), the attributes in the resulting table are renamed using the
relation name (e.g., instructor.ID)
► Cartesian product not very useful directly, but useful combined with where-
clause condition (selection operation in relational algebra).

Page 28 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 The from Clause

SELECT * FROM instructor , teaches

Page 29 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL SELECT
► COUNT
The SQL COUNT() is a function that returns the number of records of the table
in the output.
► This function is used with the SQL SELECT statement.
► Syntax of Select Count Function in SQL
► SELECT COUNT(column_name) FROM table_name;

Page 30 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL SELECT
COUNT
Bike_Name Bike_Color Bike_Cost
Pulsar Black 185,000
Apache Black NULL
KTM RC Red 90,0000 SELECT COUNT (Bike_Color) AS
Royal Enfield White NULL TotalBikeColor FROM Bikes ;

Livo Black 80,000

KTM DUKE Red 195,000
TotalBikeColor
6

Page 31 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL SELECT
► COUNT(*)
The count(*) function in SQL shows all the Null and Non-Null records present
in the table.
► Syntax of Count (*) Function in SQL

► SELECT COUNT(*) FROM table_name;

Page 32 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL SELECT
COUNT(*)
Bike_Name Bike_Color Bike_Cost
Suppose, you want to count the total number of
Livo Black 185,000
records from the Bike Table.
Apache Red NULL
Pulsar Red 90,0000 SELECT COUNT (*) FROM Bikes ;
Royal Enfield Black NULL Count(*)
KTM DUKE Black 80,000 6
KTM RC White 195,000

Page 33 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Count With WHERE clause in SQL

► We can also use the Count() function with the WHERE clause.
► The Count Function with WHERE clause in the SELECT statement shows those
records that matched the specified criteria.
► Syntax of Count() Function With WHERE clause in SQL

► SELECT COUNT(column_name) FROM table_name

WHERE [condition];

Page 34
 Count With WHERE clause in
► We can also use theSQL
Count() function with the WHERE clause.
► The Count Function with WHERE clause in the SELECT statement shows those
records that matched the specified criteria.
► Syntax of Count() Function With WHERE clause in SQL
► SELECT COUNT(column_name) FROM table_name WHERE [condition];

Page 35 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Count With WHERE clause in
SQL
Bike_Name Bike_Color Bike_Cost Count the total number of bikes whose color is
Apache Black 90,0000 black.
Livo Black NULL
KTM RC Red 185,000 SELECT COUNT (Bike_Name)
AS TotalBikeBlackColor FROM Bikes
KTM DUKE White NULL
Royal Enfield Red 80,000
Pulsar Black 195,000 WHERE Bike_Color = 'Black';

TotalBikeBlackColor
3

Page 36 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Count With WHERE clause in SQL -
Exercise
Emp_Id Emp_Name Emp_Salary Emp_City

2001 Bheem 30000 Jaipur

2002 Ankit 45000 Delhi
2003 Sumit 40000 Delhi
2004 Ram 29000 Goa
2005 Abhay 25000 Delhi

Count the total number of those employees who belong to Delhi city.

SELECT COUNT (Emp_Name) AS TotalEmpCity FROM Employee_details WHERE Emp_City = 'Delhi';

Page 37 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Count With DISTINCT keyword
► The DISTINCT keyword with the COUNT function shows only the numbers of
unique rows of a column.
► Syntax of Count Function With DISTINCT keyword in SQL
► SELECT COUNT(DISTINCT column_name) FROM table_name WHERE [condition];

Car_Name Car_Color Car_Cost

i20 White 10,85,000
SELECT COUNT (DISTINCT Car
Hyundai Venue Black 9,50,000
Swift Dezire Red 9,00,000 _Color) AS Unique_Car_Color
Hyundai Creta White 7,95,000 FROM Cars ;
Kia Seltos White 8,00,000
Kia Sonet Red 10,00,000

Count the unique colors of a car from the table.

Page 38
 Count With DISTINCT keyword-Example Mysql

Page 39
 Count With DISTINCT keyword-Example Mysql

Count the unique book category of books from

the table.
Page 40
 Count With DISTINCT keyword-Example Mysql

Count the unique book category of books from

the table.
Page 41
 SQL ORDER BY
► Whenever we want to sortClause
the records based on the columns stored in the
tables of the SQL database, then we consider using the ORDER BY clause in
SQL.
► Using the ORDER BY clause, we can sort the records in ascending or
descending order as per our requirement.
► The records will be sorted in ascending order whenever the ASC keyword is
used with ORDER by clause. DESC keyword will sort the records in descending
order.
► If no keyword is specified after the column based on which we have to sort
the records, in that case, the sorting will be done by default in the
ascending order.

Page 42 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL ORDER BY
► Syntax to sort the recordsClause
in ascending order:

SELECT ColumnName1,...,ColumnNameN FROM TableName ORDER BY ColumnName ASC;

► Syntax to sort the records in descending order:

SELECT ColumnName1,...,ColumnNameN FROM TableName ORDER BY ColumnName

DESC;

Page 43 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL ORDER BY
Clause

Page 44 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL ORDER BY
Clause
ID NAME AGE ADDRESS SALARY

2 Shiva Tiwari 22 Bhopal 21000

3 Ajeet 45 Meerut 65000

Bhargav
4 Ritesh Yadav 36 Azamgarh 26000

5 Balwant 45 Varanasi 36000

Singh SELECT * FROM customers ORDER BY
6 Mahesh 26 Mathura 22000 Name ASC;
Sharma
7 Rohit 19 Ahemdabad 38000
Shrivastav

8 Neeru 29 Pune 40000

Sharma
9 Aakash 32 Mumbai 43500
Yadav
10 Sahil Sheikh 35 Aurangabad 68800

Page 45 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL ORDER BY
ID NAME
Clause
AGE ADDRESS SALARY

9 Aakash Yadav 32 Mumbai 43500

3 Ajeet Bhargav 45 Meerut 65000

5 Balwant Singh 45 Varanasi 36000

1 Himani Gupta 21 Modinagar 22000

6 Mahesh 26 Mathura 22000

Sharma
8 Neeru Sharma 29 Pune 40000

4 Ritesh Yadav 36 Azamgarh 26000

7 Rohit 19 Ahemdabad 38000

Shrivastav
10 Sahil Sheikh 35 Aurangabad 68800

2 Shiva Tiwari 22 Bhopal 21000

Page 46 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL ORDER BY Clause -
► Write a query to Exercise
sort the records in the ascending order of the addresses
stored in the customers table.

SELECT *FROM customers ORDER BY Address;

► Write a query to sort the records in the descending order of the customer
salary stored in the customers table.

SELECT *FROM customers ORDER BY Salary DESC;

► Write a query to sort the records in the descending order of the customer age
stored in the customers table.
SELECT *FROM customers ORDER BY Age DESC;

Page 47 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL DML (Data Manipulation Language)

Page 48
 SQL JOIN

Page 44
49
 SQL JOIN

► How do I get data from multiple tables?

►A SQL JOIN combines records from two tables.
► A JOIN locates related column values in the two tables.

► A query can contain zero, one, or multiple JOIN

operations.
► INNER JOIN is the same as JOIN; the keyword INNER is
optional.

Page 50
 SQL
JOIN
► Four different types of JOINs
► (INNER) JOIN: Select records that have
matching values in both tables.
► FULL (OUTER) JOIN: Selects all records
that match either left or right table
records.
► LEFT (OUTER) JOIN: Select records from
the first (left-most) table with matching
right table records.
► RIGHT (OUTER) JOIN: Select records from
the second (right-most) table with
matching left table records

Page 51 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 The SQL JOIN syntax

► The general syntax is

SELECT column-names
FROM table-name1 INNER JOIN table-name2
ON column-name1 = column-name2
WHERE condition

► The INNER keyword is optional: it is the default as well as the most

commonly used JOIN operation.

Page 52
 The SQL JOIN Example

OR

Page 53
 The SQL JOIN
Example
► Problem: List all orders with customer information
SELECT OrderNumber, TotalAmount, FirstName,
LastName, City, Country FROM Order JOIN Customer
ON Order.CustomerId = Customer.Id

Page 54 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL LEFT
JOIN
► What is a LEFT JOIN in SQL?
► A LEFT JOIN performs a join starting with the first (left-most) table.

► Then, any matched records from the second table (right-most) will be
included.
► LEFT JOIN and LEFT OUTER JOIN are the same.

Page 55 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 The SQL LEFT JOIN syntax

► The general LEFT OUTER JOIN syntax is

SELECT column-names
FROM table-name1 LEFT OUTER JOIN table-name2
ON column-name1 = column-name2
WHERE condition

► The OUTER keyword is optional

Page 56
 The SQL LEFT JOIN syntax

Page 57
 SQL LEFT JOIN
Example
► Problem: List all customers and the total amount they spent irrespective
whether they placed any orders or not.
SELECT OrderNumber, TotalAmount, FirstName,
LastName, City, Country
FROM Customer C LEFT JOIN Order O
ON O.CustomerId = C.Id
ORDER BY TotalAmount

Page 58 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL RIGHT
JOIN
► What is a RIGHT JOIN in SQL?
► A RIGHT JOIN performs a join starting with the second (right-most) table and then any
matching first (left-most) table records.

► RIGHT JOIN and RIGHT OUTER JOIN are the same.

Page 59 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 The SQL RIGHT JOIN syntax

► The general RIGHT OUTER JOIN syntax is:

SELECT column-names
FROM table-name1 RIGHT OUTER JOIN table-
name2
ON column-name1 = column-name2
WHERE condition

► The OUTER keyword is optional

Page 60
 The SQL RIGHT JOIN Example

Page 61
 SQL RIGHT JOIN Examples

► Problem: List customers that have not placed orders

SELECT TotalAmount, FirstName, LastName,
City, Country
FROM [Order] O RIGHT JOIN Customer C
ON O.CustomerId = C.Id
WHERE TotalAmount IS NULL

Page 62
 SQL FULL
JOIN
► What does a SQL FULL JOIN return?
► FULL JOIN returns all matching records from both tables whether the other
table matches or not.
► Be aware that a FULL JOIN can potentially return very large datasets.

► These two: FULL JOIN and FULL OUTER JOIN are the same.

Page 63 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 The SQL FULL JOIN syntax

► The general FULL OUTER JOIN syntax is:

SELECT column-names
FROM table-name1 FULL OUTER JOIN table-name2
ON column-name1 = column-name2
WHERE condition

► The OUTER keyword is optional

Page 64
 SQL FULL JOIN
Examples
► Problem: Match all customers and suppliers by country
SELECT C.FirstName, C.LastName, C.Country AS CustomerCountry,
S.Country AS SupplierCountry, S.CompanyName
FROM Customer C FULL JOIN Supplier S
ON C.Country = S.Country
ORDER BY C.Country, S.Country

This returns suppliers that have no customers in their country,

and customers that have no suppliers in their country,
and customers and suppliers that are from the same country.

Page 65 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Aggregation and grouping

Page 66
 Aggregation and grouping

► Aggregate functions are a very powerful tool to analyze the data and gain
useful business insights.
► The most commonly used SQL aggregate functions include SUM, MAX, MIN, COUNT
and AVERAGE.
► Aggregators are very often used in conjunction with Grouping functions in
order to summarize the data.

Page 67 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL
COUNT()
► The COUNT() function returns the number of rows that matches a specified criteria.
► SQL COUNT(column_name)
► The COUNT(column_name) function returns the number of values (NULL values will not be
counted) of the specified column:
► SELECT COUNT(column_name) FROM table_name;
► SQL COUNT(*)
► The COUNT(*) function returns the number of records in a table:
► SELECT COUNT(*) FROM table_name;
► SQL COUNT(DISTINCT column_name)
► The COUNT(DISTINCT column_name) function returns the number of distinct values of the
specified column:
► SELECT COUNT(DISTINCT column_name) FROM table_name;

Page 68 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL COUNT()
Example
OrderID CustomerID EmployeeID OrderDate ShipperID
10265 7 2 1996-07-25 1
10266 87 3 1996-07-26 3
10267 25 4 1996-07-29 1

counts the number of orders from "CustomerID"=7 from the "Orders" table:
SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders
WHERE CustomerID=7;
OrdersFromCustomerID7

Page 69 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL COUNT(*)
Example
OrderID CustomerID EmployeeID OrderDate ShipperID
10265 7 2 1996-07-25 1
10266 87 3 1996-07-26 3
10267 25 4 1996-07-29 1

To get number of rows in the 'orders' table, the following SQL statement can be
used:
SELECT COUNT(*) FROM orders;

COUNT(*)
3

Page 70 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL COUNT(DISTINCT column_name)
Example
OrderID CustomerID EmployeeID OrderDate ShipperID
10265 7 2 1996-07-25 1
10266 87 3 1996-07-26 3
10267 25 4 1996-07-29 1

SELECT COUNT(DISTINCT ShipperID)

FROM orders;

COUNT(DISTINCT ShipperID)
2

Page 71 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL SUM,
AVG
► SELECT SUM returns the sum of the data values.
► And SELECT AVG returns the average of the data
values.
► The general SUM syntax is:
SELECT SUM(column-name)FROM table-name
► The general AVG syntax is:
SELECT AVG(column-name)FROM table-name

Page 72 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL SUM, AVG
Examples
► Problem: Compute the
total amount sold in 2013.
► SELECT SUM(TotalAmount)
Sum
FROM [Order] 658388.75
WHERE YEAR(OrderDate) = 2013

► Problem: Compute the average size of all orders.

SELECT AVG(TotalAmount) Average
1631.877819
FROM [Order]

Page 73 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL MAX and
MIN
► SELECT MIN returns the minimum value for a column.
► And SELECT MAX returns the maximum value for a column.

► The general MIN syntax is:

SELECT MIN(column-name)
FROM table-name

► The general MAX syntax is:

SELECT MAX(column-name)
FROM table-name

Page 74 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL MAX and MIN
Example
Problem: Find the cheapest product
SELECT MIN(UnitPrice)
FROM Product
UnitPrice
2.50
Problem: Find the largest order placed in 2014
SELECT MAX(TotalAmount)
FROM [Order]
WHERE YEAR(OrderDate) = 2014 TotalAmount
17250.00

Problem: Find the last order date in 2013

SELECT MAX(OrderDate) OrderDate
FROM [Order] 2013-12-31 00:00:00.000
WHERE YEAR(OrderDate) = 2013

Page 75 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL GROUP BY
Clause
► What is the purpose of the GROUP BY clause?
► The GROUP BY clause groups records into summary rows.
► It returns one record for each group.
► GROUP BY queries often include aggregates: COUNT, MAX, SUM, AVG, etc.
► A GROUP BY clause can group by one or more columns.

Page 76 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL GROUP BY
Clause
► The SQL GROUP BY syntax
SELECT column-names
FROM table-name
WHERE condition
GROUP BY column-names
► The general syntax with ORDER BY is:
SELECT column-names
FROM table-name
WHERE condition
GROUP BY column-names
ORDER BY column-names
Page 77 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL GROUP BY Clause -Example

► Problem: List the number of books in each book category

► SELECT COUNT(bookId), bcategory FROM book GROUP BY bcategory;

Page 78 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL GROUP BY Clause
Example
► Problem: List the number of customers in each country.
SELECT COUNT(Id), Country
FROM Customer
GROUP BY Country

Page 79 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL GROUP BY Clause -Example

► Problem: List the number of books published by each publisher in the

descending order of publisher name.
► SELECT COUNT(bookid) as Number_of_books, publisher FROM book1
GROUP BY publisher
ORDER by publisher DESC;

Page 80 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL GROUP BY Clause
Example
Problem: List the number of customers in each country sorted high to low
SELECT COUNT(Id), Country
FROM Customer
GROUP BY Country
ORDER BY COUNT(Id) DESC

Page 81 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL GROUP BY Clause
Example
Problem: List the total amount of each category of books as
tot_bookprice and book category in the highest amount order.
SELECT SUM(b1.bookprice) AS
tot_bookprice,b.bcategory
FROM book1 b1 JOIN book b ON
b.bookid=b1.bookid
GROUP BY b.bcategory
ORDER BY SUM(b1.bookprice) DESC;

Page 82 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL GROUP BY Clause
Example
Problem: List the total amount ordered for each customer in the highest
amount order.
SELECT SUM(O.TotalAmount) AS SUM, C.FirstName, C.LastName
FROM Order O JOIN Customer C
ON O.CustomerId = C.Id
GROUP BY C.FirstName, C.LastName
ORDER BY SUM(O.TotalAmount) DESC

Page 83 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL HAVING
Clause
► What does the HAVING clause do in a query?
► The HAVING clause is like WHERE but operates on grouped records returned by a GROUP
BY.
► HAVING applies to summarized group records, whereas WHERE applies to
individual records.
► Only the groups that meet the HAVING criteria will be returned.
► HAVING requires that a GROUP BY clause is present.
► Both WHERE and HAVING can be used in the same query at the same time.

Page 84 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 The SQL HAVING
syntax
► The general syntax is
SELECT column-names
FROM table-name
WHERE condition
GROUP BY column-names
HAVING condition
► The general syntax with ORDER BY is:
SELECT column-names
FROM table-name
WHERE condition
GROUP BY column-names
HAVING condition
ORDER BY column-names

Page 85 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL GROUP BY
Examples
► Problem: List the number of books published by each publisher and the
publisher name, who has published more than one book.

Page 86 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL GROUP BY
Examples
► Problem: List the number of books available in each edition in the ascending
order of edition.

Page 87 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL GROUP BY
Examples
► Problem: List the number of books available(with more than 1 books) in each
edition in the ascending order of edition.

Page 88 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL GROUP BY
Examples
► Problem: List the number of customers in each country. Only include countries
with more than 10 customers.
SELECT COUNT(Id), Country
FROM Customer
GROUP BY Country
HAVING COUNT(Id) > 10

Page 89 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL GROUP BY ,where,having ,order by Examples

Problem: List the number of customers in each country, except the USA, sorted
high to low. Only include countries with 9 or more customers.
SELECT
COUNT(Id), Country
FROM Customer
WHERE Country <> 'USA'
GROUP BY Country
HAVING COUNT(Id) >= 9
ORDER BY COUNT(Id) DESC

Page 90 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL GROUP BY
Examples
Problem: List all customer with average orders between $1000 and $1200.
SELECT AVG(TotalAmount), FirstName, LastName
FROM Order O JOIN Customer C ON O.CustomerId = C.Id
GROUP BY FirstName, LastName
HAVING AVG(TotalAmount) BETWEEN 1000 AND 1200

Page 91 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Nested Queries

Page 92
 SQL IN
Operator
► The IN operator allows you to specify multiple values in a WHERE clause.
► The IN operator is a shorthand for multiple OR conditions.

or

Page 82
93 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL IN and Not in Operator

 Problem : Display all customers who are located in Germany, France or UK:

 Problem : Display all customers who are not located in Germany, France or UK:

 Problem: List all customers that are from the same countries as the suppliers:

Page 82
94 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Sub Query in
SQL
► SubQuery in SQL is a query inside another query.
► Some time to get particular information from a database you may need to fire
two separate SQL queries, subQuery is a way to combine or join them in a
single query.
► SQL query which is on the inner part of the main query is called inner query
while the outer part of the main query is called outer query.
► for example in below SQL query
SELECT name FROM city WHERE pincode IN (SELECT pincode
FROM pin WHERE zone='west')

Page 82
95 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Sub Query in
SQL

► Subqueries can be categorized into two types:

► A non correlated (simple) subquery obtains its results independently
of its containing (outer) statement.

► A correlated subquery requires values from its outer query in order to

execute.

Page 83
96 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Noncorrelated Subqueries

► A noncorrelated subquery executes independently of the outer query.

► The subquery executes first, and then passes its results to the outer
query,
For example:

► Find the instructor who draws second highest salary

SELECT MAX(salary) from instructor WHERE salary NOT IN (SELECT

MAX(salary) FROM instructor)

Page 84
97 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Noncorrelated Subqueries
SELECT MAX(salary) from instructor WHERE salary NOT IN (SELECT
MAX(salary) FROM instructor)

Page 85
98 Prof. Sarju S, Department of Computer Science and Engineering, SJCET
Palai
 Noncorrelated Subqueries

SELECT MAX(`salary`) from instructor WHERE salary NOT IN (SELECT

MAX(salary) FROM instructor)

► Here the subquery is SELECT MAX(salary) FROM instructor , you can execute
and substitute the result of that query e.g. if subquery return 95000 then the
outer query is reduced to

SELECT MAX(`salary`) from instructor where salary NOT IN (95000)

Page 86
99 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Correlated Subqueries

► The same query can written as a correlated subquery as

SELECT e.name, e.salary from instructor e where
2= (SELECT COUNT(`salary`) FROM instructor p
WHERE e.salary >= p.salary)
► In this example, the subquery is SELECT COUNT(`salary`) FROM
instructor p WHERE e.salary <= p.salary, you cannot swap it's value
for the outer query because it needs to be executed for each employee.
► A correlated subquery depends upon the outer query and cannot execute
in isolation
► A correlated subquery is much slower than a non-correlated subquery

Page 87
100 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Correlated Subqueries
SELECT e.name, e.salary from instructor e where
2= (SELECT COUNT(DISTINCT `salary`) FROM
instructor p WHERE e.salary >= p.salary)

Page 87
101 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Correlated Subqueries

► A correlated subquery depends upon the outer query and cannot execute
in isolation
► A correlated subquery is much slower than a non-correlated subquery
► Here is an example for a typical correlated subquery.
► Find all employees whose salary is above average for their department.

► SELECT employee_number, name

FROM employees emp
WHERE salary > ( SELECT AVG(salary) FROM employees
WHERE department = emp. department);

Page 87
102 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Correlated Subqueries

► In the above query the outer query is

► SELECT employee_number, name FROM employees emp

WHERE salary > ...

► and the inner query (the correlated subquery) is

► SELECT AVG(salary) FROM employees WHERE department =

emp.department

► In the above nested query the inner query has to be re-executed for each
employee.

Page 87
103 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Subqueries with EXISTS or NOT EXISTS

Page 104
 Subqueries with EXISTS or NOT
EXISTS
► Use EXISTS to identify the existence of a relationship without regard for the quantity.
► For example, EXISTS returns true if the subquery returns any rows, and [NOT]
EXISTS returns true if the subquery returns no rows.
► The EXISTS condition is considered to be met if the subquery returns at least one row.
► Syntax

Page 89
105 Prof. Sarju S, Department of
S,Prof.Smitha Computer
Jacob, Scienceofand
Department Engineering,
Computer ScienceSJCET Palai
and Engineering,
SJCET Palai
 Subqueries with EXISTS or NOT
EXISTS
► Lists the books with a book price less than 400
► SELECT bookname FROM book

WHERE EXISTS (SELECT * FROM book1 WHERE

book.bookid = book1.bookid AND book1.bookprice < 400);

Page 106 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 using Join operation-same result may be achehived

► Lists the books with a book price less than 400

SELECT bookname FROM book join book1 on
book1.bookid = book.bookid WHERE book1.bookprice < 400;

Page 107 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
PRODUCTS

SUPPLIER
 Subqueries with EXISTS or NOT
EXISTS
► Lists the suppliers with a product price less than 20
► SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT * FROM Products WHERE
Products.SupplierID = Suppliers.supplierID AND Price < 20);

Page 109 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Subqueries with EXISTS or NOT
EXISTS

first and last name of the

customers who placed atleast one
order.?

SELECT fname, lname FROM Customers WHERE

EXISTS (SELECT * FROM Orders WHERE
Customers.customer_id = Orders.c_id);

Page 110 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Subqueries with EXISTS or NOT
EXISTS

last and first name of the customers

who has not placed any order.

SELECT lname, fname FROM Customer WHERE NOT

EXISTS (SELECT * FROM Orders WHERE
Customers.customer_id = Orders.c_id);

Page 111 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Subqueries with EXISTS or NOT
EXISTS

Page 89
112 Prof. Sarju S, Department of
S,Prof.Smitha Computer
Jacob, Scienceofand
Department Engineering,
Computer ScienceSJCET Palai
and Engineering,
SJCET Palai
 Views (Virtual Tables) in SQL

Page 113
 Views in DBMS?

Page 91
114
 Why Views in
DBMS?
► Views are a special version of tables in SQL.
► They provide a virtual table environment for various complex operations.
► You can select data from multiple tables, or you can select specific data
based on certain criteria in views.
► It does not hold the actual data; it holds only the definition of the view in
the data dictionary.
► The view is a query stored in the data dictionary, on which the user
can query just like they do on tables.
► It does not use the physical memory, only the query is stored in the data
dictionary.
► It is computed dynamically, whenever the user performs any query on it.
► Changes made at any point in view are reflected in the actual base table.

Page 92
115 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Why Views in
DBMS?
► In COMPANY database we may frequently issue queries that retrieve the
employee name and the project names that the employee works on.
► Rather than having to specify the join of the three tables EMPLOYEE,
WORKS_ON, and PROJECT every time we issue this query, we can define a
view that is specified as the result of these joins.
► Then we can issue queries on the view, which are specified as single table
retrievals rather than as retrievals involving two joins on three tables.
► We call the EMPLOYEE, WORKS_ON, and PROJECT tables the defining tables
of the view.

Page 93
116 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Views (Virtual Tables) in
SQL
► A view in SQL terminology is a single table that is derived from other tables
► These other tables can be base tables or previously defined views
► A view does not necessarily exist in physical form;
► It is considered to be a virtual table, in contrast to base tables,
► whose tuples are always physically stored in the database.
► This limits the possible update operations that can be applied to views, but
it does not provide any limitations on querying a view.

Page 94
117 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Views (Virtual Tables) in
SQL

Page 95
118 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Create a SQL
VIEW
► The syntax to create a VIEW is as follows:

CREATE VIEW Name AS

Select column1,
Column2...Column N
From tables
Where conditions;

Page 96
119 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL CREATE VIEW
Examples
► The following SQL creates a view that shows all customers from Brazil:

CREATE VIEW [Brazil

Customers] AS
SELECT CustomerName,
ContactName
FROM Customers
WHERE Country = 'Brazil';

SELECT * FROM [Brazil Customers];

Page 97
120 Prof. Sarju S, De
S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL CREATE VIEW
Examples
► Problem: creates a view that shows all books of
programming category:

CREATE VIEW programming_books AS

SELECT bookname, bcategory ,isbn FROM book
WHERE bcategory = 'programming';

Page 97
121 Prof. Sarju S, De
S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
P
a
g
e
9
DELETING VIEWS
8

► We have learned about

creating a View, but what if a
created View is not needed
any more?
DROP VIEW view_name;
► SQL allows us to delete an
existing View. DROP VIEW [Brazil Customers];
► We can delete or drop a
View using the DROP
statement.

Page 122 Prof. Sarju S, Department of Computer Science and Engineering, SJCET
Palai
 UPDATE
View
► The SQL UPDATE VIEW command can be used to modify the data of a
view.
► All views are not updatable.
► An updatable view is one which allows performing a UPDATE command
on itself without affecting any other table.

Page 123 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 UPDATE
View
► When can a view be updated?
► The view is defined based on one and only one table.
► The view must include the PRIMARY KEY of the table based upon which the
view has been created.
► The view should not have any field made out of aggregate functions.
► The view must not have any DISTINCT clause in its definition.
► The view must not have any GROUP BY or HAVING clause in its definition.
► The view must not have any SUBQUERIES in its definitions.
► If the view you want to update is based upon another view, the later should
be updatable.
► Any of the selected output fields (of the view) must not use constants, strings
or value expressions.

Page 124 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 UPDATE
View
Name Description
Name of the
virtual table or
view_name
view where data
will be modified. UPDATE < view_name >
Name of the SET<column1>=<value1>,<col
column1,column2 columns of the
table. umn2>=<value2>,.....
Values for the WHERE <condition>;
columns which
value1,value2
are going to be
updated.
Condition or
condition
criteria.

Page 125 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 UPDATE View
Example

► To update the view 'agentview' with following conditions -

► 'commission' must be set at .13, 'working_area' must be 'London',
the following SQL statement can be used:
UPDATE agentview
SET commission=.13
WHERE working_area=’London’;

Page 126 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 SQL updatable views

► This statement bellow creates a updatable view 'countryagent –agents from

Brisban':
CREATE VIEW countryagent
AS SELECT *
FROM agents
WHERE working_area='Brisban';

Page 127 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Uses of a View

► A good database should contain views due to the given reasons:

► Restricting data access
► Views provide an additional level of table security by restricting access to a
predetermined set of rows and columns of a table.
► Hiding data complexity
► A view can hide the complexity that exists in a multiple table join.
► Simplify commands for the user
► Views allows the user to select information from multiple tables without
requiring the users to actually know how to perform a join.
► Store complex queries
► Views can be used to store complex queries.
►

Page 128 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Uses of a View

► Rename Columns –
► Views can also be used to rename the columns without affecting the base
tables provided the number of columns in view must match the number of
columns specified in select statement.
► Thus, renaming helps to hide the names of the columns of the base tables.
► Multiple view facility
► Different views can be created on the same table for different users.

Page 129 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Assertions

Page 130
 Assertion
s
CONSTRAINTS

BUILT-IN ADDITIONAL TYPES

UNIQUE ASSERTIONS

NOT NULL

ENITITY INTEGRITY

REFERENTIAL INTEGRITY

Page 131 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Assertion
s
► Creation of assertions are constraints that are not associated with only one table.
► An assertion statement should ensure a certain condition will always exist in
the database.
► DBMS always checks the assertion whenever modifications are done in the
corresponding table.
► We can use Assertions when we know that the given particular condition is
always true.
► Assertions are not linked to specific table or event.
► It performs task specified or defined by the user.
► When the SQL condition is not met then there are chances to an entire table or
even Database to get locked up.

Page 132 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Assertion
s
► 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.
► Where a constraint cannot be expressed in these forms, we use an
assertion, e.g.
► Ensuring the sum of loan amounts for each branch is less than the sum of all
account balances at the branch.
► Ensuring every loan customer keeps a minimum of $1000 in an account.
► An assertion in SQL takes the form,
► create assertion assertion-name check predicate

Page 133 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Assertion
s
► Confirm that the employee salary should be between 5000 and 300000

create assertion salarycheck check (not exists(select empid

from employee e where e.salary>300000 or e.salary<5000))

Page 134 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Assertions
Example
► Whether the salaries of an employees must not be greater than the salary of
the manager of the corresponding department
CREATE ASSERTION Salary_check CHECK(
NOT EXISTS (SELECT * FROM Employee e, Employee m, Department D
WHERE e.salary>m.salary
AND e.dno = m.dno
AND d.mgr_ssn=m.ssn
))

Page 135 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Assertion
s
Branch (branch_name, branch_city, assets)
Loan (loan_number, amount,branch_name)
Account (account_number, balance,branch_name)
CREATE TABLE Branch(
branch_name varchar(50)PRIMARY KEY,
branch_city varchar(50),
assets double);
………………………………………………………………………………………..
CREATE TABLE Loan(
loan_number int(11) PRIMARY KEY,
branch_name varchar(50),
amount double
FOREIGN KEY(branch_name) REFERENCES Branch(branch_name));
……………………………………………………………………………………..
CREATE TABLE Account(
account_number int(11) PRIMARY KEY,
branch_name varchar(50),
balance double
FOREIGN KEY(branch_name) REFERENCES Branch(branch_name));
Page 136 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Assertion
s
INSERT INTO Branch VALUES('SBT','Pala',500000);
INSERT INTO Branch VALUES('SIB','Kottayam',240000);
INSERT INTO Branch VALUES('AX','Pala',160000);
………………………………………………………………………………………..
INSERT INTO Loan VALUES(123,'SBT',50000);
INSERT INTO Loan VALUES(100,'SIB',40000);
INSERT INTO Loan VALUES(101,'SBT',60000);
INSERT INTO Loan VALUES(106,'SIB',440000);
INSERT INTO Loan VALUES(108,'SBT',410000);
……………………………………………………………………………………..
INSERT INTO Account VALUES(222,'SBT',150000);
INSERT INTO Account VALUES(333,'SIB',140000);
INSERT INTO Account VALUES(111,'SBT',160000);
INSERT INTO Account VALUES(444,'SIB',340000);

Page 137 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Assertions
Example
► Ensuring the sum of loan amounts for each branch is less than the sum of all
account balances at the branch.
CREATE ASSERTION SUM_CONSTRAINT CHECK (NOT EXISTS
(SELECT * FROM BRANCH WHERE (SELECT SUM(AMOUNT)FROM LOAN
WHERE LOAN.BRANCH_NAME = BRANCH.BRANCH_NAME )>=
(SELECT SUM (AMOUNT) FROM ACCOUNT WHERE LOAN.BRANCH_NAME =
BRANCH.BRANCH_NAME )))

Page 138 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Assertion
s
► When an assertion is created, the system tests it for validity.
► If the assertion is valid, any further modification to the database is
allowed only if it does not cause that assertion to be violated.
► This testing may result in significant overhead if the assertions are
complex. Because of this, the assert should be used with great care.

Page 141 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Triggers

Page 142
 What is
Triggers?
► Triggers are SQL codes that are automatically executed in response to a
certain events on a particular table.
► These are used to maintain the integrity of the data.

What is Trigger?

Page 143 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 What is
Triggers?

1. New User added into Database

2. Manually send the welcome email Send to user

What happened if N number of users added to the database?

Page 144 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Trigge
r
► Triggers are stored programs, which are automatically executed or fired
when some events occur.

► Triggers are, in fact, written to be executed in response to any of the

following events −
● A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
● A database definition (DDL) statement (CREATE, ALTER, or DROP).
●A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or
SHUTDOWN).

Page 145 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Trigge
r
► A trigger is a statement that is automatically executed by the system as a
side effect of a modification to the database.
► We need to
► Specify the conditions under which the trigger is executed.
► Specify the actions to be taken by the trigger.
► In SQL, triggers are database objects, actually, a special kind of stored
procedure, which “reacts” to certain actions we make in the database.
► The main idea behind triggers is that they always perform an action in
case some event happens.

Page 146 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Trigger
Syntax
create trigger [trigger_name]
[before | after]
{insert | update | delete} on [table_name]
[for each row|for each column]
[trigger_body]
[trigger_name]: Creates or replaces an existing trigger with the trigger_name.
[before | after]: This specifies when the trigger will be executed.
{insert | update | delete}: This specifies the DML operation.
on [table_name]: This specifies the name of the table associated with the trigger.
[for each row]: This specifies a row-level trigger, i.e., the trigger will be executed
for each row being affected.
[trigger_body]: This provides the operation to be performed as trigger is fired

Page 147 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Trigge
r
► BEFORE and AFTER of Trigger:
► BEFORE triggers run the trigger action before the triggering statement is run.
► AFTER triggers run the trigger action after the triggering statement is run.

► New is a keyword refers to the row that is getting affected

Page 148 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Trigge
r
► BEFORE and AFTER of Trigger:
► BEFORE triggers run the trigger action before the triggering statement is run.
► AFTER triggers run the trigger action after the triggering statement is run.

Page 149 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Trigge
r
► Drop Trigger:
► Remove the triggers from the database

Page 150 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Trigge
r
► Display Trigger:
► Show or display the triggers from the database

Page 151 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Trigger Example

CREATE TABLE student_detail (

stud_id int NOT NULL,
Lasinserted Time,
PRIMARY KEY (stud_id) );
TRIGGER
DELIMITER //
Create Trigger after_insert_details
AFTER INSERT ON student ► DELIMITER is the
FOR EACH ROW marker for the end of
each commmand you
BEGIN send to the mysql
INSERT INTO student_detail VALUES (new.rno,CURTIME()); command line.
END // ► Delimiter // can be used
to change the
DELIMITER ; statement from
semicolon to //

Page 152 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Trigger Example

► Example:
► Given Student Report Database, in which student marks assessment is
recorded.
► In such schema, create a trigger so that the total and average of specified
marks is automatically inserted whenever a record is insert.
► Here, as trigger will invoke before record is inserted so, BEFORE Tag can be used.

Page 153 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Trigger Example

create trigger stud_marks

before INSERT
on Student
for each row
set Student.total = Student.subj1 + Student.subj2 +
Student.subj3, Student.per = Student.total * 60 / 100;

Page 154 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Trigger Example

Page 155
 Trigge
r
► Create a table customer_details (cust_id , cust_name, address).
create table customer_details(cust_id int,cust_name varchar(30),address varchar(30));
► Create a trigger whenever a new record is inserted in the customer_details table.

create trigger tri

after insert on customer_details
for each row
begin
dbms_output.put_line('A row is inserted');
end;
insert into customer_details(cust_id,cust_name,address)values(100,’Alen’,'abc’);
NB: The DBMS_OUTPUT package enables you to send messages from stored procedures and packages. The
package is especially useful for displaying PL/SQL debugging information.

Page 156 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Trigge
r
► Create a table emp_details (empid (unique), empname, salary)
► Create a trigger to display a message when a user enters a value > 20000 in the
salary field of emp_details table.
create trigger trig
after insert on emp_details
for each row
when(new.salary>20000)
begin
dbms_output.put_line('Salary is greater than 20000');
end;
insert into emp_details(empid,empname,salary)values(2,'alen',30000);

Page 157 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
example

► CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));

► CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;
► The CREATE TRIGGER statement creates a trigger named ins_sum that is
associated with the account table.
► The keyword BEFORE indicates the trigger action time. In this case, the trigger
activates before each row inserted into the table.
► The other permitted keyword here is AFTER. Within the trigger body, the OLD
and NEW keywords enable you to access columns in the rows affected by a
trigger. OLD and NEW are MySQL extensions to triggers; they are not case-
sensitive.
► OLD-REFER TO A VALUE FROM ORIGINAL ROW
► NEW-REFER TO A VALUE IN THE NEW ROW
Page 158 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
For each row-new and old

► The statement following FOR EACH ROW defines the trigger body;
► that is, the statement to execute each time the trigger activates, which occurs
once for each row affected by the triggering event.
► In the example, the trigger body is a simple SET that accumulates into a user
variable the values inserted into the amount column.
► The statement refers to the column as NEW.amount which means “the value of
the amount column to be inserted into the new row.”
► To use the trigger, set the accumulator variable to zero, execute an INSERT
statement, and then see what value the variable has afterward:

Page 159 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
example

Page 160 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
example

Page 161 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
example

Page 162 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Example-inserting data into tbstudent

Page 163 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Example-inserting data into tbstudent

Page 164 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Example-after update

Page 165 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Example-after delete

Page 166 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Trigger Uses

Page 167
 Module 3

► SQL DML (Data Manipulation Language) - SQL queries on single and multiple
tables, Nested queries (correlated and non-correlated), Aggregation and
grouping, Views, assertions, Triggers,
► SQL data types.
► Physical Data Organization - Review of terms: physical and logical records,
blocking factor, pinned and unpinned organization. Heap files, Indexing,
Single level indices, numerical examples, Multi-level indices, numerical
examples, B-Trees & B+-Trees (structure only, algorithms not required),
Extendible Hashing, Indexing on multiple keys – grid files.

Page 3168 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Physical Data Organization

Page 169
 Outline

► It’s all about disks! That’s why we always draw databases as

► Databases typically stored physically as files of records on magnetic disks
► Three primary methods for organizing file records on disk
► Unordered records
► Ordered records

► Hashed records

► The DBMS software can then retrieve, update and process this data as
needed
► Storing data on a disk
► Record Layout
► Block layout
Page 170 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Storage hierarchy

► primary storage:
► Fastest media but volatile
► cache, main memory
► Fast access and limited storage capacity
► secondary storage: next level in hierarchy, non-volatile, moderately fast access
time
► also called on-line storage
► E.g. flash memory, magnetic disks
► tertiary storage: lowest level in hierarchy, non-volatile, slow access time
► also called off-line storage
► E.g. magnetic tape, optical storage

Page 171 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Storage hierarchy

Registers
Why a hierarchy?
Cache

Memory

Disk

Tape

Page 172 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Storage Organization of
Databases
► Persistent data-large amount of data must persist over a long period. part
of this data is accessed and processed repeatedly during this period
► Transient data persists only for a limited time.
► Exists only during program execution
► Most databases are stored permanently on magnetic disk storage
► File organization
► Determines how records are physically placed on the disk
► Determines how records are accessed
► primary File organization
► determines how the file records are physically placed on disk and hence
how the records can be accessed

Page 175 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Primary file Organization of
Databases
► several primary File organization

► Heap file(unordered file)-places the records on disk in no particular

order by appending new records at the end of the file.
► Sorted file(sequential file)-keeps the records ordered, by the value of
the particular sort field

► Hashed file uses a hash function applied to a particular field called hash
key to determine the placement of the record on disk.

► B-trees-uses tree structures

Page 176 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Secondary storage devices
► Hard disk drive
► Magnetic disks are used to store large amount of data.
► Basic unit of data stored on the disk is a single bit of information
► Bits (ones and zeros)
► Grouped into bytes or characters
► Disk capacity measures storage size
► Disks may be single or double-sided
► Concentric circles called tracks
► Tracks divided into blocks or sectors
► Disk packs
► Cylinder

Page 177 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Secondary storage devices
► Hard disk drive
► Magnetic disks are used to store large amounts
of data.
► Basic unit of data stored on the disk is a single
bit of information
► Bits (ones and zeros)
► Grouped into bytes or characters
► Disk capacity measures storage size
► Disks may be single or double-sided
► Concentric circles called tracks
► Tracks divided into blocks or sectors
► Disk packs-Cylinder

Page 178 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Hard disk

Page 179 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 A typical hard drive

Figure 16.1 (a) A single-sided disk with read/write hardware

(b) A disk pack with read/write hardware

Page 181 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 A typical hard drive

Figure 16.2 Different sector organizations on disk (a) Sectors subtending a fixed angle
(b) Sectors maintaining a uniform recording density

Page 182 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Performance measures of Disk
► Access time – the time it takes from when a read or write request is issued to when data
transfer begins. Consists of:
► Seek time – time it takes to reposition the arm over the correct track.
► Average seek time is 1/2 the worst case seek time.
► Would be 1/3 if all tracks had the same number of sectors, and we ignore the time to
start and stop arm movement
► 4 to 10 milliseconds on typical disks
► Rotational latency – time it takes for the sector to be accessed to appear under the head.
► Average latency is 1/2 of the worst case latency.
► 4 to 11 milliseconds on typical disks (5400 to 15000 r.p.m.)
► Data-transfer rate – the rate at which data can be retrieved from or stored to the disk.
► 25 to 100 MB per second max rate, lower for inner tracks
► Multiple disks may share a controller, so rate that controller can handle is also important
► E.g. SATA: 150 MB/sec, SATA-II 3Gb (300 MB/sec)
► Ultra 320 SCSI: 320 MB/s, SAS (3 to 6 Gb/sec)
► Fiber Channel (FC2Gb or 4Gb): 256 to 512 MB/s

Page 185 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Placing File Records on Disk

Page 186
 Physical files and logical file

► Logical files
► Logical files do not contain data.
► They contain a description of records that are found in one or more physical files.
► A logical file is a view or representation of one or more physical files.
► Logical files that contain more than one format are referred to as multi-format
logical files.
► Physical files:
► Physical files contain the actual data that is stored on the system and a description
of how data is to be presented to or received from a program.
► They contain only one record format and one or more members.

Page 187 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Physical files and logical file

Page 188 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Data organization

► Data organization refers to the organization of data of a file into records ,

blocks and access structures.
► Access method refers to the group of operations that can be applied to easily
access the data.
► The database is stored as a collection of files in Disk.
► Disk data is stored in the form of blocks
► To read data stored in blocks ,entire block is read and
copied to RAM
► Blocks can be
► fixed sized block

► Variable sized blocks

Page 189 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Files, Fixed-Length Records, and Variable-Length
Records
► The database is stored as a collection of files.
► Each file is a sequence of records.
► A record is a sequence of fields.
► Records usually describe entities and their attributes.

► A collection of field names and their corresponding data types

constitutes a record type or record format definition
► In many cases, all records in a file are of the same record type.
► If every record in the file has exactly the same size (in bytes), the file is said to
be made up of fixed-length records.
► If different records in the file have different sizes, the file is said to
be made up of variable-length records.

Page 190 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Fixed-length
fields
► All field lengths and offsets are constant
► Computed from schema, stored in the system catalog
► Example:
CREATE TABLE User(uid INT, name CHAR(20), age INT, salary FLOAT);
0 4 24 28 36
154 Ram (padded with space) 35 25632.5

Page 191 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Files, Fixed-Length Records, and Variable-Length
Records

► Fixed length record with 71 bytes

► The file records are of the same record type, but one or more of the
fields are of varying size (variable-length fields).
► For example, the Name field of EMPLOYEE can be a variable-length
field.

► 2 Variable length record,with seperator

Page 192 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Files, Fixed-Length Records, and Variable-Length Records

► Variable length record with with three types of separator,(null values allowed)

Page 193
 Spanned versus Unspanned Records

► The records may be of fixed size or may be variable in length. One block may
contain multiple records.
► Unspanned Records :
► If a record is stored in a single block, it is called an unspanned record.

► When many records are restricted to fit within one block due to their small
size then such records are called Unspanned Records.
► Spanned Records :
► Part of the record is stored in one block and the rest in another
block it is spanned record
► When (portions of ) a single record may lie in different blocks, due to their
large size, then such records are called Spanned Records.

Page 196 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 UnSpanned Records

Page 197 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Spanned Records

Page 198 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Spanned versus Unspanned Records

Page 199 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Record Blocking –bfr for Fixed length
Records
► Blocking factor rate (bfr) is the number of records fit per block,
► block size is B bytes

► fixed-length records of size R bytes

► For spanned records,

► bfr = ⎡(B/R⎤ (ceiling function) rounds the value x up to the next

integer.
► For unspanned records

► bfr = ⎣B/R⎦ records per block (floor function) rounds down the
number x to an integer.

Page 200 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Record Blocking and Spanned versus Unspanned Records

► Suppose that the block size is B bytes.

► For a file of fixed-length records of size R bytes, with B ≥ R,
► we can fit bfr = ⎣B/R⎦ records per block

► where the ⎣(x)⎦ (floor function) rounds down the number x to an integer.

► The value bfr is called the blocking factor rate for the file.
► In general, R may not divide B exactly, so we have some unused space in each
block equal to B − (bfr * R) bytes

► To utilize this unused space, we can store part of a record on one block and the
rest on another.

Page 201 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Number of Blocks for spanned organization

Page 202
 Number of Blocks for unspanned organization

bfr = ⎣B/R⎦ records per block

Page 203
 Record Blocking - Unspanned
Records
► If records are not allowed to cross block boundaries, the organization is called
unspanned.
► This is used with fixed-length records having B > R because it makes each
record start at a known location in the block, simplifying record processing.
► For variable-length records, either a spanned or an unspanned
organization can be used.
► If the average record is large, it is advantageous to use spanning to reduce the
lost space in each block.

Page 206 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Find the blocking factor and number of blocks required to store data in a disk?

bfr = ⎣B/R⎦ records per block

Page 207 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 example1

Page 208
 Example 2

Page 209
PINNED AND UN PINNED
RECORDS
► A record is said to be pinned down or pinned record if there exists a
pointer to it somewhere in the database.
► For example, when a table look-up approach is used to locate a record,
the table contains a pointer to the record, and the record becomes
pinned down.
► The pinned records cannot be moved without reason randomly because
in that case the pointers pointing to these records will dangle.
► Any movement of pinned records should be associated with appropriate
modification of the pointers.
► In fact, the file organization method which maintains pointers to pinned
records appropriately modifies these pointers whenever the records are
inserted or deleted.

Page 212 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
PINNED AND UN PINNED
RECORDS

► Pinned Records and Blocks

► 'pinned' means if the block cannot be written back to disk safety
► indicated by a bit located in the header of the block
► To write a pinned block back to disk we need to 'unpin' the block
► Unswizzle any pointers associated to it.

Page 213 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
PINNED AND UN PINNED PAGES

Page 214 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Module 3

► SQL DML (Data Manipulation Language) - SQL queries on single and multiple
tables, Nested queries (correlated and non-correlated), Aggregation and
grouping, Views, assertions, Triggers,
► SQL data types.
► Physical Data Organization - Review of terms: physical and logical records,
blocking factor, pinned and unpinned organization. Heap files, Indexing,
Single level indices, numerical examples, Multi-level indices, numerical
examples, B-Trees & B+-Trees (structure only, algorithms not required),
Extendible Hashing, Indexing on multiple keys – grid files.

Page 3215 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Indexing

Page 216
Heap files

Page 3217 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Heap files

Page 3218 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Heap Files

Page 219
 Why Indexing is used?

• Indexing helps you to reduce the total number of I/O operations needed to retrieve that data.

Page 220 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Indexing

► Indexing is used to optimize the performance of a database by minimizing the

number of disk accesses required when a query is processed.
► The index is a type of data structure. It is used to locate and access the data in
a database table quickly.
► Indexing is a data structure technique which allows you to quickly retrieve
records from a database file.
► An Index is a small table having only two columns.
► The first column comprises a copy of the primary or candidate key of a table.
► Its second column contains a set of pointers for holding the address of the
disk block where that specific key value stored.

Page 221 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Index Structures

► The index structures are additional files on disk that provides an alternative
ways to access the records without affecting the physical placement of
records in the primary data file on disk,
► Access of records are based on the indexing fields that are used to
construct the index.
► Any fields /attribute of the table can be used to create an index.
► A variety of indexes are possible ,each of them uses a particular data structures
to speed up the search.

Page 222 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Structure of an Index in Database

Page 223 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Indexing methods in Database

Page 224 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Types of Indexing

Page 225 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Primary Indexing

Page 226
 Primary Indexing
► Primary Indexing is applied to an ordered file of records
► If the index is created on the basis of the primary key of the table, then
it is known as primary indexing.
► These primary keys are unique to each record and contain 1:1 relations between
the records.
► Primary Index is an ordered file that is fixed in length size with two fields.
► The first field is the same as the primary key and the second, field is pointed to
that specific data block
► As primary keys are stored in sorted order, the performance of the
searching operation is quite efficient.
► There is one index entry in the index file for each block
► The primary index can be classified into two types: Dense index and Sparse
index.
Page 227 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Primary Indexing

Page 228 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Page 205 Presentation title
 Primary Indexing

Sparse Index

1
5
9
13
Index Table

No of entries in the index table = No of blocks in the Hard Disk

Search Time = log2 N+ 1 where N = No entries in the index table

Page 230 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Dense Index

► In a dense index, a record is created for every search key valued in the database.
► Dense indexing helps you to search faster but needs more space to store index records.
► In dense indexing, records contain search key value and points to the real record on the disk.

Page 232 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Sparse Index

► The sparse index is an index record that appears for

only some of the values in the file.
► Sparse Index helps you to resolve the issues of dense
indexing.
► In sparse indexing technique, a range of index
columns stores the same data block address, and
when data needs to be retrieved, this block address
will be fetched.
► Sparse indexing method stores index records for
only some search key values.
► It needs less space, less maintenance overhead for
insertion, and deletions but it is slower compared to the
dense index for locating records.

Page 234 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Examples

Page 235 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Examples

Page 236 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Examples

Page 237 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Examples

Page 238 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Primary Index

► Suppose that we have an ordered file with 300,000 records stored on a disk
with block size 4,096 bytes. File records are of fixed size and are unspanned,
with record length 100 bytes. How many blocks are needed for the file?
Approximately, how many block accesses are required for a binary search in
this file?
► If Index table entry = 15 B ( Key + Pointer). how many block accesses are
required for a binary search in this file?

Page 239 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Without Index

► Ordered file with records r = 300000

► Disk block size = B = 4096 bytes
► File records are fixed size and unspanned
► Record length = R = 100 bytes
► Blocking factor bfr = [B/R] (floor) = 4096/100 = 40 records per block
► The number of blocks needed for the file = [r/bfr] (upper ceiling) =
300000/40= 7500 blocks
► A binary search on the data file = log2(7500) (upper ceiling) = 13 block
access

Page 240 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 With Index

► total bytes in each entry of index file = 15 bytes

► bfr for index file is 4096/15 = 273(flooring)
► total number of index entries = total no of blocks
► number of index blocks for the file = [number of blocks for the file /bfr] (ceiling)
► The number of index blocks = [7500/273] = 28
► To perform binary search on index file: log2(28) = 5
► To search for a record, we need one additional access to read the data block,
thus we need 5+1 = 6 block accesses using a binary search

Page 241 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Clustering Indexing

Page 242
 Clustering Index

► A clustered index can be defined as an ordered data file.

► In a clustered index, records themselves are stored in the Index and not pointers.
► Sometimes the index is created on non-primary key columns which may not be unique
for each record.
► In this case, to identify the record faster, we will group two or more columns to get the
Unique values and create an index out of them. This method is called a clustering
index.
► The records which have similar characteristics are grouped, and indexes are created for
these groups.
► suppose a company contains several employees in each department. Suppose we use
a clustering index, where all employees who belong to the same Dept_ID are
considered within a single cluster, and index pointers point to the cluster as a whole.
Here Dept_Id is a non-unique key.

Page 243 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Clustering Index

Page 244 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Clustering Index

► Clustering field is an ordered file with two fields

► first field is the clustering field of the data file
► second field is the block pointer
► There is one entry in the clustering index for each of the distinct values of the
clustering field.
► It is a non-dense index/sparse index
► Cluster index is a type of index that sorts the data rows in the table on their
key values.
► In the Database, there is only one clustered index per table.
► A clustered index defines the order in which data is stored in the table which
can be sorted in only one way. So, there can be only a single clustered index
for every table

Page 245 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Secondary Indexing

Page 246
 Secondary Index
► Data file is unsorted
► secondary index may be created on a field that is a
► candidate key and has a unique value in every record
► or on a non key field with duplicate values
► Index file is an ordered file with two fields
► First field is a key or non key
► second field is either a block pointer or record pointer
► It is an example of dense index
► There will be numerous records in the data file that have the same value for
indexing field
► The block pointer in index entry points to a disk block, which contains a set
of record pointers
► Each record pointer in that disk block points to one of the data file records
Page 247 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Secondary Index

► In secondary indexing, to reduce the size of mapping, another level of indexing is

introduced.
► In this method, the huge range for the columns is selected initially so that the
mapping size of the first level becomes small.
► Then each range is further divided into smaller ranges.
► The mapping of the first level is stored in the primary memory, so that address
fetch is faster.
► The mapping of the second level and actual data are stored in the secondary
memory (hard disk)

Page 248 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Secondary Index
Secondary Index

Searching Mechanism

► If you want to find the record of roll 111 in

the diagram, then it will search the highest
entry which is smaller than or equal to 111 in
the first level index. It will get 100 at this
level.
► Then in the second index level, again it does
max (111) <= 111 and gets 110. Now using
the address 110, it goes to the data block
and starts searching each record till it gets
111.
► This is how a search is performed in this
method. Inserting, updating or deleting is
also done in the same manner

Page 249 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Secondary Index

► A secondary index provides a logical ordering on the records by the indexing

field.
► The block pointer in the index entry points to a disk block, which contains a
set of record pointers
► Record pointer pointing to each of the repeating blocks
► Each record pointer in that disk block points to one of the data file records
► If we access the records in order of the entries in the secondary index , we get
them in order of the indexing field
► The primary and clustering indexes assume that the field used for the physical
ordering of records in the file is the same as the indexing field

Page 251 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Example

UNORDERED FILESECONDARY INDEXING

It is an example of a dense index

Page 252 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Secondary Index

Page 253
 Secondary Index

► Consider a file of 16384 records. Each record is 32 bytes long and its key field is
of size 6 bytes. The file is ordered on a non-key field, and the file organization
is unspanned. The file is stored in a file system with a block size of 1024 bytes,
and the size of a block pointer is 10 bytes. If the secondary index is built on the
key field of the file, and a multi-level index scheme is used to store the
secondary index, find the number of first-level and second-level blocks in the
multi-level index.

Page 254 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Secondary Index

► Number of records in file r= 16384

► Record size R= 32 bytes
► Block Size on file system B= 1024 bytes
► Key Size = 6 bytes
► Size of Block Pointer = 10 bytes
► Size of a record for index Entry = 10 + 6 = 16
► Number of blocks in first level = ceil(Number of records in file*recordsize for index
file)/Block Size))
= (16384 * 16)/(1024)
= 256

Page 255 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Secondary Index

► In second level, there will be 256 * 16 entries.

Number of blocks in second level = (Number of entries) / (Block Size)
= (256 * 16) / 1024
=4

Page 256 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 summary

Page 257
 summary

► Primary Index is an ordered file which is fixed length size with two fields.
► The primary Indexing is also further divided into two
types 1)Dense Index 2)Sparse Index.
► In a dense index, a record is created for every search key valued in the
database.
► A sparse indexing method helps you to resolve the issues of dense Indexing.
► The secondary Index in DBMS is an indexing method whose search key
specifies an order different from the sequential order of the file.
► Clustering index is defined as an order data file.
► Multilevel Indexing is created when a primary index does not fit in memory.

Page 258 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Types of Indexing

Page 259
 Multilevel Indexing

Page 260
 Multilevel Index

► Multilevel Indexing in Database is created when a primary index does not fit
in memory.
► In this type of indexing method, you can reduce the number of disk accesses
to short any record and kept on a disk as a sequential file and create a sparse
base on that file.
► multilevel index considers the index file as the first (or base)level of a
multilevel index
► we can create a primary index for the first level;this index to the first level is
called the second level of the multilevel index
► second level is a primary index,ie second level has one entry for each block
of the first level.This process is repeated for the second level

Page 241
261 Prof. Sarju S, Department of
S,Prof.Smitha Computer
Jacob, Scienceofand
Department Engineering,
Computer ScienceSJCET Palai
and Engineering,
SJCET Palai
 Multilevel Index

► multilevel index considers the index file as the first (or base)level of a multilevel
index
► we can create a primary index for the first level; this index to the first level is
called the second level of the multilevel index
► second level is a primary index, i.e. second level has one entry for each block of
the first level. This process is repeated for the second level
► third level index ,which is a primary index for the second level, has an entry
for each second level block.
► repeat the preceding process until all the entries of some index level fit in
a single block.
► All index levels are physically ordered files

Page 263 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Multilevel Index

► To retain the benefits of using multilevel indexing, while reducing index

insertion and deletion problems, designers adopted a multilevel index called a
dynamic multilevel index that leaves some space in each of its blocks for
inserting new entries

Page 264 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Multilevel Index

Page 266 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Multilevel Index

Page 267 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Multilevel Index

Page 268 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Multilevel Index

Page 269 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Multilevel Index

Page 270 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Multilevel Index

Page 271 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 University questions

Page 272 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
bfr of Data File=floor(B/R)
Of Level 1 Index File
 University questions

Page 279
 University questions

Page 280 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 University questions

Page 281 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 University questions

Page 283 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 University questions

Page 284 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 University questions

Page 285 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 University questions

Page 286 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 University questions

Page 287 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 University questions

Page 288 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Dynamic Multilevel Indexes Using B-Tree

Page 289
 Dynamic Multilevel Indexes Using B-Tree
► Tree Structure: -
► A tree is formed of nodes. Each node except the root, has one parent node
and 0 or more child nodes
► The root node has no parent. A node with no child nodes is a leaf node
► A non-leaf node is an internal node. The level of a node is always one higher
than its parent. The level of the root node is 0.
► A subtree of a node consists of that node and all its descendant nodes
► If the leaf nodes are at different levels, it is an unbalanced tree
► B-tree nodes are kept 50-100% full
► Pointers to the data blocks are stored at internal and leaf nodes for B-trees
► Pointers to the data blocks are stored at leaf nodes for B+ trees

Page 290 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Dynamic Multilevel Indexes Using B-Tree
► Search trees:
► A search tree is a special type of tree used to guide the search for a record given the
value of one of the record’s fields.
► A search tree of order p is a tree such that each node contains p-1 search values
and p pointers.
► <P1, K1, P2, K2, ….Pq-1, Kq-1, Pq> where q<=p.
K1,K2,…Kq-1 are search values. Each Pi is a pointer to a child node or null
► We can use search tree as a mechanism to search for records on disk.
► Two constraints must hold all the time:
► Within each node K1 < K2 < K3 < X < Ki
► For all values of X in the subtree pointed at by Pi
We have; Ki-1 < X < Ki

Page 291 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Dynamic Multilevel Indexes Using B-Tree

Page 292 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Node in a search tree

Page 293 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Search trees

Page 294 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Dynamic Multilevel Indexes Using B-Tree

► P1 –Subtree
Pr-actual record/data file

Page 295 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Dynamic Multilevel Indexes Using B-Tree

► Balanced-leaf node at same level

► Order 3 ,p-1 search values in a node
► Values <5 in first subtree
► Values between 5&8 in second node,
► Values >8 in third node

Page 296 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Dynamic Multilevel Indexes Using B-Tree

Page 297 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Dynamic Multilevel Indexes Using B-Tree

Page 298 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Dynamic Multilevel Indexes Using B-Tree
► Fig. (b) illustrate a B-Tree with p = 3.
► All search key values are unique, as it is a key field
► If we use a B-Tree on a nonkey field, we must change the file pointer Pri to
point to a block or a cluster of blocks that contain the pointers to the records. –
► The B-Tree starts with a single root node (which is also a leaf node) at level 0
► Once the root node is full with p-1 search key values, we attempt to insert
another entry in the tree; the root node splits into two nodes at level 1.
► Only the middle value is kept at the root node, the rest of the values are split
evenly on other nodes
► When a nonroot node is full, and a new entry is inserted into it, that node is
split into two nodes at the same level, and the middle entry is moved into the
parent node, along with two pointers to the newly split nodes.

Page 299 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Dynamic Multilevel Indexes Using B+ Tree

► The B+ tree is a balanced binary search tree.

► It follows a multi-level index format.
► In the B+ tree, leaf nodes denote actual data pointers.
► B+ tree ensures that all leaf nodes remain at the same height.
► In the B+ tree, the leaf nodes are linked using a link list.
► Data pointers stored only at the leaf nodes.
► Therefore, a B+ tree can support random access as well as sequential access..
► Leaf nodes have an entry for every value of the search field, and a data
pointer to the record if search field is a key field

Page 300 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Structure of a B+ Tree

Page 301 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Structure of a B+ Tree

Page 302 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Structure of a B+ Tree
► In the B+ tree, every leaf node is at equal distance from the root node.
► The B+ tree is of the order n where n is fixed for every B+ tree.
► It contains an internal node and leaf node.
► Internal node
► An internal node of the B+ tree can contain at least n/2 record pointers except the root
node.
► At most, an internal node of the tree contains n pointers.
► Leaf node
► The leaf node of the B+ tree can contain at least n/2 record pointers and n/2 key values.
► At most, a leaf node contains n record pointer and n key values.
► Every leaf node of the B+ tree contains one block pointer P to point to next leaf node.

Page 303 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Node Structure of a B+ Tree

Page 304 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Properties of a leaf Node Structure of a B+ Tree

Page 305 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Properties of a leaf Node Structure of a B+ Tree

Page 306 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Properties of a Non leaf Node Structure of a B+ Tree

Page 307 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Structure of an internal node of the B+ Tree

Page 308 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Example of a B+ Tree

Page 309 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Example of a B+ Tree

Page 310 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Example of a B+ Tree

Page 311 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Example of a B+ Tree

Page 312 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Searching a record in a B+ Tree

► Suppose we have to search 55 in the below B+ tree structure. First, we will

fetch for the intermediary node which will direct to the leaf node that can
contain a record for 55.
► So, in the intermediary node, we will find a branch between 50 and 75 nodes.
Then at the end, we will be redirected to the third leaf node. Here DBMS will
perform a sequential search to find 55.

Page 313 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Inserting a record in a B+ Tree

► Suppose we want to insert a record 60 in the below structure. It will go to the 3rd leaf node after
55. It is a balanced tree, and a leaf node of this tree is already full, so we cannot insert 60 there.
► In this case, we have to split the leaf node, so that it can be inserted into tree without affecting the
fill factor, balance and order.
► The 3rd leaf node has the values (50, 55, 60, 65, 70) and its current root node is 50. We will split the
leaf node of the tree in the middle so that its balance is not altered. So we can group (50, 55) and
(60, 65, 70) into 2 leaf nodes.
► If these two has to be leaf nodes, the intermediate node cannot branch from 50. It should have 60
added to it, and then we can have pointers to a new leaf node.
Page 314 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Inserting a record in a B+ Tree

Page 315 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Deleting a record in a B+ Tree
► Suppose we want to delete 60 from the above example. In this case, we have to remove 60 from
the intermediate node as well as from the 4th leaf node too. If we remove it from the intermediate
node, then the tree will not satisfy the rule of the B+ tree. So we need to modify it to have a
balanced tree.

► After deleting node 60 from above B+ tree and re-arranging the nodes, it will show as follows:

Page 316 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Hashing

Page 317
 Hashing

 In DBMS, hashing is a technique to directly search the location of desired data on the disk
without using an index structure.
 Hashing method is used to index and retrieves items in a database as it is faster to search that
specific item using the shorter hashed key instead of using its original value.
 Data is stored in the form of data blocks whose address is generated by applying a hash
function in the memory location where these records are stored known as a data block or data
bucket.
► Data bucket – Data buckets are memory locations where the records are stored. It is also
known as a Unit Of Storage.
► Hash function: A hash function, is a mapping function that maps all the set of search keys to
the address where actual records are placed.

Page 318 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Internal Hashing

► For internal files, hashing is typically

implemented as a hash table through the use
of an array of records
► the array index range is from 0 to M – 1
► we have M slots whose addresses
correspond to the array indexes.
► We choose a hash function that transforms
the hash field value into an integer between 0
and M − 1.
► One common hash function is the
h(K) = K mod M function, which returns the
remainder of an integer hash field value K
after division by M;
this value is then used for the record
address.
► K=25 ,M=100 ,25 mod 100=25,125 mod
100=25

Page 319 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Internal Hashing

► K=21,,M=7
21 MOD 7=0
► 23 mod 7=2

Page 320 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Other Hashing Techniques

Page 321 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Other Hashing Techniques

Page 322 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
External Hashing

Page 331 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Extendible Hashing

Page 332 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Dynamic Hashing

Page 334 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Dynamic Hashing

Page 335 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Linear Hashing

Page 336 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Grid Files

Page 337 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
Grid Files

Page 338 Prof. Sarju S,Prof.Smitha Jacob, Department of Computer Science and Engineering,
SJCET Palai
 Thank You

Disclaimer - This document contains images/texts from various internet sources. Copyright belongs to the respective content creators. Document is compiled exclusively for
study purpose and shall not be used for commercial purpose.

Page 339 Prof.Sarju S,Prof.Smitha Jacob,Department of CSE,SJCET,Palai