Database Lab/Datenbankpraktikum - Exercises
Exercises
- conceptual design (17.9.2025)
- logical design (24.9.2025)
- Consistency constraints (1.10.2025)
- Queries
- Exercises (8./15.10.2025)
- No lab on October 22 and 29 - use time for query exercises and self-studies!
- Performance (05.11.2025)
- Transactions (12.11.2025)
- Views (19.11.2025)
- User-defined functions and stored procedures (26.11.2025)
- Trigger (03.12.2025)
- Extensions and Security (10.12.2025)
- final presentations and closing (17.12.2025)
Student presentations are usually in the week following their topic
Exercises, solutions, and other material will be published in OLAT.
Conceptual Design
The first exercise is to develop a conceptual model representing the entities of a carsharing organization. A German description of the system is here and an English translation here.
Logical Design
In the second lesson, we map the conceptual design onto a logical design. In addition to mapping entity types and relationships, please pay particular attention to the following cases:
- how to implement specialization hierarchies;
- how to deal with attributes whose type is again structured (such as addresses);
- how to implement static attributes;
- how to handle domains (and whether to use them at all);
- how to handle collection-valued types (such as extras in Vehicle).
The Postgres manual about DDL can be found here (sections 5.1 and 5.10). In particular, read the section on inheritance. For the syntax definition how to create types and tables, see here and here.
Constraints
Define primary key, foreign key, uniqueness, not null, and semantic(check) constraints for the logical schema (see previous topic). See here for the relevant parts in the manual.