Databasteknik: Solutions to Exercise 2

Solution 1

Primary keys:

• In Employee: Number
• In Department: Number
• In Project: Number
• In WorksOn: Employee and Project together.

Reference attributes:

• Employee.WorksAt refers to Department.Number.
• WorksOn.Employee refers to Employee.Number.
• WorksOn.Project refers to Project.Number.

Solution 3

create table Employee
(number integer,
name varchar(10),
salary integer,
works_at integer,
primary key (number));

create table Department
(number integer,
name varchar(10),
primary key (number));

create table Project
(number integer,
name varchar(10),
primary key (number));

create table WorksOn
(employee integer,
project integer,
primary key (employee, project));

Solution 4

insert into Employee values (1, 'Lotta', 20000, 10);
insert into Employee values (2, 'Kalle', 19000, 10);

insert into Department values (10, 'Data');
insert into Department values (20, 'Städning');

insert into Project values (100, 'Apollo');
insert into Project values (200, 'Manhattan');

insert into WorksOn values (1, 100);
insert into WorksOn values (1, 200);
insert into WorksOn values (2, 100);

Solution 5

SQL>select * from Employee;
     NUMBER NAME            SALARY    WORKS_AT
=========== ========== =========== ===========
          1 Lotta            20000          10
          2 Kalle            19000          10

                  2 rows found

SQL>select * from Department;
     NUMBER NAME
=========== ==========
         10 Data
         20 Städning

                  2 rows found

SQL>select * from Project;
     NUMBER NAME
=========== ==========
        100 Apollo
        200 Manhattan

                  2 rows found

SQL>select * from WorksOn;
   EMPLOYEE     PROJECT
=========== ===========
          1         100
          1         200
          2         100

                  3 rows found

SQL>

Solution 6

alter table Department add constraint foo1 unique (name);
alter table Employee add constraint foo2 foreign key (works_at)
    references Department (number);

alter table WorksOn add constraint foo3 foreign key (employee)
    references Employee (number);                                 
alter table WorksOn add constraint foo4 foreign key (project)
    references Project (number);                                 

Solution 7

SQL>insert into Department values (30, 'Data');
MIMER/DB error -10110 in function EXECUTE
         UNIQUE constraint violation
SQL>insert into Employee values (3, 'Hjalmar', 11000, 30);
MIMER/DB error -10105 in function EXECUTE
         INSERT or UPDATE operation invalid because the referencing
         table SYSADM.EMPLOYEE does not satisfy a referential constraint
SQL>insert into WorksOn values (4, 100);
MIMER/DB error -10105 in function EXECUTE
         INSERT or UPDATE operation invalid because the referencing
         table SYSADM.WORKSON does not satisfy a referential constraint
SQL>insert into WorksOn values (1, 400);
MIMER/DB error -10105 in function EXECUTE
         INSERT or UPDATE operation invalid because the referencing
         table SYSADM.WORKSON does not satisfy a referential constraint
SQL>

Solution 8

alter table Department add manager integer;
alter table Department add constraint foo5 foreign key (manager)
    references Employee (number);

Solution 9

drop table WorksOn;
drop table Project;
alter table Employee drop constraint foo2;
drop table Department;
drop table Employee;