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

Relationships between tables

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

Many to many: a joining table

ICT Secondary 2 Term 1, Unit 2, Topic 2: pp. 45–53
GoalBreak a many-to-many relationship into two one-to-many links with a joining table.
New words
many-to-many · a record of each table may match many records of the otherjoining table · a table that holds the primary keys of the two tables, one pair per rowcomposite key · a primary key made of two or more fields taken togetherpair · one combination of a player number and an activity number
Explanation

A player may join several activities, and an activity has many players. Neither table can hold this link in one field: a single activity field in the players table cannot hold three activities, and a single player field in the activities table cannot hold many players. Some books say a many-to-many link cannot be put in a database program. It can, with one more table, a joining table. The joining table holds two fields, player_id and activity_id, and each row says “this player joins this activity”. The two fields together form the composite primary key: the same pair may never appear twice, although each number alone may repeat. The many-to-many link is now two one-to-many links: one player has many rows in the joining table, and one activity has many rows in it.

Worked examples
player_idactivity_idmeaning
11Sara – Chess
12Sara – Football
22Ali – Football
31Nour – Chess
33Nour – Drawing
42Adam – Football
Here is the joining table. The pair (1, 2) means Sara joins Football. The pair (3, 1) means Nour joins Chess. The pair (1, 2) cannot appear a second time.
QuestionRows foundAnswer
who joins activity 2?players 1, 2, 4Sara, Ali, Adam
activities of player 3?activities 1, 3Chess, Drawing
Trace with the joining table above. Who joins Football (activity 2)? Rows with 2 in activity_id: players 1, 2 and 4, so Sara, Ali and Adam. Which activities does Nour (player 3) join? Rows with 3 in player_id: activities 1 and 3, so Chess and Drawing.
Class activity

Students and school subjects are many to many. On paper draw a students table (3 students), a subjects table (3 subjects) and the joining table with at least five pairs. Mark the composite key and check that no pair repeats.

Practice
1
Why can one field not store a many-to-many link?
2
What does a joining table hold?
3
What is the composite primary key of the joining table, and what does it forbid?
4
Into which two relationships is a many-to-many link broken?