25) 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 procedure which will display crop detail harvested by given farmer in given year.
2)Write a trigger which will restricts insertion or updation of crop_farmer table having year greater than current year. (Raise user defined exception and give appropriate message)
create or replace procedure info_disp(fname varchar2,yr C_F.year%type)
is
cursor c1 is select c.c_no,c.c_name,c_season,c.pesticides from Crop c ,Farmer f ,C_F cf
where c.c_no=cf.c_no and f.f_no=cf.f_no
and f.f_name=fname and cf.year=yr;
rec c1%rowtype;
begin
dbms_output.put_line(fname||' '||yr);
open c1;
loop
fetch c1 into rec;
exit when c1%notfound;
dbms_output.put_line(rec.c_no||' '||rec.c_name||' '||rec.c_season||' '||rec.pesticides);
end loop;
close c1;
end;
declare
name_of_farmer Farmer.f_name%type:='&name_of_farmer';
year1 C_F.year%type:=&year1;
begin
info_disp(name_of_farmer,year1);
end;
Q2)
create or replace trigger t9
before insert or update on C_F
for each row
begin
if (:new.year<:old.year) then
raise_application_error(-2010,'year should be enter less than current year');
end if;
end;
Search
Recent Posts
Mail Management System
Posted on 2019-07-18Online food ordering system
Posted on 2019-07-18Library Management System
Posted on 2019-07-17Health center system project
Posted on 2019-07-17Gym Management System
Posted on 2019-07-17furniture management system
Posted on 2019-07-17Electronic shop management system
Posted on 2019-07-17Automobile Workshop Management
Posted on 2019-07-17Online Visa Processing System
Posted on 2019-07-17Inventory management System
Posted on 2019-07-17petrol-management system
Posted on 2019-07-17Cloths management system
Posted on 2019-07-17Society Management system
Posted on 2019-07-17Mall management system
Posted on 2019-07-17school management system
Posted on 2019-07-17Sales Order Processing System
Posted on 2019-07-17Retail sales management
Posted on 2019-07-17Raw Materials Management
Posted on 2019-07-17railway reservation system
Posted on 2019-07-17purchase and sales management system
Posted on 2019-07-17Placement Management System
Posted on 2019-07-17Pet Shop Management System
Posted on 2019-07-17petrol pump management system
Posted on 2019-07-17Patient Information System
Posted on 2019-07-17news agency system
Posted on 2019-07-17Cinema Booking System
Posted on 2019-07-17Medical Store System
Posted on 2019-07-17leave management System
Posted on 2019-07-17Laboratory Information Management System
Posted on 2019-07-17content management system
Posted on 2019-07-17Inventory management System
Posted on 2019-07-17Institute Management System
Posted on 2019-07-17Hotel management System
Posted on 2019-07-17Gym Management System
Posted on 2019-07-17Garage Management System
Posted on 2019-07-17Furniture shop management system
Posted on 2019-07-17Fisheries management System
Posted on 2019-07-17Fertilizer scheduling system
Posted on 2019-07-17online eye care system
Posted on 2019-07-17Dental Clinic Management System
Posted on 2019-07-17Cyber Café Management
Posted on 2019-07-17Milk Billing System
Posted on 2019-07-17Colddrink management system
Posted on 2019-07-17Cable management System
Posted on 2019-07-17Beauty parlor management system
Posted on 2019-07-17Facebook Clone
Posted on 2019-05-28Dance Class Management System
Posted on 2019-05-24Library Management System
Posted on 2019-05-24Cab Management System
Posted on 2019-05-23Blood Bank Management system
Posted on 2019-05-23Beauty Parlour Management System
Posted on 2019-05-23vissa proccesing system
Posted on 2019-05-23Toll Plazza
Posted on 2019-05-23BILLING APPLICATION
Posted on 2019-05-23FLORICULTURE MANAGEMENT SYSTEM
Posted on 2019-05-23Car On Rent
Posted on 2019-05-23E-commers Shop
Posted on 2019-05-23