☰ Contents · Computing

The library database and queries

Lessons 28–29 · 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
28

Practical lesson

Textbook: p. 62
GoalDesigns a small library database in four tables, links them and expresses a many-to-many relationship through a junction table.
New words
junction table: a table that links two tables in a many-to-many relationship · oraliq jadvalmany-to-many relationship: many records of one table match many of another · ko‘p-ko‘p aloqasidesign: planning tables and fields before entering data · loyihafield: one column of a table, one property of a record · maydon
Explanation

In the practical lesson you build a small school-library database. First plan the tables and fields in your notebook: “Authors” (AuthorID, AuthorName), “Books” (BookID, Title, AuthorID), “Readers” (ReaderID, ReaderName, Class) and “Loans” (LoanID, BookID, ReaderID, DateOut). One reader can borrow many books, and one book can be read by many readers at different times: this is a many-to-many relationship, which Access does not store directly. So “Loans” becomes a junction table and splits it into two one-to-many relationships: Books – Loans and Readers – Loans. Then build each table in Design View, mark the keys, link all four tables in the Relationships window and enter 5–6 invented records; no real personal data is entered.

Worked examples
Authors – Books: one author writes many books (one-to-many). Readers – Loans and Books – Loans are also one-to-many. Three relationship lines are drawn in all.
Invented Loans records: reader 1 borrowed 3 times, reader 2 borrowed 4 times and reader 3 borrowed 2 times. Loans has 3+4+2 = 9 records, because each borrowing is a separate row.
Class activity

“The librarian”: a pair builds the four-table database, enters 6 records in “Loans” and uses a query to show which reader borrowed which book.

Practice
1
Which table links “Books” and “Readers”?
2
Loans records 2+5+1 borrowings. How many records is that?
3
Why can a many-to-many relationship not be stored without a junction table?
4
In the “Books” table, what kind of key is AuthorID?