Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 7 views

    13 SLIP Customer Ticket 1

    Uploaded by

    Musef Shaikh

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    13 SLIP Customer Ticket 1

    Uploaded by

    Musef Shaikh
    7 views2 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    7 views2 pages

    13 SLIP Customer Ticket 1

    Uploaded by

    Musef Shaikh

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 2

    Q.1) Consider the following entities and their relationship.

    [Marks 40]
    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:

    1) Write a procedure to display names of customer who have booked bus


    on given date.
    2) 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 cust


    (
    cno number(3) primary key,
    cname varchar2(20) not null,
    ccity varchar2(20),
    cphno number(12)
    );

    create table tick


    (
    tno number(3) primary key,
    bdate date,
    fare number(10,2) check(fare > 0),
    tdate date
    cno references cust
    );

    insert into cust values(&cno,'&cname','&ccity',&cphno);

    insert into tick values(&tno,'&bdate',&fare,'&tdate',&cno);

    ____________________________________________________________________

    1) WRITE PROC TO DISPLAY NAMES OF CUST BOOKED BUS ON GIVEN DATE

    Create or Replace Procedure proc(d tick.bdate%type)


    IS
    cursor cur IS
    select c.cname from cust c, tick t where
    c.cno = t.cno and t.bdate = d;
    begin
    dbms_output.put_line('Customers are : ');
    for i in cur
    loop
    dbms_output.put_line(i.cname);
    end loop;
    end proc;
    /

    ______________________________________________________________________

    2)

    create or replace trigger t1


    before
    insert
    on
    tick
    for each row

    declare

    begin
    if :new.tdate < :new.bdate then
    raise_application_error(-20001,'Travelling Date Cannot be Less than
    today');
    end if;
    end;

    _________________________________________________________________________
    ____

    You might also like