LU06 – Assignment: Public Transport
Prerequisites
- Method of work: Group work (same group as in LU05)
- Resources: Your own database from LU05.
- Time: approx. 30 minutes
Background
We would like to enter, amend and delete VBZ tram routes from Zurich in our database.
Your tables: linie, haltestelle, fahrt.
Task
1. Insert data records
linie (columns: name, betreiber)
Linie 4, VBZ Linie 11, VBZ Linie 8, VBZ
haltestelle (columns: name, ort)
Bellevue, Zürich Rehalp, Zürich Tiefenbrunnen, Zürich Klusplatz, Zürich Zoo, Zürich
fahrt (Columns: datum, abfahrtszeit, preis, linien_id)
('2026-09-22', '07:15:00', 4.60, 1), ('2026-09-22', '07:45:00', 4.60, 1), ('2026-09-22', '08:05:00', 4.60, 2), ('2026-09-22', '08:20:00', 4.60, 3), ('2026-09-22', '08:40:00', 4.60, 2);
2. Deliberately breaking the constraint
Try to add a route without a name. Read the error message.
3. Modify data
- The price for all journeys on line 4 increases to 4.80 – update using WHERE linien_id.
- One journey on line 8 is rescheduled: date and departure time are changing – select a specific journey (e.g. by combining date and departure time) and change both columns in a single query.
First, use SELECT to check how many rows are affected by your WHERE condition – this is particularly important if you’re filtering by linien_id, as this may affect several journeys at once.
4. Deleting data
- A single journey is cancelled – delete it specifically (e.g. by combining date and departure time).
- All journeys on route 11 will be rescheduled – delete all journeys with ‘linien_id’ 2 in one go.
Remember to carry out a dry run using SELECT before every DELETE.
Guido Koch