Slip 3 - C) Consider the following entities and their relationship. Newspaper (name,language , publisher , cost ) Cities (pincode , city, state) Relationship between Newspaper and Cities is many-to-many with descriptive attribute daily required Constraints: name and pincode primary key Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1) Write a trigger which will fire before insert on the cities table which check that the pincode must be of 6 digit. (Raise user defined exception and give appropriate message). 2) Write a procedure to calculate city wise total cost of each newspaper

Solution:

SQL> create table newspaper(nno int,name varchar(15) primary key,language varchar(10),publisher  varchar(10),cost int);

SQL>  insert into newspaper values(1,'punetimes','marathi','abc',5);

SQL> create table citys(cno int primary key,city varchar(10),state varchar(15));

SQL> insert into citys values(1,'pune','mh');

SQL> create table nc(nno int,cno int,daily_required varchar(10));

SQL> insert into nc values(1,1,'yes');

1)Write a trigger which will fire before insert on the cities table which check that the pincode must be of 6 digit. (Raise user defined exception and give appropriate message).

SQL> set serveroutput on

SQL> create or replace trigger t1 before insert or update on nc

  2  for each row

  3  begin   

  4  if(:new.pincode=6)then

  5  raise_application_error(-20001,’Insert Invalid Record');

  6  end if;

  7  end;

  8  /

2)Write a procedure to calculate city wise total cost of each newspaper.

SQL> set serveroutput on

SQL>  create or replace procedure p6(t in varchar)as cursor c2 is select city,cost  from newspaper,c

itys,nc where cost=(select sum(cost)from newspaper) and newspaper.nno=nc.nno and citys.cno=nc.cno;

  2         c c2 %rowtype;

  3          begin

  4          open c2;

  5          dbms_output.put_line('city'||''||'cost');

  6          loop

  7          fetch c2 into c;

  8          exit when c2 %notfound;

  9         if(c.city=t) then

 10       dbms_output.put_line(c.city||''||c.cost);

 11       end if;

 12       end loop;

 13       close c2;

 14       end;

 15  /

Procedure created.

SQL> begin

  2  p6('pune');

  3  end;

  4  /

citycost

pune5

PL/SQL procedure successfully completed.

slip 3-

SQL> set serveroutput on

SQL>  create or replace procedure p5(n in varchar) as sum_cost newspaper.cost %type;

  2     begin

  3     select sum(cost) into sum_cost from newspaper,citys,nc where newspaper.nno=nc.nno and citys.

cno=nc.cno;

  4    if(sum_cost > 0) then

  5      dbms_output.put_line('sum cost='||sum_cost);

  6     else

  7     dbms_output.put_line('cost cannot be calculated');

  8     end if;

  9      end p5;

 10  /

Procedure created.

SQL> execute p5('5');

sum cost=15

PL/SQL procedure successfully completed.