Databasteknik: Exercise 2 - Simple Tables in Mimer

This is an exercise. You don't have to show or hand in anything, but if you have any problems or questions, you are welcome to ask your teaching assistant.

There are solutions to the problems.

Objectives

Preparations

Study the course material about the relational model, about translating an ER diagram into tables, and Mimer. We suggest that you look at the following:

Boring Low-Budget Scenario

A company has employees. An employee has a unique number, a name, a salary, and works at a department. A department has a unique number, and a name which is also unique. The work in the company is organized in projects. A project has a unique number, and a name which is also unique. An employee can work on several projects.

ER diagram

An ER diagram for the company

Note that Works at is a one-to-many relationship type, but Works on is a many-to-many relationship type. The entity types Department and Project each have two candidate keys.

There should not be an entity type called Company.

Exercises

  1. Translate the Entity-Relationship diagram to tables. Don't create tables in Mimer yet, just write down which tables are needed, and show which attributes they have. Add some example rows to clarify.

    Remember:

    As an example, here is the table Department:

    Number Name
    10 Data
    20 Städning

    [Check the solution before you continue!] (But do solve it yourself before checking!)

  2. Connect to your Mimer database, for example with BSQL. (See Getting started with Mimer.)
  3. Create the tables. (Hint: Use the SQL command create table.) Don't bother with constraints yet, except that each table should have a primary key.

    [Check the solution!]

  4. Insert some example data in the tables. (Hint: Use the SQL command insert into.)

    [Check the solution!]

  5. Show the content of each table. (Hint: Use select statements.)

    [Check the solution!]

  6. Add the following constraints:
    • The column name in the table Department is unique.
    • The column works_at in the table Employee is a reference attribute that refers to the column number in the table Department.
    • The table connecting Employee and Project has two reference attributes that refer to those two tables.
    (Hint: Use the SQL command alter table, as in alter table Department add constraint ...)

    [Check the solution!]

  7. Test the constraints by attempting to insert data that would violate them.

    [Check the solution!]

  8. Each department should hava a manager. Add a column to the department table that says who the manager is. This column should be a reference attribute to the employee table. (Hint: Use the SQL command alter table, as in alter table Department add column ...) and alter table Department add constraint ...)

    [Check the solution!]

  9. Remove all four tables. (Hint: Use the SQL command drop table. You may have to remove constraints first, using alter table.)

    [Check the solution!]


Thomas Padron-McCarthy (Thomas.Padron-McCarthy@oru.se), October 31, 2007