Slip 15 - C) Consider the following entities and their relationships. Driver (driver_id, driver_name, address) Car (license_no, model, year) Relation between Driver and Car is Many to Many with date and time as descriptive attribute. Constraint: Primary key, driver_name should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1) Write a function which will display the total number of person who are using “Swift” car 2) Write a trigger which will fire before insert or update on year. If year value is more than current year. (Raise user defined exception and give appropriate message)

Solution:

SQL> create table driver(did int primary key,dname varchar(15),address varchar(20));

SQL> create table car(lno int primary key,model  varchar(20),year int);

SQL> create table dc(did int,lno int,t_date varchar(15),time varchar(10));

SQL> insert into driver values(1,'sonu','pune');

SQL> insert into car values(101,'swift','2000');

SQL> insert into dc values(1,101,'15-2-2000','9am');

1)Write a function which will display the total number of person who are using “Swift” car

SQL> set serveroutput on

SQL> create or replace function fun2(bcd in varchar)return number as efg number;

  2  begin

  3  select sum(driver.did)into efg from driver,car,dc where driver.did=dc.did and car.lno=dc.lno and car.model='swift';

  4  if sql %found then

  5  return (efg);

  6  else

  7  return null;

  8  end if;

  9  end fun2;

 10  /

Function created.

SQL> begin

  2  dbms_output.put_line('person-'||fun2('swift'));

  3  end;

  4  /

person-8

PL/SQL procedure successfully completed.

2)Write a trigger which will fire before insert or update on year. If year value is more than current year. (Raise user defined exception and give appropriate message)

SQL> set serveroutput on

SQL> create or replace trigger t1 before insert or update on car

  2  for each row

  3  declare

  4  d1 varchar(15);

  5  d2 varchar(15);

  6  begin

  7  d1:=to_char(:new. t_date,'dd-mm-yyyy');

  8  d2:=to_char(sysdate,'dd-mm-yyyy');

  9  if(d1>d2) then

 10  raise_application_error(-20001,'year value should be less than current year.');

 11  end if;

 12  end;

 13  /