Ready Project |BCA Project | MCA Project | BBA Project | MBA Project |INDIA'S BIGGEST COLLEGE PROJECT STORE

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.
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)



create table Researcher(r_no number primary key, r_name varchar2(20)not null, r_city varchar2(20));

create table Research_Paper(rp_no number primary key, rp_title varchar2(20) not null, rp_subject varchar2(20), rp_level varchar2(20));

create table R_RP(r_no number references Researcher(r_no),rp_no number references Research_Paper(rp_no),year number);


insert into Researcher values(1,'yogesh','pune');

insert into Researcher values(2,'dipali','pune');

insert into Researcher values(3,'ram','mumbai');


insert into  Research_Paper values(101,'computer study','computer','state');

insert into  Research_Paper values(102,'computer study','computer','national');

insert into  Research_Paper values(103,'electronic study','electroinc','international');

insert into  Research_Paper values(104,'finance study','finance','state');

insert into  Research_Paper values(105,'computer study','computer','national');

insert into  Research_Paper values(106,'computer study','computer','national');

insert into  Research_Paper values(107,'computer study','computer','national');

insert into  Research_Paper values(108,'computer study','computer','national');



insert into R_RP values(1,101,2015);

insert into R_RP values(1,102,2015);

insert into R_RP values(1,103,2015);

insert into R_RP values(2,101,2016);

insert into R_RP values(3,103,2015);

insert into R_RP values(2,104,2016);

insert into R_RP values(1,102,2013);

insert into R_RP values(2,102,2013);

insert into R_RP values(3,102,2013);

insert into R_RP values(1,105,2013);

insert into R_RP values(1,106,2013);

insert into R_RP values(1,107,2013);

insert into R_RP values(1,108,2013);

Q1)

create or replace procedure info_disp(sub varchar2,yr R_RP.year%type)

is

cursor c1 is select r.rp_no,r.rp_title,r.rp_level from Researcher r1,Research_Paper r,R_RP rrp 

where r1.r_no=rrp.r_no and r.rp_no=rrp.rp_no 

and r.rp_subject=sub and rrp.year=yr;

rec c1%rowtype;

begin

dbms_output.put_line(sub||'     '||yr);

open c1;

loop

fetch c1 into rec;

exit when c1%notfound;

dbms_output.put_line(rec.rp_no||'     '||rec.rp_title||'   '||rec.rp_level);

end loop;

close c1;

end;


declare

name_of_subject Research_Paper.rp_subject%type:='&name_of_subject';

year1 R_RP.year%type:=&year1;

begin

info_disp(name_of_subject,year1);

end;

Q2)


create or replace trigger t10

    before insert or update on R_RP

    for each row

    begin

    if (:new.year<2010) then

    raise_application_error(-2010,'year should be greater than current 2010');

    end if;

    end;

Search

Project Categories

Recent Posts

Sign In