إعلانيمكن أن تكون علامتك التجارية هناعلامتك التجارية هنافي كل صفحة من Darslab · طلاب وأولياء أمور ومعلمونinfo@darslab.comاسأل عن الأسعارالأسعارinfo@darslab.com
☰ خطة العام · تكنولوجيا المعلومات والاتصالات (بالإنجليزية)

Relationships between tables

الأسبوع 6 · الوحدة ٢ «متطلبات ومراحل إنتاج المشروع» (٣) مرحلة إنشاء جداول قاعدة البيانات (٢): العلاقات بين الجداول: واحد إلى واحد، واحد إلى متعدد، متعدد إلى متعدد، المفتاح الأجنبي والمفتاح المركب · 3 دروس
2

One to many: the foreign key

ICT Secondary 2 Term 1, Unit 2, Topic 2: pp. 45–53
GoalLink two tables one to many with a foreign key and trace the records by hand.
New words
one-to-many · one record of the first table links to many records of the second, but each of those links to only oneforeign key · a field in the many table that holds the primary key of the one tablecontrolled redundancy · repeating a key value on purpose, in the foreign key, so that records can be linkedmany side · the table that holds the foreign key and many matching records
Explanation

The most common relationship is one to many. One team has many players, but each player is in only one team. To link them, copy the primary key of the one table into the many table. Here team_id of the teams table is copied into the players table, where it is called a foreign key, because it holds the primary key of another table. In the players table the values of team_id may repeat: three players may all have 1. This repeating is on purpose and is called controlled redundancy; it is not a mistake, because it is only a short number, not the team’s whole data. The primary key of the teams table is still never repeated. To use the link, match values: to find the players of team 2, look for 2 in the foreign key column; to find the team of player 5, read his team_id and look it up in the teams table.

team_id (PK)team|player_id (PK)playerteam_id (FK)
1Hawks|1Sara1
2Eagles|2Ali1
3Owls|3Nour2
|4Adam1
|5Dina3
|6Youssef2
Worked examples
Trace 1: how many players has team 2? Look for 2 in the column team_id of the players table: players 3 and 6. So team 2 (Eagles) has two players: Nour and Youssef.
Trace 2: which team is player 5 in? Player 5 is Dina and her team_id is 3. Look up 3 in the teams table: Owls. A player has only one team_id, so the answer is only one team.
Class activity

A club has many members and each member lives in one district. On paper draw a districts table and a members table. Mark the primary keys and the foreign key, write five members, and trace “who lives in district 2?” step by step.

Practice
1
In one team has many players, which table is the “many” table?
2
Where is the foreign key placed in a one-to-many link?
3
Why is it fine that team_id repeats in the players table?
4
Can the primary key team_id repeat in the teams table?