unknown symbol in ER diagram

[george]

Hi,

I'm looking at a database diagram and there is a symbol I
do not recognize. The diagram is found at

http://www.databaseanswers.org/data_models/insurance_broker
s/index.htm

What type of relationship does there exist between table
Policies (on one hand)and tables Life, Motor and Household
(on the other hand)? What type of relationship would there
be used to link these tables in Access?

Any help would be greatly appreciated,
thanks in advance, george

 

[Tim Ferguson]

What type of relationship does there exist between table
Policies (on one hand)and tables Life, Motor and Household
(on the other hand)?

I'n guessing: it's probably a SuperType-SubType arrangement. Each record in
each table in Life, Motor and Household tables relates to exactly one
record in the Policies table. Ideally, there should be a rule to prevent
any Policy existing in more than one subtype table, but AFAIK this is not a
feature of any existing RDBMS, so it has to be simulated using update
triggers.

Hope that helps


Tim F

 

[george]

Tim,

thank you very much for your reply. Could you give me any
hint as to what update triggers are or how they can be
used?

thanks again, george

 

[Tim Ferguson]

ALTER TABLE Sub1 ADD CONSTRAINT ch__sub1
CHECK (
main_ID NOT IN (SELECT main_ID FROM Sub2)
AND main_ID NOT IN (SELECT main_ID FROM Sub3)
)

Yes: that's reasonable. Do you know if this works in Jet, or what version?

All the best


Tim F

 

[Tim Ferguson]

thank you very much for your reply. Could you give me any
hint as to what update triggers are or how they can be
used?

Update triggers are a feature of fully-fledged RDBMSs, such as SQL
Server, Oracle, etc. They are not available in Access/ Jet so, if that is
what you are using, then treat this as academic only!

The essence is that you can define a SQL command(s) to be executed
whenever an update, delete or insert command affects a particular table.
Using this, you can make any checks or changes to the command you like,
cancel the whole transaction, etc. This gives a flexible approach to
implementing any kind of checks and constraints you like: at worst this
is an excuse for really bad db design (and usually makes it even more
complex and un-maintainable); at best it can allow a business rule to be
implemented that would be otherwise impossible.

Hope that helps


Tim F

 

[george]

Jamie, hi

I suppose then, I can use this code in Access. The problem
is I'm only a beginning programmer and I don't know how to
put all these into use. So let me ask a few questions

1. Do I create the tables manually or through sql?
2. Do I link my three subtables to my main table through
a 1:1 relationship?
3. Where do I write the sql code?

My final goal is to create something similar to the db
desing in the site I mentioned earlier and it's very
important to me to be able to finish it up so any hint
will be extremely helpful

thanks, george