An insurance database

[shadow]

Hi all. i have been given this as homework, but the teacher is usless. She's teaching an access course, but went herself on an Access course 2 weeks ago in the middle of term lol shouldnt she already know this?

Well anyways I went to see her 2 days ago and actually had to visit her office 7 times showing her my ER diagram which still i know is wrong but she doesnt know where lol

Here is the question:

"An insurace agent deals with a number of insurance companies. A policy-holder may have a number of policies with the insurace agent. 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, accidental damage, legal cover, windscreen cover ect. Claims are make against policies. A claim relates to only one policy. The company's products are grouped by business area i.e. life, motor, marine, home etc. Any particular product belongs to only one business area. Besides general update operations, the following processes must be supported by the system:

1. The production of renewal notices to be issued a month in advance.
2. List all policy details held by an individual customer.
3. List all claims made in the last 12 months; group by insurance company and customer."


And here is my database relationship diagram so far:

I know its not right, but seens as the teacher can't help im hoping someone on here can

Thanks, Adam Smith

 

[GaryPanic]

Customer table fine
Product table
need to have

product (motor, houshold,Avaition, marine ,life , misc , contingent ) combo box
insurer(supplier) being a cascading combo based on the above

so when you select say Motor you will only see the insurers who are offering this
policy number



Personally i would do this slightly differnetly
Product i would have
the insurer(supplier) and then the product
so
Axa- Marine
Axa- Motor
Axa- contingent
AIG- motor
Beezley - motor
Beezley -mairne
Cornhill-

this would simply your d/b a bit (given that there are only about 75 Lloyds insurers and say this number again non -lloyds



(Just my 2 pence worth)


Claims is always awkward depending on how you want it- usually for claims you want a link to the policy that it relates to ....

you can code the insurer/suppliers by a Key Number this will enable a claims by supplier and also enable a breakdown by product
claims by client simple in itself