Life after the NHL: A Database Case Study of the Development of the Professional Athlete Transition Institute Richard V. McCarthy Computer Information Systems, Quinnipiac University Hamden, CT 06518 And Wendy Ceccucci Computer Information Systems, Quinnipiac University Hamden, CT 06518 ABSTRACT The Professional Athlete Transition Institute (PATI) is focused on providing educational resources and services to meet the needs of former professional athletes. To meet the goals of the Institute a variety of information pertaining to each athlete must be collected and maintained. This case provides a detailed description of PATI, their data requirements, and the queries and reports that they regularly need to generate. PATI serves as a basis for defining requirements for a comprehensive relational data model. The case requires students to design, develop, and document a database management system. 1. BACKGROUND The Professional Athlete Transition Institute (PATI), housed at Quinnipiac University, Hamden, Connecticut was developed to provide educational resources and services to meet the needs of professional athletes. PATI was created following a survey that was sent to over 2,000 active NHL Alumni members. Over 650 members of the National Hockey League Alumni Association (NHLAA) responded to the survey making it one of the most comprehensive surveys of retired professional athletes in North America. The survey was used to initiate a needs assessment for retired athletes. Although begun in conjunction with the NHL, PATI is focused on the development of research for all professional athletes and their member organizations, and is working to provide services to the National Football League as well as the United States Olympic Committee. Quinnipiac University President John L. Lahey said his school “will strive to become a national center of excellence in the development of research pertaining to elite performers in transition. The institute also will aim to create cutting-edge educational resources and services to meet the demands of professional athletes.” The mission of PATI is “to assist our clients in finding the right program, the right education facility and the right courses to allow them to quickly meet their educational goals. We will also aim to provide our clients with significant and substantive research and information related to the athletic transition process.” PATI helps professional athletes transition from a career in athletics to other opportunities. The services they provide include educational assessments, credit development, financing opportunities and job placements to athletes worldwide. Not every professional athlete has the career longevity or star earning power of a Michael Jordan, Payton Manning or Wayne Gretsky. Many professional athletes have careers that are cut short by injuries or other factors. Oftentimes these athletes had put their educational objectives on hold to compete at the professional level, either by moving immediately into the professional ranks or through participation in minor league organizations. Age is also a critical factor. Many athletes’ careers come to an end while they are still in their twenties or thirties; leaving them at a point in their life where almost all they have previously focused on has been their particular sport. With many productive years ahead of them, they frequently find themselves in the quandary; is there life after sports? Services Offered: PATI will assess the needs and objectives of their clients to provide customized options designed to meet educational, skill development and personal goals. Their services include: * Degree Completion Assistance – assisting in completing degree requirements at any 2 or 4 year university in North America. * Skill Development and Workshops – assisting clients in choosing workshops and seminars relevant to a specific skill that they would like to acquire or improve upon. * Credit Consolidation – to facilitate their clients earning a degree, they will consolidate at one institution any university credits that were previously earned at other universities. * Life Experience Credit – designed to assist their clients in earning university credits for previous work, life or business experiences. This is referred to as a Portfolio Assessment. * Delivery Methods - designed to provide a variety of learning options, including classroom education, online learning, correspondence courses and accelerated learning. * Financing Opportunities - designed to provide access to programs available only to professional athletes. * Registration assistance and course tracking. Recently, PATI conducted their first National Hockey League Alumni Business Development and Networking Seminar, which provided former NHL players an opportunity to meet business executives and other alumni members and to discuss issues facing former athletes. “Our goal is to expand the NHLA [National Hockey League Alumni] Seminar Series so that we can bring it to every NHL city in North America," said Dale Jasinski, executive director of the Professional Athlete Transition Institute. "This will build a stronger NHL alumni network, provide a template for other sports and involve the local business community.” Seminars such as this are one of the many services provided to assist former athletes after their athletic career is over. Athletic career transition is a unique experience and each individual has their own needs and objectives. Therefore, services sometimes need to be tailored to specific individuals. Life after Sports? In a 2002 survey of former professional hockey players, PATI found that former player’s interests tended to vary, but clustered around the following: Table 1. NHLA PATI Survey Occupation Percent Hockey Related 38% Business Management & Sales 33% Professional Services 16% Community Service 7% Trades 5% Other 1% Total 100% The opportunities for former players range from traditional employment to entrepreneurship or volunteering for charitable organizations. PATI assists in maximizing the potential and marketability of their clients during this career transition phase of their life. 2. The DATABASE PROJECT Since PATI’s inception in 2002, their business has grown substantially. As a result, they have identified a need for a database that will keep track of all of their current clients. They presently have approximately 5,000 clients, so their manual record keeping has become unwieldy. Their information is currently in Excel files or contained within paper based records. They wish to achieve the following benefits: 1. Elimination of paper based files. 2. The ability to run queries and reports to identify current client information, marketing analysis, and program evaluation. 3. Provide their clients with the ability to have limited update capability on their own information (e.g., address changes, email-id changes). 4. The ability to back-up and restore client information. 5. The ability to ensure reliable and accurate client information. They have documented their current information needs to be as follows: A client information file is currently maintained within an Excel file. It contains the clients name, address, phone number (both home and cell phone), email address, fax number, date of birth and education history. Education history is optional and contains a listing of all 2/4 year colleges or universities that a client has attended, the dates they attended (start and end), the address of where the school is located, the number of credits earned at that school, their major (if applicable), and if a degree was earned the degree type is recorded (valid degree types are A.S., B.S., B.A., MBA, PhD, or DBA). Some clients had dual majors, while others were undecided. It is important to insure that the number of credits earned is reasonable. Credits between 0 and 200 are considered reasonable. On a monthly basis, PATI produces a report that lists all clients, the total number of credits that they have earned and each school they have attended (see Figure 1). If a client has not earned a degree, then one of the services that PATI provides is credit consolidation. Once a client enrolls in a university then the PATI team will work with that university to have prior credits consolidated. They will keep track of which courses the client has been given credit for, as well as the number of credit hours. Additionally, they will track which courses the client requires for degree completion, the number of credit hours, pre-requisite courses, and when the courses are offered so that they can develop and maintain an anticipated schedule (e.g. spring, fall, or every semester). (They track when the courses are offered so that they can develop (and maintain) an anticipated schedule). To manage an anticipated schedule they need to know how many credit hours per semester a client is willing to take; this varies by client but must be less than 21 credit hours. Information about the client’s status as an athlete is maintained. This includes whether they are an active player or alumni, which league they played in (currently the leagues consist of the NHL, NBA, NFL, MLB, USOC, or WNBA). For each season they record the teams the client played for, as well as the number of games played with each team. Some clients have played for more than one team during a season. Seasons are always tracked by the beginning and ending years. No client has played in a professional season prior to 1970. Figure 1. Educational History A client’s employment history for all employment that was not part of their professional athletic career is also tracked. This includes the employers name, address, phone number, the start and end date of the employment (the end date must be greater than or equal to the start date), annual salary, position title, supervisors name and phone number, as well as a brief description of their job duties. Not all clients have had jobs outside their professional career. Additionally, employment references are also logged. Typically, each client provides up to three references, though some may provide more. A reference name, address and phone number are recorded. No matter how many prior jobs a client has held, they are still required to provide up to three references. It is sometimes important for the PATI personnel to follow-up with professional teams to get information regarding their clients. Therefore, they maintain a list of contacts (name, and phone number) for each team (for every league). This contact list is printed on a monthly basis, sorted by league and team (see Figure 2). Assessment is an integral service provided by PATI. Assessment helps to identify client skills sets and areas of interest. Assessment consists of a series of tests that help place an individual in a proper program. Although the forms and questions vary, the information that is needed is the same for all assessments. Each assessment is identified by a unique id, and has an assessment title and description. Assessments consist of a series of questions that are numbered sequentially. Each question has a range of responses; however within an assessment the range is the same (i.e., 1 to 5 possible responses per question with only 1 correct response). It is important to keep track of the responses to each question for every assessment that has been completed by a client. In addition to the clients response, the correct response is needed to score the assessment (1 point is given for each correct response, 0 for an incorrect response). The client’s overall score is tracked along with the date that the assessment was taken, as a client may take the same assessment multiple times. When this occurs, each assessment is maintained. The database must be able to support the production of an assessment report for each client (see Figure 3). Lastly, PATI maintains a file of financing opportunities. This information consists of a scholarship name, address, website address and eligible athletic league. Figure 2 Contact List Figure 3 Assessment Report Database Documentation and Design: Your project team has agreed to design and implement a database using the latest relational technology available. Your team has outlined several critical deliverables that will be delivered over the life of the project to demonstrate progress and to validate that your design is sound. The first deliverable will consist of a database scope statement, which will address the following: * What is the purpose of the database? * Who will use it? * What information is required and why? * What is the flow of information? * What will the subsequent deliverables consist of? * What is the timeframe for completing each step? Once the scope of the project has been determined, logical design of the database may begin. Your team has agreed to design an E/R model that supports this database, along with a data dictionary that describes each entity and attribute, the constraints and data types of each attribute, and a definition of each of the business rules needed to support this database. The third phase of the project (that your team has determined) is the physical construction of the database. If the optional requirements (see below) are to be included in the project then they will be addressed during this phase as well. During this phase all objects required to support this database will be constructed. In addition, access modeling will be documented, describing what information is required for reporting, where it will be obtained and what indexes will be constructed to facilitate access to the database. In the fourth and final phase of the project, queries and reports will be supported. The reports (described above) are each produced on a monthly basis and as such should be set up to be run on a recurring basis. It has also been determined that the database needs to support response to the following questions: 1. List all PATI clients who live outside the United States. 2. How many clients does PATI currently have? 3. Which clients played less than 4 seasons? 4. What is the average number of seasons played by clients from the NHL? 5. What percentage of clients has earned at least one degree? 6. List the names and addresses of each client that has earned more than one degree. 7. For those clients who have an employment history, what is the average number of jobs that clients have had? 8. Which clients are 50 years old or older (list their names and age)? 9. For each client, list the team that they played for the longest. 10. Which clients did not attend any college? 11. For each client, what is the average number of games played per season; sort your response by league and then client last name, first name. 12. For every assessment that has been taken, how many clients scored greater than 75%? 13. For every assessment that has been taken, which clients scored greater than 75%? 14. Which clients have taken more than 3 assessments? 15. Which clients have taken assessments that have had a response left blank? (include the clients name, the assessment title and the question number and text that was left blank) 16. Which assessment has the fewest questions? 17. Which scholarships are available to NHL players/alumni? 18. Create SQL that will update a client’s assessment overall score when a correct response to a question is added to the database. 19. Which clients have taken the same assessment more than once (indicate the clients name and the title of the assessment)? 20. Which clients have not provided any references? Optional Requirements: The database needs to support current needs, but also should be able to support rapid expansion as a result of growth in the business. Therefore, it has been determined that the database will be separated into three separate groups; client information, assessment information and educational information. Create a procedure to back-up the database on a weekly basis; retaining at least three iterations of back-ups. In addition, create a procedure to restore the database from one of the back-up files. Test the procedures to ensure that the database can be safely and correctly restored. Establish a new user who only has access rights to query the database, but is not permitted to update the database. In addition, establish a new user who has database administrative rights. Note for Instructors: This case has been used in undergraduate and graduate course work in database management systems. It provides students an opportunity to develop a relational model that utilizes a variety of relationships and as such provides a thorough reinforcement of design skills. The queries and reports test a wide range of SQL commands. The optional requirements are designed to reinforce database administrative concepts. Solution sets for Oracle Version 9, SQL Server 2000 or Microsoft Access 2003 are available upon request. ?? ?? ?? ??