You will need to submit the entity relationship data model in MWB format with a screenshot either in JPG or PNG format, after adding at least three records to each table. The logical model should include the tables, attributes, keys, madatory role constraints, and referential interity constraints. I will also need the output script in SQL format or text-based file after forward engineering the designed ER-model.
Ryerson University CIND110 Data Organization for Data Analysts Assignment I Modeling a Relational Database Starts: Friday, September 15, 2021, 8:00 PM Due: Thursday, October 16, 2021, 11:59 PM This assignment counts for 10% of the final grade General Instructions: 1. In this assigned exercise, the ER/EER diagram is highly recommended to be done only using MySQL Workbench tool instructed in live sessions and provided as lab material in your course shell. Hand drawn diagram and/or diagrams done using any drawing software will not be graded. 2. Please ensure your answers are well-formatted and easy to read: Lines connecting ta- bles 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. Build your Model following the Points given below: [Total Points: 100] 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 Com- panies, who contact Clients and recommend Insurance Policy (IP) for them to buy. 4. [5 Pts.] Canadian Insurance Agents (IA) working for various health insurance com- panies 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 offer Insurance Policy (IP) for Clients to buy. Examples of HPI are Canada Life, Manulife, Blue-cross Blue-shield, GMS, etc. 1 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) office 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. What you need to Submit on Course Shell: 1. SUBMIT The Entity Relationship data model in MWB format with a screen-shot either in JPG or PNG format, after adding at least three records to each table. The logical model should include the tables, attributes, keys, mandatory role constraints, and referential integrity constraints. 2. SUBMIT The output script in SQL format or text-based file (e.g. TXT or DOCX), after forward engineer the designed ER-model. The script should include the struc- ture of the tables in addition to the data stored in them. 2