CS 3200 Database Design Assignment #2

The assignment uses the following schema:

create table Zeta (
  id int primary key,
  title varchar(255) not null
);
create table Beta (
  id int primary key,
  area double
);
create table Delta (
  id int primary key,
  angle double not null,
  isShownBy int not null,
  foreign key(id) references Beta(id) on update cascade on delete cascade,
  foreign key(isShownBy) references Zeta(id) on update cascade on delete cascade
);
create table DeltaType (
  delta int,
  type enum('brittle', 'ductile'),
  foreign key(delta) references Delta(id) on update cascade on delete cascade,
  primary key(delta, type)
);
create table Alpher (
  id int primary key,
  count int not null,
  name varchar(255)
);
create table Gamma (
  id int primary key,
  quantity double not null,
  isOwnedBy int,
  foreign key(isOwnedBy) references Alpher(id) on update cascade on delete no action
);
create table GammaCity (
  gamma int,
  city varchar(255),
  foreign key(gamma) references Gamma(id) on update cascade on delete cascade,
  primary key(gamma, city)
);
create table Eta (
  supply int not null,
  plot int, 
  intensity double not null,
  foreign key(supply) references Beta(id) on update cascade on delete cascade,
  foreign key(plot) references Gamma(id) on update cascade on delete cascade,
  primary key(plot)
);
create table EtaHierarchy (
  parent int,
  child int,
  foreign key(parent) references Eta(plot) on update cascade on delete cascade,
  foreign key(child) references Eta(plot) on update cascade on delete cascade,
  primary key(parent, child)
);

  1. Find all Gammas that supply a brittle Delta (i.e., whose fracture type includes brittle) that is shown by a Zeta titled George.
  2. Find the Alphers by name that own at least a total quantity of 55.33 Gammas in Boston.
  3. Find all Alphers that own a Gamma for which every supply is a Delta with a negative angle.
  4. Update all named Alphers so that their count is equal to the number of Gammas owned by the Alpher.
  5. Define a view BigGammaBoston consisting of all Gammas (including their quantity, attributes of the Alpher that owns the Gamma, and the total area the Gamma supplies) that are in Boston and that supply an area greater than 1000.