Dependencies in database

Database functional dependencies example

Given the relation LDS (L, D, S) with the following functional dependencies definitions:

LDS is decomposed into two relations: LD (L, D) and SL (S, L) which are populated as follows:

Inserting the following row violates the functional dependency S, D -> L

The question is how to enforce the the functional dependency S, D -> L on insert and update operations in the SL table?

Solution

The natural join between LD and SL tables before inserting the the 3d row into SL table looks like

After inserting the 3d row the natural join will give us

As you can see S1 D1 is repeated twice for L1 and L3 however this violates the functional dependency (S, D) -> L One combination of (S, D) must only appear once. One way to solve this problem is to prevent the operation using a trigger. The trigger code has to join the two tables together and count the number of L values given some (S, D).  if the count is greater than one in any of the result set then the insert or update operation has caused an integrity problem. In that case the trigger raises an application exception.

Here is the SQL code to do that:

I hope this tiny tutorial shed some light on basic functional dependency database concepts. If you have comments or questions, please use the comments section below. Thanks for reading.