A candidate key is:
Any set of attributes which:
a) uniquely identify table instances (tuples)
b) no longer has property a) if any attribute is removed from
the key.
Primary key: the candidate key selected for unique identification. For example, Student_id is the primary key of the STUDENT relation.
This guarantees the rows are unique as required.
Primary keys are often shown by underlining them. This means that primary keys must be:
• Minimal, e.g., (Student_id, Name) is not acceptable as
Student_id alone is sufficient.
• Time-independent, i.e., not subject to change once specified.
• Made up of attributes which have non-null values. A null value would not allow an individual row to be identified and might be
repeated in the table.
Foreign key:
It is important that tables be linked together, so the information can be assembled. Thus, the Student table over the page refers to the department the student is in via Dept_code. This attribute links the Student table to the Department table. Such a linking attribute (or set of attributes) is called a foreign key. Thus, foreign keys represent relationships between tables.
Foreign key:
A foreign key is a set of attributes in a relation R2 which have the same domain as the primary key attributes in another relation R1.
Each value of the foreign key which occurs in R2 must also occur in R1, unless the value of all the attributes in the foreign key are NULL.
Note however that it is better to avoid NULL foreign keys if possible, as they cause complications. What do you do, for example, if a student has a NULL Dept_code? Of course it may not be possible to avoid at times – a new student may join without having been given a department.
Foreign keys are sometimes marked by an asterisk*, or by underlining them with a dotted line, but they are not always marked at all.
Notice that whilst a foreign key can be NULL, a foreign key should not point to a non-existent primary key. Thus, if a student is in Dept 3158, table Department must have an entry for Dept_code = 3158. Otherwise the situation is meaningless. This existence condition is called "referential integrity", (i.e. if you refer to something, the dB must maintain its integrity by ensuring the something is there).
Access will stop many invalid actions and warn of dangerous ones. For example, if you try to delete a tuple and hence a primary key, Access will check other relations using this as a foreign key, to make sure the primary key is not referenced.
Note the foreign key → primary relation is not a symmetric relation.
Foreign key Dept_code in Student points to a unique department in Department. Dept_code in Department points to any number of students in Student.
Note the foreign key in relation A(...) can also be part of the primary key of A(...). In this case, the foreign keys are generally in the relation with
the larger number of attributes in its primary key: BOOK(B#, title, ...) LOAN(B#*, M#, ...) member borrows book
The existence of an entry in the LOAN table implies that the book referred to exists, so the foreign key is in LOAN.
However, the presence of a book in the BOOK table doesn’t imply that there will necessarily be a corresponding entry in the LOAD table.
Composite key:
A key with more than one attribute is called a composite key.
I would strongly encourage you to go through the excellent course (free) here. It explains complete Database design process in detail.