50 likes | 202 Views
4NF . Now, what happens if the ternary relationship works-in means something else : consider “the employees of an embassy use those language skills they have that are in common with the language skills needed at the embassy The following table is in BCNF. Is it in 4NF?. works-in. employee.
E N D
4NF Now, what happens if the ternary relationship works-inmeans something else: consider “the employees of an embassy use those language skills they have that are in common with the language skills needed at the embassy The following table is in BCNF. Is it in 4NF? works-in employee embassy language ACS - 3902 Ron McFadyen
4NF Suppose our US embassy only requires English Suppose our France embassy requires French and English Suppose John and April work at the US embassy. April speaks both English and French, but John speaks onlyEnglish Suppose David and Victor work at the US embassy and both speak English works-in employee embassy language John France English John France French April France English April France French David US English Victor US English ACS - 3902 Ron McFadyen
4NF • Recall, X Y, exists if when two tuples exist having the same X values: • T1(x, y1, z1) and T2(x, y2, z2), • implies the two tuples • T4(x, y2, z1) and T3(x, y1, z2) also exist x? y1? ... Is there an MVD here? works-in employee embassy language John France English John France French April France English April France French David US English Victor US English ACS - 3902 Ron McFadyen
4NF This version of works-in is in 4NF. There are no MVDs. Aside: Note however, that works-in could (for this case) be losslessly decomposed into the three projections: works-in employee embassy language emp-emb employee embassy emb-lan embassy language emp-lan employee language ACS - 3902 Ron McFadyen
4NF emp-emb emb-lan emp-lan employee embassy embassy language employee language John France France English John English April France France French April English David US US English April French Victor US David English Victor English Works-in = (emp-emb * emb-lan) * emp-lan Note that emp-lan allows us to know of languages an employee speaks where that language is not required at the embassy where he/she works. If that knowledge is important then … ACS - 3902 Ron McFadyen