RDBMS Slip No.3

Consider the following entities and their relationship.  

Newspaper (name,language , publisher , cost ) 

Cities (pincode , city, state) 

Relationship between Newspaper and Cities is many-to-many with descriptive attribute daily required Constraints: name and pincode primary key Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

1) Write a trigger which will fire before insert on the cities table which check that the pincode must be of 6 digit. (Raise user defined exception and give appropriate message). 

2) Write a procedure to calculate city wise total cost of each newspaper.

Relationship between Newspaper and Cities is many-to-many with descriptive attribute daily required Constraints: name and pincode primary key Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

Answer : 

create table newspaper(
name varchar2(30) primary key not null,
language varchar2(20),
publisher varchar2(30),
cost integer not null);

insert into newspaper values('sakal','Marahti','Rahul Gadpale',07)
insert into newspaper values('lokamat','Hindi','Rajendra Darda',5)
insert into newspaper values('Indian times','English',' Jaideep Bose',10)
insert into newspaper values('Malayala','Malayalam','Mammen Mathew',04)

create table cities(
pincode integer primary key not null,
city varchar2(20),
state varchar2(30));

insert into cities values(413101,' Pune','Maharashtra')
insert into cities values(400018,'Mumbai','Maharashtra')
insert into cities values(400017,'Mumbai','Maharashtra')
insert into cities values(189895,'Kottayam','Kerala')

create table details(
details1 integer primary key not null,
name1 varchar2(30) references newspaper(name),
pincode1 integer references cities(pincode));

insert into details values(111,'sakal',413101)
insert into details values(222,'lokamat',413101)
insert into details values(333,'Indian times',400018)
insert into details values(444,'Malayala',400017)
insert into details values(555,'sakal',189895)

1) Write a trigger which will fire before insert on the cities table which check that the pincode must be of 6 digit. (Raise user defined exception and give appropriate message). 

Answer : 

create or replace procedure pubname(pub_name1 in newspaper.language%type)
as
cursor sau is select name, cost, qty
from newspaper, cities, details
where cities.pincode=details.pincode1 and newspaper.name=details.name1
order by cost desc;
prod number;
name varchar2(30);
cost number(10);
qty number(10);

begin
open sau;
loop
fetch sau into name,cost,qty;
exit when sau%notfound;
prod:=cost*qty;
DBMS_OUTPUT.PUT_LINE('customer_id =' || name);
DBMS_OUTPUT.PUT_LINE('quantity value =' || cost);
DBMS_OUTPUT.PUT_LINE('price =' || qty);
DBMS_OUTPUT.PUT_LINE('The total value of customer purchases is = ' || prod);
end loop;
close sau;
END

declare
pub_name5 newspaper.language%type;
begin
pubname(pub_name5);
end;

2) Write a procedure to calculate city wise total cost of each newspaper.

Answer : 

create or replace trigger pincode
before insert or update of pincode on cities
for each row
begin
if:new.pincode=6 then
raise_application_error(-20001,'insert valid price');
end if;
end;