Functional dependencies Given a relation schema R = (A, B, C, D, G, H) and a set

Question

Functional dependencies

Given a relation schema R = (A, B, C, D, G, H) and a set of functional dependencies F = {D rightarrow G, CD rightarrow G, D rightarrow C, H rightarrow G} Find Fc, a canonical cover of F. Please show all the steps to get your answer. A Hollywood movie studio uses a relation called Movie to keep track of information about movie stars, what fee a star charges for appearing in a movie, movies the star has appeared in, and agents. The scheme of Movie is: Movie-scheme = (star_name, star_address, fee, title, year, length, agent, agent_address) The set of dependencies on Movie-scheme is: F = { star_name rightarrow {fee, agent, agent_address}, {star_name, title } rightarrow fee, {title, year} rightarrow length, agent rightarrow agent_address} Give a decomposition of Movie-scheme into BCNF. Show your work, and justify that each of your new schemes is in BCNF. Do NOT do unnecessary decompositions - if a scheme is in BCNF, you should not decompose it any further. Is the original Movie-scheme in 3NF? Why or why not? If not, give a lossless-join decomposition of Movie-scheme into 3NF. Show your work.

Explanation / Answer

1)
D -> G
CD -> G
D -> C
H -> G

step i) combine D -> G and CD -> G into CD -> G
now set is { CD->G, D->C, H->G}

step ii) as D->C is already present in CD->G so it further reduced to
set {D->C, H->G}
this is canonical form.

---------------------------------------------------------------------------------
2)

(1) For BCNF, it must have candidate keys and doesnt contain transitive dependencies.
in above schema, to get length attribute we have to check both {title, year}
So after decompsing:
{star_name -> fee, agent,star_address}
{fee -> title,year,length}
{agent -> agent_address}

(2)
No it is not in 3NF.
in above schema, to get length attribute we have to check both {title, year}
So there is trivial dependencies..so it not in 3NF.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.