CS 5200 Introduction to Database Management Individual 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
);
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.

  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.

Submit your assignment using a zip file containing the Java files.