Consider
the following entities and their relationship.
Route(route_no,
source, destination, no_of_station)
Bus
(bus_no, capacity, depot_name)
Relationship
between Route and Bus is one-to-many
Constraints:
primary key, foreign key,depot_name should not be null,bus capacity should be
greater than 40.
Create
a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write
a procedure which will display all bus details for a given route.
SQL>
set serveroutput on
SQL>
create or replace procedure p1(n in varchar) as cursor c1 is select capacity,
depotname, route.rno from route, bus2 where route.rno=bus2.rno;
2 c
c1%rowtype;
3
begin
4 open
c1;
5
dbms_output.put_line('capacity'||' '||'depotname');
6 loop
7 fetch
c1 into c;
8 exit
when c1%notfound;
9
if(c.rno=n) then
10
dbms_output.put_line(c. capacity||' '||c.depotname);
11 end
if;
12 end
loop;
13
close c1;
14 end;
15 /
Procedure
created.
SQL>
begin
2
p1('102');
3 end;
4 /
capacity
depotname
54
nasik
PL/SQL
procedure successfully completed.
2)Write
a trigger that restricts insertion of route having number of station less than
zero. (Raise user defined exception and give appropriate message)
SQL>
set serveroutput on
SQL>
create or replace trigger tt1 before insert or update on route
2 for
each row
3
begin
4
if(:new.noofstation<=0)then
5
raise_application_error(-20001,'number of station should be greater than
zero.');
6 end
if;
7 end;
8 /
Trigger
created.
SQL>
insert into route values(106,'nasik','mumbai',0);
insert
into route values(106,'nasik','mumbai',0)
*
ERROR
at line 1:
ORA-20001:
number of station should be greater than zero.
ORA-06512:
at "SCOTT.TT1", line 3
ORA-04088:
error during execution of trigger 'SCOTT.TT1'
