CS 5200 Introduction to Database Management Individual 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 Alpha (
  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 Alpha(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 Flood (
  region int,
  authoritySupply int,
  authorityPlot int,
  foreign key(region) references Alpha(id) on update cascade on delete cascade,
  foreign key(authoritySupply, authorityPlot) references Eta(supply, plot) on update cascade on delete cascade,
  primary key(region, authoritySupply, authorityPlot)
);
create table FloodType (
  floodRegion int,
  floodAuthoritySupply int,
  floodAuthorityPlot int,
  type enum('fluvial', 'pluvial', 'flash', 'coastal', 'urban'),
  foreign key(floodRegion, floodAuthoritySupply, floodAuthorityPlot) references Flood(region, authoritySupply, authorityPlot) on update cascade on delete cascade,
  primary key(floodRegion, floodAuthoritySupply, floodAuthorityPlot, type)
);

  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 Alphas by name that own at least a total quantity of 55.33 Gammas in Boston.
  3. List the floodtypes of floods in an unnamed Alpha region which has an authority with intensity at least 5. Do not list duplicate floodtypes.
  4. Find all Alphas that have an authority for which every supply is a Delta with a positive angle.
  5. Update all named Alphas so that their count is equal to the number of Gammas owned by the Alpha.
  6. Define a view BigGammaBoston consisting of all Gammas (including their quantity, attributes of the Alpha that owns the Gamma, and the total area the Gamma supplies) that are in Boston and that supply an area greater than 1000.