20) Consider the following entities and their relationship.
Drug(d_no, d_name, company, price)
Medical _Store(m_no,m_name,m_city,ph_no)
Relationship between Drug and Medical_Store is many-to-many with descriptive attribute quantity.
Constraints: primary key, foreign key,
m_name and d_name should not be null,
m_city can be pune or pimpri
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a function which will count total number of drugs available in given medical store.
Write a cursor which will display medical store wise drug details with available quantity.
Q1)create or replace function fun1(mno in number)
return number
is
total number;
begin
select count(d.d_no) into total from Drug d,Med_s ms,D_M dm
where d.d_no=dm.d_no and ms.m_no=dm.m_no
and dm.m_no=mno;
return total;
end;
Q2)
declare
cursor c1 is select quantity from D_M;
cursor c2 (qty D_M.quantity%type) is select d.* from
Drug d,Med_s ms,D_M dm
where d.d_no=dm.d_no and ms.m_no=dm.m_no and quantity=qty;
begin
for x in c1 loop
dbms_output.put_line(x.quantity);
for y in c2 (x.quantity) loop
dbms_output.put_line(y.d_no||' '||y.d_name||' '||y.company||' '||y.price);
end loop;
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