RDBMS-Slip11

SLIP 11

Consider the following entities and their relationship.                            
Researcher(r_no, r_name, r_city)
Research_Paper (rp_no, rp_title, rp_subject, rp_level)
Relationship between Researcher and Research_Paper is many-to-many with descriptive attribute year.
Constraints:primary key, foreign key,r_name and rp_title should not be null,rp_subject can be computer, electronics or finance.rp_level can be state, national or international.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a procedure which will display details of research paper of a given subject for a specified year.
SQL> set serveroutput on
SQL> create or replace procedure p1(n in varchar) as cursor c1 is select rptitle,rpsubject,rplevel from researchpaper,researcherpaper where researchpaper.rpno=researcherpaper.rpno and year=2015;
  2  c c1%rowtype;
  3  begin
  4  open c1;
  5  dbms_output.put_line('rptitle'||' '||'rpsubject'||' '||'rplevel');
  6  loop
  7  fetch c1 into c;
  8  exit when c1%notfound;
  9  if(c.rpsubject=n) then
 10  dbms_output.put_line(c.rptitle||' '||c. rpsubject ||' '||c.rplevel);
 11  end if;
 12  end loop;
 13  close c1;
 14  end;
 15  /

Procedure created.
SQL> begin
  2  p1('computer');
  3  end;
  4  /


rptitle rpsubject rplevel
hardware computer international
software computer international
PL/SQL procedure successfully completed.


2)Write a trigger before insert or update of each row of research_paper published after 2010 be entered into table. (Raise user defined exception and give appropriate message)
SQL> set serveroutput on
SQL> create or replace trigger t1 before insert or update on researcherpaper
  2  for each row
  3  begin
  4  if(:new.year>2010)then
  5  raise_application_error(-20001,'publish must be before 2010');
  6  end if;
  7  end;
  8  /

Trigger created.

SQL> insert into researcherpaper values(106,6,2013);
insert into researcherpaper values(106,6,2013)
            *
ERROR at line 1:
ORA-20001: publish must be before 2010
ORA-06512: at "SCOTT.T1", line 3
ORA-04088: error during execution of trigger 'SCOTT.T1'