Towards Understanding the Schema in Relational Databases

Abstract

Database systems are adept at performing efficient computations over large datasets, as long as the queries are issued by users who understand the schema and can formulate their goals in the precise framework of SQL. However, the explosion of data over the past two decades has led to more and messier processing tasks than those envisioned by the creators of the SQL standard in the 1970s. One of the reasons for this departure from the classical model of user interaction with a DBMS is the fact that some crucial information is often unavailable.

In this thesis, we work towards designing solutions for relational databases to discover the information that is often undocumented and yet useful for people to understand and work with the data. More specifically, we first propose a general rule, termed Randomness, which effectively discovers meaningful foreign keys, including multi-column foreign keys. Second, we design a data oriented solution that identifies strong relationships between relational columns and clusters them into semantic attributes, i.e. the columns that have same or similar meaning are clustered together. Lastly, we provide a principled solution to discover complex generating queries for the cases where the user has the query answer and wants to find out the generating query for further investigation and analysis. Such information is invaluably helpful for database users to express their goals into SQL queries and generally to better understand and explore the data. We validate our proposed approaches via extensive experiments using real and benchmark databases.