Dependencies in database
Database functional dependencies example
Given the relation LDS (L, D, S) with the following functional dependencies definitions:
1 |
L ->D and (S, D) -> L |
LDS is decomposed into two relations: LD (L, D) and SL (S, L) which are populated as follows:
1 2 3 4 5 6 7 8 9 10 |
LD: L1 D1 L2 D2 L3 D1 SL: S1 L1 S2 L2 |
Inserting the following row violates the functional dependency S, D -> L
1 |
S1 L3 |
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
1 2 |
S1 D1 L1 S2 D2 L2 |
After inserting the 3d row the natural join will give us
1 2 3 |
S1 D1 L1 S2 D2 L2 S1 D1 L3 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
CREATE OR REPLACE TRIGGER "NUM_CHECK" AFTER INSERT OR UPDATE OF S, L ON SL DECLARE MAX_NUM NUMBER; MAX_NUM_EXCEPTION EXCEPTION; BEGIN SELECT MAX(NL) INTO MAX_NUM FROM ( SELECT SL.S, LD.D, COUNT(SL.L) NL FROM LD, SL WHERE LD.L = SL.L GROUP BY SL.S, LD.D ); IF (MAX_NUM > 1) THEN RAISE MAX_NUM_EXCEPTION; END IF; EXCEPTION WHEN MAX_NUM_EXCEPTION THEN RAISE_APPLICATION_ERROR (-20300, 'VIOLATING FUNCTIONAL DEPENDECY: (S, D) -> L); END; |
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.