- Practice
- About 12 min
- 5 practice questions
Key identification drills: candidate, primary, alternate and foreign
Pick out candidate, primary, alternate, composite and foreign keys from table descriptions and count degree, cardinality and superkeys.
If something looks wrong, please tell us.
Keys are among the most reliably tested ideas in database papers. The questions are short, so every term must be exact. This lesson gives a one-line meaning for each, then drills that mix them.
One-line meanings
| Term | Meaning |
|---|---|
| Candidate key | A column or set of columns that could uniquely identify each row (minimal) |
| Primary key | The candidate key chosen to identify rows; unique and never NULL |
| Alternate key | A candidate key that was not chosen as the primary key |
| Composite key | A key made of two or more columns together |
| Foreign key | A column that refers to the primary key of another table |
| Superkey | Any set of columns that uniquely identifies rows; it may contain extra columns |
| Degree | Number of columns (attributes) |
| Cardinality | Number of rows (tuples) |
Drills with full working
Drill 1. TEACHER(TID, Name, Email, Phone, SubjectCode). TID and Email are both unique for every teacher. Name is not unique. Identify the keys. Candidate keys: TID and Email. If TID is chosen as primary, Email is the alternate key. SubjectCode, if it refers to a SUBJECT table, is a foreign key. The degree is 5.
Drill 2. ENROL(StudentID, CourseID, Grade). A student can join many courses, and a course has many students, but one student joins a course once. Which key is needed? Neither StudentID nor CourseID alone is unique, but the pair is. The primary key is the composite key (StudentID, CourseID). Each of the two is also a foreign key pointing to the STUDENT and COURSE tables.
Drill 3. A table has 40 rows and 6 columns. Its degree is 6 and its cardinality is 40. If 5 more rows are inserted and 1 column is added, the degree becomes 7 and the cardinality 45.
Drill 4 (counting superkeys). Table R(A, B, C) has only one candidate key, A. The superkeys are all sets that contain A: {A}, {A, B}, {A, C}, {A, B, C}. That is 4, which is 2 to the power 2, since each of the other two columns may be included or not.
Drill 5. Now R(A, B, C) has two candidate keys, A and B. A set is a superkey if it contains A or B. All subsets of three columns number 2 to the power 3 = 8. Those with neither A nor B are the empty set and {C}, which is 2. So the superkeys number 8 - 2 = 6: {A}, {B}, {A,B}, {A,C}, {B,C}, {A,B,C}.
Drill 6. Which of these is the weakest choice for a primary key of a PERSON table: a unique registration number, Name, or an AutoNumber ID? Name, because two persons can share a name. The system-generated ID is usually the best because it is unique and never changes.
A primary key is unique and never NULL. Foreign keys may repeat and may be NULL when the rule allows it. Degree counts columns; cardinality counts rows.
Typical traps
- Thinking a table can have several primary keys. It has exactly one; others are alternate keys.
- Thinking a foreign key must be unique in its own table. It usually repeats.
- Mixing degree with cardinality.
- Choosing a changing value, such as an address, as a key.
Key takeaways
- Candidate keys compete for the primary key; the rest are alternate keys.
- A composite key uses several columns together.
- A foreign key links to the primary key of another table.
Check your understanding
-
1. A table has 12 rows and 5 columns. Its cardinality is
- 5
- 17
- 12
- 60
Show answer
Answer: C. Cardinality means the number of rows, which is 12. The degree is 5.
-
2. A candidate key not chosen as primary key is called
- Foreign key
- Alternate key
- Composite key
- Super table
Show answer
Answer: B. Candidate keys that are not made primary are alternate keys.
-
3. In ENROL(StudentID, CourseID, Grade), the primary key is
- StudentID, taken alone
- Grade, taken alone
- CourseID, taken alone
- StudentID with CourseID
Show answer
Answer: D. Only the combination is unique for each row, so a composite key is needed.
-
4. Table R(A, B, C) has A as its only candidate key. How many superkeys does it have?
- 4
- 1
- 2
- 8
Show answer
Answer: A. A superkey must contain A; B and C are each optional, giving 2 x 2 = 4 sets.
-
5. A foreign key
- Must stay unique in its table
- Is always left as NULL
- Is the same as the degree
- Points to another table's key
Show answer
Answer: D. A foreign key stores values that match the primary key of a related table.
Sign in to keep track of the lessons you finish.