AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Year plan · Computing

Term 2 in review

Week 21 · Review: Term 2 · 3 lessons
3

Tables, queries and networks

Computing 9: Topics 1.8–3.1, pp. 135–272
GoalReview keys and queries in a database and the star, bus and ring topologies, and use them to answer new questions.
New words
foreign key · a field in one table that holds the primary key of a record in another table, so the tables are linkedquery · a question asked of a database: which fields to show, which criteria to meet and how to sortbackbone · the main cable of a bus topology, to which all the devices are joinedsingle point of failure · a part that, if it breaks, stops the whole network
Explanation

A relational database splits its data into tables. Each table has a primary key that identifies every record, and a table that points to another holds that key as a foreign key. A query chooses the fields to show, the criteria that records must meet and the order of the results. Criteria in the same row must all be true (AND), criteria in different rows are alternatives (OR), and a wildcard stands for any characters. A network topology is the layout of the devices. In a star, each device is joined to a switch that sends every packet only to the device with the matching IP address. On a bus, all the devices share one backbone and every packet reaches every device, and in a ring a packet passes from device to device in one direction. Each layout has a weak point, such as the switch, the backbone or a broken link in the ring, so designers compare cost, speed and reliability.

TopologyHow a packet travelsWeak point
starto the switch, then only to the matching devicethe switch
busalong the backbone to every devicea break in the backbone
ringfrom device to device in one directiona break anywhere in the loop
Worked examples
Hive_IDKeeper_IDTypeHoney_kg
H1K1Top-bar22
H2K1Frame14
H3K2Frame31
H4K3Top-bar18
H5K3Frame25
H6K2Top-bar9
Two tables and a sorted query. The Keepers table (Keeper_ID K1 Hana, K2 Tomas, K3 Mira) is linked to the Hives table by the foreign key Keeper_ID. A query shows Name and Honey_kg for hives with Honey_kg > 20, sorted from the highest. Three hives qualify: H3 (31 kg, Tomas), H5 (25 kg, Mira) and H1 (22 kg, Hana), and that is the order of the result.
CriteriaRecords foundCount
Top-bar AND Honey_kg >= 15 (same row)H1, H42
Top-bar OR Honey_kg >= 30 (different rows)H1, H3, H4, H64
AND or OR? With Type = "Top-bar" and Honey_kg >= 15 in the same row, a hive must meet both, so the result is H1 and H4, two records. With Type = "Top-bar" in one row and Honey_kg >= 30 in the next row, a hive needs to meet only one, so the result is H1, H4 and H6 (top-bar) and H3 (31 kg): four records.
TopologyWho handles the packet (scanner to stock PC)Who ignores or never sees it
ring (till, scanner, printer, office PC, stock PC, server)printer and office PC pass it on; stock PC keeps ittill and server never see it
busall five other devices receive a copy; stock PC keeps ittill, printer, office PC and server discard it
starthe switch passes it to the stock PC onlyno other device receives it
Following a packet. A hardware shop has six devices. In the ring the packet goes from the scanner to the stock PC in the order given, so it passes through the printer and the office PC on the way. On a bus every other device gets a copy and only the stock PC keeps it. In a star the switch forwards it to the stock PC alone.
Class activity

Layout race (no computer). Six players are six devices, each with a name tag. In round 1 stand in a ring and pass a note in one direction, with each player writing their tag on it as it passes. In round 2 stand in a star, with one player as the switch. In round 3 stand in a line as a bus, where the sender calls the address aloud and all the players hear it. In each round count who touched or heard the note. Then one player ‘breaks’ (leaves the game): who can no longer send a note in each round?

Practice
1
The Keepers table has the keepers K1, K2 and K3. Name the primary key and the foreign key of the Hives table, and say which record is an orphan.
Hive_IDKeeper_IDTypeHoney_kg
H1K9Frame16
H2K2Top-bar28
H3K1Top-bar11
H4K3Frame27
2
A query has Type = "Frame" and Honey_kg >= 20 in the same Criteria row. Which hives are found? Then a different query has Type = "Top-bar" in one row and Honey_kg >= 30 in the next row. Which hives are found?
Hive_IDKeeper_IDTypeHoney_kg
H1K2Frame12
H2K1Top-bar27
H3K3Frame33
H4K2Frame20
H5K1Frame29
H6K3Top-bar15
3
The six devices of a ring are joined as the picture shows. A packet goes from the till to the stock PC in the direction till, scanner, printer, office PC, stock PC. Which devices pass it on?
tillscannerprinteroffice PCstock PCserver
4
A small shop wants a network in which one broken cable cuts off only one device. Which topology should it choose, and what single part could still stop the whole network?