Database design, keys and relationships
Design one- and two-table relational databases using appropriate fields, records, types, validation, primary keys and foreign keys.
Mapped to the Cambridge IGCSE Computer Science 0265 syllabus for exams in 2029. These original explanations and self-checks are independent study support, not Cambridge papers, official marks or grades. Practical programming is taught with worked code and trace tasks; an executable Python workspace and timed digital-exam simulation are not yet available.
Before you begin
- Use the 2029 syllabus for this course. Cambridge specifies Python 3.10 or higher for programming and digital exams for both components. The syllabus currently identifies this route for 2029 only; confirm later-year availability with Cambridge and your school.
Work on paper. Try each question before revealing help, and explain why your method works.
Quick readiness check
Before database design, keys and relationships, which statement is the safest starting point?
What you will learn
- Define fields, records and validation from storage requirements
- Choose data types and a suitable primary key
- Link tables with foreign keys and joins
Tables organise records into fields
A database table contains records (rows) and fields (columns). Each field stores one kind of information. Choose a data type that matches its meaning: text, character, Boolean, integer, decimal, date, time or currency. Validation limits entries to sensible values or formats.
A primary key uniquely identifies each record and should not be blank or duplicated. A foreign key stores a value that matches a primary key in another table, creating a relationship. Joining tables combines related records by matching those key values.
Separate repeated information
A two-table design can avoid repeating details. For example, a learner table can store each learner once, while a result table stores scores and a LearnerID foreign key. A join connects each result to the correct learner.
Choose fields from stated requirements and make keys reliable. Do not use a changing attribute such as a person's name as a key when a stable identifier is available. Validation improves consistency but cannot replace access controls or backups.
Pause and explain
Which habit best checks your understanding of database design, keys and relationships?
Worked example
A school stores learners and exam results. Suggest a primary key in each table and the linking field.
Show the worked solution
- Learners need a unique identifier, such as LearnerID.
- Each result needs its own unique ResultID.
- The result table stores LearnerID as a foreign key to link to the learner.
Answer Learner.LearnerID (primary key); Result.ResultID (primary key); Result.LearnerID (foreign key).
Try a topic test
Choose Easy, Normal or Difficult. Save your answers and open worked review after finishing.
Test this topic →From guided practice to a new situation
Write your answer and reasoning first. Use a hint only if you are stuck. The model solution is for self-checking; your written work is not automatically marked.
Design fields for a single table of books with a unique identifier, title, publication date and price. Suggest types and a key.
Give me a hint
Match each field to the kind of value it stores.
Compare my reasoning
- BookID: integer or text identifier, primary key.
- Title: text; PublicationDate: date; Price: currency or decimal.
- Choose validation such as a positive price and required title.
Book(BookID PK, Title text, PublicationDate date, Price currency/decimal).
Look for these in your work
- Fields match requirements
- Types fit meaning
- Key and validation
A results table contains StudentID and SubjectCode. Explain which could be a foreign key and why the pair may be useful.
Give me a hint
Relate the value to a primary key and consider whether one learner can have multiple subjects.
Compare my reasoning
- StudentID can be a foreign key to Learner.StudentID.
- SubjectCode may link to a Subject table's primary key.
- Together they can identify a learner's subject result, subject to the database's uniqueness rules.
Both can be foreign keys; together they can link each result to a learner and a subject.
Look for these in your work
- Referenced table
- Relationship purpose
- Composite uniqueness considered
Explain how a join combines learner names with their stored scores without repeating the learner's name in every result record.
Give me a hint
Match the foreign key in Results to the primary key in Learners.
Compare my reasoning
- Learners stores LearnerID and Name; Results stores LearnerID and Score.
- The join matches Results.LearnerID to Learners.LearnerID.
- The output can show Name with Score while the name remains stored once.
Join on LearnerID to display each learner's name alongside related scores.
Look for these in your work
- Tables identified
- Matching keys stated
- Benefit explained
Common mistakes
- Using a non-unique name as the only primary key.
- Treating a foreign key as a duplicate primary key for that same table.
- Using a data type that cannot represent the required values.
What can you explain now?
What is the main purpose of a primary key?
Compare with the explanation
Uniquely identify each record
A primary key distinguishes one row from every other row in its table.
After trying it yourself, choose your next review. This is your self-assessment.
Your review choice appears on Today. Sign in to sync it across devices.
Make it stick
How do fields, records, data types and keys make a relational database reliable and joinable?
If you needed the solution, close it and solve the problem again from a blank page. A correct answer is stronger when you can explain the reason for each step.