Dbms practice - Self doubt

Question

Consider the below instance of relation : 

Employee: 

Emp_rating	Emp_name	Emp_mail	Emp_sal
1.	Surabhi	sm301@gj	40000
2.	Vishu	sp11@ap	60000
1.	Surabhi	Null	50000
3.	Yashy	ar@dy	60000

 

The maximum possible number of alternate keys for the above relational instance is/are _____

Answer 1

There are 2⁴ possibilities of making a CK

out of which mail-id attribute is always ignored as it contain a null and it will violate the entity integrity constraint

So (Mail-id),(Mail-id,_),(mail-id,,)and (mail-id,,,__)

always ignored 1+3+3+1=8

 

We need the check the remaining 8

for the potential cks

 

Rating has aa repeated value

Name has a repeated value

Sal has repeated value

Rating,Name has repeated value

Rating,Salary has no repeated values so it's a possible ck

Name,Salary has no repeated values it's a possible CK

Name,Rating,Salary has no repeated values so it's a possible ck

 

But we said we will have 8 possibilities to check...we did for 7...the last one is ∅ so it's not a CK

 

Hence the given relation will have 3 possible cks,and max no of alternate keys will be 2

Comments

Name,Salary    ⊂   Name,Rating,Salary .

A proper subset of a candidate key cannot be a candidate key. 

Answer should be 1.

---

(Emp_rating, Emp_sal) and (Emp_name, Emp_sal) are candidate keys.

(Emp_name, Emp_rating, Emp_sal) is a superkey but not a candidate key since it is not minimal.

Hence, the number of candidate keys is 2.

Therefore, the maximum number of alternate keys is 1.

Answer 2

2^4 = 16 possibilites

Emp_mail = NULL cannot be in a candidate key so
16 - 1 + 3 + 3 + 1 = 8
Repating tuples ! so the attributes alone cannote be a key
8 - 3 = 5
Emp_rating, Emp_name together also has a repeating tuple (1, Surabhi)
5 - 1 = 4
Empty Set
4 - 1 = 3
After taking 1 primary key we are left with 2 alternate keys
Answer - 2