2745

Assessment No. and Type : 1, CW

Assessment Title : Database Case Study

Submission Deadline : 8thDecember, 2017

Assessment Author : Dr. Loraine Annie

Learning Outcomes:

  1. Critically evaluate using of triggers and procedures to solve a database problem.

2. Appraise various ways of providing access to a database, through forms, scripts and programming languages.

Assessment Scenario

Consider you are the database developer for an Airline Reservation system. The ER-diagram is provided for the database design. You need to create all the required tables for the database using Oracle. Business rules which are mentioned below need to be included in the database. To implement the business rules in the database, develop Constraints, Triggers, Procedures and Functions using PL/SQL. For easier data manipulations create Forms for data entry and Reports for viewing output.

The business rules are given below.

  1. Ticket Price Variation

Ø When Booked seats reached maximum threshold then the ticket price will increase by 15% of Actual Ticketprice

Ø When Booked seats reached minimum threshold reached then the ticket price will decrease by 10% of Actual Ticketprice

Ø When Promotioncode is enabled, then the ticket price will decrease by 20% of Actual TicketPrice

  1. No negative values are allowed within the system for the ticketprice.
  2. If Facilities in Travel Class is having a valid value then the Extra Facilities Fees need to be 150 AED otherwise it default as zero.
  3. Total amount should be sum of Ticket price and Extra Facilities Fees.
  4. Booking cannot be made when the Number of Booked seats is greater than the total number of seats
  5. Booked seats need to be updated based on whenever a booking is made.
  6. Available seats need to be updated as total number of seats – booked seats.
  7. Booking cannot be made if Availability in search for flights is “NO”
  8. This supports only One-way tickets, so booking cannot be made for same departure and arrival airport in a ticket
  9. Difference between Passport Expiry Date and Travel Date need to be minimum 6 months then only booking will be successful.
  10. Booking need to be successful when the all the required passenger details are provided with valid data and payment is successful.
  11. IATA needs to be first 3 letters of the airport city name.
  12. TravelClass will be Economy and Business class.

Ø For Economy class, check-in luggage need to be 20KG per passenger + 7KG hand luggage

Ø For Business class, check-in luggage need to be 30KG per passenger + 7KG hand luggage and Special Service will be food

  1. There is a maximum limit of 5 crews on an Airplane.
  2. A crew’s total duration time need to be less than 8 hours in any one day; if it exceeds then the Salary need to have Number of extra hours * 50 AED as bonus.

Entity-Relationship (ER) Diagram

Figure 1. ER-Diagram of Airline Reservation System

Tasks:

A. Use the ER-diagram to create an efficient database for the Airline reservation system. Using appropriate SQL statements create the tables/relations and add proper constraints to the tables that match the entity relationship diagram shown in Figure 1 and satisfy the above mentioned business rules. Populate the database with valid sample data. [20]

B. Identify and document the functional dependencies in the database. [5]

C. Create Oracle forms to add / edit / delete bookings, Flights, with appropriate List of Value options. Create master-detail forms, or one combined form, for Booking,Payment,Flight Search, passenger details, flight details, and crew details. [25]

D. Create a report that shows all flights available for a specified destination from an origin on a specified date with ticket price. Order the output based on the ticket price in descending order. [7]

E. Create a report that shows the passengers flying for a particular flight on a

specified date. [3]

F. Create forms for entering the airport IATA codes, airline and crew for an airline. [5]

G. Outline the PL/SQL procedures / functions and / or triggers that have been used within your system, and justify why procedures / functions and / or triggers have been used. [20]

H. A PowerPoint presentation showing how you developed the application and a report with important screenshots. [5]

I. Demonstrate the Database Application to the Tutor in 10 minutes. [5]

J.Discuss how you would install / implement the airline reservation system within ‘real’ environment and its security implications. [5]

The learners have to submit the assignment on or before deadline in the Moodle Assignment submission link as a zipped folder with your StudentId_Name as the file name; it should contain the report, oracle file and the filled in Assignment cover sheet.

Specific Assessment Criteria:

First class (70% and above):

Students have to implement minimum 14 criteria mentioned as business rules in the database. Attempt all the tasks from A-J with excellent quality, valid data and procedures.

Second class (50-69%):

Students have to implement minimum 12 criteria mentioned as business rules in the database. Attempt at least A,B,C,D,F,G,H,I,J tasks with very good quality, valid data and procedures.

Third class (40-49%):

Students have to implement minimum 8 criteria mentioned as business rules in the database. Attempt at least A,B,C,D,G,I,J tasks with good quality, valid data and procedures.

Fail (39% and below): Students who do not meet the requirements of a third class grade will not successfully complete the assessment activity.

Late submission of assessments

· On or before deadline, actual marks.

· Up to 7 calendar days late = 10 marks subtracted but if the assignment would normally gain a pass mark, then the final mark to be no lower than the pass mark for the assignment.

· More than 7 calendar days late = 1 mark awarded (to identify that a submission was made but failed due to lateness).