3) Consider the following entities and their relationship.
Owner (o_no, o_name, o_city, o_ph_no)
Estate (e_no, e_type, e_city, e_price)
Relationship between Owner and Estate is one-to-many.
Constraints : primary key, foreign key,
o_name should not be null,
e_type can be flat, bungalow or land.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a procedure which will accept owner number and display details of all estates of given owner which belongs to pune city.
Write a cursor which will display type wise estate details. (Use parameterized cursor)
create table Owner(o_no number primary key, o_name varchar2(20), o_city varchar2(20), phno number(10));
create table Estate(e_no number primary key, e_type varchar2(20), e_city varchar2(20), e_price number(10),o_no number references Owner(o_no));
insert into Owner values(1,'yogesh','pune',9096188754);
insert into Owner values(2,'dipali','pune',9096188755);
insert into Owner values(3,'raj','pune',9096188756);
insert into Estate values(101,'flat','pune',4000000,1);
insert into Estate values(102,'land','pune',4500000,1);
insert into Estate values(103,'bunglow','mumbai',4100000,1);
insert into Estate values(104,'land','nashik',4900000,2);
Q1) create or replace procedure p7(wn in number)
as
cursor c1 is select e.e_no,e.e_type,e.e_city,e.e_price from Owner o ,Estate e
where o.o_no=e.o_no and o.o_city='pune' and o.o_no=wn;
begin
for x in c1 loop
dbms_output.put_line(x.e_no||' '||x.e_type||' '||x.e_city||' '||x.e_price);
end loop;
end;
declare
begin
p7(1);
end;
Q2)declare
cursor c1(name Estate.e_type%type)
is select e_no,e_type,e_city,e_price
from Estate
where e_type=name;
res c1%rowtype;
v_name Estate.e_type%type;
begin
for res in c1(&v_name)
loop
dbms_output.put_line(res.e_no||' '||res.e_type||' '||res.e_city||' '||res.e_price);
end loop;
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