CS 5200 Introduction to Database Management Homework #9

This assignment will use the following SQL schema:

create table Location(
  id int primary key,
  city varchar(255),
  state varchar(100),
  country varchar(255)
);
create table Person(
  id int primary key,
  name varchar(100)
);
create table Photographer(
  id int primary key references Person(id) on update cascade on delete cascade,
  livesIn int not null references Location(id) on update cascade on delete no action
);
create table Specialty(
  photographer int references Photographer(id) on update cascade on delete cascade,
  type enum('portrait','landscape','sport'),
  primary key(photographer, type)
);
create table Photo(
  id int primary key,
  takenAt timestamp not null,
  takenBy int references Photographer(id) on update cascade on delete no action,
  photographedAt int references Location(id) on update cascade on delete no action,
  type enum('portrait','landscape','sport')
);
create table Appearance(
  shows int references Person(id) on update cascade on delete cascade,
  isShownIn int references Photo(id) on update cascade on delete cascade,
  primary key(shows, isShownIn)
);

1. Develop queries (or sets of queries) for the following:
   1. List all photographers, showing the name of each one, along with the number of photographs that they have taken of each type as well as the number of photographs that they have taken with no type. Sort the photographers alphabetically by name and sort the specialties alphabetically.
   2. List the photographers by where they live, first by country, then by state, finally by city. All of these must be not null for them to be included. Sort the columns in the order country, state, city and photographer name.
2. Develop two XSLT scripts that produce HTML files for the output of each of the two queries (or sets of queries) above as produced by javax.sql.rowset.WebRowSet.writeXml(ResultSet rs, OutputStream oStream). The HTML file should display the results for the first query as a table with a column for the photographer's name, the specialty and the number of photos. Do not include an entry if the number of photos is 0. For the second query display the results as a table with columns for country, state, city and photographer.
   1. Here is an example of what the table for the first query should look like:

      Photographer	Specialty	Number of photos
      Fred	portrait	120
      		254
      	Total	374
      Mary	sport	375
      	Total	375

   2. Here is an example of what the table for the second query should look like:

      Country	State/Province	City	Photographer
      Canada	Ottawa	Toronto	Fred
      	Quebec	Montreal	Alice
      USA	Massachusetts	Boston	Bob

Upload your solution to a folder named Assignment9 in your repository. You should have one file named queries.sql for the queries and files named transform1.xsl and transform2.xsl for the XSLT transforms.