Lessons 26–27 · 2 lessons · N. I. Taylaqov (general editor), A. B. Akhmedov, M. D. Pardayeva, A. A. Abdug‘aniyev, U. M. Mirsanov. Informatics and Information Technologies, Grade 10, 1st edition. “Extremum-Press”, Tashkent, 2017
27
Linking tables in MS Access
Textbook: pp. 56–61
GoalLinks tables through a primary and a foreign key, sets up a one-to-many relationship and builds one result table from the linked tables with a query.
New words
foreign key: a field that holds the primary key value of another table · tashqi kalitone-to-many relationship: one record of one table matches many records of another · bir-ko‘p aloqasiRelationships: the window where tables are linked · Relationshipsreferential integrity: a rule that forbids a link to a record that does not exist · ma’lumotlar yaxlitligi
Explanation
In a real database the data does not fit in one table: if clubs are in one table and students in another, the club name is not repeated in every student row. Tables are linked through a common field: in the table on the “one” side it is the primary key, in the table on the “many” side it is a foreign key. To link them, open Database Tools – Relationships, add the tables from the Show Table window and drag the primary key onto the foreign key; in the Edit Relationships window, ticking “Enforce Referential Integrity” forbids a reference to a club that does not exist. To get one table from the linked tables, add the tables in Create – Query Design, drag the fields into the grid and press Run. The resulting query does not change the original tables; it only shows their combined view.
💡 Where will I use this?
In real life
In a city bus-route database the stop name is not retyped on every row: the stop sits once in its own table and the routes refer to its number. If a stop is renamed, one correction updates every route.
Who uses it
Delivery services, school administration and the programmers of online platforms work with linked tables to avoid repetition.
Try it today
Write “Place 3” on several cards and the name of Place 3 once on a separate card; when you change that name, see that all the cards stay correct.
Worked examples
“Clubs” (ClubID – key, ClubName) is the “one” side; “Students” (StudentID – key, FullName, ClubID) is the “many” side. Students.ClubID is the foreign key. One club can have several students, but each student belongs to one club.
The clubs have 5, 4 and 3 students. If a query combines Students and Clubs, the result has 5+4+3 = 12 rows; each row shows the student’s name and the club’s name.
Class activity
“The database schema”: a pair links three tables (Clubs, Students, Marks) in the Relationships window, builds a query and shows the student, club and mark columns.
Practice
1
On which tab is the window for linking tables?
Database Tools
2
The clubs have 6, 2 and 4 students. How many rows does the combined query have?
12
3
In the “Clubs – Students” link, which table has the primary key and which the foreign key?
Clubs has the primary key (ClubID), Students has the foreign key (ClubID).
4
Why is linking tables better than repeating the club name in every student row?
Repetition takes space and causes mistakes; if the name is changed in one place, it is right everywhere.