CS 3200 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 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.
    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 Alphers by name that own at least a total quantity of 55.33 Gammas in Boston.
    select a.name
      from Alpher 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. Find all Alphers that own a Gamma for which every supply is a Delta with a negative angle.

    Rephrase this query as "Find all Alphers that own a Gamma such that for every supply, the supply is a Delta with a negative angle." The supplies of a Gamma object are determined by the Eta association. So rephrase this query as "Find all Alphers that own a Gamma such that for every Eta whose plot is the gamma, the supply of the Eta is a Delta with a negative angle." Replacing the "for every" with "not exists not" gives this query: "Find all Alphers that own a Gamma such that there does not exist an Eta whose plot is the gamma, but the supply of the Eta is not a Delta with a negative 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 Alpher a
     where exists(
            select *
              from Gamma g
             where a.id = g.isOwnedBy
               and not exists(
                        select *
                          from Eta e, Beta b
                         where b.id = e.supply
                           and g.id = e.plot
                           and not exists(
                                    select *
                                      from Delta d
                                     where d.id = b.id
                                       and d.angle < 0
                                   )
                       )
           );
    

    Since the Beta is uniquely determined by e.supply, we can omit it:

    select a.id
      from Alpher a
     where exists(
            select *
              from Gamma g
             where a.id = g.isOwnedBy
               and not exists(
                        select *
                          from Eta e
                         where g.id = e.plot
                           and not exists(
                                    select *
                                      from Delta d
                                     where d.id = e.supply
                                       and d.angle < 0
                                   )
                       )
           );
    

  4. Update all named Alphers so that their count is equal to the number of Gammas owned by the Alpher.
    update Alpher a
       set a.count = 
         (select count(*)
            from Gamma g
           where g.isOwnedBy = a.id)
     where a.name is not null;
    
  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.
    create view BigGammaBoston as
      select g.id, g.quantity, a.count, a.name, sum(b.area) ta
        from Gamma g, GammaCity c, Alpher 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