ITA Refund Request System

During regular operating hours, remote ITA agents can send a request to head office to try to obtain a refund for customers in lieu of significantly delayed flights, missed connections, and flight cancellations. This process requires agents to submit a considerable amount of information described below. It also allows head office to generate a multitude of reports about approved and denied refund requests. This document describes both the information agents submit and the types of possible reports head office may ask for.

All refund requests are assigned to multiple ITA agents (called Refund Reviewers) for review prior to being accepted. This is done to prevent fraudulent requests from being approved and processed.

1. Types of User Accounts and Permissions

The system must provide for the following types of user accounts:

ACCOUNT TYPE NAME PERMISSIONS

• Administrator This type of user can access any component or area of the system including accounts of other users
• Refund Officer This type of user can access:
  • the Reports Management features;
  • the Refund Requests feature;
  • the Search Refund Reviewers feature;
  • the Assign Refund Reviewer feature;
  • the Refund Approval feature.
• Refund Reviewer This type of user can access:
  • the Search Agents/Refund Request features;
  • the View Assigned Refund Request feature;
  • the Enter Review Report feature.
• Agents This type of user can access:
  • the View Refund Request feature;
  • the Enter, Update, Delete, Save and Submit Refund Request features;
  • the Check Status feature.

2. System Features

2.1 User Accounts Management Feature

The user must first create a unique ID and password and choose one the following three possible roles to enter the system: agent, refund officer, and administrator. A user trying to register as a refund officer or new administrator needs to have her account approved by one of the existing administrators. A refund reviewer role can only be assigned to an agent. All new accounts require the following data to be submitted upon registration:

User Profile

• User ID (8-digit code)
• First Name, Last Name, Middle Initials
• Email address
• Office location
• Office address
• Position
• Daytime phone number
• Language of correspondence
• Authentication question/answer pair

Please note that in order to be eligible as a refund reviewer, one must be a current agent of the ITA organization that hasnt submitted a refund request in the last week.

2.2 Enter/Update/Delete/Save/Submit/View Refund Request (by an Agent)

Features

Upon logging in, an agent is asked to supply the following information as part of his/her application:

1) Agent Profile

• Agent ID (8-digit code)

2) Booking Information

• Legal First Name, Last Name, Middle Initials
• Date of birth
• Gender
• Current address
• Daytime phone number
• Passport number
• Food preference: (Yes/No for each of: vegan, vegetarian, halal)
• Booking code (8-digits code)
• Booking agent ID (8-digit code)
• Origin airport (3 letters)
• Destination airport (3 letters)
• Flight code (6-digit code)
• List of airport connections: zero or more records of the form < airport code, arrival time, departure time, flight code>
• Baggage connections: zero or more records of the form < airport code, arrival time, departure time, flight code>
• Recently visited countries (for customs): zero or more records of the form < country, arrival date, duration of stay>

3) Refund Request

• Request ID (10-digit code)
• Application date
• Refund type: one or more records of the form < refund amount, type: full or segment>
• Flight segments to refund: zero or more records of the form < flight code, date, origin, destination>
• Reason for request

Once all required fields have been verified (e.g., not empty and with the correct type of data), the agent can choose to save or submit his/her refund request. Once a verified refund request is submitted, the agent will receive a confirmation from the refund request system. An agent can no longer update nor delete his/her refund request once submitted. An agent can view his/her request at any time.

2.3 Check Status Feature

After submitting a refund request, an agent will be able to check the status (approved or rejected) of his/her refund request.

2.4 Reports Management Feature

Here are some examples of the statistical analysis this system supports and that an administrator or a refund officer may choose to generate and view:

• % of approved (or rejected, throughout this list of examples) refund requests from agents employed at head office
• % of approved refund requests from a specific remote office
• % of approved refund requests from agents employed at a remote office, x, who have been employed for 1 year or less
• % of approved refund requests from agents employed at a remote office, x, who have been employed for 1 year or more
• % of approved refund requests from agents employed at a remote office, x, with a position title of manager
• % of approved refund requests from agents employed at a remote office, x, with a position title of agent
• % of approved refund requests from all agents with a position title of manager
• % of approved refund requests with YYZ as the origin airport code
• % of approved refund requests within the last month
• % of first time refund request agents (i.e., agents who have never requested a refund) who have approved less than 5 pending refund requests
• % of approved refund applications from agents who took more than 30 days to process the refund request.
• % of approved refund applications from agents asking for less than $500 and who also approved at least 20 pending refund requests

2.5 Search Agents/Refund Requests Feature

A refund officer or a refund reviewer can look up a refund request by using one or a combination (using ANDs and/or ORs) of the following criteria: customers first name, family name, passport number, agent ID, flight date and/or flight code. A successful search will result in all of the refund request information (i.e., all of the data described in section 2.2) being displayed.

2.6 Search Refund Reviewers Feature

A refund officer can look up a refund reviewer by using one or a combination of the following criteria: agents first name, family name, office location, and/or the ID of the reviewing agent. A successful search will result in all of the parts of the agents profile being displayed (i.e., all of the data described in section 2.1).

2.7 Assign Refund Reviewer Feature

A refund officer must assign 4 reviewers each time a refund request is submitted. These reviewers must not be submitting a refund request in the current week. For this project, however, you can randomly assign these reviewers (as long as none of them is the refund requester!). Once assigned to a refund request, a refund reviewer can access and assess a refund request assigned to her.

2.8 Refund Approval Feature

A refund officer verifies and then approves or rejects a submitted refund request.

For this project, you can randomly choose to approve or reject a refund request.

2.9 Refund Reviewer Search and View Assigned Refund Request and Enter Review Report Feature

A refund reviewer can look up a specific assigned refund request using one or a combination of the following criteria: refund request ID, date applied, refund type, agent ID. A successful search will result in all of the refund request information (i.e., all of the data described in section 2.2) being displayed. The refund reviewer may then input a link to a file containing her assessment of the request

Assignment

1.Purpose:

In class, you learned how to decompose a relational database schema into a schema that satisfies different normal forms (NFs).

In this assignment, you need to practice designing a database for a given system using a mathematical way that guarantees the designed database satisfies the required normal forms that have neither redundancy nor anomalies.

2.DeliverablesandEvaluationMethod:

The detailed specification of the ITA system requirements can be found in DB - ITA_System_spec(v3).pdf.

Relation1{userID, userFirstame, sserLastname, middleInitials, emailAddress, officeLocation,
officeAddress, Position, daytimePhonenumber, language, authenticationQuestions, answerPair, agentID,
datelastrequestApplication, timelastrequestApplication, adminID, officerID, custmorFirstname,
customerLastname, customerMiddleinitials, dob, gender, currentAddress, daytimePhonenumber,
passportNumber, foodPreference, bookingCode, bookingAgentID, originAprilport, destinationAirport,
flightCode, airportCode, arrivalDate, arrivalTime, departureDate, departureTime, bagairportcode,
bagarrivalDate, bagarrivalTime, bagdepartureDate, bagdepatureTime, recentlyVisitedcounty,
countryArrivalDate, visitDuration, ticketPrice, flightDate}

Relation2{request ID, applicationDate, refundType, flightCode, flightDate, flightOrigin, Destination,
agentID1, agentID2, agentID3, agentID4, decisionDate}

For this assignment, the individual submission of each student must include:

Part I: Relational Database Design by Decomposition

1. 3.1 First, write down a primary key and all functional dependences for each of the two given giant tables Relation1 and Relation2 after analyzing the system requirements.

3.2 Then create a new database schema resulting from the decomposition of the schema of step 3.1 so that all relations end up in BCNF. You must show the steps of your decomposition process from the two given tables to the final set of tables that are in BCNF.

Part I: Comparing the Two Design Solutions

2. A detailed comparison of the database schema of step 1 with the database schema for Relation1 and Relation2: you must describe the pros and cons of each of these two schema designs using at least 3 real examples. For example, in order to produce a certain report (described in DB - ITA_System_spec(v3).pdf), what kind of join need to be performed and which two designs is the preferred design in that specific situation? Detailed analysis is required for each example.

Bonus: Beyond the above-mentioned deliverables, you must perform at least one of the following two tasks:

a. further decompose your schema to satisfy 4NF (with decomposition details), then compare it with the schema that you got for this assignment and the one specified in table 1. OR

b. provide detailed proofs illustrating that your result is already satisfying 4NF