Build your ER Model for the following Points given below: $[100$ Marks$]$

$1.[5$ Pts$.]$ Clients are individuals who buy Health Insurance Policy $($IP$)$ to cover their

medical expenses. They do this through Insurance Agents $($IA$),$ working in$-$person

or online.

$2.[5$ Pts$.]$ Clients are identified by $(1)$ their unique Govt. issued Health Card Number,

which has $10$ digits and $2$ letters at the end, $(2)$ their names, $(3)$ addresses, and $(4)$

ages as records.

$3.[5$ Pts$.]$ Insurance Agents $($IA$)$ are professionals working for various Insurance

Companies, who contact Clients and recommend Insurance Policy $($IP$)$ for them to buy.

$4.[5$ Pts$.]$ Canadian Insurance Agents $($IA$)$ working for various health insurance companies are identified by their $(1)$ Canadian SIN number, which has $9$ digits. For each

Agent, also the $(2)$ name, $(3)$ specialty, and $(4)$ years of experience must be recorded.

$5.[10$ Pts$.]$ Every Client has a primary Insurance Agent. They may also work with

other Agents. Every Agent has at least one Client, which does not restrict them to

having other clients simultaneously.

$6.[5$ Pts$.]$ Helth Policy Issuers $($HPI$)$ are insurance companies that are recognized by

Provincial Health Department $($PHD$)$ and o$\_$er Insurance Policy $($IP$)$ for Clients to

buy. Examples of HPI are Canada Life, Manulife, Blue$-$cross Blue$-$shield, GMS$,$ etc.

$7.[5$ Pts$.]$ Each Health Policy Issuer is identified by $(1)$ name and $(2)$ has a contact

phone number.

$8.[5$ Pts$.]$ For each Insurance Policy $($IP$),(1)$ the Issuers' trade name and $(2)$ symbol

must be recorded.

$9.[5$ Pts$.]$ A given HPI sells each Insurance, and the trade name identifies security

uniquely from among the issuer's products. If an issuer is deleted, you need not

keep track of its products any longer.

$10.[5$ Pts$.]$ Provincial Health Department $($PHD$)$ is the ministry in a Canadian Province

that is provided with various Insurance Policies $($IP$)$ records to assist through any

possible disputes on claims.

$11.[5$ Pts$.]$ PHD in provinces are identified by $(1)$ name of the province, $(2)$ o$\_$ce

address, and $(3)$ contact phone number.

$12.[10$ Pts$.]$ Each PHD lists several types of insurances and has a recommended price

and currency for each. A particular type of Insurance may be listed at several PHD$,$

and the price could vary from one province to another.

$13.[10$ Pts$.]$ Insurance Agents $($IA$)$ recommend Insurance Policies $($IP$)$ for Clients. An

Agent could recommend one or more Policies for several Clients, and one Client could

obtain recommendations from several Agents.

$14.[5$ Pts$.]$ Each Recommendation has a $(1)$ date and $(2)$ a quantity associated with it$.$

You can assume that if an Agent recommends the same Policy for the same Patient

more than once, only the last such recommendation needs to be stored, and previous

ones are ignored.

$15.[10$ Pts$.]$ Agents have long$-$term contracts with PHD$.$ An Agent can have a contract

with several provincial PHD$,$ and each PHD can render a contract to several Agents.

For each contract, you have to store a $(1)$ start date, $(2)$ an end date, and $(3)$ the

text of the contract.

Submission:

$1.$ERD in a PDF$.$

$2.$ Please ensure your answers are well$-$formatted and easy to read: Lines connecting tables are obvious, and for each relationship, the Cardinality Ratios and their directions,

are specified $($whether they are one$-$to$-$many, one$-$to$-$one, or many$-$to$-$many$).$

$3.$ It is preferable to use the naming conventions that have business acumen and are

self$-$explanatory. For example, use SIN for the SIN number, clientID for the Client

ID and so on$.$ It should be easy for anyone reading the diagram to understand