ago
74 views
2 2 votes

Consider $\text{GSL}(D,M,N_1,N_2,G_1,G_2,C_1,C_2,E,R)$, where $M$ is the primary key, together with

$M\to D,$

$E\to R,$

$N_1\to G_1C_1,$

$N_2\to G_2C_2,$

$G_1C_1\to N_1,$

$G_2C_2\to N_2$.

Remember that, because $M$ is the primary key, $M$ functionally determines every attribute of the original relation.

Which of the following is the final BCNF decomposition when the dependencies are considered in the stated order?

  1. $\{MD,\ ER,\ N_1G_1C_1,\ N_2G_2C_2\}$
     
  2. $\{ER,\ N_1G_1,\ N_2G_2,\ MC_1C_2D\}$
     
  3. $\{MER,\ N_1G_1C_1,\ N_2G_2C_2,\ DN_1N_2\}$
     
  4. $\{ER,\ N_1G_1C_1,\ N_2G_2C_2,\ MDN_1N_2E\}$

1 Answer

3 3 votes

The first dependency to consider is the dependency induced by the primary key:

$M\to$ all attributes.

Since $M$ is a key, it does not violate BCNF.

Next,

$M\to D$ also does not violate BCNF because $M$ is a superkey.

Now consider

$E\to R$.

$E$ is not a superkey.

Therefore, this is the first BCNF violation.

Decompose into $R_1(E,R)$ and a remaining relation containing

$D,M,N_1,N_2,G_1,G_2,C_1,C_2,E$.

$ER$ is in BCNF because $E$ is its key.

Next consider

$N_1\to G_1C_1$.

$N_1$ is not a superkey of the large remaining relation.

Decompose out $R_2(N_1,G_1,C_1)$.

Similarly,

$N_2\to G_2C_2$ produces $R_3(N_2,G_2,C_2)$.

The remaining schema is $R_4(M,D,N_1,N_2,E)$.

Because $M$ was the primary key of the original relation, its projected dependencies still allow it to determine the attributes of this component.

Therefore, $R_4$ is in BCNF.

Now check the two reverse dependencies.

Inside $R_2(N_1,G_1,C_1)$, we have both $N_1\to G_1C_1$ and $G_1C_1\to N_1$.

Thus both $N_1$ and $G_1C_1$ are candidate keys of $R_2$.

So no BCNF violation remains.

Exactly the same reasoning applies to $R_3(N_2,G_2,C_2)$.

Hence, the final decomposition is $\boxed{\{ER,\ N_1G_1C_1,\ N_2G_2C_2,\ MDN_1N_2E\}}$

Answer : D

ago edited ago by
Answer:
Position:
Show:

Related questions

2 2 votes
1 1 answer
55
55 views
GO Classes asked 1 day ago
55 views
Consider $R(A,B,C,D,E)$ with $F=\{AB\to C,\ BC\to D,\ CD\to E,\ DE\to A\}$.Suppose the BCNF decomposition begins with the violating FD $:CD\to E$.Which set of relations i...
4 4 votes
1 1 answer
45
45 views
GO Classes asked 1 day ago
45 views
Consider $R(A,B,C,D,E)$ with $F=\{D\to B,\ DE\to A,\ C\to AD\}$.Which of the following is a valid BCNF decomposition of $R$?$\{DB,\ ADE,\ CE\}$ $\{CDA,\ DEB\}$ $\{DB,\ AC...
1 1 vote
1 1 answer
30
30 views
GO Classes asked 1 day ago
30 views
Consider $R(I,N,W,S,Q,T,P,R)$ with $F=\{N\to W,\ IN\to S\}$.Which of the following is the BCNF decomposition produced by successively removing these violations?$R_1(N,W)$...
1 1 vote
1 1 answer
34
34 views
GO Classes asked 1 day ago
34 views
Consider $R(A,B,C,D)$ with $F=\{A\to B,\ C\to D\}$. Which of the following is a correct BCNF decomposition of $R$?$R_1(A,B)$$R_2(C,D)$$R_3(A,C)$ $R_1(A,B,C)$$R_2(C,D)$ $R...