Experiment with the Heath Theorem

In this section we explore the properties of the theorem with the help of SQL statements.

Functional Dependency Missing

We want to see what happens when we do not have the functional dependency required for the theorem:

select * from R;
 A  | B  | C  
----+----+----
 a1 | b1 | c1
 a1 | b2 | c2

Note that there is NO functional dependency A → B or A → C. We project into R1 and R2:

create table R1 as select A, B from R;
select * from R1;
 A  | B  
----+----
 a1 | b2
 a1 | b1

create table R2 as select A, C from R;
select * from R2;
 A  | C  
----+----
 a1 | c1
 a1 | c2

Now we join R1 and R2 and compare the result with the original table R:

select * from R1 natural join R2;
 A  | B  | C  
----+----+----
 a1 | b1 | c1
 a1 | b2 | c1
 a1 | b2 | c2
 a1 | b1 | c2

The join of R1 and R2 is NOT equal to R.

Functional Dependency Present

Compare the above with the following situation:

select * from R;
 A  | B  | C  
----+----+----
 a1 | b1 | c1
 a2 | b2 | c2
 a2 | b2 | c3

In this case we have A functional dependency A → B. Note that we do NOT have A → C.

The theorem deals with relations, not SQL tables; duplicate rows are possible in SQL tables, but not in relations; we use the DISTINCT option here to avoid duplicate rows in R1.

create table R1 as select distinct A, B from R;
select * from R1;
 A  | B  
----+----
 a2 | b2
 a1 | b1

create table R2 as select A, C from R;
select * from R2;
 A  | C  
----+----
 a1 | c1
 a2 | c3
 a2 | c2

In this case the join of R1 and R2 is equal to R:

select * from R1 natural join R2;
 A  | B  | C  
----+----+----
 a2 | b2 | c3
 a1 | b1 | c1
 a2 | b2 | c2