Slip 15
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:
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 /
