I have a case study that involves being a consulting firm that has to provide consulting services to a motion pictures company that wants to create a new database system. I need to create an extended$-$ER diagram, out of the ER diagram I already have. Below is the project summary and attachment of ER diagram.

$1.$ Purpose of the Case:

The primary goal of this case is to help students apply theoretical knowledge of systems analysis and database design to a real$-$world setting. MPIPP $($Motion Picture Industry Pension Plan$)$ serves as the case study to expose students to complex data processing requirements similar to what they might encounter in professional environments.

The specific educational objectives are:

Reinforce the systems analysis and design concepts learned in class.

Enhance skills in gathering user requirements and developing system models.

Provide practical experience in designing data models, data dictionaries, and system flowcharts.

Simulate real$-$world challenges, such as working with pension and retirement plan systems.

$2.$ Background on MPIPP:

MPIPP was created to provide pension benefits for unionized behind$-$the$-$scenes workers in the film and TV industries $($e$.$g$.,$ electricians, camera operators, and set designers$).$ The organization manages two main pension plans:

Basic Pension Plan $($BPP$)$: A defined$-$benefit pension plan where retirement benefits are calculated based on employment history $($hours worked, salary history, etc.$).$

Individual Account Plan $($IAP$)$: A defined$-$contribution plan funded entirely by employers, similar to a $401($k$)$ plan, but with uncertain retirement benefit amounts based on investment returns.

The case is based on the actual data and requirements of MPIPP, although some complexities have been excluded or simplified to make it suitable for classroom use.

$3.$ Operations and Data Requirements:

The case outlines the core processes that the MPIPP system needs to manage, including:

Employer Management: Employers, typically film or television production companies, need to submit information to MPIPP when they begin a new project. Each employer is assigned a unique ID and must provide details about the projects and employee contributions.

Union Management: Local unions represent specific trades, such as electricians or costume designers. The MPIPP system tracks union codes and classifies workers based on their specific roles, salaries, and pension contributions.

Employee Contributions: Employers submit monthly reports detailing the hours worked and wages paid to union workers. Based on these reports, employers contribute to both the BPP and IAP plans on behalf of their employees.

Participants $($Employees$)$: New participants $($union workers$)$ are identified when contributions are made in their name. The system tracks their pension eligibility, vesting status, and beneficiary information.

Beneficiaries: MPIPP tracks designated beneficiaries who would receive pension benefits in case of a participant's death.

Qualified Domestic Relations Order $($QDRO$)$: The system must account for pension benefits allocated to ex$-$spouses following a divorce, as directed by court orders.

MPIPP Pension Administration System MPIPP Pension Administration System