Design each table around one entity, assign a primary key that uniquely identifies every record, and use foreign keys to create relationships between tables. Use a composite key when uniqueness requires a combination of two or more attributes.
The Design Process
First, identify the entities required by the system, such as Student, Course, and Enrollment. Each entity normally becomes a table, while its characteristics become fields.
A primary key must be unique and cannot be null. For example, StudentID is preferable to StudentName because different students may have the same name, while names can also change.
A foreign key is a field that refers to a primary key in another table. It establishes a relationship and supports referential integrity, meaning that a foreign-key value must normally match an existing referenced record.
| Table | Key design | Purpose |
|---|---|---|
| Student | StudentID is the primary key | Uniquely identifies each student |
| Course | CourseID is the primary key | Uniquely identifies each course |
| Enrollment | StudentID and CourseID form a composite primary key | Records which students take which courses |
In Enrollment, StudentID is also a foreign key referencing Student, while CourseID is a foreign key referencing Course. Together, they form the composite key (StudentID, CourseID), preventing the same student from being enrolled in the same course twice.
This junction table resolves the many-to-many relationship between students and courses. One student can take many courses, and one course can contain many students.
A common misconception is that every table needs a single-field primary key. A primary key may contain multiple fields when their combined values uniquely identify a record.
Exam Technique
For an IB Computer Science A3.2 database design question, label every primary key and foreign key explicitly, show which table each foreign key references, and justify the composite key using uniqueness. Do not claim that each field within a composite key must be unique independently only the complete combination must be unique.
