Tool to get the most proper SQL for a predefined result

Question

unlike to normal situation, which I write SQL Statement for pre-assumed result, I am in a situation that I need the SQL for predefined result. for example, suppose that you have the following table for employees:

the attributed of Gender, Marital State, Nationality, Region, Position are unique for each employee

and now I need a SQL to give me result of Alice Timmonsm, Jackeline Albright, Rita Thomas.. I can write a SQL like

Select Name from Employees where 
Gender ='F' and and Position in ('Secretary', 'Manager')

but the SQL statement

Select Name from Employees where Region = 'East'

is Simpler and gives the same result

another Example: I want the simplest SQL statement to give the names: Carol Williams, Karl Perry, Benjamin Albright I can write SQL like

Select Name from Employees where 
Position in (Supervisor, Worker)

but better to write SQL :

Select Name from Employees where 
Nationality = 'Ukraine'

so, is there any tool or software that can achieve this ? I strongly believe there is such a tool .. thanks in advance to anyone who gives me a hand and lead me to solve this issue

Answer 1

Seems to me that you are asking for a software that helps you find a "cover", a smallest set of the "Functional Dependencies" which can generate all the given rules that are encoded in your Table Instance.

There are "Nonredundant Covers","Canonical Covers", a "Minimal cover" or even an "Optimal Cover". See Maier pdf, e.g. p.86

I am not aware of any commercial or free products that do this. However there might be some academic research packages out there which might help you to find these Covers.

If they follow SQL's Semantics, and if they really produce SQL Syntax as output - that is another matter though.

Perhaps try this page and enter

Supply a list of attributes:

   G,M,N,R,P,name  # first letters of column names in your table

Supply a list of known functional dependencies:

      G,M,N,R --> name
      M,N,R --> name
      G,M,N,P --> name
      N,R,P --> name
      (and many  more)

---

There is another approach, unrelated to "Relational Theory". This is more akin to Machine Learning. This approach also does not give you SQL, but helps to find the most "interesting" attributes to predict the rightmost column:

To find redundant attributes quickly and heuristically (say if you had tables with 100s of columns).

For this you could try the "AttributeSelectedClassifier" (or "Wrapper") in Weka, but this requires experimentation.One-off experiments in the Weka GUI (Explorer) may not be reliable.