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

10) Consider the following entities and their relationship.
Crop (c_no, c_name, c_season, pesticides)
Farmer (f_no, f_name, f_location)
Relationship between Crop and Farmer is many-to-many with descriptive attribute year.
Constraints: primary key, foreign key,
primary key for third table(c_no, f_no, year),
c_name and f_name should not be null,
c_season can be rabi or kharif.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a function which will return total number of farmers harvesting given crop in a given year.
2)Write a cursor which will display season wise information of crops harvested by the farmers. (Use parameterized cursor)


create table Crop (c_no number primary key, c_name varchar2(20)not null, c_season varchar2(20), pesticides varchar2(20));

create table Farmer(f_no number primary key, f_name varchar2(20)not null, f_location varchar2(20));

create table C_F(c_no number references Crop(c_no),f_no number references Farmer(f_no),year number,primary key(c_no,f_no,year));

insert into Crop values(1,'wheat','rabbi','selfer');

insert into Crop values(2,'rise','rabbi','co2');

insert into Crop values(3,'mung','kharip','calcium');

insert into Crop values(4,'jowar','kharip','phospet');


insert into Farmer values(101,'ram','pune');

insert into Farmer values(102,'ganesh','pune');

insert into Farmer values(103,'rajesh','nashik');



insert into C_F values(1,101,2014);

insert into C_F values(1,102,2014);

insert into C_F values(1,103,2014);

insert into C_F values(2,101,2015);

insert into C_F values(3,101,2015);

insert into C_F values(4,103,2016);


Q1)


create or replace function fun1(yr in number)

return number

is 

total number;

begin

select count(f.f_no) into total from Crop c ,Farmer f ,C_F cf 

where c.c_no=cf.c_no and f.f_no=cf.f_no and cf.year=yr;

return total;

end;


select fun1(2014) from dual;


Q2)

declare

      cursor c1 is select distinct c_season from Crop;

      cursor c2 (se Crop.c_season%type) is select distinct c.c_no , c.c_name ,c.pesticides,f.f_name

       from  Crop c ,Farmer f ,C_F cf 

      where c.c_no=cf.c_no and f.f_no=cf.f_no and c.c_season=se;

      begin

      for a in c1 loop

      dbms_output.put_line(a.c_season);

      for c in c2 (a.c_season) loop

     dbms_output.put_line('crop no:='||c.c_no||'crop name:='   ||c.c_name||   'pesticides:='||c.pesticides||'farmer name:='||c.f_name);

      end loop;

      end loop;

      end;

Search

Project Categories

Recent Posts

Sign In