Chapter 4 Research Result for B2C Mobile Tourism Service Classification
5.3 Database Design for B2C Mobile Tourism Service
國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
During the recommendation process, if the updated current time is around the lunch time or the dinner time periods, and the tourist has not eat yet, then the restaurant recommendation service is launched to locate a preferred restaurant within certain search range. If the current time is not in the eating time zones and is no later than 21:00 pm, the number of visited sightseeing spots is less than 7, and there are still nearby open unvisited sightseeing spots with sufficient visiting time, then the recommendation process continues to activate the sightseeing recommendation services. If the current time reaches the time limit 21:00 pm or the number of visited sightseeing spots reaches 7, then the hotel recommendation service is activated.
Finally, the complete tour plan consists of sequentially arranged places generated from restaurants, sightseeing spots, and hotel recommendations is sent to and presented in the tourist’s mobile device.
5.3 Database Design for B2C Mobile Tourism Service
For designing and developing system database, the Object-Oriented (OO) database design approach is adopted to create the conceptual data model, and then the OO model is translated into an entity-relationship (ER) model which is further transformed into the internal relational model for physical database implementation. Figure 12 presents the OO data model in which identified objects include Tourist, Tourism Information, Tour Plan, and Recommendation Process. The Tourist object composes of Needs and Preferences (N&P), Search Range and Criteria, as well as Current Location and Time objects, and the Needs and Preferences object is further classified into Sightseeing N&P, Restaurant N&P, and Hotel N&P objects. The Tourism Information object aggregates three sub-class objects including Sightseeing Spots Information, Restaurant Information, and Hotel Information objects. The Recommendation Process object has several component objects including Process Input, Recommendation Model, Recommendation Rule, and Process Output. The Tour Plan object consists of Sightseeing Spot Selection, Restaurant Selection, and Hotel Selection objects as the components.
Figure 12. Object-Oriented Data Model
Figure 13 depicts the class diagram of our prototype system. There are seven classes in our system including the tour plan class, the hotel class, the restaurant class, the sightseeing spot class, the tourist preference class, the tourist location class and the Google map class.
38
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
39
The attributes of the tour plan class include tour plan ID (tourPlanID), tourist’s mobile phone number (mobilePhoneNumber), tour date (tourDate), sequence of what to do (squenceOfWhatToDo), recommended activity (recommendedActivity), place ID (placeID), recommended interval (recommendedStartTime and recommendedEndTime), and distance from previous place (distance). The recommended activity field remarks different activities like lunch, dinner, sightseeing, or hotel. With the place ID, our system further links to the corresponding detailed information about the recommended dining, sightseeing or hotel by restaurant ID, sightseeing ID or hotel ID. The tour plan class uses methods to execute the tour plan recommendation (executeTourPlanRecommendation), to get the current time (getCurrentTime), to get the tourist’s current location (getTouristLocation), to request more detailed hotel information (getHotelInformation), to access more information about the suggested restaurant (getRestaurantInformation), to retrieve the recommended sightseeing information (getSightseeingInformation), to calculate the distance between two sequence spots (calculateDistance), and to get map display of specific site (getMap). The attributes of the hotel class include hotel ID (hotelID), name (hotelName), class (hotelClass), phone number (hotelPhoneNumber), address (hotelAddress), direction (hotelDirection), check in time (hotelCheckinTime), check out time (hotelCheckoutTime), briefing (hotelBriefing), and the location of the hotel (hotelLongitude and hotelLatitude). Four methods used by the hotel class are to execute the hotel recommendation (executeHotelRecommendation), to calculate the distance between the current location and the hotel (calculateDistance), to calculate the matching score of the selected hotel (calculateMatchScore), and to get map display of the selected hotel (getMap). The attributes of the restaurant class include restaurant ID (restaurantID), name (restaurantName), food type served (foodType), rating (restaurantRating), phone number (restaurantPhoneNumber), address (restaurantAddress), direction (restaurantDirection), open hours (restaurantOpenHours), briefing (restaurantBriefing), and the location of the restaurant (restaurantLongitude and restaurantLatitude). There are four methods used by the restaurant class. These methods help the restaurant class to execute the restaurant recommendation (executeRestaurantRecommendation), to calculate the distance between the current location and the recommended restaurant (calculateDistance), to calculate the matching score of the selected restaurant (calculateMatchScore), and to activate the map display for the chosen restaurant (getMap). The attributes of the sightseeing spot class include sightseeing ID (sightseeingID), name (sightseeingName), themes (sightseeingThemes), phone number (sightseeingPhoneNumber), address (sightseeingPhoneNumber), direction (sightseeingDirection), opening information (sightseeingOpenDays, sightseeingOpenFromHour, and sightseeingOpenToHour), briefing (sightseeingBriefing),
suggested visiting time (recommendedVisitingTime), ticket price (ticketPrice), and the location of the sightseeing spot (sightseeingLongtitude and sightseeingLatitude). Three methods are used by the sightseeing spot class including a sightseeing recommendation method (executeSightseeingRecommendation), a distance calculation method (calculateDistance), and a map display method (getMap). The attributes of the tourist preference class include tourist’s mobile phone number (mobilePhoneNUmber), requested hotel class (requestedHotelClass), search range (searchRange), weight between requested hotel class and hotel search range (weightBetweenRequestedHotelClassAndSearchRange), preferred food type (preferredFoodType), requested restaurant rating (requestedRestaurantRating), weight between requested restaurant rating and restaurant search
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
range (weightBetweenRequestedRestaurantRatingAndSearchRange), and preferred sightseeing themes (preferredSightseeingThemes). The tourist preference class uses the getTouristPreference method to retrieve the preference specification for a selected tourist. The attributes of the tourist location class is the tourist current location (touristLongtitude and touristLatitude). The getTouristLocation method reports the tourist’s current location for the tourist location class. The attributes of the Google map class contains the place’s location (placeLongitude and placeLatitude). The diplayMap method is used to activate the Google map display for a specific place.
Figure 13. Class Diagram
Table 15 describes the relational database of our prototype system. Tables in our system include the tour plan table, hotel table, hotel class description table, restaurant table, food type description table, sightseeing table, sightseeing theme table, sightseeing theme description table, tourist preference table and tourist location table. A star (*) mark represents the primary key of the table while the underline indicates the foreign key. The primary key in the tour plan table is tourPlanID while the hotelID is the primary key for the hotel table. The hotelclassID is the primary key for the hotel class description table, and the restaurantID is the primary key for the restaurant table. The foodTypeID is the primary key for the food type description table, and the sightseeingID is the primary key for the sightseeing table. The sightseeingThemeID is
40
‧
the primary key for the sightseeing theme description. The combination of the sightseeingThemeID and sightseeingID is the primary key for the sightseeing theme table because a sightseeing spot may have multiple sightseeing themes while many sightseeing spot may have the same sightseeing theme. The mobile phone number is the primary key for the tourist preference table and for the tourist location table. As for foreign keys, the mobilePhoneNumber in the tour plan table is referred to the mobilePhoneNumber in the tourist location table and to the mobilePhoneNumber in the tourist preference table. Based on different recommended activities, the place ID in the tour plan table is linked to the hotel ID in the hotel table, the restaurant ID in the restaurant table, or the sightseeing ID in the sightseeing table. The hotelClassID in the hotel table is referred to the hotelClassID in the hotel class description table. The foodTypeID in the restaurant table is related to the foodTypeID in the food type description table. The sightseeingThemeID in the sightseeing theme table is referred to the sightseeingThemeID in the sightseeing theme description table.
Table 15. Relational Schema
Table Field
Tour plan *tourPlanID, mobilePhoneNumber, tourDate,
sequenceOfWhatToDo, recommendedActivity, placeID, recommendedStartTime, recommendedEndTime, distance
Hotel *hotelID, hotelChineseName, hotelEnglishName, hotelClassID, hotelPhoneNumber, hotelChineseAddress, hotelEnglishAddress, hotelChineseDirection,
hotelEnglishDirection, hotelCheckinTime, hotelCheckoutTime, hotelChineseBriefing, hotelEnglishBriefing, hotelLongitude, hotelLatitude
Hotel class description *hotelClassID, hotelClassChineseDescription, hotelClassEnglishDescription Restaurant *restaurantID, restaurantChineseName, restaurantEnglishName, foodTypeID,
restaurantRating, restaurantPhoneNumber, restaurantChineseAddress,
restaurantEnglishAddress, restaurantChineseDirection, restaurantEnglishDirection, restaurantOpenHours, restaurantChineseBriefing, restaurantEnglishBriefing, restaurantLongitude, restaurantLatitude
Food type description *foodTypeID, foodTypeChineseName, foodTypeEnglishName Sightseeing *sightseeingID, sightseeingChineseName, sightseeingEnglishName,
sightseeingPhoneNumber, sightseeingChineseAddress, sightseeingEnglishAddress, sightseeingChineseDirection, sightseeingEnglishDirection, sightseeingOpenDays, sightseeingOpenFromHour, sightseeingOpenToHour, sightseeingChineseBriefing, sightseeingEnglishBriefing, recommendedVisitingTime, ticketPrice,
sightseeingLongitude, sightseeingLatitude Sightseeing theme
description
*sightseeingThemeID, sightseeingThemeChineseName, sightseeingThemeEnglishName
Sightseeing theme *sightseeingThemeID、*sightseeingID
Tourist preference *mobilePhoneNumber, requestedHotelClass, searchRange,
weightBetweenRequestedHotelClassAndSearchRange, preferredFoodType, requestedRestaurantRating,
weightBetweenRequestedRestaurantRatingAndSearchRange, preferredSightseeingThemes
Tourist location *mobilePhoneNumber, touristLongitude, touristLatitude Note: star (*) means the primary key, and the underline indicates the foreign key.
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
42
For model base and knowledge base design, the process modeling, decision modeling, and rule base approaches are used. Focusing on the example presented in the previous sub-section, Figures 14-17 respectively illustrate the recommendation process model as well as associated decision model and rules for the hotel, sightseeing spot and restaurant recommendations. The inputs to the personalized location-based tour plan recommendation process include tourist current location, the current time, search range (e.g. 3 km to the current location), constraints and criteria (e.g. 11:00 am - 14:00 pm and 18:00 - 21:00 pm as the lunch and dinner time periods respectively, 7 as the maximal number of visiting spots), sightseeing spots needs and preferences, restaurant needs and preferences, and hotel needs and preferences. For more specifically specifying needs and preferences, we take the hotel recommendation as an example for explanation. As a specific instance, data elements and values such as “hotel distance to the current location < = 3 km”, “room type = single”, and
“availability = yes” can be used as the matching criteria in the data retrieval step, and then, the data elements and values such as hotel distance to the current location, hotel class (3 star and above), price per room per night (less than US$150), facilities (Internet access), and customer rating score can be chosen as the hotel evaluation criteria in the ranking and selection step. Hotels with the highest total evaluation scores are ranked and presented to the tourist for selection.
Record current location and current time
Set restaurant search range, preferred lunch and dinner food type, restaurant rating Set sightseeing search range, preferred sightseeing theme, opening hours
Set hotel search range, preferred hotel class, room type, room rate, facility, customer rating
Set temp_time=current time
Set temp_location=current location While loop
If temp_time is between 11:00 am and 14:00 pm and lunch status=no or temp_time is between 18:00 pm and 21:00 pm and dinner status=no
then Activate Restaurant Recommendation Service Model (temp_location, restaurant search range, preferred food type, restaurant rating)
temp_time = temp_time + suggested_eating time temp_location = location_of_recommended_place If temp_time<21:00 pm and the number_visited_site< 7
Then Activate Sightseeing Recommendation Service Model (temp_location, sightseeing search range, preferred sightseeing theme, opening hours)
temp_time = temp_time + suggested_visitng_time temp_location = location_of_recommended_place End loop
Activate Hotel Recommendation Service Model (temp_location, hotel search range, preferred hotel class, room type, room rate, facility, customer rating)
Output tour plan (selected sightseeing spots, restaurants and hotel with sequence numbers)
Figure 14. Tour Plan Recommendation Process Model
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
43
Record current location
Set hotel search range, room type, room availability
Set preferred hotel class, room rate, facility, customer rating
Retrieve hotel distance to the current location, hotel class, room rate, facility, customer rating If Hotel distance to the current location = 0, then T1 = 100
Else T1 = 100 - 10*(Hotel distance to the current location) If Hotel class a is >= Preferred hotel class a1, then T2 = 100 Else T2 = 100 - 20*(a1 - a)
If Room rate b is <= Specified room rate b1, then T3 = 100 Else T3=100 - (b - b1)
If Facility set F contains Specified facility set F1, then T4 = 100 Else T4 = 100 – 100*Count(F1-F)/Count(F1)
If Customer rating c is >= Specified customer rating c1, then T5 = 100 Else T5 = 100 – 20*(c1-c)
SET HT = Sum(T1*W1, …, T5*W5) Subject to Sum(W1, …, W5) = 1
Where HT = total score of hotel evaluation
Ti and Wi are value and weight of the ith hotel evaluation criteria, i = 1,…, 5
Figure 15. Hotel Evaluation Model and Rules
Record current location, current time, recommended and visited sightseeing spots Set sightseeing search range, preferred sightseeing theme
Retrieve sightseeing distance to the current location, sightseeing theme, suggested visiting time
If Sightseeing distance to the current location = 0, then T1 = 100 Else T1 = 100 - 10*(Sightseeing distance to the current location)
If Sightseeing theme set S contains Specified sightseeing theme set S1, then T2 = 100 Else T2 = 100 – 100*Count(S1-S)/Count(S1)
SET ST = Sum(T1*W1, T2*W2) Subject to Sum(W1, W2) = 1
Where ST = total score of sightseeing evaluation
Ti and Wi are value and weight of the ith sightseeing evaluation criteria, i = 1, 2
Figure 16. Sightseeing Evaluation Model and Rules
Record current location
Set restaurant search range, preferred food type, customer rating
Retrieve restaurant distance to the current location, food type, customer rating If Restaurant distance to the current location = 0, then T1 = 100
Else T1 = 100 - 10*(Restaurant distance to the current location) If Customer rating c is >= Specified customer rating c1, then T3 = 100 Else T2 = 100 – 20*(c1-c)
SET RT = Sum(T1*W1, T2*W2) Subject to Sum(W1, W2) = 1
Where RT = total score of restaurant evaluation
Ti and Wi are value and weight of the ith restaurant evaluation criteria, i = 1, 2
Figure 17. Restaurant Evaluation Model and Rules
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
The resulting output elements of a recommended tour plan include tour plan ID, sequence number of attractions/activities, types of attractions/activities (sightseeing, restaurant, hotel), attraction/activity ID, attraction/activity name, information of selected sightseeing spots (e.g. name, location, address, distance to the updated current location, sightseeing theme, suggested visiting time, etc.), information of selected restaurants (e.g.
name, location, address, distance to the updated current location, food type, suggested eating time, customer rating score, etc.), information of the selected hotel (e.g. name, location, address, distance to the updated current location, hotel class, room type, room rate, check-in/check-out times, facilities, distance to point-of-interest, customer rating score, etc.), as well as a map showing all selected attractions with their sequence numbers. Through using the attraction/activity ID, detailed information about the recommended sightseeing spots, restaurants, or hotel can be accessed and reviewed.