Week 6 · Unit 2 Requirements and Production Stages of the Project (3), Create database tables stage (2): relationships between tables: one to one, one to many, many to many, foreign and composite keys · 3 lessons
1
One to one: two tables, one shared key
ICT Secondary 2 Term 1, Unit 2, Topic 2: pp. 45–53
GoalJoin two tables one to one by their primary keys, and say when a foreign key makes a one-to-one link.
New words
types of relationships · the ways two tables can be linked: one to one, one to many, many to manyone-to-one · a record of the first table joins at most one record of the second, and the other way roundjoin field · the field in each table that holds the matching valuesforeign key · a field that holds the primary key of another table
Explanation
Tables are linked by relationships. The first type is one to one: a record of the first table joins at most one record of the second, and a record of the second joins at most one of the first. A member has one library card, and a card belongs to one member. To join the tables, you link the primary key fields. The two fields must have the same data type and the same size, but they may have different names: here member_no and card_id. The database matches records by value: member 2 goes with card 2, even if the rows appear in a different order. A second way makes a one-to-one link with a foreign key, a field that holds the primary key of another table. Suppose each team has one coach and the teams table keeps coach_id. That is one to one only if coach_id is set to forbid repeats. Otherwise one coach could lead many teams, and the link would be one to many.
member_no (PK)
name
|
card_id (PK)
issued
status
1
Mona
|
1
2024
active
2
Omar
|
2
2025
paused
3
Hana
|
3
2024
active
4
Karim
|
4
2025
active
Worked examples
Question
Answer
Card of member 2
card 2
Status of that card
paused
Year that card was issued
2025
Read the picture above. Omar is member 2, so his card is card 2: issued in 2025, status paused. Even if the cards table listed card 4 first, the match is by the value of the key, so the answer would not change.
team
coach_id (FK)
repeats allowed?
type of relation
Hawks
1
no
one to one
Owls
2
no
one to one
Hawks and Owls
2 and 2
yes
one to many
A teams table keeps coach_id as a foreign key. With repeats forbidden, each coach can appear once, so the link is one to one. If repeats were allowed, coach 2 could appear for both Hawks and Owls, which is one to many.
Class activity
Think of two things that are one to one in a school (for example a pupil and his or her locker). Draw two small tables on paper, with a primary key in each, and say which fields you join and why they need the same type and size.
Practice
1
A member has one card and a card belongs to one member. What type of relationship is this?
One to one.
2
Must the two joined key fields have the same name? The same data type?
The name may differ; the data type and size must be the same.
3
Define a foreign key in your own words.
A field that holds the primary key of another table.
4
When is a foreign key link one to one and not one to many?
When the foreign key field is set to forbid repeated values.