Question I You are given a relation, Club Events (Eventld, Clubld, Memberld) wit

Question

Question I You are given a relation, Club Events (Eventld, Clubld, Memberld) with the Functional Dependencies (FDs) as follows Eventld ---> Clubld (A given event belongs to a specific Club) Memberld, Clubld -> Eventld (A member, at a specific club, participates at most in one event of that club) a) Is the relation, Club Events in BCNF? Give your reasons (5 pts) Assume that the relation, ClubEvents is decomposed into two relations, Events (Eventld.Clubld) and Activities Memberld, Eventld Consider the following sample data in the tables, Events and Activities; Events Eventld El E2 ?? Activities Memberld Clubld C1 C2 C1 Eventld M1 M2 El E2 Now, try to insert the tuple below. E3 (violates the FD, Memberld, Clubld -Eventld) M1 b) Given the relations Events and Activities, explain clearly how you would enforce the FD, Eventld, on insertions of data into the two tables

Explanation / Answer

1. a) The relation is not in BCNF.

Here (club_id, Member_id) is the potential primary key. As club_id, Member_id -> Event_id. So every non-key attribute (only one in this case) is functionally derivable from primary key. So the relation is in 3NF. However, there is a non-key to key FD., i.e., Event_id -> club_id, this FD makes the relation unconsiderable for BCNF.

Even after the decomposition of the initial table, the relation fails to be in BCNF, Because the decomposition does not preserve the FDs, So it is not dependency preserving.

b) To ensure the smooth insertion, we first have to make the decomposition dependency preserving. For that we have to add Club_id as a key member in the Activities table. Then only the insertion becomes smooth.

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

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