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:
1)Write a package, which consists of one procedure and one function. Pass drug number as a parameter to procedure and display details of that drug. Pass city as a parameter to a function and return total number of medical_store in given city.
2)Write a trigger that restricts insertion and updation of drug having price less than zero. (Raise user defined exception and give appropriate message)
SQL> Set serveroutput on
SQL> Create or replace trigger t5 before insert or update on drug
2 For each row
3 Begin
4 If(:new.price<=0) then
5 Raise_application_error(-20001,'price>0');
6 End if;
7 End;
8 /
Trigger created.
SQL> insert into drug values(106,'nutri','dabur',0);
insert into drug values(106,'nutri','dabur',0)
*
ERROR at line 1:
ORA-20001: price>0
ORA-06512: at "SCOTT.T5", line 3
ORA-04088: error during execution of trigger 'SCOTT.T5'
