Q4)
Attempt any FOUR of the following (Out of FIVE) : [4 × 4 = 16]
1.Employee(empno, ename, sal, designation)
Department(deptno, dname, loc)The relationship between employee and department
is many_to_one.
Write a function which accepts department numbers and
returns the number of employees working in that department.
set serveroutput on
create or replace function f2(abc in varchar) return number as xyz
number;
begin
select count(employee.empno) into xyz from employee,Department where employee.deptno=department.empno and
dname=’Computer’;
if sql %found then
return (xyz);
else
return null;
end if;
end f2;
/
begin
dbms_output.put_line ('Department '||f2(‘Computer’));
end;
/
2.Employee(empno, ename, sal, designation)
Department(deptno, dname, loc)The relationship between employee and department
is many_to_one.
Write a trigger that restricts insertion or updation
of employees Salary less than zero.
SQL> set
serveroutput on
SQL> create or replace trigger t4 before insert or
update on employee
2 for each row
3 begin
4 if(:new.sal<=0)then
5 raise_application_error(-20001,'Sal>0');
6 end if;
7 end;
8 /
3.Consider following relational database.
Doctor (dno, dname, dcity)
Hospital (hno, hname, hcity)
Doct-Hosp (dno, hno)
Write a function
to return count of number of hospitals located in Pune City.
set serveroutput on
create or replace function f2(abc in varchar) return number as xyz
number;
begin
select count(hospital.hno) into xyz from Hospital where hcity=’Pune’;
if sql %found then
return (xyz);
else
return null;
end if;
end f2;
/
begin
dbms_output.put_line ('No.Of Hospitals '||f2(‘Pune’));
end;
/
4.Consider the
following relational database :
Customer (cno, cname, city)
Account (ano, acc-type, balance, cno)
Define a trigger that restricts insertion or updation of account
having balance less than 100.
SQL> set
serveroutput on
SQL> create or replace trigger t4 before insert or
update on Account
2 for each row
3 begin
4 if(:new.balance<=0)then
5
raise_application_error(-20001,'balance>0');
6 end if;
7 end;
8 /
5.Consider
the following related database :
Customer (cno, cname, city)
Loan (lno, lamt, no_of_ years,
cno)
Write a procedure to display total loan amount
from Pune City
SQL> set serveroutput on
SQL>
create or replace procedure p1(n in varchar) as cursor c1 is select count (lamt),from Loan ,Customer
where Customer.cno=Loan.cno and
city=’Pune’';
2 c c1
%rowtype;
3
begin
4 open
c1;
5
dbms_output.put_line('lamt'');
6
loop
7
fetch c1 into c;
8 exit
when c1 %notfound;
9
if(c.city=n)then
10
dbms_output.put_line(c.lamt);
11 end
if;
12 end
loop;
13
close c1;
14
end;
15 /
Procedure
created.
SQL>
begin
2
p1('Pune');
3 end;
4 /
6.
6.Employee(empno, ename, sal, designation)
Department(deptno, dname, loc)The relationship between employee and department
is many_to_one.
Write a cursor which displays department wise
details of employees.
SQL> set serveroutput on
SQL> declare
2 cursor
c1(yyyy department.dname %type)is select dname,eName from employee,Deparment where
where depaerment.deptno=employee.deptno
order by dname;
3 c
c1%rowtype;
4 begin
5 open
c1('&dname ');
6 loop
7 fetch
c1 into c;
8 exit
when c1%notfound;
9 dbms_output.put_line(c.dname||''||c.eName);
10 end loop;
11 close c1;
12 end;
13
14 /
|
T1 |
T2 |
|
Read(P) |
Read(Q) |
|
P
= P – 2000 |
Q
= Q + 200 |
|
Write(P) |
Write(Q) |
|
Read(Q) |
Read(R) |
|
Q
= Q –200 |
R = R + 200 |
|
Write(Q) |
Write(R) |
|
T1 |
T2 |
|
Read(P) P = P – 2000 Write(P) |
|
|
|
Read(Q) Q= Q + 200 Write(Q) |
|
Read(Q) Q = Q –200 Write(Q) |
|
|
|
Read(R) R = R + 200 Write(R) |
8. 8.Consider the following
transaction. Give two non-serial schedules that the serializable :
|
T1 |
T2 |
|
Read(A) |
Read(B) |
|
A = A – 1000 |
B = B + 100 |
|
Write(A) |
Write(B) |
|
Read(B) |
Read(C) |
|
B = B –100 |
C = C + 100 |
|
Write(B) |
Write(C) |
Ans-
|
T1 |
T2 |
|
Read(A) A = A – 1000 Write(A) |
|
|
|
Read(B) B = B + 100 Write(B) |
|
Read(B) B = B –100 Write(B) |
|
|
|
Read(C) C = C + 100 Write(C) |
9. Following is the list of events in an interleaved execution of set T1,T2, T3 and T4. Assuming 2PL (Two Phase Lock). Is there a deadlock? If yes, which transactions are involved in deadlock?
|
Time |
Transaction |
Code |
|
t1 |
T1 |
Lock(A,X) |
|
t2 |
T2 |
Lock(B,S) |
|
t3 |
T3 |
Lock(A,S) |
|
t4 |
T1 |
Lock(C,X) |
|
t5 |
T2 |
Lock(D,X) |
|
t6 |
T1 |
Lock(D,S) |
|
t7 |
T2 |
Lock(C,S) |
T1 T2 T3
X(A)
S(B)
S(A)
X(C)
X(D)
S(D)
S(C)
Transaction is Deadlock. T1 ,T2 transactions are involved in deadlock .
10. The following is the list of events in an
interleaved execution of sets T1 , T2 , T3 and T4 assuming 2PL. Is there a
deadlock ? If yes, which transactions are involved in deadlock ?
|
Time |
Transaction |
Code |
|
t1 |
T1 |
Lock
(A, X) |
|
t2 |
T2 |
Lock(B,S) |
|
t3 |
T3 |
Lock
(C, S) |
|
t4 |
T4 |
Lock
(A, S) |
|
t5 |
T1 |
Lock
(C, S) |
|
t6 |
T2 |
Lock
(B, X) |
|
t7 |
T3 |
Lock
(D, X) |
|
t8 |
T4 |
Lock
(D, S) |
T1 T2 T3 T4
X(A)
S(B)
S(C)
S(A
)
S( C)
X(B)
X(D)
S(D)
Transaction is Deadlock. T1 ,T3,T4 transactions are involved in deadlock .
