For a database relation R(a, b, c, d) where the domains of a, b, c, and d include only atomic values, and only the following functional dependencies and those that can be inferred from them hold: a → c b → d
First normal form but not in second normal form
Let's analyze the given database relation R(a, b, c, d) and the functional dependencies to determine its normal form. The domains of a, b, c, and d are stated to include only atomic values.
Understanding the Relation and Functional Dependencies
The relation is R with attributes (a, b, c, d). The given functional dependencies (FDs) are:
\( a \rightarrow c \)
\( b \rightarrow d \)
These are the only FDs that hold, along with those inferred from them.
Checking First Normal Form (1NF)
A relation is in First Normal Form (1NF) if the domains of all attributes contain only atomic (indivisible) values. The problem statement explicitly says "the domains of a, b, c, and d include only atomic values". This directly fulfills the definition of 1NF.
Therefore, the relation R(a, b, c, d) is in First Normal Form (1NF).
Finding Candidate Keys
To check for normal forms beyond 1NF, we first need to find the candidate keys of the relation. A candidate key is a minimal superkey.
Let's examine the attributes and dependencies:
- Attributes on the left side of FDs but not exclusively on the right: a, b
- Attributes exclusively on the right side: None
- Attributes on both sides: c, d
The attributes a and b are not determined by any other attributes. They are likely part of the candidate key.
Let's find the closure of {a, b}:
\( \{a, b\}^+ \)
- Start with {a, b}.
- From \( a \rightarrow c \), we add c. Set is now {a, b, c}.
- From \( b \rightarrow d \), we add d. Set is now {a, b, c, d}.
\( \{a, b\}^+ = \{a, b, c, d\} \)
Since the closure of {a, b} includes all attributes of the relation, {a, b} is a superkey. Now, let's check if it's minimal:
- Is {a} a superkey? \( \{a\}^+ = \{a, c\} \). This is not {a, b, c, d}. So {a} is not a superkey.
- Is {b} a superkey? \( \{b\}^+ = \{b, d\} \). This is not {a, b, c, d}. So {b} is not a superkey.
Since neither {a} nor {b} is a superkey, {a, b} is a minimal superkey. Thus, the only candidate key for R is {a, b}.
Checking Second Normal Form (2NF)
A relation is in Second Normal Form (2NF) if it is in 1NF and every non-prime attribute is fully functionally dependent on every candidate key.
- Prime attributes: Attributes that are part of *any* candidate key. Here, a and b are prime attributes because they form the candidate key {a, b}.
- Non-prime attributes: Attributes that are *not* part of *any* candidate key. Here, c and d are non-prime attributes.
- Candidate Key: {a, b}.
Now we check if the non-prime attributes (c and d) are fully functionally dependent on the candidate key {a, b}. Full functional dependency means the attribute is dependent on the *entire* key, not just a proper subset of it.
Consider the FD \( a \rightarrow c \):
- 'c' is a non-prime attribute.
- 'a' is a proper subset of the candidate key {a, b}.
- \( a \rightarrow c \) means 'c' is functionally dependent on 'a'.
This shows that the non-prime attribute 'c' is functionally dependent on a proper subset ('a') of the candidate key {a, b}. This is called a partial dependency. Since there is a partial dependency involving a non-prime attribute, the relation is NOT in 2NF.
Consider the FD \( b \rightarrow d \):
- 'd' is a non-prime attribute.
- 'b' is a proper subset of the candidate key {a, b}.
- \( b \rightarrow d \) means 'd' is functionally dependent on 'b'.
This shows another partial dependency: the non-prime attribute 'd' is functionally dependent on a proper subset ('b') of the candidate key {a, b}. This further confirms the relation is NOT in 2NF.
Conclusion on Normal Form
Based on our analysis:
- The relation R(a, b, c, d) is in 1NF because all domains contain atomic values.
- The relation R(a, b, c, d) is NOT in 2NF because non-prime attributes (c and d) are partially dependent on proper subsets of the candidate key {a, b} (specifically, \( a \rightarrow c \) and \( b \rightarrow d \)).
Therefore, the relation is in First Normal Form but not in Second Normal Form.
Revision Table: Database Normal Forms
| Normal Form | Conditions |
|---|---|
| 1NF | Attributes contain only atomic values; no repeating groups. |
| 2NF | Is in 1NF, and all non-prime attributes are fully functionally dependent on every candidate key (no partial dependencies of non-prime attributes on candidate keys). |
| 3NF | Is in 2NF, and there are no transitive dependencies of non-prime attributes on any candidate key. |
| BCNF (Boyce-Codd Normal Form) | For every non-trivial functional dependency X → Y, X is a superkey. |
Additional Information: Beyond 2NF
While the relation is only in 1NF, it's useful to understand higher normal forms:
- Third Normal Form (3NF): A relation is in 3NF if it is in 2NF and there are no transitive dependencies of non-prime attributes on any candidate key. A transitive dependency exists when a non-prime attribute is dependent on another non-prime attribute, which is itself dependent on the key (e.g., Key → A, A → B, where A and B are non-prime). Since our relation isn't even in 2NF, it cannot be in 3NF.
- Boyce-Codd Normal Form (BCNF): BCNF is a stricter version of 3NF. A relation is in BCNF if for every non-trivial functional dependency X → Y, X is a superkey. This eliminates dependencies where a non-superkey determines another attribute, including dependencies between prime and non-prime attributes, or between two prime attributes that are not part of the key X. Since our relation isn't in 2NF or 3NF, it is not in BCNF either.
Important Questions from Normal Form - Teaching
Consider a schema R(A, B, C, D) and following functional dependencies.
A → B
B → C
C → D
D → B
Then decomposition of R into R 1(A, B), R 2(B, C) and R 3(B, D) is _______
If every non-key attribute is functionally dependent on the primary key, then the relation is in _______.
Consider a relation R (A, B, C, D, E, F, G, H), where each attribute is atomic, and following functional dependencies exist.
CH → G
A → BC
B → CFH
E → A
F → EG
The relation R is ______.
Match List I with List II:
List I List II (A) BCNF (I) It removes multivalued dependency (B) 3 NF (II) It is not always dependency preserving (C) 2 NF (III) It removes transitive dependency (D) 4 NF (IV) It removes partial functional dependency Choose the correct answer from the options given below:
In relational database, if a relation R is in BCNF, then which of the following is true about relation R?
