Relational Data Model

What is Relational Model?

Relational Model (RM) represents the database as a collection of relations. A relation is
nothing but a table of values.
 Every row in the table represents a collection of related data values. These rows in
the table denote a real-world entity
 The table name and column names are helpful to interpret the meaning of values in
each row.
 In the relational model, data are stored as tables.
 Relational Data Model
Some popular Relational Database management systems are:
 DB2 and Informix Dynamic Server – IBM
 Oracle and RDB – Oracle
 SQL Server and Access – Microsoft
Relational Model Concepts in DBMS
 Attribute: Each column in a Table. Attributes are the properties which define a
relation. e.g., Student_Rollno, NAME,etc.
 Tables – In the Relational model the, relations are saved in the table format. It is stored
along with its entities. A table has two properties rows and columns. Rows represent
records and columns represent attributes.
Relational Data Model

 Tuple: It is nothing but a single row of a table, which contains a single record.
 Relation Schema: A relation schema represents the name of the relation with its
attributes.
 Degree: The total number of attributes in the relation is called the degree of the
relation.
 Cardinality: Total number of rows present in the Table.
 Column: The column represents the set of values for a specific attribute.
 Relation instance: Relation instance is a finite set of tuples in the RDBMS system.
Relation instances never have duplicate tuples.
 Relation key: Every row has one, two or multiple attributes, which is called relation
key.
 Attribute domain: Every attribute has some pre-defined value and scope which is
known as attribute domain
Advantages of Relational Database Model
 Cardinality of a relation: no of rows in a table-3
 Degree of a relation: no of columns in a table-3
Schema-Instance Distinction
 Both of these help in describing the data available in a database.
 Instance: The data which is stored in the database at a particular moment of time is
called an instance of the database.
 Schema: The overall design of a database is called schema.
 Physical Schema – This schema describes the DB designed at a physical level.
 Logical Schema – This schema describes the DB designed at a logical level.
Difference Between Schema and Instance in DBMS

Parameters Schema in DBMS Instance in DBMS

Meaning Schema refers to the overall Instance basically refers to a
description of any given database. collection of data and information
that the database stores at any
particular moment.

Alterations The schema remains the same for the One can change the instances of
entire database as a whole. data and information in a
database using updation, deletion,
and addition.

Frequency of It does not change very frequently. It changes very frequently.

Change
Uses We use Schema for defining the basic We use Instance for referring to a
structure of any given database. It set of information at any given
defines how the available needs to get instance/ time.
stored.
Relational Integrity Constraints
 Relational Integrity constraints in DBMS are a pre-defined set of rules that are
applied to the table fields(columns).
Domain constraints
 Domain constraints can be defined as the definition of
a valid set of values for an attribute.
 The data type of domain includes string, character,
integer, time, date etc.
Entity integrity constraints
 The entity integrity constraint states that primary key value can't be null.
 This is because the primary key value is used to identify individual rows in relation
Referential Integrity Constraints

A referential integrity constraint is specified between

two tables.
 In the Referential integrity constraints, if a foreign key in Table 1
refers to the Primary Key of Table 2, then every value of the
Foreign Key in Table 1 must be available in Table 2 or null.
Key constraints

 Keys are the entity set that is used to identify an entity within its entity set uniquely.
 An entity set can have multiple keys, but out of which one key will be the primary
key.
ER Model to Relation Model
 Introduction to Relational algebra & Relational
calculus
 we discussed the designing of database using the Relational
model and E-R diagram. Now that we have designed the
database, we need to store and retrieve data from the database, for
this purpose we need to understand the concept of Relational
algebra and relational calculus.
 Query Language
In simple words, a Language which is used to store and retrieve data
from database is known as query language.
There are two types of query language:
Introduction to Relational algebra & Relational calculus

1. Procedural Query language:

In procedural query language, user instructs the system to perform
a series of operations to produce the desired results. Here users
tells what data to be retrieved from database and how to retrieve
it.
Ex: Relational algebra
2.Non-procedural query language:
In Non-procedural query language, user instructs the system to
produce the desired result without telling the step by step process.
Here users tells what data to be retrieved from database but
doesn’t tell how to retrieve it.
Ex: Relational calculus
Relational algebra and calculus are the theoretical concepts used
on relational model.
SQL is a practical implementation of relational algebra and
calculus.
The mathematical foundation of SQL is based upon Relational
Algebra and Relational Calculus
 Relational Algebra
 RelationalAlgebra came in 1970 and was given by Edgar
F. Codd (Father of DBMS).
 There are two kinds of query languages − relational
algebra and relational calculus.
 Relational algebra is a procedural query language(PQL)
that works on relational model.

 in PQL, a programmer/user has to mention two

things, "What to Do" and "How to Do".
 Itgives a step-by-step process to obtain the result of the
query. It uses operators to perform queries.
 Which takes Relation as input and generates relation as
output.
Relational Algebra Operators
Set Operators
 Join Operations: A Join operation combines related
tuples from different relations, if and only if a given join
condition
Different types of is
joinsatisfied.
operations areIt is denoted by ⋈.
as follows:
SQL Aggregate Functions
SQL aggregation function is used to perform the
calculations on multiple rows of a single column of a
table. It returns a single value.
1. COUNT FUNCTION: COUNT function is used to Count the number of
rows in a database table.
 SELECT COUNT(*) FROM TABLENAME;
2. SUM FUNCTION: Sum function is used to calculate the sum of all
selected columns.
 SELECT SUM(COST) FROM TABLENAME;
3. AVG FUNCTION: The AVG function is used to calculate the average
value of the numeric type.
 SELECT AVG(COST) FROM TABLENAME;
4. MAX FUNCTION: MAX function is used to find the maximum value of
a certain column.
 SELECT MAX(RATE) FROM TABLENAME;
5. MIN FUNCTION: MIN function is used to find the minimum value of a
certain column.
 SELECT MIN(RATE) FROM TABLENAME;
DBMS Aggregation:
Aggregation is a process in which a single entity alone is not able to make sense in a relationship so the relationship
of two entities acts as one entity. OR
Aggregation in DBMS(Database Management System) is a process of combining one or more entities into a single
one that is the more meaningful entity.

we know that a manager not only

manages the employee working under
them but he has to manage the project
as well. In such scenario if entity
“Manager” makes a “manages”
relationship with either “Employee” or
“Project” entity alone then it will not
make any sense because he has to
manage both. In these cases the
relationship of two entities acts as one
entity. In our example, the relationship
“Works-On” between “Employee” &
“Project” acts as one entity that has a
relationship “Manages” with the entity
“Manager”.
PL/SQL Introduction

 It is the extension of non-procedural SQL

 PL/SQL stands for “Procedural Language extensions to the Structured Query
Language”.
 PL/SQL is a block structured language.
 PL/SQL is a highly structured and readable language.
 PL/SQL is a standard and portable language for Oracle Database development.
 PL/SQL is an embedded language. PL/SQL only can execute in an Oracle
Database.
PL/SQL Introduction
 It is developed by oracle corporation in 1980’s
 It is the superset of SQL
 PL/SQL is also not a case-sensitive
 PL/SQL tells how to do, SQL tells what to do
 It is used by only oracle where as SQL can be used by any RDBMS, DBMS
technologies.
 It process only one row at a time
 It supports variables and constraints
 It supports error handling
 It supports conditional statements(if,else..)
 It supports control statements(for,while…)
PL/SQL Introduction

It supports sub-programs
procedures, functions, packages, triggers
Types of blocks:
1. Anonymous block: It has no name and it cannot be called.
2. Named block: it has a name it can be called.
There are 4 types named blocks:1.Procedure
2. functions 3.packages 4.triggers
PL/SQL block contains 3 sections:
1.Declaration section
2.Executable section
3.Exception section
 PL/SQL Introduction
Block structure:
Syntax
Declare
[Variable declaration];
Begin
<executable statements>;
Exception when <exception name> then
<executable statements>;
End
PL/SQL architecture
 Example
SET SERVEROUTPUT ON
BEGIN

DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
DBMS_OUTPUT.PUT_LINE('WELCOME TO
MITS');
DBMS_OUTPUT.PUT_LINE('CSE-D SECTION');
END;
/
OUTPUT
HELLO WORLD
WELCOME TO MITS
CSE-D SECTION
PL/SQL procedure successfully completed.
 Example
SET SERVEROUTPUT ON
DECLARE
MESG VARCHAR2(50);
BEGIN
MESG := 'HELLOWORLD';
DBMS_OUTPUT.PUT_LINE('message:'||MESG);
END;
/
OUTPUT:
message:HELLOWORLD
PL/SQL procedure successfully completed.
 Example
 DECLARE
 a integer := 30;
 b integer := 40;
 c integer;
 f real;
 BEGIN
 c := a + b;
 dbms_output.put_line('Value of c: ' || c);
 f := 100.0/3.0;
 dbms_output.put_line('Value of f: ' || f);
 END;
OUTPUT: value of c :70
value of f:33.3333333333
PL/SQL procedure successfully completed.
 Example
SET SERVEROUTPUT ON
DECLARE
MESG VARCHAR(50);
BEGIN
MESG := '&Name';
DBMS_OUTPUT.PUT_LINE('my name is:'||MESG);
END;
/
OUTPUT:
Enter value for name: srinu
old 4: MESG := '&Name';
new 4: MESG := 'srinu';
my name is:srinu
FOR Loop in PL/SQL

FOR <LOOP-VARIABLE> IN
<LOWER-LIMIT> .. <HIGHER-LIMIT>
LOOP
<EXECUTION BLOCK STARTS>
.
.
<EXECUTION BLOCK ENDS>
END LOOP;
 LOOP VARIABLE IS SELF INCREMENTAL.
 LOOP VARIABLE NO NEED TO BE DECLARED
FOR LOOP EXAMPLE
DECLARE
A INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('PROGRAM STARTS');
FOR A IN 1..5
LOOP
DBMS_OUTPUT.PUT_LINE(A);
END LOOP;
DBMS_OUTPUT.PUT_LINE('PROGRAM ENDS');
END;
OUTPUT:
PROGRAM STARTS
1 2 3 4 5
FUNCTION in PL/SQL
The PL/SQL Function is very similar to PL/SQL Procedure.
The main difference between procedure and a function is, a function must
always return a value, and on the other hand, a procedure may or may not
return a value

 CREATE [OR REPLACE] FUNCTION

function_name [parameters]
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
 RETURN return_datatype
 {IS | AS}
 BEGIN

< function_body >

 END;
 FUNCTION in PL/SQL
 Function_name: specifies the name of the function.
 [OR REPLACE] option allows modifying an existing function.
 The optional parameter list contains name, mode and types of
the parameters.
 IN represents that value will be passed from outside and OUT
represents that this parameter will be used to return a value
outside of the procedure.
 The function must contain a return statement.
 The RETURN clause specifies the data type you are going to
return from the function.
 Function_body contains the executable part.
 The AS keyword is used instead of the IS keyword for creating a
standalone function.
 FUNCTION in PL/SQL

Create a function:
 Create function adder2(n1 in number, n2 in number)
 return number
 is
 n3 number(8);
 begin
 n3 :=n1+n2;
 return n3;
 end;
 /
OUTPUT:
Function created.
FUNCTION in PL/SQL
 DECLARE
 n3 number(2);
 BEGIN
 n3 := adder2(11,22);
 dbms_output.put_line('Addition is: ' || n3);
 END;
 /
OUTPUT
Addition is: 33
FUNCTION in PL/SQL
 DECLARE
 a number;
 b number;
 c number;
 FUNCTION findMax2(x number, y 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 := findMax2(a, b);
 dbms_output.put_line(' Maximum number is: ' || c);
 END;
 /
OUTPUT : Maximum number is: 45
FUNCTION in PL/SQL
 DECLARE
 num number;
 factorial number;
 FUNCTION fact1(x number)
 RETURN number
 IS
 f number;
 BEGIN
 IF x=0 THEN
 f := 1;
 ELSE
 f := x * fact1(x-1);
 END IF;
 RETURN f;
 END;
 BEGIN
 num:= 6;
Difference between SQL and PL/SQL
No. Key SQL PL/SQL
Definition SQL, is Structural Query PL/SQL is a programming language
1 Language for the database. using SQL for a database.

2 Variables SQL has no variables. PL/SQL has variables, data types etc.
Control SQL has no FOR loop, if control PL/SQL has FOR loop, while loop, if
3 Structures and similar structures. controls and other similar structures.
Operations SQL can execute a single operation PL/SQL can perform multiple operation
4 at a time. at a time.
Language SQL is a declarative language. PL/SQL is a procedural language.
5 Type
Embedded SQL can be embedded in a PL/SQL PL/SQL can also be embedded in SQL
6 block. code.
Interaction SQL directly interacts with PL/SQL does not directly interacts with
6 database server. database server.
Orientation SQL is data oriented language. PL/SQL is application oriented
7 language.
Objective SQL is used to write queries, create PL/SQL is used to write program
8 and execute DDL and DML blocks, functions, procedures, triggers
statements. and packages.
ESCAPE clause in LIKE operator
 What will be output if you are giving LIKE predicate as '%\_%' ESCAPE '\'
1. SELECT Mname FROM MANAGER WHERE Mname LIKE '%\_%' ESCAPE '\';
Ex: RAM_PATEL
2. SELECT Mname FROM MANAGER WHERE Mname LIKE '%@%ra%' ESCAPE
‘@';
Ex: Courier%rack
 Embedded SQL
 Embedded SQL is a method of combining the computing power of a programming
language and the database manipulation capabilities of SQL.
 Embedded SQL statements are SQL statements written inline with the program source
code of the host language.
Host Language: any conventional language can be a host language, that is a language
which SQL calls are embedded.
Ex: C,C++,JAVA….
Connecting SQL to the Host Language

1. Embedded SQL: is a standard for combining SQL with seven languages.

2. CLI(Call-Level Interface):is a different approach to connecting C to an SQL
database.
3. JDBC(Java Database Connectivity): is a way to connect java with an SQL
database.
Embedded SQL
 Key Idea: Use a preprocessor to turn SQL statements into procedure calls that fit
with the host-language code.
 All embedded SQL statements begin with EXEC SQL keyword, so the
preprocessor can find them easily.
 The Preprocessor translates SQL statements to lib function calls.
 Only DML Commands are used in Embedded SQL
 A host variable is a program variable that is referenced in an SQL statement.
 There are two types of embedded SQL 1. Static SQL 2. Dynamic SQL
Programs with SQL
Host language + Embedded SQL

Preprocessor
Host language + Function calls

Host language program

Host Language
compiler
 Embedded SQL
Ex:1 main() {
int marks;
EXEC SQL select marks INTO : marks from student where student_id=6;

printf("The marks of the student is : %d", marks);

}
Ex:2 main() {
char EMPLOYEE_NAME[10];
int EMPLOYEE_SALARY,EMP_NUMBER;
printf(“Enter emp_number”);
Scanf(“%d”,&EMP_NUMBER)
EXEC SQL SELECT ename, sal
INTO :EMPLOYEE_NAME, :EMPLOYEE_SALARY FROM emp
WHERE empno = :EMP_NUMBER;
printf("The Emp Name : %S", EMPLOYEE_NAME);
printf("The Emp Salary : %d", EMPLOYEE_SALARY);
}
 Advantages of Embedded SQL

Some of the advantages of using SQL embedded in high-level languages are as follows:
 Helps to access databases from anywhere.
 Allows integrating authentication service for large scale applications.
 Provides extra security to database transactions.
 Avoids logical errors while performing transactions on our database.
 Makes it easy to integrate the frontend and the backend of our application.
Static SQL

 Embedded or Static SQL is those SQL statements that are

fixed and can't be changed at runtime in an application.
Application+SQL=FRONTEND, BACKEND=DATABASE
 These statements are compiled at the compile-time only.
 The benefit of using this statement is that you know the path
of execution of statements because you have the SQL
statements with you, so you can optimize your SQL query
and can execute the query in the best and fastest possible way.
 The way of accessing the data is predefined and these static
SQL statements are generally used on those databases that are
uniformly distributed.
Dynamic SQL
 Dynamic SQL statements are those SQL statements that are created or executed at
the run-time.
 The users can execute their own query in some application.
 These statements are compiled at the run-time. These kinds of SQL statements are
used where there is a non-uniformity in the data stored in the database.
 It is more flexible as compared to the static SQL and can be used in some flexible
applications.
 Key Static SQL (Embedded) Dynamic SQL (Interactive)
Database Access In Static SQL, database access In Dynamic SQL, how a database will be
procedure is predetermined in the accessed, can be determine only at run time.
statement.

Efficiency Static SQL statements are more Dynamic SQL statements are less
faster and efficient. efficient.

Compilation Static SQL statements are compiled at Dynamic SQL statements are compiled at
compile time. run time.

Application Plan Application Plan parsing, validation, Application Plan parsing, validation, optimization
optimization and generation are and generation are run-time activities.
compile time activities.

Use Cases Static SQL is used in case of Dynamic SQL is used in case of non-
uniformly distributed data. uniformly distributed data.

Dynamic Statements like EXECUTE Statements like EXECUTE IMMEDIATE,

Statements IMMEDIATE, EXECUTE, PREPARE EXECUTE, PREPARE are used
are not used.

Flexibility Static SQL is less flexible. Dynamic SQL is highly flexible

Triggers
 Trigger is invoked by the Oracle engine automatically whenever a
specified event occurs. Trigger is stored in the database and invoked
repeatedly, when specific condition match.
 Triggers are stored programs, which are automatically executed or
fired when some event occurs.
 A database with a set of associated triggers is generally called an active
database.
Parts of trigger
A triggers description contains three parts.
 Event(activates the trigger)− An event is a change to the database which
activates the trigger.
 Condition(whether the triggers should run or not) −

A query that is run when the trigger is activated is called a

condition.
 Action(what happens if the trigger runs) −A procedure which is executed
when the trigger is activated and its condition is true
Types of Triggers

 There are two types of triggers

1. DML triggers
2. DDL triggers
 DML triggers are again
classified into 2 types 1.
Statement level triggers 2. Row
level triggers.
 Statement level triggers: A
Statement level triggers executes
only once for each single
transaction/statement.
 Row level triggers: A Row level
trigger executes once for each
and every row in the
transaction/statement.
Creating a trigger:
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)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
 CREATE [OR REPLACE] TRIGGER trigger_name: It creates or replaces
an existing trigger with the trigger_name.
 {BEFORE | AFTER | INSTEAD OF}: This specifies when the trigger would
be executed. The INSTEAD OF clause is used for creating a trigger on a view.
 {INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML
operation.
 [OF col_name]: This specifies the column name that would be updated.
 [ON table_name]: This specifies the name of the table associated with the
trigger.
 [REFERENCING OLD AS o NEW AS n]: This allows you to refer new and
old values for various DML statements, like INSERT, UPDATE, and DELETE.
 [FOR EACH ROW]: This specifies a row-level trigger, i.e., the trigger would
be executed for each row being affected. Otherwise, the trigger will execute
just once when the SQL statement is executed, which is called a table-level
trigger.
 WHEN (condition): This provides a condition for rows for which the trigger
would fire. This clause is valid only for row-level triggers.
:NEW and :OLD Clause or Bind variable or row type variable

 OLD and NEW references are used for record level triggers these are not available for
table level triggers.
 In a row level trigger, the trigger fires for each related row. And sometimes it is
required to know the value before and after the DML statement.
 Oracle has provided two clauses in the RECORD-level trigger to hold these values.
We can use these clauses to refer to the old and new values inside the trigger body.
 :NEW – It holds a new value for the columns of the base table/view during the trigger
execution
 :OLD – It holds old value of the columns of the base table/view during the trigger
execution
 Use of trigger
Triggers may be used for any of the following reasons
 Useful for catching errors.
 Prevention of invalid transactions
 Imposing security authorizations
 To implement any complex business rule, that cannot be implemented using integrity
constraints.
 Triggers will be used to audit the process. For example, to keep track of changes made
to a table.
 The trigger is used to perform automatic action when another concerned action takes
place.
Example of Statement level Trigger
create trigger csetgr before update on emp
begin
dbms_output.put_line(‘Table Updated');
end;
/
Trigger created.
SQL>update emp set salary=salary+5000 where salary<40000;
Table Updated
3 rows updated.
SQL>update emp set salary=salary+5000 where EID=2;
Table Updated
1 row updated
Example of Row level Trigger
create trigger emp before update on emp for each row
begin
dbms_output.put_line('Table Updated');
end;
/
Trigger created.
SQL> update emp set salary=salary+10000 where salary<50000;
Table Updated
Table Updated
Table Updated
Table Updated
4 rows updated.
SQL>update emp set salary=salary+10000 where eid=105;
Table Updated
Active Databases
 A database with a set of associated triggers is generally called an active database.
 It is a set of triggers, making it quite difficult to maintain.
 Whenever a change is made to the database trigger gets activated. The action may in
turn activate other triggers or may activate the same trigger that initialized this action.
 Such type of trigger that activates itself is called a recursive trigger
 DBMS executes such chains of triggers in the same pre-defined manner but it affects
the concept of understanding the collection of triggers
 Triggers maintain data integrity in the database
 Triggers identify unusual events that occur in the database
 Triggers are used for security checks and also for auditing
Stored Procedures
 The PL/SQL stored procedure is a PL/SQL block which performs one or more
specific tasks.
 It is just like procedures in other programming languages.
 A procedure can be compiled and stored in the database as a schema object.
 It promote reusability and maintainability
The procedure contains a header and a body.
 Header: The header contains the name of the procedure and the parameters or
variables passed to the procedure.
 Body: The body contains a declaration section, execution section and exception
section similar to a general PL/SQL block.
Stored Procedure Syntax
 CREATE [OR REPLACE] PROCEDURE
procedure_name [ (parameter1 [mode 1]) datatype,… ]
 IS / AS
 [declaration_section]
 BEGIN
 executable_section
 [exception_section]
 END [procedure_name];
 Stored Procedure Example
Table creation:
create table user1(id number(10),name varchar(10));
Create Procedure Code:
 create or replace procedure insertuser(id in number, name in varchar)
 is
 begin
 insert into user1 values(id, name);
 end;
 /
Output: Procedure created.
Stored Procedure Example
Calling a Procedure:
1.SQL> EXECUTE insertuser(101,'Rahul');
Output: PL/SQL procedure successfully completed.
2.
 BEGIN
 insertuser(101,'Rahul');
 dbms_output.put_line('record inserted successfully');
 END;
 /
Output: record inserted successfully
PL/SQL procedure successfully completed.
DROP PROCEDURE:
DROP PROCEDURE procedure_name;
PL/SQL - Cursors
 Oracle creates a memory area, known as the context area, for processing an SQL
statement, which contains all the information needed for processing the statement.
 A cursor is a pointer to this context area. PL/SQL controls the context area through
a cursor.
 A cursor holds the rows (one or more) returned by a SQL statement. The set of
rows the cursor holds is referred to as the active set.
Types of Cursors
 There are two types of cursors:
 Implicit cursors: Implicit cursors are
automatically created by Oracle whenever an
SQL statement is executed
 Explicit cursors: Explicit cursors are
programmer-defined cursors for gaining more
control over the context area.
Implicit Cursors
 Implicit cursors are automatically created by Oracle whenever an SQL statement is
executed, when there is no explicit cursor for the statement.
 Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an
implicit cursor is associated with this statement.
 For INSERT operations, the cursor holds the data that needs to be inserted.
 For UPDATE and DELETE operations, the cursor identifies the rows that would be
affected.
 In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor,
which always has attributes such as %FOUND, %ISOPEN, %NOTFOUND,
and %ROWCOUNT.
Attributes & Description
 %FOUND: Returns TRUE if an INSERT, UPDATE, or DELETE
statement affected one or more rows or a SELECT INTO statement
returned one or more rows. Otherwise, it returns FALSE.
 %NOTFOUND: The logical opposite of %FOUND. It returns TRUE
if an INSERT, UPDATE, or DELETE statement affected no rows, or a
SELECT INTO statement returned no rows. Otherwise, it returns
FALSE.
 %ISOPEN: Always returns FALSE for implicit cursors, because
Oracle closes the SQL cursor automatically after executing its
associated SQL statement.
 %ROWCOUNT: Returns the number of rows affected by an
INSERT, UPDATE, or DELETE statement, or returned by a SELECT
INTO statement.
 Any SQL cursor attribute will be accessed as sql%attribute_name.
Example of Implicit Cursors
DECLARE
total_rows number(2);
BEGIN
UPDATE customers SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
/
OUTPUT:
6 customers selected
PL/SQL procedure successfully completed.
Explicit Cursors
Explicit cursors are programmer-defined cursors for
gaining more control over the context area. An explicit cursor
should be defined in the declaration section of the PL/SQL
Block. It is created on a SELECT Statement which returns more
than one row.
 Declaring the Cursor: Declaring the cursor defines the cursor with a name and the
associated SELECT statement.
Syntax: CURSOR c1 IS SELECT Id,Name from Emp;
 Opening the Cursor: Opening the cursor allocates the memory for the cursor and
makes it ready for fetching the rows returned by the SQL statement into it.
Syntax: OPEN c1;
 Fetching the Cursor: Fetching the cursor involves accessing one row at a time
Syntax: FETCH c1 INTO Eid,Ename;
 Closing the Cursor: Closing the cursor means releasing the allocated memory.
Syntax: CLOSE c1;
Example of Explicit Cursors
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;
/
OUTPUT:
1 Ashrith BKK
2 Harshini MPL
PL/SQL procedure successfully completed.
Introduction to JDBC
 JDBC stands for “Java Data Base Connectivity”.
 It is an API(Application Programming Interface) that defines how a java
programmer can access the database in tabular format from java code.
 All direct interactions with a specific database happen through a database-specific
driver.
 A driver is a software program that translates the ODBC or JDBC calls into
database-specific system calls.
 The drivers translates the SQL commands from the application into database
commands.
 Drivers are managed by driver managers
Architecture of JDBC
Architecture of JDBC
 Application: The application initiates and terminates the connection with a
database.
 JDBC API: The JDBC API allows java programs to execute SQL statements and
retrieve results.
 Driver Manager: Load JDBC drivers and pass the JDBC function calls from the
application to the correct driver.
 JDBC Drivers: intelligently communicates with the database.
Features of JDBC
 JDBC is an API, using we can communicate with any database without rewriting
our java application.
 Most of the JDBC drivers are implemented in JAVA. Hence JDBC is known as
platform-independent technology.
 Using JDBC API we can able to perform all basic database operations easily.
Relational Calculus
 Relational calculus is a non-procedural query language that tells the system what
data is to be retrieved but doesn’t tell how to retrieve it.
 1. Tuple Relational Calculus
(TRC)
Tuple relational calculus is used for selecting those tuples that satisfy the given
condition.
 Tuple Relational Calculus in DBMS uses a tuple variable (t) that goes to each row
of the table and checks if the predicate is true or false for the given row. Depending
on the given predicate condition, it returns the row or part of the row.
Tuple Relational Calculus expression Syntax
{T | P (T)} or {T | Condition (T)}
 Where t is the tuple variable that runs over every Row, and P(t) is the predicate
logic expression or condition.
Example: Table Name: customer
Customer_id Name Zip code
1 Rohit 12345
2 Rahul 13245
3 Rohit 56789
4 Amit 12345.
Example 1: Write a TRC query to get all the data of a
customer whose zip code is 12345.
TRC Query: {t \| Customer(t) ∧ t[Zipcode] = 12345 }

Customer_id Name Zip code

1 Rohit 12345

4. Amit 12345
Domain Relational Calculus
(DRC)
In domain relational calculus the records are filtered based on the domains.
 Domain Relational Calculus uses Domain variables to get the column values
required from the database based on the predicate expression or condition.
 The Domain relational calculus expression syntax:
 { x1, x2, x3, ..., xn | P (x1, x2, x3, ... ,xn)}
 x1, x2 are attributes and P is predicate expression or condition.
 Example: Table Name: customer
Customer_id Name Zip code
1 Rohit 12345
2 Rahul 13245
3 Rohit 56789
4 Amit 12345.
Example 1: Write a DRC query to get all the data of customer
whose zip code is 12345.
DRC query: {<x1,x2,x3> | <x1,x2> ∈ Customer ∧ x3 = 12345 }

Customer_id Name Zip code

1 Rohit 12345

4. Amit 12345