Converting an EERD to Access database - Total Specialisation of supertype and subtype

[blinton]

Hi,



I am struggling to understand how to convert Enhanced Entity Relationship diagrams to tables in Microsoft Access where total specialisation in supertype/subtype relationships is involved. For example in the diagram I can represent a relationship between an entity 'vehicle' as a supertype and two subtype entities 'car' and 'truck' where the car and truck subtypes have some unique attributes that are associated with only cars or only trucks and the vehicle table has attributes that are common to both cars and trucks. I can represent that in Access easily enough.

I can use the relationships tool in access to ensure that an item that is in the car table must also be in the vehicle table and that an item in the truck table is also in the vehicle table but as my database stands in access an item could be a member of the vehicle table without needing to be in either the car table or the vehicle table.

In the EER diagram I can show 'total specialisation' by adding a mandatory constraint to the relationship between the vehicle entity and the car and truck entities but in access I don't understand how I can enforce the same constraint. If I made a one to one relationship between the car table and the vehicle table and the same again for the truck table to the vehicle table then I would not be able to enter any entries in the vehicle table because they would need to exist first in both the subtype tables (which defeats the purpose of having subtype tables).

Any assistance that can be provided on how to create tables in access that reflect total specialisation (a member of a supertype must be a member of at least one subtype) would be greatly appreciated.

Cheers,

Ben

[orange]

There is a slide presentation on EERD at http://www.docstoc.com/docs/21874736...ced-ER-diagram
but it does NOT show how to move from the model to a relational database.

There is a write up about converting super and subtype relationships to tables here
http://www.dhdurso.org/articles/database-design.html
but there is no example of this.

I found these independently while looking for EERD and super and subtype models to tables.

Good luck. If you find more info or examples, or work thru your own example, please post your findings so we can all benefit.

Edit:
I found a powerpoint presentation on EERD at http://laku.net/MIS5612/chap04.ppt but no example of converting to tables

And a word doc here http://www.siue.edu/~dbock/cmis450/4-eermodel.htm

You may get some ideas on implementation in Access here
http://www.utteraccess.com/forum/Sup...-t1744599.html

And I discovered a thread in this forum, but do not see a final /follow/recommendation at
https://www.accessforums.net/showthr...bers-and-Forms