CS2001T: DATABASE MANAGEMENT

SYSTEMS
CS2801T DBMS LAB

Dr.S.Veeramani/CSE, SNU, Chennai

 Course Objective

• To learn the fundamentals of data models and to represent a database

system using ER diagrams.
• To study SQL and relational database design.
• To understand the internal storage structures using different file and
indexing techniques which will help in physical DB design.
• To understand the fundamental concepts of transaction processing-
concurrency control techniques and recovery procedures.
• To have an introductory knowledge about the Storage and Query
processing Techniques
 Course Outcome

• CO1. Classify the modern and futuristic database applications

based on size and complexity
• CO2. Map ER model to Relational model to perform database
design effectively
• CO3. Write queries using normalization criteria and optimize
queries
• CO4. Compare and contrast various indexing strategies in different
database systems
• CO5. Appraise how advanced databases differ from traditional
databases.
 Textbooks / References

•TEXTBOOK:
•Abraham Silberschatz, Henry F. Korth, S. Sudharshan,
“Database System Concepts”,Sixth Edition, Tata McGraw
Hill, 2011.
•Ramez Elmasri, Shamkant B. Navathe, “Fundamentals of
Database Systems”, Sixth Edition, Pearson, 2011.
 Syllabus
CONCEPTUAL DATA MODELING
Database environment – Database system development lifecycle – Requirements collection –Database
design - Entity-Relationship model – Enhanced-ER model – UML class diagrams.
RELATIONAL MODEL AND SQL
Relational model concepts - Integrity constraints - SQL Data manipulation – SQL Data definition –
Views - SQL programming.
RELATIONAL DATABASE DESIGN AND NORMALIZATION
ER and EER-to-Relational mapping – Update anomalies – Functional dependencies – Inference rules –
Minimal cover – Properties of relational decomposition – Normalization (upto BCNF).
TRANSACTIONS
Transaction Concepts – ACID Properties – Schedules – Serializability – Concurrency Control – Need for
Concurrency – Locking Protocols – Two Phase Locking – Deadlock – Transaction Recovery - Save Points
– Isolation Levels – SQL Facilities for Concurrency and Recovery.
NO-SQL DATABASES
No-SQL: CAP theorem – Document-based: MongoDB data model and CRUD operations; Column-
based: Hbase data model and CRUD operations.
 UNIT II

RELATIONAL MODEL AND SQL

Relational model concepts - Integrity constraints - SQL Data manipulation – SQL Data
definition –Views - SQL programming.
• CREATE TABLE table_name( Col_name1 datatype(), Col_name2 datatype(),… Col_namen
datatype(), );
• E.g
• CREATE TABLE DDL ( id int, DDL_Type varchar(50), DDL_Value int );

• ALTER TABLE table_name ADD Col_name datatype()...;

• E.g)
• ALTER TABLE DDL ADD COLUMN DDL_Example varchar(50);

• DROP Table table_name;

• TRUNCATE TABLE table_name;

• RENAME table table_name to new_table_name

• E.g)
• RENAME table DDL to DD;
• INSERT INTO Table_Name VALUES();
• E.g)
• INSERT INTO DDL (id, DDL_Type, DDL_Value) VALUES (2, 'DML', 123);

• UPDATE <table name> set to(calculation);

• E.g)
• UPDATE ddl SET DDL_Value = 555 WHERE DDL_Type = 'DML';
• DELETE FROM <table_name>
• E.g)
• DELETE FROM DDL;

• SELECT column1, column2, ...

FROM table_name
WHERE condition;
• E.g)
• SELECT * FROM Customers
WHERE Country='Mexico’;

• SELECT * FROM Customers

WHERE CustomerID > 80;
 SQL Integrity Constraints
• SQL constraints are used to specify rules for the data in a table.
• Constraints are used to limit the type of data that can go into a
table
•NOT NULL - Ensures that a column cannot have a NULL value
•UNIQUE - Ensures that all values in a column are different
•PRIMARY KEY - A combination of a NOT NULL and UNIQUE.
•FOREIGN KEY - Prevents actions that would destroy links between
tables
•CHECK - Ensures that the values in a column satisfies a specific
condition
•DEFAULT - Sets a default value for a column if no value is specified
• Rollback
• UPDATE STUDENT
• SET NAME = ‘Sherlock’
• WHERE STUDENT_NAME = ‘Jolly’;
• Rollback;
 Transaction Control Language

The TCL commands are:

COMMIT
ROLLBACK
SAVEPOINT

UPDATE STUDENT
SET NAME = ‘Sherlock’
WHERE NAME = ‘Jolly’;

COMMIT;
UPDATE NAME
SET NAME= ‘Rossie’
WHERE marks= 70;

SAVEPOINT A;

INSERT INTO STUDENT

VALUES (‘Zack’, 76);

Savepoint B;

INSERT INTO STUDENT

VALUES (‘Bruno’, 85);

Savepoint C;

SELECT *
FROM STUDENT;
• Rollback to B;

• Rollback to A;
• Rollback
• UPDATE STUDENT
• SET NAME = ‘Sherlock’
• WHERE STUDENT_NAME = ‘Jolly’;
• Rollback;
 SQL Integrity Constraints
• SQL constraints are used to specify rules for the data in a table.
• Constraints are used to limit the type of data that can go into a
table
•NOT NULL - Ensures that a column cannot have a NULL value
•UNIQUE - Ensures that all values in a column are different
•PRIMARY KEY - A combination of a NOT NULL and UNIQUE.
•FOREIGN KEY - Prevents actions that would destroy links between
tables
•CHECK - Ensures that the values in a column satisfies a specific
condition
•DEFAULT - Sets a default value for a column if no value is specified
 NOT NULL
• CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
 UNIQUE

• CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
 PRIMARY KEY

• CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
FOREIGN KEY
 FOREIGN KEY
• Persons Table(PersonID, Name, Age)

• CREATE TABLE Orders (

OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
 CHECK

• CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
 DEFAULT

• CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
CREATE TABLE Geeks (ID int NOT NULL,
Name varchar(255),Age int, Location varchar(255) DEFAULT 'Noida’);

INSERT INTO Geeks VALUES (4, 'Mira', 23, 'Delhi');

INSERT INTO Geeks VALUES (5, 'Hema', 27,DEFAULT);
INSERT INTO Geeks VALUES (6, 'Neha', 25, 'Delhi');
INSERT INTO Geeks VALUES (7, 'Khushi', 26,DEFAULT);
 Set Operations

• Union
• Union all
• Intersection
• Minus
SELECT * FROM First
UNION
SELECT * FROM Second;
SELECT * FROM First
UNION ALL
SELECT * FROM Second;
SELECT * FROM First
INTERSECT
SELECT * FROM Second;
SELECT * FROM First
MINUS
SELECT * FROM Second;
 Aggregate Function
• An aggregate function in SQL returns one value after calculating multiple values
of a column

• Count()
• Sum()
• Avg()
• Min()
• Max()
SELECT AVG (salary) FROM employees;

SELECT MIN(salary) FROM employees;

SELECT MAX (salary) FROM employees;

SELECT SUM (salary) FROM employees;

SELECT SUM (salary) AS Total_Salary FROM employees;

SELECT COUNT(empid) FROM employees; // ignore null value

SELECT COUNT(*) FROM employees; // count total no of rows

 Rename
Rename table
ALTER TABLE table_name RENAME TO new_table_name;
E.g) ALTER TABLE std RENAME TO students;

Rename attribute
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
E.g)
ALTER TABLE student RENAME COLUMN sname TO name;
• SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

• SELECT o.OrderID, o.OrderDate, c.CustomerName

FROM Customers AS c, Orders AS o
WHERE c.CustomerName="Around the
Horn" AND c.CustomerID=o.CustomerID;
• SELECT DISTINCT
• used to return only distinct (different) values
SELECT DISCTINCT (NAME) FROM Customers;

• ORDER BY
• used to sort the result-set in ascending or descending order.
• SELECT * FROM Customers
ORDER BY Country

• SELECT * FROM Customers

ORDER BY Country ASC

• SELECT * FROM Customers

ORDER BY Country ASC, CustomerName DESC;
• LIKE ( % and _)
• filter SQL records on specific string matches
• SELECT first_name FROM employees WHERE first_name LIKE ‘A%’ (start with)
• SELECT first_name FROM employees WHERE first_name LIKE 'Adam%’
• ELECT first_name FROM employees WHERE first_name LIKE '%ann’ (ends with)
• SELECT first_name FROM employees WHERE first_name LIKE '___’
• SELECT title FROM titles WHERE title NOT LIKE 'Staff’
• SELECT title FROM titles WHERE title NOT LIKE '%engineer’
• SELECT first_name, last_name FROM employees WHERE first_name LIKE 'Z%' AND
last_name LIKE 'Z%'
• -- select customers whose country names start with U followed by a single character
• SELECT *
• FROM Customers
• WHERE country LIKE 'U_’;

• -- select customers whose last_name starts with R and ends with t

• -- or customers whose last_name ends with e
• SELECT *
• FROM Customers
• WHERE last_name LIKE 'R%t' OR last_name LIKE '%e';
• BETWEEN
• selects values within a given range
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

GROUP BY
groups rows that have the same values into summary rows
often used with aggregate functions
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
• HAVING
• WHERE keyword cannot be used with aggregate functions
• lists the number of customers in each country. Only include countries
with more than 5 customers?
• SELECT COUNT(CustomerID), Country FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
• lists the number of customers in each country, sorted high to low (Only
include countries with more than 5 customers)
• SELECT COUNT(CustomerID), Country FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;
• IN
• allows you to specify multiple values in a WHERE clause
• selects all customers that are located in "Germany", "France" or "UK?
• SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK’);

• SELECT * FROM Customers

WHERE Country NOT IN ('Germany', 'France', 'UK’);

• SELECT * FROM Customers

WHERE Country IN (SELECT Country FROM Suppliers);
• The EXISTS condition in SQL is used to check whether the result of a correlated nested
query is empty (contains no tuples) or not. The result of EXISTS is a boolean value True
or False

• SELECT fname, lname FROM Customers WHERE EXISTS (SELECT * FROM

Orders WHERE Customers.customer_id = Orders.customer_id);
 JOIN
• Combine rows from two or more tables, based on a related column between
them.
• Natural Join
– Joins two tables based on same attribute name and datatypes based on implicit
condition
• INNER JOIN
– Returns records that have matching values in both tables based on explicit condition
• LEFT OUTER JOIN
– Returns all records from the left table, and the matched records from the right table
• RIGHT OUTER JOIN
– Returns all records from the right table, and the matched records from the left table
• FULL OUTER JOIN
– Returns all records when there is a match in either left or right table
 Natural Join

• Natural Join joins two tables based on same attribute name and datatypes.

• The resulting table will contain all the attributes of both the table but keep only
one copy of each common column.
• The INNER JOIN keyword selects all rows from both the tables as long as the
condition is satisfied

• The resulting table will contain all the attributes from both the tables including
common column also
• NATURAL JOIN
• SELECT * FROM Employee NATURAL JOIN Department;
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
 VIEW

• Views in SQL are kind of virtual tables.

• It stores structure information

• A View can either have all the rows of a table or specific rows based on certain condition

• Security

• Simplify complex query

• Syntax

• CREATE [OR REPLACE] VIEW view_name AS

SELECT column1, column2, ...
FROM table_name
WHERE condition;
• E.g)
• CREATE VIEW Brazil_Customers AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil’;
• Select * from Brazil_Customers;
 Using Multiple table

CREATE VIEW MarksView AS

SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;
• Using CREATE OR REPLACE

CREATE OR REPLACE VIEW Brazil_Customers AS

SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = "Brazil";

Drop view Brazil_Customers;

• Database Views Update rules
 PL/SQL Programming

• PL/SQL is a block structured language

• Procedural Language extensions to the Structured Query Language
• SQL with procedural statements
• Mainly used to create an application
DECLARE declaration statements;

variable_name data_type := value // assignment

BEGIN executable statements

Variable:= &z; //input

dbms_output.put_line(var); //output

EXCEPTIONS exception handling statements

END;
SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

-- taking input for variable a

a integer := &a ;

-- taking input for variable b

b integer := &b ;
c integer ;

BEGIN
c := a + b ;
dbms_output.put_line('Sum of '||a||' and '||b||' is = '||c);

END;
/
IF THEN Statement

IF condition THEN

statements

END IF;
IF THEN ELSIF Statement
IF condition-1 THEN
statements;
ELSIF condition-2 THEN
statements;
ELSIF condition-3 THEN
statements;
ELSE
statements;
END IF;
DECLARE

grade CHAR(1);

BEGIN

grade := 'B';

IF grade = 'A' THEN

DBMS_OUTPUT.PUT_LINE('Excellent');

ELSIF grade = 'B' THEN

DBMS_OUTPUT.PUT_LINE('Very Good');

ELSIF grade = 'C' THEN

DBMS_OUTPUT.PUT_LINE('Good');

ELSIF grade = 'D' THEN

DBMS_OUTPUT. PUT_LINE('Fair');

ELSIF grade = 'F' THEN

DBMS_OUTPUT.PUT_LINE('Poor');

ELSE

DBMS_OUTPUT.PUT_LINE('No such grade');

END IF;

END;

/
CASE selector
WHEN selector_value_1 THEN
statements_1
WHEN selector_value_1 THEN
statement_2
...
ELSE
else_statements
END CASE;
DECLARE

grade CHAR(1);

BEGIN

grade := 'B';

CASE grade

WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');

WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');

WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');

WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');

WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');

ELSE DBMS_OUTPUT.PUT_LINE('No such grade');

END CASE;

END;

/
FOR index IN [ REVERSE ] lower_bound..upper_bound LOOP
statements
END LOOP;

E.G)
BEGIN
FOR i IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE (i);
END LOOP;
DECLARE
a number(2);
BEGIN
FOR a in 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
/
DECLARE
a number(2) ;
BEGIN
FOR a IN REVERSE 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
/
DECLARE
i number(1);
j number(1);
BEGIN
FOR i IN 1..3 LOOP
FOR j IN 1..3 LOOP
dbms_output.put_line('i is: '|| i || ' and j is: ' || j);
END;
END;
END;
/
WHILE <condition>
LOOP statements;
END LOOP;

DECLARE
i INTEGER := 1;
BEGIN
WHILE i <= 10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i+1;
END LOOP;
END;
LOOP
statements;
EXIT;
{or EXIT WHEN condition;}
END LOOP;

DECLARE
i NUMBER := 1;
BEGIN
LOOP
EXIT WHEN i>10;
DBMS_OUTPUT.PUT_LINE(i);
i := i+1;
END LOOP;
END;
• Break

• Continue
 Function
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END ;
create or replace function adder(n1 in number, n2 in number)
return number
is
n3 number(8);
begin
n3 :=n1+n2;
return n3;
end;
/
DECLARE
n3 number(2);
BEGIN
n3 := adder(11,22);
dbms_output.put_line('Addition is: ' || n3);
END;
/
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM customers;

RETURN total;
END;
DECLARE
c number(2);
BEGIN
c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' || c);
END;
DECLARE
a number;
b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
 Procedure

CREATE [OR REPLACE] PROCEDURE name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END;
BEGIN
P_UPDATE_SALARY();
END;
/
DECLARE
a number(5):=&a; -- get ID from user
b number(5):=&b; --get Increment value from user
BEGIN

P_UPDATE_SALARY(a, b);

END;
/
 Cursor

A Cursor is a temporary memory that is allocated by the

database server at the time of performing
the Data Manipulation Language

It holds the multiple rows returned by the SQL statement.

➢ Implicit cursor
➢ Explicit or user defined cursor
CURSOR c_customers IS SELECT id, name, address FROM customers;

OPEN c_customers;

FETCH c_customers INTO c_id, c_name, c_addr;

CLOSE c_customers;
DECLARE

c_id customers.id%type;

c_name customers.name%type;

c_addr customers.address%type;

CURSOR c_customers is

SELECT id, name, address FROM customers;

BEGIN

OPEN c_customers;

LOOP

FETCH c_customers into c_id, c_name, c_addr;

EXIT WHEN c_customers%notfound;

dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);

END LOOP;

CLOSE c_customers;

END;

/
 Trigger
Triggers are stored programs, which are automatically executed or fired when
some events occur

• Types of PL/SQL Triggers

• There are two types of triggers based on the which level it is triggered.
1) Row level trigger - An event is triggered for each row upated, inserted or
deleted.
2) Statement level trigger - An event is triggered for each sql statement
executed.
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- sql statements
END;
BEFORE UPDATE, Statement Level: This trigger will insert a record into the table 'product_check' before a
sql update statement is executed, at the statement level.
CREATE or REPLACE TRIGGER Before_Update_Stat_product
BEFORE
UPDATE ON product
Begin
INSERT INTO product_check
Values('Before update, statement level',sysdate);
END;
/
BEFORE UPDATE, Row Level: This trigger will insert a record into the table 'product_check' before each row
is updated.
CREATE or REPLACE TRIGGER Before_Upddate_Row_product
BEFORE
UPDATE ON product
FOR EACH ROW
BEGIN
INSERT INTO product_check
Values('Before update row level',sysdate);
END;
/
• A subquery, or nested query, is a query placed within another SQL query.

• Scalar subqueries return a single value, or exactly one row and exactly one
column.
• Multirow subqueries return either:
• One column with multiple rows (i.e. a list of values), or
• Multiple columns with multiple rows (i.e. tables).
• Correlated subqueries, where the inner query relies on information obtained
from the outer query.
• Sub Quries
• We want to list paintings that are priced higher than the average

SELECT name, listed_price

FROM paintings
WHERE listed_price > (
SELECT AVG(listed_price)
FROM paintings
);
• Suppose we want to list all collectors who purchased paintings from our gallery.
We can get the necessary output using a multirow subquery

SELECT first_name, last_name

FROM collectors
WHERE id IN (
SELECT collector_id
FROM sales
);
 List of Experiments

1) Problem Identification
2) Requirement Gathering
3) Design Using ER Diagram
4) Design Using UML Diagram
5) Working with DDL and DML Commands
6) Working with Joins, Set operations and Aggregate Functions
7) Working with queries related to Projects
8) Working with Basic PL/SQL Programming
9) Working with PL/SQL Procedures
10) Working with PL/SQL Functions
11) Working with PL/SQL Cursors
12) Working with PL/SQL Triggers
13) Project Report
THANK YOU !!!