LU06 - Assignment: Animal Shelter on the Ron

• Working method: Pair work (same group as in LU05)
• Resources: Your own database from LU05.
• Time: approx. 30 minutes

We would like to help animals at the Ron Animal Shelter in Root (LU) that are up for adoption (tierschutz-luzern.ch) in our database.

Your tables: enclosures, animal species, animal.

animal-type (columns: name, origin)

Hund, Weltweit domestiziert
 
Katze, Weltweit domestiziert
 
Kaninchen, Europa

enclosure (columns: name, location, area)

Hundeaussenbereich 1, Garten, 45.50
 
Kleintierraum, Innenbereich, 20.00
 
Katzenzimmer, Innenbereich, 15.00

Animal (Columns: name, date_of_birth, gender, enclosure_id, species_id)

('Zuki', '2024-04-01', 'w', 2, 3),
('Sokka', '2024-04-01', 'm', 2, 3),
('Mila', '2019-09-03', 'w', 3, 2),
('Bäry', '2022-01-20', 'm', 1, 1),
('Luigi', '2025-09-01', 'm', 1, 1),
('Fred', '2025-11-01', 'm', 1, 1);

Try to create an animal without a name (name is NOT NULL). Read the error message.

1. An input error has been detected for Mila: she is actually housed in a different enclosure – change her enclosure_id to 2.
2. Luigi and Fred are being moved together to a new enclosure and their species is being corrected, as they are actually rabbits rather than dogs – change the ‘gehege_id’ for both to 2 and `art_id` to 3. To do this, use a `WHERE` clause that matches both at the same time.

1. Bäry has been successfully rehomed – delete his record specifically by name.
2. Zuki and Sokka are being rehomed together – delete both records using a WHERE condition that matches both at the same time.

Remember to carry out a dry run with SELECT before every DELETE.

Guido Koch