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.
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.