Consider
the following entities and their relationship.
Bus(bus_no,
capacity, source, destination)
Driver(driver_no,
driver_name, license_no, addr, age, salary)
Relationship
between Bus and Driver is many-to-many with descriptive attribute
date_of_duty_allotted and shift
Constraints:primary
key, foreign key,primary key for third table (bus_no, driver_no,
date_of_duty_allotted),
driver_name
should not be null,shift can be morning or evening.
Create
a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write
a procedure which will display bus_no and name of allotted driver on given date
and shift.
SQL>
set serveroutput on
SQL>
create or replace procedure p1(n in varchar) as cursor c1 is select
busdriver.busno, dname, shift, dateofdutyallotted from bus, driver, busdriver
where bus.busno=busdriver.busno and driver.dno=busdriver.dno and
dateofdutyallotted='25/2/15';
2 c
c1%rowtype;
3
begin
4 open
c1;
5
dbms_output.put_line('busno'||' '||'dname');
6 loop
7
fetch c1 into c;
8 exit
when c1%notfound;
9
if(c.shift=n) then
10
dbms_output.put_line(c.busno||' '||c.dname);
11 end
if;
12 end
loop;
13
close c1;
14 end;
15 /
Procedure
created.
SQL>
begin
2
p1('morning');
3 end;
4 /
busno
dname
101
ram
PL/SQL
procedure successfully completed.
2)Write
a trigger that restricts insertion or updation of driver table if driver age is
less than 18 or greater than 50. (Raise user defined exception and give
appropriate message).
SQL>
set serveroutput on
SQL>
create or replace trigger tt2 before insert or update on driver
2 for
each row
3
begin
4
if(:new.age<18 or :new.age>50)then
5
raise_application_error(-20001,'drivers age must be greater than 18 and
less than 50.');
6 end
if;
7 end;
8 /
Trigger
created.
SQL>
insert into driver values(7,'harsh',6464,'pune',55,4969);
insert
into driver values(7,'harsh',6464,'pune',55,4969)
*
ERROR
at line 1:
ORA-20001:
drivers age must be greater than 18 and less than 50.
ORA-06512:
at "SCOTT.TT2", line 3
ORA-04088:
error during execution of trigger 'SCOTT.TT2'
SQL>
insert into driver values(8,'loki',6464,'pune',15,4969);
insert
into driver values(8,'loki',6464,'pune',15,4969)
*
ERROR
at line 1:
ORA-20001:
drivers age must be greater than 18 and less than 50.
ORA-06512:
at "SCOTT.TT2", line 3
ORA-04088:
error during execution of trigger 'SCOTT.TT2'
