[digiology2]

Hi, Im having trouble understanding the following problem:

Given the relation
FilmStudio(title,year,length,filmType,studioName,s tudioAddr)

with functional dependancies:
title year -> length filType studioName
studioName -> studioAddr

find a BNCF violation and use it to decompose FilmStudio into 2 relations that are in BCNF.

is the above notation the same as:
{{title, year} -> length,filmType, studioName} ? (where title and year form a composite key) Its just my notes use this notation so just making sure.

According to my notes, the only violation for BCNF is if a determinant is not a candidate key. Is there a way of proving this formaly without using assumtions like 'no two films can have the same name in the same year' or 'no two studio's can have the same name'?

I really appreciate your help, because I have an exam soon and I have to know how to do this?


Cheers

[MacAtak]

Your in luck. We just coverd this in my class!

Is the above notation the same as:
{{title, year} -> length,filmType, studioName} ? (where title and year form a composite key) Its just my notes use this notation so just making sure.

Yes, however there is also a partial dependency between studioName and studioAddr which is why the above is not BCNF.

For it to be BCNF no nonkey attribute can describe another attribute.

To make this BCNF you must take studioAddr out of the first table and create a second which has studioName as the key and studioAddr as its attribute.

End result would be:
filmStudio(title, year, length, filmType, studioName)
{{title, year} -> length, filmType, studioName}

studioAddr(studioName, studioAddr)
{{studioName} -> studioAddr}

I'm not too sure about being able to proove this formally. All I know is that to get the final result you need to follow a specified prodcedure to make the table BCNF.

[digiology2]

I notice in my notes that for each normal form, a relation has to be in the previous one(ie for 2NF it must be in 1NF) but is that not the case with BCNF? Are you saying that it must be in 3NF(which your saying its not because its not in 2NF(Partial FD)) for it to be in BCNF?


Cheers

[digiology2]

if you are saying it must also be in 3NF for it to be in BCNF then wouldn't your answer be wrong because it has a transitive dependancy {{title, year} -> studioAddr}?
How did you go about choosing that combination of attributes for the two tables anyway?

Cheers

[MacAtak]

I notice in my notes that for each normal form, a relation has to be in the previous one(ie for 2NF it must be in 1NF) but is that not the case with BCNF?

No, that is the case. Your notes are right. BCNF is 3NF is 2NF is 1NF.

Are you saying that it must be in 3NF(which your saying its not because its not in 2NF(Partial FD)) for it to be in BCNF?

Yes.

if you are saying it must also be in 3NF for it to be in BCNF then wouldn't your answer be wrong because it has a transitive dependancy {{title, year} -> studioAddr}?

No, my answer does not have a transitive dependency becuase studioName is now a key by itself ( it is no longer a key in the first table ) making studioAddr a proper subset of studioName. A transitive dependency can only happen within the same table. At least this is my understanding.

Edit-> The above is not true. There is a transitive dependency, but it does not violate 3NF because studioName is a candidate key.

How did you go about choosing that combination of attributes for the two tables anyway?

I'll try my best...

You basically need to create tables such that there is only one key (which can be a set) that determines all other attributes for each table. So, becuase {title, year} determined everything except for studioAddr, studioAddr must be removed from that table. Then becuase studioName determined studioAddr that now becomes the key for a new table with attribute studioAddr. You then do this recursively, meaning if the new table had some FDs that did not comply with BCNF then you'd have to do the same thing again with the new table.

In other words if the left hand of a FD does not determine all attributes for that table then that FD essentially becomes the new table and you remove everything from the right hand side of that FD from the original table.

Does that make any sense...it makes sense in my head, but that doesn't help you . Let me know if you have any more questions. I'm not an expert, but this got me looking more closely at my book, which is good for me too.

[digiology2]

Originally posted by MacAtak:
No, that is the case. Your notes are right. BCNF is 3NF is 2NF is 1NF.

Ok, but this page seems to contradict this: http://defiant.yk.psu.edu/~lxn/IST_2...finitions.html
In the definitions for 2NF and 3NF its saying the relation must be in the previous one but for BCNF it doesn't, it even has an example of a relation that is not in 3NF but is in BCNF!


Thanks very much for your help, your a star!!!!!

[MacAtak]

In the definitions for 2NF and 3NF its saying the relation must be in the previous one but for BCNF it doesn't, it even has an example of a relation that is not in 3NF but is in BCNF!

Hmm, I can't seem to find the example your talking about but here's a quote from my DB book:

These forms have increasingly restrictive requirements: Every relation in BCNF is also in 3NF, every relation in 3NF is also in 2NF, and every relation in 2NF is in 1NF.

That page does seem like a good overview. The last comment on that page makes a good statement on the transitive question you had. I guess that there is a transitive dependency in my solution but it doesn't violate 3NF becuase studioName is also a candidate key.

That page is probably a good study resource. Thanks for the link and good luck!