تكنولوجيا المعلومات والاتصالات (بالإنجليزية) الصف الأول الثانوي، الأسبوع 8: Choosing rows, choosing columns, joining tables

Databases

الأسبوع 8 · الوحدة ٩ «قواعد البيانات»: لماذا نستخدم قاعدة بيانات، الجداول والسجلات والحقول، نماذج القواعد، العمليات العلائقية، نظم المعلومات · 3 دروس

Choosing rows, choosing columns, joining tables

ICT Secondary 1 Term 1, Chapter 9: pp. 111–122

GoalUse selection, projection and join on small tables and write the result table.

New words

selection · choosing the rows that meet a conditionprojection · choosing some of the columnsjoin · linking two tables through a field they sharecondition · a test that a row either meets or does not

Explanation

Three operations answer almost every question about tables. Selection keeps the rows that meet a condition and all their columns. From the Books table, the condition Topic = Science keeps the rows for B2 and B3. Projection keeps only some columns and all the rows, for example only Name and Class of the Members. Join links two tables through the field they share. The Loans table has MemberID and BookID, which are keys of the other tables, so joining them gives readable answers: LoanID L1 has MemberID M1 (Hana) and BookID B2 (Maps and Stars). Questions often need two or three operations in turn. “Which members borrowed a Science book?” First join the tables. Then select the rows where Topic is Science. Then project the Name column. The result is Hana, Omar and Karim (Hana borrowed two Science books but we list a name once). No special software language is needed to think this way: work with the small tables on paper, one step at a time.

Selection: Topic = Science

BookID	Title	Topic
B2	Maps and Stars	Science
B3	Desert Animals	Science

Worked examples

Projection: Name, Class

Name	Class
Hana	1A
Omar	1B
Layla	1A
Karim	1C

Projection on the Members table keeps the columns Name and Class (and drops MemberID). All four rows stay.

Join of Loans, Members and Books

LoanID	Name	Title
L1	Hana	Maps and Stars
L2	Omar	Desert Animals
L3	Hana	Desert Animals
L4	Layla	The Little Boat
L5	Karim	Maps and Stars

Join: the Loans table gives only keys; joining with Members and Books turns them into names and titles. The result has five rows, one per loan.

Class activity

Use the three tables on paper: list the names of members who borrowed “Maps and Stars”. Write which operations you used, in order, and the result. Then invent one more loan row and say what changes in the join result.

Practice

Selection with the condition Topic = Hobby on the Books table. Which rows remain?

Which operation chooses columns?

Using the join table, who borrowed “Desert Animals”?

Why does the Loans table store MemberID instead of the member’s name?