Ready Project |BCA Project | MCA Project | BBA Project | MBA Project |INDIA'S BIGGEST COLLEGE PROJECT STORE

4) 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:
Write a function which will return name of driver having maximum salary.
Write a cursor which will display date wise bus and their driver details.


 create table Bus(bus_no number primary key, capacity number, source varchar2(20), dest varchar2(20));

create table Driver1(driver_no number primary key, d_name varchar2(20), l_no number, addr varchar2(20), age number, sal number);

create table B_D(bus_no number references Bus(bus_no),driver_no number references Driver1(driver_no),date_of_alloted date ,shift varchar2(20),primary key(bus_no,driver_no,date_of_alloted));


insert into Bus values(101,56,'nigdi','pune');

insert into Bus values(102,54,'chikhali','akurdi');

insert into Bus values(103,36,'nigdi','pimpri');

insert into Driver1 values(201,'ram',1001,'pune',34,30000);

insert into Driver1 values(202,'rajesh',1002,'pune',23,20000);

insert into Driver1 values(203,'akash',1003,'chikhali',22,15000);

insert into B_D values(101,201,'02-aug-2016','morning');

insert into B_D values(101,202,'04-sep-2016','morning');

insert into B_D values(101,203,'02-july-2016','evening');

insert into B_D values(102,202,'01-june-2016','evening');

Q1)


create or replace function fun1

    return number

    is

    a Driver1.sal%type;

    begin

    select max(sal) into a from Driver1;

    return a;

   end;


declare

      nm Driver1.d_name%type;

      b Driver1.sal%type;

    begin

    b:=fun1();

    select d_name into nm from Driver1

    where sal=b;

    dbms_output.put_line('name of driver=  '||nm);

    end;

	
Q2)


declare

    cursor c1 is select distinct date_of_alloted from B_D;

    cursor c2 (date1 B_D.date_of_alloted%type) is select d.* from 

    Bus b,Driver1 d,B_d bd

    where b.bus_no=bd.bus_no and d.driver_no=bd.driver_no and date_of_alloted= date1;

    begin

    for x in c1 loop

    dbms_output.put_line(x.date_of_alloted);

    for y in c2 (x.date_of_alloted) loop

   dbms_output.put_line(y.driver_no||' '||y.d_name||' '||y.l_no||' '||y.addr||' '||y.age);

   end loop;

   end loop;

   end;

Search

Project Categories

Recent Posts

Sign In