Introduction to Databases (581328-9), Spring 2004

Exercises

In this course practicing is important in order to learn the issues and to master them. Some tasks for self testing are embedded in the web-based e-learning material. These tasks are aimed to provide feedback on how you have learnt the topic. They do not give any credit points.

SQL practicing

SQL queries and database maintenance operations are practiced using the SQL-Trainer software. There are 40 SQL tasks devided in three groups. Each group has a deadline of its own. Tasks succesfully carried out by the deadline bring in credit points. The Trainer software evaluates the answers and accepts or rejects them. There are no limits for the number of tries for each task. You may get up to 10 credit points of the SQL tasks, about 32 tasks are needed for the maximum credit.

If you run into problems with the SQL tasks, you may post the description of your problem into the discussion group, send e-mail to the course assistant (doucet@cs.helsinki.fi), or present your problem in the study group. The discussion group is there for students to assist each other. You are, however, not supposed to give the correct query as an assistance but hints that assist the other student to find out the correct answer. Good hints are rewarded with bonus points. The course assistant is also following the discussion group on a regular basis. Example answers for the SQL tasks are published here after the deadline.

The preliminary deadlines for the SQL-tasks are:

  • Set 1: Mon 16.2. at 12.00.
  • Set 2: Mon 23.2. at 12.00
  • Set 3: Mon 1.3. at 12.00

Hint: Do not leave answering the tasks to the last hours before the deadline. There might be computer or network problems. The working of the Trainer software is not monitored regularly during week-ends.

Go to Trainer start page

Study groups

The goal of a stydy group is to study the kernel structures and the design principles of the relational databases by the use of example cases. The stydy group has 4 official meetings and as many unofficial meetings as you want to. The course assistant is available as the instructor in the official meetings:

  • Wednesdays at 14-16 on 21th of January, 4th, 11th, and 25th of February in class C455 , (Antoine Doucet)

  • In the first meeting the students are grouped into study groups. The groups will decide on their leaders (the leader will get upto 2 extra credit points) and select the case they start working with. They also start the specification of the information content of the database.
  • The second meeting concentrates on the kernel structures of the relational databases: domais, keys, foreign keys and dependencies between data items.

    Here are the problems to be discussed in the 2nd meeting of the study group.

  • The third meeting concentrates on verifying that the database design is practical and serves the operations properly.

    Here are the problems to be discussed in the 3rd meeting of the study group.

  • In the final meeting study groups present their database designs to other groups and evaluate the plans of other groups.

Database design task in study groups

Each study group designs a small database and presents their plan as a written document. This document consists of:

  • Title
  • Description of the problem
  • Definition of the information contents of the database
    • UML diagram + associated text definitions
  • Descriptions of the tables
    • database schema as a diagram (either using the technique used in the course or a technique available in some design tool, the image must, however, be embedded in the document)
    • SQL create table statements with comments like the ones of the Pizza database - what values there are in columns
  • Database quality analysis
    • how did the group ensure the quality of the plan
    • dependency analysis and the potential changes it caused
    • some (at least 6) important use cases and their use of the database - dependency matrix (use case vs relation) or sql-queries
    • example relations
    • changes made due to the quality analysis

Technically the document should be either a MS-Word compatible document, a PDF-file or an HTML page.

In the last stydy group session groups presents their plans to other groups. The group should prepare for the presentation by making slides about

  • the problem
  • database schema as a diagram and
  • some examples of the tables

An opponent group will be assigned for each study group. The group should make their preliminary version of the database plan available for their opponent group at least 48 hours before their presentration. The opponent group should evaluate the plan:

  • did they understand the design, if not which were the problems
  • did they find some problems in the design
  • is the quality ensured properly

and present their evaluation in the presentation session.

© Harri LaineUpdated: 11.3.2004, email: Harri.Laine@cs.helsinki.fi