How do you do 1NF, 2NF, and 3NF

How many payments have been processed for any given policy to date? Hint: Draw an initial ERD for the text description then use view integration mechanisms to integrate the 3NF relations from the claim form to this initial ERD to arrive at your final relational database design c) [20 marks] Discuss methods of controlling data integrity for the following relations based on your logical ERD i. The relation which would store all the data for claims. ii. The relation which would store the data for customers of the organisation d) [15 marks] Discuss THREE situations where denormalization could be used to improve performance in your model when converting your proposed logical design (in part b) above) to physical tables. Be as specific as possible in your discussion. Include segments of modified diagrams to aid your discussion Requirements: For each situation explain how your denormalised design would improve performance of the physical model For each situation explain how your denormalised design may introduce trades-off for effectiveness. 2.2.1 A Star Insurance Company Data Description A Star Insurance is an insurance company which has been in operation for over 10 years. The company is well known for its innovative vehicle insurance products catered to both individuals and organizations. The company has over 700 employees currently and boasts of a customer base of nearly 250000 customers. The following specification and documents capture only part of the data requirement for A Star Insurance. All company 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. Interviews with stakeholders have revealed the following details regarding employees. Insurance advisors help customers purchase insurance products. He/She provides advice to customers' regarding various insurance policies through appointments. They are also responsible for processing claims. Telemarketers reach out to potential customers to promote vehicle insurance products via phone calls. They set up appointments for the advisors. Some advisors are telemarketers too as they will call and book appointments for themselves. One of the telemarketers is appointed as team leader and supervises other telemarketers Admin staff manage various administrative matters within the company such as generating reports, processing payments, facilitating recruitment events etc. These admin staff are the employees who are not directly involved in insurance-specific matters, such as receptionists, HR managers, etc. Actuary evaluates and designs insurance product options suitable for various situations. . The Customers (clients) can be individuals or organisations. There are some insurance product options tailored specifically for business vehicle needs of various organizations. If the product purchase is on behalf of an organisation, then information about the organisation is noted which includes name, industry, and annual revenue. There will be one main contact such as the 'Admin Manager' that the company corresponds with directly regarding the policies purchased by the organization If the product is purchased by an individual customer then their details such as name, phone number, address, and gender are recorded. Customers' occupation, salary and level 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 Appointments are made by telemarketers or advisors who function within the telemarketer role with prospective individual or corporate customers, to enable an insurance advisor to discuss product options with the customers. All appointments must be approved by a telemarketer team leader before the advisor meets with the prospective customer. Until an appointment is approved by the team leader the appointment status is *awaiting approval'. Once the team leader approves it the status is updated to approved'. 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 telemarketers are 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. Some appointments have follow up appointments as prospective customers sometimes need additional information before they decide to purchase insurance products. The company offers some vehicle insurance products tailored specifically for business organizations so that the business risk can be reduced. Vehicle insurance products tailored specifically for individuals are also available. These products could be of many different types, such as comprehensive/ third party single vehicle cover for individuals and organisation, comprehensive/third party cover for a fleet of corporate vehicles young driver cover for individuals, rental car insurance for individuals and companies etc. Insurance products are designed, reviewed and adjusted by actuary. 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. Page 4 of 7 Individual or business customers can purchase insurance products tailored to their individual or business vehicle 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 since minor adjustment is possible for some customers. 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. Organizations make payments annually by EFTPOS, cheque or cash, Individuals 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. Astar hopes to enable online payments for their customers later. Astar maintains a log of payments received for each policy. A claim can be made by the customers clients) when they seek the benefits provided by the insurance company due to a liability occurrence covered by the policy for an insured vehicle. Details about all insured vehicles are stored in the database. A standard form must be filled when a customer company or individual) wants to make a claim, key fields in the form include: description of claimed situation, policy ID, claim lodged date etc. The standard form used for this claim lodgement purpose can be downloaded here. You must check to ensure that your model records all the relevant information about claims indicated in this claim form. When a claim is lodged, the details about the incident (accident) is recorded in the database, using a Vehicle AccidentID as a unique identifier. It is possible that a driver other than the insured person (client) was driving the vehicle when the claim incident occurred. AStar stores details about drivers for the claims raised as accident and driver information is sometimes sought by external parties in legal proceedings. When a claim is lodged, details about damages to the insured vehicle are recorded in the database, using a VehicleDamageID as an identifier. This VehicleDamage record also indicates the lowest quote value for fixing the insured vehicle, as well as a damage description and a diagram showing the damage. It is possible that one incident could cause damage to multiple other vehicles involved in the incident and multiple pay-outs must be made from the insurance policy. The details of all vehicles impacted, all drivers of vehicles at time of incident and the owners of vehicles impacted are recorded together with a description of damage caused to each involved vehicle. When a claim is received, an insurance advisor will review the customer's policy and conduct a thorough investigation into the claimed damages/loss to mitigate fraudulent claims. Page 5 of 7 If the claim is determined to be genuine and approved by the advisor 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 reference. How many payments have been processed for any given policy to date? Hint: Draw an initial ERD for the text description then use view integration mechanisms to integrate the 3NF relations from the claim form to this initial ERD to arrive at your final relational database design c) [20 marks] Discuss methods of controlling data integrity for the following relations based on your logical ERD i. The relation which would store all the data for claims. ii. The relation which would store the data for customers of the organisation d) [15 marks] Discuss THREE situations where denormalization could be used to improve performance in your model when converting your proposed logical design (in part b) above) to physical tables. Be as specific as possible in your discussion. Include segments of modified diagrams to aid your discussion Requirements: For each situation explain how your denormalised design would improve performance of the physical model For each situation explain how your denormalised design may introduce trades-off for effectiveness. 2.2.1 A Star Insurance Company Data Description A Star Insurance is an insurance company which has been in operation for over 10 years. The company is well known for its innovative vehicle insurance products catered to both individuals and organizations. The company has over 700 employees currently and boasts of a customer base of nearly 250000 customers. The following specification and documents capture only part of the data requirement for A Star Insurance. All company 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. Interviews with stakeholders have revealed the following details regarding employees. Insurance advisors help customers purchase insurance products. He/She provides advice to customers' regarding various insurance policies through appointments. They are also responsible for processing claims. Telemarketers reach out to potential customers to promote vehicle insurance products via phone calls. They set up appointments for the advisors. Some advisors are telemarketers too as they will call and book appointments for themselves. One of the telemarketers is appointed as team leader and supervises other telemarketers Admin staff manage various administrative matters within the company such as generating reports, processing payments, facilitating recruitment events etc. These admin staff are the employees who are not directly involved in insurance-specific matters, such as receptionists, HR managers, etc. Actuary evaluates and designs insurance product options suitable for various situations. . The Customers (clients) can be individuals or organisations. There are some insurance product options tailored specifically for business vehicle needs of various organizations. If the product purchase is on behalf of an organisation, then information about the organisation is noted which includes name, industry, and annual revenue. There will be one main contact such as the 'Admin Manager' that the company corresponds with directly regarding the policies purchased by the organization If the product is purchased by an individual customer then their details such as name, phone number, address, and gender are recorded. Customers' occupation, salary and level 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 Appointments are made by telemarketers or advisors who function within the telemarketer role with prospective individual or corporate customers, to enable an insurance advisor to discuss product options with the customers. All appointments must be approved by a telemarketer team leader before the advisor meets with the prospective customer. Until an appointment is approved by the team leader the appointment status is *awaiting approval'. Once the team leader approves it the status is updated to approved'. 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 telemarketers are 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. Some appointments have follow up appointments as prospective customers sometimes need additional information before they decide to purchase insurance products. The company offers some vehicle insurance products tailored specifically for business organizations so that the business risk can be reduced. Vehicle insurance products tailored specifically for individuals are also available. These products could be of many different types, such as comprehensive/ third party single vehicle cover for individuals and organisation, comprehensive/third party cover for a fleet of corporate vehicles young driver cover for individuals, rental car insurance for individuals and companies etc. Insurance products are designed, reviewed and adjusted by actuary. 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. Page 4 of 7 Individual or business customers can purchase insurance products tailored to their individual or business vehicle 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 since minor adjustment is possible for some customers. 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. Organizations make payments annually by EFTPOS, cheque or cash, Individuals 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. Astar hopes to enable online payments for their customers later. Astar maintains a log of payments received for each policy. A claim can be made by the customers clients) when they seek the benefits provided by the insurance company due to a liability occurrence covered by the policy for an insured vehicle. Details about all insured vehicles are stored in the database. A standard form must be filled when a customer company or individual) wants to make a claim, key fields in the form include: description of claimed situation, policy ID, claim lodged date etc. The standard form used for this claim lodgement purpose can be downloaded here. You must check to ensure that your model records all the relevant information about claims indicated in this claim form. When a claim is lodged, the details about the incident (accident) is recorded in the database, using a Vehicle AccidentID as a unique identifier. It is possible that a driver other than the insured person (client) was driving the vehicle when the claim incident occurred. AStar stores details about drivers for the claims raised as accident and driver information is sometimes sought by external parties in legal proceedings. When a claim is lodged, details about damages to the insured vehicle are recorded in the database, using a VehicleDamageID as an identifier. This VehicleDamage record also indicates the lowest quote value for fixing the insured vehicle, as well as a damage description and a diagram showing the damage. It is possible that one incident could cause damage to multiple other vehicles involved in the incident and multiple pay-outs must be made from the insurance policy. The details of all vehicles impacted, all drivers of vehicles at time of incident and the owners of vehicles impacted are recorded together with a description of damage caused to each involved vehicle. When a claim is received, an insurance advisor will review the customer's policy and conduct a thorough investigation into the claimed damages/loss to mitigate fraudulent claims. Page 5 of 7 If the claim is determined to be genuine and approved by the advisor 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 reference