Datenbankpraktikum - Exercises
Übungen
- conceptual design (4.10.2016)
- logical design (11.10.2016)
- Consistency constraints (18.10.2016)
- Updates (18.10.2016)
- Queries (01./08./15.11.2016)
- Transactions (22.11.2016)
- Views (29.11.2016)
- User-defined functions and stored procedures (6.12. 2016)
- Trigger (13./20.12.2016)
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 AddressT);
- how to implement static attributes;
- how to handle domains (and whether to use them at all);
- how to handle enumeration types (such as extras in Vehicle).
The Postgres manual about DDL can be found here (sections 5.1 and 5.9). In particular, read the section on inheritance. For the syntax definition how to create types and tables, see here and here.
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
- Sample code for super groups is here
- Reference 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. Slides 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.
Reference solutions are
here.
User-defined Functions and Stored Procedures
Preparation: Chapter 35, 38, 39
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.
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. Slides are
here.
Here are the exercises for
Triggers.