CS 5200 Introduction to Database Management Individual Assignment #2 Solution

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.
    select g.id
      from Gamma g
     where exists (
        select *
          from Eta e, Delta d, DeltaType t, Zeta z
         where e.plot = g.id
           and e.supply = d.id
           and t.delta = d.id
           and t.type = 'brittle'
           and d.isShownBy = z.id
           and z.title = 'George'
        );
    

    One can use distinct in the select clause instead of a subquery.

  2. Find the Alphas by name that own at least a total quantity of 55.33 Gammas in Boston.
    select a.name
      from Alpha a
     where 55.33 <= (
        select sum(g.quantity)
          from Gamma g, GammaCity c
         where g.isOwnedBy = a.id
           and c.gamma = g.id
           and c.city = '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.
    select t.type
      from FloodType t 
     where exists(
            select *
              from Flood f, Alpha a, Eta e
             where f.region = t.floodRegion
               and f.authoritySupply = t.floodAuthoritySupply
               and f.authorityPlot = t.floodAuthorityPlot
               and f.region = a.id
               and e.supply = f.authoritySupply
               and e.plot = f.authorityPlot
               and a.name is null
               and e.intensity >= 5
           );
    
    This can be simplified in several ways. One can use distinct instead of a subquery, and one can omit the Flood table. One can omit the Flood table because the foreign key constraint on FloodType implies the existence of a Flood record with the required primary key, and no other attributes of the Flood table are needed. Because plot is the primary key of Eta, one can omit the condition e.supply = t.floodAuthoritySupply.
    select distinct t.type
      from FloodType t, Alpha a, Eta e
     where t.floodRegion = a.id
       and e.plot = t.floodAuthorityPlot
       and a.name is null
       and e.intensity >= 5;
    
  4. Find all Alphas that have an authority for which every supply is a Delta with a positive angle.

    Rephrase this query as "Find all Alphas that have an authority such that for every supply of the authority, the supply is a Delta with a positive angle." The authority is an Eta object and the supply of the Eta object is a Beta object. Replacing the "for every" with "not exists not" gives this query: "Find all Alphas that have an authority such that there does not exist a supply of the authority for which the supply is not a Delta with a positive angle." Delta is a subclass of Beta. To determine whether a Beta is a Delta one must check whether there is a Delta object with the same id as the Beta object.

    select a.id
      from Alpha a
     where exists(
            select *
              from Flood t, Eta e
             where a.id = t.region
               and t.authoritySupply = e.supply
               and t.authorityPlot = e.plot
               and not exists(
                        select *
                          from Beta b
                         where b.id = e.supply
                           and not exists(
                                    select *
                                      from Delta d
                                     where d.id = b.id
                                       and d.angle > 0
                                   )
                       )
           );
    

    As with the previous query, one can omit the condition e.supply = t.floodAuthoritySupply. Another simplification that is more subtle is that the Beta is uniquely determined by e.supply, so the only way that it can fail to exist in the subquery is if there exists a Delta with a positive angle. So one could simplify the query to the following:

    select a.id
      from Alpha a
     where exists(
            select *
              from Flood t, Eta e
             where a.id = t.region
               and t.authorityPlot = e.plot
               and exists(
                    select *
                      from Delta d
                     where d.id = e.supply
                       and d.angle > 0
                   )
           );
    

    Because one is returning identifiers, one could use distinct rather than a subquery as follows:

    select distinct a.id
      from Alpha a, Flood t, Eta e
     where a.id = t.region
       and t.authorityPlot = e.plot
       and exists(
            select *
              from Delta d
             where d.id = e.supply
               and d.angle > 0
           );
    
  5. Update all named Alphas so that their count is equal to the number of Gammas owned by the Alpha.
    update Alpha a
       set a.count = 
         (select count(*)
            from Gamma g
           where g.isOwnedBy = a.id)
     where a.name is not null;
    
  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.
    create view BigGammaBoston as
      select g.id, g.quantity, a.count, a.name, sum(b.area) ta
        from Gamma g, GammaCity c, Alpha a, Eta e, Beta b
       where c.gamma = g.id
         and g.isOwnedBy = a.id
         and g.id = e.plot
         and e.supply = b.id
         and c.city = 'Boston'
       group by g.id
         having ta > 1000;
    

Assignment #2 Rubric