15) Consider the following entities and their relationship.
Person (p_no, p_name, p_addr)
Investment (inv_no, inv_name, inv_date, inv_amt)
Relationship between Person and Investment is one-to-many.
Constraints: primary key, foreign key,
p_name and inv_name should not be null,
inv_amt should be greater than 10000.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a procedure which will display details of person, made investment on given date.
Write a trigger that restricts insertion or updation of investment having inv_date greater than current date. (Raise user defined exception and give appropriate message)
create table person
(p_no number(10) primary key,p_name varchar(15) not null,p_addr varchar(15));
insert into pers values(1,'Abhi','pimpri');
insert into pers values(2,'Ajay','pune');
create table investment
(inv_no number(10) primary key,inv_name varchar(15) not null,inv_date date,inv_amt number(10),p_no number(10) references person,check(inv_amt>10000));
insert into investment values(101,'Ajay','21-DEC-2001',20000,1);
insert into investment values(102,'Vijay','21-DEC-2002',20000,2);
Q1)
create or replace procedure p1(dt in date)
as
cursor c1 is select p.p_no,p.p_name,p.p_addr from person1 p,investment1 i
where p.p_no=i.p_no and inv_date=dt;
begin
for x in c1 loop
dbms_output.put_line(x.p_no||' '||x.p_name||' '||x.p_addr);
end loop;
end;
declare
begin
p1('21-dec-2001');
end;
Q2)
create or replace trigger t8
before insert or update on investment
for each row
Begin
if(:new.inv_date < sysdate) then
raise_application_error(-20001,'inv_date should be greater than sysdate');
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