DTA(M) Database Theory & Applications
Quiz: Functional Dependency & Normalization
Solutions
[Part A: Functional Dependency Quiz]
1. __________ refers to an attribute or group of attributes mentioned in the left-hand side of the arrow in a
Functional Dependency (FD).
a) Discriminator
b) Determinant
c) Multivalued attribute
d) All of the above
Solution: Determinant.
2. In a functional dependency X –> Y, if Y is functionally dependent on X, but not on X’s proper subsets, then
we would call the functional dependency as:
a) Full Functional Dependency
b) Partial Functional Dependency
c) Multivalued Functional Dependency
d) None of the above
Solution: Full Functional Dependency. Example: {SSN, PID} → Hours. The ‘Hours’ is only fully
dependent on both ‘SSN’ and ‘PID’ (project ID) attributes.
3. Which of the following is the result of bad database design?
a) Repetition of Information
b) Inability to represent some information
c) Inconsistent database state due to some transaction
d) All of the above
Solution: All of the above
4. If X –> {Y, Z} then X –> Y and X –> Z is
a) Composition Rule
b) Reflexivity Rule
c) Union Rule
d) Decomposition Rule
Solution: Decomposition Rule. Example: SSN →{Name, Salary} then SSN → Name and SSN → Salary
5. If X –> Y is a functional dependency and X and Y are sets of attributes, what is the relationship between X
and Y?
a) One-to-Many
b) Many-to-One
c) One-to-One
d) Many-to-Many
Solution: One-to-One
DTA(M) Database Theory & Applications
6. For a functional dependency X –> Y, it is said to be _________ if Y is the subset or equal to X.
a) Total
b) Trivial
c) Non-trivial
d) Partial
Solution: Trivial. Example: {SSN, Name} → SSN and {SSN, Name} → {SSN, Name} are both trivial FDs.
7. Which of the following functional dependencies are held in the given table?
RegNo Name Gen Edu Phone Manager
R1 Sundar M BTech 9898786756 Kumar
R2 Ram M MS 9897786776 Kumar
R3 Karthik M MCA 8798987867 Steve
R4 John M BSc 7898886756 Badrinath
R5 Priya F MS 9809780967 Kumar
R6 Ram M MTech 9876887909 Jagdeesh
a) RegNo –> {Name, Gen, Edu}
b) RegNo –> {Phone}
c) {Manager, Name} –> RegNo
d) All of the above
Solution: All of the above. All FDs hold true.
8. Consider the FDs: RegNo –> {Name, Gen, Edu, Phone, Manager}, Phone –> {RegNo, Name, Gen, Edu,
Manager} and Edu –> Manager. If these are the functional dependencies of the given relation, which of the
following is the Primary key? (it may be more than one option)
a) RegNo
b) Phone
c) {RegNo, Phone}
d) {RegNo, Edu}
Solution: RegNo or Phone; both options are correct. (c) is incorrect, since it is not the minimum super key
(it consists of two candidate keys), while (d) it is just a superkey.
9. Let the relation R(A, B, C, D, E). If {A, B}, {A, B, E}, and {C, D, E} can uniquely identify any tuple in the
relation, which of the following would be the Primary key?
a) ABE
b) CDE
c) AB
d) None of the above
Solution: The minimum super key, i.e., the set with the minimum number of attributes tha can uniquely
identify any tuples in R is the set {AB}.
10. Consider the relation R(B, O, I, S, Q, D). If S –> D, I –> B, {IS} –> Q, and B –> O, then which is the
candidate key?
a) IS
b) IB
c) BO
d) SD
DTA(M) Database Theory & Applications
Solution: The set {IS} can determine all the other attributes. I determines B, and via transitivity of B → O,
I determines both {B, O}. S determines D and both {IS} determine Q. Hence, {IS} determine {Q, D}. That
is, IS can determine B, O (from I), D (from S), Q (from IS), i.e., IS → {B, O, D, Q}.
11. If A –> B, B –> C, and C –> D, then which of the following is true?
a) A –> C
b) B –> D
c) A –> D
d) All of the above
Solution: All of the above. (a) A → via transitivity (A → B, B → C), (b) B → D via transitivity (B → C, C
→ D), and (c) A → D via all the above: A → B, B → C, C → D.
12. Which of the following in a relation schema R fully functionally determines all the attributes of R?
a) Primary Key
b) Candidate Key
c) Both Primary and Candidate Key
d) Neither Primary Key nor Candidate Key
Solution: Both Primary and Candidate Key.
13. Let the candidate keys for the relation R(A,B,C,D,E) be {A,B}, {A,C}, {C,D}, and assume that {A,B} is
chosen as the Primary key for R. Which of the following is true?
a) A is non-prime attribute
b) C is a prime attribute
c) E is prime attribute
d) None of the above
Solution: C is a prime attribute.
14. Assume that a Bank associates every Customer with the home Branch, in which the customer maintains an
account. Which of the following is true?
a) Branch –> Branch
b) Customer –> Branch
c) Customer –> Customer
d) All of the above
Solution: All of the above
15. In a relational schema R(A, B, C) with functional dependencies A –> B, B –> C, and A –> C, which of the
functional dependencies is redundant?
a) A –> C
b) A –> B
c) B –> C
d) None of the above
Solution: A → C, since this can be inferred by the transitivity: A→B and B → C.
DTA(M) Database Theory & Applications
[Part B: Normal Forms Quiz]
1. Assume that a relation R has the following properties. What is the normal form of R?
Properties: No multi-valued attributes, no partial functional dependencies with the primary key.
a) First Normal Form
b) Second Normal Form
c) Third Normal Form
d) Boyce-Codd Normal Form
2. Assume that a relation R has the following properties. What is the normal form of R?
Properties: Has no partial functional dependencies, has multi-valued attributes
(a) First Normal Form
(b) Second Normal Form
(c) Third Normal Form
(d) None of the above
3. Assume that a relation R has the following properties. What is the normal form of R?
Properties: Has no multi-valued attributes, has no partial functional dependencies, has attributes with atomic
domains, has transitive dependencies.
(a) 1NF & 2NF
(b) 1NF, 2NF, & 3NF
(c) 1NF & 3NF
(d) BCNF
4. Consider a relation R with the following functional dependencies:
{A → B, C → D, AC → E, D → F}.
How many keys does R have and what are they?
(a) 1, {(AC)}
(b) 2, {(AC), (AD)}
(c) 3, {(AC), (BC), (ABD)}
(d) 2, {(AC), (ABD)}
5. Consider the relation below. Select one of the following FDs that would violate the 3NF property?
Student (StudentID, StudentName, StudentPhone, CourseID, CourseName)
(a) StudentID → StudentName
(b) CourseID → CourseName
(c) StudentID → StudentPhone
(d) StudentID → CourseID
Explanation: The CourseID causes a transitive FD between CourseName and StudentID. That is,
StudentID determines CourseID and, due to the FD CourseID → CourseName, then StudentID
determines CourseName (via the non-prime attribute CourseID).
DTA(M) Database Theory & Applications
6. Consider the R(A, B, C, D), assume that A is the Primary Key and assume the FDs:
{A → B, A → C, AB → C, C → D}.
Which of the following would violate the 3NF rule?
(a) AB → C
(b) C → D
(c) A → BCD
(d) None of the above
Explanation: In 3NF we have that “no non-prime attribute should depend on another non-prime
attribute” (i.e, no transitive dependency). The non-prime attribute D is fully functionally dependent on
another non-prime attribute C. Hence, this violates 3NF.
7. Consider the relation R(A, B, C) with {A → B, C → B}. Assume that we decompose R into R1 (A, B) and
R2 (A, C). Which of the following is true for this case?
(a) R1 and R2 are in BCNF
(b) Dependency preserving decomposition
(c) R1 and R2 are in 3NF
(d) All of the above
Explanation: If we have a relation with just two attributes, we cannot look for partial functional
dependencies, transitive functional dependency, or multiple candidate keys. Hence, the relations R1 and
R2 are in 3NF and BCNF. The decomposition given above is not a dependency preserving decomposition.
Because the decomposition results in elimination of C → B.
8. Propose a set of FDs for the relation R(A, B, C, D) with primary key {AB} such that R is in 1NF but not in
2NF.
Solution: Consider the set of FDs: AB → CD and B → C. {AB} is the PK for this relation since AB → CD
which also implies that AB → ABCD. Moreover, the FD: B → C violates 2NF since:
• B is not a super key
• C is not part of some candidate key of R
• B is a subset of the primary key {AB} (partial functional dependency)
9. Propose a set of FDs for the relation R(A,B,C,D) with primary key {AB} such that R is in 2NF but not in
3NF.
Solution: Consider the set of FDs: AB → CD and C → D. {AB} is obviously the primary key for this
relation since AB → CD implies also that AB → ABCD. Moreover, the FD: C → D violates 3NF but not
2NF since:
• C is not a super key
• D is not part of some key of R
• D is transitively dependent of the primary key {AB} via the non-prime attribute C.
10. Consider the relation R(A, B, C) with the FD: B → C. If A is a candidate key for R, is it possible for R to be
in BCNF? If so, under what conditions? If not, explain why not.
Solution: The only way R could be in BCNF is if B is a super key for R.