Test: Concept of keys - 1 - Computer Science Engineering (CSE) MCQ

• A foreign key is a field in a table that is a primary key in another table.
• It is used to establish a relationship between two tables.
• The foreign key ensures referential integrity of the data, meaning it only allows values that are present in the primary key column of the referenced table.

To determine which of the given options is a key for the relation (R), we need to use the given functional dependencies to see which combination of attributes functionally determines all attributes in (R).

Given functional dependencies:

C → F, E → A, EC → D, A → B
Also, remember that we can use the augmentation rule, transitivity, and the union rule to find other implied dependencies.

Let's analyze each option:

Option 1) (CD)

• Given C → F, so (CD) determines (F).
• However, there's no direct dependency that allows (CD) to determine (A), (B), (E), or even (D) itself without additional attributes. (CD) does not functionally determine all attributes based on the provided dependencies.

Option 2) (EC)

• E→ A means (EC) determines (A).
• A → B implies that with (A), (EC) also determines (B).
• C → F implies that (EC) also determines (F).
• Additionally, EC → D, so (EC) also determines (D).
• Given that (EC) determines (A), (B), (C), (D), (E), and (F), all attributes in (R).

Option 3) (AE)

• Using (E→ A), (AE) still brings us back to (A). But, given (A), we can determine (B) since (A → B).
• However, without (C) or another combination, we cannot derive (D), (F) directly from (AE) based on the given dependencies.

Option 4) (AC)

• C→ F, so (AC) determines (F).
• A → B so (AC) determines (B).
• However, (AC) does not directly determine (E) or (D) based on the given dependencies. There's EC → D, but (A) doesn't help in determining (D) or (E).

Conclusion:
Only option 2 ((EC)) is capable of determining all attributes in (R), making it the key for the relation scheme based on the given functional dependencies.

Function Dependencies:
{(E, F} → {G}, {F} → {I, J}, {E, H} → {K, L}, {K} → {M}, {L} → {N}}

Option 1: {E, F}
{E, F}+ = {E, F, G, I, J}
Since K, L, M and N is missing in RHS ∴ it is not a key
Also, {E} cannot be a key because {E} is subset of {E, F}

Option 2: {E, F, H}
{E, F, H}+ = {E, F, H, G, I, J, K, L, M, N}
∴ it is a key
Key for R is {E, F, H}.

Important Points:
In relation algebra, key is primary key or candidate key.
{E, F, H, K, L} is super key. 

• Attributes of the relation which exist in at least one of the possible candidate keys, are called prime or key attributes
• Candidate key is a minimal super key and a Super key is a set of attributes that uniquely identify a tuple in a relation.
• Therefore, a prime attribute of a relation scheme R is an attribute that appears in some candidate key of R

If closure defined the complete relation then become the candidate key.

• Closure of AB =  (AB)+ = {A,B,C,D} //candidate key
• Closure of BC =  (BC)+ = {A,B,C,D}  //candidate key
• Closure of CD = (CD)+ = {C,D,A} 
• Closure of DB = (DB)+= {B,C,D,A} //candidate key
• Closure of ABCD = (ABCD)+= {A,B,C,D}   //super key
• Closure of AD = (AD)+ = {A,B,D}

The candidate keys are AB. BC, BD

Concept:
Candidate key: it is the set of attributes which uniquely identifies a relation. It is also known as superkey with no repeated attributes.

Given relation R = {A, B, C, D, E}
A ➝ B
B, C ➝ D
E ➝ C
D ➝ A

Find the closure of keys given. If all the attributes of relation are present in the closure, then it will be the candidate key of that relation.

Option1: AEF, BEF and DEF
(AEF)+ = {A, E, F, B, C, D}
(BEF)+ = {B, E, F, C, D, A}
(DEF)+ = {D, E, F, C, A, B}
These are the candidate keys of given relation.

Option2: AEF, BEF and BCF
(AEF)+ = {A, E, F, B, C, D}
(BEF)+ = {B, E, F, C, D, A}
(BCF)+ = {B, C, F, D, A}  // E is not present in the closure. Not the candidate key

Option3: AE and BE
(AE)+ = {A, E, C, B, D, }  //F is not present, not a candidate key
(BE)+ = {B, E, C, D, A}   //F is not present, not a candidate key

Option4: AE, BE and DE
(AE)+ = {A, E, C, B, D, } //F is not present, not a candidate key
(BE)+ = {B, E, C, D, A}  //F is not present, not a candidate key
(DE)+ = {D, E, A, C, B}   //F is not present, not a candidate key

Concept:-
The candidate key can be called a super key, as each candidate key is a subset of the super key. The super key with all necessary attributes is known as the candidate key. The super key with unnecessary attributes cannot be considered a candidate key.

Key Points

• A Candidate key is a minimal super key, meaning that it would cease to be a super key if you removed any attribute from the set.
• A minimum super key is referred to as a candidate and the main key since the primary key is chosen from the candidate keys.
• The minimal set of attributes that can uniquely identify a tuple is known as candidate key. For example, STUD_NO in STUDENT relation. It is a minimal super key.

Concept: 
Foreign Key :is the set of attributes in a particular relation whose values are belongs to primary key of same relation or other relation.

Statement 1: A relation scheme can have at most one foreign key.
There is no such restriction on how many number of Foreign keys a particular relation can have. A relation can have as many number of Foreign keys as Required. 
So this statement is false.

Statement 2: foreign key in a relation scheme R cannot be used to refer to tuples of R.
There is no such constraint. Foreign key can be used to refer to primary key of the same relation. Self-referencing relations are examples of such foreign key. So this statement is also false.
So option 3 is the correct answer.

Concept:
Superkey is a set of attributes within a table whose values can be used to uniquely identify a tuple. A candidate key is a minimal superkey.
Superkey is superset of candidate key or primary key.

Explanation:
Primary key is VY. (given)
All superkeys must contain this primary key VY. From the given keys, key, which doesn’t contain the VY.
Here, option 2: VWXZ
“VWXZ” doesn’t contain the primary key VY. So, it is not a superkey.

Super Key

• It is an attribute (or set of attributes) that is used to uniquely identifies all attributes in a relation. 
• All super keys can’t be candidate keys but its reverse is true. 
• There can be more than one super key.
• In relation, the number of super keys is always greater than or equal to the number of candidate keys.
• There always exists at least one super key in a table.

Candidate key

• It is a minimal set of attributes necessary to identify a tuple; this is also called a minimal super key.
• Candidate key can be more than one.
• One of the candidate keys is designated as the primary key.

Primary key

• Candidate key from the table selected by the database administrator to uniquely identify tuples in a table known as the primary key.
• Since the candidate is a minimal set of attributes necessary to identify a tuple therefore the primary key is also  a minimal set of attributes necessary to identify a tuple and hence primary key cannot be obtained by removing one or more attributes from a candidate key.

Therefore option 4 is false