Consider
the following entities and their relationship.
Customer
(c_no, c_name, c_city, c_ph_no)
Ticket
(t_no, booking_date, fare, traveling_date)
Relationship
between Customer and Ticket is one-to-many.
Constraints:
primary key, foreign key,c_name should not be null,fare should be greater than
zero.
Create
a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write
a procedure to display names of customer who have booked bus on given date.
SQL>set
serveroutput on
SQL>
create or replace procedure p1(n in varchar) as cursor c1 is select cname,
bookingdate,travelingdate from customer,ticket where customer.cno=ticket.cno;
2 c
c1%rowtype;
3
begin
4 open
c1;
5
dbms_output.put_line('cname'||''||' bookingdate'||''||' travelingdate');
6 loop
7
fetch c1 into c;
8 exit
when c1%notfound;
9
if(c. bookingdate=n) then
10
dbms_output.put_line(c.cname||''||c. bookingdate||''||c. travelingdate);
11 end
if;
12 end
loop;
13
close c1;
14 end;
15 /
Procedure
created.
SQL>
begin
2
p1('5/4/15');
3 end;
4 /
cname
bookingdate travelingdate
lokesh5/4/1512/5/15
PL/SQL
procedure successfully completed.
2)Write
a trigger that restricts insertion of ticket having traveling date smaller than
booking date.(Raise user defined exception and give appropriate message)
SQL>
set serveroutput on
SQL>
create or replace trigger t1 before insert or update on ticket
2 for each row
3
begin
4
if(:new.travelingdate<:new.bookingdate)then
5
raise_application_error(-20001,'travelingdate>bookingdate');
6 end
if;
7 end;
8 /
Trigger
created.
SQL>
insert into ticket values(103,'16/12/11',600,'8/12/11',2);
insert
into ticket values(103,'16/12/11',600,'8/12/11',2)
*
ERROR
at line 1:
