Relational Schema Practice Questions

A Foreign Key is a field (or collection of fields) in one table that uniquely identifies a row of another table. Its primary function is to enforce a relationship between two tables by ensuring that every value in the foreign key column must either match an existing primary key in the referenced table or be $NULL$. This mechanism prevents orphaned records and maintains cross-table consistency, which is formally defined as Referential Integrity. Consequently, the

Given the functional dependencies $AB\rightarrow CD, AF\rightarrow D, DE\rightarrow F, C\rightarrow G, F\rightarrow E, G\rightarrow A$.
To check option C, we calculate the attribute closure of $\{AF\}$:

1. Start with $\{AF\}^{(0)} = \{A, F\}$.
2. Applying $F\rightarrow E$ yields $\{A, F, E\}$.
3. Applying $AF\rightarrow D$ yields $\{A, F, E, D\}$.
4. No further functional dependencies can be applied because $B, C,$ and $G$ are not reachable.
   The closure is $\{A, D, E, F\}$, which is not equal to $\{A, C, D, E, F, G\}$. Therefore, statement C is false.

Attributes $E$ and $H$ do not appear on the right side of any functional dependency, so they must be included in every candidate key.
We evaluate potential keys by calculating the closures of sets including $E$ and $H$.
Calculating closures:
$\{AEH\}^+ = \{A, E, H, B, C, D\}$
$\{BEH\}^+ = \{B, E, H, C, D, A\}$
$\{DEH\}^+ = \{D, E, H, A, B, C\}$
Each set produces the full set of attributes $\{A, B, C, D, E, H\}$, confirming these are the candidate keys.

To determine the functional dependencies, we check if the same value in the determinant column corresponds to a unique value in the dependent column.

1. Check for $B \rightarrow C$: Look at rows with the same value for $B$.
2. In the first two rows, $B = 1$. However, the corresponding values for $C$ are $1$ and $0$.
3. Since the same value of $B$ maps to different values of $C$, the functional dependency $B \rightarrow C$ does not hold.
4. Therefore, $B$ does not functionally determine $C$, making this a valid conclusion based on the relation instance.

Given the functional dependencies $F = \{A \to B, C \to D\}$.
The sub-relations are $R_1(A, B)$ with $F_1 = \{A \to B\}$ and $R_2(C, D)$ with $F_2 = \{C \to D\}$.
Since $F_1 \cup F_2 = F$, the decomposition is dependency preserving.
A decomposition is lossless-join if $R_1 \cap R_2 \to R_1$ or $R_1 \cap R_2 \to R_2$.
Here, $R_1 \cap R_2 = \emptyset$, which is not a superkey for $R_1$ or $R_2$.
Thus, the decomposition is not lossless-join.

To be a key, the attribute closure must contain all attributes in $R = \{A, B, C, D, E, F\}$.
Testing the closure of $\{EC\}$:

1. Start with $\{EC\}$.
2. Using $E \rightarrow A$, we obtain $\{E, C, A\}$.
3. Using $A \rightarrow B$, we obtain $\{E, C, A, B\}$.
4. Using $C \rightarrow F$, we obtain $\{E, C, A, B, F\}$.
5. Using $EC \rightarrow D$, we obtain $\{E, C, A, B, F, D\} = \{A, B, C, D, E, F\}$.
   Since $\{EC\}^+ = R$, $EC$ is a key for $R$.