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

6) Consider the following entities and their relationship.
Train(t_no, t_name)
Passenger (p_no, p_name, addr, age)
Relationship between Train and Passenger is many-to-many with descriptive attribute date, seat_no and amt.
Constraints : primary key, foreign key,
primary key for third table (t_no, p_no, date),
t_name and p_name should not be null,
amt should be greater than zero.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a function which will display train details having maximum passenger for a given date.
Write a cursor which will display date wise train and their passenger details.


 create table Train(t_no number primary key, t_name varchar2(20) not null);

create table Passenger(p_no number primary key, p_name varchar2(20) not null, addr varchar2(20), age number);

create table T_P(t_no number references Train(t_no),p_no number references Passenger(p_no),date1 date,seat_no number,amt number,primary key(t_no,p_no,date1));

insert into Train values(2,'mumbai Express');

insert into Passenger values(101,'yogesh','pune',25);

insert into Passenger values(102,'raj','pune',15);

insert into Passenger values(103,'amol','mumbai',22);

insert into T_P values(1,101,'01-july-2016',1001,600);

insert into T_P values(1,102,'01-july-2016',1002,600);

insert into T_P values(1,103,'02-july-2016',1003,600);

insert into T_P values(2,101,'07-july-2016',1004,500);

insert into T_P values(1,102,'01-july-2014',1005,790);




Q1)

create or replace function f1

(dat T_P.date1%type)

return number is

num number;

Begin

select (tp.t_no) into num

from Train t,Passenger p,T_P tp

where t.t_no=tp.t_no

and p.p_no=tp.p_no

and date1=dat

group by tp.t_no having count(tp.t_no)>=all(select count(p_no) from T_P tp

where date1=dat group by t_no);

return num;

end;

Q2)


declare 

     cursor c1(dt T_P.date1%type)

     is select t.t_no,t.t_name,p.p_no,p_name,addr,age from Train t,Passenger p,T_P tp

    where t.t_no=tp.t_no and p.p_no=tp.p_no and date1=dt;

    
    res c1%rowtype;

    v_date T_P.date1%type;

   begin

   for res in c1(&v_date)

   loop

   dbms_output.put_line(res.t_no||' '||res.t_name||' '||res.p_name||'  '||res.addr||'  '||res.age);

  end loop;

  end;

Search

Project Categories

Recent Posts

Sign In