Please help me

From: Cathy <cathy_at_avkk.yamaha.co.jp>
Date: Tue, 9 Jan 2001 11:36:36 +0700
Message-ID: <3a5a86d5.0_at_news2.tm.net.my>


Hi my name is Cathy, can someone help me with the below question to help me with the answers for the below exercise

Thanking in advance

Entity List
Train (TrainNo#, Date, Time)



Passengers(PassengerNo#, Passenger Type, Name, Address, Phone, Gender, Age)

Bookings(BookingNo#, TrainNo#, PassengerNo#, Station)

BookItems(BookingNo#, ItemId#, SeatClass, Numseats, Fare)

b) PL/SQL

Each passenger receives a printed ticket which shows the following:

BookingNo
Passenger Name
TrainNo
Date
Time
SeatClass
Seat Number
Frequent Traveller(Y or N)

The tickets are printed via a procedure which queries a table. It is your task to write a statement which creates the table and then write a PL/SQL program, which will populate the table on a daily basis. You are not required to write the code which extracts data from the table for printing. The name of the table which stores all the information required for producing tickets is Ticket. You should create a primary key constraint for bookingno and a foreign key contraint for TrainNo

Write a statement which Creates the Ticket table, along with all its integrity constraints. All integrity constraints should be explicitly named and you should include a constraint which checks that SEATNUMBER cannot contain a value greater than 500(the maximum number of seats in a train with only one class)

Now write a PL/SQL program which does the following:

  1. Removes all records currently in the Tickets table
  2. Find the trainno for all trains scheduled for the current day
  3. Finds all passengers booked on each particular train plus other details required in the tickets table
  4. Allocates a seatno dynamically using a sequence. This sequence is called ticketseq and exists already. Its current value is 0
  5. Populate the ticket table
  6. Populates the column Frequent Traveller based upon the following rule: All passengers who have booked more than five times in the previous year are frequent traveller. The bookings table retains data for exactly for one year.

Your program must demostrate the following:

1. Usage of at least one cursor
2. Usage of explicit or implicit loops and fetches
3. Use of an exception handler which fires when no trains are found for the
current day
4. Usage of the block structure of PL/SQL at least one block Received on Tue Jan 09 2001 - 05:36:36 CET

Original text of this message