Database Lab/Datenbankpraktikum - Exercises

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.

The reference solution for the conceptual design is 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.

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

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.
The slides shown in the lab are here.

Updates

Execute updates (Insert, Update, Delete) on your database. Try to violate the constraints you previously defined. (There is no referencesolution for this topic).

Queries Preparation

In order to prepare the queries exercise, perform the following tasks:

• Install Postgres if not done already
• import the dump once it is available
• make yourself familiar with the logical schema and database.
• Write down 10 questions you would like to get answered from the Cash database (e.g., what is the most prevalent vehicle model?)

Queries

Documentation

• Queries are described in the Postgres-Manual: here and here.
• Slides shown in the lab are here.
• Slides with examples for analytic queries, temporary views, and recursive queries are here
• Sample code
  • for recursive queries
  • for analytic queries
  • for super groups

Sample Data

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:

• create a database user cashu (if it does not yet exist)
• create a database cashdb (if it does not yet exist) owned by user cashu
• Rename the file from "cashdb.tar.txt" to "cashdb-plain.tar".
• Restore the database cashdb in pgadmin4 (In the object browser, navigate to "Databases", then right-click "cashdb" and select "restore"). Choose Format "Custom or tar".
.
• Alternatively, execute the following command in a shell or command window:

  pg_restore -O -v  -d cashdb -U cashu -v cashdb-plain.tar

Performance Analysis

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.

Exercises

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.

Student presentations ...

... are here:

• part 1
• part 2
• part 3

Solutions ...

... are here.

Transactions

Documentation about transactions is here in the manual. The slides shown in the lecture are here. Exercises can be downloaded here.
Solutions are here.

Views

Preparation: Views are documented here. The slides shown in the lecture are here.
In the exercises on views we will also cover other approaches how to implement specialization hierarchies. The script containing the table definitions for the two hierarchies (Member and Vehicle) without table inheritance are here. A script for filling the tables is here.
Exercises are here.

Student presentations are here and here. Reference solutions are here.

User-defined Functions and Stored Procedures

Preparation: Chapter 38.5, 43 in the manual. We will only use SQL and PL/pgSQL as implementation languages. You thus do not need to read the sections on PL/TCL, PL/Perl and PL/Python etc.
Slides are here.
Exercises are here.

Trigger

Preparation: Chapter 39 and 40 in the manual.
Slides are here.
Exercises are here.

Security

In the last lesson we take a look at users, roles, and authentication. Scripts can be found as follows:

• user creation,
• role creation,
• authentication.

The slides shown in the lecture are here.