Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please draw the ERD showing the relationships, and labelling the PK and FK Draw a logical ERD in crow's foot notation for the case described
Please draw the ERD showing the relationships, and labelling the PK and FK
Draw a logical ERD in crow's foot notation for the case described below. Make sure to indicate all relevant entity sets, attributes, primary keys, foreign keys, relationships, and cardinalities. If you need to make any assumptions, state them clearly in your answer. PayTime Insurance PayTime Insurance is an insurance company which has been in operation for over 9 years. The company is well known for its innovative insurance products catered to individuals. The company has over 500 employees currently and boasts of a customer base of nearly 250000 customers. The following specification captures only part of the data requirement for PayTime Insurance. Employee All the employees should have their personal information stored in the database (e.g., name, age, gender, phone number, address and email). It is also important to keep track of the person's role at a specific time as employees can rotate roles throughout their employment. Some roles which employees currently fulfil include insurance advisor, telemarketer, actuary and admin staff such as receptionists, HR managers, etc. Customer Customers are individuals. When an insurance product is purchased by a customer then their details such as name, phone number, address, and gender are recorded. Customers' occupations, salaries and levels of risk involved in their jobs are stored in the database. This information is used to calculate the premium for the insurance products that individuals purchase. Appointment Employees make appointments with prospective individual customers, to discuss product options with the customers. The meetings may be conducted as face-to-face personal interviews or may even be conducted virtually via online teleconferencing tools. Date, time and terms discussed need to be recorded for each appointment. The employee is paid a commission for each successful appointment which results in a new customer purchasing a policy. Recording the status of 'successful' or 'unsuccessful' for each appointment is important for commission calculation purposes. Product Products tailored specifically for individuals such as car, house, travel and contents insurance are available. Insurance products are designed, reviewed and adjusted by an employee in the actuary role. Many actuaries can work together and design many products over time. Each product is reviewed by an actuary before the product terms are finalized after the initial design, and again periodically to ensure that products are competitive and meet the market requirements. The actuary who designs a product cannot be the one who evaluates it. Some changes may be made to products after an actuary reviews it. The history of changes is recorded, as the company wishes to know how their products have evolved over time. All products have associated liabilities covered, which describe the liabilities and costs that the individual or business can be insured for. All products have a maximum cover amount which cannot be exceeded. For all individual products there is a qualifying age which applies. Purchasing a Policy Customers can purchase insurance products tailored to their individual needs as a policy. The date and time a policy is purchased is recorded. Coverage of the product for each purchase needs to be recorded. The premium of each purchase varies depending on the purchaser's personal information and the product they are purchasing. Each purchase also has an excess amount decided by both the purchaser and the company. Each purchased policy has an end date on which the terms are reviewed/adjusted. When policies are renewed they are treated as a new purchase. Four weeks before a policy is set to expire the system generates and sends reminders to customers so that the customers will have enough time to consider the renewal of the policy. There are many ways in which the payment of premium for a policy can be made. Customers can choose to pay annually upfront by EFTPOS, cheque or cash, or pay fortnightly or monthly by direct debit. For customers who select to pay by direct debit, there bank account information will be stored. The company hopes to enable online payments for their customers at a later time. The company maintains a log of payments received for each policy. Claim A claim can be made by the customers when they seek the benefits provided by the insurance company due to a liability occurrence covered by the policy. A standard form must be filled when a customer wants to make a claim, key fields in the form include description of claimed situation, policy ID, claim lodged date and time. When a claim is received, an employee will review the customer's policy and conduct a thorough investigation into the claimed damages/loss to mitigate fraudulent claims. If the claim is determined to be genuine and approved by the employee conducting the investigation, then the claim is paid out and the date and time when the payment was processed is recorded. Not all insurance claims are paid out, but all the claims submitted by a customer should be recorded in his/her profile for future referenceStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started