Database Schema for a boat

[playah]

Hi,

i am trying to come up with a schema for a databaes. It's been a while since I have done normalization on a schema, so I could use some help.

Situation:

There are boats.
A boat is build in a shipyard.
A shipyard has an owner.
A boat is bought by an owner.
When a boat is bought by an owner, I still need to see from which shipyard the boat was bought.

A fairly simple situation you might say, by maybe my head isn't clear.
I have attached a picture with a schema I came up with.

I don't think this schema is the best solution.

In this situation a shipyard has an owner (which is correct) and the owner has a boat (which is also corrcet). Also the shipyard has a boat (could be correct, if the boat is not sold yet).

I don't think I can easily get a query that can tell me if a shipyard is still the owner, or the boat has been sold.( of course it can be done with attributes).

What would you guys come up with as a schema. I have another schema, but will not show so I don't blur your minds.

thanks

[tesuji]

Hi playah,

your data model seems to be able to answer almost all questions you have asked. Also this one: Are there owners who own both, boats and shipyards? select owner.name, shipyard.name, boat.name from owner join shipyard on owner.onwerID = shipyard.onwerID join boat on owner.onwerID = boat.onwerID;

If you want to find whether a boat still belongs to the shipyard because of not having been sold so far, one solution would be to distinguish between both sorts of owners, for example by adding a category attribute to the owner entity, say category {'private individual', 'shipyard company'}. Then the select may look like this one: select owner.name, boat.name from owner join boat on owner.ownerID = boat.ownerID where owner.category = 'shipyard company';

Another data model might be based on ERM generalization/specialization (isa-relationships), where you would have one common owner entity for generic data and two sub categories (specializations) to hold specific data on private boat owners and shipyards on the other hand.

krs,
tesu