Question

No Hand drawings, please.

Consider the following scenario and then answer the corresponding questions.

ABC Travel and Tourism specializes in providing tourist packages to customers across the globe and the company has decided to develop an in-house software system that will help customers search for travel destinations based on popularity, budget, visa restrictions, government policies and attractive climatic conditions. Customers will also be able to book their air tickets, hotel and transportation (rental car, public transportation, cab services) through this system. Prior to the booking, the system will allow users to check for their visa eligibility to travel to destinations outside of their country of citizenship. Users will also be able to review and rate destinations or hotels using the system. The company also offers pre-set packages and if a customer decides to go with a specific package, they will be asked to provide the dates for their travel. The system will find an airline reservation, hotel/ transportation choices and create an itinerary for the customer. Customers will not be allowed to customize the pre-set packages.

Once the booking is done, the system should send a notification to the users on the purchase confirmation within 2 hours. Cancellations will incur a 25% penalty, if users are “Gold” members. In all other cases, the reservations will be non-refundable. Customers can pay using their credit cards or Paypal.

Upon your review complete the following:

1. Identify ALL MAJOR the tables relevant this case, the primary key, foreign keys, attributes and data types. For each table, list the fields, description, datatype and description about that field as suggested in the format below. (30 pts). (Hint: Refer to your class diagram).

Table Name	Field Name	Description	Data Type	Comments (Identify as primary key, foreign key or any special considerations)

2. Apply 1^st, 2^nd and 3^rd normalization rules for your tables. Explain how each table satisfies these normalization rules. (Hint: You may end up creating intersection tables as needed, in addition to the tables identified for question #1). (20 points)

(Please see normalization examples from the lecture notes and show the normalization process for the tables as applicable)

3. Draw an ER diagram for this case including the tables, relationships, cardinality and all the fields. (10 points)

Answer 1

Diagram:

The above use case diagram is completely based on the requirements mentioned in the above case study.

(I) Firstly customer can check for the availability of visa for the country in which is visiting which is represented using a use case.

(ii) Customer can book hotel, airtickets and transportation on their own. But the company can also provide packages in which the company books all these. This task is represented as arrange tour.

(iii) Customer can also review and rate destinations and hotel in which they have stayed. This task is divided in two parts.

(iv) Company provides the packages and if a customer agrees the dates are stored. The task provide date is represented by that. Company also creates itinerary for customers.

(v) After the confirmation the company will send notification which is also depicted in the diagram as a task. The customer can pay in two ways- credit card or paypal for which extends is used. Customer can also cancel the tour for which they will not get any refund.

(vi) Customer is also represented as gold customers for which he/she can get refund which is described using another task.

However, you can make changes to the above diagram as per your requirements and understanding.