How do you write a store procedure to meet the following conditions:
PROXIMITY SERACH PROCEDURE
PK company_id int
PK, FK1 company_id int
PK keyword varchar(100)
The data above shows an extract of a company listings database. The company table holds the name and address of the company as well as a physical location in terms of an Easting and Northing co-ordinate. Each company has one or more keywords or phrases associated with it in the company_keyword table. The full database holds 5 million companies with an average of 4 keywords each,
A stored procedure is required to provide proximity searching of the database. The procedure should return a list of companies that satisfy the keyword search expression and fall within the defined maximum distance of the specified location. Results should be limited to show the closest companies up to a maximum of @max_records.
Example Search Expressions
@search_expression - Description
TESCO AND CASHPOINT - Return all companies that have all of the
TESCO OR SAINSBURY’’S - Return all records that have one or more of
the keywords specified.
TESCO EXPRESS AND CASHPOINT
SAINSBURY’S LOCAL - Return all companies that have either both of
the first two keywords or the third.
Distance can be calculated using the following formula:
Distance = sqrt( square(x1-x2) + square(y1-y2) )
1. Create a stored procedure to satisfy the requirement shown above
2.Comment on the database design and identify any indexes that may be