ISRO-DEC2017-15

Question

Consider the following table: $\text{Faculty(facName, dept, office, rank, dateHired)}$
 

facName	dept	office	rank	dateHired
Ravi	Art	A101	Professor	1975
Murali	Math	M201	Assistant	2000
Narayanan	Art	A101	Associate	1992
Lakshmi	Math	M201	Professor	1982
Mohan	CSC	C101	Professor	1980
Sreeni	Math	M203	Associate	1990
Tanuja	CSC	C101	Instructor	2001
Ganesh	CSC	C105	Associate	1995

(Assume that no faculty member within a single department has same name. Each faculty mimber has only one office identified in $office$).$3NF$ refers to third normal form and $BCNF$ refers to Boyee-Codd Normal Form

Then $Faculty$ is

• A. Not in 3NF,in BCNF
• B. In 3NF,not in BCNF
• C. In 3NF, in BCNF
• D. Not in 3NF, not in BCNF

Comments

I think it's d) but given key is b).

---

No faculty member within a single department has same name = facName , dept-> office, rank, dateHired

Each faculty member has only one office identified in office = facName -> office

which is not in 2nf ( i am no sure)

---

I think answer must be D)

Because here is in 2nd FD there is a case of Partial Dependency since Faculty,Department is the candidate key according to the question infer in first dependency.

correct me if Im wrong.

---

Each faculty member has only one office identified in office.

How is facName → office an FD here?
Say, Prof. John of Dept Math has office M101
Say, Prof. John of Dept Art has office A101
Then?
John → ?? 
Note-  No faculty member within a single department has the same name
Which means the faculty of different departments can have the same name.

One FD should be - facName, dept → office, rank, dateHired
What about other FDs .? I am not able to get.

---

How far I understand

facName, dept → office, rank, dateHired is one fd

facName, office → dept, rank, dateHired is another fd

So, here prime attribute are factname, office,dept

but rank and dateHired are not prime attribute

So, it is in 3NF(because there is no partial dependency and no transitive dependency)

But it should be BCNF too, right?

---

srestha
Along with the above 2 FDs you have mentioned if we assume this FD-
 office → dept
Then it will be in 3NF and not in BCNF

---

yes there is a dependency certainly

But, both of them are prime attribute and part of primary key

So, those cannot be a problem to create a relation to BCNF

I think it is not BCNF because only reason is rank and dateHired

those are not prime attribute as per the question and also dependency are unknown for them

(though if u notice keenly, u can see rank and dateHired is not depend on any other attribute. So, Can we not say rank and dateHired are also unique key and can also form primary key?)

---

Why office → dept can't be a problem for the relation to be in BCNF. 
A relational schema R is in Boyce–Codd normal form if and only 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.
  Source - Boyce Codd Normal Form 

---

So, why here X cannot be Superkey ?

---

?
In office → dept,  office is not super key so it's problematic for BCNF.

---

office is a candidate key

right?

And any subset of candidate key is a super key

office will be super key too

---

Not at all... !!!
You are telling just opposite.

• office is a prime attribute(Part of a candidate key).
• And Candidate key is a subset of the super key. But not all subsets of  super key is a candidate key

---

yes u r right

https://gateoverflow.in/211810/normalization-prime-attribute-prime-attribute-dependency
thanks :)

---

srestha
So the Conclusion is - 
There are 3 FDs - 
1.facName, dept → office, rank, dateHired 
2.facName, office → dept, rank, dateHired
3.office → dept
And the relation is in 3NF but not in BCNF. 
Ryt ? :)

---

yes ..

---

@srestha   @Soumya29

I am seriously confused about one thing here, why everyone is deducing the FD's based on the instance given.

FD's are based on the schema and not on a particular instance of that schema.

eg : 3.office → dept  here.

---

why dependency not depend on instance on schema
U have any example, where it is not depending on (in normalization) ?

how far I know. it depends on instance

---

@VS...
Yes....You are right ...
FD's are based on the schema and not on a particular instance of that schema.

But Official Answer Key says that relation is not in BCNF.
and based on the information given in the question -
Only these 2 FD's can be inferred.

1.facName, dept → office, rank, dateHired  
2.facName, office → dept, rank, dateHired

But with these 2 FD's only, Relation satisfies BCNF condition too.

So office → dept is the best assumption here.
Note - It's completely wrong to infer anything based on an instance of any relation, but as it's ISRO's question, we don't have any choice left. 

---

@Soumya29

ISRO questions :/

---

VS
Exactly  :/

---

Definition of FD from Navathe

"A FD denoted by $X\rightarrow Y$ , between two sets of attributes $X$ and $Y$ that are subsets of $R$  specifies a constraint on the possible tuples that can form a relation state $r$ of $R$ . The constraint is that, for any two tuples $t_{1}$ and $t_{2}$ in $R$ . The constraint is that, for any two tuples $t_{1}$ and $t_{2}$  in $r$ that have $t_{1}\left [ X \right ]=t_{2}\left [ X \right ]$ , they must also have $t_{1}\left [ Y\right ]=t_{2}\left [ Y\right ]$"

Here X and Y are instance of tuples $t_{1}$ and $t_{2}$

So, why both of u r telling it is not depending on instanstances

right na?

---

srestha ...No. You misunderstood the term instance.
An instance of a relation schema is the set of tuples (or data) the relation contains at a particular instant of time.
Instance changes with time like when you add a row or delete one or update some row. 

---

@Soumya

I still not got u

here schemas are dept and office

and what is instance ?

---

dept rank-> office   

if we assume this fd then relation would be in BCNF.

Answer 1

Answer is b.

facName->dept,office,rank,datehired.

office->dept.

Here in facName is primary key, so 3NF. But office is not a super key, so not in BCNF.

Comments

Here facName is enough to determine all the attributes. So, no need to take (facName,Dept). Further, if you have taken (facName,Dept), then it is a super key. So, just take facName as a candidate key and there will be no partial dependency.

---

@Aditya Vikram Sinha

no faculty member within a single department has same name.

suppose there are two person named $Ram$ one in $Maths$ dept and other in $Arts$ dept.

So if you give give input as $ram$ then how can you determine about the $ram$ of maths dept ?

 

---

yes @Satbir

I also think the same [Fact_name, dept] can be the primary key then.

Answer 2

C

Regardless of what is given in official key,

 The only FD here is facName , dept-> office, rank, dateHired

facName, office → dept, rank, dateHired  is not an FD. Where in question does it say that FacName and office can together identify other attributes? It just says "Each faculty mimber has only one office identified in", two different faculty from two different departments with same facName can work in the same office. Just because in this instance of the relation we see office -> department FD does not mean such an FD exists for all instances.

Since there is only 1 FD which is the primary key, the relation is in 3NF and BCNF

Answer 3

Usually, we're given FDs and we have to find the Normalization level.

Here, we have to derive the FDs and then find the Normalization Level based on it.

For simplicity, I'm renaming the attributes as $A$, $B$, $C$, $D$, $E$

• FDs are derived by schema, not by an instance of the table.

---

Let's derive FDs by Schema (Requirement Analysis)

Assume that no faculty member within a single department has same name.

$A,B\rightarrow A,B,C,D,E$ (A,B is Candidate Key)

Each faculty member has only one office identified in office

$A \rightarrow C$

So, Not even 2NF.

Option D

---

Now, let's derive FDs by Relation instance (which is wrong)

$A \rightarrow A,B,C,D,E$

$E \rightarrow A,B,C,D,E$

$C,D\rightarrow A,B,C,D,E$

$C\rightarrow B$ (violates BCNF)

$B,D\rightarrow A,B,C,D,E$

So, 3NF but not BCNF.

Option B

---

Option B is the official answer, though.

Answer 4

Ohkay, so as far as I've understood, some previous discussions above also helped me in deducing the solution, but I don't to what extent it is correct.

" Assume that no faculty member within a single department has same name"

So I guess, it says facName and dept. will give us unqiue tuples.

"Each faculty member has only one office identified in office"

So, this must also be similar to above condition i.e. facName and dept. together will give us unique tuples.

So now we have following functional dependencies:

Facname,dept--> office, rank, hireddate

Facname,office--> dept., rank, hireddate

So as per that CANDIDATE KEY: FacName, dept, office

So both the functional dependecies have prime attributes on LHS nad non prime attributes on RHS.

Hence, this should not even satisfy 2NF.

Pl. Correct ke if I'm wrong.