Relational databases study guide

Build the model step by step

When analysing a relational database, first identify the tables and the records represented by their rows. Then identify the attributes represented by their columns. For example, a database could separate student details from enrolment details:

Student(StudentID, Name, Year)

Enrolment(StudentID, CourseID)

The first table stores attributes about students. The second stores an association between a student and a course. Keeping these as separate tables allows the student details and enrolment details to be identified separately while still being connected.

Choosing a primary key

Ask whether one attribute can identify every record uniquely. If StudentID is different for every student, it is a suitable primary key for Student. Name should not be selected merely because it appears descriptive: two records might have the same name, so a name is not necessarily unique.

A strong exam explanation should state both the key and the reason it works. For example: “StudentID is the primary key because it uniquely identifies each student record.” Do not just list StudentID without explaining its identifying role.

Recognising a composite primary key

Consider the records in an Enrolment table:

| StudentID | CourseID | |---|---| | 101 | CS01 | | 101 | MA02 | | 102 | CS01 |

StudentID is repeated because one student can have multiple enrolments. CourseID is repeated because one course can have multiple students. Therefore, neither attribute alone identifies a row. The pair (StudentID, CourseID) distinguishes the three rows, so it can be used as a composite primary key. The reasoning depends on the combination, not on either component in isolation.

Tracing a foreign key

If StudentID is the primary key in Student, the StudentID attribute in Enrolment can be a foreign key referring to it. To explain this fully, name both roles: it identifies a student in Student and provides the link from an enrolment record to that student record. A value such as 101 in Enrolment.StudentID refers to the record whose StudentID is 101 in Student.

Exam application checklist

For a definition question, include the essential relationship:

• Attribute: a named data item or column describing a record.
• Primary key: an attribute or set of attributes that uniquely identifies a record in its table.
• Composite primary key: a primary key formed from more than one attribute.
• Foreign key: an attribute in one table that refers to a primary key in another table.

For a scenario question, identify the table, candidate attributes and whether uniqueness applies to one attribute or a combination. Then explain the link created by any foreign key. Keep the terms distinct: a primary key identifies records in its own table, whereas a foreign key refers to a key in another table.

Self-check questions

1. What is the difference between a record and an attribute?
2. Why might an identifier be a better primary key than a person's name?
3. Why can neither StudentID nor CourseID alone be the primary key of the example Enrolment table?
4. Which attribute connects Enrolment to Student, and what key does it refer to?
5. Explain why (StudentID, CourseID) is a composite primary key rather than a single-attribute primary key.

A complete answer should use precise terminology and justify each choice using uniqueness or the connection between tables.