1) Consider the following entities and their relationship.
Customer (c_no, c_name, c_city, c_ph_no)
Ticket (t_no, booking_date, fare, traveling_date)
Relationship between Customer and Ticket is one-to-many.
Constraints: primary key, foreign key
c_name should not be null,
fare should be greater than zero.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a procedure to display names of customer who have booked bus on given date.
Write a trigger that restricts insertion of ticket having traveling date smaller than booking date.(Raise user defined exception and give appropriate message)
create table Customer(c_no number primary key, c_name varchar2(20), c_city varchar2(20),phno number);
create table Ticket(t_no number primary key, b_date date, fare number, t_date date,c_no number references Customer(c_no));
insert into Customer values(1,'yogesh','pune',9096188754);
insert into Customer values(2,'Dipali','pune',9096188756);
insert into Customer values(3,'amol','mumbai',9096188554);
insert into Ticket values(1,'02-july-2016',600,'06-july-2016',1);
insert into Ticket values(2,'02-july-2016',600,'09-july-2016',2);
insert into Ticket values(3,'02-july-2016',600,'05-july-2016',3);
insert into Ticket values(4,'06-aug-2016',600,'06-sep-2016',1);
create or replace procedure p1(dt in date)
as
cursor c1 is select c.c_name from Customer c, Ticket t
where c.c_no=t.c_no and b_date=dt;
begin
for x in c1 loop
dbms_output.put_line(x.c_name);
end loop;
end;
declare
begin
p1('02-july-2016');
end;
Q2)
create or replace trigger t11
before insert or update on Ticket
for each row
begin
if(:new.b_date>:new.t_date) then
raise_application_error(-2013,'the booking date should be less then traveling date');
end if;
end;
Search
Recent Posts
Mail Management System
Posted on 2019-07-18Online food ordering system
Posted on 2019-07-18Library Management System
Posted on 2019-07-17Health center system project
Posted on 2019-07-17Gym Management System
Posted on 2019-07-17furniture management system
Posted on 2019-07-17Electronic shop management system
Posted on 2019-07-17Automobile Workshop Management
Posted on 2019-07-17Online Visa Processing System
Posted on 2019-07-17Inventory management System
Posted on 2019-07-17petrol-management system
Posted on 2019-07-17Cloths management system
Posted on 2019-07-17Society Management system
Posted on 2019-07-17Mall management system
Posted on 2019-07-17school management system
Posted on 2019-07-17Sales Order Processing System
Posted on 2019-07-17Retail sales management
Posted on 2019-07-17Raw Materials Management
Posted on 2019-07-17railway reservation system
Posted on 2019-07-17purchase and sales management system
Posted on 2019-07-17Placement Management System
Posted on 2019-07-17Pet Shop Management System
Posted on 2019-07-17petrol pump management system
Posted on 2019-07-17Patient Information System
Posted on 2019-07-17news agency system
Posted on 2019-07-17Cinema Booking System
Posted on 2019-07-17Medical Store System
Posted on 2019-07-17leave management System
Posted on 2019-07-17Laboratory Information Management System
Posted on 2019-07-17content management system
Posted on 2019-07-17Inventory management System
Posted on 2019-07-17Institute Management System
Posted on 2019-07-17Hotel management System
Posted on 2019-07-17Gym Management System
Posted on 2019-07-17Garage Management System
Posted on 2019-07-17Furniture shop management system
Posted on 2019-07-17Fisheries management System
Posted on 2019-07-17Fertilizer scheduling system
Posted on 2019-07-17online eye care system
Posted on 2019-07-17Dental Clinic Management System
Posted on 2019-07-17Cyber Café Management
Posted on 2019-07-17Milk Billing System
Posted on 2019-07-17Colddrink management system
Posted on 2019-07-17Cable management System
Posted on 2019-07-17Beauty parlor management system
Posted on 2019-07-17Facebook Clone
Posted on 2019-05-28Dance Class Management System
Posted on 2019-05-24Library Management System
Posted on 2019-05-24Cab Management System
Posted on 2019-05-23Blood Bank Management system
Posted on 2019-05-23Beauty Parlour Management System
Posted on 2019-05-23vissa proccesing system
Posted on 2019-05-23Toll Plazza
Posted on 2019-05-23BILLING APPLICATION
Posted on 2019-05-23FLORICULTURE MANAGEMENT SYSTEM
Posted on 2019-05-23Car On Rent
Posted on 2019-05-23E-commers Shop
Posted on 2019-05-23