Database Design(Normal Forms)

Question 1
Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F = {CH -> G, A -> BC, B -> CFH, E -> A, F -> EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R. How many candidate keys does the relation R have?
Cross
3
Tick
4
Cross
5
Cross
6


Question 1-Explanation: 
A+ is ABCEFGH which is all attributes except D. B+ is also ABCEFGH which is all attributes except D. E+ is also ABCEFGH which is all attributes except D. F+ is also ABCEFGH which is all attributes except D. So there are total 4 candidate keys AD, BD, ED and FD
Question 2

For the relation R(ABCDEFGH) with FD's= {CH->G, A->BC, B->CHF, E->A, F->EG such that F+ is exactly the set of FDs that hold for R.} Consider the FDs given in above question. The relation R is

Tick

in 1NF, but not in 2NF.

Cross

in 2NF, but not in 3NF.

Cross

in 3NF, but not in BCNF.

Cross

in BCNF



Question 2-Explanation: 

The table is not in 2nd Normal Form as the non-prime attributes are dependent on subsets of candidate keys. The candidate keys are AD, BD, ED and FD. In all of the following FDs, the non-prime attributes are dependent on a partial candidate key. A -> BC B -> CFH F -> EG

Question 3
Which of the following is TRUE?
Cross
Every relation in 3NF is also in BCNF
Cross
A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R
Tick
Every relation in BCNF is also in 3NF
Cross
No relation can be in both BCNF and 3NF


Question 3-Explanation: 

BCNF is a stronger version 3NF. So every relation in BCNF will also be in 3NF.

Question 4
Consider a relational table with a single record for each registered student with the following attributes.
1. Registration_Num: Unique registration number
   of each registered student
2. UID: Unique identity number, unique at the 
   national level for each citizen
3. BankAccount_Num: Unique account number at
   the bank. A student can have multiple accounts
   or join accounts. This attribute stores the 
   primary account number.
4. Name: Name of the student
5. Hostel_Room: Room number of the hostel 
Which one of the following option is INCORRECT?
Tick
BankAccount_Num is candidate key
Cross
Registration_Num can be a primary key
Cross
UID is candidate key if all students are from the same country
Cross
If S is a superkey such that S∩UID is NULL then S∪UID is also a superkey


Question 4-Explanation: 
Candidate Key value must uniquely identify the corresponding row in table. BankAccount_Number is not a candidate key. As per the question “A student can have multiple accounts or joint accounts. This attributes stores the primary account number”. If two students have a joint account and if the joint account is their primary account, then BankAccount_Number value cannot uniquely identify a row.
Question 5
Consider the following relational schema:
Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)
Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is TRUE about the above schema?
Tick
The schema is in BCNF
Cross
The schema is in 3NF but not in BCNF
Cross
The schema is in 2NF but not in 3NF
Cross
The schema is not in 2NF


Question 5-Explanation: 
A relation is in BCNF if for every one of its dependencies X → Y, at least one of the following conditions hold:
    X → Y is a trivial functional dependency (Y ⊆ X)
    X is a superkey for schema R 
Since (sname, city) forms a candidate key, there is no non-tirvial dependency X → Y where X is not a superkey
Question 6
Consider the following relational schemes for a library database: Book (Title, Author, Catalog_no, Publisher, Year, Price) Collection (Title, Author, Catalog_no) with in the following functional dependencies:
I. Title Author --> Catalog_no
II. Catalog_no --> Title, Author, Publisher, Year
III. Publisher Title Year --> Price 
Assume {Author, Title} is the key for both schemes. Which of the following statements is true?
Cross
Both Book and Collection are in BCNF
Cross
Both Book and Collection are in 3NF only
Tick
Book is in 2NF and Collection is in 3NF
Cross
Both Book and Collection are in 2NF only


Question 6-Explanation: 
Book (Title, Author, Catalog_no, Publisher, Year, Price)
Collection (Title, Author, Catalog_no) 
with in the following functional dependencies:
I. Title, Author --> Catalog_no
II. Catalog_no --> Title, Author, Publisher, Year
III. Publisher, Title, Year --> Price  

Assume {Author, Title} is the key for both schemes 
  • The table \"Collection\" is in BCNF as there is only one functional dependency “Title Author –> Catalog_no” and {Author, Title} is key for collection.
  • Book is not in BCNF because Catalog_no is not a key and there is a functional dependency “Catalog_no –> Title Author Publisher Year”.
  • Book is not in 3NF because non-prime attributes (Publisher Year) are transitively dependent on key [Title, Author].
  • Book is in 2NF because every non-prime attribute of the table is either dependent on the whole of a candidate key [Title, Author], or on another non prime attribute. In table book, candidate keys are {Title, Author} and {Catalog_no}. In table Book, non-prime attributes (attributes that do not occur in any candidate key) are Publisher, Year and Prince
Please refer Database Normalization | Normal Forms for details of normal forms.
Question 7

Consider the relation scheme R = {E, F, G, H, I, J, K, L, M, N} and the set of functional dependencies {{E, F} -> {G}, {F} -> {I, J}, {E, H} -> {K, L}, K -> {M}, L -> {N} on R. What is the key for R?

Cross

{E, F}

Tick

{E, F, H}

Cross

{E, F, H, K, L}

Cross

{E}



Question 7-Explanation: 

All attributes can be derived from {E, F, H} 
To solve these kind of questions that are frequently asked in GATE paper, try to solve it by using shortcuts so that enough amount of time can be saved. 

First Method: 
Using the given options try to obtain closure of each options. The solution is the one that contains R and also minimal Super Key, i.e Candidate Key.

A) {EF}+ = {EFGIJ} ≠ R(The given relation)

B) {EFH}+ = {EFGHIJKLMN} = R (Correct since each member of the 
                                    given relation is determined)

C) {EFHKL}+ = {EFGHIJKLMN} = R (Not correct although each member 
                                of the given relation can be determined 
                                but it is not minimal, since by the definition
                                of Candidate key it should be minimal Super Key)

 D) {E}+ = {E} ≠ R

Second Method:

Since, {EFGHIJKLMN}+ =  {EFGHIJKLMN}

{EFGHIJKLM}+ =  {EFGHIJKLMN} ( Since L -> {N}, hence can replace N by L)

In a similar way K -> {M} hence replace M by K

{EFGHIJKL}+ =  {EFGHIJKLMN} 

Again {EFGHIJ}+ =  {EFGHIJKLMN} (Since  {E, H} -> {K, L}, hence replace KL by EH)

{EFGH}+ =  {EFGHIJKLMN} (Since {F} -> {I, J} )

{EFH}+ =  {EFGHIJKLMN} (Since {E, F} -> {G} )

This explanation is contributed by Manish Rai. 

Learn more here: 
Finding Attribute Closure and Candidate Keys using Functional Dependencies

Question 8
Given the following two statements:
  S1: Every table with two single-valued 
      attributes is in 1NF, 2NF, 3NF and BCNF.

  S2: AB->C, D->E, E->C is a minimal cover for 
      the set of functional dependencies 
      AB->C, D->E, AB->E, E->C. 
Which one of the following is CORRECT?
Tick
S1 is TRUE and S2 is FALSE.
Cross
Both S1 and S2 are TRUE.
Cross
S1 is FALSE and S2 is TRUE.
Cross
Both S1 and S2 are FALSE.


Question 8-Explanation: 
 
S1: Every table with two single-valued 
      attributes is in 1NF, 2NF, 3NF and BCNF.
A relational schema R is in BCNF iff in Every non-trivial Functional Dependency X->Y, X is Super Key. If we can prove the relation is in BCNF then by default it would be in 1NF, 2NF, 3NF also. Let R(AB) be a two attribute relation, then
  1. If {A->B} exists then BCNF since {A}+ = AB = R
  2. If {B->A} exists then BCNF since {B}+ = AB = R
  3. If {A->B,B->A} exists then BCNF since A and B both are Super Key now.
  4. If {No non trivial Functional Dependency} then default BCNF.
Hence it\'s proved that a Relation with two single - valued attributes is in BCNF hence its also in 1NF, 2NF, 3NF. Hence S1 is true.
S2: AB->C, D->E, E->C is a minimal cover for 
      the set of functional dependencies 
      AB->C, D->E, AB->E, E->C.
As we know Minimal Cover is the process of eliminating redundant Functional Dependencies and Extraneous attributes in Functional Dependency Set. So each dependency of F = {AB->C, D->E, AB->E, E->C} should be implied in minimal cover. As we can see AB->E is not covered in minimal cover since {AB}+ = ABC in the given cover {AB->C, D->E, E->C} Hence, S2 is false. This explanation has been contributed by Manish Rai. Learn more about Normal forms here: Database Normalization | Introduction Database Normalization | Normal Forms
Question 9
The maximum number of superkeys for the relation schema R(E,F,G,H) with E as the key is
Cross
5
Cross
6
Cross
7
Tick
8


Question 9-Explanation: 
Maximum no. of possible superkeys for a table with n attributes = 2^(n-1) Here, n = 4. So, the possible superkeys = 24-1 = 8 The possible superkeys are : E, EH, EG, EF, EGH, EFH, EFG, EFGH
Question 10

Given the STUDENTS relation as shown below. 

 

For (StudentName, StudentAge) to be the key for this instance, the value X should not be equal to

Cross

18

Tick

19

Cross

17

Cross

20



Question 10-Explanation: 

There is already an entry with same name and age as 19. So the age of this entry must be something other than 19.

There are 89 questions to complete.

  • Last Updated : 09 Oct, 2019

Share your thoughts in the comments
Similar Reads