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 relations
instructor
anddepartment
intoin_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
anddepartment
intoin_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
) anddepartment_1(dept_name, building, budget)
. - Compare the entries in the
department
relation with those in thedepartment_1
and those in theinstructor
with those in theinstructor_1
. Comment on your findings. - Now, split the relation back into two relation
instructor_2 (ID, name, dept_name, salary, budget
) anddepartment_2(building, budget)
. - Join relations
instructor_2
anddepartment_2
intoin_dep_2 (ID, name, salary, dept_name, building, budget)
- Compare the
in_dep_2
relation within_dep
. - Split back the
in_dep_2
relation intoinstructor_2 (ID, name, dept_name, salary
) anddepartment_2(dept_name, building, budget)
. Compare the entries in thedepartment
relation with those in thedepartment_2
and those in theinstructor
with those in theinstructor_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.