In a school database, how does the computer know which student borrowed which library book, without typing the student’s name and address again on every loan? The answer is keys and relationships. This post fills in the database terms you must know – the different keys and data types – and shows how to create one-to-one and one-to-many relationships in Microsoft Access.
Build on what you learned in Forms 1–3
These Lower School ICT posts cover the basics of this topic:
- Data and Information – the data a database stores
- Spreadsheets: Introduction, definition & terminology – rows and columns of data, like a table
In this post:
- A quick reminder: tables, records and fields
- Primary, candidate, secondary (alternate) and foreign keys
- Data types
- Relationships: one-to-one and one-to-many
- Creating a relationship in Access, step by step
A quick reminder
A database is an organised collection of related data. Data is stored in tables; each row is a record (one student) and each column is a field (Surname, Date of birth). For the basics see Introduction To Databases and Database Terminology in Context.
Keys
| Key | Meaning | Example (Students table) |
|---|---|---|
| Candidate key | Any field (or fields) that could uniquely identify each record | StudentID, NationalID, Email – each is unique |
| Primary key | The candidate key chosen to uniquely identify each record. It must be unique and cannot be empty | StudentID |
| Secondary (alternate) key | A candidate key that was not chosen as the primary key; or a field used to search and sort (often not unique) | NationalID, Email (alternate); Surname (secondary, for searching) |
| Foreign key | A field in one table that is the primary key of another table; it links the tables | StudentID in the Loans table |
Tip: a composite key is a primary key made of two or more fields together, e.g. StudentID + BookID + LoanDate.
Video: Primary & Foreign Keys – https://www.youtube.com/watch?v=B5r8CcTUs5Y
Data types
| Data type (Access) | Stores | Example field |
|---|---|---|
| Short Text | Letters, symbols and numbers not used in calculations (up to 255 characters) | Name, Phone number, StudentID like “S0123” |
| Long Text | Long text | Comments, Address notes |
| Number | Numbers used in calculations | Quantity, Age |
| Currency | Money values | Price, Fees |
| Date/Time | Dates and times | DateOfBirth, LoanDate |
| Yes/No (Boolean) | Only two values | Paid? Returned? |
| AutoNumber | A unique number added automatically to each new record | LoanID |
| OLE Object / Attachment | Pictures and files | Student photo |
Why does a phone number use Short Text? It is not used in calculations, and it may start with 0 or contain +, – or spaces.
Video: Setting Up Data Types in Microsoft Access – https://www.youtube.com/watch?v=MAu7lbjl2CM
Relationships
- One-to-one (1:1): each record in table A matches at most one record in table B. Example: each Student has one Locker; each Locker belongs to one Student.
- One-to-many (1:M): one record in table A matches many records in table B. Example: one Student can have many Loans; each Loan belongs to one Student. The primary key on the “one” side (Students.StudentID) appears as a foreign key on the “many” side (Loans.StudentID).
- Relationships avoid data redundancy (typing the same details again and again) and keep data consistent.
| Students (one) | Loans (many) | |
|---|---|---|
| StudentID (PK) | 1 ──── ∞ | LoanID (PK) |
| FirstName | StudentID (FK) | |
| Surname | BookID (FK) | |
| Form | LoanDate |
Step by step: create a one-to-many relationship in Access
In Microsoft Access:
- Create the Students table in Design View: add the fields and data types, select StudentID and click Primary Key. Save.
- Create the Loans table with LoanID (AutoNumber, primary key) and a StudentID field of the same data type and size as Students.StudentID. Save and close both tables.
- Database Tools → Relationships. Add both tables.
- Drag StudentID from Students onto StudentID in Loans.
- In the Edit Relationships box, tick Enforce Referential Integrity (so you cannot add a loan for a student who does not exist), then Create.
- Access shows a line marked 1 and ∞. Save the relationships layout.
Video: MS Access Tutorial – Lesson 6 – Creating Relations with Primary Key and Foreign Key – https://www.youtube.com/watch?v=IG1RwVVgFqI
If both linked fields are primary keys (or unique), Access creates a one-to-one relationship instead. LibreOffice Base: Tools → Relationships works the same way.
Next, practise entering and using the data: Creating Forms in Microsoft Access, Creating Queries – multiple criteria, calculated fields and totals and Database reports.
Try it: Name the key or data type
Choose the correct term for each description.
| StudentID, chosen to uniquely identify each student | |
| StudentID stored in the Loans table to link to Students | |
| Email – unique, but not chosen as the primary key | |
| The best data type for a Fees field | |
| The best data type for a “Book returned?” field | |
| The best data type for a phone number |
Related
Related: Modifying An Existing Database, Understanding Cascade Delete
Related: Importing Data From Excel into An Access Database
Related: Real World Database Development Example – Inventory (recorded class, Part 1)
Paper 1 practice (multiple choice)
Choose the best answer for each question. Try them all before you check.
1. A field that uniquely identifies each record in a table is the
(A) foreign key
(B) primary key
(C) secondary key
(D) index
2. A primary key from one table that appears in another table to link them is a
(A) candidate key
(B) foreign key
(C) composite key
(D) record
3. Which data type is MOST suitable for a telephone number?
(A) Number
(B) Currency
(C) Short Text
(D) Yes/No
4. One customer can place many orders. This is a
(A) one-to-one relationship
(B) one-to-many relationship
(C) many-to-one table
(D) composite key
5. Fields that could each be used as the primary key are called
(A) candidate keys
(B) foreign keys
(C) secondary tables
(D) data types
Show answers
1. B
2. B
3. C
4. B
5. A
Paper 2 practice (structured question)
A community library in Linstead stores data in two tables: MEMBERS (MemberID, Name, Phone, DateJoined) and BORROWINGS (BorrowID, MemberID, BookTitle, DateBorrowed, Returned).
(a) Identify:
(i) the primary key of MEMBERS (1 mark)
(ii) the foreign key in BORROWINGS (1 mark)
(b) State the relationship between MEMBERS and BORROWINGS and justify your answer. (2 marks)
(c) Suggest a suitable data type for EACH field:
(i) DateJoined (1 mark)
(ii) Returned (1 mark)
(d) Explain why Phone should NOT be the Number data type. (2 marks)
Total: 8 marks
Show answers
(a)
(i) MemberID (1 mark)
(ii) MemberID (1 mark)
(b) One-to-many (1) – one member can borrow many books, but each borrowing belongs to only one member (1).
(c)
(i) Date/Time (1 mark)
(ii) Yes/No (Boolean) (1 mark)
(d) Phone numbers are not used in calculations (1), and may begin with 0 or contain symbols such as +, – or spaces, which a Number field would remove/reject (1).
Next: Steps in Problem-Solving and the Divide-and-Conquer Approach
Syllabus link: This post is relevant to the CSEC® Information Technology Syllabus (CXC 30/G/SYLL 17), Section 6: Database Management, Specific Objective 2 (“use terminology commonly associated with a database”); Specific Objective 3 (“create a database”).
Attributions to media used in this post:
No third-party images are used in this post. Embedded videos are credited on YouTube to their channels.
© 2026 Vedesh Kungebeharry. All rights reserved.