2023 1 Consider the following scenario A policy holder may have a number of policies | Assignment Collections

1. Consider the following scenario: A policy-holder may have a number of policies with the insurance company. Each policy is given a policy number and relates to a single policy-holder. The company has a range of insurance products and may put together a range of products to form a policy. Examples of motor products are: third party, fire, theft, accident damage, windscreen cover, etc. Brokers sell policies for commission and any one policy may have commission payable to more than one broker. Claims are made against policies. A claim relates to only one policy and each claim is classified according to one of six claim types. The company’s products are grouped by business area, i.e. life, motor, marine, etc. Any particular product belongs to only one business area. The company holds information on clubs and associations, for promotions and selective mailings. A policy-holder may belong to a number of different associations. (a) Produce an Entity-Relationship diagram which represents the above activities of the insurance company. Indicate the names and degrees of all the relationships. Give appropriate attributes for each entity shown, and identify the primary key for each entity. (b) Give three queries which would be most likely to be asked in this scenario and give a clear explanation of how each may be answered. 2. Arrive at a relational schema for the given Entity-Relationship model. Identify the primary and foreign keys for each relation described. You must state any design decisions you make. 3. This question requires you to give your own examples of various elements of modelling and SQL. (a) Give an example of each of the following: (i) an entity type (ii) involuted or recursive relationship (iii) optional relationship (iv) m:n relationship. (b) Give an example, using precise SQL, of each of the following: (i) a query based on a single table (ii) a query based on details from two tables (iii) inserting a row from a table (iv) deleting a row…