Help needed with a database design

shadow

Registered User.
Local time
Yesterday, 23:27
Joined
Nov 26, 2008
Messages
13
Hi there, i have been given this description of a database that needs to be created, but am getting very confused as to what tables and Primary and forign keys i need to be using in the tables.

"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."

I have started work on it and know i need:

Customer table (Customer ref, First name, Surname, ect)
Claims table (Claims ref, policy ref.)

But im a little confused as to how i am to do the rest.

Any help at all would be much appreciated, Thanks Adam
 
Judging by the wording of the question and the number of previous posts, this sounds like either homework or an exam question. Fair cop, Guv'ner?

This forum is aimed at MS Access users to help each other but, if you're honest up front, I'm sure you'll get some assistance with the question you've posted.

Can I suggest looking at the sample and template databases installed or available within MS Access? There won't be one for an insurance agent but draw an analogy and see what you can find.
 
Something like this would be a starting point...

tbl_Agent
tbl_InsuranceCompanies
tbl_Customer
tbl_Policies
tbl_BusinessArea
tbl_Claims
 
Sorry i should have said, yes this is homework. But the teacher isn't really the best.. i mean she went on an access course 2 weeks ago to learn access, keeping in mind that 2 weeks ago was the middle of the term lol

I have Attached a photo of my relationship diagram so far.. but i think its wrong. Any help would be appreciated.

Thanks, Adam

access.jpg
 
Well done for coming clean. Also, having worked in insurance in a previous career, the question was obviously put together by somebody who doesn't fully understand what they're talking about!

Personally, I'd say that your Packages table is superfluous, as is your Business/Products. As any Product can only be a member of one Business area, and any one Company may supply Products in more than one Business area, this isn't a Many to Many relationship.

Thus:
  1. Company > Business > Product
  2. Customer > Policy > Product
  3. Customer > Policy > Claims
 
so it would look a little like this:

Company table(Company id, Business type(forign field), Product type(forign field), Name, addres...)
Business table(Business type,)
Products table(Product type)

???
 
Looks cool. Is there any way in seeing the finished version?
 
Judging by the wording of the question and the number of previous posts, this sounds like either homework or an exam question. Fair cop, Guv'ner?

This forum is aimed at MS Access users to help each other but, if you're honest up front, I'm sure you'll get some assistance with the question you've posted.

Can I suggest looking at the sample and template databases installed or available within MS Access? There won't be one for an insurance agent but draw an analogy and see what you can find.

good response
 
I know, was just hoping for a bit of luck, doing a bit of a project myself haha.
 
Does anyone know any good link for Login forms for access?
 
As i am looking gto start my own broker/small insurance co
 
vAs i am looki start my own broker/smalurance co:banghead::banghead::banghead:
 

Users who are viewing this thread

Back
Top Bottom