Normal Form Practice Questions

To determine the correct statements, we analyze each normalization property:

1. Option A is true because the standard 3NF synthesis algorithm guarantees a lossless-join, dependency-preserving decomposition for any relation.
2. Option B is true because 1NF simply mandates atomicity; any relation can be represented in 1NF without losing any functional dependencies.
3. Option C is true because, unlike 3NF, BCNF decomposition does not always preserve all functional dependencies; it prioritizes eliminating redundancy at the cost of dependency preservation in certain schemas (e.g., $R(A, B, C)$ with $AB \to C$ and $C \to B$).
4. Option D is false because 2NF is less restrictive than 3NF; since a dependency-preserving 3NF decomposition always exists, a dependency-preserving 2NF decomposition is also guaranteed to exist.

[CORRECT_OPTION: A, B, C]

Let the given relational schema be $R = (name, city, owner)$ and the functional dependencies (FDs) be $F = \{name \to city, name \to owner\}$.

1. Finding Candidate Key(s) for $team$:

   • Since name determines both city and owner, name is a candidate key for the $team$ relation.

2. Checking for BCNF in $team$:

   • A relation is in BCNF if for every non-trivial FD $X \to Y$, $X$ is a superkey.
   • For $name \to city$, name is a superkey.
   • For $name \to owner$, name is a superkey.
   • Therefore, the relation $team$ is already in BCNF. This means option A and D are FALSE.

3. Analyzing the Decomposition:

   • The relation $team$ is decomposed into $t_1(name, city)$ and $t_2(name, owner)$.
   • For $t_1(name, city)$: The FD is $name \to city$. The candidate key is name. Since name is a superkey, $t_1$ is in BCNF.
   • For $t_2(name, owner)$: The FD is $name \to owner$. The candidate key is name. Since name is a superkey, $t_2$ is in BCNF.
   • Thus, both relations $t_1$ and $t_2$ are in BCNF. This makes option B TRUE.

4. Checking for Lossless Join:

   • For a decomposition $R$ into $R_1$ and $R_2$ to be lossless, two conditions must be met:
     • $R_1 \cup R_2 = R$ (which is $name, city \cup name, owner = name, city, owner$, so this holds).
     • $R_1 \cap R_2 \to R_1$ or $R_1 \cap R_2 \to R_2$ must be implied by $F$.
   • Here, $R_1 \cap R_2 = \{name\}$.
   • The FD $name \to city$ is present in $F$ and city is part of $R_1$. The FD $name \to owner$ is present in $F$ and owner is part of $R_2$.
   • Since $name \to city$ holds (meaning $\{name\} \to R_1$) and $name \to owner$ holds (meaning $\{name\} \to R_2$), both conditions are met. Alternatively, $name \to city$ holds and $city \subseteq R_1$, $name \to owner$ holds and $owner \subseteq R_2$.
   • Therefore, the decomposition is lossless. This makes option C TRUE.

[CORRECT_OPTION: B, C]

Let's analyze the properties of a relation in First Normal Form (1NF):

1. Atomic Attributes: Every attribute in the relation must hold only atomic (indivisible) values. This implies that composite attributes (attributes composed of multiple parts) and multi-valued attributes (attributes that can hold multiple values for a single tuple) are NOT allowed. Therefore, "R cannot have a composite attribute" is TRUE.
2. Candidate Keys: A relation in 1NF must have at least one candidate key to uniquely identify tuples.
3. Foreign Keys: A relation in 1NF can certainly have foreign keys; foreign keys are essential for establishing relationships between tables. So, "R cannot have a foreign key" is FALSE.
4. Multi-attribute Key: A candidate key can be composed of multiple attributes, in which case it is called a composite key or a multi-attribute key. Therefore, "R can have a multi-attribute key" is TRUE.
5. Number of Candidate Keys: A relation can have one or more candidate keys. "R cannot have more than one candidate key" is FALSE.

Based on this analysis, the TRUE statements are: R cannot have a composite attribute, and R can have a multi-attribute key.

Let's analyze each statement regarding relational data models:
(A) "A relation with only two attributes is always in BCNF." This statement is TRUE. For a relation $R(A, B)$ with two attributes, the possible non-trivial functional dependencies are $A \rightarrow B$, $B \rightarrow A$, or both, or none. In all these cases, the trivial functional dependencies (e.g., $A \rightarrow A$) are excluded. If $A \rightarrow B$ holds, and A is a candidate key (or part of one), BCNF holds. If $B \rightarrow A$ holds, and B is a candidate key, BCNF holds. If both hold, both A and B are candidate keys, and BCNF holds. If no non-trivial FDs hold, then vacuously BCNF holds. In any two-attribute relation, any non-trivial FD's left side must be a superkey, thus it is always in BCNF.
(B) "If all attributes of a relation are prime attributes, then the relation is in BCNF." This statement is FALSE. If all attributes are prime, the relation is in 3NF, but not necessarily BCNF. For example, if $R(A, B, C)$ has FDs $A \rightarrow B$, $B \rightarrow A$, $C \rightarrow A$. Candidate keys are $A C$ and $B C$. All attributes are prime. But $A \rightarrow B$ violates BCNF if $A$ is not a superkey (which it isn't here as $AC$ is a superkey). For BCNF, every determinant must be a superkey.
(C) "Every relation has at least one non-prime attribute." This statement is FALSE. A relation where all attributes are part of some candidate key (meaning all attributes are prime) would have no non-prime attributes. For example, $R(A, B)$ with $A \rightarrow B$ and $B \rightarrow A$. Both A and B are prime attributes, and there are no non-prime attributes.
(D) "BCNF decompositions preserve functional dependencies." This statement is FALSE. BCNF decomposition is always lossless but is not always dependency-preserving. It might lose some functional dependencies that existed in the original relation, especially if the determinant of a lost dependency is not a superkey of the decomposed relation.
Therefore, only statement (A) is TRUE.

To determine which functional dependencies can be inferred, we compute the closure of the left-hand side attributes for each option using the given dependencies: $P \rightarrow QR$ and $RS \rightarrow T$.

1. For $PS \rightarrow T$: $(PS)^+ = \{P, S, Q, R, T\}$. Since $T \in (PS)^+$, $PS \rightarrow T$ can be inferred.
2. For $R \rightarrow T$: $(R)^+ = \{R\}$. Since $T \notin (R)^+$, $R \rightarrow T$ cannot be inferred.
3. For $P \rightarrow R$: $(P)^+ = \{P, Q, R\}$. Since $R \in (P)^+$, $P \rightarrow R$ can be inferred.
4. For $PS \rightarrow Q$: $(PS)^+ = \{P, S, Q, R, T\}$. Since $Q \in (PS)^+$, $PS \rightarrow Q$ can be inferred.

Therefore, options A, C, and D can be inferred.

A relation is in $1NF$ if all attributes contain atomic values. The definition of $2NF$ requires that a relation be in $1NF$ and that every non-key attribute be fully functionally dependent on the entire primary key. This requirement specifically eliminates partial dependencies, where a non-key attribute relies on only a subset of a composite primary key. Since the given condition confirms that every non-key attribute depends on the primary key, it satisfies the defining criteria for $2NF$.

For a table to be in 3NF but not BCNF, it must satisfy the 3NF conditions but violate BCNF.
BCNF requires that for every nontrivial functional dependency $X \rightarrow A$, X must be a superkey.
3NF requires that for every nontrivial functional dependency $X \rightarrow A$, either X is a superkey, or A is a prime attribute.
Therefore, a table that is in 3NF but not BCNF must have at least one nontrivial functional dependency $X \rightarrow A$ where X is NOT a superkey, but A IS a prime attribute.

The decomposition of schema X(PQRS) results in Y(PR) and Z(QRS).

1. For schema Z(QRS) with FDs {QR→S, S→Q}, the candidate keys are QR and RS. The dependency S→Q violates BCNF because S is not a superkey. Therefore, statement I, which claims both are in BCNF, is false.
2. The decomposition is lossless because the common attribute between Y and Z is R, and R is the candidate key of schema Y(PR). The decomposition is also dependency preserving as all original FDs {R→P}, {QR→S}, and {S→Q} are maintained within Y or Z. Therefore, statement II is true.

Let's analyze Schema II: Registration(rollno, courseid, email) with FDs rollno, courseid → email and email → rollno.
The candidate keys are {rollno, courseid} and {email, courseid}.
The set of prime attributes (part of any candidate key) is {rollno, courseid, email}. There are no non-prime attributes.

A schema is in 3NF if for every FD $X \to Y$, either $X$ is a superkey or $Y$ is a prime attribute.

• For email → rollno, the right-hand side rollno is a prime attribute.
• For rollno, courseid → email, the determinant is a superkey.
  Thus, the schema is in 3NF.

A schema is in BCNF if for every FD $X \to Y$, $X$ must be a superkey.

• The FD email → rollno violates BCNF because its determinant, {email}, is not a superkey.

Therefore, Schema II is in 3NF but not in BCNF.

The relation is $R(a,b,c,d)$ with functional dependencies $\{a \to c, b \to d\}$.
To find the candidate key, we calculate the closure of $\{a,b\}^+ = \{a,b,c,d\}$, identifying $\{a,b\}$ as the only candidate key.
Prime attributes are $\{a, b\}$, and non-prime attributes are $\{c, d\}$.
A relation is in 2NF if no non-prime attribute is partially dependent on any candidate key.
Here, $a \to c$ and $b \to d$ indicate that non-prime attributes $\{c, d\}$ depend on subsets of the candidate key $\{a, b\}$ (i.e., $\{a\} \subset \{a,b\}$ and $\{b\} \subset \{a,b\}$).
Since partial dependencies exist, the relation is in 1NF but fails the 2NF requirement.

A set of attributes $X$ is fully functionally dependent on $Y$ if two specific conditions hold. First, $X$ must be functionally dependent on $Y$ ($Y \to X$), as stated in Option A. Second, the dependency must be minimal, meaning no proper subset of $Y$ can determine $X$; this requirement is captured in Option B. Since both conditions are necessary to define a full functional dependency, both (A) and (B) must be satisfied.

The given set of functional dependencies is F = {V $\rightarrow$ W, VW $\rightarrow$ X, Y $\rightarrow$ VX, Y $\rightarrow$ Z}.
First, we decompose Y $\rightarrow$ VX into Y $\rightarrow$ V and Y $\rightarrow$ X.
The set becomes {V $\rightarrow$ W, VW $\rightarrow$ X, Y $\rightarrow$ V, Y $\rightarrow$ X, Y $\rightarrow$ Z}.
Since V $\rightarrow$ W, the attribute W in VW $\rightarrow$ X is extraneous. We can simplify VW $\rightarrow$ X to V $\rightarrow$ X.
The set is now {V $\rightarrow$ W, V $\rightarrow$ X, Y $\rightarrow$ V, Y $\rightarrow$ X, Y $\rightarrow$ Z}.
Now, we check for redundant dependencies. Since we have Y $\rightarrow$ V and V $\rightarrow$ X, by transitivity, Y $\rightarrow$ X holds. Therefore, Y $\rightarrow$ X is redundant and can be removed.
The final minimal cover is {V $\rightarrow$ W, V $\rightarrow$ X, Y $\rightarrow$ V, Y $\rightarrow$ Z}.

The original schema has a primary key of (VOLUME, NUMBER, STARTPAGE, ENDPAGE). The functional dependency (VOLUME, NUMBER) → YEAR represents a partial dependency, because a non-prime attribute (YEAR) depends on a proper subset of the primary key. This violates the condition for 2NF, meaning the original schema is only in 1NF. The redesigned database splits the schema into two new relations. An analysis of these new relations shows that both satisfy the conditions for BCNF. Since the new database satisfies 2NF (and higher forms) but the old one did not, the weakest such normal form is 2NF.

A functional dependency (FD) $X \rightarrow Y$ is trivial if $Y \subseteq X$. We need to find the trivial FD among the given options by checking this condition.

• Option A: $\{P,R\} \rightarrow \{S,T\}$. Here, $\{S,T\} \not\subseteq \{P,R\}$. So, this is not a trivial FD.
• Option B: $\{P,R\} \rightarrow \{R,T\}$. Here, $\{R,T\} \not\subseteq \{P,R\}$ because $T$ is not in $\{P,R\}$. So, this is not a trivial FD.
• Option C: $\{P,S\} \rightarrow \{S\}$. Here, $\{S\} \subseteq \{P,S\}$. So, this is a trivial FD.
• Option D: $\{P,S,U\} \rightarrow \{Q\}$. Here, $\{Q\} \not\subseteq \{P,S,U\}$. So, this is not a trivial FD.

Therefore, the only trivial functional dependency among the options is $\{P,S\} \rightarrow \{S\}$.

To determine if the decomposition $R_1(A, B)$ and $R_2(C, D)$ preserves dependencies, we project the functional dependencies $F = \{A \rightarrow B, C \rightarrow D\}$ onto the schemas. Since $A \rightarrow B$ holds in $R_1$ and $C \rightarrow D$ holds in $R_2$, the union of these dependencies covers $F$, meaning dependency preservation is satisfied.

For the lossless join property, we check if $R_1 \cap R_2 \rightarrow (R_1 - R_2)$ or $R_1 \cap R_2 \rightarrow (R_2 - R_1)$. Here, $R_1 \cap R_2 = \emptyset$. Because the intersection is empty, the join of $R_1$ and $R_2$ results in a Cartesian product, which introduces spurious tuples. Since neither $R_1$ nor $R_2$ is derived from a key intersection constraint, the join is lossy.

Based on the provided diagram, the functional dependencies are:

1. $\{A, B\} \rightarrow \{C, D, E\}$ (Primary key is $\{A, B\}$)
2. $C \rightarrow B$

First, determine the candidate keys. Since $C \rightarrow B$ and $\{A, B\} \rightarrow \{C, D, E\}$, the attribute set $\{A, C\}$ is also a candidate key, as $\{A, C\}^{+} = \{A, C, B, D, E\}$. The prime attributes are $\{A, B, C\}$.

For the dependency $C \rightarrow B$, $B$ is a prime attribute. Thus, the condition for Third Normal Form (3NF)-where either the determinant is a superkey or the dependent attribute is prime-is satisfied. However, $C$ is not a superkey, which violates the BCNF condition (that every determinant must be a superkey). Therefore, the table is in 3NF but not BCNF.

The statement "Every time the attribute $A$ appears, it is matched with the same value of attribute $B$" indicates a functional dependency where $A$ uniquely determines $B$, denoted as $A \to B$. The phrase "but not the same value of attribute $C$" implies that for a given $A$, there can be multiple associated values of $C$, which defines a multi-valued dependency, denoted as $A \twoheadrightarrow C$. Combining these two conditions results in the dependencies $A \to B$ and $A \twoheadrightarrow C$. This perfectly matches the structure provided in option B.

A prime attribute, also known as a key attribute, is defined as any attribute that is part of at least one candidate key within a relation scheme. It does not need to be part of all candidate keys, nor is its definition tied to foreign keys or exclusively to the primary key.

Here's a step-by-step explanation for the given problem:

Statement S1 Analysis:
A table with two single-valued attributes, say R(A, B), will always have only one possible primary key: either A, B, or {A, B}. For example, if A is the key, the only non-key attribute is B. Since B is single-valued and fully functionally dependent on A, all normal forms (1NF, 2NF, 3NF, BCNF) are satisfied. This holds true for any combination of keys, making S1 TRUE.

Statement S2 Analysis:
Given functional dependencies (FDs):
F = {AB → C, D → E, AB → E, E → C}
The candidate minimal cover provided is:
F_m = {AB → C, D → E, E → C}

To check if F_m is a minimal cover for F, we verify two conditions:

1. Every FD in F can be derived from F_m (F $\subseteq$ F_m$^+$).
2. Every FD in F_m can be derived from F (F_m $\subseteq$ F$^+$).

Consider AB → E from F.
From F_m: (AB)$^+$ = {A, B, C}. E cannot be derived.
Thus, F_m cannot derive all FDs in F, meaning F_m is not a cover for F. Therefore, S2 is FALSE.