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.
Topology
How a packet travels
Weak point
star
to the switch, then only to the matching device
the switch
bus
along the backbone to every device
a break in the backbone
ring
from device to device in one direction
a break anywhere in the loop
Worked examples
Hive_ID
Keeper_ID
Type
Honey_kg
H1
K1
Top-bar
22
H2
K1
Frame
14
H3
K2
Frame
31
H4
K3
Top-bar
18
H5
K3
Frame
25
H6
K2
Top-bar
9
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.
Criteria
Records found
Count
Top-bar AND Honey_kg >= 15 (same row)
H1, H4
2
Top-bar OR Honey_kg >= 30 (different rows)
H1, H3, H4, H6
4
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.
Topology
Who 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 it
till and server never see it
bus
all five other devices receive a copy; stock PC keeps it
till, printer, office PC and server discard it
star
the switch passes it to the stock PC only
no 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_ID
Keeper_ID
Type
Honey_kg
H1
K9
Frame
16
H2
K2
Top-bar
28
H3
K1
Top-bar
11
H4
K3
Frame
27
The primary key is Hive_ID and the foreign key is Keeper_ID. The record H1 is an orphan, because Keeper_ID K9 does not exist in the Keepers table.
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_ID
Keeper_ID
Type
Honey_kg
H1
K2
Frame
12
H2
K1
Top-bar
27
H3
K3
Frame
33
H4
K2
Frame
20
H5
K1
Frame
29
H6
K3
Top-bar
15
First query (both must be true): H3, H4 and H5. Second query (either is enough): H2 and H6 are top-bar, and H3 has 33 kg, so H2, H3 and H6.
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?
The scanner, the printer and the office PC pass it on, one after another, because they lie between the till and the stock PC in the direction of the ring. The server never sees it.
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?
A star, because each device has its own cable to the switch. The switch is the single point of failure: if it breaks, no device can communicate.