unknown symbol in ER diagram

  • Thread starter Thread starter george
  • Start date Start date
G

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
 
What type of relationship does there exist between table
Policies (on one hand)and tables Life, Motor and Household
(on the other hand)?
Click to expand...

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
 
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 said:
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
Click to expand...

Do a google groups search for the exact phrase "CREATE TABLE SUV" for a
Joe Celko post on the subject. <Quote> use a compound candidate
key (vin, vehicle_type) and a constraint in each sub-class table to
assure that the vehicle_type is locked and agrees with the Vehicles
table </Quote>.

Jamie.

--
 
Tim said:
is
not a

Do a google groups search for the exact phrase "CREATE TABLE SUV" for a
Joe Celko post on the subject. <Quote> use a compound candidate
key (vin, vehicle_type) and a constraint in each sub-class table to
assure that the vehicle_type is locked and agrees with the Vehicles
table </Quote>.
Click to expand...

Thinking about this some more, if you don't like the vehicle_type style
lockdown, I think you could use CHECK constraints to do the same thing.
The following is quick/dirty and off the top of my head (there must be
a more efficient way than NOT IN):

CREATE TABLE Main (
main_ID INTEGER NOT NULL PRIMARY KEY
)
;
CREATE TABLE Sub1 (
main_ID INTEGER NOT NULL PRIMARY KEY,
CONSTRAINT fk__sub1 FOREIGN KEY (main_ID)
REFERENCES Main (main_ID)
)
;
CREATE TABLE Sub2 (
main_ID INTEGER NOT NULL PRIMARY KEY,
CONSTRAINT fk__sub2 FOREIGN KEY (main_ID)
REFERENCES Main (main_ID)
)
;
CREATE TABLE Sub3 (
main_ID INTEGER NOT NULL PRIMARY KEY,
CONSTRAINT fk__sub3 FOREIGN KEY (main_ID)
REFERENCES Main (main_ID)
)
;
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)
)
;
ALTER TABLE Sub2 ADD CONSTRAINT ch__sub2
CHECK (
main_ID NOT IN (SELECT main_ID FROM Sub1)
AND main_ID NOT IN (SELECT main_ID FROM Sub3)
)
;
ALTER TABLE Sub3 ADD CONSTRAINT ch__sub3
CHECK (
main_ID NOT IN (SELECT main_ID FROM Sub1)
AND main_ID NOT IN (SELECT main_ID FROM Sub2)
)
;

Jamie.

--
 
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)
)
Click to expand...

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

All the best


Tim F
 
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?
Click to expand...

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
 
Tim said:
Yes: that's reasonable. Do you know if this works in Jet, or what
Click to expand...
version?

The above is from my test uising Jet 4.0 via the OLE DB provider (ADO).


Jamie.

--
 
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
 
george said:
1. Do I create the tables manually or through sql?
Click to expand...

Some of the features I use (e.g. CHECK constraints) cannot be *created*
in MS Access versions earlier than Access2003 without using ADO code.
Anyhow, I always create my databases using a SQL script. This way, if
something requires changing during the design phase, I can simply amend
the script, DROP the database (i.e. Kill the .mdb file) and start over.

If not using Access2003 in 'ANSI mode', you can use the Execute method
of an ADO connection to your database e.g. this in the Access2000 VBE
Immediate Window:

CurrentProject.Connection.Execute _
"CREATE TABLE Main (
"main_ID INTEGER IDENTITY(1,1));"
2. Do I link my three subtables to my main table through
a 1:1 relationship?
Click to expand...

You could use a foreign key constraint with a unique constraint so I
guess this is a 1:1 relationship. I prefer Celko's "CREATE TABLE SUV"
approach. At first I thought it a bit odd to have vehicle_type in the
referencing table but it's needed for composite key PK and hence the
FK. My example using CHECK constraints was to show it was possible but
I don't consider it desirable.
3. Where do I write the sql code?
Click to expand...

I mainly use a text editor.

Jamie.

--
 
Back
Top