Note
There are a lot of technical details presented with this specification. This is done to provide clarity and a starting point for design discussion. However, it is not done so to specify specific class or method naming, project layout, or order of work. You are free to make many technical adjustments to the ideas presented. Implementation of the end-to-end scenario is the primary requirement.Tip
This project provides an obvious opportunity to use compound business primary keys and the documentation below depicts some of the ramifications in doing so. Synthetic, single value primary keys are permitted in your solution without penalty. Single value primary keys are much easier and should be the first choice if you have limited experience and limited time to work with the more complex primary key and foreign key mechanism.
Design 2 sets of database schema that account for the following information. Although we will deploy the 2 database schemas to the same database for the project, they should be designed to be independently deployed to separate databases. eLeague and eClub are two independent applications. This will primarily affect your attempt re-use tables or to make primary key assumptions between the two.
This table is used to store singleton information for the entire application. Although JPA requires a primary key field, this table should have only one row. Right now, it is being depicted as referencing the current season.
LEAGUE_NAMEUnique name for the league that will not change. It can be used as a primary key.
SEASON_ID(1 to 0..1 relation to ELEAGUE_SEASON) - this references the most current season and will be NULL before the start of the first season.
COORDINATOR_ID(1 to 0..1 relation to the ELEAGUE_CONTACT) - designates the overall coordinator for the league. They will be responsible for adding clubs, forming divisions, and scheduling contests.
Design a table that contains POC information for individuals responsible for the league, divisions, and teams. This information is primarily on-line information and need only contain a textual name and an e-mail address. There is no need for any more detailed information and we don't want to expose phone numbers.
IDThis is a synthetic/generated id to identify the contact.
CONTACT_NAMEThis is a required, non-unique textual name for the individual. This will be posted the league site and can be updated.
EMAILThis contains the e-mail contact information for the individual that will be posted to the web site. It its highly recommended for optimal operation of the league, but may not be available when contacts are initially entered. We should not treat this field as unique since family members have been known to share the same league e-mail address.
LOGINThis is a field that can be used to represent the individual's login to the web site. This field is not required. However, if it is not supplied, it is assumed that they will not be personally accessing the site. This field can be updated, thus it would not work well as a primary key.
Design a table to store the name and core information associated with a season. Seasons will never be removed as a part of normal operation.
IDThe unique ID for the season. This value is required, must be unique, and cannot be changed. It can be used as a primary key if desired. It can be a natural value (e.g., Fall2000) or a synthetic value.
SEASON_NAMEThe textual name for the season (e.g., "Fall 2000"). This value is required and must be unique. It could be used as a primary key if desired, but is thought to be more of a textual value that may require modification.
START_DATEEND_DATELEAGUE_ID(N:1 relation to ELEAGUE_LEAGUE). This relationship allows all seasons to form a DB relationship to the league.
Design a table that represents a division for teams, for a single season. Divisions will be arranged by group (i.e., age) and level of play. Since group/level names cannot be relied on for any specific ordering, you may wish to add a competitive rank property (11, 21, 22, 23 might equate to U11-D1, U12-D1, U12-D2, U12-D3 with U11 and U12 being a group and D1, D2, and D3 being a level).
SEASON_ID(N:1 relation to ELEAGUE_SEASON)
GROUPA name for a class of teams, such as an age group (e.g., U11, Bantam, or Masters). This is required and does not change.
LEVELA name for the competitive level of play (e.g., DI, DII -or- AA, A, B, etc.)
COORDINATOR_ID(N:1 relation to ELEAGUE_CONTACT) - this individual will be responsible for updating scores for contests within the division.
COMP_RANKINGA value that can be used to sort divisions by their competitive ranking rather than an ASCII sort of their group and level names.
Design a table that represents a team's participation in a division for a season.
TEAM_ID(N:1 relation to ELEAGUE_TEAM) - a team will participate in 0 to many seasons and we will keep historical information of the teams from season to season.
SEASON_ID,GROUP,LEVEL* (N:1 relation to ELEAGUE_DIVISION) - a team will participate in only one division per season.
Design a table that represents a team over time. We are interested in tracking points of contact for the team and divisional play. We do want to keep current and historical division play, but do not need to keep historical contact information.
IDAn artificial id for the team
TEAM_NAMEEach team must have a name, but it need not be unique
CLUB_IDEach team must have a name, but it need not be unique
Design a table that describes the role played by a contact for a team. Contacts are usually a manager or coach. You can have multiple contacts for a specific team and they can even have the same role (e.g., someone can be coach and manager and two people can be a (assistant?) coach). However, no one can have multiple roles of the name for a single team (e.g., you can be manger for 2 teams, but you can't manage the same team twice). Therefore we have the option of forming a compound primary key out of the role name, contact foreign key, and team foreign key.
ROLE_NAMEContains the type of role the contact plays for a team (e.g., manager, coach). This value is required and will not change.
CONTACT_ID(N:1 to ELEAGUE_CONTACT). This reference is mandatory and will not change.
TEAM_ID(N:1 to ELEAGUE_TEAM). This reference is mandatory and will not change.
Design a table that represents a club for teams.
IDAn artificial primary key value
CLUB_NAMEThe textual name for the club. This value must be supplied, may be changed, and must be unique.
REP_ID(N:1 to ELEAGUE_CONTACT). This reference supplies the contact information for the club rep.
Design a field that represents a venue for holding contests. Venues are owned by a club, but are given to the league to control their scheduling. Other than scheduling, the key information is to provide map, direction, and other information that would be helpful for visiting teams to locate the venue.
IDThis is an artificial value to identify a venue.
CLUB_ID(N:1 to ELEAGUE_CLUB)
VENUE_NAMEA textual name used to identify a venue (e.g., YaYa Park, field #3)
ADC_PAGEThis is a text field containing the ADC map, page, and grid where the venue is located.
DIRECTIONSThis is provided by the club and may be updated to reflect current direction information. Some clubs even add information about whether pets are allowed or whether there are bathrooms are available on-site.
ADDRESS_ID(N:1 to ELEAGUE_ADDRESS) - this is required.
Design a table to hold an address. Although it could be used for anything, it will primarily be used for people attempting to locate a venue by address information using Google Maps or their GPS Navigation.
IDThis is an artificial value to identify a specific address
STREET1STREET2CITYSTATEZIP
Design a table to hold contest information. Contents are scheduled at venues for a home and away team. They are for a specific date, start and end time, and should never overlap with another contest for the same venue. Contests are always for teams in the same division.
IDThis is an artificial value to identify a specific contest.
START_TIMEIdentifies the time of day the contest will start. This field may not be known when the contest is first identified or post-poned, so it is not required.
DURATION or END_TIMEThis either can identify the amount of time allocated to the contest or express the actual end time.
VENUE_ID(N:0..1 relation to ELEAGUE_VENUE) This relationship may not be known when the contest is first identified or post-poned, so it is not required.
HOME_TEAM_ID(N:1 relation to ELEAGUE_TEAM_SEASON) This relationship is required. Note that it is related to the team's season in this database and not directly to the team's table in eClub.
AWAY_TEAM_ID(N:1 relation to ELEAGUE_TEAM_SEASON) This relationship is required. Note that it is related to the team's season in this database and not directly to the team's table in eClub.
SEASON_ID, GROUP, LEVELUsed to complete the compound foreign key values to the home and away teams. Since teams must be in the same division, we should be able to reuse these columns to enforce that rule.
HOME_SCOREScore for home team to be updated by the division coordinator.
AWAY_SCOREScore for away team to be updated by the division coordinator.
Design a table to contain information associated with an individual in the club.
IDThis is an artificial value used to identify an individual.
FIRST_NAMEThis should be required, but may be updated
LAST_NAMEThis should be required, but may be updated
EMAILThis is optional
LOGINThis is optional. Users without a login cannot login to update their information.
Design a table to hold a many-to-many link table representing parent/child relationships between individuals in the club.
CHILD_ID(N:1 relation to ECLUB_INDIVIDUAL)
PARENT_ID(N:1 relation to ECLUB_INDIVIDUAL)
Design a table to hold information specific to a player.
ID(1:1 relation to ECLUB_INDIVIDUAL). This is both a primary and foreign key value.
JERSEY_NOThis is optional and can be updated over time.
POSITIONThis is optional and can be updated over time.
DOBThis is the date of birth for the player used to help assign a player to a pool of teams. It is required, but may need correcting over time.
Design a table to hold information specific to a coach.
ID(1:1 relation to ECLUB_INDIVIDUAL). This is both a primary and foreign key value.
CERT_NOThis is optional and can be updated. It will contain their coaching certification number.
CERT_LEVELThis is optional and can be updated. It will have values like 1, 2, 3, etc.
Design a table to hold information specific to a team within the club. Note that historical information for teams is not kept from season to season. For simplicity, we will only assign a single head coach (no assistant coaches). Note also the information below is internal to eClub. You may want to include an indentifer the league tracks this team by and store it here for querying the league about the team.
IDThis is an artificial value used to refer to a specific team.
TEAM_NAMEThis is a required field used to hold a textual name for the team. It can be updated.
LEAGUEThis is an optional field that can be updated over time. It will identify which league they are playing in.
GROUPThis is an optional field that can be updated over time. It will identify which grouping the team is playing within a league (e.g., U11 or Bantam).
LEVELMANAGER_ID(N:0..1 relation to ECLUB_INDIVIDUAL). This is optional and can be updated over time.
HEAD_COACH_ID(N:0..1 relation to ECLUB_COACH). This is optional and can be updated over time.
LEAGUE_TEAM_IDThis is an optional field that will get updated once the team is registered with the league. Since the League and Club databases are separate, we cannot re-use IDs between them.
Design a table to hold the many-to-one linkage between players and teams. Teams have multiple players and we'll limit players to only one team so far. If we model this as a link table, it will be easier later on to allow a player to be on multiple teams.
PLAYER_ID(N:1 relation to ECLUB_PLAYER)
TEAM_ID(N:1 relation to ECLUB_TEAM)
Stick to JPA 2.1 constructs
I discovered very late in the preparation for the Fall 2018 course that the course dependencies are set to Hibernate 5.3.x, to leverage JPA 2.2 in some of the examples and capabilities. However, without being and to fully run Wildfly in ee8 preview mode, we will be limited to Hibernate 5.1.x when deployed to the server in follow-on projects this semester. That version of Hibernate is JPA 2.1. This should not be an issue unless you make use of the new Java DateTime API mappings or the getStream() query result.
Design a class that encapsulates the points of contact in the system. This information will primarily be used to post point of contact information for teams, divisions, clubs, and the league to the web site.
id:intname:StringTextual name for individual to be known as.
email:Stringlogin:StringMay be assigned for individuals to login and be able to modify team, division, and contest information that they are responsible for.
Design a class that can be used to wrap a Contact for points of contact where their role must be qualified. This is the case for team points of contact where the individual can be one of manager or coach.
id:introleNameEnum(UNKNOWN, MANAGER, COACH)
contact:Contactteam:Team
Design a class that holds information for a venue. It is shown as being as being made up of an embedded Address class, where the address class holds no primary key of its own. You may map the 2 table in alternate ways.
id:intname:Stringdirections:StringadcPage:Stringaddress:Addressclub:Club
Design a class that represents a location that can be driven to. This is being depicted as an embedded class, with no primary key value of its own. You may map it in other ways.
street1:Stringstreet2:Stringcity:Stringstate:Stringzip:String
Design a class that represents a Club and all of its venues and teams.
id:intname:Stringteams:Collection<Team>venues:Collection<Team>
Design a class that represents information for a team.
id:intname:Stringclub:Clubcontacts:Collection<ContactRole>
Design a class that represents a team's division play within a season. It is anticipated that this class will have a compound primary key with the identifying information for the team and division. You will need to create an embeddable primary key to hold the information.
team:Teamdivision:Divisioncontests:List<Contest>A list of contests, possibly ordered by date.
wins:intA transient, read-only property calculated by contests the team has won this season.
losses:intA transient, read-only property calculated by contests the team has lost this season.
ties:intA transient, read-only property calculated by contests the team has tied this season.
Design a class that represents divisional play between teams within a season. Since group/level names cannot be relied on for any specific ordering, you may wish to add a competitive rank property (11, 21, 22, 23 might equate to U11-D1, U12-D1, U12-D2, U12-D3). This class also may have a compound primary key (again, these are technical details you can decide).
id:intseason:Seasonname:String(has been referred to as "group" at times)
level:intUsed to determine competitive level. May have been referred to as "ranking" at times.
coordinator:Contactteams:List<TeamSeason>
Design a class that represents play for a perion of time when contests will be held.
id:intname:StringstartDate:DateendDate:Date
Design a class that represents a contest at a venue for a home and away team. Note that the start/end times or date of contest information may or may not map directly to database column. Some of these values may be derived from other values and therefore are "Transient" as far as O/R Mapping concerned. I will list the properties that callers of this class will be concerned about; whether they come directly from the database or are derived.
id:intstartTime:DateCallers will want something they can translate to a user output that represents hh:mm
endTime:DateCallers will want something they can translate to a user output that represents hh:mm. Schedulers will also need this to determine conflict.
location:VenuehomeTeam:TeamSeasonawayTeam:TeamSeasonisComplete():booleanAnother field or null or negative scores can indicate a contest that has not yet completed.
homeScore:int or IntegerawayScore:int or Integer
Design a class that encapsulates information for an individual in the club.
id:intfirstName:StringlastName:Stringemail:Stringlogin:Stringparents:List<Individual>Zero or more parents
children:List<Individual>Zero or more children
Design a class that encapsulates information for a player.
jerseyNo:IntegerJersey numbers are not assigned right away.
position:StringPosition is not assigned right away and can be changed.
dob:DatePlayer's date of birth.
individual:IndividualThe player's identity
team:ClubTeamKeep players to a single team for simplicity.
Design a class that encapsulates information for a coach.
certLevel:StringcertNo:Stringindividual:IndividualCoach's identity.
team:ClubTeamKeep coach to a single team for simplicity.
Design a class that encapsulates information for a team. You can call it "Team", but there is already a class called "Team" in eLeague and may get confusing.
id:intname:Stringleague:StringIdentifier for league participating with. Not used.
levelName:StringDescriptive name of league's division level. Not used.
level:IntegerCompetitive level team wishes to participate at or is participating at. Not used.
leagueTeamId:IntegerA key value that identifies the team within the eLeague system. ** This is used **
manager:IndividualheadCoach:Coachplayers:List<Player>
Add validation API declarations to your business objects as appropriate. This need not be extensive or exhaustive. Just do enough to show use of declarative validation as a part of your application.
Determine the validation groups you wish to use in your application (suggest business logic interface and persistence tier).
Add validation annotations to your business objects and assign them to your designed groups as appropriate.
Manually invoke validation from the your junit test and integrate the validation into your persistence unit.
Design and implement a mechanism to ingest a starting state for eLeague based on a provided data file and parser. You will implement two primary sets of classes to support this requirement; the DAO(s) and an Ingestor.
Design and implement a set of DAOs that can be used to ingest eLeague business data into the database using the database schema you designed as a part of a separate requirement. These DAOs can optionally be tuned for ingest or simply reused from your CRUD-style requirements.
Design and implement an Ingestor that will use an externally provided parser to obtain business data for eLeague and use the Ingest DAO to populate the database.
Note
Please ignore references in the diagrams that call out use of JDBC. All DAOs can be implemented exclusively with JPA for this assignment.
Design and implement a DAO layer that will map the business objects between the object model and the database using the Java Persistence API (JPA). These DAOs will support all standard CRUD operations and can optionally implement the same interface as other potential DAO implementations. The implementation can make liberal use of JPA @Annotations, descriptor files, or a combination of both. Your interface should encapsulate the fact that an EntityManager is being used and the same EntityManager should be shared among other DAOs in the same Thread. Your DAOs should not attempt to control the transaction or they will NOT be portable to the EJB tier.
LeagueDAO/JPALeagueDAOEncapsulates the use of JPA when mapping League and closely associated business objects (Season, Division, TeamSeason, Contest, and Contact) to/from the database.
ClubDAO/JPAClubDAOEncapsulates the use of JPA when mapping Club and closely associated business objects (Venue, Address, Team, and Contact) to/from the database.
Add tuning to your database schema by augmenting the DDL files with indexes for foreign keys, joins, and where clauses. This need not be extensive or exhaustive. Just do enough to show that proper data model and database tuning is part of the overall enterprise development process.
Design an initial business interface and business logic for the applications. The core O/R mapping work will be done by the DAOs. However, it is the ultimate responsibility of these business logic implementations that either it or the business objects enforce the business rules of the application. The DAOs only perform O/R mapping and do not enforce such things as business ordering. The business logic is assumed to work within the context of a single, externally controlled transaction. Do not attempt to control the transaction of the EntityManager within these objects or you will NOT be portable to the EJB tier (without extra work). You need only implement the behavior required to implement the end-to-end use case listed in the testing section. Some of the anticipated methods are listed below.
LeagueMgmt/LeagueMgmtImplEncapsulate the actions required to create Clubs and manage Seasons and Divisions.
addSeason - league coordinators will need to create a new season for the league. The league cannot have 2 or more concurrent seasons and will know the most current season, if any.
addDivision - league coordinators will need to add divisions for a season. Club coordinators will be assigning their teams to the league's divisions for a season.
getDivisions - users will need to know which divisions exist for the most current season. There will also need to be the capability to get historical information for previous seasons.
createClub - league coordinators will need to create new clubs for the league. Once the club has been created, ClubMgmt can be used to perform management of the club details.
assignTeamDivision - clubs will have to assign their teams to divisions after the league has created them for an upcoming season.
getDivisionStandings - users will want to get divisional standings information for the most current season. This would normally consist of an ordered list of teams based on wins (you can ignore ties) and their win, loss, tie totals. Note that this may involve the creation of a transient object that is calculated on-demand by business objects and may not be persisted in the database. There will also need to be the capability to get historical information for previous seasons.
getTeam - regular users and team/club officials will need to obtain team points of contact, schedule, and contest results for the most current season. There will also need to be the capability to get historical information for previous seasons.
ClubMgmt/ClubMgmtImplEncapsulate the actions required to manage Venues and Teams.
addVenue - you will need to create one or more venues for the league to schedule contests for your club. The only thing that the club manages for the venue is keeping directional information up to date.
addTeam - you will need to create teams that play for the club. Note that clubs span multiple seasons/divisions and may even sit out a season. Therefore, teams are usually added first and then later assigned to divisions.
updateTeamContact - clubs and teams will need to update points of contact information.
ContestMgmt/ContestMgmtImplEncapsulate the actions required to schedule and manage contests between teams within the league.
scheduleSeason - league officials will need to create a home and away schedule for each team in the division with each team playing each other at least once and possibly more (for smaller divisions), up to a specified number (default to 10) number of games. Your scheduling can be extremely simple as long as it does not schedule conflicting contests. Ideally a team would not play more than once on a single day and you might want to limit contests to a specific set of days of the week. However, the fact that you have placed the scheduling within the correct architectural area is the key point. How simple you make the algorithm is totally up to you and will not impact your grade. You may decide how much scheduling gets done by the business object(s) and how much gets done by the business logic.
reportScore - division coordinators will need to be able to report the results of a contest.
LeagueTestUtil/LeagueTestUtilImplA useful tool during testing that encapsulates how to get the application back into a known state prior to running a test or to inspect values not normally exposed through the normal business interfaces.
resetAll - sanely take the state of the system down to a coldstart.
populate - you might want the database populated with a known state prior to running a test. This may delegate to the LeagueIngestor.
get/doXXX - methods that are unsafe for the actual business logic, but are needed for development and test.
LeagueIngestorThe Ingestor written as a part of a separate requirement is also logically considered part of this tier.
ingest - point an externally provided parser at a set of test data and use the DAOs to populate the system to a known state.
Note
Keep in mind that this project has two notions of a club. From the league's perpective it manages schedules, scores, and high level contact information for teams within a club. From the club's perspective, there is the need to manage coaches, players, and parents associated with the team. For that reason -- I am calling the club an organization here to avoid some confusion.
MemberMgmt/MemberMgmtImplEncapsulates the actions required to manage individuals registering with the club.
createParent - parents will need to be able to register with the club.
createPlayer - parents will need the ability to register their minors as players. All players will need at least one parent.
addCoachRole - coaches will need to be able to register with the club. It is common that a coach is also a parent.
getIndividual, Player, and Coach - created individuals will need to be retrieved.
OrgMgmt/OrgMgmtImplEncapsulate the actions required to create teams and get team information. Some of this information will come from contacting eLeague in future projects.
createTeam - club officials will need to create teams.
assignPlayers - club officials will need to assign players to teams
assignCoach - club officials will need the ability to assign a coach to a team
assignManager - club officials will need to ability to assign a manager to a team
getTeamRoster - users assigned to a team will need the ability to get the roster for team. Rosters contain coach, manager, player, and parent information.
getTeamSchedule - users will need the ability to get the schedule for a team for the most current season. This information will come from eLeague in a future project. Team schedules should have contest dates, venue, and scores.
ClubTestUtil/ClubTestUtilImplA useful tool during testing that encapsulates how to get the application back into a known state prior to running a test or to inspect values not normally exposed through the normal business interfaces.
resetAll - sanely take the state of the system down to a coldstart.
populate - it may be helpful to return the database to a known populated state between tests.
get/doXXX - methods that are unsafe for the actual business logic, but are needed for development and test.