Databasteknik: Solutions to Exercise 2

Solution 1

Employee
Number Name Salary WorksAt
1 Lotta 20000 10
2 Kalle 19000 10
  Department
Number Name
10 Data
20 Städning

Project
Number Name
100 Apollo
200 Manhattan
  WorksOn
Employee Project
1 10
1 20
2 20

Primary keys:

Reference attributes:

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;


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