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

• To learn how to connect to Mimer and run SQL commands.
• To learn the basics of how to translate an Entity-Relationship diagram into tables.
• To gain some experience using SQL as DDL in a relational DBMS, in this case Mimer.
• To learn how to insert, delete and query data using simple SQL commands.

Preparations

• Relationsmodellen (chapter 5 in the course book)
• Översättning från ER-modellen till relationsmodellen (chapter 6 in the course book)
• Getting started with Mimer, to learn how to connect to your Mimer database, and enter SQL commands
• Some Frequently Asked Questions about the Mimer Labs

Boring Low-Budget Scenario

ER diagram

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:

   • Each entity type (Employee, Department and Project) becomes a table.
   • Each one-to-one or one-to-many relationship type (Works at) becomes a reference attribute (or "foreign key", as we say in SQL).
   • Each many-to-many relationship type (Works on) becomes a table.

   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!]