CS 5200 Introduction to Database Management Solution to Individual Assignment #4

The following is the SQL schema:

create table Person(
  id int primary key auto_increment,
  name varchar(100) not null
);
create table Team(
  id int primary key auto_increment,
  name varchar(50)
);
create table Membership(
  hasMember int,
  isMemberOf int,
  start date not null,
  end date,
  foreign key(hasMember) references Person(id) on update cascade on delete cascade,
  foreign key(isMemberOf) references Team(id) on update cascade on delete cascade,
  primary key(hasMember, isMemberOf)
);
create table RoleType(
  id int primary key auto_increment,
  type varchar(100) not null
);
create table Role(
  hasMember int,
  isMemberOf int,
  hasRole int,
  foreign key(hasMember, isMemberOf) references Membership(hasMember, isMemberOf) on update cascade on delete cascade,
  foreign key(hasRole) references RoleType(id) on update cascade on delete cascade,
  primary key(hasMember, isMemberOf, hasRole)
);
create table Document(
  id int primary key auto_increment,
  title varchar(1000),
  createdBy int not null,
  createdOn date not null,
  content longtext,
  foreign key(createdBy) references Team(id) on update cascade on delete no action
);
insert into RoleType(type) values('Chair');
insert into RoleType(type) values('Secretary');
insert into RoleType(type) values('Member');

  1. Develop a Java method using JDBC that finds the documents having a specified first part of the title. For each such document print its title, the name of the team that produced it, and the names of the chairs and secretaries of the team at the time when the document was created. The method should look like this:
      public void printDocumentInfo(Connection connection, String firstPartOfTitle) ...
    
    For each document print its title and team exactly once no matter how many chairs and secretaries there are.

    Here is one possible query when the first part of the title is "Doc":

      select d.title, t.name, p.name 
        from Document d, Team t, Role r, Person p, RoleType rt 
       where d.createdBy = t.id 
         and r.isMemberOf = t.id 
         and r.hasMember = p.id 
         and r.hasRole = rt.id 
         and rt.type in ('Chair', 'Secretary') 
         and d.title like '?'
       order by d.title, t.name
    

    The reason for sorting the solution is so that title/team combinations will be grouped together. The program can then more easily print the required output.

    However, the query above will not return anything for documents whose team has neither chairs nor secretaries. So the correct solution should use an outer join like this:

      select d.title, t.name, p.name 
        from Document d join Team t on (d.createdBy = t.id) 
                   left join 
                         (Role r join Person p on (r.hasMember = p.id) 
                                 join RoleType rt on (r.hasRole = rt.id)) 
                   on (r.isMemberOf = t.id) 
       where (rt.type is null or rt.type in ('Chair', 'Secretary')) 
         and d.title like ? 
       order by d.title, t.name
    

    For a complete solution see Solution4.java.

  2. In addition to the query in the first question, the following are also common operations:
    1. Find a person given their name and list the teams that they are currently members of.
    2. Find a role by its type and list the persons who currently have that role for some team.
    3. Update the roles of a person on a team. This is done by expiring any active membership record (i.e., setting its end date, not deleting it), and creating a new membership record.
    Design the indexes for the database.

    The first question should start with the Team because that will be smaller than Document, so we should have an index on Document.createdBy to find the documents created by a team. From this one must outer join with Role which suggests having an index on Role.isMemberOf. From this point, one computes the Person and RoleType using Person.id and RoleType.id, both of which already have indexes.

    The next query requires an index on Person.name to get the person. Then to get the memberships of a person one must have an index on Membership.hasMember. To get the teams, one then uses Team.id which already has an index.

    The next query requires an index on RoleType.type to get the RoleType. Then one needs an index on Role.hasRole to get the Role record. One can find the Person using Person.id which already has an index.

    The last operation requires finding the Membership record of a Person. This requires an index on the primary key of Membership which already has an index. We then need an index on the pair (hasMember, isMemberOf) of Role to get the role records for the membership record.

    Putting these together, along with the primary keys, we need these indexes:

    1. Person: id, name
    2. Team: id
    3. Membership: (hasMember, isMemberOf), hasMember
    4. RoleType: id, type
    5. Role: (hasMember, isMemberOf, hasRole), isMemberOf, hasRole, (hasMember, isMemberOf)
    6. Document: id, createdBy

    Although all of the operations use exact matches, there are cases where a tree index will be advantageous. For Membership, a tree index on (hasMember, isMemberOf) in this order can also be used for an exact match on hasMember. Similarly for Role. The indexes are then the following:

    1. hash index on Person.id
    2. hash index on Person.name
    3. hash index on Team.id
    4. tree index on (hasMember, isMemberOf) of Membership in this order
    5. hash index on RoleType.id
    6. hash index on RoleType.type
    7. tree index on (isMemberOf, hasMember, hasRole) of Role in this order
    8. hash index on Role.hasRole
    9. hash index on Document.id
    10. hash index on Document.createdBy

    If one observes that the first question is much too unselective for indexes to be useful, then the following are the indexes:

    1. hash index on Person.id
    2. hash index on Person.name
    3. hash index on Team.id
    4. tree index on (hasMember, isMemberOf) of Membership in this order
    5. hash index on RoleType.id
    6. hash index on RoleType.type
    7. tree index on (isMemberOf, hasMember, hasRole) of Role, where the first two columns can be in either order.
    8. hash index on Role.hasRole
    9. hash index on Document.id

  3. Develop the JPA annotations for the classes of the database so that the SQL schema is produced.

    The following files show the solution:

  4. Develop a Java method using JPA that finds the documents having a specified title. Print each document, the team that produced it, and the chairs and secretaries of the team at the time when the document was created. The method should look like this:
      public void printDocumentInfo(EntityManager manager, String title) ...
    
    For each document print its document and team exactly once no matter how many chairs and secretaries there are. Note that the objects themselves are printed, not any attributes of the objects.

    The solution is in PrintInfo.java.