Datenbankpraktikum - Exercises

1. conceptual design (15.9.2015)
2. logical design (29.9.2015)
3. Consistency Constraints (6.10.2015)
4. Updates (06.10.2015)
5. Queries (13.10/20.10.)
   note that there is no lab on October 27 and November 3! As discussed in the last session, please finish the query part at home. Remember to store your solutions.
6. Transactions (10.11.2015)
7. Views (17.11.2015)
8. User-defined functions and stored procedures (24.11. 2015)
9. Trigger (1.12.2015)
10. JDBC (8.12./15.12.2015)

Conceptual Design

The first exercise is to develop a conceptual model representing the entities of a carsharing organization. A description of the system is here (so far only in German). A conceptual design (the one we will use for the rest of the lab) is here

Logical Design

In the second lesson, we map the conceptual design onto a logical design. See here for logical schema design (table definition) in PostgreSQL: ( PostgreSQL DDL, 5.1, 5.8, 5.11).
The solutions for the logical design in the form of PostgreSQL-DDL are here (types) and here (tables).

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.
Solutions are here.

Updates

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

Queries

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.
Preparation:

• Queries are described in the Postgres-Manual: here and 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:
  • Rename the file from "cashdb-plain.txt" to "cashdb-plain.tar".
  • Create the database cashdb and restore it in pgadmin3 (In the object browser, navigate to "Databases", then right-click "cashdb" and select "restore")
    . Alternatively, execute the following command in a shell or command window:

    pg_restore -c -C -d cashdb -U cashu -i -v cashdb-plain.tar

    In case pg_restore cannot be found (may happen on Windows), execute the script pg_env.bat first. This script is usually in the installation directory of PostgreSQL.
• Slides with examples for temporary views and recursive queries are here
• Sample code for recursive queries is here
• Sample code for analytic queries is here
• Die Musterlösungen sind hier.

Transactions

Documentation about transactions is here in the manual.
Exercises can be downloaded here.
Solutions are here.

Views

Preparation: Views are documented 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.
Die Musterlösungen für die Übungen zu Sichten sind hier.

User-defined Funktiones and Stored Procedures

Preparation: Chapter 35, 38, 39 in the manual. We wil lonly 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.
Here are exercises for functions and procedures.
Here are solutions for functions, procedures, and table functions.

Trigger

Preparation: Chapter 36 and 37 in the manual.
Here are the exercises for Triggers.
Solutions are here and here.

JDBC

A short introduction into JDBC is here. An introduction into JDBC on an old Postgres-page (Postgres 7.4, but the JDBC part is still accurate): link. Until December 8 you should have a Java development environment installed and should be familiar with it. Possible IDEs are (NetBeans, Eclipse, JDeveloper, ...). In the lab, weĺl show first steps with NetBeans.
Please also download a JDBC-driver for Postgres (e.g. the Type-4-driver here).
The goal of the JDBC-exercise is to complete several Java programs with the appropriate JDBC-calls. Complete the programs in the following sequence:

1. ListPersMembers.java
2. GetMembers.java
3. OpenReservations.java
4. CancelReservations.java
5. CreateInvoices.java