GATE CSE 2003 | Question: 85

Question

Consider the following functional dependencies in a database.$$\begin{array}{|l|l|}\hline \text{Date_of_Birth } \to \text{Age} & \text{Age } \to \text{Eligibility} \\\hline  \text{Name } \to \text{Roll_number} & \text{Roll_number }\to \text{Name} \\\hline   \text{Course_number }\to \text{Course_name} & \text{Course_number } \to\text{Instructor} \\\hline  \text{(Roll_number, Course_number) }\to\text{Grade} & \text{}  \\\hline \end{array}$$The relation (Roll_number, Name, Date_of_birth, Age) is

• A. in second normal form but not in third normal form
• B. in third normal form but not in BCNF
• C. in BCNF
• D. in none of the above

Comments

 R (Roll_number, Name, Date_of_birth, Age) , in given realtion we dont have  Course_number and only course number can derive course_Number . So any FD containing Course_number is not eligible for R (Roll_number, Name, Date_of_birth, Age) .

So leftover FDs are used 

---

for the relation (R, N, D, A)  CK = { DN, DR }   and   FDs are = { D -> A   |   N -> R   |   R -> N }
so it is not in BCNF (Lhs  in FDs are not super keys  , not in 3NF (either Lhs are not super keys or Rhs is non-prime attriute),  not in 2NF (Proper subset of candidate keys does not derive prime attribte for the FD,   D -> A) 
therefore , it is in 1NF

---

NO option is matching the relation in 1NF

bexz DOB--->age is partial dependency So not in 2NF

option D is correct

Answer 1

There are three FDs that are valid from the above set of FDs for the given relation :

1. Date_of_Birth $\to$ Age
2. Name $\to$ Roll_number
3. Roll_number $\to$ Name

Candidate keys for the above are : (Date_of_Birth, Name) and (Date_of_Birth, Roll_number)

Clearly there is partial dependency here (Date_of_Birth $\to$ Age) and Age is not a prime attribute. So, it is in 1NF only.

Option (D).

Comments

Why bcnf is not

---

@Jony Bhatt  When it is not in 2NF then how it can be in bcnf

---

(Date_of_Birth $\rightarrow$ Age) is NOT a Partial Dependency. It is a Full Dependency. 

(DOB.RN $\rightarrow$ Age) is a Partial Dependency. Since a candidate key $(DOB.RN)$ partially determines a Non-prime attribute $(Age)$, So, this relation is Not in 2NF.

Correct Definition of Partial Dependency: https://www.youtube.com/watch?v=HdaHz0iY2ws&t=569s 

Answer 2

Here, Date of Birth which is a part of the candidate key derives a non-prime attribute, i.e. Part of CK --> Non-Prime attribute.

Therefore PARTIAL DEPENDENCY, not in 2NF.

Comments

(Date_of_Birth $\rightarrow$ Age) is NOT a Partial Dependency. It is a Full Functional Dependency. 

(DOB.RN $\rightarrow$ Age) is a Partial Dependency. Since a candidate key $(DOB.RN)$ partially determines a Non-prime attribute $(Age)$, So, this relation is Not in 2NF.

Correct Definition of Partial Dependency: https://www.youtube.com/watch?v=HdaHz0iY2ws&t=569s 

Answer 3

there is partial dependency hence it is 1st normal form

Comments

yes

 

Answer 4

BEST ANSWER

FD Given

D ---> A

A ----> E

N ----> R

R ----> N

C -----> c

C ----> I

RC -----> G

Relation Given (RNDA)

Candidate keys for the above are : (D, N) and (D, R)       

And Non prime attributes are: A

In (Date_of_Birth → Age)

D is the proper subset of candidate key 

A is the non prime attribute 

2NF Voilation condition :    Proper subset of CK ----> Non prime          

so 2NF not holding 

if 2NF not holding then 3NF , BCNF ALSO NOT HOLD 

So it is satsfying 1NF 

Conidtion for 1NF ( ALL ATTRIBUTES ARE ATOMIC)

Answer 5

in this the higher form possible in this 1 nf and in this partial dependency not allowed in the 2 nf  so it is not 2nf if the function not in 2 nf then it is also not a 3 nf hence not in 3 nf

Answer 6

Answer option D (NONE OF THE ABOVE)

Given, relation(roll number,name,dob,age)

We will get only 3 FD
1 Dob->age
2 name->roll number
3 roll number -> name

From FD set we get (date of birth,name) or (date of birth,roll number) as candidate keys

Therefore date of birth, name, roll number are prime attributes and age is non prime attribute.

From FD set subset of prime attributes date of birth is infering non prime attribute age. Which is voilation of 2nf.

So ,Even 2nf fails. So 3nf and bcnf also fails.

Therefore answer option D