midterm-guide answers

Answers to "Additional Exercises" on Comp 305 midterm study guide

EN exercise 9.4 is below

1. Describe in English what is being asked for in example 8.

Query 8 is:

select e.lname from employee2 e
where e.salary in (select e.salary from employee2 e where e.dno = 5);

This asks for the last names of all employees whose salary matches that of an employee in department 5.

2. Give an alternative solution for example 8 that does not involve an inner query.

select e.lname from employee2 e, employee2 e2 where e.salary = e2.salary and e2.dno = 5

(that is, e2 is an employee in department 5 with the same salary as e)

3. In English, who is getting the raise in example 5? Is this fair?

The query is
update employee2 set salary = 1.10 * salary where salary >= 50000;
This gives everyone with salary >= 50,000 a 10% raise; in our usual COMPANY database (with table employee instead of employee2), that is Mr Borg only.

As to fairness, the situation might be either of the following:

highly compensated employees receive just reward for leading the company through difficult times
the 99% are being ignored.

Either way, the point is to realize only the highly paid are getting a raise.

4. Write query 7a using an inner query instead of a join.
Here is the original:

select e.lname from employee2 e, department2 d
where e.dno = d.dnumber and d.dname="maintenance";

Here it is with an inner query.

select e.lname from employee2 e
where e.dno = (select d.dnumber from department2 d where d.dname = "maintenance");

The inner query looks up the department number for the maintenance department.

5. Write insert statements so the following query returns (2, 30000, 40000):

select count(*), avg(e.salary), max(e.salary) from employee2 e
where e.dno = 5;

We have to add two employees to department 5. The max salary has to be 40,000, which means that for the average to be 30,000 the other salary has to be 20,000.

insert into employee2 values ('bob', 'smith', '234567890', 40000, null, 5);
insert into employee2 values ('james', 'johnson', '345678901', 20000, null, 5);

6. Write a query to print the number of employees, average salary and max salary for all departments with an average salary >= 30000. Also include the department number.

Select e.dno, count(*), avg(e.salary), max(e.salary)
from employee2 e
group by e.dno
having avg(e.salary) >= 30000;

EN Exercise 9.4: ER to tables

There are seven entities in the diagram, including the weak entities. Their basic attributes are in plain type; attributes added for some other reason are in bold.

SHIP_MOVEMENT (weak entity via the HISTORY relationship)
    Time_stamp: (composed of Date and Time, which is usually just a TIME attribute)
    Latitude
    Longitude
    ship_name (owner entity, via the HISTORY relationship)
    primary key: Time_stamp + ship_name

SHIP
    name (primary key)
    owner
    ship_type (to implement the TYPE relationship)
    port_name
    country_name
    sea_name These last three fully determine the port, and implement the HOME_PORT relationship

SHIP_TYPE
    Type (primary key)
    Tonnage
    Hull

STATE/COUNTRY
    Name (primary key)
    continent

SEA/OCEAN/LAKE
    name (primary key)

PORT: weak entity via the IN and ON relationships
    port_name
    state_name (owner entity, via the IN relationship)
    sea_name (owner entity, via the ON relationship)
    primary key: all three of the above

PORT_VISIT: weak entity via the SHIP_AT_PORT relationship and the SHIP and PORT entities.
    start_date
    end_date
    port_name (owner entity, via the SHIP_AT_PORT relationship)
    ship_name (owner entity, via the SHIP_AT_PORT relationship)
    primary key: start_date, port_name, ship_name