The exam is open-book, open-notes. Please put all your scratch work and your full answers in your blue exam book. (You may use the back of your book for your scratch work if you prefer.) Please keep the question sheet. Do not hand it in. The exam will last two hours, with no break and no lecture following. Please try to finish up in two hours, in accord with University regulations.

Question 1. Consider the following table (with the assumptions of Question 2.1).

Find two candidate keys for this table.

For the questions 2, 3, 4 and 5 below, consider a database that stores information about company mergers. Each merger combines two or more companies to produce one or more other companies. Each merger changes the total number of employees of the companies involved by an amount addedemployees, which may be positive or negative. (It is usually negative.)

drop table premerge;
drop table postmerge;
drop table merger;
drop table company;

create table company (
  id integer primary key,
  name varchar(255) null,
  description varchar(255) not null
);
create table merger (
  id integer primary key,
  addedemployees integer not null
);
create table premerge (
  merger integer not null references merger (id),
  company integer not null references company (id),
  primary key (merger, company)
);
create table postmerge (
  merger integer not null references merger (id),
  company integer not null references company (id),
  primary key (merger, company)
);

Question 2. Using the relational algebra, compute all companies (showing the name and description for each company) that are the premerge participants in a merger that adds more than 100 employees.

Question 3. Compute the same query as in Question 2, but use SQL instead.

Question 4. A dominant company in a merger is a company that is in both the premerge set of companies and the postmerge set. Using SQL, compute all dominant companies (giving the name and description for each one) of mergers that include a postmerge company named "BigCo".

Question 5. Using SQL, find the maximum addedemployees of a merger that has a dominant company.

Question 6. Answer question 6.3 in your textbook.