CS 3200 Introduction to Database Management Assignment #4

This assignment uses the following data model:

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) not null
);
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,
  index(title) using btree
);
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.

  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.

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

Submit your assignment using a zip file containing the Java files and a text file with your index design.