Transcript Keyword Search On Structured Database

Keyword Search On
Structured Database
by – Rajvardhan Patil
Professor – Dr. Chen
1
Search Engines & Data Format
• Search Engines:
– Unstructured data
– Semi Structured data
• Inability to Search on
– Structured / Relational Data
– Example:
• Movie Databases
• Automobile Database
• Bank Database
2
Definitions
• Attributes
– The column names of the relational table.
• Values
– The entries (rows) present in the relational table.
• Example:
car
color
doors
gears
Honda
Red
4
5
Toyota
Green
2
4
– Here,
– Attributes : car, color, doors, gears
– Values: Honda, Red, 4, 5, Toyota, Green, 2 and 4
3
Grouping
• Initially the Query is entered by the user.
•
Consider the following Example:
• USER Query: find a car having Green color and 4 doors or a car with 5
gears
• The values and operators are extracted from the query
Green -- and ---- 4 ---- or -- 5
color -- and -- doors --- or -- gears
•
Based on the Grouping algorithm, we get the following parenthesized
query:
( Green and 4 ) or 5
4
Data Structure - 1
• ( Green and 4 ) or 5
5
Step - 1
• PART A: Within ROW - combination
Now based on the above table, all possible
combinations of (Table – Attribute – Value ) for each
row is constructed.
• Here, each entry has a format of:
6
Example
•
7
Contd…
8
Step - 2
• PART B: Across ROW - Combination
– Now Based on the result of Part A, the part B is
constructed.
– For each entry in row m, calculate its
combinations with all the entries in row (m+1).
Repeat this step recursively, till all the rows are
considered.
9
Data Structure - 2
10
Create Select Statement
• From the above steps we get all the possible
combination of where clause.
• CONSTRUCTION OF SELECT Statement:
– Each row in the above table represents a Select
statement. The select statement is constructed
from each row as follow:
11
1. Select Clause
• Consider the following row, as an example:
• STEP 1: Construct select clause
– Consider all the Table.Attribute pairs present in the query
and separate them by ‘,’ delimiter. This forms our select
clause, as highlighted below.
– i.e.,
Select Vehicles.color , Vehicles.doors, Vehicles.gears
12
2. From Clause
• STEP 2: Construct from clause
• Consider all the tables present in the query and separate
them by ‘,’ delimiter. This forms our select clause, as
highlighted below.
– i.e.,
From Vehicles
(Here only one table is added, since ‘Vehicles’ table is
repeated thrice, so there is no need to add it multiple
times.)
13
3. Where Clause
• STEP 3: Construct where clause
• Append all the columns present in the row
[ omitting the (- , ~) signs ] so as to form the
where clause, as highlighted below:
– i.e.,
Where ( Vehicles.color = ‘Green’ and
Vehicles.doors =’4’ ) or Vehicles.gears=’5’
14
Conclusion
• So Overall, the select statement is:
Select Vehicles.color , Vehicles.doors,
Vehicles.gears
From Vehicles
Where ( Vehicles.color = ‘Green’ and
Vehicles.doors =’4’ ) or Vehicles.gears=’5’
• Eventually, we transformed the given english
statement query into its equivalent SQL
statement.
15
• Future Work
– To implement the set operators, not operator and
mathematical operators.
• Thank You
• Questions ???
16