Keys

Keys identify tuples and link relations.

• Candidate key: an attribute (or set) whose values are distinct for every tuple, so it can identify each one. In GUARDIAN, both GUID and GPhone are candidate keys.
• Primary key: the candidate key the designer chooses to identify tuples (GUID in GUARDIAN). A primary key value can never be NULL and never repeats.
• Alternate key: a candidate key not chosen as the primary key (GPhone).

Composite primary key. Sometimes no single attribute is unique. In ATTENDANCE, a roll number appears once for every date, and a date appears once for every student, but each pair appears only once. So {RollNumber, AttendanceDate} together form a composite primary key.

Foreign key. An attribute whose values come from the primary key of another relation is a foreign key; it is how relations are linked.

• STUDENT.GUID refers to GUARDIAN.GUID.
• ATTENDANCE.RollNumber refers to STUDENT.RollNumber.
• A foreign key value must match an existing primary key value in the referenced (primary or master) relation, or be NULL. It can be NULL only if it is not part of its own table's primary key (so ATTENDANCE.RollNumber cannot be NULL).
• In a schema diagram, a foreign key is drawn as an arrow from it to the primary key it refers to.

Example (new): in a hospital database, PATIENT has PatientID as its primary key; APPOINTMENT has {PatientID, AppointmentDate} as a composite primary key, and its PatientID is also a foreign key pointing to PATIENT, so no appointment can be booked for a patient who does not exist.