28) Consider the following entities and their relationship.
Client (c_no, c_name, c_addr, birth_date)
Policy_info (p_no, p_name, maturity_amt, prem_amt, policy_term)
Relationship between Client and Policy_info is many-to-many with descriptive attribute date_of_purchase.
Constraints: primary key, foreign key,
c_name and p_name should not be null,
policy_term should be greater than zero.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a function which will return name of policy taken by minimum number of clients.
2)Write a cursor which will display client wise policy details. (Use parameterized cursor)
Q1)
create or replace function f1
return varchar is
pnm varchar(15);
begin
select p_name into pnm
from policy_info,cp_info
where policy_info.p_no=cp_info.p_no
group by p_name having count(cp_info.c_no)<=all(select count(c_no) from cp_info
group by c_no);
return pnm;
end;
Q2)
Declare
cursor c1 is select c_name from client;
cursor c2 (cnm client.c_name%type) is
select p_name,m_amt,p_amt,p_term,dop
from client,policy,clientp
where client.c_no=clientp.c_no
and policy.p_no=clientp.p_no
and c_name=cnm;
r1 c1%rowtype;
r2 c2%rowtype;
Begin
open c1;
loop
fetch c1 into r1;
exit when c1%notfound;
dbms_output.put_line(r1.c_name);
open c2(r1.c_name);
loop
fetch c2 into r2;
exit when c2%notfound;
dbms_output.put_line(r2.p_name||r2.m_amt||r2.p_amt||r2.p_term||r2.dop);
end loop;
close c2;
end loop;
close c1;
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