The heart of the case based reasoner was programmed using SQL Server functions. These functions are detailed below:
Get Similar Cases Function
CREATE FUNCTION getSimilarCases
( @holidayType int, @price int, @numberOfPersons int, @region int, @transportationType int,
@duration int, @season int, @hotelType int )
RETURNS @idTable table (caseId int PRIMARY KEY, similarity real)
AS
BEGIN
DECLARE @maxPrice int
DECLARE @maxNumberOfPersons int
DECLARE @maxDuration int
SET @maxPrice = (SELECT MAX(price) FROM [Case])
SET @maxNumberOfPersons = (SELECT MAX(numberOfPersons) FROM [Case])
SET @maxDuration = (SELECT MAX(duration) FROM [Case])
INSERT INTO @idTable
SELECT [id], dbo.getCaseSimilarity(
@maxPrice, @maxNumberOfPersons, @maxDuration,
@holidayType, @price, @numberOfPersons, @region, @transportationType,
@duration, @season, @hotelType,
holidayTypeId, price, numberOfPersons, regionId, transportationTypeId,
duration, seasonId, accommodationTypeId
) AS similarity FROM [Case] ORDER BY similarity DESC
RETURN
END
Get Case Similarity Function
CREATE FUNCTION getCaseSimilarity
(
@maxPrice int, @maxNumberOfPersons int, @maxDuration int,
@holidayType int, @price int, @numberOfPersons int, @region int, @transportationType int,
@duration int, @season int, @hotelType int,
@otherholidayType int, @otherprice int, @othernumberOfPersons int, @otherregion int, @othertransportationType int,
@otherduration int, @otherseason int, @otherhotelType int
)
RETURNS REAL
AS
BEGIN
DECLARE @similarity REAL
SET @similarity =
1.0 * dbo.getNumericSimilarity( @maxPrice, @price, @otherPrice, 1 ) +
0.4 * dbo.getNumericSimilarity( @maxNumberOfPersons, @numberOfPersons, @otherNumberOfPersons, 0 ) +
0.7 * dbo.getNumericSimilarity( @maxDuration, @duration, @otherDuration, 0 ) +
0.7 * dbo.getSimilarity( @holidayType, @otherholidayType )
IF @similarity < 2.5 AND @holidayType IS NOT NULL AND @price IS NOT NULL RETURN @similarity / 4.4
SET @similarity = @similarity +
0.4 * dbo.getSimilarity( @region, @otherregion ) +
0.4 * dbo.getSimilarity( @transportationType, @othertransportationType ) +
0.4 * dbo.getSimilarity( @season, @otherSeason ) +
0.4 * dbo.getSimilarity( @hotelType, @otherhotelType )
RETURN @similarity / 4.4
END
Get Numeric Similarity Function
CREATE FUNCTION getNumericSimilarity
( @maximumValue real, @inputValue real, @otherValue real, @lessIsPerfect bit)
RETURNS REAL
AS
BEGIN
IF @inputValue IS NULL RETURN 0.25
DECLARE @similarity REAL
IF @lessIsPerfect = 1 AND @otherValue < @inputValue
BEGIN
SET @similarity = 1.0
END
ELSE
BEGIN
SET @similarity = (@maximumValue - ABS( @inputValue - @otherValue)) / @maximumValue
END
RETURN @similarity
END
Get Symbol Similarity Function
CREATE FUNCTION getSimilarity (@symbol1 int, @symbol2 int)
RETURNS REAL
AS
BEGIN
DECLARE @similarity REAL
IF @symbol1 IS NULL
BEGIN
SET @similarity = 0.25
END
ELSE
BEGIN
SET @similarity = (SELECT similarity FROM symbolsimilarity WHERE symbol1ID = @symbol1 AND symbol2ID = @symbol2)
END
RETURN @similarity
END
With those functions in place, the rest of the system was basic HTML and ASP.NET. I simply populated some drop-down boxes with the data from the Symbol table, had a couple more text boxes for the numerical inputs, and used the user's input to call the following SQL query:
(currently set to 10) and I only retrieve those cases with a similarity greater than 25%. Each of the strings (e.g. holidayType) hold the user's choice: a number or symbol ID (both integers) or null if the user did not enter anything for that dimension. I then fed this SQL string to the database and I could then retrieve the cases and display them on the screen.