Navigation auf uzh.ch
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.
The reference solution for the conceptual design is here.
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:
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.
The slides shown in the lab are here.
The solutions for the logical design in the form of PostgreSQL-DDL
are here (types) and here (tables). A diagram reverse-engineered from the actual schema is here
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.
The slides shown in the lab
are here.
In order to have sufficient data to test, please load a database dump containing the table definitions and data. The dump file is here. Restore the dump as follows:
pg_restore -O -v -d cashdb -U cashu -v cashdb.tar
Alternatively, download this
dump.
Create a user and database a shown above. Then open the file in PGAdmin in the SQL tool or DBeaver and run it.
You can also execute the file as shown in the lab with the following command:
psql -d cashdb -U cashu -f < path to downloaded file >
How to display and read execution plans is described here. Slides shown in the lab are here. A script to load more data into a table for performance analysis is here.
A page with the SQL exercises can be found
here.
You can also download the SQL exercises here.
Please record your own solutions in a file so that they can be compared to the reference solution later.
... are here.