RDBMS Slip No 4

Consider the following entities and their relationships.  

Client (client_no, client_name, address, birthdate) 

Policy_info (policy_no, desc, maturity_amt, prem_amt, date) 

Relation between Client and Policy_info is Many to Many Constraint: Primary key, prem_amt and maturity_amt should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

1) Write a procedure which will display all policy details having premium amount less than 5000. 

2) Write a trigger which will fire before insert or update on policy_info having maturity amount less than premium amount.

(Raise user defined exception and give appropriate message)

Consider the following entities and their relationships.  

Client (client_no, client_name, address, birthdate) 

Policy_info (policy_no, desc, maturity_amt, prem_amt, date) 

Relation between Client and Policy_info is Many to Many Constraint: Primary key, prem_amt and maturity_amt should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

Answer : 

create table client(client_no integer primary key not null, client_name varchar2(30), address varchar2(30), birthdate date);
insert into client values (11,'amit','pune','20-jan-2020');
insert into client values (12,'ramesh','satara','20-jun-2019');
insert into client values (13,'radh','baramati','1-jan-2020');
insert into client values (14,'sham','solapur','27-dec-2019');
create table policy_info(policy_no integer primary key not null, desc1 varchar2(30), maturity_amt integer, prem_amt integer, date1 date);
insert into policy_info values(21,'complet',10000,25000,'21-jan-2020');
insert into policy_info values(22,'incomplet',2000,10000,'30-jun-2020');
insert into policy_info values(23,'complet',100,200,'20-may-2020');
insert into policy_info values(24,'incomplet',1500,20050,'20-feb-2020');
create table info(info integer primary key not null, client_no1 integer references client(client_no), policy_no1 integer references policy_info(policy_no));
insert into info values(31,11,21);
insert into info values(32,14,24);
insert into info values(33,13,22);
insert into info values(34,12,23);

1) Write a procedure which will display all policy details having premium amount less than 5000. 

Answer : 

create or replace function point return number

is
total_count number;
begin
select maturity_amt into total_count from client,policy_info,info where client.client_no=info.client_no1 and policy_info.policy_no=info.policy_no1 and policy_no=21;
return total_count;
end;
call function-----------
1)By using select keyword
select point from dual
2)By using calling program

declare
total_count2 number;
begin

total_count2:=point;
dbms_output.put_line('Total maturity amount of policies of a particular client :- '||total_count2);
end;

2) Write a trigger which will fire before insert or update on policy_info having maturity amount less than premium amount.

declare
cursor p_startmonth is select policy_no,desc1,maturity_amt from policy_info where date1='30-jun-2020';
begin
for x in p_startmonth loop
dbms_output.put_line('the p no is:'||x.policy_no);
dbms_output.put_line('the p name is:'||x.desc1);
dbms_output.put_line('the p status is:'||x.maturity_amt);
end loop;
end;