1

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:

enter image description here

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

4
  • The last column, and only the last column, is always the "predictor variable" or the "target variable"? -- Can you have NULL values in your tables, and duplicate rows? (en.wikipedia.org/wiki/Codd%27s_theorem)
    – knb
    Aug 12, 2021 at 15:40
  • The last column is always the target variable.. And cannot be null Aug 13, 2021 at 9:01
  • 2
    Do you need a software that (provably) finds the shortest statement, or "a" shorter statement of a given complexity (e.g. a WHERE clause with say max 2 constraints)?
    – knb
    Aug 13, 2021 at 9:57
  • 1
    Pretty good.. This will be very useful Aug 13, 2021 at 13:47

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.