Functional dependency calculator

$Id: functional-dependencies.php,v 1.11 2006/04/10 16:32:48 bigfoot Exp $

This script automatically calculates functional dependencies for relational tables with a known set of rows.
Inspired by "Conceptual Design of Database Systems" exercises at IMMD6, FAU Erlangen, led by Maciej Suchomski.
Use the examples at the bottom of this page to see what's going on (please send me more interesting examples!).
TODO: verify correct behaviour towards transitive dependencies (feedback, please!)
TODO: external links to documentation
NOTE: This is currently alpha. Please inform me about any errors.

INPUT:

OUTPUT:

->	"functionally determines"
F	full functional dependency
P	partial functional dependency
C	key candidate
C!	minimal length key candidate
T	transitive dependency to underlined dependents (experimental!)

1.	Project_No	->	Pers_ID	F
2.	Project_No, Pers_ID, Dept_No	->	Job, Salary	P	C	T
3.	Project_No, Pers_ID, Dept_No, Job	->	Salary	P	C
4.	Project_No, Pers_ID, Dept_No, Salary	->	Job	P	C
5.	Project_No, Pers_ID, Job	->	Dept_No, Salary	P	C
6.	Project_No, Pers_ID, Job, Salary	->	Dept_No	P	C
7.	Project_No, Pers_ID, Salary	->	Dept_No, Job	P	C
8.	Project_No, Dept_No	->	Pers_ID, Job, Salary	P	C!	T
9.	Project_No, Dept_No, Job	->	Pers_ID, Salary	P	C	T
10.	Project_No, Dept_No, Job, Salary	->	Pers_ID	P	C
11.	Project_No, Dept_No, Salary	->	Pers_ID, Job	P	C	T
12.	Project_No, Job	->	Pers_ID, Dept_No, Salary	P	C!	T
13.	Project_No, Job, Salary	->	Pers_ID, Dept_No	P	C	T
14.	Project_No, Salary	->	Pers_ID, Dept_No, Job	P	C!	T
15.	Pers_ID, Dept_No, Job	->	Salary	P
16.	Pers_ID, Dept_No, Salary	->	Job	P
17.	Pers_ID, Job	->	Salary	P
18.	Pers_ID, Salary	->	Job	P
19.	Dept_No	->	Pers_ID	F
20.	Dept_No, Job	->	Pers_ID, Salary	P		T
21.	Dept_No, Job, Salary	->	Pers_ID	P
22.	Dept_No, Salary	->	Pers_ID, Job	P		T
23.	Job	->	Pers_ID, Salary	F		T
24.	Job, Salary	->	Pers_ID	P
25.	Salary	->	Pers_ID, Job	F		T

EXAMPLES

partially taken from Maciej Suchomski's exercise slides
(NEW: click "try it" to run the example):

(1)
A B C D E
---------
a b z w q
e b r w p
a d z w t
e d r w q
a f z s t
e f r s t

try it

(2) (default)
A B C
-----
a b x
a b y
a d x
a d y
c f x
c f y

try it

(3)
A B C D E
---------
a b x v q
e b y v z
a d x v t
e d y v p
a f x p t
e f y p z

try it

Cust# Name  Address
-------------------
43    Jones 121_1st
55    Smith 222_2nd

try it

Invoice# Line# Quant1 Part1      Amt1
-------------------------------------
1001     1     200    Screw      2.00
1001     2     300    Nut        2.25
1001     3     100    Whatever   0.75
1002     1       1    Motor     52.00
1002     2      10    Saw      121.00
1003     3       5    Brace     44.44

try it

Project_No Pers_ID Dept_No Job Salary
-------------------------------------
5 1 1 Analysis  13
5 1 2 Leader    18
7 1 1 Leader    18
8 1 1 Marketing 15
8 1 2 Leader    18

try it

transitive dependency?
A B C
-----
a a 1
b a 1
c b 1
d b 1
e c 2
f c 2

try it