Consider the following entities and their relationships.
Student (rollno, sname,
class, timetable)
Lab (LabNo, LabName,
capacity, equipment)
Relation between Student
and Lab is Many to One. Constraint: Primary Key, capacity should not be null.
Create a RDB in 3NF and
write PL/SQL blocks in Oracle for the following:
1)Write a procedure to
display details of students which perform practical sessions in a given Lab.
SQL> set serveroutput
on
SQL> create or
replace procedure p1(n in varchar) as cursor c1 is select sname,LabName,class from Student,Lab
where Student.LabNo=Lab.LabNo;
2 c c1
%rowtype;
3
begin
4 open
c1;
5
dbms_output.put_line('sname'||''||'LabName'||''||'class');
6
loop
7 fetch
c1 into c;
8 exit
when c1 %notfound;
9
if(c.pname=n)then
10
dbms_output.put_line(c.sname||''||c.LabName||''||C.class);
11 end
if;
12 end
loop;
13
close c1;
14
end;
15 /
2)Write a trigger which
will fire before delete on Lab (Raise user defined exception and give
appropriate message)
SQL> set serveroutput on
SQL> create or replace trigger t4 before delete on
Lab
2 for
each row
3
begin
4
if(:new.LabName=0)then
5
raise_application_error(-20001,'LabName>0');
6 end
if;
7
end;
8 /
