RDBMS: Relation database management system

Refer to a relational database plus supporting software

for managing users and processing SQL
queries ,perform backups/restores and associated
tasks.
API: application programming interface so that
developers can write programs .
RDBMS: Microsoft access
mySQL, postgresSQL
Azure SQL etc
Relation Model has 3 aspects:
1)Structures
2)Operations
3)Integrity rules
1)Structure : it is collection object or relations
Relation is called table.
2)Operation : its is used to manipulate data and
structure in a database .
3)Integrity rule: its ensures data accuracy and
consistency.
Characteristics of RDBMS
1. Data abstraction: data independence
2. Self-describing data: metadata
3. Concurrency: shared concurrent access.
4. Support to multiple view
5. Security : integrity ,availability,
accountable ,privacy
Components of RDBMS
1)Table: structure which consist of row and column
Stud_id Stud_name Stuc_add
101 Harsh Pune
102 Sohan kondwa

2) row: combination of col values in a table

3)column:collection of one type of data in table
4)field: intersection of rows and columns
5) primary key:
It is col or combination of col that used to
uniquely identify each row in a table
Stud(stud_id,stud_name,stud_add,tech_id)
Teacher(tech_id,tech_name)
P k= stud_id and tech_id
Fk= teach_id
6) foreign key:
It is a col or set of col that refer to primary key in
the same table or another table .
Advantages and disadvantages of RDBMS
Adv
1)Easy to implement and use
2)It has hight level data security
3)It has definite procedure
4)Data are restricted
5)Rdbms is consistent, durable , supports isolation
Disadvantage
1)Proper skill and training required
2)Required supportive software and hardware
3)Cost of execution is high .
4)Maintain integrity is important.

Difference between rdbms and dbms

Pl/SQL
What is PL/SQL?
Developed by oracle corporation in early 90’s
Pl/SQL mix SQL statement with procedural statement
like
If statement ,looping statementetc…
Pl/SQL superset of SQL.
Sql: it use for retrieving and manipulation for data
processing.
Pl/sql into program unit divided into two category :
1)Anonymous block
2)Stored procedure
1)Anonymous block: pl/sql block that appears within
your application .
2)Stored procedure : pl/sql that stored in the
database with a name.

PL/SQL engine
Features of PL/SQL
1. BLOCK structure :
2. Variables and constants :
3. Control structure :
4. Exception haldling :
5. Modularity
6. Cursors:
7. Build in function

Data types:
Storage format
1)CHAR DATA
TYPE:char,nchar,nvarchar,varchar2(size),long ,raw,l
ong raw
2)Numeric data type: number(p,s) i.e number(3,2)
output: 234.25,numeric(p,s),double
precision ,float,dec(p,s)/demicial(p,s) 38 digit ,int
integer ,real 63 decimal digits:

3)Date/time :date, timestamp

4)Lob data type: bfile,blob,clob ,nclob

5)Rowid data type:rowid fixed length binary data

Urowid(size):universal ROWID

Pl/SQL
Declaration
Variable declaration;
Begin
Program Excuation;
Exception
Exception handling;
End;

Function:
It take one or more value and return single result .
Build in function
1)ABS();
sql> select abs(-15) from dual;
ouput
15
2)Mod()
Sql> select MoD(15,4) from dual;

Output
3
3)SQRT()
Sql> select SQRT(121) from dual;

Output
11
4)ASCII()
Select ASCII(‘A’) from dual;
Output
65
5)INITCAP()
Select INITCAP(‘how are you’) from dual;

Output
How Are You

________________________________________________________
_____

Function
________________________________________________________
________________________________________________________
________

Create [or replace] function function_name[parameters]

Return return_datatype;
[IS/AS]
Declaration_section;
Begin
Execution_section;
Return reteurn_varible;
Exception
Exception section;
Return return_varible;
END functionname;

QUE : write function to pass empno as a parameter to

function and function will return salary.

Emp Ffunction
Empno sal
f_no f_sal

Create or replace function Ffunction [f_no IN number]

Return number;
IS
F_sal emp.sal%type;
Begin
Select sal into f_sal from emp where empno=f_no;
If SQL%found then
Return (f_sal);
Else
Return 0;
End if;
END Ffunction;

Calling function
Delete a function
Drop function function_name;
Drop function Ffunction;

Program : pass a number to function and check

whether it is divisible by 5 or not .

Create or replace function f1(f_no in number)

Return number
As
Begin
If(mod(f_no,5)=0) then
Return 1;
Else
Return 0;
End if;
End f1;
/
Select f1(10) from dual;
Output
1
Calling function
Declare
No number ;
R number;
Begin
No:=&No;
R=f1(No);
If (R=1) then
D_o.P_l(NO || ‘is divisible by 5’);
Else
D_O.P_L(No || ‘is not divisible by 5’);
End if
End;
/
// pass two strings to a function and print the largest
string using function
Create or replace function f2( str1 in varchar2, str2 in
varchar2)
Return varchar2
As
L1 number ;
L2 number ;
Begin
L1:=length(str1);
L2:=length(str2);
If (l1>l2) then
Return ‘first string is larger ‘;
Else if(l2>l1) then
Return ‘second string is larger’;
Else
Return ‘both are equal’;
Endif ;
Endif ;
End f2;
/
________________________________________________________
_
Select f2(‘sumit’,’soham’) fro dual;
Both are equa
Hw Que: program for swap using function
Que : pass a month to a function and print no of
employees joined in that month
Emp f3
V_mon hiredate f_mon
v_count

Create or replace function f3(f_mon in varchar2)

Return varchar2
Is
V_count number;
Begin
Select counr(*) into v_count from emp where
to_char(hirdate,’mon’)=f_mon;
Return v_count;
End f3;
/
Select f3(‘apr’) from dual;
2

Declare
V_mon varchar2(10);
R number;
Begin
V_mon:=&v_mon;
R:= f3(v_mon)
If (r>0) then
D_o.P_l(‘no of emp joined in || v_mon||’=’||r);
Else
D_o.P_l(‘no of emp joined in || v_mon);
End if
End;

Procedure
Syntax

Create or [replace] procedure procedure_name

[(arguments [in /out/in out ] datatype [,argument
[in/out/in out] datatype…..])]
[is/as]
[variable declaration]
[Pl/sql block]

Execute a procedure
SQL> execute myproc1(77780);
Delete a procedure
Drop procedure procedure_name;
Eg:
Drop procedure myproc1;

Que: pass deptno to procedure and print max salary of

emp working in department if deptno does not exist
print msg

Emp p1
Deptno sal p_deptno max_sal
Create or replace procedure p1(p_deptno in number)
As
Max_sal emp.sal%type;
Begin
Select max(sal) into max_sal from emp where
depno=p_deptno;
If (max_sal>0) then
D_O.P_L(‘max sal =’||max_sal);
Else
D_O.P_L(‘deptno does not exist’);
Enf if;
End p1;
SQL> execute p1(90);

________________________________________________________
____

Calling function
Delcare
V_deptno emp.deptno%type;
Begin
V_deptno:=&v_deptno;
P1(v_deptno);
End;
/

// pass deptno to a procedure procedure will pass no of

employee working in that dept in same variable .

Cursor
A cursor is a temporary work area created by system
memory when SQl and PL/SqL statements are executed
.
Attributes
%rowcount : no rows processed by sql statement .
%found :true ,if at least one row processed.
%notfound: true no rows processed
%isopen: true cursor open
False cursor close
Declaration
Syntax:
Cursor cursor_name [(parameters)] [return return_type]
is select quesry
Eg:
Declare
Cursor c_deptno is select * from emp;
Open a cursor

Open cursor_name[parameters];
Eg:
Declare
Cursor c_deptno is select * from emp;
Begin
Open c_deptno;
End;

Accessing the cursor rows

Using FETCH statement
Eg:
Declare
Cursor c_deptno is select ename,sal depetno from emp;
V_ename emp.ename%type;
V_sal emp.sal%type;
V_deptno emp.deptno%type;
Begin
Open c_deptno;
Fetch c_deptno into v_ename,v_sal,v_deptno ;
Dbms_output.put_line(v_ename||’ ‘||v_sal|| ‘ ‘||
v_deptno);
End;
SQL>/
Rohit 8000 20

Fetch by using loop

Declare
Cursor c_deptno is select ename,sal depetno from emp;
V_ename emp.ename%type;
V_sal emp.sal%type;
V_deptno emp.deptno%type;
Begin
Open c_deptno;
Loop
Fetch c_deptno into v_ename,v_sal,v_deptno ;
Exit when c_deptno%notfound ;
Dbms_output.put_line(v_ename||’ ‘||v_sal|| ‘ ‘||
v_deptno);
End loop ;
Close c_deptno;

End;
Closing cursor
Eg: close c_deptno;
For ……..loop using cursor

Eg
Declare
Cursor c_deptno is select ename,sal,deptno from emp;
Begin
Open c_deptno
For i in c_deptno
Loop
D_O.P_L(i.ename||i.sal||i.deptno);
End loop
End;
Types of cursor
1)Implicit
2)Explicit
3)Parameterised

1)Implicit cursor:
These are created by default when DML statement
like insert update and delete statement are
executed and also select statement

Eg:
Prog1: print number of rows deleted from emp

Declare
Print_row_deleted number;
Begin
Delete from emp;
Print_row_deleted=SQL%rowcount ;
Dbms_output.put_line(‘no of rows deleted’||’ ‘||
Print_row_deleted);
End;

SQL> /

No of rows deleted 12
PL/SQL procedure successfully completed

Explicit cursor

If select statement in PL/SQL block return multiple rows

then you have to explicitly create cursor which is called
as explicit cursor.
Commands
1)Declare
2)Open
3)Fetch
4)Close

Syntax:

Cursor cursor name is sql select statement ;

Parameterised cursor

Passing parameters to cursor

Syntax : cursor cursername (variable name datatype) is
<select statement…>
Open cursor
Open cursorname <value/variable/expression>

Eg:
Accept a salary and print name salary and jon of emp
having salary less than equal to accepted salary
Declare
Cursor c1(c_sal emp.sal%type) is select name,sal,job
from emp where sal<=c_sal;
Emp_rec c1%rowtype;
V_sal emp.sal%type;
Begin
V_sal=&v_sal;
For emp_rec in c1(v_sal)
Loop
D_O.P_l(emp_rec.name||emp_rec.sal||emp_rec.job);
End loop;
End;
SQL>/
Trigger
A trigger is triggered automatically when as
associated DML statement is executed.
Or
A trigger is a PL/SQL block structure which fired
when a DML statement like insert delete and
update command is excuted on a database
table .
How to create a trigger?

Program 1:
Consider the following relationship database
Doctor(dno,dname,dcity)
Hospital(hno,hname,hcity)
Doc_hosp(dno,hno)
__________________________________________________
___
Write a function to return the count of number of
hospital located in pune city.

Create or replace function f_hcount

Return number as
Hcunt number;
Begin
Select count(*) into Hcunt from hospital where
hcity=’pune’;
Return hcunt;
End F_hcount;
/
SQL> select f_hcount from dual;
F_hcout
4

Consider the following relational database

Book(bno,bname,pubname,price)
Author(ano,aname)
Book_authoe(bno,ano)
Define a trigger that restrict insertion or updation of
book having price less than 0
Create or replace trigger book_price_trig before insert
or update on book for each row
Declare
Bn number;
Begin
If inserting then
If: new.price<0 then
Raise_applicaition_error(‘-21000’,’cant insert
‘);
End if;
Enf if;
If updationg then
If: new.price<0 then
Raise_applicaition_error(‘-21000’,’cant update
‘);
End if;
Enf if;
End book_price_trig;

SQL:> insert into book values (5,alogorith,abc,-500)

Error
Consider the following relation database
Customer(cno,cname,ccity)
Loan(lno,lamt,no_of_year,cno)
Write a procedure to display the total amount from
pune city
Create or replace procedure p_amt_disp as t_loan
number;
Begin
Select sum(lamt) into t_loan from loan where
cno in(select cno from from customer where
ccity=’pune’);
Dbms_outpu.put_line(‘total loan amount’||t_loan);
End p_amt_disp
Sql>
Execute p_amt_disp;
Unit 2: Transaction management
Acid
Transaction state
Transaction operation
Schedule:serial
Concurrent
Serializability
Conflict
View
Recoverability
___________________________________________________
_____

A transaction is a program unit whose

execution accesses and possibly updates the
contents of a database

ACID properties
___________________________________________________
_____
A=atomicity

C= consistency

I= isolation

D=durability

ACID properties

Function to access the database

X= is data item
1)Read(X): this function transfer the data item X
from database to local buffer
2)Write(X): This function transfer the data item X
from database local buffer to database .

Eg: Let Ti be a transaction that transfer 50 from

account A to account B.
How to define transactions:

Ti :
read(A)
A:= A-50;
Write(A);
Read(B);
B:= B+50;
Write(B);

Transaction State
Serializability :
1) Conflict serializability
2)View serializability
1)Conflict serializability :
T1 and T2 are two transaction and
S is schedule
Ii and Ij are two instruction .
If Ii and Ij refer to different data item
Then
Ti and Ijcan be executed in any sequence.
Conflict pairs

R --- W
W -- R
W-- W

T1 T2

Read (A)
Write(A)
Read(A)
Write(A)
Read(B)
Write(B)
Read(B)
Write(B)

Precedence Graph:

No cycle /loop
Conflict serializable
Serial
Consistent
Sequence : T - T2

View Serializable :
S1:
T2 T3
T1
R(A
)
W(A)
W(A
)
W(A
)

Precedence graph:
Non conflict serializable
If we get loop in the graph then use view serializability

S2
T1 T2 T3
R(A)
W(A)
W(A)
W(A)

S1 S2
T1 T2 T3 T1 T2 T3
R(A) R(A)
W(A) W(A)
W(A) W(A)
W(A W(A)
)

A=100 A=100
A=a-40 A=a-40
A=60 A=60
A=a-40 A=a-40
A=20 A=60-
40
A=a-20
A=20
A=0
A=a-20
A=0

Indegree

T1=0

T2=1

T3=2
Therefore Sequence: : T1 - T2 - T3
________________________________________________________
_____Recoverability
Recoverability refers to the ability of system to restore
its state to a consistent and valid condition after a
failure or crash .
Keys:
1)Atomicity: Ensure that transactions are treated as a
single transaction, indivisible unit of work .if
transaction fails the system roll back to its previous
state .
2)Consistency: verifies that the database remains in
consistent state, even after multiple transaction
have been applied.
3)Durability:Grarantees that once a transaction has
been committed ,its effects are permeant and
survive even in the events of a system failure .
Types of Recoverability
1)Transaction: restored the database to a consistent
state by rolling back transactions that wre
interrupted or fail.

2)System : recovers system from failure such as

power cutoff,hardware failres ,software crashes

Techniques for ensuring Recoverability

1)Logging : records all changes made to the

database allowing the system to recover by
replaying or undoing transaction
2)Checkpointing:
it saves the state of the Systemen’ll faster
recovery by reducing the amount of work that’s
needs to be redone or undone .
3)Backup and restore:
Regulare backup of the database ,allowing
system to restore to a [revious state in case of
failure .

Two types of recoverability schedule :

1)Recoverable schedule:
It allow the system to recover from transaction
failures without violating the consistency of
the database
Properties:
1)Transaction rollback :
If T1 fails,then system can
rollback T1
without affecting other transaction
2)No dirty reads :
T1 T2
Read
Write
Read

Eg:
T1 T2
Read(A)
Write(A)
Read(A)
Read(B)

Cascadeless Schedule:
It ensure that the rollback of one transaction doe not
cause rollback of other transactions

Properties:
1)No dirty reads:
2)Strict scheduling :

T1 T2 T3
Read(A)
Read(B)
Write(A)
Read(A)
Write (A)

Read(A)

________________________________________________________
_____

Q1: Consider the following transaction. Give two non-

serial
schedules that are serializable
T1 T2
Read(X) Read(Z)
X=X+100 Read(X)
Write(X) X=X-Z
Read(Y) Write(X)
Read(Z) Read(Y)
Y=Y+Z Y=Y-100
Write(Y) Write(Y)

Solution:
S1:
T1 T2
Read(X)
X=X+100
Write(X)
Read(Z)
Read(X)
X=X-Z
Write(X)

Read(Y)
Read(Z)
Y=Y+Z
Write(Y)
Read(Y)
Y=Y-100
Write(Y)

S2:
T1 T2
Read(X)
X=X+100
Write(X)

Read(Z)

Read(Y)
Read(Z)

Read(X)
X=X-Z
Write(X)

Y=Y+Z
Write(Y)
Read(Y)
Y=Y-100
Write(Y)

Q2: Consider the following transaction .give two non

serial schedule that are serializable .
T1 T2
Read(Y) Read(X)
Read(a) Read(a)
Y=Y+a X=X+a
Write(Y) Write(X)
Read(Y)
Y=Y+a
Write(Y)

Solution:
S1:
T1 T2
Read(X)
Read(a)
X=X+a
Write(X)

Read(Y)
Read(a)
Y=Y+a
Write(Y)
Read(Y)
Y=Y+a
Write(Y)

S2
T1 T2
Read(X)
Read(a)

Read(Y)
Read(a)

X=X+a
Write(X)

Y=Y+a
Write(Y)
Read(Y)
Y=Y+a
Write(Y)

Chap 3:

Q1:lock compatibility matrix

Q2:logical and system error
Q3:Growing phase and shrinking phase
Q4: timestamp
Q5:Deadlock prevention and deadlock recovery.
Q6:log
Q7) undo and redo
Q8:what is lock? Explain shared and exclusive
lock with example.
Q9: Two-phase locking
Q10: checkpoint
Total: 35 Marks
Q1:any 5 [out of 7] 5 x 1 =5M
Q2: A] i)
ii) 2 x 3=6M
B] i)
ii) 2 x 2=4M
Q3: A] i)
ii) 2 x 3=6M
B] i)
ii) 2 x 2=4M
Q4: short note any 4 [out of 6]
4x2½
=10M
________________________________________________________
____

E_Commerce

Short question
i)What is Electronic Data Interchange (EDI)?
ii) Define B2B e-commerce.
iii) Name one legal requirement for e-commerce.
iv) State one advantage of e-commerce for
consumers.
v) What is the final stage of the e-commerce
adoption life cycle?
vi) Give an example of a payment gateway.
vii) What is spoofing in the context of e-
commerce security?
i) Define E-commerce.
ii) What is meant by B2C in e-commerce?
iii) Name one essential component of the e-
commerce infrastructure.
iv) State one function of e-commerce.
v) What is the first stage of the e-commerce
adoption life cycle?
vi) Give an example of an electronic payment
system.
vii) What is phishing in the context of e-
commerce security?
Long Questions

i) Explain the different goals of e-commerce.

ii) Describe the key technological components of
e-commerce.
i) List two advantages of e-commerce for
businesses.
ii) What are the different types of electronic
payment?
i) Explain the various stages of the e-commerce
adoption life cycle.
ii) Describe the scope of e-commerce.
i) What is a digital signature?
ii) Mention two common security threats in an
e-commerce environment.
i) Explain the different business models in e-
commerce.
ii) Describe the essential commercial components
of e-commerce.

i) List two disadvantages of e-commerce for

businesses.
ii) What is a payment gateway and what is its
role?
i) Explain the benefits and limitations of e-
commerce.
ii) Describe the applications of e-commerce.
i) What is encryption in e-commerce security?
ii) Mention two ways to ensure security in
electronic funds transfer.

________________________________________________________
____
Short note
i)The internet and E-commerce.
ii) A value exchange system in e-commerce
iii) Electronic Funds Transfer (EFT).
iv) Modern Payment Cash
v) The Threats posed by malicious code.
vi) Denial of Service (DoS) attacks
i) World Wide Web in the growth of e-commerce.
ii) Positioning Payment in e-commerce.
iii) Distributed Denial of Service (DDoS) attack .
iv) Stealing Credit card information
v) security policy
vi) Problems with online cash

Insert into abc values (&

SQL> Select * from Abc ;

Chapter 3: concurrent control and Recovery

System
Two aspect that ensure serializable result in concurrent
execution are locking and time stamping
Lock?
It is variable associated with each data item is called
lock .
Manipulating the value of lock is called locking .
Two modes:
1. Shared lock(S):
S
If Ti----A then
Ti ---- read
Can not write
2. Exclusive lock(X)
X
If Ti --- A
Then
Ti --- Read
Write

Compatibility Function
S X
S True False
X False False

Locking Technique
1.Binary locking : 1(locked) or 0 (unlocked)
2.Two-phase locking :locked _-- unlocked

Two-phase locking
1.Growing phase :
Ti can obtain lock
But can not release it

2.Shrinking phase:
Ti can release lock
But can not obtain new lock

Eg:

T1: lock_X(B); T2: lock_S(A)

Read(B) Read(A)
B:=B-50; Lock_S(B)
Write(B); Read(B)
Lock_X(A) Display(A+B)
Read(A) Unlock(A)
A:=A+50 unlock(B)
Write(A);
Unlock(B)
Unclock(A);

lock_X(B);
Read(B)
B:=B-50;
Write(B);

lock_S(A)
Read(A)

Lock_X(A)
Read(A)
A:=A+50
Write(A);
Unlock(B)
Unlock(A);

Lock_S(B)
Read(B)
Display(A+B)
Unlock(A)
unlock(B)
.