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

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.