AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Year plan · Communication Technology

Relationships between tables

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)issuedstatus
1Mona|12024active
2Omar|22025paused
3Hana|32024active
4Karim|42025active
Worked examples
QuestionAnswer
Card of member 2card 2
Status of that cardpaused
Year that card was issued2025
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.
teamcoach_id (FK)repeats allowed?type of relation
Hawks1noone to one
Owls2noone to one
Hawks and Owls2 and 2yesone 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?
2
Must the two joined key fields have the same name? The same data type?
3
Define a foreign key in your own words.
4
When is a foreign key link one to one and not one to many?