whoami Posted January 10, 2004 Posted January 10, 2004 CASE STUDY: � Airlines Reservation System� �Fly without Cry� Airways International is in business since 1997 with several Airbuses flying around the world. Its main office is located at New Street , Dollis Lane , Florida. Their flight operates thrice a week. The airbus is designed for First Class , Economy Class and Business Class. The total capacity is 300-350 seats. The company wants to computerize its operations in the following areas: � Reservations � Ticketing � Cancellations � Check In and Departures Reservations are directly handled by booking office. Reservations can be made 60 days in advance in either cash or credit. In case the ticket is not available , a wait-listed ticket is issued to the customer. This ticket is confirmed against the cancellation if any. Cancellations are also directly handled at the booking office. Cancellation charges are as below Hour Cancellation Charges 24 hrs before flight NIL 12 hrs before flight 5 % 6 hrs before flight 10 % 3 hrs before flight 15 % Wait listed tickets that do not get confirmed are fully refunded. In case of change in the class , travel date or destination is required from the customer , he has to cancel his tickets and make another reservations. Cancellations charges will be the same as mentioned above. The check-In counter is opened three hours before the flight departure . The confirmed passengers are expected to arrive 2 hrs before flight departure . The passengers pay an Airport Tax of $50 . Depending upon the class he is traveling , the baggage that he can carry is as follows Class KG First 50 Business 40 Economy 30 Exceeding the above, for every kg he has to pay 50$. Wait listed passengers have to arrive 2 hrs before the departure time. Their tickets are confirmed depending upon the availability of tickets and cancellations of that particular flight. Once their ticket is confirmed, the proceedings to be followed are same as above. REQUIREMENT. 1. Develop an entity model to support the above scenario, including attributes [name, type, length any validation checks] associated with each entity and relationships between them. Identify primary and foreign keys. 2. Set up and test the following queries 1 to 4 using SQLPLUS / Query Analyzer or any appropriate Database reports generation tool. Submit your queries and proof of execution. Each of the following reports needs to be outputted in the form of a report using SQLPLUS/Query Analyzer of using a tool such as report builder/Crystal Reports. 1. Find the number of tickets booked and cancelled in each class using group by and Union operator [5 marks] 2. Create a view to display the Reservation details order by the Reservation date [5 marks] 3. For each flight show the tickets cancelled, along with passenger name and contact info . Also display the cancellation charges enforced on him [10 marks] 4. Show analysis of Reservations done in the CURRENT YEAR with breakup on the basis of MONTH and Traveling CLASS . Class� Month Jan Feb Mar Apr May Jun Economy Class 750 15 100 23 20 15 Business Class 200 100 500 850 550 620 First Class 500 200 700 750 775 320 DATABASE TRIGGERS & PROCEDURES 4. Produce a data base procedure which will accept one parameter �DATEPARAM� and will delete cancellations before the �DATEPARAM� [10 marks] 5. Create a database trigger which on addition of any record in the cancellations will delete the corresponding reservation record.[10 marks] The above will be assessed by the production of a professional report documenting your solution using a design tool to show your entity model plus SQL scripts to show your table creation, constraints, triggers, your queries and results. Any assumptions you have made must be clearly explained. The content of each table must be included. A demonstration of your queries and database triggers will be given to a lecturer. All members of the syndicate will be present at the same time and you may be expected to discuss your design. It is expected that the queries and triggers will be pre-typed and ready run at the demonstration. You may also find it helpful to include printouts of the data contained within your tables together with your E-R model to help explain your queries and triggers. >>>WHO CAN DO THIS ON VISUAL BASIC .NET IN 24 HOURS>??????><<<<<<<<<<<<<<<<<
Moderators Robby Posted January 10, 2004 Moderators Posted January 10, 2004 We do not do your projects for you. If you have a specific question we will help you as best we can. Visit...Bassic Software
Recommended Posts