Discuss the candidate key, primary key, super key, composite key, foreign key and alternate key with example suppose relation r(a,b,c,d,e) has functional dependencies: ab c d a ae b cd e be d find all the candidate keys of r.
Answers
The value of Candidate Key is unique and non-null for every tuple.
There can be more than one candidate key in a relation. For Example, STUD_NO as well as STUD_PHONE both are candidate keys for relation STUDENT.
The candidate key can be simple (having only one attribute) or composite as well. For Example, {STUD_NO, COURSE_NO} is a composite candidate key for relation STUDENT_COURSE.
Note – In Sql Server a unique constraint that has a nullable column, allows the value ‘null‘ in that column only once. That’s why STUD_PHONE attribute as candidate here, but can not be ‘null’ values in primary key attribute.
Super Key: The set of attributes which can uniquely identify a tuple is known as Super Key. For Example, STUD_NO, (STUD_NO, STUD_NAME) etc.
Adding zero or more attributes to candidate key generates super key.
A candidate key is a super key but vice versa is not true.
Primary Key: There can be more than one candidate key in a relation out of which one can be chosen as primary key. For Example, STUD_NO as well as STUD_PHONE both are candidate keys for relation STUDENT but STUD_NO can be chosen as primary key (only one out of many candidate keys).
Alternate Key: The candidate key other than primary key is called as alternate key. For Example, STUD_NO as well as STUD_PHONE both are candidate keys for relation STUDENT but STUD_PHONE will be alternate key (only one out of many candidate keys).
Foreign Key: If an attribute can only take the values which are present as values of some other attribute, it will be foreign key to the attribute to which it refers. The relation which is being referenced is called referenced relation and corresponding attribute is called referenced attribute and the relation which refers to referenced relation is called referencing relation and corresponding attribute is called referencing attribute. Referenced attribute of referenced relation should be primary key for it. For Example, STUD_NO in STUDENT_COURSE is a foreign key to STUD_NO in STUDENT relation.
It may be worth noting that unlike, Primary Key of any given relation, Foreign Key can be NULL as well as may contain duplicate tuples i.e. it need not follow uniqueness constraint.
For Example, STUD_NO in STUDENT_COURSE relation is not unique. It has been repeated for the first and third tuple. However, the STUD_NO in STUDENT relation is a primary key and it needs to be always unique and it cannot be null.
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above
Recommended Posts:
DBMS | Anomalies in Relational Model
DBMS | Relational Model Introduction and Codd Rules
Finding Attribute Closure and Candidate Keys using Functional Dependencies
Mapping from ER Model to Relational Model
Relational Model
DBMS | Tupple Relational Calculus
DBMS | Domain Relational Calculus
DBMS | How to solve Relational Algebra problems for GATE
DBMS | Enhanced ER Model
DBMS | Attributes to Relationships in ER Model
DBMS | ER Model: Generalization, Specialization and Aggregation
Primary key Vs Unique key
DBMS | Advantages of DBMS over File system
Extended Operators in Relational Algebra
Basic Operators in Relational Algebra