# Lab4: Functional Dependency

## Exercises

Lossless decomposition of relations

- Suppose that we decompose the schema \(R = (A, B, C, D, E)\) into \[ \begin{aligned} & R_1 = (A, B, C) \\ & R_2 = (A, D, E) \end{aligned} \] Show that this decomposition is a lossless decomposition if the following set \(\mathcal{F}\) of functional dependencies holds: \[ \begin{aligned} &A \mapsto BC \\ &CD \mapsto E \\ &B \mapsto D \\ &E \mapsto A \end{aligned} \]

BCNF

- Show that if we combine the relation s
`instructor`

and`department`

into`in_dep (ID, name, salary, dept_name, building, budget)`

then the resulting relation is not in Boyce–Codd normal form (BCNF).

Alternative Definition of the Keys

- The Functional dependencies \(R(A,B,C,D,E,F,G)\) is given: \[ \begin{aligned} \mathcal{F} = &\{ \\ &ABD \mapsto EG,\\ &C \mapsto DG, \\ &E \mapsto FG, \\ &AB \mapsto C, \\ &G \mapsto F\\ &\}.\\ \end{aligned} \] Find the candidate key for \(R\).

Discovering FDs

- Given the relation:

A | B | C |
---|---|---|

a1 | b1 | c3 |

a1 | b1 | c3 |

a2 | b1 | c1 |

a2 | b1 | c1 |

a3 | b1 | c1 |

List all nontrivial functional dependencies satisfied by the relation.

Practical example

In the University Database (`univdb-sqlite.db`

), perform the following tasks:

- Join the relations
`instructor`

and`department`

into`in_dep (ID, name, salary, dept_name, building, budget)`

and display the content of the new relation. - Save the resulting relation in the database.
- Split the relation back into two relation
`instructor_1 (ID, name, dept_name, salary`

) and`department_1(dept_name, building, budget)`

. - Compare the entries in the
`department`

relation with those in the`department_1`

and those in the`instructor`

with those in the`instructor_1`

. Comment on your findings. - Now, split the relation back into two relation
`instructor_2 (ID, name, dept_name, salary, budget`

) and`department_2(building, budget)`

. - Join relations
`instructor_2`

and`department_2`

into`in_dep_2 (ID, name, salary, dept_name, building, budget)`

- Compare the
`in_dep_2`

relation with`in_dep`

. - Split back the
`in_dep_2`

relation into`instructor_2 (ID, name, dept_name, salary`

) and`department_2(dept_name, building, budget)`

. Compare the entries in the`department`

relation with those in the`department_2`

and those in the`instructor`

with those in the`instructor_2`

. Comment on your findings.

## Discovering functional dependencies using Metanome

FD Discovery

Download the Metanome profiler and a set of the functional dependency discovery algorithms, run one of the algorithms on csv file (you can find examples of datasets on the same website), and report the discovered FDs. Metanome is built using `JAVA`

so you will need to install it on your computer.